Converting a Physical Standby to Snapshot Standby
Tags: tutorial, oracle, standby, database
Published: 2020-09-12 14:51:20

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](#Manual)
  * [Data Broker Process](#Broker)



### Manual Process <a name="Manual"></a>



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:
```bash
[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:
```bash
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
```bash
# 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 :) )

```bash
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
```bash
# 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

```bash
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
```bash
[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

```bash
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
```bash
[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
```bash
# 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 <a name="Broker"></a>



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
```bash
[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
```bash
[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


```