Tuesday, April 6, 2021

Refreshing a Physical Standby Using Recover from Service on 12c

 If you’re in a situation where you need to resolve a gap in a Data Guard environment due to missing/corrupt archive logs, rolling forward the Physical Standby would be your best option vs. having to rebuild your entire physical standby database. It has become much easier to roll forward a physical standby with the updated features of RMAN on the newer versions. Before 12c, manual steps were needed to determine the current SCN, take an incremental backup, etc. Starting with 12c, the first few steps of taking and applying incremental backups are now automated, but you still have to update your control file to complete the synchronization. From 18c onwards, manual intervention is gone, and refreshing the physical standby runs in one command.

In this tutorial, I will be performing a roll-forward of the physical standby using RECOVER FROM SERVICE on a 12c Database.

Here are the TEST lab environment details:

Refreshing a Physical Standby Using Recover from Service on 12c_ test lab environment details

I simulated an unrecoverable gap in this environment by turning off log transport on the primary, then switching log files enough to cycle through the redo logs, then deleting the generated archive logs. After turning on log transport, it will clearly be unable to resolve the gap.

12345678910111213141516171819202122232425  DGMGRL> show configuration  
    
  Configuration - my_dg_config  
    
    Protection Mode: MaxPerformance  
    Members:  
    xtprim - Primary database  
      Error: ORA-16724: cannot resolve gap for one or more members  
    
     xtstby - Physical standby database  
       Warning: ORA-16809: multiple warnings detected for the member  
   
 Fast-Start Failover: DISABLED  
   
 Configuration Status:  
 ERROR   (status updated 6 seconds ago)  

Steps:

1. Standby should be in mount mode

1234567      SQL> select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from V$DATABASE;  
        
      NAME      DB_UNIQUE_NAME    OPEN_MODE     DATABASE_ROLE  
      --------- ----------------  ------------- ----------------  
      XTPRIM    XTSTBY            MOUNTED       PHYSICAL STANDBY 

2. Stop the managed recovery process (MRP) on the physical standby using broker (DGMGRL)

1234567      DGMGRL> edit database XTSTBY set state=APPLY-OFF;  
      Succeeded.  

3. Identify the datafiles on the physical standby database that are out of sync.

XTPRIM

1234567      SQL> select HXFIL, FHSCN from X$KCVFH;  
        
           HXFIL FHSCN  
      ---------- --------------------  
               1 2269109  
               3 2269109  
               4 2269109  
               5 1435690  
               6 1435690  
            7 2269109  
            9 1572106  
           10 1572106  
           11 1572106

XTSTBY

1234567  SQL> select HXFIL, FHSCN from X$KCVFH;  
    
       HXFIL FHSCN  
  ---------- --------------------  
           1 2268370  
           3 2268370  
           4 2268370  
           5 1435690  
           6 1435690  
          7 2268370  
          9 1572106  
         10 1572106  
         11 1572106  

4. Take note of the current SCN of the physical standby database. This is required to determine, in a later step, if datafiles were added to the primary database.

1234567  SQL> select CURRENT_SCN from V$DATABASE;  
    
  CURRENT_SCN  
  -----------  
      2268369  

5. Roll forward the physical standby database using the RECOVER FROM SERVICE command..

12345678910111213141516171819  [oracle@asmnode2 ~]$ rman target /  
    
  Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 22 10:51:12 2020  
    
  Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.  
    
  connected to target database: XTPRIM (DBID=3617192712, not open)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990  RMAN> recover database from service XTPRIM noredo using compressed backupset;  
    
  Starting recover at 22-JUN-20  
  Starting implicit crosscheck backup at 22-JUN-20  
  using target database control file instead of recovery catalog  
  allocated channel: ORA_DISK_1  
  channel ORA_DISK_1: SID=29 device type=DISK  
  Crosschecked 2 objects  
  Finished implicit crosscheck backup at 22-JUN-20  
   
 Starting implicit crosscheck copy at 22-JUN-20  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 22-JUN-20  
   
 searching for all files in the recovery area  
 cataloging files...  
 no files cataloged  
   
 using channel ORA_DISK_1  
 skipping datafile 5; already restored to SCN 1435690  
 skipping datafile 6; already restored to SCN 1435690  
 skipping datafile 9; already restored to SCN 1572106  
 skipping datafile 10; already restored to SCN 1572106  
 skipping datafile 11; already restored to SCN 1572106  
 channel ORA_DISK_1: starting incremental datafile backup set restore  
 channel ORA_DISK_1: using compressed network backup set from service XTPRIM  
 destination for restore of datafile 00001: +DATA/XTSTBY/DATAFILE/system.260.1043528437  
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07  
 channel ORA_DISK_1: starting incremental datafile backup set restore  
 channel ORA_DISK_1: using compressed network backup set from service XTPRIM  
 destination for restore of datafile 00003: +DATA/XTSTBY/DATAFILE/sysaux.261.1043528451  
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:08  
 channel ORA_DISK_1: starting incremental datafile backup set restore  
 channel ORA_DISK_1: using compressed network backup set from service XTPRIM  
 destination for restore of datafile 00004: +DATA/XTSTBY/DATAFILE/undotbs1.262.1043528459  
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03  
 channel ORA_DISK_1: starting incremental datafile backup set restore  
 channel ORA_DISK_1: using compressed network backup set from service XTPRIM  
 destination for restore of datafile 00007: +DATA/XTSTBY/DATAFILE/users.265.1043528467  
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03  
   
 Finished recover at 22-JUN-20 

6. Compare both primary and standby’s datafile SCN’s.

XTPRIM

1234567      SQL> select HXFIL, FHSCN from X$KCVFH;  
        
           HXFIL FHSCN  
      ---------- --------------------  
               1 2277525  
               3 2277539  
               4 2277553  
               5 1435690  
               6 1435690  
            7 2277561  
            9 1572106  
           10 1572106  
           11 1572106  

XTSTBY

1234567      RMAN> select HXFIL, FHSCN from X$KCVFH;  
        
           HXFIL FHSCN  
      ---------- --------------------  
               1 2277525  
               3 2277539  
               4 2277553  
               5 1435690  
               6 1435690  
            7 2277561  
            9 1572106  
           10 1572106  
           11 1572106  

We should now see that the datafiles are in sync.

7. Complete the synchronization by refreshing the physical standby’s control file from the primary.

12345678910  RMAN> shutdown immediate  
  RMAN> startup nomount  
1234567891011121314151617181920212223242526272829303132  RMAN> restore standby controlfile from service XTPRIM;  
    
  Starting restore at 22-JUN-20  
  allocated channel: ORA_DISK_1  
  channel ORA_DISK_1: SID=260 device type=DISK  
    
  channel ORA_DISK_1: starting datafile backup set restore  
 channel ORA_DISK_1: using network backup set from service XTPRIM  
  channel ORA_DISK_1: restoring control file  
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04  
 output file name=+DATA/XTSTBY/CONTROLFILE/current.259.1043528429  
 output file name=+FRA/XTSTBY/CONTROLFILE/current.258.1043528429  
 Finished restore at 22-JUN-20

Mount database

12345678  RMAN> alter database mount 

8. Catalog the datafile location. Since this environment is using Oracle Manage Files (OMF), the file structure and datafiles names will be different. You will get a warning message: RMAN-06139: warning: control file is not current for REPORT SCHEMA. during report schema.

123456789101112131415161718192021222324252627282930313233343536  RMAN> report schema;  
    
  Starting implicit crosscheck backup at 22-JUN-20  
  released channel: ORA_DISK_1  
  allocated channel: ORA_DISK_1  
  channel ORA_DISK_1: SID=260 device type=DISK  
  Crosschecked 10 objects  
  Finished implicit crosscheck backup at 22-JUN-20  
    
 Starting implicit crosscheck copy at 22-JUN-20  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 22-JUN-20  
   
 searching for all files in the recovery area  
 cataloging files...  
 cataloging done  
   
 List of Cataloged Files  
 =======================  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_22/thread_1_seq_30.284.1043740837  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_22/thread_1_seq_31.285.1043747041  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_22/thread_1_seq_38.286.1043749751  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_22/thread_1_seq_39.287.1043749869  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_21/thread_1_seq_25.279.1043626555  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_21/thread_1_seq_26.280.1043657483  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_21/thread_1_seq_27.281.1043657511  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_21/thread_1_seq_28.282.1043680031  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_21/thread_1_seq_29.283.1043701101  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_20/thread_1_seq_20.273.1043573589  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_20/thread_1_seq_21.274.1043573589  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_20/thread_1_seq_19.275.1043573591  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_20/thread_1_seq_22.276.1043573603  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_20/thread_1_seq_23.277.1043588677  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_20/thread_1_seq_24.278.1043604915  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_19/thread_1_seq_12.259.1043528483  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_19/thread_1_seq_13.260.1043528485  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_19/thread_1_seq_14.268.1043529443  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_19/thread_1_seq_15.269.1043529617  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_19/thread_1_seq_16.270.1043529689  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_19/thread_1_seq_17.271.1043529691  
 File Name: +FRA/XTSTBY/ARCHIVELOG/2020_06_19/thread_1_seq_18.272.1043529691  
   
 RMAN-06139: warning: control file is not current for REPORT SCHEMA  
 Report of database schema for database with db_unique_name XTSTBY  
   
 List of Permanent Datafiles  
 ===========================  
 File Size(MB) Tablespace           RB segs Datafile Name  
 ---- -------- -------------------- ------- ------------------------  
 1    0        SYSTEM               ***     +DATA/XTPRIM/DATAFILE/system.257.1043513685  
 3    0        SYSAUX               ***     +DATA/XTPRIM/DATAFILE/sysaux.258.1043513729  
 4    0        UNDOTBS1             ***     +DATA/XTPRIM/DATAFILE/undotbs1.259.1043513755  
 5    0        PDB$SEED:SYSTEM      ***     +DATA/XTPRIM/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.267.1043513859  
 6    0        PDB$SEED:SYSAUX      ***     +DATA/XTPRIM/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.266.1043513859  
 7    0        USERS                ***     +DATA/XTPRIM/DATAFILE/users.260.1043513757  
 9    0        PDB1:SYSTEM          ***     +DATA/XTPRIM/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/system.270.1043514675  
 10   0        PDB1:SYSAUX          ***     +DATA/XTPRIM/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/sysaux.271.1043514675  
 11   0        PDB1:MARVIN          ***     +DATA/XTPRIM/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/marvin.273.1043514911  
   
 List of Temporary Files  
 =======================  
 File Size(MB) Tablespace           Maxsize(MB) Tempfile Name  
 ---- -------- -------------------- ----------- --------------------  
 1    20       TEMP                 32767       +DATA/XTPRIM/TEMPFILE/temp.265.1043513851  
 2    64       PDB$SEED:TEMP        32767       +DATA/XTPRIM/A877267091982C90E0530FBEA8C0E855/TEMPFILE/temp.269.1043513889  
 3    64       PDB1:TEMP            32767       +DATA/XTPRIM/A877553C8E534E4FE0530FBEA8C06F41/TEMPFILE/temp.272.1043514677 

We need to update the datafile location and name using catalog.

12345678910111213141516171819202122232425  RMAN> catalog start with '+DATA/XTSTBY/';  
    
  searching for all files that match the pattern +DATA/XTSTBY/  
    
  List of Files Unknown to the Database  
  =====================================  
  File Name: +DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/system.266.1043528469  
  File Name: +DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/sysaux.267.1043528471  
  File Name: +DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/marvin.268.1043528479  
 File Name: +DATA/XTSTBY/A877267091982C90E0530FBEA8C0E855/DATAFILE/system.263.1043528461  
 File Name: +DATA/XTSTBY/A877267091982C90E0530FBEA8C0E855/DATAFILE/sysaux.264.1043528463  
 File Name: +DATA/XTSTBY/DATAFILE/system.260.1043528437  
 File Name: +DATA/XTSTBY/DATAFILE/sysaux.261.1043528451  
 File Name: +DATA/XTSTBY/DATAFILE/undotbs1.262.1043528459  
 File Name: +DATA/XTSTBY/DATAFILE/users.265.1043528467  
   
   
 Do you really want to catalog the above files (enter YES or NO)? YES  
 cataloging files...  
 cataloging done  
   
 List of Cataloged Files  
 =======================  
 File Name: +DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/system.266.1043528469  
 File Name: +DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/sysaux.267.1043528471  
 File Name: +DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/marvin.268.1043528479  
 File Name: +DATA/XTSTBY/A877267091982C90E0530FBEA8C0E855/DATAFILE/system.263.1043528461  
 File Name: +DATA/XTSTBY/A877267091982C90E0530FBEA8C0E855/DATAFILE/sysaux.264.1043528463  
 File Name: +DATA/XTSTBY/DATAFILE/system.260.1043528437  
 File Name: +DATA/XTSTBY/DATAFILE/sysaux.261.1043528451  
 File Name: +DATA/XTSTBY/DATAFILE/undotbs1.262.1043528459  
 File Name: +DATA/XTSTBY/DATAFILE/users.265.1043528467  
   
 List of Files Which Were Not Cataloged  
 =======================================  
 File Name: +DATA/XTSTBY/CONTROLFILE/current.257.1043528427  
   RMAN-07517: Reason: The file header is corrupted  
 File Name: +DATA/XTSTBY/CONTROLFILE/current.258.1043528427  
   RMAN-07517: Reason: The file header is corrupted

The error can be ignored since these are the old control files that we can remove later.

9. Switch to the cataloged datafile copy

123456789  RMAN> switch database to copy;  
    
  datafile 1 switched to datafile copy "+DATA/XTSTBY/DATAFILE/system.260.1043528437"  
  datafile 3 switched to datafile copy "+DATA/XTSTBY/DATAFILE/sysaux.261.1043528451"  
  datafile 4 switched to datafile copy "+DATA/XTSTBY/DATAFILE/undotbs1.262.1043528459"  
  datafile 5 switched to datafile copy "+DATA/XTSTBY/A877267091982C90E0530FBEA8C0E855/DATAFILE/system.263.1043528461"  
  datafile 6 switched to datafile copy "+DATA/XTSTBY/A877267091982C90E0530FBEA8C0E855/DATAFILE/sysaux.264.1043528463"  
  datafile 7 switched to datafile copy "+DATA/XTSTBY/DATAFILE/users.265.1043528467"  
  datafile 9 switched to datafile copy "+DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/system.266.1043528469"  
 datafile 10 switched to datafile copy "+DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/sysaux.267.1043528471"  
 datafile 11 switched to datafile copy "+DATA/XTSTBY/A877553C8E534E4FE0530FBEA8C06F41/DATAFILE/marvin.268.1043528479"

10. Use the current SCN returned in step 4 to determine if new data files were added to the primary database since the standby database was last refreshed. If yes, these datafiles need to be restored on the standby from the primary database.

12345678  RMAN> select file# from V$DATAFILE where CREATION_CHANGE# >= 2268369; 
   
  no rows selected  

11. Update the names of the online redo logs and standby redo logs in the standby control file using the following methods:

1234567  RMAN> select GROUP# from V$LOG;  
    
      GROUP#  
  ----------  
           1  
           3  
           2  
    
  RMAN> select GROUP# from V$LOGFILE where TYPE='STANDBY' group by GROUP#;  
   
     GROUP#  
 ----------  
         10  
         11  
         12  
         13  
   
 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;  
 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;  
 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;  
 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 10;  
 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 11;  
 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 12;  
 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 13;

12. Using broker (DGMGRL), start the managed recovery process (MRP).

1234567      DGMGRL> edit database XTSTBY set state=APPLY-ON;  
      Succeeded.

Physical standby should now be in sync with the primary and logs are back to being applied.