---------------------------------------by acdante--------------------------------------前提：
数据库已开启归档：
|
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 23 Next log sequence to archive 25 Current log sequence 25 SQL> |
一、 RMAN备份脚本
Level0备份脚本
|
export ORACLE_BASE=$ORACEL_BASE export ORACLE_HOME=$ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID=orcl export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK' rman target / <<EOF run{ allocate channel c1 type disk; allocate channel c2 type disk; backup incremental level 0 database format '/u01/backup/level0_%d_%s_%p_%u.bak'; sql 'alter system archive log current'; backup archivelog all delete input format '/u01/backup/archivelog_%d_%s_%p_%u.bak'; crosscheck backup; delete noprompt obsolete; release channel c1; release channel c2; } >>EOF
|
Level1备份脚本
|
export ORACLE_BASE=$ORACLE_BASE export ORACLE_HOME=$ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID=orcl export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK' rman target / <<EOF run{ allocate channel c1 type disk; allocate channel c2 type disk; backup incremental level 1 database format '/u01/backup/level1_%d_%s_%p_%u.bak'; sql 'alter system archive log current'; backup archivelog all delete input format '/u01/backup/archivelog_%d_%s_%p_%u.bak'; crosscheck backup; delete noprompt obsolete; release channel c1; release channel c2; } >>EOF |
Level2备份脚本
|
export ORACLE_SID=orcl export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK' $ORACLE_HOME/bin/rman target / <<EOF run{ allocate channel c1 type disk; allocate channel c2 type disk; backup incremental level 2 database format '/u01/backup/level2_%d_%s_%p_%u.bak'; sql 'alter system archive log current'; backup archivelog all delete input format '/u01/backup/archivelog_%d_%s_%p_%u.bak'; crosscheck backup; delete noprompt obsolete; release channel c1; release channel c2; } >>EOF |
二、备份数据库测试
1.执行0级备份
[oracle@oracle scripts]$ ll
total 12
-rwxr--r-- 1 oracle oinstall 600 Oct 18 12:46 rman_bak_level0.sh
-rwxr--r-- 1 oracle oinstall 600 Oct 18 12:46 rman_bak_level1.sh
-rwxr--r-- 1 oracle oinstall 532 Oct 18 12:46 rman_bak_level2.sh
[oracle@oracle scripts]$
[oracle@oracle scripts]$ sh rman_bak_level0.sh
查看备份日志，成功备份
2.创建表level0
SQL> create table level0 as select * from scott.emp;
Table created.
3.执行1级增量备份
[oracle@oracle scripts]$ date
Wed Oct 18 12:58:19 CST 2017
[oracle@oracle scripts]$ sh rman_bak_level1.sh
查看备份日志，成功备份
4.创建表level1
SQL> create table level1 as select * from scott.dept;
Table created.
SQL> !date
5.执行2级增量备份
[oracle@oracle scripts]$ sh rman_bak_level2.sh
查看备份日志，成功备份
三、模拟数据丢失
1. truncate table level0
SQL> truncate table level0;
Table truncated.
由于truncate删除表不经过回收站，又没有闪回，在本地进行恢复，这里需要根据备份来进行不完全恢复。我这里TRUNCATE表LEVEL0。
2.关闭数据库，启动到MOUNT
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1348160 bytes
Variable Size 629149120 bytes
Database Buffers 201326592 bytes
Redo Buffers 5152768 bytes
Database mounted.
四、恢复到指定SCN
1.查看最近一次备份片的scn
查看最近一次备份片的scn，指定恢复到scn
RMAN>list backup of database;
恢复到最近一次数据库备份的SCN---1290386
即可恢复level0表
2.数据库启动至MOUNT状态
将数据库关闭，确认数据库启动到MOUNT状态
|
Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 836976640 bytes Fixed Size 1348160 bytes Variable Size 629149120 bytes Database Buffers 201326592 bytes Redo Buffers 5152768 bytes Database mounted.
|
3.RESTORE DATABASE
|
[oracle@oracle backup]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 18 13:22:34 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1484770744, not open)
RMAN> restore database;
Starting restore at 18-OCT-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1147 device type=DISK
skipping datafile 9; already restored to file /u01/app/oracle/oradata/orcl/ts13-01.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 00001 to /u01/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/ts3.dbf channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/ts14-01.dbf channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/level0_ORCL_1_1_01shapil.bak channel ORA_DISK_1: piece handle=/u01/backup/level0_ORCL_1_1_01shapil.bak tag=TAG20171018T124925 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:05 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/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/ts201.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/ts1.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/ts2.dbf channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/orcl/test1.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/level0_ORCL_2_1_02shapil.bak channel ORA_DISK_1: piece handle=/u01/backup/level0_ORCL_2_1_02shapil.bak tag=TAG20171018T124925 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:06 Finished restore at 18-OCT-17
RMAN> |
4.RECOVER DATABASE
RMAN> recover database until scn=1290386;
|
RMAN> recover database until scn=1290386;
Starting recover at 18-OCT-17 using channel ORA_DISK_1 datafile 9 not processed because file is read-only channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00002: /u01/app/oracle/oradata/orcl/sysaux01.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/orcl/users01.dbf destination for restore of datafile 00005: /u01/app/oracle/oradata/orcl/ts201.dbf destination for restore of datafile 00006: /u01/app/oracle/oradata/orcl/ts1.dbf destination for restore of datafile 00007: /u01/app/oracle/oradata/orcl/ts2.dbf destination for restore of datafile 00011: /u01/app/oracle/oradata/orcl/test1.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/level1_ORCL_9_1_09shaq3k.bak channel ORA_DISK_1: piece handle=/u01/backup/level1_ORCL_9_1_09shaq3k.bak tag=TAG20171018T125827 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /u01/app/oracle/oradata/orcl/system01.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/orcl/undotbs01.dbf destination for restore of datafile 00008: /u01/app/oracle/oradata/orcl/ts3.dbf destination for restore of datafile 00010: /u01/app/oracle/oradata/orcl/ts14-01.dbf destination for restore of datafile 00012: /u01/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/level1_ORCL_8_1_08shaq3k.bak channel ORA_DISK_1: piece handle=/u01/backup/level1_ORCL_8_1_08shaq3k.bak tag=TAG20171018T125827 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /u01/app/oracle/oradata/orcl/system01.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/orcl/undotbs01.dbf destination for restore of datafile 00008: /u01/app/oracle/oradata/orcl/ts3.dbf destination for restore of datafile 00010: /u01/app/oracle/oradata/orcl/ts14-01.dbf destination for restore of datafile 00012: /u01/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/level2_ORCL_14_1_0eshaqbs.bak channel ORA_DISK_1: piece handle=/u01/backup/level2_ORCL_14_1_0eshaqbs.bak tag=TAG20171018T130252 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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=27 channel ORA_DISK_1: reading from backup piece /u01/backup/archivelog_ORCL_12_1_0cshaq5t.bak channel ORA_DISK_1: piece handle=/u01/backup/archivelog_ORCL_12_1_0cshaq5t.bak tag=TAG20171018T125941 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_27_956990010.dbf thread=1 sequence=27 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=28 channel ORA_DISK_1: reading from backup piece /u01/backup/archivelog_ORCL_13_1_0dshaq5t.bak channel ORA_DISK_1: piece handle=/u01/backup/archivelog_ORCL_13_1_0dshaq5t.bak tag=TAG20171018T125941 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_28_956990010.dbf thread=1 sequence=28 media recovery complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=29 channel ORA_DISK_1: reading from backup piece /u01/backup/archivelog_ORCL_18_1_0ishaqe8.bak channel ORA_DISK_1: piece handle=/u01/backup/archivelog_ORCL_18_1_0ishaqe8.bak tag=TAG20171018T130408 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished recover at 18-OCT-17
RMAN> |
由于不完全恢复需要resetlog，所以进行resetlogs 启库
如果不使用resetlogs参数，会出现如下报错：
5.查看表level0已经恢复
五、恢复到指定时间点
确认需要恢复的时间点存在数据库备份，且备份集可用。
由于进行过两次增量备份，在level0全备后，创建了level0表，12:56分；在level1增量备份后，创建了level1表，时间为13:01分，此时，如果需要将数据库恢复到没有创建表level1之前，我们可以指定恢复到特定的时间点。
此时：两张表都存在
1.DROP TABLE
此时将level0表DROP删除，剩余level1表
将数据库恢复到12:58分后，如果恢复成功，我们将只能看到level0表，而level1表将不存在。此时可实验恢复到指定时间点
2.数据库启动至MOUNT状态
|
SQL> conn / as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 836976640 bytes Fixed Size 1348160 bytes Variable Size 629149120 bytes Database Buffers 201326592 bytes Redo Buffers 5152768 bytes Database mounted.
|
3. RESET DATABASE INCARNATION
由于此前我们使用不完全恢复了数据库，且以resetlogs方式OPEN了数据库，此时，归档日志已经不可用，需要重置数据库状态到2。
RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN>
如果不进行此操作，在RESTORE数据库时会报错如下：
4.恢复脚本
|
[oracle@oracle scripts]$ cat recover_data_by_time.sql run { allocate channel c1 type disk; allocate channel c2 type disk; sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; set until time = '2017-10-18 12:58:00'; restore database; recover database; alter database open resetlogs; } |
恢复到12:55分的话，level0和level1两张表都将不存在。
5.执行脚本恢复至指定时间点
|
RMAN> @/home/oracle/scripts/recover_data_by_time.sql
RMAN> run 2> { 3> allocate channel c1 type disk; 4> allocate channel c2 type disk; 5> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; 6> set until time = '2017-10-18 12:58:00'; 7> restore database; 8> recover database; 9> alter database open resetlogs; 10> } allocated channel: c1 channel c1: SID=1137 device type=DISK
allocated channel: c2 channel c2: SID=10 device type=DISK
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
executing command: SET until clause
Starting restore at 18-OCT-17
skipping datafile 9; already restored to file /u01/app/oracle/oradata/orcl/ts13-01.dbf channel c1: starting datafile backup set restore channel c1: specifying datafile(s) to restore from backup set channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf channel c1: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/ts3.dbf channel c1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/ts14-01.dbf channel c1: restoring datafile 00012 to /u01/app/oracle/oradata/orcl/test01.dbf channel c1: reading from backup piece /u01/backup/level0_ORCL_1_1_01shapil.bak channel c2: starting datafile backup set restore channel c2: specifying datafile(s) to restore from backup set channel c2: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf channel c2: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf channel c2: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/ts201.dbf channel c2: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/ts1.dbf channel c2: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/ts2.dbf channel c2: restoring datafile 00011 to /u01/app/oracle/oradata/orcl/test1.dbf channel c2: reading from backup piece /u01/backup/level0_ORCL_2_1_02shapil.bak channel c1: piece handle=/u01/backup/level0_ORCL_1_1_01shapil.bak tag=TAG20171018T124925 channel c1: restored backup piece 1 channel c1: restore complete, elapsed time: 00:01:26 channel c2: piece handle=/u01/backup/level0_ORCL_2_1_02shapil.bak tag=TAG20171018T124925 channel c2: restored backup piece 1 channel c2: restore complete, elapsed time: 00:01:38 Finished restore at 18-OCT-17
Starting recover at 18-OCT-17 datafile 9 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_25_956990010.dbf archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_26_956990010.dbf archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_27_956990010.dbf archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_25_956990010.dbf thread=1 sequence=25 archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_26_956990010.dbf thread=1 sequence=26 archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_27_956990010.dbf thread=1 sequence=27 media recovery complete, elapsed time: 00:00:04 Finished recover at 18-OCT-17
database opened released channel: c1 released channel: c2
RMAN> **end-of-file**
RMAN> |
6.验证数据恢复
此时查询数据库数据，可以看到level0表已经恢复，而level1数据表是不存在的，至此，数据库指定时间点恢复完成。
相关知识点
完全恢复
-- 利用重做日志或增量备份将数据块恢复到最接近当前时间的时间点。之所以叫做完整恢复是由于Oracle应用了归档日志和联机重做日志中所有的修改。如果只是数据文件损坏，且存在备份及备份以来的所有归档日志文件，那么就能把数据库完全恢复到发生介质损坏的那个时间点。完全恢复分为数据库级别，数据文件级别以及表空间级别。
不完全恢复
-- 需要将数据库恢复到历史上某个时间点，或由于丢失了联机日志件或某个归档日志文件，或者使用了以前备份的控制文件进行恢复，进行的恢复叫做不完全恢复。 换句话说，恢复过程中不会应用备份产生后生成的所有的重
做日志(可能应用部分或不应用)。
通常出现下面的情况需要进行不完全恢复：
a. 几个或全部的联机重做日志文件损坏；
b.由于个别归档日志文件的丢失无法进行完整的恢复；
c. 用户操作造成的数据丢失，比如，用户误删除了一张表, 这时可进行不完全恢复将数据库恢复到误操作之前的时间点；
d. 丢失了当前的控制文件，必须使用备份的控制文件打开数据库。不过只要所有的归档日志文件和联机日志文件都在，仍然能够恢复所有的数据。
为了执行不完整介质恢复，必须使用恢复时间点以前的备份来还原数据文件，并在恢复完成后使用RESETLOG选项打开数据库。resetlogs会重置日志序列号(变为1)强制清空或重建REDO， noresetlogs则不会 。
------ 关系：
1. 不完全恢复必须使用resetlogs ；
2. 使用resetlogs大多数情况下是做不完全恢复，但也可以做完全恢复(视丢失文件类型不同)；
3.noresetlogs 必须做完全恢复时使用，但完全恢复不一定都是noresetlogs;`
4. 使用备份的控制文件需要使用using backup controlfile,使用using backup controlfile就需要使用resetlogs开启数据库；
5.使用resetlogs方式重建控制文件需要使用using backup controlfile,同上,需要使用resetlogs开启数据库；
6. 如果是以noresetlogs方式重建控制文件，不必要使用using backup controlfile，详细可以参考通过backup controlfile to trace 导出的脚本；(因为resetlogs重建控制文件是针对Use this only if online logs are damaged，而noresetlogs方式重建控制文件是针对the current versions of all online logs are available )
7. create controlfile resetlogs/noresetlogs 用noresetlogs重建控制文件时，控制文件中datafile Checkpoint SCN来自online logs中的current log头部，选择noresetlogs重建使得控制文件最新。如记录了最新的联机日志和日志序号。这个命令后介质恢复不是必需的。
- 用resetlogs重建控制文件时(一般是在线日志损坏时)，控制文件中datafile checkpoint SCN来自各数据文件头。
8. 使用备份的控制文件(using backup controlfile)则需要使用resetlogs方式打开数据库(当然不一定是不完全恢复,备份的控制文件不能自动进行完全恢复, 可c 手工apply日志进行完全恢复; 重新创建控制文件则可以自动进行完全恢复)；
9. 使用当前控制文件或noresetlogs方式重建控制文件来恢复，可以不需要resetlogs打开数据库；
备注： 备份的控制文件之所以不能自动进行完全恢复，是因为备份的控制文件的检查点SCN是比较旧的SCN, Oracle需要知道从哪个归档日志文件开始恢复，以及从这个归档日志文件的哪个位置开始恢复。存储在备份的控制文件中的日志序列号以及对应于控制文件检查点SCN的RBA (Redo Bytes Address)指出了从哪个归档文件开始以及从文件的哪个位置（重做记录）开始恢复。
incarnation
incarnation是Oracle10G新加入的新特性，用于跨越resetlogs的恢复。
当在做Media Recover的不完全恢复时，通过resetlogs打开库，则Incarnation表示这个数据库的特定的逻辑生存期。当作为DBA可能面临这样的还原：需要使用上次执行resetlogs命令打开数据库前生成的一个备份来进行还原数据库，或者可能需要还原到执行上一个resetlogs命令之前的时间点
借用askmaclean的理解：
|
如何理解Oracle中的incarnation概念？
小明 活到了90岁 这是incarnation A ， 这个matrix 矩阵世界里的 root (or oracle)管理员 将 小明世界的时间节点回退到了小明20岁时(同时resetlogs)，由于神的能力还不足以让这个宇宙里的一切量子变化都保证和之前的那一次完全一样，所以这次回退节点里的小明 是incarnation B，最后incarnation B的小明也会活到90岁。 虽然最后2个小明都90岁了，但这2个小明并不是一样的 ，他们都叫小明，但需要区别他们 所以一个叫incarnation A 另一个叫incarnation B。 在其他平行世界里可能还有其他 incarnation的小明，这取决于 root (or oracle)管理员的需求。 |
incarnation英文的翻印的“化身”，那在oracle里如何理解它呢？我理解为“数据库实体”（一个数据库场景）
数据库自从创建起就连续的发展，但经过不完全恢复后，数据库要重新设置起点，然后继续来连续的发展，而数据库
的连续性是通过SCN来保证的，在不完全恢复后，数据库的控制文件，保证重做日志文件和数据文件的scn是不一致的，所以
数据库要求通过resetlogs打开来他们的scn的同步一致。