That's a wrap - Certifications 2022

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people taking these certifications.

Well, not all exams. I did only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis. 🙂

I also did some other Cloud Vendors Certifications and of course my preferred certifications (Oracle Database 🙂)

Here, you can find a list of links to help you achieve these certifications.

OCI:

How to study for 1z0-1105-22 - Oracle Cloud Data Management 2022 Foundations Associate

http://alexzaballa.blogspot.com/2022/05/how-to-study-for-1z0-1105-22-oracle.html

How to study for 1z0-1085-22 - Oracle Cloud Infrastructure 2022 Certified Foundations Associate

http://alexzaballa.blogspot.com/2022/06/how-to-study-for-1z0-1085-22-oracle.html

How to study for 1z0-1067-22 - Oracle Cloud Infrastructure 2022 Cloud Operations Professional

http://alexzaballa.blogspot.com/2022/07/how-to-study-for-1z0-1067-22-oracle.html

How to study for 1Z0-1104-22 - Oracle Cloud Infrastructure 2022 Security Professional

http://alexzaballa.blogspot.com/2022/07/how-to-study-for-1z0-1104-22-oracle.html

How to study for 1Z0-1084-22 - Oracle Cloud Infrastructure 2022 Certified Developer Professional

http://alexzaballa.blogspot.com/2022/07/how-to-study-for-1z0-1084-22-oracle.html

How to study for 1Z0-1072-22 - Oracle Cloud Infrastructure 2022 Architect Associate

http://alexzaballa.blogspot.com/2022/07/how-to-study-for-1z0-1072-22-oracle.html

How to study for 1z0-1094-22 - Oracle Cloud Database Migration and Integration 2022 Professional

http://alexzaballa.blogspot.com/2022/08/how-to-study-for-1z0-1094-22-oracle.html

How to study for 1z0-1093-22 - Oracle Cloud Database Services 2022 Professional

http://alexzaballa.blogspot.com/2022/09/how-to-study-for-1z0-1093-22-oracle.html

How to study for 1z0-931-22 - Oracle Autonomous Database Cloud 2022 Professional

http://alexzaballa.blogspot.com/2022/09/how-to-study-for-1z0-931-22-oracle.html

How to study for 1Z0-997-22 - Oracle Cloud Infrastructure 2022 Architect Professional

http://alexzaballa.blogspot.com/2022/09/how-to-study-for-1z0-997-22-oracle.html

How to study for 1Z0-1109-22 - Oracle Cloud Infrastructure 2022 DevOps Certified Professional

http://alexzaballa.blogspot.com/2023/01/how-to-study-for-1z0-1109-22-oracle.html

Oracle Database:

How to study for 1z0-116 - Oracle Database Security Administration

http://alexzaballa.blogspot.com/2022/05/how-to-study-for-1z0-116-oracle.html

How to study for 1z0-084 - Oracle Database 19c: Performance Management and Tuning

http://alexzaballa.blogspot.com/2022/06/how-to-study-for-1z0-084-oracle.html

How to study for 1z0-149 - Oracle Database PL/SQL Developer Certified Professional

http://alexzaballa.blogspot.com/2022/09/how-to-study-for-1z0-149-oracle.html

How to study for 1Z0-076 Oracle Certified Professional, Oracle Database 19c: Data Guard Administrator

http://alexzaballa.blogspot.com/2022/12/how-to-study-for-1z0-076-oracle.html

How to study for 1z0-078 - Oracle Certified Professional, Oracle Database 19c: RAC, ASM, and Grid Infrastructure Administrator

http://alexzaballa.blogspot.com/2022/12/how-to-study-for-1z0-078-oracle.html

Others:

How to study for 1Z0-902 - Oracle Exadata Database Machine X9M Implementation Essentials

http://alexzaballa.blogspot.com/2022/06/how-to-study-for-1z0-902-oracle-exadata.html

How to study for 1z0-106 - Oracle Linux 8 Advanced System Administration Exam

http://alexzaballa.blogspot.com/2022/12/how-to-study-for-1z0-106-oracle-linux-8.html

AWS (Courses from https://learn.acloud.guru):

AWS Certified Database – Specialty

AWS Certified Solutions Architect – Associate

AWS Certified Cloud Practitioner

GCP (Renew - Courses from https://go.qwiklabs.com):

Google Cloud Certified Professional Cloud Architect

Google Cloud Certified Professional Data Engineer

Google Cloud Certified Professional Database Engineer

24 certifications in total.

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More

How to study for 1z0-106 - Oracle Linux 8 Advanced System Administration Exam

Hi all,

I have 3 vouchers(Now, it's called "Oracle Exam Attempts") to use until December/18 (I won participating in the Oracle Cloud World) and I decided to take the Linux 8 exam as my third exam.

After a few weeks of studying Oracle Linux 8, especially the changes from previous versions, I cleared the exam.

The exam consists of 60 questions (very well elaborated) and you have 1.5 hours.

This exam is intended for people who use OEL 8 in their day-to-day activities as Linux Admin and not for Oracle DBAs.

If you have ULS, this is a good starting point:

https://mylearn.oracle.com/learning-path/oracle-linux-8-system-administration/84612

You have about 60 hours of videos and thousands of PDF pages.

If not, the Oracle Linux 8 Documentation and previous experience with OEL 8 will be fine.

Review the exam topics, everything will be at the exam:

https://education.oracle.com/oracle-linux-8-advanced-system-administration/pexam_1Z0-106

And last but not least, stay away from Brain Dumps!

Best of luck

Alex Zaballa 

Read More

How to study for 1z0-078 - Oracle Certified Professional, Oracle Database 19c: RAC, ASM, and Grid Infrastructure Administrator

 Hi all,

I have 3 vouchers(Now, it's called "Oracle Exam Attempts") to use until December/18 (I won participating in the Oracle Cloud World) and I decided to take the RAC 19c exam as my second exam.

After a few weeks of studying Clusterware/ASM/RAC, especially the new features, and reviewing many concepts, I cleared the exam.

The exam consists of 77 questions (very well elaborated) and you have 2 hours.

If you have ULS, this is a good starting point:

https://learn.oracle.com/ols/learning-path/oracle-database-19c-high-availability/38560/85282

If not, the Oracle Automatic Storage Management Documentation, Clusterware Administration and Deployment Guide, and Real Application Clusters Administration and Deployment Guide will be fine.

Review the exam topics, everything will be at the exam:

https://education.oracle.com/oracle-database-19c-rac-asm-and-grid-infrastructure-administration/pexam_1Z0-078

And last but not least, stay away from Brain Dumps!

Best of luck

Alex Zaballa 

Read More

How to study for 1Z0-076 Oracle Certified Professional, Oracle Database 19c: Data Guard Administrator

Hi all,

I have 3 vouchers(Now, it's called "Oracle Exam Attempts") to use until December/18 (I won participating in the Oracle Cloud World) and I decided to take the DG19c exam.

After a few weeks of studying DG, especially the new features, and reviewing many concepts, I cleared the exam.

The exam consists of 74 questions (very well elaborated) and you have 2 hours.

I submitted the exam when I had only 4 minutes and didn’t have time to review the marked questions.

If you have ULS, this is a good starting point:

https://learn.oracle.com/ols/learning-path/earn-the-oracle-db-19c-data-guard-admin-certified-professional-credential/38560/112128

This course is delivered by Seam Kim, one of the best OU instructors.

You will see a lot of good examples to clarify the concepts.

If not, the Oracle Data Guard Concepts and Administration will be fine.

For this exam, I should have studied more about the multitenant features related to DG and logical replication.

Almost all customers are using DG, but usually, they prefer to use Oracle Golden Gate for logical replications and upgrades with minimum downtime.

Review the exam topics, everything will be at the exam:

https://education.oracle.com/oracle-database-19c-data-guard-administration/pexam_1Z0-076

And last but not least, stay away from Brain Dumps!

Best of luck

Alex Zaballa 

Read More

SQL Monitor Report is your best friend

Hi all,

One of the quickest ways to help with the SQL tuning is by getting the SQL Monitor Report.

You have a lot of options to get this, like SQL Developer, OEM, command line, etc.

To get this using the command line, you can use:

set linesize 1000 pagesize 100 verify off trimout on trimspool on echo off head off feed off timing off termout off long 1000000 longchunksize 1000000
set serveroutput on size 1000000
spool sqlmon.html replace
clear scr
select dbms_sqltune.report_sql_monitor(
                sql_id => 'YOUR_SQL_ID',
                report_level=>'ALL',
                type => 'ACTIVE')
from dual;
spool off

One easy way to get the SQL_ID is searching the GV$SQL, something like:

select * from gv$sql where sql_text like 'SELECT bla bla bla%';

REMEMBER: By default, a SQL statement that either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution will be monitored (not true for Parallel queries, Parallel DMLs, and Parallel DDLs - it's on by default).

But it's possible to force monitoring for any SQL statement by adding the MONITOR hint to the statement.

SELECT /*+ MONITOR */ * from tab where id=1;

If you can't change the code, you can use SQL PATCH or something like this:

 ALTER SYSTEM SET EVENTS 'sql_monitor [sql:MY_SQL_ID] force=true';

Sometimes, the SQL is "old" and it's not in the GV$SQL_MONITOR anymore.

For these cases, you have some options to get the historical data (based on the AWR retention).

SELECT * FROM dba_hist_reports where key1 = 'YOUR_SQL_ID';

set linesize 1000 pagesize 100 verify off trimout on trimspool on echo off head off feed off timing off termout off long 1000000 longchunksize 1000000
set serveroutput on size 1000000
spool sqlmon.html replace
clear scr
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => YOUR_REPORT_ID, TYPE => 'ACTIVE') FROM dual;
spool off

Or you can use the Performance Hub:

set linesize 1000 pagesize 100 verify off trimout on trimspool on echo off head off feed off timing off termout off long 1000000 longchunksize 1000000
set serveroutput on size 1000000
spool sqlmon.html replace
clear scr
select dbms_perf.report_sql(sql_id=>'YOUR_SQL_ID',is_realtime=>0,type=>'active',selected_start_time=>To_date('09/28/2022 21:00:00','MM/DD/YYYY HH24:MI:SS'),selected_end_time=>To_date('08/28/2022 04:00:00','MM/DD/YYYY HH24:MI:SS') ) from dual;
spool off

Probably, you will need to get the SQL_ID searching the DBA_HIST_SQLTEXT, something like:

select * from DBA_HIST_SQLTEXT where sql_text like 'SELECT bla bla bla%';

SQL Monitor has a limit on the number of lines in the execution plan, In this case, you can increase the parameter "_sqlmon_max_planlines".

***To use the Real-Time SQL Monitoring feature you need an Oracle Tuning Pack license.

This post is a contribution to #JoelKallmanDay

Thanks

Alex

Read More

How to study for 1z0-149 - Oracle Database PL/SQL Developer Certified Professional

Hi all,

I had 2 vouchers(Now, it's called "Oracle Exam Attempt") to use until the end of September and I decided to take the PL/SQL 19c exam.

My first thought was " I'll give it a try, I don't think much has changed since 11g".

And I was completely wrong!

I didn't study, just scheduled the exam and got a really bad result!

After a week of studying the new features and reviewing many concepts, I got a good score :)

If you have ULS, this is a good starting point:

https://mylearn.oracle.com/exam/oracle-database-program-with-plsql-1z0-149/105037/110643/170351

https://mylearn.oracle.com/course/oracle-database-19c-plsql-workshop/78807/

https://mylearn.oracle.com/course/oracle-database-19c-advanced-plsql/86420/

If not, the Database PL/SQL Language Reference will be fine.

You should also take a look at https://oracle-base.com/, the examples are much better than the Oracle Documentation.

Review the exam topics, everything will be at the exam:

https://education.oracle.com/oracle-database-program-with-pl-sql/pexam_1Z0-149

In about 50% of the exam, you will need to be able to analyze the PL/SQL code and tell if it will work and if not, you will need to point out the possible failures.

And last but not least, stay away from Brain Dumps!

Best of luck

Alex Zaballa 

Read More

Upgrade From 11.2.0.4: Slow Performance Adding Nullable Columns With Default Values To AWR Tables

Hi all,

I was in a project where the customer upgraded a large production database from 11g to 19c.

Phase 0 of the upgrade process took almost 4 hours and the DDLs responsible for all that time were related to new columns on AWR tables (WRH$ tables).

I was talking to Rodrigo Jorge (PM for upgrades and migrations) and he pointed me to this patch: 30387640

For example, these 2 DDLs took about 2 hours to run:

alter table WRH$_SQLSTAT add (obsolete_count number default 0);

alter table WRH$_SEG_STAT add (im_membytes number default 0);

I remember since 11g Oracle should only update the data dictionary when you are adding a new column with a default value, but what I didn't remember was that it works only for NOT NULL columns.

I found this after doing some research and here you have a great blog post about it:

https://chandlerdba.com/2014/10/30/adding-not-null-columns-with-default-values/

And another good thing, this restriction no longer exists in 12c+.

https://chandlerdba.com/2014/12/01/adding-a-default-column-in-12c/

If you are upgrading from 11g to 19c and you have a large AWR repository, consider applying patch 30387640 before the upgrade.

Thanks
Alex

Read More

Downgrading Oracle Database to an Earlier Release

Hi all,

I was in a project where the customer had one database in a training environment to upgrade from 11g to 19c.

The customer requirement was to have a fallback option in case of any issue in the next few days.

The fallback option during the upgrade is to create a GUARANTEE RESTORE POINT. But after a few days, you can lose data in case you go back to the restore point.

To be honest, I have never seen a downgrade in all my Oracle life, but it was a customer requirement.

And yes, we are not touching the COMPATIBLE parameter after the upgrade :)

We did the upgrade to 19c using AutoUpgrade and everything works great.

But, when we decided to test the catdwgrd.sql, we had a lot of ora-600 at the end of the downgrade process.

Then, I found something that I was not aware of: A document called "Required Task to Preserve Downgrade Capability".

Where you have some patches to apply on 11g:

Required Task to Preserve Downgrade Capability

Downgrading Oracle Database to an Earlier Release

Also, make sure you are taking care of the Timezone, especially updating Timezone files in the 11g home.

Thanks

Alex

 

Read More

Row Count + DBMS_COMPARISON - Logical Replication

Hi all,

I was in a project where logical replication was in place.

It was going from 11g non-exa to 19c on Exadata and they ran into some issues during their last migration attempt a year ago.

At that time OGG was sending data from 11g to 19c and another third-party tool was sending from 19c back to 11g (in case of rollback needed). The problem was that this tool is based on triggers to capture and DML commands to replicate, but you can imagine how bad it is when you have a large number of transactions and 2 database nodes.

Now, they decided to use OGG for everything and it was a great decision.

The customer asked me to validate the data (source vs destination) to make sure everything was in sync.

My first thought was to use OGG Veridata, but the license cost was an impediment to this project.

Last time, they used some procedures to do row counts at source and destination and compare the data. I got this code and worked for a few minutes to improve it. There were 16 procedures and about 30 tables and it’s now one procedure and a few tables. It’s not perfect and there is room to improve, but you can get an idea here.

As mentioned by Connor in this video, it’s not a regular situation and this kind of thing should be carefully analyzed before starting to count every row in every table.

Another point from the business area was to validate if the data was the same, not only the number of rows. For this case, I decided to use DBMS_COMPARISON.

Here you can see an example of how to generate the scripts to validate:

select '
BEGIN
DBMS_COMPARISON.drop_comparison (
    comparison_name    => ''cutover_comp_bm'');
END;
/

BEGIN
  DBMS_COMPARISON.create_comparison (
    comparison_name    => ''cutover_comp_bm'',
    schema_name        => ''YOUR_SCHEMA'',
    object_name        => '''||table_name||''',
    dblink_name        => ''db_compare'',
    remote_schema_name => ''YOUR_SCHEMA'',
    remote_object_name => '''||table_name||''');
END;
/


SET SERVEROUTPUT ON
DECLARE
  l_scan_info  DBMS_COMPARISON.comparison_type;
  l_result     BOOLEAN;
  v_comparison_name    varchar2(100):= ''cutover_comp_bm'';
BEGIN
  l_result := DBMS_COMPARISON.compare (
                comparison_name => v_comparison_name,
                scan_info       => l_scan_info,
                perform_row_dif => TRUE
              );

  IF NOT l_result THEN
    DBMS_OUTPUT.put_line(v_comparison_name||'' Differences found. scan_id='' || l_scan_info.scan_id);
  ELSE
    DBMS_OUTPUT.put_line(v_comparison_name||'' No differences found.'');
  END IF;
END;
/

' 
from dba_tables
where owner='YOUR_SCHEMA';

Some tables were really big in this database(billion of rows) and for this situation, I found the parameter scan_mode.

You can use something like:

    scan_mode          => dbms_comparison.CMP_SCAN_MODE_RANDOM,
    scan_percent       => 0.001    

DBMS_COMPARISON cannot compare LOB columns and you can use this parameter to limit the columns to be compared:

column_list        => 'YOUR COLUMNS SEPARATED BY COMMA'

You can use this SQL to generate a column list:

SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id)
  FROM dba_tab_columns
 WHERE owner = 'YOUR_OWNER'
   and table_name='YOUR_TABLE'
   and data_type not like '%LOB%';

For 21c, I found the CHECKSUM and I'm planning to take a look at it for future projects.

Thanks
Alex

Read More

How to study for 1Z0-997-22 - Oracle Cloud Infrastructure 2022 Architect Professional

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in taking these certifications.

Well, not all exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see some new things(comparing the course topics with the previous years). And that's true, I got a lot of new questions.

Of course, I don't remember the question from the 2021 exam (did it one year ago), but I didn't remember studying some of this content before.

A good starting point is here:

https://learn.oracle.com/ols/learning-path/become-an-oci-architect-professional/35644/108709

What do you need to focus on? 

• EVERYTHING :)

Official Documentation:

https://docs.cloud.oracle.com/en-us/iaas/Content/home.htm

User guide:

https://docs.cloud.oracle.com/en-us/iaas/pdf/ug/OCI_User_Guide.pdf

But I recommend you to have a free account and test everything you can! The biggest problem will be testing cross-region features because free account permits to subscribe to only one region.

You can also find some examples of questions in this link and they are really helpful to understand what to expect on the real test:

https://learn.oracle.com/ols/module/practice-exam-oci-2022-architect-professional-certification/35644/108710

Take an extra look at the Azure integration with OCI.

These 2 blog posts from Simo are great to understand the exam:

https://www.thatfinnishguy.blog/2019/11/28/how-i-studied-for-the-oracle-cloud-infrastructure-2019-certified-architect-professional-1z0-997/

and 2021 exam version:

https://www.thatfinnishguy.blog/2021/09/17/passing-the-oci-certified-architect-professional-2021-1z0-997-21/

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More

Database Migration/Upgrade - Performance Advice

Hi all

I was on a project where logical replication (OGG) was used to move a critical database from 11g non-exa to 19c on Exadata with the smallest downtime possible.

In terms of performance, for the top 100 SQLs, we had 96 improvements including SQLs running 40x faster due to smart scans. But, we had 6 regressions.

One SQL went from 0.2 ms to 0.6 ms.

I know you are thinking: “not a big deal”.

But when this SQL is executed in a batch process millions of times, the batch time goes from 42 minutes to 126 minutes and it can cause a lot of problems for the client.

I know, they should review and change this row-by-row approach, but you have no time for this during a go-live.

For this case, I got the SQL plan baseline from 11g and imported it on 19c.
Problem solved!

Now, the customer has time to improve this process and SQLs.

For example, one SQL in this process was taking 40 minutes to run, adding 2 hints (Full and Parallel) the smart scan is enabled and the same SQL is now taking 6 minutes.

Some tips:

1 - Increase your AWR retention to 31 days (60*24*31) and decrease the interval to have more granularity:

execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 44640);

2 - Start saving your SQL PLAN BASELINES in case you have regressions after the migration/upgrade (11g database):

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = true;

**take a look on your SYSAUX tablespace, because you can start having space pressure

3 - Export your AWR Repository on the 11g database (in case you need any performance information in the future and in the 11g database is not accessible anymore):

$ORACLE_HOME/rdbms/admin/awrextr.sql

4 - Sometimes, it's a good idea to copy DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT to your new database (in case the 11g database is not accessible anymore and you need SQL time information).

One example of how to compare the execution time between the old database and the new database:

col execs for 999,999,999
col avg_etime for 999,999.999999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = 'YOR_SQL_ID'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

Carlos Sierra also has a great script to check regressions:

https://carlos-sierra.net/2014/11/02/finding-sql-with-performance-changing-over-time/

5 - Transfer all your SQL Plan Baselines to the new database to quickly fix SQL regressions:

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name      => 'spm_stage_table',
    table_owner     => 'your_user');
END;
/

SET SERVEROUTPUT ON
DECLARE
  v_plans  NUMBER;
BEGIN
  v_plans := DBMS_SPM.pack_stgtab_baseline(
    table_name      => 'spm_stage_table',
    table_owner     => 'your_user');

  DBMS_OUTPUT.put_line('SQL Plans Total: ' || v_plans);
END;
/

Use expdp/impdp to move the table spm_stage_table to the new database.

An example of how to find the SQL HANDLE used by a specific SQL_ID:

with subq_mysql as
    (select sql_id
     ,      (select dbms_sqltune.sqltext_to_signature(ht.sql_text)
             from dual) sig
     from   dba_hist_sqltext       ht
     where  sql_id = 'YOUR_SQL_ID')
    ,    subq_baselines as
    (select b.signature
     ,      b.plan_name
    ,      b.accepted
    ,      b.created
    ,      o.plan_id
    ,      b.sql_handle
    from   subq_mysql             ms
    ,      dba_sql_plan_baselines b
    ,      sys.sqlobj$            o
    where  b.signature   = ms.sig
    and    o.signature   = b.signature
    and    o.name        = b.plan_name)
   ,    subq_awr_plans as
   (select  sn.snap_id
    ,       to_char(sn.end_interval_time,'DD-MON-YYYY HH24:MI') dt
    ,       hs.sql_id
    ,       hs.plan_hash_value
    ,       t.phv2
    ,       ms.sig
    from    subq_mysql        ms
    ,       dba_hist_sqlstat  hs
    ,       dba_hist_snapshot sn
    ,       dba_hist_sql_plan hp
    ,       xmltable('for $i in /other_xml/info
                      where $i/@type eq "plan_hash_2"
                      return $i'
                     passing xmltype(hp.other_xml)
                     columns phv2 number path '/') t
    where   hs.sql_id          = ms.sql_id
    and     sn.snap_id         = hs.snap_id
    and     sn.instance_number = hs.instance_number
    and     hp.sql_id          = hs.sql_id
    and     hp.plan_hash_value = hs.plan_hash_value
    and     hp.other_xml      is not null)
   select awr.*
   ,       nvl((select max('Y')
               from   subq_baselines b
                where  b.signature = awr.sig
               and    b.accepted  = 'YES'),'N') does_baseline_exist
   ,      nvl2(b.plan_id,'Y','N') is_baselined_plan
   ,      to_char(b.created,'DD-MON-YYYY HH24:MI')  when_baseline_created
   ,b.sql_handle
   from   subq_awr_plans awr
   ,      subq_baselines b
   where  b.signature (+) = awr.sig
   and    b.plan_id   (+) = awr.phv2
  order by awr.snap_id;

Example of how to load the SQL Plan Baseline for one specific SQL:

SET SERVEROUTPUT ON
DECLARE
  v_plans  NUMBER;
BEGIN
  v_plans := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'spm_stage_table',
    table_owner     => 'your_user',
    sql_handle => 'SQL_2644bb9a823bec0e');

  DBMS_OUTPUT.put_line('Plan Unpacked: ' || v_plans);
END;
/

6 - If you want to use a good execution plan and have it on the AWR repository, you can create a SQL Plan Baseline:

variable x number;
begin
  :x := dbms_spm.load_plans_from_awr( begin_snap=>310417,end_snap=>310418,
                                     basic_filter=>q'# sql_id='cm4dv9adjj6u3' and plan_hash_value='1563030161' #' );
end;
/ 

Thanks
Alex

Read More

How to study for 1z0-931-22 - Oracle Autonomous Database Cloud 2022 Professional

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in taking these certifications.

Well, not all exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a few new things(comparing the course topics with the previous years). 

I don't remember the question from the 2021 exam (did it one year ago), but I was not surprised with any new questions that I have no idea about.

A good starting point is here:

https://learn.oracle.com/ols/learning-path/become-an-autonomous-database-professional/35644/97809

You can also find some examples of questions in this link and they are really helpful to understand what to expect on the real test:

https://learn.oracle.com/ols/module/practice-exam-oracle-autonomous-database-administration-workshop/35644/98401

Make sure you understand all the differences between Autonomous Shared and Autonomous Dedicated.

Study:

• Autonomous Dedicated
• Autonomous JSON
• Autonomous Data Guard
• Graph
• Oracle Text
• Data Insights
• Spatial

And last but not least, stay away from Brain Dumps!

Best of luck

Alex Zaballa 

Read More

How to study for 1z0-1093-22 - Oracle Cloud Database Services 2022 Professional

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in taking these certifications.

Well, not all exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a few new things(comparing the course topics with the previous years). 

I don't remember the question from the 2021 exam (did it one year ago), but I was not surprised with any new questions that I have no idea about.

A good starting point is here:

https://learn.oracle.com/ols/learning-path/become-an-oracle-cloud-database-services-professional-2022/35644/108789

I recommend you to have a free account and test everything you can, especially NoSQL and MYSQL.

You can also find some examples of questions in this link and they are really helpful to understand what to expect on the real test.

Make sure you understand all the differences between ExaCS and ExaCC.

Also, have a good understanding of Monitoring and especially Performance Hub.

And last but not least, stay away from Brain Dumps!

Best of luck

Alex Zaballa 

Read More

How to study for 1z0-1094-22 - Oracle Cloud Database Migration and Integration 2022 Professional

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in taking these certifications.

Well, not all the exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a few new things(comparing the course topics with the previous years). 

I don't remember the question from the 2021 exam (did it one year ago), but I was not surprised with any new questions that I have no idea about.

And I did the test at 9:00PM, after 13 working hours (I was almost sleeping).

After reading a message from Simo on Twitter, I decided to try my luck :)

This exam privileges the knowledge acquired on a daily basis much more than the knowledge acquired in preparation for the test.

In this preparation material, I missed having something about how to analyze the workload of environments before migration and how this impacts the decision of choosing the shape and type of service to be used in the Cloud.

It's also missing some checks you have to do before working in a big production migration.

And I think they should share more about Cloud Premigration Advisor Tool.

Since I created mig360 and I'm working on Cloud migration projects, the "regular" database migration topics on this test are ok.

For sure you will need some real-world experience to clear most of the ZDM related questions.

About the ODI topics, It was very deep (much more than I expect).

Even working in environments where I have to deal with ODI every day, A lot of questions are difficult to answer if you are not an ODI Developer.

About the preparation, you can find everything you need here:

https://learn.oracle.com/ols/learning-path/become-an-oracle-cloud-database-migration-and-integration-professional-2022/35644/108775

You can find some examples of questions here:

 

https://learn.oracle.com/ols/module/practice-exam-oracle-cloud-database-migration-and-integration-professional/35644/100745

I hope Oracle is planning to allow us to download the PDFs again because the online reader is terrible. From 0 to 10, the usability of this tool is -1, especially on mobile devices.

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More

How to study for 1Z0-1072-22 - Oracle Cloud Infrastructure 2022 Architect Associate

 Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in taking these certifications.

Well, not all the exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see some new things(comparing the course topics with the previous years). And that's true, I got a lot of new questions.

Of course, I don't remember the question from the 2021 exam (did it one year ago), but I didn't remember studying some of this content before.

And I did the test at 10:00PM, after 14 working hours (my brain was not working well :) ).

A good starting point is here:

https://learn.oracle.com/ols/learning-path/become-an-oci-architect-associate/35644/108703

I hope Oracle is planning to allow us to download the PDFs again because the online reader is terrible. From 0 to 10, the usability of this tool is -1, especially on mobile devices.

Official Documentation:

https://docs.cloud.oracle.com/en-us/iaas/Content/home.htm

User guide:

https://docs.cloud.oracle.com/en-us/iaas/pdf/ug/OCI_User_Guide.pdf

You can find some examples of questions here:

 

https://learn.oracle.com/ols/module/practice-exam-oracle-cloud-infrastructure-architect-associate-certification/35644/108768

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More

How to study for 1Z0-1084-22 - Oracle Cloud Infrastructure 2022 Certified Developer Professional

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in taking these certifications.

Well, not all the exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a few new things(comparing the course topics with the previous years). 

I don't remember the question from the 2021 exam (did it one year ago), but I was not surprised with any new questions that I have no idea about.

A good starting point is here:

https://learn.oracle.com/ols/learning-path/become-an-oci-developer-professional/35644/108219

I hope Oracle is planning to allow us to download the PDFs again because the online reader is terrible. From 0 to 10, the usability of this tool is -1, especially on mobile devices.

What do you need to focus on?

• Cloud-Native Architecture
• Kubernetes
• Docker
• Microservices
• Software Testing
• OCI Logging Services

These demos are great to understand all the Development process using OCI:

https://learn.oracle.com/ols/course/developing-cloud-native-applications-on-oci-workshop-solution-recordings/35644/102393

Some of the content of this test is not in the videos or PDFs, this is why it's always good to read the OCI Official documentation.

Don't forget to read the topics:

https://education.oracle.com/oracle-cloud-infrastructure-2022-developer-professional/pexam_1Z0-1084-22

The practice exam is good and you can have a good feel of what to expect during the exam.

But I recommend you to have a free account and test everything you can!

Additional material:

https://kubernetes.io/
https://fnproject.io/

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More

How to study for 1Z0-1104-22 - Oracle Cloud Infrastructure 2022 Security Professional

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in taking these certifications.

Well, not all the exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work  on a day-to-day basis 🙂

A good starting point is here:

https://learn.oracle.com/ols/learning-path/become-a-cloud-security-professional/35644/108608

Reading the Official documentation is always a plus for the exams:

https://docs.oracle.com/en-us/iaas/Content/Security/Concepts/security_guide.htm

https://docs.oracle.com/en-us/iaas/Content/Security/Concepts/security_overview.htm

I also recommend you take a deep look at network features, peering, NSGs, SLs, connectivity, and how they relate to security. Don't forget to go deep on Vault, Keys, Secrets, etc.

I hope Oracle is planning to allow us to download the PDFs again because the online reader is terrible. From 0 to 10, the usability of this tool is -1, especially on mobile devices.

Don't forget to read the topics:

https://education.oracle.com/oracle-cloud-infrastructure-security-2022-professional/pexam_1Z0-1104-22

The practice exam is good and you can have a good feel of what to expect during the exam.

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More

How to study for 1z0-1067-22 - Oracle Cloud Infrastructure 2022 Cloud Operations Professional

Hi all,

I decided to take all the 2022 OCI exams to check what changed and guide people in the process of taking these certifications.

Well, not all the exams. I will do only the ones that I did in 2019/2020/2021 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a few new things(comparing the course topics with the previous years). 

I don't remember the question from the 2021 exam (did it one year ago), but I was not surprised with any new questions that I have no idea about.

This certification changed the name from "Associate" to "Professional".

The focus for this test is day-to-day activities, you need to know the commands/syntax and not only concepts. Of course, concepts are very important to understand the questions.

What do you need to focus on?

• OCI-CLI
• Terraform
• Policies
• MQL
• Quotas
• What and how you can move Cross-region
• Budgets and Alerts
• What is related to Compartments and what is related to the Tenancy
• Upload and Download files using the cli and the console
• Tags
• NVMe - Performance and how to protect
• Object Storage life cycle
• VPNs

You can find everything here:

https://learn.oracle.com/ols/learning-path/become-an-oci-cloud-operations-professional/35644/108754

But I recommend you to have a free account and test everything you can!

I hope Oracle is planning to allow us to download the PDFs again because the online reader is really bad. From 0 to 10, the usability of this tool is -1, especially on mobile devices.

Don't forget to read the topics:

https://education.oracle.com/en/cat%C3%A1logo-de-produtos-ouexam-pexam_1z0-1067-22/pexam_1Z0-1067-22

The practice exam is good and you can have a good feel of what to expect during the exam.

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More

How to study for 1z0-1085-22 - Oracle Cloud Infrastructure 2022 Certified Foundations Associate

Hi all,

Usually, May is the month of the year to start renewing OCI Certifications to the latest version!

Oracle University took a little longer this year, but now, it's time :)

This is a simple test (only generic concepts) and you have about 11+ hours of videos to watch and 2 PDFs to read.

The starting point is here: https://learn.oracle.com/ols/learning-path/become-an-oci-foundations-associate/35644/108448

I hope Oracle is planning to allow us to download the PDFs again because the online reader is really bad. From 0 to 10, the usability of this tool is -1, especially on mobile devices.

A lot of the content of this test is not in the videos or PDFs, this is why it's always good to read the OCI Official documentation.

The practice exam is good and you can have a good feel of what to expect during the exam.

You can do the exam online for free (15 attempts to pass) and it's not even proctored! 

Good luck! And last but not least, stay away from Brain Dumps!

Alex Zaballa

Read More

How to study for 1Z0-902 - Oracle Exadata Database Machine X9M Implementation Essentials

 Hi all,

About the level of the exam, it's hard! Much harder than I expected (compared with previous Exadata exams)!

Maybe that explains why we are seeing a few people with this certification!

I hope Oracle is planning to allow us to download the PDFs again because the online reader is really bad. From 0 to 10, the usability of this tool is -1, especially on mobile devices.

A good starting point is here:

https://learn.oracle.com/ols/learning-path/earn-the-oracle-exadata-x9m-certified-impl-specialist-credential/38560/95058

You have 26+ hours of videos and 1,140 PDF pages.

Some of the content of this test are not in the videos or PDFs, this is why it's always good to read the Exadata Database Machine Documentation.

Some of the questions are not in the Oracle University material.

I was not expecting to see questions about the Exadata rack Installation, but it's on the exam topics (I missed this).

In this case, I recommend you study the Installation and Configuration Guide for Exadata Database Machine

Unfortunately,  there is no practice exam to check what to expect during the exam.

And last but not least, stay away from Brain Dumps!

Best of luck
Alex Zaballa

Read More