{"id":51571,"date":"2022-07-19T18:16:12","date_gmt":"2022-07-19T18:16:12","guid":{"rendered":"https:\/\/dbtut.com\/?p=51571"},"modified":"2022-07-19T18:23:20","modified_gmt":"2022-07-19T18:23:20","slug":"failover-a-physical-standby-database-with-sqlplus-commands","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/","title":{"rendered":"Failover A Physical Standby Database With SQLPLUS Commands"},"content":{"rendered":"

In today’s article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands.<\/p>\n

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.<\/p>\n

We prefer Flashback because it is fast as a method of converting to Physical Standby Database, which will become DISABLE after failover.<\/p>\n

This process will consist of 5 steps.<\/p>\n

A. Preliminary Preparation
\nB. Failover to Physical Standby
\nC. Checks After Failover
\nC. Physical Standby of Old Primary with Flashback
\nD. Final Checks<\/p>\n

A. PRELIMINARY PREPARATION<\/h3>\n

1. We check the parameters about whether Flashback is turned on in the databases and how long the Flashback logs will be stored.<\/p>\n

[Primary-1] SQL> show parameter db_flashback_retention\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_flashback_retention_target        integer     1440\r\n\r\n[Primary-1] SQL> select flashback_on from v$database;\r\n\r\nFLASHBACK_ON\r\n------------------\r\nNO\r\n<\/pre>\n
[Physical-1] SQL> show parameter db_flashback_retention\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_flashback_retention_target        integer     1440\r\n\r\n[Physical-1] SQL> select flashback_on from v$database;\r\n\r\nFLASHBACK_ON\r\n------------------\r\nNO\r\n<\/pre>\n
[Logical-1] SQL> show parameter db_flashback_retention\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\ndb_flashback_retention_target        integer     1440\r\n\r\n[Logical-1] SQL> select flashback_on from v$database;\r\n\r\nFLASHBACK_ON\r\n------------------\r\nNO\r\n<\/pre>\n
2. We activate flashback.<\/p>\n
[Primary-1] SQL> alter database flashback on;\r\n\r\nDatabase altered.\r\n<\/pre>\n
[Physical-1] SQL> alter database recover managed standby database cancel;\r\n\r\nDatabase altered.\r\n\r\n[Physical-1] SQL> alter database flashback on;\r\n\r\nDatabase altered.\r\n<\/pre>\n
[Logical-1] SQL> alter database stop logical standby apply;\r\n\r\nDatabase altered.\r\n\r\n[Logical-1] SQL> alter database flashback on;\r\n\r\nDatabase altered.\r\n<\/pre>\n
3. We check the flashback status.<\/p>\n
[Primary-1] SQL> select flashback_on from v$database;\r\n\r\nFLASHBACK_ON\r\n------------------\r\nYES\r\n<\/pre>\n
[Physical-1] SQL> select flashback_on from v$database;\r\n\r\nFLASHBACK_ON\r\n------------------\r\nYES\r\n<\/pre>\n
[Logical-1] SQL> select flashback_on from v$database;\r\n\r\nFLASHBACK_ON\r\n------------------\r\nYES\r\n<\/pre>\n
4. We check whether flashback logs are created.<\/p>\n
[Primary-1] SQL>  select file_type,number_of_files,percent_space_used from v$recovery_area_usage;\r\n\r\nFILE_TYPE            NUMBER_OF_FILES PERCENT_SPACE_USED\r\n-------------------- --------------- ------------------\r\nCONTROL FILE                       0                  0\r\nREDO LOG                           0                  0\r\nARCHIVED LOG                     760              27.55\r\nBACKUP PIECE                       0                  0\r\nIMAGE COPY                         0                  0\r\nFLASHBACK LOG                      0                  0\r\nFOREIGN ARCHIVED LOG               0                  0\r\n\r\n7 rows selected.\r\n<\/pre>\n
[Physical-1] SQL> select file_type,number_of_files,percent_space_used from v$recovery_area_usage;\r\n\r\nFILE_TYPE            NUMBER_OF_FILES PERCENT_SPACE_USED\r\n-------------------- --------------- ------------------\r\nCONTROL FILE                       0                  0\r\nREDO LOG                           0                  0\r\nARCHIVED LOG                       6                .12\r\nBACKUP PIECE                       0                  0\r\nIMAGE COPY                         0                  0\r\nFLASHBACK LOG                      4               1.99\r\nFOREIGN ARCHIVED LOG               0                  0\r\n\r\n7 rows selected.\r\n<\/pre>\n
[Logical-1] SQL> select file_type,number_of_files,percent_space_used from v$recovery_area_usage;\r\n\r\nFILE_TYPE            NUMBER_OF_FILES PERCENT_SPACE_USED\r\n-------------------- --------------- ------------------\r\nCONTROL FILE                       0                  0\r\nREDO LOG                           0                  0\r\nARCHIVED LOG                     362              23.66\r\nBACKUP PIECE                       0                  0\r\nIMAGE COPY                         0                  0\r\nFLASHBACK LOG                      4                 .5\r\nFOREIGN ARCHIVED LOG             666              25.33\r\n\r\n7 rows selected.\r\n<\/pre>\n
5. We start Redo Apply operations on Standby Databases.<\/p>\n
[Physical-1] SQL> alter database recover managed standby database using current logfile disconnect;\r\n\r\nDatabase altered.\r\n<\/pre>\n
[Logical-1] SQL> alter database start logical standby apply immediate;\r\n\r\nDatabase altered.\r\n<\/pre>\n
6. We check if there is Redo and Transport LAG after recovery.<\/p>\n
[Physical-1] SQL> set linesize 9000\r\n[Physical-1] SQL> column name format a25\r\n[Physical-1] SQL> column value format a20\r\n[Physical-1] SQL> column time_computed format a25\r\n[Physical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;\r\n\r\nNAME                      VALUE                TIME_COMPUTED\r\n------------------------- -------------------- -------------------------\r\ntransport lag             +00 00:00:00         01\/20\/2017 13:30:07\r\napply lag                 +00 00:00:00         01\/20\/2017 13:30:07\r\napply finish time         +00 00:00:00.000     01\/20\/2017 13:30:07\r\nestimated startup time    26                   01\/20\/2017 13:30:07\r\n<\/pre>\n
[Logical-1] SQL> set linesize 9000\r\n[Logical-1] SQL> column name format a25\r\n[Logical-1] SQL> column value format a20\r\n[Logical-1] SQL> column time_computed format a25\r\n[Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;\r\n\r\nNAME                      VALUE                TIME_COMPUTED\r\n------------------------- -------------------- -------------------------\r\ntransport lag             +00 00:00:00         01\/20\/2017 13:30:05\r\napply lag                 +00 00:00:00         01\/20\/2017 13:30:05\r\napply finish time         +00 00:00:00.000     01\/20\/2017 13:30:05\r\nestimated startup time    24                   01\/20\/2017 13:30:05\r\n<\/pre>\n
7. After Flashback and Redo Apply, we check the accuracy of everything with DDL & DML processes.<\/p>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nLOCATIONS_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nDEPARMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nJOB_HISTORY_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\nselect table_name from dba_tables where owner='TEST'\r\n                       *\r\nERROR at line 1:\r\nORA-01219: database not open: queries allowed on fixed tables\/views only\r\n<\/pre>\n
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.<\/p>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOB_HISTORY_YEDEK\r\nREGIONS_YEDEK\r\nLOCATIONS_YEDEK\r\nDEPARMENTS_YEDEK\r\nJOBS_YEDEK\r\nEMPLOYEES_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
[Primary-1] SQL> drop table test.JOB_HISTORY_YEDEK;\r\n\r\nTable dropped.\r\n\r\n[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nLOCATIONS_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nDEPARMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\nselect table_name from dba_tables where owner='TEST'\r\n                       *\r\nERROR at line 1:\r\nORA-01219: database not open: queries allowed on fixed tables\/views only\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nLOCATIONS_YEDEK\r\nDEPARMENTS_YEDEK\r\nJOBS_YEDEK\r\nEMPLOYEES_YEDEK\r\n<\/pre>\n
B. FAILOVER TO PHYSICAL STANDBY DATABASE<\/h3>\n
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.<\/p>\n
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.<\/p>\n
Now let’s see how the Failover process works.<\/p>\n
1. Let’s assume that we can mount the Primary Database due to the problem, but it does not come to OPEN mode.<\/p>\n
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.<\/p>\n
If I succeed in this, it means that I will perform a Failover operation with zero data loss, which is a great thing.<\/p>\n
For this, let’s set up our test environment first.<\/p>\n
a. I want to break the sync of the standby side with the Primary. For this reason, I prevent certain archives from going there.<\/p>\n
[Primary-1] SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';\r\n\r\nSystem altered.\r\n<\/pre>\n
b. We learn the current archive redo log numbers of the databases in the Data Guard Environment.<\/p>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            70          1\r\n            68          2\r\n<\/pre>\n
The reason we put a WHERE condition here is because RESETLOGS is done in the database for previous tests.<\/p>\n
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.<\/p>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            70          1\r\n            68          2\r\n<\/pre>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            70          1 YES\r\n            68          2 YES\r\n<\/pre>\n
c. With the Log Switch operation, I check whether the archives are going to standby sides.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[Primary-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            71          1\r\n            69          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            71          1\r\n            69          2\r\n<\/pre>\n
Although LOG_ARCHIVE_DEST_STATE_2 is DEFERed, the reason why sequence# increases is because the logs accumulated until DEFER must be sent.<\/p>\n
DEFER will not send the next logs instantly.<\/p>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            71          1 YES\r\n            69          2 YES\r\n<\/pre>\n
\u00e7. 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.<\/p>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\n<\/pre>\n
d. We create a new table.<\/p>\n
[Primary-1] SQL> create table test.job_history_yedek as select * from hr.job_history;\r\n\r\nTable created.\r\n\r\n[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
e.We check the archive numbers by performing the Log Switch operation.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[Primary-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            72          1\r\n            70          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            71          1\r\n            69          2\r\n<\/pre>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            72          1 YES\r\n            70          2 YES\r\n<\/pre>\n
f. We create another table.<\/p>\n
[Primary-1] SQL> create table test.departments_yedek as select * from hr.departments;\r\n\r\nTable created.\r\n\r\n[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\nDEPARTMENTS_YEDEK\r\n<\/pre>\n
g. We check the archive numbers by performing the Log Switch operation.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[Primary-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            73          1\r\n            71          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            71          1\r\n            69          2\r\n<\/pre>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            73          1 YES\r\n            71          2 YES\r\n<\/pre>\n
\u011f. We create another table<\/p>\n
[Primary-1] SQL> create table test.countries_yedek as select * from hr.countries;\r\n\r\nTable created.\r\n\r\n[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nCOUNTRIES_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\nDEPARTMENTS_YEDEK\r\nCOUNTRIES_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
h. We are doing Log Switching.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[Primary-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            74          1\r\n            72          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            71          1\r\n            69          2\r\n<\/pre>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            74          1 YES\r\n            72          2 YES\r\n<\/pre>\n
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.<\/p>\n
[root@primary1 ~]# su - grid\r\n[grid@primary1 ~]$ srvctl status database -d primary\r\nInstance primary1 is running on node primary1\r\nInstance primary2 is running on node primary2\r\n[grid@primary1 ~]$ srvctl stop database -d primary\r\n[grid@primary1 ~]$ srvctl status database -d primary\r\nInstance primary1 is not running on node primary1\r\nInstance primary2 is not running on node primary2\r\n[grid@primary1 ~]$ srvctl start database -d primary -o mount\r\n[grid@primary1 ~]$ srvctl status database -d primary\r\nInstance primary1 is running on node primary1\r\nInstance primary2 is running on node primary2\r\n<\/pre>\n
[Primary-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nMOUNTED\r\nMOUNTED\r\n<\/pre>\n
i.I find the highest sequence# in each thread in Physical Standby and understand which archives are missing on the Standby side.<\/p>\n
[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');\r\n\r\n    THREAD       LAST\r\n---------- ----------\r\n         1         71\r\n         2         69\r\n<\/pre>\n
j. Before moving on to failover actions, question the status and role of the Databases in the environment one last time.<\/p>\n
[Primary-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nMOUNTED\r\nMOUNTED\r\n<\/pre>\n
[Physical-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nOPEN\r\nOPEN\r\n<\/pre>\n
[Logical-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nOPEN\r\nOPEN\r\n<\/pre>\n
[Primary-1] SQL> select database_role from gv$database;\r\n\r\nDATABASE_ROLE\r\n----------------\r\nPRIMARY\r\nPRIMARY\r\n<\/pre>\n
[Physical-1] SQL> select database_role from gv$database;\r\n\r\nDATABASE_ROLE\r\n----------------\r\nPHYSICAL STANDBY\r\nPHYSICAL STANDBY\r\n<\/pre>\n
[Logical-1] SQL> select database_role from gv$database;\r\n\r\nDATABASE_ROLE\r\n----------------\r\nLOGICAL STANDBY\r\nLOGICAL STANDBY\r\n<\/pre>\n
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.<\/p>\n
a. We send Current Redos and Archive Logs to the Standby side.<\/p>\n
[Primary-1] SQL> ALTER SYSTEM FLUSH REDO TO 'standby';\r\nALTER SYSTEM FLUSH REDO TO 'primary'\r\n*\r\nERROR at line 1:\r\nORA-01105: mount is incompatible with mounts by other instances\r\n<\/pre>\n
b. This error is received when all instances are open in RAC structure. That’s why I’m closing Node 2.<\/p>\n
[Primary-2] SQL> shu immediate;\r\nORA-01109: database not open\r\n\r\n\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\n<\/pre>\n
[Primary-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nMOUNTED\r\n<\/pre>\n
c. I try again to post archive and current redos.<\/p>\n
[Primary-1] SQL> ALTER SYSTEM FLUSH REDO TO 'standby';\r\nALTER SYSTEM FLUSH REDO TO 'standby'\r\n*\r\nERROR at line 1:\r\nORA-16416: No viable switchover targets available\r\n<\/pre>\n
\u00e7. 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.<\/p>\n
[Primary-1] SQL> show parameter log_archive_dest_state_2\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_state_2             string      DEFER\r\n<\/pre>\n
[Primary-1] SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';\r\n\r\nSystem altered.\r\n<\/pre>\n
[Primary-1] SQL> ALTER SYSTEM FLUSH REDO TO 'standby';\r\nALTER SYSTEM FLUSH REDO TO 'standby'\r\n*\r\nERROR at line 1:\r\nORA-16447: Redo apply was not active at the target standby database\r\n<\/pre>\n
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.<\/p>\n[Primary-1]—–>ALERT LOG<\/p>\n
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*';\r\nMon Jan 23 13:43:19 2017\r\nALTER SYSTEM FLUSH REDO TO 'standby' CONFIRM APPLY\r\nALTER SYSTEM FLUSH REDO TO standby CONFIRM APPLY [Process Id: 3829] (primary1)\r\nFlush redo: No wait for non-current ORLs to be archived\r\nWaiting for all FAL entries to be archived...\r\nAll FAL entries have been archived.\r\nWaiting for dest_id 2 to become synchronized...\r\nMon Jan 23 13:43:22 2017\r\nDeleted Oracle managed file +FRA\/primary\/archivelog\/2017_01_10\/thread_2_seq_216.492.932920295\r\nMon Jan 23 13:43:22 2017\r\nDeleted Oracle managed file +FRA\/primary\/archivelog\/2017_01_10\/thread_2_seq_217.491.932920309\r\nMon Jan 23 13:43:22 2017\r\nDeleted Oracle managed file +FRA\/primary\/archivelog\/2017_01_10\/thread_1_seq_356.490.932920311\r\nActive, synchronized flush redo target has been identified\r\nRecovery 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\r\n<\/pre>\n[Standby-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 13:43:01 2017\r\nRFS[12]: Assigned to RFS process 9146\r\nRFS[12]: Opened log for thread 1 sequence 72 dbid 1769705496 branch 933872485\r\nMon Jan 23 13:43:02 2017\r\nRFS[13]: Assigned to RFS process 9150\r\nRFS[13]: Opened log for thread 1 sequence 74 dbid 1769705496 branch 933872485\r\nArchived Log entry 1681 added for thread 1 sequence 72 rlc 933872485 ID 0x69946a6c dest 2:\r\nArchived Log entry 1682 added for thread 1 sequence 74 rlc 933872485 ID 0x69946a6c dest 2:\r\nMon Jan 23 13:43:02 2017\r\nRFS[14]: Assigned to RFS process 9156\r\nRFS[14]: Opened log for thread 2 sequence 72 dbid 1769705496 branch 933872485\r\nArchived Log entry 1686 added for thread 2 sequence 72 rlc 933872485 ID 0x69946a6c dest 2:<\/pre>\n[Standby-2]—–>ALERT LOG<\/p>\n
Mon Jan 23 13:43:27 2017\r\nRFS[5]: Assigned to RFS process 21307\r\nRFS[5]: Opened log for thread 1 sequence 73 dbid 1769705496 branch 933872485\r\nMon Jan 23 13:43:28 2017\r\nRFS[6]: Assigned to RFS process 21316\r\nRFS[6]: Opened log for thread 2 sequence 71 dbid 1769705496 branch 933872485\r\nMon Jan 23 13:43:28 2017\r\nRFS[7]: Assigned to RFS process 21326\r\nRFS[7]: Opened log for thread 2 sequence 70 dbid 1769705496 branch 933872485\r\nArchived Log entry 1683 added for thread 1 sequence 73 rlc 933872485 ID 0x69946a6c dest 2:\r\n<\/pre>\n
e. We are launching Redo-Apply.<\/p>\n
[Physical-1] SQL> alter database recover managed standby database using current logfile disconnect;\r\n\r\nDatabase altered.\r\n<\/pre>\n[Standby-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 13:43:42 2017\r\nalter database recover managed standby database using current logfile disconnect\r\nAttempt to start background Managed Standby Recovery process (primary1)\r\nMon Jan 23 13:43:42 2017\r\nMRP0 started with pid=53, OS id=9258 \r\nMRP0: Background Managed Standby Recovery process started (primary1)\r\n started logmerger process\r\nMon Jan 23 13:43:47 2017\r\nManaged Standby Recovery starting Real Time Apply\r\nMon Jan 23 13:43:48 2017\r\nReconfiguration started (old inc 16, new inc 18)\r\nList of instances:\r\n 1 2 (myinst: 1) \r\n Global Resource Directory frozen\r\n Communication channels reestablished\r\n Master broadcasted resource hash value bitmaps\r\n Non-local Process blocks cleaned out\r\nMon Jan 23 13:43:48 2017\r\n LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\nMon Jan 23 13:43:48 2017\r\n LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n Set master node info \r\n Submitted all remote-enqueue requests\r\n Dwn-cvts replayed, VALBLKs dubious\r\n All grantable enqueues granted\r\n Submitted all GCS remote-cache requests\r\n Fix write in gcs resources\r\nReconfiguration complete\r\nParallel Media Recovery started with 4 slaves\r\nMon Jan 23 13:43:49 2017\r\nBlock change tracking file is current.\r\nStarting background process CTWR\r\nMon Jan 23 13:43:49 2017\r\nCTWR started with pid=59, OS id=9329 \r\nBlock change tracking service is active.\r\nWaiting for all non-current ORLs to be archived...\r\nAll non-current ORLs have been archived.\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_1_seq_72.605.934033381\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_2_seq_69.273.934030667\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_2_seq_70.589.934033409\r\nCompleted: alter database recover managed standby database using current logfile disconnect\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_1_seq_73.604.934033407\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_2_seq_71.395.934033407\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_1_seq_74.608.934033381\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_2_seq_72.587.934033383\r\nMedia Recovery Waiting for thread 1 sequence 75\r\n<\/pre>\n[Standby-2]—–>ALERT LOG<\/p>\n
Archived Log entry 1684 added for thread 2 sequence 71 rlc 933872485 ID 0x69946a6c dest 2:\r\nArchived Log entry 1685 added for thread 2 sequence 70 rlc 933872485 ID 0x69946a6c dest 2:\r\nMon Jan 23 13:44:13 2017\r\nManaged Standby Recovery starting Real Time Apply\r\nMon Jan 23 13:44:13 2017\r\nReconfiguration started (old inc 16, new inc 18)\r\nList of instances:\r\n 1 2 (myinst: 2) \r\n Global Resource Directory frozen\r\n Communication channels reestablished\r\nMon Jan 23 13:44:13 2017\r\n * domain 0 valid = 1 according to instance 1 \r\n Master broadcasted resource hash value bitmaps\r\n Non-local Process blocks cleaned out\r\nMon Jan 23 13:44:13 2017\r\n LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\nMon Jan 23 13:44:13 2017\r\n LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n Set master node info \r\n Submitted all remote-enqueue requests\r\n Dwn-cvts replayed, VALBLKs dubious\r\n All grantable enqueues granted\r\n Submitted all GCS remote-cache requests\r\n Fix write in gcs resources\r\nReconfiguration complete\r\n<\/pre>\n
f. We check whether the tables created after LOG_ARCHIVE_DEST_STATE_2 DEFER.<\/p>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nCOUNTRIES_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
3. Suppose we could not put the Primary Database in MOUNT mode or the ALTER SYSTEM FLUSH REDO command failed.<\/p>\n
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,<\/p>\n
I first set up a test environment.<\/p>\n
a. I want to break the sync of the standby side with the Primary. For this reason, I prevent certain archives from going there.<\/p>\n
[Primary-1] SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';\r\n\r\nSystem altered.\r\n<\/pre>\n
b. We are querying the user’s existing tables.<\/p>\n
Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nCOUNTRIES_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nCOUNTRIES_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\nDEPARTMENTS_YEDEK\r\nCOUNTRIES_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
c. We are doing Log Switching.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            79          1\r\n            74          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            79          1\r\n            74          2\r\n<\/pre>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            79          1 YES\r\n            74          2 YES\r\n<\/pre>\n
\u00e7. We are querying the existence of the tables by dropping a table.<\/p>\n
[Primary-1] SQL> drop table test.COUNTRIES_YEDEK;\r\n\r\nTable dropped.\r\n\r\n[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nCOUNTRIES_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\nDEPARTMENTS_YEDEK\r\n<\/pre>\n
d. We are doing Log Switching.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            80          1\r\n            74          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            79          1\r\n            74          2\r\n<\/pre>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            80          1 YES\r\n            74          2 YES\r\n<\/pre>\n
e. We are deleting another table.<\/p>\n
[Primary-1] SQL> drop table test.DEPARTMENTS_YEDEK;\r\n\r\nTable dropped.\r\n\r\nPrimary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nCOUNTRIES_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n\r\n6 rows selected.\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
f. We are doing another Log Switch operation.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            82          1\r\n            74          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            79          1\r\n            74          2\r\n<\/pre>\n
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            82          1 YES\r\n            74          2 YES\r\n<\/pre>\n
4. We are starting to work on sending the archive logs to the other Standby side.<\/p>\n
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.<\/p>\n
[root@primary1 nfs_alan]# mkdir nonexist_archived_logs\r\n[root@primary1 nfs_alan]# chmod -R 777 nonexist_archived_logs\/<\/pre>\n
b. We copy the archives missing from ASM on the primary side to the folder created in the sharing area.<\/p>\n[Primary-1]\n
 <\/p>\n
ASMCMD> pwd\r\n+fra\/primary\/ARCHIVELOG\/2017_01_23\r\n\r\nASMCMD> cp thread_1_seq_80.476.934034467 \/nfs_alan\/nonexist_archived_logs\r\ncopying +fra\/primary\/ARCHIVELOG\/2017_01_23\/thread_1_seq_80.476.934034467 -> \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_80.476.934034467\r\nASMCMD> cp thread_1_seq_81.474.934034601 \/nfs_alan\/nonexist_archived_logs\r\ncopying +fra\/primary\/ARCHIVELOG\/2017_01_23\/thread_1_seq_81.474.934034601 -> \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_81.474.934034601\r\nASMCMD> cp thread_1_seq_82.475.934034607 \/nfs_alan\/nonexist_archived_logs\r\ncopying +fra\/primary\/ARCHIVELOG\/2017_01_23\/thread_1_seq_82.475.934034607 -> \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_82.475.934034607\r\n<\/pre>\n
[root@primary1 nfs_alan]# cd \/nfs_alan\/nonexist_archived_logs\/\r\n[root@primary1 nonexist_archived_logs]# ls\r\nthread_1_seq_80.476.934034467  thread_1_seq_82.475.934034607\r\nthread_1_seq_81.474.934034601\r\n<\/pre>\n
c. On the standby side, we copy the archive logs to the relevant folder in ASM.<\/p>\n[Standby-1]\n
ASMCMD> cp \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_80.476.934034467 +fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_80\r\ncopying \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_80.476.934034467 -> +fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_80\r\nASMCMD> cp \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_81.474.934034601 +fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_81\r\ncopying \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_81.474.934034601 -> +fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_81\r\nASMCMD> cp \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_82.475.934034607 +fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_82\r\ncopying \/nfs_alan\/nonexist_archived_logs\/thread_1_seq_82.475.934034607 -> +fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_82\r\n<\/pre>\n
c. We REGISTER to APPLY the archives.<\/p>\n
[Standby-1] SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_80';\r\n\r\nDatabase altered.\r\n\r\n[Standby-1] SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_81';\r\n\r\nDatabase altered.\r\n\r\n[Standby-1] SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_82';\r\n\r\nDatabase altered.\r\n<\/pre>\n[Standby-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 14:22:19 2017\r\nALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_80'\r\nThere are 1 logfiles specified.\r\nALTER DATABASE REGISTER [PHYSICAL] LOGFILE\r\nResynchronizing thread 1 from sequence 79 to 80\r\nCompleted: ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_80'\r\nMon Jan 23 14:22:20 2017\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_1_seq_80\r\nMedia Recovery Waiting for thread 1 sequence 81\r\nMon Jan 23 14:22:34 2017\r\nALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_81'\r\nThere are 1 logfiles specified.\r\nALTER DATABASE REGISTER [PHYSICAL] LOGFILE\r\nResynchronizing thread 1 from sequence 80 to 81\r\nCompleted: ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_81'\r\nMon Jan 23 14:22:37 2017\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_1_seq_81\r\nMedia Recovery Waiting for thread 1 sequence 82\r\nMon Jan 23 14:22:45 2017\r\nALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_82'\r\nThere are 1 logfiles specified.\r\nALTER DATABASE REGISTER [PHYSICAL] LOGFILE\r\nResynchronizing thread 1 from sequence 81 to 82\r\nCompleted: ALTER DATABASE REGISTER PHYSICAL LOGFILE '+fra\/standby\/archivelog\/2017_01_23\/thread_1_seq_82'\r\nMon Jan 23 14:22:48 2017\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_23\/thread_1_seq_82\r\nMedia Recovery Waiting for thread 1 sequence 83<\/pre>\n
d. We are checking the Archive Log Sequence#s.<\/p>\n
\t\t[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#;\r\n\t\t\r\n\t\tMAX(SEQUENCE#)    THREAD#\r\n\t\t-------------- ----------\r\n\t\t            82          1\r\n\t\t            74          2\r\n<\/pre>\n
\t\t[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#;\r\n\t\t\r\n\t\tMAX(SEQUENCE#)    THREAD#\r\n\t\t-------------- ----------\r\n\t\t            82          1\r\n\t\t            74          2\r\n<\/pre>\n
\t\t[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log group by thread#, applied;\r\n\t\t\r\n\t\tMAX(SEQUENCE#)    THREAD# APPLIED\r\n\t\t-------------- ---------- --------\r\n\t\t            82          1 YES\r\n\t\t            74          2 YES\r\n\t\t\r\n<\/pre>\n
e. After LOG_ARCHIVE_DEST_2 is DEFERED to set up the Test Environment, we check whether the deleted tables are also deleted from the Physical Standby.<\/p>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
5. Now that I have prevented data loss, I can start the preparations for the Switchover process. First, we stop the RECOVER operation in the Physical Standby Database.<\/p>\n
[Physical-1] SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\r\n\r\nDatabase altered.\r\n<\/pre>\n
6. We finish the RECOVER process in the Physical Standby Database.<\/p>\n
[Physical-1] SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;\r\n\r\nDatabase altered.\r\n<\/pre>\n[Standby-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 14:30:28 2017\r\nALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH\r\nAttempt to do a Terminal Recovery (primary1)\r\nMedia Recovery Start: Managed Standby Recovery (primary1)\r\n started logmerger process\r\nMon Jan 23 14:30:28 2017\r\nManaged Standby Recovery not using Real Time Apply\r\nMon Jan 23 14:30:28 2017\r\nReconfiguration started (old inc 20, new inc 22)\r\nList of instances:\r\n 1 2 (myinst: 1) \r\n Global Resource Directory frozen\r\n Communication channels reestablished\r\n Master broadcasted resource hash value bitmaps\r\n Non-local Process blocks cleaned out\r\nMon Jan 23 14:30:29 2017\r\n LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\nMon Jan 23 14:30:29 2017\r\n LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n Set master node info \r\n Submitted all remote-enqueue requests\r\n Dwn-cvts replayed, VALBLKs dubious\r\n All grantable enqueues granted\r\n Submitted all GCS remote-cache requests\r\n Fix write in gcs resources\r\nReconfiguration complete\r\nParallel Media Recovery started with 4 slaves\r\nMon Jan 23 14:30:30 2017\r\nBlock change tracking file is current.\r\nStarting background process CTWR\r\nMon Jan 23 14:30:30 2017\r\nCTWR started with pid=51, OS id=16799 \r\nBlock change tracking service is active.\r\nMedia Recovery Waiting for thread 1 sequence 83\r\nBegin: Standby Redo Logfile archival\r\nEnd: Standby Redo Logfile archival\r\nTerminal Recovery timestamp is '01\/23\/2017 14:30:31'\r\nTerminal Recovery: applying standby redo logs.\r\nTerminal Recovery: thread 1 seq# 83 redo required\r\nMedia Recovery Waiting for thread 1 sequence 83\r\nTerminal Recovery: End-Of-Redo log allocation\r\nMRP: Validating standby redo logfile 5\r\nMedia Recovery Log +DATA\/standby\/onlinelog\/group_5.267.932283683\r\nTerminal Recovery: log 5 reserved for thread 1 sequence 83\r\nRecovery of Online Redo Log: Thread 1 Group 5 Seq 83 Reading mem 0\r\n  Mem# 0: +DATA\/standby\/onlinelog\/group_5.267.932283683\r\n  Mem# 1: +FRA\/standby\/onlinelog\/group_5.260.932283683\r\nIdentified End-Of-Redo (failover) for thread 1 sequence 83 at SCN 0xffff.ffffffff\r\nIncomplete Recovery applied until change 8284490 time 01\/23\/2017 14:03:26\r\nMedia Recovery Complete (primary1)\r\nTerminal Recovery: successful completion\r\nMon Jan 23 14:30:33 2017\r\nARC5: Archiving not possible: error count exceeded\r\nARCH: Archival stopped, error occurred. Will continue retrying\r\nORACLE Instance primary1 - Archival Error\r\nORA-16014: log 5 sequence# 83 not archived, no available destinations\r\nORA-00312: online log 5 thread 1: '+DATA\/standby\/onlinelog\/group_5.267.932283683'\r\nORA-00312: online log 5 thread 1: '+FRA\/standby\/onlinelog\/group_5.260.932283683'\r\nForcing ARSCN to IRSCN for TR 0:8284490\r\nAttempt to set limbo arscn 0:8284490 irscn 0:8284490 \r\nResetting standby activation ID 1771334252 (0x69946a6c)\r\nReconfiguration started (old inc 22, new inc 24)\r\nList of instances:\r\n 1 2 (myinst: 1) \r\n Global Resource Directory frozen\r\n Communication channels reestablished\r\n Master broadcasted resource hash value bitmaps\r\n Non-local Process blocks cleaned out\r\n LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n Set master node info \r\n Submitted all remote-enqueue requests\r\n Dwn-cvts replayed, VALBLKs dubious\r\n All grantable enqueues granted\r\n Submitted all GCS remote-cache requests\r\n Fix write in gcs resources\r\nReconfiguration complete\r\nBlock change tracking service stopping.\r\nStopping background process CTWR\r\nCompleted: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH<\/pre>\n
If an error is received as a result of this operation, it means that there is a GAP and this GAP cannot be closed. As long as the GAP cannot be closed, there is data loss.<\/p>\n
If the GAP cannot be closed despite the operations, then the following command is executed considering the data loss.<\/p>\n
[Physical-1] SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;\r\n\r\nDatabase altered.\r\n<\/pre>\n
7. If there is no need for the ACTIVATE PHYSICAL STANDBY command, we now query the status of the Physical Standby Database for Switchover.<\/p>\n
[Physical-1] SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;\r\n\r\nSWITCHOVER_STATUS\r\n--------------------\r\nTO PRIMARY\r\n<\/pre>\n
8. We do Physical Standby Database PRIMARY with Switchover process.<\/p>\n
[Physical-1] SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;\r\n\r\nDatabase altered.\r\n<\/pre>\n[Standby-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 14:35:45 2017\r\nALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY\r\nALTER DATABASE SWITCHOVER TO PRIMARY (primary1)\r\nMaximum wait for role transition is 15 minutes.\r\nAll dispatchers and shared servers shutdown\r\nCLOSE: killing server sessions.\r\nCLOSE: all sessions shutdown successfully.\r\nMon Jan 23 14:35:46 2017\r\nSMON: disabling cache recovery\r\nMon Jan 23 14:35:46 2017\r\nARC2: Archiving not possible: error count exceeded\r\nBackup controlfile written to trace file \/u01\/app\/oracle\/diag\/rdbms\/standby\/primary1\/trace\/primary1_ora_15435.trc\r\nStandby terminal recovery start SCN: 8284489\r\nRESETLOGS after complete recovery through change 8284490\r\nOnline log +DATA\/standby\/onlinelog\/group_1.263.932283679: Thread 1 Group 1 was previously cleared\r\nOnline log +FRA\/standby\/onlinelog\/group_1.256.932283681: Thread 1 Group 1 was previously cleared\r\nOnline log +DATA\/standby\/onlinelog\/group_2.264.932283681: Thread 1 Group 2 was previously cleared\r\nOnline log +FRA\/standby\/onlinelog\/group_2.257.932283681: Thread 1 Group 2 was previously cleared\r\nOnline log +DATA\/standby\/onlinelog\/group_3.265.932283681: Thread 2 Group 3 was previously cleared\r\nOnline log +FRA\/standby\/onlinelog\/group_3.258.932283681: Thread 2 Group 3 was previously cleared\r\nOnline log +DATA\/standby\/onlinelog\/group_4.266.932283681: Thread 2 Group 4 was previously cleared\r\nOnline log +FRA\/standby\/onlinelog\/group_4.259.932283681: Thread 2 Group 4 was previously cleared\r\nStandby became primary SCN: 8284488\r\nMon Jan 23 14:35:52 2017\r\nSetting recovery target incarnation to 6\r\nMon Jan 23 14:35:57 2017\r\nAUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.\r\nSwitchover: Complete - Database mounted as primary\r\nCompleted: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY\r\n<\/pre>\n
9. We open the Physical Standby Databases.<\/p>\n
[Physical-1] SQL> alter database open;\r\n\r\nDatabase altered.\r\n<\/pre>\n
[Physical-2] SQL> alter database open;\r\n\r\nDatabase altered.\r\n<\/pre>\n
[Physical-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nOPEN\r\nOPEN\r\n<\/pre>\n
[Logical-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nOPEN\r\nOPEN\r\n<\/pre>\n
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.<\/p>\n
Therefore, the old Primary Database should be closed and the log flow should be stopped.<\/p>\n
[Primary-1] SQL> shu immediate;\r\nORA-01109: database not open\r\n\r\n\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\n<\/pre>\n
With this process, the following error in the logs disappeared.<\/p>\n[Standby-1]—–>ALERT LOG<\/p>\n
Fatal NI connect error 12514, connecting to:\r\n (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))))\r\n\r\n  VERSION INFORMATION:\r\n        TNS for Linux: Version 11.2.0.4.0 - Production\r\n        TCP\/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production\r\n  Time: 23-JAN-2017 14:44:48\r\n  Tracing not turned on.\r\n  Tns error struct:\r\n    ns main err code: 12564\r\n    \r\nTNS-12564: TNS:connection refused\r\n    ns secondary err code: 0\r\n    nt main err code: 0\r\n    nt secondary err code: 0\r\n    nt OS err code: 0\r\nError 12514 received logging on to the standby\r\nPING[ARC2]: Heartbeat failed to connect to standby 'primary'. Error is 12514.\r\n<\/pre>\n
[Physical-1] SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';\r\n\r\nSystem altered.\r\n<\/pre>\n
C. CHECKS AFTER FAILOVER<\/h3>\n
1. We are querying the Roles, Recovery Modes and Apply Modes of the databases.<\/p>\n
[Logical-1] SQL> set linesize 9000\r\n[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';\r\n\r\nOPEN_MODE            DATABASE_ROLE    DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE\r\n-------------------- ---------------- --------------- ----------------------- --------------------\r\nREAD WRITE           LOGICAL STANDBY  OPEN            IDLE                    MAXIMUM PERFORMANCE\r\n<\/pre>\n
2. We start Redo Apply in Logical Standby Database.<\/p>\n
[Logical-1] SQL> alter database start logical standby apply immediate;\r\n\r\nDatabase altered.\r\n<\/pre>\n
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.<\/p>\n[Logical-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 14:01:56 2017\r\nRFS[12]: Opened log for thread 2 sequence 1 dbid 1769705496 branch 934036549\r\nArchive log rejected (thread 2 sequence 1) by RFS clients\r\nMon Jan 23 14:01:59 2017\r\nalter database start logical standby apply immediate\r\nALTER DATABASE START LOGICAL STANDBY APPLY (primary1)\r\nwith optional part\r\nIMMEDIATE\r\nAttempt to start background Logical Standby process\r\nMon Jan 23 14:01:59 2017\r\nLSP0 started with pid=51, OS id=23766 \r\nCompleted: alter database start logical standby apply immediate\r\nLOGMINER: Parameters summary for session# = 1\r\nLOGMINER: Number of processes = 3, Transaction Chunk Size = 201\r\nLOGMINER: Memory Size = 30M, Checkpoint interval = 150M\r\nLOGMINER: SpillScn 8284802, ResetLogScn 7750661\r\nLOGMINER: summary for session# = 1\r\nLOGMINER: StartScn: 0 (0x0000.00000000)\r\nLOGMINER: EndScn: 0 (0x0000.00000000)\r\nLOGMINER: HighConsumedScn: 8284796 (0x0000.007e6a7c)\r\nLOGMINER: session_flag: 0x1\r\nLOGMINER: Read buffers: 16\r\nLOGMINER: Memory LWM: limit 10M, LWM 24M, 80%\r\nLOGMINER: Memory Release Limit: 1M\r\nFatal Error: LogMiner processed beyond new branch scn.\r\nLOGSTDBY status: ORA-01346: LogMiner processed redo beyond specified reset log scn\r\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/logical\/primary1\/trace\/primary1_lsp0_23766.trc:\r\nORA-01346: LogMiner processed redo beyond specified reset log scn\r\n<\/pre>\n
It is clear from the error that LogMiner is trying to process an SCN much ahead of the time it is RESETLOGS, but Redo Apply must start from an earlier moment to avoid data inconsistency.<\/p>\n
Therefore, it is necessary to wrap the Logical Standby Database just before the SCN with RESETLOGS_CHANGE.<\/p>\n
3. Although it is seen that there is no problem in Redo Transport, this is still seen with the Log Switch operation.<\/p>\n
[Physical-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n[Standby-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 14:58:40 2017\r\nThread 1 advanced to log sequence 9 (LGWR switch)\r\n  Current log# 1 seq# 9 mem# 0: +DATA\/standby\/onlinelog\/group_1.263.932283679\r\n  Current log# 1 seq# 9 mem# 1: +FRA\/standby\/onlinelog\/group_1.256.932283681\r\nMon Jan 23 14:58:40 2017\r\nArchived Log entry 1725 added for thread 1 sequence 8 ID 0x699440c0 dest 1:\r\nMon Jan 23 14:58:40 2017\r\nLNS: Standby redo logfile selected for thread 1 sequence 9 for destination LOG_ARCHIVE_DEST_3\r\n<\/pre>\n[Logical-2]—–>ALERT LOG<\/p>\n
Mon Jan 23 14:16:48 2017\r\nRFS[11]: Selected log 7 for thread 1 sequence 9 dbid 1769705496 branch 934036549\r\nMon Jan 23 14:16:48 2017\r\nRFS LogMiner: Registered logfile [+FRA\/logical\/foreign_archivelog\/standby\/2017_01_23\/thread_1_seq_8.1256.934035409] to LogMiner session id [1]\r\n<\/pre>\n[Standby-2]—–>ALERT LOG<\/p>\n
Mon Jan 23 14:59:17 2017\r\nARC4: Archive log rejected (thread 2 sequence 1) at host 'logical'\r\nFAL[server, ARC4]: FAL archive failed, see trace file.\r\nARCH: FAL archive failed. Archiver continuing\r\nORACLE Instance primary2 - Archival Error. Archiver continuing.\r\n<\/pre>\n
4. After the Log Switch operation, we check whether the Redos go to Logical Standby.<\/p>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n             9          1\r\n             6          2\r\n<\/pre>\n
[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;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n             9          1 NO\r\n             3          1 CURRENT\r\n             2          1 YES\r\n             6          2 NO\r\n             2          2 CURRENT\r\n<\/pre>\n
As it can be understood from here, REDOs are going but cannot be processed.<\/p>\n
5. We learn the SCN just before RESETLOGS.<\/p>\n
[Physical-1] SQL> select to_char(resetlogs_change# -2) from v$database;\r\n\r\nTO_CHAR(RESETLOGS_CHANGE#-2)\r\n----------------------------------------\r\n8284489\r\n<\/pre>\n
6. We learn the current SCN of the Logical Standby Database.<\/p>\n
[Logical-1] SQL> select to_char(current_scn) from v$database;\r\n\r\nTO_CHAR(CURRENT_SCN)\r\n----------------------------------------\r\n8428834\r\n<\/pre>\n
7. Logical Standby is wrapped with Database Flashback just before RESETLOGS (SCN in item 5).<\/p>\n
[Logical-1] SQL> flashback standby database to scn 8284489;\r\nflashback standby database to scn 8284489\r\n*\r\nERROR at line 1:\r\nORA-38757: Database must be mounted and not open to FLASHBACK.\r\n<\/pre>\n
8. We put the Database MOUNT mode.<\/p>\n
[Logical-1] SQL> shu immediate;\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.<\/pre>\n
[Logical-2] SQL> shu immediate;\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\n<\/pre>\n
[Logical-1] SQL> startup mount;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 6480490496 bytes\r\nFixed Size                  2265384 bytes\r\nVariable Size            1241517784 bytes\r\nDatabase Buffers         5217714176 bytes\r\nRedo Buffers               18993152 bytes\r\nDatabase mounted.\r\n<\/pre>\n
9. Again with the Logical Standby Database Flashback, it is wrapped just before the RESETLOGS (SCN in the 5th item).<\/p>\n
[Logical-1] SQL> flashback standby database to scn 8284489;\r\nflashback standby database to scn 8284489\r\n*\r\nERROR at line 1:\r\nORA-01665: control file is not a standby control file\r\n<\/pre>\n
10. We issue the command as follows.<\/p>\n
[Logical-1] SQL> flashback database to scn 8284489;\r\nflashback database to scn 8284489\r\n*\r\nERROR at line 1:\r\nORA-38729: Not enough flashback database log data to do FLASHBACK.\r\n<\/pre>\n
11. We query the relevant VIEW to go back to the earliest Flashback Logs and learn this information.<\/p>\n
[Logical-1] SQL> select table_name from dictionary where lower(table_name) like '%flashback%';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nDBA_FLASHBACK_ARCHIVE\r\nDBA_FLASHBACK_ARCHIVE_TABLES\r\nDBA_FLASHBACK_ARCHIVE_TS\r\nDBA_FLASHBACK_TXN_REPORT\r\nDBA_FLASHBACK_TXN_STATE\r\nUSER_FLASHBACK_ARCHIVE\r\nUSER_FLASHBACK_ARCHIVE_TABLES\r\nUSER_FLASHBACK_TXN_REPORT\r\nUSER_FLASHBACK_TXN_STATE\r\nV$FLASHBACK_DATABASE_LOGFILE\r\nV$FLASHBACK_DATABASE_LOG\r\n\r\nTABLE_NAME\r\n------------------------------\r\nV$FLASHBACK_DATABASE_STAT\r\nGV$FLASHBACK_DATABASE_LOGFILE\r\nGV$FLASHBACK_DATABASE_LOG\r\nGV$FLASHBACK_DATABASE_STAT\r\nV$FLASHBACK_TXN_MODS\r\nV$FLASHBACK_TXN_GRAPH\r\n\r\n17 rows selected.\r\n<\/pre>\n
[Logical-1] SQL> alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';\r\n\r\nSession altered.\r\n\r\n[Logical-1] SQL> select OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME from V$FLASHBACK_DATABASE_LOG;\r\n\r\nOLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI\r\n-------------------- -------------------\r\n             8319963 23-01-2017 11:54:48\r\n<\/pre>\n
12. We are flashbacking the database with the oldest SCN.<\/p>\n
[Logical-1] SQL> flashback database to scn 8319963;\r\n\r\nFlashback complete.\r\n<\/pre>\n
13. We open the database with RESETLOGS.<\/p>\n
[Logical-1] SQL> alter database open resetlogs;\r\n\r\nDatabase altered.\r\n<\/pre>\n
14. We query Recovery Mode and Apply LAG.<\/p>\n
[Logical-1] SQL> alter database start logical standby apply immediate;\r\n\r\nDatabase altered.\r\n\r\n[Logical-1] SQL> set linesize 9000\r\n[Logical-1] SQL> column name format a25\r\n[Logical-1] SQL> column value format a20\r\n[Logical-1] SQL> column time_computed format a25\r\n[Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;\r\n\r\nNAME                      VALUE                TIME_COMPUTED\r\n------------------------- -------------------- -------------------------\r\ntransport lag             +00 00:48:56         01\/23\/2017 22:52:54\r\napply lag                 +00 00:48:56         01\/23\/2017 22:52:54\r\napply finish time         +00 00:00:00.001     01\/23\/2017 22:52:54\r\nestimated startup time    22                   01\/23\/2017 22:52:54\r\n<\/pre>\n
Apply Lag, which was 8 hours after the problem, was reduced to 48 minutes.<\/p>\n
[Logical-1] SQL> set linesize 9000\r\n[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';\r\n\r\nOPEN_MODE            DATABASE_ROLE    DATABASE_MODE   RECOVERY_MODE        PROTECTION_MODE\r\n-------------------- ---------------- --------------- ----------------------- --------------------\r\nREAD WRITE           LOGICAL STANDBY  OPEN            LOGICAL REAL TIME APPLY MAXIMUM PERFORMANCE\r\n<\/pre>\n
15. We conclude the test by creating a table.<\/p>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Physical-1] SQL> create table test.locations_yedek as select * from hr.locations;\r\n\r\nTable created.\r\n\r\n[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\nLOCATIONS_YEDEK\r\n<\/pre>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\nLOCATIONS_YEDEK\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\nLOCATIONS_YEDEK\r\n<\/pre>\n
16. It is also seen that the logs are processed by performing the Log Switch operation.<\/p>\n
[Physical-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[Physical-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[Physical-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[Physical-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            22          1\r\n            19          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            22          1\r\n            19          2\r\n<\/pre>\n
[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;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            22          1 YES\r\n            19          2 YES\r\n<\/pre>\n
17. We see that all logs are processed, not just the current logs.<\/p>\n
[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');\r\n\r\n SEQUENCE#    THREAD# APPLIED\r\n---------- ---------- --------\r\n         2          1 YES\r\n         3          1 YES\r\n         4          1 YES\r\n         5          1 YES\r\n         6          1 YES\r\n         7          1 YES\r\n         8          1 YES\r\n         9          1 YES\r\n        10          1 YES\r\n        11          1 YES\r\n        12          1 YES\r\n\r\n SEQUENCE#    THREAD# APPLIED\r\n---------- ---------- --------\r\n        13          1 YES\r\n        14          1 YES\r\n        15          1 YES\r\n        16          1 YES\r\n        17          1 YES\r\n        18          1 YES\r\n        19          1 YES\r\n        20          1 YES\r\n        21          1 YES\r\n        22          1 YES\r\n         1          2 YES\r\n\r\n SEQUENCE#    THREAD# APPLIED\r\n---------- ---------- --------\r\n         2          2 YES\r\n         3          2 YES\r\n         4          2 YES\r\n         5          2 YES\r\n         6          2 YES\r\n         7          2 YES\r\n         8          2 YES\r\n         9          2 YES\r\n        10          2 YES\r\n        11          2 YES\r\n        12          2 YES\r\n\r\n SEQUENCE#    THREAD# APPLIED\r\n---------- ---------- --------\r\n        13          2 YES\r\n        14          2 YES\r\n        15          2 YES\r\n        16          2 YES\r\n        17          2 YES\r\n        18          2 YES\r\n        19          2 YES\r\n\r\n40 rows selected.<\/pre>\n
\u00c7. BECOME A PHYSICAL STANDBY OF OLD PRIMARY WITH FLASHBACK<\/h3>\n
After failover, my Primary Database was DISABLE. Therefore, in order to use this Database as Physical Standby again,<\/p>\n
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.<\/p>\n
As we have done both long and before, Backup management will not be tried and Flashback feature will be used in this scenario.<\/p>\n
1. We learn when Physical Standby becomes Primary from the new Primary Database.<\/p>\n
[Physical-1] SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;\r\n\r\nTO_CHAR(STANDBY_BECAME_PRIMARY_SCN)\r\n----------------------------------------\r\n8284488\r\n<\/pre>\n
2. We mount the old Primary Database.<\/p>\n
[Primary-1] SQL> startup mount;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 6480490496 bytes\r\nFixed Size                  2265384 bytes\r\nVariable Size            1241517784 bytes\r\nDatabase Buffers         5217714176 bytes\r\nRedo Buffers               18993152 bytes\r\nDatabase mounted.\r\n<\/pre>\n
3. The old Primary Database is wrapped in the SCN where the Physical Standby is Primary with Flashback.<\/p>\n
[Primary-1] SQL> FLASHBACK DATABASE TO SCN 8284488;\r\n\r\nFlashback complete.\r\n<\/pre>\n[Primary-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 15:08:11 2017\r\nFLASHBACK DATABASE TO SCN 8284488\r\nFlashback Restore Start\r\nFlashback Restore Complete\r\nFlashback Media Recovery Start\r\n started logmerger process\r\nParallel Media Recovery started with 4 slaves\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_2_seq_72.496.934031319\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_74.495.934031307\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_75.490.934034015\r\nMon Jan 23 15:08:14 2017\r\nRecovery of Online Redo Log: Thread 2 Group 3 Seq 73 Reading mem 0\r\n  Mem# 0: +DATA\/primary\/onlinelog\/group_3.266.932223145\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_76.487.934034023\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_77.483.934034085\r\nRecovery of Online Redo Log: Thread 2 Group 4 Seq 74 Reading mem 0\r\n  Mem# 0: +DATA\/primary\/onlinelog\/group_4.267.932223147\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_78.479.934034149\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_79.478.934034201\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_80.476.934034467\r\nFlashback Media Recovery Log +FRA\/primary\/archivelog\/2017_01_23\/thread_1_seq_81.474.934034601\r\nRecovery of Online Redo Log: Thread 1 Group 2 Seq 82 Reading mem 0\r\n  Mem# 0: +DATA\/primary\/onlinelog\/group_2.262.932222811\r\nIncomplete Recovery applied until change 8284489 time 01\/23\/2017 14:03:26\r\nFlashback Media Recovery Complete\r\nCompleted: FLASHBACK DATABASE TO SCN 8284488\r\n<\/pre>\n
4. The old Primary Database is converted to Physical Standby.<\/p>\n
[Primary-1] SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;\r\n\r\nDatabase altered.\r\n<\/pre>\n[Primary-1]—–>ALERT LOG<\/p>\n
Mon Jan 23 15:09:08 2017\r\nALTER DATABASE CONVERT TO PHYSICAL STANDBY\r\nALTER DATABASE CONVERT TO PHYSICAL STANDBY (primary1)\r\nFlush standby redo logfile failed:1649\r\nClearing standby activation ID 1771334252 (0x69946a6c)\r\nThe primary database controlfile was created using the\r\n'MAXLOGFILES 192' clause.\r\nThere is space for up to 188 standby redo logfiles\r\nUse the following SQL commands on the standby database to create\r\nstandby redo logfiles that match the primary database:\r\nALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;\r\nALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;\r\nALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;\r\nALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;\r\nALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;\r\nShutting down archive processes\r\nArchiving is disabled\r\nCompleted: ALTER DATABASE CONVERT TO PHYSICAL STANDBY\r\nMon Jan 23 15:09:14 2017\r\nSUCCESS: diskgroup FRA was dismounted\r\nSUCCESS: diskgroup DATA was dismounted\r\nNOTE: Database dismounted; ASMB process exiting\r\nStopping background process RBAL\r\nStopping background process MARK\r\nMon Jan 23 15:09:17 2017\r\nNOTE: Shutting down MARK background process\r\n<\/pre>\n
5. We question the status of the new Physical Standby.<\/p>\n
[Primary-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nSTARTED\r\n<\/pre>\n
6. We open the new Physical Standby in MOUNT mode.<\/p>\n
[Primary-1] SQL> shu immediate;\r\nORA-01507: database not mounted\r\n\r\n\r\nORACLE instance shut down.\r\n[Primary-1] SQL> startup mount;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 6480490496 bytes\r\nFixed Size                  2265384 bytes\r\nVariable Size            1241517784 bytes\r\nDatabase Buffers         5217714176 bytes\r\nRedo Buffers               18993152 bytes\r\nDatabase mounted.\r\n<\/pre>\n
[Primary-2] SQL> startup mount;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 6480490496 bytes\r\nFixed Size                  2265384 bytes\r\nVariable Size            1241517784 bytes\r\nDatabase Buffers         5217714176 bytes\r\nRedo Buffers               18993152 bytes\r\nDatabase mounted.\r\n<\/pre>\n
7. The Role and Status of the New Physical Standby is questioned.<\/p>\n
[Primary-1] SQL> select status from gv$instance;\r\n\r\nSTATUS\r\n------------\r\nMOUNTED\r\nMOUNTED\r\n\r\n[Primary-1] SQL> select database_role from gv$database;\r\n\r\nDATABASE_ROLE\r\n----------------\r\nPHYSICAL STANDBY\r\nPHYSICAL STANDBY\r\n<\/pre>\n
8. LOG_ARCHIVE_DEST_STATE_2 is ENABLED so that the archive logs can go to the new Physical Standby.<\/p>\n
[Physical-1] SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';\r\n\r\nSystem altered.\r\n\r\n[Physical-1] SQL> column dest_name format a20\r\n[Physical-1] SQL> column error format a15\r\n[Physical-1] SQL> column destination format a20\r\n[Physical-1] SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;\r\n\r\n   DEST_ID DEST_NAME            STATUS    PROTECTION_MODE      DESTINATION    ERROR            SRL\r\n---------- -------------------- --------- -------------------- -------------------- --------------- ---\r\n         1 LOG_ARCHIVE_DEST_1   VALID     MAXIMUM PERFORMANCE                 NO\r\n         2 LOG_ARCHIVE_DEST_2   VALID     MAXIMUM PERFORMANCE  primary        YES\r\n         3 LOG_ARCHIVE_DEST_3   VALID     MAXIMUM PERFORMANCE  logical     \r\n<\/pre>\n
9. We start RECOVERY in the new Physical Standby.<\/p>\n
[Primary-1] SQL> alter database recover managed standby database using current logfile disconnect;\r\n\r\nDatabase altered.\r\n<\/pre>\n
D. FINAL CHECKS<\/h3>\n
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.<\/p>\n
1. Log Switch operation is performed on both 2 Primary Nodes.<\/p>\n
[Physical-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[Physical-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
[Physical-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n[Physical-2] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n
2. We check the sequence# of the archive logs in the databases.<\/p>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            25          1\r\n            21          2\r\n<\/pre>\n
[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#;\r\n\r\nMAX(SEQUENCE#)    THREAD#\r\n-------------- ----------\r\n            25          1\r\n            21          2\r\n<\/pre>\n
[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;\r\n\r\nMAX(SEQUENCE#)    THREAD# APPLIED\r\n-------------- ---------- --------\r\n            25          1 YES\r\n            21          2 YES\r\n<\/pre>\n
Now we write a new RESETLOGS_TIME for the WHERE condition in the queries.<\/p>\n
3. We check for lag.<\/p>\n
[Primary-1] SQL> set linesize 9000\r\n[Primary-1] SQL> column name format a25\r\n[Primary-1] SQL> column value format a20\r\n[Primary-1] SQL> column time_computed format a25\r\n[Primary-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;\r\n\r\nNAME                      VALUE                TIME_COMPUTED\r\n------------------------- -------------------- -------------------------\r\ntransport lag             +00 00:00:00         01\/23\/2017 15:20:21\r\napply lag                 +00 00:00:00         01\/23\/2017 15:20:21\r\napply finish time         +00 00:00:00.000     01\/23\/2017 15:20:21\r\nestimated startup time    26                   01\/23\/2017 15:20:21\r\n<\/pre>\n
[Logical-1] SQL> set linesize 9000\r\n[Logical-1] SQL> column name format a25\r\n[Logical-1] SQL> column value format a20\r\n[Logical-1] SQL> column time_computed format a25\r\n[Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;\r\n\r\nNAME                      VALUE                TIME_COMPUTED\r\n------------------------- -------------------- -------------------------\r\ntransport lag                                  01\/23\/2017 14:38:06\r\napply lag                 +00 01:15:14         01\/23\/2017 14:38:06\r\napply finish time                              01\/23\/2017 14:38:06\r\nestimated startup time    22                   01\/23\/2017 14:38:06\r\n<\/pre>\n
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.<\/p>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\nselect table_name from dba_tables where owner='TEST'\r\n                       *\r\nERROR at line 1:\r\nORA-01219: database not open: queries allowed on fixed tables\/views only\r\n<\/pre>\n
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.<\/p>\n
[Primary-1] SQL> alter database recover managed standby database cancel;\r\n\r\nDatabase altered.\r\n\r\n[Primary-1] SQL> alter database open;\r\n\r\nDatabase altered.\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
5. The table is DROPed and queried from the standbys.<\/p>\n
[Physical-1] SQL> drop table test.EMPLOYEES_YEDEK;\r\n\r\nTable dropped.\r\n\r\n[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nREGIONS_YEDEK\r\nJOBS_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":484,"featured_media":51612,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-51571","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":437,"today_views":0},"yoast_head":"\nFailover A Physical Standby Database With SQLPLUS Commands - Database Tutorials<\/title>\n<meta name=\"description\" content=\"In today's article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands. In a system with a Data Guard Environment\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Failover A Physical Standby Database With SQLPLUS Commands - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"In today's article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands. In a system with a Data Guard Environment\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2022-07-19T18:16:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-19T18:23:20+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png\" \/>\n\t<meta property=\"og:image:width\" content=\"782\" \/>\n\t<meta property=\"og:image:height\" content=\"370\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Onur ARDAHANLI\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Onur ARDAHANLI\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"34 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/\"},\"author\":{\"name\":\"Onur ARDAHANLI\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6\"},\"headline\":\"Failover A Physical Standby Database With SQLPLUS Commands\",\"datePublished\":\"2022-07-19T18:16:12+00:00\",\"dateModified\":\"2022-07-19T18:23:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/\"},\"wordCount\":1955,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png\",\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/\",\"name\":\"Failover A Physical Standby Database With SQLPLUS Commands - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png\",\"datePublished\":\"2022-07-19T18:16:12+00:00\",\"dateModified\":\"2022-07-19T18:23:20+00:00\",\"description\":\"In today's article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands. In a system with a Data Guard Environment\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png\",\"width\":782,\"height\":370},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Failover A Physical Standby Database With SQLPLUS Commands\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6\",\"name\":\"Onur ARDAHANLI\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g\",\"caption\":\"Onur ARDAHANLI\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/onurardahanli\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Failover A Physical Standby Database With SQLPLUS Commands - Database Tutorials","description":"In today's article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands. In a system with a Data Guard Environment","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/","og_locale":"en_US","og_type":"article","og_title":"Failover A Physical Standby Database With SQLPLUS Commands - Database Tutorials","og_description":"In today's article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands. In a system with a Data Guard Environment","og_url":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/","og_site_name":"Database Tutorials","article_published_time":"2022-07-19T18:16:12+00:00","article_modified_time":"2022-07-19T18:23:20+00:00","og_image":[{"width":782,"height":370,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png","type":"image\/png"}],"author":"Onur ARDAHANLI","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Onur ARDAHANLI","Est. reading time":"34 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/"},"author":{"name":"Onur ARDAHANLI","@id":"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6"},"headline":"Failover A Physical Standby Database With SQLPLUS Commands","datePublished":"2022-07-19T18:16:12+00:00","dateModified":"2022-07-19T18:23:20+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/"},"wordCount":1955,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png","articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/","url":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/","name":"Failover A Physical Standby Database With SQLPLUS Commands - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png","datePublished":"2022-07-19T18:16:12+00:00","dateModified":"2022-07-19T18:23:20+00:00","description":"In today's article, we will learn to Failover A Physical Standby Database With SQLPLUS Commands. In a system with a Data Guard Environment","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2022\/07\/Ekran-goruntusu-2022-07-19-211322.png","width":782,"height":370},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2022\/07\/19\/failover-a-physical-standby-database-with-sqlplus-commands\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Failover A Physical Standby Database With SQLPLUS Commands"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/7fcd466cd0d347ec64aaa48f18f780c6","name":"Onur ARDAHANLI","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ecd20c3e1374ced4e1aefc82101cce4cd437be8fd957d1be3d106668b8a1b990?s=96&d=mm&r=g","caption":"Onur ARDAHANLI"},"url":"https:\/\/dbtut.com\/index.php\/author\/onurardahanli\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/51571","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/484"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=51571"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/51571\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/51612"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=51571"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=51571"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=51571"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}