Upgrade Oracle Database 10g to 11g walkthrough in detail, by upgrade paths
In any standard cases, live migration with DBUA is recommended.
To be checked well in advance to the migration date.
Check time zone
SQL> SELECT version FROM v$timezone_file;
Caution with source databases version < 10.2.0.4
Check for Streams errors
SQL> SELECT count (*) from dba_apply_error;
Check for Archive Log Mode
SQL> archive log list
Check free space
$ df -h (Solaris)
$ bdf (HP-UX)
Check for backup in place
check with active backup mechanism
RMAN> list backups;
Check for enough space for additional cold backup during migration
SQL> select TRUNC (sum(bytes)/1e9+1) db_size from dba_data_files;
Check for internal / evil parameters
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
Investigate necessarity of network connections
Are there any network connections to be initiated from within the database ?
With DB 11g, network ACLs (access control lists) have been introduced.
Network connections and usage of certain packages like UTL_SMTP or UTL_TCP are restricted ba ACLS. If these are present in the 10g source databasem the ACLs have to be defined.
For example: connections to an external mail server.
To be performed during the migration date.
Get AWR report
Get an AWR report of, say, the last two weeks, to have performance related numbers in case a comparison is necessary after the upgrade.
Adjust SYSTEM ans SYSAUX tablespaces
Have at least 256MB available on SYSTEM ans SYSAUX tablespaces.
SQL> select file_id, file_name, bytes/1024/1024 from dba_data_files where tablespace_name like 'SYS___';
Or, in case of ASM:
SQL> alter tablespace SYSAUX add datafile size 2G;
Adjust memory-related parameters
Both SGA_TARGET and SGA_MAX_SIZE are recommended to have 750M as target size, usually.
SQL> show sga
In case DBUA crashes with error "sga_target is bigger then sga_max_size", it has been useful to set SGA_MAX_SIZE bigger, like 1G, disregarding the meaning of the error message...
Adjust JAVA_POOL_SIZE and Sharted Pool Size:
SQL> show parameter _pool_size
Parameters for RAC
If migration a Real Application Cluster database, make sure the parameter "cluster_database" is set when migrating via DBUA.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- -----
cluster_database boolean TRUE
SGA should at least be 1600MB.
SQL> show sga
Total System Global Area 838860800 bytes
Fixed Size 2055824 bytes
Variable Size 473956720 bytes
Database Buffers 348127232 bytes
Redo Buffers 14721024 bytes
Create a spare pfile.
SQL> create pfile='/tmp/initOPS.ora' from spfile;
Make sure there is a parameter file in $ORACLE_HOME/dbs, which is pointing to, if possible, the spfile inside ASM.
$ cat /oracle/dbsystem/product/rdbms/10.2_1/dbs/initops1.ora
Take an additional backup of the oratab.
Save the output of:
srvctl status service –d <DB_NAME>
Unset the Oracle environment
Check for dependencies to ACLs
|SQL> SELECT DISTINCT owner FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');|
For these schemas, network ACLs may have to be created after the upgrade.
Purge Recycle Bin
SQL> PURGE DBA_RECYCLEBIN;
Recompile invalid objects
Create dictionay statistics
In Oracle 10g:
SQL> EXECUTE dbms_stats.gather_dictionary_stats
In Oracle 9i:
(userschema => 'SYS',
options => 'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE);
Restart the database to make to parameter changes active.
You max cross-check you are connect to the correct database before:
SQL> select count(*) from v$session;
SQL> select * from global_name;
SQL> select version from v$instance;
To restart a single instance database:
SQL> shutdown immediate
To restart a RAC database:
srvctl status database –d <DB_NAME>
Run Pre Upgrade script
Run Pre-Upgrade-Script from Ortacle 11g home.
Analyse the output.
Most of the tips told here have already been taken care of.
will be taken care of later by the Upgrade Assistant.
Take care when Underscore Parameters are found, i.e.:
"Oracle recommends removing all hidden parameters prior to upgrading."
SQL> alter system reset "_evil=full" scope=spfile sid='*';
Note invalid objects
Take a note of still invalid objects for later reference.
SQL> spool invalid_objects_10g.log;
Check for periodic OS jobs
Check for UNIX jobs ruinning and affecting the database, and un-schedule them.
$ crontab -l | grep -i $ORACLE_SID
Create a directory for cold backup
Create a directory for the cold backup the Upgrade Assistant will perform later.
$ mkdir /$USER/oradata3/"$ORACLE_SID"_upgrade11
Drop some synonyms which trigger certain bugs:
SQL> drop public synonym 'XMLCONCAT';
If this sysnonym was present, also issue:
SQL> alter package DBMS_SQLTUNE_INTERNAL compile body;
see also: MOS note: 1271490.1
Set 11g Oracle Home
Make sure you are connected with X11 forwarding / tunneling.
|ubuntu$ ssh -Y oracle@orcl1|
orcl1$ xlogo &
|export ORACLE_HOME=/oracle/dbsystem/product/11.2_3export PATH=$ORACLE_HOME/bin:$PATH|
|$ which dbua|
If the tools do not start with Java Null Pointer Exception, try setting ulimit.
See Java Null Pointer Exception.
Select database to migrate
Ignore the invalid objects warning
The messages shown should correspond to the results we got earlier in the pre-migration phase.
Select upgrade options
Do not necessarily select the most extensive degree of parallelism.
You can turn off archiving during upgrade to gain performance, since the database is backued up just before the upgrdae.
But do not do this if Streams is using in DB.
Do upgrade the timezone thing.
Backup the database and select the directory you create before.
Move Database Files
Decide on wether you want to migrate data to ASM.
Recovery and Diagnostic Locations
Decide on wether you want to introduce a Fast Recovery Area (FRA), which is recommended.
Decide on wether to configure Database Control (aka Enterprise Manager), or let your database join Grid Control, which is recommended in case you do have an installation.
Decide wether you want to register the database to any directory (if you have one), and which Listeners you want to be configured automatically.
You can configure the Listeners later, manually.
In this case, select the Listeners tab and select "Register the database with selected listeners only" and do not select any listeners.
Review the summary
Review the summary page and Finish the upgrade.
Errors doring DBUA run
IDENTIFIER SYS.DBMS_JAVA MUST BE DECLARED
11GR2 DBUA ORA-06550 PLS-00201 IDENTIFIER SYS.DBMS_JAVA MUST BE DECLARED
See MOS note: ID 1066828.1
When upgrading from 10.2.0.4 to 220.127.116.11, the following error is returned:
ORA-06550: line 1, column 7
Cause: Bug 9315778 - DBUPGRADE: ORA-06550 SYS.DBMS_JAVA NOT DECLARED
Ignore the error if the upgrade is still running or completed, or install patch 9315778 before running the upgrade
sga_target is bigger then sga_max_size
Restore the backup with DBUA, set SGA_MAX_SIZE way bigger then SGA_TARGET and retry DBUA.
Review the Results
Review the DBUA Upgrade Results page and Close DBUA.
Review the entry in Oratab (/etc/oratab or /var/opt/oratab) DBUA has created, and check it reflects the correct settings regarding autostart und Oracle Home.
Post Upgrade Script
Login with a fresh shell and review your Unix environment is set correct.
Run Sqlplus and see everything looks correct.
Run the Post Upgrade Script:
Oracle Database 11.2 Post-Upgrade Status Tool 02-15-2012 16:14:17
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server VALID 18.104.22.168.0 00:14:46
Gathering Statistics 00:01:05
Total Upgrade Time: 00:15:52
PL/SQL procedure successfully completed.
Recompile any invalid objects again.
|SQL> select comp_name, version, status from dba_registry;|
SQL> select * from dba_objects where status='INVALID';
Compare invalid objects with list created pre upgrade.
Check Listener registration DBUA performed, or create them manually if you skipped DBUA Listener configuration.
Check which listeners are running:
oracle ~$ ps -ef | grep lsnrctl
|$ vi $TNS_ADMIN/listener.ora$ lsnrctl reload LISTENER11$ lsnrctl status LISTENER11 | grep $ORACLE_SID|
Use the name of your listener.
Sometimes, the Listener refuses to add a new entry without complanining.
Stop and start the Listener in these cases.
If you have different Listeners for 10g and 11g installations on your machine, you have to reload both listeners.
Review the directory registration DBUA did, if you configured it do do any.
Optionally, register the database with OID manually using netmgr.
In netmgr, numeric keys and backspace key do not work.
Pay special attntion the correct port is listed.
Restart the database to get the SPFile right:
SQL> shutdown immediate
Set COMPATIBLE parameter, and verify wether LOCAL_LISTENER is needed.
SQL> alter system set compatible='11.2.0' scope=spfile;
Use srvctl in case of RAC.
LOCAL_LISTENER may not be needed if Data Guard is not in place.
From the moment you set COMPATIBLE to 11.x no downgrade is possible any more.
It is possible to let COMPATIBLE be 10.x for test purposes.
Verify the Default Profile is set accoding to your local policies.
Create any 11g Profiles you might have in your enterprise for security purposes, like e.g. password verification.
|ALTER PROFILE "DEFAULT" LIMIT SESSIONS_PER_USER UNLIMITED|
CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED IDLE_TIME UNLIMITED
COMPOSITE_LIMIT UNLIMITED PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 28
You may optionally reset all accounts to have a fresh starting point in time for password aging:
SQL> select 'ALTER USER '||u.name||' IDENTIFIED BY VALUES ||u.password||;'
Fixed Table Stats
Renew Fixewd Table Statistics:
SQL> execute dbms_stats.gather_fixed_objects_stats;
Verify chnaged done by DBUA to local TNS configuration or edit TNSNames configuration manually.
$ vi $TNS_ADMIN/tnsnames.ora
If you disabled any external (cron-) jobs during migration, you may no re-enable them now.
Setup any Network ACLs, if defined.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'ippst.xml',
description => 'Permission for IPPS_STAMM user',
principal => 'IPPS_STAMM',
is_grant => true,
privilege => 'connect',
start_date => null,
end_date => null);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ippst.xml',
principal => 'IPPS_STAMM',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'ippst.xml',
host => 'mailgate.xyz.com',
lower_port => null,
upper_port => null);
Perform a last general check through all layes of configuration.
$ tnsping OPS
$ sqlplus scott/tiger@OPS
SQL*Plus: Release 22.214.171.124.0 Production on Fri Nov 25 12:49:41 2011
I the new 11g database system can be build in parallel on separate hardware, Export / Import (via Data Pump) is a possible migration scenario.
It is very helpful for the scenario to have proper create scripts for the database schemas and objects.
Tablespaces and Schemata are recommended to be created before any import is done.
Is is alos possible to do a full database import from Oracle 10g to 11g, but this is not recommended, since SYS objects are often also imported accidentially.
Install new 11g database software home and install a new Custom database using custom scripts or DBCA.
Create Tablepspaces as in source database.
See Generate Create Tablespace DDL.
See Free.sql also to control available space.
SQL> select 'CREATE DIRECTORY ' || directory_name || ' as || directory_path ||*;'
Generate a pfile from both databases and compare.
SQL> create pfile='/tmp/db1.ora' from spfile;
Global Names has consequences on the way database links are resolved.
If you get ORA-02085 after migration, try resetting this parameter.
In many cases, these parameters have to be reset:
SQL> alter system reset control_file_record_keep_time scope=spfile;
SQL> create directory OPS_MIG as '/tank/spool/opsmis';
$ cat ops.par
Restart Sorce database in Restricted Mode to prevent users from logging in if schedules and possible.
SQL> shutdown immediate;
In RAC environments, use srvctl to shutdown all nodes, of course.
$ srvctl stop database -d OPS
$ expdp \"/ AS SYSDBA\" parfile=ops.par
You can schedule this with "at", but take care of the environment.
Move the dump file to the new system, using scp or ftp or cp.
$ vi ops-imp.par
Compare and check all database links.
Check for all TNS configuration entries on the source DB being necessary for database links on target DB.
Transfer all external Jobs and Programs necessary to the new system.
Check wether all users and schemas have been transfered.
Compare all objects and there validity.
SQL> select count(*) from dba_objects;
Check private and public synonyms.
This is the preferred scenario for upgrades in HA-environments where parallel build of the 11g system is possible hardware-wise and downtime shall be minimal.