Oracle备份重建控制文件

建议在数据文件或者日志文件发生变化时对控制文件进行备份,在这之前备份的控制文件会被标记为已过时。 可以使用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