Home > HA > Snapshot Standby

Snapshot Standby

Converting physical standby to snapshot standby

Depens on Data guard configuration change protection mode:

On Primary database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE  PERFORMANCE ;
SQL>  alter system archive log current;

Use these queries to check synchronization between primary and standby:

Primary: SQL> select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL> select thread#, max(sequence#) “Last Standby Seq Received”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL>select thread#, max(sequence#) “Last Standby Seq Applied”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied=’YES’
group by thread# order by 1;

Stop logs shipment on primary database:
ALTER SYSTEM SET log_archive_dest_state_2=’DEFER’ SCOPE=BOTH SID=’*’;

On Physical Standby:

On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

Stop Redo Apply on standby , if it is active.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Ensure that the database is mounted, but not open.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

SQL> ALTER DATABASE OPEN;

Check database status:

select database_role,open_mode from v$database;

Start all instances and database services

srvctl  start service -d <database_name> -s <service_name>

Reverting back to physical standby

On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

Then on run this on instance which is still up:

SQL> shutdown immediate

SQL> startup mount

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> shutdown immediate

SQL> startup mount

Start redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE;

On primary database:

Start redo transport services:

SQL> alter system set log_archive_dest_state_2=enable scope=both sid=’*’;

SQL>  alter system archive log current;

Use queries from the top to verify that all logs are received and apply.


About these ads
Categories: HA
  1. April 8, 2010 at 8:52 pm

    Hi,

    Never used that new feature of 11g.
    Is this using flashback database recovery ? I have similar solution based on 10g DataGuard and flashback.
    Wondering if there is any advantage of that beside a more simple scripting.

    regards,
    Marcin Przepiorowski

  2. April 8, 2010 at 9:05 pm

    Yes it does use flashback in background.
    In 10g you would create restore point and flashback.In 11g Oracle introduced CONVERT command.Well if you are using DGMGRL ( Data Guard broker ) than whole process is even more simple.

  3. DBA
    April 20, 2010 at 6:17 am

    hi,
    I am really not getting what is the use of this, as managed recovery has been stopped and standby is lagging behind, it will take time to get in sync with primary.
    better to use Active Data Guard ..

  4. April 20, 2010 at 2:00 pm

    1) Oracle Active Data Guard enables read-only access

    2) You need to have license for Active Data Guard ( about 10k per CPU on both sides ( primary and standby site)

    3)Snapshot database is used for testing

  5. bernard polarski
    July 19, 2010 at 8:59 am

    interesting feature and many thanks for sharing this, I was not aware of but 10k per CPU ….. Outch, at this price I am not going to ever advocate this feature. I will advice to tick to simple rman duplicate from active or rman restore back on a test server.

  6. July 19, 2010 at 4:52 pm

    10k without discount for Active Data Guard.. .. but keep in mind that you have to pay for every CPU both primary and secondary … Snapshot standby is free…

  1. April 16, 2010 at 7:47 pm
  2. April 16, 2010 at 8:11 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 47 other followers

%d bloggers like this: