Maintaining Online Redo Log Files

log file status解读:

CURRENT->表明当前oracle正在往里面写redo log data

INACTIVE->表明当前的日志文件组已经可以被覆盖了、或者可以被archived.表明其已经发生检查点.

FAST_START_MTTR_TARGET参数指定了oracle在规定的参数值内,必须进行一次DBWn操作.

SQL> show parameter FAST_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW
SQL> alter system set FAST_START_MTTR_TARGET=300 scope=both;

System altered.

SQL> show parameter FAST_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     300
fast_start_parallel_rollback         string      LOW
SQL> 
View Code
SQL> alter system switch logfile;

System altered.
手工触发log switch
SQL> alter system checkpoint;

System altered.
手工触发checkpoint

新增日志组、新增日志组成员

SQL> set line 200
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
SQL> l
  1* alter database add logfile group 4 ('/u01/app/oracle/oradata/testdb/redo04.log') size 100M
SQL> /

Database altered.
SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO
         4         ONLINE  /u01/app/oracle/oradata/testdb/redo04.log          NO

SQL> 
新增日志组
SQL> alter database add logfile member '/u01/app/oracle/oradata/testdb/redo04a.rdo' to group 4;

Database altered.

SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO
         4 INVALID ONLINE  /u01/app/oracle/oradata/testdb/redo04a.rdo         NO
         4         ONLINE  /u01/app/oracle/oradata/testdb/redo04.log          NO

SQL> 
新增日志组成员

注意:往一个组里添加成员,不需要指定大小;默认按照其他现有成员大小创建.

删除日志组

SQL> select group#,thread#,sequence#,status from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1         55 INACTIVE
         2          1         56 INACTIVE
         3          1         57 ACTIVE
         4          1         58 CURRENT
SQL> alter database drop logfile member '/u01/app/oracle/oradata/testdb/redo04a.rdo';
alter database drop logfile member '/u01/app/oracle/oradata/testdb/redo04a.rdo'
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/testdb/redo04.log'
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/testdb/redo04a.rdo'
当前日志状态:第4组成员处于current状态,不能执行删除操作;否则会报错;
SQL> alter system switch logfile;

System altered.

SQL> select group#,thread#,sequence#,status from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1         59 CURRENT
         2          1         56 INACTIVE
         3          1         57 ACTIVE
         4          1         58 ACTIVE

SQL> alter database drop logfile member '/u01/app/oracle/oradata/testdb/redo04a.rdo';

Database altered.

SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO
         4         ONLINE  /u01/app/oracle/oradata/testdb/redo04.log          NO

SQL> 
进行一次logfile switch之后,当前活动状态组不是4号,即可删除4号组成员;
11203ora-> rm -rf redo04a.rdo
最后执行删除物理成员;
SQL> alter database drop logfile member '/u01/app/oracle/oradata/testdb/redo04.log';
alter database drop logfile member '/u01/app/oracle/oradata/testdb/redo04.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /u01/app/oracle/oradata/testdb/redo04.log for group 4


SQL> 
假如组里只包含一个成员,此时执行删除组成员操作你是失败的.如果想删除,只能删除组.
  • An instance requires at least two groups of online redo log files;
  • An active or current group cannot be dropped.
  • When an online redo log file group is dropped,the operating system files are not deleted.
SQL> alter database drop logfile group 4;

Database altered.

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

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1         59 NO  CURRENT
         2          1         56 YES INACTIVE
         3          1         57 YES INACTIVE

SQL> 
执行日志组逻辑删除操作.

Relocate & Rename

You can use operating system commands to relocate redo logs,then use the ALTER DATABASE statement to make their new names(locations) known to the database.This procedure is necessary,for example,if the disk currently used for some redo log files is going to be removed,or if datafiles and a number of redo log files are stored on the same disk and should  be separated to reduce contention.

To rename redo log members ,you must have the ALTER DATABASE system privilege.Additionally,you might also need operating system privileges to copy files to the desired location and privileges to open and backup the database.

Before relocating your redo logs,or making any other structural changes to the database,completely back up the database in case you experience problems while performing the operation.As a precaution,after renaming or relocating a set of redo log files,immedidately backup the database control file.

 

方法一:alter database rename file command

  0.执行rename之前,数据库文件信息

SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/testdb/redo04.log') size 50M;

Database altered.

SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO
         4         ONLINE  /u01/app/oracle/oradata/testdb/redo04.log          NO
View Code

1、shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
View Code

  2、copy the online redo log files to the new location

11203ora-> cp redo04.log  ..
11203ora-> ll
total 2520580
-rw-r----- 1 oracle oinstall   9814016 Jul 15 11:50 control01.ctl
-rw-r----- 1 oracle oinstall 209723392 Jul 15 11:50 DATACENTER01.dbf
-rw-r----- 1 oracle oinstall 362422272 Jul 15 11:50 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 15 11:50 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 15 10:31 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 15 11:06 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Jul 15 11:50 redo04.log
-rw-r----- 1 oracle oinstall 209723392 Jul 15 11:50 rman_ts01.dbf
-rw-r----- 1 oracle oinstall 650125312 Jul 15 11:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 765468672 Jul 15 11:50 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Jul 15 10:14 temp01.dbf
-rw-r----- 1 oracle oinstall 120594432 Jul 15 11:50 undotbs01.dbf
-rw-r----- 1 oracle oinstall  15736832 Jul 15 11:50 users01.dbf
11203ora-> cd ..
11203ora-> ll
total 51208
-rw-r----- 1 oracle oinstall 52429312 Jul 15 11:51 redo04.log
drwxr-x--- 2 oracle oinstall     4096 Jul 15 11:50 testdb
View Code

  3、place the database in mount mode

SQL> startup mont;
SP2-0714: invalid combination of STARTUP options
SQL> startup mount;
ORACLE instance started.

Total System Global Area  430075904 bytes
Fixed Size                  2229064 bytes
Variable Size             327158968 bytes
Database Buffers           92274688 bytes
Redo Buffers                8413184 bytes
Database mounted.
View Code

  4、execute the command

SQL> alter database rename file '/u01/app/oracle/oradata/testdb/redo04.log' to '/u01/app/oracle/oradata/redo04.log';

Database altered.
View Code

  5、open database for normal operation

SQL> alter database open;

Database altered.
View Code

6、查看新的日志组信息

SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO
         4         ONLINE  /u01/app/oracle/oradata/redo04.log                 NO
View Code

  alter database rename file '$ORACLE_HOME/ORADATA/testdb/log2a.rdo' to '$ORACLE_HOME/ORADATA/log1c.rdo';

方法二:add new members and drop old members.

查询日志组

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

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1         59 NO  CURRENT
         2          1         56 YES INACTIVE
         3          1         57 YES INACTIVE

SQL> 
v$log
SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log          NO
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log          NO
         3         ONLINE  /u01/app/oracle/oradata/testdb/redo03.log          NO

SQL> 
v$logfile

What is Redo Threads?

When speaking in the context of multiple database instance,the redo log for each database  instance is also referred  to as a redo thread.In typical configurations,only one database instance accesses an Oracle Database,so only one thread is present.In an Oracle Real Application Clusters environment,however,two or more instance concurrently access a single database and each instance has its own thread of redo.A separate redo thread for each instance avoids contention for a single set of redo log files,thereby eliminating a potential performance bottleneck.

Redo File 配置

 

How Redo Files Work   

Online Redo log files are used in a cyclic fashion   

When a online redo log file is full,LGWR will move to the next log group.      

  • - Called a log switch(The act of switching from one log file group to the other is called a log switch)      
  • - Checkpoint operation also occurs      
  • - Information written to the control file

A checkpoint is the writing of dirty(modified) blocks from the buffer cache to disk.

log switch 一定会引发  checkpoint ,但相反不成立。

When will LGWR write redo?    

  • When a transaction commits    
  • Every three seconds    
  • When the Redo Log Buffer becomes one-third full    
  • When there is more than a megabyte of changed records in the Redo Log Buffer.    
  • Before the DBWn writes modified blocks in the Database Buffer Cache to the data files.

ASM管理存储---新增日志组

alter database add logfile group 4 ('+DATA_DG_A/orcl/onlinelog/group_4_01.log','+DATA_DG_B/orcl/onlinelog/group_4_02.log') size 512M;
alter database add logfile group 5 ('+DATA_DG_A/orcl/onlinelog/group_5_01.log','+DATA_DG_B/orcl/onlinelog/group_5_02.log') size 512M;
alter database add logfile group 6 ('+DATA_DG_A/orcl/onlinelog/group_6_01.log','+DATA_DG_B/orcl/onlinelog/group_6_02.log') size 512M;
View Code
SQL> select * from v$logfile order by 1,4;

    GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
         1                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_1.261.852573709              NO
         1                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_1_02.log                     NO
         2                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_2.262.852573745              NO
         2                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_2_02.log                     NO
         3                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_3.263.852573779              NO
         3                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_3_02.log                     NO

6 rows selected.
新增日志之前
SQL> alter database add logfile group 4 ('+DATA_DG_A/orcl/onlinelog/group_4_01.log','+DATA_DG_B/orcl/onlinelog/group_4_02.log') size 512M;

Database altered.

SQL> select * from v$logfile order by 1,4;

    GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
         1                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_1.261.852573709              NO
         1                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_1_02.log                     NO
         2                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_2.262.852573745              NO
         2                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_2_02.log                     NO
         3                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_3.263.852573779              NO
         3                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_3_02.log                     NO
         4                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_4_01.log                     NO
         4                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_4_02.log                     NO

8 rows selected.
新增日志组4
SQL> alter database add logfile group 5 ('+DATA_DG_A/orcl/onlinelog/group_5_01.log','+DATA_DG_B/orcl/onlinelog/group_5_02.log') size 512M;

Database altered.

SQL> select * from v$logfile order by 1,4;

    GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
         1                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_1.261.852573709              NO
         1                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_1_02.log                     NO
         2                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_2.262.852573745              NO
         2                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_2_02.log                     NO
         3                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_3.263.852573779              NO
         3                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_3_02.log                     NO
         4                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_4_01.log                     NO
         4                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_4_02.log                     NO
         5                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_5_01.log                     NO
         5                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_5_02.log                     NO

10 rows selected.
新增日志组5
SQL> alter database add logfile group 6 ('+DATA_DG_A/orcl/onlinelog/group_6_01.log','+DATA_DG_B/orcl/onlinelog/group_6_02.log') size 512M;

Database altered.

SQL> select * from v$logfile order by 1,4;

    GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
         1                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_1.261.852573709              NO
         1                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_1_02.log                     NO
         2                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_2.262.852573745              NO
         2                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_2_02.log                     NO
         3                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_3.263.852573779              NO
         3                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_3_02.log                     NO
         4                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_4_01.log                     NO
         4                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_4_02.log                     NO
         5                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_5_01.log                     NO
         5                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_5_02.log                     NO
         6                       ONLINE                +DATA_DG_A/orcl/onlinelog/group_6_01.log                     NO

    GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
         6                       ONLINE                +DATA_DG_B/orcl/onlinelog/group_6_02.log                     NO

12 rows selected.
新增日志组6

 

 

 

 

 

 

 

 

posted @ 2013-07-15 10:51  ArcerZhang  阅读(319)  评论(0编辑  收藏  举报