oracle调整日志组大小,调整Oracle单实例日志文件大小

---sqlplus连接服务器调整

C:\Users\user>sqlplus top_user/********@1001_solgle

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 30 10:45:20 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col space_num format a10

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 50m        CURRENT

2 50m        INACTIVE

3 50m        INACTIVE

SQL> select member from v$logfile;

MEMBER

-----------------------------------------------------------------------

E:\ORACLE_DATA\SYS_DATE\solgle\REDO03.LOG

E:\ORACLE_DATA\SYS_DATE\solgle\REDO02.LOG

E:\ORACLE_DATA\SYS_DATE\solgle\REDO01.LOG

----删除未在使用的日志

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 50m        CURRENT

2 50m        INACTIVE

3 50m        INACTIVE

SQL> alter database drop logfile group 2;

Database altered.

-----到日志文件路径上删除group 2文件，既(E:\ORACLE_DATA\SYS_DATE\solgle\REDO02.LOG)

-----否则创建时报文件已经存在

SQL> alter database add logfile group 2 'E:\ORACLE_DATA\SYS_DATE\solgle\REDO02.LOG' size 200m;

Database altered.

SQL>

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 50m        CURRENT

2 200m       UNUSED

3 50m        INACTIVE

SQL>

SQL> alter database drop logfile group 3;

Database altered.

SQL>

----同理清理对应的日志文件

----创建日志组3

SQL> alter database add logfile group 3 'E:\ORACLE_DATA\SYS_DATE\solgle\REDO03.LOG' size 200m;

Database altered.

SQL>

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 50m        CURRENT

2 200m       UNUSED

3 200m       UNUSED

SQL>

SQL> alter system switch logfile;

System altered.

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 50m        ACTIVE

2 200m       CURRENT

3 200m       UNUSED

SQL>

SQL> alter system switch logfile;    ----执行了多次

System altered.

----查看日志状态

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 50m        ACTIVE

2 200m       CURRENT

3 200m       ACTIVE

----等待日志归档完成

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 50m        INACTIVE

2 200m       CURRENT

3 200m       INACTIVE

SQL>

SQL> alter database drop logfile group 1;

Database altered.

----删除日志组group 1文件

SQL> alter database add logfile group 1 'E:\ORACLE_DATA\SYS_DATE\solgle\REDO01.LOG' size 200m;

Database altered.

SQL>

-----日志调整完成

SQL> select group#,bytes/1024/1024||'m' space_num,status from v$log;

GROUP# SPACE_NUM  STATUS

---------- ---------- ----------------

1 200m       UNUSED

2 200m       CURRENT

3 200m       INACTIVE

SQL>