Adding new physical standby database and apply incremental backup to sync

just another day in my work…
i will add another physical standby to our existing setup.

        Database Version : 11.2.0.2
	primary          :  FITS
	physical standby :  FITSDG
	physical standby :  FITSDG02 <<--- i will add this.

1. restore the cold backup from another standby.
canceled and shutdown the first standby database and copy all file to my another server.

-filesystem not the same.
primary :	/dbFITS/db04/oradata/FITS
source : 	/dbGTNIP/db04/oradata/FITSDG	<<<---- cold backup taken from first primary.
target : 	/dbGTNIP/db04/oradata/FITSDG02

2.start the database using with new db_unique_name.
copied and edited the pfile from first standby and edit it.

*.control_files='/dbGTNIP/db04/oradata/FITSDG02/control01.ctl','/dbGTNIP/db04/oradata/FITSDG02/control02.ctl','/dbGTNIP/db04/oradata/FITSDG02/control03.ctl'
*.db_file_name_convert='/dbFITS/db04/oradata/FITS','/dbGTNIP/db04/oradata/FITSDG02'
*.db_unique_name='FITSDG02'
*.log_archive_config='dg_config=(FITSDG02,FITS)'
*.log_archive_dest_1='LOCATION=/dbGTNIP/db05/oradata/FITSDG02/arch/dgarc','valid_for=(ALL_ROLES,ONLINE_LOGFILE)'
*.log_archive_dest_2='LOCATION=/dbGTNIP/db05/oradata/FITSDG02/arch','valid_for=(STANDBY_ROLE,STANDBY_LOGFILE)'
FITSDG02.log_archive_format='%t_%s_%r.dbf'
FITSDG02.log_archive_trace=0
*.log_file_name_convert='/dbFITS/db04/oradata/FITS','/dbGTNIP/db04/oradata/FITSDG02'

3. cancel the recovery on standby

Stop Redo Apply on the standby database:
in my case i’m using the cold backup of our first standby database FITSDG

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

4. get current scn , this will be our reference for our incremental backup .

SQL> col CURRENT_SCN format 9999999999999999
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

 CURRENT_SCN
-----------------
316495036169

5. perform incremental backup of primary using the SCN above. backup also included standby controlfile.

RMAN> BACKUP INCREMENTAL FROM SCN 316495036169 DATABASE FORMAT '/dbGTNIP/backup/ForStandby_%U' tag 'FOR STANDBYx';

Starting backup at 06-FEB-12

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=541 device type=DISK
backup will be obsolete on date 13-FEB-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/dbFITS/db04/oradata/FITS/mks_01.dbf
input datafile file number=00025 name=/dbFITS/db04/oradata/FITS/undotbs07.dbf
input datafile file number=00026 name=/dbFITS/db04/oradata/FITS/undotbs08.dbf
input datafile file number=00027 name=/dbFITS/db04/oradata/FITS/undotbs09.dbf
input datafile file number=00028 name=/dbFITS/db04/oradata/FITS/undotbs10.dbf
input datafile file number=00029 name=/dbFITS/db04/oradata/FITS/undotbs11.dbf
input datafile file number=00030 name=/dbFITS/db04/oradata/FITS/undotbs12.dbf
input datafile file number=00031 name=/dbFITS/db04/oradata/FITS/undotbs13.dbf
input datafile file number=00024 name=/dbFITS/db04/oradata/FITS/undotbs06.dbf
input datafile file number=00019 name=/dbFITS/db04/oradata/FITS/undotbs02.dbf
input datafile file number=00020 name=/dbFITS/db04/oradata/FITS/undotbs03.dbf
input datafile file number=00021 name=/dbFITS/db04/oradata/FITS/undotbs04.dbf
input datafile file number=00023 name=/dbFITS/db04/oradata/FITS/undotbs05.dbf
input datafile file number=00002 name=/dbFITS/db04/oradata/FITS/sysaux01.dbf
input datafile file number=00003 name=/dbFITS/db04/oradata/FITS/undotbs01.dbf
input datafile file number=00004 name=/dbFITS/db04/oradata/FITS/users01.dbf
input datafile file number=00001 name=/dbFITS/db04/oradata/FITS/system01.dbf
input datafile file number=00054 name=/dbFITS/db04/oradata/FITS/undotbs14.dbf
channel ORA_DISK_1: starting piece 1 at 06-FEB-12
channel ORA_DISK_1: finished piece 1 at 06-FEB-12
piece handle=/dbGTNIP/backup/ForStandby_28n2liep_1_1 tag=FOR STANDBYX comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 04:04:08

using channel ORA_DISK_1
backup will be obsolete on date 13-FEB-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 06-FEB-12
channel ORA_DISK_1: finished piece 1 at 06-FEB-12
piece handle=/dbGTNIP/backup/ForStandby_29n2m0oh_1_1 tag=FOR STANDBYX comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 06-FEB-12

RMAN> backup current controlfile for standby format '/dbGTNIP/backup/standbyctlx02.ctl';

Starting backup at 06-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 06-FEB-12
channel ORA_DISK_1: finished piece 1 at 06-FEB-12
piece handle=/dbGTNIP/backup/standbyctlx02.ctl tag=TAG20120206T223306 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-FEB-12

RMAN> exit

6. catalog the backup from primary

lnx03ora.bai.com: FITSDG02> rman nocatalog target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 6 22:36:54 2012

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

connected to target database: FITS (DBID=1686172659, not open)
using target database control file instead of recovery catalog

RMAN> catalog backuppiece '/dbGTNIP/backup/ForStandby_28n2liep_1_1';

cataloged backup piece
backup piece handle=/dbGTNIP/backup/ForStandby_28n2liep_1_1 RECID=829 STAMP=774571041

RMAN> catalog backuppiece '/dbGTNIP/backup/ForStandby_29n2m0oh_1_1';

cataloged backup piece
backup piece handle=/dbGTNIP/backup/ForStandby_29n2m0oh_1_1 RECID=830 STAMP=774571054

7. perfom the recovery

RMAN> recover database noredo;

Starting recover at 06-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=690 device type=DISK
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/06/2012 22:37:59
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

RMAN> configure device type sbt_tape clear;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 1;
RMAN configuration parameters are successfully reset to default value

RMAN>

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=690 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbGTNIP/backup/ForStandby_28n2liep_1_1 RECID=833 STAMP=774571337
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/dbGTNIP/backup/ForStandby_29n2m0oh_1_1 RECID=834 STAMP=774571348
Crosschecked 2 objects

RMAN> recover database noredo;

Starting recover at 06-FEB-12
using channel ORA_DISK_1
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: /dbGTNIP/db04/oradata/FITSDG/system01.dbf
destination for restore of datafile 00002: /dbGTNIP/db04/oradata/FITSDG/sysaux01.dbf
destination for restore of datafile 00003: /dbGTNIP/db04/oradata/FITSDG/undotbs01.dbf
destination for restore of datafile 00004: /dbGTNIP/db04/oradata/FITSDG/users01.dbf
destination for restore of datafile 00017: /dbGTNIP/db04/oradata/FITSDG/mks_02.dbf
destination for restore of datafile 00018: /dbGTNIP/db04/oradata/FITSDG/mks_03.dbf
destination for restore of datafile 00019: /dbGTNIP/db04/oradata/FITSDG/undotbs02.dbf
destination for restore of datafile 00020: /dbGTNIP/db04/oradata/FITSDG/undotbs03.dbf
destination for restore of datafile 00021: /dbGTNIP/db04/oradata/FITSDG/undotbs04.dbf
destination for restore of datafile 00023: /dbGTNIP/db04/oradata/FITSDG/undotbs05.dbf
destination for restore of datafile 00024: /dbGTNIP/db04/oradata/FITSDG/undotbs06.dbf
destination for restore of datafile 00025: /dbGTNIP/db04/oradata/FITSDG/undotbs07.dbf
destination for restore of datafile 00026: /dbGTNIP/db04/oradata/FITSDG/undotbs08.dbf
destination for restore of datafile 00027: /dbGTNIP/db04/oradata/FITSDG/undotbs09.dbf
destination for restore of datafile 00028: /dbGTNIP/db04/oradata/FITSDG/undotbs10.dbf
destination for restore of datafile 00029: /dbGTNIP/db04/oradata/FITSDG/undotbs11.dbf
destination for restore of datafile 00030: /dbGTNIP/db04/oradata/FITSDG/undotbs12.dbf
destination for restore of datafile 00031: /dbGTNIP/db04/oradata/FITSDG/undotbs13.dbf
destination for restore of datafile 00054: /dbGTNIP/db04/oradata/FITSDG/undotbs14.dbf
channel ORA_DISK_1: reading from backup piece /dbGTNIP/backup/ForStandby_28n2liep_1_1
channel ORA_DISK_1: piece handle=/dbGTNIP/backup/ForStandby_28n2liep_1_1 tag=FOR STANDBYX
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:25:06

Finished recover at 06-FEB-12

RMAN> exit

8. shutdown database, we need to nomount the database to restore the latest ctl file.

lnx03ora.bai.com: FITSDG02> sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 6 23:18:33 2012

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

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

SQL> select instance_name from v$instance;

INSTANCE_NAME
------------------------------------------------
FITSDG02

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

10. startup nomount.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size                  2238472 bytes
Variable Size            3758098424 bytes
Database Buffers         9059696640 bytes
Redo Buffers                7335936 bytes
SQL> exit

11. restore standby controlfile.

lnx03ora.bai.com: FITSDG02> rman nocatalog target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 6 23:19:24 2012

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

connected to target database: FITS (not mounted)
using target database control file instead of recovery catalog

RMAN> restore standby controlfile from '/dbGTNIP/backup/standbyctlx02.ctl';

Starting restore at 06-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=/dbGTNIP/db04/oradata/FITSDG02/control01.ctl
output file name=/dbGTNIP/db04/oradata/FITSDG02/control02.ctl
output file name=/dbGTNIP/db04/oradata/FITSDG02/control03.ctl
Finished restore at 06-FEB-12

RMAN> exit

12. shutdown and mount database.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size                  2238472 bytes
Variable Size            3758098424 bytes
Database Buffers         9059696640 bytes
Redo Buffers                7335936 bytes
Database mounted.
SQL>

13. clear all logfile.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;

14. add/enable database on dg broker on primary.

lnx07ora: FITS> dgmgrl /
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - FITS
  Protection Mode: MaxPerformance
  Databases:
    FITS   - Primary database
    FITSDG - Physical standby database

Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> exit

--- lets add the DB on the configuration

lnx07ora: FITS> dgmgrl /
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>  add database 'FITSdg02' as connect identifier is FITSDG02_DGMGRL maintained as physical;
Database "FITSdg02" added
DGMGRL> show configuration;
Configuration - FITS
  Protection Mode: MaxPerformance
  Databases:
    FITS     - Primary database
    FITSDG   - Physical standby database
    FITSdg02 - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

--- now enable the new database.

DGMGRL> enable database 'FITSdg02';
Enabled.
DGMGRL> show configuration;
Configuration - FITS
  Protection Mode: MaxPerformance
  Databases:
	FITS     - Primary database
	FITSDG   - Physical standby database
	FITSdg02 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

thanks for visiting my blog.

Advertisements

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