Oracle 19c-Convert Physical Standby To Snapshot Standby ~ Oracle DBA Secrets

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database.To Configure snapshot standby, first, we need to create a physical standby and then we will convert from physical standby to Snapshot standby.

Developers want to testing on Fresh Live Data, but DBA can't allow them to test on Primary, then how to archive developer requirements.. DBA can convert physical standby to snapshot standby in R/W mode.

Hence Developers can make their changes in Snapshot databases. Whatever changes are done on snapshot standby will be flushed out once converted back to the physical standby database from snapshot standby.

Primary database changes will not applied to the snapshot standby database why because there is no MRP process running the snapshot database.

No need to enable flashback database.

Only need to have db_recovery_file_dest and db_recovery_file_dest_size on physical standby.

1. Check Database Role and Verify Archive log GAP

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
-------------------- ----------------------------- ----------------------------------- -----------
ITMSPRD PHYSICAL STANDBY READ ONLY WITH APPLY ARCHIVELOG

SQL>select INST_ID,PROCESS,STATUS,THREAD#,sequence#,BLOCK# from gv$managed_standby;
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK#
---------- --------- ------------ ---------- ---------- ----------
1 ARCH CLOSING 1 665547 761856
1 DGRD ALLOCATED 0 0 0
1 DGRD ALLOCATED 0 0 0
1 ARCH CLOSING 1 665544 737280
1 ARCH CLOSING 1 665548 735232
1 ARCH CLOSING 1 665549 780288
1 ARCH CLOSING 1 665542 745472
1 ARCH CLOSING 1 665546 808960
1 ARCH CLOSING 1 665543 747520
1 RFS IDLE 1 0 0
1 RFS RECEIVING 1 665897 77825
1 RFS RECEIVING 1 665895 346113
1 RFS RECEIVING 1 665896 126977
1 RFS RECEIVING 1 665892 778241
1 RFS RECEIVING 1 665893 473089
1 RFS RECEIVING 1 665894 393217
1 MRP0 WAIT_FOR_LOG 1 665892 0
17 rows selected.

SQL>select distinct(THREAD#),max(sequence#) from v$archived_log group by THREAD# order by 1;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 665891

SQL>select distinct(THREAD#),max(sequence#) from v$archived_log where APPLIED='YES' group by THREAD# order by 1;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 665889

SQL> select 'Last Log applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union select 'Last Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);
LOGS TIME
-------------------- ------------------
Last Log applied : 11-JAN-24:11:40:54
Last Log received : 11-JAN-24:11:41:21

2. Verify Flash Recovery Area and Flashback database

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> show parameter db_recovery_file_dest ;
NAME TYPE VALUE
------------------------------------ ------------ -------------------------------------------
db_recovery_file_dest string /oracle/ora19c/19cbase/fast_recovery_area/
db_recovery_file_dest_size big integer 15G

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /fodb_arch1/ITMSPRD/arch
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0

SQL> Show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
-------------------- ----------------------------- ----------------------------------- -----------
ITMSPRD PHYSICAL STANDBY READ ONLY WITH APPLY ARCHIVELOG

3. Cancel MRP on Standby

SQL> alter database recover managed standby database cancel;

Database altered.

4. Covert to Snapshot Standby from Physical Standby

SQL> alter database convert to snapshot standby;
Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
-------------------- ----------------------------- ----------------------- ---------------------
ITMSPRD SNAPSHOT STANDBY MOUNTED ARCHIVELOG

SQL> alter database open;
Database altered.

5. Testing

6. Covert Back to Physical Standby from Snapshot Standby

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 6.8719E+10 bytes

Fixed Size 37257296 bytes

Variable Size 1.4764E+10 bytes

Database Buffers 5.3687E+10 bytes

Redo Buffers 231174144 bytes

Database mounted.

SQL>

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME DATABASE_ROLE OPEN_MODE LOG_MODE
-------------------- ----------------------------- ----------------------- ---------------------
ITMSPRD SNAPSHOT STANDBY MOUNTED ARCHIVELOG

SQL> alter database convert to physical standby;

Database altered.

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 6.8719E+10 bytes

Fixed Size 37257296 bytes

Variable Size 1.4764E+10 bytes

Database Buffers 5.3687E+10 bytes

Redo Buffers 231174144 bytes

Database mounted.

Database opened.

SQL>

7. Start MRP on Standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Oracle DBA Secrets

Thursday, August 08, 2024