config.sh step (step 10 in the supplement) was stuck while starting bi_server1 with DB archiver error (connect as sysdba only until freed)

Please find the below link to follow

https://tamimdba.wordpress.com/2011/05/25/error-ora-00257-archiver-error-connect-internal-only-until-freed/

OAC: Downloading Snapshots Throwing Error: “A connection to the server has failed.(status=504)”

On Oracle Analytics Cloud (OAC) 18.2.1 version,

Unable to download the snapshot and failed with the following error - "A connection to the server has failed" as shown below

As a workaround, metadata command line utilities can be used to export the bar from source and import the bar on the target VM.

1. Take a snapshot of the system as it exists now.
2. Restore the snapshot that needs to be migrated.

Detailed Steps:

1. SSH into the source OAC VM and run a command line utility to generate/export a BAR file.
2. Use the below command to generate/export the snapshot bar file.

cd /bi/app/public/bin

Example:

./export_archive bootstrap /u01/app/oracle/tools/home/oracle/exportdir/ --loglevel DEBUG --logdir /u01/app/oracle/tools/home

This will prompt for a password to use in order to encrypt the BAR file (The same as that of the bar file generation that occurs on Analytics Console UI page)

      3. Copy this BAR file into the target OAC VM.
      4. Restore the BAR on the target.

cd /bi/app/public/bin
Example:
./import_archive bootstrap /u01/app/oracle/tools/home/oracle/exportdir/1536578078486/bootstrap.bar --loglevel DEBUG --logdir /u01/app/oracle/tools/home/oracle/logdir/

     5. On the source, go back to SAC UI and restore to the backup snapshot created by step 1.

Timeout Issues : ODI

01. How to set up the Timeout for the graphical environment (ODI Studio)?

ODI12c

The Oracle Data Integrator Timeout parameter (in seconds) is set in the ODI 12c Studio tool menu bar from ODI > Tools > Preferences > ODI > System, as shown below:

Modifications of these "Parameters" only impact the local ODI Studio on which the modifications have been performed.

ODI 11g

The Oracle Data Integrator Timeout parameter (in seconds) is set in the ODI 11g Studio tool menu bar from ODI > User Parameters > Property "Oracle Data Integrator Timeout".

The default value is 30 seconds.

02. How to set up the Timeout for the Agents (standalone Agent, colocated Agent, and J2EE Agent)?

The Oracle Data Integrator Timeout of any ODI 12c Agent might be configured from ODI Studio > Topology, by setting the desired number of seconds into the "JDBC connection timeout" property from the physical Agent > "Properties" tab, as shown below:

03. Additional settings for the J2EE Agent

In the WLS Admin Console, go to the deployment of the ODI J2EE Agent, and modify the Session Timeout field to the desired number of seconds.

Additionally, the connection timeout, and the number of retries to establish the connection can also be increased by configuring the JDBC Data Source parameters in WLS Admin Console.

Go to Services > Data Sources entry. Edit the desired Data Source (odiMasterRepository, odiWorkRepository, etc). Go to the Configuration > Connection Pool tab. Click on the "Advanced" link at the bottom of page, and set the Connection Creation Retry Frequency, Login Delay, and Inactive Connection Timeout fields to the desired values.

Also notice the oracle.net.CONNECT_TIMEOUT property (milli-seconds) in the Connection Pool tab > "Properties" text box.

Java heap space error while importing the custom mappings into test environment: : ODI 11g/12c

We are trying to import (smart import) our custom mappings into the test environment and we are getting the java heap space error.

Syntax for setting the Java properties

• ODI configuration files: product.conf, odi.conf and ide.conf

Increase it to below values in the mentioned files:

• ODI command files: setODIDomainEnv.cmd, and setODIDomainEnv.sh

Migrating BIAPPS Configration : BIACM (Data Load Parameters, Domains and Mappings, Reporting Parameters)

The migration of functional setup data (Data Load Parameters, Domains and Mappings,
Reporting Parameters) is performed by an export of the setup data from Configuration Manager
in the source environment and then an import of the data into Configuration Manger in the
target environment.

To migrate functional setup data:

1 Log into Configuration Manager in the source environment (for example, development)
as a user with BI Applications Administrator privileges. Navigate to Setup Data Export
and Import: Export Setup Data using the left hand Tasks pane.

2  From the Table tool bar on the Export Setup Data page, click on the Export icon to
display the New Data Entry Dialog: Export. In the Export dialog:
           a. Provide a meaningful name for the export file name
           b. Select the following objects to export:
                        · Data Load Parameters
                        · Domains and Mappings
                        · Reporting Parameters

NOTE: Do not select System Setups. System setups have already been completed as part of Step 1 Creating a Target Test or Production Environment .

3. Click on the Export button. In the File Dow

nload dialog, click Save to save the ZIP file to location that you specify.

4. Copy the ZIP file exported in step 3 above to a file location that is accessible from the

machine that will run the target Configuration Manager browser window.

5. Log into Configuration Manager in the target environment (for example, test) as a user

with BI Applications Administrator privileges. Navigate to Setup Data Export and Import:

Import Setup Data using the left hand Tasks pane.

6. From the Table tool bar on the Import Setup Data page, click on the Import data icon to

display the New Data Entry Dialog: Import Data.

7. In the Import Data dialog, browse to locate the ZIP file copied to the location in step 4

above.

8. Click OK to import the functional setup data into the target Configuration Manager. The
Import table is updated with details of the import.

ODI Load Plan got stuck at Index Creation Step: Oracle BIAPPS 11g

The reason why it is getting stuck is because of the PARALLEL_LEVEL parameter value passed greater than 1 in (DBMS_PARALLEL_EXECUTE.RUN_TASK ) DBMS package.

Sample parameters passed as below :

DBMS_PARALLEL_EXECUTE.RUN_TASK('CREATE_INDEXES_17813500', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 2) ;

ODI Load Plan Screenshot:

There are two possible reason when your load plan will get stuck up.

01. If the below DB parameter is 0 and parallel_level => {more than 1}, then your load plan will get stuck.

SQL> show parameter job_queue_processes 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 

job_queue_processes integer 0 

02. If your dbms_scheduler was disabled, kindly enable the same.


Execution of PL/SQL package (DBMS_PARALLEL_EXECUTE.RUN_TASK) with below parameters:

Schema on Write(Traditional Databases) vs Schema on Read (Hadoop)

The fundamental difference while comparing Traditional Databases viz Oracle,SQL Server,DB2 with Hadoop is  Schema on Write vs Schema on Read.

Schema on Write

The steps as below :

Step1 : The first step here is create schema i.e. define Table Structure. For Example:

                                                 CREATE TABLE EMP

                                                  (

                                                      Ename STRING,

                                                     EmpID INT,

                                                    Salary FLOAT,...)

Step2 : Once the table exists then only we can load data to it. For Example: Bulk load data into EMP table from emp.txt file

                                          BULK INSERT EMP

                                          FROM 'C:\EMPDATA\emp.txt'

                                         WHERE FILELDTERMINATOR= ","

Step3 : Once the data is loaded we can query the data using SELECT statement. For Example :

                             SELECT Ename,Salary,... FROM EMP;

The above three steps demonstrate the schema on write which our traditional databases possesses. It is important to note here that we can't add data to the table unless the schema has been declared.

If the data changes for a given column say data-type of that very column changes from INT to VARCHAR2 or a new column has been added to the table, then whole data need to be deleted for the column and need to be re-loaded. This holds good when we have small set of data or we do not have the foreign keys. But when we have terabytes of data and foreign key existing in the table then it will really be a challenging problem. 

Hadoop or any other big data technologies generally use Schema on Read. Schema on Read follow the different sequence.

Schema on Read

Step1 : Load the data on hadoop cluster.

                          hdfs dfs -CopyFromLocal /tmp/EMP.txt /usr/hadoop/emp

Step2: Query the data using pyton script or hive command or by any other means. For Example:

                        hive> SELECT * from EMP; OR

                        hadoop jar Hadoop-Emp.jar -mapper emp-map.py -reducer emp-red.py -input /usr/hadoop/emp/*.txt -output /usr/hadoop/output/query1A

Here, the data structure is interpreted as it is read through python script or hive command as shown above. If the column is added to the table or datatype of a column got changed we can adjust the script to read the data. We do not need to reload the whole data.

Let us understand the above theory with the help of below example :

Consider we have a USER table where in two columns are there, namely NAME and AGE with the sample data shown.

When we try to write the sample data shown in USER table in traditional database, it will throw error because NAME is a varchar column and we are trying to insert integer data to it. Similarly, AGE is an integer column and we are trying to insert XYZ (VARCHAR) data to it. Hence, the schema is verified while writing the data.Therefore, traditional database has total control over the storage. This gives ability to database to enforce schema as data is written. This is called as Schema on Write. 

When comes to Hadoop or any big data technologies, it does not have any control over storage.When we try to load the above data into a hive or HDFS table, the loading will be successful. While reading the data, HIVE will verify the schema. As 123 is integer and 'XYZ' is varchar, NULL value will be displayed for the NAME and AGE fields  for the values as 123 and 'XYZ' respectively. The data will be verified while querying the data and hence Schema on Read. Therefore, when data is loaded schema is not verified in Hadoop or big data technologies while as schema check happens while reading the data.

ORA-01843: not a valid month;01843. 00000 - "not a valid month"

If any of you is encountering an error like "ORA-01843: not a valid month" while running an oracle SQL via any tool such as SQL Developer,TOAD,Oracle Data Integrator, Informatica then the only possibility which is there is that some columns in a table is of VARCHAR datatype which has data values as the date values whose format is different from what you have defined in "NLS_SESSION_PARAMETERS" of the database.

Demonstration:

01. SELECT * FROM NLS_SESSION_PARAMETERS; [Login via SYS to check the values ]

 02. Create a table say "TEST" with one VARCHAR column say the column name "DT"

CREATE TABLE TEST (DT VARCHAR(20));

03.  INSERT a data in the column different from your NLS_DATE_FORMAT.In our case NLS_DATE_FORMAT is 'DD-MON-RR'

INSERT INTO TEST VALUES('31/12/2014');

Clearly we have inserted the data in format other than NLS_DATE_FORMAT.

04. Try Query the "DT" column using the TO_DATE function without specifying any format specifier.You will run into the issue "ORA-01843: not a valid month"

SELECT TO_DATE(DT) FROM TEST;

05. Therefore, You need to specify either the format specifier while querying the VARCHAR column using TO_DATE function or insert the data in a table for the same VARCHAR column in the format what is defined as in NLS_DATE_FORMAT of the database.

SELECT TO_DATE(DT,'DD/MM/YYYY') FROM TEST;

Note : - If in the same "DT" column if you have one data say in "DD/MM/YYYY" format and other data in "MM/DD/YYYY" format then the data values are not consistent and the SQL will fail.Therefore, it is mandatory to insert the data in the same column in consistent format which can be either in "DD/MM/YYYY" or "MM/DD/YYYY" across the data set for the column. 

Understanding of 'MANUAL'/'DIFFERENCE' records in sub ledger fact.[OBIA]

Overview of  'DIFFERENCE' and 'MANUAL' records:

'DIFFERENCE' and 'MANUAL' records in the base fact i.e sub ledger fact gets created based on the comparison done between GL and the corresponding sub ledger.This entire process is called as GL Reconciliation process.

It is important to remember that only GL related dimensions will get populated for these kinds of records in the corresponding base fact.For Example :-If you are looking for party name for these types of records then in warehouse it will be populated as ‘Unspecified’.

'DIFFERENCE' records:

If the journal amount in General Ledger does not match the amount of all the corresponding accounting entries for the given journal lines in base fact, it inserts one row for the difference amount into the corresponding subledger fact.These records are tagged as DIFFERENCE. To find the details in your DW please do the join as per “W_AR_XACT_F.DOC_TYPE_WID = W_XACT_TYPE_D.ROW_WID AND W_XACT_TYPE_D.W_XACT_TYPE_CODE  = 'DIFFERENCE'”

'DIFFERENCE' records can be genuine or ungenuine.It will be ungenuine primarily in below two cases :-
01. When a user mistakenly maps a GL natural account number to an incorrect Group Account Number, incorrect accounting entries might be inserted into the sub ledger fact table.For Example : natural account 1210 is classified as belonging to 'AR' Group Account Number in "file_group_acct_codes_ora.csv" when it should be classified as having 'AP' Group Account Number. 
Remedy : you need to correct the Group Account Number in "file_group_acct_codes_ora.csv" for all those given accounts.
02. Records are not posted in GL in EBS sides.
Remedy : Identify all those source distribution ids and post it to GL[EBS].

'MANUAL' records:

'MANUAL' records are created when someone tries to create the journals in the GL side manually which has no subledger information.For example : for a GL Account Id 140 and Amount $200 is manually created in GL EBS side.

Generic problem encountered as part of DIFFERENCE records in OBIA:

Let us say a customer tries to match the "Accounts Receivable line from "GL Balance Sheet" and "AR Balance - from DSO report" and says that balances are not matching.
For example:-
AR Balance - from DSO report   -- Jan-12 (1,025,804) [Also remember DSO Report is at customer level]
Accounts Receivable from GL Balance Sheet. -- Jan-12 (53,001,270)
Clearly, there is huge difference between both the balances states there is some problem existing.
It is important to note that when you will SUM(AR_DOC_AMT) at customer level and you have DIFF/MAN records in your AR base fact. This will cause the discrepancy in balances.
This is because for DIFF/MAN records customer name will be populated as ‘Unspecified’ and hence W_AR_XACT_F will have the CUSTOMER_WID as 0.
Therefore, aggregation at customer level or Customer Accounts level will never going to give you the matching balances between GL and AR.
While as when you do the aggregation at LEDGER/GL Account level balances will match.

DIFFERENCE record example in Database/DW side:

Let us consider the INTEGRATION_ID of a DIFFERENCE record is '751124~2545'

SELECT SUM(AR_DOC_AMT) FROM W_AR_XACT_F WHERE ACCOUNT_DOC_ID IN (SELECT SOURCE_DISTRIBUTION_ID FROM W_GL_LINKAGE_INFORMATION_G WHERE JOURNAL_LINE_INTEGRATION_ID = '751124~2545');
-- (-28704)

SELECT INTEGRATION_ID,OTHER_DOC_AMT FROM W_GL_OTHER_F WHERE INTEGRATION_ID = '751124~2545';
-- 28704

SELECT AR_DOC_AMT FROM W_AR_XACT_F WHERE INTEGRATION_ID = '751124~2545';
-- 57408

Clearly, a DIFFERENCE record of (-28704) is created in W_AR_XACT_F.

Working with Users and Groups in the Embedded LDAP Server..!

When you install OBIEE, the installer asks you to enter the username and password for an administrative user, which we use it to log in to Fusion Middleware Control.

To add new users to your system and assign them to groups (ex LDAP groups, AD groups) , you use the web based Oracle WebLogic  Administration Console, which contains features for managing the embedded LDAP server.

Example of Creating New Users and adding them to Groups  

01.   Log in to Oracle WebLogic  Administration Console(http://:7001/console) from the user having administrative privileges (example weblogic/welcome1)
02.  When the home page appears, click on Security Realms

03.    When Summary of Security Realms page appears, click on “myrealm”. ”myrealm” is the default container for security settings.
04.    Once you click on “myrealm” it will take you to the next page. Click on “Users and Groups” tab to start creating new users.

05.    Click on New button and enter the details for new users.”DefaultAuthenticator” refers to your embedded LDAP server ,which is default provider for authentication for newly configured system.Click “OK” to create the user.

 

06.    To add the user to one of the LDAP group in your LDAP directory , therefore to grant the user to a application role , click the user (“mayank”), it will take you to the another  page , Go to groups.

07.    Select the LDAP group/s and from the left pane and click “save” to complete the process.

Working with Application Roles and Policies

The user is created and added him/her in BIAuthors LDAP group, this group must be linked to BIAuthor application role .This role is granted to “BIAuthors” LDAP group in OPSS policy store as part of default security configuration.
To check how Application Roles and Policies are administrated in using Fusion Middleware Control
01.    Login to Enterprise Manager using url (http://:7001/em) using administrative user.
02.    When the home page is displayed, Go to Business Intelligence --> coreapplication. Right click “coreapplication”. Security --> Application Policies/Role will get displayed.

03.    Click on Application Roles. Locate on BIAuthor Application Role  and click on it.

04.    You can see in the bottom pane, two other objects have been granted this role.One is the BIAuthors LDAP group and other is the BIAdministrator  application role.This means this very object “BIAuthor” inherits the permission and privileges of BIAdministrator  application role.

Creating and Managing Application Roles

01.    Create an application role using the Fusion Middleware Control(em)
02.    Create a matching LDAP group using the Oracle Weblogic Admin Console or identify in FMW which existing LDAP group you want to map it to the application role.
03.    In FMW(em), grant the role to LDAP group.
04.    Using Admin Console, add user to relevant LDAP groups.
05.    Launch the Oracle BI Administrator tool and refresh its view of the current application roles in your Policy Store.
Example:
01.    Create an application role as shown above by logging into (http://:7001/em)
Name: FINANCE Manager
Description: Financial Analytics Manager

02.    Create the corresponding LDAP group and assign it to the required user to the group. For doing this log in to Oracle WebLogic  Administration Console(http://:7001/console)  from the user having administrative privileges (example weblogic/welcome1)
Name: FINANCE Managers
03.    Finally add the required user to this LDAP group.
04.    Log in back to the Fusion Middleware Control (em) and launch the application role page again. Click on the application role “FINANCE Manager”. Click on the Edit button.

05. To grant this new application role to the corresponding LDAP group, in the Member section Click add button and then select the LDAP group from the searched principal group.

Creating and Managing Application Policies

Application Policies like application role are created and held in a policy store and administrated and accessed by OPSS. You can access the existing by logging into Enterprise Manager using url (http://:7001/em) using administrative user. When the home page is displayed, Go to Business Intelligence --> coreapplication. Right click “coreapplication”. Security --> Application Policies/Role will get displayed.

Application Policies are basically set of JAVA permissions associated with a principle. For Example: The BI Author application policy allow you to develop reports and other perform other report authoring task.

Few application policies which are granted to the application role BIAuthor as below :-
oracle.bi.publisher.developReport
oracle.bi.publisher.developDataModel
EPM_Essbase_Administrator
EPM_Essbase_Calculate
EPM_Calc_Manager_Designer
oracle.epm.financialreporting.editBatch   
oracle.epm.financialreporting.editBook
oracle.epm.financialreporting.editReport
oracle.epm.financialreporting.scheduleBatch
oracle.epm.essbasestudio.cpadmi

The above are set of JAVA permissions associated with a principle. Oracle BI ship all possible combination of policies under the application role BIAdministrator, BISystem, BIConsumer, BIAuthor.
Therefore if you created an application role which is linked to a LDAP group and finally to an user, you just need to assign the any of the four application role to inherit their policies.

If you freshly want to create an application policy which is not among the available policies under the given four application role, you need to write the JAVA program for the same and then deploy it into the weblogic.
Always remember BIConsumer is by default assigned to user once you create it.

WORKING WITH EVENT TASKS[Informatica Workflow Task]

WORKING WITH EVENT TASKS

We can define events in the workflow to specify the sequence of task execution.

Use the following tasks to help you use events in the workflow:

v    Event-Wait task. The Event-Wait task waits for an event to occur. Once the event triggers, the Integration Service continues executing the rest of the workflow.

Types of Events:

·        Pre-defined event: A pre-defined event is a file-watch event. This event waits for a specified file to arrive at a given location or directory.

Let us try to understand it with an example. You wanted to wait for a file to arrive in a directory while keeping the workflow on running mode. Once the file arrives in the directory the mapping session should automatically be kicked off to load the data into target.

·        User-defined event: A user-defined event is a sequence of tasks in the Workflow. We create events and then raise them as per need. In this case we first raise a user defined event and then configure the event wait which will wait for user defined event to occur and triggers the next session in the workflow

    EVENT RAISE: Event-Raise task represents a user-defined event. We use this task to raise a user defined event.

    EVENT WAIT: Event-Wait task waits for a file watcher event or user defined event to occur before executing the next session in the workflow.

Scenario:-

You have two sessions. The first session first remove the duplicates from the file (s_remove_dups) and once this gets completed then you need to run another session which calculates minimum and maximum salary (s_max_min_sal)

How to configure User-defined event

·         Workflow -> Create -> Give name wf_event_wait_event_raise -> Click ok.

·         Workflow -> Edit -> Events Tab and add events EVENT1 there.

·         Drag “s_remove_dups”

·         Click Tasks -> Create -> Select EVENT RAISE from list. Give name.

·         Right click event_raise_example -> EDIT -> Properties Tab -> Open Value for User Defined Event and Select EVENT1 from the list displayed. Apply -> OK.

·         Click link between event_raise_example and s_remove_dups and give the condition $s_remove_dups.Status=SUCCEEDED

·         Click Tasks -> Create -> Select EVENT WAIT from list. Give name event_wait_example. Click Create and then done.

·         Link event_wait_example to START task.

·         Right click event_wait_example -> EDIT-> EVENTS tab.

·         Select User Defined there. Select the Event1 by clicking Browse Events button.

·         Apply -> OK.

·         Drag s_max_min_sal and link it to event_wait_example.

·         Mapping -> Validate

·         Repository -> Save.

·         Run workflow and see.

Screenshot of Details