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

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

--查询当前数据文件
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.

--查询表空间名称
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

7 rows selected.

--在主库primary上rename的tablespace offline
SQL> alter tablespace test offline;

Tablespace altered.

--在主库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
[oracle@oracle orcl]$

--修改数据库层面上的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.

--主库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.

--查看备库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.

--停掉备库日志应用，并关闭备库standby
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--在备库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

--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.

--修改数据库层面上的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

--修改初始化参数文件
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

--重新修改数据库层面上的datafile
SQL> alter database rename file '/u01/oracle/oradata/dg/test.dbf' to '/u01/oracle/oradata/dg/test01.dbf';

Database altered.

--将初始化参数文件改回
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

--查询备库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

--备库standby重新应用日志
SQL> alter database recover managed standby database disconnect from session;

Database altered.


--查看主备库日志是否一致
--主库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

--备库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.

<script type="text/javascript"> $(function () { $('pre.prettyprint code').each(function () { var lines = $(this).text().split('\n').length; var $numbering = $('<ul/>').addClass('pre-numbering').hide(); $(this).addClass('has-numbering').parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($('<li/>').text(i)); }; $numbering.fadeIn(1700); }); }); </script>

版权声明：本文为博主原创文章，未经博主允许不得转载。