Hello Friends,
I have tested the below in my environment and i was able to upgrade the database to 12.1.0.2. This upgrade done for oracle E-biz R12.2 so it covers lot additional steps for application tier as well. Please refer these steps only if you are upgrading for E-business suite database.
a) Take a backup using RMAN
(or)
b) Perform Cold Backup ( if your database is in NOARCHIVELOG mode)
--> db_backup_directory >> Location of the Database backup.
--> controlfile_backup_directory >> Location of the Controlfile backup.
•
Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.
•
Ensure that you do not have duplicate objects in the SYS and SYSTEM schema.
SQL> SELECT dbms_stats.get_prefs('CONCURRENT') from dual;
When 'concurrent statistics gathering' is not not set to 'FALSE', change the value to 'FALSE before the upgrade.
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/
Scripts placed at /a02/upgrade_files_12c
cd <location of the script>
$ sqlplus / as sysdba
sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit
Review the report
$ORACLE_HOME/rdbms/admin/utlrp.sql
Please run health check scripts as recommended by oracle. Note 136697.1 hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g
Script - /a02/upgrade_files_12c
Note 884522.1 How to Download and Run Oracle's Database Pre-Upgrade Utility
@$ORACLE_HOME/rdbms/admin/preupgrd.sql
Review the report and fix the errors.
Take a backup of db links. Copy the output and keep it safer.
SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
2 3 ||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
4 5 ||chr(10)||';' TEXT
6 FROM SYS.LINK$ L, SYS.USER$ U
7 WHERE L.OWNER# = U.USER#;
TIMESTAMP WITH TIME ZONE Data Type –Conditional
DST 18 upgrade. This Can be done as part of post upgrade.
Ref : 1665676.1
Optimizer Statistics
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Run the following query to determine if there are any materialized view refreshes still in progress:
SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
Ensure That No Files Need Media Recovery Before Upgrading
SELECT * FROM v$recover_file;
Ensure That No Files Are in Backup Mode Before Upgrading, make sure no files are in backup mode.
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
Purge the Database Recycle Bin Before Upgrading :
SQL> PURGE DBA_RECYCLEBIN
NOTE : The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors and to minimize the upgrade time.
Verify SYS and SYSTEM Default tablespace
Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.
You must have sufficient space in the 'SYSTEM' tablespace or set extents to unlimited.
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
Check whether database has any externally authenticated SSL users
select name from sys.user$ where ext_username is not null and password='GLOBAL';
Location of datafiles, redo logs and control files
Note the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;
Review and Remove any unnecessary hidden/underscore parameters
SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
Check the XDB ACLs has start_date and end_date ACE attributes
Before upgrading the database to 12c, please run the below query as SYS:
SQL> select aclid, start_date, end_date from xds_ace where start_date is not null;
Download and Install Oracle 12c Release 1 in a new Oracle Home and make sure there are no relinking errors.
Install database examples which is mandatory step for ebs. You wont have oracle database examples with 12c stage, you need to download examples through patch p21419221_121020_platform_9of10.zip.
Install the latest available Patchset from My Oracle Support (if available).
Install the latest opatch available for your platform and database version (if available).
Install the latest available Critical Patch Update (if available).
Copy following configuration files from the $ORACLE_HOME of the database being upgraded to the new Oracle Home for Oracle Database 12c
- Parameter file (spfile or pfile)
- Password file (orapwsid)
Remove or comment out obsolete and deprecated initialization parameters.
Stop the listener for the database.
$ lsnrctl stop
After 12c oracle home installation
Execute the below in source:
@?/rdbms/admin/utluppkg.sql
SET SERVEROUTPUT ON;
exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA')
SET SERVEROUTPUT OFF;
Save Database Control Files and Data with the emdwgrd Utility
$ emdwgrd -save -sid old_SID -path save_directory
if upgrading database from 11.2.0.3
It is recommended to apply the patch 21550777 on 12.1.0.2 ORACLE_HOME before starting the upgrade to Materialized views related issues.
/d02/DEV/oratest/12.1.0/OPatch/opatch apply
21550777
18966843
19291380
19393542
19472320
19627012
19649152
19779059
19835133
19896336
20093776
20181016
20204035
20294666
20798891
20830911
20887355
21153266
22223463
18793246
19591608
25643931 (19908836)
20766180
21321429
21864513
21904072
21967332
Footnote 2 - If a conflict is reported with Patch 18893947 roll back the conflicting patch before applying Patch 21967332.
22098146
22338374
Footnote 3 - If a conflict is reported with Patch 21188532 roll back the conflicting patch before applying Patch 22338374.
22496904
22731026
23089357
23595848
24481723
24560906
FOotnote 4 - If a conflict is reported with Patch 21286665 roll back the conflicting patch before applying Patch 24560906.
24911308
Footnote 5 - If a conflict is reported with Patch 23745950 roll back the conflicting patch before applying Patch 24911308.
20369415
20880215
Apply Jan cpu 2015
Rollback the patches below
20369415
20766180
21321429
21967332
22731026 (Not in oh)
24684434 (not in oh)
25154882 (Not in oh)
24326444 (Not in oh)
25203714 (Not in oh)
23595848
24481723
24732088
24911308
25643931
Applied Jan 2017 cpu
24732088
cd /a02/patches/12c_upg_files/24917987/24968615/24732088
opatch apply
25101514
24917972 JVM
Applied rolled back patches
20369415
20766180
21321429
21967332
22731026
24684434
25154882
24326444
25203714
$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Install JRE 6 or 7 (Copy appsutil directory from source to target oracle home)
Ensure Applications patching cycle is complete
Shut down Applications server processes and database listener
Drop SYS.ENABLED$INDEXES (conditional)
Remove the MGDSYS schema (conditional)
•
Create a new listener for Oracle Database 12c Release 1 (12.1) database
•
Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
bash-3.2$ export ORACLE_HOME=/d02/DEV/oratest/12.1.0/
bash-3.2$ export PATH=$ORACLE_HOME/bin:$PATH
bash-3.2$ export ORACLE_BASE=/d02/DEV/oratest
•
Update the oratab entry to set the new ORACLE_HOME pointing to ORCL and disable automatic startup.
Sample : cat /etc/oratab
#orcl:/opt/oracle/product/11.2/db_1:N
orcl:/opt/oracle/product/12.2/db_1:N
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
SQL> exit
Run the catctl.pl script from the new Oracle home.
In this release, the new Upgrade Utility, catctl.pl, replaces catupgrd.sql.
To run catctl.pl on Linux:
Example: Where parallelism is 6 ( n=6)
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
103861
Post upgrade
Please create stats on fixed objects two weeks after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
DST 18 upgrade
After the upgrade to 12.1.0.2 you can:
* (recommended) update the 12.1.0.2 database(s) to DSTv18 (standard DST version of 12.1.0.2) by :
•
or using the upg_tzv_check.sql and upg_tzv_apply.sql scripts in note 1585343.1 Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database .
•
or following note 1509653.1 Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST from step 3a) using "18" as (<the new DST version number>) in that note.
* (optional) update to a higher DST than 18 version if this is needed.
The latest DST patch and related note on how to apply this is found in Note 412160.1 Updated Time Zones in Oracle Time Zone File patches under "C) Notes covering the current DST available updates".
Simply follow the note for the latest DST update
Conn / as sysdba
-- this gives the current RDBMS DST version
SELECT version FROM v$timezone_file;
-- check also
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
Conn / as sysdba
spool countstatsTSTZ.log
@countstatsTSTZ.sql
spool off
For most databases the biggest amount of data that is affected by DST updates will be in DBMS_SCHEDULER tables.
Conn / as sysdba
exec dbms_scheduler.purge_log;
Conn / as sysdba
-- check current nr of rows in HISTHEAD / HISTGRM
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
-- check the current retention of stats
-- the default value is 31
select systimestamp - dbms_stats.get_stats_history_availability from dual;
-- now disable stats retention
exec dbms_stats.alter_stats_history_retention(0);
-- remove all stats
exec DBMS_STATS.PURGE_STATS(systimestamp);
-- check result of purge
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
-- AFTER the DST update you can set the retention back to the or
Conn / as sysdba
spool upg_tzv_check.log
@upg_tzv_check.sql
spool off
A succesfull run will show at the end:
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Conn / as sysdba
spool upg_tzv_apply.log
@upg_tzv_apply.sql
spool off
A succesfull run will show at the end:
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
To see what's happening during upg_tzv_check.sql or upg_tzv_apply.sql one can use:
CONN / as sysdba
set PAGES 1000
select TARGET, TO_CHAR(START_TIME,'HH24:MI:SS - DD-MM-YY'), TIME_REMAINING, SOFAR,
TOTALWORK, SID, SERIAL#, OPNAME from V$SESSION_LONGOPS
where sid in
(select SID from V$SESSION where CLIENT_INFO = 'upg_tzv')
and SOFAR < TOTALWORK
order by START_TIME;
select S.SID, S.SERIAL#, S.SQL_ID, S.PREV_SQL_ID,
S.EVENT#, S.EVENT, S.P1TEXT, S.P1, S.P2TEXT, S.P2, S.P3TEXT, S.P3, S.TIME_REMAINING_MICRO,
S.SEQ#, S.BLOCKING_SESSION, BS.PROGRAM "Blocking Program",
Q1.SQL_TEXT "Current SQL", Q2.SQL_TEXT "Previous SQL"
from V$SESSION S, V$SQLAREA Q1, V$SQLAREA Q2, V$SESSION BS
where S.SQL_ID = Q1.SQL_ID(+)
and S.PREV_SQL_ID = Q2.SQL_ID(+)
and S.BLOCKING_SESSION = BS.SID(+)
and S.CLIENT_INFO = 'upg_tzv';
Fix for ORA-01804: failure to initialize timezone information:
This is resolved by editing the template ad8idbux.env in location .../oracle/db/tech_st/11.2.0/appsutil/template changing:
From:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg.dat
To:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg_17.dat
exec dbms_stats.alter_stats_history_retention(31);
After the upgrade, run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.
@$ORACLE_HOME/rdbms/admin/utluiobj.sql
20880215
AND perform one of the following as the table is already logically corrupted
a. Reinitialize the columns in the table:
alter table <owner>.<table_name> modify ( <column_name> null);
alter table <owner>.<table_name> modify ( <column_name> not null);
OR
b. Drop and recreate the affected table(s) ("schema"."table")
JAN CPU 2017 - Patch post installation instructions.
Is it enough if you run datapatch once for all patches. Though catctl.pl will cover this step. We can execute datapatch once if needed.
% ./datapatch –verbose
1.
If the OJVM PSU is also installed, you may see invalid objects after execution of datapatch in the previous step. If this is the case, run utlrp.sql to revalidate these objects.
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
2.
SQL> CONNECT / AS SYSDBA
3.
SQL> @utlrp.sql
4.
This patch now includes the OJVM Mitigation patch (Patch:19721304). If an OJVM PSU is installed or planned to be installed, no further actions are necessary. Otherwise, the workaround of using the OJVM Mitigation patch can be activated. As SYSDBA do the following from the admin directory:
5.
SQL > @dbmsjdev.sql
6.
SQL > exec dbms_java_dev.disable
For more information on the OJVM mitigation patch, see Document 1929745.1 Oracle Recommended Patches -- "Oracle JavaVM Component Database PSU" (OJVM PSU) Patches.
2.6.3 Upgrade Oracle Recovery Manager Catalog
If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
RMAN> UPGRADE CATALOG;
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action,
version,
status,
bundle_series from sys.dba_registry_sqlpatch order by action_time;
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the database server node.
sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql)
[APPS schema name]
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/adgrants.sql APPS
Connected.
Set CTXSYS parameter
$ sqlplus "/ as sysdba"
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
Validate Workflow ruleset
On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:
$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\
[APPLSYS user] [APPS user]
Deregister the current database server (conditional)
If you plan to change the database port, host, or database name parameter on the database server, you must also update AutoConfig on the database tier and deregister the current database server node.
$ sqlplus apps/[APPS password]
SQL> exec fnd_conc_clone.setup_clean;
Implement and run AutoConfig
Enabling AutoConfig on a New Oracle Home
Install Java Runtime Environment (JRE) on the Database tier
** copy entire appsutil directory from source to target oracle home
The JRE resides in the <ORACLE_HOME>/appsutil/jre directory on the database
tier.
3. Generate the Database Context File
$ perl <RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl
4. Run AutoConfig on the Database tier
Run AutoConfig on the database tier by executing one of the following commands:
On UNIX:
$ <RDBMS_ORACLE_HOME>/appsutil/bin/adconfig.sh \
contextfile=<context_file>
References :
Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)
Interoperability Notes Oracle EBS 12.2 with Oracle Database 12c Release 1 (Doc ID 1926201.1)
