Oracle非歸檔模式Media Recovery錯誤之--ORA-26040

Oracle非歸檔模式Media Recovery錯誤之--ORA-26040

系統內容：

作業系統：Linux RH55

Oracle：  Oracle 11gR2

類比案例：

1、查看資料庫模式

18:12:36 SYS@ prod>archive log list;

Database log mode              No Archive Mode

Automatic archival            Disabled

Archive destination            /dsk4/arch1

Oldest online log sequence    1

Current log sequence          3

2、建立新的Tablespace

18:13:19 SYS@ prod>create tablespace tbs2

18:13:30  2  datafile '/u01/app/oracle/oradata/prod/tbs2.dbf' size 10m;

Tablespace created.

3、對資料庫進行冷備

[oracle@rh6 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 24 18:14:30 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=239333010)

RMAN> run {

2> shutdown immediate;

3> startup mount;

4> backup database format '/dsk3/bak/%s_%d.bak';

5> alter database open;

6> }

4、備份完成在新的Tablesapce上建立Object

18:19:40 SYS@ prod>conn scott/tiger

Connected.

18:30:13 SCOTT@ prod>create table t1 tablespace tbs2 as select * from emp;

Table created.

Elapsed: 00:00:01.00

18:30:45 SCOTT@ prod>select count(*) from t1;

  COUNT(*)

----------

        14

Elapsed: 00:00:00.02

18:30:56 SCOTT@ prod>insert into t1 select * from t1 where rownum=1;

1 row created.

Elapsed: 00:00:00.02

18:31:16 SCOTT@ prod>commit;

Commit complete.

Elapsed: 00:00:00.04

18:31:18 SCOTT@ prod>insert into t1 select * from t1 where rownum=1;

1 row created.

Elapsed: 00:00:00.01

18:31:23 SCOTT@ prod>select count(*) from t1;

  COUNT(*)

----------

        16

5、查看Redo Log資訊

18:32:14 SYS@ prod>select group#,sequence# ,status from v$log;

    GROUP#  SEQUENCE# STATUS

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

        1          4 CURRENT

        2          2 INACTIVE

        3          3 INACTIVE

Elapsed: 00:00:00.05

--------------------------------------分割線 --------------------------------------

Oracle 11G RAC 修改歸檔模式

Oracle手工完全恢複案例（歸檔模式）

Oracle手工恢複案例（非歸檔模式）

Oracle歸檔模式設定的相關指令與簡要說明

Oracle 10g 歸檔模式下備份指令碼

Oracle 歸檔模式與非歸檔模式的切換

--------------------------------------分割線 --------------------------------------

6、類比資料檔案被破壞

[oracle@rh6 ~]$ rm /u01/app/oracle/oradata/prod/tbs2.dbf

18:32:28 SYS@ prod>shutdown abort

ORACLE instance shut down.

重新啟動Instance到mount，查看redo log，日誌沒有發生switch

18:33:06 SYS@ prod>startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2217952 bytes

Variable Size            775948320 bytes

Database Buffers          54525952 bytes

Redo Buffers                2412544 bytes

Database mounted.

18:33:42 SYS@ prod>select group#,sequence# ,status from v$log;

    GROUP#  SEQUENCE# STATUS

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

        1          4 CURRENT

        3          3 INACTIVE

        2          2 INACTIVE

Open database出現資料檔案丟失錯誤：

18:34:17 SYS@ prod>alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 9 - see DBWR trace file

ORA-01110: data file 9: '/u01/app/oracle/oradata/prod/tbs2.dbf'

7、對Database做Media Recovery（因為沒有日誌切換，做complete recover）

[oracle@rh6 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 24 18:34:35 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=239333010, not open)

RMAN> restore datafile 9;

Starting restore at 24-JUL-14

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 00009 to /u01/app/oracle/oradata/prod/tbs2.dbf

channel ORA_DISK_1: reading from backup piece /dsk3/bak/34_PROD.bak

channel ORA_DISK_1: piece handle=/dsk3/bak/34_PROD.bak tag=TAG20140724T181640

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 24-JUL-14

RMAN> recover datafile 9;

Starting recover at 24-JUL-14

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 24-JUL-14

查看警示日誌：

Thu Jul 24 18:34:49 2014

Full restore complete of datafile 9 /u01/app/oracle/oradata/prod/tbs2.dbf.  Elapsed time: 0:00:00

  checkpoint is 2168258

Thu Jul 24 18:34:57 2014

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover if needed

 datafile 9

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0

  Mem# 0: /dsk1/oradata/prod/redo03a.log

  Mem# 1: /dsk2/oradata/prod/redo03b.log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0

  Mem# 0: /dsk1/oradata/prod/redo01a.log

  Mem# 1: /dsk2/oradata/prod/redo01b.log

Media Recovery Complete (prod)

Completed: alter database recover if needed

 datafile 9

通過group1和3，進行了media recovery ！

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 下一頁