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>';
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>;
}
{
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.
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;
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
Thanks for sharing, very much useful to fix archive gap after flashback to restore point on primary and standby.
ReplyDelete