Role Forward Physical Standby Using service method

 Oracle Physical standby sometimes can go out of sync and it may not be possible to get the archive logs due to the size and amount of time. In these cases earlier we need to either rebuild the standby or take the incremental backup from primary database and recover the standby.

With 12c in place oracle introduced one more method commonly known as recover form service as well which makes this process simple and straightforward.

Using service method does following

1. It takes the incremental backup from the SCN of the standby datafile and transfer that backup to DR site over the network (earlier this method use to be done manually).

2. Applies the incremental backup to physical standby.


Lets discuss the steps now in detail:


Step1: Start the standby database is mount state.

    srvctl stop database -d <db unique name>

    srvctl start database -d <db unique name> -o mount


Step2: Stop the standby database recovery

    alter database recover managed standby database cancel;


Step3: Identify the scn on primary and standby database and keep that in records to be used later.

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

Above needs to be checked on both primary and standby and will give you scn of each datafile which will tell what all datafiles on standby is lagging behind primary scn and need recovery.

Also gather the current_scn on standby

    select to_char(current_scn) from v$database;


Step4: Now the main step comes which recovers your standby using service method

    rman> recover database from service <primary service name> noredo using compressed backupset;

This step would take time based on recovery your standby needs.


Step5: Since we have done the datafile recovery and our control file would be still having old scn, it's time to restore the controlfile as well.

This would also need some additional steps as your datafile naming could be different names due to various reasons.

    shutdown immediate

    startup nomount

    rman> restore standby controlfile from service <primary service name>;

    rman> alter database mount;

    rman> report schema;

Now if your datafiles, tempfiles lies in different location on standby following below steps else skip to next one

    rman> catalog start with '/datafile location/';

    rman> switch database to copy;


Step6: Now there would be cases where some new datafiles would be added on primary site during the time your standby was out of sync. 

In order to identify we need to use current_scn value from standby and run query on primary

    select file# from v$datafile where creation_change# = <current_scn value>;

In case of no files reported we are good to skip next steps else follow along

Identify the datafiles which are newly created and restore them on standby

    rman> run{

                        set newname for database to '/';

                        restore datafile <datafile number> from service <primary service name>;

                    }

Repeat above steps for all the datafiles which was created later on primary site and needed to be restore on standby.


Step7: Next step would be to update the names of standby redo log files

    alter database clear logfile group <group_number>;


Step8: Standby the standby recovery

        alter database recover managed standby database using current logfile disconnect;


Step9: Perform some switches on primary site and make sure everything is in sync

       alter system archive log current;


For more details refer to Oracle Support Metalink Doc Id: 1987763.1

Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s...
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose. ...
Read more

Error Processing Request. - Oracle APEX Post upgrade (Invalid WWV_FLOW_SESSION_RAS)

There would be occasions where you would upgrade your oracle database which is hosting your APEX and post upgrade you get error while opening APEX URL " Error Processing Request Contact your application administrator " This looks very strange as you have just upgraded only database. Database version and APEX version are compatible. For Argument lets say both are on 19c.  Now since UI doesn't give detailed information about the error you would need to get to backend oracle database and query view apex_debug_messages.     select * from apex_debug_messages; Looking at the results of this view you would get the clue of the error in this case it was:      ORA-04063 package body APEX_190100.WWV_FLOW_SESSION_RAS has error This means this package body has problem and looking at the status of this body it's found to be invalid and when you try to compile it get following error      alter package APEX_050100.WWV_FLOW_SESSION_RAS compile body;  ...
Read more