In this scenario, SAT is the logical standby database and NYC is the primary database.

1. On the SAT database, issue the following statement to configure the FAL_SERVER parameter to enable automatic recovery of log files.

   SQL> ALTER SYSTEM SET FAL_SERVER='<tns_name_to_new_primary>';
   SQL> ALTER SYSTEM SET FAL_CLIENT='<tns_name_to_local_database>';
   

2. Call the PREPARE_FOR_NEW_PRIMARY routine to verify that the logical standby database is capable of serving as a standby database to the new primary database. During this step, local copies of log files that pose a risk for data divergence are deleted from the local database. These log files are then requested for re-archival directly from the new primary database.

   On the SAT database, issue the following statement:

   SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY( -
   >  former_standby_type => 'PHYSICAL' -
   >  dblink => 'nyc_link');
   

   Note:

   If the ORA-16109 message is returned and the 'LOGSTDBY: prepare_for_new_primary failure -- applied too far, flashback required.' warning is written in the alert.log, perform the following steps:

   1. Flash back the database to the SCN as stated in the warning and then

   2. Repeat this step before continuing.

   See Flashing Back a Logical Standby Database to a Specific Applied SCN for an example of how to flash back a logical standby database to an Apply SCN.

3. On the SAT database, issue the following statement to start SQL Apply:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

In this scenario, SAT is the logical standby database and NYC is the primary database.

1. Ensure the new primary database is ready to support logical standby databases. On the NYC database, ensure the following query returns a value of NONE. Otherwise the new primary database has not completed the work required to enable support for logical standby databases. For example:

   SQL> SELECT PENDING_ROLE_CHANGE_TASKS FROM V$DATABASE;
   

   A value of NONE must be returned before you attempt to reinstate an old primary database.

2. On the SAT database, issue the following statement to configure the FAL_SERVER parameter to enable automatic recover of log files:

   SQL> ALTER SYSTEM SET FAL_SERVER='<tns_name_to_new_primary>';
   SQL> ALTER SYSTEM SET FAL_CLIENT='<tns_name_to_local_database>';
   

3. Call the PREPARE_FOR_NEW_PRIMARY routine to verify the logical standby database is capable of being a standby to the new primary. During this step, local copies of log files which pose a risk for data divergence are deleted from the local database. These log files are then requested for re-archival directly from the new primary database.

   On the SAT database, issue the following statement:

   SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY( -
   > former_standby_type => 'LOGICAL' -
   > dblink => 'nyc_link');
   

   Note:

   If the ORA-16109 message is returned and the LOGSTDBY: prepare_for_new_primary failure -- applied too far, flashback required warning is written in the alert.log file, perform the following steps:

   1. Flash back the database to the SCN as stated in the warning and then

   2. Repeat this step before continuing.

   See Flashing Back a Logical Standby Database to a Specific Applied SCN for an example of how to flash back a logical standby database to an Apply SCN.

4. On the SAT database, issue the following statements to start SQL Apply:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY nyc_link;
   

   This statement must always be issued without the real-time apply option enabled. To enable real-time apply on the logical standby database, wait for the above statement to complete successfully, and then issue the following statements:

   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover.

1. On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

   SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
   

2. Shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in the previous step.

   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP MOUNT;
   SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;
   

3. To convert the database to a physical standby database, issue the following statement on the old primary database:

   SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
   

4. To start transporting redo to the new physical standby database, perform the following steps on the new primary database:

   1. Issue the following query to see the current state of the archive destinations:

      SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -
      > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

   2. If necessary, enable the destination:

      SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
      

   3. Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully. Issue the following SQL statements on the new primary database:

      SQL> ALTER SYSTEM SWITCH LOGFILE;
      SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,- 
      > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
      

      On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases.

5. Start Redo Apply on the new physical standby database:

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
   

   Redo Apply automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply needs to be restarted one or more times until it has applied beyond the SCN at which the new primary database became the primary database. Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See "Performing a Switchover to a Physical Standby Database" for more information.

These steps assume that the Oracle Data Guard configuration has already completed a failover involving a logical standby database and that Flashback Database has been enabled on the old primary database.

With Oracle Database Release 19c, if the standby is in mount mode and flashback database is enabled, you may not need to do anything. In the common case, if there is sufficient flashback data available, the standby is automatically flashed back so that the standby continues to follow the primary.

If the standby is open and flashback database is enabled, you can place the standby in mount mode and then start standby recovery. The MRP, in an attempt to let the standby follow the primary, triggers an automatic flashback of the standby. If this operation succeeds, as reported in the alert log, you need to follow up with opening the Active Data Guard instance.

If automatic flashback is not triggered, or automatic flashback did not result in the standby following the primary, you can perform the steps described in this section.

On logical standby databases, when SQL Apply encounters a redo record for an operation performed with the NOLOGGING clause on a table that is not being skipped, it stops with the following error:

ORA-16211 unsupported record found in the archived redo log

To recover after the NOLOGGING clause is specified, re-create one or more tables from the primary database, as described in Adding or Re-Creating Tables On a Logical Standby Database.

When the redo is applied to the physical standby database, a portion of the data file is marked as being unrecoverable. When you either fail over to the physical standby database, or open the standby database for read-only access, and attempt to read the range of blocks that are marked as UNRECOVERABLE, you see error messages similar to the following:

ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

When you use STANDBY NOLOGGING FOR LOAD PERFORMANCE mode it is still possible for a query executed on a physical standby to report a corrupt block due to a nologging operation. However, when managed recovery is running on that standby as part of an Active Data Guard configuration, it automatically fetches a replacement block from the primary. (Managed recovery is started with the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT statement.) Managed recovery attempts to fetch all such blocks shortly after they are first created and continues attempting to fix them until it succeeds. Whenever managed recovery is started, it again begins fixing any outstanding blocks from earlier recovery sessions. This automatic recovery applies only to corrupt blocks caused by redo generated with the STANDBY NOLOGGING FOR LOAD PERFORMANCE mode enabled. Blocks corrupted due to conventional nonlogged operations must use the following procedure. These steps describe a simple approach that recovers all nonlogged blocks. (See follow-on sections for other approaches such as determining whether a backup is required after unrecoverable operations, and recovering parts of a physical standby database.)

1. Stop recovery on the standby:

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Recover the nonlogged blocks by connecting RMAN to the standby and issue the following command:

   RMAN> RECOVER DATABASE NONLOGGED BLOCK;

   If the presence of unrecoverable blocks is only found after a switchover, then you can use these same two steps, but the primary database must be just mounted ( not open) and, RMAN must be connected to the primary.

   It is possible that the RMAN RECOVER command may not be able to recover all the nonlogged blocks. The reasons that this might happen are detailed in the alert log of the database from which the RMAN command was executed. The most common reason is that a block has been modified recently at the primary and not yet written to its corresponding data file. This may mean that the block sent to the standby is too old to replace the unrecoverable block on the standby. To resolve this, issue the RECOVER command again at a later time after the block has been written out to the data file.

The following is an example of an alert log entry for an execution of the RECOVER command which left some blocks unrecovered:

Started Nonlogged Block Replacement recovery on file 7 (ospid 13005 rcvid 11319003446180375696)
Finished Nonlogged Block Replacement recovery on file 7. 5 blocks remain
  Statistics for replacement block source database (service=dg3tns)
  (Use of it stopped due to error 12942 received from it)
  Blocks requested 5, blocks received 0.

  Reason replacement blocks accepted or rejected               Blocks Last block
  -------------------------------------------------------- ---------- ----------
  Not received: Rejected by sender. Wrong state or SCN              5         21 

In this case, the command was run on a standby and the primary did not send any blocks but instead reported the following Oracle error: .

ORA-12942: database incarnation at source does not match

An examination of the alert logs would reveal that the primary had performed FLASHBACK DATABASE and OPEN RESETLOGS commands, but the standby had not been flashed back. This means the standby would now be on an orphaned branch of redo and therefore the primary could not supply data blocks that would be known to be the correct version.

To do so, take the following steps:

See Oracle Database Reference for more information about the V$DATAFILE view.

This procedure requires that the COMPATIBLE initialization parameter of the old primary be set to at least 11.0.0.

1. On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

   SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
   

2. Restore the database with a backup taken before the old primary had reached the SCN at which the standby became the new primary (standby_became_primary_scn). Then, perform a point-in-time recovery to recover the old primary to that same point.

   Issue the following RMAN commands:

   RMAN> RUN
       {
         SET UNTIL SCN <standby_became_primary_scn + 1>;
         RESTORE DATABASE;
         RECOVER DATABASE;
        }
   

   With user-managed recovery, you can first restore the database manually. Typically, a backup taken a couple of hours before the failover would be old enough. You can then recover the failed primary using the following command:

   SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CHANGE -
   >  <standby_became_primary_scn + 1>;
   

   Unlike a reinstantiation that uses Flashback Database, this procedure adds one to standby_became_primary_scn. For data files, flashing back to an SCN is equivalent to recovering up until that SCN plus one.

3. Perform the following steps on the old primary database:

   1. Issue the following statement on the old primary database:

      SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
      

   2. Shut down and restart the database:

      SQL> SHUTDOWN IMMEDIATE;
      SQL> STARTUP MOUNT;
      

4. Issue the following command:

   SQL> ALTER DATABASE OPEN READ ONLY;
   

   The goal of this step is to synchronize the control file with the database by using a dictionary check. After this command, check the alert log for any actions suggested by the dictionary check. Typically, no user action is needed if the old primary was not in the middle of adding or dropping data files during the failover.

5. If you have purchased a license for the Oracle Active Data Guard option and would like to operate your physical standby database in active query mode, skip this step. Otherwise, bring your standby database to the mount state.

   For example:

   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP MOUNT;
   

6. Before the new standby database was created, the new primary database probably stopped transmitting redo to the remote destination. To restart redo transport services, perform the following steps on the new primary database:

   1. Issue the following query to see the current state of the archive destinations:

      SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -
      > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

   2. If necessary, enable the destination:

      SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
      

   3. Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully.

      Note:

      This is an important step in order for the old primary to become a new standby following the new primary. If this step is not done, the old primary may recover to an incorrect database branch. The only way to correct the problem then is to convert the old primary again.

      At the SQL prompt, enter the following statements:

      SQL> ALTER SYSTEM SWITCH LOGFILE;
      SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -
      > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
      

      On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases. This step can be skipped if both the primary and standby database roles were set up with the VALID_FOR attribute in one server parameter file (SPFILE). By doing this, the Oracle Data Guard configuration operates properly after a role transition.

7. Start Redo Apply on the new physical standby database, as follows:

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
   

   Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See "Performing a Switchover to a Physical Standby Database" for more information.

1. On the new primary database, issue the following query to determine the SCN to which you want to recover the failed primary database:

   SQL> SELECT APPLIED_SCN RECOVERY_SCN FROM V$LOGSTDBY_PROGRESS;
   

   Also on the new primary database, determine the SCN to use in dealing with archive logs, as follows:

   1. Ensure all standby redo logs have been archived. Issue the following query, looking for a value of NONE to be returned. Depending on the size of the database and the number of logs needing to be archived, it could take some time before a status of NONE is returned.

      SQL> SELECT PENDING_ROLE_CHANGE_TASKS FROM V$DATABASE;
      

   2. After a status of NONE has been returned, run the following query to retrieve the SCN for dealing with archive logs as part of this recovery:

      SQL> SELECT VALUE ARCHIVE_SCN FROM SYSTEM.LOGSTDBY$PARAMETERS -
      > WHERE NAME='STANDBY_BECAME_PRIMARY_SCN';
      

2. Remove any archive logs created at the time of, or after the failover operation, from the failed primary database. If the failed primary database was isolated from the standby, it could have divergent archive logs that are not consistent with the current primary database. To ensure these divergent archive logs are never applied, they must be deleted from backups and the fast recovery area. You can use the following RMAN command to delete the relevant archive logs from the fast recovery area:

   RMAN> DELETE FORCE ARCHIVELOG FROM SCN ARCHIVE_SCN;
   

   Once deleted, these divergent logs and subsequent transactions can never be recovered.

3. On the new primary database, issue the following query to determine the minimum set of log files that must be copied to the failed primary database before recovering from a backup:

   SQL> SELECT file_name FROM DBA_LOGSTDBY_LOG WHERE next_change# > ARCHIVE_SCN;
   

   Retrieve the required standby logs, copy the backup set to the new standby and restore it to the new standby fast recovery area. Because these logs are coming from standby redo logs, they are not part of the standby's standard archives. The RMAN utility is able to use a partial file name to retrieve the files from the correct location.

   The following is a sample use of the RMAN BACKUP command:

   RMAN> BACKUP AS COPY DEVICE TYPE DISK FORMAT '/tmp/test/%U'
   > ARCHIVELOG LIKE '<partial file names from above>%';
   

   The following is a sample use of the RMAN RESTORE command:

   RMAN> CATALOG START WITH '/tmp/test';
   RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 33 UNTIL SEQUENCE 35;
   

4. Restore a backup of all the original primary's data files and recover to RECOVERY_SCN + 1. Oracle recommends that you leverage the current control file.

   1. Start up the database in restricted mode to protect it from rogue transactions until the GUARD ALL command can be issued after the database has been opened.

   2. Use the backup to restore the data files of the failed primary database.

   3. Turn off flashback database, if it is enabled (necessary for the USING BACKUP CONTROLFILE clause).

   4. Perform point-in-time recovery to RECOVERY_SCN +1 in SQL*Plus.

   Whether you are using a current control file or a backup control file, you must specify the USING BACKUP CONTROLFILE clause to allow you to point to the archive logs being restored. Otherwise, the recovery process could attempt to access online redo logs instead of the logs retrieved in Step 3. When prompted for the sequences retrieved in Step 3, ensure you specify the file names of the restored archive log copies, as follows:

   SQL> RECOVER DATABASE UNTIL CHANGE RECOVERY_SCN + 1 USING BACKUP CONTROLFILE;
   

5. Open the database with the RESETLOGS option:

   SQL> ALTER DATABASE OPEN RESETLOGS;
   

6. Enable Database Guard

   SQL> ALTER DATABASE GUARD ALL;
   

7. Create a database link to the new primary database and start SQL Apply:

   SQL> CREATE PUBLIC DATABASE LINK myLink -
   > CONNECT TO SYSTEM IDENTIFIED BY password -
   > USING 'service name of new primary database';
   

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY myLink;
   

   At this point, you can disable restricted session (ALTER SYSTEM DISABLE RESTRICTED SESSION) or, if you need to restart the database to re-enable Flashback from Step 4c, let this restart turn off RESTRICTED SESSION.