Converting Between Physical Standby and Snapshot Standby Database (Oracle 12c)

-

 Start Redo Apply


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

OR

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;

** Remember that in Oracle 12c, Real-time Redo Apply is enabled by Default. So, the first command will enable the real-time apply (This behavior is different than 11g version).
This means, if you have specified "DELAY" option in your Redo Transport parameter (log_archive_dest_n) on the primary database and you enable the redo apply using the first command on Standby Database in 12c version, then the database will ignore the DELAY option and apply the redo in real-time. (You will see a message similar to below in the alert.log file informing you that the DELAY option is ignored).

WARNING: Managed Standby Recovery started with REAL TIME APPLY
  DELAY 30 minutes specified at primary ignored


** If you want to use DELAY option in Oracle 12c, you must enable redo apply using the 2nd commnd.

Stop Redo Apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


Monitor Redo Apply Progress

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Steps for converting Physical Standby Database into a Snapshot Standby Database 


1. Stop Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Ensure that the database is mounted:
SQL> SHITDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> select name, open_mode, database_role from v$database;

3. Issue the following SQL command to perform the conversion
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

4. Open the database in read/write mode
SQL> ALTER DATABASE OPEN READ WRITE;


Steps to Convert a Snapshot standby database into a Physical Standby Database

1. Stop the database and start in MOUNT mode
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

2. Issue the following SQL commands to perform the conversion
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

3. start Redo Apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

OR

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;

Few Things to Note :
* When the database is in the Snapshot Mode, the redo data will be received by the Standby database, but will not be applied.
* Redo data received while the database was a snapshot standby database is automatically applied when database is converted backto Physical Standby Mode and the Redo Apply is started.
* Snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.


Steps to apply the redo until a given timestamp and then convert to Snapshot Standby

This steps can be used, if your standby database is running in Snapshot Standby Mode and you are planning to convert it back to Physical Standby, Apply the redo to a sepcific timestamp and then convert it back to Snapshot Standby mode and open the database in Read-Write Mode. (This is similar to do a fast forward your your Standby database to a given timestamp)

Assume that your Standby database is operating in Snapshot Standby Mode and is in Read-Write status.

1. Shutdown the database and start in Mount mode.
SQL> shutdown immediate
SQL> startup mount

2. Convert the database to Physical Standby Mode
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

3. Apply the Redo until the specified time
ALTER DATABASE RECOVER automatic standby database until time '2017-10-01 23:30:12';
** Follow the same timestamp format in your command.

4. Ensure that the database is mounted:
SQL> select name, open_mode, database_role from v$database;

3. Issue the following SQL command to perform the conversion
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

4. Open the database in read/write mode
SQL> ALTER DATABASE OPEN READ WRITE;


The following step can be used, if your standby Database is in Physical Standby Mode and you are using DELAY option in your redo transport and you want to manually apply the log to a sepcific timestamp, which is not yet applied due to DELAY option. (Manually override the DELAY option).
Then open in Snapshot Standby Mode.

Assume that your Standby database is operating in Physical Standby Mode and Redo-Apply Mode is ON (With DELAY option)

1. Stop Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Apply the Redo until the specified time
ALTER DATABASE RECOVER automatic standby database until time '2017-10-01 23:30:12';

2. Ensure that the database is mounted:
SQL> select name, open_mode, database_role from v$database;

3. Issue the following SQL command to perform the conversion
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

4. Open the database in read/write mode
SQL> ALTER DATABASE OPEN READ WRITE;

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more