Switchover and Switchback between Primary RAC and Physical Standby (Single Instance): ======================================================================================== Check the instances in Primary RAC ===================================== [oracle@rac-p1 ~]$ ps -ef | grep pmon oracle 4770 1 0 13:16 ? 00:00:00 asm_pmon_+ASM1 oracle 7833 1 0 13:18 ? 00:00:00 ora_pmon_racp1 oracle 22071 21019 0 15:13 pts/1 00:00:00 grep pmon [oracle@rac-p1 ~]$ [oracle@rac-p1 ~]$ srvctl status database -d racp Instance racp1 is running on node rac-p1 Instance racp2 is running on node rac-p2 [oracle@rac-p1 ~]$ Shutdown the 2nd instance of Primary RAC ============================================ [oracle@rac-p1 ~]$ srvctl stop instance -d racp -i racp2 [oracle@rac-p1 ~]$ [oracle@rac-p1 ~]$ srvctl status database -d racp Instance racp1 is running on node rac-p1 Instance racp2 is not running on node rac-p2 [oracle@rac-p1 ~]$ Login to Primary RAC and start "switcover" process ===================================================== [oracle@rac-p1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 4 15:14:27 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> select open_mode,database_role from gv$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE PRIMARY SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- NOT ALLOWED SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SQL> alter system set log_archive_dest_2='SERVICE="racs" LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="racs" net_timeout=30' scope=both sid='*'; System altered. Note-1: If DG broker is in place we have to set some of the parameters in Primary RAC and Physical Standby Note-2: If “Sessions Active”: alter database commit to switchover to physical standby with session shutdown; SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> alter database commit to switchover to physical standby; Database altered. SQL> shut immediate; ORA-01012: not logged on [oracle@rac-p1 ~]$ . oraenv ORACLE_SID = [racp1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac-p1 ~]$ [oracle@rac-p1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 4 15:21:25 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 943718400 bytes Fixed Size 2931136 bytes Variable Size 411043392 bytes Database Buffers 524288000 bytes Redo Buffers 5455872 bytes Database mounted. SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racp MOUNTED PHYSICAL STANDBY SQL> alter database open; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ ---------------------------- -------------------- racp READ ONLY WITH APPLY PHYSICAL STANDBY Switchover Single Instance from Physical Standby to Primary role ==================================================================== SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racs READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> alter database commit to switchover to primary; Database altered. SQL> shu immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 3338665984 bytes Fixed Size 2929888 bytes Variable Size 805309216 bytes Database Buffers 2516582400 bytes Redo Buffers 13844480 bytes Database mounted. SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racs MOUNTED PRIMARY SQL> alter database open; Database altered. SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racs READ WRITE PRIMARY Checking the transactions between Single Instance (Primary Database) to RAC (Physical Standby Instance) ======================================================================= SQL> insert into scott.dept values (90,'ORACLE','US'); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SQL> alter system set log_archive_dest_2='SERVICE="racp" LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="racp" net_timeout=30' scope=both; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. Check the transactions in RAC (Physical Standby Database) ============================================================ SQL> select * from scott.dept where deptno=90; DEPTNO DNAME LOC ---------- -------------- ------------- 90 ORACLE US Switchback Single Instance (racs) from Primary role to Physical Standby Database role ======================================================================================== SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> select db_unique_name,open_mode,database_role from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racs READ WRITE PRIMARY SQL> alter database commit to switchover to physical standby; Database altered. SQL> shutdown immediate; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options [oracle@rac-stdby ~]$ [oracle@rac-stdby ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 4 15:33:46 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 3338665984 bytes Fixed Size 2929888 bytes Variable Size 805309216 bytes Database Buffers 2516582400 bytes Redo Buffers 13844480 bytes Database mounted. SQL> alter database open; Database altered. SQL> select db_unique_name,open_mode,database_role from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------------- racs READ ONLY PHYSICAL STANDBY SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select db_unique_name,open_mode,database_role from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ ---------------------------- ------------------- racs READ ONLY WITH APPLY PHYSICAL STANDBY Switchback Primary RAC from Physical Standby Database role to Primary Database role ====================================================================================== [oracle@rac-p1 ~]$ srvctl status database -d racp Instance racp1 is running on node rac-p1 Instance racp2 is not running on node rac-p2 [oracle@rac-p1 ~]$ exit SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ --------------------------- --------------------- racp READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> alter database commit to switchover to primary; Database altered. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 943718400 bytes Fixed Size 2931136 bytes Variable Size 411043392 bytes Database Buffers 524288000 bytes Redo Buffers 5455872 bytes Database mounted. SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racp MOUNTED PRIMARY SQL> alter database open; Database altered. SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racp READ WRITE PRIMARY SQL> ! Note: Once Switchover and Switchback done start the second instance of Primary RAC [oracle@rac-p1 ~]$ srvctl status database -d racp Instance racp1 is running on node rac-p1 Instance racp2 is not running on node rac-p2 [oracle@rac-p1 ~]$ [oracle@rac-p1 ~]$ srvctl start instance -d racp -i racp2 [oracle@rac-p1 ~]$ [oracle@rac-p1 ~]$ srvctl status database -d racp Instance racp1 is running on node rac-p1 Instance racp2 is running on node rac-p2 [oracle@rac-p1 ~]$ Check the transactions between Primary RAC to Physical Standby (Single Instance) ===================================================================== SQL> select db_unique_name,open_mode,database_role from gv$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- racp READ WRITE PRIMARY racp READ WRITE PRIMARY SQL> insert into scott.dept values (92,'ORACLE','US'); 1 row created. SQL> commit; Commit complete. SQL> alter system switch all logfile; System altered. SQL> alter system switch all logfile; System altered. Login to Single Instance (Physical Standby Database) ======================================================= SQL> select db_unique_name,open_mode,database_role from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ ---------------------------- --------------------- racs READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select * from scott.dept where deptno=92 order by deptno; DEPTNO DNAME LOC ---------- -------------- ------------- 92 ORACLE US SQL> Hope it helps.... I will try the same process using 'dgmgrl' and let see...... If any comments also please welcome ...... I will try to modify...
Tuesday, December 4, 2018
Switchover and Switchback between Primary RAC and Physical Standby (Single Instance)
Subscribe to:
Post Comments (Atom)
Please post using dgmgrl ASAP. Thanks
ReplyDeleteNice Article. Can you please mention one TNS entry having both Primary and standby entry which can handle the switchover and doesn't require a manual deactivate(primary TNS) and activate(standby TNS) at middlware/weblogic data source.
ReplyDeleteWhy you stop one RAC instance?
ReplyDeletethank
I really liked your blog post.Much thanks again. Awesome.
ReplyDeleteSAP Grc training
SAP Secrity training
oracle sql plsql training
go langaunage training