//
you're reading...
Oracle Database Upgrade

Database Upgrade from Oracle 9i R2 (9.2.0.7) HP-UX to 11g R2 (11.2.0.4) OEL cross platform – section3

In section 2, I upgraded the Oracle Database software from Oracle 9i R2 (9.2.0.7) to 10g R2 (10.2.0.5)

Now, it is the time to upgrade the database

Create pfile from current spfile of Oracle 9i database and copy that parameter file to the new 10g ORACLE HOME

cp /prodexe/app/oracle/9.2/inittestdb.ora /u01/app/oracle/product/10.2/dbhome_1/dbs/

Some Oracle 9i Initialization Parameters has been deprecated in Oracle 10g R2, please check the Oracle Onine Doc to modify it.

oracle@newzontest01-testdb $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 15 14:37:59 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;
File created.
SQL> shutdown immediate;

Shutdown database and stop listener.

SQL> shutdown immediate
oracle@newzonetest01$ lsnrctl stop

Very IMPORTANT step – create the backup!

In the enterprise environment, you can use SAN Snapshot (only take about 5mins). If you don’t have SAN, copy the physical files somewhere safe. If you don’t have the backup directory, or you don’t have a way to make the backup; you should not carry on this upgrade process.

Change the entry for Oracle 10g

Edit ‘/etc/oratab’ entry and change entry of old 9i ORACLE_HOME to 10g ORACLE_HOME. (In SUN Solaris is ‘/var/opt/oracle/oratab’, in HP UX, AIX is /etc/oratab). Reboot the UNIX server after change.

Do this step only if you have standby database

SQL> startup mount

ORACLE instance started.
..........
 
SQL> select open_mode, DATABASE_ROLE, guard_status, SWITCHOVER_STATUS from v$database;
 
OPEN_MODE  DATABASE_ROLE GUARD_S SWITCHOVER_STATUS
---------- ------------- ------- ------------------
MOUNTED     PRIMARY      NONE    NOT ALLOWED

SQL> alter database set standby database to maximize performance;
 
Database altered.
 
SQL> select open_mode, DATABASE_ROLE, guard_status, SWITCHOVER_STATUS from v$database;
 
OPEN_MODE  DATABASE_ROLE GUARD_S SWITCHOVER_STATUS
---------- ------------- ------- ------------------
MOUNTED    PRIMARY       NONE    NOT ALLOWED

Login using 10g environment and execute following statements:

oracle@newzonetest01> sqlplus /as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on 20 Oct 26 15:31:46 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='$ORACLE_HOME/dbs/inittestdb.ora';
ORACLE instance started.

Total System Global Area 473956352 bytes
Fixed Size                  2041464 bytes
Variable Size             432019848 bytes
Database Buffers           33554432 bytes
Redo Buffers                6340608 bytes
Database mounted.
Database opened.

SQL>
CREATE TABLESPACE SYSAUX
DATAFILE '/data01/oradata/testdb/sysaux01.dbf'
SIZE 4000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

Start with upgrade process and check log file for errors:

Set SYSTEM as default tablespace for SYS and SYSTEM users.

SQL> column username format a20
SQL> column default_tablespace format a20
SQL> select distinct username, default_tablespace from dba_users where username in ('SYS','SYSTEM');

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

If the default tablespace is not system, run following script.

SQL> alter user sys default tablespace SYSTEM;
SQL> alter user system default tablespace SYSTEM;

Start the database upgrade script

SQL> set echo on
SQL> SPOOL /tmp/upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql;
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;
SQL> spool off

If the script return an error – ORA-25153: Temporary Tablespace is Empty, run following script to fix the error.

SQL> Alter tablespace TEMP add tempfile '<Datafile Location>' size 1024M autoextend on;

Create the MDSYS user with a command in the following format:

SQL> CREATE USER MDSYS IDENTIFIED BY mdsys;

Or, if the user MDSYS do exist.

SQL> alter user mdsys identified by mdsys account unlock;

Grant the required privileges to the MDSYS user by running the following procedure

SQL> @$ORACLE_HOME/md/admin/mdprivs.sql

Install Spatial by running the following procedure:

SQL> conn mdsys/mdsys
SQL> @$ORACLE_HOME/md/admin/catmd.sql;
SQL> ALTER USER MDSYS ACCOUNT LOCK;

Re-created the ORDSYS account and re-installed the intermedia packages

SQL> Drop user ordsys cascade;
SQL> alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION null;

Following steps only need to do it, only if the profile set for ORDSYS has the paramater password_verfy_function as verify_funtion, Otherwise would prevent ordinst.sql Step 3 being successful as it prevents from using the old password

SQL> @$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX

SQL> create user ordsys identified by ORDSYS default tablespace SYSAUX;

ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user

SQL> alter user ORDSYS account lock password expire

ERROR at line 1:
ORA-01918: user 'ORDSYS' does not exist

Create the users and grant the appropriate privileges.

  • Start SQL*Plus and connect as SYSDBA.
  • Invoke ordinst.sql with two parameters for the Oracle interMedia tablespace and the Oracle Location services tablespace.
SQL> @$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX

Install Oracle interMedia types and packages.

  • Start SQL*Plus and connect as SYSDBA.
  • Invoke iminst.sql
SQL> @$ORACLE_HOME/ord/im/admin/iminst.sql

Reverting back the change Step 2

alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION verify_function; 

Oracle intermedia was now in valid state

Set compatible value to 10.2.0.5.0, create spfile from pfile, restart and recompile.

Run post-upgrade status tool – utlu102s.sql

@$ORACLE_HOME/rdbms/admin/utlu102s.sql;

Re-Create the database listener in new Oracle Home


oracle@newzonetest01-testdb@/u01/app/oracle/product/10.2/dbhome_1/network/admin $ pwd
/u01/app/oracle/product/10.2/dbhome_1/network/admin
oracle@newzonetest01-testdb@/u01/app/oracle/product/10.2/dbhome_1/network/admin $ vi listener.ora

# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
      )
    )
  )

Enable listener in new Oracle Home

oracle@newzonetest01# lsnrctl start
Advertisements

About daviewning

I am an Oracle DBA

Discussion

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: