[20120925]truncate的恢复.txt

[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;

-- 恢复成功!

时间： 2024-10-23 18:21:45

[20120925]truncate的恢复.txt的相关文章

[20140425]11GR2 truncate后恢复测试.txt

[20140425]11GR2 truncate后恢复测试.txt --做一个truncate后恢复测试在11GR2下,我的测试环境使用使用dataguard.备用库打开flashback. --可以利用在备用库flashback到truncate前,然后在传输到主库的方式看看. 1.建立测试环境: -- 我的测试环境:primary的tnsnames别名test,standby主机的tnsnames别名testdg.数据库版本:11GR2. -- 备用库打开在read only模式,并且rea

[20171121]rman使用copy image恢复.txt

[20171121]rman使用copy image恢复.txt --//上个星期做数据文件块头恢复时,提到使用rman备份数据文件时,文件头数据库信息是最后写入备份集文件的,在filesperset=1的情况 --//下写入备份集文件中的倒数第2块就是文件头的备份.参考链接: http://blog.itpub.net/267265/viewspace-2147297/=>[20171115]恢复数据文件块头4补充.txt --//而且我最后还做了测试证明如果resotre数据文件,实际上文件

[20170411]bbed删除记录的恢复.txt

[20170411]bbed删除记录的恢复.txt --//昨天上午做的测试,链接:http://blog.itpub.net/267265/viewspace-2136933/ --//我当时并没有选择恢复记录,仅仅看删除的内容.因为这样恢复是存在许多问题. --//执行 drop function scott.sleep ; 删除sys.source$相关记录仅仅是该命令的一小部分,恢复 --//sys.source$相关记录会存在许多问题,但是如果是应用数据恢复还是可以,实际上以前我的博客

[20170105]关于使用datafilecopy恢复.txt

[20170105]关于使用datafilecopy恢复.txt --如果指定恢复数据文件是从datafilecopy,必须加括号,写一个例子说明: 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------

[20141218]误操作删除dual表的恢复.txt

[20141218]误操作删除dual表的恢复.txt --没事,做一个误操作删除dual表的恢复,没想到不能按照网上介绍的方法恢复,做一个记录. 1.建立测试数据库: mkdir -p /mnt/ramdisk mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk $ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName test -sid test -s

[20150430]列删除的简单恢复.txt

[20150430]列删除的简单恢复.txt SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx          

[20140507]实例crash恢复.txt

[20140507]实例crash恢复.txt 如果发生了实例崩溃,只需要在日志文件中找到检查点位置(low cache rba),从此开始应用所有的重做日志文件, 就完成了前滚操作. 实例崩溃后,再次启动数据库,oracle会到控制文件中读取low cache rba,这就是检查点位置. 从此处开始应用重做日志,应用到on disk rba的位置.on disk rba是磁盘中重做日志文件的最后一条重做记录的rba. 加快恢复速度,确定恢复日志的起点. 不管redo记录的事务提交还是非提交,都

[20120906]alter table set unused column后的恢复.txt

[20120906]alter table set unused column后的恢复.txt 我们知道表在alter table 表 set unused column 字段名 后的恢复,数据并没有真正的删除,昨天开发问如果出现误操作是否能够恢复(概率也太小了). 大家知道在执行以上操作后,执行很快,对应字段的数据并没有真正删除,自己觉得好奇,测试看看. 1.测试环境: SQL> select * from v$version ; BANNER ------------------------

[20130530]OS block header破坏以及恢复.txt

[20130530]OS block header破坏以及恢复.txt oracle文件的第一个块(block 0)是OS block header,在数据库中查询不到信息,记录的是OS信息,以及文件大小的等信息: 如果损坏,应该对数据文件影响大吗? 自己做一个测试看看. 1.介绍OS block header:@verSQL> @verBANNER--------------------------------------------------------------------------