oracle rename dg,Oracle 11g R2之物理Dataguard 重命名数据文件

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

1.查询当前数据文件

SQL>selectnamefromv$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

6rows selected.

2.查询表空间名称

SQL>selecttablespace_namefromdba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

TEST

7rows selected.

3.在主库primary上rename的tablespace offline

SQL>altertablespace 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>altertablespace test rename datafile'/u01/oracle/oradata/orcl/test.dbf'to'/u01/oracle/oradata/orcl/test01.dbf';

Tablespace altered.

SQL>altertablespace test online;

Tablespace altered.

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

SQL>altersystem switch logfile;

System altered.

SQL>selectnamefromv$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

6rows selected.

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

SQL>selectnamefromv$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

6rows selected.

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

SQL>alterdatabaserecover managed standbydatabasecancel;

Databasealtered.

SQL>shutdownimmediate

Databaseclosed.

Databasedismounted.

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 Area784998400bytes

Fixed Size2257352bytes

Variable Size499125816bytes

DatabaseBuffers281018368bytes

Redo Buffers2596864bytes

Databasemounted.

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

SQL>alterdatabaserenamefile'/u01/oracle/oradata/dg/test.dbf'to'/u01/oracle/oradata/dg/test01.dbf';

alterdatabaserenamefile'/u01/oracle/oradata/dg/test.dbf'to'/u01/oracle/oradata/dg/test01.dbf'

*

ERROR at line1:

ORA-01511: errorinrenaminglog/data

files

ORA-01275: Operation RENAMEisnotallowedifstandbyfilemanagementis

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>altersystemsetstandby_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>alterdatabaserenamefile'/u01/oracle/oradata/dg/test.dbf'to'/u01/oracle/oradata/dg/test01.dbf';

Databasealtered.

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

SQL>altersystemsetstandby_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>selectnamefromv$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>alterdatabaserecover managed standbydatabasedisconnectfromsession;

Databasealtered.

查看主备库日志是否一致

17.主库primary

SQL>archiveloglist

Databaselogmode              Archive Mode

Automatic archival

Enabled

Archive destination/u01/archive/orcl

Oldest onlinelogsequence38

Nextlogsequencetoarchive40

Currentlogsequence40

18.备库standby

SQL>selectsequence#,appliedfromv$archived_log;

SEQUENCE# APPLIED

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

18YES

19YES

20YES

21YES

22YES

23YES

24YES

25YES

26YES

27YES

28YES

SEQUENCE# APPLIED

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

29YES

30YES

31YES

32YES

33YES

34YES

35YES

36YES

37YES

38YES

39YES

22rows selected.