[20120925]truncate的恢复.txt
1.做好备份:
RMAN> delete archivelog all completed before 'sysdate-8/24' ;
RMAN> backup database format '/data/testtest/%U';
Starting backup at 2012-09-25 10:25:08
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=00003 name=/u01/app/oracle11g/oradata/test/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle11g/oradata/test/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
input datafile file number=00004 name=/u01/app/oracle11g/oradata/test/users01.dbf
input datafile file number=00005 name=/u01/app/oracle11g/oradata/test/example01.dbf
input datafile file number=00006 name=/u01/app/oracle11g/oradata/test/rman01.dbf
input datafile file number=00007 name=/u01/app/oracle11g/oradata/test/tools01.dbf
input datafile file number=00008 name=/u01/app/oracle11g/oradata/test/test01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-09-25 10:25:11
channel ORA_DISK_1: finished piece 1 at 2012-09-25 10:27:06
piece handle=/data/testtest/04nm2uk6_1_1 tag=TAG20120925T102508 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2012-09-25 10:27:07
channel ORA_DISK_1: finished piece 1 at 2012-09-25 10:27:09
piece handle=/data/testtest/05nm2unq_1_1 tag=TAG20120925T102508 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2012-09-25 10:27:09
RMAN> backup archivelog all format '/data/testtest/%U';
Starting backup at 2012-09-25 10:29:23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=362 RECID=87 STAMP=794908818
input archived log thread=1 sequence=363 RECID=88 STAMP=794917764
channel ORA_DISK_1: starting piece 1 at 2012-09-25 10:29:26
channel ORA_DISK_1: finished piece 1 at 2012-09-25 10:29:29
piece handle=/data/testtest/06nm2us6_1_1 tag=TAG20120925T102925 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2012-09-25 10:29:29
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 1.73G DISK 00:01:46 2012-09-25 10:26:56
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20120925T102508
Piece Name: /data/testtest/04nm2uk6_1_1
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/system01.dbf
2 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/users01.dbf
5 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/example01.dbf
6 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/rman01.dbf
7 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/tools01.dbf
8 Full 3009355788 2012-09-25 10:25:11 /u01/app/oracle11g/oradata/test/test01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4 Full 11.33M DISK 00:00:02 2012-09-25 10:27:08
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20120925T102508
Piece Name: /data/testtest/05nm2unq_1_1
SPFILE Included: Modification time: 2012-09-24 09:45:30
SPFILE db_unique_name: TEST
Control File Included: Ckp SCN: 3009355834 Ckp time: 2012-09-25 10:27:06
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
5 42.09M DISK 00:00:03 2012-09-25 10:29:29
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20120925T102925
Piece Name: /data/testtest/06nm2us6_1_1
List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 362 3009331782 2012-09-24 22:03:46 3009351894 2012-09-25 08:00:16
1 363 3009351894 2012-09-25 08:00:16 3009355903 2012-09-25 10:29:23
2.建立测试数据:
connect scott
create table t as select rownum id,'test' name from dual connect by levelSQL> select current_scn,sysdate from v$database ;
CURRENT_SCN SYSDATE
----------- -------------------
3009356081 2012-09-25 10:32:01
SQL> truncate table t;
Table truncated.
3.启动恢复:
SQL> alter system archive log current;
System altered.
RMAN> backup archivelog all format '/data/testtest/%U';
Starting backup at 2012-09-25 10:33:48
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=362 RECID=87 STAMP=794908818
input archived log thread=1 sequence=363 RECID=88 STAMP=794917764
input archived log thread=1 sequence=364 RECID=89 STAMP=794918002
input archived log thread=1 sequence=365 RECID=90 STAMP=794918028
channel ORA_DISK_1: starting piece 1 at 2012-09-25 10:33:51
channel ORA_DISK_1: finished piece 1 at 2012-09-25 10:33:54
piece handle=/data/testtest/07nm2v4f_1_1 tag=TAG20120925T103350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2012-09-25 10:33:54
4.由于仅仅一台机器,关闭数据库:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cd /u01/app/oracle11g/oradata/
$ mv test test.20120925
$ mkdir test
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1560281960 bytes
Database Buffers 570425344 bytes
Redo Buffers 4964352 bytes
第一步：恢复控制文件：
RMAN> restore controlfile from '/data/testtest/05nm2unq_1_1';
Starting restore at 2012-09-25 10:40:02
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 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/oracle11g/oradata/test/control01.ctl
output file name=/u01/app/oracle11g/flash_recovery_area/test/control02.ctl
Finished restore at 2012-09-25 10:40:07
第二步：进入mount状态：
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
第三步: 恢复:
run
{
allocate channel c1 type disk ;
allocate channel c2 type disk ;
set until scn 3009356081 ;
restore database skip forever tablespace 'SYSAUX','EXAMPLE','RMAN','TOOLS','TEST' until scn 3009356081 ;
recover database skip forever tablespace 'SYSAUX','EXAMPLE','RMAN','TOOLS','TEST' until scn 3009356081 ;
release channel c1 ;
release channel c2 ;
}
allocated channel: c1
channel c1: SID=176 device type=DISK
allocated channel: c2
channel c2: SID=355 device type=DISK
executing command: SET until clause
Starting restore at 2012-09-25 11:26:53
Starting implicit crosscheck backup at 2012-09-25 11:26:53
Crosschecked 1 objects
Finished implicit crosscheck backup at 2012-09-25 11:27:12
Starting implicit crosscheck copy at 2012-09-25 11:27:12
Crosschecked 1 objects
Finished implicit crosscheck copy at 2012-09-25 11:27:13
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_09_25/o1_mf_1_365_86260dvr_.arc
File Name: /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_09_25/o1_mf_1_363_8625r43v_.arc
File Name: /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_09_25/o1_mf_1_364_8625zlnx_.arc
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/oracle11g/oradata/test/system01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle11g/oradata/test/undotbs01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle11g/oradata/test/users01.dbf
channel c1: reading from backup piece /data/testtest/04nm2uk6_1_1
channel c1: piece handle=/data/testtest/04nm2uk6_1_1 tag=TAG20120925T102508
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:01:15
Finished restore at 2012-09-25 11:28:36
Starting recover at 2012-09-25 11:28:37
Executing: alter database datafile 2 offline drop
Executing: alter database datafile 5 offline drop
Executing: alter database datafile 6 offline drop
Executing: alter database datafile 7 offline drop
Executing: alter database datafile 8 offline drop
starting media recovery
archived log for thread 1 with sequence 363 is already on disk as file /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_09_25/o1_mf_1_363_8625r43v_.arc
archived log for thread 1 with sequence 364 is already on disk as file /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_09_25/o1_mf_1_364_8625zlnx_.arc
archived log file name=/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_09_25/o1_mf_1_363_8625r43v_.arc thread=1 sequence=363
archived log file name=/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_09_25/o1_mf_1_364_8625zlnx_.arc thread=1 sequence=364
media recovery complete, elapsed time: 00:00:02
Finished recover at 2012-09-25 11:28:51
released channel: c1
released channel: c2
5.插曲:
再前面脚本中.我使用引号,如果不使用引号,会出现一些错误.例如:
RMAN> recover database skip forever tablespace SYSAUX,EXAMPLE,RMAN,TOOLS,TEST until scn 3009356081;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "rman": expecting one of: "double-quoted-string, identifier, single-quoted-string"
RMAN-01007: at line 1 column 57 file: standard input
--line 1 column 57 =>正好指向rman的r位置.想想rman可能是特殊关键字,要加上引号.
RMAN> recover database skip forever tablespace SYSAUX,EXAMPLE,'RMAN',TOOLS,TEST until scn 3009356081;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"
RMAN-01007: at line 1 column 70 file: standard input
--line 1 column 70 => 正好指向test的t位置.才想起来test对于rman也是关键字.
写成如下OK:
RMAN> recover database skip forever tablespace SYSAUX,EXAMPLE,'RMAN',TOOLS,'TEST' until scn 3009356081;
看来建立表空间避开关键字,不行全部加引号.如下:
restore database skip forever tablespace 'SYSAUX','EXAMPLE','RMAN','TOOLS','TEST' until scn 3009356081 ;
recover database skip forever tablespace 'SYSAUX','EXAMPLE','RMAN','TOOLS','TEST' until scn 3009356081 ;
6.测试结果如何:
SQL> alter database open read only ;
Database altered.
select count(*) from scott.t;
-- 恢复成功!
