What are the possible Oracle upgrade paths?
To 10.2.0.4
Current version Interim version Target version
9.2.0.3 (or earlier) 9.2.0.8 10.2.0.410.2.0.2 -> 10.2.0.4
10.2.0.3 -> 10.2.0.4
To 11.2.0.1
Current version Interim version Target version
9.2.0.3 (or earlier) 9.2.0.8 11.2.0.110.2.0.2 -> 11.2.0.1
10.2.0.3 -> 11.2.0.1
10.2.0.4 -> 11.2.0.1
To 11.2.0.2
Current version Interim version Target version
9.2.0.3 (or earlier) 9.2.0.8 11.2.0.2 10.2.0.2 -> 11.2.0.2
10.2.0.3 -> 11.2.0.2
10.2.0.4 -> 11.2.0.2
11.2.0.1 -> 11.2.0.2
To 11.2.0.3
Current version Interim version Target version
9.2.0.3 (or earlier) 9.2.0.8 11.2.0.3 10.2.0.2 -> 11.2.0.3
10.2.0.3 -> 11.2.0.3
10.2.0.4 -> 11.2.0.3
11.2.0.1 -> 11.2.0.3
11.2.0.2 -> 11.2.0.3
How to Upgrade Oracle Database 10.1.0.4 to 11.2.0.3 full steps?
Follow below Steps one by one.
1. Current database version is 10.2.0.4.0 64 Bit
2. Install 11.2.0.3 Software on current server
3. Direct upgrade to 11.2.0.3 Patch Set 2
1. Download 11.2.0.3 software from
https://support.oracle.com/epmos/faces/PatchSearchResults?_adf.ctrl-state=3n90j2bi_9&_afrLoop=524858892182336
Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
Platform: Linux x86-64
Size 5 G
p10404530_112030_Linux-x86-64_1of7.zip 1.3 GB
p10404530_112030_Linux-x86-64_2of7.zip 1.1 GB
p10404530_112030_Linux-x86-64_3of7.zip 933.8 MB
p10404530_112030_Linux-x86-64_4of7.zip 628.7 MB
p10404530_112030_Linux-x86-64_5of7.zip 587.9 MB
p10404530_112030_Linux-x86-64_6of7.zip 457.7 MB
p10404530_112030_Linux-x86-64_7of7.zip 108.6 MB
Download above software from Oracle Support and keep in a staging location
2. Check Kernel version & OS Version
2.6.32 or later
uname –a (Use this command to check Kernel version and OS version
3. Create new ORACLE_HOME location
mkdir -p /u01/oracle/UAT12/11.2.0.3
4. Set Oracle Inventory
Edit /etc/oraInst.loc to point to /u01/oracle/UAT12/oraInventory
mkdir -p /u01/oracle/UAT12/oraInventory
5. Install 11.2.0.3 Enterprise edition Oracle software
Enable VNC, Enter following command on VNC to start OUI for installation
/u01/stage/11203_sw/runInstaller
Use new ORACLE_HOME for 11.2.0.3 as /u01/oracle/UAT12/11.2.0.3
6. Install Oracle Database 11g Products from the 11g Examples CD
Enable VNC, Enter following command on VNC to start Example Installer
/u01/stage/11203_sw/examples/runInstaller
7. Set the environment
export ORACLE_HOME = /u01/oracle/UAT12/11.2.0.3
export PATH = $ORACLE_HOME/bin: $ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib.
export PERL5LIB= $ORACLE_HOME/perl/lib: $ORACLE_HOME/perl/lib/site_perl
8. Create nls/data/9idata directory
$ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
After creating the directory, make
sure that the ORA_NLS10 environment variable is set to the full path of
the 9idata directory whenever you enable the 11g Oracle home.
9. Apply recommended Patches for 11.2.0.3 ORACLE_HOME
Patch 14275605: DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)
Use Opatch to apply above patch
10. Apply 11.2.0.3 Performance patches:
Patch:14267798 Merge patch fixing the following two bugs:
• Document:13004894.8 Wrong results with SQL_TRACE / 10046
• Document:13743357.8 Wrong results on first execution
Patch:11072246 Wrong Cardinality estimations for columns with DESC indexes
11. Set the environment to Existing 10.2.0.4 Environment
Set SID,ORACLE_HOME,PATH,LD_LIBRARY_PATH
login to UAT12 database
12. Run Pre-Upgrade Information Tool in 10.2.0.4 database
sqlplus '/ as sysdba'
spool pre_upg_11203_info.log
@/u01/oracle/UAT12/11.2.0.3/rdbms/admin/utlu112i.sql
spool off
Check
the output of the Pre-Upgrade Information Tool in upgrade_info.log
Run
utlrp.sql to fix invalid objects or components issue
Important: If you see a warning
about the presence of the release 10g DMSYS schema in the database, then
you must drop the DMSYS schema before proceeding with the upgrade.
13. Check
timezone version of 10.2.0.4 database and 11.2.0.3 and compare if both are same
SQL>
SELECT version FROM v$timezone_file;
VERSION
---------
4
* For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5
there is no need to apply any patchset before upgrading to 11.2.0.3
Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.
Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.
* (recommended) update the 11.2.0.3 database(s) to
DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the
RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step
3a) using "14" as (<the new DST version
number>) in that note. When going to DSTv14 there is no need to apply any
DST patch to the 11.2.0.3 home.
14. Upgrade
11.2.0.1 to 11.2.0.3 using Manual upgrade method
15. Get
DBA_REGISTRY information
sqlplus '/ as sysdba'
select
COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;
16. Purge
Recycle bin
PURGE DBA_RECYCLEBIN;
17. Gather
Dictionary stats
EXECUTE
dbms_stats.gather_dictionary_stats;
18. Shutdown
UAT12 database listener and database
shut immediate
lsnrctl
stop UAT12
19. Copy of parameter files and edit for upgrade
Copy the init parameter file from 10.2.0.4 ORACLE_HOME/dbs into 11.2.0.3 ORACLE_HOME/dbs
Adjust the parameter file for the upgrade
Adjust initialization parameters that might cause upgrade problems.
• Remove obsolete initialization parameters based on pre-upgrade tool output
• Set the COMPATIBLE parameter if not already explicitly set
Adjust the parameter file for the upgrade
Adjust initialization parameters that might cause upgrade problems.
• Remove obsolete initialization parameters based on pre-upgrade tool output
• The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST
• Set the COMPATIBLE parameter if not already explicitly set
If you are upgrading from 10.1.0.x
or 10.2.0.x then you can leave the COMPATIBLE parameter set to it's
current value until the upgrade has been completed successfully. This
will avoid any unnecessary ORA-942 errors from being reported in SMON
trace files during the upgrade (because the upgrade is looking for 10.2
objects that have not yet been created).
grep -i comp initUAT12.ora
*.compatible = 10.2.0.4.0
20. Prepare Oracle environment
export ORACLE_HOME = /u01/oracle/UAT12/11.2.0.3
export PATH = $ORACLE_HOME/bin: $ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib.
export ORACLE_BASE=/u01/oracle/UAT12/
21. Verify /etc/oratab
UAT12 should point to /u01/oracle/UAT12/11.2.0.3 as ORACLE_HOME
22. Run upgrade scripts in VNC session
cd $ORACLE_HOME/rdbms/admin
sqlplus '/ as sysdba'
STARTUP UPGRADE
Monitor the alert log file and check for any errors.
SPOOL upgrade_uat12_11203.log
@catupgrd.sql
The output is at:
/u01/oracle/UAT12/11.2.0.3_upgrade_uat12_11203.log
STARTUP
Post-Upgrade Status Tool.
@utlu112s.sql
Verify the results, and if there any errors, must be resolved at this point.
@catuppst.sql
Identifying Invalid Objects
Any invalid SYS/SYSTEM objects found before upgrading the database are stored in
the table named registry$sys_inv_objs. Any invalid non-SYS/SYSTEM objects
found before upgrading the database are stored in registry$nonsys_inv_objs.
To identify any new invalid objects due to the upgrade
After the upgrade, run ORACLE_HOME/rdbms/admin/utluiobj.sql
Compile invalids
@utlrp.sql
23. Post Upgrade Steps
24. Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]
A) Applying 11.2.0.3 on 11.2.0.2 or 11.2.0.1
Check the current version of the
11.2.0.2 or 11.2.0.1 Oracle RDBMS time zone definitions - this needs to
be done for ALL databases in the 11.2.0.2 or 11.2.0.1 home:
SQL> conn / as sysdba
Connected.
SQL>SELECT version FROM v$timezone_file;
VERSION
----------
14
A.1) The result is lower than 14 for all databases (typically 11 or 14)
Note : Every database in 11.2.0.1
home needs to be checked, if one or more has a result higher than 14 you
need to follow step A.3)
If your current RDBMS time timezone
version is lower than 14 , install 11.2.0.3 in a new home and update
the 11.2.0.2 or 11.2.0.1 database to 11.2.0.3
You can skip any DST related
sections in the patchset documentation , there is no need to apply DST
patches or check for DST issues for the update to 11.2.0.3
The 11.2.0.3 RDBMS DST version after the update to 11.2.0.3 will be the same as your 11.2.0.2 or 11.2.0.1 system.
After the upgrade to 11.2.0.3 you can
* (recommended) update to DSTv14
(standard DST version of 11.2.0.3) by following Note 977512.1 Updating
the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from
step 3a) onwards, when going to DSTv14 there is no need to apply any DST
patch to the 11.2.0.3 home
* (optional) update to a higher DST than DSTv14 version if this is needed.
The latest available DST patch is
found in Note 412160.1 Updated DST transitions and new Time Zones in
Oracle Time Zone File patches
A.2) The result is 14 for all databases
Note : Every database in 11.2.0.2 or
11.2.0.1 home needs to be checked, if one or more has a result higher
than 14 you need to follow step A.3)
If your current RDBMS time timezone
version is 14 , install 11.2.0.3 in a new home and update the 11.2.0.2
or 11.2.0.1 database to 11.2.0.2.
You can skip any DST related
sections in the patchset documentation , there is no need to apply DST
patches or check for DST issues for the update to 11.2.0.3
The 11.2.0.3 RDBMS DST version after the update to 11.2.0.3 will be 14.
(optionally) After the upgrade to
11.2.0.3 you can check if there is a newer DST version out and , if
needed, update every database to this DST version. The latest available
DST patch is found in Note 412160.1 Updated DST transitions and new Time
Zones in Oracle Time Zone File patches
A.3) The result is higher than 14 for one or more databases
If your current RDBMS time timezone
version is higher then 14, you need to re-apply the same RDBMS DST
version as found in step A) after installing the 11.2.0.3 software . Or
in other words, you need to apply the 11.2.0.3 RDBMS DST patch for the
same DST version before opening the database in the new 11.2.0.3
software home.
We suggest to apply the 11.2.0.3 RDBMS DST patch after installing the 11.2.0.3 software with the Oracle Universal installer.
If you also need to patch the OJVM
then we suggest to apply the latest DST OJVM fix for 11.2.0.3, even if
your current RDBMS DST patch is lower. This is best done right after
installing the RDBMS DST patch, no need to follow the OJVM DST patch
readme instructions as there is no database yet.
For the actual OJVM and RDBMS DST
patches for your 11.2.0.3 release please see Note 412160.1 Updated Time
Zones in Oracle Time Zone File patches
After installing the same RDBMS (and
the OJVM - which may be the lastest/higher than the RDBMS DST version)
DST patch in the 11.2.0.3 ORACLE_HOME upgrade to 11.2.0.3 without any
action on TSLTZ or TSTZ data. Follow the upgrade or patchset apply
instructions, you can skip any DST related sections.
The 11.2.0.3 RDBMS DST version after
the upgrade to 11.2.0.2 will be the same DST version as used in the
11.2.0.2 or 11.2.0.1 Oracle RDBMS.
(optionally) After the upgrade to
11.2.0.3 you can check if there is a newer DST version out and , if
needed, update every database to this DST version. The latest available
DST patch is found in Note 412160.1 Updated DST transitions and new Time
Zones in Oracle Time Zone File patches
25. Do the actual RDBMS DST version update of the database using DBMS_DST
conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
-- check if previous prepare window is ended
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
-- If DST_UPGRADE_STATE is "PREPARE" then you did not ended
-- the prepare window in step 3)
-- If there are objects containing TSTZ data in recycle bin,
-- please purge the bin now.
-- Otherwise dbms_dst.begin_upgrade will report "ORA-38301: Can not perform DDL/DML over objects in Recycle Bin".
purge dba_recyclebin;
-- clean used tables
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
-- this alter session might speed up DBMS_DST on some db's
-- this alter session might speed up DBMS_DST on some db's
-- see Bug 10209691
alter session set "_with_subquery"=materialize;
-- to avoid the issue in note 1407273.1
alter session set "_simple_view_merging"=TRUE;
-- start upgrade window
EXEC DBMS_DST.BEGIN_UPGRADE(14);
-- the message
-- "An upgrade window has been successfully started."
-- will be seen
-- check if this select
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- gives this output:
-- PROPERTY_NAME VALUE
-- --------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE
-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE
-- BEGIN_UPGRADE upgrades system
tables that contain TSTZ data and marks user tables (containing TSTZ
data) with the UPGRADE_IN_PROGRESS property.
-- even if this select gives no rows you still need to do to the rest of the steps
-- it simply gives an indication of how many user objects need to processed in the later steps
-- some oracle provided users may be listed here, that is normal
SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
-- restart the database
shutdown immediate
startup
alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
-- now upgrade the tables who need action
set serveroutput on
VAR numfail number
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;
/
-- ouput of this will be a list of tables like:
-- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
-- Number of failures: 0
-- ....
-- Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
-- Number of failures: 0
-- Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
-- Number of failures: 0
-- Failures:0
-- if there where no failures then end the upgrade.
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
-- output that will be seen:
-- An upgrade window has been successfully ended.
-- Failures:0
-- last checks
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- needed output:
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
SELECT * FROM v$timezone_file;
-- needed output:
-- FILENAME VERSION
-- ------------------ ----------
-- timezlrg_<new version>.dat <new version>
-- if registry$database exists then update this static table also with the new DST version
-- this table is used during upgrade and contians the DST version from before the upgrade
-- this update is mainly to avoid confusion when people notice this has a lower version
select TZ_VERSION from registry$database;
update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;
26. Configuring Fine-Granined Access to External Network services After upgrading Oracle database.
Create ACLs as per note id: 958129.1:
SQL> SELECT OWNER, REFERENCED_NAME FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); 2 3
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL();
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE();
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL();
COMMIT;
SQL> select * from dba_network_acls;
27. Upgrade Oracle Text - None
After an upgrade to the new Oracle
Database 11g release, copy the following files from the previous Oracle
home to the new Oracle home:
■ Stemming user-dictionary files
■ User-modified KOREAN_MORPH_LEXER dictionary files
■ USER_FILTER executables
These files affect all databases installed in the given Oracle home.
You can obtain a list of these files by doing the following:
1. Looking at $ORACLE_HOME/ctx/admin/ctxf102.txt
2. Executing $ORACLE_HOME/ctx/admin/ctxf102.sql as database user SYS,SYSTEM, or CTXSYS
28. Configure and Start new database listener
Get your listener.ora and tnsnames.ora files from Old Home to New home
Set the TNS_ADMIN environment variable to the directory where you got your listener.ora and tnsnames.ora files.
No comments:
Post a Comment