建议在数据文件或者日志文件发生变化时对控制文件进行备份,在这之前备份的控制文件会被标记为已过时。 可以使用alter database bacup controlfile命令进行备份,备份方式有两种,一种是备份为二进制文件;另一种是备份为脚本文件
将控制文件备份到二进制文件
SYS@zwcdb:~>alter database backup controlfile to '/u01/app/control.bak.ctl';
Database altered.
SYS@zwcdb:~>!ls -lh /u01/app/control*
-rw-r----- 1 oracle oinstall 6.8M Jan 6 10:52 /u01/app/control04.ctl
-rw-r----- 1 oracle oinstall 6.8M Jan 6 10:51 /u01/app/control.bak.ctl
控制文件转储为脚本
SYS@zwcdb:~>oradebug setmypid
Statement processed.
SYS@zwcdb:~>ALTER DATABASE backup controlfile TO trace;
DATABASE altered.
SYS@zwcdb:~>oradebug tracefile_name
/u01/app/oracle/admin/primarydb/udump/zwcdb_ora_3401.trc
查看zwcdb_ora_3401.trc文件内容会发现以下两段脚本,区别在于第一段是noresetlogs,第二段是resetlogs。这里日志组不需要重建,所以将使用noresetlogs
MAXLOGFILES 16 表示最大日志文件个数为16
MAXLOGMEMBERS 3 表示日志文件组的成员是3个
MAXDATAFILES 100 表示最大的数据文件个数是100
MAXINSTANCES 8 表示最大实例个数是8
MAXLOGHISTORY 292 表示最大的历史日志文件个数是292
CREATE CONTROLFILE REUSE DATABASE "ZWCDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/primarydb/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/primarydb/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/primarydb/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/primarydb/system01.dbf',
'/u01/app/oracle/oradata/primarydb/undotbs01.dbf',
'/u01/app/oracle/oradata/primarydb/sysaux01.dbf',
'/u01/app/oracle/oradata/primarydb/users01.dbf',
'/u01/app/oracle/oradata/primarydb/example01.dbf'
CHARACTER SET ZHS16GBK
;
resetlogs
CREATE CONTROLFILE REUSE DATABASE "ZWCDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/primarydb/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/primarydb/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/primarydb/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/primarydb/system01.dbf',
'/u01/app/oracle/oradata/primarydb/undotbs01.dbf',
'/u01/app/oracle/oradata/primarydb/sysaux01.dbf',
'/u01/app/oracle/oradata/primarydb/users01.dbf',
'/u01/app/oracle/oradata/primarydb/example01.dbf'
CHARACTER SET ZHS16GBK
;
模拟控制文件丢失
[oracle@zwc app]$ rm -rf control04.ctl
[oracle@zwc app]$ rm -rf /u01/app/oracle/oradata/primarydb/control0*
[oracle@zwc app]$ sqlplus / AS sysdba
SQL*Plus: Release 10.2.0.1.0 - Production ON Fri Jan 6 11:02:55 2012
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@zwcdb:~>shutdown abort
ORACLE instance shut down.
重建控制文件
[oracle@zwc app]$ sqlplus / AS sysdba
SQL*Plus: Release 10.2.0.1.0 - Production ON Fri Jan 6 11:07:00 2012
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
Connected TO an idle instance.
SYS@zwcdb:~>startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed SIZE 1218992 bytes
Variable SIZE 92276304 bytes
DATABASE Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SYS@zwcdb:~>CREATE CONTROLFILE REUSE DATABASE "ZWCDB" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/primarydb/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/primarydb/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/primarydb/redo03.log' SIZE 50M
11 DATAFILE
12 '/u01/app/oracle/oradata/primarydb/system01.dbf',
13 '/u01/app/oracle/oradata/primarydb/undotbs01.dbf',
14 '/u01/app/oracle/oradata/primarydb/sysaux01.dbf',
15 '/u01/app/oracle/oradata/primarydb/users01.dbf',
16 '/u01/app/oracle/oradata/primarydb/example01.dbf'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
SYS@zwcdb:~>RECOVER DATABASE;
Media recovery complete.
SYS@zwcdb:~>ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SYS@zwcdb:~>ALTER DATABASE OPEN;
DATABASE altered.
SYS@zwcdb:~>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/primarydb/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
[oracle@zwc ~]$ sqlplus / AS sysdba
SQL*Plus: Release 10.2.0.1.0 - Production ON Fri Jan 6 11:12:42 2012
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@zwcdb:~>SELECT open_mode,log_mode FROM v$database;
OPEN_MODE LOG_MODE
---------- ------------
READ WRITE ARCHIVELOG
SYS@zwcdb:~>SELECT name FROM v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/primarydb/control01.ctl
/u01/app/oracle/oradata/primarydb/control02.ctl
/u01/app/oracle/oradata/primarydb/control03.ctl
/u01/app/control04.ctl
SYS@zwcdb:~>!ls /u01/app/control*
/u01/app/control04.ctl /u01/app/control.bak.ctl
SYS@zwcdb:~>!ls $ORACLE_BASE/oradata/primarydb/con*
/u01/app/oracle/oradata/primarydb/control01.ctl /u01/app/oracle/oradata/primarydb/control02.ctl /u01/app/oracle/oradata/primarydb/control03.ctl
SYS@zwcdb:~>conn scott/tiger@zwcdb
Connected.
SCOTT@zwcdb:~>SELECT tname FROM tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE