Oracle 9i restore the deleted table with RMAN

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.