段亚东的Oracle笔记

故障类别
控制文件-11-10
归档、有备份、意外删除
归档、无备份、意外删除
无归档、意外删除
控制文件简介
使用：
    在数据库从started状态启动到mount状态的时候读取控制文件。
作用：
    记录创建数据库的时间戳
    数据文件的位置
    联机日志文件位置
    表空间信息
    日志历史记录(log history)
    归档日志信息
    备份信息
    当前日志序列号(log sequence number)
    检查点信息(checkpoint)
更改
    当数据库增加、重命名、删除一个数据文件或一个联机日志文件时，
    oracle服务器进程（server process）会立即更新控制文件，
    日志写进程（LGWR）会把当前日志序列号记录到控制文件中，
    检查点进程（CKPT）把检查点信息记录到控制文件中，

    归档进程（ArchN）把归档信息记录到控制文件中。

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--------------------------有归档，意外删除----------------------

启动数据库并查看数据库归档模式和控制文件路径

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
SQL> alter system switch logfile;

NAME
---------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

删除控制文件模拟控制文件损坏

SQL> !mv /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control01.ctl.bak

SQL> !mv /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl.bak

查看警告日志文件报错，提示控制文件无法读写

tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Mon Nov 07 23:53:55 2016
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_36967.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

备份控制文件到trc文件

SQL> alter database backup controlfile to trace;

Database altered.

查看trc文件并根据提示进行重建控制文件

tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

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 BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/tbs_ctrl.dbf',
'/u01/app/oracle/oradata/orcl/ts_biao.dbf'
CHARACTER SET ZHS16GBK
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_07/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
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;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--

根据提示先启动数据库到nomount状态

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Control file created.

根据提示恢复数据库

SQL> recover database;
Media recovery complete.

强制归档

SQL> alter system archive log all;

System altered.

打开数据库

SQL> alter database open;

控制文件重建完成。

以上