基于時間點的不完全恢複實驗2

實驗目的：test1使用者使用truncate table 和drop方式删除t1,t2 表，測試資料恢複。

1、源庫建立環境：

建立2個表空間：

SQL> create tablespace test1 datafile'/u01/app/oracle/oradata/aix5/test01.dbf' size 10m;

Tablespace created.

SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/aix5/test02.dbf' size 10m;

建立2個使用者并授權：

SQL> create user test1 identified by test1 default tablespace test1;

User created.

SQL> create user test2 identified by test2 default tablespace test2;

SQL> grant dba to test1;

Grant succeeded.

SQL> grant dba to test2;

2、此時做一個rman全備資料庫，步驟略過。

SQL> conn / as sysdba

Connected.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/archive

Oldest online log sequence     75

Next log sequence to archive   77

Current log sequence           77

SQL> alter system switch logfile;

System altered.

3、建立實驗表：

test1建立t1,t2表

SQL> conn test1/test1

SQL> create table t1 as select * from all_users;

Table created.

SQL> create table t2 as select * from all_users;

test2建立t1,t2表

SQL> conn test2/test2

SQL> create table t1 (id number);

SQL> create table t2 (id number);

test1再建立t3表

SQL> conn test1/test1;

SQL> create table t3 (id number);

SQL> insert into t3 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

記錄t3表插入記錄後時間，

SQL> select sysdate from dual;

SYSDATE

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

2010-11-27 11:11:00

SQL> select count(*) from t1;

  COUNT(*)

----------

        30

SQL> select count(*) from t2;

SQL> select count(*) from t3;

         1

4、清空t1表。

SQL> truncate table t1;

Table truncated.

删除t2表。

SQL> drop table t2 purge;

SQL> col object_name format a20;

SQL>  select object_name,last_ddl_time from user_objects;

OBJECT_NAME          LAST_DDL_TIME

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

T1                   2010-11-27 11:11:41

T3                   2010-11-27 11:10:18

5、準備目标庫的環境：

$ pwd

/u01/app/oracle

$ mkdir -p ./admin/aux/bdump

$ mkdir -p ./admin/aux/cdump

$ mkdir -p ./admin/aux/udump

$ mkdir -p ./admin/aux/adump

$ ls -l

total 48

drwxr-s---    6 oracle   oinstall        512 Nov 27 11:33 admin

-rw-r--r--    1 oracle   oinstall       1042 Nov 27 08:53 dbs

drwxr-s---    4 oracle   oinstall        512 Nov 27 09:43 flash_recovery_area

drwxrwxr-x    6 oracle   oinstall        512 Oct 28 12:07 oraInventory

drwxr-s---    5 oracle   oinstall        512 Nov 27 10:05 oradata

drwxrws---    3 oracle   oinstall        512 Oct 27 15:58 product

$ mkdir ./oradata/aux

$ mkdir /u01/archive/aux

$ cd $ORACLE_HOME/dbs

/u01/app/oracle/product/10.2.0/db1/dbs

$ mv inittest.ora initaux.ora

$ orapwd file=orapwaux password=oracle entries=10

$ vi initaux.ora

"initaux.ora" 30 lines, 1143 characters 

aux.__db_cache_size=209715200

aux.__java_pool_size=4194304

aux.__large_pool_size=4194304

aux.__shared_pool_size=88080384

aux.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/aux/adump'

*.background_dump_dest='/u01/app/oracle/admin/aux/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/aux/control01.ctl','/u01/app/oracle/oradata/aux/control02.ctl','/u01/app/oracle/oradata/aux/control0

3.ctl'

*.core_dump_dest='/u01/app/oracle/admin/aux/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='aix5','aux'

*.db_name='aux'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=auxXDB)'

*.job_queue_processes=10

*.log_archive_dest_2='location=/u01/archive/aux'

*.log_file_name_convert='aix5','aux'

*.open_cursors=300

*.pga_aggregate_target=157286400

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=314572800

*.sga_target=314572800

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/aux/udump'

6、目标庫啟動到nomount狀态。

$ export ORACLE_SID=aux

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Nov 27 11:34:30 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2083760 bytes

Variable Size              96470096 bytes

Database Buffers          209715200 bytes

Redo Buffers                6303744 bytes

7、開始duplicate ,可以使用skip tablespace 指令跳過不需要的表空間。

$ export  ORACLE_SID=aux

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Nov 27 11:15:08 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: AIX5 (DBID=2940324364)

connected to auxiliary database: AUX (not mounted)

RMAN> run{

2> allocate auxiliary channel d1 device type disk;

3> allocate channel c1 device type disk;

4> set until time "to_date('2010-11-27 11:11:00','yyyy-mm-dd hh24:mi:ss')";

5> duplicate target database to aux skip tablespace test2 nofilenamecheck;

6> }

using target database control file instead of recovery catalog

allocated channel: d1

channel d1: sid=155 devtype=DISK

allocated channel: c1

channel c1: sid=137 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-NOV-10

Datafile 7 skipped by request

contents of Memory Script:

{

   set until scn  2360497;

   set newname for datafile  1 to 

 "/u01/app/oracle/oradata/aux/system01.dbf";

   set newname for datafile  2 to 

 "/u01/app/oracle/oradata/aux/undotbs01.dbf";

   set newname for datafile  3 to 

 "/u01/app/oracle/oradata/aux/sysaux01.dbf";

   set newname for datafile  4 to 

 "/u01/app/oracle/oradata/aux/users01.dbf";

   set newname for datafile  5 to 

 "/u01/app/oracle/oradata/aux/example01.dbf";

   set newname for datafile  6 to 

 "/u01/app/oracle/oradata/aux/test01.dbf";

   restore

   check readonly

   clone database

   skip tablespace  TEST2   ;

}

executing Memory Script

executing command: SET NEWNAME

Starting restore at 27-NOV-10

channel d1: starting datafile backupset restore

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

restoring datafile 00002 to /u01/app/oracle/oradata/aux/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/aux/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/aux/users01.dbf

channel d1: reading from backup piece /u01/orabk/db_AIX5_0blu20ga_1_1.dbf

channel d1: restored backup piece 1

piece handle=/u01/orabk/db_AIX5_0blu20ga_1_1.dbf tag=TAG20101127T104009

channel d1: restore complete, elapsed time: 00:00:46

restoring datafile 00001 to /u01/app/oracle/oradata/aux/system01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/aux/example01.dbf

restoring datafile 00006 to /u01/app/oracle/oradata/aux/test01.dbf

channel d1: reading from backup piece /u01/orabk/db_AIX5_0clu20ga_1_1.dbf

piece handle=/u01/orabk/db_AIX5_0clu20ga_1_1.dbf tag=TAG20101127T104009

channel d1: restore complete, elapsed time: 00:01:06

Finished restore at 27-NOV-10

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG 

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '/u01/app/oracle/oradata/aux/redo01.log' ) SIZE 50 M  REUSE,

  GROUP  2 ( '/u01/app/oracle/oradata/aux/redo02.log' ) SIZE 50 M  REUSE,

  GROUP  3 ( '/u01/app/oracle/oradata/aux/redo03.log' ) SIZE 50 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/aux/system01.dbf'

 CHARACTER SET ZHS16GBK

   switch clone datafile all;

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=736169850 filename=/u01/app/oracle/oradata/aux/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=736169850 filename=/u01/app/oracle/oradata/aux/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=736169850 filename=/u01/app/oracle/oradata/aux/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=736169850 filename=/u01/app/oracle/oradata/aux/example01.dbf

datafile 6 switched to datafile copy

input datafile copy recid=5 stamp=736169850 filename=/u01/app/oracle/oradata/aux/test01.dbf

   set until time  "to_date('2010-11-27 11:11:00','yyyy-mm-dd hh24:mi:ss')";

   recover

    delete archivelog

   ;

Starting recover at 27-NOV-10

datafile 7 not processed because file is offline

starting media recovery

archive log thread 1 sequence 77 is already on disk as file /u01/archive/1_77_733512527.dbf

archive log filename=/u01/archive/1_77_733512527.dbf thread=1 sequence=77

unable to find archive log

archive log thread=1 sequence=78

released channel: d1

released channel: c1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-03002: failure of Duplicate Db command at 11/27/2010 11:37:35

RMAN-03015: error occurred in stored script Memory Script

RMAN-06054: media recovery requesting unknown log: thread 1 seq 78 lowscn 2361416

8.複制完成後檢查：

複制到最後有錯誤資訊，提示需要78号日志，檢查發現78号日志是源庫的目前SEQUENCE，還沒有進行歸檔。

檢查目标資料庫，已經到mount狀态。

SQL> select open_mode from v$database;

OPEN_MODE

MOUNTED

檢視oradata下檔案。

total 2808256

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:42 control01.ctl

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:42 control02.ctl

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:42 control03.ctl

-rw-r-----    1 oracle   oinstall  157294592 Nov 27 11:37 example01.dbf

-rw-r-----    1 oracle   oinstall  377495552 Nov 27 11:37 sysaux01.dbf

-rw-r-----    1 oracle   oinstall  534781952 Nov 27 11:37 system01.dbf

-rw-r-----    1 oracle   oinstall   10493952 Nov 27 11:37 test01.dbf

-rw-r-----    1 oracle   oinstall  335552512 Nov 27 11:37 undotbs01.dbf

-rw-r-----    1 oracle   oinstall    5251072 Nov 27 11:37 users01.dbf

9、嘗試打開資料庫：

SQL> alter database open resetlogs;

Database altered.

再次檢查資料檔案，redo檔案已經自動生成，複制資料庫完成。

total 3115480

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:43 control01.ctl

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:43 control02.ctl

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 11:43 control03.ctl

-rw-r-----    1 oracle   oinstall  157294592 Nov 27 11:43 example01.dbf

-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo01.log

-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo02.log

-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo03.log

-rw-r-----    1 oracle   oinstall  377495552 Nov 27 11:43 sysaux01.dbf

-rw-r-----    1 oracle   oinstall  534781952 Nov 27 11:43 system01.dbf

-rw-r-----    1 oracle   oinstall   10493952 Nov 27 11:43 test01.dbf

-rw-r-----    1 oracle   oinstall  335552512 Nov 27 11:43 undotbs01.dbf

-rw-r-----    1 oracle   oinstall    5251072 Nov 27 11:43 users01.dbf

$

檢查其它資料檔案，發現複制表空間時跳過的test2表空間狀态還是recover的，這裡直接删除它就可以了。

SQL> select name,bytes,status from v$datafile;

NAME                                                    BYTES STATUS

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

/u01/app/oracle/oradata/aux/system01.dbf            534773760 SYSTEM

/u01/app/oracle/oradata/aux/undotbs01.dbf           335544320 ONLINE

/u01/app/oracle/oradata/aux/sysaux01.dbf            377487360 ONLINE

/u01/app/oracle/oradata/aux/users01.dbf               5242880 ONLINE

/u01/app/oracle/oradata/aux/example01.dbf           157286400 ONLINE

/u01/app/oracle/oradata/aux/test01.dbf               10485760 ONLINE

/u01/app/oracle/product/10.2.0/db1/dbs/MISSING0000          0 RECOVER

7

7 rows selected.

SQL> select name from v$tablespace;

NAME

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

SYSTEM

UNDOTBS1

SYSAUX

USERS

EXAMPLE

TEST1

TEMP

TEST2

8 rows selected.

10、資料完整性檢查：

使用test1登入，檢查資料：

SQL> select count(*) from test1.t1;

select count(*) from test1.t1

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from tab;

no rows selected

發現使用者下的t1,t2表都不存在了，不完全恢複沒有成功，估計是要78号日志時發生了錯誤造成的。

重新進行duplicate 複制，複制前源庫先switch 切換日志：

繼續實驗：

1、源庫操作switch：

Oldest online log sequence     76

Next log sequence to archive   78

Current log sequence           78

SQL> !ls -l /u01/archive

total 397584

-rw-r-----    1 oracle   oinstall   47127552 Nov 25 01:17 1_73_733512527.dbf

-rw-r-----    1 oracle   oinstall   47127552 Nov 25 21:26 1_74_733512527.dbf

-rw-r-----    1 oracle   oinstall   47127552 Nov 26 21:00 1_75_733512527.dbf

-rw-r-----    1 oracle   oinstall   47510016 Nov 27 05:00 1_76_733512527.dbf

-rw-r-----    1 oracle   oinstall   14043648 Nov 27 11:09 1_77_733512527.dbf

-rw-r-----    1 oracle   oinstall     607744 Nov 27 11:55 1_78_733512527.dbf

drwxr-sr-x    2 oracle   oinstall        512 Nov 27 11:34 aux

drwxr-sr-x    2 oracle   oinstall        512 Nov 27 10:08 test

2、目标庫啟動到Nomount：

SQL> startup nomount force;

3、開始duplicate：

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Nov 27 11:56:59 2010

5> duplicate target database to aux skip tablespace test2 nofilenamecheck;

channel c1: sid=142 devtype=DISK

   set until scn  2361416;

channel d1: restore complete, elapsed time: 00:00:56

input datafile copy recid=1 stamp=736171151 filename=/u01/app/oracle/oradata/aux/undotbs01.dbf

input datafile copy recid=2 stamp=736171151 filename=/u01/app/oracle/oradata/aux/sysaux01.dbf

input datafile copy recid=3 stamp=736171151 filename=/u01/app/oracle/oradata/aux/users01.dbf

input datafile copy recid=4 stamp=736171151 filename=/u01/app/oracle/oradata/aux/example01.dbf

input datafile copy recid=5 stamp=736171151 filename=/u01/app/oracle/oradata/aux/test01.dbf

archive log thread 1 sequence 78 is already on disk as file /u01/archive/1_78_733512527.dbf

archive log filename=/u01/archive/1_78_733512527.dbf thread=1 sequence=78

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

Finished recover at 27-NOV-10

   shutdown clone;

   startup clone nomount ;

user interrupt received

RMAN-03002: failure of Duplicate Db command at 11/27/2010 12:15:59

RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

4、當複制到上面紅色字executing Memory Script 時長時間沒有反應。

檢視目标庫的alert檔案，應該是在建立test2表空間時給卡死了，日志資訊如下：

Thread 1 advanced to log sequence 77 (LGWR switch)

  Current log# 1 seq# 77 mem# 0: /u01/app/oracle/oradata/aix5/redo01.log

Sat Nov 27 10:10:56 2010

create tablespace test1 datafile'/u01/app/oracle/oradata/aix5/test01.dbf' size 10m

Sat Nov 27 10:10:57 2010

Completed: create tablespace test1 datafile'/u01/app/oracle/oradata/aix5/test01.dbf' size 10m

Sat Nov 27 10:11:18 2010

create tablespace test2 datafile '/u01/app/oracle/oradata/aix5/test02.dbf' size 10m

Completed: create tablespace test2 datafile '/u01/app/oracle/oradata/aix5/test02.dbf' size 10m

~

再檢視生成的資料檔案，除redo02,redo03外都大于原先生成的11:43 分了，

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 12:15 control01.ctl

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 12:15 control02.ctl

-rw-r-----    1 oracle   oinstall    5652480 Nov 27 12:15 control03.ctl

-rw-r-----    1 oracle   oinstall  157294592 Nov 27 11:59 example01.dbf

-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:55 redo01.log

-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo02.log

-rw-r-----    1 oracle   oinstall   52429312 Nov 27 11:43 redo03.log

-rw-r-----    1 oracle   oinstall  377495552 Nov 27 11:59 sysaux01.dbf

-rw-r-----    1 oracle   oinstall  534781952 Nov 27 11:59 system01.dbf

-rw-r-----    1 oracle   oinstall   10493952 Nov 27 11:59 test01.dbf

-rw-r-----    1 oracle   oinstall  335552512 Nov 27 11:59 undotbs01.dbf

-rw-r-----    1 oracle   oinstall    5251072 Nov 27 11:59 users01.dbf

是以按 ctrl+c 結束複制任務。

5、目标庫打開資料庫：

6、檢查資料庫，可以看到2010-11-27 11:11:00 前的資料已經全部恢複：

SQL>  select count(*) from test1.t1;

SQL>  select count(*) from test1.t2;

SQL> select count(*) from test1.t3;

SQL> col name format a60;

NAME                                                              BYTES STATUS

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

/u01/app/oracle/oradata/aux/system01.dbf                      534773760 SYSTEM

/u01/app/oracle/oradata/aux/undotbs01.dbf                     335544320 ONLINE

/u01/app/oracle/oradata/aux/sysaux01.dbf                      377487360 ONLINE

/u01/app/oracle/oradata/aux/users01.dbf                         5242880 ONLINE

/u01/app/oracle/oradata/aux/example01.dbf                     157286400 ONLINE

/u01/app/oracle/oradata/aux/test01.dbf                         10485760 ONLINE

/u01/app/oracle/product/10.2.0/db1/dbs/MISSING00007                   0 RECOVER

SQL> drop tablespace test2 including contents and datafiles;

Tablespace dropped.

本文轉自 gjm008 51CTO部落格，原文連結：http://blog.51cto.com/gaoshan/433943，如需轉載請自行聯系原作者