Oracle 9i 用RMAN 恢復刪除掉的表

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>

表恢復回來了