幫朋友做的一個增加LOGFILE GROUP 和切換日誌的紀錄

幫一個朋友臨時試驗了一個紀錄出來。。

請參考吧 。。

[@more@]

- 以DBA 身份進入資料庫
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jan 4 16:32:57 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> desc v$log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE

- 察看現在的REDO LOG 有幾組
SQL> select group#,BYTES,MEMBERS, STATUS from v$log;

GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 536870912 1 CURRENT
2 536870912 1 INACTIVE
3 536870912 1 INACTIVE

SQL> desc v$logfile
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(257)
IS_RECOVERY_DEST_FILE VARCHAR2(3)

- 看資料庫現在的REDO FILE 位置
SQL> select GROUP#,STATUS,MEMBER from v$logfile;

GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
/d01/dmdata/dmdev/redo01.log

2
/d02/dmdata/dmdev/redo02.log

3
/d03/dmdata/dmdev/redo03.log

-看現在的日誌檔案的大小(500M)
SQL> ! ls -l /d01/dmdata/dmdev/redo01.log
-rw-r----- 1 oracle dba 536871424 Jan 4 16:34 /d01/dmdata/dmdev/redo0
1.log

- 增加一組新GROUP4 大小為1000M
SQL> alter database add logfile group 4 '/d01/dmdata/dmdev/redo04.log' size 1000
M;

Database altered.

- 察看增加結果
SQL> set linesize 1000
SQL> /

GROUP# STATUS MEMBER
---------- ------- -------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------
1 /d01/dmdata/dmdev/redo01.log
2 /d02/dmdata/dmdev/redo02.log
3 /d03/dmdata/dmdev/redo03.log
4 /d01/dmdata/dmdev/redo04.log

- 看現在的日誌狀態,只有在INACTIVE 狀態太能DROP 掉
SQL> select group#,BYTES,MEMBERS, STATUS from v$log;

GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 536870912 1 CURRENT
2 536870912 1 INACTIVE
3 536870912 1 INACTIVE
4 1048576000 1 UNUSED

- 手動切換日誌,把想要刪除的GROUP 切換到INACTIVE 狀態,本例為了刪除GROUP 1
SQL> alter system switch logfile;

System altered.
- 繼續察看狀態
SQL> select group#,BYTES,MEMBERS, STATUS from v$log;

GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 536870912 1 ACTIVE
2 536870912 1 INACTIVE
3 536870912 1 INACTIVE
4 1048576000 1 CURRENT

- 繼續切換
SQL> alter system switch logfile;

System altered.

SQL> select group#,BYTES,MEMBERS, STATUS from v$log;

GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 536870912 1 ACTIVE
2 536870912 1 INACTIVE
3 536870912 1 CURRENT
4 1048576000 1 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,BYTES,MEMBERS, STATUS from v$log;

GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 536870912 1 ACTIVE
2 536870912 1 CURRENT
3 536870912 1 ACTIVE
4 1048576000 1 ACTIVE

- 因為是測試,所以,採取切換checkpoint 的方法,把狀態調整好,PRD 系統一定注意使用啊
SQL> alter system checkpoint;

System altered.
- 現在GROUP 1 的狀態是INACTIVE 了,可以DROP 了
SQL> select group#,BYTES,MEMBERS, STATUS from v$log;

GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 536870912 1 INACTIVE
2 536870912 1 CURRENT
3 536870912 1 INACTIVE
4 1048576000 1 INACTIVE

- DROP GROUP 1
SQL> alter database drop logfile group 1;

Database altered.
- 察看狀態
SQL> select group#,BYTES,MEMBERS, STATUS from v$log;

GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
2 536870912 1 CURRENT
3 536870912 1 INACTIVE
4 1048576000 1 INACTIVE

- 其他的組,可以採用同樣的方法,線上操作