Background & Overview
The following documentation provides instructions for upgrading an Oracle 10.2.0.4 database to an Oracle 11.2 database residing on Sun Solaris 10.
Assumptions & Pre-Requisites
This document expects and assumes the following:
- The instructions are carried out by a qualified DBA.
- Access to the internet is available.
- All necessary client software, e.g. Telnet and X-Server is available.
- The resultant database will be standalone, i.e. not part of a cluster or failover configuration.
- All references to SID should be replaced with correct database name as derived using a suitable database naming standard.
- There is only ONE database installed on the server.
Useful Information
This section outlines useful information and changes to previous 10g documentation found on this site. Some have been made for cosmetic reasons, others for more practical considerations.
- A new directory structure exists. See below.
- A new init.ora parameter called diag_dest has been set and maps to the diag directory in the new directory structure mentioned above.
- A listener.ora file has a new parameter called ADR_BASE_LISTENER. This maps to the base of the diag directory.
- There are a number of new maintenance jobs in 11g. These can be turned off as part of this step-by-step guide.
- Read the Known Issues section below.
Step-By-Step Guide
1. Ensure the new directory structure exists. See below.
2. Download the binaries for the Oracle 11g Database into /u01/app/oracle/SOFTWARE/DATABASE_11G
Enterprise Edition for Oracle Solaris Operating System (SPARC) (64-bit)
solaris.sparc64_11gR2_database_1of2.zip := 1,373,903,591 bytes; cksum - 2779246655
solaris.sparc64_11gR2_database_2of2.zip := 1,048,619,225 bytes; cksum - 3179998886
3. Unzip the downloaded files using the following commands
- cd /u01/app/oracle/SOFTWARE/DATABASE_11G
- unzip solaris.sparc64_11gR2_database_1of2.zip
- unzip solaris.sparc64_11gR2_database_2of2.zip
4. Unset the ORACLE_HOME and TNS_ADMIN
- unset ORACLE_HOME
- unset TNS_ADMIN
5. Start x-server.
6. Start the installer.
- cd /u01/app/oracle/SOFTWARE/DATABASE_11G/database
- export DISPLAY= Enter Your IP Address
- ./runInstaller
7. Follow the instructions as demonstrated in the Example Install section below.
Before continuing apply any oneoff patches, patch bundles or CPU patch bundles recommended by Oracle or the application vendor. At the time of writing this guide, the following PSU is available: 9654983
8. If PSU 11.2.0.1.2 has been applied then patch 9315778 will need to be applied to avoid ORA-06550 druing the upgrade.
9. Ensure all Pre-Upgrade Requirements are Met.
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> @?/rdbms/admin/utlrp
- SQL> purge dba_recyclebin
- SQL> alter database datafile '/u03/oradata/SID/system/d1/system_01.dbf' resize 1000m;
10. Download the Latest Upgrade Check Script from Metalink article 884522.1
11. Perform an upgrade check on the exiting 10g database.
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> startup
- SQL> spool /tmp/upgrade_check.log
- SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlu112i.sql
12. Review the output and resolve any issues.
13. Start the listener
- . oraenv
- ORACLE_SID = [oracle] ? SID
- lsnrctl start
14. Start DBUA.
- cd /u01/app/oracle/product/11.2.0/dbhome_1/bin
- export DISPLAY= Enter Your IP Address
- dbua
15. Follow the instructions as demonstrated in the Example Upgrade section below.
16. Update the Oracle .profile
- Update the .profile using the listing below as a template.
- Log off and log back onto Oracle to pick up the new .profile
17. Create the admin directories
- cd /u01/app/oracle/admin/SID
- mkdir pfile
- mkdir audit
18. Reconfigure the new listener
- . oraenv
- ORACLE_SID = [oracle] ? SID
- lsnrctl stop
- lsnrctl start
19. Create new spfile
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- create pfile='/u01/app/oracle/admin/SID/pfile/initSID.ora' from spfile
- shutdown immediate
- quit
- cd /u01/app/oracle/admin/SID/pfile
- mv initSID.ora initSID.POST_UPGRADE
- Create a pfile using the listing below.
- cd $ORACLE_HOME/dbs
- rm spfileSID.ora
- rm initSID.ora
- ln -s /u01/app/oracle/admin/SID/pfile/initSID.ora
- sqlplus '/ as sysdba'
- startup
- create spfile='/u01/app/oracle/admin/SID/pfile/spfileSID.ora' from pfile;
- shutdown immediate
- quit
- cd $ORACLE_HOME/dbs
- rm initSID.ord
- ln -s /u01/app/oracle/admin/SID/pfile/spfileSID.ora
- cd /u01/app/oracle/admin/SID/pfile
- rm initSID.ora
- sqlplus '/ as sysdba'
- startup
- quit
20. Remove the old binaries
- rm -rf OLD_ORACLE_HOME
21. Check the database is registered with the listener
- lsnrctl status
22. Ensure DST update is not in progress
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> select property_name, substr(property_value, 1, 30) value from database_properties where property_name like 'DST_%' order by property_name;
- NB DST_SECONDARY_TT_VERSION should be set to 0
- NB DST_UPGRADE_STATE should be set to NONE
23. Prepare the DST Timezone Update
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> exec DBMS_DST.BEGIN_PREPARE(&version);
- NB For 11.2.0.1, the latest version is 11. For 11.2.0.2 the latest version is 14.
24. Ensure the prepare has completed successfully.
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> select property_name, substr(property_value, 1, 30) value from database_properties where property_name like 'DST_%' order by property_name;
- NB DST_SECONDARY_TT_VERSION should be set to the version being upgraded to. E.g. 11
- NB DST_UPGRADE_STATE should be set to prepare
25. Clear out the DST upgrade logging tables prior to checkinng for affected data
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> truncate table sys.dst$trigger_table;
- SQL> truncate table sys.dst$affected_tables;
- SQL> truncate table sys.dst$error_table;
26. Check for data that may be affected by the DST update
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> BEGIN DBMS_DST.FIND_AFFECTED_TABLES (affected_tables => 'sys.dst$affected_tables', log_errors => TRUE, log_errors_table => 'sys.dst$error_table'); END; /
- SQL> select * from sys.dst$affected_tables;
- NB This will show a list of tables affected by the DST update. Hopefully none.
- SQL> SELECT * FROM sys.dst$error_table;
- NB This will show type errors that occured in the rows reported in dst$affected_tables.
27. End the prepare state in preparation for the upgrade.
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> EXEC DBMS_DST.END_PREPARE;
- SQL> select property_name, substr(property_value, 1, 30) value from database_properties where property_name like 'DST_%' order by property_name;
- NB DST_SECONDARY_TT_VERSION should be set to 0
- NB DST_UPGRADE_STATE should be set to NONE
28. Upgrade the DST timezone.
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> shutdown immediate
- SQL> startup upgrade
- SQL> purge dba_recyclebin
- SQL> truncate table sys.dst$trigger_table;
- SQL> truncate table sys.dst$affected_tables;
- SQL> truncate table sys.dst$error_table;
- SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
- SQL> select property_name, substr(property_value, 1, 30) value from database_properties where property_name like 'DST_%' order by property_name;
- SQL> shutdown immediate;
- SQL> startup
- SQL> set serveroutput on
- SQL> VAR numfail number
- SQL> BEGIN DBMS_DST.UPGRADE_DATABASE(:numfail,parallel => TRUE,log_errors => TRUE,log_errors_table => 'SYS.DST$ERROR_TABLE',log_triggers_table => 'SYS.DST$TRIGGER_TABLE',error_on_overlap_time => FALSE,error_on_nonexisting_time => FALSE); DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); END; /
- SQL> VAR fail number
- SQL> BEGIN DBMS_DST.END_UPGRADE(:fail); DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail); END; /
- SQL> select * from v$timezone_file;
- NB This should report the new timezone file version. E.g. 11
29. Check for Depricated, non-default parameters.
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE' and isdefault='FALSE'
- NB This should return 0 rows. If not, migrate to a more suitable init.ora.
30. Disable the default 11g maintenance jobs if required.
- sqlplus /nolog
- SQL> connect sys as sysdba
- SQL> BEGIN
- SQL> dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL,window_name => NULL);
- SQL> dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
- SQL> dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
- SQL> END;
- SQL> /
31. Run Gather Stats against 'SYS.
- sqlplus /nolog
- SQL> connect sys as sysdba
- SQL> exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Example Install
- Un-tick the I wish to receive security updates via My Oracle Support box.
- Click Next.
- Click Yes to remain uninformed.
- Select Install database software only.
- Click Next.
- Ensure Single instance database installation is selected.
- Click Next.
- Ensure languages Englsih and English (United Kingdom) are selected.
- Click Next.
- Ensure Enterprise Edition is selected.
- Click Next.
- Set Oracle Base to /u01/app/oracle
- Allow Software Location to default
- Click Next.
- Ensure Database Administrator (OSDBA) Group is set to dba
- Ensure Database Operator (OSOPER) Group is set to dba
- Click Next
- This page may not be displayed.
- Review and fix any issues.
- Click Next
Note: The installer may report a failed check on OS Patch 124861-15. Its possible that this failed check can be ignored. Review Metalink document 969497.1
- Review the summary.
- If required save the response file by selecting Save Response File
- Click Finish
- This screen moves on automatically when complete.
- Ask Unix Administrator to run root.sh.
- Click OK.
- Click Close.
Example Upgrade
- Click Next.
- Select the database you wish to upgrade.
- Click Next.
- Review the warnings and fix if applicable.
- Click Yes.
- Ensure Recompile invalid objects at the end of upgrade is ticked.
- Turn off archiving if required. Mostly this will not be necessary.
- Ensure Backup database is NOT ticked.
- NB You should backup the database prior to this step using the normal backup method.
- Click Next.
- If you left archiving on, Click Yes to continue.
- Ensure Do Not Move Database Files as Part of Upgrade is selected.
- Click Next.
- Ensure Specify Flash Recovery Area is Un-Ticked.
- Ensure the Diagnostics Destination is set to /u01/app/oracle
- Click Next.
- Review the Database Upgrade Summary.
- Click Finish.
- Monitor the upgrade progress.
- NOTE This screen will move on automatically.
- Click Ignore.
- NOTE - Review the Known Issues section below regarding this error message.
- Review the Upgrade Results
- Click Close.
File Listings
Oracle .profile
#----------------------------------------------------------------------
# Configure Terminal Settings.
#----------------------------------------------------------------------
stty susp ^Z
stty quit ^C
stty erase ^?
export TERM=vt100-w
export ORACLE_TERM=vt100
#----------------------------------------------------------------------
# Configure Shell Settings.
#----------------------------------------------------------------------
set -o vi
export PATH=/bin:/usr/sbin:/usr/bin:/usr/local/bin:$PATH
export EDITOR=vi
export HOSTNAME=`hostname`
export PS1='$LOGNAME@$HOSTNAME:$ORACLE_SID> '
export TMPDIR=/tmp
export TEMP=/tmp
umask 022
#----------------------------------------------------------------------
# Configure Aliases.
#----------------------------------------------------------------------
alias ll="ls -la"
alias bdf="df -k"
#----------------------------------------------------------------------
# Configure Oracle Settings.
#----------------------------------------------------------------------
export ORACLE_BASE=/u01/app/oracle
export SQLPATH=$ORACLE_BASE/scripts/dba/sql
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_HOME_LISTNER=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORATAB=/var/opt/oracle/oratab
oratab
SID:/u01/app/oracle/product/11.2.0/dbhome_1:Y
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = <SID>)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = <SID>)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
initSID.ora
###########################################
# Cache and I/O
###########################################
db_block_size=8192
memory_target=1606418432
db_block_checking=MEDIUM
db_block_checksum='FULL'
###########################################
# Cursors and Library Cache
###########################################
open_cursors=500
###########################################
# Database Identification
###########################################
compatible='11.2.0.0.0'
db_name='SID'
###########################################
# Diagnostics and Statistics
###########################################
diagnostic_dest='/u01/app/oracle'
###########################################
# File Configuration
###########################################
control_files='+DATA/SID/control01.ctl','+DATA/SID/control02.ctl',
'+DATA/SID/control03.ctl'
###########################################
# Processes
###########################################
processes=1000
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace='UNDO'
###########################################
# Security and Auditing
###########################################
audit_trail='db'
remote_login_passwordfile='EXCLUSIVE'
###########################################
# Enabling Archivelog mode
###########################################
log_archive_dest='+FRA/SID/arch/d1'
log_archive_format=arch_%t_%s_%r.log
###########################################
# Defer Segment Creation
###########################################
deferred_segment_creation=FALSE
File Systems
/--- /var --- /opt --- /oracle
|
--- /u01 --- /app --- /oracle --- /software
| |
| --- /dba --- /scripts
| |
| --- /sql
| |
| --- /diag
| |
| --- /product --- /11.2.0 ---/dbhome_1
| |
| --- /admin --- /SID --- /adump
| |
| --- /create
| |
| --- /exp
| | (link to /u09)
| |
| --- /parfile
| |
| --- /pfile
| |
| --- /scripts ---/base ---/rfc
|
|
--- /u02 --- /oradata --- /SID --- /redo --- /d1
| |
| --- /redo --- /d2
|
--- /u03 --- /oradata --- /SID --- /indx --- /d1
| |
| --- /sysaux --- /d1
| |
| --- /system --- /d1
| |
| --- /tools --- /d1
| |
| --- /users --- /d1
|
--- /u04 --- /oradata --- /SID --- /undo --- /d1
|
--- /u05 --- /oradata --- /SID --- /temp --- /d1
|
--- /u06 --- /oradata --- /SID --- /arch --- /d1
|
--- /u07 --- /oradata --- /SID --- /data --- /d1
|
--- /u08 --- /oradata --- /SID --- /index --- /d1
|
--- /u09 --- /oradata --- /SID --- /exp --- /d1
|
--- /u10 --- /oradata --- /SID --- /flash --- /d1
Known Issues
Error ORA-06550 During Upgrade.
Description
Whilst running the upgrade you may encouner an ORA-06550 as detailled in Metalink article 1066828.1.
Fix
This error can be ignored. However, you can avoid the error by applying patch 9315778 to the 11.2 binaries prior to the upgrade starting.