📋Steps in this guide1/2

How to do perform the same using Dataguard Broker

Covert Physical Standby database to Snapshot Standby database R/W mode.

oracle clusteringintermediate

by OracleDba

12 views

Covert Physical Standby database to Snapshot Standby database R/W mode.

0. Overview 1. Environment PRIMARY: (DELL) STANDBY: (DELL_DG) 2. Configure Standby Database (DELL_DG) 3. Verify archive log GAP On Primary On Standby 4. Verify Flash Recovery Area and Flashback database Primary Standby 5. Cancel MRP on Standby 6. Covert to snapshot standby from physical standby 7. Testing

Code/Command (click line numbers to comment):

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database.

To Configure snapshot standby, first we need to create
physical
standby and then we will convert from
physical
standby to Snapshot standby

Developers want to testing on Fresh Live Data, but DBA can't allow them to test on Primary, then how to
archive
developer requirement.. DBA can convert
physical
standby to snapshot standby in R/W mode. Hence Developers can make their changes in Snapshot databases.

Whatever changes done on snapshot standby will be flushed out once convert back to
physical
standby database from snapshot standby.

Primary database changes will not applied to snapshot standby database why because there is no MRP process running snapshot database.
No need to enable flashback database.
Only need to have db_recovery_file_dest and db_recovery_file_dest_size on physical standby.

Platform          : Linuxx86_64
Server Name       : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version        : Oracle 11.2.0.4
File system	  : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version        : Oracle 11.2.0.4
File system       : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

This is sample document, database names will be different
http://www.br8dba.com/active-dataguard/

SQL>
select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN
DELL
PRIMARY
READ WRITE

SQL>
select max(sequence#) from v$archived_log where archived='YES';
MAX(SEQUENCE#)
--------------
57  <----
SQL>

SQL>
select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN
DELL_DG
PHYSICAL STANDBY
READ ONLY WITH APPLY

SQL>
select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
57 <-----
SQL>

SQL>
select flashback_on from v$database;
FLASHBACK_ON
------------------
NO <------
SQL>

SQL>
show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest
string      /u02/oracle/oradata/fast_recovery_area
db_recovery_file_dest_size
big integer
3G
SQL>

SQL> select flashback_on from v$database;

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

SQL>
SQL>
show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest
string      /u02/oracle/oradata/fast_recovery_area
db_recovery_file_dest_size
big integer 3G
SQL>

SQL>
select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN
DELL_DG
PHYSICAL STANDBY
READ ONLY WITH APPLY

SQL>
alter database recover managed standby database cancel;
Database altered.

SQL>

SQL>
alter database convert to snapshot standby;
Database altered.

SQL>
select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED
DELL_DG
SNAPSHOT STANDBY
MOUNTED

SQL>
SQL>
alter database open;
Database altered.

SQL>
select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN
DELL_DG
SNAPSHOT STANDBY
READ WRITE
SQL>
Alert log file:
Sat Dec 29 14:52:49 2018
alter database recover managed standby database cancel
Sat Dec 29 14:52:50 2018
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/dell_dg/DELL_DG/trace/DELL_DG_mrp0_3982.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (DELL_DG)
Managed Standby Recovery Canceled (DELL_DG)
Completed: alter database recover managed standby database cancel
Sat Dec 29 15:15:16 2018
alter database convert to snapshot standby
Starting background process RVWR
Sat Dec 29 15:15:16 2018
RVWR started with pid=24, OS id=25202
Allocated 4194304 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/29/2018
15:15:16
Killing 4 processes with pids 4048,4044,4046,4050 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 8813
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Sat Dec 29 15:15:20 2018
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1228396
Resetting resetlogs activation ID 3965561545 (0xec5daac9)
Online log /u02/oracle/oradata/DELL_DG/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u02/oracle/oradata/DELL_DG/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u02/oracle/oradata/DELL_DG/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1228394
Sat Dec 29 15:15:20 2018
Setting recovery target incarnation to 4
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
Sat Dec 29 15:16:01 2018
ARC1: Becoming the 'no SRL' ARCH
Sat Dec 29 15:16:22 2018
idle dispatcher 'D000' terminated, pid = (17, 1)
Sat Dec 29 15:16:30 2018
alter database open
Data Guard Broker initializing...
Data Guard Broker initialization complete
Sat Dec 29 15:16:30 2018
Assigning activation ID 3966853654 (0xec716216)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u02/oracle/oradata/DELL_DG/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Dec 29 15:16:30 2018
SMON: enabling cache recovery
Sat Dec 29 15:16:30 2018
NSA2 started with pid=25, OS id=25215
[8813] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:26075724 end:26075874 diff:150 (1 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
Database Characterset is WE8MSWIN1252
Sat Dec 29 15:16:30 2018
SMCO started with pid=26, OS id=25217
No Resource Manager plan active
Starting background process QMNC
Sat Dec 29 15:16:31 2018
QMNC started with pid=27, OS id=25219
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

SQL>
select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG
SNAPSHOT STANDBY
READ WRITE
SQL>
create user raj identified by raj default tablespace users temporary tablespace temp;
User created.

SQL>
grant connect, resource to raj;
Grant succeeded.

SQL>
conn raj/raj;
Connected.
SQL>
create table test (a number);
Table created.

SQL>
insert into test values (1);
1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL>
commit;
Commit complete.

SQL>
select count(*) from test;
COUNT(*)
----------
         3

SQL>

Using DG Broker – Convert Physical standby to Snapshot standby

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

DGMGRL>
show configuration;
Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
DELL_DG - Physical standby database <----
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
DGMGRL>


DGMGRL>
CONVERT DATABASE "DELL_DG" TO SNAPSHOT STANDBY;
Converting database "DELL_DG" to a Snapshot Standby database, please wait...
Database "DELL_DG" converted successfully
DGMGRL>

DGMGRL>
show configuration;
Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
DELL_DG - Snapshot standby database <----
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!