2008/05/08
备份&恢复之十三:损坏全部控制文件
损坏多个控制文件,或者人为的删除了所有的控制文件,通过控制文件的复制已经不能解决问题,这个时候需要重新建立控制文件。
同时注意,alter database backup control file to trace可以产生一个控制文件的文本备份。
测试环境:
1 操作系统:Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本:Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 关闭数据库,删除一个controlfile.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
删除一个控制文件
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ rm *.ctl
4 启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info
看alert_ORCL.log日志:
starting up 1 shared server(s) ...
Thu May 8 15:36:43 2008
ALTER DATABASE MOUNT
Thu May 8 15:36:43 2008
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 8 15:36:46 2008
ORA-205 signalled during: ALTER DATABASE MOUNT...
数据库只能启动到nomount状态
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORCL STARTED
5 停止数据库,拷贝一个好的控制文件替换坏的控制文件或修改init.ora中的控制文件参数,取消这个坏的控制文件。
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
6 在internal或sys下运行如下创建控制文件的脚本,注意完整列出联机日志或数据文件的路径,或修改由alter database backup control file to trace备份控制文件时产生的脚本,去掉多余的注释即可。
重建控制文件
在/u01/app/oracle/admin/orcl/udump下修改orcl_ora_44224.trc文件。
修改后保存名为:/home/mzl/BackupDatabase/createControlfile.sql
内容为:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/perfstat.dbf',
'/u01/app/oracle/oradata/orcl/risenet.dbf'
CHARACTER SET WE8ISO8859P1
;
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
7 运行createControlfile.sql脚本:
SQL> @/home/mzl/BackupDatabase/createControlfile.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
System altered.
Database altered.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORCL OPEN
说明:
1、重建控制文件用于恢复全部数据文件的损坏,需要注意其书写的正确性,保证包含了所有的数据文件与联机日志
2、经常有这样一种情况,因为一个磁盘损坏,我们不能再恢复(store)数据文件到这个磁盘,因此在store到另外一个盘的时候,我们就必须重新创建控制文件,用于识别这个新的数据文件,这里也可以用这种方法用于恢复
参考itpub上piner的备份与恢复案例:
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
在服务器测试成功
备份&恢复之十三:损坏全部控制文件
损坏多个控制文件,或者人为的删除了所有的控制文件,通过控制文件的复制已经不能解决问题,这个时候需要重新建立控制文件。
同时注意,alter database backup control file to trace可以产生一个控制文件的文本备份。
测试环境:
1 操作系统:Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本:Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 关闭数据库,删除一个controlfile.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
删除一个控制文件
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ rm *.ctl
4 启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info
看alert_ORCL.log日志:
starting up 1 shared server(s) ...
Thu May 8 15:36:43 2008
ALTER DATABASE MOUNT
Thu May 8 15:36:43 2008
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 8 15:36:46 2008
ORA-205 signalled during: ALTER DATABASE MOUNT...
数据库只能启动到nomount状态
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORCL STARTED
5 停止数据库,拷贝一个好的控制文件替换坏的控制文件或修改init.ora中的控制文件参数,取消这个坏的控制文件。
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
6 在internal或sys下运行如下创建控制文件的脚本,注意完整列出联机日志或数据文件的路径,或修改由alter database backup control file to trace备份控制文件时产生的脚本,去掉多余的注释即可。
重建控制文件
在/u01/app/oracle/admin/orcl/udump下修改orcl_ora_44224.trc文件。
修改后保存名为:/home/mzl/BackupDatabase/createControlfile.sql
内容为:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/perfstat.dbf',
'/u01/app/oracle/oradata/orcl/risenet.dbf'
CHARACTER SET WE8ISO8859P1
;
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
7 运行createControlfile.sql脚本:
SQL> @/home/mzl/BackupDatabase/createControlfile.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
System altered.
Database altered.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORCL OPEN
说明:
1、重建控制文件用于恢复全部数据文件的损坏,需要注意其书写的正确性,保证包含了所有的数据文件与联机日志
2、经常有这样一种情况,因为一个磁盘损坏,我们不能再恢复(store)数据文件到这个磁盘,因此在store到另外一个盘的时候,我们就必须重新创建控制文件,用于识别这个新的数据文件,这里也可以用这种方法用于恢复
参考itpub上piner的备份与恢复案例:
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
在服务器测试成功
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-263359/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-263359/