关于联机重做日志文件

Redo Online Log Files:
The log sequence number is assigned each time that the Oracle server writes to a log group to uniquely identify each online redo log file. The current log sequence number is stored in the control file and in the header of all data files.
(1.Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it.
2.Each online or archived redo log file is uniquely identified by its  log sequence number, in ascending order)

MAXLOGFILES parameter: absolute maximum of online redo log file groups
MAXLOGMEMBERS parameter: maximum number of members per group.

Online redo log files are used in a cyclic fashion. Each online redo log file group is identified by a log sequence number that is overwritten each time the log is reused.
CKPT updates the control file to reflect that it has completed a checkpoint successfully. If the checkpoint is caused by a log switch, CKPT also updates the headers of the data files.

A checkpoint occurs, for example, in the following situations:
* At every log switch
* When an instance has been shut down with the normal, transactional, or immediate option
* When forced by setting the FAST_START_MTTR_TARGET initialization parameter
* When manually requested by the database administrator
* When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN BACKUP]command causes checkpointing on specific data files

 

*Forcing Log Switches and Checkpoints
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
SQL> select group#, thread#, sequence#, status from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1         11 CURRENT
         2          1          9 INACTIVE
         3          1         10 INACTIVE

SQL> alter system switch logfile;        #Forcing a log switch
System altered.

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

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1         11 ACTIVE
         2          1         12 CURRENT
         3          1         10 INACTIVE
--Redo threads: the redo log for each DB instance(mutiple DB instances)
www.dba-oracle.com/grid_183.htm

* Checkpoints can be forced by:

– Setting FAST_START_MTTR_TARGET parameter
        FAST_START_MTTR_TARGET = 600
– ALTER SYSTEM CHECKPOINT command
        ALTER SYSTEM CHECKPOINT;

SQL> show parameter fast                                                                        #建议使用通配符查找参数

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
fast_start_io_target                 integer     0
fast_start_mttr_target             integer     0                                                #我的居然是0？
fast_start_parallel_rollback      string      LOW

SQL> alter system set fast_start_mttr_target = 300 scope=both;

System altered.

SQL> show parameter fast

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
fast_start_io_target                 integer     0
fast_start_mttr_target             integer     300                                            #每五分钟写一次吧
fast_start_parallel_rollback      string      LOW

SQL> desc v$logfile;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 GROUP#                                             NUMBER
 STATUS                                              VARCHAR2(7)
 TYPE                                                  VARCHAR2(7)
 MEMBER                                            VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                VARCHAR2(3)                                    #10g才有

SQL> col member format a30
SQL> select * from v$logfile;

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

 

*Add log file group

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

 

Database altered.                                                                                     #去oradata可以看到redo的大小。

*Adding Online Redo Log File Members
SQL> alter database add logfile member
  2  '/u01/oradata/wade/redo04a.log' to group 4;
SQL> select * from v$logfile;

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

5 rows selected.

#A redo log file becomes INVALID if the database cannot access it.
#INVALID <> STALE
If the file (log member) already exists, it must have the same size, and you must specify the REUSE option. You can identify the target group either by specifying one or more members of the group or by specifying the group number.

 

*Dropping Online Redo Log File Groups
##ALTER DATABASE DROP LOGFILE GROUP 3;                                       --相对于add而言
NOTE：drop a member of an active group, force a log switch to occur.
SQL> select group#, members, archived, status from v$log;

    GROUP#    MEMBERS ARC STATUS
---------- ---------- --- ----------------
         1          1 NO  CURRENT
         2          1 NO  INACTIVE
         3          1 NO  INACTIVE
         4          1 NO  INACTIVE

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance wade (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/oradata/wade/redo01.log'

#alter system switch logfile;几次后，将group1切换到inactive状态，然后删掉即可，同时用OS命令将操作系统文件删掉!

 

*Dropping Online Redo Log File Members
      redo log member被删掉后，相应的操作系统文件并没有被重磁盘上删除。control files被更新，把该member从数据库结构中删掉。所以要用相关的操作系统命令，将redo log member彻底干掉！
SQL> alter database drop logfile member '/u01/oradata/wade/redo05a.log';

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

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

--去oradata/SID/用ll命令查看，发现redo04.log还在，故rm -f redo04a.log
--v$log里面可以看归档，而v$logfile不行；v$logfile里用member看地址，v$log里用members看成员个数。
--当我要去删掉第四组里面的唯一一个member的时候
SQL> alter database drop logfile member '/u01/oradata/wade/redo04.log';
alter database drop logfile member '/u01/oradata/wade/redo04.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /u01/oradata/wade/redo04.log for group 4

*Relocating or Renaming Online Redo Log Files
Tips: members on different disk, eliminating contention between LGWR&ABCn bkp.
      Datafiles should be placed on different disks from redo log files.
(The Oracle server changes only the pointers in the control files, but does not physically rename or create any operating system files.)
solution1: ALTER DATABASE RENAME FILE command
ALTER DATABASE RENAME FILE '$HOME/ORADATA/u01/log2a.rdo'
TO '$HOME/ORADATA/u02/log1c.rdo';
requirements or procedures:  
– Shut down the database.
– Copy the online redo log files to the new location.
– Place the database in MOUNT mode.
– Execute the command.
– Open database for normal operation.
[oracle@Oracle9iDemo wade]# cp redo03.log ..
[oracle@Oracle9iDemo wade]# cd ..
[oracle@Oracle9iDemo oradata]# ll
total 51268
drwxr-xr-x  3 oracle oinstall     4096 Dec  4 15:06 gwan
-rw-r-----  1 oracle oinstall     52429312 Dec  7 04:36 redo03.log
drwxr-x---  2 oracle oinstall     4096 Dec  6 15:19 wade
[oracle@Oracle9iDemo oradata]# mv redo03.log redo03.rdo
[oracle@Oracle9iDemo oradata]# ll
total 51268
drwxr-xr-x  3 oracle oinstall     4096 Dec  4 15:06 gwan
-rw-r-----  1 root   root     52429312 Dec  7 04:36 redo03.rdo
SQL> alter database rename file '/u01/oradata/wade/redo03.log'
  2  to '/u01/oradata/redo03.rdo';
Database altered.
SQL> col member format a40  
SQL> select * from v$logfile;

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

 

solution2:Add new members and drop old members.                    ***strongly recommended

 

*Archived Redo Log Files

#打开归档模式的命令：alter database archivelog; 要在mount下
#关闭归档模式的命令：alter database noarchivelog;  
#archive log list;检查是否成功 

noarchivelog Mode: redo log files overwritten when filled and log switches ocurr. 直到改组的checkpoint完成，LGWR才开始重写redo log file
archivelog Mode: inactive groups of filled redo log files must be archived.
There are two ways in which online redo log files can be archived:
* Manually
* Automatically (recommended method)
* When successfully archived:
– An entry in the control file is made
– Records: archive log name, log sequence number, and high and low system change number (SCN)
* Filled online redo log files cannot be reused until:
– A checkpoint has taken place
– File has been archived by ARCn
Information about archived logs can be obtained from V$INSTANCE.
SQL> select archiver from v$instance;

ARCHIVE
-------
STARTED

 

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG