Oracle认证综合辅导：Oracle9i用RMAN恢复删除掉的表
发布时间：2012/9/16 22:15:06 来源：城市网学院 编辑：admin
在实际工作过程中开发人员往往不小心删除掉了一个重要的表，这时候他们会找管理员找回删除掉的表，具体场景如下：
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> #mit;
Commit #plete.
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 targetsys/oracle@oracle9ivmnocatalog
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 #ment=NONE
channel ORA_DISK_1: backup set #plete, elapsed time: 00:01:17
Finished backup at 16-JUN-11
RMAN> quit
Recovery Manager #plete.
[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 targetsys/oracle@oracle9ivmnocatalog
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 #mand: 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 #plete
Finished restore at 16-JUN-11
Starting recover at 16-JUN-11
starting media recovery
media recovery #plete
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>
表恢复回来了
