Search This Blog

Thursday 16 January 2014

How to refresh GUIDs for OBIEE 11g ?

Purpose

The purpose of this document is to provide clear steps for refreshing GUIDs in OBIEE 11g. The document also shows what to expect in the logs to confirm GUID refresh has completed successfully.

Details

1) Stop all obiee services (opmnctl stopall) 

2) Edit presentation server instanceconfig.xml with the following - put it inside Catalog tag
<UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs> 

3) Edit obiee server NQSconfig.INI file with the following
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES; 

4) Start OBIEE services (opmnctl startall) 

5) Wait until presentation services are listed as down (opmnctl status) 

6) stop obiee services (opmnctl stopall) 

The saw log should show the following:
-----------------
Saw.log
[2012-12-19T09:38:03.000-07:00] [OBIPS] [NOTIFICATION:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] Starting to update Account GUIDs[[
File:initializecatalog.cpp
Line:1046
Location:
   saw.subsystem.catalog.initialize.upgrade
   saw.subsystem.catalog.initialize
   saw.subsystems.catalogbootstrapper.loadcatalog
   saw.webextensionbase.init
   saw.sawserver
ecid:
]]
[2012-12-19T09:38:07.000-07:00] [OBIPS] [NOTIFICATION:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] Succeeded in updating account GUIDs from back end user population store[[
File:initializecatalog.cpp
Line:1131
Location:
   saw.subsystem.catalog.initialize.upgrade
   saw.subsystem.catalog.initialize
   saw.subsystems.catalogbootstrapper.loadcatalog
   saw.webextensionbase.init
   saw.sawserver
ecid:
]]

7) Edit presentation server instanceconfig.xml with the following
<UpdateAccountGUIDs>none</UpdateAccountGUIDs> 

8) Edit obiee server NQSconfig.INI file with the following
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = NO; 

9) Start OBIEE services (opmnctl startall) 

10) Verify OBIEE services are up (opmnctl status)

Reference:

Cheers!
Bose

Wednesday 15 January 2014

ADF_FACES-60097 : For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1


Occurs:
When I was trying to upload the latest repository (.rpd) in em, I couldn't save the changes and stopped by this error. So here is the steps what I followed to overcome this issue. Read below to know about the issue and resolve.
 
Applies to:
Business Intelligence Server Enterprise Edition - Version 11.1.1.5.0  and later
Information in this document applies to any platform.

Symptoms:
When selecting the 'Coreapplication' within the Oracle Business Intelligence (OBIEE) folder (under the Server farm) in Enterprise Manager the following error is generated:
ADF_FACES-60097 : For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1



Cause:

Bug:10372673 - QA: HOT: UNABLE TO ACCESS EM > COREAPPLICATION - INCIDENT ERROR REPORTED


The diagnostics.dmp shows the following:
[ServletContext@122564910[app:em module:/em path:/em spec-version:2.5]] Root cause of ServletException.
java.io.IOException: Stream closed
at java.io.BufferedInputStream.getInIfOpen(BufferedInputStream.java:134)
atjava.io.BufferedInputStream.fill(BufferedInputStream.java:218)
etc....
 
Solution:
1. stop all services, including WebLogic
2.Delete the instance.jspx.xml file in folder 'FMWhome\user_projects\domains\bifoundation_domain\sysman\mds\partition1\ai\bi\mdssys\cust\user\USER_NAME'
3.  Restart the services,  The instance.jspx.xml file will be recreated.
Note:
The USER_NAME refers to the userid in your specific system. For example, weblogic or biadmin.
This solution is also documented in the following document:
<Doc ID 1303644.1> OBIEE Enterprise Manager Error: Stream Closed Server Exception during PPR, #13
Please ensure that a backup of the environment is taken beforehand.

References
NOTE:1303644.1 - OBIEE 11g: Error: "Stream Closed" Server Exception in Enterprise Manager during PPR, #13

Tip:
If you have used the username like 'Weblogic', 'WEBLOGIC', the corresponding username folder will be created under that folder. so we could simply login into em and we could restart the services!

Cheers!
Bose

Thursday 9 January 2014

ODI 11g Step by Step Installation

This post explains installation steps of Oracle Data Integrator (ODI) 11g. Download the ODI software from the oracle software: ODI Download Oracle Page

Prior to the installation of ODI, It is necessary to Install Java JDK1.6 or above.

Unzip the downloaded software and double click one setup.exe. In Universal Installer screen, It may prompted for Java location to continue the installation. So copy Java path and right click on this window bar -> mark ->paste to paste the path
Step1:
Just click 'Next' with the below step
Step2:
If you have valid oracle support id then select second option with this step and enter the details so that the latest updates would be updated automatically. otherwise select 'Skip Software Updates' and click 'Next'
Step3:
If you are installed 'OBIEE 11g' in the machine then select all the components with this step so one of step you will be prompted to select middleware home. but here I have just installing ODI without Middleware path
Step4:
Once the prerequisites passed with this step click 'Next'
Step5:
In this step, we could see the default path where ODI will be installed. If we want to change, we could do where we could install ODI
Step6:
We have already created Repository using OBIEE 11g RCU. so select 'Skip Repository Configuration' and then click 'Next'
Step7:
In this step select database type, connection string, db user name and password and then click 'Next'
Step8:
In this step enter SUPERVISOR passoword 'Admin123' and then click 'Next'
Step9:
In this step, work repository which we have created using RCU would be selected. If not select in this step and click 'Next'
Step10:
Give the Agent Name that you want to create with this installation and then click 'Next'
Agent Name: ODIAgent
Agent Port: 20910
Step11:
In this step uncheck receiving my oracle support and click 'Next'
Just click 'Yes' with this popup window
Step12:
This step save ODI path details if you want or just click Install
Installation Progress
After configuration progess reached 100% click 'Next'
Just click 'Finish' the complete ODI Installation
Now go to All Programs -> Oracle -> Oracle Data Integrator -> ODI Studio and click 'Connect Respository' and enter details like below screen
We have connected with ODI Repository in ODI Studio

Cheers!
Bose

ODI Master and Work Repository Creation Using RCU (OBIEE 11g)

Oracle Data Integrator (ODI) Installation steps are now more easy with Repository Creation Utility (RCU). Double click RCU.bat from the downloaded and unzipped RCU Folder.

Step1:
Oracle Universal Installer checks prerequisites and system requirements and will load the first step of the installation. Just click 'Next' with the Below Screen
Step2:
Select 'Create' and click 'Next' with this screen.
Step3:
In this step, type all the necessary details in the required fields and click 'Next' with this step

Database Type: Oracle Database
Host Name: Bose-PC
Port: 1521
Service Name: ORCL
UserName: sys
Password: Password1
Role: SYSDBA
This step will check the prerequisites. Just click 'OK' once the prerequisites passes
Step4:
In this step check 'Oracle Data Integrator' Component. Type if you want to change the prefix and repository name.
Once we clicked 'Next' with the above screen It will check prerequisites and click 'OK' with the below screen
Step5:
Give the password which you want to give for ODI Work and Master repositories. If you want to give different password, select the appropriate option and click 'Next'
Step6:
This step is an important step so Please keep in your mind or store somewhere all the credentials and details in a file which you are entering here and then click 'Next'

Master Repository ID(001): 001
Supervisor Password: Admin123
Confirm Supervisor Password: Admin123
Work Repository Type: D
Work Repository ID: 101
Work Repository Name: WORKREP01
Work Repository Password: Admin123
Confirm Work Repository Password: Admin123

Step7:
This step will gives the information of ODI Schema name, schema owner and temporary tablespace name. Just Click 'Next' with this step
In this confirmation pop-up, Just click 'OK'
This will create the tablespace and click 'OK' once the steps passes
Step8:
In this step, click 'Create'
Step9:
so ODI Schema creation is completed with the above step. Just click 'Close' to complete the repository creation.
In the next coming post, we will continue to the ODI Installation using this ODI repositories.

Cheers!
Bose

Tuesday 7 January 2014

Usage Tracking in OBIEE 11.1.1.7.0 (11g)

Why Usage Tracking:
Usage Tracking is used to monitor system and ad-hoc query performance and to provide input on usage trends for use in billing users and departments for resources. Usage tracking is particularly useful in determining user queries that are creating performance bottlenecks, based on query frequency and response time. When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query and writes statistics to a usage tracking log file or inserts them directly to a database table. For the purposes of this tutorial, you will use a database table, which is the recommended leading practice.

Here We will separate Usage Tracking Process to 4 Steps

I. Creating Uasge Tracking Table
II. Creating and Customizing rpd file
III. changing the Configuration in EM
IV. Testing the Usage Tacking in Answers

StepI: Creating Usage Tracking Table
If we latest OBIEE 11g installed in our machine, open sqlplus and connect DEV_BIPLATFORM schema.
write the below query:
select table_name from tabs where table_name like='S%';
The table in the name S_NQ_ACCT is related to usage tracking. so we donot have any manual work in the usage tracking table creation. So before setting up the usage tracking we couldn't have any rows in the table

StepII: Setting up the Repository File
1. Create 'New Database' by right clicking on the empty space in physical layer of the rpd  and name it as 'UsageTracking'
2. Right click on the physical layer object 'UsageTracking' ->New Object -> select 'Connection Pool and enter the detail like below screen 
Connection Pool Name: UsageTracking ConnectionPool
Data Source Name: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Bose-PC)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
OR
Data Source Name: //Bose-PC:1521/ORCL 
User Name: DEV_BIPLATFORM
Password: Admin123
Click 'OK' after you enter the password and you will be prompted to confirm the password. Type the same password.  
3. Import the 'S_NQ_ACCT' table from DEV_BIPLATFORM Schema to the 'UsageTracking'  database repository.
4. Double Click on the DEV_BIPLATFORM physical folder and change it 'UsageTracking Schema' So that It would like below screen
5. Now Come to BMM Layer and Create UsageTracking BMM Layer folder and Create for new logical tables in that BMM Layer like shown in the below picture

6. Drag the following three physical columns from Usage Tracking Schema>S_NQ_ACCT to the Measures logical table in the UsageTracking business model. For each column, right-click the logical column and select Rename, and then apply aggregation rule like shown in the table:

Physical Column
Rename
Aggregation Rule
QUERY_TEXT
Query Count
Count
ROW_COUNT
Row Count
Sum
TOTAL_TIME_SEC
Total Time Seconds
Sum
  
7. Drag the following three physical columns from UsageTracking Schema>S_NQ_ACCT to the Time logical table in the UsageTracking business model. Rename them as follows:

Physical Column
Rename
Key
START_DT
Start Date
 Yes
START_HOUR_MIN
Start Hour Minute

END_HOUR_MIN
End Hour Minute


8. Here is the example screen screen for how to create Key for Time Logical table
Under Keys Tab -> type 'Time_key' in the key name and select 'Start Date' in the columns.
9. Drag the following two physical columns from UsageTracking Schema>S_NQ_ACCT to the Topic logical table in the UsageTracking business model. Rename them as follows:
Physical Column Rename
QUERY_TEXT Logical SQL
SUBJECT_ AREA_NAME Subject Area
 Do not forget to to apply 'Logical SQL' as a key column for this logical table.

10. Drag the USER_NAME physical column from UsageTracking Schema>S_NQ_ACCT to the User logical table in the UsageTracking business mode. Rename it to User Name and apply this column as a key to this table.


Below is the complete BMM Layer Model Screen for UsageTracking:

11. Right click on the UsageTracking BMM Layer folder -> select 'Business Model Diagram' -> Whole Diagram and the create joins between the tables as shown in the below screen:
12. Just drag the UsageTracking folder to Presentation Layer of the repository.
Now Save the repository and check the consistency check. RPD works are done!!!

StepIII: Setting up the EM
 1. Open EM window using the link  http://localhost:7001/em by entering weblogic username and password
Expand WebLogicDomain  and the click 'bifoundation_domain' in the left pane. Now select 'System MBean Browser' in the WebLogic Domain in the right side pane like show in the picture
2. expand 'Application Defined MBeans' and the expand 'oracle.biee.admin' in that
3. Expand 'BIDomain' in oracle.biee.admin and click on second 'BIDomain' as show in the picture. Now select 'operation' tab from right side pane and then click 'lock'
Click 'Invoke'. Once we get the screen like below, click 'Return'
4. Now expand BIDomain.BIServer.ServerConfiguration under oracle.biee.admin from the left pane. Now select all the following option from the right side pane and apply the change using the following steps
                         UsageTrackingCentrallyManaged
                         UsageTrackingConnectionPool
                         UsageTrackingDirectInsert
                         UsageTrackingEnabled
                         UsageTrackingPhysicalTableName
Select UsageTrackingCentrallyManaged and then select value 'true' and apply. After successful apply, click Return to previous screen
Select UsageTrackingConnectionPool and type the value "UsageTracking".UsageTracking ConnectionPool" and then apply. After successful apply, click Return to previous screen

Select UsageTrackingPhysicalTableName and type the value "UsageTracking"."UsageTracking Schema"."S_NQ_ACCT" and then apply. After successful apply, click Return to previous screen
Select UsageTrackingDirectInsert and select the value 'true' and then apply. After successful apply, click Return to previous screen
Select UsageTrackingEnabled and select the value 'true' and then click apply. After successful apply, click Return to previous screen
So with the above screen all the configuration related to Usage Tracking is applied. Now  Come to BIDomain and select 'BIDomain' under orace.biee.admin to commit the changes. Select Operation tab and select 'simplecommit'
In simplecommit screen click 'Invoke'. After successful execution of simplecommit click retun to previous screen.
Now upload the updated repository in EM and restart the BI Services.

StepIV: Checking results with Answers using UsageTracking Subject Area
Now Browse some analysis from the existing dashboard or create some reports and create one report using "UsageTracking" subject for the details
select User Name from User, Logical SQL and Subject Area from Topic, Start Hour Minute from Time and Row Count from Measures table in the criteria section of creating new analysis. Now check with the results
Below Screen Shows the results for username, logical sql, query count, row count, total time seconds from the Usage Tracking subject Area.
Now If you are checking the table 'S_NQ_ACCT' in tabale, we could see the updated records.

Cheers!
Bose

What is Oracle Database Link and How to create database link (DB Link)

 https://www.oracletutorial.com/oracle-administration/oracle-create-database-link/

Recent Posts