ITExperts4u Blog’s

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

• Consistent Gets – The number of accesses made to the block buffer to retrieve data in a consistent mode.
• DB Blk Gets – The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
• Physical Reads – The cumulative number of blocks read from disk.

• Logical reads are the sum of consistent gets and db block gets.
• The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
• Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select    sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
        sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
        sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
        round((sum(decode(name, 'consistent gets',value, 0)) +
               sum(decode(name, 'db block gets',value, 0)) -
               sum(decode(name, 'physical reads',value, 0))) /
              (sum(decode(name, 'consistent gets',value, 0)) +
               sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from   v$sysstat

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

• Gets – Total number of requests for information on the data object.
• Cache Misses – Number of data requests resulting in cache misses

• Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select    sum(GETS),
        sum(GETMISSES),
        round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from    v$rowcache

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

• Pins – The number of times a pin was requested for objects of this namespace.
• Reloads – Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

• Hit Ratio should be > 85%

select    sum(PINS) Pins,
        sum(RELOADS) Reloads,
        round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from    v$librarycache

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

• Executions – The number of times a pin was requested for objects of this namespace.
• Cache Misses – Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

• Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select    sum(PINS) Executions,
        sum(RELOADS) cache_misses,
        sum(RELOADS) / sum(PINS) miss_ratio
from    v$librarycache

? Microsoft Office 2007 Enterprise Volume Version (Eng and Kor)
? UltraISO Premium Edition 8.6.1.1985
? Nero Burning ROM 6.6.1.15 Ultra Edition

1. Make New Folder (Example => C:\OFFICE12)

2. UltraISO Run => File => Open => Office 2007 image File (Example => OFFICE12.ISO)
=> UltraISO Actions Menu => Extract.. => C:\OFFICE12 => OK => Exit

3. Windows Start Menu => Run => Type C:\OFFICE12\Setup /admin => OK

4. Office user appointment tool appears

5. Select => To make new establishment user appointment file for next product => OK
(Microsoft Office Enterprise 2007)

6. Each corrects menu

7. File => Save (YourFileName.MSP) => C:\OFFICE12 (Example => Setup.MSP) => Exit

8. Open C:\OFFICE12 Folder => Open autorun.inf => Fix => Save => Exit

Original
[autorun]
OPEN=SETUP.EXE
ICON=SETUP.EXE,0

shell\configure=&Configure…
shell\configure\command=SETUP.EXE

shell\install=&Install…
shell\install\command=SETUP.EXE

Fix
[autorun]
OPEN=SETUP.EXE /adminfile Setup.MSP
ICON=SETUP.EXE,0

shell\configure=&Configure…
shell\configure\command=SETUP.EXE

shell\install=&Install…
shell\install\command=SETUP.EXE

9. UltraISO Run => Select all folders and file in OFFICE12 folder
=> Selecting all folders and file do drag by a mouse inside UltraISO
=> Save (Example => OFFICE_2007_Auto.ISO)

10. Burn! OFFICE_2007_Auto.ISO

11. End..

I succeeded like this

Manual modification of the OID database is not supported. The proper way is to always restore from a valid backup.
However, in certain circumstances you may need to delete the duplicate rows from the CT_DN table. In such circumstances you may need to follow the steps given below. You MUST take a backup of system before proceeding with the steps below:

REMOVING DUPLICATE ENTRIES:

1) In order to determine duplicate values in table cn_dn issue the following command while logged in as ODS user:
select rdn, parentdn, count(*)
from ct_dn
group by rdn, parentdn
having count(*) > 1;

2) If (1) returns any values then for sanity reasons we need to find out which rows we need to keep.
You can do that using the following sql:

select min(rowid),rdn, parentdn
from ct_dn
group by rdn, parentdn;

3) Now you can delete rows from ct_dn table using the following command:

delete from ct_dn
where rowid not in (select min(rowid) from ct_dn group by rdn,parentdn);

4) You will need to do the same thing for any other tables that have duplicate rows.
(Obs: Because the other table named here generic TABLENAME should have different columns name
sql statements are similar
for 2. select ENTRYID, count(*) from TABLENAME group by ENTRYID having count(*) > 1;
for 3. select min(rowid),ENTRYID from TABLENAME group by ENTRYID;
for 4. delete from TABLENAME where rowid not in (select min(rowid) from TABLENAME group by ENTRYID);
)

Please note there’s an index on CT_DN table which prevents this. You will need to check if you have the right indexes on your table.

5) Run the following selects using sqlplus when logged in as ODS:

- select index_name, uniqueness from user_indexes where table_name=’CT_DN’;
- select index_name, column_name, column_position from user_ind_columns
where table_name=’CT_DN’ order by 1,3;

It should return 3 indexes:

INDEX_NAME UNIQUENESS
—————————— ———-
EP_DN UNIQUE
RP_DN UNIQUE
PN_DN NONUNIQUE

The index RP_DN which consists of two columns, parentdn and rdn makes sure that we can’t have duplicate DNs. Check that this index exists and that it is unique, and that it covers correct columns.

If you do not have the index RP_DN then most likely your result will look like this:

SQL> select index_name, uniqueness from user_indexes where table_name=’CT_DN’;
INDEX_NAME UNIQUENESS
—————————— ———-
EP_DN UNIQUE
PN_DN NONUNIQUE

SQL> select index_name, column_name, column_position from user_ind_columns
2 where table_name=’CT_DN’ order by 1,3;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
—————————— ————————- —————
EP_DN ENTRYID 1
EP_DN PARENTDN 2
PN_DN PARENTDN 1

As discussed earlier there should be an index called “RP_DN” listed – notice it is not.
Since it is missing you can create it with the following sql but ONLY after you have removed duplicate rows
:

CREATE UNIQUE INDEX rp_dn on ct_dn (parentdn,rdn)
TABLESPACE OLTS_IND_CT_DN
PARALLEL 2 UNRECOVERABLE;

After you have created the index you will see the following result:

select index_name, uniqueness from user_indexes where table_name=’CT_DN’;

INDEX_NAME UNIQUENESS
—————————— ———-
EP_DN UNIQUE
RP_DN UNIQUE
PN_DN NONUNIQUE

select index_name, column_name, column_position from user_ind_columns
where table_name=’CT_DN’ order by 1,3;

INDEX_NAME COLUMN_NAME COLUMN_POSITION
—————————————————————
EP_DN ENTRYID 1
EP_DN PARENTDN 2
PN_DN PARENTDN 1
RP_DN PARENTDN 1
RP_DN RDN 2

1) Finding the GUID of a User

a. Using SQL
SQL> select guid from portal.wwsec_person$ where user_name = ‘<user_name>’;
b. Using ldapsearch:
ldapsearch -h myhost -p 3060 -D cn=orcladmin -w <GUI_password_for_orcladmin_user> -b “” -s sub “cn=<cn_for_problem_user>” “*” orclguid

2) Manually updating a users GUID
SQL> update portal.wwsec_person$ set guid = <OID_user_GUID> where user_name =’user_name’;
SQL> commit;

I had a recent activity which involved configuring Oracle BI Applications to utilize the preconfigured reports that come with it.

I was able to do the entire setup on my Dell Vostro with 4GB RAM and 2.2 GHz processer, but for better performance I would recommend better hardware.

When I initially started I had difficulty as the Oracle Documentation is very vast and it’s not specific to Oracle EBS, and deals with PeopleSoft, JD Edwards, Siebel and Oracle EBS moreover you will have to follow different guides for OBI Apps, Informatica and OBIEE.

Thus I have compiled this precise document which should help anyone who is planning this setup.

The following Steps also include the common issues you might encounter during setup and their solution.

Before proceeding you will require the following software’s.

1) Oracle Business Intelligence Enterprise Edition 10.1.3 http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/index.html

2) Oracle Business Intelligence Applications 7.9.6 http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/business-intelligence-10g-165415.html

3) Oracle Business Intelligence Data Warehouse Console 10.1.3 http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/business-intelligence-10g-165415.html

4) Oracle EBS R12(Which will be the source)

5) Informatica 8.6 with hot fix 6 (You will get this along with the CD’s for Oracle BI Apps), if you don’t then try torrents.

6) Oracle Database 10g(Which will hold DAC and Informatica Repositories) http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Assumptions:-

I am assuming that you have already installed Oracle BI Enterprise Edition 10(which should be a complete installation).

I am assuming that you have already installed an Oracle 10g/11g database to store the DAC Repository, Informatica Repository and the Oracle Business Analytics Warehouse.

I am assuming that you have already installed Oracle EBS R12

Step 1)

Database Configurations -

Create two users dac_rep and info_rep which will be the schemas for the DAC Repository and Informatica Repository.

Creating schema’s :-

Create user dac_rep identified by dac_rep;

Create user info_rep identified by info_rep;

**For Performance you can give separate tablespaces for both the users.

Creating SSE_ROLE

Create role sse_role;

Grant connect,resource to sse_role;

Grant sse_role to dac_rep

Step 2)

Installing Oracle BI Apps

1) Click on Setup.exe -> on welcome page click next ->Give the locations of Oracle BI Infrastructure and Oracle BI Data(Ex: – ‘D:/OracleBI’ and ‘D:/OracleBIData’) -> select the application you want to install, select Financial Analytics Now wait after clicking on next till the finish button is displayed->click on finish

Step 3)

Installing Informatica Power Center

a) Installing informatica Power Center Client Tools

Run the install.exe from PowerCenter_8.6.1_SE_for_Windows_32bit, follow the instructions of the installation wizard

b) Installing Informatica PowerCenter HotFix 6

Install Informatica PowerCenter Hotfix 6 from the PowerCenter_8.6.1_HotFix6_for_Windows_32bit follow the instructions of the installation wizard.

c) Installing Informatica Power Center Services 8.6.1

Run the install.bat from PowerCenter_8.6.1_SE_for_Windows_32 , follow the instructions of the installation wizard.

d) Install the Informatica PowerCenter Hotfix 6

Install the Informatica PowerCenter Hotfix 6 from the PowerCenter_8.6.1_HotFix6_for_WIN_32 and follow the instructions of the installation wizard.

Step 4)

Creating the Informatica Repository Service

Go to Start->Programs->Informatica->Informatica PowerCenter Administration Console

On the left side of the Administration Console click create and then select ‘Create Repository Service’

The following are the options:-

Service Name – Name of the Repository Service

This name should be same as your OBIEE Repository name that got installed with , ex:- Oracle_BI_DW_Base, which got installed during the installation of Oracle BI Applications(in location OracleBI\dwrep\Informatica\Repository)

If you name it to something else other than the repository than you will have problems later as oracle ‘assumes’ that you shall name it the same as Oracle BI Repository,

Unfortunately I did the mistake of naming it different and it took me a week to realize what the problem was!

Location – Domain and Folder where the service is created

License – Leave it as it is

Version – Leave it as it is

Node- Leave it as it is

Primary Node – Leave it as it is

Backup Nodes – Leave it as it is

Database Type – Leave it as it is

CodePage – Leave it as it is

ConnectString – Use the service name of the database 10g Database, that will hold the informatica repository i.e orcl

DBUser – info_rep

DBPassword – Password of info_rep

Tablespace_name – Leave it as it is

Creation Mode – Select ‘Create new repository content’

Enable the repository Service – Select this option to enable the service after its creation

Step 5)

Creating the Informatica Integration Service

Go to Start->Programs->Informatica->Informatica PowerCenter Administration Console

Service Name – Name of the Integration service

Location – Leave it as it is

License – Leave it as it is

Version – Leave it as it is

Assign – Leave it as it is

Run the Service on Grid – Leave it as it is

Primary Node – Leave it as it is

Backup Nodes – Leave it as it is

Domain of Associated Repository Service – Leave it as it is

Associated Repository Service – Select the Repository

Repository User Name – info_rep

Repository Password – info_rep

Security Domain – Leave it as it is

Data Movement Mode – Leave it as it is

Step 6)

Restoring the Prebuilt Informatica Repository

1) Copy the file Oracle_BI_DW_Base.rep from OracleBI\dwrep\Informatica\Repository to \Informatica PowerCenter8.6.1\server\infa_shared\Backup directory.

2) In Informatica PowerCenter Administration Console, select Oracle_BI_DW_Base

3) In the General Properties area of the Properties tab, make sure the OperatingMode value is Exclusive.

To change the Operating Mode value, click Edit, and then select a new value from the drop-down list. Click OK to exit edit mode.

4) Choose Actions, then Delete Contents.

5) At the Delete Contents for dialog box, enter Oracle_BI_DW_Base and password (for example, Administrator\Administrator), then click OK.

6) Choose Actions > Restore Contents.

7) At the Restore Contents dialog box, select Oracle_BI_DW_Base.rep from the Select Backup File drop-down list.

Select the Restore as New check box.

9) Click OK to start the restore process.

10) When the restore process is complete click close

11) When a repository is restored, the repository becomes a standalone repository. After restoring the repository, you need to promote it to a global repository.

12) In the Administration Console, select Oracle_BI_DW_Base.rep .

13) If the Repository Service is running in normal mode, change the operating mode to exclusive.

14) If the Repository Service is not enabled, click Enable.

15) In the general properties for the service, click Edit.

16) Select GlobalRepository, and click OK

17) Enter your user name, password, and security domain,Click OK.

18) Change the OperatingMode value to Normal.

a. Go to the Properties tab.

b. In the General Properties area, click Edit.

c. Click the OperatingMode drop-down list, and select Normal.

19) If prompted, enter the repository username and password.

Step 7)

Copying Source Files and Lookup Files

1) Copy the source files in \OracleBI\dwrep\Informatica\SrcFiles on the machine where the Oracle BI Applications installer was run to the source files directory on the PowerCenter Services machine, for example \Informatica PowerCenter8.6.1\server\infa_shared\SrcFiles

2) Copy the lookup files in \OracleBI\dwrep\Informatica\LkpFiles on the machine where the Oracle BI Applications installer was run to the lookup files directory on the PowerCenter Services machine, for example Informatica PowerCenter8.6.1\server\infa_shared\LkpFiles.

Step

Setting PowerCenter Integration Services Custom Properties

To set Informatica PowerCenter Integration Services Custom Properties, In Informatica PowerCenter Administration Console, select the Integration Service -> Click the Properties tab -> In the Custom Properties area, click Edit -> Create a custom property by clicking Add to display new Name and Value fields

Click Add and add the following

ServerPort 4006(Default)

overrideMpltVarWithMapVar Yes

Click ok

Step 9)

Creating the Repository Administration User in Native Security Domain

1) Go to PowerCenter Administration Console -> click Configure Security icon in the top, right corner of the page-> on security page, click Create User, or select an existing user defined in the native domain

2) On the Privileges tab of the new or existing user, click the appropriate Repository Service.

3) In the Edit Roles and Privileges page, expand the Repository Service, and under System-Defined Roles, select the Administrator role check box, Click OK

Step 10)

Installing and configuring DAC

1) Click on Setup.exe in Oracle Business Intelligence Data Warehouse Administration Console directory ->Next-> In the Specify Installation Location, enter the path where you want to install DAC -> select the informatica version 8.x -> Specify the locations of Informatica PowerCenter Services and Informatica PowerCenter domain file -> click install-> Finish

2) For JDBC Connectivity copy the file ojdbc14.jar from jdbc\lib(ex:- C:\oracle\product\10.2.0\db_1\jdbc\lib\classes14.jar) to dac\lib(as I am using database 10g if its 11g then copy ojdbc6.jar from jdbc\lib to dac\lib)

Step 11)

Creating ODBC Connection on Windows

Control Panel ->Administrative Tools ->Data Sources(ODBC)->SYSTEM DSN T->Add->select Oracle Merant ODBC

Enter the following details

Data Source Name – EBS R12

Server Name – apps

Client version – 10gR1

Click Test to check for connectivity.

Step 12)

Copying the Metadata Files

1) Copy the Folder name ‘Export’ from ‘OraclelBI\dwrep\DAC_metadata\DAC_Client\export’ to ‘dac\export’

2) Copy the file ‘deletetriggers.list’ from ‘OraclelBI\dwrep\DAC_metadata\DAC_Client’ to ‘dac\conf’

3) Copy the file ‘parameterfileOLTP.txt’ from ‘OraclelBI\dwrep\DAC_metadata\DAC_Server’ to ‘dac\Informatica\parameters\input’

4) Copy the file ‘parameterfileDW.txt´from ‘OraclelBI\dwrep\DAC_metadata\DAC_Server’ to ‘dac\Informatica\parameters\input’

Step 13)

Upgrading the DAC Repository Schema

1) Start the DAC Client clicking on startclient.bat from bifoundation\dac directory -> Click Configure->Create Connection->Connection Type->Enter ‘orcl’ as connection string->Host name of orcl database->1521->leave default->leave default->click on create authentication file-> navigate to the folder where you want to store the file, click OK->Enter a unique name for Authentication file, click OK->enter table owner name as ‘dac_rep’ and password, click test connection->Apply->Finish

2) In the Login Dialog box select the connection->enter username as Administrator->enter password ->login ->Click YES for ‘Create a repository’

Step 14)

Importing metadata in DAC Repository

Go to DAC->Tools->DAC Repository Management->Import->select Logical Check and System Check -> In the Application List table select Universal and Oracle R12.1(Our Source System version)->Select Truncate Repository check box->select Enable batch mode check box ->click ok->to confirm retype the text in text box and click yes->click yes->click OK.

Step 15)

Creating the Business Analytics Warehouse Tables

Go to DAC ->tools->ETL Management->Configure->in source dialog box select target and source database ->Click OK->Data Warehouse Configuration Wizard->select create data warehouse tables check box->click next->

In the Data Warehouse tab enter the details

Database type -> Oracle

Container-> Leave Blank

Table Owner-> DAC_REP

Password -> Password

ODBC Data Source -> ORCL

Data Area -> Leave as it is

Index Area -> Leave as it is

Select ‘Is Unicode’ option then Click Start

Step 16)

Configuring the Connection between DAC Server and DAC Client

Go to DAC->Tools-> DAC Server Management-> DAC Server Setup

In the Repository Connection Information Tab enter the following details:-

Connection Type – Oracle

Connection Fields – Select OCI8 and enter TNSNAME as ‘orcl’

Table Owner Name – dac_rep

Password – Password

Click |Test to check if its working and then click on save

Step 17)

Setting DAC System Properties

Go to DAC->Views menu->Setup-> DAC System Properties

DAC Server Host – Host Name where you installed DAC Server

DAC Server OS – Windows

InformaticaParameterFileLocation – \Informatica\PowerCenter8.6.1\ server\infa_shared\SrcFiles

Step 18)

Registering Informatica Services in DAC

Go to DAC ->Setup View->Informatica Servers Tab

1) Modify the record “INFORMATICA_DW_SERVER”

Name – Integration Service Name(It need not be the same as the one created in Step 5)

Type – Informatica

Service – Integration Service Name (as created in Step 5)

Server Port – 4006

Domain – Informatica Domain name

Login – Administrator

Password – Informatica Repository Password

Maximum Sessions – Leave the Default to 10

Repository Name – Oracle_BI_DW_Base.rep

Inactive – Leave it as default

Test to check if it’s working fine

Click Save

2) Modify the record “INFORMATICA_REP_SERVER”

Name – Repository Service Name (It need not be the same as the one created in Step 4)

Type – Repository

Hostname – Hostname of Repository Server

Server Port – 6001

Login – Administrator

Password – Password

Maximum Sessions – 10

Repository Name – Oracle_BI_DW_Base.rep

Inactive – Leave it as default

Test to check if it’s working fine

Click Save

Step 19)

Setting Physical Data Sources

Go to DAC -> Setup-> Physical Data Sources

Name – DataWarehouse (Do not Change this to anything else, this is the default)

Type – Warehouse

Connection Type – Oracle(OCI

Instance or TNS Name – ORCL

Table Owner – DAC_REP

Table Owner Password – Password

Max Num Connections – Leave as default

DB Host – Full name of Database Instance (ex:- orcl.domain.com)

Port – 1521

Dependency Priority – Leave as default

Data Source Number – Leave as default

Default Index Space – Leave as default

Test to check if it’s working fine

Click Save

Step 20)

Configuring Relational and Application Connections in Informatica Workflow Manager

Goto Informatica PowerCenter Workflow Manager from Start->Programs

Click on Repository->Add->Add Repository Dialogue box ->

Repository – Oracle_BI_DW_Base

Username – Administrator

Choose the repository and click on Connect->Connect to Repository dialog box appears

Password – Administrator

Click Add beside Add Domain box

Specify the Domain that was created during Informatica PoweCenter Services installation

Hostname – Full Name of machine(Ex: hostname.domain.com)

Gateway – 6001

Click OK and Save and click on Connect

Step 21)

Configuring Relational Connections

Goto Informatica PowerCenter Workflow Manager from Start->Programs

New -> select Oracle as database type ->OK

Define the Connections in relational connection dialog box

Name – DataWarehouse( This should be exactly same as the one in Physical Data Sources tab in DAC, else it will cause inconsistency between DAC and Informatica

Type – Oracle

User Name – info_rep

Password – Password

Connect String – Connection String of the database

CodePage – Your Code page

Step 22)

Configuring Oracle BI Repository Connections

In the physical layer click on Oracle Data Warehouse , Enter the connection information for data warehouse in Oracle Data Warehouse Connection Pool and Oracle Data Warehouse Repository Initblocks Connection Pool.

Step 23)

Configuring Connection Pools

Goto File Open in Oracle BI Administration Tool and open OracleBIAnalyticsApps.rpd file from \OracleBI\Server\Repository

In the Physical pane, double-click the Oracle Data Warehouse object, in the database field specify Oracle and click on Save, click Yes when it asks to check global Consistency, click OK when the warnings are displayed.

In the Physical Pane double-click the Oracle Data Warehouse Connection Pool to display the Connection Pool dialog.

Do not change the below fields starting with VALUEOF, these are Functions which will update the value from Variables Manager, which will be configured in next step.

Data source name – ‘VALUEOF(ORACLE_OLTP_USER)’

User name field – ‘VALUEOF(ORA_EBS_OLTP_DSN)’

Password – Password

Step 24)

Configuring Repository Variables

In Oracle BI Administration Tool click on Manage -> Variables ->Static

OLAP_DSN – Data Source Name of warehouse database

OLAP_USER – DAC_REP

OLAPTBO – DAC_REP

GLOBAL_CURRENCY1 – Same as the one specified in Source System Parameter values in DAC

GLOBAL_CURRENCY2 – Same as the one specified in Source System Parameter values in DAC

GLOBAL_CURRENCY3 – Same as the one specified in Source System Parameter values in DAC

ORA_EBS_OLTP_DSN – EBS R12(Created in Step 11)

ORA_EBS_OLTP_USER – dac_rep

Step 25)

Creating a new Container

You will not be able to edit the pre-configured containers so create a new container.

Goto DAC -> File -> New Source System Container -> Create as a Copy of Existing Container-> select Universal from drop down -> OK

Step 26)

Creating an ETL Process

In DAC -> Goto Execute view -> click on Execution Plan tab -> New -> Edit -> Specify Name of the ETL Process -> Goto Subject Areas->Click on Add/Remove -> Select the new container that you created in above step -> Select Financials – Payables or anything click Add and OK

On the Parameters tab click Generate , select the below values

DBConnection_OLAP – DataWarehouse

DBConnection_OLTP – ORA_R12

Click Build

Do not Click Run Now as you will get an error ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found during Full Load of ETL

There is a bug in R12 Vision that has to be removed before running a full ETL

Go to the Source system, connect using apps, issue the following command

SQL> SELECT person_id, effective_start_date, effective_end_date FROM per_all_people_f WHERE person_id = 6272;

person_id

effective_start_date

effective_end_date

6272

04-JAN-91

06-FEB-02

6272

04-JAN-91

31-DEC-12

As you can see person_id ’6272′ has duplicate values effective_start_date, change the effective_start_date in the second row to a date after 06-FEB-02 or delete that record by giving

SQL> delete from per_all_people_f where person_id=6272 and effective_end_date =’06-FEB-02′;

1 row deleted.

Now Goto DAC and click on Run Now to Start Full Load.

Concurrent Manager FAQs

Q: Which Concurrent Managers (CCMs) are supported by the Sysadmin team?

* Internal Concurrent Manager (ICM)
* Standard Manager
* Conflict Resolution Manager (CRM)

The setup of product-specific managers related issues should

be directed to the associated product group, i.e. the Inventory manager

Q: Where do the concurrent request output and log files reside?

A: $APPLCSF/out or $Product_Top/out for output files

$APPLCSF/log or $Product_Top/log for log files

Q: Where are temporary files located?

A: Temporary files reside in:

$APPLPTMP$APPLTMP

$REPORTS25_TMP (Release 11.0)

$REPORTS60_TMP (Release 11.5)

Q: How to change PMON method to LOCK.

To check the process monitor (PMON) method status:

Connect as database user “APPS

How To License A New Product in Oracle Applications

1. After purchasing a license for an Application Product/Module, the first thing is to use license manager to record that the product/module is now licensed. License manager can be run 2 ways:

a. log in through Oracle Application Manager (OAM) and select Site Map -> Administration tab -> License Manager

b. run adlicmgr.sh from AD_TOP/bin

Using both methods you can license by Module or by individual Product. Select the check box for the Module/Product to be licensed and Save/Next

2. The Products are now licensed but not ready to be used as none of the Forms/Packages etc would have been compiled and loaded into the Database for these new Products. Use adadmin to generate the files for the new Product/s

a. run adadmin

b. Select Generate Application Files menu

c. Select all of the following Menu options:
- Generate message files
- Generate forms files
- Generate report files
- Generate graphic files
- Generate product JAR files

d. Back to Main Menu

e. Select Compile/Reload Applications Database Entities menu

f. Select all of the following Menu options:
- Compile APPS schema
- Compile menu information
- Compile flexfields

3. Once complete, check that the Forms/JSPs for the new Products licensed now work. The Products are now ready to be configured.

4. Run autoconfig.

I’ve been using PuTTY for years, its easy to use. So far, only one disadvantage I found. It really pain to copy PuTTY settings one by one from one Windows machine to another. Especially when you have a lot of entries saved in there.

Here I’ll write a trick to backup and restore PuTTY settings from one Windows machine to another. This trick tested on Windows XP and Vista.

You’ll need administrator access to both machines as we will be using Windows Registry Editor to backup and restore. Windows Registry Editor is a powerful tools, please be careful with anything you do.

Here we go…

To backup:

* Windows XP:

Click Start Menu >> Run

Windows Vista:

Click Start Menu >> Start Search box

* Type:

regedit

* Hit Enter or Return key, it will open Windows Registry Editor
* Navigate to:

HKEY_CURRENT_USER\Software\SimonTatham

* Right click on “SimonTatham” key >> Export
* Save as putty.reg and put it somewhere.

To restore:

* Copy/transfer putty.reg to another machine which already have PuTTY installed.
* Double click putty.reg file to import the entries to registry

Here are the list of Oracle Metalink Notes, I do refer regularly. Moreover, these are very informative. So, I thought of sharing those note ids here with you.

Note: You have to have an account in Oracle Metalink to access notes.

Installation

Note: 452120.1 – How to locate the log files and troubleshoot RapidWiz for R12
Note: 329985.1 – How to locate the Rapid Wizard Installation log files for Oracle Applications 11.5.8 and higher
Note: 362135.1 – Configuring Oracle Applications Release 11i with Oracle10g Release 2 Real Application Clusters and Automatic Storage Management
Note: 312731.1 – Configuring Oracle Applications Release 11i with 10g RAC and 10g ASM
Note: 216550.1 – Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 279956.1 – Oracle E-Business Suite Release 11i with 9i RAC: Installation and Configuration using AutoConfig
Note: 294932.1 – Recommendations to Install Oracle Applications 11i
Note: 403339.1 – Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite Release 12.0.4 Upgrade
Note: 455398.1 – Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 11i
Note: 402311.1 – Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Microsoft Windows
Note: 405565.1 – Oracle Applications Release 12 Installation Guidelines

AD Utilities

Note: 178722.1 – How to Generate a Specific Form Through AD utility ADADMIN
Note: 109667.1 – What is AD Administration on APPS 11.0.x ?
Note: 112327.1 – How Does ADADMIN Know Which Forms Files To Regenerate?
Note: 136342.1 – How To Apply a Patch in a Multi-Server Environment
Note: 109666.1 – Release 10.7 to 11.0.3 : What is adpatch ?
Note: 152306.1 – How to Restart Failed AutoInstall Job
Note: 356878.1 – How to relink an Applications Installation of Release 11i and Release 12
Note: 218089.1 – Autoconfig FAQ
Note: 125922.1 – How To Find Oracle Application File Versions

Cloning

Note: 419475.1 – Removing Credentials from a Cloned EBS Production Database
Note: 398619.1 – Clone Oracle Applications 11i using Oracle Application Manager (OAM Clone)
Note: 230672.1 – Cloning Oracle Applications Release 11i with Rapid Clone
Note: 406982.1 – Cloning Oracle Applications Release 12 with Rapid Clone
Note: 364565.1 – Troubleshooting RapidClone issues with Oracle Applications 11i
Note: 603104.1 – Troubleshooting RapidClone issues with Oracle Applications R12
Note: 435550.1 – R12 Login issue on target after cloning
Note: 559518.1 – Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
Note: 216664.1 – FAQ: Cloning Oracle Applications Release 11i

Patching

Note: 225165.1 – Patching Best Practices and Reducing Downtime
Note: 62418.1 – PATCHING/PATCHSET FREQUENTLY ASKED QUESTIONS
Note: 181665.1 – Release 11i Adpatch Basics
Note: 443761.1 – How to check if a certain Patch was applied to Oracle Applications instance?
Note: 231701.1 – How to Find Patching History (10.7, 11.0, 11i)
Note: 60766.1 – 11.0.x : Patch Installation Frequently Asked Questions
Note: 459156.1 – Oracle Applications Patching FAQ for Release 12
Note: 130608.1 – AdPatch Basics
Note::60766.1 – Patch Installation FAQ (Part 1)

Upgrade

Note: 461709.1 – Oracle E-Business Suite Upgrade Guide – Plan
Note: 293166.1 – Previous Versions of e-Business 11i Upgrade Assistant FAQ
Note: 224875.1 – Installation, Patching & Upgrade Frequently Asked Questions (FAQ’s)
Note: 224814.1 – Installation, Patching & Upgrade Current Issues
Note: 225088.1 – Installation, Patching & Upgrade Patches Guide
Note: 225813.1 – Installation, Patching & Upgrade Setup and Usage Guide
Note: 224816.1 – Installation, Patching & Upgrade Troubleshooting Guide
Note: 216550.1 – Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 362203.1 – Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Note: 423056.1 – Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.2)
Note: 726982.1 – Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.3)
Note: 452783.1 – Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
Note: 406652.1 – Upgrading Oracle Applications 11i DB to DB 10gR2 with Physical Standby in Place
Note: 316365.1 – Oracle Applications Release 11.5.10.2 Maintenance Pack Installation Instructions
Note: 418161.1 – Best Practices for Upgrading Oracle E-Business Suite

Printer

Note: 297522.1 – How to investigate printing issues and work towards its resolution ?
Note: 110406.1 – Check Printing Frequently Asked Questions
Note: 264118.1 – Pasta Pasta Printing Setup Test
Note: 200359.1 – Oracle Application Object Library Printer Setup Test
Note: 234606.1 – Oracle Application Object Library Printer Initialization String Setup Test
Note: 1014599.102 – Subject: How to Test Printer Initialization Strings in Unix

Performance

Note: 390137.1 – FAQ for Collections Performance
Note: 216205.1 – Database Initialization Parameters for Oracle Applications Release 11i
Note: 169935.1 – Troubleshooting Oracle Applications Performance Issues
Note: 171647.1 – Tracing Oracle Applications using Event 10046
Note: 153507.1 – Oracle Applications and StatsPack
Note: 356501.1 – How to Setup Pasta Quickly and Effectively
Note: 333504.1 – How To Print Concurrent Requests in PDF Format
Note: 356972.1 – 11i How to troubleshoot issues with printers

Others

Note: 189367.1 – Best Practices for Securing the E-Business Suite
Note: 403537.1 – Best Practices For Securing Oracle E-Business Suite Release 12
Note: 454616.1 – Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Note: 394692.1 – Oracle Applications Documentation Resources, Release 12
Note: 370274.1 – New Features in Oracle Application 11i
Note: 130183.1 – How to Get Log Files from Various Programs for Oracle Applications
Note: 285267.1 – Oracle E-Business Suite 11i and Database FAQ
Note: 453137.1 – Oracle Workflow Best Practices Release 12 and Release 11i
Note: 398942.1 – FNDCPASS Utility New Feature ALLORACLE
Note: 187735.1 – Workflow FAQ – All Versions

Oracle Applications 11i/12 Online Documentation

http://www.oracle.com/technology/documentation/applications.html

Note: Above are only the few important notes, there are bunch of notes on many other issues and topics in the metalink. I would suggest one to refer the metalink for more note ids.

Leave your comments if you feel these Oracle Metalink Notes help you in solving your Application issues.

##
## before cleanup
##

13:42:56 APPS@XTPR:host01> r
1 select node_name, node_mode, support_cp,
2 support_web, support_admin, support_forms
3* from FND_NODES

NODE_NAME N S S S S
—————————— – - – - –
HOST01 O Y N Y N
MIDTIER01 O N Y N Y
APPSERVER21 N Y N Y <– bogust host
APPSERVER22 N Y N Y <– bogust host
APPSERVER23 N Y N Y <– bogust host
AUTHENTICATION O N N N N

6 rows selected.

##
## Cleanup
##

13:42:58 APPS@XTPR:host01> EXEC FND_CONC_CLONE.SETUP_CLEAN;

PL/SQL procedure successfully completed.

13:44:12 APPS@XTPR:host01> commit;

Commit complete.

##
## After cleanup
##

13:44:47 APPS@XTPR:host01> r
1 select node_name, node_mode, support_cp,
2 support_web, support_admin, support_forms
3* from FND_NODES

no rows selected

##
## Run AutoConfig
##

## BE tier
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/XTPR_host01.xml appspass=<pass>

## MT tier
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/XTPR_midtier01.xml appspass=<pass>

##
## After AutoConfig runs
##

13:58:15 APPS@XTPR:host01> r
1 select node_name, node_mode, support_cp,
2 support_web, support_admin, support_forms
3* from FND_NODES

NODE_NAME N S S S S
—————————— – - – - –
MIDTIER01 O N Y N Y
AUTHENTICATION O N N N N
HOST01 O Y N Y N

3 rows selected.

Important Note

Be careful not to run this on production, it cleans out all printer
definitions, previsously executed requests logs and outputs. It is okay to run
only on the freshly created environment.