Hello Friend's,
In this post we will discuss , how to convert physical standby database to snapshot standby.
what is snapshot standby :
A snapshot standby database receives and archives redo data from a primary database,
but are not applied. Redo data received from the primary database is applied
when a snapshot standby database is converted back into a physical standby database,
after discarding all local updates to the snapshot standby database
[oracle@dm01dr01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 6 09:43:36 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- --------------------
FINDB dm01dr01.database.com PHYSICAL STANDBY READ ONLY ARCHIVELOG 12.1.0.1.0 ALLOWED 06-JUL-2020 09:03:25
WITH APPLY
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 78 78 0
2 69 69 0
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
WITH APPLY
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database convert to snapshot standby;
Database altered.
Alert Log file for snapshot standby conversion :
Mon Jul 06 10:03:36 2020
alter database convert to snapshot standby
ORA-38784 signalled during: alter database convert to snapshot standby...
alter database recover managed standby database cancel
Mon Jul 06 10:03:55 2020
MRP0: Background Media Recovery cancelled with status 16037
Mon Jul 06 10:03:56 2020
Errors in file /oracle/app/orawork/diag/rdbms/findr/Findr/trace/Findr_mrp0_6703. trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Mon Jul 06 10:03:56 2020
MRP0: Background Media Recovery process shutdown (Findr)
Mon Jul 06 10:03:56 2020
Managed Standby Recovery Canceled (Findr)
Completed: alter database recover managed standby database cancel
alter database convert to snapshot standby
Starting background process RVWR
Mon Jul 06 10:04:15 2020
RVWR started with pid=42, OS id=8421
Mon Jul 06 10:04:16 2020
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_07/06/2020 10:04:15
Killing 7 processes (PIDS:4356,4364,4382,4380,5151,5148,5155) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 6965
Stopping Emon pool
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
Mon Jul 06 10:04:19 2020
SMON: disabling cache recovery
Mon Jul 06 10:04:19 2020
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 2404782
Mon Jul 06 10:04:19 2020
Waiting for all non-current ORLs to be archived...
Mon Jul 06 10:04:19 2020
All non-current ORLs have been archived.
Resetting resetlogs activation ID 764874592 (0x2d970f60)
Online log +DATA/FINDR/ONLINELOG/group_1.269.1041899703: Thread 1 Group 1 was pr eviously cleared
Online log +FRA/FINDR/ONLINELOG/group_1.258.1041899709: Thread 1 Group 1 was pre viously cleared
Online log +DATA/FINDR/ONLINELOG/group_2.270.1041899715: Thread 1 Group 2 was pr eviously cleared
Online log +FRA/FINDR/ONLINELOG/group_2.259.1041899721: Thread 1 Group 2 was pre viously cleared
Online log +DATA/FINDR/ONLINELOG/group_3.271.1041899727: Thread 2 Group 3 was pr eviously cleared
Online log +FRA/FINDR/ONLINELOG/group_3.260.1041899733: Thread 2 Group 3 was pre viously cleared
Online log +DATA/FINDR/ONLINELOG/group_4.272.1041899739: Thread 2 Group 4 was pr eviously cleared
Online log +FRA/FINDR/ONLINELOG/group_4.261.1041899743: Thread 2 Group 4 was pre viously cleared
Standby became primary SCN: 2404780
Mon Jul 06 10:04:20 2020
Setting recovery target incarnation to 3
Mon Jul 06 10:04:20 2020
AUDIT_TRAIL initialization parameter is changed back to its original value as sp ecified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
We do need to do any thing in this ,it automatically starts the process RVWR creates a garunteed restore point.
SQL> select SCN,GUARANTEE_FLASHBACK_DATABASE,TIME,PRESERVED,NAME from gv$restore_point;
SCN GUA TIME PRE NAME
---------- --- -------------------------------------------------- --- ----------------------------------------------------------------------
2404781 YES 06-JUL-20 10.04.15.000000000 AM YES SNAPSHOT_STANDBY_REQUIRED_07/06/2020 10:04:15
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- --------------------
1 Findr FINDB dm01dr01.database.com SNAPSHOT STANDBY ARCHIVELOG MOUNTED 12.1.0.1.0 ALLOWED 06-JUL-2020 09:03:25
SQL> alter database open;
Database altered.
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- --------------------
1 Findr FINDB dm01dr01.database.com SNAPSHOT STANDBY ARCHIVELOG READ WRITE 12.1.0.1.0 ALLOWED 06-JUL-2020 09:03:25
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FINPDB MOUNTED
SQL>
SQL>
SQL> alter pluggable database FINPDB open read write;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FINPDB READ WRITE NO
------------------Database Converted to Snapshot standby----------------------------
To convert Database back to Physical standby stop database and start in mount mode
SQL>
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 939526232 bytes
Database Buffers 184549376 bytes
Redo Buffers 9383936 bytes
Database mounted.
SQL>
SQL> alter database convert to physical standby;
Database altered.
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SQL> SQL> SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- --------------------
1 Findr FINDB dm01dr01.database.com PHYSICAL STANDBY ARCHIVELOG MOUNTED 12.1.0.1.0 ALLOWED 06-JUL-2020 15:50:10
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 81
RFS IDLE 72
RFS IDLE 81
9 rows selected.
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 80 80 0
2 71 71 0
Alertlog output of convert back :
Mon Jul 06 15:51:05 2020
Archived Log entry 88 added for thread 2 sequence 71 rlc 1038428388 ID 0x2d970f60 dest 2:
Mon Jul 06 15:51:13 2020
alter database convert to physical standby
Mon Jul 06 15:51:13 2020
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (Findr)
Mon Jul 06 15:51:13 2020
Killing 4 processes (PIDS:31349,31352,31360,31372) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 31345
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file +FRA/FINDR/FLASHBACK/log_1.347.1045044255
Deleted Oracle managed file +FRA/FINDR/FLASHBACK/log_2.348.1045044257
Guaranteed restore point dropped
Clearing standby activation ID 771449189 (0x2dfb6165)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 188 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;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 9 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 10 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 11 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby
Mon Jul 06 15:51:37 2020
ARC2: Becoming the active heartbeat ARCH
Hope This Helps, See you back in next post
Regards
Sultan Khan
2 comments
Click here for commentsSimple and good steps
ReplyGood work in updating the knowledge base
ReplyConversionConversion EmoticonEmoticon