Oracle 11gR2 Active Data Guard调整案例[1]
客户的环境是Oracle RAC Database 11.2.0.3 for Linux x86_64bit+Oracle Database 11.2.0.3 for Linux x86_64bit（ADG备库），RAC使用的是ASM，备库使用的是文件系统。客户在RAC主库为SYSTEM表空间添加了一个数据文件，由于脚本有问题该数据文件被放在了node 1的本地文件系统上，导致node 2的数据库实例宕掉，这一过程被成功的同步到了备用库，备用库同步的该文件对应的文件系统空间不足，同样需要调整其位置，通过下面的步骤模拟出现问题及解决问题的步骤。
一.模拟误操作过程。
1.主库操作：
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DBFILE1
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DBFILE1/ractest/datafile/users.259.754173059
+DBFILE1/ractest/datafile/undotbs1.258.754173059
+DBFILE1/ractest/datafile/sysaux.257.754173057
+DBFILE1/ractest/datafile/system.256.754173057
+DBFILE1/ractest/datafile/undotbs2.264.754173315
+DBFILE1/ractest/datafile/soe.274.686330585
+DBFILE1/ractest/datafile/liubinglin.276.786500233
+DBFILE1/ractest/datafile/liubinglin.268.805074155
8 rows selected.
当前RAC的所有数据文件都存放在ASM磁盘组中。
1).模拟错误的为SYSTEM表空间添加一个数据文件。
SQL> alter tablespace system add datafile 'system02.dbf' size 5m;
Tablespace altered.
客户在为SYSETM表空间添加system02.dbf数据文件的时候并未指定ASM磁盘组的名称。
/××××××××××××××××××××××××××××××××××××××××××××/
注意：
由于指定了db_create_file_dest参数，那么datafile后面可以不跟任何参数，会在db_create_file_dest参数指定的位置创建相应的文件。例如：
SQL> create tablespace t1 datafile size 5m;
Tablespace created.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DBFILE1/ractest/datafile/users.259.754173059
+DBFILE1/ractest/datafile/undotbs1.258.754173059
+DBFILE1/ractest/datafile/sysaux.257.754173057
+DBFILE1/ractest/datafile/system.256.754173057
+DBFILE1/ractest/datafile/undotbs2.264.754173315
+DBFILE1/ractest/datafile/soe.274.686330585
+DBFILE1/ractest/datafile/liubinglin.276.786500233
+DBFILE1/ractest/datafile/liubinglin.268.805074155
+DBFILE1/ractest/datafile/t1.279.814356763
10 rows selected.
/××××××××××××××××××××××××××××××××××××××××××××/
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DBFILE1/ractest/datafile/users.259.754173059
+DBFILE1/ractest/datafile/undotbs1.258.754173059
+DBFILE1/ractest/datafile/sysaux.257.754173057
+DBFILE1/ractest/datafile/system.256.754173057
+DBFILE1/ractest/datafile/undotbs2.264.754173315
+DBFILE1/ractest/datafile/soe.274.686330585
+DBFILE1/ractest/datafile/liubinglin.276.786500233
+DBFILE1/ractest/datafile/liubinglin.268.805074155
/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
9 rows selected.
由于未指定磁盘组的名称，导致文件被创建到本地文件系统的$ORACLE_HOME/dbs默认目录下。
备库操作：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ractestusers.259.754173059
/u01/app/oracle/oradata/ractestundotbs1.258.754173059
/u01/app/oracle/oradata/ractestsysaux.257.754173057
/u01/app/oracle/oradata/ractestsystem.256.754173057
/u01/app/oracle/oradata/ractestundotbs2.264.754173315
/u01/app/oracle/oradata/ractestsoe.274.686330585
/u01/app/oracle/oradata/ractestliubinglin.276.786500233
/u01/app/oracle/oradata/ractestliubinglin.268.805074155
/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
9 rows selected.
在主库的操作被成功的同步到了备库。
二.问题处理过程。
执行上面的步骤后数据库存在两个问题，由于在主库为SYSTEM表空间添加的数据文件RAC其他实例无法访问导致实例无法正常的工作。在备用库上，由于/u01文件系统空间较小，所以必须将system02.dbf移动到其他目录下。下面讨论这两个问题的处理过程。
主库操作：
1).停止RAC所有的数据库实例。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2).将system02.dbf数据文件拷贝到ASM磁盘组中。
[root@rhel1 bin]# su - grid
[grid@rhel1 ~]$ asmcmd -p
ASMCMD [+] > cp /u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf +DBFILE1/ractest/datafile/
copying /u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf -> +DBFILE1/ractest/datafile/system02.dbf
ASMCMD [+] > cd +DBFILE1/ractest/datafile/
ASMCMD [+DBFILE1/ractest/datafile] > ls
LIUBINGLIN.268.805074155
LIUBINGLIN.276.786500233
SOE.274.686330585
SYSAUX.257.754173057
SYSTEM.256.754173057
UNDOTBS1.258.754173059
UNDOTBS2.264.754173315
USERS.259.754173059
XIAOYANG.275.786499073
system02.dbf
3).重命名system02.dbf的位置。
ASMCMD [+DBFILE1/ractest/datafile] > exit
[grid@rhel1 ~]$ exit
logout
[root@rhel1 bin]# su - oracle
[oracle@rhel1 ~]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 2 09:18:12 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2230600 bytes
Variable Size 486540984 bytes
Database Buffers 289406976 bytes
Redo Buffers 6819840 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DBFILE1/ractest/datafile/system.256.754173057
+DBFILE1/ractest/datafile/sysaux.257.754173057
+DBFILE1/ractest/datafile/undotbs1.258.754173059
+DBFILE1/ractest/datafile/users.259.754173059
+DBFILE1/ractest/datafile/undotbs2.264.754173315
+DBFILE1/ractest/datafile/soe.274.686330585
+DBFILE1/ractest/datafile/liubinglin.276.786500233
+DBFILE1/ractest/datafile/liubinglin.268.805074155
/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
9 rows selected.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '+DBFILE1/ractest/datafile/system02.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DBFILE1/ractest/datafile/users.259.754173059
+DBFILE1/ractest/datafile/undotbs1.258.754173059
+DBFILE1/ractest/datafile/sysaux.257.754173057
+DBFILE1/ractest/datafile/system.256.754173057
+DBFILE1/ractest/datafile/undotbs2.264.754173315
+DBFILE1/ractest/datafile/soe.274.686330585
+DBFILE1/ractest/datafile/liubinglin.276.786500233
+DBFILE1/ractest/datafile/liubinglin.268.805074155
+DBFILE1/ractest/datafile/system02.dbf
9 rows selected.
完成之后，将RAC其他实例启动起来。
备用库操作：
1).停止备库数据库实例。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2).移动数据文件位置。
[oracle@RedHat5 ~]$ mv /u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf /u01/app/oracle/oradata/system02.dbf
3).重命名system02.dbf位置。
[oracle@redhat5 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 2 01:28:22 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 251660392 bytes
Database Buffers 54525952 bytes
Redo Buffers 4747264 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ractestsystem.256.754173057
/u01/app/oracle/oradata/ractestsysaux.257.754173057
/u01/app/oracle/oradata/ractestundotbs1.258.754173059
/u01/app/oracle/oradata/ractestusers.259.754173059
/u01/app/oracle/oradata/ractestundotbs2.264.754173315
/u01/app/oracle/oradata/ractestsoe.274.686330585
/u01/app/oracle/oradata/ractestliubinglin.276.786500233
/u01/app/oracle/oradata/ractestliubinglin.268.805074155
/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
9 rows selected.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '/u01/app/oracle/oradata/system02.dbf';
alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '/u01/app/oracle/oradata/system02.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的情况下无法执行该操作。
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
修改standby_file_management=manual：
SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '/u01/app/oracle/oradata/system02.dbf';
Database altered.
重命名成功。
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
恢复standby_file_management的值：
SQL> alter system set standby_file_management=auto;
System altered.
主库操作：
执行下面的步骤验证日志传输服务是否恢复正常。
SQL> alter system switch logfile;
System altered.
切换RAC所有实例的logfile。
SQL> select dest_id,thread#,max(sequence#) from v$archived_log where resetlogs_change#=5069294 group by dest_id,thread#
DEST_ID THREAD# MAX(SEQUENCE#)
---------- ---------- --------------
2 1 17
1 2 11
1 1 19
2 2 9
等待数秒后，
SQL> select dest_id,thread#,max(sequence#) from v$archived_log where resetlogs_change#=5069294 group by dest_id,thread#;
DEST_ID THREAD# MAX(SEQUENCE#)
---------- ---------- --------------
2 1 20
1 2 12
1 1 20
2 2 12
日志传输服务恢复正常。
--end--