Converting a Physical Standby Database to Snapshot Standby can be of a great solution if you need to do some new code test in a system but you can't have a full Test Clone.
Also, since the standby is a block by block copy of the Primary, we are making sure the test is made using the same data we have in our "production database".
Once the Test is completed, we can just convert it back to Physical Standby and the system will revert to same incarnation we have in the primary and get on sync with it.
If we have Data broker (and we always should) the conversion is just a command, but just in case, we will explore the Manual Process as well as the Data Broker Process.
The Process below is done in a 11g Database, but is the same for newer versions.
In our Scenario, the Standby is running in rac2-node1/2 and called st112, and the primary is running in rac1-node1/2 and called db112
Index
Manual Process
First thing we need to make sure for this process is to have the Standby Mounted (won't work if it is open) and Recovery Process has also need to be stopped.
So lets do just that:
[oracle@rac2-node1 ~]$ srvctl stop database -d st112
[oracle@rac2-node1 ~]$ srvctl start database -d st112 -o mount
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
st112 > select inst_id, process, status MRP_stat, thread#, sequence# from gv$managed_standby where process like 'MRP%';
INST_ID PROCESS MRP_STAT THREAD# SEQUENCE#
---------- --------- ------------ ---------- ----------
1 MRP0 APPLYING_LOG 2 294
st112 > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
st112 > select inst_id, process, status MRP_stat, thread#, sequence# from gv$managed_standby where process like 'MRP%';
no rows selected
st112 >
We are now ready to convert the system to Snapshot Standby with the following action:
st112 > alter database convert to snapshot standby;
Database altered.
st112 >
What is really interesting in this process, is the the system will automatically create a Guaranteed restore point (no need to have Flashback Enable for it) so we can revert back to previous incarnation point once we want
# Alert log Output #
Sat Sep 12 13:44:11 2020
alter database convert to snapshot standby
Sat Sep 12 13:44:13 2020
RVWR started with pid=51, OS id=22583
Allocated 15937344 bytes in shared pool for flashback generation buffer
Sat Sep 12 13:44:25 2020
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_09/12/2020 13:44:12
Sat Sep 12 13:44:28 2020
Killing 6 processes with pids 22066,22070,21896,21832,21893,21838 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 21645 on instance 1
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 2449463
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1011253944 (0x3c4682b8)
Online log +RECO/st112/onlinelog/group_1.274.1022534215: Thread 1 Group 1 was previously cleared
# Checking GRP detail #
st112 >SELECT Name, STORAGE_SIZE/1024/1024/1024 as "Restore Point Size GB" FROM V$RESTORE_POINT where GUARANTEE_FLASHBACK_DATABASE='YES';
NAME Restore Point Size GB
-------------------------------------------------- ---------------------
SNAPSHOT_STANDBY_REQUIRED_09/12/2020 13:44:12 .09765625
st112 >
We have now the system converted to Snapshot Standby, but still as mounted, so we can now open each instance from sqlplus or just restart the system as open using clusterware (I prefer clusterware method :) )
st112 >
set lines 500 pages 150
col HOST_NAME for a35
SELECT INSTANCE_NAME, TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP", DATABASE_ROLE, OPEN_MODE from GV$INSTANCE, V$DATABASE
order by 2;st112 > st112 > st112 > 2
INSTANCE_NAME STARTUP DATABASE_ROLE OPEN_MODE
-------------- -------------------- ----------------- ---------
st1121 12-SEP-2020 13:39:18 SNAPSHOT STANDBY MOUNTED
st1122 12-SEP-2020 13:39:18 SNAPSHOT STANDBY MOUNTED
st112 >
[oracle@rac2-node1 ~]$ srvctl stop database -d st112
[oracle@rac2-node1 ~]$ srvctl start database -d st112 -o open
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
st112 > set lines 500 pages 150
col HOST_NAME for a35
SELECT INSTANCE_NAME, TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP", DATABASE_ROLE, OPEN_MODE from GV$INSTANCE, V$DATABASE
order by 2;st112 > st112 > 2
INSTANCE_NAME STARTUP DATABASE_ROLE OPEN_MODE
-------------- --------------------- ---------------- -----------
st1121 12-SEP-2020 13:54:21 SNAPSHOT STANDBY READ WRITE
st1122 12-SEP-2020 13:54:23 SNAPSHOT STANDBY READ WRITE
st112 >
Let's compare Incarnation details and see how we are in a different Incarnation
# Primary #
db112 > select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, STATUS from V$DATABASE_INCARNATION;
db112 > select INCARNATION#, RESETLOGS_ID, RESETLOGS_TIME, STATUS from V$DATABASE_INCARNATION;
INCARNATION# RESETLOGS_ID RESETLOGS STATUS
------------ ------------ --------- -------
1 824297850 24-AUG-13 PARENT
2 1022093290 19-OCT-19 CURRENT <<<<<<
db112 >
# Standby #
st112 > select INCARNATION#, RESETLOGS_ID, RESETLOGS_TIME, STATUS from V$DATABASE_INCARNATION;
INCARNATION# RESETLOGS_ID RESETLOGS STATUS
------------ ------------ --------- -------
1 824297850 24-AUG-13 PARENT
2 1022093290 19-OCT-19 PARENT
3 1042027587 02-JUN-20 ORPHAN
4 1042028356 02-JUN-20 ORPHAN
5 1050670277 09-SEP-20 ORPHAN
6 1050672747 09-SEP-20 ORPHAN
7 1050932670 12-SEP-20 CURRENT <<<<<<
7 rows selected.
st112 >
We can now point the applications to our Sanpshot Standby to do any new code tests
st112 > CREATE USER snaptest
IDENTIFIED BY snaptest
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
User created.
st112 > CREATE TABLE snaptest.persons(
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
PRIMARY KEY(first_name)
);
Table created.
st112 >
Once we have our tests done, we can revert the system to Physical Standby and let it get on sync with the Primary Database.
For this, we will need to stop the Database and start one of the instances as mounted
[oracle@rac2-node1 ~]$ srvctl stop database -d st112
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 12 14:14:42 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
st112 > startup mount
ORACLE instance started.
Total System Global Area 2471931904 bytes
Fixed Size 2255752 bytes
Variable Size 704644216 bytes
Database Buffers 1744830464 bytes
Redo Buffers 20201472 bytes
Database mounted.
st112 >
We can now do the system conversion back to Physiscal Standby.
Like we did earlier, is interesting to see the alert log to see how the system is restored using the Guaranteed Restore Point we saw earlier
st112 > alter database convert to physical standby;
Database altered.
st112 >
# Alert Log Outuput #
Sat Sep 12 14:22:59 2020
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (st1121)
Sat Sep 12 14:22:59 2020
Killing 6 processes with pids 27915,27917,27788,27791,27796,27798 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 27527 on instance 1
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Deleted Oracle managed file +RECO/st112/flashback/log_1.416.1050932653
Deleted Oracle managed file +RECO/st112/flashback/log_2.410.1050932659
Guaranteed restore point dropped
Clearing standby activation ID 1039656977 (0x3df7e811)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 188 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;
ARC0: Archival stopped
Sat Sep 12 14:23:09 2020
ARCH shutting down
ARC5: Archival stopped
Completed: alter database convert to physical standby
Sat Sep 12 14:23:11 2020
SUCCESS: diskgroup RECO was dismounted
SUCCESS: diskgroup DATA11 was dismounted
NOTE: Database dismounted; ASMB process exiting
Stopping background process RBAL
Let's restart the system now using clusterware and see how the system gets on sync with primary
[oracle@rac2-node1 ~]$ srvctl stop database -d st112
[oracle@rac2-node1 ~]$ srvctl start database -d st112
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
st112 > set lines 500 pages 150
col HOST_NAME for a35
SELECT INSTANCE_NAME, TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP", DATABASE_ROLE, OPEN_MODE from GV$INSTANCE, V$DATABASE
order by 2;
INSTANCE_NAME STARTUP DATABASE_ROLE OPEN_MODE
---------------- ----------------------------- ---------------- --------------------
st1121 12-SEP-2020 14:26:16 PHYSICAL STANDBY READ ONLY WITH APPLY
st1122 12-SEP-2020 14:26:16 PHYSICAL STANDBY READ ONLY WITH APPLY
st112 >
st112 > select inst_id, process, status MRP_stat, thread#, sequence# from gv$managed_standby where process like 'MRP%';
INST_ID PROCESS MRP_STAT THREAD# SEQUENCE#
---------- --------- ------------ ---------- ----------
1 MRP0 APPLYING_LOG 1 321
We can see how the Standby is back to same Incarnation than the Primary now
# Primary #
db112 > select INCARNATION#, RESETLOGS_ID, RESETLOGS_TIME, STATUS from V$DATABASE_INCARNATION;
INCARNATION# RESETLOGS_ID RESETLOGS STATUS
------------ ------------ --------- -------
1 824297850 24-AUG-13 PARENT
2 1022093290 19-OCT-19 CURRENT <<<<<<
db112 >
# Standby #
st112 > select INCARNATION#, RESETLOGS_ID, RESETLOGS_TIME, STATUS from V$DATABASE_INCARNATION;
INCARNATION# RESETLOGS_ID RESETLOGS STATUS
------------ ------------ --------- -------
1 824297850 24-AUG-13 PARENT
2 1022093290 19-OCT-19 CURRENT <<<<<<
3 1042027587 02-JUN-20 ORPHAN
4 1042028356 02-JUN-20 ORPHAN
5 1050670277 09-SEP-20 ORPHAN
6 1050672747 09-SEP-20 ORPHAN
7 1050932670 12-SEP-20 ORPHAN
8 1050935830 12-SEP-20 ORPHAN
8 rows selected.
st112 >
Data Broker Process
The process is much simpler using Data broker since will take of the the whole process so we just need one command to be executed and the system can be Open Read-Only at the start of the process
[oracle@rac2-node1 ~]$ dgmgrl
DGMGRL> connect sys/Welcome1@st112
Connected.
DGMGRL> show configuration
Configuration - dg_broker_config
Protection Mode: MaxPerformance
Databases:
db112 - Primary database
st112 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database st112
Database - st112
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
st1121 (apply instance)
st1122
Database Status:
SUCCESS
DGMGRL>
DGMGRL> convert database st112 to snapshot standby;
Converting database "st112" to a Snapshot Standby database, please wait...
Database "st112" converted successfully
DGMGRL> show database st112
Database - st112
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 6 minutes 52 seconds (computed 0 seconds ago)
Instance(s):
st1121 (apply instance)
st1122
Database Status:
SUCCESS
DGMGRL> exit
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
st112 > set lines 500 pages 150
col HOST_NAME for a35
SELECT INSTANCE_NAME, TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP", DATABASE_ROLE, OPEN_MODE from GV$INSTANCE, V$DATABASE
order by 2;st112 > st112 > 2
INSTANCE_NAME STARTUP DATABASE_ROLE OPEN_MODE
-------------- --------------------- ---------------- -----------
st1121 12-SEP-2020 14:28:14 SNAPSHOT STANDBY READ WRITE
st1122 12-SEP-2020 14:28:14 SNAPSHOT STANDBY READ WRITE
st112 >
The conversion back is as simple as that
[oracle@rac2-node1 ~]$ dgmgrl
DGMGRL> connect sys/Welcome1@st112
Connected.
DGMGRL> convert database st112 to physical standby;
Converting database "st112" to a Physical Standby database, please wait...
Operation requires shutdown of instance "st1121" on database "st112"
Shutting down instance "st1121"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "st1121" on database "st112"
Starting instance "st1121"...
ORACLE instance started.
Database mounted.
Continuing to convert database "st112" ...
Operation requires shutdown of instance "st1121" on database "st112"
Shutting down instance "st1121"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "st1121" on database "st112"
Starting instance "st1121"...
ORACLE instance started.
Database mounted.
Database "st112" converted successfully
DGMGRL>
INSTANCE_NAME STARTUP DATABASE_ROLE OPEN_MODE
---------------- ----------------------------- ---------------- --------------------
st1121 12-SEP-2020 14:31:22 PHYSICAL STANDBY READ ONLY WITH APPLY
st1122 12-SEP-2020 14:32:00 PHYSICAL STANDBY READ ONLY WITH APPLY
Latest comments (0)