Oracle误删除表空间的恢复-白红宇

Oracle误删除表空间的恢复

阅读量：6092 次

发布时间：2019-06-20

本文共 32605 字，大约阅读时间需要 108 分钟。

对于误删除表空间的恢复，本文通过基于数据库的时间点恢复和基于表空间的时间点恢复分别加以讨论

一通过基于数据库的时间点恢复被误删除的表空间

1 需要注意的事项

a 基于数据库的时间点恢复将会回退整个数据库。

b 误删除表空间，当数据库有之前可用于恢复的全库备份和相关归档，如果对数据库执行不完全恢复，恢复该数据库到删除表空间之前的状态，便可恢复误删除的表空间。但实际上当我们删除表空间，数据库备份中将不存在关于该表空间的的信息,直接进行恢复将会出现问题。如下所示：

RMAN> list backup of database;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

34 Incr 0 2.04G DISK 00:02:22 2014-02-09 19:13:39

BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20140209T191116

Piece Name: /backup/crm/full-20140209-14p0792l_1_1.bak

List of Datafiles in backup set 34

File LV Type Ckp SCN Ckp Time Name

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

1 0 Incr 3892854 2014-02-09 19:11:17 //CRM/CRM/system01.dbf

2 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/sysaux01.dbf

3 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/undotbs01.dbf

4 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/users01.dbf

5 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/jxc.dbf

6 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/pos.dbf

7 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/user01.dbf

8 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/erp.dbf

9 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/undotbs03.dbf

12 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/crm.dbf

RMAN> host;

[oracle@dest bak]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 19:16:40 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>drop tablespace pos including contents and datafiles;

Tablespace dropped.

RMAN> list backup of database;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

34 Incr 0 2.04G DISK 00:02:22 2014-02-09 19:13:39

BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20140209T191116

Piece Name: /backup/crm/full-20140209-14p0792l_1_1.bak

List of Datafiles in backup set 34

File LV Type Ckp SCN Ckp Time Name

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

1 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/system01.dbf

2 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/sysaux01.dbf

3 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/undotbs01.dbf

4 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/CRM/users01.dbf

5 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/jxc.dbf

6 0 Incr 3892854 2014-02-09 19:11:17

7 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/user01.dbf

8 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/erp.dbf

9 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/undotbs03.dbf

12 0 Incr 3892854 2014-02-09 19:11:17 /oracle/CRM/crm.dbf

所以，在恢复前应该先用上一次全备份时刻控制文件备份恢复当前控制文件，之后再对整个数据库执行基于时间点的不完全恢复

2 创建测试表空间及相应的用户

[oracle@dest bak]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:18:28 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group#,archived,sequence#,status from v$log;

GROUP# ARC SEQUENCE# STATUS

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

1 NO 1 CURRENT

2 YES 0 UNUSED

3 YES 0 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> create tablespace jxc datafile '/oracle/CRM/jxc.dbf' size 10m;

Tablespace created.

SQL> create user zx identified by dhhzdhhz default tablespace jxc;

User created.

SQL> grant connect ,resource to zx;

Grant succeeded.

SQL> exit

3 备份数据库

Disconnected from Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dest bak]$ ls

crm201402091452.log crm201402091727.log fullbk.txt rman.sh

[oracle@dest bak]$ cat rman.sh

#!/bin/bash

export today=`date +%Y%m%d%H%M`

export ORACLE_HOME=/oracle/app/db1

export ORACLE_SID=CRM

$ORACLE_HOME/bin/rman target sys/DHHZDHHZ log=/oracle/bak/crm.log cmdfile=/oracle/bak/fullbk.txt

mv /oracle/bak/crm.log "/oracle/bak/crm${today}.log"

[oracle@dest bak]$ cat fullbk.txt

run{

delete noprompt obsolete;

crosscheck backup;

delete noprompt expired backup;

crosscheck archivelog all;

delete noprompt expired archivelog all;

backup incremental level=0 database format '/backup/crm/full-%T-%U.bak';

backup archivelog all format '/backup/crm/arch-%T-%U.bak';

backup current controlfile format '/backup/crm/ctl-%T-%U.bak';

backup spfile format '/backup/crm/spf-%T-%U.bak';

delete noprompt archivelog all completed before 'SYSDATE - 7';

}

[oracle@dest bak]$ ./rman.sh

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> [oracle@dest bak]$

[oracle@dest bak]$

[oracle@dest bak]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Feb 9 18:32:41 2014

connected to target database: CRM (DBID=3641885733)

RMAN> list backup of tablespace jxc;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

23 Incr 0 2.04G DISK 00:02:29 2014-02-09 18:31:05

BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140209T182835

Piece Name: /backup/crm/full-20140209-0np076ik_1_1.bak

List of Datafiles in backup set 23

File LV Type Ckp SCN Ckp Time Name

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

5 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/jxc.dbf

RMAN> exit

4 在表空间上创建测试数据

[oracle@dest bak]$ sqlplus zx/dhhzdhhz

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:33:07 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test1(i int);

Table created.

SQL> begin

2 for i in 1..20 loop

3 insert into test1 values(i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> select * from zx.test1;

----------

20 rows selected.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dest bak]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:34:17 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

5 记录drop掉表空间前当前数据库的时间点。

SQL> select current_scn,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SYSDATE,'YY

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

3892004 2014-02-09 18:35:05

SQL>

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

6 drop掉表空间和相应的数据文件

SQL> drop tablespace jxc including contents and datafiles;

Tablespace dropped.

7 恢复控制文件

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

24 Incr 0 9.67M DISK 00:00:04 2014-02-09 18:31:19

BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20140209T182835

Piece Name: /backup/crm/full-20140209-0op076nj_1_1.bak

Control File Included: Ckp SCN: 3891837 Ckp time: 2014-02-09 18:31:15

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

34 Full 9.64M DISK 00:00:02 2014-02-09 18:31:38

BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20140209T183136

Piece Name: /backup/crm/ctl-20140209-12p076o8_1_1.bak

Control File Included: Ckp SCN: 3891864 Ckp time: 2014-02-09 18:31:36

RMAN> list backup of database;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

23 Incr 0 2.04G DISK 00:02:29 2014-02-09 18:31:05

BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140209T182835

Piece Name: /backup/crm/full-20140209-0np076ik_1_1.bak

List of Datafiles in backup set 23

File LV Type Ckp SCN Ckp Time Name

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

1 0 Incr 3891775 2014-02-09 18:28:36 //CRM/CRM/system01.dbf

2 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/sysaux01.dbf

3 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/undotbs01.dbf

4 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/users01.dbf

5 0 Incr 3891775 2014-02-09 18:28:36

6 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/pos.dbf

7 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/user01.dbf

8 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/erp.dbf

9 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/undotbs03.dbf

12 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/crm.dbf

RMAN> startup force nomount;

Oracle instance started

Total System Global Area 952020992 bytes

Fixed Size 2232208 bytes

Variable Size 633340016 bytes

Database Buffers 310378496 bytes

Redo Buffers 6070272 bytes

RMAN> restore controlfile to '/oracle/CRM/CRM/control01.ctl' from '/backup/crm/ctl-20140209-12p076o8_1_1.bak';

Starting restore at 2014-02-09 18:56:10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2014-02-09 18:56:13

RMAN> exit

Recovery Manager complete.

[oracle@dest bak]$ cd /oracle/CRM/CRM

[oracle@dest CRM]$ ls -lt

total 3325464

-rw-r----- 1 oracle oinstall 10076160 Feb 9 18:56 control01.ctl

-rw-r----- 1 oracle oinstall 10076160 Feb 9 18:53 control02.ctl

-rw-r----- 1 oracle oinstall 555753472 Feb 9 18:42 sysaux01.dbf

-rw-r----- 1 oracle oinstall 828383232 Feb 9 18:42 system01.dbf

-rw-r----- 1 oracle oinstall 115351552 Feb 9 18:42 undotbs01.dbf

-rw-r----- 1 oracle oinstall 1251745792 Feb 9 18:42 users01.dbf

-rw-r----- 1 oracle oinstall 209715712 Feb 9 18:38 redo01.log

-rw-r----- 1 oracle oinstall 209715712 Feb 9 18:35 redo03.log

-rw-r----- 1 oracle oinstall 209715712 Feb 9 18:34 redo02.log

-rw-r----- 1 oracle oinstall 30416896 Feb 9 15:00 temp01.dbf

[oracle@dest CRM]$ cp control01.ctl control02.ctl

[oracle@dest CRM]$ ls

control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

control02.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf

[oracle@dest CRM]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Feb 9 18:57:57 2014

connected to target database: CRM (not mounted)

RMAN> alter database mount;

using target database control file instead of recovery catalog

database mounted

RMAN> list backup of database;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

23 Incr 0 2.04G DISK 00:02:29 2014-02-09 18:31:05

BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140209T182835

Piece Name: /backup/crm/full-20140209-0np076ik_1_1.bak

List of Datafiles in backup set 23

File LV Type Ckp SCN Ckp Time Name

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

1 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/system01.dbf

2 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/sysaux01.dbf

3 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/undotbs01.dbf

4 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/CRM/users01.dbf

5 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/jxc.dbf

6 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/pos.dbf

7 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/user01.dbf

8 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/erp.dbf

9 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/undotbs03.dbf

12 0 Incr 3891775 2014-02-09 18:28:36 /oracle/CRM/crm.dbf

8 执行基于数据库的时间点恢复回退整个数据库至2014-02-09 18:35:05

RMAN> run{

2> set until time "to_date('2014-02-09 18:35:05','YYYY-MM-DD HH24:MI:SS')";

3> restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 2014-02-09 18:59:02

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

skipping datafile 6; already restored to file /oracle/CRM/pos.dbf

skipping datafile 7; already restored to file /oracle/CRM/user01.dbf

skipping datafile 8; already restored to file /oracle/CRM/erp.dbf

skipping datafile 9; already restored to file /oracle/CRM/undotbs03.dbf

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /oracle/CRM/CRM/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /oracle/CRM/CRM/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oracle/CRM/CRM/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oracle/CRM/CRM/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /oracle/CRM/jxc.dbf

channel ORA_DISK_1: restoring datafile 00012 to /oracle/CRM/crm.dbf

channel ORA_DISK_1: reading from backup piece /backup/crm/full-20140209-0np076ik_1_1.bak

channel ORA_DISK_1: piece handle=/backup/crm/full-20140209-0np076ik_1_1.bak tag=TAG20140209T182835

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:28

Finished restore at 2014-02-09 19:01:32

Starting recover at 2014-02-09 19:01:33

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /oracle/archivelog/arch_1_4_839094460.arch

archived log for thread 1 with sequence 5 is already on disk as file /oracle/CRM/CRM/redo02.log

archived log for thread 1 with sequence 6 is already on disk as file /oracle/CRM/CRM/redo03.log

archived log file name=/oracle/archivelog/arch_1_4_839094460.arch thread=1 sequence=4

archived log file name=/oracle/CRM/CRM/redo02.log thread=1 sequence=5

archived log file name=/oracle/CRM/CRM/redo03.log thread=1 sequence=6

media recovery complete, elapsed time: 00:00:04

Finished recover at 2014-02-09 19:01:46

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name CRM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

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

1 790 SYSTEM *** /oracle/CRM/CRM/system01.dbf

2 530 SYSAUX *** /oracle/CRM/CRM/sysaux01.dbf

3 110 UNDOTBS1 *** /oracle/CRM/CRM/undotbs01.dbf

4 1193 USERS *** /oracle/CRM/CRM/users01.dbf

5 10 JXC *** /oracle/CRM/jxc.dbf

6 100 POS *** /oracle/CRM/pos.dbf

7 5 USER01 *** /oracle/CRM/user01.dbf

8 100 ERP *** /oracle/CRM/erp.dbf

9 100 UNDOTBS3 *** /oracle/CRM/undotbs03.dbf

12 10 CRM *** /oracle/CRM/crm.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 29 TEMP 29 /oracle/CRM/CRM/temp01.dbf

RMAN> exit

9 以resetlogs方式打开数据库

[oracle@dest CRM]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 9 19:02:10 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open resetlogs;

Database altered.

10 验证数据是否恢复

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

JXC ONLINE

POS ONLINE

USER01 ONLINE

ERP ONLINE

UNDOTBS3 ONLINE

CRM ONLINE

11 rows selected.

SQL> col file_name for a50

SQL> set linesize 400

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME TABLESPACE_NAME STATUS

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

/oracle/CRM/crm.dbf CRM AVAILABLE

/oracle/CRM/undotbs03.dbf UNDOTBS3 AVAILABLE

/oracle/CRM/erp.dbf ERP AVAILABLE

/oracle/CRM/user01.dbf USER01 AVAILABLE

/oracle/CRM/pos.dbf POS AVAILABLE

/oracle/CRM/CRM/users01.dbf USERS AVAILABLE

/oracle/CRM/CRM/undotbs01.dbf UNDOTBS1 AVAILABLE

/oracle/CRM/CRM/sysaux01.dbf SYSAUX AVAILABLE

/oracle/CRM/CRM/system01.dbf SYSTEM AVAILABLE

/oracle/CRM/jxc.dbf JXC AVAILABLE

10 rows selected.

SQL> select * from zx.test1;

----------

20 rows selected.

二通过基于表空间的时间点恢复被误删除的表空间

1 创建测试用的表jxc表空间及用户

SQL> select name from v$datafile;

NAME

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

//CRM/CRM/system01.dbf

/oracle/CRM/CRM/sysaux01.dbf

/oracle/CRM/CRM/undotbs01.dbf

/oracle/CRM/CRM/users01.dbf

/oracle/CRM/user01.dbf

/oracle/CRM/erp.dbf

/oracle/CRM/undotbs03.dbf

/oracle/CRM/crm.dbf

8 rows selected.

SQL> create tablespace jxc datafile '/oracle/CRM/jxc.dbf' size 10m;

Tablespace created.

SQL> create user zx identified by dhhzdhhz default tablespace jxc;

User created.

SQL> grant resource,connect to zx;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:05:25 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> exit

2 对整个数据库做一次全备份

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dest ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 10 02:07:05 2014

connected to target database: CRM (DBID=3641885733)

RMAN> list backup of database;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

RMAN> run{

2> delete noprompt obsolete;

3> crosscheck backup;

4> delete noprompt expired backup;

5> crosscheck archivelog all;

6> delete noprompt expired archivelog all;

7> backup incremental level=0 database format '/backup/crm/full-%T-%U.bak';

8> backup archivelog all format '/backup/crm/arch-%T-%U.bak';

9> backup current controlfile format '/backup/crm/ctl-%T-%U.bak';

10> backup spfile format '/backup/crm/spf-%T-%U.bak';

11> delete noprompt archivelog all completed before 'SYSDATE - 7';

12> }

.................备份过程省略.........................................

3 在jxc表空间上创建测试数据

[oracle@dest ~]$ sqlplus zx/dhhzdhhz

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:12:17 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test1(i int);

Table created.

SQL> begin

2 for i in 1..20 loop

3 insert into test1 values(i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:13:35 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

4 查询当前数据库的时间

SQL> select current_scn,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$database;

CURRENT_SCN TO_CHAR(SYSDATE,'YY

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

3905661 2014-02-10 02:14:23

5 删除jxc表空间

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> drop tablespace jxc including contents and datafiles;

Tablespace dropped.

SQL> exit

6 执行全自动rman管理的表空间时间点恢复

[oracle@dest ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 10 02:17:40 2014

connected to target database: CRM (DBID=3641885733)

RMAN> recover tablespace jxc until time "to_date('2014-02-10 02:14:23','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/backup';

Starting recover at 2014-02-10 02:20:12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Tablespace UNDOTBS3

Creating automatic instance, with SID='DctA'

initialization parameters used for automatic instance:

db_name=CRM

db_unique_name=DctA_tspitr_CRM

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/backup

log_archive_dest_1='location=/backup'

#No auxiliary parameter file used

starting up automatic instance CRM

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2225872 bytes

Variable Size 100665648 bytes

Database Buffers 184549376 bytes

Redo Buffers 4837376 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace jxc

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2014-02-10 02:14:23','yyyy-mm-dd hh24:mi:ss')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 2014-02-10 02:20:39

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=81 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /backup/crm/ctl-20140210-1up081kd_1_1.bak

channel ORA_AUX_DISK_1: piece handle=/backup/crm/ctl-20140210-1up081kd_1_1.bak tag=TAG20140210T021020

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/backup/CRM/controlfile/o1_mf_9hhkqsc7_.ctl

Finished restore at 2014-02-10 02:20:42

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2014-02-10 02:14:23','yyyy-mm-dd hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 9 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 5 to

"/oracle/CRM/jxc.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 9, 2, 5;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /backup/CRM/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2014-02-10 02:20:49

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /backup/CRM/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /backup/CRM/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00009 to /backup/CRM/datafile/o1_mf_undotbs3_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /backup/CRM/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/CRM/jxc.dbf

channel ORA_AUX_DISK_1: reading from backup piece /backup/crm/full-20140210-1ip081fb_1_1.bak

channel ORA_AUX_DISK_1: piece handle=/backup/crm/full-20140210-1ip081fb_1_1.bak tag=TAG20140210T020739

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16

Finished restore at 2014-02-10 02:22:05

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_system_9hhkr221_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_undotbs1_9hhkr231_.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=7 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_undotbs3_9hhkr24l_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=839125325 file name=/backup/CRM/datafile/o1_mf_sysaux_9hhkr22c_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2014-02-10 02:14:23','yyyy-mm-dd hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 9 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 5 online";

# recover and open resetlogs

recover clone database tablespace "JXC", "SYSTEM", "UNDOTBS1", "UNDOTBS3", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 9 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 5 online

Starting recover at 2014-02-10 02:22:07

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /oracle/archivelog/arch_1_9_839098938.arch

archived log for thread 1 with sequence 10 is already on disk as file /oracle/archivelog/arch_1_10_839098938.arch

archived log for thread 1 with sequence 11 is already on disk as file /oracle/archivelog/arch_1_11_839098938.arch

archived log for thread 1 with sequence 12 is already on disk as file /oracle/archivelog/arch_1_12_839098938.arch

archived log for thread 1 with sequence 13 is already on disk as file /oracle/archivelog/arch_1_13_839098938.arch

archived log file name=/oracle/archivelog/arch_1_9_839098938.arch thread=1 sequence=9

archived log file name=/oracle/archivelog/arch_1_10_839098938.arch thread=1 sequence=10

archived log file name=/oracle/archivelog/arch_1_11_839098938.arch thread=1 sequence=11

archived log file name=/oracle/archivelog/arch_1_12_839098938.arch thread=1 sequence=12

archived log file name=/oracle/archivelog/arch_1_13_839098938.arch thread=1 sequence=13

media recovery complete, elapsed time: 00:00:02

Finished recover at 2014-02-10 02:22:15

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace JXC read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/backup''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/backup''";

}

executing Memory Script

sql statement: alter tablespace JXC read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_DctA":

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table "SYS"."TSPITR_EXP_DctA" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_DctA is:

EXPDP> /backup/tspitr_DctA_28726.dmp

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace JXC:

EXPDP> /oracle/CRM/jxc.dbf

EXPDP> Job "SYS"."TSPITR_EXP_DctA" successfully completed at 02:25:39

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

Performing import of metadata...

IMPDP> Master table "SYS"."TSPITR_IMP_DctA" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_DctA":

IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

IMPDP> Job "SYS"."TSPITR_IMP_DctA" successfully completed at 02:26:50

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace JXC read write';

sql 'alter tablespace JXC offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace JXC read write

sql statement: alter tablespace JXC offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file /backup/CRM/datafile/o1_mf_temp_9hhkvg7w_.tmp deleted

auxiliary instance file /backup/CRM/onlinelog/o1_mf_3_9hhkv3mp_.log deleted

auxiliary instance file /backup/CRM/onlinelog/o1_mf_2_9hhktyoc_.log deleted

auxiliary instance file /backup/CRM/onlinelog/o1_mf_1_9hhktqyl_.log deleted

auxiliary instance file /backup/CRM/datafile/o1_mf_sysaux_9hhkr22c_.dbf deleted

auxiliary instance file /backup/CRM/datafile/o1_mf_undotbs3_9hhkr24l_.dbf deleted

auxiliary instance file /backup/CRM/datafile/o1_mf_undotbs1_9hhkr231_.dbf deleted

auxiliary instance file /backup/CRM/datafile/o1_mf_system_9hhkr221_.dbf deleted

auxiliary instance file /backup/CRM/controlfile/o1_mf_9hhkqsc7_.ctl deleted

Finished recover at 2014-02-10 02:27:09

RMAN> exit

Recovery Manager complete.

7 online jxc测试表空间

[oracle@dest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 10 02:27:55 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

JXC OFFLINE

USER01 ONLINE

ERP ONLINE

UNDOTBS3 ONLINE

CRM ONLINE

10 rows selected.

SQL> alter tablespace jxc online;

Tablespace altered.

8 验证测试数据是否恢复

SQL> select * from zx.test1;

----------

20 rows selected.

转载地址：http://celwa.baihongyu.com/

你可能感兴趣的文章

WCF服务模型

查看>>

与Java Web Service相关的若干概念

查看>>

Linux - 修复Ubuntu错误“System program problem detected”

查看>>

从Ibatis过渡到Mybatis-比较Mybaits较与Ibatis有哪些方面的改进

查看>>

Android 中文API （66） —— BluetoothClass.Device

查看>>

三款主流型 Windows Client 功能对比

在实践中深入理解VMware虚拟机的上网模式：桥接模式

查看>>

运维经验分享（二）-- Linux Shell之ChatterServer服务控制脚本二次优化

查看>>

mount failed, reason given by server: Permission denied错误处理

查看>>

SCVMM 2012 安装及绿色新功能介绍

查看>>

在oracle中常用到的一些命令

查看>>

应用交付工程师Troubleshooting经验分享2

Windows API 函数列表附帮助手册

查看>>