使用恢復建議恢復資料庫

這裡舉例以下使用恢復建議來恢復丟失當前使用重做日誌檔案組的資料庫。
這個恢復過程，只需要3條簡單的語句就可以完成。以下是演示的過程：（3條語句）
RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;
環境：無備份，開歸檔。

----恢復丟失當前使用重做日誌檔案狀況下的資料庫：

---檢視日誌檔案的資訊：

sys@PROD>col member for a50

sys@PROD>select group#,member from v$logfile;

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/PROD/redo03.log

         2 /u01/app/oracle/oradata/PROD/redo02.log

         1 /u01/app/oracle/oradata/PROD/redo01.log

#共有三個日誌檔案組，每個組只有一個日誌成員。

 ---檢視日誌組的狀態：

sys@PROD>

sys@PROD>select group#,status from v$log;

    GROUP# STATUS

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

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

#當前正在使用的是第三號日誌檔案組。

 

---檢視當前的資料庫模式：

sys@PROD>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     13

Next log sequence to archive   15

Current log sequence           15

#當前資料庫處於歸檔模式，如果不是歸檔模式，需要調至歸檔模式。

 

---給每個日誌組新增一個日誌成員：

sys@PROD>Alter database add logfile member

  2  '/u01/app/oracle/oradata/PROD/redo01b.log' to group 1;

Database altered.

 

sys@PROD>Alter database add logfile member

  2  '/u01/app/oracle/oradata/PROD/redo02b.log' to group 2;

Database altered.

 

sys@PROD>Alter database add logfile member

  2  '/u01/app/oracle/oradata/PROD/redo03b.log' to group 3;

Database altered.

#日誌檔案成功新增完成，新增的日誌檔案成員預設的大小與該組原有的成員的大小一致。

當在新增日誌組的時候，需要指定日誌檔案成員的大小。

---再次檢視日誌檔案資訊：

sys@PROD>select group#,member from v$logfile;

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/PROD/redo03.log

         2 /u01/app/oracle/oradata/PROD/redo02.log

         1 /u01/app/oracle/oradata/PROD/redo01.log

         1 /u01/app/oracle/oradata/PROD/redo01b.log

         2 /u01/app/oracle/oradata/PROD/redo02b.log

         3 /u01/app/oracle/oradata/PROD/redo03b.log

#日誌成員新增成功。

---檢視日誌組與成員的資訊：

sys@PROD>select group#,status,members,bytes/1024/1024 Mbytes

  2  from v$log;

    GROUP# STATUS              MEMBERS     MBYTES

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

         1 INACTIVE                  2         50

         2 INACTIVE                  2         50

         3 CURRENT                   2         50

#當前使用的是第三組日誌檔案，每個日誌組包括兩個日誌成功，每個日誌檔案的大小為50M。

 

---刪除當前使用日誌組的一個日誌成員：

/u01/app/oracle/oradata/PROD/redo03b.log

sys@PROD>!rm /u01/app/oracle/oradata/PROD/redo03b.log

sys@PROD>!ls /u01/app/oracle/oradata/PROD/redo03b.log

ls: /u01/app/oracle/oradata/PROD/redo03b.log: No such file or directory

#刪除完成。

 

---致性關庫並重新開啟：

sys@PROD>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@PROD>startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             507513936 bytes

Database Buffers          322961408 bytes

Redo Buffers                2371584 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 5748

Session ID: 1 Serial number: 5

----嘗試切換日誌檔案：

[oracle@enmo ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 18 11:06:44 2016

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

Connected to an idle instance.

 

PROD>startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             507513936 bytes

Database Buffers          322961408 bytes

Redo Buffers                2371584 bytes

Database mounted.

PROD>alter system switch logfile;

alter system switch logfile

*

ERROR at line 1:

ORA-01109: database not open

 

---透過rman檢視檔案錯誤資訊：

[oracle@enmo ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 18 11:09:32 2016

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

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

RMAN>

RMAN> list failure;

 

using target database control file instead of recovery catalog

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

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

2025       CRITICAL OPEN      18-NOV-16     Redo log group 3 is unavailable

2022       HIGH     OPEN      18-NOV-16     Redo log file /u01/app/oracle/oradata/PROD/redo03.log is corrupt

 

---透過rman檢視恢復丟失檔案的建議：

RMAN>  advise failure;

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

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

2025       CRITICAL OPEN      18-NOV-16     Redo log group 3 is unavailable

2022       HIGH     OPEN      18-NOV-16     Redo log file /u01/app/oracle/oradata/PROD/redo03.log is corrupt

 

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

 

Mandatory Manual Actions

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

no manual actions available

 

Optional Manual Actions

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

no manual actions available

 

Automated Repair Options

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

Option Repair Description

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

1      Open resetlogs  

  Strategy: The repair includes complete media recovery with no data loss

 

---利用rman透過修復建議修復丟失的檔案：

RMAN>  repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3535562984.hm

 

contents of repair script:

   # recover database until cancel and open resetlogs

   sql 'alter database recover database until cancel';

   alter database open resetlogs;

 

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

sql statement: alter database recover database until cancel

 

database opened

repair failure complete

 

---修復完成並開啟資料庫：

PROD>select status from v$instance;

STATUS

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

OPEN

#資料庫已經處於開啟狀態。