Oracle DG備庫手動管理新增資料檔案

Purpose

Describe how the standby_file_management parameter can be used with raw devices.  In addition, describe how to recover from errors after they have occurred.

 

Using standby_file_management with Raw Devices

By setting the standby_file_management parameter to auto whenever new data files are added or dropped on the primary database the standby database will take the corresponding action.  This is true as long as the standby is utilizing a file system.  If the standby is utilizing raw devices for datafiles then the standby file management parameter will continue to work but manual intervention is needed.  This manual intervention involves assuring that the raw devices exist prior to the standby performing recovery of the redo that will create the new datafile.  Considering the example given below:

 

On the primary create a new tablespace where the datafile reside in a raw device.  At the same time create the same raw device on the standby database: 

 

SQL> create tablespace mts2 datafile '/dev/raw/raw100' size 1m; 

 

Tablespace created. 

 

SQL> alter system switch logfile; 

 

System altered.

 

The standby is able to automatically add the datafile as the raw devices exist.  The standby alert log shows: 

 

Fri Apr  8 09:49:31 2005 

Media Recovery Log /u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o1_mf_1_7_15ffgt0z_.arc 

Recovery created file /dev/raw/raw100 

Successfully added datafile 6 to media recovery 

Datafile #6: '/dev/raw/raw100' 

Media Recovery Waiting for thread 1 sequence 8 (in transit) 

 

However, if the raw device has been created on the primary but not on the standby then the MRP process will shutdown due to file creation errors.  Consider the following:

 

On the primary: 

 

SQL> create tablespace mts3 datafile '/dev/raw/raw101' size 1m; 

 

Tablespace created. 

 

SQL> alter system switch logfile; 

 

System altered. 

 

The standby host does not have the /dev/raw/raw101 raw device created.     The standby alert log shows the following when recovering the archive: 

 

Fri Apr  8 10:00:22 2005

Media Recovery Log /u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o1_mf_1_8_15ffjrov_.arc

File #7 added to control file as 'UNNAMED00007'.

Originally created as:

'/dev/raw/raw101'

Recovery was unable to create the file as:

'/dev/raw/raw101'

MRP0: Background Media Recovery terminated with error 1274

Fri Apr  8 10:00:22 2005

Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc:

ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created

ORA-01119: error in creating database fi  le '/dev/raw/raw101'

ORA-27041: unable to open file

Linux Error: 13: Permission denied

Additional information: 1

Some recovered datafiles maybe left media fuzzy

Media recovery may continue but open resetlogs may fail

Fri Apr  8 10:00:22 2005

Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc:

ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created

ORA-01119: error in creating database file '/dev/raw/raw101'

ORA-27041: unable to open file

Linux Error: 13: Permission denied

Additional information: 1

Fri Apr  8 10:00:22 2005

MTS; MRP0: Background Media Recovery process shutdown

ARCH: Connecting to console port...

 

Recovering From Errors

To correct the above issue you must first create the raw slice on the standby and assign permissions to the Oracle user.  Then perform the following steps: 

 

SQL> select name from v$datafile; 

 

NAME 

-------------------------------------------------------------------------------- 

/u01/MILLER/MTS/system01.dbf 

/u01/MILLER/MTS/undotbs01.dbf 

/u01/MILLER/MTS/sysaux01.dbf 

/u01/MILLER/MTS/users01.dbf 

/u01/MILLER/MTS/mts.dbf 

/dev/raw/raw100 

/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007 

 

SQL> alter system set standby_file_management=manual; 

 

SQL> alter database create datafile 

  2  '/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007' 

  3  as 

  4  '/dev/raw/raw101'; 

 

Within the standby alert log you should see the following: 

 

Fri Apr  8 10:09:30 2005 

alter database create datafile 

'/dev/raw/raw101' as '/dev/raw/raw101' 

Fri Apr  8 10:09:30 2005 

Completed: alter database create datafile 

'/dev/raw/raw101' a 

 

On the standby set standby_file_management to auto and restart managed recovery:

 

SQL> alter system set standby_file_management=auto; 

 

SQL> recover managed standby database disconnect;

 

At this point recovery will correctly use the new raw device datafile and recovery will continue.

 

Considerations

-             If raw device path names are not the same on the standby host as they are on the primary host you must use db_file_name_convert to convert the path names.

-         The above steps only work with a database that is not making use of Oracle Managed Files.