Data Guard's RMAN backup setup physical standby

1. General steps

The steps for creating a standby database through RMAN backup are roughly as follows:

1) set the primary database to force loggin Mode

2) set the primary database to archived log mode.

3) configure the parameter files for the master and slave databases.

4) The password files on both sides are consistent.

5) configure the listener

6) Start standby to nomount with the parameter file

7) completely back up the master database through rman

8) create a control file for standby using rman

9) use the rman dumplicate command to create a slave Database

10) after creation, the standby database is automatically started to the mount state, and redo apply is not automatically started.

Ii. Implement creation operations

Environment: primary database name DGWH, standby database name DGBJ, db_unique_name = DGWH

In the 10 steps I have summarized, the first 5 steps are the same as the conventional method. For more information, see my blog:

Http://5073392.blog.51cto.com/5063392/1299100

Theoretical knowledge:

Http://5073392.blog.51cto.com/5063392/1297346

Detailed configuration steps are provided, which are omitted here, starting from step 1.

Note that I have directly created the spfile file. After modifying the parameter file, you can use create spfile from pfile to create the file.

1) Use spfile on the slave database to start to nomount.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 297798460 bytes

Database Buffers 12582912 bytes

Redo Buffers 6365184 bytes

SQL>

2) Start the master database to the mount state, and then use rman to connect to the backup database.

RMAN> backup database include current controlfile for standby plus archivelog; // note that the standby control file is created while the database is backed up, and archive logs are included. It can also be split into backup database; copy current controlfile for standby to '/tmp/st. ctl'

Starting backup at 22-SEP-13

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID = 154 device type = DISK

Channel ORA_DISK_1: starting archived log backup set

Channel ORA_DISK_1: specifying archived log (s) in backup set

Input archived log thread = 1 sequence = 463 RECID = 917 STAMP = 826842432

Channel ORA_DISK_1: starting piece 1 at 22-SEP-13

Channel ORA_DISK_1: finished piece 1 at 22-SEP-13

Piece handle =/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/empty _. bkp tag = TAG20130922T224340 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-SEP-13

Starting backup at 22-SEP-13

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number = 00001 name =/u01/app/oracle/oradata/DGWH/datafile/o1_mf_system_9361jnkf _. dbf

Input datafile file number = 00002 name =/u01/app/oracle/oradata/DGWH/datafile/o1_mf_sysaux_9361jnkx _. dbf

Input datafile file number = 00003 name =/u01/app/oracle/oradata/DGWH/datafile/o1_mf_undotbs1_9361jnol _. dbf

Input datafile file number = 00004 name =/u01/app/oracle/oradata/DGWH/datafile/o1_mf_users_9361jnpq _. dbf

Channel ORA_DISK_1: starting piece 1 at 22-SEP-13

Channel ORA_DISK_1: finished piece 1 at 22-SEP-13

Piece handle =/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/empty _. bkp tag = TAG20130922T224346 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

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

Including current SPFILE in backup set

Channel ORA_DISK_1: starting piece 1 at 22-SEP-13

Channel ORA_DISK_1: finished piece 1 at 22-SEP-13

Piece handle =/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/empty _. bkp tag = TAG20130922T224346 comment = NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 // piece handle generates three backup slices to/u01/app/oracle/flash_recovery_area/DGWH/backupset

Finished backup at 22-SEP-13

Starting backup at 22-SEP-13

Using channel ORA_DISK_1

Specification does not match any archived log in the recovery catalog

Backup canceled because all files were skipped

Finished backup at 22-SEP-13

3) copy the backup set to the standby Database

Note: after several hours of delay, I always report the following errors. I understand that using rman to build a data file does not need to be copied as usual, so there is no manual copy of the Backup set to the slave database. I did not mention this step when I read many blogs written by famous people on the Internet. When the following error occurs, I check that the directory file does not exist, there is no problem with permissions. I started to think that there is a problem with the BACKUP command. I tried many times and still couldn't do it. I tried to copy the backup set to the same directory as the master database backup set, finally succeeded ....

Channel ORA_AUX_DISK_1: reading from backup piece/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_tag20130922t201%_93xqvgm9 _. bkp

Channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/empty _. bkp

ORA-19505: failed to identify file "/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_tag20130922t2011__93xqvgm9 _. bkp"

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

First, create/u01/app/oracle/flash_recovery_area/DGWH/backupset/directory using the oracle account on the slave database. If you use root, you need to modify the directory permission.

[Oracle @ localhost flash_recovery_area] mkdir-p DGWH/backupset

Execute the scp command in the master database to copy all the backup files to the slave database. You can find out which backup files are generated in step 1, or you can view them using the relevant command.

[Oracle @ localhost] cd/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22

[Oracle @ localhost 2013_09_22] $ scp * 192.168.31.3:/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22

Oracle@192.168.31.3's password:

O1_mf_annnn_TAG20130922T224340_93y0k0wl _. bkp 100% 480KB 479.5KB/s 00:00

O1_mf_ncsnf_tag20130922t2241__93y0n4ss _. bkp 100% 13 MB 12.6 MB/s

O1_mf_nnndf_tag20130922t2241__93y0k69f _. bkp 100% 1090 MB 2.6 MB/s

4) use rman to connect to the master and slave Databases

[Oracle @ localhost admin] $ rman target/auxiliary sys/oracle @ DGBJ // connect to both the master and slave Databases

Recovery Manager: Release 11.1.0.6.0-Production on Sun Sep 22 19:41:53 2013

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Connected to target database: DGWH (DBID = 773380365, not open) // The status of the master database is not open, that is, mount

Connected to auxiliary database: DGWH (not mounted) // The status of the standby database is not mounted, that is, nomout

RMAN>

If the Listener Configuration is incorrect, the following error is returned.

Connected to target database: DGWH (DBID = 773380365, not open)

RMAN-00571: ========================================================== ==============================

RMAN-00569: ==================== error message stack follows ==========================

RMAN-00571: ========================================================== ==============================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-12514: TNS: listener does not currently know of service requested in connect descriptor

RMAN> duplicate target database for standby; // use the duplicate command to restore

Starting Duplicate Db at 22-SEP-13

Using channel ORA_AUX_DISK_1

Contents of Memory Script:

{

Restore clone standby controlfile;

SQL clone 'alter database mount standby database ';

}

Executing Memory Script

Starting restore at 22-SEP-13

Channel ORA_AUX_DISK_1: starting datafile backup set restore

Channel ORA_AUX_DISK_1: restoring control file

Channel ORA_AUX_DISK_1: reading from backup piece/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_tag20130922t2241__93y0n4ss _. bkp

Channel ORA_AUX_DISK_1: piece handle =/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/empty _. bkp tag = TAG20130922T224346

Channel ORA_AUX_DISK_1: restored backup piece 1

Channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

Output file name =/u01/app/oracle/oradata/DGBJ/controlfile/DGBJ01.ctl // specify the path of the backup control file in the slave database, duplicate will automatically copy two copies to the corresponding path, so you must start to nomount to read the parameter files of the slave database.

Output file name =/u01/app/oracle/flash_recovery_area/DGBJ/controlfile/DGBJ02.ctl

Finished restore at 22-SEP-13

SQL statement: alter database mount standby database

Contents of Memory Script:

{

Set newname for tempfile 1

"/U01/app/oracle/oradata/DGBJ/datafile/o1_mf_temp_9361onbf _. tmp ";

Switch clone tempfile all;

Set newname for datafile 1

"/U01/app/oracle/oradata/DGBJ/datafile/o1_mf_system_9361jnkf _. dbf ";

Set newname for datafile 2

"/U01/app/oracle/oradata/DGBJ/datafile/o1_mf_sysaux_9361jnkx _. dbf ";

Set newname for datafile 3

"/U01/app/oracle/oradata/DGBJ/datafile/o1_mf_undotbs1_9361jnol _. dbf ";

Set newname for datafile 4

"/U01/app/oracle/oradata/DGBJ/datafile/o1_mf_users_9361jnpq _. dbf ";

Restore

Clone database

;

}

Executing Memory Script

Executing command: SET NEWNAME

Renamed tempfile 1 to/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_temp_9361onbf _. tmp in control file

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Starting restore at 22-SEP-13

Using channel ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: starting datafile backup set restore

Channel ORA_AUX_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_AUX_DISK_1: restoring datafile 00001 to/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_system_9361jnkf _. dbf

Channel ORA_AUX_DISK_1: restoring datafile 00002 to/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_sysaux_9361jx _. dbf

Channel ORA_AUX_DISK_1: restoring datafile 00003 to/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_undotbs1_9361jnol _. dbf

Channel ORA_AUX_DISK_1: restoring datafile 00004 to/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_users_9361jnpq _. dbf

Channel ORA_AUX_DISK_1: reading from backup piece/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_nnndf_tag20130922t2241__93y0k69f _. bkp

Channel ORA_AUX_DISK_1: piece handle =/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/empty _. bkp tag = TAG20130922T224346

Channel ORA_AUX_DISK_1: restored backup piece 1

Channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:11

Finished restore at 22-SEP-13

Contents of Memory Script:

{

Switch clone datafile all;

}

Executing Memory Script

Datafile 1 switched to datafile copy

Input datafile copy RECID = 9 STAMP = 826325100 file name =/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_system_93g65fcl _. dbf

Datafile 2 switched to datafile copy

Input datafile copy RECID = 10 STAMP = 826325100 file name =/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_sysaux_93g65fht _. dbf

Datafile 3 switched to datafile copy

Input datafile copy RECID = 11 STAMP = 826325100 file name =/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_undotbs1_93g65fo3 _. dbf

Datafile 4 switched to datafile copy

Input datafile copy RECID = 12 STAMP = 826325100 file name =/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_users_93g65fqj _. dbf

Finished Duplicate Db at 22-SEP-13

5) Build and verify

Create a test table for the master database:

SQL> create table test1 (id int );

Table created.

SQL> insert into test1 values (1 );

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered

Slave database Verification:

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby databaSe disconnect from session; // execute redo apply

Database altered.

SQL> select * from test1;

ID

----------

1

If synchronization fails, check whether the password files are consistent.

This article is from the "Step by Step" blog. For more information, contact the author!