Convert Physical Standby To Snapshot Standby Oracle 19c Database
In this article we will be converting Pysical Standby database to Snapshot standby database.
Assumption
=> We assume that Primary and Standby database is configured. For Step-by-Step setup Click . Setting DC-DR on 19C
=> Currently Standby database is running in Maximum Performance mode with Read-Only Apply services.
=> Snapshot database is updateable and uses flashback techniques.
=> Once converted back to Physical Standby all cahnages done during Snapshot mode are lost.
=> Below setup is being used for this article.
Primary & Standby Site Steps
=> Check Primary database details
SQL> select name,open_mode,database_role,protection_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET READ WRITE PRIMARY MAXIMUM PERFORMANCE
DBSET READ WRITE PRIMARY MAXIMUM PERFORMANCE
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 29
2 13
=> Check Standby database details
SQL> select name,open_mode,database_role,protection_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 29
2 13
=> Check Flashback status on Standby database
SQL> select flashback_on from gv$database;
FLASHBACK_ON
------------------
NO
NO
let we first cancle MRP and then enable Flashback on Standby database.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select name,open_mode,database_role,protection_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 8256M
Let we enable Flashback now.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from gv$database;
FLASHBACK_ON
------------------
YES
YES
=> Stop databse on Standby site and start in Mount mode as below.
[oracle@racsetn1 ~]$ srvctl stop database -d DBTAR
[oracle@racsetn1 ~]$ srvctl start database -db DBTAR -startoption mount
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> select name,open_mode,database_role,protection_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 34
2 16
=> Convert Standby Database to Snapshot mode and Open database.
SQL> alter database convert to snapshot standby;
Database altered.
SQL> select name,open_mode,database_role,protection_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET MOUNTED SNAPSHOT STANDBY MAXIMUM PERFORMANCE
DBSET MOUNTED SNAPSHOT STANDBY MAXIMUM PERFORMANCE
SQL> alter database open;
Database altered.
SQL> select name,open_mode,database_role,protection_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE
DBSET MOUNTED SNAPSHOT STANDBY MAXIMUM PERFORMANCE
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@racsetn1 ~]$
[oracle@racsetn1 ~]$ ssh racsetn2
Last login: Sun Jun 26 01:50:29 2022
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
SQL> alter database open;
Database altered.
[oracle@racsetn2 ~]$
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> select name,open_mode,database_role,protection_mode from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE
DBSET READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE
=> Create sample records on Standby Site.
SQL> create table snap_db (id int);
Table created.
SQL> insert into snap_db values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from snap_db;
ID
----------
1
=> Check ControlFile status on Snapshot database .
SQL> select name,open_mode,database_role,protection_mode,CONTROLFILE_TYPE from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
--------- -------------------- ---------------- -------------------- -------
DBSET READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE CURRENT
DBSET READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE CURRENT
=> Check Flashback logs on standby site,
[grid@racsetn1 ~]$ asmcmd
ASMCMD> cd FRA
ASMCMD> cd DBTAR
ASMCMD> cd FLASHBACK
ASMCMD> ls
log_1.304.1108417131
log_2.305.1108417133
log_3.306.1108417137
log_4.307.1108417139
ASMCMD> ls -lrt
Type Redund Striped Time Sys Name
FLASHBACK UNPROT COARSE JUN 26 21:00:00 Y log_1.304.1108417131
FLASHBACK UNPROT COARSE JUN 26 21:00:00 Y log_2.305.1108417133
FLASHBACK UNPROT COARSE JUN 26 21:00:00 Y log_3.306.1108417137
FLASHBACK UNPROT COARSE JUN 26 21:00:00 Y log_4.307.1108417139
=> Check Process status on Standby Site.
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 CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
DGRD ALLOCATED 0
RFS IDLE 0
RFS IDLE 0
13 rows selected.
=> Now we will be Discarding Snapshot mode and converting Standby DB to Physical Standby as below.
Stop datababase and Start only on Node-1 as below
[oracle@racsetn1 ~]$ srvctl stop database -d DBTAR
[oracle@racsetn1 ~]$ srvctl start instance -d DBTAR -i DBTAR1 -o mount
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> select name,open_mode,database_role,protection_mode,CONTROLFILE_TYPE from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
--------- -------------------- ---------------- -------------------- -------
DBSET MOUNTED SNAPSHOT STANDBY MAXIMUM PERFORMANCE CURRENT
=> Conver to Physical Standby
SQL> alter database convert to physical standby;
Database altered.
SQL> select name,open_mode,database_role,protection_mode,CONTROLFILE_TYPE from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
--------- -------------------- ---------------- -------------------- -------
DBSET MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY
=> Recycle Complete database and start in READONLY MODE as below.
[oracle@racsetn1 ~]$ srvctl stop database -d DBTAR
[oracle@racsetn1 ~]$ srvctl start database -db DBTAR -startoption "READ ONLY"
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> select name,open_mode,database_role,protection_mode,CONTROLFILE_TYPE from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
--------- -------------------- ---------------- -------------------- -------
DBSET READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY
DBSET READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY
=> Start MRP process on Standby Site and check status.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select name,open_mode,database_role,protection_mode,CONTROLFILE_TYPE from gv$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
--------- -------------------- ---------------- -------------------- -------
DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY
DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY
=> Check if above create table exists now.
SQL> select * from snap_db;
select * from snap_db
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
=> Check Sync Status as below.
on Primary Site.
SQL> select thread#,max(sequence#) from gv$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 37
2 18
SQL> select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 37
2 18
on Standby Site.
SQL> select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 37
2 18
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; 2 3 4
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 37 37 0
2 18 18 0
SQL>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444