How to study for 1Z0-1084-23 - Oracle Cloud Infrastructure 2023 Certified Developer Professional

Hi everyone,

I decided to take all the 2023 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 to 2022 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a lot of new things(comparing the course topics with the previous years and OCI database improvements) and that was true, a lot of new questions. 

A good starting point is here:

https://mylearn.oracle.com/ou/learning-path/become-an-oci-developer-professional-2023/121998

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

New Topics:

• OCI Service Mesh
• Serverless Queues

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-2023-developer-professional/pexam_1Z0-1084-23

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-1109-23 - Oracle Cloud Infrastructure 2023 DevOps Certified Professional

Hi everyone,

I decided to take all the 2023 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 to 2022 and are the things that I work on a day-to-day basis 🙂

On this test, I was not expecting to see new things, since the 2022 exam is very recent. But unfortunately, it was not true, I got a lot of topics not covered in the videos and especially on the PDFs. 

This exam consists of 55 questions and you have 1.5 hours.

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

https://mylearn.oracle.com/ou/learning-path/become-an-oci-devops-professional-2023/121756

These are good documentation to read and understand the concepts:

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

  https://docs.oracle.com/en-us/iaas/Content/devops/using/getting_started.htm

  https://developer.oracle.com/technologies/devops.html

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.

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

https://education.oracle.com/learning-paths/pexam_1Z0-1109-23

1st attempt:

2nd attempt:

One interesting thing about his test, if you go to the Oracle official practice test, you will find a lot of questions that you will not find the answer in Oracle's material/documentation. And the worst part, you can find these answers on AWS, GCP, AZURE, and to my surprise, Alibaba Cloud.

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

Best of luck

Alex Zaballa 

Read More

How to study for 1Z0-1110-23 - Oracle Cloud Infrastructure Data Science 2023 Professional

Hello everyone,

I was not planning to take this certification, since it's not something I work with and/or have contact with daily. But, when I was studying for GCP certifications, I was introduced to some tools and concepts and decided to learn more about them.

This exam consists of 55 questions and you have 1.5 hours.

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

https://mylearn.oracle.com/ou/learning-path/become-an-oci-data-science-professional-2023/121944

These are good documentation to read and understand the concepts:

https://docs.oracle.com/en-us/iaas/data-science/using/data-science.htm

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

https://education.oracle.com/oracle-cloud-infrastructure-data-science-2022-professional/pexam_1Z0-1110-23

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.

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

Best of luck

Alex Zaballa 

Read More

How to study for 1Z0-1096-23 - Oracle Machine Learning using Autonomous Database 2023 Associate

Hi everyone,

I decided to take all the 2023 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 to 2022 and are the things that I work on a day-to-day basis 🙂

I'm not working with OML, but when I did the GCP Certifications and OCI Data Science I became interested in these topics. Then, this was the first time that I took this exam.

A good starting point is here:

https://mylearn.oracle.com/ou/learning-path/become-an-associate-on-oracle-machine-learning-with-autonomous-database-2023/119951

The material is very simple, you have 2 hours of videos and 3 hours of hands-on lab.

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.

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.oracle.com/en/database/oracle/machine-learning/

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

Best of luck
Alex Zaballa

Read More

How to study for 1Z0-1072-23 - Oracle Cloud Infrastructure 2023 Architect Associate

Hi everyone,

I decided to take all the 2023 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 to 2022 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a lot of new things(comparing the course topics with the previous years and OCI database improvements) and that was true, a lot of new questions. 

A good starting point is here:

https://mylearn.oracle.com/ou/learning-path/become-an-oci-architect-associate-2023/122195

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

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

Best of luck
Alex Zaballa

Read More

How to study for 1z0-1093-23 - Oracle Cloud Database Services 2023 Professional

Hi everyone,

I decided to take all the 2023 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 to 2022 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a lot of new things(comparing the course topics with the previous years and OCI database improvements) and that was true, a lot of new questions. 

A good starting point is here:

https://mylearn.oracle.com/ou/learning-path/become-an-oracle-base-database-services-professional-2023/122178

I recommend you to have a free/trial 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-931-23 - Oracle Autonomous Database Cloud 2023 Professional

Hi everyone,

I decided to take all the 2023 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 to 2022 and are the things that I work on a day-to-day basis 🙂

On this test, I was expecting to see a lot of new things(comparing the course topics with the previous years and autonomous database improvements) and that was true, a lot of new questions. 

A good starting point is here:

https://mylearn.oracle.com/ou/learning-path/become-an-oracle-autonomous-database-cloud-professional-2023/122183

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 Autonomous Shared and Autonomous Dedicated.

Study hard:

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

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

Best of luck

Alex Zaballa 

Read More

How to study for 1z0-1105-23 - Oracle Cloud Data Management 2023 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 year, we have free OCI Certifications again and some good prizes.

You can find more details here.

Here you can find the list of live sessions to help you with the preparation.

About the OCI Cloud Data Management 2023 Foundations Exam, this is a simple test (only generic concepts) and you have about 6 hours of videos to watch and 2 PDFs to read.

The starting point is here: https://mylearn.oracle.com/ou/learning-path/become-an-oracle-cloud-data-management-foundations-associate-2023/122101

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.

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.

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 (55 questions in 1.5 hours ) for free (15 attempts to pass) and it's not even proctored! 

You can do this exam in English and Brazilian Portuguese.

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

Alex Zaballa

Read More

How to study for 1z0-1085-23 - Oracle Cloud Infrastructure 2023 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 year, we have free OCI Certifications again and some good prizes.

You can find more details here.

Here you can find the list of live sessions to help you with the preparation.

About the OCI Foundation Exam, this is a simple test (only generic concepts) and you have about 5 hours of videos to watch, 2 hours of hands lab, and 2 PDFs to read.

The starting point is here: https://mylearn.oracle.com/ou/learning-path/become-an-oci-foundations-associate-2023/122043

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.

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.

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 (35 questions in one hour ) for free (15 attempts to pass) and it's not even proctored! 

You can do this exam in English, Brazilian Portuguese and Spanish.

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

Alex Zaballa

Read More

How to study for 1z0-1094-23 - Oracle Cloud Database Migration and Integration 2023 Professional

Hi all,

I decided to take all the 2023 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 to 2022 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) and that was true, a lot of new questions. 

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 analyzing 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 on 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://mylearn.oracle.com/ou/learning-path/become-an-oracle-cloud-database-migration-and-integration-professional-2023/122246

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

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-1115-23-Oracle Cloud Infrastructure 2023 Multicloud Architect Associate

Hello everyone,

After a long time without taking certifications (4 months), I decided to study and try this new exam from Oracle. 

The exam consists of 30 questions and you have 1 hour to finish.

The course is very comprehensive and the certification questions are very well elaborated!

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

https://mylearn.oracle.com/ou/learning-path/become-an-oci-multicloud-architect/120606

These are good documentation to read and understand the concepts:

https://docs.oracle.com/en-us/iaas/Content/multicloud/intro_how_multi-cloud_works.htm

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

https://education.oracle.com/oracle-cloud-infrastructure-2023-multicloud-architect-associate/pexam_1Z0-1115-23

On the ULS course, 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.

Be sure you have a good understanding of FastConnect and ODSA.

Here you can find great notes from https://twitter.com/BrokeDba about what to study.

I far as I remember, this is the first time in my life that I achieved this score in a certification exam :)

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

Best of luck

Alex Zaballa 

Read More

How to study for 1Z0-1110-22 - Oracle Cloud Infrastructure Data Science 2022 Professional

Hello everyone,

I was not planning to take this certification, since it's not something I work with and/or have contact with daily. But, when I was studying for GCP certifications, I was introduced to some tools and concepts and decided to learn more about them.

This is a new exam e a brand new content, the exam consists of 55 questions and you have 1.5 hours.

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

https://mylearn.oracle.com/ou/learning-path/become-an-oci-data-science-professional/107725

These are good documentation to read and understand the concepts:

https://docs.oracle.com/en-us/iaas/data-science/using/data-science.htm

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

https://education.oracle.com/produktkatalog-ouexam-pexam_1z0-1110-22/pexam_1Z0-1110-22

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://mylearn.oracle.com/ou/component/practice-exam-oci-data-science-professional-certification/107703

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

Best of luck

Alex Zaballa 

Read More

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

Hi all,

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

This is a new exam e a brand new content, the exam consists of 50 questions and you have 1.5 hours.

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

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

These are good documentation to read and understand the concepts:

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

  https://docs.oracle.com/en-us/iaas/Content/devops/using/getting_started.htm

  https://developer.oracle.com/technologies/devops.html

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

https://education.oracle.com/oracle-cloud-infrastructure-devops-professional/pexam_1Z0-1109-22

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

Best of luck

Alex Zaballa 

Read More

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