Oracle technology: Using Rman for tablespace recovery based on Point-in-time

Example Description:

(1) Create 2 table spaces first.

Create tablespace user01 datafile ' +dg1 ' size 1M;

Create tablespace user02 datafile ' +dg1 ' size 1M;

(2) Create a table on each table space.

CREATE TABLE Scott.customers

(cust_id int,cust_name varchar2 ()) tablespace User01;

CREATE TABLE Scott.sales

(ID int,cust_name varchar2 (a), Sales_Amount number (8,2)) tablespace User02;

(3) Insert 2 records in each table, submit. Check the current point in time and the table space User01 to revert to the current point in time.

INSERT into scott.customers values (1, ' soctt ');

INSERT into scott.customers values (2, ' SMITH ');

INSERT into scott.sales values (1, ' Scott ', 8000);

INSERT into scott.sales values (1, ' SMITH ', 10000);

COMMIT;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

Date

Thursday, March 11, 2010 21:44:52 CST

(4) TRUNCATE table 1, insert 2 records into Table 2. Create a table in table Space 1.

TRUNCATE TABLE scott.customers;

INSERT into Scott.sales values (3, ' Scott ', 6000);

INSERT into Scott.sales values (4, ' BLAKE ', 6700);

Commit

CREATE TABLE Scott.employee (ID int,name varchar2 ()) tablespace User01;

(5) The use of Rman for table Space 1 is based on point-in-time recovery.

--rman part of the recovery table space

Recover Tablespace User01

Until Time "To_timestamp (' 2010-03-11 21:44:52 ', ' yyyy-mm-dd hh24:mi:ss ')"

Auxiliary destination '/home/oracle/backup ';

(6) User01 The table space online, check that the data in table 1 is recovered, check whether the data in table 2 is 4, and check that the newly created table does not already exist.

Alter tablespace USER01 Online;

SELECT * from Scott.customers;

cust_id Cust_name

---------- ----------

1 SOCTT

2 SMITH

SELECT * from Scott.sales;

ID Cust_name Sales_Amount

---------- ---------- ------------

1 SCOTT 8000

1 SMITH 10000

3 SCOTT 6000

4 BLAKE 6700

SELECT * from dba_tables where owner = ' SCOTT ' and table_name= ' EMPLOYEE ';

No rows selected

Everything as we wish, at this time, the table space is not fully restored to completion.

Attention:

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Only self-contained tablespaces can be based on a separate, incomplete recovery. Self-contained means that objects in the table space do not depend on objects in other tablespaces, such as the base of the index in the table space in other table spaces,

LOB columns for some tables are placed in other table spaces.

As in the previous example, execute:

Create INDEX scott.idx_customers on scott.customers (cust_name) tablespace user02;

Begin

Dbms_tts.transport_set_check (' User02 ', true);

End

SELECT * from Transport_set_violations;

Will prompt: Index SCOTT. Idx_customers in tablespace USER02 points to table SCOTT. CUSTOMERS in Tablespace USER01.

Begin

Dbms_tts.transport_set_check (' User01,user02 ', true);

End

SELECT * from Transport_set_violations;

There is no hint, because the User01/user02 tablespace is self-contained as a collection.

Above this

The process looks simple, but the database has done a lot of work by itself at step 5 o'clock, all the previous manual need to do it a step also did not less, specifically as follows:

(1) Establish the parameter file

(2) Start auxiliary instance to Nomount State

(3) Recovery of auxiliary instance control files

(4) Start auxiliary instance to Mount,restore table space corresponding data files and auxiliary files (table space system and undo files)

(5) The above several data files online, restore the table space User01,system and undo

(6) Open Secondary database (resetlogs)

(7) Exp export table Space User01;

(8) Close the auxiliary library

(9) Imp table space User01;

(10) Delete the corresponding data files and other files of the auxiliary library.

Author: 51cto Oracle small-mixed son