Syncing Standby Database Using SCN Based Backup

In Oracle dataguard environment sometime we face issues with slow network pushing us into situation where the changes are piled up and normal redo shipping speed is not sufficient to cover the gap within required business timelines or might be archives/redo never shipped to standby database because of unavailability of standby database and you don’t have enough space on primary site to hold all required archives for recovery. In those situations you might take a decision clear space on primary site after moving archives to tape. Later on when standby will be available FAL service would not able to fetch the gap. One option is to restore those archives to standby site and register those archives while another option, which is of intermediate complexity in case we don’t have identical physical structure in both sites.

Today we will demonstrate recovery of standby database with SCN based incremental backup in environment where we have shared filesystem on primary site while standby database is hosting files in ASM.

  1. Check the minimum SCN on standby database from where the changes need to be applied.

    Standby Site

    SQL> select min (checkpoint_change#) from v$datafile_header order by 1;

    MIN(CHECKPOINT_CHANGE#)
    ———————–
    109182027

     

  2. Stop log shipping from Primary to standby as we need to stop standby database during this activity.

    Primary Site

    SQL> alter system set log_archive_dest_state_2=defer scope=both;

     

  3. Take SCN based backup with SCN number we have note down from step 1.

    Primary Site

    rman target /

    RMAN> BACKUP INCREMENTAL FROM SCN 109182027 DATABASE FORMAT ‘/backup/backup4stby_%U’ tag ‘4STANDBY’;

     

  4. SCP the backup pieces to the standby host

     

  5. Catalog backup pieces to standby site.

    Standby Site

    Catalog the backup pieces to standby control file.

    MASKSTBY1 % rman target /

    Recovery Manager: Release 11.2.0.3.0 – Production on Wed Apr 30 22:10:15 2014

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

    connected to target database: MASKPRMY (DBID=1365341345, not open)

     

    RMAN>catalog start with ‘/u02/backups/MASKSTBY’;

    using target database control file instead of recovery catalog

    searching for all files that match the pattern /u02/backups/MASKSTBY

     

    List of Files Unknown to the Database

    =====================================

    File Name: /u02/backups/MASKSTBY/ForStandby_02p72dts_1_1

    File Name: /u02/backups/MASKSTBY/ForStandby_01p72dfs_1_1

     

    Do you really want to catalog the above files (enter YES or NO)? YES

    cataloging files…

    cataloging done

     

    List of Cataloged Files

    =======================

    File Name: /u02/backups/MASKSTBY/ForStandby_02p72dts_1_1

    File Name: /u02/backups/MASKSTBY/ForStandby_01p72dfs_1_1

    RMAN> exit

  6. Cancel the managed recovery at the standby host and recover standby with backup

    Standby Site

    SQL> alter database recover managed standby database cancel;

    RMAN> recover database noredo;

    Starting recover at 30-APR-2014 22:12:02

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=591 instance=MASKSTBY1 device type=DISK

    channel ORA_DISK_1: starting incremental datafile backup set restore

    channel ORA_DISK_1: specifying datafile(s) to restore from backup set

    destination for restore of datafile 00001: +DATA/MASKSTBY/datafile/system.677.810819327

    destination for restore of datafile 00002: +DATA/MASKSTBY/datafile/sysaux.678.810819327

    destination for restore of datafile 00003: +DATA/MASKSTBY/datafile/undotbs1.679.810819327

    destination for restore of datafile 00004: +DATA/MASKSTBY/datafile/users.680.810819327

    destination for restore of datafile 00005: +DATA/MASKSTBY/datafile/undotbs2.681.810819329

    destination for restore of datafile 00006: +DATA/MASKSTBY/datafile/custom_apps_data.682.810819331

    destination for restore of datafile 00007: +DATA/MASKSTBY/datafile/custom_apps_idx.683.810819331

    channel ORA_DISK_1: reading from backup piece /u02/backups/MASKSTBY/ForStandby_01p72dfs_1_1

    channel ORA_DISK_1: piece handle=/u02/backups/MASKSTBY/ForStandby_01p72dfs_1_1 tag=STANDBY

    channel ORA_DISK_1: restored backup piece 1

    channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

     

    Finished recover at 30-APR-2014 22:12:10

    RMAN> exit

     

  7. Take the backup of the control file as standby at the primary site using RMAN.

    Primary Site

    SQL> BACKUP CURRENT CONTROLFILE FOR STANDBY tag standbycrtl format ‘/tmp/control_standby.ctl’;


  8. List database location from standby database as primary and standby sites have different file structure.

    Standby Site

    RMAN> report schema;

    List of Permanent Datafiles

    ===========================

    File Size(MB) Tablespace RB segs Datafile Name

    —- ——– ——————– ——- ————————

    1 1050 SYSTEM *** +DATA/MASKSTBY/datafile/system.677.810819327

    2 1600 SYSAUX *** +DATA/MASKSTBY/datafile/sysaux.678.810819327

    3 105 UNDOTBS1 *** +DATA/MASKSTBY/datafile/undotbs1.679.810819327

    4 5 USERS *** +DATA/MASKSTBY/datafile/users.680.810819327

    5 487 UNDOTBS2 *** +DATA/MASKSTBY/datafile/undotbs2.681.810819329

    6 10240 CUSTOM_APPS_DATA *** +DATA/MASKSTBY/datafile/custom_apps_data.682.810819331

    7 4096 CUSTOM_APPS_IDX *** +DATA/MASKSTBY/datafile/custom_apps_idx.683.810819331

     

    List of Temporary Files

    =======================

    File Size(MB) Tablespace Maxsize(MB) Tempfile Name

    —- ——– ——————– ———– ——————–

    1 20 TEMP 32767 +DATA/MASKSTBY/tempfile/temp.701.812135229

     

  9. SCP the standby control file to the standby host and restore control file on standby database.

    Standby Site

    RMAN> shutdown immediate;

    RMAN> startup nomount;

    RMAN> RESTORE STANDBY CONTROLFILE FROM ‘/u02/backups/MASKSTBY/stby_control.ctl’;

     

    Starting restore at 01-MAY-2014 03:54:41

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=1 instance=MASKSTBY1 device type=DISK

     

    channel ORA_DISK_1: restoring control file

    channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

    output file name=+DATA/MASKSTBY/controlfile/current.676.810818661

    output file name=+FRA/MASKSTBY/controlfile/current.15472.810818661

    Finished restore at 01-MAY-2014 03:54:46

     

  10. Check physical structure of database by querying control file, it will report structure of primary, which need to be changed.

    Standby Site

    SQL> alter database mount;

    Database altered.

    RMAN> report schema;

    RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

    Report of database schema for database with db_unique_name MASKPRMY

    List of Permanent Datafiles

    ===========================

    File Size(MB) Tablespace RB segs Datafile Name

    —- ——– ——————– ——- ————————

    1 0 SYSTEM *** /u287/oradata/MASKPRMY/system01.dbf

    2 0 SYSAUX *** /u287/oradata/MASKPRMY/sysaux01.dbf

    3 0 UNDOTBS1 *** /u287/oradata/MASKPRMY/undotbs01.dbf

    4 0 USERS *** /u287/oradata/MASKPRMY/users01.dbf

    5 0 UNDOTBS2 *** /u287/oradata/MASKPRMY/undotbs02.dbf

    6 0 CUSTOM_APPS_DATA *** /u287/oradata/MASKPRMY/CUSTOM_APPS_DATA.dbf

    7 0 CUSTOM_APPS_IDX *** /u287/oradata/MASKPRMY/CUSTOM_APPS_IDX.dbf

     

    List of Temporary Files

    =======================

    File Size(MB) Tablespace Maxsize(MB) Tempfile Name

    —- ——– ——————– ———– ——————–

    1 20 TEMP 32767 /u287/oradata/MASKPRMY/temp01.dbf

     

  11. Rename the file references in controlfile.

    Standby Site

    Note: we tried “alter database rename file” and got following error as there is no file which we try to rename only file available is in ASM.

     

    SQL> alter database rename file ‘/u287/oradata/MASKPRMY/system01.dbf’ to ‘+DATA/MASKSTBY/datafile/system.677.810819327’;

    ERROR at line 1:

    ORA-01511: error in renaming log/data files

    ORA-01516: nonexistent log file, data file, or temporary file

    “/u287/oradata/MASKPRMY/system01.dbf”

     

    If we use RMAN then it will be done without any issue.

     

    RMAN> run

    {

    set newname for datafile 1 to ‘+DATA/MASKSTBY/datafile/system.677.810819327’;

    set newname for datafile 2 to ‘+DATA/MASKSTBY/datafile/sysaux.678.810819327’;

    set newname for datafile 3 to ‘+DATA/MASKSTBY/datafile/undotbs1.679.810819327’;

    set newname for datafile 4 to ‘+DATA/MASKSTBY/datafile/users.680.810819327’;

    set newname for datafile 5 to ‘+DATA/MASKSTBY/datafile/undotbs2.681.810819329’;

    set newname for datafile 6 to ‘+DATA/MASKSTBY/datafile/custom_apps_data.682.810819331’;

    set newname for datafile 7 to ‘+DATA/MASKSTBY/datafile/custom_apps_idx.683.810819331’;

     

    switch datafile all;

    }

     

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

     

    datafile 1 switched to datafile copy

    input datafile copy RECID=469 STAMP=846389340 file name=+DATA/MASKSTBY/datafile/system.677.810819327

    datafile 2 switched to datafile copy

    input datafile copy RECID=470 STAMP=846389340 file name=+DATA/MASKSTBY/datafile/sysaux.678.810819327

    datafile 3 switched to datafile copy

    input datafile copy RECID=471 STAMP=846389340 file name=+DATA/MASKSTBY/datafile/undotbs1.679.810819327

    datafile 4 switched to datafile copy

    input datafile copy RECID=472 STAMP=846389340 file name=+DATA/MASKSTBY/datafile/users.680.810819327

    datafile 5 switched to datafile copy

    input datafile copy RECID=473 STAMP=846389340 file name=+DATA/MASKSTBY/datafile/undotbs2.681.810819329

    datafile 6 switched to datafile copy

    input datafile copy RECID=474 STAMP=846389340 file name=+DATA/MASKSTBY/datafile/custom_apps_data.682.810819331

    datafile 7 switched to datafile copy

    input datafile copy RECID=475 STAMP=846389341 file name=+DATA/MASKSTBY/datafile/custom_apps_idx.683.810819331

     

     

    RMAN> run

    {

    set newname for tempfile 1 to ‘+DATA/MASKSTBY/tempfile/temp.701.812135229’;

    switch tempfile all;

    }

    executing command: SET NEWNAME

    renamed tempfile 1 to +DATA/MASKSTBY/tempfile/temp.701.812135229 in control file

     

    Check the references again:

     

    RMAN> report schema;

     

    RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

    Report of database schema for database with db_unique_name MASKSTBY

    List of Permanent Datafiles

    ===========================

    File Size(MB) Tablespace RB segs Datafile Name

    —- ——– ——————– ——- ————————

    1 1050 SYSTEM *** +DATA/MASKSTBY/datafile/system.677.810819327

    2 1600 SYSAUX *** +DATA/MASKSTBY/datafile/sysaux.678.810819327

    3 105 UNDOTBS1 *** +DATA/MASKSTBY/datafile/undotbs1.679.810819327

    4 5 USERS *** +DATA/MASKSTBY/datafile/users.680.810819327

    5 487 UNDOTBS2 *** +DATA/MASKSTBY/datafile/undotbs2.681.810819329

    6 10240 CUSTOM_APPS_DATA *** +DATA/MASKSTBY/datafile/custom_apps_data.682.810819331

    7 4096 CUSTOM_APPS_IDX *** +DATA/MASKSTBY/datafile/custom_apps_idx.683.810819331

     

    List of Temporary Files

    =======================

    File Size(MB) Tablespace Maxsize(MB) Tempfile Name

    —- ——– ——————– ———– ——————–

    1 20 TEMP 32767 +DATA/MASKSTBY/tempfile/temp.701.812135229

     

  12. Start shipping from primary and restart media recovery on standby site.

    Primary Site

    Enable redo shipping from primary to standby

     

    SQL> alter system set log_archive_dest_state_2=enable scope=both;

    System altered.

     

    Standby Site

    Stop and restart media recovery

     

    SQL> alter database recover managed standby database cancel;

    Database altered.

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

    Database altered.

    SQL> select inst_id,process,thread#,sequence#,blocks,status from gv$managed_standby where process like ‘%MRP%’;

    INST_ID PROCESS    THREAD# SEQUENCE# BLOCKS STATUS

    ———- ——— ———- ———- ———- ————

         1 MRP0      1 319 1024000 APPLYING_LOG

     

Though there ware couple of other alternatives as well but we found this one important not just standby database but could also be used with minor modifications to achieve migration to remote datacenter with restricted low bandwidth connectivity.

Disclaimer: Whatever we discussed in this blog, is of generic oracle technological overview based on our experiences in day to day operational work as well as reading from oracle online documentations. Our aim is to explore alternative ways to achieve the high availability and to share knowledge with everyone who is looking for it.

Advertisements

Share Your Comment

Please log in using one of these methods to post your comment:

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

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: