ORACLE REDO 日志调整

日期：20130605 10：00

 环境：ORACLE 11.2.0.3 RAC,双节点，

REDHAT 5.8 +DS: DELL 3600I

 

查询过去24小时的日志切换之间的平均，最大，最小次数：平均时间在5分钟以下，可能建议要求增加日志文件的大小：

 

 

 

WITHlog_history AS

       (SELECTthread#, first_time,

               LAG(first_time)OVER (ORDERBYthread#, sequence#)

                  last_first_time,

               (first_time

                - LAG(first_time)OVER (ORDERBYthread#, sequence#))

                    * 24* 60   last_log_time_minutes,

               LAG(thread#)OVER (ORDERBYthread#, sequence#)

                   last_thread#

        FROMv$log_history)

SELECTROUND(MIN(last_log_time_minutes),2) min_minutes,

       ROUND(MAX(last_log_time_minutes),2) max_minutes,

       ROUND(AVG(last_log_time_minutes),2) avg_minutes

FROM log_history

WHERE     last_first_time ISNOTNULL

      ANDlast_thread# = thread#

      ANDfirst_time > SYSDATE - 1;

 

 

 

查询结果：

 

      MIN_MINUTES   MAX_MINUTES   AVG_MINUTES

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

1       0.03                     160.22                           2.03

 

 

 

查询日志

 

SQL> showparameters thread;

NAME                                 TYPE        VALUE

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

parallel_threads_per_cpu             integer     2

thread                               integer     2

 

 

在集群环境中，每个节点上的数据库实例都需要自己的重做日志组。例如，如果集群数据库拥有三个节点和实例，每个实例至少有两个重做日志组，则整个集群至少 需要6个联机重做日志组。一般而言，两个日志组是最低限量，工程上可以给每个实例更多的组。一则提高效率，二则提高恢复性。但无论多少组，所有日志文件存 放于共享存储介质上，并可以被其他实例访问。

 

服务器参数文件中的thread参数定义了节点一(rac1)实例的线程号为1，而节点而节点二（rac2)实例的线程号为2:

Initracdb1.ora

racdb1.instance_number=1

racdb2.instance_number=2

 

 

 

 

 

 

查询当前日志文件组及所属实例：

selectgroup#,THREAD#,l.MEMBERS,BYTES fromv$log l

 

      GROUP#    THREAD#   MEMBERS BYTES

1       1                          1                2       52428800

2       2                          1                2       52428800

3       3                          2                2       52428800

4       4                          2                2       52428800

 

 

 

下面来给线程1的实例添加一个组，组号为5，采用ASM自动存储方式，因此无需指定文件名和大小：
SQL>alter database add logfile thread 1 group 5  size 102400kb;

下面来给线程2的实例添加一个组，组号为6，采用ASM自动存储方式，因此无需指定文件名和大小。
SQL>alter database add logfile thread 2 group 6 size 102400kb;

 

在各个节点进行：切换当前日志到新的日志组
　　alter system switch logfile;
　　alter system switch logfile;

 

 

 

selectgroup#,THREAD#,l.MEMBERS,BYTES,l.STATUS fromv$log l

 

      GROUP#    THREAD#   MEMBERS BYTES        STATUS

1                1                1       2                52428800   ACTIVE

2                2                1       2                52428800   INACTIVE

3                3                2       2                52428800   INACTIVE

4                4                2       2                52428800   ACTIVE

5                5                1       2                104857600 CURRENT

6                6                2       2                104857600 CURRENT

 

 

3、删除旧的日志组

　　alter database drop logfile group 2;
　　alter database drop logfile group 3;

 

下面步骤为添加两个日志组，5，6到进程1，2，并修改其它日志组，把大小从50M 修改成100 M,

先切换日志从active到 inactive,再删除日志组，后添加日志组。

 

 

SQL> alterdatabase add logfile thread 1 group 5 size 100M;

alter database addlogfile thread 1 group 5  size 100M

ORA-00933: SQL 命令未正确结束

SQL> alter database add logfile thread 1 group 5 ;

Database altered

 

SQL> alter database add logfile thread 2 group 6 ;

Database altered

 

SQL> alter system switch logfile;

System altered

 

SQL> alter database drop logfile group 2;

Database altered

 

SQL> alter database add logfile thread 1 group 2 ;

Database altered

 

SQL> alter database drop logfile group 3;

Database altered

 

SQL> alter database add logfile thread 2 group 3 ;

Database altered

 

SQL> alter system switch logfile;

System altered

 

SQL> alter database drop logfile group 1;

Database altered

 

SQL>  alterdatabase add logfile thread 1 group 1 ;

Database altered

 

SQL> alter database drop logfile group 4;

Database altered

 

SQL> alter database add logfile thread 2 group 4;

Database altered

 

SQL>  altersystem switch logfile;

System altered

 

 

以上添加的日志组，ORACLE RAC 会按照文件保存规则，自动生成两个文件：

       GROUP#     STATUS      TYPE MEMBER    IS_RECOVERY_DEST_FILE

1       5                 ONLINE      +DATA/racdb/onlinelog/group_5.322.817383997      NO

2       5                 ONLINE      +RECOVERY/racdb/onlinelog/group_5.374.817383999       YES

 

 

======================================================================

完成后：

     GROUP# THREAD#         MEMBERS       BYTES                STATUS

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

1                1                1                2                          104857600       CURRENT

2                2                1                2                          104857600       ACTIVE

3                3                2                2                          104857600       ACTIVE

4                4                2                2                          104857600       CURRENT

5                5                1                2                          104857600       ACTIVE

6                6                2                2                          104857600       ACTIVE

 

 

备份一下控制文件：

 

RMAN>  backup current controlfile

2> ;

 

Starting backup at2013-06-06 11:29:26

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: SID=7254 instance=racdb1 device type=DISK

channelORA_DISK_1: starting full datafile backup set

channelORA_DISK_1: specifying datafile(s) in backup set

including currentcontrol file in backup set

channelORA_DISK_1: starting piece 1 at 2013-06-06 11:29:31

channelORA_DISK_1: finished piece 1 at 2013-06-06 11:29:38

piecehandle=+RECOVERY/racdb/backupset/2013_06_06/ncnnf0_tag20130606t112928_0.387.817385371tag=TAG20130606T112928 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at2013-06-06 11:29:38

 

Starting ControlFile Autobackup at 2013-06-06 11:29:38

piecehandle=+RECOVERY/racdb/autobackup/2013_06_06/n_817385379.390.817385381comment=NONE

Finished ControlFile Autobackup at 2013-06-06 11:29:46

 

RMAN> listbackup of controlfile;

 

 

后面跟踪一天后的情况。

 

 

 

 

 

 

参考URL:

http://netclassroom.blog.163.com/blog/static/13575134420118234357827/