Oracle 11g upgrading tips

The following is a compilation of interesting tips I found when upgrading to 11g.

Things you may not know:

- SQL Developer is installed by default when you use one of the templates from DBCA.
- Oracle XML DB will be installed as well when the DB gets created.
- Oracle Data mining creation schema is now part of catproc.sql
- Your can upgrade directly to 11g using DBUA from 9.2.0.4 or higher, 10.1.0.2 or higher or
10.2.0.1 or higher.
- If your optimizer statistics are stale, then DBUA will recommend for you to exit and collect
statistics before you continue.
- If you did not applied the time zone patches to your 9i or 10g database then DBUA will fail to
upgrade the database until you apply the required patches.
- A new parameter called "diagnostic_dest" determinate where your log files will be created, this
parameter replaces background_dump_dest, user_dump_dest and core_dump_dest.
- DBUA will give you the option to move files from file system to file system, from file system to
ASM and from ASM to file system.
- DBUA will also give you the option to recompile invalid objects after the upgrade.
- If you need to restore and you let DBUA do the backup for you, your restore files are located
on $ORACLE_BASE/admin//backup.


Personal and optional Advice

- Although it is possible to install and upgrade your database to 11g at the same time, please abstain from it as you may face problems during the install.

- Be sure you have a good install before trying to perform the upgrade, I advice as well to create a small new database, just to be sure the software is working properly and you have all the parameters in place.

- If you have never tested your backups before, now would be a good idea to do it. Also if you are like me that do not trust the tape library, then try the restore in a different server and document your restore process. If you need to restore you will have a lot of eyes in your shoulders, and if you start checking the manual for restore you are not going to transmit a lot of confidence to them.

- DBUA will present you to do the backup of your database for you, however I recommend you do your own.

RMAN> shutdown immediate
RMAN> startup restrict
RMAN> backup database plus archivelog format '' tag pre-upgrade;
RMAN> shutdown immediate
RMAN> startup

If you need to restore

startup nomount
run {
restore controlfile from '';
alter database mount;
restore database from tag pre-upgrade
recover database noredo;
alter database open resetlogs;
}

After the restore is complete, you need to point your environment variables to the old Oracle sofware location.

- Do another backup after you successfully upgrade.

- Check your DB alert.log after the upgrade for any errors.

- Check the DBA_REGISTRY view to make sure all the components reflect the new version.

- The issues I frequently find while upgrading are tablespace space and memory, be sure you have
enough of both. After you upgrade you can bet your footprint will take more memory and many times
more disk.

- before you upgrade do dump the control file to trace:

SQL> alter database backup controlfile to trace;


These are a must

- Backup your binaries and database before trying to upgrade.
- Check the release notes, most of the time they tell you what errors can you expect.
- Read the upgrade manual.
- DBUA logs are by default located at $ORACLE_HOME/cfgtoolslogs/dbua/db_name, tail the
log files during the upgrade and check them again after the upgrade.

UpgradeResults.htm = Summary of what DBUA will upgrade
Post_Upgrade.log = Log file for post upgrade work.
Oracle_Server.log = Detail of the migration execution
Trace.log = Detail tracing information of the upgrade process.

- Check that your redo log file size are bigger than 4MB. The upgrade will fail if they are not.

- Run the pre-upgrade information tool $ORACLE_HOME/rdbms/admin/utlu111i.sql this will
will tell you if you need to change anything to go to 11g.

- After you are done with the upgrade and post upgrade activities check for invalid objects and compile, you can use the view dba_invalid_objects for this.

- Collect statistics for your tables.

New 11g Parameters

- asm_preferred_read_failure_groups
- client_result_cache_lg
- client_result_cache_size
- commit_logging
- commit_wait
- control_management_pack_access
- db_lost_write_protect
- db_secure_file
- db_ultra_safe
- ddl_lock_timeout
- diagnostic_dest
- global_txn_process
- java_jit_enabled
- ldap_directory_sysauth
- memory_max_target
- memory_target
- optimizer_capture_sql_plan_baselines
- optimizer_use_invisible_indexes
- optimizer_use_pending_statistics
- optimizer_use_sql_plan_baselines
- parallel_io_cap_enabled
- plscope_setting
- redo_transport_user
- resource_manager_cpu_allocation
- result_cache_max_result
- result_cache_max_size
- result_cache_mode
- result_cache_remote_expiration
- sec_case-sensitive_logon
- sec_max_failed_login_sttempts
- sec_protocol_error_further_action
- sec_protocol_error_trace_action
- sec_return_server_release_banner
- xml_db_events

Deprecated 11g parameters

- background_dump_dest
- core_dump_dest
- user_dump_dest
- commit_write
- commit_wait
- instance_groups
- log_archive_local_first
- plsql_debug
- plsql_v2_compatibility
- remote_os_authent
- standby_archive_dest
- transaction_lag

Obsolete parameters in 11g

- ddl_wait_for_locks
- logmnr_max_persistent_sessions
- plsql_compiler_flags