Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Today we will learn how to Automatically Connect Clients to New Primary in Role Changes (Client Connectivity – RAC).
Management of database services in RAC structure is done with SRVCTL utility.
The meanings of some attributes to be used when adding services are as follows.
-d: db_unique_name
-s: service_name
-r: instance_names
-l: db_roles
-e: failover_type {NONE | SESSION | SELECT}
-m: failover_method {NONE | BASIC}
-z: failover_retries
-w: failover_delay
If standby databases are created with Enterprise Manager, they are not automatically registered to Oracle Restart. So we add it manually as follows.
1 2 3 4 5 | srvctl add database -d <db_unique_name> -o <oracle_home> [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY }] [-s {start_options}] [-t {stop_options}] [-n <db_name>] [-y {AUTOMATIC | MANUAL}] [-a "<diskgroup_list>"] |
• If the Data Guard Environment is managed by the Broker, then the service is automatically started and the FAN ONS event is broadcast to the applications by the Broker itself, but if the Broker is not used, then this work is done with the help of Triggers.
○ STARTUP TRIGGER starts the service on the new Primary database.
○ The ROLE_CHANGE Trigger tells JDBC Clients to connect to the old Physical Standby instead of connecting to the Original Primary database.
• In the broker, this process is managed completely automatically. The process steps are as follows.
○ When the failover process is completed and the old Physical Standby database becomes Primary, the Broker broadcasts the old Primary as down and the new Primary as a FAN event.
○ In applications using JDBC, OCI or ODP.NET as Oracle Client, clients are automatically connected to the new Primary database with Fast Connection Failover (FCF) configuration.
• Let’s take a closer look at the Client Failover components.
Connect Time Failover: It redirects failed connection requests to secondary listener.
Transparent Application Failover: Allows the re-executing of failing SELECT statements, rollback of DML operations, and re-execution of ALTER SESSION statements by the application.
Fast Application Notification: When the original primary database fails, it sends a notification to the applications.
Let’s Test
1. We add service.
1 | [oracle@primary1 ~]$ srvctl add service -d primary -s prmy -r primary1,primary2 -l primary -m BASIC -e SELECT -w 1 -z 180 |
2. The status of the service is questioned.
1 2 | [oracle@primary1 ~]$ srvctl status service -d primary -s prmy Service prmy is not running. |
3. Since the service will be started from the operating system, we define the sh script at a location in the operating system.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [oracle@primary1 ~]$ cd $ORACLE_HOME [oracle@primary1 db_1]$ pwd /u01/app/oracle/product/11.2.0/db_1 [oracle@primary1 db_1]$ cd bin/ [oracle@primary1 bin]$ vi StartupService.sh #!/bin/ksh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$BASE_PATH export TIMESTAMP=`date '+%y%m%d%H%M%s'` export LOGFILE=/tmp/StartupService_${TIMESTAMP}.log echo "Starting $0 at `date`" > $LOGFILE HOSTNAME=`hostname | awk -F. '{print $1}'` echo "`date` : Starting prmy database service" >> $LOGFILE srvctl start service -d $DB_UNIQUE_NAME -s prmy -i $ORACLE_SID >> $LOGFILE 2>&1 echo "`date` : Start of prmy database service complete" >> $LOGFILE |
4. We create Trigger that will start the service.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [Primary-1] SQL> CREATE OR REPLACE TRIGGER manage_services_start after startup on database 2 3 DECLARE 4 role VARCHAR(30); 5 BEGIN 6 SELECT DATABASE_ROLE INTO role FROM V$DATABASE; 7 IF role = 'PRIMARY' THEN 8 dbms_scheduler.create_job( 9 job_name=>'start_dbsvc', 10 job_type=>'executable', 11 job_action=>'<ORACLE_HOME>/bin/StartupService.sh', 12 enabled=>TRUE 13 ); 14 END IF; 15 END; 16 / Trigger created. |
5. We check whether the trigger occurs on the Primary and Standby sides.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> select TRIGGER_NAME from dba_triggers where lower(trigger_name) like '%manage_service%'; TRIGGER_NAME ------------------------------ MANAGE_SERVICES_START [Physical-1] SQL> select TRIGGER_NAME from dba_triggers where lower(trigger_name) like '%manage_service%'; TRIGGER_NAME ------------------------------ MANAGE_SERVICES_START |
6. We restart the Primary database to see if the service has started in STARTUP.
1 2 3 4 5 6 7 8 9 10 11 | [oracle@primary1 bin]$ srvctl stop database -d primary [oracle@primary1 bin]$ srvctl status database -d primary Instance primary1 is not running on node primary1 Instance primary2 is not running on node primary2 [oracle@primary1 bin]$ srvctl start database -d primary [oracle@primary1 bin]$ srvctl status database -d primary Instance primary1 is running on node primary1 Instance primary2 is running on node primary2 |
7. It is checked whether the service starts automatically or not.
1 2 | [oracle@primary1 bin]$ srvctl status service -d primary -s prmy Service prmy is running on instance(s) primary1,primary2 |
8. It is checked whether the service is running in Physical Standby.
1 2 | [oracle@standby1 ~]$ srvctl status service -d standby -s physcl Service physcl is not running. |
9. We check whether the service starts automatically in Stanby by performing the switchover operation.
1 | https://dbtut.com/index.php/2022/04/05/switchover-from-primary-database-to-physical-standby-database-with-sqlplus-commands/ |
10. We check the status of the services on the Primary and Standby side.
1 2 3 4 5 | [oracle@primary1 ~]$ srvctl status service -d primary -s prmy Service prmy is not running. [oracle@standby1 ~]$ srvctl status service -d standby -s physcl Service physcl is not running. |
Could not understand why the service did not start. Will look again later.
