What 1Z0-076 Certification Really Means for Tomorrow

In an era where data is the lifeblood of every enterprise, ensuring its continuous availability and protection against unforeseen disasters is not just a best practice – it's a fundamental requirement. Organizations worldwide rely heavily on robust database systems, and Oracle Database 19c stands as a cornerstone for many mission-critical applications. But even the most powerful database is vulnerable without a comprehensive high availability and disaster recovery strategy. This is where Oracle Data Guard comes into play, and why the 1Z0-076 Certification is becoming an increasingly vital credential for database professionals.

The 1Z0-076 certification isn't merely about understanding a set of features; it signifies a strategic foresight into maintaining business continuity and resilience in an ever-evolving digital landscape. As an expert SEO and certification-focused article writer, my goal is to provide deep insights into the Oracle 1Z0-076 certification, its syllabus, and its profound implications for the future of database administration. We'll explore how mastering Oracle Database 19c Data Guard positions professionals to safeguard critical data assets, ensuring operational stability and paving the way for advanced, highly available architectures.

The Evolving Landscape of Data Management and High Availability

The demands on database systems have never been higher. Downtime, whether planned or unplanned, can lead to significant financial losses, reputational damage, and erosion of customer trust. With the proliferation of cloud computing, big data analytics, and real-time processing, the need for zero data loss and near-zero downtime solutions is paramount. Oracle Data Guard, a core component of Oracle Database, provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable Oracle databases to survive disasters and data corruptions.

Tomorrow's database administrators and architects won't just manage databases; they'll be guardians of data availability, performance, and integrity. The ability to design, implement, and manage complex disaster recovery solutions like Oracle Data Guard will differentiate true experts. This certification validates the skills necessary to perform these critical tasks, making it a benchmark for excellence in database resilience.

Why Oracle Database 19c Data Guard is Critical

Oracle Database 19c, as a Long Term Support (LTS) release, is designed for stability and longevity. Its integration with Data Guard offers unparalleled capabilities for high availability, disaster recovery, and data protection. Data Guard ensures that your primary database remains available even during planned outages or unplanned failures by maintaining synchronized standby databases. These standby databases can be located across geographical regions, offering maximum protection against site-specific disasters.

Furthermore, features like Active Data Guard unlock read-only capabilities on standby databases, offloading reporting and backup operations from the primary, thereby optimizing resource utilization and performance. Understanding these sophisticated features and their practical application is at the heart of the 1Z0-076 certification.

Understanding the 1Z0-076 Certification: Oracle Database 19c - Data Guard Administration

The 1Z0-076 certification validates your expertise in implementing, maintaining, and troubleshooting Oracle Data Guard configurations with Oracle Database 19c. It covers everything from the basics of Data Guard to advanced topics like Data Guard Broker administration, fast-start failover, and integration with RMAN for backup and recovery.

Let's delve into the specifics of this crucial exam:

• Exam Name: Oracle Database 19c - Data Guard Administration
• Exam Code: 1Z0-076
• Exam Price: USD $245 (Pricing may vary by country or by localized currency)
• Duration: 120 minutes
• Number of Questions: 74
• Passing Score: 61%

Achieving this passing score demonstrates a robust understanding of Data Guard concepts and practical administration tasks. For more details on the certification, you can always refer to the official Oracle 1Z0-076 exam page.

Who Should Pursue This Certification?

This certification is ideal for database administrators, database architects, and anyone responsible for ensuring the high availability and disaster recovery of Oracle Database 19c environments. It's particularly beneficial for professionals looking to specialize in critical infrastructure management and those aiming to validate their skills in a rapidly evolving IT landscape.

Deep Dive into the 1Z0-076 Exam Syllabus Topics

The comprehensive syllabus for the 1Z0-076 exam ensures that certified professionals possess a holistic understanding of Oracle Data Guard. Each topic is designed to build foundational knowledge and then progress to advanced implementation and management strategies. Mastery of these areas is essential not just for passing the exam, but for effectively deploying and maintaining robust Data Guard environments in real-world scenarios.

Oracle Data Guard Basics

This foundational topic introduces the core components and architecture of Oracle Data Guard. It covers the concepts of primary and standby databases, redo transport services, and apply services. Understanding these basics is crucial for grasping how Data Guard ensures data synchronization and protection. For tomorrow, this means being able to articulate the fundamental benefits of Data Guard to stakeholders and design basic high-availability solutions.

Managing Oracle Net Services in a Data Guard Environment

Effective communication between primary and standby databases is paramount. This section focuses on configuring and managing Oracle Net Services, including listener configurations, tnsnames.ora entries, and service registration. Proper network setup is critical for seamless redo transport and client connectivity, particularly in complex, geographically dispersed Data Guard configurations. Future administrators will need to troubleshoot intricate network issues that span on-premises and cloud deployments.

Creating a Physical Standby Database by Using SQL and RMAN Commands

This is a hands-on skill area, detailing the process of building a physical standby database using both SQL commands and Oracle Recovery Manager (RMAN). It includes steps like preparing the primary database, copying data files, creating a control file, and initiating redo apply. Proficiency here means not just knowing the commands but understanding the underlying mechanisms and potential pitfalls, which is vital for quick disaster recovery setup and troubleshooting.

Using Oracle Active Data Guard: Supported Workloads in Read-Only Standby Databases

Oracle Active Data Guard is a key feature, enabling read-only access to a physical standby database while it is actively applying redo from the primary. This topic explores the various workloads supported, such as reporting, ad-hoc queries, and backups. In tomorrow's data-intensive world, leveraging Active Data Guard for workload offloading and improved performance will be a strategic advantage, making it a critical skill for optimizing database resources.

Creating and Managing a Snapshot Standby Database

Snapshot standby databases offer a unique capability: they can be opened for read-write operations for testing, development, or reporting purposes, and then easily converted back into a physical standby database. This section covers the creation and management of such environments. This flexibility is invaluable for DevOps practices and rapid application development, allowing testing against production-like data without impacting the primary database.

Creating a Logical Standby Database

While physical standby databases offer identical data copies, logical standby databases allow for different data structures and can be used for different purposes, such as reporting or upgrades, while applying changes via SQL. This topic delves into the creation and specific use cases of logical standbys. Understanding both physical and logical standby options gives administrators a broader toolkit for various business requirements.

Oracle Data Guard Broker Basics

The Data Guard Broker simplifies the management and automation of Data Guard configurations. This section introduces the broker's architecture, components, and fundamental operations. The broker is indispensable for managing complex configurations, automating role transitions, and monitoring the entire Data Guard environment from a single interface. Future-proof administrators will heavily rely on such automation tools.

Creating a Data Guard Broker Configuration

Building on the basics, this topic focuses on the practical steps involved in creating a broker configuration. It covers adding databases to the broker, configuring properties, and enabling the configuration. This practical skill ensures that administrators can efficiently set up and manage Data Guard using the streamlined broker interface, reducing manual errors and operational overhead.

Monitoring a Data Guard Broker Configuration

Effective monitoring is crucial for maintaining the health and performance of a Data Guard environment. This section covers various monitoring tools and commands available through the Data Guard Broker (DGMGRL). Proactive monitoring helps identify potential issues before they impact availability, a skill that is increasingly automated but still requires expert interpretation.

Configuring Data Protection Modes

Data Guard offers different data protection modes (Maximum Protection, Maximum Availability, Maximum Performance) to balance data loss tolerance with performance overhead. This topic examines how to configure and manage these modes based on specific business requirements. Choosing the correct protection mode is a critical design decision for any high-availability solution.

Performing Role Transitions

Role transitions (switchovers and failovers) are the core operations of a Data Guard environment. This section details the procedures for gracefully transitioning roles during planned maintenance (switchover) and rapidly recovering from primary database failures (failover). Mastery of these procedures is directly tied to minimizing downtime and ensuring business continuity.

Using Flashback Database in a Data Guard Configuration

Flashback Database allows you to quickly revert a database to a previous point in time, which can be invaluable for recovery from logical corruptions or user errors. This topic explores how Flashback Database integrates with Data Guard, enhancing recovery capabilities and reducing recovery time objectives (RTO). The combination of Data Guard and Flashback Database provides a powerful safety net.

Enabling Fast-Start Failover

Fast-Start Failover (FSFO) is an automated failover mechanism managed by the Data Guard Broker, significantly reducing recovery time during primary database failures. This section covers the configuration and management of FSFO, including observers and failover conditions. Implementing FSFO is a key step towards achieving near-zero downtime and highly automated disaster recovery.

Backup and Recovery Considerations in an Oracle Data Guard Configuration

Integrating backup and recovery strategies with Data Guard is essential. This topic discusses how RMAN operations, such as backups and restores, interact with primary and standby databases, including performing backups on standby databases to offload the primary. Understanding these considerations is critical for a complete data protection strategy. Professionals can find comprehensive study resources to deepen their understanding of these complex topics.

Patching and Upgrading Databases in a Data Guard Configuration

Maintaining a Data Guard environment through patching and upgrades requires careful planning to minimize downtime. This section covers strategies for patching and upgrading both primary and standby databases, often leveraging rolling upgrades to maintain high availability. This skill is increasingly important as security patches and software updates become more frequent.

Optimizing and Tuning a Data Guard Configuration

Achieving optimal performance and efficiency in a Data Guard environment involves careful tuning of various parameters, including redo transport and apply services. This topic explores methods for monitoring performance, identifying bottlenecks, and implementing tuning strategies to ensure the Data Guard configuration meets desired recovery point objective (RPO) and RTO. For deep insights and best practices on performance, consider exploring the Oracle 1Z0-076 certification details.

Managing Physical Standby Files After Structural Changes on the Primary Database

When structural changes occur on the primary database (e.g., adding data files, tablespaces), these changes must be propagated and managed correctly on the physical standby. This section covers the procedures for managing these changes, ensuring that the standby remains synchronized and consistent with the primary. This is a common operational task that requires precision.

Using Oracle Active Data Guard: Far Sync and Real-Time Cascading

Advanced Active Data Guard features like Far Sync and Real-Time Cascading enhance disaster recovery capabilities by allowing for zero data loss over long distances and efficient distribution of redo data. Far Sync provides zero data loss over extreme distances, while real-time cascading distributes redo to multiple standbys. This topic covers the architecture and implementation of these advanced features, representing the cutting edge of Oracle Data Guard capabilities.

Enhanced Client Connectivity in a Data Guard Environment

Ensuring seamless client application connectivity during role transitions is vital. This section explores features like Fast Application Notification (FAN) and Transparent Application Failover (TAF), which allow applications to automatically reconnect to the new primary database after a failover or switchover. Implementing these features minimizes application downtime and provides a superior user experience.

The Strategic Value of 1Z0-076 in Tomorrow's IT

The 1Z0-076 certification is more than just a credential; it's an investment in a skillset that will remain highly relevant and in-demand. As businesses continue their digital transformation journeys and increasingly adopt hybrid and multi-cloud strategies, the complexity of managing and protecting data will only grow. Certified professionals will be at the forefront of designing and implementing resilient architectures that can withstand outages and ensure continuous operation.

Career Advancement and Industry Recognition

Holding the 1Z0-076 certification sets you apart as a specialist in Oracle Database 19c Data Guard. It signals to employers that you possess verified expertise in a critical area of database management – high availability and disaster recovery. This often translates into better job opportunities, higher salaries, and increased responsibilities within organizations. It's a testament to your commitment to mastering essential Oracle technologies.

Mitigating Business Risk

For organizations, having certified Data Guard administrators means mitigating significant business risks associated with data loss and downtime. These professionals can design robust disaster recovery plans, implement best practices, and quickly recover from incidents, protecting the organization's most valuable asset: its data. This strategic role is invaluable in today's competitive landscape.

Preparing for the 1Z0-076 Exam

Effective preparation is key to success. To pass the Oracle 1Z0-076 exam, a combination of theoretical knowledge and practical experience is highly recommended. Here are some strategies:

• Study Guide: Utilize an official 1Z0-076 study guide that aligns with the exam syllabus. Focus on understanding concepts rather than rote memorization.
• Oracle 1Z0-076 Practice Questions: Regularly test your knowledge with Oracle 1Z0-076 practice questions. This helps you become familiar with the exam format and identify areas needing further study.
• Oracle Database 19c Data Guard Training: Consider enrolling in official Oracle Database 19c Data Guard training. The Oracle Database 19c Data Guard Administration Workshop is an excellent resource for hands-on experience and expert instruction.
• Hands-on Experience: Set up your own Data Guard environment in a lab setting. Practice creating, managing, and performing role transitions. Practical application solidifies theoretical understanding.
• Review 1Z0-076 Exam Dumps (Cautiously): While some candidates may look for 1Z0-076 exam dumps, it's crucial to use them only as supplementary material for self-assessment, not as a primary study source. Relying solely on dumps can lead to a superficial understanding and failure in applying knowledge to real-world scenarios.
• Official Resources: Explore options for purchasing Oracle certification vouchers or consider the Oracle Unlimited Learning Subscription for comprehensive access to training and exams.

Focus on understanding Oracle 19c Data Guard concepts and architecture, including Oracle Data Guard physical standby database configuration and Oracle Active Data Guard features and benefits. Mastering Oracle Data Guard switchover and failover procedures, as well as Oracle Data Guard broker administration and commands (like managing Oracle Data Guard with DGMGRL), will be crucial. These skills are fundamental to building robust Oracle database high availability solutions and disaster recovery strategies using Oracle Data Guard.

Conclusion

The 1Z0-076 Certification for Oracle Database 19c - Data Guard Administration is a powerful credential that speaks volumes about a professional's expertise in one of the most critical aspects of modern database management: high availability and disaster recovery. As data volumes explode and the demand for always-on systems intensifies, the skills validated by this certification will only become more indispensable.

Earning this certification means you are equipped to design, implement, and maintain the robust data protection strategies that tomorrow's enterprises will demand. It signifies a forward-thinking approach to database administration, distinguishing you as a strategic asset capable of safeguarding business continuity. Invest in your future by pursuing this vital certification, and position yourself at the forefront of Oracle Database expertise.

For additional insights and exam preparation tips, feel free to visit our study blog for Oracle exams.

Frequently Asked Questions About 1Z0-076 Certification

1. What is the primary purpose of the Oracle 1Z0-076 Certification?

The primary purpose of the Oracle 1Z0-076 Certification is to validate a professional's expertise in implementing, configuring, and managing Oracle Data Guard for Oracle Database 19c. This ensures high availability, disaster recovery, and data protection for critical database environments.

2. How does 1Z0-076 Certification benefit my career?

Earning the 1Z0-076 Certification demonstrates specialized skills in a highly in-demand area of database administration. It enhances career opportunities, potentially leads to higher salaries, and provides industry recognition as an expert in Oracle Database 19c Data Guard administration, crucial for maintaining business continuity.

3. What are the key topics covered in the 1Z0-076 exam syllabus?

The 1Z0-076 exam syllabus covers a wide range of topics including Oracle Data Guard Basics, creating and managing physical and logical standby databases, using Oracle Active Data Guard and its advanced features like Far Sync, Data Guard Broker administration, role transitions (switchover/failover), Flashback Database integration, and backup/recovery considerations within a Data Guard setup.

4. Is hands-on experience necessary to pass the 1Z0-076 exam?

While theoretical knowledge from study guides and practice questions is important, hands-on experience is highly recommended. Practical application of Data Guard concepts, such as creating standby databases, configuring the broker, and performing role transitions, is crucial for truly understanding the material and successfully tackling scenario-based exam questions.

5. What is the cost of the Oracle 1Z0-076 certification exam?

The exam price for Oracle 1Z0-076 is USD $245. However, it's important to note that pricing may vary by country or by localized currency, so it's always best to check the official Oracle certification website for the most current information applicable to your region.

Continue reading

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

The DBMS_JOB package has been deprecated since 12cR2. Oracle 19c takes the demise of the DBMS_JOB package a step further by converting any DBMS_JOB jobs to DBMS_SCHEDULER jobs.

◉ Create a Job Using DBMS_JOB

In Oracle 19c jobs created using the DBMS_JOB package are implemented as DBMS_SCHEDULER jobs, as demonstrated below.

We can see from the output below we don't have any jobs for this user.

conn test/test@pdb1

column what format a30

select job, what from user_jobs;

0 rows selected.

SQL>

column job_name format a30

column job_action format a30

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

We create a job using the DBMS_JOB.SUBMIT procedure, but we are not going to issue a COMMIT statement.

declare

  l_job  pls_integer;

begin

  dbms_job.submit (

    job       => l_job,

    what      => 'begin null; end;',

    next_date => trunc(sysdate)+1,

    interval  => 'trunc(sysdate)+1'

  );

end;

/

We can see the job is listed in the USER_JOBS and USER_SCHEDULER_JOBS views.

select job, what from user_jobs;

       JOB      WHAT

---------- ------------------------------

         1        begin null; end;

1 row selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

------------------------------ ------------------------------

DBMS_JOB$_1                    begin null; end;

1 row selected.

SQL>

Notice the JOB_NAME of "DBMS_JOB$_?" for the DBMS_SCHEDULER job that has been generated.

◉ Transactional Jobs

One of the reasons people still use the DBMS_JOB package is it allows you to create jobs that are part of a bigger transaction. If a failure causes an exception, all the current work along with the jobs defined as part of it can be rolled back. We can demonstrate this using the job created above. Remember, we didn't issue a COMMIT, so the job is not visible from another session connected to the same user.

Without closing the original session, open a new connection and check for the jobs.

conn test/test@pdb1

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

Now return to the original session and the jobs are still visible.

select job, what from user_jobs;

       JOB     WHAT

---------- ------------------------------

         1      begin null; end;

1 row selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

------------------------------ ------------------------------

DBMS_JOB$_1                    begin null; end;

1 row selected.

SQL>

Issue a ROLLBACK, and the job definition will be removed.

rollback;

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

As a result, the DBMS_JOB package can still be used to create transactional jobs, that are implemented using the DBMS_SCHEDULER scheduler. This also provides backwards compatibility.

◉ Materialized View Refresh Groups

Up to and including Oracle 18c, materialized view refresh groups were implemented using the kernel APIs exposed by the old DBMS_JOB package. In Oracle 19c things look a little different.

Create a table, materialized and refresh group including that materialized view.

create table t1 (id number);

create materialized view t1_mv

refresh force

on demand

as

select * from t1;

begin

   dbms_refresh.make(

     name                 => 'MINUTE_REFRESH',

     list                 => '',

     next_date            => sysdate,

     interval             => '/*1:mins*/ sysdate + 1/(60*24)',

     implicit_destroy     => false,

     lax                  => false,

     job                  => 0,

     rollback_seg         => null,

     push_deferred_rpc    => true,

     refresh_after_errors => true,

     purge_option         => null,

     parallelism          => null,

     heap_size            => null);

end;

/

begin

   dbms_refresh.add(

     name => 'MINUTE_REFRESH',

     list => 'T1_MV',

     lax  => true);

end;

/

We don't see a job in the USER_JOBS view, but we do see one in the USER_SCHEDULER_JOBS view.

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

------------------------------ ------------------------------

MV_RF$J_0_S_210                dbms_refresh.refresh('"TEST"."

                               MINUTE_REFRESH"');

1 row selected.

SQL>

But this job is transactional, in that a ROLLBACK will remove the job, along with the refresh group definition.

rollback;

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

It would appear the refresh group functionality has been re-implemented using the kernel APIs that sit under the DBMS_SCHEDULER package, but without the implicit commit. Similar to the way the DBMS_JOB interface has been re-implemented. This is not 100% backwards compatible, as the associated job is not visible in the USER_JOBS view. If you have any functionality that relies on the link between the refresh groups and the old scheduler, it will need revisiting. I can't imagine that will be a problem for most people.

You can clean up the test table and materialized view using these commands.

drop materialized view t1_mv;

drop table t1 purge;

◉ Security : The CREATE JOB Privilege is Required?

At first glance the loophole discussed here sounds really bad, but remember that even in Oracle 18c, any user connected to the database could create a job using the DBMS_JOB interface, so this loophole is no worse than what came before. It just breaks the DBMS_SCHEDULER security.

As Connor McDonald pointed out, the conversion means users require the CREATE JOB privilege to allow them to create jobs using the DBMS_JOB package, where previously they didn't. We can see this if we create a user with just the CREATE SESSION privilege and attempt to create a job.

create user test2 identified by test2;

grant create session to test2;

conn test2/test2@pdb1

declare

  l_job  pls_integer;

begin

  dbms_job.submit (

    job       => l_job,

    what      => 'begin null; end;',

    next_date => trunc(sysdate)+1,

    interval  => 'trunc(sysdate)+1'

  );

end;

/

Error report -

ORA-27486: insufficient privileges

ORA-06512: at "SYS.DBMS_ISCHED", line 9387

ORA-06512: at "SYS.DBMS_ISCHED", line 9376

ORA-06512: at "SYS.DBMS_ISCHED", line 175

ORA-06512: at "SYS.DBMS_ISCHED", line 9302

ORA-06512: at "SYS.DBMS_IJOB", line 196

ORA-06512: at "SYS.DBMS_JOB", line 168

ORA-06512: at line 4

27486. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a scheduler operation without the

           required privileges.

*Action:   Ask a sufficiently privileged user to perform the requested

           operation, or grant the required privileges to the proper user(s).

SQL>

There is a loophole caused by the refresh group implementation. If we repeat the previous refresh group example, we can see we are able to create a job without the CREATE JOB privilege.

begin

   dbms_refresh.make(

     name                 => 'MINUTE_REFRESH',

     list                 => '',

     next_date            => sysdate,

     interval             => '/*1:mins*/ sysdate + 1/(60*24)',

     implicit_destroy     => false,

     lax                  => false,

     job                  => 0,

     rollback_seg         => null,

     push_deferred_rpc    => true,

     refresh_after_errors => true,

     purge_option         => null,

     parallelism          => null,

     heap_size            => null);

end;

/

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

------------------------------ ------------------------------

MV_RF$J_0_S_242                dbms_refresh.refresh('"TEST2".

                               "MINUTE_REFRESH"');

1 row selected.

SQL>

That in itself is not devastating because it's for a very specific purpose, but most of Oracle's security is based on you being able to do whatever you want with objects you already own, so what happens if we try to change the attributes?

begin

  dbms_scheduler.set_attribute (

    name      => 'MV_RF$J_0_S_242',

    attribute => 'job_action',

    value     => 'begin null; end;'

  );

end;

/

SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

------------------------------ ------------------------------

MV_RF$J_0_S_242                begin null; end;

1 row selected.

SQL>

So we can create a job using the DBMS_REFRESH package, then alter it to suit our purpose, giving us the ability to create a job without the need for the CREATE JOB privilege.

It would appear the re-implementation of the DBMS_REFRESH package has not followed the same security rules as that used by the other scheduler implementations. I'm sure this will get fixed in a future release.

Until this issue is resolved, you should probably revoke EXECUTE on the DBMS_REFRESH package from PUBLIC, as you may already do for the DBMS_JOB package.

Note. I raised this issue as "SR 3-20860955641 : Jobs can be created without the CREATE JOB privilege". This is now Bug 30357828 and is being worked on.

Miscellaneous

 

◉ The CREATE JOB privilege is necessary to create jobs using the DBMS_JOB package.

◉ During upgrades to 19c, any jobs defined using DBMS_JOB get converted to DBMS_SCHEDULER jobs. See Mike Dietrich's post about this.

◉ The SCHEDULER$_DBMSJOB_MAP dictionary table provides the mapping between the old DBA_JOBS job and the DBA_SCHEDULER job.

SQL> desc scheduler$_dbmsjob_map

Name            Null?    Type

--------------- -------- -------------

DBMS_JOB_NUMBER NOT NULL NUMBER

JOB_OWNER       NOT NULL VARCHAR2(128)

JOB_NAME        NOT NULL VARCHAR2(128)

SQL>

Source: oracle-base.com

Continue reading

Unlocking the Power of Oracle Databases: A Comprehensive Guide to 19c, 21c, and 23c

In today's digital landscape, data management lies at the heart of every successful organization. Enterprises worldwide rely on robust database management systems to store, retrieve, and manipulate vast amounts of data efficiently. Among the plethora of options available, Oracle Databases stand out as a cornerstone in the realm of relational databases.

Introduction to Oracle Databases

Oracle Corporation, a global leader in database technology, has been at the forefront of innovation for decades. With each iteration, Oracle databases evolve to meet the ever-changing demands of modern businesses. The latest versions, Oracle 19c, 21c, and the upcoming 23c, offer a myriad of features and enhancements, empowering organizations to unlock the full potential of their data infrastructure.

Oracle 19c: The Foundation of Stability and Performance

Oracle Database 19c serves as the bedrock of reliability and performance for enterprises worldwide. Built upon a foundation of stability, this version introduces numerous advancements aimed at optimizing database operations. From enhanced security features to improved automation capabilities, Oracle 19c sets the standard for efficient data management in the digital age.

Key Features of Oracle 19c

• Automatic Indexing: Streamline database performance with intelligent index management.
• Real Application Testing: Ensure seamless application upgrades and migrations with comprehensive testing tools.
• Database In-Memory: Accelerate query performance and analytics with in-memory processing capabilities.
• Multitenant Architecture: Maximize resource utilization and cost-efficiency through consolidated database deployment.

Embracing Innovation with Oracle 21c

As technology continues to evolve, so do the demands placed on database systems. Oracle Database 21c emerges as a beacon of innovation, delivering cutting-edge features designed to address the challenges of the digital era. From native blockchain tables to persistent memory support, Oracle 21c empowers organizations to stay ahead of the curve.

Revolutionary Features of Oracle 21c

• Native JSON Data Type: Simplify development and integration of JSON-based applications.
• Blockchain Tables: Ensure data immutability and integrity with blockchain-powered tables.
• Persistent Memory Support: Enhance performance and scalability with direct access to persistent memory devices.
• AutoML for In-Database Machine Learning: Democratize machine learning capabilities with automated model generation and deployment.

Looking Ahead: Oracle 23c and Beyond

As businesses embrace digital transformation, the need for agile and scalable database solutions becomes more apparent than ever. Oracle Database 23c, poised for release in the near future, promises to redefine the boundaries of data management once again. While specific details remain under wraps, industry analysts anticipate groundbreaking features that will further cement Oracle's position as a leader in the database landscape.

Anticipated Innovations in Oracle 23c

• Autonomous Operations: Leverage AI and machine learning to automate routine database tasks.
• Enhanced Security: Bolster data protection with advanced encryption and access control mechanisms.
• Serverless Architecture: Embrace cloud-native principles with a serverless database deployment model.
• Hybrid Cloud Integration: Seamlessly extend on-premises databases to the cloud for enhanced flexibility and scalability.

Conclusion

In conclusion, Oracle Databases represent the pinnacle of innovation and reliability in the world of data management. From the stalwart stability of Oracle 19c to the cutting-edge features of Oracle 21c and the promise of Oracle 23c, Oracle continues to empower organizations to harness the full potential of their data assets. By staying abreast of the latest advancements and embracing the power of Oracle technology, businesses can position themselves for success in an increasingly data-driven world.

Continue reading

JSON_SERIALIZE in Oracle Database 19c

The JSON_SERIALIZE function converts a JSON document from any supported data type to text.

The Problem

We can store JSON data in a number of different data types, including binary types. Let's create a test table to demonstrate the issue.

-- drop table json_documents purge;

create table json_documents (

  id    number,

  data  blob,

  constraint json_documents_is_json check (data is json)

);

insert into json_documents (id, data) values (1, '{"id":1,"first_name":"Iron","last_name":"Man"}');

commit;

If we try to display the data directly, we don't get anything useful.

select data from json_documents;

DATA

--------------------------------------------------------------------------------

7B226964223A312C2266697273745F6E616D65223A2249726F6E222C226C6173745F6E616D65223A

SQL>

We can manually convert a BLOB to a CLOB. For example, we can use the BLOB_TO_CLOB function created by the blob_to_clob.sql script, or for small amounts of data using the UTL_RAW package.

select blob_to_clob(data) as data from json_documents;

DATA

--------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

select utl_raw.cast_to_varchar2(data) as data from json_documents;

DATA

--------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

We could also use the JSON_QUERY function to return the whole document, rather than a fragment.

select json_query(data, '$') as data from json_documents;

DATA

----------------------------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

1 row selected.

SQL>

JSON_SERIALIZE Basic Usage

The documentation provides the following description of the JSON_SERIALIZE function.

JSON_SERIALIZE (target_expr [ json_query_returning_clause ] [ PRETTY ]

                [ ASCII ] [ TRUNCATE ] [ json_query_on_error_clause ])

The target expression is the JSON we want to convert.

In its basic form we can convert the JSON data from any supported data type to text, similar to what we did with the BLOB_TO_CLOB function.

select json_serialize(data) as data from json_documents;

DATA

--------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

We can use the JSON_SERIALIZE function to convert the output from other SQL/JSON calls. In this case we use the JSON_OBJECT function to produce a JSON document in binary form, then convert it text using the JSON_SERIALIZE function.

select json_serialize(

         json_object(empno, ename, hiredate returning blob)

       pretty) as data

from   emp

where  empno = 7369;

DATA

--------------------------------------------------------------------------------

{

  "empno" : 7369,

  "ename" : "SMITH",

  "hiredate" : "1980-12-17T00:00:00"

}

SQL>

Format Output

The returning clause works like that of the other SQL/JSON functions, as described here.

The PRETTY keyword displays the output in a human readable form, rather than minified.

select json_serialize(a.data pretty) as data

from   json_documents a

where  a.data.first_name = 'Iron';

DATA

--------------------------------------------------------------------------------

{

  "id" : 1,

  "first_name" : "Iron",

  "last_name" : "Man"

}

SQL>

The TRUNCATE keyword indicates the output should be truncated to fit the return type. In the following example the return type is VARCHAR2(10), so the output is truncated to fit.

select json_serialize(a.data returning varchar2(10) truncate) as data

from   json_documents a

where  a.data.first_name = 'Iron';

DATA

---------------------------------------------------------------------------------

{"id":1,"f

SQL>

Unlike some of the other SQL/JSON functions, the TRUNCATE function doesn't seem necessary, as it seems to truncate the output to match the returning clause.

select json_serialize(a.data returning varchar2(10)) as data

from   json_documents a

where  a.data.first_name = 'Iron';

DATA

---------------------------------------------------------------------------------

{"id":1,"f

SQL>

The ASCII keyword indicates the output should convert any non-ASCII characters to JSON escape sequences.

Error Handling

If there are any failures during the processing of the data the default response is to return a NULL value. The way an error is handled can be specified explicitly with the ON ERROR clause.

-- Default behaviour.

select json_serialize('This is not JSON!' null on error) as data

from   dual;

DATA

--------------------------------------------------------------------------------

SQL>

select json_serialize('This is not JSON!' error on error) as data

from   dual;

       *

ERROR at line 2:

ORA-40441: JSON syntax error

SQL>

PL/SQL Support

There is no support for JSON_SERIALIZE in direct PL/SQL assignments.

set serveroutput on

declare

  l_blob  blob;

  l_clob  clob;

begin

  l_blob := utl_raw.cast_to_raw('{"id":1,"first_name":"Iron","last_name":"Man"}');

  l_clob := JSON_SERIALIZE(l_blob);

  DBMS_OUTPUT.put_line('After : ' || l_clob);

END;

/

  l_clob := JSON_SERIALIZE(l_blob);

            *

ERROR at line 6:

ORA-06550: line 6, column 13:

PLS-00201: identifier 'JSON_SERIALIZE' must be declared

ORA-06550: line 6, column 3:

PL/SQL: Statement ignored

SQL>

The simple workaround for this is to make the assignment using a query from dual.

declare

  l_blob  blob;

  l_clob  clob;

begin

  l_blob := utl_raw.cast_to_raw('{"id":1,"first_name":"Iron","last_name":"Man"}');

  select json_serialize(l_blob)

  into   l_clob

  from   dual;

  dbms_output.put_line('After : ' || l_clob);

end;

/

After : {"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

21c Update : JSON Data Type Support

Oracle 21c introduced the JSON data type. The JSON_SERIALIZE function also support this new data type.

23c Update : ORDERED Keyword

In Oracle 23c the ORDERED keyword has been added to the JSON_SERIALIZE function. When present, the members are serialized in ascending alphabetical order by field name.

declare

  l_json  json;

  l_clob  clob;

begin

  l_json := json('{"id":1,"first_name":"Iron","last_name":"Man"}');

  select json_serialize(l_json ordered)

  into   l_clob;

  dbms_output.put_line('After : ' || l_clob);

end;

/

After : {"first_name":"Iron","id":1,"last_name":"Man"}

SQL>

Source: oracle-base.com

Continue reading

What Careers Require 1Z0-149 Certification?

If you are an Oracle Database Application Development certified professional, you have already gained a competitive edge over others. The next step is to pursue advanced certifications to enhance your value in the job market. One such certification is the Oracle Database Program with PL/SQL 1Z0-149 certification, which validates your skills in programming with PL/SQL in Oracle Database 19c.

The demand for Oracle Database professionals is increasing daily due to the widespread use of Oracle Database in various industries. The Oracle Database Program with PL/SQL certification is an advanced certification that focuses on developing skills in programming with PL/SQL in Oracle Database 19c. With this certification, you can demonstrate your expertise in Oracle Database Application Development, which employers highly value.

Benefits of 1Z0-149 Certification

The Oracle Database Program with PL/SQL certification offers several benefits to professionals, such as:

• Increased value in the job market
• Enhanced credibility and recognition
• Improved job performance and productivity
• Higher salary and career growth opportunities
• Access to Oracle's expert community

⇉ Also Read: What Is Oracle 1Z0-149 Certification and Why Should You Care? ⇇

Top Career Opportunities for 1Z0-149 Certified Professionals

• Database Developer: A database developer is responsible for designing, developing, and maintaining databases for organizations. With 1Z0-149 certification, you can demonstrate your expertise in programming with PL/SQL, which is essential for database development.
• Database Administrator: A database administrator ensures security, availability, and performance. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is necessary for database administration.
• Database Analyst: A database analyst analyzes and interprets data to provide insights and recommendations to organizations. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is necessary for database analysis.
• PL/SQL Developer: A PL/SQL developer is responsible for developing and maintaining PL/SQL code for applications. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is essential for PL/SQL development.
• Business Intelligence Analyst: A business intelligence analyst analyzes data to provide insights to help organizations make informed decisions. With 1Z0-149 certification, you can demonstrate your PL/SQL programming expertise necessary for business intelligence analysis.
• Data Warehouse Analyst: A data warehouse analyst is responsible for designing, developing, and maintaining data warehouses for organizations. With 1Z0-149 certification, you can demonstrate your PL/SQL programming expertise, which is essential for data warehouse development.
• IT Project Manager: An IT project manager manages projects from initiation to completion. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in managing IT projects that involve Oracle Database.
• Cloud Administrator: A cloud administrator manages cloud-based databases and applications. With 1Z0-149 certification, you can demonstrate your PL/SQL programming expertise, essential for managing Oracle Database in the cloud.
• Database Security Analyst: A database security analyst is responsible for ensuring the security of databases and the data they contain. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in implementing database security measures.
• Database Architect: A database architect is responsible for designing and maintaining databases that meet an organization's needs. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is essential for database architecture.

• Database Administrator Manager: A database administrator manager manages a team of administrators who maintain and optimize databases for an organization. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in managing a team of Oracle Database administrators.
• Data Analyst: A data analyst collects, analyzes, and interprets data to provide insights to help organizations make informed decisions. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which is necessary for data analysis in Oracle Database.
• Database Performance Analyst: A database performance analyst is responsible for identifying and resolving database performance issues. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in optimizing the performance of Oracle Database.
• Database Developer Manager: A database developer manager manages a team of developers who create and maintain databases for an organization. With 1Z0-149 certification, you can demonstrate your expertise in PL/SQL programming, which can be valuable in managing a team of Oracle Database developers.

These are just a few career opportunities available to 1Z0-149 certified professionals. With this certification, you can gain the skills and knowledge necessary to pursue a rewarding Oracle Database development and administration career.

Conclusion

The Oracle Database Program with PL/SQL certification is a valuable credential that can open up numerous professional career opportunities. The certification validates your skills in programming with PL/SQL in Oracle Database 19c, which employers highly value.

Continue reading

DBMS_CLOUD : Installation on 19c and 21c On-Prem Databases

This article describes how to install the DBMS_CLOUD package in on-prem 19c and 21c databases. This package is already installed in databases on the Oracle Cloud.

◉ Reference

This article is a run through of the installation instructions in this MOS note.

- How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

You should always refer to this note, as the instructions may change over time.

◉ Install DBMS_CLOUD

Create a directory to hold the installation files and an SSL wallet.

mkdir -p /home/oracle/dbc/commonstore/wallets/ssl

Create a file called "/home/oracle/dbc/dbms_cloud_install.sql" with the following contents.

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

set verify off

-- you must not change the owner of the functionality to avoid future issues

define username='C##CLOUD$SERVICE'

create user &username no authentication account lock;

REM Grant Common User Privileges

grant INHERIT PRIVILEGES on user &username to sys;

grant INHERIT PRIVILEGES on user sys to &username;

grant RESOURCE, UNLIMITED TABLESPACE, SELECT_CATALOG_ROLE to &username;

grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE,

CREATE ANY CREDENTIAL, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, ALTER SESSION, CREATE JOB to &username;

grant CREATE SESSION, SET CONTAINER to &username;

grant SELECT on SYS.V_$MYSTAT to &username;

grant SELECT on SYS.SERVICE$ to &username;

grant SELECT on SYS.V_$ENCRYPTION_WALLET to &username;

grant read, write on directory DATA_PUMP_DIR to &username;

grant EXECUTE on SYS.DBMS_PRIV_CAPTURE to &username;

grant EXECUTE on SYS.DBMS_PDB_LIB to &username;

grant EXECUTE on SYS.DBMS_CRYPTO to &username;

grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;

grant EXECUTE ON SYS.DBMS_ISCHED to &username;

grant EXECUTE ON SYS.DBMS_PDB_LIB to &username;

grant EXECUTE on SYS.DBMS_PDB to &username;

grant EXECUTE on SYS.DBMS_SERVICE to &username;

grant EXECUTE on SYS.DBMS_PDB to &username;

grant EXECUTE on SYS.CONFIGURE_DV to &username;

grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;

grant EXECUTE on SYS.DBMS_CREDENTIAL to &username;

grant EXECUTE on SYS.DBMS_RANDOM to &username;

grant EXECUTE on SYS.DBMS_SYS_SQL to &username;

grant EXECUTE on SYS.DBMS_LOCK to &username;

grant EXECUTE on SYS.DBMS_AQADM to &username;

grant EXECUTE on SYS.DBMS_AQ to &username;

grant EXECUTE on SYS.DBMS_SYSTEM to &username;

grant EXECUTE on SYS.SCHED$_LOG_ON_ERRORS_CLASS to &username;

grant SELECT on SYS.DBA_DATA_FILES to &username;

grant SELECT on SYS.DBA_EXTENTS to &username;

grant SELECT on SYS.DBA_CREDENTIALS to &username;

grant SELECT on SYS.AUDIT_UNIFIED_ENABLED_POLICIES to &username;

grant SELECT on SYS.DBA_ROLES to &username;

grant SELECT on SYS.V_$ENCRYPTION_KEYS to &username;

grant SELECT on SYS.DBA_DIRECTORIES to &username;

grant SELECT on SYS.DBA_USERS to &username;

grant SELECT on SYS.DBA_OBJECTS to &username;

grant SELECT on SYS.V_$PDBS to &username;

grant SELECT on SYS.V_$SESSION to &username;

grant SELECT on SYS.GV_$SESSION to &username;

grant SELECT on SYS.DBA_REGISTRY to &username;

grant SELECT on SYS.DBA_DV_STATUS to &username;

alter session set current_schema=&username;

REM Create the Catalog objects

@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql

@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql

@$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql

@$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql

REM Create the Package Spec

@$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb

@$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql

@$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb

@$ORACLE_HOME/rdbms/admin/dbms_cloud.sql

@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb

REM Create the Package Body

@$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb

-- Create the metadata

@$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql

alter session set current_schema=sys;

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Run the script in all containers including this seed. This will make new containers already include the DBMS_CLOUD package installation.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

  -u sys/SysPassword1 \

  --force_pdb_mode 'READ WRITE' \

  -b dbms_cloud_install \

  -d /home/oracle/dbc \

  -l /home/oracle/dbc \

  dbms_cloud_install.sql

Check the log files once the command is complete, to make sure it has run in all containers.

◉ Create a Wallet

Create a wallet to allow HTTPS access to the cloud URIs.

Download the dbc_certs.tar file from the link provided in the MOS note. These instructions assume it is placed on the "/tmp" directory.

mkdir -p /home/oracle/dbc/commonstore/wallets/ssl

cd /home/oracle/dbc/commonstore/wallets/ssl

tar -xvf /tmp/dbc_certs.tar

Create a wallet and load the certificates. We are using the wallet password "MyPassword1", but you should change it to something more secure for your installation.

orapki wallet create -wallet . -pwd MyPassword1 -auto_login

orapki wallet add -wallet . -trusted_cert -cert ./VeriSign.cer -pwd MyPassword1

orapki wallet add -wallet . -trusted_cert -cert ./BaltimoreCyberTrust.cer -pwd MyPassword1

orapki wallet add -wallet . -trusted_cert -cert ./DigiCert.cer -pwd MyPassword1

Edit the "sqlnet.ora" file, adding in the following entry to identify the wallet. For read-only Oracle homes, this will be in the "/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora" file. For regular Oracle homes it will be in the "$ORACLE_HOME/network/admin/sqlnet.ora" location.

WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA=

  (DIRECTORY=/home/oracle/dbc/commonstore/wallets/ssl)))

◉ Create Access Control Entries (ACEs)

We need to create an Access Control Entry (ACE) so the C##CLOUD$SERVICE can access the cloud services.

Create a file called "/home/oracle/dbc/dbc_aces.sql" with the following contents. Edit the sslwalletdir setting if you have altered the location.

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

-- you must not change the owner of the functionality to avoid future issues

define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER

-- - SSL Wallet directory

define sslwalletdir=/home/oracle/dbc/commonstore/wallets/ssl

--

-- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS

--

-- define proxy_uri=<your proxy URI address>

-- define proxy_host=<your proxy DNS name>

-- define proxy_low_port=<your_proxy_low_port>

-- define proxy_high_port=<your_proxy_high_port>

-- Create New ACL / ACE s

begin

-- Allow all hosts for HTTP/HTTP_PROXY

dbms_network_acl_admin.append_host_ace(

host =>'*',

lower_port => 443,

upper_port => 443,

ace => xs$ace_type(

privilege_list => xs$name_list('http', 'http_proxy'),

principal_name => upper('&clouduser'),

principal_type => xs_acl.ptype_db));

--

-- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS

--

-- Allow Proxy for HTTP/HTTP_PROXY

-- dbms_network_acl_admin.append_host_ace(

-- host =>'&proxy_host',

-- lower_port => &proxy_low_port,

-- upper_port => &proxy_high_port,

-- ace => xs$ace_type(

-- privilege_list => xs$name_list('http', 'http_proxy'),

-- principal_name => upper('&clouduser'),

-- principal_type => xs_acl.ptype_db));

--

-- END PROXY SECTION

--

-- Allow wallet access

dbms_network_acl_admin.append_wallet_ace(

wallet_path => 'file:&sslwalletdir',

ace => xs$ace_type(privilege_list =>

xs$name_list('use_client_certificates', 'use_passwords'),

principal_name => upper('&clouduser'),

principal_type => xs_acl.ptype_db));

end;

/

-- Setting SSL_WALLET database property

begin

-- comment out the IF block when installed in non-CDB environments

if sys_context('userenv', 'con_name') = 'CDB$ROOT' then

execute immediate 'alter database property set ssl_wallet=''&sslwalletdir''';

--

-- UNCOMMENT THE FOLLOWING COMMAND IF YOU ARE USING A PROXY

--

-- execute immediate 'alter database property set http_proxy=''&proxy_uri''';

end if;

end;

/

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Run script in the root container.

conn / as sysdba

@@/home/oracle/dbc/dbc_aces.sql

◉ Verify the Installation

Create a file called "/home/oracle/dbc/verify.sql" with the following contents. Edit the wallet path and password as required.

-- you must not change the owner of the functionality to avoid future issues

define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER

-- - SSL Wallet directory and password

define sslwalletdir=/home/oracle/dbc/commonstore/wallets/ssl

define sslwalletpwd=MyPassword1

-- create and run this procedure as owner of the ACLs, which is the future owner

-- of DBMS_CLOUD

CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS

request_context UTL_HTTP.REQUEST_CONTEXT_KEY;

req UTL_HTTP.REQ;

resp UTL_HTTP.RESP;

data VARCHAR2(32767) default null;

err_num NUMBER default 0;

err_msg VARCHAR2(4000) default null;

BEGIN

-- Create a request context with its wallet and cookie table

request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(

wallet_path => 'file:&sslwalletdir',

wallet_password => '&sslwalletpwd');

-- Make a HTTP request using the private wallet and cookie

-- table in the request context

req := UTL_HTTP.BEGIN_REQUEST(

url => url,

request_context => request_context);

resp := UTL_HTTP.GET_RESPONSE(req);

DBMS_OUTPUT.PUT_LINE('valid response');

EXCEPTION

WHEN OTHERS THEN

err_num := SQLCODE;

err_msg := SUBSTR(SQLERRM, 1, 3800);

DBMS_OUTPUT.PUT_LINE('possibly raised PLSQL/SQL error: ' ||err_num||' - '||err_msg);

UTL_HTTP.END_RESPONSE(resp);

data := UTL_HTTP.GET_DETAILED_SQLERRM ;

IF data IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE('possibly raised HTML error: ' ||data);

END IF;

END;

/

set serveroutput on

BEGIN

&clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');

END;

/

set serveroutput off

drop procedure &clouduser..GET_PAGE;

Run the script. The script should produce the phrase "valid response".

conn / as sysdba

@/home/oracle/dbc/verify.sql

◉ List the Contents of a Bucket

This section of the article assumes you have an object storage bucket on Oracle Cloud and you've defined an Auth Token to access it. You can read how to create a bucket and an Auth Token in this article.

- Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket

Create a test user.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;

create user testuser1 identified by testuser1 quota unlimited on users;

grant connect, resource to testuser1;

Make sure the test user can create credentials and has access to the DBMS_CLOUD package.

grant create credential to testuser1;

grant execute on dbms_cloud to testuser1;

Connect to the test user and create a credential.

conn testuser1/testuser1@//localhost:1521/pdb1

begin

  dbms_credential.drop_credential(

    credential_name => 'obj_store_cred');

end;

/

begin

  dbms_credential.create_credential(

    credential_name => 'obj_store_cred',

    username        => 'me@example.com',

    password        => 'my-auth-token');

end;

/

We can now use the LIST_OBJECTS table function to get a list of objects in the bucket.

select object_name

from   dbms_cloud.list_objects(

         'obj_store_cred',

         'https://objectstorage.uk-london-1.oraclecloud.com/n/{my-namespace}/b/ob-bucket/o/');

OBJECT_NAME

--------------------------------------------------------------------------------

Image 930.png

SQL>

Source: oracle-base.com

Continue reading

Oracle Database 19c Installation On Fedora 36 (F36)

This article describes the installation of Oracle Database 19c 64-bit on Fedora 36 (F36) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive.

◉ Download Software

Download the Oracle software from OTN or MOS depending on your support status.

- OTN: Oracle Database 19c (19.3) Software (64-bit).

- edelivery: Oracle Database 19c (19.3) Software (64-bit)

◉ Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

<IP-address>  <fully-qualified-machine-name>  <machine-name>

For example.

127.0.0.1       localhost localhost.localdomain localhost4 localhost4.localdomain4

192.168.56.141  fedora36.localdomain  fedora36

◉ Set Kernel Parameters

Add the following lines to the "/etc/sysctl.conf" file, or in a file called "/etc/sysctl.d/98-oracle.conf".

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

Run one of the following commands to change the current kernel parameters, depending on which file you edited.

/sbin/sysctl -p

# Or

/sbin/sysctl -p /etc/sysctl.d/98-oracle.conf

Add the following lines to a file called "/etc/security/limits.d/oracle-database-server-19c-preinstall.conf" file.

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

Stop and disable the firewall. You can configure it later if you wish.

# systemctl stop firewalld

# systemctl disable firewalld

Set SELinux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

The server will need a reboot for the change to take effect.

◉ Setup

The following packages are listed as required. Some are commented out as they are not present in the Fedora repository.

#dnf groupinstall "GNOME Desktop" -y

#dnf groupinstall "Development Tools" -y

#dnf groupinstall "Administration Tools" -y

#dnf groupinstall "System Tools" -y

dnf install -y bc    

dnf install -y binutils

#dnf install -y compat-libcap1

dnf install -y compat-libstdc++-33

#dnf install -y dtrace-modules

#dnf install -y dtrace-modules-headers

#dnf install -y dtrace-modules-provider-headers

#dnf install -y dtrace-utils

dnf install -y elfutils-libelf

dnf install -y elfutils-libelf-devel

dnf install -y fontconfig-devel

dnf install -y glibc

dnf install -y glibc-devel

dnf install -y ksh

dnf install -y libaio

dnf install -y libaio-devel

#dnf install -y libdtrace-ctf-devel

dnf install -y libXrender

dnf install -y libXrender-devel

dnf install -y libX11

dnf install -y libXau

dnf install -y libXi

dnf install -y libXtst

dnf install -y libgcc

dnf install -y librdmacm-devel

dnf install -y libstdc++

dnf install -y libstdc++-devel

dnf install -y libxcb

dnf install -y make

dnf install -y net-tools # Clusterware

dnf install -y nfs-utils # ACFS

dnf install -y python # ACFS

dnf install -y python-configshell # ACFS

dnf install -y python-rtslib # ACFS

dnf install -y python-six # ACFS

dnf install -y targetcli # ACFS

dnf install -y smartmontools

dnf install -y sysstat

# Added by me.

yum install -y unixODBC

# compat-libpthread-nonshared.

dnf install -y libnsl2

dnf install -y libnsl2.i686

dnf install -y libxcrypt-compat

dnf install -y http://rpmfind.net/linux/fedora/linux/development/rawhide/Everything/x86_64/os/Packages/c/compat-libpthread-nonshared-2.35.9000-17.fc37.x86_64.rpm

#dnf update -y

Create the new groups and users.

groupadd -g 54321 oinstall

groupadd -g 54322 dba

groupadd -g 54323 oper

#groupadd -g 54324 backupdba

#groupadd -g 54325 dgdba

#groupadd -g 54326 kmdba

#groupadd -g 54328 asmdba

#groupadd -g 54328 asmoper

#groupadd -g 54329 asmadmin

useradd -u 54321 -g oinstall -G dba,oper oracle

passwd oracle

We are not going to use the extra groups, but include them if you do plan on using them.

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

mkdir -p /u02/oradata

chown -R oracle:oinstall /u01 /u02

chmod -R 775 /u01 /u02

Putting mount points directly under root without mounting separate disks to them is typically a bad idea. It's done here for simplicity, but for a real installation "/" storage should be reserved for the OS.

If you are using X Emulation, login as root and issue the following command.

xhost +<machine-name>

You will need to add the following symbolic links or the Oracle Universal Installer (OUI) will not start.

# Fix for Oracle on Fedora.

rm -f /usr/lib64/libnsl.so.1

rm -f /usr/lib/libnsl.so.1

ln -s /usr/lib64/libnsl.so.3.0.0 /usr/lib64/libnsl.so.1

ln -s /usr/lib/libnsl.so.3.0.0 /usr/lib/libnsl.so.1

Set up the environment for the "oracle" user. The "$" characters are escaped using "\". If you are not creating the file with the cat command, you will need to remove the escape characters.

mkdir -p /home/oracle/scripts

cat > /home/oracle/scripts/setEnv.sh <<EOF

# Oracle Settings

export TMP=/tmp

export TMPDIR=\$TMP

export ORACLE_HOSTNAME=fedora36.localdomain

export ORACLE_UNQNAME=cdb1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=\$ORACLE_BASE/product/19.0.0/dbhome_1

export ORA_INVENTORY=/u01/app/oraInvenotry

export ORACLE_SID=cdb1

export PDB_NAME=pdb1

export DATA_DIR=/u02/oradata

export PATH=/usr/sbin:/usr/local/bin:\$PATH

export PATH=\$ORACLE_HOME/bin:\$PATH

export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib

EOF

echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile

chown -R oracle:oinstall /home/oracle/scripts

◉ Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=<machine-name>:0.0; export DISPLAY

Perform a software-only installation either using interactive mode (GUI) or silent mode and run the root scripts when prompted. Notice the setting of the CV_ASSUME_DISTID environment variable, so fake the OS.

# Unzip software.

cd $ORACLE_HOME

unzip -oq /path/to/software/LINUX.X64_193000_db_home.zip

# Fix for linking error suggested by Steven Kennedy.

cd $ORACLE_HOME/lib/stubs

mv libc.so libc.so.hide

mv libc.so.6 libc.so.6.hide

# Fake OS.

export CV_ASSUME_DISTID=OEL7.8

# Interactive mode.

#./runInstaller

# Silent mode.

./runInstaller -ignorePrereq -waitforcompletion -silent                        \

    -responseFile ${ORACLE_HOME}/install/response/db_install.rsp               \

    oracle.install.option=INSTALL_DB_SWONLY                                    \

    ORACLE_HOSTNAME=${ORACLE_HOSTNAME}                                         \

    UNIX_GROUP_NAME=oinstall                                                   \

    INVENTORY_LOCATION=${ORA_INVENTORY}                                        \

    SELECTED_LANGUAGES=en,en_GB                                                \

    ORACLE_HOME=${ORACLE_HOME}                                                 \

    ORACLE_BASE=${ORACLE_BASE}                                                 \

    oracle.install.db.InstallEdition=EE                                        \

    oracle.install.db.OSDBA_GROUP=dba                                          \

    oracle.install.db.OSBACKUPDBA_GROUP=dba                                    \

    oracle.install.db.OSDGDBA_GROUP=dba                                        \

    oracle.install.db.OSKMDBA_GROUP=dba                                        \

    oracle.install.db.OSRACDBA_GROUP=dba                                       \

    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false                                 \

    DECLINE_SECURITY_UPDATES=true

Run the root scripts when prompted.

As a root user, execute the following script(s):

        1. /u01/app/oraInvenotry/orainstRoot.sh

        2. /u01/app/oracle/product/19.0.0/dbhome_1/root.sh

You are now ready to create a database.

◉ Database Creation

You create a database using the Database Configuration Assistant (DBCA). The interactive mode will display GUI screens to allow user input, while the silent mode will create the database without displaying any screens, as all required options are already specified on the command line.

# Start the listener.

lsnrctl start

# Interactive mode.

# dbca

# Silent mode.

dbca -silent -createDatabase                                                   \

     -templateName General_Purpose.dbc                                         \

     -gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} -responseFile NO_VALUE         \

     -characterSet AL32UTF8                                                    \

     -sysPassword SysPassword1                                                 \

     -systemPassword SysPassword1                                              \

     -createAsContainerDatabase true                                           \

     -numberOfPDBs 1                                                           \

     -pdbName ${PDB_NAME}                                                      \

     -pdbAdminPassword PdbPassword1                                            \

     -databaseType MULTIPURPOSE                                                \

     -memoryMgmtType auto_sga                                                  \

     -totalMemory 2000                                                         \

     -storageType FS                                                           \

     -datafileDestination "${DATA_DIR}"                                        \

     -redoLogFileSize 50                                                       \

     -emConfiguration NONE                                                     \

     -ignorePreReqs

◉ Post Installation

Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

cdb1:/u01/app/oracle/product/19.0.0/dbhome_1:Y

Source: oracle-base.com

Continue reading