Hello everyone,
Best of luck
Hello everyone,
Hi all,
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 🙂)
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
Google Cloud Certified Professional Cloud Architect
Google Cloud Certified Professional Data Engineer
24 certifications in total.
And last but not least, stay away from Brain Dumps!
Best of luck
Alex Zaballa
Hi all,
Hi all,
Hi all,
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
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
Hi all,
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
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
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
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';
scan_mode => dbms_comparison.CMP_SCAN_MODE_RANDOM,
scan_percent => 0.001
column_list => 'YOUR COLUMNS SEPARATED BY COMMA'
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%';
Hi all,
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 🙂
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;
$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).
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
/
https://carlos-sierra.net/2014/11/02/finding-sql-with-performance-changing-over-time/
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;
/
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;
/
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;
/
Hi all,
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 🙂
Hi all,
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 🙂
Hi all,
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 🙂
This exam privileges the knowledge acquired on a daily basis much more than the knowledge acquired in preparation for the test.
You can find some examples of questions here:
Hi all,
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 🙂
Official Documentation:
User guide:
You can find some examples of questions here:
Hi all,
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-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?
Hi all,
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:
Hi all,
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 🙂