給redo增加mirror之後別忘了switch幾次

講課時隨手的一些總結[@more@]

SQL> col member format a40
SQL> select group#,member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------
2 D:ORADATATESTREDO02.LOG
3 D:ORADATATESTREDO03.LOG
1 D:ORADATATESTREDO01.LOG

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

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE

SQL> alter database add logfile 'd:oradatatestredo04.log' size 50m;

資料庫已更改。

SQL> select group#,members from v$log;

GROUP# MEMBERS
---------- ----------
1 1
2 1
3 1
4 1

SQL> alter database add logfile member 'd:oradatatestlogredo04.log' to group
4;

資料庫已更改。

SQL> select group#,members from v$log;

GROUP# MEMBERS
---------- ----------
1 1
2 1
3 1
4 2

SQL> select group#,member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------
2 D:ORADATATESTREDO02.LOG
3 D:ORADATATESTREDO03.LOG
1 D:ORADATATESTREDO01.LOG
4 D:ORADATATESTREDO04.LOG
4 D:ORADATATESTLOGREDO04.LOG

SQL> alter database add logfile member 'd:oradatatestlogredo03.log' to group
3;

資料庫已更改。

SQL> alter database add logfile member 'd:oradatatestlogredo02.log' to group
2;

資料庫已更改。

SQL> alter database add logfile member 'd:oradatatestlogredo01.log' to group
1;

資料庫已更改。

SQL> select group#,member from v$logfile order by group#,member;

GROUP# MEMBER
---------- ----------------------------------------
1 D:ORADATATESTLOGREDO01.LOG
1 D:ORADATATESTREDO01.LOG
2 D:ORADATATESTLOGREDO02.LOG
2 D:ORADATATESTREDO02.LOG
3 D:ORADATATESTLOGREDO03.LOG
3 D:ORADATATESTREDO03.LOG
4 D:ORADATATESTLOGREDO04.LOG
4 D:ORADATATESTREDO04.LOG

已選擇8行。

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

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 INACTIVE
3 2 INACTIVE
4 2 UNUSED

SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
--==============================
--刪除當前日誌檔案'D:ORADATATESTREDO01.LOG'
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area 272629760 bytes
Fixed Size 1248528 bytes
Variable Size 180355824 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
資料庫裝載完畢。
SQL> select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
4 2 UNUSED
3 2 INACTIVE
2 2 INACTIVE

SQL> alter database open;
alter database open
*
第 1 行出現錯誤:
ORA-00313: 無法開啟日誌組 1 (用於執行緒 1) 的成員
ORA-00312: 聯機日誌 1 執行緒 1: 'D:ORADATATESTREDO01.LOG'
ORA-00312: 聯機日誌 1 執行緒 1: 'D:ORADATATESTLOGREDO01.LOG'

--這裡明明redo有mirror可是卻打不開？檢視狀態發現原來redo做了mirror之後其實mirror

logfile並沒有被完全利用上，因此給redo做了mirror之後千萬別忘了連續swithch幾次...讓oracle徹底使用上這些mirror redo logfile

SQL> select group#,member,status from v$logfile order by group#,member;

GROUP# MEMBER STATUS
---------- ---------------------------------------- -------
1 D:ORADATATESTLOGREDO01.LOG INVALID
1 D:ORADATATESTREDO01.LOG
2 D:ORADATATESTLOGREDO02.LOG INVALID
2 D:ORADATATESTREDO02.LOG
3 D:ORADATATESTLOGREDO03.LOG INVALID
3 D:ORADATATESTREDO03.LOG STALE
4 D:ORADATATESTLOGREDO04.LOG INVALID
4 D:ORADATATESTREDO04.LOG

已選擇8行。

SQL>
SQL> select status from v$instance;

STATUS
------------
MOUNTED
SQL> recover database until cancel;
完成介質恢復。
SQL> alter database open;
alter database open
*
第 1 行出現錯誤:
ORA-01589: 要開啟資料庫則必須使用 RESETLOGS 或 NORESETLOGS 選項

SQL> alter database open resetlogs;

資料庫已更改。

SQL> select group#,member,status from v$logfile order by group#,member;

GROUP# MEMBER STATUS
---------- ---------------------------------------- -------
1 D:ORADATATESTLOGREDO01.LOG
1 D:ORADATATESTREDO01.LOG
2 D:ORADATATESTLOGREDO02.LOG
2 D:ORADATATESTREDO02.LOG
3 D:ORADATATESTLOGREDO03.LOG
3 D:ORADATATESTREDO03.LOG
4 D:ORADATATESTLOGREDO04.LOG
4 D:ORADATATESTREDO04.LOG

已選擇8行。