Upgrade Oracle 9i RAC to Oracle 10g RAC (part 4)

Step 4: Upgrade the Database

Database upgrade has reduced in complexity. Oracle provides a pre-upgrade information tool that analyzes the database and provides recommendations for proper database settings prior to the upgrade. There are also fewer steps to manually upgrade a database.

RAC database can be upgraded using the Database Upgrade Assistant (DBUA) or by performing a manual upgrade. The method demonstrated here is a manual database upgrade. Ensure that a full cold backup is taken before attempting the upgrade. For detailed information on performing an upgrade, please refer to http://download-east.oracle.com/docs/...upgrade.htm#i1011372.

4a. Create password files for RAC instances

Using the orapwd utility, create orapwprod1a and orapwprod1b files in $ORACLE_HOME/dbs on the first and second node respectively.

4b. Create init.ora for 10g RAC instances

On node 1:

[oracle@salmon1]$ cp /u01/app/oracle/product/9.2.0/dbs/initprod1a.ora $ORACLE_HOME/dbs 
[oracle@salmon1]$ more $ORACLE_HOME/dbs/initprod1a.ora
spfile=/ocfs/prod1/spfileprod1.ora

On node 2:

[oracle@salmon2]$ cp /u01/app/oracle/product/9.2.0/dbs/initprod1b.ora $ORACLE_HOME/dbs 
[oracle@salmon2]$ more $ORACLE_HOME/dbs/initprod1b.ora
spfile=/ocfs/prod1/spfileprod1.ora

4c. Create tnsnames.ora on RAC nodes

[oracle@salmon1]$ more $ORACLE_HOME/network/admin/tnsnames.ora
LISTENERS_PROD1 =
  (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = salmon1-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = salmon2-vip)(PORT = 1521))
  )

PROD1 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = salmon1-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = salmon2-vip)(PORT = 1521))
      (LOAD_BALANCE = yes)
      (CONNECT_DATA =
          (SERVICE_NAME = PROD1)
          (FAILOVER_MODE =
              (TYPE = SELECT)
              (METHOD = BASIC)
              (RETRIES = 200)
              (DELAY = 5)
          )
      )
  )

PROD1A =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = salmon1-vip)(PORT = 1521))
      (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = PROD1)
          (INSTANCE_NAME = PROD1A)
      )
  )

PROD1B =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = salmon2-vip)(PORT = 1521))
      (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = PROD1)
          (INSTANCE_NAME = PROD1B)
      )
  )

4d. Edit pfile

Modify the file, pfile_singleinst saved earlier in Step 1j. Comment the following two parameters:

*.cluster_database_instances=2
*.cluster_database=true

4e. Start up 9i database in single-instance mode

[root@salmon1]# export ORACLE_HOME=/u01/app/oracle/product/9.2.0
[root@salmon1]# $ORACLE_HOME/oracm/bin/ocmstart.sh
oracm </dev/null 2>&1 >/u01/app/oracle/product/9.2.0/oracm/log/cm.out &
[root@salmon1]# su - oracle
[oracle@salmon1]$ echo $ORACLE_HOME
/u01/app/oracle/product/ocm
[oracle@salmon1]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jul 30 06:13:06 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile=/tmp/pfile_singleinst

4f. Perform pre-database upgrade checks

Make the necessary adjustments reported in the pre-upgrade information tool report before proceeding with the upgrade. The SYSAUX tablespace is created after starting the database in the new 10g release.

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i

4g. Gather optimizer statistics

Optimizer statistics is automatically gathered during the upgrade for dictionary objects that have stale or missing statistics. To shorten the upgrade time, consider collecting the statistics before the upgrade.

exec dbms_stats.gather_schema_stats('CTXSYS', options=>'GATHER', estimate_percent
=> DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('SYS', options=>'GATHER', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

4h. Connect role privilege

Prior to Oracle 10gR2, the privileges granted to Connect role are:

SQL> select PRIVILEGE from DBA_SYS_PRIVS where grantee='CONNECT';

PRIVILEGE
--------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

In Oracle 10gR2, the only privilege granted to the Connect role is Create Session. Grant the necessary privileges to users or roles before the database upgrade. The upgrade scripts automatically make the required adjustments to the Oracle-supplied users.

4i. Verify sys and system default tablespace

SQL> select USERNAME, DEFAULT_TABLESPACE from DBA_USERS where username in ('SYS','SYSTEM');

USERNAME    DEFAULT_TABLESPACE
----------- --------------------
SYSTEM      SYSTEM
SYS         SYSTEM

4j. Check for invalid objects

Recompile any invalid objects and obtain a list of objects that could not be recompiled.

4k.Update oratab file

Update the /etc/oratab file to reflect the new 10g ORACLE_HOME.

4l. Disable jobs

Disable any cron or snapshot jobs.

4m. Shut down 9i database

SQL> shutdown immediate;

4n. Upgrade database

In the new Oracle 10gR2 environment on node 1, run the following statements in the following order::

1. SQL> startup pfile=/tmp/pfile_singleinst upgrade

2. SQL> create tablespace sysaux datafile '/ocfs/prod1/sysaux01.dbf' size

500M extent management local segment space management auto;

3. SQL> spool /tmp/catupgrd.log

4. SQL> @$ORACLE_HOME/rdbms/admin/catupgrd

Review log file for errors.

4o. Check for invalid objects

Recompile any invalid objects and obtain a list of objects that could not be recompiled. Compare this list with the list of invalid objects before the upgrade.

4p. Optimizer statistics gathering job

After the database upgrade, an optimizer statistics collection job called GATHER_STATS_JOB was created and is scheduled to run when the MAINTENANCE_WINDOW_GROUP window group is opened.

The MAINTENANCE_WINDOW_GROUP consists of two windows – the WEEKNIGHT_WINDOW and the WEEKEND_WINDOW. The WEEKNIGHT _WINDOW opens Monday through Friday at 10pm for 8 hours and the WEEKEND_WINDOW opens on Saturday at 12am for 48 hours.

SQL> select JOB_NAME, WINDOW_GROUP_NAME, SW.WINDOW_NAME, DURATION, SW.REPEAT_INTERVAL 
  2  from DBA_SCHEDULER_JOBS j, DBA_SCHEDULER_WINGROUP_MEMBERS wm, DBA_SCHEDULER_WINDOWS sw
  3  where j.schedule_name=wm.window_group_name
  4  and sw.window_name=wm.window_name
  5  and job_name='GATHER_STATS_JOB';

JOB_NAME          WINDOW_GROUP_NAME         WINDOW_NAME       DURATION
----------------- ------------------------- ----------------- ---------------
REPEAT_INTERVAL
--------------------------------------------------------------------------------
GATHER_STATS_JOB  MAINTENANCE_WINDOW_GROUP  WEEKEND_WINDOW    +002 00:00:00
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

GATHER_STATS_JOB  MAINTENANCE_WINDOW_GROUP  WEEKNIGHT_WINDOW  +000 08:00:00
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

You may want to make the necessary modifications to the window properties or disable the job only if you have other methods of keeping the statistics current.

4q. Start up 10g database in cluster mode

Modify the following entries in pfile_singleinst and restart the database using the modified pfile.

*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0'
*.log_archive_format='prod1_%t_%s_%r.arc'  

The compatible parameter once set to 10.2.0 is irreversible. You cannot downgrade the database back to 9.2.0.

The log_archive_format requires a "%r" format specification when the compatible is set to 10.2.0. The "%r" refers to the logical incarnation of the database and changes each time when the database is opened using the resetlogs command. The default value of log_archive_format is %t_%s_%r.dbf.

4r. Create spfile

Create the spfile and restart the instance using the new spfile.

SQL> create spfile='/ocfs/prod1/spfileprod1.ora' from pfile='/tmp/pfile_singleinst';

4s. Start up the second instance

[oracle@salmon2]$ export ORACLE_SID=prod1b
[oracle@salmon2]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 30 08:51:13 2005

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

Connected to an idle instance.

SQL> startup

4t. Register the RAC instances with Oracle Clusterware

[oracle@salmon1]$ srvctl add database -d prod1 -o $ORACLE_HOME
[oracle@salmon1]$ srvctl add instance -d prod1 -i prod1a -n salmon1
[oracle@salmon1]$ srvctl add instance -d prod1 -i prod1b -n salmon2
[oracle@salmon1]$ srvctl config database -d prod1
salmon1 prod1a /u01/app/oracle/product/10.2.0/db_1
salmon2 prod1b /u01/app/oracle/product/10.2.0/db_1

4u. Remove Oracle 9i RAC start up/shut down scripts

Remove any scripts that are responsible for starting and stopping Oracle 9i RAC processes such as the Oracle Cluster Manager and Global Services Daemons. Reboot the cluster nodes and verify that the Oracle 10g Clusterware, nodeapps and instances are automatically started.

[oracle@salmon1]$ crs_stat -t
Name            Type           Target     State      Host        
-----------------------------------------------------------------
ora.prod1.db    application    ONLINE     ONLINE     salmon1
ora....1a.inst  application    ONLINE     ONLINE     salmon1 
ora....1b.inst  application    ONLINE     ONLINE     salmon2
ora....N1.lsnr  application    ONLINE     ONLINE     salmon1
ora....on1.gsd  application    ONLINE     ONLINE     salmon1
ora....on1.ons  application    ONLINE     ONLINE     salmon1
ora....on1.vip  application    ONLINE     ONLINE     salmon1
ora....N2.lsnr  application    ONLINE     ONLINE     salmon2 
ora....on2.gsd  application    ONLINE     ONLINE     salmon2
ora....on2.ons  application    ONLINE     ONLINE     salmon2
ora....on2.vip  application    ONLINE     ONLINE     salmon2
[oracle@salmon1]$ srvctl status database -d prod1
Instance prod1a is running on node salmon1
Instance prod1b is running on node salmon2
Use the following commands to manually start and stop the instances:
srvctl start database -d prod1
srvctl start instance -d prod1 -i prod1a 
srvctl start instance -d prod1 -i prod1b
srvctl stop database -d prod1
srvctl stop instance -d prod1 -i prod1a
srvctl stop instance -d prod1 -i prod1b

4v. Re-enable jobs

Re-enable any jobs that were disabled before the upgrade.

Congratulations, you have upgraded your Oracle 9i RAC to Oracle 10g RAC!

previous  	next