rman模拟故障恢复实验

补充1：restore database和recover database的区别
restore 只是用备份来还原,recover是用archivelog或者online log

举例说明：
假设我时间点A,做了个备份,时间点B数据库挂了
restore database ;// 这个操作利用时间点A做的备份来还原,返回到时间点A
recover database ;//这个操作利用archivelog and online log做recover,从时间点A,推进到时间点B
restore 是转储 也是還原被损坏文件(RMAN经常用)
recover 是恢复 通过redo log & archive log恢复


补充2：rman模拟故障恢复过程（所有spfile、controlfile、datafile均丢失）-前提是数据库故障前有rman备份
1）RMAN> show all;
CONFIGURE CONTROLFILE AUTOBACKUP ON;   ---控制文件备份的同时，会自动备份参数文件

SQL> select dbid from v$database;  --1669126943

2）先给数据库做个备份：
RMAN> backup database format '/orabak/whole_%d_%U';
Starting backup at 18-DEC-15
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=00002 name=/u01/oradata/tinadb/sysaux01.dbf
input datafile file number=00001 name=/u01/oradata/tinadb/system01.dbf
input datafile file number=00005 name=/u01/oradata/tinadb/ts_tina01.dbf
input datafile file number=00003 name=/u01/oradata/tinadb/undotbs01.dbf
input datafile file number=00004 name=/u01/oradata/tinadb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-DEC-15
channel ORA_DISK_1: finished piece 1 at 18-DEC-15
piece handle=/orabak/whole_TINADB_0vqp4nrf_1_1 tag=TAG20151218T143214 comment=NONE   ---注意这行，可以看到备份集的具体名称和tag标签
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 18-DEC-15

Starting Control File and SPFILE Autobackup at 18-DEC-15
piece handle=/u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp comment=NONE
--注意这行，我们可以看到控制文件和spfile都自动备份了，因为我配置了CONFIGURE CONTROLFILE AUTOBACKUP ON;

Finished Control File and SPFILE Autobackup at 18-DEC-15

[root@oratest orabak]# cd /orabak
[root@oratest orabak]# ll
-rw-r-----. 1 oracle oinstall 1238605824 Dec 18 14:32 whole_TINADB_0vqp4nrf_1_1  

[root@oratest orabak]# cd /u01/oracle/TINADB/autobackup/2015_12_18/
[root@oratest 2015_12_18]# ll
-rw-r-----. 1 oracle oinstall 10158080 Dec 18 14:32 o1_mf_s_898785171_c77b4mv5_.bkp
  
3）模拟spfile，controlfile，datafile全部都丢失
SQL> shutdown immediate;

删除文件：
[oracle@oratest dbs]$ cd /u01/oracle/dbs/
[oracle@oratest dbs]$ rm -f pfiletinadb.ora  spfiletinadb.ora  

[oracle@oratest dbs]$ cd /u01/oradata/tinadb/
[oracle@oratest dbs]$ rm -f *.dbf  redo*.log  control01.ctl

[root@oratest test]# cd /u01/fast_recovery_area/tinadb/
[root@oratest tinadb]# rm -f control02.ctl   

4)以oracle默认的参数文件init.ora启动后，恢复spfile
RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/dbs/inittinadb.ora'
starting Oracle instance without parameter file for retrieval of spfile  
Oracle instance started
Total System Global Area     158662656 bytes
Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes  

RMAN> set dbid=1669126943   --一定要设置dbid才行
executing command: SET DBID

RMAN> restore spfile from autobackup;
Starting restore at 18-DEC-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151218
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151217
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151216
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151215
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151214
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151213
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151212
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 12/18/2015 15:20:27
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

由于使用的默认参数文件启动，如果更改过autobackup的位置或格式（allocate或format），
恢复时就会找不到路径，可以从警告日志找到autobackup的位置，用日志号最新一个恢复参数文件  


RMAN> restore spfile from '/u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp';
---刚刚备份时生成的那个controlfile和spfile的备份
Starting restore at 18-DEC-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 18-DEC-15

5）以新生成的spfile启动库并恢复控制文件
RMAN> shutdown immediate;        
Oracle instance shut down

RMAN> set dbid=1669126943
executing command: SET DBID

RMAN> startup nomount;         
connected to target database (not started)
Oracle instance started
Total System Global Area    2087780352 bytes
Fixed Size                     2229944 bytes
Variable Size                520096072 bytes
Database Buffers            1560281088 bytes
Redo Buffers                   5173248 bytes

RMAN> restore controlfile from autobackup;
Starting restore at 18-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

recovery area destination: /u01/oracle/
database name (or database unique name) used for search: TINADB
channel ORA_DISK_1: AUTOBACKUP /u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151218
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/tinadb/control01.ctl
output file name=/u01/fast_recovery_area/tinadb/control02.ctl    --还是原来的那两个目录
Finished restore at 18-DEC-15

6）恢复库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

RMAN> restore database;   --利用之前的全备恢复到备份的时刻状态
Starting restore at 18-DEC-15
using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/oradata/tinadb/system01.dbf
skipping datafile 2; already restored to file /u01/oradata/tinadb/sysaux01.dbf
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 00003 to /u01/oradata/tinadb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/tinadb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/tinadb/ts_tina01.dbf
channel ORA_DISK_1: reading from backup piece /orabak/whole_TINADB_0vqp4nrf_1_1
channel ORA_DISK_1: piece handle=/orabak/whole_TINADB_0vqp4nrf_1_1 tag=TAG20151218T143214
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-DEC-15

RMAN> recover database;  ---利用归档和在线日志回复数据库到最新状态
Starting recover at 18-DEC-15
using channel ORA_DISK_1
starting media recovery

archived log for thread 1 with sequence 109 is already on disk as file /u01/oracle/TINADB/archivelog/1_109_898687982.dbf
archived log for thread 1 with sequence 110 is already on disk as file /u01/oracle/TINADB/archivelog/1_110_898687982.dbf
archived log file name=/u01/oracle/TINADB/archivelog/1_109_898687982.dbf thread=1 sequence=109
archived log file name=/u01/oracle/TINADB/archivelog/1_110_898687982.dbf thread=1 sequence=110
unable to find archived log
archived log thread=1 sequence=111
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2015 15:39:45
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 111 and starting SCN of 1889531


可见，出现此错误的原因是恢复需要的日志记录在控制文件或恢复目录中找不到。解决方法分两种情况：
1.如果相关的日志存在且可用的话，就将此日志记录添加到控制文件或恢复目录中。
2.如果相关的日志已经被删除了或不可用了，那么就按照错误的提示scn将数据库恢复到此scn，本案例是2292709。
也就是说此时数据库只能进行不完全恢复了，在打开数据库时得使用resetlogs打开。

RMAN> recover database until scn 1889531;
Starting recover at 18-DEC-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-DEC-15

RMAN> alter database open resetlogs;
database opened

整个过程恢复完成！！！


补充3：rman恢复数据库到某一个指定时刻---基于归档日志

1）当前正常环境
SQL> select * from tina.salgrade;
     GRADE LOSAL    HISAL
---------- ---------- ----------
1   700     1200
2 1201     1400
3 1401     2000
4 2001     3000
5 3001     9999

RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
validation succeeded for archived log
archived log file name=/u01/oracle/TINADB/archivelog/1_5_898789368.dbf RECID=101 STAMP=898791906
validation succeeded for archived log
archived log file name=/u01/oracle/TINADB/archivelog/1_6_898789368.dbf RECID=102 STAMP=898791928
validation succeeded for archived log
archived log file name=/u01/oracle/TINADB/archivelog/1_7_898789368.dbf RECID=103 STAMP=898792550
Crosschecked 3 objects

SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
--------------------------------------------
18-DEC-15 16.35.09.058343 PM +08:00

SQL> alter system archive log current;
System altered.


2）误操作
SQL> drop table tina.salgrade;   ---16:35之后进行的操作
Table dropped.

SQL> select * from tina.salgrade;
select * from tina.salgrade                *
ERROR at line 1:
ORA-00942: table or view does not exist

3）利用归档日志，回退到操作之前
启动库到mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size     2229944 bytes
Variable Size   520096072 bytes
Database Buffers 1560281088 bytes
Redo Buffers     5173248 bytes
Database mounted.

回退：
RMAN> run{
set until time "to_date('2015-12-18 16:35','yyyy-mm-dd hh24:mi')";
restore database;
recover database;
}2> 3> 4> 5>

executing command: SET until clause

Starting restore at 18-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 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 00002 to /u01/oradata/tinadb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/tinadb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/tinadb/ts_tina01.dbf
channel ORA_DISK_1: reading from backup piece /orabak/tinadb/db_0_tinadb_13qp4tpm_1_1
channel ORA_DISK_1: piece handle=/orabak/tinadb/db_0_tinadb_13qp4tpm_1_1 tag=TAG20151218T161342
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
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 /u01/oradata/tinadb/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/tinadb/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /orabak/tinadb/db_0_tinadb_14qp4tpm_1_1
channel ORA_DISK_1: piece handle=/orabak/tinadb/db_0_tinadb_14qp4tpm_1_1 tag=TAG20151218T161342  --先去读取最近一次的全备
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 18-DEC-15

Starting recover at 18-DEC-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/oracle/TINADB/archivelog/1_5_898789368.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/oracle/TINADB/archivelog/1_6_898789368.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/oracle/TINADB/archivelog/1_7_898789368.dbf
channel ORA_DISK_1: starting archived log restore to default destination  ---开始恢复归档日志。
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /orabak/arch19qp4ts7_41_1
channel ORA_DISK_1: piece handle=/orabak/arch19qp4ts7_41_1 tag=TAG20151218T161501
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/oracle/TINADB/archivelog/1_3_898789368.dbf thread=1 sequence=3
archived log file name=/u01/oracle/TINADB/archivelog/1_4_898789368.dbf thread=1 sequence=4
archived log file name=/u01/oracle/TINADB/archivelog/1_5_898789368.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-DEC-15

SQL> alter database open resetlogs;
Database altered.

SQL> select * from tina.salgrade;   --数据果然回来了。

     GRADE LOSAL    HISAL
---------- ---------- ----------
1   700     1200
2 1201     1400
3 1401     2000
4 2001     3000
5 3001     9999

SQL> alter system switch logfile;
System altered.

完成！