Source : 11.2.0.3
Target : 11.2.0.4
Application : R12.2.4
OS : Oracle solaris 5.10
Interoperability
Notes E-Business Suite Release 12.2 with Database 11g Release 2 (Doc ID 1623879.1)
Complete Checklist for Manual Upgrade to Oracle
Database 11gR2 (11.2) (Doc ID 837570.1)
Recommendations
for Source database
1) Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.
1) Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.
2) Ensure that you do not have duplicate
objects in the SYS and SYSTEM schema.
The following objects are permissible
duplicate objects:
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
Note: All these checks are done when you execute
step 3 (dbupgdiag.sql)
3) Disable the custom triggers that would
fire before/after DDL and enable them after the upgrade is complete.
Requirements
and recommendations for target database
- Check the certification of Oracle
11gR2 with your Platform/Operating system before downloading and
installing Oracle 11gR2. Please check the certification information on My
Oracle Support.
- Download and Install Oracle 11g
Release 2 in a new Oracle Home and make sure there are no relinking
errors. (Detailed steps provided in the upcoming section)
- Install the latest available
Patchset from Metalink (if available).
- Install the latest opatch
available for your platform and database version (if available).
- Either take a Cold or Hot backup
of your source database (advisable to have cold backup).
- For an awareness of
performance-related issues in 11.2.0.2 . Please refer Note
1320966.1 "Things
to Consider Before Upgrade to 11.2.0.2 in Relation to Database
Performance"
Database
Installation:
Prepare
to create the 11.2.0 Oracle home
The
11.2.0 Oracle home must be installed on the database server node in a different
directory than the current Oracle home.
Read
Chapters 1 and 2 of the Oracle Database Installation Guide 11g Release
2 (11.2) for your platform. Also read Chapter 1 and the "System
Considerations and Requirements" section of Chapter 3 of the Oracle Database Upgrade Guide 11g Release 2 (11.2). Make sure you thoroughly understand the
installation and upgrade processes. Perform any step that is relevant for your
environment.
Attention: For
HP Itanium platforms using the NFS file system, choose a disk to install the
11g Oracle home separate from the database files. The mount options of the
disk containing the database files must include forcedirectio. The 11g Oracle
home (or any other binaries) must not include the forcedirectio mount option.
|
Install
the 11.2.0.4 software
Log
in to the database server node as the owner of the Oracle RDBMS file system and
database instance. Ensure that environment settings, such as ORACLE_HOME, are
set for the new Oracle home you are about to create, and not for any existing
Oracle homes on the database server node. Perform all the steps in Chapter 4,
"Installing Oracle Database" of the Oracle Database Installation
Guide 11g Release 2 (11.2) for your platform.
In
the Installation Types window, use the Product Languages button to select any
languages other than American English that are used by your Applications
database instance. Choose the Enterprise Edition installation type. In the
subsequent windows, select the options not to upgrade an existing database and
to install the database software only.
Note:
1.
When
installing 11.2.0.4 on Oracle Linux 7 or Red Hat Linux 7, ignore the warning
regarding missing package 'pdksh-5.2.14' during pre-requisite checks.
2.
When
installing 11.2.0.4 on Oracle Linux 7 or Red Hat Linux 7 and encountering the
installer error in invoking target 'agent nmhs' of makefile with the log file
showing an undefined reference to symbol 'B_DestroyKeyObject', users should
review and implement the workaround noted in the document Installation
of Oracle 11.2.0.4 Database Software on OL7 fails with "undefined
reference to symbol 'B_DestroyKeyObject'" error".
|
Install Oracle Database 11g Products from the
11g Examples CD (mandatory)
On
the database server node, as the owner of the Oracle RDBMS file system and
database instance, perform the tasks in the "Install Oracle Database
Examples" section in the Oracle Database Examples Installation Guide.
In
the Installation Types window, use the Product Languages button to select any
languages other than American English that are used by your Applications
database instance.
After
the installation, make sure that:
c.
The
ORACLE_BASE environment variable must be set accordingly.
d.
The
ORACLE_HOME environment variable points to the new 11.2.0
Oracle home.
e.
The
PATH environment variable includes $ORACLE_HOME/bin and the directory where the
new perl executable is located (usually $ORACLE_HOME/perl/bin).
f.
The
LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
g.
The
PERL5LIB environment variable points to the directories where the new perl
libraries are located ($ORACLE_HOME/perl/lib/[perl version] and
$ORACLE_HOME/perl/lib/site_perl/[perl version] for UNIX/Linux,
$ORACLE_HOME/perl/lib and $ORACLE_HOME/perl/site/lib for Windows).
As you read this document,
bear the following important points in mind:
- Oracle strongly recommends that
you always use the latest version of the opatch utility, available from My
Oracle Support via Patch 6880880.
- More information on patching the
technology Oracle Homes is available in My Oracle Support Knowledge Document 1355068.1, Oracle
E-Business Suite 12.2 Patching Technology Components Guide.
Section 2: EBS
Technology Codelevel Checker (ETCC)
ETCC can be downloaded via Patch 17537119 from My Oracle Support. ETCC maps missing bugfixes to the
default corresponding patches and displays them in a patch recommendation
summary. If your Oracle E-Business Suite 12.2 installation has additional
patches installed as well as the recommended patches, you may need to install a
merge patch from Oracle Support. As well as installation instructions and basic
commands, the README for this patch includes a number of usage scenarios and
examples so should be carefully reviewed before running ETCC.
Note:
Always use the latest version of ETCC, as new bugfixes will not be checked by
older versions of the utility. Also, patch recommendation summaries are only
provided for the latest and latest but one quarterly bundles supported by
Oracle E-Business Suite Release 12.2. Refer to Section 3: Database Patches and
Bug Numbers for the Oracle database releases and bundle documentation.
The two scripts that make
up ETCC are:
- checkDBpatch.sh (checkDBpatch.cmd on Microsoft Windows). This
is the Database EBS Technology Codelevel Checker (DB-ETCC), which
determines if all the needed bugfixes exist in the specified database
ORACLE_HOME. When using Oracle Real Application Clusters (Oracle RAC)
ensure ETCC is run on every database ORACLE_HOME.
- checkMTpatch.sh (checkMTpatch.cmd on Microsoft Windows). This
is the Middle Tier EBS Technology Codelevel Checker (MT-ETCC), which
determines if all the needed bugfixes exist in the middle tier file
system.
Section
3.1: Database 11.2.0.4 Patches and Bug Numbers
Table
1.1 - Patches and bug numbers for Database 11.2.0.4.0 (UNIX platforms)
'First included in
startCD' column is not relevant for 11.2.0.4 Database. Apply all the patches
listed for your platform.
Acronyms used: N/A = Not
applicable.
Footnote 3 - After applying the patch, you must run the
following Oracle E-Business Suite Release 12.2 specific script and then restart the
database (which is not documented in the patch readme):
$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Footnote 4 - If a conflict is reported with any of
these previously documented fixes: Patch
21114125, Patch
21472186 or Patch
22855980 roll back the conflicting patch before
applying Patch
22876444.
23477849
Patch installation :
20523280
1. Maintain
a location for storing the contents of the patch ZIP file. In the rest of the
document, this location (absolute path) is referred to as
<PATCH_TOP_DIR>.
2. Extract
the contents of the patch ZIP file to the location you created in Step (1). To
do so, run the following command:
$ unzip
-d <PATCH_TOP_DIR> p20523280_112040_SOLARIS64.zip
3. Navigate
to the <PATCH_TOP_DIR>/20523280 directory:
$ cd
<PATCH_TOP_DIR>/20523280
4. Install
the patch by running the following command:
Note:
When
OPatch starts, it validates the patch and ensures that there are no conflicts
with the software already installed in the ORACLE_HOME of the Oracle Database.
OPatch categorizes conflicts into the following types:
- Conflicts with a patch already applied to the
ORACLE_HOME - In this case, stop the patch installation and contact Oracle
Support Services.
- Conflicts with a patch already applied
to the ORACLE_HOME that is a subset of the patch you are trying to apply - In this case, continue with the patch
installation because the new patch contains all the fixes from the existing
patch in the ORACLE_HOME. The subset patch will automatically be rolled back
prior to the installation of the new patch.
4.
Start the services from the Oracle home.
Pre
upgrade steps :
To download and use the
latest Pre-Upgrade Information Tool see the following:
How to Download and Run Oracle's Database Pre-Upgrade Utility Note 884522.1
How to Download and Run Oracle's Database Pre-Upgrade Utility Note 884522.1
or
Run the Pre-Upgrade
Information Tool for Collecting Pre-Upgrade Information
Step1
- Log into the system as the owner of the Oracle Database 11g Release
2 (11.2) Oracle Home directory.
- Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the
Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to
a directory outside of the Oracle Home, such as the temporary directory on
your system.
$ORACLE_HOME/rdbms/admin/utlu112i.sql
Failure to run the pre-upgrade tool
(utlu112i.sql) will result in the following error while running the
catupgrd.sql script :
SQL> SELECT
TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number
It is required to restore the database back to previous version in order to run the preupgrade tool (utlu112i.sql ) .
Step 2
- Change to the directory where utlu112i.sql had been copied in the
previous step.
- Start SQL*Plus and connect to the database instance as a user with
SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note
that the database should be started using the source Oracle Home .
$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
Database
This section displays global database information about the current database, such as the database name, release number and compatibility level. A warning is displayed if you must adjust the COMPATIBLE initialization parameter before the database is upgraded.
Logfiles
This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number and recommended size is displayed.
In a manual upgrade using SQL scripts and utilities, new files of at least 4 MB (preferably 10 MB) must be created in the current database, and any redo log files less than 4 MB must be dropped before the database is upgraded. These tasks are performed automatically by the Database Upgrade Assistant.
Tablespaces
This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade.In a manual upgrade using SQL scripts and utilities, space must be added to tablespaces that do not have enough free space in the current database. These tablespace adjustments must be made before the database is upgraded. This task is performed automatically by the Database Upgrade Assistant.
Update Parameters
This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments must be made to the parameter file after it is copied to the new Oracle Database 11g release.
Deprecated Parameters
This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 11g release.Obsolete Parameters This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 11g release2 (11.2). Obsolete initialization parameters must be removed from the parameter file before the database is upgraded.
This section displays global database information about the current database, such as the database name, release number and compatibility level. A warning is displayed if you must adjust the COMPATIBLE initialization parameter before the database is upgraded.
Logfiles
This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number and recommended size is displayed.
In a manual upgrade using SQL scripts and utilities, new files of at least 4 MB (preferably 10 MB) must be created in the current database, and any redo log files less than 4 MB must be dropped before the database is upgraded. These tasks are performed automatically by the Database Upgrade Assistant.
Tablespaces
This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade.In a manual upgrade using SQL scripts and utilities, space must be added to tablespaces that do not have enough free space in the current database. These tablespace adjustments must be made before the database is upgraded. This task is performed automatically by the Database Upgrade Assistant.
Update Parameters
This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments must be made to the parameter file after it is copied to the new Oracle Database 11g release.
Deprecated Parameters
This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 11g release.Obsolete Parameters This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 11g release2 (11.2). Obsolete initialization parameters must be removed from the parameter file before the database is upgraded.
Step3
Check for the integrity of the source database.
Check for the integrity of the source database prior to starting the upgrade by downloading and running the dbupgdiag.sql script from the My Oracle Support article below:
Check for the integrity of the source database.
Check for the integrity of the source database prior to starting the upgrade by downloading and running the dbupgdiag.sql script from the My Oracle Support article below:
Note
556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information
(dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database until there is no change in the number of invalid objects.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.
It is advisable to do a Health Check
using "hcheck.sql" script .Please refer following article to download
the script (Note 136697.1)
Step 4
Deprecated CONNECT Role
After upgrading to Oracle Database 11g Release 2 (11.2) from Oracle Database9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:
Deprecated CONNECT Role
After upgrading to Oracle Database 11g Release 2 (11.2) from Oracle Database9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrade.
The upgrade scripts adjust the privileges for the Oracle-supplied users.
In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:
SELECT GRANTEE,PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE ='CONNECT'
GRANTEE PRIVILEGE
------- ----------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK
FROM DBA_SYS_PRIVS
WHERE GRANTEE ='CONNECT'
GRANTEE PRIVILEGE
------- ----------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK
From Oracle 10.2, 'CONNECT' role only includes 'CREATE SESSION' privilege.
Step 5
Create script for DBLINK (in case the database has to be downgraded again).
During the upgrade to Oracle Database 11g Release 2 (11.2) from Oracle Database 9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are encrypted. To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links do not exist in the downgraded database. If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.
Create script for DBLINK (in case the database has to be downgraded again).
During the upgrade to Oracle Database 11g Release 2 (11.2) from Oracle Database 9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are encrypted. To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links do not exist in the downgraded database. If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
Step
6
Check
for TIMESTAMP WITH TIMEZONE Datatype
The RDBMS DST patching has been greatly improved in 11gR2.
Unlike upgrading for older versions (upgrading 10.2.0.4 to 11.1.0.7 for
example) there is no need anymore to
apply "dst patches" on the old version *before* the upgrade.
If you upgrade from an older RDBMS version to 11gR2 the DST version in 11gR2
after the upgrade will be
simply the same as the DST version that was used in the older RDBMS version.
There are however a few situations where some extra steps are needed,
so please do check below notes before upgrading to 11gR2., depending on to what
11gR2 version you upgrade to
Applying the RDBMS DSTv17 patch 12949905 on the server
side in 11.2.
After a DST patch is
installed in an 11.2 $ORACLE_HOME there are steps who need to be done to change
a existing database to use this newer DST version.
Simply applying the RDBMS DST patch and restarting the database will NOT enable the new applied RDBMS DST version patch (like it did in pre-11.2 versions).
For 11.2.0.4:
Simply applying the RDBMS DST patch and restarting the database will NOT enable the new applied RDBMS DST version patch (like it did in pre-11.2 versions).
For 11.2.0.4:
* Request
(if needed) and download RDBMS DSTv17 Patch
12949905 for your platform.
* Apply the RDBMS DSTv17 Patch 12949905 using Opatch, there is no need to shut down or stop the database or other processes.
* Apply the RDBMS DSTv17 Patch 12949905 using Opatch, there is no need to shut down or stop the database or other processes.
Patches location -
/a02/patches
Step
8
Optimizer Statistics
When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.
To determine the schemas which lack statistics, either review the output of the utlu112i.sql script or download and run the script from the below article:
Optimizer Statistics
When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.
To determine the schemas which lack statistics, either review the output of the utlu112i.sql script or download and run the script from the below article:
Note
560336.1 Script to Check
Schemas with Stale Statistics
To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Step 9SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Disable Oracle Database Vault (Not applicable to my env)
When upgrading from Oracle Database release 10.2, if you have enabled the Oracle Database Vault option in your current Oracle Home, then you must disable Oracle Database Vault in the target Oracle Home where the new release 11.2 software is installed before upgrading the database, and enable it again when the upgrade is finished. If Database Vault is enabled, then DBUA will return an error asking you to disable Database Vault prior to upgrade.
You must do this before upgrading the database. Enable Oracle Database Vault again once the upgrade is completed.
Please refer to the following Documentation/Articles for complete information to Disable/Enable Oracle Database Vault.
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
Step 10
** Not applicable to my env
Backing up Enterprise Manager Database Control Data. This can be skipped if EM Database Control Console is not being used or not configured.
After upgrading to Oracle Database 11g release 2 (11.2), if you want to downgrade Oracle Enterprise Manager Database Control you must save your Database Control files and data before upgrading your database. The emdwgrd utility can be used to keep a copy of your database control files and data before upgrading your database. The emdwgrd utility resides in the ORACLE_HOME/bin directory in the Oracle Database 11g release 2 (11.2) home.
1. Set ORACLE_HOME to your old Oracle Home
2. Set ORACLE_SID to the SID of the database being upgraded.
3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.
Example : export
SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
4. Change directory to Oracle Database 11g release 2 (11.2) home.
5. Run the emdwgrd command.
a. Run the following command for single instance database:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
4. Change directory to Oracle Database 11g release 2 (11.2) home.
5. Run the emdwgrd command.
a. Run the following command for single instance database:
$ emdwgrd -save -sid old_SID -path save_directory
Step 12
This optional check is introduced to spot any logical corruption in underlying objects and their dependencies.
This proactive check is introduced to avoid any failure in database upgrade at a later stage due to such corruption. If there is corruption the upgrade will most likely fail.
To check for corruption in the dictionary, use the following commands in SQL*Plus (connected as sys):
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade ;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade ;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade ;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade ;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off
This creates a script
called analyze.sql.
Now execute the following steps:
Now execute the following steps:
$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
This script (analyze.sql) should not return any errors.
Note:
1. ORA-30657 might occur if there is any external table validated, which can be safely ignored as per Note 209355.1 ORA-30657: Using ANALYZE TABLE for an External Table
2. Errors shown below when
executing analyze.sql can be ignored:
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
3. "ORA-00054: resource busy and
acquire with NOWAIT specified" may
be returned when analyzing AWR tables (WRH$_...)
to workaround this error AWR can be temporarily disabled :
to workaround this error AWR can be temporarily disabled :
3.a) get current value for
snapshot interval :
select
snap_interval,retention from dba_hist_wr_control;
3.b) set this interval to
zero to temporarily disable AWR :
exec
dbms_workload_repository.modify_snapshot_settings(interval=>0);
3.c) Analyze the WRH$ tables
3.d) Revert back to initial value :
exec dbms_workload_repository.modify_snapshot_settings(interval=><value
in mn of snap_interval returned
at 3.a>);
Step 13
Before upgrading Oracle
Database, you must wait until all materialized views have completed refreshing
and check that replication is stopped.
Run the following query to determine if there are any materialized view refreshes still in progress:
Run the following query to determine if there are any materialized view refreshes still in progress:
SQL>
select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
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;
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
If the second query
returns any row, then use Note
1442457.1 : During 11g
Upgrade, Mview refresh warning
Step 14
Ensure that no files need media recovery and that no files are in backup mode.
Ensure that no files need media recovery and that no files are in backup mode.
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
This should return no rows.
Step 16
Resolve outstanding distributed transactions prior to the upgrade.
Resolve outstanding distributed transactions prior to the upgrade.
SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
Step 17
To check if a standby database exists, issue the following query:
To check if a standby database exists, issue the following query:
SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';
If this query returns a row, then sync the standby database with the primary database.
1. Make sure all the logs are transported to the standby server after a final log switch
in the primary.
2. Start the recovery of the standby database with the NODELAY option.
Disable all batch and cron jobs.
About jobs initiated with Oracle the packages DBMS_JOB, DBMS_SCHEDULER can be used , regarding cron jobs (external jobs controlled at the OS level), this is a task for your Unix administrator
See also :
Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER
Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB
Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification
Step 19
Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.
You must have sufficient space in the tablespace or be set to extents unlimited.
Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.
You must have sufficient space in the tablespace or be set to extents unlimited.
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
FROM dba_users
WHERE username in ('SYS','SYSTEM');
If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command.
SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;
Step 20
Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.
Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.
SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
FROM dba_tables
WHERE table_name='AUD$';
If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS .
Note: If the AUD$ table exists and is in use, upgrade performance can be effected depending on the number of records in the table.
Step 21
Check whether database has any externally authenticated SSL users.
Check whether database has any externally authenticated SSL users.
SQL> SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
If any SSL users are found then Step 33
has to be followed after the upgrade.
Step 22
Note down 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.
Step 22
Note down 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;.
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;.
Step 23
If the you have upgraded the Grid
Infrastructure then this step is not needed as it was done as part of the GI
install / upgrade
a) Stop the listener for the database.
a) Stop the listener for the database.
$ lsnrctl stop
Previous versions of the listener are not
supported for use with an Oracle Database 11g Release 2 (11.2) database.
However, it is possible to use the new version of the listener with previous
versions of Oracle Databases.
If you are upgrading from 9i or upgrading manually without using DBUA, run Oracle Net Configuration Assistant before upgrading the Oracle RAC database.
This is a two-step option.
You must first run Oracle Net Configuration Assistant from the old Oracle Home to remove the old listener.
- Invoke the Netca
- Choose the configuration you want to do ==> Choose Listener Configuration
- Select what you want to do ==> Delete
- Select the listener you want to delete .
Then you must run Oracle Net Configuration Assistant again from the new Oracle Database 11g Release 2 (11.2) Home to create a new listener.
- Invoke the Netca
- Choose the configuration you want to do ==> Choose Listener Configuration
- Select what you want to do ==> Add
- Provide the detail that is required to configure the listener.
You must remove the old listener before creating a new one. If you attempt to create a new listener from the new Oracle Home first, and use the same name and port as the old listener, then Oracle Net Configuration Assistant returns an error.
Note: This is your only option if you want to upgrade your Oracle RAC database manually.
b) Stop other executable such as dbconsole, isqlplus, etc.
If you are upgrading from 9i or upgrading manually without using DBUA, run Oracle Net Configuration Assistant before upgrading the Oracle RAC database.
This is a two-step option.
You must first run Oracle Net Configuration Assistant from the old Oracle Home to remove the old listener.
- Invoke the Netca
- Choose the configuration you want to do ==> Choose Listener Configuration
- Select what you want to do ==> Delete
- Select the listener you want to delete .
Then you must run Oracle Net Configuration Assistant again from the new Oracle Database 11g Release 2 (11.2) Home to create a new listener.
- Invoke the Netca
- Choose the configuration you want to do ==> Choose Listener Configuration
- Select what you want to do ==> Add
- Provide the detail that is required to configure the listener.
You must remove the old listener before creating a new one. If you attempt to create a new listener from the new Oracle Home first, and use the same name and port as the old listener, then Oracle Net Configuration Assistant returns an error.
Note: This is your only option if you want to upgrade your Oracle RAC database manually.
b) Stop other executable such as dbconsole, isqlplus, etc.
$ emctl stop dbconsole
$ isqlplusctl stop
$ isqlplusctl stop
Step 24
Shutdown the database.
Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> shutdown immediate;
Back up the
Database
1. Perform Cold Backup (Recommended)
(or)
2. Take a backup using RMAN
1. Perform Cold Backup (Recommended)
(or)
2. Take a backup using RMAN
Connect to RMAN:
rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '<db_backup_directory>%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '<controlfile_backup_directory>';
}
rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '<db_backup_directory>%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '<controlfile_backup_directory>';
}
--> backup_directory >> Location of the Database backup.
--> controlfile_backup_directory >> Location of the Controlfile backup.
Step
25
- copy the
initialization file from source Oracle Home to <target 11GR2 home>/dbs
(<target 11GR2 home>\database on Windows)
- then process in target
11GR2 directory (<target 11GR2 home>/dbs for unix and <target 11GR2
home>\database for Windows) to the needed modiciations :
Comment out obsoleted parameters (Appendix A) and change all deprecated parameters (Appendix A).
Comment out obsoleted parameters (Appendix A) and change all deprecated parameters (Appendix A).
It is also
recommended to remove all hidden parameters set manually prior to
upgrading.
* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.
According to Bug 8937877, CORE_DUMP_DEST is not deprecated.
Refer to the below article for understanding directory structure in 11g and DIAGNOSTIC_DEST.
* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.
According to Bug 8937877, CORE_DUMP_DEST is not deprecated.
Refer to the below article for understanding directory structure in 11g and DIAGNOSTIC_DEST.
Step 27
If your operating system is UNIX then complete this step, else skip to the next step.
1. Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH , SHLIB_PATH and LIBPATH ( for AIX )
If your operating system is UNIX then complete this step, else skip to the next step.
1. Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH , SHLIB_PATH and LIBPATH ( for AIX )
For example:
$ export ORACLE_HOME=<location of Oracle 11.2>
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=<Oracle_Base set during installation>
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
$ export SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH
$ export LIBPATH=$ORACLE_HOME/lib:$LIBPATH
$ export ORACLE_HOME=<location of Oracle 11.2>
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=<Oracle_Base set during installation>
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
$ export SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH
$ export LIBPATH=$ORACLE_HOME/lib:$LIBPATH
Note : If ORACLE_BASE is not known, after setting PATH to 11gR2 Oracle Home, execute 'orabase', which will point the location of the base.
Note : Unset/Remove the ORA_TZFILE
environment variable if it is set in your environment .
Database
Upgrade:
9.
Ensure
Applications patching cycle is complete
If
the patching cycle has not been completed, as the owner of the source
administration server, run the following command to finish any in-progress adop
session:
$ adop phase=cutover,cleanup
10.
Shut
down Applications server processes and database listener
On
each application tier server node, shut down all server processes or services.
On the database tier server node, shut down the Oracle Net or Net8 database
listener in the old Oracle home.
Note: The
Applications will be unavailable to users until all remaining tasks in this
section are completed.
|
Attention: Make
sure that you do not have the LOCAL_LISTENER initialization parameter set to
ensure that the database does not inadvertently point to a non-existent
listener during upgrade.
|
11.
Drop
SYS.ENABLED$INDEXES (conditional)
If
the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database
as SYSDBA and running the following command to drop it:
SQL> drop table sys.enabled$indexes;
#
Multiple entries with the same $ORACLE_SID are not allowed.
DEV:/d02/DEV/oratest/11204:N
Upgrade
the database instance
If you are upgrading database from 10g, verify following information
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
-------------------------------------------------------------
FOR COLUMNS ID SIZE 1
If it reports "FOR COLUMNS ID SIZE 1", it might create issues during upgrade. Hence as a workaround execute
SQL>exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');
Refer: Unpublished BUG 22454765 - CARRYING METHOD_OPT = "FOR COLUMNS ID SIZE 1" FROM 10G WILL BREAK UPGRADE
Upgrading Database to 11gR2
Step 28
Step 28
9.
Modify initialization parameters
Use the following sections in document 396009.1, Database
Initialization Parameter Settings for Oracle Applications Release 12 on My Oracle Support as a guideline:
a.
Common database initialization parameters
b.
Release-specific database initialization parameters for 11gR2
c.
Additional Database Initialization Parameters For Oracle
E-Business Suite Release 12.2
d.
Database initialization parameter sizing
Attention: If you
encounter the error:
ORA-04030: out of process memory when trying to allocate 822904 bytes (pga heap, kco buffer) ORA-07445: exception encountered: core dump [dbgtfdFileWrite()+48] then set the _pga_max_size initialization parameter to a larger value as follows: _pga_max_size=104857600 Restart your database. |
If
native compilation of PL/SQL code is disabled in your database instance, ignore
the initialization parameters that pertain to the native compilation of PL/SQL
code. Ensure that the temporary tablespace is properly defined. Follow the
instructions in the Temporary Tablespace Setup section.
Note: There are
many ways to generate the parameter file (for example, copying and updating
an old init.ora parameter file). You are not confined to using the parameter
files generated by the database upgrade assistant.
|
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
Note: If you are upgrading from 9.2 and the SYSAUX table already exists then drop the existing SYSAUX tablespace. The SYSAUX tablespace needs to be created immediately after the database is started in upgrade mode using 11g binaries (with Compatibility set to atleast 10.1 and just prior to the running of the catupgrd.sql scripts.
Create the SYSAUX tablespace only if you are upgrading from Oracle Database9i Release 2 (9.2) with the following mandatory attributes:
ONLINE
PERMANENT
READ WRITE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
The Pre-Upgrade Information Tool provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section. Refer to the output generated by the utlu112i.sql script in Step 1. The following SQL statement would create a 500 MB SYSAUX tablespace for the database:
SQL> CREATE TABLESPACE SYSAUX
DATAFILE '<location>/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
DATAFILE '<location>/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off
These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.
Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed.
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql
SQL> STARTUP
SQL> @utlu112s.sql
Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql
This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
Note
556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information
(dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
Disregard
warnings related to Network ACLs. AutoConfig manages all the E-Business Suite
Network ACLs.
When
upgrading all statistics tables, note that Oracle E-Business Suite has only one
statistics table, APPLSYS.FND_STATTAB, that needs to be upgraded.
If
you plan to change the PL/SQL compilation mode, disable the compilation of
objects.
10. Revoke ORA$BASE grant
If the ORA$BASE edition is not the default edition, a grant has to
be revoked.
To
see the default edition, use SQL*Plus to connect to the database as SYSDBA and
run the following command:
SQL> select * from database_properties where property_name='DEFAULT_EDITION';
If
ORA$BASE is not returned by the query, use SQL*Plus to connect to the database
as SYSDBA and run the following command:
SQL> revoke use on edition ora$base from public;
11. Natively compile PL/SQL
code (optional)
You can choose to run Oracle E-Business Suite 12 PL/SQL database
objects in natively compiled mode with Oracle Database 11g. See the
"Compiling PL/SQL Program Units for Native Execution" section of
Chapter 12 of Oracle Database
PL/SQL Language Reference 11g Release 2 (11.2).
Verify the listener.ora file.
For the upgraded instance(s) Verify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener.
lsnrctl start
Environment Variables
1. Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH, SHLIB_PATH and LIBPATH ( for AIX )
Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle Database 11g Release 2 (11.2) Home.
Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.
2. Modify /etc/oratab entry to use automatic startup.
SID:ORACLE_HOME:Y
For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y
For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y
After the upgrade
note : this simply repeats the same actions as given in the "DST" notes referenced in step 6 of this note.
Check the current version of the Oracle time zone definitions in the upgraded database:
note : this simply repeats the same actions as given in the "DST" notes referenced in step 6 of this note.
Check the current version of the Oracle time zone definitions in the upgraded database:
SQL> conn / as sysdba
Connected.
SQL>SELECT version FROM v$timezone_file;
VERSION
----------
4
Connected.
SQL>SELECT version FROM v$timezone_file;
VERSION
----------
4
Upgrade Statistics Tables Created by the
DBMS_STATS Package.
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');
In the example, 'SYS' is the owner of the statistics table and 'dictstattab' is the name of the statistics table. Execute this procedure for each statistics table.
Enable Database Vault
Refer to the following documents for enabling Oracle Database Vault:
Refer to the following documents for enabling Oracle Database Vault:
Note
453903.1 - Enabling and
Disabling Oracle Database Vault in UNIX
SQL> create spfile from pfile;
Change passwords for Oracle-Supplied
Accounts.
You can view the status of all accounts by issuing the following SQL statement:
You can view the status of all accounts by issuing the following SQL statement:
SQL> SELECT username, account_status FROM dba_users ORDER BY username;
To lock and expire passwords, issue the following SQL statement:
SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
TDE (Transparent Data Encryption)
If your are using Transparent Data
Encryption then you will have to rekey the master key as follows:
SQL> alter system set encryption key identified by
"<wallet password>";
Gather Fixed Object Statistics
Please create stats on fixed objects two
weeks after the upgrade using the below command
SQL>EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL>EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
It would to good to gather the statistic
during non-peak hours
Patch Post installation steps :
After you install the patch, reload the packages into
the Oracle Database.
change directory to $ORACLE_HOME/rdbms/admin and then
connect as SYSDBA and run the following in given order
:
Note : Below steps must be executed in the order they
appear in.
SQL> @?/rdbms/admin/catbundle.sql EBS apply
SQL>
@?/md/admin/catmgdidcode.sql
23477849
The following steps load modified SQL files into the
database. For a RAC environment, perform these steps on only one node.
$ sqlplus
/nolog
SQL> CONNECT
/ AS SYSDBA
SQL>
@?/sqlpatch/23477849/postinstall.sql
22098122
1. For each
database instance running on the Oracle home being patched, connect to the
database using SQL*Plus. Connect as SYSDBA and run the below script as follows:
$ sqlplus
/nolog
SQL> CONNECT
/ AS SYSDBA
SQL>
@?/sqlpatch/22098122/postinstall.sql
18966843
1. For each
database instance running on the Oracle home being patched, connect to the
database using SQL*Plus. Connect as SYSDBA and run the below script as follows:
$ sqlplus
/nolog
SQL> CONNECT
/ AS SYSDBA
SQL>
@?/sqlpatch/18966843/postinstall.sql
22731026
1. For each
database instance running on the Oracle home being patched, connect to the
database using SQL*Plus. Connect as SYSDBA and run the below script as follows:
$ sqlplus
/nolog
SQL> CONNECT
/ AS SYSDBA
SQL>
@?/sqlpatch/22731026/postinstall.sql
DST patch post installation instructions :
Conn / as sysdba
-- this gives the current RDBMS DST versionSELECT 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;
-- the output gives
-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <current DST version> <<<<------ this should match version FROM v$timezone_file if not make sure the database is open when selecting from v$timezone_file;
-- DST_SECONDARY_TT_VERSION 0 <<<<------ this should be "0" if not then see point 3a) in note 977512.1 (for 11gR2) or note 1509653.1 (for 12c)
-- DST_UPGRADE_STATE NONE <<<<------ this should be "NONE" if not then see point 3a) in note 977512.1 (for 11gR2) or note 1509653.1 (for 12c)
Execute the below as sysdba :
Scripts can be found in /a02/patches
Conn / as sysdba
spool countstatsTSTZ.log
@countstatsTSTZ.sql
spool off
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 original value
exec dbms_stats.alter_stats_history_retention(31);
run
upg_tzv_check.sql using SQL*PLUS from the database home
Note that upg_tzv_check.sql takes no arguments, it will detect the
highest installed DST patch automatically and needs no downtime, this can be
executed on a live production database but it WILL purge the dba_recyclebin.
Conn / as sysdba
spool upg_tzv_check.log
@upg_tzv_check.sql
spool off
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.
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
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.
Note : 1585343.1INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
Create
nls/data/9idata directory
On the
database server node, as the owner of the Oracle RDBMS file system and database
instance, run the following command to create the $ORACLE_HOME/nls/data/9idata
directory.
$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
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.
18. Start the new database
listener (conditional)
If the Oracle Net listener for the database instance in the new
Oracle home has not been started, you must start it now. Since AutoConfig has
not yet been implemented, start the listener with the lsnrctl executable
(UNIX/Linux) or Services (Windows). See the Oracle Database Net Services
Administrator's Guide, 11g Release 2 (11.2) for more information.
Attention: Set the
TNS_ADMIN environment variable to the directory where you created your
listener.ora and tnsnames.ora files.
|
19. Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows)
from the administration server node to the database server node. Use SQL*Plus
to connect to the database as SYSDBA and run the script using the following
command:
$ sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql)
[APPS schema name]
20. Grant create procedure
privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration
server node to the database server node. Use SQL*Plus to connect to the
database as APPS and run the script using the following command:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
21. Compile invalid objects
Use SQL*Plus to connect to the database as SYSDBA and run the
$ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
22. Set CTXSYS parameter
Use SQL*Plus to connect to the database as SYSDBA and run the
following command:
$ sqlplus "/ as sysdba"
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
23. 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]
24. 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.
Use
SQL*Plus to connect to the database as APPS and run the following command:
$ sqlplus apps/[APPS password]
SQL> exec fnd_conc_clone.setup_clean;
25.
Implement and run AutoConfig
Enabling AutoConfig on a New
Oracle Home
In Release 12.2, AutoConfig is
enabled by default on the application tier. However, it
might not be enabled on the
database tier in the following scenarios:
• The database tier was not
created by Rapid Install.
• Cross-platform migration has
been performed on the database tier.
• The database has been upgraded
to Oracle Database 11g.
• The database tier has been
upgraded as part of an Oracle E-Business Suite upgrade
from Release 11i to
12.2.
To enable AutoConfig on the
database tier, perform the following steps in the order
listed:
1. Copy AutoConfig to the RDBMS ORACLE_HOME
Update the RDBMS ORACLE_HOME file
system by following the steps in Applying
the Latest AutoConfig
Updates above.
2. 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. Ensure that the JRE version
on the database tier is at least at:
• Solaris: Java SE Runtime Environment (build 1.6.0_21-b06)
• Linux x86-64: Java SE Runtime Environment (build 1.6.0_17-b04)
You can obtain the JRE from the
official download location
[http://www.oracle.com/technetwork/java/javase/downloads/index.html].
Warning: Do not download the Java SE Development
Kit (JDK).
Instead, download the JRE that
supports 64-bit JVM. For additional
information on installation, see Using Latest Java 6.0 Update With
Oracle E-Business Suite
Release 12 (Doc ID 455492.1), disregarding
the note about downloading 32-bit
JRE.
3. Generate the Database Context File
Technical Configuration 3-29
Execute the following command to
create your database context file:
$ perl
<RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl
Important: If you run the adbldxml.pl utility for an instance
that is
part of an Oracle RAC environment,
all the Oracle RAC instances
must be running so that
adbldxml.pl can connect to them and
gather information about the
configuration.
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>
Attention: For
Windows customers, after creating the XML context file, ensure that the
variable s_dlsnname has the proper listener service name.
|
| Attention: When running AutoConfig on the
Patch APPL_TOP, ignore all errors. |
| Attention: If the database tier node is
different from the Applications tier node, after running AutoConfig on the
database tier, modify tcp.invited_nodes of the generated
[TNS_ADMIN]/sqlnet.ora to include the Applications tier so that the
Applications tier can connect to the database. |
26. Gather statistics for SYS
schema
Copy $APPL_TOP/admin/adstats.sql from the administration server
node to the database server node. Note that adstats.sql has to be run in
restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the
following commands to run adstats.sql in restricted mode:
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
Note: Make sure
that you have at least 1.5 GB of free default temporary tablespace.
|
27. Create Demantra privileges
(conditional)
28. Re-create custom database
links (conditional)
If the Oracle Net listener in the 11.2.0 Oracle home is defined
differently than the one used by the old Oracle home, you must re-create any
custom self-referential database links that exist in the Applications database
instance. To check for the existence of database links, use SQL*Plus on the
database server node to connect to the Applications database instance as APPS
and run the following query:
$ sqlplus apps/[apps password]
SQL> select db_link from all_db_links;
The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist,
should have been updated with the new port number by AutoConfig in the previous
step.
If
you have custom self-referential database links in the database instance, use
the following commands to drop and re-create them:
$ sqlplus apps/[apps password]
SQL> drop database link [custom database link];
SQL> create database link [custom database link] connect to
[user] identified by [password] using
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])
(PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))';
where [custom database link], [user], [password],
[hostname], [port number], and [ORACLE_SID] reflect the
new Oracle Net listener for the database instance.
29. Restart Applications
server processes
Restart all the Application tier server processes that you shut
down previously. Remember that the Oracle Net listener for the database
instance, as well as the database instance itself, need to be started in the
11.2 Oracle home. Users may return to the system.
30. Synchronize Workflow views
Log on to Oracle E-Business Suite with the "System
Administrator" responsibility. Click Requests > Run > Single Request
and the OK button. Enter the following parameters:
o Request Name = Workflow Directory Services User/Role
Validation
o Batch Size = 10000
o Fix dangling users = Yes
o Add missing user/role assignments = Yes
o Update WHO columns in WF tables = No
Click
"OK" and "Submit".
No comments:
Post a Comment