[WK-T]ORACLE RAC +ASM Backup and Recovery(三)
Loss of control files
前言：默认创建好的RAC集群只有一个控制文件，而在单实例数据库中有三个控制文件，基于控制文件的重要性，我们手动添加两个控制文件，RAC环境下添加控制文件可以参考文章：http://blog.itpub.net/29634949/viewspace-1259969/
1.查看RAC环境下的控制文件信息
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+RAC_GROUP/hhpen1/controlfile/current.262.857093601
+RAC_GROUP/hhpen1/controlfile/control02.ctl
+RAC_GROUP/hhpen1/controlfile/control03.ctl
2.模拟控制文件丢失
由于控制文件在ORACLE数据库运行期间会被ORACLE进程锁定，无法直接删除，因此我们首先在两个节点shutdown数据库，然后再删除控制文件；
[oracle@rac1 log]$ srvctl status database -d HHPEN1
Instance HHPEN11 is not running on node rac1
Instance HHPEN12 is not running on node rac2
前言：默认创建好的RAC集群只有一个控制文件，而在单实例数据库中有三个控制文件，基于控制文件的重要性，我们手动添加两个控制文件，RAC环境下添加控制文件可以参考文章：http://blog.itpub.net/29634949/viewspace-1259969/
1.查看RAC环境下的控制文件信息
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+RAC_GROUP/hhpen1/controlfile/current.262.857093601
+RAC_GROUP/hhpen1/controlfile/control02.ctl
+RAC_GROUP/hhpen1/controlfile/control03.ctl
2.模拟控制文件丢失
由于控制文件在ORACLE数据库运行期间会被ORACLE进程锁定，无法直接删除，因此我们首先在两个节点shutdown数据库，然后再删除控制文件；
[oracle@rac1 log]$ srvctl status database -d HHPEN1
Instance HHPEN11 is not running on node rac1
Instance HHPEN12 is not running on node rac2
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd RAC_GROUP/HHPEN1/CONTROLFILE/
ASMCMD> ls
Current.262.857093601
backup.271.857132459
backup.291.857132429
control02.ctl
control03.ctl
ASMCMD> rm -rf control02.ctl
ASMCMD> ls
Current.262.857093601
backup.271.857132459
control03.ctl
如果是数据库打开状态下可以使用select DBID from v$database;
但是现在控制文件损坏了，只能启动到nomount状态，我们可以使用如下方式查看数据库的DBID
[oracle@rac2 dbs]$ pwd
/u01/app/oracle/db_1/dbs
[oracle@rac2 dbs]$ ls c-*
c-2744404128-20140901-01 c-2744404128-20140901-02 --2744404128就是数据库的DBID
2)对控制文件执行恢复操作
[oracle@rac1 ~]$ export ORACLE_SID=HHPEN11
[oracle@rac1 ~]$ rman target /
RMAN> set DBID=2744404128;
executing command: SET DBID
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd RAC_GROUP/HHPEN1/CONTROLFILE/
ASMCMD> ls
Current.262.857093601
backup.271.857132459
backup.291.857132429
control02.ctl
control03.ctl
ASMCMD> rm -rf control02.ctl
ASMCMD> ls
Current.262.857093601
backup.271.857132459
control03.ctl
3.测试能否打开数据库
SQL> startup --要是能打开的话，以后数据库可以不要控制文件了，也就没有控制文件损坏恢复这一说了
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2022144 bytes
Variable Size 188744960 bytes
Database Buffers 343932928 bytes
Redo Buffers 2170880 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------------- ------------------------
HHPEN11 STARTED
1)首先要知道数据库的DBIDSQL> startup --要是能打开的话，以后数据库可以不要控制文件了，也就没有控制文件损坏恢复这一说了
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2022144 bytes
Variable Size 188744960 bytes
Database Buffers 343932928 bytes
Redo Buffers 2170880 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------------- ------------------------
HHPEN11 STARTED
4.使用RMAN备份对控制文件执行恢复
如果是数据库打开状态下可以使用select DBID from v$database;
但是现在控制文件损坏了，只能启动到nomount状态，我们可以使用如下方式查看数据库的DBID
[oracle@rac2 dbs]$ pwd
/u01/app/oracle/db_1/dbs
[oracle@rac2 dbs]$ ls c-*
c-2744404128-20140901-01 c-2744404128-20140901-02 --2744404128就是数据库的DBID
2)对控制文件执行恢复操作
[oracle@rac1 ~]$ export ORACLE_SID=HHPEN11
[oracle@rac1 ~]$ rman target /
RMAN> set DBID=2744404128;
executing command: SET DBID
RMAN> restore controlfile from autobackup;
Starting restore at 01-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20140901
channel ORA_DISK_1: autobackup found: c-2744404128-20140901-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=+RAC_GROUP/hhpen1/controlfile/current.262.857093601
output filename=+RAC_GROUP/hhpen1/controlfile/control02.ctl
output filename=+RAC_GROUP/hhpen1/controlfile/control03.ctl
Finished restore at 01-SEP-14
Starting restore at 01-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20140901
channel ORA_DISK_1: autobackup found: c-2744404128-20140901-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=+RAC_GROUP/hhpen1/controlfile/current.262.857093601
output filename=+RAC_GROUP/hhpen1/controlfile/control02.ctl
output filename=+RAC_GROUP/hhpen1/controlfile/control03.ctl
Finished restore at 01-SEP-14
SQL> alter database mount; --有了控制文件，就可以将数据库启动到mount状态了
Database altered.
由于只是控制文件丢失，数据文件仍在，因此并不需要对整个数据库进行修复操作，只需要执行recover命令，重新应用备份的控制文件后生成的那些重做
日志即可；
RMAN> recover database;
Starting recover at 01-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 instance=HHPEN11 devtype=DISK
starting media recovery
archive log thread 1 sequence 6 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607
archive log thread 2 sequence 69 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745
archive log thread 2 sequence 70 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607 thread=1 sequence=6
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745 thread=2 sequence=69
creating datafile fno=6 name=+RAC_GROUP/hhpen1/datafile/test.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/01/2014 13:45:41
ORA-01119: error in creating database file '+RAC_GROUP/hhpen1/datafile/test.dbf'
ORA-17502: ksfdcre:4 Failed to create file +RAC_GROUP/hhpen1/datafile/test.dbf
ORA-15005: name "hhpen1/datafile/test.dbf" is already used by an existing alias
由于之前模拟了数据文件的丢失操作，其他控制文件中已经有了其配置信息，对数据库进行备份的时候并没有创建test表空间，而只对数据库执行修复操作
的话，肯定会报错。
RMAN> restore datafile 6; --对数据文件执行修复操作
Starting restore at 01-SEP-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/01/2014 13:49:20
RMAN-06085: must use SET NEWNAME command to restore datafile /u01/app/oracle/db_1/dbs/UNNAMED00006
SQL> select FILE#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 +RAC_GROUP/hhpen1/datafile/system.267.857093505 SYSTEM
2 +RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509 ONLINE
3 +RAC_GROUP/hhpen1/datafile/sysaux.263.857093507 ONLINE
4 +RAC_GROUP/hhpen1/datafile/users.265.857093511 ONLINE
5 +RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701 ONLINE
6 /u01/app/oracle/db_1/dbs/UNNAMED00006 RECOVER
6 rows selected.
Database altered.
由于只是控制文件丢失，数据文件仍在，因此并不需要对整个数据库进行修复操作，只需要执行recover命令，重新应用备份的控制文件后生成的那些重做
日志即可；
RMAN> recover database;
Starting recover at 01-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 instance=HHPEN11 devtype=DISK
starting media recovery
archive log thread 1 sequence 6 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607
archive log thread 2 sequence 69 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745
archive log thread 2 sequence 70 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607 thread=1 sequence=6
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745 thread=2 sequence=69
creating datafile fno=6 name=+RAC_GROUP/hhpen1/datafile/test.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/01/2014 13:45:41
ORA-01119: error in creating database file '+RAC_GROUP/hhpen1/datafile/test.dbf'
ORA-17502: ksfdcre:4 Failed to create file +RAC_GROUP/hhpen1/datafile/test.dbf
ORA-15005: name "hhpen1/datafile/test.dbf" is already used by an existing alias
由于之前模拟了数据文件的丢失操作，其他控制文件中已经有了其配置信息，对数据库进行备份的时候并没有创建test表空间，而只对数据库执行修复操作
的话，肯定会报错。
RMAN> restore datafile 6; --对数据文件执行修复操作
Starting restore at 01-SEP-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/01/2014 13:49:20
RMAN-06085: must use SET NEWNAME command to restore datafile /u01/app/oracle/db_1/dbs/UNNAMED00006
登录数据库查看数据文件信息，如下：
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
HHPEN12 MOUNTED
SQL> col name for a50;SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
HHPEN12 MOUNTED
SQL> select FILE#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 +RAC_GROUP/hhpen1/datafile/system.267.857093505 SYSTEM
2 +RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509 ONLINE
3 +RAC_GROUP/hhpen1/datafile/sysaux.263.857093507 ONLINE
4 +RAC_GROUP/hhpen1/datafile/users.265.857093511 ONLINE
5 +RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701 ONLINE
6 /u01/app/oracle/db_1/dbs/UNNAMED00006 RECOVER
6 rows selected.
查看当前数据库需要恢复的数据文件，如下：
SQL> select * from v$recover_file where error like '%FILE%';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ------------
6 ONLINE ONLINE FILE MISSING 0
SQL> select file#,name from v$datafile where file#=6;
FILE# NAME
---------- --------------------------------------------------
6 /u01/app/oracle/db_1/dbs/UNNAMED00006
SQL> select * from v$recover_file where error like '%FILE%';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ------------
6 ONLINE ONLINE FILE MISSING 0
SQL> select file#,name from v$datafile where file#=6;
FILE# NAME
---------- --------------------------------------------------
6 /u01/app/oracle/db_1/dbs/UNNAMED00006
现在需要对数据文件进行改名字，如果说你忘记之前设置的数据文件的名字了，不要紧，使用如下SQL语句查询
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
5 UNDOTBS2 YES NO YES
6 TEST YES NO YES
7 rows selected.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
5 UNDOTBS2 YES NO YES
6 TEST YES NO YES
7 rows selected.
SQL> alter database create datafile '/u01/app/oracle/db_1/dbs/UNNAMED00006' as '+RAC_GROUP/hhpen1/datafile/test.dbf';
Database altered.
Database altered.
在RMAN执行数据库的恢复操作，如下，显然是OK了：
RMAN> recover database;
Starting recover at 01-SEP-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 6 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607
archive log thread 2 sequence 69 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745
archive log thread 2 sequence 70 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745 thread=2 sequence=69
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607 thread=1 sequence=6
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747 thread=2 sequence=70
media recovery complete, elapsed time: 00:00:03
Finished recover at 01-SEP-14
RMAN> recover database;
Starting recover at 01-SEP-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 6 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607
archive log thread 2 sequence 69 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745
archive log thread 2 sequence 70 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745 thread=2 sequence=69
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607 thread=1 sequence=6
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747 thread=2 sequence=70
media recovery complete, elapsed time: 00:00:03
Finished recover at 01-SEP-14
SQL> alter database open resetlogs; --以resetlogs方式打开数据库
alter database open resetlogs
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active --看来是好事多磨啊，我喜欢
alter database open resetlogs
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active --看来是好事多磨啊，我喜欢
此时查看数据文件状态，发现test表空间是online状态，如下：
SQL> select FILE#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 +RAC_GROUP/hhpen1/datafile/system.267.857093505 SYSTEM
2 +RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509 ONLINE
3 +RAC_GROUP/hhpen1/datafile/sysaux.263.857093507 ONLINE
4 +RAC_GROUP/hhpen1/datafile/users.265.857093511 ONLINE
5 +RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701 ONLINE
6 +RAC_GROUP/hhpen1/datafile/test.dbf ONLINE
6 rows selected.
SQL> select FILE#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 +RAC_GROUP/hhpen1/datafile/system.267.857093505 SYSTEM
2 +RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509 ONLINE
3 +RAC_GROUP/hhpen1/datafile/sysaux.263.857093507 ONLINE
4 +RAC_GROUP/hhpen1/datafile/users.265.857093511 ONLINE
5 +RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701 ONLINE
6 +RAC_GROUP/hhpen1/datafile/test.dbf ONLINE
6 rows selected.
原因分析：RMAN备份或恢复的时候，数据库不能以resetlogs方式打开，关闭当前操作的会话，打开新的会话使用resetlogs方式打开数据库，结果成功了。
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
HHPEN11 OPEN
HHPEN12 OPEN
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
HHPEN11 OPEN
HHPEN12 OPEN
SQL> conn dayong/dayong; --备份之后创建的用户还在，表还在，表中数据还在
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MYTABLE TABLE
SQL> select * from mytable;
ID NAME
---------- ----------
1 dayong
2 xiaoru
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MYTABLE TABLE
SQL> select * from mytable;
ID NAME
---------- ----------
1 dayong
2 xiaoru
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14288937/viewspace-1264070/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 缺少log_archive_config导致归档路径被禁用
- [WK-T]ORACLE RAC +ASM Backup and Recovery(四)
- [WK-T]ORACLE RAC +ASM Backup and Recovery(二)
- [WK-T]ORACLE RAC +ASM Backup and Recovery(一)
- [WK-T]ORACLE 10G RAC+ASM增加controlfile
- How to copy a datafile from ASM to a file system not using RMAN
- Oracle Flashback(二)
- Oracle Flashback(一)
- ORACLE10g Stream表级复制配置
- ORACLE10g Stream用户级复制配置
