Upgrade Oracle 9i RAC to Oracle 10g RAC - Page 6

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&section=&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.