Redo log 的添加与删除

1 查看redo 信息

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

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

         3 ONLINE  /u01/app/oracle/oradata/xezf/redo03.log

         2 ONLINE  /u01/app/oracle/oradata/xezf/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/xezf/redo01.log

         4 STANDBY /u01/app/oracle/oradata/xezf/redo04.log

         5 STANDBY /u01/app/oracle/oradata/xezf/redo05.log

         6 STANDBY /u01/app/oracle/oradata/xezf/redo06.log

         7 STANDBY /u01/app/oracle/oradata/xezf/redo07.log

7 rows selected.

 

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;  

    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024

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

         1          1 YES INACTIVE                      50

         2          1 NO  CURRENT                       50

         3          1 YES INACTIVE                      50

 

 

2 修改standby redo

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

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

         3 ONLINE  /u01/app/oracle/oradata/xezf/redo03.log

         2 ONLINE  /u01/app/oracle/oradata/xezf/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/xezf/redo01.log

 

添加standby redo

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/xezf/std_redo04.log') size 100M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/xezf/std_redo05.log') size 100M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/xezf/std_redo06.log') size 100M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/xezf/std_redo07.log') size 100M;

Database altered.

 

SQL> select group#,type, member from v$logfile;

 

    GROUP# TYPE    MEMBER

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

         3 ONLINE  /u01/app/oracle/oradata/xezf/redo03.log

         2 ONLINE  /u01/app/oracle/oradata/xezf/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/xezf/redo01.log

         4 STANDBY /u01/app/oracle/oradata/xezf/std_redo04.log

         5 STANDBY /u01/app/oracle/oradata/xezf/std_redo05.log

         6 STANDBY /u01/app/oracle/oradata/xezf/std_redo06.log

         7 STANDBY /u01/app/oracle/oradata/xezf/std_redo07.log

 

7 rows selected.

 

3 修改Online redo

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;  

 

    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024

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

         1          1 YES INACTIVE                      50

         2          1 NO  CURRENT                       50

         3          1 YES INACTIVE                      50

 

先处理inactive， 它表示已经完成规定的，可以删除。

 

SQL>  alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

*

ERROR at line 1:

ORA-01567: dropping log 3 would leave less than 2 log files for instance xezf

(thread 1)

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/xezf/redo03.log'

-- 至少要2个redo组，看来还是只能慢慢来了。

 

SQL> alter database add logfile  group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;    

alter database add logfile  group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M

*

ERROR at line 1:

ORA-00301: error in adding log file '/u01/app/oracle/oradata/xezf/redo01.log' -

file cannot be created

ORA-27038: created file already exists

Additional information: 1

 

-- 物理文件没有删除，手工的把物理文件删除后，在创建：

SQL> alter database add logfile  group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;

Database altered.

 

SQL>  select group#,thread#,archived,status, bytes/1024/1024 from v$log;  

 

    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024

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

         1          1 YES UNUSED                       100

         2          1 NO  CURRENT                       50

         3          1 YES INACTIVE                      50

 

group1 搞定了。

 

SQL> alter database drop logfile group 3;

Database altered.

 

删除对应的物理文件，在添加

SQL> alter database add logfile  group 3 ('/u01/app/oracle/oradata/xezf/redo03.log') size 100M;

 

Database altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024

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

         1          1 YES UNUSED                       100

         2          1 NO  CURRENT                       50

         3          1 YES UNUSED                       100

 

group3 搞定。

 

切换一下logfile，在删除group2

 

SQL> alter system switch logfile;

System altered.

SQL>  select group#,thread#,archived,status, bytes/1024/1024 from v$log;

 

    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024

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

         1          1 NO  CURRENT                      100

         2          1 YES ACTIVE                        50

       -- group 正在归档，我们等会在看一下

         3          1 YES UNUSED                       100

 

几分钟之后：

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

 

    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024

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

         1          1 NO  CURRENT                      100

         2          1 YES INACTIVE                      50

         3          1 YES UNUSED                       100

 

SQL>  alter database drop logfile group 2;

Database altered.

删除物理文件，在创建

SQL> alter database add logfile  group 2 ('/u01/app/oracle/oradata/xezf/redo02.log') size 100M;

Database altered.

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

 

    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024

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

         1          1 NO  CURRENT                      100

         2          1 YES UNUSED                       100

         3          1 YES UNUSED                       100

 

搞定。