Skip to content

Oracle Standby HA Service

    COLUMN name FORMAT A30
    COLUMN network_name FORMAT A30

    SELECT name,
    network_name
    FROM dba_services
    ORDER BY 1;

    create or replace procedure start_ha_service
    is
    v_role VARCHAR(30);
    begin
    select DATABASE_ROLE into v_role from V$DATABASE;
    if v_role = ‘PRIMARY’ then
    DBMS_SERVICE.START_SERVICE(‘ip7_ha’);
    else
    DBMS_SERVICE.STOP_SERVICE(‘ip7_ha’);
    end if;
    end;
    /

    TRIGGER TO START SERVICE ON DB STARTUP:

    create or replace TRIGGER ha_on_startup
    after startup on database
    begin
    start_ha_service;
    end;
    /

    TRIGGER TO START SERVICE ON DB ROLECHANGE:

    create or replace TRIGGER ha_on_role_change
    after db_role_change on database
    begin
    start_ha_service;
    end;
    /

    SQL> exec DBMS_SERVICE.CREATE_SERVICE (service_name => ‘ip7_ha’, network_name => ‘ip7_ha’, failover_method => ‘BASIC’, failover_type => ‘SELECT’, failover_retries => 30, failover_delay => 10);

    PL/SQL procedure successfully completed.

    SQL> exec start_ha_service;

    PL/SQL procedure successfully completed.

    SQL> alter system archive log current;
    SQL> alter system archive log current;
    SQL> alter system archive log current;

    $lsnrctl services

    LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 01-SEP-2020 19:03:24

    Copyright (c) 1991, 2019, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-19-dg1.localdomain)(PORT=1521)))
    Services Summary…
    Service “86b637b62fdf7a65e053f706e80a27ca.world” has 1 instance(s).
    Instance “cdb1”, status READY, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:4 refused:0 state:ready
    LOCAL SERVER
    Service “addf32572088016fe0536538a8c061b3.world” has 1 instance(s).
    Instance “cdb1”, status READY, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:4 refused:0 state:ready
    LOCAL SERVER
    Service “cdb1” has 1 instance(s).
    Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:1 refused:0
    LOCAL SERVER
    Service “cdb1.world” has 2 instance(s).
    Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:0 refused:0
    LOCAL SERVER
    Instance “cdb1”, status READY, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:4 refused:0 state:ready
    LOCAL SERVER
    Service “cdb1XDB.world” has 1 instance(s).
    Instance “cdb1”, status READY, has 1 handler(s) for this service…
    Handler(s):
    “D000” established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER
    (ADDRESS=(PROTOCOL=tcp)(HOST=ol7-19-dg1.localdomain)(PORT=21805))
    Service “cdb1_CFG.world” has 1 instance(s).
    Instance “cdb1”, status READY, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:4 refused:0 state:ready
    LOCAL SERVER
    Service “cdb1_DGMGRL.world” has 1 instance(s).
    Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:0 refused:0
    LOCAL SERVER
    Service “ip7_ha.world” has 1 instance(s).
    Instance “cdb1”, status READY, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:4 refused:0 state:ready
    LOCAL SERVER
    Service “pdb1.world” has 1 instance(s).
    Instance “cdb1”, status READY, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:4 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully

    TNS Names

    ====> Client System

    ip7_ha=
    (DESCRIPTION=
    (CONNECT_TIMEOUT=10 ms)(RETRY_COUNT=3)
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=1521)))
    (CONNECT_DATA=
    (SERVICE_NAME=ip7_ha.world)))

    SQL> select name,open_mode,database_role from v$database;
    
    NAME      OPEN_MODE            DATABASE_ROLE
    --------- -------------------- ----------------
    CDB1      READ WRITE           PRIMARY
    
    SQL> /
    
    NAME      OPEN_MODE            DATABASE_ROLE
    --------- -------------------- ----------------
    CDB1      READ WRITE           PRIMARY

    SQL> select name,db_unique_name,open_mode,database_role from v$database;

    NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
    --------- ------------------------------ -------------------- ----------------
    CDB1      cdb1                           CLOSED BY SWITCHOVER PHYSICAL STANDBY
    
    SQL> /
    
    NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
    --------- ------------------------------ -------------------- ----------------
    CDB1      cdb1_stby                      READ WRITE           PRIMARY
    
    SQL>
    SQL>
    [[email protected] admin]$ dgmgrl sys/[email protected]
    DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue Sep 1 19:05:24 2020
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

    Welcome to DGMGRL, type “help” for information.
    Connected to “cdb1”
    Connected as SYSDBA.
    DGMGRL> show configuration;

    Configuration – my_dg_config

    Protection Mode: MaxPerformance
    Members:
    cdb1 – Primary database
    Error: ORA-16778: redo transport error for one or more members

    cdb1_stby - Physical standby database
      Error: ORA-12541: TNS:no listener

    Fast-Start Failover: Disabled

    Configuration Status:
    ERROR (status updated 3 seconds ago)

    DGMGRL> show configuration;

    Configuration – my_dg_config

    Protection Mode: MaxPerformance
    Members:
    cdb1 – Primary database
    cdb1_stby – Physical standby database
    Warning: ORA-16853: apply lag has exceeded specified threshold

    Fast-Start Failover: Disabled

    Configuration Status:
    WARNING (status updated 52 seconds ago)

    DGMGRL> switchover to cdb1_stby;
    Performing switchover NOW, please wait…
    Operation requires a connection to database “cdb1_stby”
    Connecting …
    Connected to “cdb1_stby”
    Connected as SYSDBA.
    New primary database “cdb1_stby” is opening…
    Operation requires start up of instance “cdb1” on database “cdb1”
    Starting instance “cdb1″…
    Connected to an idle instance.
    ORACLE instance started.
    Connected to “cdb1”
    Database mounted.
    Connected to “cdb1”
    Switchover succeeded, new primary is “cdb1_stby”
    DGMGRL> show configuration;

    Configuration – my_dg_config

    Protection Mode: MaxPerformance
    Members:
    cdb1_stby – Primary database
    Warning: ORA-16809: multiple warnings detected for the member

    cdb1      - Physical standby database

    Fast-Start Failover: Disabled

    Configuration Status:
    WARNING (status updated 202 seconds ago)

    DGMGRL>

    #

    BEGIN
    DBMS_SERVICE.modify_service(
    service_name => ‘ip7_ha’, network_name => ‘ip7_ha’
    );
    END;
    /

    BEGIN
    DBMS_SERVICE.stop_service(
    service_name => ‘ip7_ha’
    );
    END;
    /

    BEGIN
    DBMS_SERVICE.delete_service(
    service_name => ‘ip7_ha’
    );
    END;
    /

    BEGIN
    DBMS_SERVICE.disconnect_session(
    service_name => ‘ip7_ha’,
    disconnect_option => DBMS_SERVICE.immediate
    );
    END;
    /

    Leave a Reply