Oracle-Data-Guard之物理备库重命名数据文件

发表于 | 分类于

Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

On the Primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> select FILE#, NAME from v$datafile where file# = 9;
     FILE# NAME
---------- --------------------------------------------------
         9 /home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf
SQL> alter tablespace tab05 offline;
Tablespace altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
[oracle@hzvscmdb mmpdb3]$ mv /home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf /home/oracle/app/oracle/oradata/mmpdb3/tab05_tbs.dbf
SQL> alter tablespace tab05 rename datafile
  '/home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf' to
  '/home/oracle/app/oracle/oradata/mmpdb3/tab05_tbs.dbf';
Tablespace altered.
SQL> select FILE#, NAME from v$datafile where file# = 9;
     FILE# NAME
---------- ------------------------------------------------------------
         9 /home/oracle/app/oracle/oradata/mmpdb3/tab05_tbs.dbf
SQL> alter system switch logfile;
System altered.

On the Standby

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> select FILE#, NAME from v$datafile where file# = 9;
     FILE# NAME
---------- --------------------------------------------------
         9 /home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> alter tablespace tab05 rename datafile
  '/home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf' to
  '/home/oracle/app/oracle/oradata/mmpdb3/tab05_tbs.dbf';  2    3
alter tablespace tab05 rename datafile
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database rename file '/home/oracle/app/oracle/oradata/mmpdb3/tab05.dbf' to '/home/oracle/app/oracle/oradata/mmpdb3/tab05_tbs.dbf';
Database altered.
SQL> select FILE#, NAME from v$datafile where file# = 9;
     FILE# NAME
---------- --------------------------------------------------
         9 /home/oracle/app/oracle/oradata/mmpdb3/tab05_tbs.d
           bf
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  2> DISCONNECT FROM SESSION;

On the Primary and Standby

1
2
3
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.

If you do not rename the corresponding datafile at the standby system, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:

1
2
3
ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock datafile 4 - see DBWR trace file
ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf'