1.模拟控制文件丢失后的数据库恢复(完全恢复)
今天的主题是备份与恢复，目的就是保护数据的安全性，众所周知Oracle之所以在市场上占据了50%的份额，与它提供了强大的数据保护措施是分不开的，下面我们就来简捷的介绍一下。
1）物理备份
冷备：这是最原始的一种备份方法，又是最简单可行的，就和copy一份文件一样，直接把库shutdown拷贝一份即可，操作简单，恢复快。当在一个没有专业人员的场合下，告诉他们这么操作是简单可行的，不是不可能只是你没遇到，一切皆有可能哦！
热备：Oracle专业备份工具RMAN，这是在8i就有的东东，很强大，可以在很多维度层面进行备份恢复，利用RMAN可以在联机的情况下进行在线备份与恢复。
2）逻辑备份
Exp/Imp：表级 用户级 数据库级进行逻辑备份，逻辑是对于业务层面而言的，例如我只想备份 person employment address表的内容用这种方法将会非常简单，它的亮点更在于备份出来的文件非常好迁移，兼容不同版本
Expdp/Impdp：这是上面2个工具的高级版，可压缩速度更快传输表空间的最佳利器，但只能用在服务器端
3）实例恢复
实例是什么，就是内存区+后台进程，那么实例恢复也就是恢复内存数据，例如突然死机 掉电 强制关库等，在你startup 启动时候后台会自动进行实例恢复。SMON进程负责执行
4）介质恢复
就是恢复硬盘数据，例如 文件被误删除 坏块等，需要手工恢复
在介绍了几种备份恢复方法后，我们进入topic，如何进行控制文件丢失恢复，先看一下数据库各种状态
5）SYS@LEO1>archivelog list 数据库处于非归档状态
Database logmode No Archive Mode
Automaticarchival Disabled
Archivedestination /u02/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online logsequence 71
Current logsequence 73
我们要先做一个RMAN全备，首先启动归档功能
[oracle@leonarding1oracle]$ pwd
/u02/app/oracle
[oracle@leonarding1oracle]$ mkdir archdata 创建一个归档日志目录
在ORACLE10g和11g版本，ORACLE默认的日志归档路径为闪回恢复区，但我们也可以修改为自己指定的目录路径
SYS@LEO1>altersystem set log_archive_dest_1='location=/u02/app/oracle/archdata' scope=both;
System altered.
SYS@LEO1>setlinesize 300 pagesize 999
设置的归档日志保存路径已经生效
SYS@LEO1>selectdest_name,destination,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_1';
DEST_NAME DESTINATION STATUS ERROR
------------------------------------------------------------------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1 /u02/app/oracle/archdata VALID
启动到mount状态，启动归档模式
SYS@LEO1>shutdownimmediate 关库
Database closed.
Databasedismounted.
ORACLE instanceshut down.
SYS@LEO1>startupmount mount状态
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>alterdatabase archivelog; 启动归档模式
Database altered.
SYS@LEO1>alterdatabase open; 打开数据库
Database altered.
注：凡是alter database操作都是对控制文件进行修改
凡是alter system 操作都是对参数文件进行修改
SYS@LEO1>altersystem switch logfile; 手工切换日志（不会触发检查点，自动切换会）
System altered.
SYS@LEO1>selectsequence#,name,archived,applied from v$archived_log; 查看已经归档的日志信息
SEQUENCE# NAME ARC APPLIED
---------------------------------------------------------------------------------------------------------------------------------------------
73 /u02/app/oracle/archdata/1_73_813654649.dbf YES NO
操作系统层面查看，没有问题也生成了
[oracle@leonarding1archdata]$ ll
total 5624
-rw-r----- 1oracle asmadmin 5757952 Apr 25 21:28 1_73_813654649.dbf
SYS@LEO1>archive log list
Database logmode Archive Mode 归档模式
Automaticarchival Enabled 自动归档启动
Archivedestination /u02/app/oracle/archdata归档日志目录
Oldest online logsequence 72 旧在线日志序号
Next log sequenceto archive 74 下一个归档日志序号
Current logsequence 74 当前日志序号
下面我们就要进行RMAN全库备份了，在此之前还需要设置一下RMAN的环境变量
6）登陆RMAN
[oracle@leonarding1archdata]$ rman target sys/oracle
Recovery Manager:Release 11.2.0.1.0 - Production on Fri Apr 26 06:05:24 2013
Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.
connectedto target database: LEO1 (DBID=1692458681) 只有连接到目标库才能显示环境变量，这些元数据是存放在控制文件中的
显示当前RMAN的环境变量
RMAN> show all;
using targetdatabase control file instead of recovery catalog
RMAN configurationparameters for database with db_unique_name LEO1 are:
CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUPOPTIMIZATION OFF; # default
CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default
CONFIGURECONTROLFILE AUTOBACKUP OFF; # default
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPETO BACKUPSET; # default
CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPEDISK TO 1; # default
CONFIGUREMAXSETSIZE TO UNLIMITED; # default
CONFIGUREENCRYPTION FOR DATABASE OFF; # default
CONFIGUREENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;# default
CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default
创建RMAN默认备份介质保存目录/u02/app/oracle/backup
[oracle@leonarding1 oracle]$ mkdir backup
RMAN> configure channel device type disk format'/u02/app/oracle/backup/DB_%U';
new RMANconfiguration parameters:
CONFIGURE CHANNELDEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
new RMANconfiguration parameters are successfully stored 新man配置参数生效
配置控制文件自动备份并保存到/u02/app/oracle/backup/control目录
[oracle@leonarding1backup]$ mkdir control
[oracle@leonarding1control]$ pwd
/u02/app/oracle/backup/control
RMAN> configure controlfile autobackup on; 启动控制文件自动备份
new RMANconfiguration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMANconfiguration parameters are successfully stored
RMAN> configure controlfile autobackup format for device type diskto '/u02/app/oracle/backup/control/cf_%F';
new RMANconfiguration parameters: 配置控制文件自动备份目录和格式
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';
new RMANconfiguration parameters are successfully stored
调整备份介质保留期为7天
RMAN> configure retention policy to recovery window of 7 days;
new RMANconfiguration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7DAYS;
new RMANconfiguration parameters are successfully stored
显示配置后RMAN环境变量
RMAN> show all;
RMAN configurationparameters for database with db_unique_name LEO1 are:
CONFIGURE RETENTION POLICY TO RECOVERYWINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default
CONFIGURECONTROLFILE AUTOBACKUP ON;
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPETO BACKUPSET; # default
CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPEDISK TO 1; # default
CONFIGURECHANNEL DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
CONFIGUREMAXSETSIZE TO UNLIMITED; # default
CONFIGUREENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTIONALGORITHM 'AES128'; # default
CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;# default
CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default
带颜色的就是我们刚刚修改过的变量
7）启动RMAN的压缩备份功能对数据库进行全备Oracle10g只压缩RMAN元数据11g真正压缩了数据
backup as compressed backupset full database format 命令行中直接指定压缩选项即可
'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn' includecurrent controlfile
plus
archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input; 全部备份完之后，删除备份过的旧归档日志
如果我们想使用默认通道默认配置备份一次数据库，同时删除备份过的归档日志，那么命令为
RMAN> backup ascompressed backupset full database include current controlfile plus archivelogdelete all input;
Starting backup at26-APR-13 备份时间
current logarchived
allocated channel:ORA_DISK_1 默认通道磁盘
channelORA_DISK_1: SID=140 device type=DISK
channelORA_DISK_1: starting compressed archived log backup set 先压缩备份的归档日志
channel ORA_DISK_1:specifying archived log(s) in backup set 备份了73 74归档日志
input archived logthread=1 sequence=73 RECID=1 STAMP=813706084
input archived logthread=1 sequence=74 RECID=2 STAMP=813739820
channelORA_DISK_1: starting piece 1 at 26-APR-13 启动备份片
channelORA_DISK_1: finished piece 1 at 26-APR-13 完成备份片
piece handle=/u02/app/oracle/backup/DB_01o81bpd_1_1tag=TAG20130426T065020 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒
channelORA_DISK_1: deleting archived log(s) 删除已备份的归档日志73 74
archived log filename=/u02/app/oracle/archdata/1_73_813654649.dbf RECID=1 STAMP=813706084
archived log filename=/u02/app/oracle/archdata/1_74_813654649.dbf RECID=2 STAMP=813739820
Finished backup at26-APR-13
Starting backup at 26-APR-13
using channelORA_DISK_1
channelORA_DISK_1: starting compressed full datafile backup set 再压缩备份数据文件
channelORA_DISK_1: specifying datafile(s) in backup set 指定备份如下数据文件
input datafilefile number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf
input datafilefile number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf
input datafilefile number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf
input datafilefile number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf
input datafilefile number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf
channelORA_DISK_1: starting piece 1 at 26-APR-13 启动备份片
channelORA_DISK_1: finished piece 1 at 26-APR-13 完成备份片
piece handle=/u02/app/oracle/backup/DB_02o81bpf_1_1tag=TAG20130426T065022 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:01:45备份完成用时1分45秒
channelORA_DISK_1: starting compressed full datafile backup set
channelORA_DISK_1: specifying datafile(s) in backup set
includingcurrent control file in backupset 这个是备份数据文件的同时包含备份控制文件
channelORA_DISK_1: starting piece 1 at 26-APR-13
channel ORA_DISK_1:finished piece 1 at 26-APR-13
piece handle=/u02/app/oracle/backup/DB_03o81bso_1_1tag=TAG20130426T065022 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒
Finished backup at26-APR-13
Starting backup at 26-APR-13
current logarchived
using channelORA_DISK_1
channelORA_DISK_1: starting compressed archived log backup set
channelORA_DISK_1: specifying archived log(s) in backup set
input archived logthread=1 sequence=75 RECID=3 STAMP=813739930 备份75归档日志
channel ORA_DISK_1:starting piece 1 at 26-APR-13
channelORA_DISK_1: finished piece 1 at 26-APR-13
piece handle=/u02/app/oracle/backup/DB_04o81bsq_1_1tag=TAG20130426T065210 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒
channelORA_DISK_1: deleting archived log(s) 删除已备份的归档日志75
archived log filename=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=3 STAMP=813739930
Finished backup at26-APR-13
Starting ControlFile and SPFILE Autobackup at 26-APR-13 启动控制文件和参数文件自动备份
piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00comment=NONE
Finished Control File and SPFILE Autobackup at26-APR-13 完成自动备份
在操作系统上都可以找到对应的备份集并且已经删除了备份过的旧归档日志
[oracle@leonarding1backup]$ pwd
/u02/app/oracle/backup
[oracle@leonarding1backup]$ ll
total 249468
drwxr-xr-x 2oracle oinstall 4096 Apr 26 06:52control
-rw-r-----1 oracle asmadmin 2790912 Apr 26 06:50DB_01o81bpd_1_1
-rw-r-----1 oracle asmadmin 251551744 Apr 26 06:52 DB_02o81bpf_1_1
-rw-r-----1 oracle asmadmin 1097728 Apr 26 06:52DB_03o81bso_1_1
-rw-r-----1 oracle asmadmin 7168 Apr 26 06:52DB_04o81bsq_1_1
[oracle@leonarding1 backup]$ cd control/
[oracle@leonarding1control]$ ll
total 9600
-rw-r-----1 oracle asmadmin 9830400 Apr 26 06:52 cf_c-1692458681-20130426-00
[oracle@leonarding1archdata]$ pwd
/u02/app/oracle/archdata
[oracle@leonarding1archdata]$ ll 归档日志全没有了
total 0
新的归档日志是从76号开始，75号之前都已经备份并删除
SYS@LEO1>archivelog list
Database logmode Archive Mode
Automaticarchival Enabled
Archivedestination /u02/app/oracle/archdata
Oldest online logsequence 74
Next log sequenceto archive 76
Current logsequence 76
到此我们的备份准备已经完成，稍微休息一下：）坐车上班班
8）SYS@LEO1>select status from v$instance; 检查数据库状态
STATUS
------------
OPEN
LEO1@LEO1>showparameter control_files 我们检查一下控制文件个数
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_files string /u02/app/oracle/oradata/LEO1/control01.ctl,
/u02/app/oracle/oradata/LEO1/control02.ctl
[oracle@leonarding1LEO1]$ ll 操作系统上也是2个没有问题
total 2618136
-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:01control01.ctl
-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:01control02.ctl
一般控制文件丢失大多数都是被误删除了，用rm 命令删除control01.ctl文件
[oracle@leonarding1trace]$ pwd
/u02/app/oracle/diag/rdbms/leo1/LEO1/trace
[oracle@leonarding1 trace]$ tail -10falert_LEO1.log 实时监控告警日志看看有什么变化
Fri Apr 2606:50:20 2013
Thread 1 advancedto log sequence 75 (LGWR switch)
Current log# 3 seq# 75 mem# 0:/u02/app/oracle/oradata/LEO1/redo03.log
Archived Log entry2 added for thread 1 sequence 74 ID 0x64e13fb9 dest 1:
Fri Apr 2606:52:10 2013
ALTER SYSTEMARCHIVE LOG
Fri Apr 2606:52:10 2013
Thread 1 advancedto log sequence 76 (LGWR switch)
Current log# 1 seq# 76 mem# 0:/u02/app/oracle/oradata/LEO1/redo01.log
Archived Log entry3 added for thread 1 sequence 75 ID 0x64e13fb9 dest 1:
[oracle@leonarding1LEO1]$ rm control01.ctl 模拟control01文件丢失的场景
LEO1@LEO1>createtablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10mautoextend off;
create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off
l 我们创建一个表空间，此时突然报错
ERROR at line 1:
ORA-00210: cannot open the specified controlfile 不能打开指定的控制文件
ORA-00202:control file: '/u02/app/oracle/oradata/LEO1/control01.ctl' 控制文件丢失
ORA-27041:unable to open file 无法打开这个文件
Linux-x86_64Error: 2: No such file or directory 找不到这个文件，好恐怖bless，赶紧看看alert日志
Additionalinformation: 3
Alert_LEO1.log日志内容
create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off
ORA-210 signalledduring: create tablespace test datafile'/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off...
Fri Apr 26 09:14:15 2013
Errors in file/u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_m000_7975.trc:
ORA-00210: cannot open the specified control file
ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unableto open file
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
是不是和上面报的错误信息一样啊，由于是我们自己搞的鬼，所以我们明白是怎么回事，如果在生产库上就要首先查看日志信息进行分析啦，好了现在我们开始修复吧->start on
思路：我们首先要清楚oracle为了保证其稳定性，控制文件都是多路复用的，如果使用dbca安装10g默认有3个控制文件 11g有两个，我们删除了其中一个，可能有的朋友会说，我们镜像出来一个控制文件不就好了么，没错思路很正确，但前提是要关闭数据库使所有的SCN号一致，也有可能你会遇上无法立即关闭的情况，木有办法只能强制关闭了，
SYS@LEO1>shutdownimmediate
ORA-00210: cannotopen the specified control file
ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unableto open file
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
SYS@LEO1>shutdownabort
ORACLE instanceshut down.
[oracle@leonarding1LEO1]$ cp control02.ctl control01.ctl 使用完好的控制文件恢复被删除的控制文件
[oracle@leonarding1LEO1]$ ll
total 2618136
-rw-r----- 1oracle oinstall 9748480 Apr 26 09:34control01.ctl
-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:28control02.ctl
SYS@LEO1>startupmount 启动到mount状态没有报错，说明我们恢复成功了
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>selectcheckpoint_change# from v$database; 数据库全局SCN号，放在控制文件里
CHECKPOINT_CHANGE#
--------------------------------
909922
SYS@LEO1>selectname,checkpoint_change# from v$datafile; 数据文件SCN号，放在控制文件里
NAME CHECKPOINT_CHANGE#
----------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/LEO1/system01.dbf 909922
/u02/app/oracle/oradata/LEO1/sysaux01.dbf 909922
/u02/app/oracle/oradata/LEO1/undotbs01.dbf 909922
/u02/app/oracle/oradata/LEO1/users01.dbf 909922
/u02/app/oracle/oradata/LEO1/leo1.dbf 909922
SYS@LEO1>selectname,checkpoint_change# from v$datafile_header; 数据文件头SCN号，放在数据文件头里
NAME CHECKPOINT_CHANGE#
----------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/LEO1/system01.dbf 909922
/u02/app/oracle/oradata/LEO1/sysaux01.dbf 909922
/u02/app/oracle/oradata/LEO1/undotbs01.dbf 909922
/u02/app/oracle/oradata/LEO1/users01.dbf 909922
/u02/app/oracle/oradata/LEO1/leo1.dbf 909922
SYS@LEO1>selectname,last_change# from v$datafile; 数据文件结束SCN号，放在控制文件里
NAME LAST_CHANGE#
----------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/LEO1/system01.dbf
/u02/app/oracle/oradata/LEO1/sysaux01.dbf
/u02/app/oracle/oradata/LEO1/undotbs01.dbf
/u02/app/oracle/oradata/LEO1/users01.dbf
/u02/app/oracle/oradata/LEO1/leo1.dbf
这个LAST_CHANGE为NULL，我们要知道Oracle做不做实例恢复就是看这个SCN是否为NULL
如果数据库非正常关闭值为NULL
如果数据库正常关闭值为xxxxxx
特例：数据库为open状态时LAST_CHANGE也为NULL，但现在我们是mount状态
SYS@LEO1>selectstatus from v$instance;
STATUS
------------
MOUNTED
我们在开打数据库的一霎那就会启动实例恢复，看alert日志即可
SYS@LEO1>alterdatabase open;
Database altered.
Alert日志内容
Fri Apr 2609:50:08 2013
alter databaseopen 打开数据库
Beginningcrash recovery of 1 threads 进行实例恢复
parallel recovery started with 2 processes 启动2个恢复进程
Started redo scan
Completed redoscan
read 30 KB redo, 31 data blocks need recovery
Started redoapplication at
Thread 1: logseq 76, block 23074
Recovery of OnlineRedo Log: Thread 1 Group 1 Seq 76 Reading mem 0 从76号日志为起始位置
Mem# 0:/u02/app/oracle/oradata/LEO1/redo01.log 应用redo日志进行恢复
Completed redoapplication of 0.02MB
Completed crashrecovery at
Thread 1: logseq 76, block 23134, scn 934394 一直应用到redo最后一个SCN号
31 data blocks read, 31 data blocks written,30 redo k-bytes read 恢复了31个数据块，读取了30K redo
Fri Apr 26 09:50:08 2013
LGWR: STARTINGARCH PROCESSES 启动归档进程进行归档
Fri Apr 2609:50:08 2013
ARC0 started withpid=22, OS id=8396
ARC0: Archivalstarted
LGWR: STARTINGARCH PROCESSES COMPLETE
ARC0: STARTINGARCH PROCESSES
Fri Apr 2609:50:09 2013
ARC1 started withpid=23, OS id=8400
Fri Apr 2609:50:09 2013
ARC2 started withpid=24, OS id=8404
ARC1: Archivalstarted
Fri Apr 2609:50:09 2013
ARC3 started withpid=25, OS id=8408
ARC2: Archivalstarted
ARC1: Becoming the'no FAL' ARCH
ARC1: Becoming the'no SRL' ARCH
ARC2: Becoming theheartbeat ARCH
Thread 1 advancedto log sequence 77 (thread open)
Thread 1 opened atlog sequence 77
Current log# 2 seq# 77 mem# 0:/u02/app/oracle/oradata/LEO1/redo02.log
Successful open of redo thread 1
MTTR advisory isdisabled because FAST_START_MTTR_TARGET is not set
Fri Apr 26 09:50:09 2013
SMON: enablingcache recovery SMON进程负责实例的恢复
Archived Log entry4 added for thread 1 sequence 76 ID 0x64e13fb9 dest 1:
ARC3: Archivalstarted
ARC0: STARTINGARCH PROCESSES COMPLETE
Successfullyonlined Undo Tablespace 2.
Verifying fileheader compatibility for 11g tablespace encryption..
Verifying 11g fileheader compatibility for tablespace encryption completed
SMON: enabling txrecovery
DatabaseCharacterset is ZHS16GBK
No ResourceManager plan active
replication_dependency_trackingturned off (no async multimaster replication found)
Startingbackground process QMNC
Fri Apr 2609:50:14 2013
QMNC started withpid=26, OS id=8412
Completed: alterdatabase open 到此我们完成数据库的open动作，实例恢复完毕
Fri Apr 2609:50:18 2013
Startingbackground process CJQ0
Fri Apr 2609:50:18 2013
CJQ0 started withpid=30, OS id=8436
从alter日志的流程上我们就可以看出，oracle实例恢复的内容过程是什么样的，通过以上案例我们即了解了控制文件的恢复过程又了解了数据库实例的恢复过程，可谓一举两得，大家好好的消化消化，休息一下，该上班工作啦：）
9）下了班我们继续，上次讲到了，使用copy方式来恢复控制文件，下面再讲一种使用备份集来恢复控制文件的方法。
【参】Books-> Backupand Recovery Reference -> RESTORE 和 RECOVER 参考官方文档是个好习惯
SYS@LEO1>shutdownimmediate 我们先关闭数据库
Database closed.
Databasedismounted.
ORACLE instanceshut down.
[oracle@leonarding1LEO1]$ rm -rf control01.ctl 删除控制文件
SYS@LEO1>startup
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
ORA-00205: errorin identifying control file, check alert log for more info
指定的控制文件错误，检查alert日志获取更多信息
[oracle@leonarding1trace]$ tail -20f alert_LEO1.log
ALTERDATABASE MOUNT
ORA-00210: cannotopen the specified control file
ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl' 这写着1号控制文件丢失
ORA-27037: unable to obtain file status
Linux-x86_64Error: 2: No such file or directory 找不到这个文件
Additionalinformation: 3
ORA-205 signalled during:ALTER DATABASE MOUNT...
Fri Apr 2619:44:05 2013
Checker run found1 new persistent data failures
SYS@LEO1>shutdownimmediate 关库
ORA-01507:database not mounted
ORACLE instanceshut down.
[oracle@leonarding1~]$ rman target sys/oracle 链接RMAN
Recovery Manager: Release 11.2.0.1.0 - Production onFri Apr 26 19:49:00 2013
Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected totarget database (not started) 库没有启动
RMAN> startupnomount 把库启动到nomount状态
Oracle instancestarted
Total System GlobalArea 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
有一个地方容易出错，大家都喜欢用这条语句来恢复
RMAN> RESTORECONTROLFILE FROM AUTOBACKUP;
Starting restoreat 26-APR-13
using channelORA_DISK_1
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130426
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130425
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130424
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130423
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130422
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130421
channel ORA_DISK_1:looking for AUTOBACKUP on day: 20130420
channelORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/26/201320:01:04
RMAN-06172: noAUTOBACKUP found or specified handle is not a valid copy or piece
报错：说找不到指定的备份集
RMAN> show all;
RMAN configurationparameters for database with db_unique_name LEO1 are:
CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUPOPTIMIZATION OFF; # default
CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default
CONFIGURECONTROLFILE AUTOBACKUP OFF; # default
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
原来我们配置的控制文件自动备份保存目录是不是失效啦，这是为什么呢，原来rman元数据信息是写在controlfile中的，而现在控制文件又损坏了不能打开，一个不能打开的文件我们是不是读不到里面的内容啊，我们只启动到了nomount状态只读取参数文件信息，因此我们在恢复控制文件的时候指定一下“原来备份的保存目录”告诉rman从哪个路径下可以找到备份集就可以了。
RMAN> restore controlfile from'/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00';
Starting restoreat 26-APR-13
using channelORA_DISK_1
channelORA_DISK_1: restoring control file
channelORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u02/app/oracle/oradata/LEO1/control01.ctl
output filename=/u02/app/oracle/oradata/LEO1/control02.ctl
Finished restoreat 26-APR-13
[oracle@leonarding1LEO1]$ ll
total 2618136
-rw-r----- 1oracle asmadmin 9748480 Apr 26 20:17control01.ctl
-rw-r----- 1oracle asmadmin 9748480 Apr 26 20:17control02.ctl
我们一起恢复了所有的控制文件
RMAN> alterdatabase mount; 现在数据库可以正常加载了对吧
database mounted
released channel:ORA_DISK_1
那我们可以alter database open来打开数据库吗，显然是不行的，大家知道为什么吗？
你想想如果这个控制文件是从10天之前的一个备份还原的与当前的数据库物理结构能一致吗！
显然是不可以的，我们只有用备份在重新同步数据库
RMAN> restoredatabase; RMAN备份还原restore（复制）数据文件
Starting restoreat 26-APR-13
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=134 device type=DISK
channelORA_DISK_1: starting datafile backup set restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
channelORA_DISK_1: restoring datafile 00001 to/u02/app/oracle/oradata/LEO1/system01.dbf
channelORA_DISK_1: restoring datafile 00002 to/u02/app/oracle/oradata/LEO1/sysaux01.dbf
channelORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/LEO1/undotbs01.dbf
channelORA_DISK_1: restoring datafile 00004 to/u02/app/oracle/oradata/LEO1/users01.dbf
channelORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf
channelORA_DISK_1: reading from backup piece /u02/app/oracle/backup/DB_02o81bpf_1_1
channelORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_02o81bpf_1_1tag=TAG20130426T065022
channelORA_DISK_1: restored backup piece 1
channelORA_DISK_1: restore complete, elapsed time: 00:02:36
Finished restore at26-APR-13
RMAN> recoverdatabase; 应用redo日志恢复recover（同步）数据库
Starting recoverat 26-APR-13
using channelORA_DISK_1
starting mediarecovery
archived log forthread 1 with sequence 75 is already on disk as file/u02/app/oracle/oradata/LEO1/redo03.log
archived log forthread 1 with sequence 76 is already on disk as file/u02/app/oracle/oradata/LEO1/redo01.log
archived log forthread 1 with sequence 77 is already on disk as file/u02/app/oracle/oradata/LEO1/redo02.log
archived log filename=/u02/app/oracle/oradata/LEO1/redo03.log thread=1 sequence=75
archived log filename=/u02/app/oracle/oradata/LEO1/redo01.log thread=1 sequence=76
archived log filename=/u02/app/oracle/oradata/LEO1/redo02.log thread=1 sequence=77
mediarecovery complete, elapsed time: 00:00:14 这就是介质恢复，要把数据文件同步到损坏的前一刻
因为这是不完全恢复，因此我们不能用alter database open来打开数据库
RMAN> alterdatabase open resetlogs;
database opened
SYS@LEO1>selectstatus from v$instance; 数据库已打开，可以正常使用
STATUS
------------
OPEN
小结：因为我们进行了不完全恢复，恢复到之前的某一点，现在数据库以这点为一个新的起点（相当是一个焕然一新的库），resetlogs就是重置归档日志从1开始编码，之前的归档全部无效。
SYS@LEO1>altersystem switch logfile; 我们重新切换一次
System altered.
[oracle@leonarding1oracle]$ cd archdata
[oracle@leonarding1archdata]$ ll
total 14028
-rw-r-----1 oracle asmadmin 222720 Apr 26 21:051_1_813790699.dbf
-rw-r----- 1oracle asmadmin 5632 Apr 26 20:581_75_813654649.dbf
-rw-r----- 1oracle asmadmin 11844608 Apr 26 20:58 1_76_813654649.dbf
-rw-r----- 1oracle asmadmin 2284032 Apr 26 20:581_77_813654649.dbf
注意：归档日志编码重置了，从1开始了，75 76 77归档日志全部无效了，为了不碍眼你可以删除掉。
2.模拟状态为inactive的日志损坏的恢复实验(完全恢复)
Redo log：是用于记录数据库所有变化信息的文件，只要有底层的块变化就会产生信息，当遇到需要恢复的场景，我们可以利用这些redo log进行实例恢复和介质恢复。Redo log日志通常情况下都是分成若干组存在数据库中，每个组都会有2个以上的成员互为镜像（保证高可用），每个组循环切换使用。下面我们来看看redo log的恢复操作。
SYS@LEO1>selectgroup#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
-------------------- ---------- --- ----------------
1 1 52428800 YES INACTIVE
2 1 52428800 NO CURRENT
3 1 52428800 YES UNUSED
现在有3个redo组，每个组只有一个成员，每个成员大小为50M，显然这是不合理的，保证每个日志文件组中包含不少于一个日志文件成员，那么我们现在可以添加一下，要记住哦如果在生产环境下，redo日志组成员要放在不同的磁盘上，防止磁盘级损坏。
[oracle@leonarding1LEO1]$ mkdir disk2 我们创建一个disk2目录模拟第二块磁盘
SYS@LEO1>alter database add logfile member
'/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log'to group 1, 给组1添加成员
'/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log'to group 2, 给组2添加成员
'/u02/app/oracle/oradata/LEO1/disk2/redo03_b.log'to group 3; 给组3添加成员
2 3 4
Database altered.
SYS@LEO1>selectgroup#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
-------------------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES UNUSED
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP#MEMBER STATUS
------------------------------------------------------------ -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2/u02/app/oracle/oradata/LEO1/redo02.log
3/u02/app/oracle/oradata/LEO1/redo03.log
1/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log INVALID
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log INVALID
3/u02/app/oracle/oradata/LEO1/disk2/redo03_b.log INVALID
6 rows selected.
Ok，日志组成员添加完成，每个大小和原来成员一致50M，路径放在disk2目录上
SYS@LEO1>archivelog list 已经是归档模式
Database logmode Archive Mode
Automaticarchival Enabled
Archivedestination /u02/app/oracle/archdata
我们做几次日志切换，把所有redo log 都使用一遍
SYS@LEO1>altersystem switch logfile;
System altered.
SYS@LEO1>altersystem switch logfile;
System altered.
SYS@LEO1>altersystem switch logfile;
System altered.
SYS@LEO1>archivelog list
Database logmode Archive Mode
Automaticarchival Enabled
Archivedestination /u02/app/oracle/archdata
Oldest online logsequence 3
Next log sequenceto archive 5
Current logsequence 5
我们切换了3次生成了3个归档日志2 3 4号，下一次该归档的是5号
[oracle@leonarding1archdata]$ ll
total 30708
-rw-r----- 1oracle asmadmin 222720 Apr 26 21:051_1_813790699.dbf
-rw-r-----1 oracle asmadmin 17069056 Apr 28 07:03 1_2_813790699.dbf
-rw-r-----1 oracle asmadmin 1024 Apr 28 07:031_3_813790699.dbf
-rw-r-----1 oracle asmadmin 3072 Apr 28 07:031_4_813790699.dbf
现在我们看到已经切换到组2为当前redo 组了，组1和组3已经全部归档
SYS@LEO1>selectgroup#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
-------------------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
如果想把inactive的日志恢复首先要了解日志各种状态含义
Current：Oracle当前正在使用的redo log，就是LGWR进程正在写入的redo log，在实例恢复时会用到此日志，这个redo log记录着数据库最后的SCN号。
Active：表示日志是活动的但不是当前正在使用的redolog，active意味着checkpoint动作尚未完成（脏数据还没有完全刷到磁盘上）or 归档模式下该日志的内容还没有完全归档，这两种情况下都会让日志为active状态，在实例恢复时也会用到此日志文件，因此该日志文件不能被覆盖。
Inactive：表示日志是不活动的，checkpoint动作已经完成，日志内容已经完全归档，可以被后续redo数据覆盖，实例恢复时不在需要，但在介质恢复时可能需要
Unused：表示日志从未使用过，里面没有任何的旧数据，可能是刚刚添加的日志 or resetlogs之后被重置的日志。
Clearing：表示日志在alter database clear logfile命令之后被重新创建为一个空日志，原来日志中的内容被全部清空，日志被清空后状态重置为unused。
Clearing_current：表示日志在current状态下被清空了，如果发生切换，在写日志文件头时会报一个I/O错误，一定要警惕这种状态，可能会导致数据永久丢失。
SYS@LEO1>selectgroup#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
-------------------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
现在是第一组和第三组为inactive，我们直接删除第一组中的一个成员redo01.log
[oracle@leonarding1 LEO1]$ rm redo01.log 操作系统级别删除
由于是不活动日志此时数据库不受影响会继续运行，我们关库再重启看看会发生什么
SYS@LEO1>shutdownimmediate 关库
Database closed.
Databasedismounted.
ORACLE instanceshut down.
SYS@LEO1>startup 重启
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
DatabaseBuffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP# MEMBER STATUS
------------------------------------------------------------ -------
1/u02/app/oracle/oradata/LEO1/redo01.log INVALID
2/u02/app/oracle/oradata/LEO1/redo02.log
3/u02/app/oracle/oradata/LEO1/redo03.log
1/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
3 /u02/app/oracle/oradata/LEO1/disk2/redo03_b.log
SYS@LEO1>selectgroup#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
-------------------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
6 rows selected.
SYS@LEO1>archivelog list
Database logmode Archive Mode
Automaticarchival Enabled
Archivedestination /u02/app/oracle/archdata
Oldest online logsequence 3
Next log sequenceto archive 5
Current logsequence 5
没有报错信息，验证奇迹的时刻，按理说oracle在检测到redolog日志丢失的时候会启动告警，实际上什么也没有发生，不科学，难道oracle没有检测到redolog丢失嘛？？？
Errors in file/u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_lgwr_10850.trc:
ORA-00313: open failed for members of loggroup 1 of thread 1
ORA-00312:online log 1 thread 1: '/u02/app/oracle/oradata/LEO1/redo01.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
非也非也，我们在alert日志中发现了告警踪迹“打开组1成员失败并且指出了失败文件路径”，那为什么在sqlplus中没有告警信息呢？？？从Oracle设计的理念上可以窥视出，它想让数据库尽可能的活下来，只要不需要的动作就可以先忽略掉（尽管已经检测出来了，会在状态上标注为invalid）。那么ok，我们开始恢复
【参】Books->Administrator’sGuide -> 6 Managing the Redo Log ->Dropping RedoLog Groups and Members
SYS@LEO1>shutdownimmediate 关库
Database closed.
Databasedismounted.
ORACLE instanceshut down.
SYS@LEO1>startupmount 启动到mount状态
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
DatabaseBuffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
刚才我们是从操作系统层面删除的，但控制文件中还保留了redolog信息，我们在从数据库层面删除
SYS@LEO1>ALTERDATABASE DROP LOGFILE MEMBER '/u02/app/oracle/oradata/LEO1/redo01.log';
Database altered.
切记一定要先删除，再创建否则会报文件已经存在的错误：
ORA-01577:cannot add log file '/u02/app/oracle/oradata/LEO1/redo01.log' – file alreadypart of database
SYS@LEO1>alterdatabase add logfile member '/u02/app/oracle/oradata/LEO1/redo01.log' to group1;
Database altered.
SYS@LEO1>alterdatabase open; 打开数据库
Database altered.
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP# MEMBER STATUS
------------------------------------------------------------ -------
1 /u02/app/oracle/oradata/LEO1/redo01.log INVALID
2/u02/app/oracle/oradata/LEO1/redo02.log
3/u02/app/oracle/oradata/LEO1/redo03.log
1/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
3 /u02/app/oracle/oradata/LEO1/disk2/redo03_b.log
怎么还是显示invalid无效呢，上面说过只要不需要就可以先忽略掉
6 rows selected.
SYS@LEO1>selectgroup#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
-------------------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
我们做2次切换，把当前组切换为组1
SYS@LEO1>alter systemswitch logfile;
System altered.
SYS@LEO1>altersystem switch logfile;
System altered.
这次是不是状态变成正常的啦，用到了自动监测，到此我们模拟inactive日志恢复完成
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP#MEMBER STATUS
------------------------------------------------------------ -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2/u02/app/oracle/oradata/LEO1/redo02.log
3/u02/app/oracle/oradata/LEO1/redo03.log
1/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
3 /u02/app/oracle/oradata/LEO1/disk2/redo03_b.log
附加另一种方法，还可以先删除损坏组，在增加新组的方式来恢复
SYS@LEO1>ALTERDATABASE DROP LOGFILE GROUP 3;
Database altered.
这个只是在控制文件上删除，你还需要在操作系统层面上删除
[oracle@leonarding1LEO1]$ rm redo03.log
[oracle@leonarding1disk2]$ rm redo03_b.log
SYS@LEO1>ALTERDATABASE ADD LOGFILE GROUP 5('/u02/app/oracle/oradata/LEO1/redo05.log','/u02/app/oracle/oradata/LEO1/disk2/redo05_b.log')SIZE 50M;
Database altered.
我们增加了第五组，状态良好
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP# MEMBER STATUS
------------------------------------------------------------ -------
1/u02/app/oracle/oradata/LEO1/redo01.log
2/u02/app/oracle/oradata/LEO1/redo02.log
5/u02/app/oracle/oradata/LEO1/redo05.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5 /u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
6 rows selected.
新添加的第五组为unused状态，还没有使用
SYS@LEO1>selectgroup#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
-------------------- ---------- --- ----------------
1 2 52428800 NO CURRENT
2 2 52428800 YES INACTIVE
5 2 52428800 YES UNUSED
补充：使用alter database clear logfile n 命令恢复 inactive状态损坏的日志文件
Clearing：表示日志在alter database clear logfile命令之后被重新创建为一个空日志，原来日志中的内容被全部清空，日志被清空后状态重置为unused。
原理：这个方法的恢复原理就是把损坏的日志文件重新创建一次，创建成一个新的空日志，表面上看相当于把日志内容给清空了保证一致性，因为这种状态的redolog已经归档完毕，里面的数据已经没有意义了，清空or覆盖都不影响数据库的运行。
举例
SQL> alterdatabase clear logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
小结：在进行恢复redolog日志之前一定要先掌握其状态要领，了解redolog正处于什么状态，会不会丢失数据，根据其特点采用合理的恢复方案。
3.模拟状态为active的日志损坏的数据恢复实验（不完全恢复）
首先我们先看看Active日志特点
Active：表示日志是活动的但不是当前正在使用的redolog，active意味着checkpoint动作尚未完成（脏数据还没有完全刷到磁盘上）or 归档模式下该日志的内容还没有完全归档，这两种情况下都会让日志为active状态，在实例恢复时也会用到此日志文件，因此该日志文件不能被覆盖。
Active和Currentredolog日志有一个共同之处就是实例恢复时会用到这个日志文件，它们都是checkpoint检查点还没有完成时保护数据安全的最后屏障，如果它们损坏or勿删除了会导致数据的丢失，这是非常危险的，就算有RMAN备份也不能恢复current 状态的数据。
有的朋友会很奇怪不是说有全库备份就可以完事大吉了嘛！这是一种完美主义，没有一种四海皆准的备份，如果你在RMAN备份时候仔细查看了备份日志就会发现一个惊奇的秘密，RMAN是不会备份
Redolog的，但会备份archivelog，redolog是用多路复用方式镜像备份的，因此如果删除了current 重做日志那么就会丢失数据，我们要警惕这种场景，做一名优秀的DBA！
注：基本上，如果是当前在线日志受损坏，很难不丢数据。但最差的情况下是可以恢复到上一个可用的归档日志时间点的。
恢复方法：
A 使用镜像文件来恢复，不会丢失数据
B 隐含参数_allow_resetlogs_corruption=TRUE 进行不一致性恢复，会丢失数据
C RMAN不完全恢复，有全备，有归档，可以保证数据的一致性，会丢失数据
###################################################################################
A 使用镜像文件来恢复，不会丢失数据
SYS@LEO1>selectgroup#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
-------------------- ---------- --- ---------- ----------------
1 2 52428800 YES 7 INACTIVE
2 2 52428800 NO 9 CURRENT
5 2 52428800 YES 8 INACTIVE
第二组为当前正在使用的redo组
[oracle@leonarding1LEO1]$ mv redo02.log redo02.log.bak 我们改个名字
SYS@LEO1>shutdownabort 我们强制关库，按理说应该使用current log进行实例恢复，但current log备我们删除了，连库都起不来，如何恢复呢
SYS@LEO1>startup 启动的时候是可以正常启动的，但在告警日志里面会报错
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
DatabaseBuffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
DDE:Problem Key 'ORA 313' was flood controlled (0x1) (no incident)
ORA-00313:open failed for members of log group 2 of thread 1
ORA-00313:open failed for members of log group 2 of thread 1
我们很奇怪为什么我们可以启动呢？
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP# MEMBER STATUS
------------------------------------------------------------ -------
1/u02/app/oracle/oradata/LEO1/redo01.log
2/u02/app/oracle/oradata/LEO1/redo02.log INVALID
5 /u02/app/oracle/oradata/LEO1/redo05.log
1/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5/u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
哦原来第二组有2个成员互为镜像如果第一个成员不可用时oracle就会标记为invalid，把redo数据写入到redo02_b.log第二个成员中继续支持oracle正常运行
SYS@LEO1>altersystem switch logfile;
System altered.
SYS@LEO1>selectgroup#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
-------------------- ---------- --- ---------- ----------------
1 2 52428800 YES 16 INACTIVE
2 2 52428800 NO 18 CURRENT
5 2 52428800 YES 17 ACTIVE
看还可以正常切换，正常使用，但目前只有一个成员可用，也是非常危险的，我们要恢复原状
SYS@LEO1>alterdatabase drop logfile member '/u02/app/oracle/oradata/LEO1/redo02.log';
Database altered.
SYS@LEO1>alterdatabase add logfile member '/u02/app/oracle/oradata/LEO1/redo02.log' to group2;
Database altered.
我们先删除在创建，然后多切换几次，redolog就恢复了
SYS@LEO1>altersystem switch logfile;
System altered.
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP# MEMBER STATUS
------------------------------------------------------------ -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2/u02/app/oracle/oradata/LEO1/redo02.log
5/u02/app/oracle/oradata/LEO1/redo05.log
1/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5 /u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
小结：使用镜像文件来恢复，不会丢失数据，因为没有影响到oracle正常的运行
################################################################################
B 隐含参数_allow_resetlogs_corruption=true 进行不一致性恢复，会丢失数据
第二种方法，我把所有的current log全部改名，看看能否恢复
SYS@LEO1>shutdownabort 强制关库
ORACLE instanceshut down.
[oracle@leonarding1 LEO1]$ mv redo02.logredo02.log.bak 修改第一个成员
[oracle@leonarding1disk2]$ mv redo02_b.log redo02_b.log.bak 修改第二个成员
SYS@LEO1>startup 启动库
ORACLE instancestarted.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
DatabaseBuffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
ORA-00313: openfailed for members of log group 2 of thread 1 打开redo日志失败
ORA-00312: onlinelog 2 thread 1: '/u02/app/oracle/oradata/LEO1/redo02.log' 指明哪个日志
ORA-27037: unableto obtain file status 获取不到日志状态
Linux-x86_64Error: 2: No such file or directory 因为没有这个文件
Additionalinformation: 3
ORA-00312: onlinelog 2 thread 1:
'/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log' 第一个成员找不到我们看看第二个成员有没有
ORA-27037: unable to obtain file status
Linux-x86_64Error: 2: No such file or directory 也是没有这个文件对吧
Additional information: 3
SYS@LEO1>selectstatus from v$instance; 现在实例正在处于mount状态
STATUS
------------
MOUNTED
我们可以使用隐含参数_allow_resetlogs_corruption=true 进行不一致恢复，这是oracle给予我们提供的一种临时急救方法，一般不到万不得已不建议使用，因为这样会丢失数据的，而丢失数据是不能接受的。我们继续
SYS@LEO1>altersystem set "_allow_resetlogs_corruption"=true scope=spfile; 静态修改需重启生效
System altered.
SYS@LEO1>shutdownimmediate
ORA-01109:database not open
Databasedismounted.
ORACLE instance shutdown.
SYS@LEO1>startupmount 启动到mount状态
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
DatabaseBuffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>recoverdatabase until cancel;
until cancel：一直恢复到数据库能够恢复的最后一个日志，尽最大努力恢复
场景：current/active log有丢失情况下或者有归档日志丢失的情况下，一直可恢复到丢失前的最后一个日志，则中止。
SYS@LEO1>recoverdatabase until cancel;
ORA-00279: change 1056653 generated at 04/30/201308:35:09 needed for thread 1
ORA-00289:suggestion : /u02/app/oracle/archdata/1_19_813790699.dbf
ORA-00280: change1056653 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO| CANCEL}
cancel 这时需手工输入cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547:warning: RECOVER succeeded but OPEN RESETLOGS wouldget error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: datafile 1: '/u02/app/oracle/oradata/LEO1/system01.dbf'
需要resetlogs方式开打数据库，也就是非一致性打开
Resetlogs做的几件事：
1）数据文件头scn号为准，同步控制文件和在线日志文件scn号
2）重新创建redolog日志（创建一个空日志），重置为unused
3）重置归档日志序号从1开始编码
-rw-r----- 1oracle asmadmin 222720 Apr 26 21:051_1_813790699.dbf
-rw-r----- 1oracle asmadmin 7168 Apr 30 10:25 1_1_814098124.dbf
4）让数据库重新进入一个新的生命周期
SYS@LEO1>alterdatabase open resetlogs;
alter databaseopen resetlogs
*
ERROR at line 1:
ORA-00603: ORACLEserver session terminated by fatal error 当前会话被终止
ORA-00600:internal error code, arguments: [2662], [0], [1056672], [0],
[1056735],[12583120], [], [], [], [], [], []
ORA-00600:internal error code, arguments: [2662], [0], [1056671], [0],
[1056735],[12583120], [], [], [], [], [], []
ORA-01092: ORACLEinstance terminated. Disconnection forced
ORA-00600:internal error code, arguments: [2662], [0], [1056668], [0],
[1056735],[12583120], [], [], [], [], [], []
Process ID: 9651
Session ID: 125Serial number: 5
SYS@LEO1>startup
ORACLE instancestarted.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
DatabaseBuffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
SYS@LEO1>selectstatus from v$instance;
STATUS
------------
OPEN
SYS@LEO1>col member for a50
SYS@LEO1>selectgroup#,member,status from v$logfile;
GROUP# MEMBER STATUS
------------------------------------------------------------ -------
1/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5/u02/app/oracle/oradata/LEO1/redo05.log
1/u02/app/oracle/oradata/LEO1/redo01.log
2/u02/app/oracle/oradata/LEO1/redo02.log
5 /u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
6 rows selected.
SYS@LEO1>selectgroup#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
-------------------- ---------- --- ---------- ----------------
1 2 52428800 YES 1 INACTIVE
2 2 52428800 NO 2 CURRENT
5 2 52428800 YES 0 UNUSED
小结：这种方式打开后的数据库要立刻全备一次，之前的备份已经无效，保证数据的安全性
C RMAN不完全恢复，有全备，有归档，可以保证数据的一致性，会丢失数据，这种方法我们在下面的实验中证明
4.假设在有最后一次全库备份之后，你误删除了一张表，请使用备份+归档来将数据库恢复到删除表之前的状态(不完全恢复)
进入Rman做全库备份
[oracle@leonarding1backup]$ rman target /
Recovery Manager:Release 11.2.0.1.0 - Production on Tue Apr 30 11:08:29 2013
Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected totarget database: LEO1 (DBID=1692458681)
RMAN> backupfull database format
'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn'include current controlfile
plus
archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input;
2> 3> 4>
Starting backup at 30-APR-13
current logarchived
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=22 device type=DISK
RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of backup plus archivelog commandat 04/30/2013 11:13:27
RMAN-06059:expected archived log not found, loss of archived log compromisesrecoverability
ORA-19625: error identifying file/u02/app/oracle/archdata/1_75_813654649.dbf
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
75号归档日志缺失，Rman在操作系统上找不到对应的日志文件
当手工删除了归档日志以后，Rman备份会检测到日志缺失，从而无法进一步继续执行。
所以此时需要手工执行crosscheck过程，之后Rman备份可以恢复正常
RMAN>crosscheck archivelog all; 交叉检查，有3个日志被手工删除
released channel:ORA_DISK_1
allocated channel:ORA_DISK_1
channelORA_DISK_1: SID=22 device type=DISK
validation failedfor archived log
archivedlog file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9STAMP=813790708
validation failedfor archived log
archivedlog file name=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7STAMP=813790702
validation failedfor archived log
archivedlog file name=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8STAMP=813790706
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206
validationsucceeded for archived log
archived log filename=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824
Crosschecked 27 objects
RMAN> deleteexpired archivelog all; 删除所有过期归档日志
released channel:ORA_DISK_1
allocated channel:ORA_DISK_1
channelORA_DISK_1: SID=22 device type=DISK
List of ArchivedLog Copies for database with db_unique_name LEO1
=====================================================================
Key Thrd Seq S Low Time
------- ----------- - ---------
9 1 75 X 26-APR-13
Name: /u02/app/oracle/archdata/1_75_813654649.dbf
7 1 76 X 26-APR-13
Name: /u02/app/oracle/archdata/1_76_813654649.dbf
8 1 77 X 26-APR-13
Name: /u02/app/oracle/archdata/1_77_813654649.dbf
Do you really wantto delete the above objects (enter YES or NO)? y 75 76 77号日志被删除
deleted archivedlog
archived log filename=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9 STAMP=813790708
deleted archivedlog
archived log filename=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7 STAMP=813790702
deleted archivedlog
archived log filename=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8 STAMP=813790706
Deleted 3 EXPIREDobjects
RMAN> backup fulldatabase format 现在Rman备份可以恢复正常，同时删除备份过的归档日志
'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn' includecurrent controlfile
plus
archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input;2> 3> 4>
Starting backup at 30-APR-13
current logarchived
using channelORA_DISK_1
channelORA_DISK_1: starting archived log backup set
channel ORA_DISK_1:specifying archived log(s) in backup set
input archived logthread=1 sequence=1 RECID=10 STAMP=813791152
input archived logthread=1 sequence=2 RECID=11 STAMP=813913400
input archived logthread=1 sequence=3 RECID=12 STAMP=813913401
input archived logthread=1 sequence=4 RECID=13 STAMP=813913405
input archived logthread=1 sequence=5 RECID=14 STAMP=813933703
input archived logthread=1 sequence=6 RECID=15 STAMP=813933734
input archived logthread=1 sequence=7 RECID=16 STAMP=814090435
input archived logthread=1 sequence=8 RECID=17 STAMP=814090645
input archived logthread=1 sequence=9 RECID=18 STAMP=814090877
input archived logthread=1 sequence=10 RECID=19 STAMP=814091008
input archived logthread=1 sequence=11 RECID=20 STAMP=814091028
input archived logthread=1 sequence=12 RECID=21 STAMP=814091032
input archived logthread=1 sequence=13 RECID=22 STAMP=814091036
input archived logthread=1 sequence=14 RECID=23 STAMP=814091057
input archived logthread=1 sequence=15 RECID=24 STAMP=814091184
input archived logthread=1 sequence=16 RECID=25 STAMP=814091638
input archived logthread=1 sequence=17 RECID=26 STAMP=814091658
input archived logthread=1 sequence=18 RECID=27 STAMP=814091709
input archived logthread=1 sequence=19 RECID=28 STAMP=814092394
channelORA_DISK_1: starting piece 1 at 30-APR-13
channelORA_DISK_1: finished piece 1 at 30-APR-13
piecehandle=/u02/app/oracle/backup/arch_bk1_0co8cds4112.rmn tag=TAG20130430T113324comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:07
channelORA_DISK_1: deleting archived log(s)
archived log filename=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152
archived log filename=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400
archived log filename=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401
archived log filename=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405
archived log filename=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703
archived log filename=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734
archived log filename=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435
archived log filename=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645
archived log filename=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877
archived log filename=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008
archived log filename=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028
archived log filename=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032
archived log filename=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036
archived log filename=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057
archived log filename=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184
archived log filename=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638
archived log filename=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658
archived log filename=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709
archived log filename=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394
channelORA_DISK_1: starting archived log backup set
channelORA_DISK_1: specifying archived log(s) in backup set
input archived logthread=1 sequence=1 RECID=30 STAMP=814098353
channelORA_DISK_1: starting piece 1 at 30-APR-13
channelORA_DISK_1: finished piece 1 at 30-APR-13
piecehandle=/u02/app/oracle/backup/arch_bk1_0do8cdsc113.rmn tag=TAG20130430T113324comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01
channelORA_DISK_1: deleting archived log(s)
archived log filename=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353
channelORA_DISK_1: starting archived log backup set
channelORA_DISK_1: specifying archived log(s) in backup set
input archived logthread=1 sequence=20 RECID=29 STAMP=814092405
channelORA_DISK_1: starting piece 1 at 30-APR-13
channelORA_DISK_1: finished piece 1 at 30-APR-13
piecehandle=/u02/app/oracle/backup/arch_bk1_0eo8cdsd114.rmn tag=TAG20130430T113324comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1:deleting archived log(s)
archived log filename=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405
channelORA_DISK_1: starting archived log backup set
channelORA_DISK_1: specifying archived log(s) in backup set
input archived logthread=1 sequence=2 RECID=31 STAMP=814100979
input archived logthread=1 sequence=3 RECID=32 STAMP=814101206
input archived logthread=1 sequence=4 RECID=33 STAMP=814101824
input archived logthread=1 sequence=5 RECID=34 STAMP=814102404
channelORA_DISK_1: starting piece 1 at 30-APR-13
channelORA_DISK_1: finished piece 1 at 30-APR-13
piecehandle=/u02/app/oracle/backup/arch_bk1_0fo8cdse115.rmn tag=TAG20130430T113324comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1:deleting archived log(s)
archived log filename=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979
archived log filename=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206
archived log filename=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824
archived log filename=/u02/app/oracle/archdata/1_5_814098124.dbf RECID=34 STAMP=814102404
Finished backup at30-APR-13
Starting backup at 30-APR-13
using channelORA_DISK_1
channelORA_DISK_1: starting full datafile backup set
channelORA_DISK_1: specifying datafile(s) in backup set
input datafilefile number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf
input datafilefile number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf
input datafilefile number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf
input datafilefile number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf
input datafilefile number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf
channelORA_DISK_1: starting piece 1 at 30-APR-13
channelORA_DISK_1: finished piece 1 at 30-APR-13
piecehandle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn tag=TAG20130430T113336comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:01:25
channelORA_DISK_1: starting full datafile backup set
channelORA_DISK_1: specifying datafile(s) in backup set
including currentcontrol file in backup set
channelORA_DISK_1: starting piece 1 at 30-APR-13
channelORA_DISK_1: finished piece 1 at 30-APR-13
piecehandle=/u02/app/oracle/backup/full_bk1_0ho8cdv5117.rmn tag=TAG20130430T113336comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at30-APR-13
Starting backup at 30-APR-13
current logarchived
using channelORA_DISK_1
channelORA_DISK_1: starting archived log backup set
channelORA_DISK_1: specifying archived log(s) in backup set
input archived logthread=1 sequence=6 RECID=35 STAMP=814102503
channelORA_DISK_1: starting piece 1 at 30-APR-13
channelORA_DISK_1: finished piece 1 at 30-APR-13
piecehandle=/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn tag=TAG20130430T113503comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01
channelORA_DISK_1: deleting archived log(s)
archived log filename=/u02/app/oracle/archdata/1_6_814098124.dbf RECID=35 STAMP=814102503
Finished backup at30-APR-13
Starting Control File and SPFILE Autobackup at30-APR-13
piecehandle=/u02/app/oracle/backup/control/cf_c-1692458681-20130430-01 comment=NONE
Finished ControlFile and SPFILE Autobackup at 30-APR-13
我们现在进入数据库创建一个表leo1并插入三条记录
SYS@LEO1>createtable leo1 (name varchar2(20),age number,riqi date);
Table created.
SYS@LEO1>insertinto leo1 values('leonarding',28,sysdate);
1 row created.
SYS@LEO1>insertinto leo1 values('sun_vn',26,sysdate);
1 row created.
SYS@LEO1>insertinto leo1 values('tiger',18,sysdate);
1 row created.
SYS@LEO1>commit;
Commit complete.
SYS@LEO1>select* from leo1; 完成
NAME AGE RIQI
------------------------------ ---------
leonarding 28 30-APR-13
sun_vn 26 30-APR-13
tiger 18 30-APR-13
SYS@LEO1>selectgroup#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
-------------------- ---------- --- ---------- ----------------
1 2 52428800 NO 7 CURRENT
2 2 52428800 YES 5 INACTIVE
5 2 52428800 YES 6 INACTIVE
我们创建表和插入信息是写入当前日志组1
SYS@LEO1>altersystem switch logfile; 切换日志组
System altered.
SYS@LEO1>altersystem switch logfile;
System altered.
SYS@LEO1>selectgroup#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
-------------------- ---------- --- ---------- ----------------
1 2 52428800 YES 7 INACTIVE
2 2 52428800 YES 8 INACTIVE
5 2 52428800 NO 9 CURRENT
现在组1已经完成归档，如果我们此时勿删除了leo1表，我们可以把数据库恢复到7号归档日志的那一点，就可以恢复勿删除的leo1表了。我们顺便把当前redolog日志也损坏了，看看可不可以扶起数据库来。
SYS@LEO1>droptable leo1 purge; 删除表
我们删除第五组的2个成员
[oracle@leonarding1LEO1]$ rm -rf redo05.log
[oracle@leonarding1LEO1]$ cd disk2/
[oracle@leonarding1disk2]$ rm -rf redo05_b.log
SYS@LEO1>startup
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
DatabaseBuffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
ORA-00313: openfailed for members of log group 5 of thread 1
ORA-00312: onlinelog 5 thread 1:
'/u02/app/oracle/oradata/LEO1/disk2/redo05_b.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
ORA-00312: onlinelog 5 thread 1: '/u02/app/oracle/oradata/LEO1/redo05.log'
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
找不到第五组redo，实际上已经被我们删除了，进入rman进行恢复操作
[oracle@leonarding1 backup]$ rman target /
Recovery Manager:Release 11.2.0.1.0 - Production on Tue Apr 30 12:47:55 2013
Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected totarget database: LEO1 (DBID=1692458681, not open)
RMAN> restoredatabase;
Starting restoreat 30-APR-13
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channelORA_DISK_1: SID=133 device type=DISK
channelORA_DISK_1: starting datafile backup set restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
channelORA_DISK_1: restoring datafile 00001 to/u02/app/oracle/oradata/LEO1/system01.dbf
channelORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/LEO1/sysaux01.dbf
channelORA_DISK_1: restoring datafile 00003 to/u02/app/oracle/oradata/LEO1/undotbs01.dbf
channelORA_DISK_1: restoring datafile 00004 to/u02/app/oracle/oradata/LEO1/users01.dbf
channelORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf
channelORA_DISK_1: reading from backup piece/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn
channelORA_DISK_1: piece handle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmntag=TAG20130430T113336
channelORA_DISK_1: restored backup piece 1
channelORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restoreat 30-APR-13
我们只需恢复到7号归档日志状态点，就可以找回我们删除了的leo1表
RMAN> recoverdatabase until sequence 7 thread 1;
Starting recover at 30-APR-13
using channelORA_DISK_1
starting mediarecovery
channelORA_DISK_1: starting archived log restore to default destination
channelORA_DISK_1: restoring archived log
archived logthread=1 sequence=6
channelORA_DISK_1: reading from backup piece/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn
channel ORA_DISK_1:piece handle=/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmntag=TAG20130430T113503
channelORA_DISK_1: restored backup piece 1
channelORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log filename=/u02/app/oracle/archdata/1_6_814098124.dbf thread=1 sequence=6
media recoverycomplete, elapsed time: 00:00:01
Finished recoverat 30-APR-13
SYS@LEO1>alterdatabase open resetlogs;
Database altered.
resetlogs做的几件事：
1）数据文件头scn号为准，同步控制文件和在线日志文件scn号
2）重新创建redolog日志（创建一个空日志），重置为unused
3）重置归档日志序号从1开始编码
4）让数据库重新进入一个新的生命周期
SYS@LEO1>select* from leo1;
NAME AGE RIQI
------------------------------ ---------
leonarding 28 30-APR-13
sun_vn 26 30-APR-13
tiger 18 30-APR-13
这个表存在说明应用了 sequence为7的归档
小结：到此我们的系列恢复实验完美完成，里面阐述了各种不同环境下的恢复测试，简明了重做日志和归档日志关系，在什么情况下可以恢复到什么级别，牢实掌握如上信息，可以让你遇事不慌张事半功倍。