oracle 物理dataguard rename数据文件实例
我们知道当STANDBY_FILE_MANAGEMENT 设置为AUTO时，如果primary库新建一个datafile，standby是会自动同步新建一个datafile；但当primary rename一个datafile时，尽管STANDBY_FILE_MANAGEMENT 设置为AUTO，standby也不会同步，此时需要手动修改；
做如下实验来验证，将ZYH表空间的datafile重命名为/u01/oracle/oradata/standby/zyh02.dbf
| 代码如下 | 复制代码 |
|
01 SQL> select name from v$datafile; 02 03 NAME 04 -------------------------------------------------------------------------------- 05 /u01/oracle/oradata/standby/system01.dbf 06 /u01/oracle/oradata/standby/undotbs01.dbf 07 /u01/oracle/oradata/standby/sysaux01.dbf 08 /u01/oracle/oradata/standby/users01.dbf 09 /u01/oracle/oradata/standby/mgmt.dbf 10 /u01/oracle/oradata/standby/mgmt_ecm_depot1.dbf 11 /u01/oracle/oradata/standby/zyh01.dbf 12 13 7 rows selected. 14 15 SQL> select tablespace_name from dba_tablespaces; 16 17 TABLESPACE_NAME 18 ------------------------------ 19 SYSTEM 20 UNDOTBS1 21 SYSAUX 22 TEMP 23 USERS 24 MGMT_TABLESPACE 25 MGMT_ECM_DEPOT_TS 26 ZYH 27 28 8 rows selected. 29 30 SQL> |
|
1、在primary上，将rename的tablespace离线
| 代码如下 | 复制代码 |
|
1 SQL> alter tablespace zyh offline; 2 3 Tablespace altered. 2 3 Tablespace altered. 4 5 SQL> alter tablespace zyh online; 6 7 Tablespace altered. |
|
4、主库切换日志，查看数据文件的位置
| 代码如下 | 复制代码 |
|
01 SQL> alter system switch logfile; 02 03 System altered. 04 05 SQL> select name from v$datafile; 06 07 NAME 08 -------------------------------------------------------------------------------- 09 /u01/oracle/oradata/standby/system01.dbf 10 /u01/oracle/oradata/standby/undotbs01.dbf 11 /u01/oracle/oradata/standby/sysaux01.dbf 12 /u01/oracle/oradata/standby/users01.dbf 13 /u01/oracle/oradata/standby/mgmt.dbf 14 /u01/oracle/oradata/standby/mgmt_ecm_depot1.dbf 15 /u01/oracle/oradata/standby/zyh02.dbf 16 17 7 rows selected. |
|
备库上进行查询，发现datafile还是zyh01.dbf，说明备库没有自动同步
| 代码如下 | 复制代码 |
|
01 SQL> select name from v$datafile; 02 03 NAME 04 -------------------------------------------------------------------------------- 05 /u01/oracle/oradata/spdb/system01.dbf 06 /u01/oracle/oradata/spdb/undotbs01.dbf 07 /u01/oracle/oradata/spdb/sysaux01.dbf 08 /u01/oracle/oradata/spdb/users01.dbf 09 /u01/oracle/oradata/spdb/mgmt.dbf 10 /u01/oracle/oradata/spdb/mgmt_ecm_depot1.dbf 11 /u01/oracle/oradata/spdb/zyh01.dbf 12 13 7 rows selected. |
|
5、停掉备库日志应用，并关闭备库
| 代码如下 | 复制代码 |
|
1 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 2 3 Database altered. 4 5 SQL> shutdown immediate; 6 ORA-01109: database not open 7 8 Database dismounted. 9 ORACLE instance shut down. 2 ORACLE instance started. 3 4 Total System Global Area 285212672 bytes 5 Fixed Size 1218992 bytes 6 Variable Size 92276304 bytes 7 Database Buffers 188743680 bytes 8 Redo Buffers 2973696 bytes 9 Database mounted. |
|
8、将STANDBY_FILE_MANAGEMENT 参数设置为MANUAL，并在数据库层面将datafile 重命名
| 代码如下 | 复制代码 |
|
01 SQL> show parameter STANDBY_FILE_MANAGEMENT 02 03 NAME TYPE VALUE 04 ------------------------------------ ----------- ------------------------------ 05 standby_file_management string AUTO 06 SQL> alter system set standby_file_management=MANUAL; 07 08 System altered. 09 10 SQL> alter database rename file '/u01/oracle/oradata/spdb/zyh01.dbf' to '/u01/oracle/oradata/spdb/zyh02.dbf'; 11 12 Database altered. |
|
9、重新应用日志
| 代码如下 | 复制代码 |
|
1 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 2 3 Database altered. |
|
10、查看两边日志是否同步
主库：
| 代码如下 | 复制代码 |
|
1 SQL> archive log list 2 Database log mode Archive Mode 3 Automatic archival Enabled 4 Archive destination /u01/oracle/standby 5 Oldest online log sequence 209 6 Next log sequence to archive 211 7 Current log sequence 211 |
|
备库：
| 代码如下 | 复制代码 |
|
01 SQL> select sequence#,applied from v$archived_log; 02 03 SEQUENCE# APP 04 ---------- --- 05 197 YES 06 196 YES 07 198 YES 08 199 YES 09 200 YES 10 201 YES 11 202 YES 12 203 YES 13 204 YES 14 205 YES 15 206 YES 16 17 SEQUENCE# APP 18 ---------- --- 19 207 YES 20 208 YES 21 209 YES 22 210 YES 23 24 15 rows selected. |
|
11、将STANDBY_FILE_MANAGEMENT 参数改为auto
| 代码如下 | 复制代码 |
|
2 3 System altered |
|
