Saturday, March 31, 2018

Convert 12c STANDBY database to SNAPSHOT Standby


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/myprod $ sqlplus / as sysdba

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                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /pkg/myprod/u200
db_recovery_file_dest_size           big integer 5G

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim",
NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;

select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;


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  scope=both;

System altered.

SQL> Show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /pkg/myprod/u200
db_recovery_file_dest_size           big integer 30G

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                                                          PRE NAME                       CON_ID
---------- --------------------- --- ------------ ------------------------- --------------------------------------------------------------------------- --- ---------------------- ----------
2.4753E+11                     1 NO             0 23-AUG-14 01.52.56.000000000 AM                                                                             NO  DR_BR_TO_RN_082214              0
                                                 

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 SQLSELECT 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                     1 YES    134217728
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/1024 usedGB from v$recovery_file_dest;

     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/wallet
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/scripts $ sqlplus / as sysdba

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/1024 usedGB from v$recovery_file_dest;

     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                                         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


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 

1 comments:

svr said...

This is extremely great information for these blog!! And Very good work. It is very interesting to learn from to easy understood. Thank you for giving information. Please let us know and more information get post to link.
weblogic administrator training

Post a Comment

Auto Scroll Stop Scroll