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

Your email address will not be published.