Main “steps” documents id:

362205.1: 10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1: Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2) part no. E23633-07

Other doc_id: 881505.1, 1367654.1

Troubleshooting:

Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]



Contents

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1. 1

Before the Database Installation and Upgrade. 3

I.       Interoperability “Pre”: 3

Database Upgrade: 4

II.      Pre-Export the database: 4

III.         Pre-Import the database: 6

IV-         Export the database: 9

V-     Import the database: 10

IV-         Post-Import and database configuration: 11

Before the Database Installation and Upgrade

       I.            Interoperability “Pre”:

From doc_id: 1265268.1 -> Section1: Upgrading an E-Business Suite 11i Database to Oracle Database 11g Release 2 (11.2.0)

1)   Verify S/W and system’s versions:

a)      Ebs 11.5.10.2

b)      ATG Rollup Patch 6

c)       Developer 6i Patch set 18

d)      AutoConfig Latest version with 9835302

2)   Nothing to do

3)   Nothing to do

4)   Deregister the current database server

To deregister the current database server node because If you plan to change the database port, host, SID, or database name parameter, run the following command as the owner of the Oracle RDBMS file system and current database instance:

$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl

appspass=[APPS db user password]

contextfile=$CONTEXT_FILE -removeserver

5)   Update application tier context file with new database listener port number

If you want to continue use the APPS tier, modify XML file with new database info. OR just make new cloned instance to connect to new DB tier.

s_dbhost New database hostname
s_dbdomain New database domain name
s_db_serv_sid New database SID
s_dbport New database listener port
s_apps_jdbc_connect_descriptor NULL
6)   Upgrade the database instance

362205.1:   10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1:   Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2)  part no. E23633-07

According to previous documents the upgrade will be using import/export method.

Database Upgrade:

    II.            Pre-Export the database:

From doc_id: 362205.1 -> Section1: Prepare the source system

From doc_id: 557738.1 -> Section1: Prepare the source system

7)   Apply the Applications consolidated export/import utility patch

Apply both patches for exporting 10 (4872830) and 11g (1235353).

8)   Apply latest Applications database preparation scripts patch

Apply Patch 7225862 to every application tier server node in the source system.

9)   Create a working directory on source database node

Create a working directory named expimp in the source system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

10)       Generate target database instance creation script adcrdb.sql (10g),  aucrdb.sql (11g)

11g $sqlplus system/manager  @$AU_TOP/patch/115/sql/auclondb.sql 11

10g $sqlplus system/manager  @$AU_TOP/patch/115/sql/adclondb.sql

Then edit this script to reflect the new environment.

“Split it into 2 files, first one for CREATE DATABASE statement include in it the UNDO TABLESPACE section. The other file is to the rest of tablespaces adcrdb_db.sql and adcrdb_tbs.sql”.

Also adpostcrdb.sql which you use to convert tablespaces to locally managed will be created.

11)       Record Advanced Queue settings

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

It generates auque2.sql in the current directory.

12)       Remove rebuild index parameter in spatial indexes

SQL> select * from dba_indexes where index_type=’DOMAIN’ and

upper (parameters) like ‘%REBUILD%’;

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index [index name] rebuild parameters (‘parameters’)

Where [parameters] is the original parameter set without the rebuild_index parameter.

13)       Synchronize Text indexes

$ sqlplus ‘/ as sysdba’

SQL> select pnd_index_owner,pnd_index_name,count(*)

from ctxsys.ctx_pending

group by pnd_index_owner,pnd_index_name;

To synchronize the indexes, run the following command:

SQL> exec ctx_ddl.sync_index(‘[index owner].[index name]‘);

14)       Drop SYS.ENABLED$INDEXES (conditional)

SQL> drop table sys.enabled$indexes;

15)       Shut down Applications server processes and database listener



 III.            Pre-Import the database:

16)       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

a) Finish OS prerequisites for new Oracle home “new host or exist host”.

b) Oracle OS owner profile option and environment variables.

17)       Install the base 11.2.0 software: install  software only

18)       Install Oracle Database 11g Products from the 11g Examples CD

19)       Create nls/data/9idata directory

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

20)       Apply additional 11.2.0.1 RDBMS patches  “9 patches using opatch”

Append Opatch tool to PATH,

$export PATH=$PATH:$ORACLE_HOME/OPatch

and run the following patches:

8570322

8685327

8761974

8771297

12672969-> instead of 8772028

8796511

8897784

8964142

8405205

21)       Create working directory on target database server.

Create a working directory named expimp in the target system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

22)       Copy scripts needed

Copy the following from Source environment “database or application servers” to the working directory on target database:

        I.            Copy initialization parameter file and CBO parameter file (initSID.ora and ifilecbo.ora “if exists”) from source database server, modify wanted parameter
      II.            Copy modified database creation scripts (adcrdb_db.sql and adcrdb_tbs.sql and adpostcrdb.sql) from source database server
    III.            Copy database preparation scripts (addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql) OR (audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql) from $APPL_TOP/admin directory of the source administration server node.
    IV.            Copy auque2.sql generated file on step 18 from source database server working directory.
      V.            Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the target database server node
    VI.            Copy $APPL_TOP/admin/adgrants.sql sql from the administration server node to the working directory in the target database server node.
  VII.            Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the working directory in the target database server node.
Create appsutil.zip file
perl /bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

Then copy it to new ORACLE_HOME then

unzip -o appsutil.zip

23)       Create the target database instance

Use modified adcrdb.sql script, generated from step 17

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> spool adcrdb.log;

SQL> startup nomount;

SQL> @adcrdb_db.sql

SQL> @adcrdb_tbs.sql

SQL> @ adpostcrdb.sql

SQL> exit;

24)     Set up the SYS schema
the audb1110.sql, audb1120.sqlor addb1020.sql script sets up the SYS schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSDBA and run as sys.

$ sqlplus “/ as sysdba” @/u01/expimp/audb1120.sql

25)     Set up the SYSTEM schema
the ausy1110.sql, ausy1120.sql or adsy1020.sql script sets up the SYSTEM schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system

$ sqlplus system/manager @/u01/expimp/ausy1120.sql

26)     Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql or adjv1020.sql script installs the Java Virtual Machine (JVM) in the database. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system.

$ sqlplus system/manager @/u01/expimp/aujv1120.sql

27)     Install other required components

the aumsc1110.sql, aumsc1120.sql or admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and as system.

$ sqlplus system/manager @/u01/expimp/aumsc1120.sql FALSE SYSAUX TEMP

28)     Install custom RDBMS components (conditional)
if you have other custom RDBMS components loaded in the source database such as Label Security; install them in the target database. To determine the RDBMS components that are loaded in the source and target databases, use SQL*Plus to connect to the databases as SYSDBA and run the following command:

SQL> select * from dba_registry;

29)     Disable automatic gathering of statistics
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> exit;

30)       Back up the target database instance
the target database instance is now prepared for an import of the Applications data. You should perform a backup before starting the import.

IV-             Export the database:

31)       Create the export parameter file

Copy $AU_TOP/patch/115/import/auexpdp.dat template file and edit it with proper values as following:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

filesize=1G

full=y

logfile=full_expdpapps.log

32)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

33)       Shut down Applications server processes

34)       Grant privilege to source system schema

$ sqlplus / as sysdba

SQL> grant EXEMPT ACCESS POLICY to system;

35)            Export the Applications database instance

If the source database is 10.2.0 or 11.1.0, use the following command:

$ expdp system/[system password] \

parfile=[export parameter file name]

If the source database is 11.2.0, use the following command:

$ expdp “‘/ as sysdba’” parfile=[export parameter file name]

36)       Revoke privilege from source system schema

SQL> revoke EXEMPT ACCESS POLICY from system;

V-                  Import the database:

37)       Create the import parameter file

Copy the export parameter file you created in Step 30 from the source database server node to the working directory in the target database server node, renaming it if necessary. Updating the new file with the following changes converts it to an import parameter file:

Remove the exclude parameters.
Remove the filesize parameter.
Change the name of the log file.
Uncomment the transform parameter.
It will be like this:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

full=y

transform=oid:n

logfile=impdpapps.log

38)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

39)       Copy the export dump files from source to target

40)       Set Oracle Text parameter

if the target database is 11.2.0, use SQL*Plus to connect to the target database as SYSDBA and run the following command to grant datastore privileges:

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter (‘file_access_role’, ‘public’);

41)            Import the Applications database instance

Start the import session on the target database server node using the customized import parameter file.

If the target database is 11.1.0, use the following command:

$ impdp system/manager parfile=[import parameter file name]

If the target database is 11.2.0, use the following command:

$ impdp “‘/ as sysdba’” parfile=[import parameter file name]

42)       Revoke privilege from target system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> revoke EXEMPT ACCESS POLICY from system;

IV-             Post-Import and database configuration:

From doc: Export/import notes on Applications 11i Database 11g [ID 557738.1]

And doc: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]

And doc Using AutoConfig to Manage System Configurations with Oracle Applications 11i [ID 165195.1]

43)       Reset Advanced Queues

Run file created in step:11.

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @/u01/expimp/auque2.sql

44)       Perform patch post-install instructions
run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.

45)       Run adgrants.sql

$ sqlplus “/ as sysdba” @adgrants.sql APPS

46)       Grant create procedure privilege on CTXSYS

$ sqlplus apps/apps @adctxprv.sql SYSTEM CTXSYS

47)       Set CTXSYS parameter

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);

48)       Deregister the current database server

$ sqlplus apps/apps

SQL> exec fnd_conc_clone.setup_clean;

49)       Some errors will be shown in the autoconfig

To avoid any error in running autoconfig in coming steps, review and verify the number and status of CTXSYS objects, synm. and grants.
To avoid any problem in INDEXES owned by CTXSYS, please run the following selected scripts from document: @jtfiappr.sql apps
Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]

enable applsys.wf_java_defered queue


some of grants may be lost so run the following list:
Compile the invalid objects.
$ sqlplus “/ as sysdba” @$ORACLE_HOME/rdbms/admin/utlrp.sql

50)       Generate your Database Context File

Ensure that shell reflect the oracle_home and bin.

From extracted appsutil.zip

cd /appsutil/bin
perl adbldxml.pl tier=db appsuser=apps

answer the question to generate XML file.

51)       Generate and Apply AutoConfig Configuration files in DB tier.

Execute the following commands:

cd /appsutil/bin
adconfig.sh contextfile=XML file created in step 50

52)       Re-create custom database links
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 dba_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.

Drop database link “PRODUCTION.HQ.ASQ.COM”;

create database link “PRODUCTION.HQ.ASQ.COM”

using ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.local)(PORT=1521))))(CONNECT_DATA=(SERVICE_NAME=UPGRD))’;

Drop public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM”;

create public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.HQ.ASQ.COM”;

create database link “EDW_APPS_TO_WH.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL”;

create database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.HQ.ASQ.COM”;

create database link “APPS_TO_APPS.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL”;

create database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.LOCAL”;

create database link “APPS_TO_APPS.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.LOCAL”;

create database link “EDW_APPS_TO_WH.LOCAL” using ’192.168.1.245:1521/UPGRD’;

53)       Generate and Apply AutoConfig Configuration files in APPS tier.

54)       Apply Oracle Human Resources (HRMS) patch 7721754

55)       Use adadmin to:

Compile invalid object
Re-create grants and synonyms using adadmin
Compile flexfield

Oracle LogMiner

Today we got an issue that stuck our database. Database generated an expected number of archives which consumed the free space on the server. We asked our team if there is any long running process or abnormal process they confirmed nothing happened. We got the root cause by tracing the archives using the Oracle logMiner. The issue was a lot of inserts came to a custom audit table.

First identify the name and location for your archives.

select name,FIRST_TIME

from v$archived_log

where trunc(FIRST_TIME) = trunc(to_date('22-02-2013','dd-mm-yyyy'));

-------------------------------------              ------------------------------

/OraData/Archive/arch_1_226111.arc            08/03/2007 19:06:19

/OraData/Archive/arch_1_226112.arc            08/03/2007 19:06:20

/OraData/Archive/arch_1_226113.arc            08/03/2007 19:06:22

Then let the logminor use theses logs:

SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/OraData/Archive/arch_1_226111.arc');

PL/SQL procedure successfully completed.

SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/OraData/Archive/arch_1_226112.arc');

PL/SQL procedure successfully completed.

SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/OraData/Archive/arch_1_226113.arc');

PL/SQL procedure successfully completed.

From the belwo query we can check if logminor can see the files:

SELECT * FROM V$LOGMNR_LOGS;

Then initiate the mining process by:

SQL> exec DBMS_LOGMNR.START_LOGMNR(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

We can check the executed SQL statements by one of the following queries:

select seg_owner,seg_name,seg_type_name,operation ,min(TIMESTAMP) mintime,max(TIMESTAMP) maxtime,count(*) vcount

from v$logmnr_contents

group by seg_owner,seg_name,seg_type_name,operation

order by count(*) desc; 

SELECT timestamp, scn, start_scn, operation, seg_owner, seg_name, sql_redo

FROM V$LOGMNR_CONTENTS

ORDER BY timestamp, scn;

 

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1

Main “steps” documents id:

362205.1: 10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1: Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2) part no. E23633-07

Other doc_id: 881505.1, 1367654.1

Troubleshooting:

Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]



Contents

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1. 1

Before the Database Installation and Upgrade. 3

I.       Interoperability “Pre”: 3

Database Upgrade: 4

II.      Pre-Export the database: 4

III.         Pre-Import the database: 6

IV-         Export the database: 9

V-     Import the database: 10

IV-         Post-Import and database configuration: 11

Before the Database Installation and Upgrade

       I.            Interoperability “Pre”:

From doc_id: 1265268.1 -> Section1: Upgrading an E-Business Suite 11i Database to Oracle Database 11g Release 2 (11.2.0)

1)   Verify S/W and system’s versions:

a)      Ebs 11.5.10.2

b)      ATG Rollup Patch 6

c)       Developer 6i Patch set 18

d)      AutoConfig Latest version with 9835302

2)   Nothing to do

3)   Nothing to do

4)   Deregister the current database server

To deregister the current database server node because If you plan to change the database port, host, SID, or database name parameter, run the following command as the owner of the Oracle RDBMS file system and current database instance:

$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl

appspass=[APPS db user password]

contextfile=$CONTEXT_FILE -removeserver

5)   Update application tier context file with new database listener port number

If you want to continue use the APPS tier, modify XML file with new database info. OR just make new cloned instance to connect to new DB tier.

s_dbhost New database hostname
s_dbdomain New database domain name
s_db_serv_sid New database SID
s_dbport New database listener port
s_apps_jdbc_connect_descriptor NULL
6)   Upgrade the database instance

362205.1:   10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1:   Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2)  part no. E23633-07

According to previous documents the upgrade will be using import/export method.

Database Upgrade:

    II.            Pre-Export the database:

From doc_id: 362205.1 -> Section1: Prepare the source system

From doc_id: 557738.1 -> Section1: Prepare the source system

7)   Apply the Applications consolidated export/import utility patch

Apply both patches for exporting 10 (4872830) and 11g (1235353).

8)   Apply latest Applications database preparation scripts patch

Apply Patch 7225862 to every application tier server node in the source system.

9)   Create a working directory on source database node

Create a working directory named expimp in the source system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

10)       Generate target database instance creation script adcrdb.sql (10g),  aucrdb.sql (11g)

11g $sqlplus system/manager  @$AU_TOP/patch/115/sql/auclondb.sql 11

10g $sqlplus system/manager  @$AU_TOP/patch/115/sql/adclondb.sql

Then edit this script to reflect the new environment.

“Split it into 2 files, first one for CREATE DATABASE statement include in it the UNDO TABLESPACE section. The other file is to the rest of tablespaces adcrdb_db.sql and adcrdb_tbs.sql”.

Also adpostcrdb.sql which you use to convert tablespaces to locally managed will be created.

11)       Record Advanced Queue settings

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

It generates auque2.sql in the current directory.

12)       Remove rebuild index parameter in spatial indexes

SQL> select * from dba_indexes where index_type=’DOMAIN’ and

upper (parameters) like ‘%REBUILD%’;

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index [index name] rebuild parameters (‘parameters’)

Where [parameters] is the original parameter set without the rebuild_index parameter.

13)       Synchronize Text indexes

$ sqlplus ‘/ as sysdba’

SQL> select pnd_index_owner,pnd_index_name,count(*)

from ctxsys.ctx_pending

group by pnd_index_owner,pnd_index_name;

To synchronize the indexes, run the following command:

SQL> exec ctx_ddl.sync_index(‘[index owner].[index name]‘);

14)       Drop SYS.ENABLED$INDEXES (conditional)

SQL> drop table sys.enabled$indexes;

15)       Shut down Applications server processes and database listener



 III.            Pre-Import the database:

16)       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

a) Finish OS prerequisites for new Oracle home “new host or exist host”.

b) Oracle OS owner profile option and environment variables.

17)       Install the base 11.2.0 software: install  software only

18)       Install Oracle Database 11g Products from the 11g Examples CD

19)       Create nls/data/9idata directory

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

20)       Apply additional 11.2.0.1 RDBMS patches  “9 patches using opatch”

Append Opatch tool to PATH,

$export PATH=$PATH:$ORACLE_HOME/OPatch

and run the following patches:

8570322

8685327

8761974

8771297

12672969-> instead of 8772028

8796511

8897784

8964142

8405205

21)       Create working directory on target database server.

Create a working directory named expimp in the target system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

22)       Copy scripts needed

Copy the following from Source environment “database or application servers” to the working directory on target database:

        I.            Copy initialization parameter file and CBO parameter file (initSID.ora and ifilecbo.ora “if exists”) from source database server, modify wanted parameter
      II.            Copy modified database creation scripts (adcrdb_db.sql and adcrdb_tbs.sql and adpostcrdb.sql) from source database server
    III.            Copy database preparation scripts (addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql) OR (audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql) from $APPL_TOP/admin directory of the source administration server node.
    IV.            Copy auque2.sql generated file on step 18 from source database server working directory.
      V.            Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the target database server node
    VI.            Copy $APPL_TOP/admin/adgrants.sql sql from the administration server node to the working directory in the target database server node.
  VII.            Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the working directory in the target database server node.
Create appsutil.zip file
perl /bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

Then copy it to new ORACLE_HOME then

unzip -o appsutil.zip

23)       Create the target database instance

Use modified adcrdb.sql script, generated from step 17

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> spool adcrdb.log;

SQL> startup nomount;

SQL> @adcrdb_db.sql

SQL> @adcrdb_tbs.sql

SQL> @ adpostcrdb.sql

SQL> exit;

24)     Set up the SYS schema
the audb1110.sql, audb1120.sqlor addb1020.sql script sets up the SYS schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSDBA and run as sys.

$ sqlplus “/ as sysdba” @/u01/expimp/audb1120.sql

25)     Set up the SYSTEM schema
the ausy1110.sql, ausy1120.sql or adsy1020.sql script sets up the SYSTEM schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system

$ sqlplus system/manager @/u01/expimp/ausy1120.sql

26)     Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql or adjv1020.sql script installs the Java Virtual Machine (JVM) in the database. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system.

$ sqlplus system/manager @/u01/expimp/aujv1120.sql

27)     Install other required components

the aumsc1110.sql, aumsc1120.sql or admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and as system.

$ sqlplus system/manager @/u01/expimp/aumsc1120.sql FALSE SYSAUX TEMP

28)     Install custom RDBMS components (conditional)
if you have other custom RDBMS components loaded in the source database such as Label Security; install them in the target database. To determine the RDBMS components that are loaded in the source and target databases, use SQL*Plus to connect to the databases as SYSDBA and run the following command:

SQL> select * from dba_registry;

29)     Disable automatic gathering of statistics
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> exit;

30)       Back up the target database instance
the target database instance is now prepared for an import of the Applications data. You should perform a backup before starting the import.

IV-             Export the database:

31)       Create the export parameter file

Copy $AU_TOP/patch/115/import/auexpdp.dat template file and edit it with proper values as following:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

filesize=1G

full=y

logfile=full_expdpapps.log

32)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

33)       Shut down Applications server processes

34)       Grant privilege to source system schema

$ sqlplus / as sysdba

SQL> grant EXEMPT ACCESS POLICY to system;

35)            Export the Applications database instance

If the source database is 10.2.0 or 11.1.0, use the following command:

$ expdp system/[system password] \

parfile=[export parameter file name]

If the source database is 11.2.0, use the following command:

$ expdp “‘/ as sysdba’” parfile=[export parameter file name]

36)       Revoke privilege from source system schema

SQL> revoke EXEMPT ACCESS POLICY from system;

V-                  Import the database:

37)       Create the import parameter file

Copy the export parameter file you created in Step 30 from the source database server node to the working directory in the target database server node, renaming it if necessary. Updating the new file with the following changes converts it to an import parameter file:

Remove the exclude parameters.
Remove the filesize parameter.
Change the name of the log file.
Uncomment the transform parameter.
It will be like this:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

full=y

transform=oid:n

logfile=impdpapps.log

38)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

39)       Copy the export dump files from source to target

40)       Set Oracle Text parameter

if the target database is 11.2.0, use SQL*Plus to connect to the target database as SYSDBA and run the following command to grant datastore privileges:

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter (‘file_access_role’, ‘public’);

41)            Import the Applications database instance

Start the import session on the target database server node using the customized import parameter file.

If the target database is 11.1.0, use the following command:

$ impdp system/manager parfile=[import parameter file name]

If the target database is 11.2.0, use the following command:

$ impdp “‘/ as sysdba’” parfile=[import parameter file name]

42)       Revoke privilege from target system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> revoke EXEMPT ACCESS POLICY from system;

IV-             Post-Import and database configuration:

From doc: Export/import notes on Applications 11i Database 11g [ID 557738.1]

And doc: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]

And doc Using AutoConfig to Manage System Configurations with Oracle Applications 11i [ID 165195.1]

43)       Reset Advanced Queues

Run file created in step:11.

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @/u01/expimp/auque2.sql

44)       Perform patch post-install instructions
run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.

45)       Run adgrants.sql

$ sqlplus “/ as sysdba” @adgrants.sql APPS

46)       Grant create procedure privilege on CTXSYS

$ sqlplus apps/apps @adctxprv.sql SYSTEM CTXSYS

47)       Set CTXSYS parameter

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);

48)       Deregister the current database server

$ sqlplus apps/apps

SQL> exec fnd_conc_clone.setup_clean;

49)       Some errors will be shown in the autoconfig

To avoid any error in running autoconfig in coming steps, review and verify the number and status of CTXSYS objects, synm. and grants.
To avoid any problem in INDEXES owned by CTXSYS, please run the following selected scripts from document: @jtfiappr.sql apps
Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]

enable applsys.wf_java_defered queue


some of grants may be lost so run the following list:
Compile the invalid objects.
$ sqlplus “/ as sysdba” @$ORACLE_HOME/rdbms/admin/utlrp.sql

50)       Generate your Database Context File

Ensure that shell reflect the oracle_home and bin.

From extracted appsutil.zip

cd /appsutil/bin
perl adbldxml.pl tier=db appsuser=apps

answer the question to generate XML file.

51)       Generate and Apply AutoConfig Configuration files in DB tier.

Execute the following commands:

cd /appsutil/bin
adconfig.sh contextfile=XML file created in step 50

52)       Re-create custom database links
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 dba_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.

Drop database link “PRODUCTION.HQ.ASQ.COM”;

create database link “PRODUCTION.HQ.ASQ.COM”

using ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.local)(PORT=1521))))(CONNECT_DATA=(SERVICE_NAME=UPGRD))’;

Drop public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM”;

create public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.HQ.ASQ.COM”;

create database link “EDW_APPS_TO_WH.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL”;

create database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.HQ.ASQ.COM”;

create database link “APPS_TO_APPS.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL”;

create database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.LOCAL”;

create database link “APPS_TO_APPS.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.LOCAL”;

create database link “EDW_APPS_TO_WH.LOCAL” using ’192.168.1.245:1521/UPGRD’;

53)       Generate and Apply AutoConfig Configuration files in APPS tier.

54)       Apply Oracle Human Resources (HRMS) patch 7721754

55)       Use adadmin to:

Compile invalid object
Re-create grants and synonyms using adadmin
Compile flexfield

DBA interview questions

1. How can I Export 9i data in 8i ? I mean when I Export from 9i and Import in 8i, I face errors. what sould i do?
2. How do you verify and validate the Datafile which is backed up?
3. how much time will it take a hotbackup if the batabase size is 60Gb?
4. What is mean live dead lock? How to prevent it.?
5. what is the difference between recovery and restoring of the database?
6. What are the different tools available for hot backups?
7. what is the difference between media recovery and crash recovery..?

Media recovery is a process to recover database from backup when physical disk failure occur.
Cash recovery is a automated process take care by oracle when instance failure occur.

8. what do you mean by MEDIA RECOVERY..?

Media recovery is a process of restoring the physical backups and making all the 
restored datafiles consistent with each other by having same SCN’s in their header’s.


9. what is the disk migration?what isthe steps involved in diskmigration?
10. What are the steps involved in Database Startup ?
11. What are the steps involved in Database Shutdown?
12. What is Restricted Mode of Instance Startup ?
13. What are the different modes of mounting a Database with the Parallel Server ?
14. What is Full Backup ?
15. Can Full Backup be performed when the database is open ?
16. What is Partial Backup ?
17. What is On-line Redo Log?
18. What is Mirrored on-line Redo Log ?
19. What is Archived Redo Log ?
20. What are the advantages of operating a database in ARCHIVELOG mode over operating it
21. What are the steps involved in Instance Recovery ?

Change Oracle Database Character Set : NLS_CHARACTERSET

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

ALTER DATABASE db_name CHARACTER SET new_character_set;

db_name is optional. The character set name should be specified without quotes. For example:

ALTER DATABASE CHARACTER SET AL32UTF8;

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:
4. STARTUP MOUNT;

5. ALTER SYSTEM ENABLE RESTRICTED SESSION;

6. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

7. ALTER SYSTEM SET AQ_TM_PROCESSES=0;

8. ALTER DATABASE OPEN;

9. ALTER DATABASE CHARACTER SET new_character_set;

10. SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

11. STARTUP;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;