丟失當前使用的重做的日誌情況下，把資料庫恢復到正常狀態，並且儘量把資料丟失的風險
降到最低。oracle防止資料丟失的機制也非常靈活到位，當丟失當前的redo日志文件，oracle要求
把它歸檔完成了才能刪除，歸檔的前提就是把當前的日誌組切換到另外一個日誌檔案組。以下是
實驗的過程：

----恢復丟失當前重做日誌檔案組的資料庫：

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

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

6 rows selected.

#共有3個日誌組6個日子成員。

---檢視當前的日誌檔案組：

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

GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

#當前使用的是第1個日誌組。

---檢視測試表中的記錄：

PROD>select * from scott.test;

X Y

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

1 10

2 20

#共有兩條記錄。

---往測試表中插入兩條測試資料記錄：

PROD>insert into scott.test values(3,30);

1 row created.

PROD>insert into scott.test values(4,40);

1 row created.

PROD>commit;

Commit complete.

#插入測試資料完成。

---再次檢視嘗試表中的記錄：

PROD>select * from scott.test;

X Y

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

1 10

2 20

3 30

4 40

#現在共有4條記錄。

---刪除當前使用的日誌組：

PROD>!ls /u01/app/oracle/oradata/PROD/redo01.log

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

PROD>!ls /u01/app/oracle/oradata/PROD/redo01b.log

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

已經刪除第一個日誌檔案組。

---檢視日誌的歸檔狀態：

PROD>select group#,members,status,archived

2 from v$log;

GROUP# MEMBERS STATUS ARC

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

1 2 CURRENT NO

2 2 UNUSED YES

3 2 UNUSED YES

---切換日誌：

PROD>alter system switch logfile;

System altered.

#已經切換。

---再次檢視日誌組的歸檔情況。

PROD>select group#,members,status,archived

2 from v$log;

GROUP# MEMBERS STATUS ARC

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

1 2 ACTIVE NO

2 2 CURRENT NO

3 2 UNUSED YES

#由於已經刪除了第一個日誌檔案組，導致不能正常完成歸檔。

---嘗試刪除第一個日誌檔案組：

PROD>alter database drop logfile group 1;

alter database drop logfile group 1

*

ERROR at line 1:

ORA-00350: log 1 of instance PROD (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01.log'

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01b.log'

#警告必須讓第一個個日誌檔案組完成歸檔才能刪除。

---關閉資料庫並嘗試重啟資料庫：

PROD>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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: 16396

Session ID: 1 Serial number: 5

PROD>select status from v$instance;

ERROR:

ORA-03114: not connected to ORACLE

#不能正常開啟資料庫，無法連線資料。

--嘗試再次關閉例項：

PROD>shutdown immediate;

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

PROD>

---退出例項並嘗試開啟到mount狀態：

PROD>quit

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

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

[oracle@enmo ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 21:08:07 2016

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 database noarchivelog;

Database altered.

PROD>archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 2

Current log sequence 3

PROD>
#如果不是非歸檔模式，依然不能在oracle系統層刪除丟失的日誌檔案。

---刪除第一個日誌檔案組：

PROD>alter database drop logfile group 1;

Database altered.

---檢視日誌檔案組的歸檔狀態：

PROD>select group#,members,archived,status from v$log;

GROUP# MEMBERS ARC STATUS

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

3 2 YES UNUSED

2 2 NO CURRENT

---重新新增第一個日誌檔案組兩個日誌檔案成員：

PROD>alter database add logfile group 1

2 ('/u01/app/oracle/oradata/PROD/redo01.log',

3 '/u01/app/oracle/oradata/PROD/redo01b.log')

4 size 50M;

Database altered.

--同時檢視alert日誌檔案的記錄：

Tue Nov 22 21:19:06 2016

alter database add logfile group 1

('/u01/app/oracle/oradata/PROD/redo01.log',

'/u01/app/oracle/oradata/PROD/redo01b.log')

size 50M

Completed: alter database add logfile group 1

('/u01/app/oracle/oradata/PROD/redo01.log',

'/u01/app/oracle/oradata/PROD/redo01b.log')

size 50M

#新增日誌檔案組成功。

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

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

6 rows selected.

#日誌新增成功。

---再次檢視日誌檔案組的歸檔情況：

PROD>select group#,members,archived,status from v$log;

GROUP# MEMBERS ARC STATUS

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

1 2 YES UNUSED

3 2 YES UNUSED

2 2 NO CURRENT

---嘗試開啟資料庫：

PROD>alter database open;

Database altered.

---嘗試檢視Scott使用者的資料記錄是否丟失：

PROD>select * from scott.test;

X Y

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

1 10

2 20

3 30

4 40

#資料庫已經恢復，並無資料丟失。

丟失當前current重做日誌檔案下恢復資料庫

相關文章