Convert Physical Standby To Snapshot Standby Database
===================================================================
High Level Steps to converting physical standby database to snapshot standby database
===================================================================
- No gap
- enable flashback standby side
- shutdown and start in mount standby database
- convert to snapshot standby
Steps to converting back a snapshot standby database to physical standby database
=====================================================================
- Shut down the snapshot standby database and open it in Mount mode.
- Convert the snapshot standby database to physical standby database
- Bounce the physical standby database and start the Managed Recovery Process (MRP) on it.
Primary database Details
====================================
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
SQL>
SQL>
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN india PRIMARY READ WRITE
SQL>
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 18
SQL>
Standby database Details:
=================================
SQL> select name,db_unique_name ,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
INDIA india_stby PHYSICAL STANDBY READ ONLY WITH APPLY
check gap status ( make sure no gap )
SQL>
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 18 18 0
SQL>
SQL>
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 18
SQL>
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL>
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>
SQL>
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> alter system set db_recovery_file_dest_size=2;
System altered.
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 2
SQL>
SQL> select name from V$RECOVERY_FILE_DEST;
NAME
---------------------------------------------------------------------
SQL> show parameter reco
NAME TYPE VALUE
------------------------------------ ----------- --------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 2
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL>
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;
System altered.
SQL> select name from V$RECOVERY_FILE_DEST;
NAME
-----------------------------------------------------------
/u01/app/oracle/fra
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL>
Cancel recovery process
==============================
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select name,db_unique_name ,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
INDIA india_stby PHYSICAL STANDBY READ ONLY
SQL>
shutdown standby database and startup mount
===============================================
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 8628640 bytes
Variable Size 369100384 bytes
Database Buffers 662700032 bytes
Redo Buffers 8146944 bytes
Database mounted.
SQL>
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on ;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
SQL>
* we have to increase recovery size
SQL> alter system set db_recovery_file_dest_size=3g;
System altered.
SQL> alter database flashback on ;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Now convert it to snapshot standby
=========================================
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database convert to snapshot standby ;
Database altered.
SQL>
SQL> alter database open;
Database altered.
SQL> select name,db_unique_name ,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
INDIA india_stby SNAPSHOT STANDBY READ WRITE
SQL>
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
NAME GUA
-------------------------------------------------------------------------------------------------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_05/08/2021 11:17:56 YES
SQL>
for Testing create table in this snapshot standby
===================================================
SQL> create table hr.test_snap (id number(5),name varchar2(50));
Table created.
SQL>
SQL> insert into hr.test_snap values (1,'ANURAG');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.test_snap;
ID NAME
---------- --------------------------------------------------
1 ANURAG
SQL>
SQL>
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL>
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL> select name,db_unique_name ,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
INDIA india_stby SNAPSHOT STANDBY READ WRITE
on primary
=================
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL>
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 21
SQL>
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN india PRIMARY READ WRITE
SQL>
Convert it again to physical standby again.
===================================================
Shut down the snapshot standby database and open it in Mount mode.
==================================================================
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 8628640 bytes
Variable Size 369100384 bytes
Database Buffers 662700032 bytes
Redo Buffers 8146944 bytes
Database mounted.
SQL> select name,db_unique_name ,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
INDIA india_stby SNAPSHOT STANDBY MOUNTED
SQL>
Convert the snapshot standby database to physical standby database.
===================================================================
SQL>
SQL>
SQL> alter database convert to physical standby;
Database altered.
SQL> select name,db_unique_name ,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
INDIA india_stby PHYSICAL STANDBY MOUNTED
Bounce the standby database
===========================
SQL> alter database open;
Database altered.
SQL> select name,db_unique_name ,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
INDIA india_stby PHYSICAL STANDBY READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CLOSING 24
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 25
RFS IDLE 0
MRP0 APPLYING_LOG 25
10 rows selected.
on primary
============
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 27
SQL>
on standby
===============
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 26
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CLOSING 27
ARCH CLOSING 25
ARCH CONNECTED 0
RFS IDLE 28
RFS IDLE 0
MRP0 APPLYING_LOG 28
10 rows selected.
SQL>
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 27 27 0
SQL>
Table does not exists which we created during database was snapshot standby
=================================================================================
SQL>
SQL>
SQL> select * from hr.test_snap;
select * from hr.test_snap
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
