oracle logfile日誌檔案常規操作小記

日誌檔案維護相關資料見下:

----新增日誌檔案組,一個組可包含多個日誌檔案,且大小內容相同
SQL> alter database add logfile group 4 ('d:\oracle11g_64bit\oradata\orcl\redo04
.log') size 200m;

Database altered.

SQL> alter database add logfile group 5 ('d:\oracle11g_64bit\oradata\orcl\redo05
.log') size 200m;

Database altered.

SQL> alter database add logfile group 6 ('d:\oracle11g_64bit\oradata\orcl\redo06
.log') size 200m;

Database altered.

--須先在os上重新命名檔案為目標檔名,爾後執行下述命令,否則掃如下錯誤
SQL> alter database rename file 'd:\oracle11g_64bit\oradata\orcl\redo06.log' to
'd:\oracle11g_64bit\oradata\orcl\redo06_1.log';
alter database rename file 'd:\oracle11g_64bit\oradata\orcl\redo06.log' to 'd:\o
racle11g_64bit\oradata\orcl\redo06_1.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01512: error renaming log file D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO06.LOG -
new file d:\oracle11g_64bit\oradata\orcl\redo06_1.log not found
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) ？？？????？???？？???

---日誌檔案未在os真正刪除
SQL> alter database drop logfile group 6;

Database altered.

--再次佐證日誌檔案未物理刪除
SQL> alter database add logfile group 6 ('d:\oracle11g_64bit\oradata\orcl\redo06
.log') size 200m;
alter database add logfile group 6 ('d:\oracle11g_64bit\oradata\orcl\redo06.log'
) size 200m
*
ERROR at line 1:
ORA-00301: error in adding log file
'd:\oracle11g_64bit\oradata\orcl\redo06.log' - file cannot be created
ORA-27038: created file already exists
OSD-04010: ??? ??, ???????

 

SQL> alter database add logfile group 6 ('d:\oracle11g_64bit\oradata\orcl\redo06
.log') size 200m;

Database altered.

SQL> alter database rename file 'd:\oracle11g_64bit\oradata\orcl\redo06.log' to
'd:\oracle11g_64bit\oradata\orcl\redo06_1.log';

Database altered.

---current狀態的檔案不能刪除
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: 'D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO03.LOG'

--關於日誌檔案status列含義,請參考:
http://space.itpub.net/9240380/viewspace-670477

---因在alert頻見ckpt未完成及不能寫入新日誌,增加日誌組及日誌檔案大小
SQL> select group#,members,archived,bytes/1024/1024 mb,status from v$log;

    GROUP#    MEMBERS ARCHIV         MB STATUS
---------- ---------- ------ ---------- --------------------------------
         3          1 NO             50 CURRENT
         4          1 YES           200 UNUSED
         5          1 YES           200 UNUSED
         6          1 YES           200 UNUSED
         7          1 YES           200 UNUSED
         8          1 YES           200 UNUSED
         9          1 YES           200 UNUSED

7 rows selected.