Hi Readers!!, Upgrade operation would be the essential task that takes into consideration. I have listed down below some of the important information related to planning the database configuration, and testing.
Methods
Database Upgrade Assistant
GUI Interface to Guide you through the process and oracle preferred method.
Advantages
- Automates all tasks
- Performs both Release and Patch set upgrades
- Supports Single Instance databases and Oracle RAC
- Informs user and fixes upgrade prerequisites
- Automatically reports errors found in spool logs
- Provides complete HTML report of the upgrade process
- Offers less control over individual upgrade steps
Manual Upgrade
Command-line upgrade using Oracle supplied SQL scripts and utilities
Advantages
- The DBA controls every step of the upgrade process
Disadvantages
- More work
- Manual checks required of spool logs for error
- More error-prone
- More difficult to automate
Export/Import
Full or Partial Export followed by full or partial import into Oracle Database 11g
Advantages
- Defragments the data
- Restructures the database
- Enables the copying of specified database objects or users
- Serves as a backup archive
Disadvantage
- Can take a long time
- Restructuring the database requires an additional check to validate the index, physical location of the database, etc.
Prepare and Preserve:
Information related to preserving and preparing the source environment for the Oracle Database 11g upgrade(Load test in test environment i.e, should be exactly same as production)
Execute pre-upgrade script
If you are upgrading the database manually, it is required that the pre-upgrade scripts be run. Ensure that the pre-upgrade scripts are run in the original oracle home. It will then log, datafiles sizes adequately fit for the upgrade, initialization parameters, check if sysaux tablespace is created or not, tells how/when to upgrade the timezone file version.
Oracle recommends gathering data dictionary statistics before upgrading the data dictionary. Sysaux tablespace has be to created before invoking upgrade script in manual method whereas, in DBUA, it will be created automatically.
Compatible initialization parameter
Once the database has been upgraded, the COMPATIBLE parameter has been set to 11.2, and the
database has been restarted, then the datafiles, control files and online log files are updated to the new version. This in turn will prevent the database from being downgraded in the future.
When this error occurs the only way to downgrade the database is to restore the database from the backup taken before the database was upgraded or to use any alternate strategies in places like Streams or Export/Import.
it is recommended to leave the parameter set to 10.1.0 or 10.2.0 depending on the setting used before the upgrade until the newly upgraded database performance and functionality is acceptable.
Common pitfalls to avoid :-)
It is mandatory to run the pre-upgrade tool before the upgrade process otherwise the upgrade will not continue.
take a complete online backup of your database as well as make a backup copy of the following files: init.ora or spfile, password file, and all SQL*Net files (sqlnet.ora, listener.ora, tnsnames.ora, and so on...).
Back up the oraInventory and ORACLE_HOME directories.
To avoid "unable to extend" errors, set AUTO EXTEND ON MAXSIZE UNLIMITED for the SYSTEM and SYSAUX tablespaces.
When upgrading from Oracle9i Release 2, the minimum value required for a shared pool is 448 MB
for 64-bit systems and 224 MB for 32-bit systems. When upgrading from Oracle Database 10g and above, the minimum value required for the shared pool is 590 MB for 64-bit systems and 295 MB for 32-bit
systems.
If you are using Database Upgrade Assistance (DBUA) as the method to upgrade, make sure that there is sufficient OS temp space.
Ensure there are no invalid objects in SYS and SYSTEM user schema. Check for invalid objects by performing the following commands:
spool invalid_pre.lst
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off
If there are invalid objects you can recompile them by logging in as a SYSDBA user and running the utlrp.sql located in the $ORACLE_HOME/RDBMS/admin directory.
If invalid objects still exist which do not have a name that begins with x_$, check the owner of objects which can't be compiled successfully. In most cases these objects belong to database options that have been installed in earlier releases but do not exist in the current installation anymore. In this case a support request (SR) with Oracle Support should be opened to ask for the necessary scripts to drop these objects.
If valid or invalid objects exist that have a name that begins with x_$, these are not Oracle created objects, they should be dropped before the upgrade.
Verify that all dba_registry components are valid.
SQL> select substr(comp_id,1,15) comp_id,
substr(comp_name,1,30) comp_name,
substr(version,1,10) version,
status
from dba_registry
order by modified;
Upgrade: Final reminders and information required for the actual upgrade
Post Upgrade: Testing and analysis which should be performed after upgrading your test and production databases to Oracle Database 11g. And down forget to test the performance of the database. Because that is one of the common issues post-upgrade. I hope these pointers are helpful to you.
Thanks for reading!!