redo文件管理 - 数据库编程

1. 查看数据库版本

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE	11.2.0.1.0	Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

2. 查看重做日志文件

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

3 ONLINE /home/oracle/app/oracle/oradata/orcl/redo03.log NO

2 ONLINE /home/oracle/app/oracle/oradata/orcl/redo02.log NO

1 ONLINE /home/oracle/app/oracle/oradata/orcl/redo01.log NO

3. 查看重做日志组信息

SQL> select group#,bytes,members from v$log;

    GROUP#	BYTES	 MEMBERS

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

	 1   52428800	       1

	 2   52428800	       1

	 3   52428800	       1

4. 查看重做日志和重做日志组状态

SQL> select a.group#,a.member,b.status,b.bytes

from v$logfile a,v$log b

where a.group#=b.group#;

GROUP# MEMBER STATUS BYTES

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

3 /home/oracle/app/oracle/oradata/orcl/redo03.log CURRENT 52428800

2 /home/oracle/app/oracle/oradata/orcl/redo02.log INACTIVE 52428800

1 /home/oracle/app/oracle/oradata/orcl/redo01.log INACTIVE 52428800

4. 添加重做日志组

每个重做日志组可以添加一个或者多个重做日志文件

语法：

alter database add logfile group 组序号('文件名1','文件名2',......'文件n'） size 文件大小;

SQL> alter database add logfile group 4('/home/oracle/app/oracle/oradata/orcl/redo04_01.log',

'/home/oracle/app/oracle/oradata/orcl/redo04_02.log') size 100M;

SQL> select a.group#,a.member,b.bytes/1024/1024 "M",b.status

from v$logfile a,v$log b

where a.group#=b.group#;

GROUP# MEMBER M STATUS

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

3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 CURRENT

2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 INACTIVE

1 /home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE

4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log 100 UNUSED

4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 UNUSED

绿色标记：刚创建的重做日志文件名称，大小，状态。其中文件名为redo04_01.log和red04_02.log，文件大小100M，

状态unused（因为刚创建，重做日志文件从未被使用）

知识扩展：redo四种状态

current：当前活动重做日志，进行实例恢复是必须的！

active：活动的非当前重做日志，实例恢复时会被用到。此状态意味checkpoint尚未完成.

inactive：非活动日志，在实例恢复时不需要，但介质恢复时需要。

unused：日志从未被写入，可能刚被添加或者resetlogs后被重置

5.添加重做日志文件

添加重置日志文件，不需要指定文件大小，新创建的重做日志文件大小与已经存在的重做日志大小一致

语法：

alter database add logfile member '文件名' to group 组序号

SQL> alter database add logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_04.log' to group 4;

SQL> select a.group#,a.member,b.bytes/1024/1024,b.status

    from v$logfile a,v$log b

       where a.group#=b.group#;

    GROUP# MEMBER					      B.BYTES/1024/1024 STATUS

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

	 3 /home/oracle/app/oracle/oradata/orcl/redo03.log		     50 CURRENT

	 2 /home/oracle/app/oracle/oradata/orcl/redo02.log		     50 INACTIVE

	 1 /home/oracle/app/oracle/oradata/orcl/redo01.log		     50 INACTIVE

	 4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log		    100 UNUSED

	 4 /home/

redo文件管理(一)