Hello Everyone, today I’m going to write a post on how to convert a 12c standalone standby database to SNAPSHOT STANDBY database (which is Read/Write Mode)
Before Converting lets check the current status of the standby database
STANDBY database:
orastandby:myprod:/export/
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 15:20:02 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> Show parameter db_recovery_file_dest
NAME
------------------------------
db_recovery_file_dest
db_recovery_file_dest_size
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_
NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;
select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
MYPROD READ ONLY WITH APPLY NONE PHYSICAL STANDBY
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 72036
SQL> set line 280
SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS FROM V$MANAGED_STANDBY where process like '%MRP%') process,(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;
Database MRP process Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYPROD WAIT_FOR_LOG 1 72036 72036 0
SQL> alter system set db_recovery_file_dest_size=30G
System altered.
SQL> Show parameter db_recovery_file_dest
NAME
------------------------------
db_recovery_file_dest
db_recovery_file_dest_size
Check Filesystem space………
SQL> !df -h /pkg/myprod/u200
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg01130-lvol11
473G 204G 245G 46% /pkg/myprod/u200
SQL> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
STOP MRP:
SQL> set time on
15:27:39 SQL> alter database recover managed standby database cancel;
Database altered.
Check any Restore points
15:29:52 SQL> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME
---------- --------------------- --- ------------ ------------------------- ------------------------------
2.4753E+11 1 NO 0 23-AUG-14 01.52.56.000000000 AM
15:32:26 SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
15:32:42 SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
15:33:15 SQL> Startup
ORACLE instance started.
Total System Global Area 5368709120 bytes
Fixed Size 3722144 bytes
Variable Size 2919236704 bytes
Database Buffers 2147483648 bytes
Redo Buffers 298266624 bytes
Database mounted.
Database opened.
15:33:26 SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
---------------------- -------------------- ------- ----------------
MYPROD READ WRITE NONE SNAPSHOT STANDBY
15:33:34 SQL> col TIME for a25
15:33:47 SQL> col NAME for a22
15:33:49 SQL> set line 280
15:33:53 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;
NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
---------------------- ---------- ------------------------- --------------------- --- ------------
SNAPSHOT_STANDBY_REQUI 3.1991E+11 13-JAN-17 03.32.39.000000
RED_01/13/2017 15:32:39 000 PM
By DEFAULT Oracle will create a Guarantee Restore point and it uses this during reverting back to standby.
15:34:12 SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/
MAXGB USEDGB
---------- ----------
30 5
Now my Standby database is now in Read/Write mode
REVERT BACK TO PHYSICAL STANDBY:
15:49:53 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
15:50:28 SQL> startup mount
ORACLE instance started.
Total System Global Area 5368709120 bytes
Fixed Size 3722144 bytes
Variable Size 2919236704 bytes
Database Buffers 2147483648 bytes
Redo Buffers 298266624 bytes
Database mounted.
15:50:59 SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; (watch the alert log)
Database altered.
15:51:16 SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
15:51:57 SQL> startup nomount
ORACLE instance started.
Total System Global Area 5368709120 bytes
Fixed Size 3722144 bytes
Variable Size 2919236704 bytes
Database Buffers 2147483648 bytes
Redo Buffers 298266624 bytes
15:52:24 SQL> alter database mount standby database;
Database altered.
Check wallet and OPEN if it is closed:
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
CON_ID
----------
FILE
/opt/oracle/admin/myprod/
OPEN UNKNOWN SINGLE NO
0
SQL> alter database recover managed standby database disconnect from session;
NOTE: If you use just “alter database recover managed standby database” from sql then recovery starts and the sql session doesn’t release until all recovery is completed so always use “disconnect from session” and it will run in the back ground
If active standby:
SQL> alter database recover managed standby database using current logfile disconnect ; (starts mrp )
orastandby:myprod:/opt/oracle/
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 15:54:44 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> col TIME for a25
SQL> col name for a44
SQL> set line 280
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;
No rows selected.
Restore point has been dropped.
SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/
MAXGB USEDGB
---------- ----------
30 3.375
SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS FROM V$MANAGED_STANDBY where process like '%MRP%') process,(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;
Database MRP process Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYPROD WAIT_FOR_LOG 1 1 1 0
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME
------------------------------
MYPROD
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 72036
After few log switches at primary then I see everything is normal
SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS FROM V$MANAGED_STANDBY where process like '%MRP%') process,(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;
Database MRP process Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYPROD WAIT_FOR_LOG 1 72041 72041 0
Now "MYPROD" database is back to standby mode and working fine, Thanks
