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.***

Wednesday, 19 September 2012

Post Upgradation Task for Oracle Database

After successful upgrade of database your primary task is to 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, check your application log for unexpected error. Here is the some of the required tasks that are specified for your environment after you upgrade your database, regardless whether you used any Upgradation method.
Setting Environment Variables on Linux and UNIX Systems
After Manual Upgrades If your environment is Linux, and if you performed a manual upgrade of Oracle Database, then you must ensure that below environment variables point to the directories of the new Oracle Database release.
Note that DBUA automatically makes necessary changes to environment variables and if you are upgrading a cluster database then perform these checks on all nodes on which the cluster database has instances configured.
Setting oratab and Scripts to Point to the New Oracle Home
After you upgrade Oracle Database to the new release, you must ensure that your oratab file and any client scripts that set the value of ORACLE_HOME point to the new Oracle home that is created for the new Oracle Database release.
Note DBUA automatically points oratab to the new Oracle home, client scripts must be checked no matter which method you use to upgrade.
Upgrading the Recovery Catalog 
You must upgrade your recovery catalog to the new upgraded oracle release in order to prepare the RMAN backup. For more details please check: Oracle Database Backup and Recovery User's Guide .
Upgrading the Time Zone
If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to upgrade the time zone file.
Upgrading Statistics Tables Created by the DBMS_STATS Package
After Upgrading Oracle Database If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running the following procedure:
In the example, SCOTT is the owner of the statistics table and STAT_TABLE is the name of the statistics table. Perform this procedure for each statistics table.
Upgrading Externally Authenticated SSL Users
If you are upgrading from Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), and you are using externally authenticated SSL users, then you must run the SSL external users conversion (extusrupgrade) script to upgrade those users.
ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring
<hostname:port_no:sid> --dbuser <db admin> --dbuserpassword
<password> -a
Note: If you are upgrading from Oracle Database 10g Release 2 (10.2) or higher, then you are not required to run this command.
Installing Oracle Text Supplied Knowledge Bases
The Oracle Text supplied knowledge bases are part of the companion Oracle Database 11g release products and are not immediately available after an upgrade to the new Oracle Database 11g release. Any Oracle Text features dependent on the supplied knowledge bases which were available before the upgrade does not function after the upgrade. To re-enable such features, you must install the Oracle Text supplied knowledge bases from the installation media.
After an upgrade, all user extensions to the Oracle Text supplied knowledge bases must be regenerated. These changes affect all databases installed in the given Oracle home. For more information about: Oracle Text Application Developer's Guide 
Updating Your Oracle Application Express Configuration
You must complete a series of post-installation steps to configure Application Express for use with the new Oracle Database 11g release: Oracle Application Express Installation Guide 
Note: If your database originally included Application Express Version 3.2 or higher, then there is no additional configuration necessary after upgrading to the new Oracle Database 11g release and If your database was not an Oracle Express Edition (XE) database, but contained an earlier version of Application Express (HTML DB), then the latest version is automatically installed during the upgrade.
Configuring Fine-Grained Access to External Network Services
Oracle Database 11g includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use these packages, then you must install Oracle XML DB if it is not installed. You must also configure network access control lists (ACL) in the database before installing these packages. Visit the below reference guide for more details:
You have to also perform some of the recommended tasks after Upgrading Database. These tasks are recommended but not required, regardless of whether you performed the upgrade manually or by using DBUA:
·              Backup the Database
·              Reset password to enforce case sensitivity
·              Set Threshold values for Tablespace Alerts.
·              Migrate from Rollback segment to automatic Undo management
·              Test the Upgraded Production database
If you are performing a Manual upgrade of Oracle Database rather than using DBUA, then you must perform additional tasks after your database is upgraded.
Change or Create Passwords for Upgraded Database:
SQL> SELECT username, account_status
         FROM dba_users
         ORDER BY username;
To lock an expire passwords issue the following SQL statement:
If the REMOTE_LOGIN_PASSWORDFILE parameter is set to either exclusive or shared, create a password file with ORAPWD.
Change PFILE to a SPFILE
If you are currently using a traditional initialization parameter file, then perform the following steps to migrate to a server parameter file:
1.      If the initialization parameter file is located on a client computer, then transfer the file from the client computer to the server computer.
2.      Create a SPFILE using the CREATE SPFILE statement.
Note: You are not required to start the database to issue a CREATE SPFILE statement.
3.      ‘Start up’ the instance using the newly-created SPFILE.
Upgrade the Oracle Clusterware Configuration
If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database. Run srvctl from ORACLE_HOME/bin
Remove Obsolete Parameter:
Each release of Oracle Database introduces new initialization parameters, deprecates some initialization parameters, and makes some initialization parameters obsolete. You must remove adjust these to take the advantage of new parameter.
Setting the COMPATIBLE Parameter
This is very important setting always set the exact compatible parameter. Always perform backup before changing this parameter directly. 
  1. If you are using SPFILE then follow these steps:
SQL> Shutdown and restart the database
  1. If you are using PFILE then follow these steps:
Edit the PFILE to change COMPATIBLE parameter
Startup database using modified PFILE.
Configuring tnsnames.ora and Listener.ora
When you perform a manual upgrade without using DBUA, the tnsnames.ora file is not automatically populated with new configuration information and settings. Therefore, you may need to manually update tnsnames.ora and adjust local_listener and remote_listener parameter references if these need to be resolved.
Configure Enterprise Manager
If you are not yet using Oracle Enterprise Manager to manage your database, then install and configure Enterprise Manager Database Control. If your database is being managed by Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control, then use the following command to update the configuration:
emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]
You must run this from the Oracle home of the new Oracle Database release. When prompted, provide the Oracle home from which the configuration is being upgraded.
You can also configure Enterprise Manager using DBCA. Select the Configure Database Options option, and then select the Enterprise Manager Repository option.
Set CLUSTER_DATABASE Parameter for Oracle RAC
For upgrades of Oracle RAC databases, in "Preparing the New Oracle Home for a Manual Upgrade", you were instructed to set the CLUSTER_DATABASE parameter to FALSE before upgrading a cluster database. Now as the upgrade is finished, you must set back this parameter to TRUE.



Post a Comment