以下是具体的一些操作代码：

SQL> conn / as sysdba;
已连接。

//获取数据库有多少重做日志组等等信息

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

    GROUP# SEQUENCE#    MEMBERS      BYTES STATUS           ARC
---------- ---------- ---------- ---------- ---------------- ---
         1         74          1   52428800 INACTIVE         NO
         2         75          1   52428800 CURRENT          NO
         3         73          1   52428800 INACTIVE         NO

//添加重做日志文件组

SQL> alter database add logfile
2 ('F:\disk1\redo04a.log','F:\disk2\redo04b.log')
3 size 15M;

数据库已更改。

//验证新的重做日志组

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

    GROUP# SEQUENCE#    MEMBERS      BYTES STATUS           ARC
---------- ---------- ---------- ---------- ---------------- ---
         1         74          1   52428800 INACTIVE         NO
         2         75          1   52428800 INACTIVE         NO
         3         76          1   52428800 CURRENT       NO
         4          0          2   15728640 UNUSED           YES

//设置格式，以便于现示清晰

SQL> col member for a50;
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
3 ONLINE D:\ORACLE1\PRODUCT\10.2.0\ORADATA\ORDB\REDO03.LOG
NO

2 ONLINE D:\ORACLE1\PRODUCT\10.2.0\ORADATA\ORDB\REDO02.LOG
NO

1 STALE ONLINE D:\ORACLE1\PRODUCT\10.2.0\ORADATA\ORDB\REDO01.LOG
NO

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
4 ONLINE F:\DISK1\REDO04A.LOG
NO

4 ONLINE F:\DISK2\REDO04B.LOG
NO

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
3 ONLINE D:\ORACLE1\PRODUCT\10.2.0\ORADATA\ORDB\REDO03.LOG
NO

2 ONLINE D:\ORACLE1\PRODUCT\10.2.0\ORADATA\ORDB\REDO02.LOG
NO

1 STALE ONLINE D:\ORACLE1\PRODUCT\10.2.0\ORADATA\ORDB\REDO01.LOG
NO

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
IS_
---
4 ONLINE F:\DISK1\REDO04A.LOG
NO

4 ONLINE F:\DISK2\REDO04B.LOG
NO

//删除不需要的重组日志文件,并且验证是否删除成功

alter database drop logfile group 4;
select * from v$logfile;

//在每个重做日志组中添加一个成员

SQL> alter database add logfile member
2 'F:\disk1\redo01b.log' to group 1,
3 'F:\disk1\redo02b.log' to group 2,
4 'F:\disk1\redo03b.log' to group 3;
数据库已更改

//验证是否添加成功

select group#,sequence#,members,bytes,status,archived from v$log;

//获得每个新创建的重做日志文件名，利用数据字典V$logfile

col member for a50;
set line 100;
select * from v$logfile;

//如何删除不需要的重做日志成员

alter database drop Logfile member
2 'F:\disk1\redo01b.log';

//清楚联机重做日志文件

alter database clear logfile group x;

以上代码是自己亲手做的，保证绝对的正确！

ITPub博客

oracle数据库之----重做日志相关操作

最新文章