by Vincent Chan
5m. Migrate redo logs of the first instance to ASM
SQL> alter system set db_create_online_log_dest_1='+DG1';
System altered.
SQL> alter system set db_create_online_log_dest_2='+DG2';
System altered.
SQL> select l.group#, thread#, member
2 from v$log l, v$logfile lf
3 where l.group#=lf.group#;
GROUP# THREAD# MEMBER
---------- ---------- --------------------------------
1 1 /ocfs/prod1/redo01.log
2 1 /ocfs/prod1/redo02.log
3 2 /ocfs/prod1/redo03.log
4 2 /ocfs/prod1/redo04.log
SQL> alter database add logfile group 5 size 10M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 100M;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 100M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> select l.group#, thread#, member
2 from v$log l, v$logfile lf
3 where l.group#=lf.group#;
GROUP# THREAD# MEMBER
---------- ---------- ----------------------------------------------
1 1 +DG1/prod1/onlinelog/group_1.268.565168737
2 1 +DG1/prod1/onlinelog/group_2.269.565168889
3 2 /ocfs/prod1/redo03.log
4 2 /ocfs/prod1/redo04.log
1 1 +DG2/prod1/onlinelog/group_1.257.565168763
2 1 +DG2/prod1/onlinelog/group_2.258.565168913
6 rows selected.
5n. Modify init.ora of both RAC instances
On node 1:
[oracle@salmon1]$ more $ORACLE_HOME/dbs/initprod1a.ora
spfile='+DG1/spfileprod1.ora'
On node 2:
[oracle@salmon2]$ more $ORACLE_HOME/dbs/initprod1b.ora
spfile='+DG1/spfileprod1.ora'
5o. Migrate database spfile to ASM
On prod1a:
SQL> create pfile='/tmp/pfilemig' from spfile;
File created.
SQL> create spfile='+DG1/spfileprod1.ora' from pfile='/tmp/pfilemig';
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 310378744 bytes
Database Buffers 209715200 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
spfile string +DG1/spfileprod1.ora
5p. Create the second ASM instance
[oracle@salmon1]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@salmon1]$ scp spfile+ASM1A.ora salmon2:/u01/app/oracle/product/10.2.0/db_1/dbs/spfile+ASM1B.ora
spfile+ASM1A.ora 100% 1536 1.4MB/s 00:00
Create the second ASM instance on the second node.
[oracle@salmon2]$ mkdir -p /u01/app/oracle/admin/+ASM/bdump
[oracle@salmon2]$ mkdir -p /u01/app/oracle/admin/+ASM/cdump
[oracle@salmon2]$ mkdir -p /u01/app/oracle/admin/+ASM/udump
[oracle@salmon2]$ export ORACLE_SID=+ASM1B
[oracle@salmon1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 18:27:28 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 96468992 bytes
Fixed Size 1217908 bytes
Variable Size 70085260 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
5q. Migrate redo logs of the second instance to ASM
[oracle@salmon1 dbs]$ export ORACLE_SID=prod1b
[oracle@salmon1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 18:45:26 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 310378744 bytes
Database Buffers 209715200 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select l.group#, thread#, member
2 from v$log l, v$logfile lf
3 where l.group#=lf.group#;
GROUP# THREAD# MEMBER
---------- ---------- -------------------------------------------------
1 1 +DG1/prod1/onlinelog/group_1.268.565168737
2 1 +DG1/prod1/onlinelog/group_2.269.565168889
3 2 /ocfs/prod1/redo03.log
4 2 /ocfs/prod1/redo04.log
1 1 +DG2/prod1/onlinelog/group_1.257.565168763
2 1 +DG2/prod1/onlinelog/group_2.258.565168913
6 rows selected.
SQL> alter database add logfile group 5 size 10M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 100M;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database add logfile group 4 size 100M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> select l.group#, thread#, member
2 from v$log l, v$logfile lf
3 where l.group#=lf.group#;
GROUP# THREAD# MEMBER
---------- ---------- --------------------------------------------------
1 1 +DG1/prod1/onlinelog/group_1.268.565168737
2 1 +DG1/prod1/onlinelog/group_2.269.565168889
3 2 +DG1/prod1/onlinelog/group_3.271.565179129
4 2 +DG1/prod1/onlinelog/group_4.272.565180943
1 1 +DG2/prod1/onlinelog/group_1.257.565168763
2 1 +DG2/prod1/onlinelog/group_2.258.565168913
3 2 +DG2/prod1/onlinelog/group_3.259.565179149
4 2 +DG2/prod1/onlinelog/group_4.260.565180961
8 rows selected.
5r. Using ASM Command-Line Utility
ASM Command-Line utility (asmcmd) was introduced in Oracle
10gR2 to provide a Unix-like command-line interface to view and administer
files and directories stored in ASM. Refer to http://www.oracle.com/pls/db102/db102.show_toc?which=main&partno=b14215&maxlevel=2§ion=&expand=55928
for details on asmcmd.
[oracle@salmon1]$ export ORACLE_SID=+ASM1A
[oracle@salmon1]$ asmcmd ls
DG1/
DG2/
RECOVERYDEST/
[oracle@salmon1]$ asmcmd ls -l DG1/PROD1/DATAFILE
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE AUG 01 19:00:00 Y APPS_D.264.565160121
DATAFILE MIRROR COARSE AUG 01 19:00:00 Y DRSYS.263.565160111
DATAFILE MIRROR COARSE AUG 01 19:00:00 Y SYSAUX.260.565160061
DATAFILE MIRROR COARSE AUG 01 19:00:00 Y SYSTEM.257.565159841
DATAFILE MIRROR COARSE AUG 01 19:00:00 Y UNDOTBS1.258.565159949
DATAFILE MIRROR COARSE AUG 01 19:00:00 Y UNDOTBS2.259.565160005
DATAFILE MIRROR COARSE AUG 01 19:00:00 Y USERS.262.565160103
[oracle@salmon1]$ asmcmd -p
ASMCMD [+] > ls
DG1/
DG2/
RECOVERYDEST/
ASMCMD [+] > ls DG1
PROD1/
cf1.dbf
spfileprod1.ora
ASMCMD [+] > ls -l DG1
Type Redund Striped Time Sys Name
Y PROD1/
N cf1.dbf =>
+DG1/PROD1/CONTROLFILE/backup.256.565159777
N spfileprod1.ora =>
+DG1/PROD1/PARAMETERFILE/spfile.267.565170213
ASMCMD [+] > exit
5s. Register the ASM instances with Oracle Clusterware
For higher availability, register the ASM instances under
the Oracle Clusterware framework. When registered, the clusterware should
detect any failed instances and automatically attempt to start up the
instances. The clusterware should also automatically start up the instances
when the servers are rebooted.
On node 1:
[oracle@salmon1]$ srvctl add asm -n salmon1 -i +ASM1A -o $ORACLE_HOME
[oracle@salmon1]$ srvctl start asm -n salmon1
[oracle@salmon1]$ srvctl status asm -n salmon1
ASM instance +ASM1A is running on node salmon1.
On node 2:
[oracle@salmon2]$ srvctl add asm -n salmon2 -i +ASM1B -o $ORACLE_HOME
[oracle@salmon2]$ srvctl start asm -n salmon2
[oracle@salmon2]$ srvctl status asm -n salmon2
ASM instance +ASM1B is running on node salmon2.
[oracle@salmon1]$ ps -ef | grep dbw | grep -v grep
oracle 21231 1 0 19:41 ? 00:00:00 asm_dbw0_+ASM1A
[oracle@salmon1]$ kill -9 21231
[oracle@salmon1]$ ps -ef | grep dbw | grep -v grep
[oracle@salmon1]$ srvctl status asm -n salmon1
ASM instance +ASM1A is not running on node salmon1.
[oracle@salmon1]$ ps -ef | grep dbw | grep -v grep
oracle 26123 1 1 11:51 ? 00:00:00 asm_dbw0_+ASM1A
[oracle@salmon1]$ srvctl status asm -n salmon1
ASM instance +ASM1A is running on node salmon1.
After successfully migrating all the data files over to ASM,
the old data files are no longer needed and can be removed. Your RAC database
is now running on ASM!
Conclusion
Compared to its predecessors, Oracle 10g RAC is much easier
to install, use and manage, thereby reducing the total cost of ownership. Now
that you have upgraded your RAC environment to Oracle 10g, you can take a look
at and start using the new features. I hope that this guide has provided a
clear and concise method of performing the upgrade.
Author Bio
Vincent Chan (vkchan99@yahoo.com) is a Senior Consultant at
MSD Inc. He is an Oracle Certified Master DBA with more than nine years of
experience architecting and implementing Oracle solutions for various clients.