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.
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.