Table of Contents
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)3. Unzip the downloaded files using the following commands
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
4. Unset the ORACLE_HOME and TNS_ADMIN
- cd /u01/app/oracle/SOFTWARE/DATABASE_11G
- unzip solaris.sparc64_11gR2_database_1of2.zip
- unzip solaris.sparc64_11gR2_database_2of2.zip
5. Start x-server.
- unset ORACLE_HOME
- unset TNS_ADMIN
6. Start the installer.
7. Follow the instructions as demonstrated in the Example Install section below.
- cd /u01/app/oracle/SOFTWARE/DATABASE_11G/database
- export DISPLAY= Enter Your IP Address
- ./runInstaller
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.
10. Download the Latest Upgrade Check Script from Metalink article 884522.1
- . 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;
11. Perform an upgrade check on the exiting 10g database.
12. Review the output and resolve any issues.
- . 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
13. Start the listener
14. Start DBUA.
- . oraenv
- ORACLE_SID = [oracle] ? SID
- lsnrctl start
15. Follow the instructions as demonstrated in the Example Upgrade section below.
- cd /u01/app/oracle/product/11.2.0/dbhome_1/bin
- export DISPLAY= Enter Your IP Address
- dbua
16. Update the Oracle .profile
17. Create the admin directories
- Update the .profile using the listing below as a template.
- Log off and log back onto Oracle to pick up the new .profile
18. Reconfigure the new listener
- cd /u01/app/oracle/admin/SID
- mkdir pfile
- mkdir audit
19. Create new spfile
- . oraenv
- ORACLE_SID = [oracle] ? SID
- lsnrctl stop
- lsnrctl start
20. Remove the old binaries
- . 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
21. Check the database is registered with the listener
- rm -rf OLD_ORACLE_HOME
22. Ensure DST update is not in progress
- lsnrctl status
23. Prepare the DST Timezone Update
- . 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
24. Ensure the prepare has completed successfully.
- . 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.
25. Clear out the DST upgrade logging tables prior to checkinng for affected data
- . 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
26. Check for data that may be affected by the DST update
- . 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;
27. End the prepare state in preparation for the upgrade.
- . 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.
28. Upgrade the DST timezone.
- . 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
29. Check for Depricated, non-default parameters.
- . 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
30. Disable the default 11g maintenance jobs if required.
- 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.
31. Run Gather Stats against 'SYS.
- 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> /
- 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
- 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
1 comment:
Post a Comment