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  size100m;

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  size100m;

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恢复正常。