In actual work, developers often accidentally delete an important table. At this time, they will find the Administrator to retrieve the deleted table. The specific scenarios are as follows:
1. Developers:
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>
.........................
After a while
.........................
2. System Backup
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,200 2, 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 556 m disk 00:02:09 16-JUN-11
BP Key: 3 Status: AVAILABLE Tag: tag20151116t070233
Piece Name:/u01/ubackup/rman_ I _04mf0p09_1_1
SPFILE encoded ded: 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. The developer goes to the Administrator, and the Administrator starts to recover:
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,200 2, 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/too