Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands.
In a system with a Data Guard Environment consisting of a dual-node Primary Database, a dual-node Physical Standby Database and a dual-node Logical Standby Database in a RAC structure, we perform a Failover operation to the Physical Standby Database.
We prefer Flashback because it is fast as a method of converting to Physical Standby Database, which will become DISABLE after failover.
This process will consist of 5 steps.
A. Preliminary Preparation
B. Failover to Physical Standby
C. Checks After Failover
C. Physical Standby of Old Primary with Flashback
D. Final Checks
A. PRELIMINARY PREPARATION
1. We check the parameters about whether Flashback is turned on in the databases and how long the Flashback logs will be stored.
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter db_flashback_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 [Primary-1] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO |
1 2 3 4 5 6 7 8 9 10 11 | [Physical-1] SQL> show parameter db_flashback_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 [Physical-1] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO |
1 2 3 4 5 6 7 8 9 10 11 | [Logical-1] SQL> show parameter db_flashback_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 [Logical-1] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO |
2. We activate flashback.
1 2 3 | [Primary-1] SQL> alter database flashback on; Database altered. |
1 2 3 4 5 6 7 | [Physical-1] SQL> alter database recover managed standby database cancel; Database altered. [Physical-1] SQL> alter database flashback on; Database altered. |
1 2 3 4 5 6 7 | [Logical-1] SQL> alter database stop logical standby apply; Database altered. [Logical-1] SQL> alter database flashback on; Database altered. |
3. We check the flashback status.
1 2 3 4 5 | [Primary-1] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
1 2 3 4 5 | [Physical-1] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
1 2 3 4 5 | [Logical-1] SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
4. We check whether flashback logs are created.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Primary-1] SQL> select file_type,number_of_files,percent_space_used from v$recovery_area_usage; FILE_TYPE NUMBER_OF_FILES PERCENT_SPACE_USED -------------------- --------------- ------------------ CONTROL FILE 0 0 REDO LOG 0 0 ARCHIVED LOG 760 27.55 BACKUP PIECE 0 0 IMAGE COPY 0 0 FLASHBACK LOG 0 0 FOREIGN ARCHIVED LOG 0 0 7 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Physical-1] SQL> select file_type,number_of_files,percent_space_used from v$recovery_area_usage; FILE_TYPE NUMBER_OF_FILES PERCENT_SPACE_USED -------------------- --------------- ------------------ CONTROL FILE 0 0 REDO LOG 0 0 ARCHIVED LOG 6 .12 BACKUP PIECE 0 0 IMAGE COPY 0 0 FLASHBACK LOG 4 1.99 FOREIGN ARCHIVED LOG 0 0 7 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Logical-1] SQL> select file_type,number_of_files,percent_space_used from v$recovery_area_usage; FILE_TYPE NUMBER_OF_FILES PERCENT_SPACE_USED -------------------- --------------- ------------------ CONTROL FILE 0 0 REDO LOG 0 0 ARCHIVED LOG 362 23.66 BACKUP PIECE 0 0 IMAGE COPY 0 0 FLASHBACK LOG 4 .5 FOREIGN ARCHIVED LOG 666 25.33 7 rows selected. |
5. We start Redo Apply operations on Standby Databases.
1 2 3 | [Physical-1] SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
1 2 3 | [Logical-1] SQL> alter database start logical standby apply immediate; Database altered. |
6. We check if there is Redo and Transport LAG after recovery.
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-1] SQL> set linesize 9000 [Physical-1] SQL> column name format a25 [Physical-1] SQL> column value format a20 [Physical-1] SQL> column time_computed format a25 [Physical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/20/2017 13:30:07 apply lag +00 00:00:00 01/20/2017 13:30:07 apply finish time +00 00:00:00.000 01/20/2017 13:30:07 estimated startup time 26 01/20/2017 13:30:07 |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> column name format a25 [Logical-1] SQL> column value format a20 [Logical-1] SQL> column time_computed format a25 [Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/20/2017 13:30:05 apply lag +00 00:00:00 01/20/2017 13:30:05 apply finish time +00 00:00:00.000 01/20/2017 13:30:05 estimated startup time 24 01/20/2017 13:30:05 |
7. After Flashback and Redo Apply, we check the accuracy of everything with DDL & DML processes.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ LOCATIONS_YEDEK REGIONS_YEDEK JOBS_YEDEK DEPARMENTS_YEDEK EMPLOYEES_YEDEK JOB_HISTORY_YEDEK 6 rows selected. |
1 2 3 4 5 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; select table_name from dba_tables where owner='TEST' * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only |
This error is received because the Physical Standby database is in Mount mode. If we want to see the results here, then Active Data Guard license is obtained and the database is OPEN.
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOB_HISTORY_YEDEK REGIONS_YEDEK LOCATIONS_YEDEK DEPARMENTS_YEDEK JOBS_YEDEK EMPLOYEES_YEDEK 6 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Primary-1] SQL> drop table test.JOB_HISTORY_YEDEK; Table dropped. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ LOCATIONS_YEDEK REGIONS_YEDEK JOBS_YEDEK DEPARMENTS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; select table_name from dba_tables where owner='TEST' * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only |
1 2 3 4 5 6 7 8 9 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK LOCATIONS_YEDEK DEPARMENTS_YEDEK JOBS_YEDEK EMPLOYEES_YEDEK |
B. FAILOVER TO PHYSICAL STANDBY DATABASE
We should try to fix the problem by making the necessary interventions during this time, perhaps taking into account some interruptions in the Primary Database, if not, we should consider the Failover process as a last resort.
Because after Failover, our Primary Database will be DISABLE. If we do not have a third standby database in our Data Guard Environment until we prepare it as Physical Standby, we will continue with a single system.
Now let’s see how the Failover process works.
1. Let’s assume that we can mount the Primary Database due to the problem, but it does not come to OPEN mode.
In this case, if we are not using the Maximum Protection mode, the first thing to do is to send archives and current redos from Primary to Physical Standby, if any, in order to avoid data loss.
If I succeed in this, it means that I will perform a Failover operation with zero data loss, which is a great thing.
For this, let’s set up our test environment first.
a. I want to break the sync of the standby side with the Primary. For this reason, I prevent certain archives from going there.
1 2 3 | [Primary-1] SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*'; System altered. |
b. We learn the current archive redo log numbers of the databases in the Data Guard Environment.
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 70 1 68 2 |
The reason we put a WHERE condition here is because RESETLOGS is done in the database for previous tests.
Because of this operation, I find the most up-to-date RESETLOGS_TIME from VIEW and write that value to the query, since the queue is back to the beginning.
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 70 1 68 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 70 1 YES 68 2 YES |
c. With the Log Switch operation, I check whether the archives are going to standby sides.
1 2 3 | [Primary-1] SQL> alter system switch logfile; System altered. |
1 2 3 | [Primary-2] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 71 1 69 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 71 1 69 2 |
Although LOG_ARCHIVE_DEST_STATE_2 is DEFERed, the reason why sequence# increases is because the logs accumulated until DEFER must be sent.
DEFER will not send the next logs instantly.
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 71 1 YES 69 2 YES |
ç. Before the tables to be created for test purposes, the tables belonging to the user are queried to see if the Redos are Transport and Apply.
1 2 3 4 5 6 7 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK |
1 2 3 4 5 6 7 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK |
1 2 3 4 5 6 7 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK |
d. We create a new table.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> create table test.job_history_yedek as select * from hr.job_history; Table created. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK |
1 2 3 4 5 6 7 8 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
e.We check the archive numbers by performing the Log Switch operation.
1 2 3 | [Primary-1] SQL> alter system switch logfile; System altered. |
1 2 3 | [Primary-2] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 72 1 70 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 71 1 69 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 72 1 YES 70 2 YES |
f. We create another table.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Primary-1] SQL> create table test.departments_yedek as select * from hr.departments; Table created. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK |
1 2 3 4 5 6 7 8 9 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK DEPARTMENTS_YEDEK |
g. We check the archive numbers by performing the Log Switch operation.
1 2 3 | [Primary-1] SQL> alter system switch logfile; System altered. |
1 2 3 | [Primary-2] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 73 1 71 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 71 1 69 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 73 1 YES 71 2 YES |
ğ. We create another table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [Primary-1] SQL> create table test.countries_yedek as select * from hr.countries; Table created. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ COUNTRIES_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK 6 rows selected. |
1 2 3 4 5 6 7 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK DEPARTMENTS_YEDEK COUNTRIES_YEDEK 6 rows selected. |
h. We are doing Log Switching.
1 2 3 | [Primary-1] SQL> alter system switch logfile; System altered. |
1 2 3 | [Primary-2] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 74 1 72 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 71 1 69 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 74 1 YES 72 2 YES |
I. I assume that the Primary does not switch to OPEN mode at the time of the disaster, and I put the Primary Database in MOUNT mode.
1 2 3 4 5 6 7 8 9 10 11 12 | [root@primary1 ~]# su - grid [grid@primary1 ~]$ srvctl status database -d primary Instance primary1 is running on node primary1 Instance primary2 is running on node primary2 [grid@primary1 ~]$ srvctl stop database -d primary [grid@primary1 ~]$ srvctl status database -d primary Instance primary1 is not running on node primary1 Instance primary2 is not running on node primary2 [grid@primary1 ~]$ srvctl start database -d primary -o mount [grid@primary1 ~]$ srvctl status database -d primary Instance primary1 is running on node primary1 Instance primary2 is running on node primary2 |
1 2 3 4 5 6 | [Primary-1] SQL> select status from gv$instance; STATUS ------------ MOUNTED MOUNTED |
i.I find the highest sequence# in each thread in Physical Standby and understand which archives are missing on the Standby side.
1 2 3 4 5 6 | [Physical-1] SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER(PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS'); THREAD LAST ---------- ---------- 1 71 2 69 |
j. Before moving on to failover actions, question the status and role of the Databases in the environment one last time.
1 2 3 4 5 6 | [Primary-1] SQL> select status from gv$instance; STATUS ------------ MOUNTED MOUNTED |
1 2 3 4 5 6 | [Physical-1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
1 2 3 4 5 6 | [Logical-1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
1 2 3 4 5 6 | [Primary-1] SQL> select database_role from gv$database; DATABASE_ROLE ---------------- PRIMARY PRIMARY |
1 2 3 4 5 6 | [Physical-1] SQL> select database_role from gv$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY PHYSICAL STANDBY |
1 2 3 4 5 6 | [Logical-1] SQL> select database_role from gv$database; DATABASE_ROLE ---------------- LOGICAL STANDBY LOGICAL STANDBY |
2. The job of installing the Test Environment is finished here. Now I am doing the job of sending the missing archive logs and CURRENT REDOs from the Primary on the Standby side.
a. We send Current Redos and Archive Logs to the Standby side.
1 2 3 4 5 | [Primary-1] SQL> ALTER SYSTEM FLUSH REDO TO 'standby'; ALTER SYSTEM FLUSH REDO TO 'primary' * ERROR at line 1: ORA-01105: mount is incompatible with mounts by other instances |
b. This error is received when all instances are open in RAC structure. That’s why I’m closing Node 2.
1 2 3 4 5 6 | [Primary-2] SQL> shu immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. |
1 2 3 4 5 | [Primary-1] SQL> select status from gv$instance; STATUS ------------ MOUNTED |
c. I try again to post archive and current redos.
1 2 3 4 5 | [Primary-1] SQL> ALTER SYSTEM FLUSH REDO TO 'standby'; ALTER SYSTEM FLUSH REDO TO 'standby' * ERROR at line 1: ORA-16416: No viable switchover targets available |
ç. The reason for this error is that the LOG_ARCHIVE_DEST_STATE_2 parameter, which will send LOGs to the Target it will fail, is deferred.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string DEFER |
1 2 3 | [Primary-1] SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*'; System altered. |
1 2 3 4 5 | [Primary-1] SQL> ALTER SYSTEM FLUSH REDO TO 'standby'; ALTER SYSTEM FLUSH REDO TO 'standby' * ERROR at line 1: ORA-16447: Redo apply was not active at the target standby database |
D. The reason for this error is that it shows that Redo-Transport is done but Redo-Apply is not. Below are the logs falling into ALERT_LOG.
[Primary-1]—–>ALERT LOG1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*'; Mon Jan 23 13:43:19 2017 ALTER SYSTEM FLUSH REDO TO 'standby' CONFIRM APPLY ALTER SYSTEM FLUSH REDO TO standby CONFIRM APPLY [Process Id: 3829] (primary1) Flush redo: No wait for non-current ORLs to be archived Waiting for all FAL entries to be archived... All FAL entries have been archived. Waiting for dest_id 2 to become synchronized... Mon Jan 23 13:43:22 2017 Deleted Oracle managed file +FRA/primary/archivelog/2017_01_10/thread_2_seq_216.492.932920295 Mon Jan 23 13:43:22 2017 Deleted Oracle managed file +FRA/primary/archivelog/2017_01_10/thread_2_seq_217.491.932920309 Mon Jan 23 13:43:22 2017 Deleted Oracle managed file +FRA/primary/archivelog/2017_01_10/thread_1_seq_356.490.932920311 Active, synchronized flush redo target has been identified Recovery is not running at physical standby 'LOG_ARCHIVE_DEST_2'.If this standby is the target, please startmanaged standby recovery at the target and re-issueFLUSH REDO command |
1 2 3 4 5 6 7 8 9 10 11 12 | Mon Jan 23 13:43:01 2017 RFS[12]: Assigned to RFS process 9146 RFS[12]: Opened log for thread 1 sequence 72 dbid 1769705496 branch 933872485 Mon Jan 23 13:43:02 2017 RFS[13]: Assigned to RFS process 9150 RFS[13]: Opened log for thread 1 sequence 74 dbid 1769705496 branch 933872485 Archived Log entry 1681 added for thread 1 sequence 72 rlc 933872485 ID 0x69946a6c dest 2: Archived Log entry 1682 added for thread 1 sequence 74 rlc 933872485 ID 0x69946a6c dest 2: Mon Jan 23 13:43:02 2017 RFS[14]: Assigned to RFS process 9156 RFS[14]: Opened log for thread 2 sequence 72 dbid 1769705496 branch 933872485 Archived Log entry 1686 added for thread 2 sequence 72 rlc 933872485 ID 0x69946a6c dest 2: |
1 2 3 4 5 6 7 8 9 10 | Mon Jan 23 13:43:27 2017 RFS[5]: Assigned to RFS process 21307 RFS[5]: Opened log for thread 1 sequence 73 dbid 1769705496 branch 933872485 Mon Jan 23 13:43:28 2017 RFS[6]: Assigned to RFS process 21316 RFS[6]: Opened log for thread 2 sequence 71 dbid 1769705496 branch 933872485 Mon Jan 23 13:43:28 2017 RFS[7]: Assigned to RFS process 21326 RFS[7]: Opened log for thread 2 sequence 70 dbid 1769705496 branch 933872485 Archived Log entry 1683 added for thread 1 sequence 73 rlc 933872485 ID 0x69946a6c dest 2: |
1 2 3 | [Physical-1] SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | Mon Jan 23 13:43:42 2017 alter database recover managed standby database using current logfile disconnect Attempt to start background Managed Standby Recovery process (primary1) Mon Jan 23 13:43:42 2017 MRP0 started with pid=53, OS id=9258 MRP0: Background Managed Standby Recovery process started (primary1) started logmerger process Mon Jan 23 13:43:47 2017 Managed Standby Recovery starting Real Time Apply Mon Jan 23 13:43:48 2017 Reconfiguration started (old inc 16, new inc 18) List of instances: 1 2 (myinst: 1) Global Resource Directory frozen Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Mon Jan 23 13:43:48 2017 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Mon Jan 23 13:43:48 2017 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Submitted all GCS remote-cache requests Fix write in gcs resources Reconfiguration complete Parallel Media Recovery started with 4 slaves Mon Jan 23 13:43:49 2017 Block change tracking file is current. Starting background process CTWR Mon Jan 23 13:43:49 2017 CTWR started with pid=59, OS id=9329 Block change tracking service is active. Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_1_seq_72.605.934033381 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_2_seq_69.273.934030667 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_2_seq_70.589.934033409 Completed: alter database recover managed standby database using current logfile disconnect Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_1_seq_73.604.934033407 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_2_seq_71.395.934033407 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_1_seq_74.608.934033381 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_2_seq_72.587.934033383 Media Recovery Waiting for thread 1 sequence 75 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | Archived Log entry 1684 added for thread 2 sequence 71 rlc 933872485 ID 0x69946a6c dest 2: Archived Log entry 1685 added for thread 2 sequence 70 rlc 933872485 ID 0x69946a6c dest 2: Mon Jan 23 13:44:13 2017 Managed Standby Recovery starting Real Time Apply Mon Jan 23 13:44:13 2017 Reconfiguration started (old inc 16, new inc 18) List of instances: 1 2 (myinst: 2) Global Resource Directory frozen Communication channels reestablished Mon Jan 23 13:44:13 2017 * domain 0 valid = 1 according to instance 1 Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Mon Jan 23 13:44:13 2017 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Mon Jan 23 13:44:13 2017 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Submitted all GCS remote-cache requests Fix write in gcs resources Reconfiguration complete |
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ COUNTRIES_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK 6 rows selected. |
In this case, I can minimize data loss by querying the largest SEQUENCE# in each THREAD in the Primary and Physical Standby Database and copying the missing archives to the Physical Standby Database at the operating system level and REGISTER. To test this,
I first set up a test environment.
a. I want to break the sync of the standby side with the Primary. For this reason, I prevent certain archives from going there.
1 2 3 | [Primary-1] SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*'; System altered. |
b. We are querying the user’s existing tables.
1 2 3 4 5 6 7 8 9 10 11 12 | Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ COUNTRIES_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK 6 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ COUNTRIES_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK 6 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK DEPARTMENTS_YEDEK COUNTRIES_YEDEK 6 rows selected. |
c. We are doing Log Switching.
1 2 3 4 5 6 7 8 9 10 | [Primary-1] SQL> alter system switch logfile; System altered. [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 79 1 74 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 79 1 74 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 79 1 YES 74 2 YES |
ç. We are querying the existence of the tables by dropping a table.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Primary-1] SQL> drop table test.COUNTRIES_YEDEK; Table dropped. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ COUNTRIES_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK 6 rows selected. |
1 2 3 4 5 6 7 8 9 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK DEPARTMENTS_YEDEK |
d. We are doing Log Switching.
1 2 3 4 5 6 7 8 9 10 | [Primary-1] SQL> alter system switch logfile; System altered. [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 80 1 74 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 79 1 74 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 80 1 YES 74 2 YES |
e. We are deleting another table.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> drop table test.DEPARTMENTS_YEDEK; Table dropped. Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ COUNTRIES_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK 6 rows selected. |
1 2 3 4 5 6 7 8 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
f. We are doing another Log Switch operation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Primary-1] SQL> alter system switch logfile; System altered. [Primary-1] SQL> alter system switch logfile; System altered. [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 82 1 74 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 79 1 74 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 82 1 YES 74 2 YES |
4. We are starting to work on sending the archive logs to the other Standby side.
a. We create folders in which archives will be saved. These folders are created in the sharing area so that both databases can access them.
1 2 | [root@primary1 nfs_alan]# mkdir nonexist_archived_logs [root@primary1 nfs_alan]# chmod -R 777 nonexist_archived_logs/ |
b. We copy the archives missing from ASM on the primary side to the folder created in the sharing area.
[Primary-1]
1 2 3 4 5 6 7 8 9 | ASMCMD> pwd +fra/primary/ARCHIVELOG/2017_01_23 ASMCMD> cp thread_1_seq_80.476.934034467 /nfs_alan/nonexist_archived_logs copying +fra/primary/ARCHIVELOG/2017_01_23/thread_1_seq_80.476.934034467 -> /nfs_alan/nonexist_archived_logs/thread_1_seq_80.476.934034467 ASMCMD> cp thread_1_seq_81.474.934034601 /nfs_alan/nonexist_archived_logs copying +fra/primary/ARCHIVELOG/2017_01_23/thread_1_seq_81.474.934034601 -> /nfs_alan/nonexist_archived_logs/thread_1_seq_81.474.934034601 ASMCMD> cp thread_1_seq_82.475.934034607 /nfs_alan/nonexist_archived_logs copying +fra/primary/ARCHIVELOG/2017_01_23/thread_1_seq_82.475.934034607 -> /nfs_alan/nonexist_archived_logs/thread_1_seq_82.475.934034607 |
1 2 3 4 | [root@primary1 nfs_alan]# cd /nfs_alan/nonexist_archived_logs/ [root@primary1 nonexist_archived_logs]# ls thread_1_seq_80.476.934034467 thread_1_seq_82.475.934034607 thread_1_seq_81.474.934034601 |
c. On the standby side, we copy the archive logs to the relevant folder in ASM.
[Standby-1]1 2 3 4 5 6 | ASMCMD> cp /nfs_alan/nonexist_archived_logs/thread_1_seq_80.476.934034467 +fra/standby/archivelog/2017_01_23/thread_1_seq_80 copying /nfs_alan/nonexist_archived_logs/thread_1_seq_80.476.934034467 -> +fra/standby/archivelog/2017_01_23/thread_1_seq_80 ASMCMD> cp /nfs_alan/nonexist_archived_logs/thread_1_seq_81.474.934034601 +fra/standby/archivelog/2017_01_23/thread_1_seq_81 copying /nfs_alan/nonexist_archived_logs/thread_1_seq_81.474.934034601 -> +fra/standby/archivelog/2017_01_23/thread_1_seq_81 ASMCMD> cp /nfs_alan/nonexist_archived_logs/thread_1_seq_82.475.934034607 +fra/standby/archivelog/2017_01_23/thread_1_seq_82 copying /nfs_alan/nonexist_archived_logs/thread_1_seq_82.475.934034607 -> +fra/standby/archivelog/2017_01_23/thread_1_seq_82 |
1 2 3 4 5 6 7 8 9 10 11 | [Standby-1] SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_80'; Database altered. [Standby-1] SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_81'; Database altered. [Standby-1] SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_82'; Database altered. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | Mon Jan 23 14:22:19 2017 ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_80' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Resynchronizing thread 1 from sequence 79 to 80 Completed: ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_80' Mon Jan 23 14:22:20 2017 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_1_seq_80 Media Recovery Waiting for thread 1 sequence 81 Mon Jan 23 14:22:34 2017 ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_81' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Resynchronizing thread 1 from sequence 80 to 81 Completed: ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_81' Mon Jan 23 14:22:37 2017 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_1_seq_81 Media Recovery Waiting for thread 1 sequence 82 Mon Jan 23 14:22:45 2017 ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_82' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Resynchronizing thread 1 from sequence 81 to 82 Completed: ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra/standby/archivelog/2017_01_23/thread_1_seq_82' Mon Jan 23 14:22:48 2017 Media Recovery Log +FRA/standby/archivelog/2017_01_23/thread_1_seq_82 Media Recovery Waiting for thread 1 sequence 83 |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 82 1 74 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('21/01/2017 17:01:25', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 82 1 74 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 82 1 YES 74 2 YES |
1 2 3 4 5 6 7 8 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 | [Physical-1] SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. |
1 2 3 | [Physical-1] SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | Mon Jan 23 14:30:28 2017 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH Attempt to do a Terminal Recovery (primary1) Media Recovery Start: Managed Standby Recovery (primary1) started logmerger process Mon Jan 23 14:30:28 2017 Managed Standby Recovery not using Real Time Apply Mon Jan 23 14:30:28 2017 Reconfiguration started (old inc 20, new inc 22) List of instances: 1 2 (myinst: 1) Global Resource Directory frozen Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Mon Jan 23 14:30:29 2017 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Mon Jan 23 14:30:29 2017 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Submitted all GCS remote-cache requests Fix write in gcs resources Reconfiguration complete Parallel Media Recovery started with 4 slaves Mon Jan 23 14:30:30 2017 Block change tracking file is current. Starting background process CTWR Mon Jan 23 14:30:30 2017 CTWR started with pid=51, OS id=16799 Block change tracking service is active. Media Recovery Waiting for thread 1 sequence 83 Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival Terminal Recovery timestamp is '01/23/2017 14:30:31' Terminal Recovery: applying standby redo logs. Terminal Recovery: thread 1 seq# 83 redo required Media Recovery Waiting for thread 1 sequence 83 Terminal Recovery: End-Of-Redo log allocation MRP: Validating standby redo logfile 5 Media Recovery Log +DATA/standby/onlinelog/group_5.267.932283683 Terminal Recovery: log 5 reserved for thread 1 sequence 83 Recovery of Online Redo Log: Thread 1 Group 5 Seq 83 Reading mem 0 Mem# 0: +DATA/standby/onlinelog/group_5.267.932283683 Mem# 1: +FRA/standby/onlinelog/group_5.260.932283683 Identified End-Of-Redo (failover) for thread 1 sequence 83 at SCN 0xffff.ffffffff Incomplete Recovery applied until change 8284490 time 01/23/2017 14:03:26 Media Recovery Complete (primary1) Terminal Recovery: successful completion Mon Jan 23 14:30:33 2017 ARC5: Archiving not possible: error count exceeded ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance primary1 - Archival Error ORA-16014: log 5 sequence# 83 not archived, no available destinations ORA-00312: online log 5 thread 1: '+DATA/standby/onlinelog/group_5.267.932283683' ORA-00312: online log 5 thread 1: '+FRA/standby/onlinelog/group_5.260.932283683' Forcing ARSCN to IRSCN for TR 0:8284490 Attempt to set limbo arscn 0:8284490 irscn 0:8284490 Resetting standby activation ID 1771334252 (0x69946a6c) Reconfiguration started (old inc 22, new inc 24) List of instances: 1 2 (myinst: 1) Global Resource Directory frozen Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Submitted all GCS remote-cache requests Fix write in gcs resources Reconfiguration complete Block change tracking service stopping. Stopping background process CTWR Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH |
If the GAP cannot be closed despite the operations, then the following command is executed considering the data loss.
1 2 3 | [Physical-1] SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; Database altered. |
7. If there is no need for the ACTIVATE PHYSICAL STANDBY command, we now query the status of the Physical Standby Database for Switchover.
1 2 3 4 5 | [Physical-1] SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY |
8. We do Physical Standby Database PRIMARY with Switchover process.
1 2 3 | [Physical-1] SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered. |
[Standby-1]—–>ALERT LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | Mon Jan 23 14:35:45 2017 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ALTER DATABASE SWITCHOVER TO PRIMARY (primary1) Maximum wait for role transition is 15 minutes. All dispatchers and shared servers shutdown CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. Mon Jan 23 14:35:46 2017 SMON: disabling cache recovery Mon Jan 23 14:35:46 2017 ARC2: Archiving not possible: error count exceeded Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/standby/primary1/trace/primary1_ora_15435.trc Standby terminal recovery start SCN: 8284489 RESETLOGS after complete recovery through change 8284490 Online log +DATA/standby/onlinelog/group_1.263.932283679: Thread 1 Group 1 was previously cleared Online log +FRA/standby/onlinelog/group_1.256.932283681: Thread 1 Group 1 was previously cleared Online log +DATA/standby/onlinelog/group_2.264.932283681: Thread 1 Group 2 was previously cleared Online log +FRA/standby/onlinelog/group_2.257.932283681: Thread 1 Group 2 was previously cleared Online log +DATA/standby/onlinelog/group_3.265.932283681: Thread 2 Group 3 was previously cleared Online log +FRA/standby/onlinelog/group_3.258.932283681: Thread 2 Group 3 was previously cleared Online log +DATA/standby/onlinelog/group_4.266.932283681: Thread 2 Group 4 was previously cleared Online log +FRA/standby/onlinelog/group_4.259.932283681: Thread 2 Group 4 was previously cleared Standby became primary SCN: 8284488 Mon Jan 23 14:35:52 2017 Setting recovery target incarnation to 6 Mon Jan 23 14:35:57 2017 AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file. Switchover: Complete - Database mounted as primary Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY |
9. We open the Physical Standby Databases.
1 2 3 | [Physical-1] SQL> alter database open; Database altered. |
1 2 3 | [Physical-2] SQL> alter database open; Database altered. |
1 2 3 4 5 6 | [Physical-1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
1 2 3 4 5 6 | [Logical-1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
10. When turning the old Physical Standby Database into OPEN mode for Primary Mode, errors are received about the old Primary Database as can be seen from the logs.
Therefore, the old Primary Database should be closed and the log flow should be stopped.
1 2 3 4 5 6 | [Primary-1] SQL> shu immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. |
With this process, the following error in the logs disappeared.
[Standby-1]—–>ALERT LOG1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary-scan.tivibulab.local)(PORT=1521)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)))(SDU=65535)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)(CID=(PROGRAM=oracle)(HOST=standby1.tivibulab.local)(USER=oracle)))) VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 23-JAN-2017 14:44:48 Tracing not turned on. Tns error struct: ns main err code: 12564 TNS-12564: TNS:connection refused ns secondary err code: 0 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 Error 12514 received logging on to the standby PING[ARC2]: Heartbeat failed to connect to standby 'primary'. Error is 12514. |
1 2 3 | [Physical-1] SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*'; System altered. |
C. CHECKS AFTER FAILOVER
1. We are querying the Roles, Recovery Modes and Apply Modes of the databases.
1 2 3 4 5 6 | [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> select d.open_mode, d.database_role, a.database_mode, a.recovery_mode, a.protection_mode from v$archive_dest_status a, v$database d where a.database_mode <> 'UNKNOWN'; OPEN_MODE DATABASE_ROLE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE -------------------- ---------------- --------------- ----------------------- -------------------- READ WRITE LOGICAL STANDBY OPEN IDLE MAXIMUM PERFORMANCE |
2. We start Redo Apply in Logical Standby Database.
1 2 3 | [Logical-1] SQL> alter database start logical standby apply immediate; Database altered. |
Although Redo Apply is started, IDLE appears again when the recovery mode is queried. Then we check from ALERT LOGS. It is seen that the following error is entered in the logs.
[Logical-1]—–>ALERT LOG1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | Mon Jan 23 14:01:56 2017 RFS[12]: Opened log for thread 2 sequence 1 dbid 1769705496 branch 934036549 Archive log rejected (thread 2 sequence 1) by RFS clients Mon Jan 23 14:01:59 2017 alter database start logical standby apply immediate ALTER DATABASE START LOGICAL STANDBY APPLY (primary1) with optional part IMMEDIATE Attempt to start background Logical Standby process Mon Jan 23 14:01:59 2017 LSP0 started with pid=51, OS id=23766 Completed: alter database start logical standby apply immediate LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 8284802, ResetLogScn 7750661 LOGMINER: summary for session# = 1 LOGMINER: StartScn: 0 (0x0000.00000000) LOGMINER: EndScn: 0 (0x0000.00000000) LOGMINER: HighConsumedScn: 8284796 (0x0000.007e6a7c) LOGMINER: session_flag: 0x1 LOGMINER: Read buffers: 16 LOGMINER: Memory LWM: limit 10M, LWM 24M, 80% LOGMINER: Memory Release Limit: 1M Fatal Error: LogMiner processed beyond new branch scn. LOGSTDBY status: ORA-01346: LogMiner processed redo beyond specified reset log scn Errors in file /u01/app/oracle/diag/rdbms/logical/primary1/trace/primary1_lsp0_23766.trc: ORA-01346: LogMiner processed redo beyond specified reset log scn |
Therefore, it is necessary to wrap the Logical Standby Database just before the SCN with RESETLOGS_CHANGE.
3. Although it is seen that there is no problem in Redo Transport, this is still seen with the Log Switch operation.
1 2 3 | [Physical-1] SQL> alter system switch logfile; System altered. |
[Standby-1]—–>ALERT LOG
1 2 3 4 5 6 7 8 | Mon Jan 23 14:58:40 2017 Thread 1 advanced to log sequence 9 (LGWR switch) Current log# 1 seq# 9 mem# 0: +DATA/standby/onlinelog/group_1.263.932283679 Current log# 1 seq# 9 mem# 1: +FRA/standby/onlinelog/group_1.256.932283681 Mon Jan 23 14:58:40 2017 Archived Log entry 1725 added for thread 1 sequence 8 ID 0x699440c0 dest 1: Mon Jan 23 14:58:40 2017 LNS: Standby redo logfile selected for thread 1 sequence 9 for destination LOG_ARCHIVE_DEST_3 |
[Logical-2]—–>ALERT LOG
1 2 3 4 | Mon Jan 23 14:16:48 2017 RFS[11]: Selected log 7 for thread 1 sequence 9 dbid 1769705496 branch 934036549 Mon Jan 23 14:16:48 2017 RFS LogMiner: Registered logfile [+FRA/logical/foreign_archivelog/standby/2017_01_23/thread_1_seq_8.1256.934035409] to LogMiner session id [1] |
[Standby-2]—–>ALERT LOG
1 2 3 4 5 | Mon Jan 23 14:59:17 2017 ARC4: Archive log rejected (thread 2 sequence 1) at host 'logical' FAL[server, ARC4]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance primary2 - Archival Error. Archiver continuing. |
4. After the Log Switch operation, we check whether the Redos go to Logical Standby.
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 9 1 6 2 |
1 2 3 4 5 6 7 8 9 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 9 1 NO 3 1 CURRENT 2 1 YES 6 2 NO 2 2 CURRENT |
As it can be understood from here, REDOs are going but cannot be processed.
5. We learn the SCN just before RESETLOGS.
1 2 3 4 5 | [Physical-1] SQL> select to_char(resetlogs_change# -2) from v$database; TO_CHAR(RESETLOGS_CHANGE#-2) ---------------------------------------- 8284489 |
6. We learn the current SCN of the Logical Standby Database.
1 2 3 4 5 | [Logical-1] SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 8428834 |
7. Logical Standby is wrapped with Database Flashback just before RESETLOGS (SCN in item 5).
1 2 3 4 5 | [Logical-1] SQL> flashback standby database to scn 8284489; flashback standby database to scn 8284489 * ERROR at line 1: ORA-38757: Database must be mounted and not open to FLASHBACK. |
8. We put the Database MOUNT mode.
1 2 3 4 | [Logical-1] SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. |
1 2 3 4 | [Logical-2] SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. |
1 2 3 4 5 6 7 8 9 | [Logical-1] SQL> startup mount; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. |
9. Again with the Logical Standby Database Flashback, it is wrapped just before the RESETLOGS (SCN in the 5th item).
1 2 3 4 5 | [Logical-1] SQL> flashback standby database to scn 8284489; flashback standby database to scn 8284489 * ERROR at line 1: ORA-01665: control file is not a standby control file |
10. We issue the command as follows.
1 2 3 4 5 | [Logical-1] SQL> flashback database to scn 8284489; flashback database to scn 8284489 * ERROR at line 1: ORA-38729: Not enough flashback database log data to do FLASHBACK. |
11. We query the relevant VIEW to go back to the earliest Flashback Logs and learn this information.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Logical-1] SQL> select table_name from dictionary where lower(table_name) like '%flashback%'; TABLE_NAME ------------------------------ DBA_FLASHBACK_ARCHIVE DBA_FLASHBACK_ARCHIVE_TABLES DBA_FLASHBACK_ARCHIVE_TS DBA_FLASHBACK_TXN_REPORT DBA_FLASHBACK_TXN_STATE USER_FLASHBACK_ARCHIVE USER_FLASHBACK_ARCHIVE_TABLES USER_FLASHBACK_TXN_REPORT USER_FLASHBACK_TXN_STATE V$FLASHBACK_DATABASE_LOGFILE V$FLASHBACK_DATABASE_LOG TABLE_NAME ------------------------------ V$FLASHBACK_DATABASE_STAT GV$FLASHBACK_DATABASE_LOGFILE GV$FLASHBACK_DATABASE_LOG GV$FLASHBACK_DATABASE_STAT V$FLASHBACK_TXN_MODS V$FLASHBACK_TXN_GRAPH 17 rows selected. |
1 2 3 4 5 6 7 8 9 | [Logical-1] SQL> alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS'; Session altered. [Logical-1] SQL> select OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME from V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI -------------------- ------------------- 8319963 23-01-2017 11:54:48 |
12. We are flashbacking the database with the oldest SCN.
1 2 3 | [Logical-1] SQL> flashback database to scn 8319963; Flashback complete. |
13. We open the database with RESETLOGS.
1 2 3 | [Logical-1] SQL> alter database open resetlogs; Database altered. |
14. We query Recovery Mode and Apply LAG.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [Logical-1] SQL> alter database start logical standby apply immediate; Database altered. [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> column name format a25 [Logical-1] SQL> column value format a20 [Logical-1] SQL> column time_computed format a25 [Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:48:56 01/23/2017 22:52:54 apply lag +00 00:48:56 01/23/2017 22:52:54 apply finish time +00 00:00:00.001 01/23/2017 22:52:54 estimated startup time 22 01/23/2017 22:52:54 |
Apply Lag, which was 8 hours after the problem, was reduced to 48 minutes.
1 2 3 4 5 6 | [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> select d.open_mode, d.database_role, a.database_mode, a.recovery_mode, a.protection_mode from v$archive_dest_status a, v$database d where a.database_mode <> 'UNKNOWN'; OPEN_MODE DATABASE_ROLE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE -------------------- ---------------- --------------- ----------------------- -------------------- READ WRITE LOGICAL STANDBY OPEN LOGICAL REAL TIME APPLY MAXIMUM PERFORMANCE |
15. We conclude the test by creating a table.
1 2 3 4 5 6 7 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical-1] SQL> create table test.locations_yedek as select * from hr.locations; Table created. [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK LOCATIONS_YEDEK |
1 2 3 4 5 6 7 8 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK LOCATIONS_YEDEK |
1 2 3 4 5 6 7 8 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK LOCATIONS_YEDEK |
16. It is also seen that the logs are processed by performing the Log Switch operation.
1 2 3 4 5 6 7 | [Physical-1] SQL> alter system switch logfile; System altered. [Physical-1] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 7 | [Physical-2] SQL> alter system switch logfile; System altered. [Physical-2] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 22 1 19 2 |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 22 1 19 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 22 1 YES 19 2 YES |
17. We see that all logs are processed, not just the current logs.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | [Logical-1] SQL> SELECT sequence#,thread#,applied FROM dba_logstdby_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS'); SEQUENCE# THREAD# APPLIED ---------- ---------- -------- 2 1 YES 3 1 YES 4 1 YES 5 1 YES 6 1 YES 7 1 YES 8 1 YES 9 1 YES 10 1 YES 11 1 YES 12 1 YES SEQUENCE# THREAD# APPLIED ---------- ---------- -------- 13 1 YES 14 1 YES 15 1 YES 16 1 YES 17 1 YES 18 1 YES 19 1 YES 20 1 YES 21 1 YES 22 1 YES 1 2 YES SEQUENCE# THREAD# APPLIED ---------- ---------- -------- 2 2 YES 3 2 YES 4 2 YES 5 2 YES 6 2 YES 7 2 YES 8 2 YES 9 2 YES 10 2 YES 11 2 YES 12 2 YES SEQUENCE# THREAD# APPLIED ---------- ---------- -------- 13 2 YES 14 2 YES 15 2 YES 16 2 YES 17 2 YES 18 2 YES 19 2 YES 40 rows selected. |
Ç. BECOME A PHYSICAL STANDBY OF OLD PRIMARY WITH FLASHBACK
After failover, my Primary Database was DISABLE. Therefore, in order to use this Database as Physical Standby again,
I will either take Standby Backup from the new Primary Database and set up a new Physical Standby Database here, or if I have Flashback Logs before Failover, I will quickly do Physical Standby with Flaschback.
As we have done both long and before, Backup management will not be tried and Flashback feature will be used in this scenario.
1. We learn when Physical Standby becomes Primary from the new Primary Database.
1 2 3 4 5 | [Physical-1] SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 8284488 |
2. We mount the old Primary Database.
1 2 3 4 5 6 7 8 9 | [Primary-1] SQL> startup mount; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. |
3. The old Primary Database is wrapped in the SCN where the Physical Standby is Primary with Flashback.
1 2 3 | [Primary-1] SQL> FLASHBACK DATABASE TO SCN 8284488; Flashback complete. |
[Primary-1]—–>ALERT LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | Mon Jan 23 15:08:11 2017 FLASHBACK DATABASE TO SCN 8284488 Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start started logmerger process Parallel Media Recovery started with 4 slaves Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_2_seq_72.496.934031319 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_74.495.934031307 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_75.490.934034015 Mon Jan 23 15:08:14 2017 Recovery of Online Redo Log: Thread 2 Group 3 Seq 73 Reading mem 0 Mem# 0: +DATA/primary/onlinelog/group_3.266.932223145 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_76.487.934034023 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_77.483.934034085 Recovery of Online Redo Log: Thread 2 Group 4 Seq 74 Reading mem 0 Mem# 0: +DATA/primary/onlinelog/group_4.267.932223147 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_78.479.934034149 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_79.478.934034201 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_80.476.934034467 Flashback Media Recovery Log +FRA/primary/archivelog/2017_01_23/thread_1_seq_81.474.934034601 Recovery of Online Redo Log: Thread 1 Group 2 Seq 82 Reading mem 0 Mem# 0: +DATA/primary/onlinelog/group_2.262.932222811 Incomplete Recovery applied until change 8284489 time 01/23/2017 14:03:26 Flashback Media Recovery Complete Completed: FLASHBACK DATABASE TO SCN 8284488 |
4. The old Primary Database is converted to Physical Standby.
1 2 3 | [Primary-1] SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered. |
[Primary-1]—–>ALERT LOG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | Mon Jan 23 15:09:08 2017 ALTER DATABASE CONVERT TO PHYSICAL STANDBY ALTER DATABASE CONVERT TO PHYSICAL STANDBY (primary1) Flush standby redo logfile failed:1649 Clearing standby activation ID 1771334252 (0x69946a6c) The primary database controlfile was created using the 'MAXLOGFILES 192' clause. There is space for up to 188 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800; Shutting down archive processes Archiving is disabled Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY Mon Jan 23 15:09:14 2017 SUCCESS: diskgroup FRA was dismounted SUCCESS: diskgroup DATA was dismounted NOTE: Database dismounted; ASMB process exiting Stopping background process RBAL Stopping background process MARK Mon Jan 23 15:09:17 2017 NOTE: Shutting down MARK background process |
5. We question the status of the new Physical Standby.
1 2 3 4 5 | [Primary-1] SQL> select status from gv$instance; STATUS ------------ STARTED |
6. We open the new Physical Standby in MOUNT mode.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Primary-1] SQL> shu immediate; ORA-01507: database not mounted ORACLE instance shut down. [Primary-1] SQL> startup mount; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. |
1 2 3 4 5 6 7 8 9 | [Primary-2] SQL> startup mount; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. |
7. The Role and Status of the New Physical Standby is questioned.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Primary-1] SQL> select status from gv$instance; STATUS ------------ MOUNTED MOUNTED [Primary-1] SQL> select database_role from gv$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY PHYSICAL STANDBY |
8. LOG_ARCHIVE_DEST_STATE_2 is ENABLED so that the archive logs can go to the new Physical Standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Physical-1] SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*'; System altered. [Physical-1] SQL> column dest_name format a20 [Physical-1] SQL> column error format a15 [Physical-1] SQL> column destination format a20 [Physical-1] SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS; DEST_ID DEST_NAME STATUS PROTECTION_MODE DESTINATION ERROR SRL ---------- -------------------- --------- -------------------- -------------------- --------------- --- 1 LOG_ARCHIVE_DEST_1 VALID MAXIMUM PERFORMANCE NO 2 LOG_ARCHIVE_DEST_2 VALID MAXIMUM PERFORMANCE primary YES 3 LOG_ARCHIVE_DEST_3 VALID MAXIMUM PERFORMANCE logical |
9. We start RECOVERY in the new Physical Standby.
1 2 3 | [Primary-1] SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
D. FINAL CHECKS
After failover, we check the healthy functioning of Redo Transport and Redo Apply after the old Primary database is Physical Standby and the old Physical Standby is Primary.
1. Log Switch operation is performed on both 2 Primary Nodes.
1 2 3 4 5 6 7 | [Physical-1] SQL> alter system switch logfile; System altered. [Physical-1] SQL> alter system switch logfile; System altered. |
1 2 3 4 5 6 7 | [Physical-2] SQL> alter system switch logfile; System altered. [Physical-2] SQL> alter system switch logfile; System altered. |
2. We check the sequence# of the archive logs in the databases.
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 25 1 21 2 |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 25 1 21 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('23/01/2017 14:35:49', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 25 1 YES 21 2 YES |
Now we write a new RESETLOGS_TIME for the WHERE condition in the queries.
3. We check for lag.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> column name format a25 [Primary-1] SQL> column value format a20 [Primary-1] SQL> column time_computed format a25 [Primary-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/23/2017 15:20:21 apply lag +00 00:00:00 01/23/2017 15:20:21 apply finish time +00 00:00:00.000 01/23/2017 15:20:21 estimated startup time 26 01/23/2017 15:20:21 |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> column name format a25 [Logical-1] SQL> column value format a20 [Logical-1] SQL> column time_computed format a25 [Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag 01/23/2017 14:38:06 apply lag +00 01:15:14 01/23/2017 14:38:06 apply finish time 01/23/2017 14:38:06 estimated startup time 22 01/23/2017 14:38:06 |
4. Finally, we check whether Real-Time Apply is active by DROPing the existing table. For this, we first query the existing tables belonging to the user.
1 2 3 4 5 6 7 8 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; select table_name from dba_tables where owner='TEST' * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only |
Query could not be performed because the database is in MOUNT mode. If there is an ACTIVE DATA GUARD license, query can be made by pulling into OPEN mode.
1 2 3 4 5 6 7 | [Primary-1] SQL> alter database recover managed standby database cancel; Database altered. [Primary-1] SQL> alter database open; Database altered. |
1 2 3 4 5 6 7 8 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
5. The table is DROPed and queried from the standbys.
1 2 3 4 5 6 7 8 9 10 11 | [Physical-1] SQL> drop table test.EMPLOYEES_YEDEK; Table dropped. [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
1 2 3 4 5 6 7 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ REGIONS_YEDEK JOBS_YEDEK JOB_HISTORY_YEDEK |
