Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 18 September 2012

Manual Upgrading Oracle 9i database to 10g

1.      Perform a cold backup of the database. It is always secure take the backup of database before major changing.
2.      Run the utlrp.sql to compile if any invalid object
  oracle10g>@?ORACLE_HOME\rdbms\admin\utlrp.sql
3.      Create SYSAUX Tablespace with the following parameter.
CREATE TALESPACE sysaux
DATAFILE ‘C:\oracle1\oradata\sysaux.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
4.      Consider you have already installed oracle 10g on server and listener configuration has been done. Connect to the database to be upgraded and run utlu102i.sql to determine the pre-upgrade tasks to be completed.
Note: If you get any errors during the upgrade script execution, re-execute the script after fixing the error. The post-upgrade status utility utlu102s.sql gives the name of specific script to run to fix the failed component. It is better to run the utlu102s.sql utility with the TEXT option. It queries the DBA_SERVER_REGISTRY to determine upgrade status and provides information about invalid or incorrect component upgrades. It also provides names of scripts to re-run to fix the errors.
SQL> spool \tmp\upgrade.txt
SQL>@?ORACLE_HOME\rdbms\admin\utlu102i.sql
SQL> spool off
SQL*Plus: Release 9.2.0.7.0 – Production on Thu Sep 13 16:24:23 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 – Production
SQL> spool preupgrade.log
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    09-13-2012 16:25:55
**********************************************************************
Database:
**********************************************************************
–> name:       orcl3
–> version:    9.2.0.7.0
–> compatible: 9.2.0
–> blocksize:  8192
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
–> The existing log files are adequate. No changes are required.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 246 MB
…. AUTOEXTEND additional space required: 54 MB
WARNING: –> TEMP tablespace is not large enough for the upgrade.
…. currently allocated size: 30 MB
…. minimum required size: 58 MB
…. increase current size by: 18 MB
…. tablespace is NOT AUTOEXTEND ENABLED.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: –> “shared_pool_size” needs to be increased to at least 183677531
WARNING: –> “java_pool_size” needs to be increased to at least 77108963
WARNING: –> “streams_pool_size” is not currently defined and needs a value of
at least 60321646
WARNING: –> “large_pool_size” needs to be increased to at least 9388504
WARNING: –> “session_max_open_files” needs to be increased to at least 20
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
– No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
– No obsolete parameters found. No changes are required
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views         [upgrade]  VALID
–> Oracle Packages and Types    [upgrade]  VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 10g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:SYS
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
–> New “SYSAUX” tablespace
…. minimum required size for database upgrade: 500 MB
PL
/SQL procedure successfully completed.

Review the output and make any necessary alteration. Also make a note of how many invalid objects is there. Below is the brief description of the output of the utlu102i.sql script.
Database:
This section displays global database information about the current database such as the database name, release number, and compatibility level. A warning is displayed if the COMPATIBLE initialization parameter needs to be adjusted before the database is upgraded.
Logfiles:
This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number, and recommended size are displayed.
New files of at least 4 MB (preferably 10 MB) need to be created in the current database. Any redo log files less than 4 MB must be dropped before the database is upgraded.
Tablespaces:
This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade. If the tablespace does not have enough free space, then space must be added to the tablespace in the current database. Tablespace adjustments need to be made before the database is upgraded.
Update Parameters:
This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments need to be made to the parameter file after it is copied to the new Oracle Database 10g release.
Deprecated Parameters:
This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 10g release.
Obsolete Parameters:
This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 10g release. Obsolete initialization parameters need to be removed from the parameter file before the database is upgraded.
Components:
This section displays a list of database components in the new Oracle Database 10g release that will be upgraded or installed when the current database is upgraded.
Miscellaneous Warnings:
This section provides warnings about specific situations that may require attention before and/or after the upgrade.
SYSAUX Tablespace:
This section displays the minimum required size for the SYSAUX tablespace, which is required in Oracle Database 10g. The SYSAUX tablespace must be created after the new Oracle Database 10g release is started and BEFORE the upgrade scripts are invoked.
Note: If you are using DBUA to upgrade, the assistant will prompt you for the creation of the SYSAUX tablespace so there is no need to create it manually.
5.      Shutting down the database delete the oracle service from 9i ORACLE_HOME and create new service for oracle 10g ORACLE_HOME using ORADIM utility.
ORADIM -DELETE -SID <db_name>
ORADIM -NEW -SID <db_name> -INTPWD PASSWORD -STARTMODE MANUAL -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
6.      Copy the pfile or spfile.ora and password file from the 9i Oracle home directory to the Oracle 10g Oracle home directory. Adjust the following parameters in pfile:
        Adjust the COMPATIBLE parameter 10.2.0.1
Note: The minimum value required is 9.2.0 for the upgrade. Remember by mistake if you set this parameter to 10.0.1, then you will never be able to downgrade the database to 9i.
        You must remove obsolete parameters.
        Set the DB_DOMAIN parameter properly.
7.      Once sure all the environment variables are set to correctly reference the Oracle 10g ORACLE_HOME (On Unix: verify ORACLE_HOME, PATH, ORA_NLS33, and LD_LIBRARY_PATH). Use SQL*Plus, and connect to the database using the SYSDBA privilege.
8.      Start the instance by using the STARTUP UPGRADE mode with the updated pfile.
Now wait for some time it will work itself. It is better to spool the output to a file so that you can review it afterward.
@?\rdbms\admin\catupgrd.sql    
9.      Run the utlrp.sql script to re-compile all invalid objects.
oracle10g>@?ORACLE_HOME\rdbms\admin\utlrp.sql
Compare the number of invalid object with the number noted earlier compile. Hope fully it should be the same or less.
10.  Then check the status of upgrade
@?\rdbms\admin\utlu102s.sql
11.  Restart the database and create spfile from pfile.
12.  Finally take the Backup of the database.
As we know upgrading the database will sometimes degrade the performance. So after successful upgrade check the performance and ensure the database is stable:
Collect statistics during critical and peak load before and after upgrade and compare it.
Review alert.log for ora-7445, ora-600 or unexpected trace files and check your application log for unexpected error.Check my other post for details of Post Upgradation Work.

2 comments:

  1. Hi ,

    "What stops oracle not to allow doengrade to lower version once we change the compatable parameter,where all the places it does the modifiations,what exactly it does internally.''
    Regards
    Lakshmi N

    ReplyDelete
  2. BlueHost is ultimately one of the best website hosting provider for any hosting services you require.

    ReplyDelete