Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Migrate oracle online Redo logs to 11g ASM Storage

Posted by Hendry chinnapparaj on September 1, 2011

Thursday, 1 September 2011

Hendry dasan

asmtest:/u01/ofsad2> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 1 09:07:28 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Time Start: 01/09/2011 9:14:36 AM

GROUP# STATUS  TYPE

———- ——- ——-

MEMBER

——————————————————————————————————————————————–

IS_RECOVERY_DEST_FILE

———————

7         ONLINE

/u02/oradata/asmtest/redo07.log

NO

8         ONLINE

/u02/oradata/asmtest/redo08.log

NO

9         ONLINE

/u02/oradata/asmtest/redo09.log

NO

3 rows selected.

Time End: 01/09/2011 9:14:36 AM

Elapsed Time for Script Execution: 578 msecs

This parameter db_create_file_dest has to point to your ASM Disk Group

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_create_file_dest                  string      +DATA

set pages 1000 lines 150

column member format a50

select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#

Time Start: 01/09/2011 9:21:12 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

7  524288000 INACTIVE         /u02/oradata/asmtest/redo07.log

8  524288000 INACTIVE         /u02/oradata/asmtest/redo08.log

9  524288000 CURRENT          /u02/oradata/asmtest/redo09.log

3 rows selected.

Time End: 01/09/2011 9:21:12 AM

Elapsed Time for Script Execution: 484 msecs

You can drop the inactive and create new groups

SQL> Alter database drop logfile group 7;

Database altered.

SQL> Alter database add logfile group 1 size 524288000;

Database altered.

SQL> Alter database drop logfile group 8;

Database altered.

SQL> Alter database add logfile group 2 size 524288000;

Database altered.

set pages 1000 lines 150

column member format a50

select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#

Time Start: 01/09/2011 9:26:33 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 UNUSED           +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 UNUSED           +DATA/asmtest/onlinelog/group_2.871.760699567

9  524288000 CURRENT          /u02/oradata/asmtest/redo09.log

1  524288000 UNUSED           +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 UNUSED           +DATA/asmtest/onlinelog/group_2.872.760699569

5 rows selected.

Time End: 01/09/2011 9:26:34 AM

Elapsed Time for Script Execution: 609 msecs

Both log groups 2 and 3 are shifted to ASM diskgroup DATA. Log group 9 is current so cannot be dropped. so do a log switch

Time Start: 01/09/2011 9:28:54 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 ACTIVE           +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.871.760699567

9  524288000 ACTIVE           /u02/oradata/asmtest/redo09.log

1  524288000 ACTIVE           +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.872.760699569

5 rows selected.

Time End: 01/09/2011 9:28:54 AM

Elapsed Time for Script Execution: 500 msecs

Log switched and group 9 is no more CURRENT but it is still active. ACTIVE means the group is still required for crash recovery and hasn’t yet check pointed. An active log group can’t be deleted either.

SQL> alter system checkpoint;

System altered.

Time Start: 01/09/2011 9:30:04 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.871.760699567

9  524288000 INACTIVE         /u02/oradata/asmtest/redo09.log

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.872.760699569

5 rows selected.

Time End: 01/09/2011 9:30:04 AM

Elapsed Time for Script Execution: 546 msecs

SQL> Alter database drop logfile group 9;

Database altered.

SQL> Alter database add logfile group 3 size 524288000;

Time Start: 01/09/2011 10:13:07 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.871.760699567

3  524288000 UNUSED           +DATA/asmtest/onlinelog/group_3.875.760699865

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.872.760699569

3  524288000 UNUSED           +DATA/asmtest/onlinelog/group_3.876.760699867

6 rows selected.

Time End: 01/09/2011 10:13:08 AM

Elapsed Time for Script Execution: 484 msecs

Advertisements

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: