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.