ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 9i 用RMAN 恢复删除掉的表

Oracle 9i 用RMAN 恢复删除掉的表

原创 Linux操作系统 作者:tian1982tian 时间:2011-06-21 12:57:17 0 删除 编辑
Oracle 10g 用RMAN 恢复删除掉的表
       在实际工作过程中开发人员往往不小心删除掉了一个重要的表,这时候他们会找管理员找回删除掉的表,具体场景如下:
1、开发人员:
SQL> conn usr1/usr1
Connected.
SQL>  create table t( id int,name varchar2(10));
Table created.
SQL> insert into t values(0,'yejun0');
1 row created.
SQL> insert into t values(1,'yejun1');
1 row created.
SQL> commit;
Commit complete.
SQL>
.........................
过了一段时间
.........................
2、系统备份
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-06-16:07:50:58
[oracle@oracle9idemo cold]$ rman target sys/oracle@oracle9ivm nocatalog
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database: DENVER (DBID=4041114247)
using target database controlfile instead of recovery catalog
RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    556M       DISK        00:02:09     16-JUN-11     
        BP Key: 3   Status: AVAILABLE   Tag: TAG20110616T070233
        Piece Name: /u01/ubackup/rman_i_04mf0p09_1_1
  SPFILE Included: Modification time: 14-JUN-11
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1073814281 16-JUN-11 /u01/oradata/denver/system01.dbf
  2       Full 1073814281 16-JUN-11 /u01/oradata/denver/undotbs01.dbf
  3       Full 1073814281 16-JUN-11 /u01/oradata/denver/cwmlite01.dbf
  4       Full 1073814281 16-JUN-11 /u01/oradata/denver/drsys01.dbf
  5       Full 1073814281 16-JUN-11 /u01/oradata/denver/example01.dbf
  6       Full 1073814281 16-JUN-11 /u01/oradata/denver/indx01.dbf
  7       Full 1073814281 16-JUN-11 /u01/oradata/denver/odm01.dbf
  8       Full 1073814281 16-JUN-11 /u01/oradata/denver/tools01.dbf
  9       Full 1073814281 16-JUN-11 /u01/oradata/denver/users01.dbf
  10      Full 1073814281 16-JUN-11 /u01/oradata/denver/xdb01.dbf
  11      Full 1073814281 16-JUN-11 /u01/oradata/denver/app1_01.dbf
  12      Full 1073814281 16-JUN-11 /u01/oradata/denver/app2_01.dbf
RMAN> delete backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
3       3       1   1   AVAILABLE   DISK        /u01/ubackup/rman_i_04mf0p09_1_1
Do you really want to delete the above objects (enter YES or NO)? yes;
"yes;" is an invalid response - please re-enter.
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/ubackup/rman_i_04mf0p09_1_1 recid=3 stamp=753951756
Deleted 1 objects
RMAN> list backup;
RMAN> backup database format '/u01/ubackup/rman_i_%U'
2> ;
Starting backup at 16-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/u01/oradata/denver/system01.dbf
input datafile fno=00002 name=/u01/oradata/denver/undotbs01.dbf
input datafile fno=00005 name=/u01/oradata/denver/example01.dbf
input datafile fno=00011 name=/u01/oradata/denver/app1_01.dbf
input datafile fno=00010 name=/u01/oradata/denver/xdb01.dbf
input datafile fno=00006 name=/u01/oradata/denver/indx01.dbf
input datafile fno=00009 name=/u01/oradata/denver/users01.dbf
input datafile fno=00003 name=/u01/oradata/denver/cwmlite01.dbf
input datafile fno=00004 name=/u01/oradata/denver/drsys01.dbf
input datafile fno=00007 name=/u01/oradata/denver/odm01.dbf
input datafile fno=00008 name=/u01/oradata/denver/tools01.dbf
input datafile fno=00012 name=/u01/oradata/denver/app2_01.dbf
channel ORA_DISK_1: starting piece 1 at 16-JUN-11
channel ORA_DISK_1: finished piece 1 at 16-JUN-11
piece handle=/u01/ubackup/rman_i_05mf0s5q_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:17
Finished backup at 16-JUN-11
RMAN> quit
Recovery Manager complete.
[oracle@oracle9idemo cold]$
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-06-16:07:59:58
SQL> drop table t;
Table dropped.
SQL>
3、开发人员去找管理员,管理员开始恢复:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL>
[oracle@oracle9idemo cold]$ rman target sys/oracle@oracle9ivm nocatalog
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database: DENVER (DBID=4041114247)
using target database controlfile instead of recovery catalog
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> set until time "to_date('2011-06-16:07:59:22','YYYY-MM-DD:HH24:MI:SS')";
6> restore database;
7> recover database;
8> alter database open resetlogs;
9> }
allocated channel: c1
channel c1: sid=13 devtype=DISK
allocated channel: c2
channel c2: sid=14 devtype=DISK
allocated channel: c3
channel c3: sid=15 devtype=DISK
executing command: SET until clause
Starting restore at 16-JUN-11
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/denver/system01.dbf
restoring datafile 00002 to /u01/oradata/denver/undotbs01.dbf
restoring datafile 00003 to /u01/oradata/denver/cwmlite01.dbf
restoring datafile 00004 to /u01/oradata/denver/drsys01.dbf
restoring datafile 00005 to /u01/oradata/denver/example01.dbf
restoring datafile 00006 to /u01/oradata/denver/indx01.dbf
restoring datafile 00007 to /u01/oradata/denver/odm01.dbf
restoring datafile 00008 to /u01/oradata/denver/tools01.dbf
restoring datafile 00009 to /u01/oradata/denver/users01.dbf
restoring datafile 00010 to /u01/oradata/denver/xdb01.dbf
restoring datafile 00011 to /u01/oradata/denver/app1_01.dbf
restoring datafile 00012 to /u01/oradata/denver/app2_01.dbf
channel c1: restored backup piece 1
piece handle=/u01/ubackup/rman_i_05mf0s5q_1_1 tag=TAG20110616T075642 params=NULL
channel c1: restore complete
Finished restore at 16-JUN-11
Starting recover at 16-JUN-11
starting media recovery
media recovery complete
Finished recover at 16-JUN-11
database opened
released channel: c1
released channel: c2
released channel: c3
RMAN>
SQL> SELECT status FROM v$instance;
STATUS
------------
OPEN
SQL>
现在看看恢复前后数据库文件Apply的时间变化:
[oracle@oracle9idemo denver]$ ll
total 1343956
-rw-r-----  1 oracle oinstall 104865792 Jun 16 08:00 app1_01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jun 16 08:00 app2_01.dbf
drwxr-xr-x  2 oracle oinstall      4096 Jun 16 07:56 archive
-rw-r-----  1 oracle oinstall   2285568 Jun 16 08:00 control01.ctl
-rw-r-----  1 oracle oinstall   2285568 Jun 16 08:00 control02.ctl
-rw-r-----  1 oracle oinstall   2285568 Jun 16 08:00 control03.ctl
-rw-r-----  1 oracle oinstall  20979712 Jun 16 08:00 cwmlite01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jun 16 08:00 drsys01.dbf
-rw-r-----  1 oracle oinstall 156639232 Jun 16 08:00 example01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jun 16 08:00 indx01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jun 16 08:00 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Jun 16 07:58 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jun 16 07:58 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jun 16 08:00 redo03.log
-rw-r-----  1 oracle oinstall 398467072 Jun 16 08:00 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jun 15 17:18 temp01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jun 16 08:00 tools01.dbf
-rw-r-----  1 oracle oinstall 209723392 Jun 16 08:00 undotbs01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jun 16 08:00 users01.dbf
-rw-r-----  1 oracle oinstall  47194112 Jun 16 08:00 xdb01.dbf
[oracle@oracle9idemo denver]$ ll
total 1343956
-rw-r-----  1 oracle oinstall 104865792 Jun 16 08:05 app1_01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jun 16 08:05 app2_01.dbf
drwxr-xr-x  2 oracle oinstall      4096 Jun 16 07:56 archive
-rw-r-----  1 oracle oinstall   2285568 Jun 16 08:23 control01.ctl
-rw-r-----  1 oracle oinstall   2285568 Jun 16 08:23 control02.ctl
-rw-r-----  1 oracle oinstall   2285568 Jun 16 08:23 control03.ctl
-rw-r-----  1 oracle oinstall  20979712 Jun 16 08:05 cwmlite01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jun 16 08:05 drsys01.dbf
-rw-r-----  1 oracle oinstall 156639232 Jun 16 08:05 example01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jun 16 08:05 indx01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jun 16 08:05 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Jun 16 08:05 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jun 16 08:05 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jun 16 08:23 redo03.log
-rw-r-----  1 oracle oinstall 398467072 Jun 16 08:05 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jun 15 17:18 temp01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jun 16 08:05 tools01.dbf
-rw-r-----  1 oracle oinstall 209723392 Jun 16 08:05 undotbs01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jun 16 08:05 users01.dbf
-rw-r-----  1 oracle oinstall  47194112 Jun 16 08:05 xdb01.dbf
[oracle@oracle9idemo denver]$
4、开发人员查表:
SQL> conn usr1/usr1
Connected.
SQL> SELECT * FROM t;
        ID NAME
---------- ----------
         0 yejun0
         1 yejun1
SQL>
表恢复回来了
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16400082/viewspace-700398/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    164447