archive log

idle> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
1 1 166 52428800 512 1 NO INACTIVE 3245716 2013/12/16 22:20:33 3269932 2013/12/17 14:31:52
3 1 168 52428800 512 1 NO CURRENT 3303501 2013/12/18 14:29:46 281474976710655

2 1 167 52428800 512 1 NO INACTIVE 3269932 2013/12/17 14:31:52 3303501 2013/12/18 14:29:46

我们看到一共有三组日志，分别是group1，group2，group3，oracel 建议每组日志里有1个以上的成员（member），而我们目前的环境是分别都只有1个成员

下面添加成员：语法1：ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

语法2：ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'    TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

idle> select * from v$logfile;


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


idle> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/ocp/testlog/redo01.log' TO GROUP 1;


Database altered.


idle> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/ocp/testlog/redo02.log' TO GROUP 2;


Database altered.


idle> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/ocp/testlog/redo03.log' TO GROUP 3;


Database altered.

一般同一组的不通成员所在的位置不会在一起，我这边是测试环境就直接放在了testlog下区分下

下面来看下创建后的文件

idle> select * from v$log;


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1        166   52428800        512          2 NO  INACTIVE               3245716 16-DEC-13      3269932 17-DEC-13
         3          1        168   52428800        512          2 NO  CURRENT                3303501 18-DEC-13   2.8147E+14
         2          1        167   52428800        512          2 NO  INACTIVE               3269932 17-DEC-13      3303501 18-DEC-13


idle> select * from v$logfile;


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

查看数据库是否开启archive

idle> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbs/arch
Oldest online log sequence     166
Current log sequence           168

idle> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

要开启归档模式，需要先把数据库停止shutdown，然后开始到mount状态，开启归档，最后open数据库。

idle> startup mount
ORACLE instance started.


Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             272630968 bytes
Database Buffers          788529152 bytes
Redo Buffers                5541888 bytes
Database mounted.
idle> alter database archivelog
  2  ;


Database altered.


idle> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/dbs/arch
Oldest online log sequence     166
Next log sequence to archive   168
Current log sequence           168

idle> alter database open;


Database altered.

日志切换，当写满一个文件会自动切换，也可以用命令手动切换

idle> alter system switch logfile;


System altered.

dba_hist_log视图可以查看之前的log是在哪个group及是否归档

idle> select group#,sequence#,bytes,members,ARCHIVED,status from dba_hist_log order by sequence# desc;


    GROUP#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         3        174   52428800          2 NO  CURRENT
         2        173   52428800          2 YES INACTIVE
         1        172   52428800          2 YES INACTIVE
         2        167   52428800          1 NO  CURRENT
         2        167   52428800          1 NO  CURRENT
         2        167   52428800          1 NO  CURRENT
         2        167   52428800          1 NO  CURRENT
         2        167   52428800          1 NO  CURRENT
         1        166   52428800          1 NO  INACTIVE
         1        166   52428800          1 NO  INACTIVE
         1        166   52428800          1 NO  CURRENT
         1        166   52428800          1 NO  INACTIVE
         1        166   52428800          1 NO  INACTIVE
         1        166   52428800          1 NO  INACTIVE
         3        165   52428800          1 NO  CURRENT
         3        165   52428800          1 NO  INACTIVE
         3        165   52428800          1 NO  INACTIVE
         3        165   52428800          1 NO  INACTIVE
         3        165   52428800          1 NO  INACTIVE