Recently i obtained the Autonomous Database Cloud Specialist certification. In this blog i would like to brief about my learning on the topic. However, for the readers who are new to Oracle Cloud, i would recommend going through my other blog on OCI Fundamentals before starting with this blog for a better understanding .

Introduction

Databases store critical business information and are essential for the efficient operation of modern organizations. Business applications add new records to existing databases or use database information to create reports, analyze trends, or perform other business transactions. DBAs are often overburdened with time-consuming manual tasks of managing and maintaining databases, that may lead to DBA errors resulting in catastrophic impact on uptime, performance, and security. Databases that are slow-running or unavailable due to downtime can negatively impact employee productivity and frustrate customers. As the amount and velocity of data available is accelerating, there is a need for efficient, secure database management that enhances data security, reduces downtime, improves performance and is not vulnerable to human error. An autonomous database can help achieve these objectives.

Autonomous Database Technical Overview

Autonomous Database Cloud marks the culmination of four decades of technology innovation. The Oracle Autonomous Database is built on components like the Exadata system that has been designed to run an Oracle database from day one. The next component as mentioned earlier is the Oracle database that should be of version of 19c or higher. The final component would be the Oracle Cloud Infrastructure, the Oracle Autonomous Cloud Platform, Oracle Autonomous Applications, and many other Oracle innovations, incorporating machine learning to eliminate human intervention once policies are set. The OCI can be setup for automated DC operations that may include provisioning, patching, upgrading, online backups, monitoring, scaling, diagnosing, performance tuning, optimizing, testing, change management of complex applications and workloads, automatically handling failures and errors.

Workloads

The Oracle Autonomous Database supports enterprise applications delivered by Oracle. Even customer and ISV applications that run on top of any Oracle database are supported. Cloud native applications running in Terraform or running as Kubernetes containers, as well as operations running with function as a service are also supported. And also, one thing to keep in mind is the Oracle Autonomous Database is absolutely capable of running performance-intensive workloads.

The Autonomous database is classified based on the workload types, namely : Autonomous data warehouse(ADW), Autonomous transaction processing(ATP) service. Now, the difference between the two is that the ADW, with data being stored in columnar format is well suited for analytic workloads. Also workloads where data is not changing too frequently, operations that are more suited to either a data warehouse, data mart, machine learning, a data lake etc. On the other hand, ATP, with data being stored in row format (best for single-row lookups) is well suited for workloads where they could be very transaction focused, or they could be batch or reporting focused.

Benefits and Attributes

There are several benefits of an autonomous database. The main ones are as follows.

  • Maximum database uptime, performance, and security―including automatic patches and fixes
  • Elimination of manual, error-prone management tasks through automation
  • Reduced costs and improved productivity by automating routine tasks

The autonomous database (ADB) is simple, fast and elastic. Exadata infrastructure provides highest performance, availability and seamless scale-up or scale-out. ADB leverages AI and machine learning to provide full, end-to-end automation for provisioning, security, updates, availability, performance, change management, and error prevention. In this respect, an autonomous database has specific characteristics as follows.

  • Self-driving : All database and infrastructure management, monitoring, and tuning processes are automated.
  • Self-securing : It stores all data in encrypted format and only the authenticated users and applications can access data. Encryption for data at rest and data in motion is provided along with automatic patching of all components like firmware, OS, database, hypervisor etc.
  • Self-repairing : This can prevent downtime, including unplanned maintenance by making use of ML techniques and best practices to automate database operations. It provides an SLA of 99.95% availability.

Architectural Components

The Oracle Autonomous Database is tightly integrated with Oracle Cloud Infrastructure. The Autonomous Database runs on Exadata Systems hosted on OCI Datacenters. But the Autonomous Database storage runs on Exadata storage services that are directly connected to the Exadata compute nodes for the highest performance, as well as availability.The Oracle Machine Learning servers allow the operations that are available for managing all of the components to be run independently of the Exadata service themselves. The user processes, application services, and other things that end users would be running against these services all come in through the connection managed services, and these connection managers help route, and direct, and distribute the workload coming into the autonomous service to the appropriate Exadata service where the workload is running. This architecture is used in each AD of a region along with load balancer to provide high availability. OCI object storage is used to support ADB for file storage outside the database. All backups run automatically to a dedicated OCI object storage component. All user backups are pointing to user-defined object storage buckets that the user would have to create and configure.

The provisioning, deployment to the life cycle maintenance of an Autonomous Database can be done using the OCI Console, OCI CLIs and the REST APIs. Using Oracle Rest Data Services (ORDS) developers can easily build Rest APIs for data and procedures in the database. Developers can use the OCI SQL developer web or the desktop version of SQL developer, or any other developer tool that supports Oracle database connections. Monitoring is available through the cloud service dashboard. Securely connect to ADB using credential wallets via SQL*Net, JDBC, ODBC. Access from onprem to ADB through fastconnect public peering is possible. Extract, Transform and Load ,i.e, ETL/BI tools can directly connect to ADB instance and can also be run while still connected to ADB. When connecting to other cloud environments, FastConnect with Megaport cloud router is used to connect to ADB or OCI from/to other clouds.

The Oracle Machine Learning component or a solution is a product that is built on top of the notebooks, and it’s possible to quickly start running queries to utilize the algorithms without having to setup any additional component. You’ll also notice that we have Oracle Data Visualization Desktop, which is a client tool that runs on both Windows, Mac OS 10 for running workloads and visualizing data sets within the Autonomous Database. The ADB supports many data movement features such as ODI platform, Oracle GoldenGate, Oracle’s data guard to connect workloads to the Oracle Cloud. Oracle Data Safe, a free service provides a console, a centralized dashboard to manage and assess the security of your enterprise of database services. Simple widgets or wizards help you connect your Oracle Analytics Cloud to Autonomous Database to solve your analytics problems. So to summarize, the Autonomous Database provides a full set of capabilities to support workloads tied to analytics, tied to business intelligence, tied to machine learning etc.

ADB Workflow

There are six main steps to the migration and deployment to moving to an Autonomous Database.

  • Determine level of automation and functionality required
  • Determine main workload characteristics for the database
    This is about choosing a workload that fits with the ATP/ADW database. Is it a mission critical application that’s supporting a business operation? perhaps it’s a hybrid workload, where it’s a partial transaction workload and partial analytics or a bit of both. It could, in fact, be a very large database, with updates happening fairly frequently. Or it could even be used for real-time analytics and machine learning against a transactional workload. ATP would be the best fit for workloads where application developers would be using the sandbox/testing environment. Similarly, ADW would be the best choice for data scientists to perform the ML testing on their notebooks.
  • Provision the ADW or ATP service
    The provisioning is automatically done for you. Here, you simply choose the shape and the type of workload, i.e, ADW or ATP. And then, provisioning the server, the storage, the virtual machines, and all of this is running on a scalable RAC cluster on top of an Exadata platform.
  • Automated Configuration
    The configuration of the hardware layer is completely automated for you. The Exadata is ready to use. There’s no need for any Exadata software installation, no configuration, nor any management. All the initialization parameters are automatically optimized, depending on which workload you’re choosing.
  • Load data to the new database
    If we look at one time loads, data is best staged through the Oracle Object storage. It’s extremely easy to access and configure. It’s secure, it’s encrypted, and it’s highly available. And it provides a seamless way to migrate, and move, and lift, and shift data into the Autonomous Database. Alternatively, in case of regular or repeating pattern of uploads, there are other approaches you may choose to take, and Oracle GoldenGate is one of those approaches. Your workload can run simultaneously or in real time synchronized with that alternative data source.
  • Migrate Application
    For existing applications, you want to keep the application in its existing environment and replace the existing database. And in that case, it’s simply a case of rerouting the connectivity strings of that application to now point to the Autonomous Database. Another approach is to actually rehouse that existing application in the OCI through an exact image, by virtualization as well as containers. Another approach might be to actually pull down a marketplace image from Oracle Cloud Infrastructure Marketplace, and deploy a compute image of that, and populate or configure the application to work against your Autonomous Database. Also, the new applications developed on OCI can obviously be pointed to work with the ADB. The Oracle Cloud Migration Advisor provides valuable guidelines and use cases on cloud migration.

ADB Deployment Choices

There are two physical deployment choices, namely; Autonomous Database Shared and Autonomous Database Dedicated. The shared infrastructure means, your autonomous database service will be running on an Exadata system that is being shared with other customers running their database workloads on that same physical Exadata system. The dedicated infrastructure, on the other hand, is for tenants and customers who have workloads where they want control over the physical infrastructure in which they’re laying down and running their data workload.

The Autonomous Database Dedicated Infrastructure is built on top of Oracle’s Exadata E7 or E8. It has a virtual cloud network that you would need to configure. You will be creating an Autonomous Container Database– a CDB–where the number of databases is dependent upon the availability SLA type that you are deploying for. The container database will be deployed on a RAC cluster, if under 16 OCPUs the workload will be made local but if the OCPU count exceeds 16 then the workload is spread across multiple servers. So, when you provision an Autonomous Database Dedicated, you will indeed be carving up a container database. And then from within the container database, you also do have Autonomous Shared database style if you wish. The DBAs, developers, data scientists provision their databases within the container databases that have been already provisioned for them by the fleet administrators. Some of the notable differences between ADB dedicated and shared is as follows.

Provisioning an ADB

When provisioning an autonomous database dedicated infrastructure, it’s mandatory, that the VCN and subnet must be created before end users can connect to the ADB dedicated database. However in case of autonomous database shared it is not a mandatory requirement. Provisioning the dedicated autonomous databasae infrastructure would involve three steps. The first task is to provision the dedicated autonomous Exadata infrastructure, once that is configured it is possible to provision a dedicated autonomous container database. The first two tasks are performed by the fleet administrators whereas the final task ,i.e, provisioning the dedicated autonomous database can be performed by the regular DBAs.

On the OCI console, login as a fleet administrator and not as a regular tenant. Navigate to menu>Autonomous Transaction Processing and select the appropriate admin compartment and click on the Autonomous Exadata Infrastructure under Dedicated Infrastructure item. Now we can start with provisioning by clicking on Create Autonomous Exadata Infrastructure. Enter the compartment, Display name, Select the AD, Exadata system model and system configuration. Choose the VCN and subnet in which to run and set the network security group. Specify the maintenance schedule of Exadata infrastructure by clicking on Modify Schedule under Configure the Automatic Maintenance item. Choose a license type and click on the Create Autonomous Exadata Infrastructure button.

Once the Exadata infrastructure has been created, the next step would be to create the Autonomous Container Database (A-CDB). The same fleet administrator login is used here as well. Navigate to menu>ATP, maintain the same compartment as earlier and click on Autonomous Container Database under Dedicated Infrastructure item. Now we can Create Autonomous Container Database by clicking on the button of the same name. Enter the compartment, Display name. Select the Autonomous Exadata Infrastructure created earlier. Configure the automatic maintenance schedule of A-CDB by clicking on Modify Maintenance button. Enter the Backup retention policy by specifying the number of days and click on Create Autonomous Container Database button.

Now that the Autonomous Exadata Infrastructure and Autonomous Container Database are created, the DBA can login using his admin credentials. Navigate to menu>ATP or ADW and select the appropriate compartment and workload type. Select Autonomous Database under Autonomous Database item and click on Create Autonomous Database button. Enter the Compartment, Display name, Database name and choose the workload type and deployment type. Since the deployment type selected is Dedicated Infrastructure we can select the earlier created Exadata Infrastructure Compartment and Container Database. Enter the other necessary information like OCPU count, Storage, Auto scaling to configure the database. Enter the dba admin password once again and click on Create Autonomous Database to provision the ADB.

In case the deployment type selected is Shared Infrastructure, we can select the database version, OCPU count, Storage, Auto scaling to configure the database. Select the Access type ,i.e, non-VCN or VCN and license type. Click on Create Autonomous Database to provision the ADB.

Connecting to ADB

Wallet is mandatory when connecting to Autonomous Database Shared. However, with Autonomous Database Dedicated, you can choose to connect directly without using a wallet. All apps use a secure connection to connect to the Autonomous Database, and the Autonomous Database uses certificate authentication and SSL. Certificate authentication uses an encrypted key stored in a wallet on both client and server and is automatically generated by ADB. When you download the wallet from your Autonomous Database, you’ll see there’s a bundle of files that are included with it. There are two types of wallets for an ADB Shared. An instance wallet contains only the credentials and keys for the individual Autonomous Database being provisioned. The regional wallet, on the other hand used only by administrators, contain the credentials and keys for all of the Autonomous Databases in a specified region. For ADB Dedicated, the wallet file contains only the credentials and keys for a single ADB.

You can download the wallet both from the DB Connection button on your main instance details page, as well as through the Service Console. You can also use API calls to download the wallet as well. However, you would need the wallet file along with database password to connect. It provides a secure way to connect to your database to route you to it, but not to actually log into the database. In the Oracle cloud, the private instances in private subnet can connect to Oracle services network like ADB through service gateway for secure connection. Predefined DB service names are tpurgent, tp, high, medium and low. The tpurgent and tp are ATP services that are focussed on OLTP workloads, the other ADW services can be used as ATP services but doesnt provide the high priority. The ADW services high, medium and low are used for analytics.

ADB can be connected through client utilities such as SQL*Net, JDBC/thin or thick, ODBC and all connections use SSL for encryption. Clients require security credentials wallet to connect using these tools and can connect through public internet or fastconnect. Connection through the SQLdeveloper on the local PC is as follows. Inorder to connect to the autonomous database, click on New/Select DB Connection. Enter details like, Name, DB type(oracle), user name & Password, connection type(Cloud Wallet), upload the wallet_employee config file. Next click on “Test” button, if successful click on the “connect” button to connect to the database. Even tools like SQL Developer Web from the service console can be used to connect once the access permission is set by the administrator. Please refer to the link for details about the connection setup using various other tools. Also it is good to know that we can connect through Active Directory to the ADB. Even third party tools can be configured and used to connect to the ADB. Please refer to the link for details.

Migration to ADB

The first thing to consider when migrating to the Oracle Autonomous Database is how to load and move your data into the Autonomous Database. When it comes to loading your data, the traditional tools like Oracle SQL Loader, can be used to load data into the Oracle database, as well as tools such as Oracle Data Pump. Oracle supports many different types of data types, from SQL Loader text files, to export-input dump files, to CSV, to JSON, to parquet etc. When it comes to supporting object storage, third party cloud object storage platform, such AWS S3 and Azure Blob, and, Oracle’s own object storage can be used. Even, shifting data from an OCI application layer, as well as from virtual machines that may be hosted in your cloud tenancy is possible.

when migrating an existing database into the autonomous database, it’s important to realize that a physical database cannot simply be migrated to an autonomous database. The database must first be converted to pluggable database by upgrading to the latest version of the database, 18c or higher. And it must be TDE encrypted ,i.e, Transparent Data Encryption applicable to sensitive data. When we look at approaches to move data into the Autonomous Database, data can be obviously moved with Data Pump. It can perform upgrade versions. Its platform-independent. It’s a very portable way to load data into a pre-existing or pre-created, new Autonomous Database. Talking about Oracle GoldenGate, it is possible to set up GoldenGate between your on-premise or your other source system and use the Autonomous Database as a target database for replication in real time with zero down time.

There are many other approaches to data migration with loading tools like SQLDeveloper Data Import Wizard, SQLDeveloper Migration Workbench, DBMS_CLOUD Package, external tables, data sync, ETL that can be used according to situations where one will work better than the other. The DBMS_CLOUD package is the most preferred method for loading data. Because with this tool, we can load various types of data, like a CSV, an Avro file, a JSON file, or just a regular text file. The DBMS_CLOUD package will permit you to perform these upgrades, inserts, and data loads fairly efficiently. First step would be to copy your files, in whatever format they are, to the Oracle Object Storage, and then run the DBMS_CLOUD package from within your Autonomous Database to load that data into your Autonomous Database. Refer to the list of all procedures available at the following link. The package can work with very large data volumes to perform the operation, and it works independently of the Autonomous Database you’re connected through to.

Data Loading to ADB

Oracle is tightly integrated with Oracle’s own object storage, however, the Oracle Autonomous Database is fully integrated with other major object storages like AWS S3, Azure blob. So using object storage, we can load data directly from object storage into your database, or alternatively query object storage files without actually physically moving them into your database. The data can either be in CSV, delimited, JSON, Parquet etc. text file formats. Let us look at an example of loading data into the object storage and then to the ADB using the DBMS_CLOUD package.

  • Download wallet by navigating to OCI console>user ADW>DB Connection>Download Wallet. Once downloaded copy the wallet to the necessary directory.
  • Create a new user by navigating to Tools>Open SQL Developer Web. Type the below code to assign a new tablespace to the user.
 CREATE USER new_user IDENTIFIED BY "Newpwd123";
 GRANT dwrole to new_user;
 Alter user new_user default tablespace data;
 Grant unlimited tablespace to new_user;
  • Next, open SQLdeveloper on the local PC. Inorder to connect to the database, click on New/Select DB Connection. Enter details like Name, DB type (oracle), user name & Password (mentioned in the abv code), connection type (Cloud Wallet), upload the wallet_employee config file. Next click on Test button, if successful click on the Connect button to connect to the Database.
  • Navigate to Identity>Users>Existing or New User>Auth Tokens and click on Generate Tokens on the OCI console.
  • In order to create a new user credentials used for running/executing packages or procedures, run the following code on desktop SQLdeveloper into which we had logged in earlier.
BEGIN
 DBMS_CLOUD.drop_credential(credential_name => 'OBJ_STORE_CRED');
END; /*Check for any existing credentials*/
/
BEGIN
 DBMS_CLOUD.create_credential (
  credential_name => 'OBJ_STORE_CRED',
  username => 'Existing or New User as on the OCI console',
  password => 'Auth Token generated earlier'
 );
  • Inorder to stage the data files on Object storage, navigate to OCI console Object Storage>Object Storage and click on create bucket to create a new one with the default setting. Once bucket is created, click on Upload Objects to upload the necessary files.
  • Create a sql file to generate multiple tables on the desktop SQLdeveloper. Once the tables have been created, data can be uploaded onto the tables from Object storage using the following code strip.
BEGIN
  DBMS_CLOUD_copy_data(
   table_name => 'exampletable',
   credential_name => 'OBJ_STORE_CRED',
   file_url_list => 'https://objectstorage.us-......', 
/*respective object URL from the obj storage using option 'view object details'*/
   format => json_object('a' value 'true', 'b' value 'true')
   );
END;
  • Run the above code strip and query using the code, SELECT * FROM exampletable; .You can see the data from object storage file loaded onto the table.
  • Incase of multiple tables using multiple data files staged on the object storage, below code strip shows how the load operations went by.
SELECT *
FROM user_load_operations
WHERE type = 'COPY'
ORDER BY id;
  • In case the data need not be stored on the ADB table from the Object storage, we can create an external table to just view the data as follows.
BEGIN
  DBMS_CLOUD_.create_external_table(
    table_name => 'exampletable_ext',
    credential_name => 'OBJ_STORE_CRED',
    file_url_list => 'https://.....',
    column_list   => 'acol,bcol,ccol,dcol,ecol,fcol',                     
/*columns info to match with the column data in object storage*/
    format => json_object('a' value 'true', 'b' value 'true')
);
END;
  • Use select * from exampletable_ext; to query the external table data. The data can be loaded from oracle, AWS or Azure object storage.

Data Pump is very powerful for platform independence and also very powerful when you want to be able to do a very fast move or migration. It lets you manage data from any object store like AWS S3, Azure Blob Storage or from Oracle’s Object Storage. And the data can be loaded directly from those object stores or directly from your client onto ADB. Datapump expdp/impdp can be used for databases version 10.1 and above.

Administer the ADB

Autonomous database is a self managing database. However, there are some operations like start, stop, scale, backup, recovery etc that even a regular administrator would want to perform on their database.

The stop, scale operation on an ADB instance can be performed on demand to conserve resources. Similarly even the start operation can be performed on demand. On the console, navigate to your existing ADW instance>Scale Up/Down tab and enter the OCPU Count, Storage to scale. The ADB can be used when the ADW scaling is in progress. To stop the ADB, navigate to ADW>More Actions and select Stop option. Inorder to start again, from More Actions select Start. Auto scaling can be enabled while provisioning the instance or from the ADW instance>Scale Up/Down tab after the instance is provisioned.

ADB automatically backups your DB with a retention period of 60days with the option of point in time recovery. Manual backups are performed using the cloud object storage bucket. Autonomous Database instance>Backups section on the console give the list of automatic backups. To create a manual backup, one needs to create a bucket by navigating to Object Storage>Object Storage and click on create bucket. Once bucket is created, navigate to sql developer web and type the following code in the worksheet.

ALTER DATABASE PROPERTY SET default bucket='https://swiftobjectstorage.regionnameinfo.oraclecloud.com/v1/bucketnamespaceinfo';      /*OCI Object Storage tenancy URL*/
BEGIN
DBMS_CLOUD_drop_credential(credential_name => 'OBJ_STORAGE_CRED');
END;

BEGIN
 DBMS_CLOUD.create_credential (
   credential_name => 'OBJ_STORAGE_CRED',
   username => 'oracleusername',
   password => 'pwdabc'        /*pwd generated from user>Auth tokens>Generate*/ Tokens
  );
END;

alter database property set default_credential = 'ADMIN.OBJ_STORE_CRED';

Under Autonomous Database>Backups section click on Create Manual Backups button to create manual backups. In case of restoring the DB from the backup, navigate to More Actions>Restore and enter the time from which to restore or select a backup from the list. When selected the restore process will begin. Once complete, the additional task would be to stop and start the ADB inorder to open it in read-write mode. It is possible to create a brand new Autonomous Database from an existing Autonomous Database and to create that as a clone. You can create it as a full copy of the source Autonomous Database or you can create a clone that contains just the metadata/schema of that Autonomous Database. Navigate to your ADB instance>More Actions and select the Create Clone option, enter the necessary details to create the ADB Clone.

Oracle Machine Learning, Analytics, Other tools

Oracle ML is a sql notebook interface for data scientists to perform machine learning on Oracle ADB. It is a rich set of visualization tools and mechanism that allows collaboration on building and deploying predictive models. There a 30 plus ML algorithms available to data scientists on ADB. The OML can be interfaced with Oracle Analytics cloud or other 3rd party tools. Easiest way of creating OML users is through the console. Navigate to the Service Console>Administration>Manage Oracle ML Users. Create a new user by clicking on Create and enter details. Then, you would create the workspaces that these users are going to use to run their work within. Next is you create a project wherein multiple ML users participate and they each build notebooks. Click on Notebooks icon for data analytics. Here you can run an analytics operation or SQL statements/scripts that may have be saved earlier for a periodic operation task or any instant task. Chart icon can be clicked in the notebooks to display charts/groups etc.

Two of the most common tools that use the Autonomous Data Warehouse is the Oracle Analytics Cloud and Data Visualization Desktop (DVD). DVD is a desktop extension of OA Cloud that supports an isolated environment as well. These tools bring the power of data and analytics to every process interaction and decision in every environment– cloud, on-premises, desktop, and data center. Download and install the Oracle Analytics desktop software. On the console move to Menu> Analytics and create a new instance. Once done, move to analytics instance > Oracle Analytics Cloud URL to open the oracle analytics cloud home page. The home window of data visualization desktop software and analytics cloud appear the same. In the home window of DVD/OA Cloud you can create a new connection, project, dataset, script etc. For further information on Oracle Cloud Analytics refer to the link here.

If we click on the Tools in the instance console, we can see the earlier machine learning, SQLDeveloper Web, Oracle APEX ,i.e, Application Express, user administration, SODA drivers ,i.e, simple Oracle document access options. If we click the button to access APEX, it will take us to a screen where we can define our users. The first time we login, we’ll be requested to create a workspace. Upon setting up the workspace, creating an application is extremely easy. Upon connecting to the other option under Tools, SQLDeveloper Web, you’ll see that it gives you both the ability to run SQL statements to see your schema and to do data modeling. It also provides you with a dashboard to see the health and status of your database. Onpremise SQL Developer is the desktop version of SQL Developer Web. SQL Developer 17.4 and later can connect to ADB through Oracle Wallet.

Securing the ADB

The identity and access management is used to create OCI policies that work specifically with the autonomous dedicated database. So, in this case, the fleet and database administrators are configuring what a resource is. The RESOURCE we’re setting could be one of exadata infrastructure service. It could be a container database or an autonomous database or backups. The policy statement would be in the below format. GROUPA is a set of users in a Project with the same priviliges and COMPARTMENTA is a specific set of service resources only accessible to GROUPAs who are explicitly granted access. And the VERB refers to permission asssigned to the GROUPA that can be either inspect, read, use, or manage. 

allow group <GROUPA> to <VERB> <RESOURCE> in compartment <COMPARTMENTA>

Access control lists (ACL) provides a mechanism to block all IP addresses that are not in a specified list from accessing the database. Once an ACL is set up, you need to be aware that the database will only accept connections from addresses that are in the access control list, and all other client connections and services that are subsets like SQL Developer Web, APEX, OML will be blocked. The ACL can be enabled while provisioning or by navigating to your existing ADB instance>More Actions and click on Access Control List to set the necessary IP addresses/CIDR block/VCN. Though the ADB is publicly routable, no one can access it if they are not part of the ACL. However, Private Endpoints allows you to keep all traffic to and from your ADB off the public internet. You can provision/clone an ADB to use private endpoints and configure a VCN in you tenancy to use with the private endpoint. Refer to the link for further details on configuring private endpoints.

The Network security groups (NSG) create a virtual firewall for your Autonomous Database using security rules. So just as we created access security rules or access controls to set up restrictions, we can do similar things with our security rules. And security rules, provide a mechanism to open or to allow certain ports, either ingress or egress rules, to talk in or out of your cloud service. And in the case of the autonomous database, we’re allowing an ingress of 1522. Upto five NSGs can be specified to control access to your ADB. For DBAs and developers that would prefer using cloud services programmatically rather than console, OCI offers REST APIs. This provides a mechanism for customized deployment and management scripts that can be reused. Calls to the OCI using REST APIs can be written in popular scripting languages like python, ruby, node.js, bash, curl etc. All OCI API requests must be signed for authentication purposes.

OCI Command Line Interface (CLI) is a tool that can be used on its own or with the console to complete OCI tasks.Tasks include ability to run scripts, extend console capability. CLI is built on python that makes calls to OCI REST APIs to provide functionalities implemented for various services. To use CLI, one should have OCI account, a user account in a group with a policy that grants the desired permissions to be able to make API calls. The Cloud shell/CLI shell can be accessed from the console by clicking on the icon at the top right, so there is no need of any client software. The command oci db autonomous-database lists all the services available to ADB on CLI. For example if we want to create an ADB, the necessary information would be compartment ID, db name, display name, admin password, cpu core count, data storage size in TB, db workload, license model, autoscaling enabled. When this is executed on CLI, we can observe on the console that the ADB is getting provisioned. There are many operations that can all be done very simply using the OCI CLI. It is also possible to provision an ADB using terraform.

Further Reading :