Description:
In this article we are going to performing the Oracle 11gR2 two node RAC to RAC manual failover steps to a physical standby database.
Let us consider, we have primary site with two node RAC and standby site also configured with two node RAC in 11gR2. Here, we can see the primary and standby configuration:
Let us verify the status and configuration of the RAC database:
$ srvctl config database -d ORCL_PRODRAC1 -a Database unique name: ORCL_PRODRAC1 Database name: ORCL Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1 Oracle user: oracle Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: ORCL_PRODRAC1 Database instances: ORCL1,ORCL2 Disk Groups: DBWR_DATA,DBWR_FRA Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed
$ srvctl config database -d ORCL_PRODRAC2 -a Database unique name: ORCL_PRODRAC2 Database name: ORCL Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1 Oracle user: oracle Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora Domain: Start options: read only Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: ORCL_PRODRAC2 Database instances: ORCL1,ORCL2 Disk Groups: DBWR_DATA,DBWR_FRA Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed
Let us assume, the primary and standby sites are geographical segmented and due to a natural disaster or some power outage issue both the nodes of the primary site become unavailable for the application and we may not be able to restore/recover the primary site within a reasonable period of time,. In this situation, we will be pushed to make the available standby database which is with most recent recovery to be converted into primary and reduce the downtime of the database and make the database available for continuation of the business.
So, let’s follow the below steps for the above scenario.
A) Pre-Checks for Failover
1) Ensure primary is using Flashback for reinstate the failed primary
2) Flush any unsent redo in primary
3) Verify the standby database has the most recent archived redo log from each primary redo thread
4) Identify and resolve any archived redo log gaps
5) Repeat Step 4 until all gaps are resolved for each thread
B) Failover
1) Stop Redo Apply
2) Finish applying all received redo data
3) Verify that the target standby database is ready to become a primary database
4) Switch the physical standby database to the primary role
5) Open the new primary database
Let us verify the status and configuration of the RAC database:
$ srvctl config database -d ORCL_PRODRAC1 -a Database unique name: ORCL_PRODRAC1 Database name: ORCL Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1 Oracle user: oracle Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: ORCL_PRODRAC1 Database instances: ORCL1,ORCL2 Disk Groups: DBWR_DATA,DBWR_FRA Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed
$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
A) Pre-Failover Checks
Step 1 – Ensure primary is using Flashback for reinstate the failed primary: (Primary Side)
In order to reinstate the failed primary database, the flashback option must be enabled in the primary
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO
If the flashback is disabled, follow the below step to enable flashback in the primary. This should be done before the failover.
I) Ensure db_recovery_file_dest is set
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE -------------------------- ----------- ------------------ db_recovery_file_dest string +DBWR_FRA db_recovery_file_dest_size big integer 12000M
If the above command returns any value, go for step III. Else set a proper directory or diskgroups.
SQL> alter system set db_recovery_file_dest='+DBWR_FRA' SCOPE=spfile sid='*'; System altered.
II) Ensure db_recovery_file_dest_size is set
SQL> alter system set db_recovery_file_dest_size=20G SCOPE=spfile sid='*'; System altered.
III) Stop and start the database
$ srvctl stop database -d ORCL_PRODRAC1 $ srvctl start database -d ORCL_PRODRAC1 -o mount SQL> alter database flashback on; Database altered. SQL> alter database open; <--- Both Nodes Database altered. SQL> alter system set db_flashback_retention_target=2880 sid='*'; System altered.
Let our scenario Primary Database Crush Due to several reason like Hardware failure.
B) Failover
Step 1 – Stop Redo Apply:- (Standby Side)
Issue the following SQL statement on the target standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
Step 2 – Finish applying all received redo data:
Issue the following SQL statement on the target standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered.
Step 3 – Verify that the target standby database is ready to become a primary database:
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the target standby database
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
A value of either TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and continue to query this view until either TO PRIMARY or SESSIONS ACTIVE is returned.
Step 4 – Switch the physical standby database to the primary role:
Issue the following SQL statement on the target standby database
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered.
Note:-
The WITH SESSION SHUTDOWN clause can be omitted from the switchover statement if the query of the SWITCHOVER_STATUS column performed in the previous step returned a value of TO PRIMARY.
Step 5 – Open the new primary database:
SQL> ALTER DATABASE OPEN; <-- Both Nodes Database altered.
$ srvctl status database -d ORCL_PRODRAC2 -v Instance ORCL1 is running on node prodrac201. Instance status: Open. Instance ORCL2 is running on node prodrac202. Instance status: Open.
Do not use srvctl command to start the secondary instance in the new primary database, since we configured the “Start options as read only” and Oracle Cluster Register do not recognize the switchover/failover automatically. So, if we use srvctl the secondary instance will shutdown as below
$ tail -f alert_ORCL2.log
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {1:13107:16648} */
ALTER DATABASE OPEN READ ONLY /* db agent *//* {1:13107:16648} */
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Errors in file /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL2/trace/ORCL2_ora_5999.trc:
ORA-16002: database already open for read/write access by another instance
ORA-16002 signalled during: ALTER DATABASE OPEN READ ONLY /* db agent *//* {1:13107:16648} */...
Sun Aug 19 16:15:39 2018
Shutting down instance (abort)
License high water mark = 1
USER (ospid: 6022): terminating the instance
Instance terminated by USER, pid = 6022
Sun Aug 19 16:15:39 2018
Instance shutdown complete
Note: We can modify the database configuration details as below and use srvctl if we need.
$ srvctl modify database -d ORCL_PRODRAC2 -r PRIMARY -s OPEN $ srvctl config database -d ORCL_PRODRAC2 -a Database unique name: ORCL_PRODRAC2 Database name: ORCL Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1 Oracle user: oracle Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: ORCL_PRODRAC2 Database instances: ORCL1,ORCL2 Disk Groups: DBWR_DATA,DBWR_FRA Mount point paths: Services: Type: RAC Database is enabled Database is administrator managed
- After this we will start the new primary Database using SRVCTL
- After failover take fresh RMAN backup of New primary database
Reference:
https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB00625