ASM : Multiplex redolog files
If you have two diskgroup, you want to multiplex redo in different diskgroup, you just add redo log desitnation
diskgroup.
sql>alter system set db_create_online_log_dest_1='+RED01' scope=spfile; System altered. sql>alter system set db_create_online_log_dest_2='+REDO2' scope=spfile; System altered SQL> create pfile='/u01/initcrmprod.ora' from spfile; File created. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@siebeldb ~]$ vi /u01/initcrmprod.ora [oracle@siebeldb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 28 18:12:42 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/u01/initcrmprod.ora'; File created. SQL> startup ORACLE instance started. Total System Global Area 6747725824 bytes Fixed Size 2213976 bytes Variable Size 3623880616 bytes Database Buffers 3087007744 bytes Redo Buffers 34623488 bytes Database mounted. Database opened. SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#; GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 3 52428800 INACTIVE +DATA/crmprod/onlinelog/group_3.263.781072923 3 52428800 INACTIVE +FRA/crmprod/onlinelog/group_3.259.781072923 2 52428800 INACTIVE +DATA/crmprod/onlinelog/group_2.262.781072911 GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 2 52428800 INACTIVE +FRA/crmprod/onlinelog/group_2.258.781072913 1 52428800 CURRENT +DATA/crmprod/onlinelog/group_1.261.781072891 1 52428800 CURRENT +FRA/crmprod/onlinelog/group_1.257.781072891 6 rows selected. QL> show parameter db_create_online_log_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_online_log_dest_1 string +REDO1 db_create_online_log_dest_2 string +REDO2 db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 52428800; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 size 52428800; Database altered. 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 crmprod (thread 1) - cannot drop ORA-00312: online log 1 thread 1: '+DATA/crmprod/onlinelog/group_1.261.781072891' ORA-00312: online log 1 thread 1: '+FRA/crmprod/onlinelog/group_1.257.781072891' SQL> alter system switch logfile; System altered. SQL> alter database drop logfile group 1; alter database drop logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance crmprod (thread 1) ORA-00312: online log 1 thread 1: '+DATA/crmprod/onlinelog/group_1.261.781072891' ORA-00312: online log 1 thread 1: '+FRA/crmprod/onlinelog/group_1.257.781072891' SQL> alter system switch logfile; System altered. SQL> alter database drop logfile group 1; alter database drop logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance crmprod (thread 1) ORA-00312: online log 1 thread 1: '+DATA/crmprod/onlinelog/group_1.261.781072891' ORA-00312: online log 1 thread 1: '+FRA/crmprod/onlinelog/group_1.257.781072891' SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#; GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 3 52428800 CURRENT +REDO1/crmprod/onlinelog/group_3.256.781812953 3 52428800 CURRENT +REDO2/crmprod/onlinelog/group_3.256.781812953 2 52428800 ACTIVE +REDO1/crmprod/onlinelog/group_2.257.781812977 GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 2 52428800 ACTIVE +REDO2/crmprod/onlinelog/group_2.257.781812977 1 52428800 ACTIVE +DATA/crmprod/onlinelog/group_1.261.781072891 1 52428800 ACTIVE +FRA/crmprod/onlinelog/group_1.257.781072891 6 rows selected. SQL> alter system checkpoint global; System altered. SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#; GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 3 52428800 CURRENT +REDO1/crmprod/onlinelog/group_3.256.781812953 3 52428800 CURRENT +REDO2/crmprod/onlinelog/group_3.256.781812953 2 52428800 INACTIVE +REDO1/crmprod/onlinelog/group_2.257.781812977 GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 2 52428800 INACTIVE +REDO2/crmprod/onlinelog/group_2.257.781812977 1 52428800 INACTIVE +DATA/crmprod/onlinelog/group_1.261.781072891 1 52428800 INACTIVE +FRA/crmprod/onlinelog/group_1.257.781072891 6 rows selected. SQL> alter database drop logfile group 1; Database altered. SQL> alter database add logfile group 1 size 52428800; Database altered. SQL> select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#; GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 3 52428800 CURRENT +REDO1/crmprod/onlinelog/group_3.256.781812953 3 52428800 CURRENT +REDO2/crmprod/onlinelog/group_3.256.781812953 2 52428800 INACTIVE +REDO1/crmprod/onlinelog/group_2.257.781812977 GROUP# BYTES STATUS ---------- ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 2 52428800 INACTIVE +REDO2/crmprod/onlinelog/group_2.257.781812977 1 52428800 UNUSED +REDO1/crmprod/onlinelog/group_1.258.781813125 1 52428800 UNUSED +REDO2/crmprod/onlinelog/group_1.258.781813125 6 rows selected. SQL>
No comments yet
DBA 