oracle datafile logfile group,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'

---因在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.