Physical Standby Database To Snapshot standby Database

Convert Physical Standby Database To Snapshot standby Database

The difference between a read-only standby and a snapshot standby is that the snapshot standby is fully up dateable. It was possible in Oracle 10g to open a standby database as read-write but as from version 11g you now have the snapshot feature. This new feature makes it simpler to make the standby read-write and to revert to back again with the use of the Broker, also it is advised to use the flashback database feature as it makes life a whole lot simpler.

This snapshot standby database is fully updatable database and it is opened for read write operation. When the snapshot database is created from physical standby database, redo logs are still transferred to standby site but are not applied.

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

SBYPROD                        MOUNTED              PHYSICAL STANDBY

SQL> alter system set db_flashback_retention_target=1440;

System altered.

SQL>  Show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /u01/app/oracle/flash_recovery

                                                 _area

db_recovery_file_dest_size           big integer 3882M

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

NO

Make DATABASE FLASHBACK ON

SQL> alter system set db_flashback_retention_target=1440;

System altered.

SQL> alter system set db_recovery_file_dest_size=5g;

System altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>  select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

SQL> alter database flashback on;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL>  select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

SBYPROD                        MOUNTED              SNAPSHOT STANDBY

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  651378688 bytes

Fixed Size                  2216184 bytes

Variable Size             390074120 bytes

Database Buffers          255852544 bytes

Redo Buffers                3235840 bytes

Database mounted.

Database opened.

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

SBYPROD                        READ WRITE           SNAPSHOT STANDBY

****************************************************

Revert back to Physical Standby

****************************************************

First shutdown database

open in mount mode

convert in physical standby mode

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  651378688 bytes

Fixed Size                  2216184 bytes

Variable Size             390074120 bytes

Database Buffers          255852544 bytes

Redo Buffers                3235840 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

AGAIN SHUTDOWN DATABASE AND OPEN IN MOUNT MODE

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  651378688 bytes

Fixed Size                  2216184 bytes

Variable Size             390074120 bytes

Database Buffers          255852544 bytes

Redo Buffers                3235840 bytes

Database mounted.

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

SBYPROD                        MOUNTED              PHYSICAL STANDBY

Recover Physical Standby database

------------------------------------------------------------

ORA-01153: an incompatible media recovery is active

------------------------------------------------------------

SQL> recover managed standby database disconnect from session;

Media recovery complete.

Check for database role

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

SBYPROD                        MOUNTED              PHYSICAL STANDBY

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area/sbyprod/

Oldest online log sequence     0

Next log sequence to archive   0

Current log sequence           42

CHECK MRP PROCESS

SQL> select PROCESS,STATUS from v$managed_standby;

PROCESS   STATUS

--------- ------------

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CLOSING

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

MRP0      WAIT_FOR_LOG

33 rows selected.

Pankaj Yadav

Physical standby to Active data guard

Change Physical standby to Active data guard (Read only) with Apply

Convert Physical Standby Database To Snapshot Database

Verify Snapshot Standby Database

Do Some Testing In Snapshot Database

Convert Snapshot Database Back To Physical Standby Database

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

SQL> alter database recover managed standby database cancel;      

Database altered.

SQL> alter database open;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY

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

Database altered.

SQL> select PROCESS,STATUS from v$managed_standby;

PROCESS   STATUS

--------- ------------

ARCH      CLOSING

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

RFS       IDLE

PROCESS   STATUS

--------- ------------

RFS       IDLE

MRP0      WAIT_FOR_LOG

35 rows selected.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY WITH APPLY

[oracle@standby ~]$ ps -ef | grep mrp

oracle   17877     1  0 01:03 ?        00:00:00 ora_mrp0_sbyprod

oracle   17919 17896  0 01:04 pts/2    00:00:00 grep mrp

*************************************************

Return to recovery mode from Read only

*************************************************

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  651378688 bytes

Fixed Size                  2216184 bytes

Variable Size             390074120 bytes

Database Buffers          255852544 bytes

Redo Buffers                3235840 bytes

Database mounted.

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

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area/sbyprod/

Oldest online log sequence     32

Next log sequence to archive   0

Current log sequence           34

-------------------------------------------

enable real-time apply of redo 

sql>alter database recover managed standby database using current logfile disconnect;

Physical Standby database creation Using RMAN Duplicate From Active Database

Create Physical Standby Using RMAN Duplication From Active Database (New in 11g) :

	Primary Site	Standby Site
Hostname	prod.erp.com	standby.erp.com
Database Name	prod	prod
Database Unique Name	prod	sbyprod
Net Service Name	prod	sbyprod

Primary Site Preparation :

1. Enable Database Force Logging

SQL> select FORCE_LOGGING from v$database;

FOR

---

NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> col group# for 9999

col member for a50

set linesize 120

SQL> select member,type from v$logfile;

MEMBER                                             TYPE

-------------------------------------------------- -------

/u01/app/oracle/oradata/prod/redo03.log            ONLINE

/u01/app/oracle/oradata/prod/redo02.log            ONLINE

/u01/app/oracle/oradata/prod/redo01.log            ONLINE

/u01/app/oracle/oradata/prod/sby_redo01            STANDBY

/u01/app/oracle/oradata/prod/sby_redo02            STANDBY

/u01/app/oracle/oradata/prod/sby_redo03            STANDBY

/u01/app/oracle/oradata/prod/sby_redo04            STANDBY

7 rows selected.

SQL> create pfile from spfile;

File created.

3.Modify the init.ora parameters:

Modify the init.ora parameters on the primary database side to reflect the following values. If you are using spfile, you can add these parameter online without shutting down the primary database.

DB_UNIQUE_NAME=prod

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prod,sbyprod)’

LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/flash_recovery_area/prod/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod’

LOG_ARCHIVE_DEST_2=’SERVICE=sbyprod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbyprod’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=sbyprod

DB_FILE_NAME_CONVERT=’sbyprod’,'prod’

LOG_FILE_NAME_CONVERT=’/u01/app/oracle/flash_recovery_area/sbyprod/’,'/u01/app/oracle/flash_recovery_area/prod/’

STANDBY_FILE_MANAGEMENT=AUTO

SQL> alter system set DB_UNIQUE_NAME=prod scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,sbyprod)' scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/flash_recovery_area/prod/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=sbyprod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbyprod' scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;

System altered.

SQL> ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=spfile;

System altered.

SQL> alter system set FAL_SERVER=sbyprod scope=spfile;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='sbyprod','prod' SCOPE=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/flash_recovery_area/sbyprod','/u01/app/oracle/flash_recovery_area/prod/' scope=spfile;

System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

5.Create a init.ora file for Standby Database:

SQL> create pfile from spfile;

File created.

---------------------------------------------------------------------

MAKE LISTENER.ORA AND TNSNAMES.ORA ENTRY ON BOTH SIDE

check tnsping both side

Copy password file from pimary to standby and rename

------------------------------------------------------

Creating Physical Standby Database :

1.Create Physical Standby Using RMAN Duplication From Active Database (New in 11g) :

[oracle@standby admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 6 22:36:13 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sql>

Create Physical Standby Using RMAN Duplication From Active Database 

[oracle@standby dbhome_1]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 20 00:35:09 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/oracle123@prod

connected to target database: PROD (DBID=255899578)

RMAN> connect auxiliary sys/oracle123@sbyprod

connected to auxiliary database: PROD (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 20-APR-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprod' auxiliary format

 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsbyprod'   ;

}

executing Memory Script

Starting backup at 20-APR-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=87 device type=DISK

Finished backup at 20-APR-14

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/sbyprod/control01.ctl';

   restore clone controlfile to  '/u01/app/oracle/flash_recovery_area/sbyprod/control02.ctl' from

 '/u01/app/oracle/oradata/sbyprod/control01.ctl';

}

executing Memory Script

Starting backup at 20-APR-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prod.f tag=TAG20140420T003558 RECID=4 STAMP=845339759

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 20-APR-14

Starting restore at 20-APR-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 20-APR-14

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/sbyprod/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/sbyprod/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/sbyprod/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/sbyprod/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/sbyprod/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/sbyprod/example01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oracle/oradata/sbyprod/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oracle/oradata/sbyprod/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oracle/oradata/sbyprod/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oracle/oradata/sbyprod/users01.dbf"   datafile

 5 auxiliary format

 "/u01/app/oracle/oradata/sbyprod/example01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/sbyprod/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 20-APR-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf

output file name=/u01/app/oracle/oradata/sbyprod/system01.dbf tag=TAG20140420T003608

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf

output file name=/u01/app/oracle/oradata/sbyprod/sysaux01.dbf tag=TAG20140420T003608

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/prod/example01.dbf

output file name=/u01/app/oracle/oradata/sbyprod/example01.dbf tag=TAG20140420T003608

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf

output file name=/u01/app/oracle/oradata/sbyprod/undotbs01.dbf tag=TAG20140420T003608

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf

output file name=/u01/app/oracle/oradata/sbyprod/users01.dbf tag=TAG20140420T003608

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 20-APR-14

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=845339885 file name=/u01/app/oracle/oradata/sbyprod/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=845339885 file name=/u01/app/oracle/oradata/sbyprod/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=845339885 file name=/u01/app/oracle/oradata/sbyprod/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=845339885 file name=/u01/app/oracle/oradata/sbyprod/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=845339885 file name=/u01/app/oracle/oradata/sbyprod/example01.dbf

Finished Duplicate Db at 20-APR-14

RMAN>

SQL> select database_role,PROTECTION_MODE,open_mode from v$database;

DATABASE_ROLE    PROTECTION_MODE      OPEN_MODE

---------------- -------------------- --------------------

PHYSICAL STANDBY MAXIMUM PERFORMANCE  MOUNTED

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY NOT ALLOWED

Verifications :

On Standby:

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area/sbyprod/

Oldest online log sequence     30

Next log sequence to archive   0

Current log sequence           31

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# APPLIED

---------- ---------

        28 YES

        29 YES

        30 IN-MEMORY

-----------------------------------------------------------------

On Primary :

Do some redo log switches

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> /

System altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area/prod/

Oldest online log sequence     31

Next log sequence to archive   33

Current log sequence           33

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

PRIMARY          TO STANDBY

----------------------------------------------------------------------

On Standby:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# APPLIED

---------- ---------

        28 YES

        29 YES

        30 YES

        31 YES

        32 YES

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area/sbyprod/

Oldest online log sequence     31

Next log sequence to archive   0

Current log sequence           33

SQL> select PROCESS,STATUS from v$managed_standby;

PROCESS   STATUS

--------- ------------

ARCH      CLOSING

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

RFS       IDLE

PROCESS   STATUS

--------- ------------

RFS       IDLE

MRP0      APPLYING_LOG

35 rows selected.