dg环境中standby_file_management参数分析

    关于dg环境的standby_file_management参数，oracle解释是主库数据文件变化时，从库自动更新

    下面来测试一下standby_file_management参数和数据文件变化的关系吧。

　　接下来主库删除表空间

　　SQL> drop tablespace dbaxiaoyu including contents and datafiles;

　　Tablespace dropped.

　　SQL> alter system switch logfile;

　　System altered.

　　SQL> select name from v$tablespace where name='DBAXIAOYU';

　　no rows selected

　　备库下

　　SQL> show parameter standby_file_management;

　　NAME TYPE VALUE

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

　　standby_file_management string AUTO

　　SQL> select name from v$tablespace where name='DBAXIAOYU';

　　no rows selected

　　standby_file_management=auto下主库删除表空间，备库自动删除，其实在manual下备库也会自动更新，下面有测试，不过增加数据文件就不同了。

　　备库修改参数standby_file_management=manual

　　SQL> alter system set standby_file_management='manual';

　　System altered

　　主库增加表空间

　　SQL> create tablespace xiaoyu datafile '/db/oracle/oracle/oradata/xiaoyu/xiaoyu01.dbf' size 50m;

　　Tablespace created.

　　SQL> alter system switch logfile;

　　System altered.

　　备库下查看数据文件名称有点奇怪

　　SQL> select ts#,name from v$tablespace where name='XIAOYU';

　　TS# NAME

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

　　5 XIAOYU

　　SQL> select name from v$datafile where ts#=5;

　　NAME

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

　　/db/oracle/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

　　备库的alert日志：

　　Thu Sep 20 03:52:10 2012

　　RFS[2]: Archived Log: '/backup/1_81_794196200.dbf'

　　Primary database is in MAXIMUM PERFORMANCE mode

　　RFS[2]: No standby redo logfiles created

　　Thu Sep 20 03:52:13 2012

　　Media Recovery Log /backup/1_81_794196200.dbf

　　File #5 added to control file as 'UNNAMED00005' because

　　the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL

　　The file should be manually created to continue.

　　Errors with log /backup/1_81_794196200.dbf

　　MRP0: Background Media Recovery terminated with error 1274

　　Thu Sep 20 03:52:13 2012

　　Errors in file /db/oracle/oracle/admin/standby/bdump/standby_mrp0_4637.trc:

　　ORA-01274: cannot add datafile '/db/oracle/oracle/oradata/xiaoyu/xiaoyu01.dbf' - file could not be created

　　Some recovered datafiles maybe left media fuzzy

　　Media recovery may continue but open resetlogs may fail

　　Thu Sep 20 03:52:13 2012

　　Errors in file /db/oracle/oracle/admin/standby/bdump/standby_mrp0_4637.trc:

　　ORA-01274: cannot add datafile '/db/oracle/oracle/oradata/xiaoyu/xiaoyu01.dbf' - file could not be created

　　Thu Sep 20 03:52:13 2012

　　MRP0: Background Media Recovery process shutdown (standby)

　看出此时由于数据文件无法获取，MRP进程已经关闭,手动修改下控制文件中的路径。

　　SQL> alter database create datafile '/db/oracle/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' as '/db/oracle/oracle/oradata/standby/xiaoyu01.dbf';

　　Database altered.

　　SQL> select name from v$datafile where ts#=5;

　　NAME

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

　　/db/oracle/oracle/oradata/standby/xiaoyu01.dbf

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

　　Database altered.

　　主库删除xiaoyu表空间

　　SQL> drop tablespace xiaoyu including contents and datafiles;

　　Tablespace dropped.

　　SQL> alter system switch logfile;

　　System altered.

　　备库查询也被删除了，此时即使standby_file_management=manual

　　SQL> select ts#,name from v$tablespace where name='XIAOYU';

　　no rows selected

　

　 rename数据文件

　　SQL> alter tablespace xiaoyu offline;

　　Tablespace altered.

　　oracle@primary xiaoyu]$ mv xiaoyu001.dbf dbaxiaoyu001.dbf

　　SQL> alter tablespace xiaoyu rename datafile '/db/oracle/oracle/oradata/xiaoyu/xiaoyu001.dbf' to '/db/oracle/oracle/oradata/xiaoyu/dbaxiaoyu001.dbf';

　　Tablespace altered.

　　SQL> alter tablespace xiaoyu online;

　　Tablespace altered.

　　SQL> alter system switch logfile;

　　System altered.

　　备库下数据文件并没有自动rename，需要手动rename

　　SQL> select name from v$datafile where ts#=5;

　　NAME

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

　　/db/oracle/oracle/oradata/standby/xiaoyu01.dbf

　　SQL> alter database rename file '/db/oracle/oracle/oradata/standby/xiaoyu001.dbf' to '/db/oracle/oracle/oradata/standby/dbaxiaoyu001.dbf';

　　alter database rename file '/db/oracle/oracle/oradata/standby/xiaoyu001.dbf' to '/db/oracle/oracle/oradata/standby/dbaxiaoyu001.dbf'

　　*

　　ERROR at line 1:

　　ORA-01156: recovery in progress may need access to files

　　需要先关闭redo应用

　　SQL> alter database recover managed standby database cancel;

　　Database altered.

　　SQL> alter database rename file '/db/oracle/oracle/oradata/standby/xiaoyu001.dbf' to '/db/oracle/oracle/oradata/standby/dbaxiaoyu001.dbf';

　　alter database rename file '/db/oracle/oracle/oradata/standby/xiaoyu001.dbf' to '/db/oracle/oracle/oradata/standby/dbaxiaoyu001.dbf'

　　*

　　ERROR at line 1:

　　ORA-01511: error in renaming log/data files

　　ORA-01275: Operation RENAME is not allowed if standby file management is

　　automatic.

　　SQL> alter system set standby_file_management='manual';

　　System altered.

　　看来还要设置standby_file_management=manual，才能手动rename数据文件。

　　SQL> alter database rename file '/db/oracle/oracle/oradata/standby/xiaoyu001.dbf' to '/db/oracle/oracle/oradata/standby/dbaxiaoyu001.dbf';

　　Database altered

（对于tts传输的表空间和裸设备管理的数据文件同样需要手动管理，裸设备下必须设置standby_file_management=manual）