oracle實驗記錄 （logfile基礎操作）
logfile的基本操作
SQL> select group#,member from v$logfile;
GROUP#
----------
MEMBER
------------------------------------------------------------------------------
3
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
2
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG
1
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG
GROUP#
----------
MEMBER
------------------------------------------------------------------------------
1
E:\REDO01_2.LOG
新增一個group
SQL> alter database add logfile group 4 ('e:\redo04_1.log','e:\redo04_2.log') si
ze 10m;
Database altered.
SQL> select group#,member from v$logfile;
SQL> alter database add logfile group 5 ('e:\redo05_1.log','d:\redo05_2.log') si
ze 10m;
Database altered.
GROUP# MEMBER
---------- ------------------------------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG
4 E:\REDO04_1.LOG
GROUP# MEMBER
---------- ------------------------------
4 E:\REDO04_2.LOG
6 rows selected.
新增組員(向組)
SQL> alter database add logfile member 'e:\redo04_3.log' to group 4;
Database altered.
新增組員(向組中成員 ,組中有幾個寫幾個)
MAXLOGMEMBERS 3 controlfile中 所以group 4已經不能新增
SQL> alter database add logfile member 'e:\redo04_4.log' to ('E:\REDO04_2.LOG','
E:\REDO04_1.LOG','e:\redo04_3.log');
alter database add logfile member 'e:\redo04_4.log' to ('E:\REDO04_2.LOG','E:\RE
DO04_1.LOG','e:\redo04_3.log')
*
ERROR at line 1:
ORA-00357: too many members specified for log file, the maximum is 3
SQL> alter database add logfile member 'e:\redo01_3.log' to ('E:\REDO01_2.LOG','
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG');
Database altered. 給group1加
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG
4 E:\REDO04_1.LOG
GROUP# MEMBER
---------- ------------------------------
4 E:\REDO04_2.LOG
4 E:\REDO04_3.LOG
1 E:\REDO01_3.LOG
8 rows selected.
刪除操作
SQL> select group# ,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE~~~~~~~~~不活動的可以刪除
2 1 INACTIVE
3 1 CURRENT
4 3 UNUSED**************新加入的還未使用 可以刪除
SQL> alter database drop logfile member 'E:\REDO04_2.LOG';
Database altered. 刪除1個成員
drop logfile group 4刪除整個組
另外還有clear logfile 清除logfile重建 無法歸檔的話unarchived
移動操作
只能移動不活動的
檢視環境
SQL> select group# ,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 UNUSED
3 1 INACTIVE
4 2 CURRENT
SQL> col member format a30
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG
4 E:\REDO04_1.LOG
GROUP# MEMBER
---------- ------------------------------
4 E:\REDO04_3.LOG
1 E:\REDO01_3.LOG
7 rows selected.
目標:移動group 4中 一個member, 並刪除另一個member or group
SQL> alter system switch logfile;
System altered.
SQL> select group# ,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 2 ACTIVE~~~~~~~~~~~~~~~還有未寫入disk的 dirty buffer
SQL> alter system checkpoint;~~~~~~~~實驗環境dirty block數目還不足以發生檢查點寫入,為了實驗速度 ,手動執行檢查點 寫入
從alert可以看出 switch之後 在等待 並沒有立刻完成( 完成條件不足) swtich logfile是個級別低的full checkpoint 發生後要等待 條件滿足DBWn才寫,具體條件會單獨在另一次實驗中記錄(增量檢查點，完全檢查點)
Beginning log switch checkpoint up to RBA [0x8.2.10], SCN: 0x0000.00514d4c
Thread 1 advanced to log sequence 8
Wed Jul 22 10:12:31 2009
ARC2: Evaluating archive log 4 thread 1 sequence 7
Wed Jul 22 10:12:32 2009
Committing creation of archivelog 'E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\ARCHIVELOG\2009_07_22\O1_MF_1_7_56DXDJ2G_.ARC'
Created Oracle managed file E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\ARCHIVELOG\2009_07_22\O1_MF_1_7_56DXDJ2G_.ARC
Wed Jul 22 10:12:32 2009
Current log# 2 seq# 8 mem# 0: E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG
Wed Jul 22 10:13:39 2009
Beginning global checkpoint up to RBA [0x8.b16.10], SCN: 5328377
Completed checkpoint up to RBA [0x8.2.10], SCN: 5328204
Completed checkpoint up to RBA [0x8.b16.10], SCN: 5328377
System altered.
SQL> select group# ,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 2 INACTIVE**************都寫入了
SQL> host copy E:\REDO04_1.LOG d:\redo04_1.log
已複製 1 個檔案。
SQL> host copy E:\REDO04_1.LOG d:\redo04_1.log
已複製 1 個檔案。
SQL> alter database rename file 'E:\REDO04_1.LOG' to 'd:\redo04_1.log';
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG
4 D:\REDO04_1.LOG***************************
GROUP# MEMBER
---------- ------------------------------
4 E:\REDO04_3.LOG
1 E:\REDO01_3.LOG
7 rows selected.
SQL> alter database drop logfile member 'D:\REDO04_1.LOG';
alter database drop logfile member 'D:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'D:\REDO04_1.LOG'
SQL> alter database drop logfile member 'E:\REDO04_3.LOG';
alter database drop logfile member 'E:\REDO04_3.LOG'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
SQL> alter database drop log group 4;
ERROR:
ORA-03114: not connected to ORACLE
SQL> alter database drop logfile group 4;
ERROR:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile member 'E:\REDO04_3.LOG';
alter database drop logfile member 'E:\REDO04_3.LOG'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~總結這樣分盤存放 造成無法刪除 member 無發刪除group
SQL> alter database rename file 'D:\REDO04_1.LOG' to 'e:\redo04_1.log';
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG
4 E:\REDO04_1.LOG
GROUP# MEMBER
---------- ------------------------------
4 E:\REDO04_3.LOG
1 E:\REDO01_3.LOG
7 rows selected.
SQL> select group# ,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 2 INACTIVE
SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'
SQL> alter database drop logfile member 'E:\REDO04_4.LOG';
alter database drop logfile member 'E:\REDO04_4.LOG'
*
ERROR at line 1:
ORA-00360: not a logfile member: E:\REDO04_4.LOG
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
移動回來也無法刪除
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG
4 E:\REDO04_1.LOG
GROUP# MEMBER
---------- ------------------------------
4 E:\REDO04_3.LOG
1 E:\REDO01_3.LOG
7 rows selected.
SQL> select group# ,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 2 INACTIVE
SQL> alter database drop logfile member 'E:\REDO04_3.LOG';
alter database drop logfile member 'E:\REDO04_3.LOG'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'
SQL> conn / as sysdba
Connected.
SQL> alter database drop logfile group 4
2 ;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
切幾次也不行
SQL> alter database clear logfile group 4;
Database altered.
SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'
SQL> alter database drop logfile member 'E:\REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_1.LOG'
SQL> alter database drop logfile group 4
2 ;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
清除也不行
SQL> startup force
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> select group# ,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 CURRENT
2 1 INACTIVE
3 1 INACTIVE
4 2 INACTIVE
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [1], [], [], [], [], [], []
SQL>
重啟也不行
SQL> alter database add logfile member 'E:\REDO04_4.LOG' TO GROUP 4;
Database altered.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [2], [], [], [], [], [], []
SQL> alter database drop logfile member 'E:\REDO04_4.LOG';
alter database drop logfile member 'E:\REDO04_4.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_4.LOG'
新增一個再刪也不行
SQL> col member format a30
SQL> select group# ,member from v$logFILE;
GROUP# MEMBER
---------- ------------------------------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG
4 E:\REDO04_1.LOG
GROUP# MEMBER
---------- ------------------------------
4 E:\REDO04_4.LOG
4 E:\REDO04_3.LOG
1 E:\REDO01_3.LOG
8 rows selected.
SQL> select group# ,members, status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 CURRENT
2 1 INACTIVE
3 1 INACTIVE
4 3 INACTIVE
SQL> alter database drop logfile member 'E:\REDO04_4.LOG','E:\REDO04_3.LOG','E:\
REDO04_1.LOG';
alter database drop logfile member 'E:\REDO04_4.LOG','E:\REDO04_3.LOG','E:\REDO0
4_1.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form. a valid logfile in group 4
ORA-01517: log member: 'E:\REDO04_4.LOG'
!!!!!!!!!!!!!!!!!!!!!!!!最後 倒騰來倒騰去 REDO LOG 出問題了 group4 關閉歸檔依然有問題 group4 中
alter database open
Wed Jul 22 12:05:01 2009
Errors in file e:\oracle\product\10.1.0\admin\orcl\bdump\orcl_lgwr_9040.trc:
ORA-00314: log 4 of thread 1, expected sequence# 22 doesn't match 0
ORA-00312: online log 4 thread 1: 'D:\REDO04_1.LOG'
Wed Jul 22 12:05:01 2009
Errors in file e:\oracle\product\10.1.0\admin\orcl\bdump\orcl_lgwr_9040.trc:
ORA-00314: log 4 of thread 1, expected sequence# 22 doesn't match 0
ORA-00312: online log 4 thread 1: 'D:\REDO04_1.LOG'
LGWR 程式有問題
理論就算current logfile 損壞了,要恢復比較麻煩drop 不了
SQL> alter database clear logfile group 4;重建
Database altered.
SQL> alter database open;
Database altered.
SQL> col member format a30
SQL> select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- ------------------------------ -------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA STALE
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG STALE
4 D:\REDO04_1.LOG
GROUP# MEMBER STATUS
---------- ------------------------------ -------
4 E:\REDO04_4.LOG
4 E:\REDO04_3.LOG INVALID
1 E:\REDO01_3.LOG STALE
8 rows selected.
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 3 CURRENT
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 INACTIVE
3 1 CURRENT
4 3 ACTIVE~~~~~~~~~切一下
SQL> alter system checkpoint;
System altered.
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 INACTIVE
3 1 CURRENT
4 3 INACTIVE
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2739], [2], [], [], [], [], [], []
還是刪除不了
SQL> select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- ------------------------------ -------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA STALE
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG STALE
4 D:\REDO04_1.LOG
GROUP# MEMBER STATUS
---------- ------------------------------ -------
4 E:\REDO04_4.LOG
4 E:\REDO04_3.LOG INVALID
1 E:\REDO01_3.LOG STALE
8 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- ------------------------------ -------
3 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO03.LOG
2 E:\ORACLE\PRODUCT\10.1.0\ORADA
TA\ORCL\REDO02.LOG
1 E:\ORACLE\PRODUCT\10.1.0\ORADA STALE
TA\ORCL\REDO01.LOG
1 E:\REDO01_2.LOG STALE
4 D:\REDO04_1.LOG
GROUP# MEMBER STATUS
---------- ------------------------------ -------
4 E:\REDO04_4.LOG
4 E:\REDO04_3.LOG INVALID
1 E:\REDO01_3.LOG STALE
8 rows selected.
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 3 INACTIVE
~最後好象還是沒有辦法~~~~~~~~~~~~~~~
正確的 分盤儲存logfile 方式 是如下的(在建立或ADD時候 分盤)
SQL> alter database add logfile group 5 ('e:\redo05_1.log','d:\redo05_2.log') si
ze 10m;
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 3 ACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 3 CURRENT
5 2 INACTIVE
SQL> select group#,member,status from v$logfile where group#=5;
GROUP# MEMBER STATUS
---------- ------------------------------ -------
5 E:\REDO05_1.LOG~~~~~~~~~~~~~~~~~
5 D:\REDO05_2.LOG~~~~~~~~~~~~~~~~~~~~~
SQL> alter database drop logfile member 'D:\REDO05_2.LOG';
Database altered.
SQL> alter database add logfile member 'd:\redo05_2.log' to group 5;
Database altered.
SQL> select group#,member,status from v$logfile where group#=5;
GROUP# MEMBER STATUS
---------- ------------------------------ -------
5 E:\REDO05_1.LOG~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5 D:\REDO05_2.LOG INVALID~~~~~~~~~~~~~~
SQL> alter database drop logfile group 5;
Database altered.
SQL> select group#,member,status from v$logfile where group#=5;
no rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-610591/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄（logfile基礎操作2）Oracle
- oracle實驗記錄 （dump logfile）Oracle
- oracle實驗記錄 （基礎,truncate與delete區別實驗）Oracledelete
- oracle實驗記錄 （SQL*PLUS 命令操作）OracleSQL
- oracle實驗記錄（並行操作與FTS COST）Oracle並行
- oracle實驗記錄 （恢復-rman操作delete(all) input ）Oracledelete
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- oracle實驗記錄 （恢復-rman操作(設定&備份)）Oracle
- oracle實驗記錄 (oracle reset parameter)Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- oracle實驗記錄 （FTS的cost與基數計算）Oracle
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （事務控制）Oracle
- oracle實驗記錄 （函式index）Oracle函式Index
- oracle實驗記錄 （bigfile tablespace）Oracle
- oracle實驗記錄 （恢復-redo）Oracle
- oracle實驗記錄 （expdp/impdp使用）Oracle
- oracle實驗記錄 （transport tablespace(Rman)）Oracle
- oracle實驗記錄 （使用exp/imp）Oracle
- oracle logfile日誌檔案常規操作小記Oracle
- oracle實驗記錄 （dump index b*tree）OracleIndex
- oracle實驗記錄 （CKPT的觸發）Oracle
- oracle實驗記錄 （dump undo4）Oracle
- oracle實驗記錄 （dump undo3）Oracle
- oracle實驗記錄 （dump undo2）Oracle
- oracle實驗記錄 手工 duplicate database(1)OracleDatabase
- oracle實驗記錄Rman duplicate database(1)OracleDatabase
- oracle實驗記錄 （許可權,role）Oracle
- oracle實驗記錄 （PFILE 啟動SPFILE）Oracle
- oracle實驗記錄 （SHARED server MODE）OracleServer