Oracle 11g R2之物理Dataguard 重命名数据文件

当STANDBY_FILE_MANAGEMENT = AUTO时，如果主库primary新建一个datafile，备库standby是会自动同步新建一个datafile；但是当主库primary 重命名一个datafile时，尽管STANDBY_FILE_MANAGEMENT 设置为AUTO，备库standby也不会同步，因此需要dba手动修改；

1.查询当前数据文件

SQL> select name from v$datafile;

NAME

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

/u01/oracle/oradata/orcl/system01.dbf

/u01/oracle/oradata/orcl/sysaux01.dbf

/u01/oracle/oradata/orcl/undotbs01.dbf

/u01/oracle/oradata/orcl/users01.dbf

/u01/oracle/oradata/orcl/test.dbf

6 rows selected.

2.查询表空间名称

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

TEST

7 rows selected.

3.在主库primary上rename的tablespace offline

SQL> alter tablespace test offline;

Tablespace altered.

4.在主库primary使用mv命令对datafile重命名

[oracle@oracle ~]$ cd /u01/oracle/oradata/orcl/

[oracle@oracle orcl]$ ls |grep test

test.dbf

[oracle@oracle orcl]$ mv test.dbf test01.dbf

[oracle@oracle orcl]$ ls |grep test

test01.dbf

5.修改数据库层面上的datafile,并使其online

SQL>  alter tablespace test rename datafile '/u01/oracle/oradata/orcl/test.dbf' to '/u01/oracle/oradata/orcl/test01.dbf';

Tablespace altered.

SQL> alter tablespace test online;

Tablespace altered.

6.主库primary切换logfile并查看数据文件

SQL> alter system switch logfile;

System altered.

SQL> select name from v$datafile;

NAME

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

/u01/oracle/oradata/orcl/system01.dbf

/u01/oracle/oradata/orcl/sysaux01.dbf

/u01/oracle/oradata/orcl/undotbs01.dbf

/u01/oracle/oradata/orcl/users01.dbf

/u01/oracle/oradata/orcl/test01.dbf

6 rows selected.

7.查看备库standby上数据文件，发现并没有自动同步过来

SQL> select name from v$datafile;

NAME

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

/u01/oracle/oradata/dg/system01.dbf

/u01/oracle/oradata/dg/sysaux01.dbf

/u01/oracle/oradata/dg/undotbs01.dbf

/u01/oracle/oradata/dg/users01.dbf

/u01/oracle/oradata/dg/test.dbf

6 rows selected.

8.停掉备库日志应用，并关闭备库standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

9.在备库standby使用mv命令对datafile重命名

[oracle@dg dg]$ cd /u01/oracle/oradata/dg

[oracle@dg dg]$ ls |grep test

test.dbf

[oracle@dg dg]$ mv test.dbf test01.dbf

[oracle@dg dg]$ ls |grep test

test01.dbf

10.mount备库standby

SQL> startup mount

ORACLE instance started.

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             499125816 bytes

Database Buffers          281018368 bytes

Redo Buffers                2596864 bytes

Database mounted.

11.修改数据库层面上的datafile(会报错)

SQL> alter database rename file '/u01/oracle/oradata/dg/test.dbf' to '/u01/oracle/oradata/dg/test01.dbf';

alter database rename file '/u01/oracle/oradata/dg/test.dbf' to '/u01/oracle/oradata/dg/test01.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.

可以发现如果STANDBY_FILE_MANAGEMENT = AUTO时是无法rename备库standby中的datafile

12.修改初始化参数文件

SQL> show parameter standby

NAME                                 TYPE        VALUE

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

standby_archive_dest                 string      ?/dbs/arch

standby_file_management              string      AUTO

SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> show parameter standby

NAME                                 TYPE        VALUE

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

standby_archive_dest                 string      ?/dbs/arch

standby_file_management              string      MANUAL

13.重新修改数据库层面上的datafile

SQL> alter database rename file '/u01/oracle/oradata/dg/test.dbf' to '/u01/oracle/oradata/dg/test01.dbf';

Database altered.

14.将初始化参数文件改回

SQL> alter system set standby_file_management=AUTO;

System altered.

SQL> show parameter standby

NAME                                 TYPE        VALUE

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

standby_archive_dest                 string      ?/dbs/arch

standby_file_management              string      AUTO

15.查询备库standby的datafile是否修改过来

SQL> select name from v$datafile;

NAME

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

/u01/oracle/oradata/dg/system01.dbf

/u01/oracle/oradata/dg/sysaux01.dbf

/u01/oracle/oradata/dg/undotbs01.dbf

/u01/oracle/oradata/dg/users01.dbf

/u01/oracle/oradata/dg/test01.dbf

16.备库standby重新应用日志

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

Database altered.

查看主备库日志是否一致

17.主库primary

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/archive/orcl

Oldest online log sequence     38

Next log sequence to archive   40

Current log sequence           40

18.备库standby

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED

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

        18 YES

        19 YES

        20 YES

        21 YES

        22 YES

        23 YES

        24 YES

        25 YES

        26 YES

        27 YES

        28 YES

 SEQUENCE# APPLIED

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

        29 YES

        30 YES

        31 YES

        32 YES

        33 YES

        34 YES

        35 YES

        36 YES

        37 YES

        38 YES

        39 YES

22 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29812844/viewspace-1988737/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29812844/viewspace-1988737/