CONVERT PHYSICAL STANDBY TO SNAPSHOT STANDBY DATABASE USING MANUAL METHOD IN ORACLE DATAGUARD

Snapshot standby database is a new feature introduced in oracle version 11g which acts as a clone mirror image of primary database operating in read write mode without log apply.

If there is a physical standby database running, we can easily convert to snapshot standby for testing and offloading purposes.

As the SNAPSHOT STANDBY, name itself denotes that taking a snapshot of the primary database for performing quick tests.

A guaranteed restore point is automatically created by oracle after conversion of snapshot standby for rollback purposes to get back to previous physical standby state in case of failures.

FIG – 1.0 – SNAPSHOT STANDBY

Check prerequisites in primary and standby database before conversion.

Birds view steps:

Here PRIM stands for primary database and STDBY stands for physical standby database

kish@PRIM>select name,open_mode,database_role,switchover_status from gv$database;
kish@STDBY>select name,open_mode,database_role,switchover_status from gv$database;
kish@STDBY>alter database recover managed standby database cancel;
kish@STDBY>shu immediate;
kish@STDBY>startup mount;
kish@STDBY>alter database flashback on;
kish@STDBY>select flashback_on from v$database;
kish@STDBY>alter database convert to snapshot standby;
kish@STDBY>select database_role from v$database;
kish@STDBY>alter database open;
kish@STDBY>select open_mode from v$database;

In primary – switchover state should be TO STANDBY

kish@PRIM>select name,open_mode,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
DBX00     READ WRITE           PRIMARY          TO STANDBY

Check standby database status and cancel MRP first

kish@STDBY>select name,open_mode,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
DBX00     MOUNTED              PHYSICAL STANDBY NOT ALLOWED

kish@STDBY>alter database recover managed standby database cancel;

Database altered.

Bounce the standby database in mount state. You dont need guaranteed restore point because oracle automatically create for you. No worries! But for safer side enable flashback

kish@STDBY>shu immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
kish@STDBY>startup mount;
ORACLE instance started.

Total System Global Area                                         1509949440 bytes
Fixed Size                                                          2924640 bytes
Variable Size                                                     989859744 bytes
Database Buffers                                                  503316480 bytes
Redo Buffers                                                       13848576 bytes
Database mounted.
kish@STDBY>select * from v$restore_point;

no rows selected

kish@STDBY>select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
kish@STDBY>alter database flashback on;

Database altered.

kish@STDBY>select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Issue the below command and check the database open mode. If the database is opened in read write mode, then congratz ! you have converted to snapshot standby

kish@STDBY>alter database convert to snapshot standby;

Database altered.

kish@STDBY>select database_role from v$database;

DATABASE_ROLE
----------------
SNAPSHOT STANDBY

When the time you issue convert command, check the logs parallely for any errors and background working of snapshot standby. For learning purpose, you can see the comments below

alter database convert to snapshot standby
++++++++++ RVWR process automatically start to read and write flashback data from flashback buffer to FRA++++++++++

Starting background process RVWR
Wed Sep 22 03:56:28 2021
RVWR started with pid=31, OS id=9470
Wed Sep 22 03:56:29 2021

++++++++++Oracle automatically created GRP and allocated buffers in shared pool for flashback generation. Oracle also kills all RFS process to freeze log shipping. Cool isnt it !?++++++++++++

Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_09/22/2021 03:56:28
Killing 2 processes (PIDS:9451,9449) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 9447
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 2137589 time 09/22/2021 03:53:16
Resetting resetlogs activation ID 2195284314 (0x82d9615a)
Online log /apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log: Thread 1 Group 1 was previously cleared
Online log /apps01/FRA/DBX01/onlinelog/o1_mf_2_jnl5wd63_.log: Thread 1 Group 2 was previously cleared
Online log /apps01/FRA/DBX01/onlinelog/o1_mf_3_jnl5wllv_.log: Thread 1 Group 3 was previously cleared
Online log /apps01/FRA/DBX01/onlinelog/o1_mf_4_jnl5ww8k_.log: Thread 1 Group 4 was previously cleared
Online log /apps01/FRA/DBX01/onlinelog/stdbylog1.log: Thread 1 Group 5 was previously cleared
+++++++++++
Oracle also notes down the SCN of standby  before the physical standby get converted to snapshot standby
Set the new incarnation 
Then convert to snapshot standby
++++++++++++

Standby became primary SCN: 2137587
Wed Sep 22 03:56:31 2021
Setting recovery target incarnation to 3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
alter database open
Wed Sep 22 03:56:52 2021
Ping without log force is disabled
.
Wed Sep 22 03:56:52 2021
Assigning activation ID 2195429791 (0x82db999f)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log
Successful open of redo thread 1
Wed Sep 22 03:56:52 2021
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Sep 22 03:56:52 2021
ARC2: Becoming the 'no SRL' ARCH

Open the database and there you go!

kish@STDBY> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

kish@STDBY>alter database open;

Database altered.

kish@STDBY>select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

RFS will be vanished because oracle killed all RFS process to freeze log shipping

kish@STDBY>select process,status,sequence# from v$managed_standby;

PROCESS   STATUS                                                SEQUENCE#
--------- ------------ --------------------------------------------------
ARCH      CONNECTED                                                     0
ARCH      CONNECTED                                                     0
ARCH      CONNECTED                                                     0
ARCH      CONNECTED                                                     0

Also the standby sequence is reset

kish@STDBY>select group#,thread#,sequence#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;

    GROUP#    THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ------------- ------------
         1          1          1       3627733   2.8147E+14
         2          1          0             0            0
         3          1          0             0            0
         8          1          0             0            0
         5          1          0             0            0
         6          1          0             0            0
         7          1          0             0            0
         4          1          0             0            0

8 rows selected.

Check the GRP status which is automatically created

kish@STDBY>select NAME,GUARANTEE_FLASHBACK_DATABASE,SCN from v$restore_point;

NAME            GUA                                                SCN
--------------- --- --------------------------------------------------
SNAPSHOT_STANDB YES                                            2137588
Y_REQUIRED_09/2
2/2021 03:56:28

I switch the logfile in primary

kish@PRIM>alter system switch logfile;

System altered.

Then i was able to see RFS and LNS in standby database. Not sure why ! But i can confirm that RFS receive the redo from primary but not applying it. MRP process doesnot apply logs nor running.

kish@STDBY>select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                 73
LNS       CONNECTED             0

7 rows selected.

kish@STDBY>!ps -ef|grep 'mrp'
oracle   10045  9976  0 17:08 pts/1    00:00:00 /bin/bash -c ps -ef|grep 'mrp'
oracle   10047 10045  0 17:08 pts/1    00:00:00 grep mrp

Lets not discuss more about internal workings of snapshot standby in this post as we see that in another post and lets get into testing of snapshot standby

In Snap standby: – Advantage

Now it is going to be interesting to perform some DDL and DML changes in snapshot standby for testing some application or business data.

kish@STDBY>create table t1 as select * from t;

Table created.
kish@STDBY>select * from t;

NAME               ID
---------- ----------
abc                 1
abc                 1

kish@STDBY>alter table t modify name varchar2(30);

Table altered.

kish@STDBY>insert into t values('snapshot_work_or_not?',2);

1 row created.

kish@STDBY>insert into t values('rechechk_work_or_not?',3);

1 row created.

kish@STDBY>insert into t values('rechechk_again',4);

1 row created.

kish@STDBY>insert into t values('it_worked',5);

1 row created.

kish@STDBY>commit;

Commit complete.

kish@STDBY>select * from t;

NAME                                   ID
------------------------------ ----------
abc                                     1
abc                                     1
snapshot_work_or_not?                   2
rechechk_work_or_not?                   3
rechechk_again                          4
it_worked                               5

6 rows selected.

Snapshot standby is also a boon for offloading the backups to reduce overhead to primary database even during the business hours. Awesome !

RMAN> backup current controlfile;

Starting backup at 02-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 02-OCT-21
channel ORA_DISK_1: finished piece 1 at 02-OCT-21
piece handle=/apps01/FRA/DBX01/backupset/2021_10_02/o1_mf_ncnnf_TAG20211002T172044_jojkwoz7_.bkp tag=TAG20211002T172044 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 02-OCT-21

Starting Control File and SPFILE Autobackup at 02-OCT-21
piece handle=/apps01/FRA/DBX01/autobackup/2021_10_02/o1_mf_s_1084900849_jojkwtf4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-OCT-21
RMAN-08591: WARNING: invalid archived log deletion policy

RMAN> select database_role from v$database;

DATABASE_ROLE
----------------
SNAPSHOT STANDBY

To get back to physical standby

Bounce the database to mount mode

kish@STDBY>shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
kish@STDBY>startup mount;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             989859744 bytes
Database Buffers          503316480 bytes
Redo Buffers               13848576 bytes
Database mounted.

Convert to physical standby

kish@STDBY>alter database convert to physical standby;

Database altered.

Refer logs to learn what is happening behind the back

alter database convert to physical standby
Sat Oct 02 17:48:17 2021
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (dbx01)
Sat Oct 02 17:48:17 2021
++++++ Oracle again kill RFS process for revert +++++++

Killing 2 processes (PIDS:10799,10797) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 10795

++++++Oracle removes the archivelog files which were generated for the table t during the DML changes++++++

Sat Oct 02 17:48:18 2021
Deleted Oracle managed file /apps01/FRA/DBX01/archivelog/2021_10_02/o1_mf_1_80_jojmggvg_.arc
Sat Oct 02 17:48:19 2021

++++++ Post conversion oracle also drops the restore point which was created automatically++++++

Flashback Restore Start
Flashback: dropped tablespace #3: 'TEMP' of pdb #1 from the control file.
Flashback: created tablespace #3: 'TEMP' of pdb #1 in the controlfile.
Flashback: dropped tablespace #6: 'TEMP1' of pdb #1 from the control file.
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Clearing standby activation ID 2196472829 (0x82eb83fd)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 184 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl8.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl9.f' SIZE 52428800;

++++++Datafiles which were marked offline are changed back to online++++++

Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 8 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 9 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 10 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 11 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 12 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 13 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
ARCH: Warning; less destinations available than specified
by LOG_ARCHIVE_MIN_SUCCEED_DEST init.ora parameter
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby

Check if MRP is started and applying logs

kish@STDBY>!ps -ef|grep 'mrp'
oracle   10834     1  0 17:49 ?        00:00:14 ora_mrp0_dbx01
oracle   11357 10699  0 18:19 pts/1    00:00:00 /bin/bash -c ps -ef|grep 'mrp'
oracle   11359 11357  0 18:19 pts/1    00:00:00 grep mrp

kish@STDBY>select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                 80
MRP0      WAIT_FOR_LOG         80

7 rows selected.

Check the role and status

kish@STDBY>select database_role,status from v$database,v$instance;

DATABASE_ROLE    STATUS
---------------- ------------
PHYSICAL STANDBY MOUNTED

Verify the MRP sync

kish@STDBY>alter database recover managed standby database using current logfile disconnect;

Database altered.

kish@STDBY>select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       RECEIVING            73
RFS       IDLE                  0
MRP0      APPLYING_LOG         71

7 rows selected.

Restore point is dropped

kish@STDBY>select * from v$restore_point;

no rows selected

Turn flashback off if no longer needed

kish@STDBY>alter database flashback off;

Database altered.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s