ODI - How to track load plan step package scenario behind code from session id

Follow the Steps:

Go to operator tab => Load plan executions and select the plan 

Expand the steps and select the SCEN and click on session id.

 

Click on package steps

Expand and select the operation and open session tasks.

Go to code

Now you can see the execuated code.

OCI - DB check Active sessions in Performance Hub

Suppose we have ADW database configured in OCI and We are running ODI load plans which contains multiple Package scenarios. Each scenario is inserting or marging data to configured ADW database. Sometimes the scenario is taking longer time like more than 2 or 3 hrs but not completing the process and got stuck. How we know that the particular process got stuck.  Using performance hub, we can easily track the sessions when it is active or idle. Based on the perfomace metrics, we can take further decision to kill the stuck process and restart the failed scenario again.

Steps to check performance hub:

Login to OCI console and navigate to Databases.

Click on Autonomous Data Warehouse

Choose compartment and select adw db

Click on performance tab or you can also go from Databse actions.

Performance page will open. It will show all the sessions time while idle or active. Got to SQL monitoring section and check all the running sqls.

ODI - DB Query to check sessions status if active

Query:

Select sid,serial#,program,module,status,username,schemaname from gv$session where status ='ACTIVE'

This query is very useful to see all the active processes even we can see the ODI sessions here.

OIC - Merge two different schema files into a single CSV file

What :  We will read 2 csv files having different schemas and marge them into a single final csv file.

Input: 

Sample_1.csv

Sample_2.csv

Output:

Final.csv

Implementation Steps:

• Create a FTP connection and create an Orchestrated integration.
• Read 1st file using ftp connection and 1st CSV file schema.

• Write the file in the Stage location with the same 1st CSV schema and map the read file.

• Read the 2nd file using FTP connection and 2nd CSV file schema.

• Write file into a stage location with the 2nd CSV schema. Keep the same file name and location. And also, keep Append to existing file checked in and map the read 2nd file.

• Write the both marged file into a location using FTP connection.

OIC - iCal Expression to run each month on specific day and specific time

 FREQ=MONTHLY;BYMONTHDAY=1,10,15;BYHOUR=5,10 15,20;BYMINUTE=15;

The following exp indicates that the integration will run each month on the 1st, 10th, and 15th day of the month at 5:15AM, 10:15AM, 3:15PM, and 8:15PM

FREQ:DAILY;BYHOUR=0,1,2,3 4,5,6,7,8,9,10,12,13,14,15,16,17,18,19,20 21,22;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55;

The following exp will run daily each 5 mins interval except 11th hour of the day.

OCI - Oracle Cloud Infrastructure foundations 2021 associate 1Z0-1085-21 exam questions and answers

Q7: correct answers C and D

Q8: correct answers B

Q11: correct answers: CDE

Q14: correct answers: ADE

Q22: Correct answers: BE

Q29: correct ans: EF

Q32: correct ans: BD

Q38: correct ans: B

Q50: correct ans: AE

ODI - database - datafile - check used and allowed max file size

Run the following commands to get the tablesapce used size, allocated max size:

For all Tablespace:

Select FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024/1024 size_gb, maxbytes/1024/1024/1024 max_gb, STATUS, AUTOEXTENSIBLE, ONLINE_STATUS from dba_data_files

For speecific tablespace:

Select FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024/1024 size_gb, maxbytes/1024/1024/1024 max_gb, STATUS, AUTOEXTENSIBLE, ONLINE_STATUS from dba_data_files where  tablespace_name in ('undo_8')