加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

Oracle不可忽视的controlfile的恢复方式

(2016-04-18 14:55:53)
标签：

控制文件

恢复

controlfile

oracle

 分类： Oracle备份恢复
Oracle控制文件的重要性不言而喻，控制文件丢失整个数据库是启不来的。
一、多路复用:当1个控制文件受损或丢失，copy其他路径下的控制文件，这个比较简单，这里不做讨论；

二、基于用户管理的简单备份恢复
1.查看控制文件位置
SQL> select name,status from v$controlfile;

NAME   STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/orcl1/control01.ctl
/u01/app/oracle/oradata/orcl1/control02.ctl
/u01/app/oracle/oradata/orcl1/control03.ctl

2.进入sqlplus,备份控制文件到trace
SQL> alter database backup controlfile to trace;

3.模拟损坏所有控制文件
[oracle@stephenlinux orcl1]$ rm -rf control0*

4.shutdown immedite和startup都会报相应控制文件错误，我们执行shutdown abort，并启动数据库到nomount

SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl1/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.

5.我们到trace目录中（udump）找到刚刚的trace文件，得到一段controlfile的生成脚本
[oracle@stephenlinux udump]$ pwd
/u01/app/oracle/admin/orcl1/udump
[oracle@stephenlinux udump]$ cat orcl1_ora_4294.trc 

...
CREATE CONTROLFILE REUSE DATABASE "ORCL1" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl1/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/orcl1/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/orcl1/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl1/system01.dbf',
  '/u01/app/oracle/oradata/orcl1/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl1/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl1/users01.dbf',
  '/u01/app/oracle/oradata/orcl1/example01.dbf',
  '/u01/app/oracle/oradata/orcl1/tbs1.dbf'
CHARACTER SET ZHS16GBK;
...
6.在sqlplus中执行上面的controlfile生成脚本
SQL> @/u01/app/oracle/admin/orcl1/udump/create_controlfile.sql

open数据库：
SQL> alter database open；
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl1/system01.dbf'

恢复数据库再open：
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.
至此，控制文件恢复成功，数据库正常打开。
三、上述情况属于非常乐观且极易恢复的模拟实验现场，但是在实际情况中，环境却险要恶劣的多。往往控制文件丢失后，用户还会进行大量的操作，比如创建表空间等等，这些使得控制文件的恢复要困难许多。
1.用纯copy方式备份一份控制文件
[oracle@stephenlinux orcl1]$ cp control01.ctl control01.ctl.bak

2.test用户下创建一个表来模拟数据操作
SQL> create table test1.aa(name varchar2(10));

Table created.

3.查看一下当前在线日志的status，并切换日志，产生归档。

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT

SQL> alter system archive log current;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 ACTIVE

...
查看最后一次归档后的日志和数据文件状态：
SQL> select GROUP#,SEQUENCE#,ARCHIVED,FIRST_CHANGE# from v$log;

    GROUP#  SEQUENCE# ARC FIRST_CHANGE#
---------- ---------- --- -------------
1    7 YES 608692
2    8 YES 608696
3    9 NO 608704

SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
1       608704
2       608704
3       608704
4       608704
5       608704
6       608704

6 rows selected.

4.模拟控制文件丢失
[oracle@stephenlinux orcl1]$ rm -rf control01.ctl control02.ctl control03.ctl 

5.通过copy恢复控制文件
[oracle@stephenlinux orcl1]$ cp control01.ctl.bak control01.ctl
[oracle@stephenlinux orcl1]$ cp control01.ctl control02.ctl
[oracle@stephenlinux orcl1]$ cp control01.ctl control03.ctl

6.启动数据库到mount并且open，open时报错，控制文件过旧

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl1/system01.dbf'
ORA-01207: file is more recent than control file - old control file

7.利用archivelog来恢复控制文件，此步骤需指定归档文件
SQL> recover database using backup controlfile;
ORA-00279: change 606591 generated at 04/18/2016 14:47:33 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_3_ck93o44g_.arc
ORA-00280: change 606591 for thread 1 is in sequence #3


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_3_ck93o44g_.arc
ORA-00279: change 608639 generated at 04/18/2016 15:37:08 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_4_ck93qf1s_.arc
ORA-00280: change 608639 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_3_ck93o44g_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
suggested
ORA-00308: cannot open archived log 'suggested'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_4_ck93qf1s_.arc
ORA-00279: change 608675 generated at 04/18/2016 15:38:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_5_ck93qw30_.arc
ORA-00280: change 608675 for thread 1 is in sequence #5
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_4_ck93qf1s_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_5_ck93qw30_.arc
ORA-00279: change 608682 generated at 04/18/2016 15:38:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_6_ck93r24o_.arc
ORA-00280: change 608682 for thread 1 is in sequence #6
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_5_ck93qw30_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_6_ck93r24o_.arc
ORA-00279: change 608692 generated at 04/18/2016 15:38:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_7_ck93r507_.arc
ORA-00280: change 608692 for thread 1 is in sequence #7
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_6_ck93r24o_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_7_ck93r507_.arc
ORA-00279: change 608696 generated at 04/18/2016 15:38:44 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_8_ck93rn0h_.arc
ORA-00280: change 608696 for thread 1 is in sequence #8
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_7_ck93r507_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_8_ck93rn0h_.arc
ORA-00279: change 608704 generated at 04/18/2016 15:38:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_9_%u_.arc
ORA-00280: change 608704 for thread 1 is in sequence #9
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_8_ck93rn0h_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_9_%u_.arc
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_9_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
到需要恢复sequence 9时提示需要归档o1_mf_1_9_%u_.arc，并没有这份归档文件，步骤3中查得的sequence 9在redo03.log中，所以此步骤需指定redo03.log在线日志恢复

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl1/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.
至此控制文件恢复成功！

四、基于rman的备份恢复

 从上面可以看书，采用用户管理备份与恢复的方式来恢复我们的控制文件还是有一定的时间复杂度和空间复杂度的。那么到底有没有一个简单点得方法来解决这个问题吗？答案，仍然是肯定的。那就是RMAN。

1.rman备份

RMAN> backup database format '/u01/app/oracle/rmanbackup/full_%d_%U.db' include current controlfile
2> plus archivelog format '/u01/app/oracle/rmanbackup/arch_%d_%U.arc' delete input;


2.记录DBID（后续恢复需要用到）
connected to target database: ORCL1 (DBID=1293714786)

3.模拟控制文件丢失

4.关闭数据库，并以nomount模式启动
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size    2097624 bytes
Variable Size  159387176 bytes
Database Buffers  369098752 bytes
Redo Buffers    6287360 bytes

5.rman恢复控制文件
RMAN> set dbid=1293714786

executing command: SET DBID

RMAN> restore controlfile from '/u01/app/oracle/rmanbackup/full_ORCL1_03r3btkb_1_1.db';

Starting restore at 18-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/ORCL1/autobackup/2016_04_18/o1_mf_s_909496777_ck8xy9jx_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/orcl1/control01.ctl
output filename=/u01/app/oracle/oradata/orcl1/control02.ctl
output filename=/u01/app/oracle/oradata/orcl1/control03.ctl
Finished restore at 18-APR-16
6.数据库还原、恢复
RMAN> catalog start with '/u01/app/oracle/rmanbackup';

searching for all files that match the pattern /u01/app/oracle/rmanbackup

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/rmanbackup/full_ORCL1_02r3btj8_1_1.db
File Name: /u01/app/oracle/rmanbackup/arch_ORCL1_01r3btj6_1_1.arc
File Name: /u01/app/oracle/rmanbackup/full_ORCL1_03r3btkb_1_1.db
File Name: /u01/app/oracle/rmanbackup/arch_ORCL1_04r3btke_1_1.arc

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

mount数据库：
RMAN> alter database mount;

数据库恢复打开：
RMAN> restore database;
RMAN> recover database;

Starting recover at 18-APR-16
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/oradata/orcl1/redo02.log
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_3_ck97foyo_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/orcl1/redo01.log
archive log filename=/u01/app/oracle/oradata/orcl1/redo02.log thread=1 sequence=2
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_3_ck97foyo_.arc thread=1 sequence=3
creating datafile fno=7 name=/u01/app/oracle/oradata/orcl1/testtbs.dbf
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2016_04_18/o1_mf_1_3_ck97foyo_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/oradata/orcl1/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-APR-16
RMAN> alter database open resetlogs;

database opened




喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有