kingsql 如何修改Oralce日誌檔案大小

kingsql發表於2013-03-17
SQL
如果日誌頻繁切換，就需要增加日誌檔案的大小

>select GROUP#,STATUS,MEMBER from v$logfile;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 STALE /u01/app/oracle/oradata/hzh/redo03.log
2 STALE /u01/app/oracle/oradata/hzh/redo02.log
1 /u01/app/oracle/oradata/hzh/redo01.log
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 1 CURRENT NO 50M
2 1 INACTIVE YES 50M
3 1 INACTIVE YES 50M
>alter database add logfile group 4 ('/u01/app/oracle/oradata/hzh/redo04.log','/u01/app/oracle/oradata/hzh/redo04b.log') size 200M;
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 1 CURRENT NO 50M
2 1 INACTIVE YES 50M
3 1 INACTIVE YES 50M
4 2 UNUSED YES 200M
>alter database drop logfile group 2;
Database altered.
>alter database drop logfile group 3;
Database altered.
>alter system switch logfile;
System altered.
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 1 ACTIVE YES 50M
4 2 CURRENT NO 200M
[oracle@hong ~]$ cd /u01/app/oracle/oradata/hzh/
[oracle@hong hzh]$ rm redo02* redo03*
>alter database add logfile group 2 ('/u01/app/oracle/oradata/hzh/redo02.log','/u01/app/oracle/oradata/hzh/redo02b.log') size 200M;
Database altered.
>alter database add logfile group 3 ('/u01/app/oracle/oradata/hzh/redo03.log','/u01/app/oracle/oradata/hzh/redo03b.log') size 200M;
Database altered.
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 1 INACTIVE YES 50M
2 2 UNUSED YES 200M
3 2 UNUSED YES 200M
4 2 CURRENT NO 200M
>alter database drop logfile group 1;
Database altered.
[oracle@hong hzh]$ rm redo01.log
 >alter database add logfile group 1 ('/u01/app/oracle/oradata/hzh/redo01.log','/u01/app/oracle/oradata/hzh/redo01b.log') size 200M;
Database altered.
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 2 UNUSED YES 200M
2 2 UNUSED YES 200M
3 2 UNUSED YES 200M
4 2 CURRENT NO 200M
 >alter system switch logfile;
System altered.
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 2 CURRENT NO 200M
2 2 UNUSED YES 200M
3 2 UNUSED YES 200M
4 2 ACTIVE YES 200M
 >alter system switch logfile;
System altered.
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 2 ACTIVE YES 200M
2 2 CURRENT NO 200M
3 2 UNUSED YES 200M
4 2 INACTIVE YES 200M
到此，已經修改完了，後面的步驟，純屬個人潔癖
 >alter database drop logfile group 4;
Database altered.
>select GROUP#,MEMBERS,STATUS,ARCHIVED,BYTES/1024/1024||'M' bt from v$log;
GROUP# MEMBERS STATUS ARC BT
---------- ---------- ---------------- --- ----------
1 2 ACTIVE YES 200M
2 2 CURRENT NO 200M
3 2 UNUSED YES 200M
[oracle@hong hzh]$ rm redo04*

kingsql作品
如有建議請回帖或發電子郵件至
★希望向高手學習、同時幫助新手★

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-756331/，如需轉載，請註明出處，否則將追究法律責任。

相關文章