Thursday, 8 February 2018

Convert Physical Standby To Snapshot Standby

Environment:-

Database Name:- HAPPY
Primary DB Unique Name/SID - Server:- HAPPYDG1 - servera
Standby DB Unique Name/SID - Server:- HAPPYDG2 - serverb
---------------------------------------------------------

Steps:-

1. Setup a Physical Standby Database (Dataguard Setup)
2. Below is the dataguard setup used
3. Check the Dataguard configuration from dgmgrl
4. Check the sequence which is generated in primary and standby database 
5. Check the Flashback of Standby Database
6. Check the Standby Database is SYNC with Primary
7. Cancel the recovery mode in Standby Database
8. Shutdown and Startup mount the database
9. Convert the Standby Database to Snapshot Standby and open the database in read-write
10. We can check the archive log switch in primary but in standby it will not apply
11. We can now insert/change data in Snapshot Standby Database
12. Now we will convert the snapshot standby database to physical standby database - Shutdown and startup mount the database
13. Convert the snapshot standby database to physical standby database then shutdown
14. Startup the database and enable the recovery mode
15. Make sure the physical standby database is sync with primary
16. Check if the user/table is present or not (which was created in snapshot standby database)
17. Check the snapshot standby details
18. We can Convert physical standby to snapshot standby Using DGMGRL also
19. We can Convert snapshot standby to physical standby Using DGMGRL also


Detail Steps:-

1. Setup a Physical Standby Database (Dataguard Setup)

Setup a Data guard Environment as pre-requisite.

2. Below is the dataguard setup used

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

 STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         HAPPYDG1         PRIMARY          READ WRITE

 STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      HAPPYDG2         PHYSICAL STANDBY MOUNTED

3. Check the Dataguard configuration from dgmgrl


 [oracle@servera ~]$ dgmgrl /

 DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

 Copyright (c) 2000, 2009, Oracle. All rights reserved.

 Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;

 Configuration - happydgconf

   Protection Mode: MaxPerformance
  Databases:
    happydg1 - Primary database
    happydg2 - Physical standby database

 Fast-Start Failover: DISABLED

 Configuration Status:
SUCCESS

 DGMGRL> exit
[oracle@servera ~]$


4. Check the sequence which is generated in primary and standby database 

Primary:-

17:46:08 SYS@HAPPYDG1>select thread#,max(sequence#) from v$archived_log group by thread#;

    THREAD# MAX(SEQUENCE#)
---------- --------------
         1             42

Standby:-

17:46:13 SYS@HAPPYDG2>select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

    THREAD# MAX(SEQUENCE#)
---------- --------------
         1             42

 

5. Check the Flashback of Standby Database


 17:50:18 SYS@HAPPYDG2>select flashback_on from v$database;

 FLASHBACK_ON
------------------
NO

6. Check the Standby Database is SYNC with Primary


 17:51:17 SYS@HAPPYDG2>select process,status from v$managed_standby;

 PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      WAIT_FOR_LOG

 9 rows selected.

 17:45:55 SYS@HAPPYDG2>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# ;

     Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     42                    42          0

  
 

7. Cancel the recovery mode in Standby Database


 17:52:46 SYS@HAPPYDG2>alter database recover managed standby database cancel;

 Database altered.

 17:55:02 SYS@HAPPYDG2>select process,status from v$managed_standby;

 PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
RFS       IDLE

 8 rows selected.

8. Shutdown and Startup mount the database


 17:55:04 SYS@HAPPYDG2>shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
17:55:18 SYS@HAPPYDG2>startup mount
ORACLE instance started.

 Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
Database mounted.

9. Convert the Standby Database to Snapshot Standby and open the database in read-write


 17:55:27 SYS@HAPPYDG2>alter database convert to snapshot standby;

 Database altered.

 17:55:44 SYS@HAPPYDG2>alter database open;

 Database altered.

 17:55:49 SYS@HAPPYDG2>select status,instance_name,database_role,open_mode from v$database,v$Instance;

 STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         HAPPYDG2         SNAPSHOT STANDBY READ WRITE


10. We can check the archive log switch in primary but in standby it will not apply

Primary:-

18:06:41 SYS@HAPPYDG1>alter system switch logfile;

 System altered.

 18:06:46 SYS@HAPPYDG1>/

 System altered.

 18:06:47 SYS@HAPPYDG1>/

 System altered.

 18:06:50 SYS@HAPPYDG1>/
System altered.

 18:06:50 SYS@HAPPYDG1>/
System altered.

 18:06:51 SYS@HAPPYDG1>/
System altered.

 18:06:56 SYS@HAPPYDG1>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     61
Next log sequence to archive   63
Current log sequence           63

Standby:-

17:55:54 SYS@HAPPYDG2>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2


11. We can now insert/change data in Snapshot Standby Database


 17:58:42 SYS@HAPPYDG2>create user test identified by test;

 User created.

 17:59:21 SYS@HAPPYDG2>grant connect,resource to test;

 Grant succeeded.

 17:59:33 SYS@HAPPYDG2>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@serverb ~]$ sqlplus test/test@HAPPYDG2

 SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 8 18:00:54 2018

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 18:02:10 TEST@HAPPYDG2>create table tab1 (i int,name varchar2(20));

 Table created.

 18:03:03 TEST@HAPPYDG2>insert into tab1 values(&i,'&name');
Enter value for i: 101
Enter value for name: hello
old   1: insert into tab1 values(&i,'&name')
new   1: insert into tab1 values(101,'hello')

 1 row created.

 18:03:17 TEST@HAPPYDG2>/
Enter value for i: 102
Enter value for name: hari
old   1: insert into tab1 values(&i,'&name')
new   1: insert into tab1 values(102,'hari')

 1 row created.

 18:03:24 TEST@HAPPYDG2>/
Enter value for i: 103
Enter value for name: sam
old   1: insert into tab1 values(&i,'&name')
new   1: insert into tab1 values(103,'sam')

 1 row created.

 18:03:28 TEST@HAPPYDG2>commit;

 Commit complete.

 18:03:31 TEST@HAPPYDG2>select * from tab1;

          I NAME
---------- --------------------
       101 hello
       102 hari
       103 sam

 18:03:50 TEST@HAPPYDG2>select * from tab;

 TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TAB1                           TABLE

 18:05:50 TEST@HAPPYDG2>update tab1 set name='GOOGLY' where name='hari';

 1 row updated.

 18:06:01 TEST@HAPPYDG2>commit;

 Commit complete.

 18:06:04 TEST@HAPPYDG2>select * from tab1;

          I NAME
---------- --------------------
       101 hello
       102 GOOGLY
       103 sam


12. Now we will convert the snapshot standby database to physical standby database - Shutdown and startup mount the database


 18:09:14 SYS@HAPPYDG2>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
18:09:34 SYS@HAPPYDG2>startup mount
ORACLE instance started.

 Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
Database mounted.

13. Convert the snapshot standby database to physical standby database then shutdown


 18:09:43 SYS@HAPPYDG2>alter database convert to physical standby;

 Database altered.

 18:09:59 SYS@HAPPYDG2>shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
18:10:14 SYS@HAPPYDG2>



14. Startup the database and enable the recovery mode


 18:10:24 SYS@HAPPYDG2>startup
ORACLE instance started.

 Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
18:11:31 SYS@HAPPYDG2>alter database recover managed standby database disconnect from session;

 Database altered.

 18:11:33 SYS@HAPPYDG2>select status,instance_name,database_role,open_mode from v$database,v$Instance;

 STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         HAPPYDG2         PHYSICAL STANDBY READ ONLY



15. Make sure the physical standby database is sync with primary

Primary:-

18:12:00 SYS@HAPPYDG1>select thread#,max(sequence#) from v$archived_log group by thread#;

    THREAD# MAX(SEQUENCE#)
---------- --------------
         1             62
 
[oracle@servera ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

 Copyright (c) 2000, 2009, Oracle. All rights reserved.

 Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;

 Configuration - happydgconf

   Protection Mode: MaxPerformance
  Databases:
    happydg1 - Primary database
    happydg2 - Physical standby database

 Fast-Start Failover: DISABLED

 Configuration Status:
SUCCESS

Standby:-  

18:11:51 SYS@HAPPYDG2>select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

    THREAD# MAX(SEQUENCE#)
---------- --------------
         1             62

 17:45:55 SYS@HAPPYDG2>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# ;

     Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     62                    62          0


16. Check if the user/table is present or not (which was created in snapshot standby database)


 18:10:32 SYS@HAPPYDG2>select status,instance_name,database_role,open_mode from v$database,v$Instance;

 STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         HAPPYDG2         PHYSICAL STANDBY READ ONLY

 18:12:32 SYS@HAPPYDG2>select * from test.tab1;
select * from test.tab1
                   *
ERROR at line 1:
ORA-00942: table or view does not exist


17. Check the snapshot standby details


 18:12:40 SYS@HAPPYDG2>set lines 180
col STARTUP_TIME for A25
col BEGIN_INTERVAL_TIME for A25
col  END_INTERVAL_TIME for A25
select SNAP_ID,DBID,INSTANCE_NUMBER,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from DBA_HIST_SNAPSHOT;

    SNAP_ID       DBID INSTANCE_NUMBER STARTUP_TIME              BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ---------- --------------- ------------------------- ------------------------- -------------------------
         1 1555746649               1 08-FEB-18 04.56.04.000 PM 08-FEB-18 04.56.04.000 PM 08-FEB-18 05.30.49.598 PM


18. We can Convert physical standby to snapshot standby Using DGMGRL also


[oracle@servera ~]$dgmgrl sys/password@HAPPYDG1

 DGMGRL> show configuration;

 Configuration - happydgconf

   Protection Mode: MaxPerformance
  Databases:
    happydg1 - Primary database
    happydg2 - Physical standby database

 Fast-Start Failover: DISABLED

 Configuration Status:
SUCCESS

 DGMGRL> convert database happydg2 to snapshot standby;
Converting database "happydg2" to a Snapshot Standby database, please wait...
Database "happydg2" converted successfully
DGMGRL> show configuration;

 Configuration - happydgconf

   Protection Mode: MaxPerformance
  Databases:
    happydg1 - Primary database
    happydg2 - Snapshot standby database

 Fast-Start Failover: DISABLED

 Configuration Status:
SUCCESS

 DGMGRL> exit



19. We can Convert snapshot standby to physical standby Using DGMGRL also
[oracle@servera ~]$dgmgrl sys/password@HAPPYDG1

 DGMGRL> show configuration;

 Configuration - happydgconf

   Protection Mode: MaxPerformance
  Databases:
    happydg1 - Primary database
    happydg2 - Snapshot standby database

 Fast-Start Failover: DISABLED

 Configuration Status:
SUCCESS

 DGMGRL> convert database happydg2 to physical standby;
Converting database "happydg2" to a Physical Standby database, please wait...
Operation requires shutdown of instance "HAPPYDG2" on database "happydg2"
Shutting down instance "HAPPYDG2"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "HAPPYDG2" on database "happydg2"
Starting instance "HAPPYDG2"...
ORACLE instance started.
Database mounted.
Continuing to convert database "happydg2" ...
Operation requires shutdown of instance "HAPPYDG2" on database "happydg2"
Shutting down instance "HAPPYDG2"...
ORA-01109: database not open

 Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "HAPPYDG2" on database "happydg2"
Starting instance "HAPPYDG2"...
ORACLE instance started.
Database mounted.
Database "happydg2" converted successfully
DGMGRL> show configuration;

 Configuration - happydgconf

   Protection Mode: MaxPerformance
  Databases:
    happydg1 - Primary database
    happydg2 - Physical standby database

 Fast-Start Failover: DISABLED

 Configuration Status:
SUCCESS

 DGMGRL> exit



*************************** END *****************************

 

  

Posted by at No comments:
Subscribe to: Posts (Atom)