Sunday, 15 December 2013

How to convert to snapshot dataguard

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

DGMGRL> show configuration

Configuration
  Name:                gavin
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    apex   - Primary database
    apexdg - Snapshot standby database

Fast-Start Failover: DISABLED

Current status for "gavin":
SUCCESS


We now connect to the snapshot standby database which is open in read-write mode and we create a table 


apex:/u01/oracle/scripts> export ORACLE_SID=apexdg
apexdg:/u01/oracle/scripts> sqlplus sh/SH

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:33:29 2008

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test_snapshot
  2  as select * from all_objects;

Table created.

SQL> select count(*) from test_snapshot;

  COUNT(*)
----------
     56467

At this point in time, we revert back to the original state

DGMGRL> convert database 'apexdg' to physical standby;

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

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "apexdg" on database "apexdg"
Starting instance "apexdg"...
ORACLE instance started.
Database mounted.
Database "apexdg" converted successfully
DGMGRL>


We now connect to the physical standby database and see that all the changes we made while
the standby database was functioning as a snapshot standby have been rolled back. The table
which we created in the snapshot standby database is not physically present in the physical
standby database.

apexdg:/u01/oracle/scripts> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:48:38 2008

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open read only;

Database altered.

SQL> conn sh/SH@apexdg
Connected.


SQL> select count(*) from test_snapshot;
select count(*) from test_snapshot
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

No comments:

Post a Comment