Skip to content
Advertisements

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>
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: