Advanced Backup & Recovery For Oracle Dataguard based on The Reference Using Recovery Manager with Oracle Data Guard in Oracle Database 10g

In a standby environment, backing up data files and archived redo log files taken on the primary or standby system are usable on either system for recovery. Although some files such as the control file and SPFILE must be backed up on the primary database, the process of backing up data files and archived redo log files can be off-loaded to the standby system, to minimize the effect of backups on the production system.

Only those archived redo log files that were created by the standby instance can be backed up at the standby site. If there were any archived redo log files generated before the standby database was started, they must be backed up on the primary database. For example, if the first log sent from the primary database to the standby is log sequence 100 thread 1, then the backup of archived redo log files whose log sequence is less than 100 must be done on the primary database.

So I initiated this series by the following case hoping that it could give you some help and hints on this. During the next few days, I will update other case to record the process of my experiment.

Case One -- Recover a datafile in primary site from the backup taken in standby site using catalog.

10.248.38.170 RAC01 Primary DB (under MAXIMUM AVAILABILITY protection mode)
10.248.38.171 RAC02 Standby DB

SQL> select name,database_role role,log_mode,
2 protection_mode,protection_level
3 from v$database;

NAME ROLE LOG_MODE PROTECTION_MODE PROTECTION_LEVEL
---------- ------- ------------ -------------------- --------------------
O01DMS0A PRIMARY ARCHIVELOG MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Being aware of that in this case a shared directory (/archbk) is exported across the two node using NFS which makes the backup & recovery a little easier.

The configuration for UFS at Primary site.

[oracle@RAC01 o01dms0arch]$ cat /etc/exports
/sharedisk *(rw,sync,no_wdelay,insecure_locks,no_root_squash)
[oracle@RAC01 o01dms0arch]$ cat /etc/fstab# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/VolGroup00/LogVol00 / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
/dev/VolGroup00/LogVol01 swap swap defaults 0 0
/dev/hda /media/cdrom auto pamconsole,exec,noauto,managed 0 0
/dev/fd0 /media/floppy auto pamconsole,exec,noauto,managed 0 0
nas1:/sharedisk /archbk nfs rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0 0 0

The configuration of NFS at Standby site.

[oracle@RAC02 archbk]$ cat /etc/fstab
# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/VolGroup00/LogVol00 / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
/dev/VolGroup00/LogVol01 swap swap defaults 0 0
/dev/hda /media/cdrom auto pamconsole,exec,noauto,managed 0 0
/dev/fd0 /media/floppy auto pamconsole,exec,noauto,managed 0 0
nas1:/sharedisk /archbk nfs rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0 0 0

Configuration for RMAN

[oracle@RAC01 o01dms0arch]$ rman target / catalog

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 20 14:45:01 2008

connected to target database: O01DMS0A (DBID=231561342)
connected to recovery catalog database

RMAN> show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/archbk/O01DMS0A_%U_BK.bak';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/archbk/snapcf_O01DMS0A.f';

[@more@]

1 @Standby Database.

[oracle@RAC02 ~]$ rman target / catalog

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 19 06:55:04 2008

connected to target database: O01DMS0A (DBID=231561342, not open)
connected to recovery catalog database

RMAN> backup database; -- take a full backup at standby site.

Starting backup at 19-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/ddms02/dms/o02dms0suprt1/sales_02.O01DMS0A
input datafile fno=00007 name=/ddms02/dms/o02dms0suprt1/sales_01.O01DMS0A
input datafile fno=00008 name=/ddms02/dms/o02dms0suprt1/ndx_01.O01DMS0A
input datafile fno=00005 name=/ddms02/dms/o02dms0suprt1/system_02.O01DMS0A
input datafile fno=00003 name=/ddms02/dms/o02dms0suprt1/sysaux_01.O01DMS0A
input datafile fno=00002 name=/ddms02/dms/o02dms0suprt1/undo_01.O01DMS0A
input datafile fno=00001 name=/ddms02/dms/o02dms0suprt1/system_01.O01DMS0A
input datafile fno=00006 name=/ddms02/dms/o02dms0suprt1/business_01.O01DMS0A
input datafile fno=00009 name=/ddms02/dms/o02dms0suprt1/ndx_02.O01DMS0A
input datafile fno=00010 name=/ddms02/dms/o02dms0suprt1/ndx_03.O01DMS0A
input datafile fno=00011 name=/ddms02/dms/o02dms0suprt1/tools_01.O01DMS0A
channel ORA_DISK_1: starting piece 1 at 19-JUN-08
channel ORA_DISK_1: finished piece 1 at 19-JUN-08
piece handle=/archbk/O01DMS0A_42jja4br_1_1_BK.bak tag=TAG20080619T071505 comment =NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:11:29
Finished backup at 19-JUN-08

Starting Control File and SPFILE Autobackup at 19-JUN-08
piece handle=/ddms02/dms/oracle/10.2.0-64/dbs/c-231561342-20080619-00 comment=NO NE
Finished Control File and SPFILE Autobackup at 19-JUN-08

DB

[oracle@RAC01 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 19 09:35:35 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/ddms01/dms/o01dms0suprt1/system_01.O01DMS0A
/ddms01/dms/o01dms0suprt1/undo_01.O01DMS0A
/ddms01/dms/o01dms0suprt1/sysaux_01.O01DMS0A
/ddms01/dms/o01dms0suprt1/sales_02.O01DMS0A
/ddms01/dms/o01dms0suprt1/ndx_02.O01DMS0A
/ddms01/dms/o01dms0suprt1/system_02.O01DMS0A
/ddms01/dms/o01dms0suprt1/business_01.O01DMS0A
/ddms01/dms/o01dms0suprt1/sales_01.O01DMS0A
/ddms01/dms/o01dms0suprt1/ndx_01.O01DMS0A
/ddms01/dms/o01dms0suprt1/ndx_03.O01DMS0A
/ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A

11 rows selected.

SQL> conn lyon
Enter password:
Connected.

SQL> create table aaa tablespace tools as select * from dba_users;

Table created.

SQL> commit;

Commit complete.

SQL> select count(*) from aaa;

COUNT(*)
----------
12

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

Move a datafile to emulate the loss of a datafile in Primary site.
[oracle@RAC01 ~]$ mv /ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A /ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A.bak

[oracle@RAC01 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 19 09:40:52 2008

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 683671552 bytes
Fixed Size 1221228 bytes
Variable Size 213912980 bytes
Database Buffers 457179136 bytes
Redo Buffers 11358208 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A'

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
[oracle@RAC01 ~]$ rman target / catalog

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 19 09:41:51 2008

connected to target database: O01DMS0A (DBID=231561342, not open)
connected to recovery catalog database

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

RMAN> run
{
set newname for datafile 11 to '/ddms01/dms/o01dms0suprt1/tools_01.O01DMS0A';
restore datafile 11;
recover datafile 11;
}2> 3> 4> 5> 6>

executing command: SET NEWNAME

Starting restore at 19-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=431 devtype=

Case One -- Recover a datafile in primary site from the backup taken in standby site using catalog d

相關文章