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:
Posts (Atom)