Home > Oracle > 正文

无备份无归档模式下控制文件丢失故障处理

故障类别
控制文件-11-10
归档、有备份、意外删除
归档、无备份、意外删除
无归档、意外删除

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

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

controlfile error
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--------------------------无归档,意外删除----------------------

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

SQL> startup
ORACLE instance started.

Total System Global Area 1064763392 bytes
Fixed Size 2260040 bytes
Variable Size 666895288 bytes
Database Buffers 390070272 bytes
Redo Buffers 5537792 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Current log sequence 6
SQL> select name from v$controlfile;

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

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

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


添加表空间数据文件模拟控制文件读写。

SQL>alter tablespace tbs_ctrl add datafile '/u01/app/oracle/oradata/orcl/tbs_ctrl02.dbf' size 1m autoextend on next 1m;


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

Sun Nov 04 19:58:51 2016
alter tablespace tbs_ctrl add datafile '/u01/app/oracle/oradata/orcl/tbs_ctrl02.dbf' size 1m autoextend on next 1m
Completed: alter tablespace tbs_ctrl add datafile '/u01/app/oracle/oradata/orcl/tbs_ctrl02.dbf' size 1m autoextend on next 1m

Sun Nov 04 20:00:27 2016
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_33304.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
Fri Nov 04 20:00:38 2016
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_32087.trc
Completed: ALTER DATABASE BACKUP CONTROLFILE TO TRACE

$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_32087.trc

-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
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/tbs_ctrl02.dbf',
'/u01/app/oracle/oradata/orcl/tbs_test.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_04/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_11_04/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
-- 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 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.


重建控制文件需要数据库在nomount状态,因为控制文件丢失,所以必须强制关闭然后启动到nomount状态。

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> shutdown immediate
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

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

Total System Global Area 1064763392 bytes
Fixed Size 2260040 bytes
Variable Size 666895288 bytes
Database Buffers 390070272 bytes
Redo Buffers 5537792 bytes


在nomount状态运行trc文件的重建控制文件的命令,并恢复和打开数据库。

SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 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 512E 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/tbs_ctrl02.dbf',
'/u01/app/oracle/oradata/orcl/tbs_test.dbf'
CHARACTER SET ZHS16GBK
22 ;

Control file created.

SQL> recover database;
Media recovery complete.


因为onlin日志均未丢失所以直接打开数据库。

SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Database altered.

SQL> select name from v$controlfile;

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

SQL>


以上。

上一篇:Oracle安装目录权限不能错,chmod chown
下一篇:无备份有归档模式下控制文件丢失故障处理
XX平台数据库无法启动CRS-2674  HAIP

XX平台数据库无法启动CRS-2674 HAIP

有备份有归档模式下控制文件丢失故障处理

有备份有归档模式下控制文件丢失故障处理

无备份有归档模式下控制文件丢失故障处理

无备份有归档模式下控制文件丢失故障处理

Oracle 热备份与恢复实验

Oracle 热备份与恢复实验

Oracle闪回查询/闪回恢复

Oracle闪回查询/闪回恢复

About Me

About Me

Oracle 常用表

Oracle 常用表

发表评论

昵称
邮箱
网址

沙发空闲中,快来抢!