Disaster Recovery Solution for Oracle Analytics Server on Oracle Cloud Marketplace using Snapshot Replication

High Level Steps involved in the Disaster Recovery Setup

1. Create Primary Oracle Analytics Server Instance in one Region e.g. Ashburn

2. Create Disaster Recovery Oracle Analytics Server Instance in another Region e.g. Phoenix

3. Configure both Oracle Analytics Server Instances to use same external connections like SMTP Server, Data Sources and Database Tables that involved in the Security Configuration like BISQLGroupProvider, Act as Configuration, Data Level Security, etc.

4. Configure both the Oracle Analytics Server Instances share the same Security Rules to connect to same on-Premise Data Sources or Cloud Data Sources.

5. Both Oracle Analytics Server Instances are running independent of each other which means both doesn't share the RCU Database Schemas to sync the connections, Catalog Objects, etc. (Answers Reports/DV Projects).

6. Same Set of Users and Groups can be accessed with in the Oracle Analytics Cloud Service Console, Users and Role Section as both of the Services run on same Oracle Identity Cloud Service (IDCS) for Identity Management.

7. Snapshot Create and Restore is the method available to synchronize the content between the two Oracle Analytics Server Instances.

8. Creation of Snapshots and Restore can be automated using WLST Scripting or else using REST APIs in future releases.

9. Snapshot Create on Primary Oracle Analytics Server Instance and Restore on Disaster Recovery Oracle Analytics Server Instance will not sync Data File content and BI Publisher JDBC Connections, may need to manually move across Instances.

10. At this moment we don’t have automatic Snapshot Create and Snapshot Restore mechanism. We need to perform this task manually and periodically.

11. Due to the periodic manual tasks involved in restoring the Content from Primary Oracle Analytics Server Instance to Disaster Recovery Oracle Analytics Server Instance, Live Content Sync is not possible between the Instances. We might need to live with delayed content sync.

12. Create a Load Balancer in each Region and decide on a single DNS name e.g oas.oracleceal.com for both the Load Balancers.

13. Get the SSL/TLS Certificate for the DNS Name and implement the same SSL Certificate to both the Load Balancers in each Region.

14. Use Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover) and configure Ashburn Load Balancer as the Primary and Phoenix Load Balancer as the Disaster Recovery Load Balancer.

15. Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover) maps the Primary Load Balancer IP Address to the DNS Name using “A” Record in Oracle Cloud Infrastructure Zones.

16. When the Primary Load Balancer or Oracle Analytics Server is not reachable based on the Health Check Policy defined, the Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover) will remove the Primary Load Balancer IP Address from the DNS Mapping in “A” Record and maps the Disaster Recovery Load Balancer IP Address to the DNS Name in the “A” Record of the Oracle Cloud Infrastructure Zones.

17. By this configuration we can create Disaster Recovery and make sure the end user always use the same URL.

18. When the Primary Load Balancer or Oracle Analytics Server is reachable based on the Health Check Policy defined, the Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover) will Fall back to Primary Load Balancer i.e. remove the Secondary Load Balancer IP Address from the DNS Mapping in “A” Record and maps the Primary Load Balancer IP Address to the DNS Name in the “A” Record of the Oracle Cloud Infrastructure Zones.

19. Before the Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover) Fallback to the Primary we need to take the latest snapshot of Secondary Oracle Analytics Server and restore it on Primary Oracle Analytics Server.

Oracle Analytics Server Disaster Recovery Architecture and Configuration across Different Oracle Cloud Infrastructure (OCI) Regions

Oracle Analytics Server Instance configured with Load Balancer in the Front End.

In this Blog, we have created Oracle Analytics Server Instance in a Private Subnet and Load Balancer in a Public Subnet of the VCN.

Demonstrating Disaster Recovery Architecture using Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover)

You can also create Oracle Analytics Server Instance and Load Balancer in the same Public Subnet of the VCN.

Oracle Cloud Infrastructure (OCI) Traffic Management Steering Policy

For Disaster Recovery we use Failover Policy Type

◉ Failover:  Use Oracle Cloud Infrastructure Health Checks to determine the health of answers. If the primary answer is determined to be unhealthy, DNS traffic is automatically steered to the secondary answer.

Subscribe for Secondary Oracle Cloud Infrastructure (OCI) Region (Phoenix)

We have two regions Ashburn and Phoenix subscribed for the Oracle CloudInfrastructure Tenancy

On the Oracle Cloud Infrastructure (OCI) Console of Home Region (Ashburn)

• Create Component
• Create a Virtual Cloud Network (VCN)
• Access Control
• Route Rules
• Create Oracle Analytics Server Instance in the Private Subnet of the VCN
• Generate SSL Certificate for Load Balancer for the desired DNS Name
• Create a Load Balancer with Reserved Public IP Address on the Public Subnet of the VCN
• Configure the Load Balancer to the backend Oracle Analytics Server
• Get a Domain from Domain Providers like GoDaddy based on the DNS Name
• Create Public Zone
• Create Oracle Cloud Infrastructure Traffic Management Steering Policy

On the Oracle Cloud Infrastructure Console Secondary Region (Phoenix)

• Use Existing Compartment created in Home Region Ashburn
• Create a Virtual Cloud Network (VCN)
• Access Control
• Route Rules
• Create Oracle Analytics Server Instance in the Private Subnet of the VCN
• Use the already generated same SSL Certificate for Load Balancer
• Create a Load Balancer with Reserved Public IP Address on the Public Subnet of the VCN
• Configure the Load Balancer to the backend Oracle Analytics Server
• Use Existing Public Zone created in Home Region, no steps required here
• Use Existing Oracle Cloud Infrastructre Traffic Management Steering Policy, no steps required here

Let us check that we have an Oracle Analytics Server Front End with Load Balancer and the Load Balancer configured with same SSL Certificate and Hostname as the DNS Name e.g. oas.oracleceal.com in both the Oracle Cloud Infrastructure Regions i.e Ashburn and Phoenix.

Oracle Analytics Server Compute Instance

Ashburn

Phoenix

Load Balancer

Ashburn

Phoenix

Load Balancer Backend Set Health Check

How to find the Status Code

From any Bastion Server or Windows Server (on Public Subnet) that can access the Oracle Analytics Server (on Private Subnet), run below command

curl -k -vvv http://<Oracle Analytics Server Private IP Address>:<Port No>/dv

e.g.  curl -k -vvv http://10.0.1.253:9502/dv  

Two Load Balancers URL’s for Oracle Analytics Server (Primary and Disaster Recovery)

Ashburn:   https://129.213.208.51/dv

Phoenix:    https://129.153.95.118/dv

Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover)

To work with the Oracle Cloud Infrastructure Traffic Management Steering Policies, you need delegated Zone in Oracle Cloud Infrastructure DNS. Oracle is not a registrar, so you need a domain, get a domain if you don’t have one.

Get a Domain e.g. oracleceal.com from Domain Provider like GoDaddy

GoDaddy uses its own NameServers

Can create an “A Record” at GoDaddy DNS Management mapping the Load Balancer Reserved IP address to the required Sub Domain e.g. oas.oracleceal.com, but we need to delegate the Domain to Oracle Cloud Zone.

To delegate the Domain, we should create a DNS Zone in Oracle Cloud Infrastructure for that Domain and use the Oracle Cloud Infrastructure NameServers at the Domain Provider e.g. GoDaddy

Create Public Zone on the Oracle Cloud Infrastructure (OCI) Home Region (e.g. Ashburn)

In Oracle Cloud Infrastructure Console Navigate to Networking à DNS Management à Zones à Create Public Zone

Obtain the Public Zone Name Server hostnames

Add these DNS Server Oracle NameServers to your Domain provider like GoDaddy.

Login to Domain Provider portal change the Name Servers based on the DNS Zone created in Oracle Cloud Infrastructure.

Create Oracle Cloud Infrastructure Traffic Management Steering Policy (Failover)

Login to Oracle Cloud Infrastructure Console as an Administrator, Select Home Region e.g. Ashburn

Navigate to Networking à Under DNS Management select Traffic Management Steering Policies

Select the Compartment as “oasmp”.

Click on Create Traffic Management Steering Policy

Select the Policy Type as Failover

Create Answer Pools (Pool 1 - Ashburn, Pool 2 - Phoenix)

Create Pool Priority (Which Pool should be the Primary Instance and which one to be the Disaster Recovery Instance based on Health Check Fail of the Primary Instance)

Create a Health Check

Click on Show Advanced Options

Select the Compartment where Oracle Cloud Infrastructure Public Zone is created. e.g. oacdr

Test the access to Primary Oracle Analytics Server Instance.

https://oas.oracleceal.com/dv

Test the Oracle Cloud Infrastructure Traffic Management Steering Failover from Primary to Disaster Recovery Load Balancer, by stopping services at Primary Oracle Analytics Server Instance.

https://oas.oracleceal.com/dv

LIMITATION: Since the content is replicated using Snapshots and not by using common Database RCU Schemas, we suggest to take snapshot from DR Instance i.e. secondary Oracle Analytics Server Instance after a failover from Primary to Secondary (i.e. DR Oracle Analytics Server) and restore it to Primary Oracle Analytics Server Instance before allowing Users to access the Primary Oracle Analytics Server Instance through the same URL.

Migrate Metadata and Content between Primary and Disaster Recovery Oracle Analytics Server Instances

Check this Section in Oracle Analytics Server Documentation, Move Oracle Analytics Server Between Environments

Migrating Content using Snapshots

A snapshot captures the state of your environment at a point in time

Exporting Snapshot in Primary Oracle Analytics Server Instance using script:

Run the exportarchive command to create a BAR file:

$DOMAIN_HOME/bitools/bin/exportarchive.sh <service instance key> <export directory> encryptionpassword=<password>

Example: ./exportarchive.sh ssi /tmp encryptionpassword=Admin123

Result:

Importing Snapshot in Disaster Recovery Oracle Analytics Server Instance using script:

Copy the snapshot created in Primary Oracle Analytics Server Instance to Disaster Recovery Oracle Analytics Server Instance.

Run the exportarchive command to create a BAR file:

$DOMAIN_HOME/bitools/bin/importarchive.sh <service instance key> <location of BAR file> encryptionpassword=<password>

Example: ./importarchive.sh ssi /tmp/ssi.bar encryptionpassword=Admin123

Result:

Snapshot Exclusions:

There are a few items that aren't included in a snapshot

• System settings - Any properties that you configured on the System Settings page.
• Snapshot list - The list of snapshots that you see on the Snapshot page.
• BI Publisher JDBC connections.
• Don’t include data that's hosted on external data sources

Migrating File-based Data

Snapshots don’t include the file-based data which are used for creating data sets in Oracle Analytics Server.

To migrate file-based data, you must export your data files to an archive file in the Primary Oracle Analytics Server Instance and import the archive file into the Disaster Recovery Oracle Analytics Server Instance.

Export Data files from Primary Oracle Analytics Server Instance

• Navigate to ORACLE_HOME/bi/modules/oracle.bi.publicscripts
• Copy migrate_datafiles.py to migrate_datafiles_export.py
• Update migrate_datafiles_export.py as follows

domain_home = topology.get_domain_home()

oracle_home = topology.get_oracle_home()

wlst_path = topology.get_wlst_path()

internal_scripts_path = os.path.join(topology.get_publicscripts_path(), 'internal')

To

domain_home = '[PATH_DOMAIN_HOME]'

oracle_home = '[PATH_ORACLE_HOME]'

wlst_path = '[PATH_WLST.SH]'

internal_scripts_path = '[PATH_INTERNAL_SCRIPTS]'

Example:

domain_home = "/u01/fmw/user_projects/domains/bi"

oracle_home = "/u01/fmw/bi"

wlst_path = "/u01/fmw/oracle_common/common/bin/wlst.sh"

internal_scripts_path = "/u01/fmw/bi/modules/oracle.bi.publicscripts/internal"

Export data files to an archive file in the Primary Oracle Analytics Server Instance using following command

python migrate_datafiles.py /tmp/DATAFILES export --logdir=/tmp

Result:

Import Data files to Disaster Recovery Oracle Analytics Server Instance

• Copy DATFILES archive file from source environment to target environment.
• Navigate to ORACLE_HOME/bi/modules/oracle.bi.publicscripts
• Copy migrate_datafiles.py to migrate_datafiles_import.py
• Update migrate_datafiles_import.py as follows

domain_home = topology.get_domain_home()

oracle_home = topology.get_oracle_home()

wlst_path = topology.get_wlst_path()

internal_scripts_path = os.path.join(topology.get_publicscripts_path(), 'internal')

To

domain_home = '[PATH_DOMAIN_HOME]'

oracle_home = '[PATH_ORACLE_HOME]'

wlst_path = '[PATH_WLST.SH]'

internal_scripts_path = '[PATH_INTERNAL_SCRIPTS]'

Example:

domain_home = "/u01/fmw/user_projects/domains/bi"

oracle_home = "/u01/fmw/bi"

wlst_path = "/u01/fmw/oracle_common/common/bin/wlst.sh"

internal_scripts_path = "/u01/fmw/bi/modules/oracle.bi.publicscripts/internal"

Import data files from the archive file into the Disaster Recovery Oracle Analytics Server Instance using following command

python migrate_datafiles.py /tmp/DATAFILES import --logdir=/tmp

Result:

Migrating BI Publisher JDBC Connections:

Snapshots don’t include the BI Publisher JDBC Connections hence copy datasources.xml file from Primary Oracle Analytics Server Instance to Disaster Recovery Oracle Analytics Server Instance.

Path: /fmw/user_projects/domains/bi/config/fmwconfig/biconfig/bipublisher/Admin/DataSource/datasources.xml

Note: Once JDBC Connections are migrated, need to enter the passwords in the Disaster Recovery Oracle Analytics Server Instance and submit, apply.

Source: oracle.com

Continue reading

Automate compliance reports with Oracle Data Safe

Oracle Data Safe, the unified control center for your Oracle databases, now provides the capability to schedule and automate audit reports so that the auditors periodically receive them at a scheduled frequency.

Read More: 1Z0-078: Oracle Database 19c: RAC, ASM, and Grid Infrastructure Administration

Demonstrating audit compliance to auditors and security compliance professionals requires periodic reports showing database activity – the information you are collecting in your audit trail. Data Safe lets you automate report generation, helping ensure that your security compliance reports are ready when you need them. Working with the Oracle Cloud Infrastructure (OCI) notification service, Data Safe generates events that can be used to notify auditors that reports are ready, or integrate Data Safe with other services to retrieve, store, and process those reports.

To schedule periodic reports, open Security Center, navigate to Activity Auditing and under related resources, click Audit Reports. Select the report that you wish to schedule and click Manage Report Schedule as shown below. Manage Report Schedule lets you define the schedule frequency, report format, time span of audit events and set limits on the number of rows that will be contained within a report.

Manage Report Schedule

The latest report is available for download in Audit Reports.

Prior reports are available for download in Audit Reports History. The historical list makes it easy to pick out the latest report, determine if the report was generated on-demand or by schedule, and verify the file format for the report.

For pro-active notifications when a scheduled report is generated, leverage the Data Safe OCI event Scheduled Report Generated and configure rules in OCI Events service as shown below.

In the Event Rule actions, associate an appropriate notification action. The screenshot below shows an email subscription notification associated to the rule action to pro-actively trigger an email to let the auditor know of the latest scheduled report instance whenever it is generated.

When the scheduled report is generated, an email notification is triggered with the format as shown below.

Note two of the links embedded in the email.  The hyperlink in reportURLInConsole lets the auditor navigate to the Oracle Data Safe service console, and by clicking Download Report, they are able to download the scheduled report instance.

To automate download of the report, use the hyperlink in reportURL to access the report from any OCI SDK client. The helpDoc parameter refers to the Data Safe API required for invocation: getReportContent.

For instance, sample REST invocation of getReportContent with POSTMAN using value in reportURL is shown below.

Sample OCI-CLI invocation of getReportContent using value in reportURL is shown below.

In a nutshell, to enable continuous availability of audit reports to your auditors and security administrators to help meet compliance requirements, leverage the scheduling and associated automation capability offered by Oracle Data Safe service.

Source: oracle.com

Continue reading

Stay Ahead of the Game: Why 1Z0-1094-22 is the Future of Oracle Certification?

The Oracle 1Z0-1094-22 exam is a critical certification exam for professionals seeking to advance their careers in the Oracle community. This exam demonstrates mastery of Oracle Fusion Middleware, an essential component of modern IT infrastructure. Passing the exam indicates proficiency in the platform and opens up different career opportunities in the IT industry. This article provides tips and tricks on how to pass the Oracle 1Z0-1094-22 exam and boost your career.

If you are preparing to take the Oracle 1Z0-1094-22 exam, congratulations! This is an essential step in your career as an Oracle Cloud Database Migration and Integration 2022 Certified Professional. However, passing the exam can be challenging. The exam tests your knowledge of OCI architecture, services, and deployment, among other things. In this article, we will provide valuable tips to assist you in confirming your success in the exam.

Understanding the Oracle 1Z0-1094-22 Exam

The Oracle 1Z0-1094-22 exam is a 90 minutes certification exam with 55 multiple-choice questions. The exam measures the candidate's proficiency in Oracle topics, and passing the exam requires a 70% or higher score.

To prepare for the exam, candidates should review the objectives and topics, study relevant documentation and guides, and take practice tests. Candidates should also focus on time management, common pitfalls, and test-taking strategies to succeed in the exam.

Tips for Passing the Oracle 1Z0-1094-22 Exam

Time management is crucial during the exam, so candidates should concentrate on answering the more straightforward questions and moving on to the more challenging ones. They should also be aware of common pitfalls such as overthinking, minimizing the exam, and changing answers frequently. Test-taking strategies such as eliminating wrong answers, taking breaks, and reviewing answers can also help reduce exam anxiety.

To become an Oracle Cloud Database Migration and Integration 2022 Certified Professional (OCP), passing the 1Z0-1094-22 certification exam is necessary. This exam tests your knowledge of Oracle services, architecture, and security features. But how can you pass this exam in just 30 days? This article will provide a comprehensive guide on passing the 1Z0-1094-22 certification exam in 30 days.

1. Create a Study Plan

A study plan is crucial to passing the 1Z0-1094-22 certification exam in 30 days. You must dedicate enough time each day to study the exam topics thoroughly. Divide the issues into manageable chunks, and allocate enough time for each subject. Your study plan should also include practice exams to assess your progress.

2. Get Familiar with the OCP Services

The 1Z0-1094-22 certification exam covers a wide range of OCP services. You need to get familiar with these services and understand their features, use cases, and pricing. Oracle provides documentation that you can use to learn about these services.

3. Study the 1Z0-1094-22 Exam Topics

The exam topics for the 1Z0-1094-22 certification exam are divided into categories. Each category covers specific topics that you need to study. You can find the detailed exam topics on Oracle's website. Examine these topics using documentation, online courses, and practice exams.

4. Take 1Z0-1094-22 Practice Exams

Taking practice exams is a crucial part of your exam preparation. Some platforms provide practice exams that you can purchase, but practice exams are available online. Practice exams will help you assess progress, identify weaknesses, and improve your time management skills.

5. Join Study Groups

Joining study groups is an excellent way to prepare for the 1Z0-1094-22 certification exam. You can find study groups on social media platforms, forums, and online communities. In these groups, you can ask questions, share resources, and get support from other candidates.

6. Attend Online Courses

Oracle provides online courses that cover exam topics in detail. These courses include videos, quizzes, and hands-on labs.

7. Use Flashcards

Flashcards are an excellent tool to help you memorize key concepts and definitions. You can create your flashcards or use pre-made ones available online. Flashcards can help you review essential topics quickly and are also great for revision.

8. Review Your Notes Regularly

As you study, make sure to take notes. These notes will help you revise the exam topics quickly. Review your messages regularly, and make sure to include any additional information that you may have missed.

9. Stay Motivated

Staying motivated is essential to passing the 1Z0-1094-22 certification exam in 30 days. Set achievable goals, reward yourself for progress, and stay positive. Remember that passing the exam is possible with dedication and hard work. Keep going even if you encounter complex topics or questions during your study process.

Instead, focus on understanding the concepts thoroughly and use the resources available. Seek help from study groups or online communities, and do not hesitate to reach out to professionals who have already passed the exam. With determination and perseverance, you can achieve your goal of becoming an OCP professional.

Conclusion

Passing the Oracle 1Z0-1094-22 exam is a significant accomplishment for IT professionals seeking to advance their careers in the Oracle community. This article has provided tips and tricks on how to pass the exam, including time management strategies, common pitfalls to avoid, and test-taking strategies.

Additionally, it has discussed the benefits of Oracle 1Z0-1094-22 certification and the career opportunities available for certified professionals. Candidates can find additional resources to prepare for the exam and stay up-to-date with exam changes and updates.

Continue reading

Writing Applications for JSON Documents in a Sharded Environment

Part 1: Sharding Oracle JSON and how to choose sharding keys for data distribution

Oracle’s JSON capabilities allow native JSON support with the JSON data type which also supports relational database features, including transactions, indexing, declarative querying, and views. Often JSON applications need to distribute their data across multiple instances or shards, either for scalability or geographical distribution.

Oracle Sharding is a database technology that allows data to scale to massive data and transactions volume, provides fault isolation, and supports data sovereignty.

In this two part blog we look at different options to configure JSON in a sharded environment, including how to choose a sharding key and how to distribute JSON data.

Included are examples in both Java and Python which show how data is queried and inserted from an application.

This walk through was developed on Oracle 21c, which first supports the JSON data type.

Introduction

Motivations for sharding JSON as with other types of data are:

1. For scaling, scaling horizontally provides support for large volumes of transactions as well as large volumes of data.

2. For availability reasons, application data spread across multiple instances limits the impact of an outage of any one instance or node.

3. For data sovereignty, you can provide geographical isolation to a subset of data while ensuring it is part of the same database.

Planning JSON and Sharding

There are many different options when choosing how to shard JSON. We will cover two common use cases here.

The first use case is simply the ability to distribute data across multiple instances. Sharding requires a sharding key, but the sharding key does not necessarily need to be a field within the JSON document. In the first example we show how you can use an external key to distribute and query and access data across shards.

In the second example we use a JSON field as a sharding key with a non-unique sharding key. This allows us a bit more control over data distribution because documents with the same sharding key are kept together in the same shard.

In both of these examples we use the system-managed sharding method. This method defers the placement of data to a system provided hash function.

If you need even greater control, you could opt for the user-managed sharding or composite sharding method. Those options are not described in this blog series, but more information and examples are available in the documentation referenced at the end of this post.

Use Case 1: Independent Sharding Keys

After setting up a system-managed sharding environment, we create an all shard user. When the environment is available we can begin to create the necessary schemas.

The simplest schema is a table which holds both the sharding key and the JSON data. Connecting to the shard catalog database as the sharding user, we can create this table as follows:

/* Create the sharded table */

CREATE SHARDED TABLE CUSTOMERS

(

"ID" VARCHAR2(255) NOT NULL,

"CUSTPROFILE" JSON,

PRIMARY KEY (ID)

)

TABLESPACE SET TSP_SET_1

PARTITION BY CONSISTENT HASH (ID) PARTITIONS AUTO;

Our sharding key is the column ID. Note that the ID we are using for each document is unique and so we are setting this here as the primary key. A sharding key, however, does not need to be unique. But if it is not, then it needs to be part of the primary key. We will revisit this in more detail in Part 2 when we discuss SODA. For now, we will use a unique sharding key.

We have several options for loading data into the new table. We can of course seed the table with externally loaded data. We can also insert data with an application.

In the following code samples we show how to

1) Insert and retrieve data from our JSON table by initiating a direct connection to the shard where the data resides, and then

2) Connect to the shard catalog to issue a query across all data

Single-Shard Queries

For single-shard queries or operations, the recommended way of accessing data is Direct Query Routing.

Oracle Sharding allows you to connect directly to a shard to process queries by providing a sharding key along with the database connection request. In the example below, we set up a UCP connection pool first.

In Java, we would have:

Example 1: Inserts with Independent Sharding Keys (Java)

// Get the PoolDataSource for UCP

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

// Set the connection factory and other properties. The connection is to the Global

// Service Manager (GSM) instance

pds.setConnectionFactoryClassName(OracleDataSource.class.getName());

pds.setURL(DB_URL);

pds.setUser(DB_USER);

pds.setPassword(DB_PASSWORD);

pds.setConnectionPoolName("JDBC_UCP_POOL");

// We cannot get the connection until we have the Shard key which is part of the SQL

// We first set the sharding key or document id. We can set it explicitly

// for example shardingKeyVal=’10’; but if we are

 // just using it for hashing we can also set it as a UUID

UUID uuid=UUID.randomUUID{};

String shardingKeyVal=uuid.toString{};

 

// Now we build the connection using this shard key

OracleShardingKey sdkey = pds.createShardingKeyBuilder().subkey(shardingKeyVal, OracleType.VARCHAR2).build();

System.out.println("Creating Connection...");

Connection conn = pds.createConnectionBuilder().shardingKey(sdkey).build();

Now we have a connection to a specific shard.

From here we proceed with our operations, whether it be insert, or update, or select.

PreparedStatement pstmt = conn.prepareStatement(“INSERT INTO CUSTOMERS VALUES (:1,:2)”);

String str = "{\"name\":\"Jean\", \"job\": \"Intern\", \"salary\":20000}";

 

Pstmt.setString(1, shardingKeyVal);

pstmt.setObject(2, str, OracleType.JSON);

pstmt.execute();

system.out.println(“Document inserted.”);

Note that to insert another document, we do have to set a new UUID and then create a new connection so that the document is inserted into the correct shard.

In Oracle 21c, we have a Sharding Data Source which allows the connection to use the sharding key in the query without the need to specify it explicitly. The previous exercise, however, is useful, however, both because the Sharding Data Source is only supported in Oracle 21c and is only supported for JDBC UCP. In Part 2, where we discuss SODA access, we will also return to this topic.

Multi-Shard Queries

Direct shard connections are great for when we want to work with individual documents, but what if we want to search across all documents?

For this, we can run queries against the shard catalog database, which automatically farms out the query across the shards.

We create a connection to the shard catalog and then run our query.

For example, again using UCP:

Example 2: Queries Across Shards (Java)

// Get the PoolDataSource for UCP

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

// Set the connection factory first before all other properties

// Our connection here is to the Shard catalog

pds.setConnectionFactoryClassName(OracleDataSource.class.getName());

pds.setURL(DB_URL);

pds.setUser(DB_USER);

pds.setPassword(DB_PASSWORD);

pds.setConnectionPoolName("JDBC_UCP_POOL");

 

// Now we get a direct connection to the shard catalog

System.out.println("Initiating UCP and Creating Connection...");

Connection conn = pds.getConnection();

And then we issue our query as usual:

PreparedStatement stmt = conn.prepareStatement(

"SELECT c.custprofile FROM CUSTOMERS c WHERE c.data.salary.number() > :1");

stmt.setInt(1, 30000);

ResultSet rs = stmt.executeQuery();

while (rs.next()) {

OracleJsonObject obj = rs.getObject(1, OracleJsonObject.class);

String name = obj.getString("name");

String job = obj.getString("job");

System.out.println(name + " - " + job);

}

rs.close();

stmt.close();

The results are JSON objects which match the query regardless of which shard they reside on.

In an application you may want to also retrieve the ID in the above query and then use that to perform further work on specific objects, and update them directly on the shard that they reside on.

Use Case 2: Sharding on JSON Fields

In the first use case, we looked at how to simply distribute JSON data across shards using a simple but unrelated unique ID. In this example, we take a look at how to shard JSON using fields in the JSON document.

This has some unique benefits:

1. Because sharding keys do not have to be unique, JSON objects with the same shard key value are clustered together. For example, everyone with the same US ZIP code will reside on the same shard. This can make queries on a single shard possible, lowering the number of cross-shard queries.

2. Physical placement (mapping data to physical shards) becomes easier and may be used when considering data governance issues or other issues where there is a relationship between the field value and physical access.

It is important to choose the correct sharding key. Consult the Oracle documentation on sharding key choice and limitations. In particular, a sharding key is a field in the data that should rarely, if ever, change.

As before, we create a table but add one more field that will hold the value of the field that we intend to use in sharding. Let’s use the ZIP code example as mentioned before. So we create the table as:

/* Create the sharded table */

CREATE SHARDED TABLE CUSTOMERS

(

"ID" VARCHAR2(255) NOT NULL,

"ZIP" VARCHAR2(60) NOT NULL,

"CUSTPROFILE" JSON,

PRIMARY KEY (ID,ZIP))

TABLESPACE SET TSP_SET_1

PARTITION BY CONSISTENT HASH (ZIP) PARTITIONS AUTO;

There are two differences to note here:

Firstly, we added the column (ZIP) that will hold the JSON field.

Secondly, we have created a Primary Key that includes ID and ZIP. We will continue to use ID both because it provides us a unique ID to use to build a Primary Key, and also because it will make it easier to access this table using SODA in the next installment of this series. The Primary Key however must be unique AND it must include the sharding key, so we make it a combination of the two fields.

It is not possible to shard directly from JSON fields, thus the need to create a column to use for the sharding key. But we also have to keep this column in sync. To enforce these requirements we can use a database trigger.

These requirements to both create the column and keep the column in sync can also be enforced by the application. See Appendix B for more details.

As the sharding user, on the shard catalog database, we can issue the following commands to create the trigger:

alter session enable shard ddl

create or replace procedure COLLECTION_BF_ZIP_CUSTOMERS AS

begin

EXECUTE IMMEDIATE 'alter session enable shard operations';

EXECUTE IMMEDIATE q'%

Create or Replace TRIGGER CUST_BF_TRIG

BEFORE INSERT or UPDATE on CUSTOMERS

FOR EACH ROW

begin

:new.ZIP := JSON_VALUE(:NEW.CUSTPROFILE, '$.ZIP' error on error error on empty);

end;

%';

end;

/

exec sys.exec_shard_plsql('app_schema.collection_bf_zip_customers()',4+1+2);

In the example above, ZIP is assumed to be a top-level field in the JSON document. If the value is in a nested field, for example under an ADDRESS field, you must include the field hierarchy, for example '$.ADDRESS.ZIP'.

Also, the returned JSON_VALUE must match the type of the JSON field, in this case it defaults to VARCHAR. If we wanted to have ZIP be a NUMBER for example, then the statement above would have a RETURNING NUMBER clause in addition to the error clause.

The exact name of the procedure is only chosen for convenience. The important parts are the appropriate column to update and which JSON field it maps to.

The procedure above wraps a trigger that is then created locally on each shard. The purpose of the trigger is to allow the application to add or update JSON and have the shard field (ZIP in this case) kept in sync automatically.

Now, as in the previous examples we will demonstrate how to perform queries using direct routing, as well as proxy routing via the shard catalog.

Single-Shard Queries

Again, the shard we need to connect to is determined by the data we need to work with. In this case, the ZIP field, which is the sharding key, is being used to determine the correct shard to connect to.

We build the UCP Connection as in Example 1, but then:

Example 3: Inserts with JSON Sharding Keys (Java)

…

// We will still set the document id since we still need it as a unique key.

// Later, when using SODA we will allow SODA to set this for us so we do not need to

// provide it.

UUID uuid=UUID.randomUUID{};

String documentid=uuid.toString{};

// To get the connection as before we need the sharding key but this time it is ZIP, which is

// part of our JSON. We can set it explicitly for example as shardingKeyVal=”94065” or

// if we have an OracleJsonObject all ready to insert then we can grab the ZIP from there as

// below

String shardingKeyVal = object.getString("ZIP");

// Now we build the connection using this sharding key as before

OracleShardingKey sdkey = pds.createShardingKeyBuilder().subkey(shardingKeyVal, OracleType.VARCHAR2).build();

System.out.println("Creating Connection...");

Connection conn = pds.createConnectionBuilder().shardingKey(sdkey).build()

PreparedStatement pstmt = conn.prepareStatement(“INSERT INTO CUSTOMERS (ID, CUSTPROFILE) VALUES (:1,:2)”;

Pstmt.setString(1, documentid);

pstmt.setObject(2, obj, OracleType.JSON);

pstmt.execute();

system.out.println(“Document inserted.”);

We have created a new document in a specific shard, and the ZIP field will be populated automatically.

To insert more documents we have to establish a new database connection. But note that if the next document we want to work with has the same ZIP then we do not need to establish a new database connection. We are already connected to the correct shard.

Multi-Shard Queries

Queries across shards work the same way as in Example 2. The only thing to add, as before, is that if the query involves an equality on the sharding key (ZIP in this case) then the connection can also be made directly to the shard instead of to the shard catalog.

For example, if our query is to find all customers named ‘SMITH’ within one specific ZIP code, then we can connect to the shard that holds that data and issue the query.

Conclusion

In this post we have demonstrated how we can distribute JSON data across Database Shards. If the intent is primarily to distribute the data evenly, then a key can be generated, unique to each Document, to act as a document identifier. This ID is then hashed in order to distribute the documents equally across available shards.

We can also tie the sharding key to a specific field within the JSON document. This allows us to set up a meaningful document ID that can be used as the basis for retrieval and update of the document. The major consideration, however, is that this field is a static field, not one that is updated.

So far, we have used code samples that use SQL in order to work with and search for documents. In Part 2, we will show you how Simple Oracle Document Access (SODA) can be used instead of SQL in the application code to work with JSON documents in a sharded database configuration.

Appendix A: Python Examples

The following are Python equivalents of the above Java code snippets in this post. The SQL is the same in creating the database objects but the equivalent application code is below:

Independent Shard Keys

Example 4: Inserts with independent shard keys (Python)

# import the cx_Oracle module for Python

import cx_Oracle

# Create a connection pool that will be used for connecting to all shards

# The components of the dsn are hostname (shard director),

# port (usually 1522), global service (created with GDSCTL)

dsn=cx_Oracle.makedsn("shard_director_host",1522,service_name="service_name")

pool=cx_Oracle.SessionPool("app_schema","password",dsn, soda_metadata_cache=True)

# Connect to a specific shard by using the sharding key, which in this example is

# set explicitly as a UUID

shrdkey=uuid.uuid4();

connection=pool.acquire(shardingkey=[shrdkey]);

# Setup our JSON

data = dict(name="Jean", job="intern", country="Germany")

inssql = "insert into CUSTOMERS values (:1, :2)"

#Insert the document

cursor.setinputsizes(None, oracledb.DB_TYPE_JSON)

cursor.execute(inssql, [shrdkey, data])

# After you have finished, release this connection back into the pool

pool.release(connection)

# If you want to add or work with more customers, start with another connection

# For example: connection=pool.acquire(shardingkey=["123"]) and so on.

#When you are completely finished working with customers you can shut down the pool

pool.close()

As in the Java example, we need to generate a new UUID and grab a new connection from the pool in order to insert another document

Example 5: Queries across Shards (Python)

import cx_Oracle

# Create an unpooled connection to the shard catalog

# In general, pooled connections should be used for all connections.

# This is shown here only as an example.

# The connect string connects to the shard director, but uses

#  the catalog service, e.g. GD$catalog.oradbcloud

connection = cx_Oracle.connect("app_schema","password","db_connect_string")

# Build the query and execute it with a cursor

# Python supports JSON natively and once retrieved into a JSON object can be manipulated

sql = "SELECT c.custprofile FROM customers c where c.data.salary.number() > :1”"

for j, in cursor.execute(sql, 30000):

      print(j)

# Close the unpooled connection

Connection.close()

Sharding on JSON Fields

Example 6: Inserts with JSON Sharding Keys (Python)

# import the cx_Oracle module for Python

import cx_Oracle

# Create a connection pool that will be used for connecting to all shards

# The components of the dsn are hostname (shard director),

# port (usually 1522), global service (created using GDSCTL)

dsn=cx_Oracle.makedsn("shard_director_host",1522,service_name="service_name")

pool=cx_Oracle.SessionPool("app_schema","password",dsn,soda_metadata_cache=True)

# Connect to a specific shard by using the shard key, a ZIP code. which in this

# example is set explicitly as '94065', but this might be passed in or part of a loop

# You must know beforehand whether you are creating or working with a document

# with a specific ZIP code value.

connection=pool.acquire(shardingkey=["94065"])

 

# Now generate our unique UUID

genuuid=uuid.uuid4();

# Setup our JSON as before

data = dict(name="Jean", job="intern", country="Germany")

inssql = "insert into CUSTOMERS (ID, CUSTPROFILE) values (:1, :2)"

#Insert the document

cursor.setinputsizes(None, oracledb.DB_TYPE_JSON)

cursor.execute(inssql, [genuuid, data])

# After you have finished, release this connection back into the pool

pool.release(connection)

# If you want to add or work with more customers with a different

# shard key start with another connection

# For example: connection=pool.acquire(shardingkey=["10012"]) and so on.

 

# When you are completely finished working with customers, shut down the pool.

pool.close()

Appendix B: Enforcing Constraints with Application Logic

In the post above, a database trigger was used to ensure that the database field (ZIP in our example) was in sync with the equivalent field inside the JSON.

There are three options for how to handle this synchronization. We used Option 1 in the text since this requires nothing from the application, but the other two should be considered as well.

Option 1: Database Trigger to enforce Inserts AND Updates

When JSON is inserted or updated, a trigger ensures that the Relational field is updated. This trigger fires even if the relevant field is not itself being updated. An example of this trigger is in the above post.

Sharding keys should not be updated so the effect of the trigger when the shard key field is updated is an error message such as ORA-02672: cannot update sharding key

Option 2: Database Trigger to enforce only Inserts

When JSON is first inserted, this trigger ensures that the Relational field is updated. This trigger does not fire when the JSON field is later updated.

The advantage of this is that the trigger is not fired when most updates are made. Unfortunately, when the field corresponding to the sharding key is updated then the JSON field and the Relational field will be out of sync.

However, because sharding keys should not be updated, we recommend that the application itself enforce the constraint and not allow users to update the JSON field corresponding to the sharding key.

This trigger is created similarly to the one in the main post except instead of “BEFORE INSERT or UPDATE” it should read “BEFORE INSERT” only.

Option 3: No Database Trigger

With no trigger, it is entirely up to the application to enforce the following two constraints:

1. When a row is created, the relational field (ZIP in our example) should be inserted as part of the same atomic transaction as the JSON field.

2. Updates to the JSON field should not allow the field corresponding to the sharding key/relational field to be updated.

Source: oracle.com

Continue reading

Oracle Backup & Recovery Technologies - Year in Review

We are looking forward to another exciting year across our technology portfolio, including Recovery Manager (RMAN), Oracle Secure Backup (OSB), Database Backup Cloud Service (DBBCS), Zero Data Loss Recovery Appliance (ZDLRA), and the new ZDLRA-based OCI service, Oracle Database Zero Data Loss Autonomous Recovery Service (ZRCV). Future blogs will highlight new product offerings, best practices, and commentary on industry trends in the backup and recovery market.

Read More: 1Z0-078: Oracle Database 19c: RAC, ASM, and Grid Infrastructure Administration

In looking ahead, it’s always good to spend a little time looking back at highlights of the previous year, demonstrating our innovation and leadership in Oracle data protection and recovery solutions.

Zero Data Loss Recovery Appliance

Marking its 8th year in commercial availability, Recovery Appliance continues to evolve unique database protection capabilities beyond general-purpose backup solutions. These capabilities are centered around: Resilient Recovery, Infrastructure Efficiency and especially visible with customers today: Effectively Defending and Recovering from Ransomware. To that end, the appliance protects transactions in real-time, validates backups for database recoverability, and optimizes backups for fast recovery. These capabilities are a necessity in the face of ransomware attacks which can occur at any time and spread to any number of systems. Recovering all affected systems quickly and to a transactionally-consistent safe point is crucial for data availability and data quality – these are fundamental tenets for which Recovery Appliance was designed, as shown below.

Recovery Appliance End-to-End Ransomware Protection

 

In the latest RA 21.1 software release, new capabilities were introduced to defend against malicious access and tampering.

First, role-based users allow finer-grained provisioning for system access, helping tighten overall security controls in your backup infrastructure:

◉ Named administrator user limited to day-to-day activities via documented management API and Enterprise Manager

◉ Monitor user with read-only / reporting access

◉ Quorum administrator approval to gain system/root privileges. 

Second, new immutability settings prevent the deletion of backups by any internal or external user within a pre-defined retention period. Backups continue to be preserved under the original retention period even if an administrator reduces the setting in an attempt to force the system to purge older backups.

Given the critical nature and impact of ransomware attacks, these capabilities were purposefully designed to meet requirements outlined in SEC 17a-4(f), a key data governance regulation in the banking industry – see the product assessment report for more details on the regulation and relevant capabilities offered by the appliance.

With respect to Recovery Appliance management capabilities, Oracle Enterprise Manager 13.5 was enhanced to support the aforementioned role-based user model and immutability controls, in addition to TLS network encryption, backup & recovery high availability between appliances, and compatibility with REST-enabled management products:

◉ Named Users Management (Administrator, Monitoring User, VPC User)

◉ Administrator API Execution History Monitoring

◉ Immutable (Compliance) Backups

◉ Configuration and Backup to TLS-enabled Appliance

◉ End-to-End Replication Configuration & Management for High Availability

◉ New REST APIs for Common Administrator Operations

Zero Data Loss Autonomous Recovery Service

At CloudWorld 2022, we announced the Oracle Database Zero Data Loss Autonomous Recovery Service. This new fully managed Oracle Cloud Infrastructure (OCI) service is based on Zero Data Loss Recovery Appliance technology and delivers protection capabilities for Oracle Database services running on OCI. The Recovery Service helps organizations meet the business-critical need to reduce ransomware risk, financial requirements for improved operational efficiency, and user expectations for cloud service simplicity.

Zero Data Loss Autonomous Recovery Service Architecture Overview

Oracle Database Backup Service

The Database Backup Cloud Module now supports the use of OCI Object Storage Locked Retention Rules for compliance and ransomware protection. This Retention Rule prevents any deletion or modification of objects in a designated bucket for a pre-defined period of time.

These retention controls were also assessed to meet SEC 17a-4(f), a key regulation for financial services customers – for more information, see the product assessment report.

Oracle Secure Backup

Oracle Secure Backup (OSB) protects file systems as well as Oracle and MySQL Databases to disk, tape and Cloud storage on-premises, in the Cloud, and for hybrid environments. As Cloud deployments become ubiquitous, the product team has been focusing on OCI data protection use cases, including backup of block volumes, VMs, and Oracle Home directories of Exadata Database Service databases. In fact, there are hundreds of customers today protecting their OCI Compute Instances and File Systems with OSB:

◉ 300 Tenancies (Customers)

◉ 6,500 Clients (5000 Linux, 1500 Windows)

◉ 6,500 Full Backups per week

◉ 39,000 Incremental Backups per week

◉ 10 PB stored in OCI Object Storage

◉ 10 Restores per month

OSB recently became available on the OCI Marketplace for fast and easy deployment into an OCI tenancy. In just a few clicks, an OSB Domain Admin Server is set up and ready to use. The deployment includes Ansible PlayBooks to automate the installation of OSB Client Agents on your OCI Compute Instances.

Source: oracle.com

Continue reading