ITPub博客

首页 > Linux操作系统 > Linux操作系统 > how to resize and multiplex redo group members under ASM/RAC

how to resize and multiplex redo group members under ASM/RAC

原创 Linux操作系统 作者:xuzhendong 时间:2012-06-14 13:43:32 0 删除 编辑
##
## Objective
##

   In this example I have two ASM disk groups for redo logs:
   
      REDOA
      REDOB
   
   There are FOUR instances, each instance will have TWO redo groups 
   with TWO members each, one on REDOA the other on REDOB.
   
   Originally this database was created with a single REDO member in 
   REDOA disk group sized 50mb -- my goal here is to:
   
      a) "resize" it to 256mb
      b) multiplex to REDOB
   

##
## Procedure
##

sqlplus /nolog
connect / as sysdba

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

    GROUP# STATUS  TYPE    MEMBER                              IS_
---------- ------- ------- ----------------------------------- ---
         1         ONLINE  +REDOA/dbname/redo01.log             NO
         2 STALE   ONLINE  +REDOA/dbname/redo02.log             NO
         7         ONLINE  +REDOA/dbname/redo07.log             NO
         8 STALE   ONLINE  +REDOA/dbname/redo08.log             NO
         5 STALE   ONLINE  +REDOA/dbname/redo05.log             NO
         6         ONLINE  +REDOA/dbname/redo06.log             NO
         3         ONLINE  +REDOA/dbname/redo03.log             NO
         4         ONLINE  +REDOA/dbname/redo04.log             NO

8 rows selected.

SQL> set lines 132
SQL> set trims on
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         53   52428800          1 NO  CURRENT                2274385 08-OCT-08
         2          1         52   52428800          1 NO  INACTIVE               2114717 07-OCT-08
         3          2         27   52428800          1 NO  CURRENT                2206475 08-OCT-08
         4          2         26   52428800          1 NO  INACTIVE               2030730 07-OCT-08
         5          3         17   52428800          1 NO  INACTIVE               1780062 06-OCT-08
         6          3         18   52428800          1 NO  CURRENT                2051445 07-OCT-08
         7          4         21   52428800          1 NO  CURRENT                2072021 07-OCT-08
         8          4         20   52428800          1 NO  INACTIVE               1932027 07-OCT-08

8 rows selected.


-- first create temporary redo-groups
-- so that we can delete GROUP 1-8 and recreate them
-- with a bigger 256m size ...
--
-- NOTE:
--   I didn't really have to create these temp groups with 
--   2 members each ... one member each would suffice
--

ALTER DATABASE ADD LOGFILE THREAD 1
 GROUP 10 ('+REDOA/DBNAME/redo01-tmp.log','+REDOB/DBNAME/redo01-tmp.log') SIZE 10m,
 GROUP 20 ('+REDOA/DBNAME/redo02-tmp.log','+REDOB/DBNAME/redo02-tmp.log') SIZE 10m;

ALTER DATABASE ADD LOGFILE THREAD 2
 GROUP 30 ('+REDOA/DBNAME/redo03-tmp.log','+REDOB/DBNAME/redo03-tmp.log') SIZE 10m,
 GROUP 40 ('+REDOA/DBNAME/redo04-tmp.log','+REDOB/DBNAME/redo04-tmp.log') SIZE 10m;

ALTER DATABASE ADD LOGFILE THREAD 3
 GROUP 50 ('+REDOA/DBNAME/redo05-tmp.log','+REDOB/DBNAME/redo05-tmp.log') SIZE 10m,
 GROUP 60 ('+REDOA/DBNAME/redo06-tmp.log','+REDOB/DBNAME/redo06-tmp.log') SIZE 10m;

ALTER DATABASE ADD LOGFILE THREAD 4
 GROUP 70 ('+REDOA/DBNAME/redo07-tmp.log','+REDOB/DBNAME/redo07-tmp.log') SIZE 10m,
 GROUP 80 ('+REDOA/DBNAME/redo08-tmp.log','+REDOB/DBNAME/redo08-tmp.log') SIZE 10m;



SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         53   52428800          1 NO  CURRENT                2274385 08-OCT-08
         2          1         52   52428800          1 NO  INACTIVE               2114717 07-OCT-08
         3          2         27   52428800          1 NO  CURRENT                2206475 08-OCT-08
         4          2         26   52428800          1 NO  INACTIVE               2030730 07-OCT-08
         5          3         17   52428800          1 NO  INACTIVE               1780062 06-OCT-08
         6          3         18   52428800          1 NO  CURRENT                2051445 07-OCT-08
         7          4         21   52428800          1 NO  CURRENT                2072021 07-OCT-08
         8          4         20   52428800          1 NO  INACTIVE               1932027 07-OCT-08
        10          1          0   10485760          2 YES UNUSED                       0
        20          1          0   10485760          2 YES UNUSED                       0
        30          2          0   10485760          2 YES UNUSED                       0

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
        40          2          0   10485760          2 YES UNUSED                       0
        50          3          0   10485760          2 YES UNUSED                       0
        60          3          0   10485760          2 YES UNUSED                       0
        70          4          0   10485760          2 YES UNUSED                       0
        80          4          0   10485760          2 YES UNUSED                       0

16 rows selected.

SQL>


-- now run "alter system switch logfile;" on all instances
-- untill all of the GROUP 1-8 become "INACTIVE"
-- like shown below:
--

SQL> r
  1* select * from v$log

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         61   52428800          1 NO  INACTIVE               2288120 08-OCT-08  <-- should be "INACTIVE"
         2          1         60   52428800          1 NO  INACTIVE               2288098 08-OCT-08  <-- should be "INACTIVE"
         3          2         31   52428800          1 NO  INACTIVE               2288234 08-OCT-08  <-- should be "INACTIVE"
         4          2         30   52428800          1 NO  INACTIVE               2288221 08-OCT-08  <-- should be "INACTIVE"
         5          3         21   52428800          1 NO  INACTIVE               2288289 08-OCT-08  <-- should be "INACTIVE"
         6          3         22   52428800          1 NO  INACTIVE               2288294 08-OCT-08  <-- should be "INACTIVE"
         7          4         25   52428800          1 NO  INACTIVE               2288317 08-OCT-08  <-- should be "INACTIVE"
         8          4         24   52428800          1 NO  INACTIVE               2288311 08-OCT-08  <-- should be "INACTIVE"
        10          1         62   10485760          2 NO  CURRENT                2288123 08-OCT-08
        20          1         59   10485760          2 NO  INACTIVE               2288084 08-OCT-08
        30          2         32   10485760          2 NO  CURRENT                2288243 08-OCT-08

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
        40          2         29   10485760          2 NO  INACTIVE               2288217 08-OCT-08
        50          3         23   10485760          2 NO  CURRENT                2288296 08-OCT-08
        60          3         20   10485760          2 NO  INACTIVE               2288287 08-OCT-08
        70          4         26   10485760          2 NO  ACTIVE                 2288319 08-OCT-08
        80          4         27   10485760          2 NO  CURRENT                2288322 08-OCT-08

16 rows selected.



-- now drop GROUP 1-8
--

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
        10          1         62   10485760          2 NO  CURRENT                2288123 08-OCT-08
        20          1         59   10485760          2 NO  INACTIVE               2288084 08-OCT-08
        30          2         32   10485760          2 NO  CURRENT                2288243 08-OCT-08
        40          2         29   10485760          2 NO  INACTIVE               2288217 08-OCT-08
        50          3         23   10485760          2 NO  CURRENT                2288296 08-OCT-08
        60          3         20   10485760          2 NO  INACTIVE               2288287 08-OCT-08
        70          4         26   10485760          2 NO  INACTIVE               2288319 08-OCT-08
        80          4         27   10485760          2 NO  CURRENT                2288322 08-OCT-08

8 rows selected.

SQL>


## remove the actuall files (GROUP 1-8) from the ASM
##
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM2
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo01.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo02.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo07.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo08.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo05.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo06.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo03.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo04.log';
exit

## verify files were removed
##

asmcmd
ASMCMD> cd REDOA
ASMCMD> find . *
+REDOA/DBNAME/
+REDOA/DBNAME/CONTROLFILE/
+REDOA/DBNAME/CONTROLFILE/Current.256.666981619
+REDOA/DBNAME/ONLINELOG/
+REDOA/DBNAME/ONLINELOG/group_10.271.667595443
+REDOA/DBNAME/ONLINELOG/group_20.272.667595443
+REDOA/DBNAME/ONLINELOG/group_30.265.667595411
+REDOA/DBNAME/ONLINELOG/group_40.266.667595411
+REDOA/DBNAME/ONLINELOG/group_50.267.667595411
+REDOA/DBNAME/ONLINELOG/group_60.268.667595411
+REDOA/DBNAME/ONLINELOG/group_70.269.667595413
+REDOA/DBNAME/ONLINELOG/group_80.270.667595413
+REDOA/DBNAME/control01.ctl
+REDOA/DBNAME/redo01-tmp.log
+REDOA/DBNAME/redo02-tmp.log
+REDOA/DBNAME/redo03-tmp.log
+REDOA/DBNAME/redo04-tmp.log
+REDOA/DBNAME/redo05-tmp.log
+REDOA/DBNAME/redo06-tmp.log
+REDOA/DBNAME/redo07-tmp.log
+REDOA/DBNAME/redo08-tmp.log
ASMCMD> exit


-- now re-create it with "dual" members and bigger size
--

ALTER DATABASE ADD LOGFILE THREAD 1
 GROUP 1 ('+REDOA/DBNAME/redo01.log','+REDOB/DBNAME/redo01.log') SIZE 256000K,
 GROUP 2 ('+REDOA/DBNAME/redo02.log','+REDOB/DBNAME/redo02.log') SIZE 256000K;

ALTER DATABASE ADD LOGFILE THREAD 2
 GROUP 3 ('+REDOA/DBNAME/redo03.log','+REDOB/DBNAME/redo03.log') SIZE 256000K,
 GROUP 4 ('+REDOA/DBNAME/redo04.log','+REDOB/DBNAME/redo04.log') SIZE 256000K;

ALTER DATABASE ADD LOGFILE THREAD 3
 GROUP 5 ('+REDOA/DBNAME/redo05.log','+REDOB/DBNAME/redo05.log') SIZE 256000K,
 GROUP 6 ('+REDOA/DBNAME/redo06.log','+REDOB/DBNAME/redo06.log') SIZE 256000K;

ALTER DATABASE ADD LOGFILE THREAD 4
 GROUP 7 ('+REDOA/DBNAME/redo07.log','+REDOB/DBNAME/redo07.log') SIZE 256000K,
 GROUP 8 ('+REDOA/DBNAME/redo08.log','+REDOB/DBNAME/redo08.log') SIZE 256000K;


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0  262144000          2 YES UNUSED                       0
         2          1          0  262144000          2 YES UNUSED                       0
         3          2          0  262144000          2 YES UNUSED                       0
         4          2          0  262144000          2 YES UNUSED                       0
         5          3          0  262144000          2 YES UNUSED                       0
         6          3          0  262144000          2 YES UNUSED                       0
         7          4          0  262144000          2 YES UNUSED                       0
         8          4          0  262144000          2 YES UNUSED                       0
        10          1         62   10485760          2 NO  CURRENT                2288123 08-OCT-08
        20          1         59   10485760          2 NO  INACTIVE               2288084 08-OCT-08
        30          2         32   10485760          2 NO  CURRENT                2288243 08-OCT-08

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
        40          2         29   10485760          2 NO  INACTIVE               2288217 08-OCT-08
        50          3         23   10485760          2 NO  CURRENT                2288296 08-OCT-08
        60          3         20   10485760          2 NO  INACTIVE               2288287 08-OCT-08
        70          4         26   10485760          2 NO  INACTIVE               2288319 08-OCT-08
        80          4         27   10485760          2 NO  CURRENT                2288322 08-OCT-08

16 rows selected.


-- now run "alter system switch logfile;" on all instances
-- untill all of the GROUP 10-80 become "INACTIVE"
-- like shown below:
--

  1* select * from v$log

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         67  262144000          2 NO  CURRENT                2289166 08-OCT-08
         2          1         64  262144000          2 NO  INACTIVE               2289154 08-OCT-08
         3          2         37  262144000          2 NO  INACTIVE               2289194 08-OCT-08
         4          2         38  262144000          2 NO  CURRENT                2289199 08-OCT-08
         5          3         28  262144000          2 NO  INACTIVE               2289258 08-OCT-08
         6          3         29  262144000          2 NO  CURRENT                2289266 08-OCT-08
         7          4         32  262144000          2 NO  CURRENT                2289332 08-OCT-08
         8          4         29  262144000          2 NO  INACTIVE               2289315 08-OCT-08
        10          1         66   10485760          2 NO  INACTIVE               2289163 08-OCT-08
        20          1         65   10485760          2 NO  INACTIVE               2289157 08-OCT-08
        30          2         36   10485760          2 NO  INACTIVE               2289192 08-OCT-08

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
        40          2         35   10485760          2 NO  INACTIVE               2289189 08-OCT-08
        50          3         27   10485760          2 NO  INACTIVE               2289254 08-OCT-08
        60          3         26   10485760          2 NO  INACTIVE               2289243 08-OCT-08
        70          4         30   10485760          2 NO  INACTIVE               2289322 08-OCT-08
        80          4         31   10485760          2 NO  INACTIVE               2289329 08-OCT-08

16 rows selected.



-- now drop GROUP 10-80
--

alter database drop logfile group 10;
alter database drop logfile group 20;
alter database drop logfile group 30;
alter database drop logfile group 40;
alter database drop logfile group 50;
alter database drop logfile group 60;
alter database drop logfile group 70;
alter database drop logfile group 80;


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         67  262144000          2 NO  CURRENT                2289166 08-OCT-08
         2          1         64  262144000          2 NO  INACTIVE               2289154 08-OCT-08
         3          2         37  262144000          2 NO  INACTIVE               2289194 08-OCT-08
         4          2         38  262144000          2 NO  CURRENT                2289199 08-OCT-08
         5          3         28  262144000          2 NO  INACTIVE               2289258 08-OCT-08
         6          3         29  262144000          2 NO  CURRENT                2289266 08-OCT-08
         7          4         32  262144000          2 NO  CURRENT                2289332 08-OCT-08
         8          4         29  262144000          2 NO  INACTIVE               2289315 08-OCT-08

8 rows selected.

SQL>


## and finally remove the actuall files (GROUP 10-80) from the ASM
##
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM2
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo01-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo02-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo03-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo04-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo05-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo06-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo07-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo08-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo01-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo02-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo03-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo04-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo05-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo06-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo07-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo08-tmp.log';
exit

## verify files were removed
##

asmcmd
ASMCMD> cd REDOA
ASMCMD> find . *
+REDOA/DBNAME/
+REDOA/DBNAME/CONTROLFILE/
+REDOA/DBNAME/CONTROLFILE/Current.256.666981619
+REDOA/DBNAME/ONLINELOG/
+REDOA/DBNAME/ONLINELOG/group_1.264.667596567
+REDOA/DBNAME/ONLINELOG/group_2.263.667596569
+REDOA/DBNAME/ONLINELOG/group_3.262.667596571
+REDOA/DBNAME/ONLINELOG/group_4.261.667596573
+REDOA/DBNAME/ONLINELOG/group_5.260.667596575
+REDOA/DBNAME/ONLINELOG/group_6.259.667596577
+REDOA/DBNAME/ONLINELOG/group_7.258.667596579
+REDOA/DBNAME/ONLINELOG/group_8.257.667596581
+REDOA/DBNAME/control01.ctl
+REDOA/DBNAME/redo01.log
+REDOA/DBNAME/redo02.log
+REDOA/DBNAME/redo03.log
+REDOA/DBNAME/redo04.log
+REDOA/DBNAME/redo05.log
+REDOA/DBNAME/redo06.log
+REDOA/DBNAME/redo07.log
+REDOA/DBNAME/redo08.log
ASMCMD> cd +REDOB/
ASMCMD> pwd
+REDOB
ASMCMD> find . *
+REDOB/DBNAME/
+REDOB/DBNAME/CONTROLFILE/
+REDOB/DBNAME/CONTROLFILE/Current.256.666981619
+REDOB/DBNAME/ONLINELOG/
+REDOB/DBNAME/ONLINELOG/group_1.265.667596567
+REDOB/DBNAME/ONLINELOG/group_2.266.667596569
+REDOB/DBNAME/ONLINELOG/group_3.267.667596571
+REDOB/DBNAME/ONLINELOG/group_4.268.667596573
+REDOB/DBNAME/ONLINELOG/group_5.269.667596575
+REDOB/DBNAME/ONLINELOG/group_6.270.667596577
+REDOB/DBNAME/ONLINELOG/group_7.271.667596579
+REDOB/DBNAME/ONLINELOG/group_8.272.667596583
+REDOB/DBNAME/control02.ctl
+REDOB/DBNAME/redo01.log
+REDOB/DBNAME/redo02.log
+REDOB/DBNAME/redo03.log
+REDOB/DBNAME/redo04.log
+REDOB/DBNAME/redo05.log
+REDOB/DBNAME/redo06.log
+REDOB/DBNAME/redo07.log
+REDOB/DBNAME/redo08.log
ASMCMD> exit

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8996530/viewspace-732794/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-06-14

  • 博文量
    3
  • 访问量
    2149