RMAN恢復單個表空間或被DROP/DELETE/TRUNCATE的表
1、初始化資料
JZH@jzh>create table t as select * from dba_objects;
Table created.
JZH@jzh>select count(*) from t;
COUNT(*)
----------
74756
JZH@jzh>select bytes/1024/1024 from dba_segments where segment_name='T';
BYTES/1024/1024
---------------
9
2、備份資料庫
[oracle@jzh5 oradata]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 5 08:25:42 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JZH (DBID=249975939)
RMAN> run{
2> allocate channel d1 type disk;
3> allocate channel d2 type disk;
4> crosscheck archivelog all;
5> delete noprompt expired archivelog all;
6> backup as compressed backupset database format '/home/oracle/backup20161205/full_%d_%T_%s.bak';
7> sql 'alter system archive log current';
8> backup as compressed backupset filesperset 2 format '//home/oracle/backup20161205/arch_%d_%T_%s.bak' archivelog all;
9> backup current controlfile format '/home/oracle/backup20161205/ctl_%d_%T_%s.bak';
10> release channel d1;
11> release channel d2;
12> }
3、資料庫啟到nomount狀態
SYS@jzh>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@jzh>startup nomount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 482348416 bytes
Database Buffers 348127232 bytes
Redo Buffers 2396160 bytes
RMAN> restore controlfile from '/home/oracle/backup20161205/ctl_JZH_20161205_20.bak';
Starting restore at 05-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/jzh/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/jzh/control02.ctl
Finished restore at 05-DEC-16
RMAN> sql "alter database mount clone database";
sql statement: alter database mount clone database
released channel: ORA_DISK_1
4、還原資料庫
RMAN> run {
2> allocate channel d1 type disk;
3> allocate channel d2 type disk;
4> set newname for datafile 1 to '/u01/app/oracle/oradata/jzh/system01.dbf';
5> set newname for datafile 2 to '/u01/app/oracle/oradata/jzh/sysaux01.dbf';
6> set newname for datafile 3 to '/u01/app/oracle/oradata/jzh/undotbs01.dbf';
7> set newname for datafile 4 to '/u01/app/oracle/oradata/jzh/users01.dbf';
8> restore tablespace system,undotbs1,sysaux,users;
9> sql"alter database datafile 5 offline drop";
10> switch datafile all;
11> sql"alter database datafile 1,2,3,4 online";
12> recover database skip forever tablespace example,temp;
13> release channel d1;
14> release channel d2;
15> }
allocated channel: d1
channel d1: SID=19 device type=DISK
allocated channel: d2
channel d2: SID=20 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 05-DEC-16
channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00002 to /u01/app/oracle/oradata/jzh/sysaux01.dbf
channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/jzh/users01.dbf
channel d1: reading from backup piece /home/oracle/backup20161205/full_JZH_20161205_22.bak
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set
channel d2: restoring datafile 00001 to /u01/app/oracle/oradata/jzh/system01.dbf
channel d2: restoring datafile 00003 to /u01/app/oracle/oradata/jzh/undotbs01.dbf
channel d2: reading from backup piece /home/oracle/backup20161205/full_JZH_20161205_21.bak
channel d1: piece handle=/home/oracle/backup20161205/full_JZH_20161205_22.bak tag=TAG20161205T092012
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:45
channel d2: piece handle=/home/oracle/backup20161205/full_JZH_20161205_21.bak tag=TAG20161205T092012
channel d2: restored backup piece 1
channel d2: restore complete, elapsed time: 00:01:05
Finished restore at 05-DEC-16
sql statement: alter database datafile 5 offline drop
sql statement: alter database datafile 1,2,3,4 online
Starting recover at 05-DEC-16
Executing: alter database datafile 5 offline drop
starting media recovery
archived log for thread 1 with sequence 169 is already on disk as file /u01/app/arch/1_169_891180422.dbf
archived log for thread 1 with sequence 170 is already on disk as file /u01/app/arch/1_170_891180422.dbf
archived log file name=/u01/app/arch/1_169_891180422.dbf thread=1 sequence=169
archived log file name=/u01/app/arch/1_170_891180422.dbf thread=1 sequence=170
RMAN-08187: WARNING: media recovery until SCN 1622249 complete
archived log file name=/u01/app/arch/1_170_891180422.dbf thread=1 sequence=171
released channel: d1
released channel: d2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/05/2016 09:24:46
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/arch/1_170_891180422.dbf'
ORA-00283: recovery session canceled due to errors
ORA-01112: media recovery not started
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 171 and starting SCN of 1622249
5、重建控制檔案
SYS@jzh>alter database backup controlfile to trace as '/tmp/controlfile.sql';
Database altered.
SYS@jzh>shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@jzh>startup nomount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 482348416 bytes
Database Buffers 348127232 bytes
Redo Buffers 2396160 bytes
SYS@jzh>CREATE CONTROLFILE REUSE DATABASE "JZH" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/jzh/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/jzh/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/jzh/redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 '/u01/app/oracle/oradata/jzh/system01.dbf',
13 '/u01/app/oracle/oradata/jzh/sysaux01.dbf',
14 '/u01/app/oracle/oradata/jzh/undotbs01.dbf',
15 '/u01/app/oracle/oradata/jzh/users01.dbf'
16 CHARACTER SET AL32UTF8
17 ;
Control file created.
SYS@jzh>alter database open resetlogs;
Database altered.
SYS@jzh>select file#,name from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
4 /u01/app/oracle/oradata/jzh/users01.dbf
3 /u01/app/oracle/oradata/jzh/undotbs01.dbf
2 /u01/app/oracle/oradata/jzh/sysaux01.dbf
1 /u01/app/oracle/oradata/jzh/system01.dbf
5 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00005
6、驗證
[oracle@jzh5 ~]$ ls /u01/app/oracle/oradata/jzh
control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf------------example資料庫沒有還原出來
JZH@jzh>select count(*) from t;
COUNT(*)
----------
74756
JZH@jzh>select bytes/1024/1024 from dba_segments where segment_name='T';
BYTES/1024/1024
---------------
9
JZH@jzh>create table t as select * from dba_objects;
Table created.
JZH@jzh>select count(*) from t;
COUNT(*)
----------
74756
JZH@jzh>select bytes/1024/1024 from dba_segments where segment_name='T';
BYTES/1024/1024
---------------
9
2、備份資料庫
[oracle@jzh5 oradata]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 5 08:25:42 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JZH (DBID=249975939)
RMAN> run{
2> allocate channel d1 type disk;
3> allocate channel d2 type disk;
4> crosscheck archivelog all;
5> delete noprompt expired archivelog all;
6> backup as compressed backupset database format '/home/oracle/backup20161205/full_%d_%T_%s.bak';
7> sql 'alter system archive log current';
8> backup as compressed backupset filesperset 2 format '//home/oracle/backup20161205/arch_%d_%T_%s.bak' archivelog all;
9> backup current controlfile format '/home/oracle/backup20161205/ctl_%d_%T_%s.bak';
10> release channel d1;
11> release channel d2;
12> }
3、資料庫啟到nomount狀態
SYS@jzh>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@jzh>startup nomount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 482348416 bytes
Database Buffers 348127232 bytes
Redo Buffers 2396160 bytes
RMAN> restore controlfile from '/home/oracle/backup20161205/ctl_JZH_20161205_20.bak';
Starting restore at 05-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/jzh/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/jzh/control02.ctl
Finished restore at 05-DEC-16
RMAN> sql "alter database mount clone database";
sql statement: alter database mount clone database
released channel: ORA_DISK_1
4、還原資料庫
RMAN> run {
2> allocate channel d1 type disk;
3> allocate channel d2 type disk;
4> set newname for datafile 1 to '/u01/app/oracle/oradata/jzh/system01.dbf';
5> set newname for datafile 2 to '/u01/app/oracle/oradata/jzh/sysaux01.dbf';
6> set newname for datafile 3 to '/u01/app/oracle/oradata/jzh/undotbs01.dbf';
7> set newname for datafile 4 to '/u01/app/oracle/oradata/jzh/users01.dbf';
8> restore tablespace system,undotbs1,sysaux,users;
9> sql"alter database datafile 5 offline drop";
10> switch datafile all;
11> sql"alter database datafile 1,2,3,4 online";
12> recover database skip forever tablespace example,temp;
13> release channel d1;
14> release channel d2;
15> }
allocated channel: d1
channel d1: SID=19 device type=DISK
allocated channel: d2
channel d2: SID=20 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 05-DEC-16
channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00002 to /u01/app/oracle/oradata/jzh/sysaux01.dbf
channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/jzh/users01.dbf
channel d1: reading from backup piece /home/oracle/backup20161205/full_JZH_20161205_22.bak
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set
channel d2: restoring datafile 00001 to /u01/app/oracle/oradata/jzh/system01.dbf
channel d2: restoring datafile 00003 to /u01/app/oracle/oradata/jzh/undotbs01.dbf
channel d2: reading from backup piece /home/oracle/backup20161205/full_JZH_20161205_21.bak
channel d1: piece handle=/home/oracle/backup20161205/full_JZH_20161205_22.bak tag=TAG20161205T092012
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:45
channel d2: piece handle=/home/oracle/backup20161205/full_JZH_20161205_21.bak tag=TAG20161205T092012
channel d2: restored backup piece 1
channel d2: restore complete, elapsed time: 00:01:05
Finished restore at 05-DEC-16
sql statement: alter database datafile 5 offline drop
sql statement: alter database datafile 1,2,3,4 online
Starting recover at 05-DEC-16
Executing: alter database datafile 5 offline drop
starting media recovery
archived log for thread 1 with sequence 169 is already on disk as file /u01/app/arch/1_169_891180422.dbf
archived log for thread 1 with sequence 170 is already on disk as file /u01/app/arch/1_170_891180422.dbf
archived log file name=/u01/app/arch/1_169_891180422.dbf thread=1 sequence=169
archived log file name=/u01/app/arch/1_170_891180422.dbf thread=1 sequence=170
RMAN-08187: WARNING: media recovery until SCN 1622249 complete
archived log file name=/u01/app/arch/1_170_891180422.dbf thread=1 sequence=171
released channel: d1
released channel: d2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/05/2016 09:24:46
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/arch/1_170_891180422.dbf'
ORA-00283: recovery session canceled due to errors
ORA-01112: media recovery not started
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 171 and starting SCN of 1622249
5、重建控制檔案
SYS@jzh>alter database backup controlfile to trace as '/tmp/controlfile.sql';
Database altered.
SYS@jzh>shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@jzh>startup nomount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 482348416 bytes
Database Buffers 348127232 bytes
Redo Buffers 2396160 bytes
SYS@jzh>CREATE CONTROLFILE REUSE DATABASE "JZH" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/jzh/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/jzh/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/jzh/redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 '/u01/app/oracle/oradata/jzh/system01.dbf',
13 '/u01/app/oracle/oradata/jzh/sysaux01.dbf',
14 '/u01/app/oracle/oradata/jzh/undotbs01.dbf',
15 '/u01/app/oracle/oradata/jzh/users01.dbf'
16 CHARACTER SET AL32UTF8
17 ;
Control file created.
SYS@jzh>alter database open resetlogs;
Database altered.
SYS@jzh>select file#,name from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
4 /u01/app/oracle/oradata/jzh/users01.dbf
3 /u01/app/oracle/oradata/jzh/undotbs01.dbf
2 /u01/app/oracle/oradata/jzh/sysaux01.dbf
1 /u01/app/oracle/oradata/jzh/system01.dbf
5 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00005
6、驗證
[oracle@jzh5 ~]$ ls /u01/app/oracle/oradata/jzh
control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf------------example資料庫沒有還原出來
JZH@jzh>select count(*) from t;
COUNT(*)
----------
74756
JZH@jzh>select bytes/1024/1024 from dba_segments where segment_name='T';
BYTES/1024/1024
---------------
9
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10271187/viewspace-2129793/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle恢復表delete/truncate/drop的方法總結Oracledelete
- Oracle RMAN 表空間恢復Oracle
- Oracle RMAN 表空間的完全恢復Oracle
- rman恢復資料檔案 恢復表空間
- 【RMAN】表空間基於時間點的RMAN恢復-TSPITR
- 使用ODU恢復被truncate表的資料
- Oracle 11g RMAN恢復-只讀表空間的恢復Oracle
- 【北亞資料恢復】誤操作導致雲伺服器表被truncate，表內資料被delete的資料恢復資料恢復伺服器delete
- mysql 無備份恢復drop資料-共享表空間MySql
- 恢復被執行truncate table的表資料
- 使用ODU恢復oracle被truncate的表資料Oracle
- 【Oracle】rman 恢復只讀表空間資料庫Oracle資料庫
- mysql恢復drop表MySql
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- 通過flashback database恢復被刪除的表空間Database
- 恢復Oracle表空間的方法Oracle
- 使用RMAN對PDB中的表空間或資料檔案執行完全恢復
- Oracle Truncate表恢復(ODU)Oracle
- drop、delete 與truncatedelete
- 非系統表空間損壞，rman備份恢復
- Oracle 11g RMAN恢復-只讀表空間的恢復（備份是在表空間只讀狀態下做的)Oracle
- 【TSPITR】RMAN表空間基於時間點的自動恢復
- ORACLE—DELETE表後的恢復Oracledelete
- 【Oracle 恢復表空間】 實驗Oracle
- SYSAUX表空間管理及恢復UX
- Truncate,Delete,Drop的比較.delete
- Oracle Delete表恢復(ODU)Oracledelete
- truncate delete drop 區別delete
- 恢復oracle中drop掉的表Oracle
- ORACLE 11g TSPITR恢復被刪除的表空間Oracle
- 【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案
- Oracle Drop表(purge)恢復(ODU)Oracle
- TEMP表空間的檔案丟失或損壞後的恢復
- 根據表空間的TSPITR恢復
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- 備份與恢復--一個表空間能否被多個資料庫讀寫？資料庫
- 表空間TSPITR恢復-實驗
- 使用RMAN簡單遷移表空間