帮朋友做的一个增加LOGFILE GROUP 和切换日志的纪录

最新推荐文章于 2024-06-25 09:51:30 发布

clare0807

最新推荐文章于 2024-06-25 09:51:30 发布

阅读量201

点赞数

文章标签：数据库

帮一个朋友临时试验了一个纪录出来。。

请参考吧。。

[@more@]

- 以DBA 身份进入数据库
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jan 4 16:32:57 2008

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

- 其他的组,可以采用同样的方法,在线操作

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7318139/viewspace-996455/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7318139/viewspace-996455/