《Oracle 数据备份与恢复微实践》
新年新群招募： 中国Oracle精英联盟 170513055
群介绍：本群是大家的一个技术分享社区，在这里可以领略大师级的技术讲座，还有机会参加Oracle举办的技术沙龙，与兴趣相投的小伙伴一起笑谈风云起，感悟职场情!
一 数据库版本
SYS@LEO1>show user
USER is "SYS"
SYS@LEO1>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
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>archive log list 数据库处于非归档状态
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 71
Current log sequence 73
我们要先做一个RMAN全备，首先启动归档功能
[oracle@leonarding1 oracle]$ pwd
/u02/app/oracle
[oracle@leonarding1 oracle]$ mkdir archdata 创建一个归档日志目录
在ORACLE10g和11g版本，ORACLE默认的日志归档路径为闪回恢复区，但我们也可以修改为自己指定的目录路径
SYS@LEO1>alter system set log_archive_dest_1='location=/u02/app/oracle/archdata' scope=both;
System altered.
SYS@LEO1>set linesize 300 pagesize 999
设置的归档日志保存路径已经生效
SYS@LEO1>select dest_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>shutdown immediate 关库
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup mount mount状态
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>alter database archivelog; 启动归档模式
Database altered.
SYS@LEO1>alter database open; 打开数据库
Database altered.
注：凡是alter database操作都是对控制文件进行修改
凡是alter system 操作都是对参数文件进行修改
SYS@LEO1>alter system switch logfile; 手工切换日志（不会触发检查点，自动切换会）
System altered.
SYS@LEO1>select sequence#,name,archived,applied from v$archived_log; 查看已经归档的日志信息
SEQUENCE# NAME ARC APPLIED
---------------------------------------------------------------------------------------------------------------------------------------------
73 /u02/app/oracle/archdata/1_73_813654649.dbf YES NO
操作系统层面查看，没有问题也生成了
[oracle@leonarding1 archdata]$ ll
total 5624
-rw-r----- 1 oracle asmadmin 5757952 Apr 25 21:28 1_73_813654649.dbf
SYS@LEO1>archive log list
Database log mode Archive Mode 归档模式
Automatic archival Enabled 自动归档启动
Archive destination /u02/app/oracle/archdata 归档日志目录
Oldest online log sequence 72 旧在线日志序号
Next log sequence to archive 74 下一个归档日志序号
Current log sequence 74 当前日志序号
下面我们就要进行RMAN全库备份了，在此之前还需要设置一下RMAN的环境变量
6）登陆RMAN
[oracle@leonarding1 archdata]$ 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.
connected to target database: LEO1 (DBID=1692458681) 只有连接到目标库才能显示环境变量，这些元数据是存放在控制文件中的
显示当前RMAN的环境变量
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name LEO1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE 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 RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
new RMAN configuration parameters are successfully stored 新man配置参数生效
配置控制文件自动备份并保存到/u02/app/oracle/backup/control目录
[oracle@leonarding1 backup]$ mkdir control
[oracle@leonarding1 control]$ pwd
/u02/app/oracle/backup/control
RMAN> configure controlfile autobackup on; 启动控制文件自动备份
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> configure controlfile autobackup format for device type disk to '/u02/app/oracle/backup/control/cf_%F';
new RMAN configuration parameters: 配置控制文件自动备份目录和格式
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/app/oracle/backup/control/cf_%F';
new RMAN configuration parameters are successfully stored
调整备份介质保留期为7天
RMAN> configure retention policy to recovery window of 7 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
显示配置后RMAN环境变量
RMAN> show all;
RMAN configuration parameters for database with db_unique_name LEO1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/app/oracle/backup/control/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; # default
带颜色的就是我们刚刚修改过的变量
7）启动RMAN的压缩备份功能对数据库进行全备Oracle 10g只压缩RMAN元数据11g真正压缩了数据
backup as compressed backupset full 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; 全部备份完之后，删除备份过的旧归档日志
如果我们想使用默认通道默认配置备份一次数据库，同时删除备份过的归档日志，那么命令为
RMAN> backup as compressed backupset full database include current controlfile plus archivelog delete all input;
Starting backup at 26-APR-13 备份时间
current log archived
allocated channel: ORA_DISK_1 默认通道磁盘
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set 先压缩备份的归档日志
channel ORA_DISK_1: specifying archived log(s) in backup set 备份了73 74归档日志
input archived log thread=1 sequence=73 RECID=1 STAMP=813706084
input archived log thread=1 sequence=74 RECID=2 STAMP=813739820
channel ORA_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_01o81bpd_1_1 tag=TAG20130426T065020 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒
channel ORA_DISK_1: deleting archived log(s) 删除已备份的归档日志73 74
archived log file name=/u02/app/oracle/archdata/1_73_813654649.dbf RECID=1 STAMP=813706084
archived log file name=/u02/app/oracle/archdata/1_74_813654649.dbf RECID=2 STAMP=813739820
Finished backup at 26-APR-13
Starting backup at 26-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set 再压缩备份数据文件
channel ORA_DISK_1: specifying datafile(s) in backup set 指定备份如下数据文件
input datafile file number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf
input datafile file number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf
input datafile file number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_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_02o81bpf_1_1 tag=TAG20130426T065022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45备份完成用时1分45秒
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set 这个是备份数据文件的同时包含备份控制文件
channel ORA_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_1 tag=TAG20130426T065022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒
Finished backup at 26-APR-13
Starting backup at 26-APR-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=75 RECID=3 STAMP=813739930 备份75归档日志
channel ORA_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_04o81bsq_1_1 tag=TAG20130426T065210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒
channel ORA_DISK_1: deleting archived log(s) 删除已备份的归档日志75
archived log file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=3 STAMP=813739930
Finished backup at 26-APR-13
Starting Control File and SPFILE Autobackup at 26-APR-13 启动控制文件和参数文件自动备份
piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-APR-13 完成自动备份
在操作系统上都可以找到对应的备份集并且已经删除了备份过的旧归档日志
[oracle@leonarding1 backup]$ pwd
/u02/app/oracle/backup
[oracle@leonarding1 backup]$ ll
total 249468
drwxr-xr-x 2 oracle oinstall 4096 Apr 26 06:52 control
-rw-r----- 1 oracle asmadmin 2790912 Apr 26 06:50 DB_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:52 DB_03o81bso_1_1
-rw-r----- 1 oracle asmadmin 7168 Apr 26 06:52 DB_04o81bsq_1_1
[oracle@leonarding1 backup]$ cd control/
[oracle@leonarding1 control]$ ll
total 9600
-rw-r----- 1 oracle asmadmin 9830400 Apr 26 06:52 cf_c-1692458681-20130426-00
[oracle@leonarding1 archdata]$ pwd
/u02/app/oracle/archdata
[oracle@leonarding1 archdata]$ ll 归档日志全没有了
total 0
新的归档日志是从76号开始，75号之前都已经备份并删除
SYS@LEO1>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
Oldest online log sequence 74
Next log sequence to archive 76
Current log sequence 76
到此我们的备份准备已经完成，稍微休息一下：）坐车上班班
8）SYS@LEO1>select status from v$instance; 检查数据库状态
STATUS
------------
OPEN
LEO1@LEO1>show parameter control_files 我们检查一下控制文件个数
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u02/app/oracle/oradata/LEO1/control01.ctl,
/u02/app/oracle/oradata/LEO1/control02.ctl
[oracle@leonarding1 LEO1]$ ll 操作系统上也是2个没有问题
total 2618136
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 09:01 control01.ctl
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 09:01 control02.ctl
一般控制文件丢失大多数都是被误删除了，用rm 命令删除control01.ctl文件
[oracle@leonarding1 trace]$ pwd
/u02/app/oracle/diag/rdbms/leo1/LEO1/trace
[oracle@leonarding1 trace]$ tail -10f alert_LEO1.log 实时监控告警日志看看有什么变化
Fri Apr 26 06:50:20 2013
Thread 1 advanced to log sequence 75 (LGWR switch)
Current log# 3 seq# 75 mem# 0: /u02/app/oracle/oradata/LEO1/redo03.log
Archived Log entry 2 added for thread 1 sequence 74 ID 0x64e13fb9 dest 1:
Fri Apr 26 06:52:10 2013
ALTER SYSTEM ARCHIVE LOG
Fri Apr 26 06:52:10 2013
Thread 1 advanced to log sequence 76 (LGWR switch)
Current log# 1 seq# 76 mem# 0: /u02/app/oracle/oradata/LEO1/redo01.log
Archived Log entry 3 added for thread 1 sequence 75 ID 0x64e13fb9 dest 1:
[oracle@leonarding1 LEO1]$ rm control01.ctl 模拟control01文件丢失的场景
LEO1@LEO1>create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off;
create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off
l 我们创建一个表空间，此时突然报错
ERROR at line 1:
ORA-00210: cannot open the specified control file 不能打开指定的控制文件
ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl' 控制文件丢失
ORA-27041: unable to open file 无法打开这个文件
Linux-x86_64 Error: 2: No such file or directory 找不到这个文件，好恐怖bless，赶紧看看alert日志
Additional information: 3
Alert_LEO1.log日志内容
create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off
ORA-210 signalled during: 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: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
是不是和上面报的错误信息一样啊，由于是我们自己搞的鬼，所以我们明白是怎么回事，如果在生产库上就要首先查看日志信息进行分析啦，好了现在我们开始修复吧->start on
思路：我们首先要清楚oracle为了保证其稳定性，控制文件都是多路复用的，如果使用dbca安装10g默认有3个控制文件 11g有两个，我们删除了其中一个，可能有的朋友会说，我们镜像出来一个控制文件不就好了么，没错思路很正确，但前提是要关闭数据库使所有的SCN号一致，也有可能你会遇上无法立即关闭的情况，木有办法只能强制关闭了，
SYS@LEO1>shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@LEO1>shutdown abort
ORACLE instance shut down.
[oracle@leonarding1 LEO1]$ cp control02.ctl control01.ctl 使用完好的控制文件恢复被删除的控制文件
[oracle@leonarding1 LEO1]$ ll
total 2618136
-rw-r----- 1 oracle oinstall 9748480 Apr 26 09:34 control01.ctl
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 09:28 control02.ctl
SYS@LEO1>startup mount 启动到mount状态没有报错，说明我们恢复成功了
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>select checkpoint_change# from v$database; 数据库全局SCN号，放在控制文件里
CHECKPOINT_CHANGE#
--------------------------------
909922
SYS@LEO1>select name,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>select name,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>select name,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>select status from v$instance;
STATUS
------------
MOUNTED
我们在开打数据库的一霎那就会启动实例恢复，看alert日志即可
SYS@LEO1>alter database open;
Database altered.
Alert日志内容
Fri Apr 26 09:50:08 2013
alter database open 打开数据库
Beginning crash recovery of 1 threads 进行实例恢复
parallel recovery started with 2 processes 启动2个恢复进程
Started redo scan
Completed redo scan
read 30 KB redo, 31 data blocks need recovery
Started redo application at
Thread 1: logseq 76, block 23074
Recovery of Online Redo Log: Thread 1 Group 1 Seq 76 Reading mem 0 从76号日志为起始位置
Mem# 0: /u02/app/oracle/oradata/LEO1/redo01.log 应用redo日志进行恢复
Completed redo application of 0.02MB
Completed crash recovery 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: STARTING ARCH PROCESSES 启动归档进程进行归档
Fri Apr 26 09:50:08 2013
ARC0 started with pid=22, OS id=8396
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Apr 26 09:50:09 2013
ARC1 started with pid=23, OS id=8400
Fri Apr 26 09:50:09 2013
ARC2 started with pid=24, OS id=8404
ARC1: Archival started
Fri Apr 26 09:50:09 2013
ARC3 started with pid=25, OS id=8408
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 77 (thread open)
Thread 1 opened at log sequence 77
Current log# 2 seq# 77 mem# 0: /u02/app/oracle/oradata/LEO1/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Apr 26 09:50:09 2013
SMON: enabling cache recovery SMON进程负责实例的恢复
Archived Log entry 4 added for thread 1 sequence 76 ID 0x64e13fb9 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Apr 26 09:50:14 2013
QMNC started with pid=26, OS id=8412
Completed: alter database open 到此我们完成数据库的open动作，实例恢复完毕
Fri Apr 26 09:50:18 2013
Starting background process CJQ0
Fri Apr 26 09:50:18 2013
CJQ0 started with pid=30, OS id=8436
从alter日志的流程上我们就可以看出，oracle实例恢复的内容过程是什么样的，通过以上案例我们即了解了控制文件的恢复过程又了解了数据库实例的恢复过程，可谓一举两得，大家好好的消化消化，休息一下，该上班工作啦：）
9）下了班我们继续，上次讲到了，使用copy方式来恢复控制文件，下面再讲一种使用备份集来恢复控制文件的方法。
【参】Books-> Backup and Recovery Reference -> RESTORE 和 RECOVER 参考官方文档是个好习惯
SYS@LEO1>shutdown immediate 我们先关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@leonarding1 LEO1]$ rm -rf control01.ctl 删除控制文件
SYS@LEO1>startup
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
ORA-00205: error in identifying control file, check alert log for more info
指定的控制文件错误，检查alert日志获取更多信息
[oracle@leonarding1 trace]$ tail -20f alert_LEO1.log
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl' 这写着1号控制文件丢失
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory 找不到这个文件
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Apr 26 19:44:05 2013
Checker run found 1 new persistent data failures
SYS@LEO1>shutdown immediate 关库
ORA-01507: database not mounted
ORACLE instance shut down.
[oracle@leonarding1 ~]$ rman target sys/oracle 链接RMAN
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 26 19:49:00 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started) 库没有启动
RMAN> startup nomount 把库启动到nomount状态
Oracle instance started
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
有一个地方容易出错，大家都喜欢用这条语句来恢复
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
Starting restore at 26-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130426
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130425
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130424
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130423
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130422
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130421
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130420
channel ORA_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/2013 20:01:04
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
报错：说找不到指定的备份集
RMAN> show all;
RMAN configuration parameters for database with db_unique_name LEO1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE 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 restore at 26-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/u02/app/oracle/oradata/LEO1/control01.ctl
output file name=/u02/app/oracle/oradata/LEO1/control02.ctl
Finished restore at 26-APR-13
[oracle@leonarding1 LEO1]$ ll
total 2618136
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 20:17 control01.ctl
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 20:17 control02.ctl
我们一起恢复了所有的控制文件
RMAN> alter database mount; 现在数据库可以正常加载了对吧
database mounted
released channel: ORA_DISK_1
那我们可以alter database open来打开数据库吗，显然是不行的，大家知道为什么吗？
你想想如果这个控制文件是从10天之前的一个备份还原的与当前的数据库物理结构能一致吗！
显然是不可以的，我们只有用备份在重新同步数据库
RMAN> restore database; RMAN备份还原restore（复制）数据文件
Starting restore at 26-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/LEO1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/LEO1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/LEO1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/backup/DB_02o81bpf_1_1
channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_02o81bpf_1_1 tag=TAG20130426T065022
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
Finished restore at 26-APR-13
RMAN> recover database; 应用redo日志恢复recover（同步）数据库
Starting recover at 26-APR-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 75 is already on disk as file /u02/app/oracle/oradata/LEO1/redo03.log
archived log for thread 1 with sequence 76 is already on disk as file /u02/app/oracle/oradata/LEO1/redo01.log
archived log for thread 1 with sequence 77 is already on disk as file /u02/app/oracle/oradata/LEO1/redo02.log
archived log file name=/u02/app/oracle/oradata/LEO1/redo03.log thread=1 sequence=75
archived log file name=/u02/app/oracle/oradata/LEO1/redo01.log thread=1 sequence=76
archived log file name=/u02/app/oracle/oradata/LEO1/redo02.log thread=1 sequence=77
media recovery complete, elapsed time: 00:00:14 这就是介质恢复，要把数据文件同步到损坏的前一刻
因为这是不完全恢复，因此我们不能用alter database open来打开数据库
RMAN> alter database open resetlogs;
database opened
SYS@LEO1>select status from v$instance; 数据库已打开，可以正常使用
STATUS
------------
OPEN
小结：因为我们进行了不完全恢复，恢复到之前的某一点，现在数据库以这点为一个新的起点（相当是一个焕然一新的库），resetlogs就是重置归档日志从1开始编码，之前的归档全部无效。
SYS@LEO1>alter system switch logfile; 我们重新切换一次
System altered.
[oracle@leonarding1 oracle]$ cd archdata
[oracle@leonarding1 archdata]$ ll
total 14028
-rw-r----- 1 oracle asmadmin 222720 Apr 26 21:05 1_1_813790699.dbf
-rw-r----- 1 oracle asmadmin 5632 Apr 26 20:58 1_75_813654649.dbf
-rw-r----- 1 oracle asmadmin 11844608 Apr 26 20:58 1_76_813654649.dbf
-rw-r----- 1 oracle asmadmin 2284032 Apr 26 20:58 1_77_813654649.dbf
注意：归档日志编码重置了，从1开始了，75 76 77归档日志全部无效了，为了不碍眼你可以删除掉。
2.模拟状态为inactive的日志损坏的恢复实验(完全恢复)
Redo log：是用于记录数据库所有变化信息的文件，只要有底层的块变化就会产生信息，当遇到需要恢复的场景，我们可以利用这些redo log进行实例恢复和介质恢复。Redo log日志通常情况下都是分成若干组存在数据库中，每个组都会有2个以上的成员互为镜像（保证高可用），每个组循环切换使用。下面我们来看看redo log的恢复操作。
SYS@LEO1>select group#,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@leonarding1 LEO1]$ 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>select group#,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>select group#,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>archive log list 已经是归档模式
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
我们做几次日志切换，把所有redo log 都使用一遍
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
我们切换了3次生成了3个归档日志2 3 4号，下一次该归档的是5号
[oracle@leonarding1 archdata]$ ll
total 30708
-rw-r----- 1 oracle asmadmin 222720 Apr 26 21:05 1_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:03 1_3_813790699.dbf
-rw-r----- 1 oracle asmadmin 3072 Apr 28 07:03 1_4_813790699.dbf
现在我们看到已经切换到组2为当前redo 组了，组1和组3已经全部归档
SYS@LEO1>select group#,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>select group#,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>shutdown immediate 关库
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup 重启
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
SYS@LEO1>select group#,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>select group#,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>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 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 log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/LEO1/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
非也非也，我们在alert日志中发现了告警踪迹“打开组1成员失败并且指出了失败文件路径”，那为什么在sqlplus中没有告警信息呢？？？从Oracle设计的理念上可以窥视出，它想让数据库尽可能的活下来，只要不需要的动作就可以先忽略掉（尽管已经检测出来了，会在状态上标注为invalid）。那么ok，我们开始恢复
【参】Books->Administrator’s Guide -> 6 Managing the Redo Log ->Dropping Redo Log Groups and Members
SYS@LEO1>shutdown immediate 关库
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup mount 启动到mount状态
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
刚才我们是从操作系统层面删除的，但控制文件中还保留了redolog信息，我们在从数据库层面删除
SYS@LEO1>ALTER DATABASE 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 already part of database
SYS@LEO1>alter database add logfile member '/u02/app/oracle/oradata/LEO1/redo01.log' to group 1;
Database altered.
SYS@LEO1>alter database open; 打开数据库
Database altered.
SYS@LEO1>select group#,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>select group#,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 system switch logfile;
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
这次是不是状态变成正常的啦，用到了自动监测，到此我们模拟inactive日志恢复完成
SYS@LEO1>select group#,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>ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
这个只是在控制文件上删除，你还需要在操作系统层面上删除
[oracle@leonarding1 LEO1]$ rm redo03.log
[oracle@leonarding1 disk2]$ rm redo03_b.log
SYS@LEO1>ALTER DATABASE 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>select group#,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>select group#,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> alter database clear logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
小结：在进行恢复redolog日志之前一定要先掌握其状态要领，了解redolog正处于什么状态，会不会丢失数据，根据其特点采用合理的恢复方案。
3.模拟状态为active的日志损坏的数据恢复实验（不完全恢复）
首先我们先看看Active日志特点
Active：表示日志是活动的但不是当前正在使用的redolog，active意味着checkpoint动作尚未完成（脏数据还没有完全刷到磁盘上）or 归档模式下该日志的内容还没有完全归档，这两种情况下都会让日志为active状态，在实例恢复时也会用到此日志文件，因此该日志文件不能被覆盖。
Active和Current redolog日志有一个共同之处就是实例恢复时会用到这个日志文件 ，它们都是checkpoint检查点还没有完成时保护数据安全的最后屏障，如果它们损坏or勿删除了会导致数据的丢失，这是非常危险的，就算有RMAN备份也不能恢复current 状态的数据。
有的朋友会很奇怪不是说有全库备份就可以完事大吉了嘛！这是一种完美主义，没有一种四海皆准的备份，如果你在RMAN备份时候仔细查看了备份日志就会发现一个惊奇的秘密，RMAN是不会备份
Redolog的，但会备份archivelog，redolog是用多路复用方式镜像备份的，因此如果删除了current 重做日志那么就会丢失数据，我们要警惕这种场景，做一名优秀的DBA！
注：基本上，如果是当前在线日志受损坏，很难不丢数据。但最差的情况下是可以恢复到上一个可用的归档日志时间点的。
恢复方法：
A 使用镜像文件来恢复，不会丢失数据
B 隐含参数_allow_resetlogs_corruption=TRUE 进行不一致性恢复，会丢失数据
C RMAN不完全恢复，有全备，有归档，可以保证数据的一致性，会丢失数据
###################################################################################
A 使用镜像文件来恢复，不会丢失数据
SYS@LEO1>select group#,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@leonarding1 LEO1]$ mv redo02.log redo02.log.bak 我们改个名字
SYS@LEO1>shutdown abort 我们强制关库，按理说应该使用current log进行实例恢复，但current log备我们删除了，连库都起不来，如何恢复呢
SYS@LEO1>startup 启动的时候是可以正常启动的，但在告警日志里面会报错
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 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>select group#,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>alter system switch logfile;
System altered.
SYS@LEO1>select group#,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>alter database drop logfile member '/u02/app/oracle/oradata/LEO1/redo02.log';
Database altered.
SYS@LEO1>alter database add logfile member '/u02/app/oracle/oradata/LEO1/redo02.log' to group 2;
Database altered.
我们先删除在创建，然后多切换几次，redolog就恢复了
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>select group#,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>shutdown abort 强制关库
ORACLE instance shut down.
[oracle@leonarding1 LEO1]$ mv redo02.log redo02.log.bak 修改第一个成员
[oracle@leonarding1 disk2]$ mv redo02_b.log redo02_b.log.bak 修改第二个成员
SYS@LEO1>startup 启动库
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1 打开redo日志失败
ORA-00312: online log 2 thread 1: '/u02/app/oracle/oradata/LEO1/redo02.log' 指明哪个日志
ORA-27037: unable to obtain file status 获取不到日志状态
Linux-x86_64 Error: 2: No such file or directory 因为没有这个文件
Additional information: 3
ORA-00312: online log 2 thread 1:
'/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log' 第一个成员找不到我们看看第二个成员有没有
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory 也是没有这个文件对吧
Additional information: 3
SYS@LEO1>select status from v$instance; 现在实例正在处于mount状态
STATUS
------------
MOUNTED
我们可以使用隐含参数_allow_resetlogs_corruption=true 进行不一致恢复，这是oracle给予我们提供的一种临时急救方法，一般不到万不得已不建议使用，因为这样会丢失数据的，而丢失数据是不能接受的。我们继续
SYS@LEO1>alter system set "_allow_resetlogs_corruption"=true scope=spfile; 静态修改需重启生效
System altered.
SYS@LEO1>shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup mount 启动到mount状态
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>recover database until cancel;
until cancel：一直恢复到数据库能够恢复的最后一个日志，尽最大努力恢复
场景：current/active log有丢失情况下或者有归档日志丢失的情况下，一直可恢复到丢失前的最后一个日志，则中止。
SYS@LEO1>recover database until cancel;
ORA-00279: change 1056653 generated at 04/30/2013 08:35:09 needed for thread 1
ORA-00289: suggestion : /u02/app/oracle/archdata/1_19_813790699.dbf
ORA-00280: change 1056653 for thread 1 is in sequence #19
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel 这时需手工输入cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/app/oracle/oradata/LEO1/system01.dbf'
需要resetlogs方式开打数据库，也就是非一致性打开
Resetlogs做的几件事：
1）数据文件头scn号为准，同步控制文件和在线日志文件scn号
2）重新创建redolog日志（创建一个空日志），重置为unused
3）重置归档日志序号从1开始编码
-rw-r----- 1 oracle asmadmin 222720 Apr 26 21:05 1_1_813790699.dbf
-rw-r----- 1 oracle asmadmin 7168 Apr 30 10:25 1_1_814098124.dbf
4）让数据库重新进入一个新的生命周期
SYS@LEO1>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server 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: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1056668], [0],
[1056735], [12583120], [], [], [], [], [], []
Process ID: 9651
Session ID: 125 Serial number: 5
SYS@LEO1>startup
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
SYS@LEO1>select status from v$instance;
STATUS
------------
OPEN
SYS@LEO1>col member for a50
SYS@LEO1>select group#,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>select group#,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@leonarding1 backup]$ 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 to target database: LEO1 (DBID=1692458681)
RMAN> backup full 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 log archived
using target database 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 command at 04/30/2013 11:13:27
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u02/app/oracle/archdata/1_75_813654649.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
75号归档日志缺失，Rman在操作系统上找不到对应的日志文件
当手工删除了归档日志以后，Rman备份会检测到日志缺失，从而无法进一步继续执行。
所以此时需要手工执行crosscheck过程，之后Rman备份可以恢复正常
RMAN> crosscheck archivelog all; 交叉检查，有3个日志被手工删除
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
validation failed for archived log
archived log file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9 STAMP=813790708
validation failed for archived log
archived log file name=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7 STAMP=813790702
validation failed for archived log
archived log file name=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8 STAMP=813790706
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824
Crosschecked 27 objects
RMAN> delete expired archivelog all; 删除所有过期归档日志
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
List of Archived Log 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 want to delete the above objects (enter YES or NO)? y 75 76 77号日志被删除
deleted archived log
archived log file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9 STAMP=813790708
deleted archived log
archived log file name=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7 STAMP=813790702
deleted archived log
archived log file name=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8 STAMP=813790706
Deleted 3 EXPIRED objects
RMAN> backup full database format 现在Rman备份可以恢复正常，同时删除备份过的归档日志
'/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 log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=10 STAMP=813791152
input archived log thread=1 sequence=2 RECID=11 STAMP=813913400
input archived log thread=1 sequence=3 RECID=12 STAMP=813913401
input archived log thread=1 sequence=4 RECID=13 STAMP=813913405
input archived log thread=1 sequence=5 RECID=14 STAMP=813933703
input archived log thread=1 sequence=6 RECID=15 STAMP=813933734
input archived log thread=1 sequence=7 RECID=16 STAMP=814090435
input archived log thread=1 sequence=8 RECID=17 STAMP=814090645
input archived log thread=1 sequence=9 RECID=18 STAMP=814090877
input archived log thread=1 sequence=10 RECID=19 STAMP=814091008
input archived log thread=1 sequence=11 RECID=20 STAMP=814091028
input archived log thread=1 sequence=12 RECID=21 STAMP=814091032
input archived log thread=1 sequence=13 RECID=22 STAMP=814091036
input archived log thread=1 sequence=14 RECID=23 STAMP=814091057
input archived log thread=1 sequence=15 RECID=24 STAMP=814091184
input archived log thread=1 sequence=16 RECID=25 STAMP=814091638
input archived log thread=1 sequence=17 RECID=26 STAMP=814091658
input archived log thread=1 sequence=18 RECID=27 STAMP=814091709
input archived log thread=1 sequence=19 RECID=28 STAMP=814092394
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0co8cds4112.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152
archived log file name=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400
archived log file name=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401
archived log file name=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405
archived log file name=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703
archived log file name=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734
archived log file name=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435
archived log file name=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645
archived log file name=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877
archived log file name=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008
archived log file name=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028
archived log file name=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032
archived log file name=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036
archived log file name=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057
archived log file name=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184
archived log file name=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638
archived log file name=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658
archived log file name=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709
archived log file name=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=30 STAMP=814098353
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0do8cdsc113.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=29 STAMP=814092405
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0eo8cdsd114.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=31 STAMP=814100979
input archived log thread=1 sequence=3 RECID=32 STAMP=814101206
input archived log thread=1 sequence=4 RECID=33 STAMP=814101824
input archived log thread=1 sequence=5 RECID=34 STAMP=814102404
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0fo8cdse115.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979
archived log file name=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206
archived log file name=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824
archived log file name=/u02/app/oracle/archdata/1_5_814098124.dbf RECID=34 STAMP=814102404
Finished backup at 30-APR-13
Starting backup at 30-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf
input datafile file number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf
input datafile file number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn tag=TAG20130430T113336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/full_bk1_0ho8cdv5117.rmn tag=TAG20130430T113336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-APR-13
Starting backup at 30-APR-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=35 STAMP=814102503
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn tag=TAG20130430T113503 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_6_814098124.dbf RECID=35 STAMP=814102503
Finished backup at 30-APR-13
Starting Control File and SPFILE Autobackup at 30-APR-13
piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130430-01 comment=NONE
Finished Control File and SPFILE Autobackup at 30-APR-13
我们现在进入数据库创建一个表leo1并插入三条记录
SYS@LEO1>create table leo1 (name varchar2(20),age number,riqi date);
Table created.
SYS@LEO1>insert into leo1 values('leonarding',28,sysdate);
1 row created.
SYS@LEO1>insert into leo1 values('sun_vn',26,sysdate);
1 row created.
SYS@LEO1>insert into 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>select group#,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>alter system switch logfile; 切换日志组
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>select group#,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>drop table leo1 purge; 删除表
我们删除第五组的2个成员
[oracle@leonarding1 LEO1]$ rm -rf redo05.log
[oracle@leonarding1 LEO1]$ cd disk2/
[oracle@leonarding1 disk2]$ rm -rf redo05_b.log
SYS@LEO1>startup
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1:
'/u02/app/oracle/oradata/LEO1/disk2/redo05_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 1: '/u02/app/oracle/oradata/LEO1/redo05.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 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 to target database: LEO1 (DBID=1692458681, not open)
RMAN> restore database;
Starting restore at 30-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/LEO1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/LEO1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/LEO1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn
channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn tag=TAG20130430T113336
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 30-APR-13
我们只需恢复到7号归档日志状态点，就可以找回我们删除了的leo1表
RMAN> recover database until sequence 7 thread 1;
Starting recover at 30-APR-13
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_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.rmn tag=TAG20130430T113503
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/app/oracle/archdata/1_6_814098124.dbf thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-APR-13
SYS@LEO1>alter database 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的归档
小结：到此我们的系列恢复实验完美完成，里面阐述了各种不同环境下的恢复测试，简明了重做日志和归档日志关系，在什么情况下可以恢复到什么级别，牢实掌握如上信息，可以让你遇事不慌张事半功倍。
控制文件恢复，重做日志恢复，完全恢复，不完全恢复，备份
刘盛Leonarding
2013.4.30
天津&spring
分享技术~成就梦想
Blog：www.leonarding.com
##########################################################################################
如果喜欢我的文章就请扫下面二维码吧！关注微信号：leonarding_public
在这里你能得到技术、实事、热点消息等新兴事物的思考和观点，别的地方可能没有的东西。我将为大家提供最新技术与资讯动态，传递正能量。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26686207/viewspace-1475577/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26686207/viewspace-1475577/
扫一扫
563
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
