ORACLE恢復案例

一、資料庫恢復案例

1、丟失或損壞一個資料檔案

1.) 連線資料庫，建立測試表並插入記錄

[oracle@web ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 29 08:58:51 2011

test@ORCL> conn test/admin

Connected.

test@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 備份資料庫表空間users

[oracle@web ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 09:12:48 2011

connected to target database: ORCL (DBID=1284637334)

RMAN> run{

allocate channel c1 type disk;

backup tag 'tsusers' format '/software/rman_bak/tsusers_%u_%s_%p' tablespace users;

release channel c1;

}

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=52 device type=DISK

Starting backup at 29-11月-11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/home/oracle/oradata/users02.dbf

input datafile file number=00004 name=/home/oracle/oradata/users01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/tsusers_2fmsrvss_79_1 tag=TSUSERS comment=NONE

channel c1: backup set complete, elapsed time: 00:01:25

Finished backup at 29-11月-11

Starting Control File and SPFILE Autobackup at 29-11月-11

piece handle=/home/oracle/dbbackup/ctl_20111129_c-1284637334-20111129-01 comment=NONE

Finished Control File and SPFILE Autobackup at 29-11月-11

released channel: c1

RMAN>

3.) 繼續在測試表中插入記錄

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

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

1

2

TEST@ORCL> alter system switch logfile;

System altered.

TEST@ORCL> r

1* alter system switch logfile;

System altered.

4.) 關閉資料庫，模擬丟失資料檔案

SYS@ORCL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

[oracle@web oradata]$ rm users02.dbf

5.) 啟動資料庫，檢查錯誤

idle> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-01157: 無法標識/鎖定資料檔案 5 - 請參閱 DBWR 跟蹤檔案

ORA-01110: 資料檔案 5: '/home/oracle/oradata/users02.dbf'

6.) 先開啟資料庫

TEST@ORCL> alter database datafile 5 offline drop;

Database altered.

TEST@ORCL> alter database open;

Database altered.

7.) 恢復該資料檔案表空間

恢復指令碼可以是恢復單個資料檔案

run{

allocate channel c1 type disk;

restore datafile 5;

recover datafile 5;

sql 'alter database datafile 5 online';

release channel c1;

}

也可以是,恢復表空間

run{

allocate channel c1 type disk;

restore tablespace users;

recover tablespace users;

sql 'alter database datafile 5 online';

release channel c1;

}

過程如下：

[oracle@web oradata]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 09:32:23 2011

connected to target database: ORCL (DBID=1284637334)

RMAN> run{

allocate channel c1 type disk;

restore datafile 5;

recover datafile 5;

sql 'alter database datafile 5 online';

release channel c1;

}

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=42 device type=DISK

Starting restore at 29-11月-11

channel c1: starting datafile backup set restore

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

channel c1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel c1: reading from backup piece /software/rman_bak/tsusers_2fmsrvss_79_1

channel c1: piece handle=/software/rman_bak/tsusers_2fmsrvss_79_1 tag=TSUSERS

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:01:26

Finished restore at 29-11月-11

Starting recover at 29-11月-11

starting media recovery

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

Finished recover at 29-11月-11

sql statement: alter database datafile 5 online

released channel: c1

8.) 檢查資料是否完整

TEST@ORCL> select * from test;

A

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

1

2

說明：

1、RMAN也可以實現單個表空間或資料檔案的恢復，恢復過程可以在mount下或open方式下，如果在open方式下恢復，可以減少down機時間。

2、如果損壞的是一個資料檔案，建議offline並在open方式下恢復。

3、這裡可以看到，RMAN進行資料檔案與表空間恢復的時候，程式碼都比較簡單，而且能保證備份與恢復的可靠性，所以建議採用RMAN的備份與恢復。

2、多資料檔案丟失或損壞

1.) 連線資料庫，建立測試表並插入記錄

TEST@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 備份資料庫

[oracle@web oradata]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 09:48:36 2011

connected to target database: ORCL (DBID=1284637334)

RMAN> run {

allocate channel c1 type disk;

backup database format ‘/software/rman_bak/full_%U’;

release channel c1;

}

released channel: ORA_DISK_1

allocated channel: c1

channel c1: SID=48 device type=DISK

Starting backup at 29-11月-11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/home/oracle/oradata/users02.dbf

input datafile file number=00001 name=/opt/oracle/oradata/orcl/system01.dbf

input datafile file number=00003 name=/opt/oracle/oradata/orcl/undotbs01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2kmss2d7_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:01:55

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00004 name=/home/oracle/oradata/users01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2lmss2gq_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:35

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00002 name=/home/oracle/oradata/sysaux01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2mmss2hu_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:25

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00006 name=/home/oracle/oradata/undotbs02.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2nmss2in_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:07

Finished backup at 29-11月-11

Starting Control File and SPFILE Autobackup at 29-11月-11

piece handle=/home/oracle/dbbackup/ctl_20111129_c-1284637334-20111129-03 comment=NONE

Finished Control File and SPFILE Autobackup at 29-11月-11

released channel: c1

3.) 繼續在測試表中插入記錄

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

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

1

2

TEST@ORCL> alter system switch logfile;

System altered.

TEST@ORCL> r

1* alter system switch logfile

System altered.

4.) 關閉資料庫，模擬丟失資料檔案

SQL@ORCL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

[oracle@web oradata]$ ll

total 3396900

-rw-r----- 1 oracle oinstall 629153792 Nov 29 10:08 sysaux01.dbf

-rw-r----- 1 oracle oinstall 629153792 Nov 29 10:08 undotbs02.dbf

-rw-r----- 1 oracle oinstall 643833856 Nov 29 10:08 users01.dbf

-rw-r----- 1 oracle oinstall 1572872192 Nov 29 10:08 users02.dbf

[oracle@web oradata]$ rm -rf *.dbf

5.) 啟動資料庫，檢查錯誤

sys@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-01157: 無法標識/鎖定資料檔案 2 - 請參閱 DBWR 跟蹤檔案

ORA-01110: 資料檔案 2: '/home/oracle/oradata/sysaux01.dbf'

查詢v$recover_file

TEST@ORCL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

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

2 ONLINE ONLINE FILE NOT FOUND 0

4 ONLINE ONLINE FILE NOT FOUND 0

5 ONLINE ONLINE FILE NOT FOUND 0

6 ONLINE ONLINE FILE NOT FOUND 0

可以知道有四個資料檔案需要恢復

6.) 利用RMAN進行恢復

RMAN> run{

allocate channel c1 type disk;

restore database;

recover database;

sql 'alter database open';

release channel c1;

}

Starting restore at 29-11月-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

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 /opt/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2kmss2d7_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2kmss2d7_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

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

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 00004 to /home/oracle/oradata/users01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2lmss2gq_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2lmss2gq_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

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

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 00002 to /home/oracle/oradata/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2mmss2hu_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2mmss2hu_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

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

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 00006 to /home/oracle/oradata/undotbs02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2nmss2in_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2nmss2in_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 29-11月-11

Starting recover at 29-11月-11

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 29-11月-11

sql statement: alter database open

RMAN>

7.) 檢查資料庫的資料（完全恢復）

TEST@ORCL> select * from test;

A

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

1

2

說明：

1、只要有備份與歸檔存在，RMAN也可以實現資料庫的完全恢復（不丟失資料）。

2、同OS備份恢復，適合於丟失大量資料檔案，或包含系統資料檔案在內的資料庫的恢復。

3、目標資料庫在mount下進行，如果恢復成功，再開啟資料庫。

4、RMAN的備份與恢復命令相對比較簡單並可靠，建議有條件的話，都採用RMAN進行資料庫的備份。

3、基於時間的不完全恢復案例

OS熱備份下的基於時間的恢復

不完全恢復可以分為基於時間的恢復，基於改變的恢復與基於撤消的恢復，這裡已基於時間的恢復為例子來說明不完全恢復過程。

基於時間的恢復可以不完全恢復到現在時間之前的某一個時間，對於某些誤操作，如刪除了一個資料表，可以在備用恢復環境上恢復到表的刪除時間之前，然後把該表匯出到正式環境，避免一個人為的錯誤。

1.) 連線資料庫，建立測試表並插入記錄

TEST@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 備份資料庫(最好備份所有的資料檔案，包括臨時資料檔案)

RMAN> run {

Allocate channel c1 type disk;

Backup database format ‘/software/rman_bak/full_%U’;

Release channel c1;

}

或冷備份也可以

3.) 刪除測試表

假定刪除前的時間為T1，在刪除之前，便於測試，繼續插入資料並應用到歸檔。

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

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

1

2

TEST@ORCL> alter system switch logfile;

Statement processed.

TEST@ORCL> alter system switch logfile;

Statement processed.

TEST@ORCL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2011-11-29 14:07:04

TEST@ORCL> drop table test;

Table dropped.

4.) 準備恢復到時間點

時間點為T1，找回刪除的表，先關閉資料庫

RMAN> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

RMAN> exit

Recovery Manager complete.

5.) restore所有的資料檔案

不完全恢復需要還原所有的資料庫檔案，所以還原資料庫是比較快捷的做法，而且要在mount狀態下進行。

[oracle@web oradata]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 14:12:51 2011

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 243273120 bytes

Database Buffers 12582912 bytes

Redo Buffers 4980736 bytes

RMAN> restore database;

Starting restore at 29-11月-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

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 /opt/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_30mssgj3_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_30mssgj3_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

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

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 00004 to /home/oracle/oradata/users01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_31mssgnl_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_31mssgnl_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

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

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 00002 to /home/oracle/oradata/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_32mssgof_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_32mssgof_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

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

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 00006 to /home/oracle/oradata/undotbs02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_33mssgp8_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_33mssgp8_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 29-11月-11

如果是冷備份，則複製剛才備份的所有資料檔案回來。

6.) 開始不完全恢復

資料庫恢復到T1時間。

RMAN> run {

sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';

set until time '2011-11-29 14:07:04';

recover database;

};

sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"

executing command: SET until clause

Starting recover at 29-11月-11

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 29-11月-11

7.) 開啟資料庫檢查資料

RMAN> alter database open resetlogs;

Database altered.

TEST@ORCL> select * from test;

A

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

1

2

說明注意：

1、不完全恢復最好備份所有的資料，冷備份亦可，因為恢復過程是從備份點往後恢復的，如果因為其中一個資料檔案的時間戳(SCN)大於要恢復的時間點，那麼恢復都是不可能成功的。

2、不完全恢復有三種方式，過程都一樣，僅僅是recover命令有所不一樣，這裡用基於時間的恢復作為示例。

3、不完全恢復之後，都必須用resetlogs的方式開啟資料庫，建議馬上再做一次全備份，因為resetlogs之後再用以前的備份恢復是很難了。

4、以上是在刪除之前獲得時間，但是實際應用中，很難知道刪除之前的實際時間，但可以採用大致時間即可，或可以採用分析日誌檔案(logmnr)，取得精確的需要恢復的時間。

5、一般都是在測試機後備用機器上採用這種不完全恢復，恢復之後匯出/匯入被誤刪的表回生產系統。

4、基於SCN的不完全恢復案例

以上用OS備份說明了一個基於時間的恢復，現在用RMAN說明一個基於改變的恢復。

1.) 連線資料庫，建立測試表並插入記錄

TEST@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 備份資料庫

RMAN> run {

allocate channel c1 type disk;

backup database format '/software/rman_bak/full_%U';

release channel c1;

};

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=44 device type=DISK

Starting backup at 29-11月-11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/home/oracle/oradata/users02.dbf

input datafile file number=00001 name=/opt/oracle/oradata/orcl/system01.dbf

input datafile file number=00003 name=/opt/oracle/oradata/orcl/undotbs01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_30mssgj3_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:02:26

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00004 name=/home/oracle/oradata/users01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_31mssgnl_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:25

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00002 name=/home/oracle/oradata/sysaux01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_32mssgof_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:25

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00006 name=/home/oracle/oradata/undotbs02.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_33mssgp8_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:03

Finished backup at 29-11月-11

Starting Control File and SPFILE Autobackup at 29-11月-11

piece handle=/home/oracle/dbbackup/ctl_20111129_c-1284637334-20111129-07 comment=NONE

Finished Control File and SPFILE Autobackup at 29-11月-11

released channel: c1

RMAN>

3.) 刪除測試表

在刪除之前，便於測試，繼續插入資料並應用到歸檔，並獲取刪除前的scn號。

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

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

1

2

TEST@ORCL> alter system switch logfile;

Statement processed.

TEST@ORCL> alter system switch logfile;

Statement processed.

Oracle 9i之後用：

test@ORCL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

21069993

Oracle 9i之前用：

TEST@ORCL> select max(ktuxescnw * power(2, 32) + ktuxescnb) scn from x$ktuxe;

SCN

----------

21069993

TEST@ORCL> drop table test;

Table dropped.

4.) 準備恢復資料庫

恢復到SCN 21069993，先關閉資料庫，然後啟動到mount下。

RMAN> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 247467424 bytes

Database Buffers 8388608 bytes

Redo Buffers 4980736 bytes

5.) 開始恢復到改變點

RMAN> run{

allocate channel c1 type disk;

restore database;

recover database until scn 21069993;

sql 'ALTER DATABASE OPEN RESETLOGS';

release channel c1;

}

allocated channel: c1

channel c1: SID=18 device type=DISK

Starting restore at 29-11月-11

channel c1: starting datafile backup set restore

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

channel c1: restoring datafile 00001 to /opt/oracle/oradata/orcl/system01.dbf

channel c1: restoring datafile 00003 to /opt/oracle/oradata/orcl/undotbs01.dbf

channel c1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel c1: reading from backup piece /software/rman_bak/full_30mssgj3_1_1

channel c1: piece handle=/software/rman_bak/full_30mssgj3_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:01:56

channel c1: starting datafile backup set restore

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

channel c1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf

channel c1: reading from backup piece /software/rman_bak/full_31mssgnl_1_1

channel c1: piece handle=/software/rman_bak/full_31mssgnl_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:35

channel c1: starting datafile backup set restore

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

channel c1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf

channel c1: reading from backup piece /software/rman_bak/full_32mssgof_1_1

channel c1: piece handle=/software/rman_bak/full_32mssgof_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:35

channel c1: starting datafile backup set restore

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

channel c1: restoring datafile 00006 to /home/oracle/oradata/undotbs02.dbf

channel c1: reading from backup piece /software/rman_bak/full_33mssgp8_1_1

channel c1: piece handle=/software/rman_bak/full_33mssgp8_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:15

Finished restore at 29-11月-11

Starting recover at 29-11月-11

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_1_7f8xm8fo_.arc

archived log for thread 1 with sequence 2 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_7f8xmgm3_.arc

archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_3_7f8yd42f_.arc

archived log file name=/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_1_7f8xm8fo_.arc thread=1 sequence=1

archived log file name=/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_7f8xmgm3_.arc thread=1 sequence=2

archived log file name=/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_3_7f8yd42f_.arc thread=1 sequence=3

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

Finished recover at 29-11月-11

sql statement: alter database open resetlogs

released channel: c1

6.) 檢查資料

TEST@ORCL> select * from test;

A

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

1

2

可以看到，表依然存在。說明：

1、RMAN也可以實現不完全恢復，方法比OS備份恢復的方法更簡單可靠

2、RMAN可以基於時間，基於改變與基於日誌序列的不完全恢復，基於日誌序列的恢復可以指定恢復到哪個日誌序列，如

run {

allocate channel ch1 type disk;

allocate channel ch2 type 'sbt_tape';

set until logseq 1234 thread 1;

restore controlfile to '$ORACLE_HOME/dbs/cf1.f'

replicate controlfile from '$ORACLE_HOME/dbs/cf1.f';

alter database mount;

restore database;

recover database;

sql "ALTER DATABASE OPEN RESETLOGS";

}

3、與所有的不完全恢復一樣，必須在mount下，restore所有備份資料檔案，需要resetlogs。

4、基於改變的恢復比基於時間的恢復更可靠，但是可能也更復雜，需要知道需要恢復到哪一個改變號(SCN)，在正常生產中，獲取SCN的辦法其實也有很多，如查詢資料庫字典表(V$archived_log or v$log_history)，或分析歸檔與聯機日誌(logmnr)等。

5、損壞聯機日誌的恢復方法

1.) 損壞非當前聯機日誌

聯機日誌分為當前聯機日誌和非當前聯機日誌，非當前聯機日誌的損壞是比較簡單的，一般透過clear命令就可以解決問題。

a.) 啟動資料庫

sys@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 251661728 bytes

Database Buffers 4194304 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-03113: 通訊通道的檔案結尾

程式 ID: 16373

會話 ID: 1 序列號: 5

遇到ORA-00312 or ORA-00313錯誤，從這裡我們知道日誌組1的資料檔案損壞了，從報警檔案可以看到更詳細的資訊。如：

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: ' /opt/oracle/oradata/orcl/redo03.log'

b.) 檢視V$log檢視

TEST@ORCL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

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

1 1 NO CURRENT

2 0 YES UNUSED

3 0 YES UNUSED

可以知道，該組是非當前狀態，而且已經歸檔。

c.) 用CLEAR命令重建該日誌檔案

SYS@ORCL> alter database clear logfile group 3;

如果是該日誌組還沒有歸檔，則需要用

SYS@ORCL> alter database clear unarchived logfile group 3;

d.) 開啟資料庫，重新備份資料庫

SYS@ORCL> alter database open;

說明：

1、如果損壞的是非當前的聯機日誌檔案，一般只需要clear就可以重建該日誌檔案，但是如果該資料庫處於歸檔狀態但該日誌還沒有歸檔，就需要強行clear。

2、建議clear，特別是強行clear後作一次資料庫的全備份。

3、此方法適用於歸檔與非歸檔資料庫。

2.) 損壞當前聯機日誌

歸檔模式下當前日誌的損壞有兩種情況：

一、是資料庫是正常關閉，日誌檔案中沒有未決的事務需要例項恢復，當前日誌組的損壞就可以直接用alter database clear unarchived logfile group n來重建。

二、是日誌組中有活動的事務，資料庫需要媒體恢復，日誌組需要用來同步，有兩種補救辦法：

A.最好的辦法就是透過不完全恢復，可以保證資料庫的一致性，但是這種辦法要求在歸檔方式下，並且有可用的備份。

B.透過強制性恢復，但是可能導致資料庫不一致。

下面分別用來說明這兩種恢復方法

a.) 透過備份來恢復

1、開啟資料庫，會遇到一個類似的錯誤

SQL@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-03113: 通訊通道的檔案結尾

程式 ID: 19294

會話 ID: 1 序列號: 5

Alert日誌報錯：

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19294.trc:

ORA-00313: 無法開啟日誌組 1 (用於執行緒 ) 的成員

ORA-00312: 聯機日誌 1 執行緒 1: '/opt/oracle/oradata/orcl/redo01.log'

USER (ospid: 19294): terminating the instance due to error 313

Tue Nov 29 15:35:13 2011

ARC1 started with pid=21, OS id=19298

Instance terminated by USER, pid = 19294

2、檢視V$log，發現是當前日誌

TEST@ORCL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

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

1 1 NO CURRENT

3 0 YES UNUSED

2 0 YES UNUSED

3、發現clear不成功

TEST@ORCL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of thread 1

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/orcl/redo01.log '

4、複製有效的資料庫的全備份，並不完全恢復資料庫

可以採用獲取最近的SCN的辦法用until scn恢復或用until cnacel恢復

RMAN> recover database until cancel

先選擇auto，儘量恢復可以利用的歸檔日誌，然後重新。

RMAN> recover database until cancel

這次輸入cancel，完成不完全恢復，也就是說恢復兩次。如：

RMAN> recover database until cancel;

Auto

……

RMAN> recover database until cancel;

Cancel;

5、利用alter database open resetlogs開啟資料庫

說明：

1、這種辦法恢復的資料庫是一致的不完全恢復，會丟失當前聯機日誌中的事務資料。

2、這種方法適合於歸檔資料庫並且有可用的資料庫全備份。

3、恢復成功之後，記得再做一次資料庫的全備份。

4、建議聯機日誌檔案一定要實現鏡相在不同的磁碟上，避免這種情況的發生，因為任何資料的丟失對於生產來說都是不容許的。

b.) 如果沒有備份，進行強制性恢復

1、開啟資料庫，會遇到一個類似的錯誤

sys@ORCL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-03113: 通訊通道的檔案結尾

程式 ID: 21358

會話 ID: 1 序列號: 5

Alert日誌：

Tue Nov 29 15:49:39 2011

ARC1 started with pid=21, OS id=21362

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_21321.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/orcl/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_21321.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/orcl/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_21358.trc:

ORA-00313: 無法開啟日誌組 1 (用於執行緒 ) 的成員

ORA-00312: 聯機日誌 2 執行緒 1: '/opt/oracle/oradata/orcl/redo02.log'

USER (ospid: 21358): terminating the instance due to error 313

Tue Nov 29 15:49:39 2011

ARC2 started with pid=22, OS id=21364

Instance terminated by USER, pid = 21358

2、檢視V$log，發現是當前日誌

TEST@ORCL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

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

1 3 YES INACTIVE

3 4 YES INACTIVE

2 5 NO CURRENT

3、發現clear不成功

TEST@ORCL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/orcl/redo02.log '

4、把資料庫down掉

TEST@ORCL> shutdown immediate

5、在init.ora中加入如下引數

_allow_resetlogs_corruption=TRUE

6、重新啟動資料庫,利用until cancel恢復

TEST@ORCL> recover database until cancel;

Cancel

如果出錯，不再理會，發出

TEST@ORCL> alter database open resetlogs;

7、資料庫被開啟後，馬上執行一個full export

8、shutdown資料庫,去掉_all_resetlogs_corrupt引數

9、重建庫

10、import並完成恢復

11、建議執行一下ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;

說明：

1、該恢復方法是沒有辦法之後的恢復方法，一般情況下建議不要採用，因為該方法可能導致資料庫的不一致。

2、該方法也丟失資料，但是丟失的資料沒有上一種方法的資料多，主要是未寫入資料檔案的已提交或未提交資料。

3、建議成功後嚴格執行以上的7到11步，完成資料庫的檢查與分析

4、全部完成後做一次資料庫的全備份

5、建議聯機日誌檔案一定要實現鏡相在不同的磁碟上，避免這種情況的發生，因為任何資料的丟失對於生產來說都是不容許的。

6、損壞控制檔案的恢復方法

1.) 損壞單個控制檔案

損壞單個控制檔案是比較容易恢復的，因為一般的資料庫系統，控制檔案都不是一個，而且所有的控制檔案都互為鏡相，只要複製一個好的控制檔案替換壞的控制檔案就可以了。

1、控制檔案損壞，最典型的就是啟動資料庫出錯，不能mount資料庫。

SQL@ORCL> startup

ORA-00205: error in identifying controlfile, check alert log for more info

檢視報警日誌檔案，有如下資訊

alter database mount

Mon May 26 11:59:52 2003

ORA-00202: controlfile: '/opt/oracle/oradata/orcl /control01.ctl'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系統找不到指定的檔案。

2、停止資料庫

TEST@ORCL> shutdown immediate

3、複製一個好的控制檔案替換壞的控制檔案或修改init.ora中的控制檔案引數，取消這個壞的控制檔案。

4、重新啟動資料

TEST@ORCL> startup

說明：

1、損失單個控制檔案是比較簡單的，因為資料庫中所有的控制檔案都是鏡相的，只需要簡單的複製一個好的就可以了。

2、建議鏡相控制檔案在不同的磁碟上。

3、建議多做控制檔案的備份，長期保留一份由alter database backup control file to trace產生的控制檔案的文字備份。

2.) 損壞全部控制檔案

損壞多個控制檔案，或者人為的刪除了所有的控制檔案，透過控制檔案的複製已經不能解決問題，這個時候需要重新建立控制檔案。同時注意，alter database backup control file to trace可以產生一個控制檔案的文字備份。以下是詳細重新建立控制檔案的步驟

1、關閉資料庫

TEST@ORCL> shutdown immediate;

2、刪除所有控制檔案，模擬控制檔案的丟失。

3、啟動資料庫，出現錯誤，並不能啟動到mount下

TEST@ORCL> startup

ORA-00205: error in identifying controlfile, check alert log for more info

檢視報警日誌檔案，有如下資訊:

Tue Nov 29 16:02:42 2011

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/opt/oracle/flash_recovery_area/orcl/control02.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/opt/oracle/oradata/orcl/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Tue Nov 29 16:02:44 2011

Checker run found 2 new persistent data failures

ORA-205 signalled during: ALTER DATABASE MOUNT...

4、關閉資料庫

TEST@ORCL> shutdown immediate;

5、在internal或sys下執行如下建立控制檔案的指令碼，注意完整列出聯機日誌或資料檔案的路徑，或修改由alter database backup control file to trace備份控制檔案時產生的指令碼，去掉多餘的註釋即可。

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 '/opt/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/opt/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/opt/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/oradata/orcl/system01.dbf',

'/home/oracle/oradata/sysaux01.dbf',

'/opt/oracle/oradata/orcl/undotbs01.dbf',

'/home/oracle/oradata/users01.dbf',

'/home/oracle/oradata/users02.dbf',

'/home/oracle/oradata/undotbs02.dbf'

CHARACTER SET ZHS16GBK;

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf' SIZE 3408704 REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

或者：

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 '/opt/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/opt/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/opt/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/oradata/orcl/system01.dbf',

'/home/oracle/oradata/sysaux01.dbf',

'/opt/oracle/oradata/orcl/undotbs01.dbf',

'/home/oracle/oradata/users01.dbf',

'/home/oracle/oradata/users02.dbf',

'/home/oracle/oradata/undotbs02.dbf'

CHARACTER SET ZHS16GBK;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

6、ORA-00283和ORA-01610

idle> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: 要開啟資料庫則必須使用 RESETLOGS 或 NORESETLOGS 選項

idle> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: 檔案 1 需要介質恢復

ORA-01110: 資料檔案 1: '/opt/oracle/oradata/orcl/system01.dbf'

idle> recover datafile 1;

ORA-00283: 恢復會話因錯誤而取消

ORA-01610: 使用 BACKUP CONTROLFILE 選項的恢復必須已完成

idle> recover database using backup controlfile until cancel;

ORA-00279: 更改 21081244 (在 11/29/2011 15:39:54 生成) 對於執行緒 1 是必需的

ORA-00289: 建議: /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc

ORA-00280: 更改 21081244 (用於執行緒 1) 在序列 #2 中

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: 無法開啟歸檔日誌 '/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc'

ORA-27037: 無法獲得檔案狀態

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: 無法開啟歸檔日誌 '/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc'

ORA-27037: 無法獲得檔案狀態

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 將出現如下錯誤

ORA-01194: 檔案 1 需要更多的恢復來保持一致性

ORA-01110: 資料檔案 1: '/opt/oracle/oradata/orcl/system01.dbf'

這裡有幾個選項：

suggested:在上面ORA-00289: 建議:....會按這個檔案去恢復

filename:自己指定日誌檔案

auto:自動選擇，個人覺得好像和suggestted一樣。

cancel:這個可以不從介質恢復。

這裡使用suggested和auto都報錯，因為歸檔日誌已經不存在。檢視聯機日誌檔案：

idle> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

1 1 0 52,428,800 512 1 YES UNUSED 0 0

3 1 0 52,428,800 512 1 YES CURRENT 0 0

2 1 0 52,428,800 512 1 YES UNUSED 0 0

發現這裡的理解有問題，因為採用的是resetlogs，所以日誌檔案肯定是unused的。如果線上日誌未損壞，則可以指定線上日誌檔案執行恢復。

idle> recover database using backup controlfile until cancel;

ORA-00279: 更改 21081244 (在 11/29/2011 15:39:54 生成) 對於執行緒 1 是必需的

ORA-00289: 建議: /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc

ORA-00280: 更改 21081244 (用於執行緒 1) 在序列 #2 中

Specify log: {=suggested | filename | AUTO | CANCEL}

/opt/oracle/oradata/orcl/redo01.log

ORA-00339: 歸檔日誌未包含任何重做

ORA-00334: 歸檔日誌: '/opt/oracle/oradata/orcl/redo01.log'

ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 將出現如下錯誤

ORA-01194: 檔案 1 需要更多的恢復來保持一致性

ORA-01110: 資料檔案 1: '/opt/oracle/oradata/orcl/system01.dbf'

idle> recover database using backup controlfile until cancel;

ORA-00279: 更改 21081244 (在 11/29/2011 15:39:54 生成) 對於執行緒 1 是必需的

ORA-00289: 建議: /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc

ORA-00280: 更改 21081244 (用於執行緒 1) 在序列 #2 中

Specify log: {=suggested | filename | AUTO | CANCEL}

/opt/oracle/oradata/orcl/redo02.log

Log applied.

Media recovery complete.

idle> alter database open resetlogs;

Database altered.

sys@ORCL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND OFF;

Tablespace altered.

7、如果沒有錯誤，資料庫將啟動到open狀態下。

說明：

1、重建控制檔案用於恢復全部資料檔案的損壞，需要注意其書寫的正確性，保證包含了所有的資料檔案與聯機日誌。

2、經常有這樣一種情況，因為一個磁碟損壞，我們不能再恢復(store)資料檔案到這個磁碟，因此在store到另外一個盤的時候，我們就必須重新建立控制檔案，用於識別這個新的資料檔案，這裡也可以用這種方法用於恢復。

二、其它恢復案例

1、flashback table

1.Flashback Table語法

FLASHBACK TABLE tablename TO TIMESTAMP to_timestamp('2011-05-07 08:23:48','yyyy-mm-dd hh24:mi:ss');

FLASHBACK TABLE employee TO SCN 123456;

FLASHBACK TABLE t1 TO TIMESTAMP to_timestamp('2011-05-07 08:23:48','yyyy-mm-dd hh24:mi:ss'); ENABLE TRIGGERS;

2.閃回的前提條件

需要有flashback any table的系統許可權或者是該表的flashback物件許可權。

需要有該表的SELECT, INSERT, DELETE, ALTER許可權

必須保證該表ROW MOVEMENT

3.驗內容及目的

l 使用flashback table閃回到之前某個時間點。

l 驗證flashback table不能夠閃回被drop掉的索引。

l 驗證flashback table不能夠閃回truncate的表內容。

4.建立測試表

test@ORCL> set time on

15:18:07 test@ORCL> create table t1 as select * from dba_objects;

Table created.

15:19:10 test@ORCL> create table t2 as select * from t1;

Table created.

15:19:46 test@ORCL> select count(*) from t1;

COUNT(*)

----------

73861

15:20:19 test@ORCL> select count(*) from t2;

COUNT(*)

----------

73861

15:23:01 test@ORCL> create index inx_test1 on t1(object_name);

Index created.

15:23:07 test@ORCL> create index inx_test2 on t1(object_id);

Index created.

15:23:55 test@ORCL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2011-11-30 15:23:56

5.模擬刪除

刪除表的索引，delete方式刪除資料，truncate方式刪除資料。

15:26:34 test@ORCL> drop index inx_test1;

Index dropped.

15:26:36 test@ORCL> delete from t1;

73861 rows deleted.

15:26:54 test@ORCL> commit;

Commit complete.

15:28:02 test@ORCL> truncate table t2;

Table truncated.

15:28:13 test@ORCL> select count(*) from t1;

COUNT(*)

----------

0

15:28:21 test@ORCL> select count(*) from t2;

COUNT(*)

----------

0

6.Flashback Table閃回測試

15:28:28 test@ORCL> alter table t1 enable row movement;

Table altered.

15:30:55 test@ORCL> alter table t2 enable row movement;

Table altered.

15:32:41 test@ORCL> flashback table t1 to timestamp to_timestamp('2011-11-30 15:26:36','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

15:33:53 test@ORCL> flashback table t2 to timestamp to_timestamp('2011-11-30 15:28:02','yyyy-mm-dd hh24:mi:ss');

flashback table t2 to timestamp to_timestamp('2011-11-30 15:28:02','yyyy-mm-dd hh24:mi:ss')

*

ERROR at line 1:

ORA-01466: 無法讀取資料 - 表定義已更改 ----說明truncate的資料無法flashback。

15:35:19 test@ORCL> select count(*) from t1;

COUNT(*)

----------

73861

15:35:30 test@ORCL> select count(*) from t2;

COUNT(*)

----------

0

15:36:54 test@ORCL> select index_name from user_indexes where table_name = 'T1';

INDEX_NAME

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

INX_TEST2 --- 說明drop的索引無法flashback。

7.小結

l 使用flashback table可以將delete方式刪除的表閃回到之前某個時間點。

l flashback table功能不能夠將被drop掉的索引恢復出來，因為drop索引的過程屬於DDL操作，不記錄undo資訊。

l flashback table功能不能夠將被truncate的表內容恢復出來，原因也是truncate操作過程是不記錄undo資訊。

8. Flashback Versions Query

從oracle 10g開始，Flashback Technologies有了很大的改進，其中的Flashback Versions Query技術可以讓你輕鬆檢視到兩個時間點或scn點之間的同一資料的變動情況(必須是在flashback範圍以內)。例如下面的語句可以輕鬆檢視t_dept表中09:00:00到09:16:00之間的資料變化情況：

SQL> SELECT * FROM mis.t_sys_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

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

deptid deptcode deptname deptlevel terminated parent flag manager short depttype sort u8code

5522 101004 投資公司 2 0 1 1 5697 0 7 TZ

5522 101004 投資公司 2 0 1 0 5697 0 7 TZ

2 rows selected

SQL> SELECT * FROM mis.t_sys_dept d

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:16:00','yyyy-mm-dd hh24:mi:ss')

WHERE D.DEPTid=5522;

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

VERSIONS BETWEEN TIMESTAMP

*

ERROR AT line 2:

ORA-00933: SQL command NOT properly ended

偽列：

如果使用偽列，則不能使用*來統配表中所有的欄位：

SQL> SELECT versions_operation,* FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

SELECT versions_operation,* FROM oa.t_dept

*

ERROR at line 1:

ORA-00936: missing expression

SQL> SELECT *,versions_operation FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

SELECT *,versions_operation FROM oa.t_dept

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

使用偽列和欄位名：

SQL> SELECT deptid,deptname,versions_operation FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

DEPTID DEPTNAME V

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

5522 投資公司 U

5522 投資公司

如果要使用*來統配表中所有的欄位，則必須在*前加上表名：

SQL> SELECT oa.t_dept.*,versions_operation FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

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

deptid deptcode deptname deptlevel terminated parent flag manager short depttype sort u8code

5522 101004 投資公司 2 0 1 1 5697 0 7 TZ

5522 101004 投資公司 2 0 1 0 5697 0 7 TZ

2 rows selected

注：關於Flashback Pseudocolumns介紹

VERSIONS_STARTSCN

Starting SCN when the row was first created. This identifies the SCN when the data first took on the values displayed in the row version.If NULL, the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_STARTTIME

Starting TIMESTAMP when the row version was first created. This identifies the time when the data first took on the values displayed in the row version. If NULL, the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_ENDSCN

Ending SCN when the row version expired. This identifies the row expiration SCN. If NULL, then either the row version is still current or the row corresponds to a DELETE operation.

VERSIONS_ENDTIME

Ending TIMESTAMP when the row version expired. This identifies the row expiration time. If NULL, then either the row version is still current or the row corresponds to a DELETE operation.

VERSIONS_XID

Identifier of the transaction that created the row version.

VERSIONS_OPERATION

This is the operation performed by the transaction that modified the data. The values are I for insertion, D for deletion, or U for update.

2、flashback database

閃回技術通常用於快速簡單恢復資料庫中出現的認為誤操作等邏輯錯誤，從閃回的方式可以分為基於資料庫級別閃回、表級別閃回、事務級別閃回，根據閃回對資料的影響程度又可以分為閃回恢復，閃回查詢。閃回恢復將修改資料，閃回點之後的資料將全部丟失。而閃回查詢則可以查詢資料被DML的不同版本，也可以在此基礎之上確定是否進行恢復等。本文主要描述flashback database的使用。

1、flashback database特性

l flashback database閃回到過去的某一時刻。

l 閃回點之後的工作全部丟失。

l 使用resetlogs建立新的場景並開啟資料庫(一旦resetlogs之後，將不能再flashback至resetlogs之前的時間點)。

常用的場景

l truncate table恢復。

l 多表發生意外錯誤需要恢復。

l 使用閃回日誌來實現資料庫閃回，閃回點之後的資料將丟失。

2、flashback database的組成

閃回緩衝區:當啟用flashback database,則sga中會開闢一塊新區域作為閃回緩衝區,大小由系統分配

啟用新的rvwr程式:rvwr程式將閃回緩衝區的內容寫入到閃回日誌中，注意閃回日誌不同於聯機重做日誌，閃回日誌在聯機重做日誌基礎之上生成，是完整資料塊映像的日誌。聯機日誌則是變化的日誌。閃回日誌不能複用，也不能歸檔。閃回日誌使用迴圈寫方式。

3、flashback database的配置

flashback database要求資料庫必須處於歸檔模式，且閃回之後必須使用resetlogs開啟資料庫。

a. 檢視資料庫的歸檔模式及閃回是否啟用

test@ORCL> select log_mode,open_mode,flashback_on from v$database;

LOG_MODE OPEN_MODE FLASHBACK_ON

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

ARCHIVELOG READ WRITE NO --FLASHBACK_ON為NO，則表示閃回特性尚未啟用。

b.檢視及設定閃回目錄、閃回目錄空間大小等

--可以使用alter system set db_recovery_file_dest 來設定新路徑

--可以使用alter system set db_recovery_file_dest_size來設定新的大小

test@ORCL> show parameter db_recovery

NAME TYPE VALUE

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

db_recovery_file_dest string /opt/oracle/flash_recovery_area

db_recovery_file_dest_size big integer 3882M

c.設定閃回保留目標生存期

test@ORCL> show parameter db_flashback

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440 --預設為分鐘，即24小時。

test@ORCL> alter system set db_flashback_retention_target=30;

System altered. --設定保留時間為半小時

d.啟用flashback

如果是oracle 10g，必須在mount狀態下開啟，否則會報錯。Oracle 10g以後可以在open狀態直接開啟。

sys@ORCL> conn / as sysdba

Connected.

sys@ORCL> alter database flashback on;

Database altered.

sys@ORCL> ho ps -ef|grep rvw --可以看到新增了後臺程式rvwr

oracle 5212 1 0 14:00 ? 00:00:00 ora_rvwr_orcl

oracle 6167 440 0 14:03 pts/1 00:00:00 /bin/bash -c ps -ef|grep rvw

--下面檢視閃回區分配的大小為大約M，閃回分鐘以內的資料則需要M左右的空間

--注意列oldest_flashback_time說明了允許返回的最早的時間點

test@ORCL> select oldest_flashback_scn old_flhbck_scn,oldest_flashback_time old_flhbck_tim,retention_target rete_trgt,flashback_size/1024/1024 flhbck_siz, estimated_flashback_size/1024/1024 est_flhbck_size from v$flashback_database_log;

OLD_FLHBCK_SCN OLD_FLHBCK_TIM RETE_TRGT FLHBCK_SIZ EST_FLHBCK_SIZE

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

21298440 01-12月-11 30 7.8125 0

test@ORCL> select * from v$flashback_database_stat; --檢視閃回

BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE

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

01-12月-11 01-12月-11 2859008 3702784 761856 0

test@ORCL> select * from v$sgastat where name like 'flashback%'; --檢視sga中分配的閃回空間大小

POOL NAME BYTES

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

shared pool flashback generation buff 3,981,120

shared pool flashback_marker_cache_si 9,200

test@ORCL> ho ls -hlt $ORACLE_BASE/flash_recovery_area/ORCL/flashback --檢視生成的閃回日誌

total 7.9M

-rw-r----- 1 oracle oinstall 7.9M Dec 1 14:08 o1_mf_7fg603wq_.flb

test@ORCL> select * from v$flash_recovery_area_usage; --檢視閃回空間使用情況

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

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

CONTROL FILE 0 0 0

REDO LOG 0 0 0

ARCHIVED LOG 7.05 0 7

BACKUP PIECE 0 0 0

IMAGE COPY 0 0 0

FLASHBACK LOG .2 0 1

FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

4、模擬閃回資料庫

1.模擬使用者錯誤

sys@ORCL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 8

Next log sequence to archive 10

Current log sequence 10

sys@ORCL> select sysdate from dual;

SYSDATE

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

2011-12-01 14:55:02

sys@ORCL> drop table test.t3;

Table dropped.

SYS AS SYSDBA on 2005-03-29 17:02:22 >select sysdate from dual;

SYSDATE

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

2011-12-01 14:56:03

14:57:48 sys@ORCL> create table test.t1 as select * from dba_users;

Table created.

14:59:20 sys@ORCL> select sysdate from dual;

SYSDATE

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

2011-12-01 14:59:26

15:00:33 sys@ORCL> create table test.t2 as select * from dba_tablespaces;

Table created.

15:00:55 sys@ORCL> select sysdate from dual;

SYSDATE

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

2011-12-01 15:01:01

2.進行閃回操作

15:07:02 sys@ORCL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

15:09:08 sys@ORCL> startup mount;

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 251661728 bytes

Database Buffers 4194304 bytes

Redo Buffers 4980736 bytes

Database mounted.

15:09:23 sys@ORCL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

15:10:20 sys@ORCL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE

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

21298440 2011-12-01 14:00:58 30 12288000 4339712

15:13:29 sys@ORCL> flashback database to timestamp to_timestamp ('2011-12-01 14:56:03','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

3.只讀開啟，驗證資料

15:14:25 sys@ORCL> alter database open read only;

Database altered.

15:14:36 sys@ORCL> select * from test.t3;

select * from test.t3

*

ERROR at line 1:

ORA-00942: 表或檢視不存在

4.繼續修正恢復

15:15:07 sys@ORCL> alter database close;

Database altered.

15:16:05 sys@ORCL> flashback database to timestamp to_timestamp ('2011-12-01 14:36:03','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

15:16:12 sys@ORCL> alter database open;

alter database open

*

ERROR at line 1:

ORA-16196: 以前曾開啟和關閉過資料庫

15:16:41 sys@ORCL> alter database dismount;

Database altered.

15:17:10 sys@ORCL> shutdown immediate

ORA-01507: 未裝載資料庫

ORACLE instance shut down.

15:17:59 sys@ORCL> startup mount

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 251661728 bytes

Database Buffers 4194304 bytes

Redo Buffers 4980736 bytes

Database mounted.

15:18:47 sys@ORCL> alter database open read only;

Database altered.

15:19:31 sys@ORCL> select * from test.t3;

no rows selected ---test.t3表已經成功找回。

5.resetlogs開啟資料庫

15:21:40 sys@ORCL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

15:21:51 sys@ORCL> startup mount;

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

15:22:08 sys@ORCL> alter database open resetlogs;

Database altered.

注意：一旦resetlogs之後，將不能再flashback至resetlogs之前的時間點。

3、imp恢復

1、匯入型別

1、匯入表

imp scott/tiger TABLES=(dept,emp) file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

2、匯入方案

imp scott/tiger SCHEMAS=scott file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

3、匯入表空間

imp system/manager TABLESPACES=user01 file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

4、匯入資料庫

imp system/manager FULL=y file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

2、 imp 選項

E:\>imp help=y

可以透過輸入 IMP 命令和您的使用者名稱/口令

跟有您的使用者名稱 / 口令的命令:

例項: IMP SCOTT/TIGER

或者, 可以透過輸入 IMP 命令和各種自變數來控制“匯入”按照不同引數。

要指定引數，您可以使用關鍵字:

格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,...,vlaueN)

例項: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

或 TABLES=(T1: P1,T1: P2)，如果 T1 是分割槽表

USERID 必須是命令列中的第一個引數。

關鍵字說明（預設）

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

USERID 使用者名稱/口令

FULL 匯入整個檔案 (N)

BUFFER 資料緩衝區大小

FROMUSER 所有人使用者名稱列表

FILE 輸入檔案 (EXPDAT.DMP)

TOUSER 使用者名稱列表

SHOW 只列出檔案內容 (N)

TABLES 表名列表

IGNORE 忽略建立錯誤 (N)

RECORDLENGTH IO記錄的長度

GRANTS 匯入許可權 (Y)

INCTYPE 增量匯入型別

INDEXES 匯入索引 (Y)

COMMIT 提交陣列插入 (N)

ROWS 匯入資料行 (Y)

PARFILE 引數檔名

LOG 螢幕輸出的日誌檔案

CONSTRAINTS 匯入限制 (Y)

DESTROY 覆蓋表空間資料檔案 (N)

INDEXFILE 將表/索引資訊寫入指定的檔案

ANALYZE 執行轉儲檔案中的 ANALYZE 語句 (Y)

FEEDBACK 顯示每 x 行 (0) 的進度

TOID_NOVALIDATE 跳過指定型別 id 的校驗

FILESIZE 各轉儲檔案的最大尺寸

RESUMABLE 在遇到有關空間的錯誤時掛起 (N)

RESUMABLE_NAME 用來標識可恢復語句的文字字串

RESUMABLE_TIMEOUT RESUMABLE 的等待時間

COMPILE 編譯過程, 程式包和函式 (Y)

STREAMS_CONFIGURATION 匯入 Streams 的一般後設資料 (Y)

STREAMS_INSTANITATION 匯入 Streams 的例項化後設資料 (N)

RECALCULATE_STATISTICS 重新計算統計值 (N)

SKIP_UNUSABLE_INDEXES 跳過不可用索引的維護 (N)

下列關鍵字僅用於可傳輸的表空間

TRANSPORT_TABLESPACE 匯入可傳輸的表空間後設資料 (N)

TABLESPACES 將要傳輸到資料庫的表空間

DATAFILES 將要傳輸到資料庫的資料檔案

TTS_OWNERS 擁有可傳輸表空間集中資料的使用者

【聽海日誌】之ORACLE恢復案例

一、資料庫恢復案例

1、丟失或損壞一個資料檔案

1.) 連線資料庫，建立測試表並插入記錄

2.) 備份資料庫表空間users

3.) 繼續在測試表中插入記錄

4.) 關閉資料庫，模擬丟失資料檔案

5.) 啟動資料庫，檢查錯誤

6.) 先開啟資料庫

7.) 恢復該資料檔案表空間

8.) 檢查資料是否完整

2、多資料檔案丟失或損壞

1.) 連線資料庫，建立測試表並插入記錄

2.) 備份資料庫

3.) 繼續在測試表中插入記錄

4.) 關閉資料庫，模擬丟失資料檔案

5.) 啟動資料庫，檢查錯誤

6.) 利用RMAN進行恢復

7.) 檢查資料庫的資料（完全恢復）

3、基於時間的不完全恢復案例

1.) 連線資料庫，建立測試表並插入記錄

2.) 備份資料庫(最好備份所有的資料檔案，包括臨時資料檔案)

3.) 刪除測試表

4.) 準備恢復到時間點

5.) restore所有的資料檔案

6.) 開始不完全恢復

7.) 開啟資料庫檢查資料

4、基於SCN的不完全恢復案例

1.) 連線資料庫，建立測試表並插入記錄

2.) 備份資料庫

3.) 刪除測試表

4.) 準備恢復資料庫

5.) 開始恢復到改變點

6.) 檢查資料

5、損壞聯機日誌的恢復方法

1.) 損壞非當前聯機日誌

2.) 損壞當前聯機日誌

a.) 透過備份來恢復

b.) 如果沒有備份，進行強制性恢復

6、損壞控制檔案的恢復方法

1.) 損壞單個控制檔案

2.) 損壞全部控制檔案

二、其它恢復案例

1、flashback table

1.Flashback Table語法

2.閃回的前提條件

3.驗內容及目的

4.建立測試表

5.模擬刪除

6.Flashback Table閃回測試

7.小結

8. Flashback Versions Query

2、flashback database

1、flashback database特性

2、flashback database的組成

3、flashback database的配置

a. 檢視資料庫的歸檔模式及閃回是否啟用

b.檢視及設定閃回目錄、閃回目錄空間大小等

c.設定閃回保留目標生存期

d.啟用flashback

4、模擬閃回資料庫

1.模擬使用者錯誤

2.進行閃回操作

3.只讀開啟，驗證資料

4.繼續修正恢復

5.resetlogs開啟資料庫

3、imp恢復

1、匯入型別

1、匯入表

2、匯入方案

3、匯入表空間

4、匯入資料庫

2、 imp 選項

相關文章