Saturday, May 8, 2021

Convert Physical Standby To Snapshot Standby Database

 

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>


By -
Labels: ,

No comments:

Post a Comment

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Subscribe to: Post Comments (Atom)

Oracle Database Deep Health Check Checklist

Oracle Database Deep Health Check Checklist Recommended assessment levels Level Purpose Typical scope L1 Operational health Availability, sp...