日志文件维护相关资料见下:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1004.htm#i2135589

----添加日志文件组,一个组可包含多个日志文件,且大小内容相同
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.

ITPub博客

oracle logfile日志文件常规操作小记

最新文章