OracleDBWorld

How to convert Physical Standby database to Snapshot Standby

Posted on by N Namrata

From oracle 11g we have one more feature enabled to help ourselves. “Snapshot Standby”.
This way you can open a physical database in read write mode and revert back to physical standby.
So what Option was available for 10g database ? We will discuss it in another blog.
Lets start with how to convert Physical Standby database to Snapshot Standby and Revert.

Did you know, “Even if database is opened in Snapshot standby, log shipment / RFS will be running. Only MRP will be stopped.”

Environment Details –>

Oracle Version – > Oracle 11.2.0.4
OS – > RHEL 6
PSU – > OCT 2019

How to Convert Physical standby database to Snapshot Standby

How to Convert Physical standby database to Snapshot Standby || www.oracledbworld.com

Pre-checks

1 – > Database version is 11g or higher
2 – > FRA is enabled.

How to Enable FRA

Set following parameters –
1) db_recovery_file_dest_size
2) db_recovery_file_dest

Eg –

NAME				                 VALUE
------------------------------------ ------------------------------------------------------------
db_recovery_file_dest		         /home/oracledbworld/app/oracledbworld/fast_recovery_area
db_recovery_file_dest_size	         4182M

Step by Step conversion of physical standby to snapshot standby.

  1. Lets check the sync status of the database

Primary Side- 

08:55:13 SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
ORCL	  READ WRITE           PRIMARY

Elapsed: 00:00:00.02
08:55:29 SQL> 
08:55:30 SQL> select protection_level,protection_mode from v$database;

PROTECTION_LEVEL     PROTECTION_MODE
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

Elapsed: 00:00:00.00
08:55:56 SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	   113

Standby Side-

08:54:13 SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	           DATABASE_ROLE
--------- -------------------- ----------------
ORCL	  READ ONLY WITH APPLY PHYSICAL STANDBY

Elapsed: 00:00:00.02
08:54:29 SQL> 
08:54:30 SQL> select protection_level,protection_mode from v$database;

PROTECTION_LEVEL     PROTECTION_MODE
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

Elapsed: 00:00:00.00
08:54:56 SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
	   113

2. -> Check FRA is enabled 

08:56:57 SQL> show parameter recover

NAME				                 VALUE
------------------------------------ ------------------------------------------------------------
db_recovery_file_dest		         /home/oracledbworld/app/oracledbworld/fast_recovery_area
db_recovery_file_dest_size	         4182M

3. -> In my case database is Read only with apply, I have to bring it in Mount stage.

Note -> My database was a single instance database, If I had RAC database, then we have to bring down both the instances. And start only one instance in mount stage and proceed further. ( This is the only change in RAC database. Rest all steps are same. )

08:57:30 SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME	  OPEN_MODE	           DATABASE_ROLE	FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL	  READ ONLY WITH APPLY PHYSICAL STANDBY NO

Elapsed: 00:00:00.01
08:58:16 SQL> alter database recover managed standby database cancel;

Database altered.

Elapsed: 00:00:01.01
08:58:34 SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
08:58:46 SQL> startup mount;
ORACLE instance started.

Total System Global Area  430075904 bytes
Fixed Size		    2253944 bytes
Variable Size		  331352968 bytes
Database Buffers	   88080384 bytes
Redo Buffers		    8388608 bytes
Database mounted.
08:58:57 SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE	FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL	  MOUNTED	       PHYSICAL STANDBY NO

Elapsed: 00:00:00.01

Here if you see, I didn’t start the recovery since I have to convert the database in snapshot standby.

4. – > Before you open the database, it’s always a best practice to check following points –> 

08:59:02 SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oracle/app/orcldr/redo03.log
/oracle/app/orcldr/redo02.log
/oracle/app/orcldr/redo01.log
/oracle/app/orcldr/standbyredo01.log
/oracle/app/orcldr/standbyredo02.log
/oracle/app/orcldr/standbyredo03.log


6 rows selected.

Elapsed: 00:00:00.01
08:59:10 SQL> select dest_id,db_unique_name,destination,error from v$archive_dest;

   DEST_ID DB_UNIQUE_NAME		  DESTINATION				   ERROR
---------- ------------------------------ ---------------------------------------- -----------------------------------------------------------------
	 1 NONE 			  USE_DB_RECOVERY_FILE_DEST
	 2 NONE
	 3 NONE
	 4 NONE
	 5 NONE
	 6 NONE
	 7 NONE
	 8 NONE
	 9 NONE
	10 NONE
	11 NONE
	12 NONE
	13 NONE
	14 NONE
	15 NONE
	16 NONE
	17 NONE
	18 NONE
	19 NONE
	20 NONE
	21 NONE
	22 NONE
	23 NONE
	24 NONE
	25 NONE
	26 NONE
	27 NONE
	28 NONE
	29 NONE
	30 NONE
	31 NONE
	32 NONE 			  USE_DB_RECOVERY_FILE_DEST

32 rows selected.

Let me tell you the reason behind it. When you try to open your DR database, redo logs are cleared and will be used. Hence when you have a path in redo logs which doesn’t exists then it will give you error while converting the database in snapshot. So it’s a best practice for you to do it.

Why we are checking the destination –> Case study is coming in your way soon Here –>

Here we have no destination and all redologs path exists for me. I am moving to next step-

5 – > Convert the database in Snapshot Standby -> .

09:00:18 SQL> alter database convert to snapshot standby;

Database altered.

Elapsed: 00:00:00.52
09:01:38 SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE	FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL	  MOUNTED	       SNAPSHOT STANDBY RESTORE POINT ONLY

Elapsed: 00:00:00.00

09:04:13 SQL>

6 – > Check alert log of your database 

Completed: ALTER DATABASE   MOUNT
Fri Oct 09 08:59:58 2020
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Fri Oct 09 09:01:37 2020
alter database convert to snapshot standby
Starting background process RVWR
Fri Oct 09 09:01:38 2020
RVWR started with pid=24, OS id=29836 
Allocated 3981120 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/09/2020 09:01:37
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1018048
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1580248772 (0x5e30aec4)
Online log /oracle/app/orcldr/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oracle/app/orcldr/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oracle/app/orcldr/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1018046
Fri Oct 09 09:01:38 2020
Setting recovery target incarnation to 3
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

7 – > Now I usually prefer to shutdown the database. It better to bounce it after you see Completed : alter database convert to snapshot standby

If you are using RAC database, then you can stop the database and start the database with both the instances. And release.

09:04:13 SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
09:04:34 SQL> startup
ORACLE instance started.

Total System Global Area  430075904 bytes
Fixed Size		    2253944 bytes
Variable Size		  331352968 bytes
Database Buffers	   88080384 bytes
Redo Buffers		    8388608 bytes
Database mounted.
Database opened.
09:04:47 SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE	FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL	  READ WRITE	       SNAPSHOT STANDBY RESTORE POINT ONLY

Elapsed: 00:00:00.02
09:04:55 SQL>

Yes it is restore point only, Snapshot standby database creates restore point using which it roll it back. 

09:06:14 SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME				                  RESTORE_ PRE NAME
---------- --------------------- --- ------------ ----------------------------------- -------- --- -------------------------------------------------
   1018047		               2 YES	 52428800 09-OCT-20 09.01.37.000000000 AM	       YES SNAPSHOT_STANDBY_REQUIRED_10/09/2020 09:01:37

Elapsed: 00:00:00.01

You are done. Release the system to Application team to Test the Environment.

Points to Remember

1 You have enough space to hold the archives and flashback logs ( It’s always better to understand the application team requirement and statement they are planning to execute. )
2 Before conversion there shouldn’t any destination on the database which you are converting to snapshot standby.

Step by Step How to Convert Physical standby database to Snapshot Standby (RAC)

If you have a RAC standby database and you want to convert it to snapshot standby database then,

  1. Check the sync status and FRA is enabled.
select name,open_mode,database_role,flashback_on from gv$database;
show parameter db_recover

---Production
select thread#,max(sequence#) from v$archived_log order by thread#;

---Standby
select thread#,max(sequence#) from v$log_history order by thread#;

2. Stop the recovery and Shutdown the database

alter database recover managed standby database cancel;
srvctl status database -d <db_unique_name> -v
srvctl stop database -d <db_unique_name>

3. Start only on instance in mount stage

srvctl start instance -d <db_unique_name> -i <instance_name_for_node_1> -o mount

4. Convert the database to snapshot standby

select name,open_mode,database_role,flashback_on from v$database;
alter database convert to snapshot standby database;
select name,open_mode,database_role,flashback_on from v$database;

5. Shutdown the database

srvctl stop database -d <db_unique_name>

6. Startup both the instance in read write

srvctl start database -d <db_unique_name>

How to Convert Snapshot standby database to Physical Standby

How to Convert Snapshot standby database to Physical Standby || https://www.oracledbworld.com/

Here once we have confirmation from application team, we will put it back in recovery mode.

1 – > We will check the status of the database

09:04:47 SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE	FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL	  READ WRITE	       SNAPSHOT STANDBY RESTORE POINT ONLY

Elapsed: 00:00:00.02

2 – > Shutdown the database, startup the instance with mount stage.

if you have RAC database, then bring down all the instances. here and Bring up only one instance in mount stage.

09:04:55 SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
09:05:46 SQL> 
09:06:00 SQL> startup mount;
ORACLE instance started.

Total System Global Area  430075904 bytes
Fixed Size		    2253944 bytes
Variable Size		  331352968 bytes
Database Buffers	   88080384 bytes
Redo Buffers		    8388608 bytes
Database mounted.
09:06:09 SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE	FLASHBACK_ON
--------- -------------------- ---------------- ------------------
ORCL	  MOUNTED	       SNAPSHOT STANDBY RESTORE POINT ONLY

Elapsed: 00:00:00.01
09:06:14 SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME				                  RESTORE_ PRE NAME
---------- --------------------- --- ------------ ----------------------------------- -------- --- -------------------------------------------------
   1018047		               2 YES	 52428800 09-OCT-20 09.01.37.000000000 AM	       YES SNAPSHOT_STANDBY_REQUIRED_10/09/2020 09:01:37

Elapsed: 00:00:00.01
09:06:52 SQL>

3 – > Now we will convert the database to physical standby again

09:11:21 SQL> alter database convert to physical standby;

Database altered.

Elapsed: 00:00:04.31

09:15:39 SQL>

4 – >Lets check the alert log –> 

alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (orcldr)
Killing 1 processes with pids 30021 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 30019
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point 
Stopping background process RVWR
Deleted Oracle managed file /home/oracledbworld/app/oracledbworld/fast_recovery_area/ORCLDR/flashback/o1_mf_hr12c21n_.flb
Deleted Oracle managed file /home/oracledbworld/app/oracledbworld/fast_recovery_area/ORCLDR/flashback/o1_mf_hr12c4g8_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 1581118118 (0x5e3df2a6)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 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;
Shutting down archive processes
Archiving is disabled
Fri Oct 09 09:13:53 2020
Fri Oct 09 09:13:53 2020
ARCH shutting downARCH shutting down

ARC3: Archival stopped
ARC1: Archival stopped
Fri Oct 09 09:13:53 2020
ARCH shutting down
ARC2: Archival stopped
Fri Oct 09 09:13:53 2020
ARCH shutting down
ARC0: Archival stopped
Completed: alter database convert to physical standby

Note -> For 11g your instance will dismount when alter database convert to physical standby is completed. But after 12c, your instance will be mounted after alter database convert to physical standby is completed.

5 – > Once you see “Completed: alter database convert to physical standby” in alert log we will shutdown the database startup ( in mount / read only depending upon your old status)

09:15:39 SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
09:15:46 SQL> startup 
ORACLE instance started.

Total System Global Area  430075904 bytes
Fixed Size		    2253944 bytes
Variable Size		  331352968 bytes
Database Buffers	   88080384 bytes
Redo Buffers		    8388608 bytes
Database mounted.
Database opened.
09:15:57 SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME						   OPEN_MODE	       DATABASE_ROLE		FLASHBACK_ON
------------------------------ ------------------- -------------------- ----------------
ORCL						   READ ONLY	       PHYSICAL STANDBY 	NO

Elapsed: 00:00:00.01
09:16:01 SQL> select * from v$restore_point;

no rows selected

Elapsed: 00:00:00.01
09:16:15 SQL>

6 – > Start the recover And check the sync status

09:16:17 SQL> alter database recover managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:06.05
09:17:32 SQL>

7 – > Our alert log looks like this –>

Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /oracle/app/orcldr/redo01.log
Clearing online log 1 of thread 1 sequence number 1
Completed: alter database recover managed standby database disconnect from session
Clearing online redo logfile 1 complete
Media Recovery Log /home/oracledbworld/app/oracledbworld/fast_recovery_area/ORCLDR/archivelog/2020_10_09/o1_mf_1_114_hr12jtsc_.arc
Media Recovery Log /home/oracledbworld/app/oracledbworld/fast_recovery_area/ORCLDR/archivelog/2020_10_09/o1_mf_1_115_hr12jtso_.arc
Media Recovery Waiting for thread 1 sequence 116

We completed our activity.

How to Convert Snapshot standby database to Physical Standby (RAC)

If you have a RAC standby database and you want to convert it back to physical standby database then,

1. Shutdown the standby database which was opened in snapshot standby database

srvctl stop database -d <db_unique_name>

2. Start only on instance in mount stage

srvctl start instance -d <db_unique_name> -i <instance_name_for_node_1> -o mount

3. Convert the database to snapshot standby

select name,open_mode,database_role,flashback_on from v$database;
alter database convert to physical standby;
select name,open_mode,database_role,flashback_on from v$database;

4. Shutdown the database


srvctl status database -d <db_unique_name> -v
srvctl stop database -d <db_unique_name>

5. Startup both the instance in read only

srvctl start database -d <db_unique_name> -o "read only"

6. Start the recovery and Check the sync status 

alter database recover managed standby database disconnect from session;

Reference – Snapshot Standby and Real Application Testing(RAT) (Doc ID 1662664.1)

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *