控制文件是数据库的重要组成部分,我们需要掌握有关控制文件在不同故障场景下的恢复方法。本文讨论当部分控制文件丢失场景下的恢复方法。
1.查询系统控制文件信息
sys@ora10g> col name for a50
sys@ora10g> select name from v$controlfile;
NAME
--------------------------------------------------
/oracle/ora10gR2/oradata/ora10g/control01.ctl
/oracle/ora10gR2/oradata/ora10g/control02.ctl
/oracle/ora10gR2/oradata/ora10g/control03.ctl
本系统中存在三个控制文件。他们内容是完全一样。目的何在?没错,就是为了防止个别控制文件丢失或出现故障时后可以使用其他有效的控制文件进行恢复。
2.模拟个别控制文件丢失故障场景
1)人为删除一个控制文件
这里,我们删除控制文件control03.ctl。
ora10g@secdb /home/oracle$ rm -f /oracle/ora10gR2/oradata/ora10g/control03.ctl
在删除的这段时间里,alert中并没有记录任何与之相关的信息。
2)尝试使用IMMEDIATE选项关闭数据库
sys@ora10g> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
可见,在正常关闭数据库的过程中,Oracle需要读取控制文件内容,当发现个别控制文件丢失后会抛出异常。
3)此时alert中也记录相关的报错信息。
Fri Jul 1 21:21:35 2011
Errors in file /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_9062.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jul 1 21:21:35 2011
Errors in file /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_9062.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jul 1 21:21:35 2011
Errors in file /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_9062.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
3.恢复个别控制文件丢失故障场景
方法很简单,停止数据库,使用完好的数据库控制文件生成丢失或损坏的控制文件。
1)使用ABORT选项关闭数据库实例
按照上面的方法,使用IMMEDIATE选项是不能关闭数据库的。
数据库后台进程依然活跃着。
ora10g@secdb /home/oracle$ ps -ef | grep _ora10g | grep -v grep
oracle 31646 1 0 Jun27 ? 00:00:00 ora_pmon_ora10g
oracle 31648 1 0 Jun27 ? 00:00:00 ora_psp0_ora10g
oracle 31650 1 0 Jun27 ? 00:00:00 ora_mman_ora10g
oracle 31652 1 0 Jun27 ? 00:00:03 ora_dbw0_ora10g
oracle 31654 1 0 Jun27 ? 00:00:02 ora_lgwr_ora10g
oracle 31656 1 0 Jun27 ? 00:00:32 ora_ckpt_ora10g
oracle 31658 1 0 Jun27 ? 00:00:14 ora_smon_ora10g
oracle 31660 1 0 Jun27 ? 00:00:00 ora_reco_ora10g
oracle 31662 1 0 Jun27 ? 00:00:02 ora_cjq0_ora10g
oracle 31664 1 0 Jun27 ? 00:00:14 ora_mmon_ora10g
oracle 31666 1 0 Jun27 ? 00:00:05 ora_mmnl_ora10g
oracle 31676 1 0 Jun27 ? 00:00:00 ora_arc0_ora10g
oracle 31678 1 0 Jun27 ? 00:00:00 ora_arc1_ora10g
oracle 31682 1 0 Jun27 ? 00:00:00 ora_qmnc_ora10g
oracle 31688 1 0 Jun27 ? 00:00:00 ora_q000_ora10g
oracle 31690 1 0 Jun27 ? 00:00:01 ora_q001_ora10g
我们只好使用“粗暴”的ABORT方式停止实例。
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 1 21:51:27 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> shutdown abort;
ORACLE instance shut down.
2)使用完好的控制文件生成丢失的控制文件control03.ctl
ora10g@secdb /home/oracle$ cp /oracle/ora10gR2/oradata/ora10g/control01.ctl /oracle/ora10gR2/oradata/ora10g/control03.ctl
3)启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 1 21:56:10 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
NotConnected@> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 318767928 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
到此,个别控制文件丢失或损坏的故障处理完毕。
4.小结
对于仅仅丢失或损坏部分控制文件的场景,恢复相对简单。
恢复代价是需要重新启动数据库。
Good luck.
secooler
11.07.01
-- The End --
1.查询系统控制文件信息
sys@ora10g> col name for a50
sys@ora10g> select name from v$controlfile;
NAME
--------------------------------------------------
/oracle/ora10gR2/oradata/ora10g/control01.ctl
/oracle/ora10gR2/oradata/ora10g/control02.ctl
/oracle/ora10gR2/oradata/ora10g/control03.ctl
本系统中存在三个控制文件。他们内容是完全一样。目的何在?没错,就是为了防止个别控制文件丢失或出现故障时后可以使用其他有效的控制文件进行恢复。
2.模拟个别控制文件丢失故障场景
1)人为删除一个控制文件
这里,我们删除控制文件control03.ctl。
ora10g@secdb /home/oracle$ rm -f /oracle/ora10gR2/oradata/ora10g/control03.ctl
在删除的这段时间里,alert中并没有记录任何与之相关的信息。
2)尝试使用IMMEDIATE选项关闭数据库
sys@ora10g> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
可见,在正常关闭数据库的过程中,Oracle需要读取控制文件内容,当发现个别控制文件丢失后会抛出异常。
3)此时alert中也记录相关的报错信息。
Fri Jul 1 21:21:35 2011
Errors in file /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_9062.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jul 1 21:21:35 2011
Errors in file /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_9062.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jul 1 21:21:35 2011
Errors in file /oracle/ora10gR2/admin/ora10g/udump/ora10g_ora_9062.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/ora10gR2/oradata/ora10g/control03.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
3.恢复个别控制文件丢失故障场景
方法很简单,停止数据库,使用完好的数据库控制文件生成丢失或损坏的控制文件。
1)使用ABORT选项关闭数据库实例
按照上面的方法,使用IMMEDIATE选项是不能关闭数据库的。
数据库后台进程依然活跃着。
ora10g@secdb /home/oracle$ ps -ef | grep _ora10g | grep -v grep
oracle 31646 1 0 Jun27 ? 00:00:00 ora_pmon_ora10g
oracle 31648 1 0 Jun27 ? 00:00:00 ora_psp0_ora10g
oracle 31650 1 0 Jun27 ? 00:00:00 ora_mman_ora10g
oracle 31652 1 0 Jun27 ? 00:00:03 ora_dbw0_ora10g
oracle 31654 1 0 Jun27 ? 00:00:02 ora_lgwr_ora10g
oracle 31656 1 0 Jun27 ? 00:00:32 ora_ckpt_ora10g
oracle 31658 1 0 Jun27 ? 00:00:14 ora_smon_ora10g
oracle 31660 1 0 Jun27 ? 00:00:00 ora_reco_ora10g
oracle 31662 1 0 Jun27 ? 00:00:02 ora_cjq0_ora10g
oracle 31664 1 0 Jun27 ? 00:00:14 ora_mmon_ora10g
oracle 31666 1 0 Jun27 ? 00:00:05 ora_mmnl_ora10g
oracle 31676 1 0 Jun27 ? 00:00:00 ora_arc0_ora10g
oracle 31678 1 0 Jun27 ? 00:00:00 ora_arc1_ora10g
oracle 31682 1 0 Jun27 ? 00:00:00 ora_qmnc_ora10g
oracle 31688 1 0 Jun27 ? 00:00:00 ora_q000_ora10g
oracle 31690 1 0 Jun27 ? 00:00:01 ora_q001_ora10g
我们只好使用“粗暴”的ABORT方式停止实例。
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 1 21:51:27 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> shutdown abort;
ORACLE instance shut down.
2)使用完好的控制文件生成丢失的控制文件control03.ctl
ora10g@secdb /home/oracle$ cp /oracle/ora10gR2/oradata/ora10g/control01.ctl /oracle/ora10gR2/oradata/ora10g/control03.ctl
3)启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 1 21:56:10 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
NotConnected@> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 318767928 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
到此,个别控制文件丢失或损坏的故障处理完毕。
4.小结
对于仅仅丢失或损坏部分控制文件的场景,恢复相对简单。
恢复代价是需要重新启动数据库。
Good luck.
secooler
11.07.01
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-701185/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-701185/