RMAN恢復單個表空間或被DROP/DELETE/TRUNCATE的表

parknkjun發表於2016-12-05
原文網址 : http://blog.itpub.net/10271187/viewspace-2129793/
delete
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



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10271187/viewspace-2129793/，如需轉載，請註明出處，否則將追究法律責任。

相關文章