performed Switchover and Switchback in Oracle 12c

12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1

 Performed Switchover and Switchback  in Oracle 12c
  1. 2 instances Oracle 12c RAC primary database ( TSTDB)
  2. Single instance Oracle 12c RAC Standby database ( TSTSBY)
  3. Noted – all the issues – documented steps

Switchover and Switchback in 12c

1.       Ensure log_archive_dest_2 is configured on PRIMARY and STANDBY databases
2.       Media Recovery Process (MRP) is active on STANDBY and in sync with PRIMARY database
3.       Create STANDBY REDO logs on PRIMARY, if not exists
4.       FAL_CLIENT & FAL_SERVER parameters set on both databases

   

 Pre-Switchover in 12c

 oracle@raj1 (SID:TSTDB_1) /h01/app/oracle/db/12.1.0.2_03/dbs $ srvctl status database -d TSTDB

 Instance TSTDB_1 is running on node raj1
Instance TSTDB_2 is running on node raj2
DGMGRL> connect /

 Connected as SYSDG.

 DGMGRL> show configuration

  Configuration - TSTDBCONF

   Protection Mode: MaxPerformance

   Members:

   TSTDB  - Primary database

     TSTSBY - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:

 SUCCESS   (status updated 59 seconds ago)
Let’s run the command on the primary database to validate if the environments are ready for the role transition

SQL> select status , error from v$archive_dest where dest_id=2;

STATUS    ERROR

 --------- -----------------------------------------------------------------

 INACTIVE
SQL> alter database switchover to TSTSBY verify;

 alter database switchover to TSTSBY verify

 *ERROR at line 1:

 ORA-16475: succeeded with warnings, check alert log for more details


 SQL> alter database switchover to TSTSBY;

Database altered.


 SQL>

  oracle@raj1 (SID:TSTDB_1) /h01/app/oracle/db/12.1.0.2_03/dbs $ srvctl status database -d TSTDB

 Instance TSTDB_1 is not running on node raj1

 Instance TSTDB_2 is not running on node raj2

 oracle@(SID:TSTDB_1) /h01/app/oracle/db/12.1.0.2_03/dbs

  SQL> select status,instance_name,database_role from v$database,v$instance;


 STATUS       INSTANCE_NAME    DATABASE_ROLE

 ------------ ---------------- ----------------

 MOUNTED      TSTSBY           PRIMARY


 SQL> alter database open;
Database altered.


 SQL>


 SQL> select status , error from v$archive_dest where dest_id=2;
STATUS    ERROR

 --------- -----------------------------------------------------------------

 VALID
SQL> alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

  System altered.
ALTER SYSTEM SET log_archive_dest_2='service="TSTDB"','ASYNC NOAFFIRM delay=1 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTDB" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;


 SQL> select status,instance_name,database_role from v$database,v$instance;


 STATUS       INSTANCE_NAME    DATABASE_ROLE

 ------------ ---------------- ----------------

 MOUNTED      TSTDB_1          PHYSICAL STANDBY

SQL> alter database recover managed standby database using archived logfile disconnect;

  Database altered.

2019-04-15 14:58:17.261000 +10:00

 Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_1_seq_97.1086.1005663391

 Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_2_seq_69.2336.1005662909

 Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_2_seq_70.4376.1005663391

 Resetting standby activation ID 0 (0x0)

 2019-04-15 14:58:18.367000 +10:00

 Media Recovery End-Of-Redo indicator encountered

 Media Recovery Continuing

 Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_2_seq_71.3281.1005663441

 Media Recovery Delayed for 1 minute(s) (thread 1 sequence 98)

 2019-04-15 14:58:19.586000 +10:00

 Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_1_seq_98.3285.1005663439

 Media Recovery Waiting for thread 1 sequence 99 (in transit)

 2019-04-15 15:00:57.038000 +10:00

 RFS[1]: Selected log 6 for thread 1 sequence 100 dbid 3526761822 branch 1005127022

 Archived Log entry 777 added for thread 1 sequence 99 ID 0xd2f7d7c3 dest 1:

 ARC6: Archive log thread 1 sequence 99 available in 1 minute(s)

 Media Recovery Delayed for 1 minute(s) (thread 1 sequence 99)

 2019-04-15 15:01:01.067000 +10:00

 RFS[1]: No standby redo logfiles available for T-1

 RFS[1]: Opened log for thread 1 sequence 101 dbid 3526761822 branch 1005127022

 Archived Log entry 778 added for thread 1 sequence 100 ID 0xd2f7d7c3 dest 1:

 ARC7: Archive log thread 1 sequence 100 available in 1 minute(s)

To revert (switch back) to the previous situation, perform the same action. Remember, now, your primary is your previous STANDBY and standby is previous PRIMARY. 



 TSTDB_1 - up and running

 TSTDB_2 - down

TSTSBY is primary


 SQL> alter database switchover to TSTDB verify;

 alter database switchover to TSTDB verify

 *

 ERROR at line 1:

 ORA-16475: succeeded with warnings, check alert log for more details

Online log +FLASH/TSTDB/ONLINELOG/group_23.4033.1004695265: Thread 2 Group 23 was previously cleared

 Online log +DATA/TSTDB/ONLINELOG/group_24.525.1004695265: Thread 2 Group 24 was previously cleared

 Online log +FLASH/TSTDB/ONLINELOG/group_24.1681.1004695265: Thread 2 Group 24 was previously cleared

 Standby became primary SCN: 14048950

 Switchover: Complete - Database mounted as primary

 SWITCHOVER: completed request from primary database
SQL> alter database switchover to TSTDB;


 Database altered.
SQL>


 TSTDB
SQL> alter database open;
Database altered. 

 ALTER SYSTEM SET log_archive_dest_2='service="TSTSBY"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSBY" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH ;

stop
srvctl stop database -d TSTDB


 start

srvctl start database -d TSTDB
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

 DGMGRL> connect /

 Connected as SYSDG.

 DGMGRL> show configuration

Configuration - TSTDBCONF

  Protection Mode: MaxPerformance

   Members:

   TSTDB  - Primary database

     TSTSBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

 SUCCESS   (status updated 59 seconds ago)

DGMGRL>

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more