Saturday, June 16, 2007

Creating End User Layer for R12 and Importing all Business Areas

Summary: Discoverer 10g Import of all Base EEX Files are done when the existing End User Layer needs to be rebuild or corrupted.

This action plan will help in building a fresh End User Layer from the existing EEX Files on the Middle Tier of the Server. The entire below action plan does not require any Windows Utility as 10g BI has EUL API's to handle the discoverer related activity. This enhancement help Discoverer Administrator to work more on less time with flexible EUL API utilities. The same activity could also be performed using the Discoverer Administrator Utilities on Windows. Below Action Plan helps in performing the same.

Action Plan using EUL APIs
Action Plan using Discoverer Admin Utility

Execution Summary: EUL5_US Database Export is taken as a Rollback Action Plan. The End User Layer EUL5_US is completely dropped. Using the EUL API's or DIS51ADM the new empty EUL is created. Once the EUL is Created, Generate All Business Views is run from the Oracle Applications. Once Generate All Business View is completed, Invalid Objects in the Database is compiled and made sure that there are no new invalids. Mount EBSO Application Tier Server Node $AU_TOP/discover to Oracle10gAS Discoverer Instance. Import the Contents using adupdeul.sh using MKS Toolkit and Refresh all the Business Area.

Caution: All Customers Customized Business Area and Workbooks will be completely removed when importing all the Base EEX Files.

Services / Component Affected: All Discoverer related Services

Downtime Estimation: This action plan depends on the Database Load and an average time will be around 8 Hours

Action Plan using EUL APIs:

1. Take an EUL User Database Export ( User : EUL5_US )
Operations will take the export dump and move it to $AU_TOP/discover.

2. Run the following sql;
select default_tablespace from dba_users where username='EUL5_US';

3. Drop the EUL5_US User ( drop user eul5_us cascade; )

4. Connect to the 10g Discoverer Instance

5. Make sure TNSPING Works towards the EBSO Instance from the 10g Discoverer Instance.

6. Create New Discoverer End User Layer
$ORACLE_HOME/bin/eulapi
-CREATE_EUL
-APPS_MODE
-CONNECT system/@
-USER EUL5_US
-PASSWORD
-DEFAULT_TABLESPACE
-TEMPORARY_TABLESPACE
-EUL_LANGUAGE US
-APPS_GRANT_DETAILS APPS/

7. Regenerate Business Views by running the "Generate All Business Views" Resp: Business View Setup responsibility.


8. After Regeneration of the Business Views has been completed, check the Business View Generator output file. It should not contain any errors.


9. Recompile all objects in the APPS schema using adadmin.


10. Ensure the BIS views exist and all BIS views are valid by issuing the following command in

SQL*Plus:
$ sqlplus apps/@
SQL> select object_name from user_objects
where object_type = 'VIEW' and
status = 'INVALID' and
( object_name like '%FV_%' or object_name like '%FG_%' or
object_name like '%BV_%' or object_name like '%BG_%' );

If necessary, recompile those objects so that all views are valid. The Discoverer refresh process may stop if a select statement from a invalid BIS View causes the error.

11. Mount EBSO Application Tier Server Node $AU_TOP/discover to Oracle10gAS Discoverer Instance.


12. Grant End User Layer Administration Privileges to SYSADMIN

$ORACLE_HOME/bin/eulapi
-CONNECT /@
-GRANT_PRIVILEGE
-USER SYSADMIN
-PRIVILEGE administration
-PRIVILEGE all_admin_privs
-LOG

13. Make sure user SYSADMIN has full security access to all Business Areas
$ORACLE_HOME/bin/eulapi
-CONNECT /@
-GRANT_PRIVILEGE
-USER SYSADMIN
-BUSINESS_AREA_ADMIN_ACCESS %
-WILDCARD
-LOG

14. Import Discoverer Content for Release 11i using adupdeul.sh mode=complete
sh adupdeul.sh
connect=sysadmin/sysadmin-password@< ;EBSO_DB>
resp="System Administrator"
gwyuid=APPLSYSPUB/PUB
fndnam=APPS
secgroup="Standard"
topdir=
language=US
eulprefix=EUL5
eultype=OLTP
mode=complete
iashome=
logfile=import_complete_eul_us.log

15. Refresh the Discoverer End User Layer

Action Plan using Discoverer Admin Utility:

1. Take an EUL User Database Export ( User : EUL5_US )
Operations will take the export dump and move it to $AU_TOP/discover.

2. Run the following sql;
select default_tablespace from dba_users where username='EUL5_US';

3. Drop the EUL5_US User ( drop user eul5_us cascade; )


4. Add the Instance TNS Entry to the File D:\ODisc10g\NET80\ADMIN\tnsnames.ora on Discoverer Admin Server


5. Open a Command Window and type "tnsping ORACLE_SID" and make sure you get a response.


6. Create New Discoverer End User Layer

D:/ODisc10G/bin/dis51adm.exe
/CREATE_EUL
/APPS_MODE
/CONNECT system/@$ORACLE_SID
/USER EUL5_US
/PASSWORD EUL_US
/DEFAULT_TABLESPACE
/TEMPORARY_TABLESPACE TEMP
/EUL_LANGUAGE US
/APPS_GRANT_DETAILS apps/
/SHOW_PROGRESS

7. Regenerate Business Views by running the "Generate All Business Views" Resp: Business View Setup responsibility.


8. After Regeneration of the Business Views has been completed, check the Business View Generator output file. It should not contain any errors.


9. Recompile all objects in the APPS schema using adadmin.


10. Ensure the BIS views exist and all BIS views are valid by issuing the following command in

SQL*Plus:
$ sqlplus apps/@
SQL> select object_name from user_objects
where object_type = 'VIEW' and
status = 'INVALID' and
( object_name like '%FV_%' or object_name like '%FG_%' or
object_name like '%BV_%' or object_name like '%BG_%' );
If necessary, recompile those objects so that all views are valid. The Discoverer refresh process may stop if a select statement from a invalid BIS View causes the error.

11. Copy all the contents from discover directory from $AU_TOP directory to Local Machine where Discoverer Administrator is Installed ( Directory : D:EEX$ORACLE_SID )


12. Connect as EUL5_US on Discoverer Administrator and grant access to User SYSADMIN and System Administrator Responsibility.

Open Discoverer Administrator Edition 10g (D:Odisc10gbindis51adm.exe)
Connect as EUL5_US with the Standard Password
Make sure you De-Select "Oracle Application User"
Select the Priviledge Dialog from Tools >> Privileges
From the Privileges tab, select the Applications user 'SYSADMIN' from the pull down menu.
In the Privilege window, click the checkboxes for:
Administration,
Format Business Area
Create/Edit Business Area
Create Summaries
Set Privilege
Manage Schedule Workbook
Click Apply Button to Save
Repeat the same grant for the Oracle Applications responsibility 'System Administrator'.
Ensured that Discoverer Admin Edition is able to connect to the Instance as SYSADMIN and Resp System Administrator.
Note : Make sure that you select "Oracle Application User" option.

13. Import Discoverer Content for Release 11i using adupdeul.sh mode=complete
sh adupdeul.sh
connect=sysadmin/@
resp="System Administrator"
gwyuid=APPLSYSPUB/PUB
fndnam=APPS
secgroup="Standard"
topdir=D:/EEX/XXXXX/discover
language=US
eulprefix=EUL5
eultype=OLTP
mode=complete
iashome=D:/ODisc10g/bin
logfile=import_complete_eul_us.log

14. Refresh the Discoverer End User Layer

Popular Posts