Skip to main content

To fix huge lag between primary and DR using 12c feature: 12c Recover from service




Scope


If transport or apply lag is huge difference, then we can fix using 12c new feature “Recover from Service”.

Prerequisites:

  • Oracle Net connectivity is established between the physical standby database and the primary database.
  • You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database.
  • The password files on the primary database and the physical standby database are the same.
  • The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0 or higher.
  • Start RMAN and connect as target to the physical standby database.

Detailed Steps:


Step 1:
Stop the MRP process in standby database.
If dg broker:
Edit database <dbname> set state=’APPLY-OFF’;

Step 2: 
Shut down the standby database and mount the same.
Shut immediate;  (srvctl stop database –d <dbname>)   à standby db
Startup mount;

Step 3:
Check SCN value in datafile headers in both primary and standby

select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;

Check for scn difference in both primary and standby datafile headers ..kindly note them

Step 4:
Note the current SCN of the physical standby database. This is required to determine, in a later step, if new data files were added to the primary database.
Select current_scn from v$database;

Step 5:
The RECOVER … FROM SERVICE command refreshes the standby data files and rolls them forward to the same point-in-time as the primary.
Connect to rman target / in standby database.
Now, run below command to make it sync with primary.

RMAN> recover database from service <primary_service_name> noredo using compressed backupset;

Step 6:
After finishing recovery compare scn in headers of both primary and standby database.
select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;

Step 7:
Control file is not updated with new SCN’s so we have to create standby control file from primary.

Shut immediate;
Startup nomount;

Connect to rman utility and run below command to recreate standby controlfile from primary

Rman>restore standby controlfile from service <primary_service_name>;

Step 8:
After this step, the names of files in the standby control file are the names that were used in the primary database.
NOTE:  Depending on the configuration, the path and/or names of the standby datafiles after the standby controlfile refresh may be correct and thus steps #9 and #10 can be skipped.  

Mount the database after restoring standby control file
Rman> alter database mount;
Rman > report schema;

It will give list of datafiles location, if it is same as primary then ignore below step 9 & 10.
Our case, we have to follow step 9 & 10.

Step 9:
Update the names of the data files and the temp files in the standby control file.
Use the CATALOG command and the SWITCH command to update all the data file names.

        RMAN> catalog start with '<path where the actual standby datafile existed>';

Ex: catalog start with ‘+DG_NAME/DB_NAME/datafile/’;


Step 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 data files need to be restored on the standby from the primary database.
Sql > SELECT file# FROM V$DATAFILE WHERE creation_change# >= <SCN taken in step 4>;

Note: If no files are returned in Step 10, then go to Step 12. If one or more files are returned in Step 10, then restore these data files from the primary database as in step 11.


Step 11:
If you are not connected to a recovery catalog, then use the following commands to restore data files that were added to the primary after the standby was last refreshed (assuming new datafile added to the primary):
Rman>
RUN
{
SET NEWNAME FOR DATABASE TO 'Standby file location';
RESTORE DATAFILE <new datafile id> FROM SERVICE <primary service name>;
}
If you are connected to a recovery catalog, then use the following command to restore data files that were added to the primary after the standby was last refreshed (assuming new data file added to the primary):
RMAN>RESTORE DATAFILE <new datafile id> FROM SERVICE <primary service name>;

Step 12:
 Switch to cataloged copy.
RMAN> SWITCH DATABASE TO COPY;


Below steps 13 & 14 are optional. If online redo logs are not updated or showing same as primary, then follow below steps.
Renaming log files is recommended when the number of online redo logs files and standby redo log files is the same in the primary database and the physical standby database.

Step 13:
Update the names of the online redo logs and standby redo logs in the standby control file using one of the following methods:
à Use the ALTER DATABASE CLEAR command to clear the log files in all redo log groups of the standby database. RMAN then recreates all the standby redo logs and the online redo log files.
 Note:
Clearing log files is recommended only if the standby database does not have access to the online redo log files and standby redo log files of the primary database( for ex: standby and primary at same server or using same ASM disk group). If the standby database has access to the redo log files of the primary database and the redo log file names of the primary database are OMF names, then the ALTER DATABASE command will delete log files on the primary database.
à Use the ALTER DATABASE RENAME FILE command to rename the redo log files.
   Use a separate command to rename each log file.

 To rename log files, the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.
 

Step 14:
 (Oracle Active Data Guard only) Perform the following steps to open the physical standby database:
ALTER SYSTEM ARCHIVE LOG CURRENT;
On the physical standby database, run the following commands:
        RECOVER DATABASE;
        ALTER DATABASE OPEN READ ONLY;

Step 15:
Start the managed recovery processes on the physical standby database by using the following command:

     ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If DG broker then,
Dgmgrl> edit database <db_name> set state=’APPLY-ON’;


Source : followed steps as specified in Oracle DOC id : 1987763.1

Comments

  1. Thanks for sharing, very much useful to fix archive gap after flashback to restore point on primary and standby.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to unmount and mount ACFS FILESYSTEM

How to unmount and mount ACFS FileSystem in Linux Unmounting ACFS File Systems ACFS file systems can be used by Oracle RAC for hosting software files for the database. It can also be used as a general purpose file system for non-database files. The ACFS file system is managed and administered by the Oracle Grid Infrastructure, So ACFS file systems will be impacted when shutting down the GI stack for patching GI homes. Shut down the processes (including non-Oracle processes) that use the files on ACFS, then unmount the ACFS file system. If the ACFS file system is used by Oracle database software, then perform Steps 1 and 2. 1.      Execute the following command to find the names of the CRS managed ACFS file system resource.  # crsctl stat res -w "TYPE = ora.acfs.type" -p | grep VOLUME 2.      Execute the following command to stop the CRS managed ACFS file system resource with the resource name found from Step 1. ...

How to perform GRID/RDBMS patching of DB systems deployed in OCI using dbcli command line utility

This procedure covers OS, Grid and RDBMS home patching for Oracle Cloud Infrastructure (OCI) DB systems using the dbcli utility. Run every step as root . Advantages of dbcli patching Simple command line, with no manual patch download or copy to the nodes. Patches are downloaded automatically if the service gateway can reach Oracle Object Storage and the YUM repos. Applied in a rolling fashion, so no downtime is needed. Disadvantages You can't choose the patch version; the latest available in OCI is applied. Rollback isn't possible from the console, and dbcli support is limited (see the note at the end). Steps 1. Backup Make sure a recent full backup exists before patching. 2. Blackout Create a blackout for both nodes (console or emctl ) to avoid unnecessary alerts. 3. Check patch availability # dbcli describe-component 4. Pre-checks Grid home: # dbcli update-server -p # dbcli describe-job -i <jobId> RDBMS home: # dbcli list-dbhomes...