1、建立測試表
SQL> conn scott/oracle
Connected.
SQL> create table binbin as select * from user_objects;
Table created.
SQL> select count(1) from binbin;
COUNT(1)
----------
16
SQL> alter system switch logfile;
System altered.
2、備份
RMAN> backup database format='/opt/backup/dbfull_%U.bak';
Starting backup at 2013-10-01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/ORCL/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/ORCL/sysaux01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/ORCL/undotbs01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-10-01
channel ORA_DISK_1: finished piece 1 at 2013-10-01
piece handle=/opt/backup/dbfull_0holbhgn_1_1.bak tag=TAG20131001T220055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2013-10-01
channel ORA_DISK_1: finished piece 1 at 2013-10-01
piece handle=/opt/backup/dbfull_0iolbhjn_1_1.bak tag=TAG20131001T220055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 2013-10-01
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-10-01 22:49:27
select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ --------------- ------------- -------------------
1 1 2 52428800 1 NO CURRENT 772273 2013-10-01 22:46:31
2 1 1 52428800 1 YES INACTIVE 771785 2013-10-01 22:41:53
3 1 0 52428800 1 YES UNUSED 0
3、truncate測試表
SQL> truncate table binbin;
Table truncated.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ --------------- ------------- -------------------
1 1 2 52428800 1 YES ACTIVE 772273 2013-10-01 22:46:31
2 1 1 52428800 1 YES INACTIVE 771785 2013-10-01 22:41:53
3 1 3 52428800 1 NO CURRENT 772410 2013-10-01 22:50:27
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 285212672 bytes
Fixed Size 2020224 bytes
Variable Size 96472192 bytes
Database Buffers 184549376 bytes
Redo Buffers 2170880 bytes
Database mounted.
4、第一恢複：
[[email protected] backup]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on ÐÇÆÚ¶þ 10ÔÂ 1 22:52:22 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1344415282, not open)
RMAN> run {
allocate channel a1 type disk;
sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
set until time '2013-10-01 22:49:27';
restore database;
recover database;
sql 'alter database open resetlogs';
release channel a1;
}2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: a1
channel a1: sid=154 devtype=DISK
sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
Starting restore at 2013-10-01
channel a1: starting datafile backupset restore
channel a1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/ORCL/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/ORCL/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/ORCL/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/oradata/ORCL/users01.dbf
channel a1: reading from backup piece /opt/backup/dbfull_0kolbk72_1_1.bak
channel a1: restored backup piece 1
piece handle=/opt/backup/dbfull_0kolbk72_1_1.bak tag=TAG20131001T224658
channel a1: restore complete, elapsed time: 00:01:16
Finished restore at 2013-10-01
Starting recover at 2013-10-01
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 2013-10-01
sql statement: alter database open resetlogs
released channel: a1
5、檢視sequence是否被重置
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
select * from v$Log；
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ --------------- ------------- ----------
1 1 2 52428800 1 YES INACTIVE 772644 2013-10-01
2 1 3 52428800 1 YES INACTIVE 772646 2013-10-01
3 1 4 52428800 1 NO CURRENT 772650 2013-10-01
6、第二次恢複
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2020224 bytes
Variable Size 100666496 bytes
Database Buffers 180355072 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL>
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1344415282 PARENT 1 2005-10-22
2 2 ORCL 1344415282 PARENT 525876 2013-05-28
3 3 ORCL 1344415282 ORPHAN 693177 2013-09-12
4 4 ORCL 1344415282 PARENT 693177 2013-09-12
5 5 ORCL 1344415282 PARENT 693583 2013-09-12
6 6 ORCL 1344415282 PARENT 704038 2013-09-14
7 7 ORCL 1344415282 PARENT 728384 2013-09-15
8 8 ORCL 1344415282 PARENT 771785 2013-10-01
9 9 ORCL 1344415282 CURRENT 772367 2013-10-01
RMAN>
run {
allocate channel a1 type disk;
sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
set until time '2013-10-01 22:49:27';
restore database;
recover database;
sql 'alter database open resetlogs';
release channel a1;
}
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: sid=154 devtype=DISK
sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
released channel: a1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 10/01/2013 22:57:49
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
RMAN>
提示until time '2013-10-01 22:49:27'是resetlogs之前的時間。
7、修改 incarnation
RMAN> reset database to incarnation 8;
database reset to incarnation 8
RMAN>
run {
allocate channel a1 type disk;
sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
set until time '2013-10-01 22:49:27';
restore database;
recover database;
sql 'alter database open resetlogs';
release channel a1;
}
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: sid=154 devtype=DISK
sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
Starting restore at 2013-10-01
channel a1: starting datafile backupset restore
channel a1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/ORCL/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/ORCL/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/ORCL/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/oradata/ORCL/users01.dbf
channel a1: reading from backup piece /opt/backup/dbfull_0kolbk72_1_1.bak
channel a1: restored backup piece 1
piece handle=/opt/backup/dbfull_0kolbk72_1_1.bak tag=TAG20131001T224658
channel a1: restore complete, elapsed time: 00:01:15
Finished restore at 2013-10-01
Starting recover at 2013-10-01
starting media recovery
media recovery failed
released channel: a1
MAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/01/2013 22:59:47
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time '10ÔÂ 01 2013 22:49:27'
ORA-00283: recovery session canceled due to errors
ORA-38727: FLASHBACK DATABASE requires a current control file.
從這個報錯可以看出打開了flashback
8、關閉flashback
RMAN> sql 'alter database flashback off';
sql statement: alter database flashback off
9、再次恢複
RMAN>
run {
allocate channel a1 type disk;
sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
set until time '2013-10-01 22:49:27';
restore database;
recover database;
sql 'alter database open resetlogs';
release channel a1;
}
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: sid=154 devtype=DISK
sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
Starting restore at 2013-10-01
skipping datafile 1; already restored to file /opt/oracle/oradata/ORCL/system01.dbf
skipping datafile 2; already restored to file /opt/oracle/oradata/ORCL/undotbs01.dbf
skipping datafile 3; already restored to file /opt/oracle/oradata/ORCL/sysaux01.dbf
skipping datafile 4; already restored to file /opt/oracle/oradata/ORCL/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 2013-10-01
Starting recover at 2013-10-01
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /u01/backup/1_2_827707313.dbf
archive log filename=/u01/backup/1_2_827707313.dbf thread=1 sequence=2
media recovery complete, elapsed time: 00:00:03
Finished recover at 2013-10-01
sql statement: alter database open resetlogs
released channel: a1
RMAN>
RMAN> list incarnation ;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1344415282 PARENT 1 2005-10-22
2 2 ORCL 1344415282 PARENT 525876 2013-05-28
3 3 ORCL 1344415282 ORPHAN 693177 2013-09-12
4 4 ORCL 1344415282 PARENT 693177 2013-09-12
5 5 ORCL 1344415282 PARENT 693583 2013-09-12
6 6 ORCL 1344415282 PARENT 704038 2013-09-14
7 7 ORCL 1344415282 PARENT 728384 2013-09-15
8 8 ORCL 1344415282 PARENT 771785 2013-10-01
9 9 ORCL 1344415282 ORPHAN 772367 2013-10-01
10 10 ORCL 1344415282 CURRENT 772367 2013-10-01
10、驗證是否恢複成功
SQL> conn scott/oracle
Connected.
SQL> select count(1) from binbin;
COUNT(1)
----------
16
![BMR(Block Media Recovery)[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)