DATA GUARD手工管理資料檔案

一般情況下，會採用自動管理standby資料庫檔案檔案的方式，但是有時候會採用手工方式管理，比如standby資料庫使用裸裝置的情況。

看一個例子：

SQL> select name, open_mode, database_role, db_unique_name
  2  from v$database;

NAME                           OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME
------------------------------ ---------- ---------------- ------------------------------
PRIMARY                        READ WRITE PRIMARY          primary

SQL> select name from v$datafile;

NAME
----------------------------------------
/data/oradata/primary/system01.dbf
/data/oradata/primary/undotbs01.dbf
/data/oradata/primary/sysaux01.dbf
/data/oradata/primary/users01.dbf
/data/oradata/primary/test01.dbf

檢查standby資料庫檔案資訊：

SQL> select name, open_mode, database_role, db_unique_name
  2  from v$database;

NAME                           OPEN_MODE  DATABASE_ROLE    DB_UNIQUE_NAME
------------------------------ ---------- ---------------- ------------------------------
PRIMARY                        MOUNTED    PHYSICAL STANDBY standby

SQL> select name from v$datafile;

NAME
-----------------------------------
/data/oradata/standby/system01.dbf
/data/oradata/standby/undotbs01.dbf
/data/oradata/standby/sysaux01.dbf
/data/oradata/standby/users01.dbf
/data/oradata/standby/test01.dbf

SQL> show parameter standby_file

NAME                            TYPE        VALUE
------------------------------- ----------- ------------------------------
standby_file_management         string      AUTO
SQL> show parameter convert

NAME                            TYPE        VALUE
------------------------------- ----------- ------------------------------
db_file_name_convert            string      /data/oradata/primary, /data/oradata/standby
log_file_name_convert           string      /data/oradata/primary, /data/oradata/standby

雖然主庫和備庫的檔案路徑不一致，但是standby資料庫配置了file_name_convert引數，Oracle可以根據主庫的名稱自動建立備庫的資料檔案：

SQL> create tablespace new         
  2  datafile '/data/oradata/primary/new01.dbf'
  3  size 100m;

Tablespace created.

SQL> alter system switch logfile;

System altered.

檢查備庫的資料檔案新增情況：

SQL> select name from v$datafile;

NAME
----------------------------------------
/data/oradata/standby/system01.dbf
/data/oradata/standby/undotbs01.dbf
/data/oradata/standby/sysaux01.dbf
/data/oradata/standby/users01.dbf
/data/oradata/standby/test01.dbf
/data/oradata/standby/new01.dbf

6 rows selected.

下面將standby資料庫的資料檔案管理設定手工狀態：

SQL> alter system set standby_file_management = manual;

System altered.

這時主庫增加新的資料檔案：

SQL> alter tablespace new   
  2  add datafile '/data/oradata/primary/new02.dbf'
  3  size 100m;

Tablespace altered.

SQL> alter system switch logfile;

System altered.

檢查standby資料庫的資料檔案資訊：

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/data/oradata/standby/system01.dbf
/data/oradata/standby/undotbs01.dbf
/data/oradata/standby/sysaux01.dbf
/data/oradata/standby/users01.dbf
/data/oradata/standby/test01.dbf
/data/oradata/standby/new01.dbf
/opt/ora10g/product/10.2.0/db_1/dbs/UNNAMED00007

7 rows selected.

檢查alert檔案可以看到：

Fri Dec 24 05:34:35 2010
RFS[6]: Archived Log: '/data/oradata/standby/archivelog/1_15_737020478.dbf'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: No standby redo logfiles created
Fri Dec 24 05:34:35 2010
Media Recovery Log /data/oradata/standby/archivelog/1_15_737020478.dbf
Recovery created file /data/oradata/standby/new01.dbf
Successfully added datafile 6 to media recovery
Datafile #6: '/data/oradata/standby/new01.dbf'
Media Recovery Waiting for thread 1 sequence 16 (in transit)
Fri Dec 24 05:35:26 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=MEMORY;
Fri Dec 24 05:36:16 2010
RFS[6]: Archived Log: '/data/oradata/standby/archivelog/1_16_737020478.dbf'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: No standby redo logfiles created
Fri Dec 24 05:36:16 2010
Media Recovery Log /data/oradata/standby/archivelog/1_16_737020478.dbf
File #7 added to control file as 'UNNAMED00007' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /data/oradata/standby/archivelog/1_16_737020478.dbf
MRP0: Background Media Recovery terminated with error 1274
Fri Dec 24 05:36:16 2010
Errors in file /opt/ora10g/admin/standby/bdump/standby_mrp0_17670.trc:
ORA-01274: cannot add datafile '/data/oradata/primary/new02.dbf' - file could not be created
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Fri Dec 24 05:36:17 2010
Errors in file /opt/ora10g/admin/standby/bdump/standby_mrp0_17670.trc:
ORA-01274: cannot add datafile '/data/oradata/primary/new02.dbf' - file could not be created
Fri Dec 24 05:36:17 2010
MRP0: Background Media Recovery process shutdown (standby)

可以看到，由於需要手工介入建立資料檔案，Oracle自動停止了恢復過程。

下面透過手工方式新增新的資料檔案：

SQL> alter database create datafile 
  2  '/opt/ora10g/product/10.2.0/db_1/dbs/UNNAMED00007'
  3  as '/data/oradata/standby/new02.db';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/data/oradata/standby/system01.dbf
/data/oradata/standby/undotbs01.dbf
/data/oradata/standby/sysaux01.dbf
/data/oradata/standby/users01.dbf
/data/oradata/standby/test01.dbf
/data/oradata/standby/new01.dbf
/data/oradata/standby/new02.db

7 rows selected.

SQL> alter database recover managed standby database disconnect from session;

啟動standby資料庫的恢復：

Fri Dec 24 05:49:16 2010
alter database create datafile
'/opt/ora10g/product/10.2.0/db_1/dbs/UNNAMED00007'
as '/data/oradata/standby/new02.db'
Fri Dec 24 05:49:17 2010
Completed: alter database create datafile
'/opt/ora10g/product/10.2.0/db_1/dbs/UNNAMED00007'
as '/data/oradata/standby/new02.db'
Fri Dec 24 05:51:22 2010
alter database recover managed standby database disconnect from session
Fri Dec 24 05:51:22 2010
Attempt to start background Managed Standby Recovery process (standby)
MRP0 started with pid=18, OS id=22218
Fri Dec 24 05:51:22 2010
MRP0: Background Managed Standby Recovery process started (standby)
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 7 processes
Media Recovery Log /data/oradata/standby/archivelog/1_16_737020478.dbf
Media Recovery Waiting for thread 1 sequence 17 (in transit)
Fri Dec 24 05:51:28 2010
Completed: alter database recover managed standby database disconnect from session

從日誌中可以看到，STANDBY恢復正常。