Oracle Autonomous Database (ADB) on Oracle Cloud Infrastructure (OCI) is a cloud service that leverages machine learning to automate routine database tasks, offering users a self-driving, self-securing, and self-repairing database solution. This blog post will delve into setting up and interacting with an Autonomous Transaction Processing (ATP) instance, showcasing how to deploy a sample application to demonstrate its capabilities.
Overview of Oracle Autonomous Database
Self-Driving: Automates performance tuning and scaling.
Self-Securing: Applies security patches automatically.
Self-Repairing: Offers built-in high availability and backup solutions.
Step 1: Creating an Autonomous Database
Log into OCI Console: Go to console.oracle.com and log in to your account.
Create Autonomous Database:
- Navigate to the Database section and click on Autonomous Database.
- Click on Create Autonomous Database.
- Fill in the required details:
- Display Name: MyATPDB
- Database Name: MYATPDB
- Database Type: Autonomous Transaction Processing
- CPU Count: 1 (can be adjusted later)
- Storage: 1 TB (adjust as necessary)
- Configure the Admin Password and ensure you store it securely.
- Click Create Autonomous Database.
Step 2: Setting Up the Network
2.1: Create a Virtual Cloud Network (VCN)
- Navigate to the Networking Section.
- Click on Create VCN and fill in the necessary details:
- VCN Name: MyVCN
- CIDR Block: 10.0.0.0/16
- Subnets: Create a public subnet with a CIDR block of 10.0.0.0/24.
2.2: Configure Security Lists
- In the VCN settings, add a security rule to allow traffic to your database:
- Source CIDR: Your public IP address (for SQL Developer access).
- IP Protocol: TCP
- Source Port Range: All
- Destination Port Range: 1522 (default for ADB)
Step 3: Connecting to the Autonomous Database
3.1: Download Wallet
- In the ADB console, navigate to your database and click on DB Connection.
- Download the Client Credentials (Wallet). This will be a zip file containing the wallet and connection files.
3.2: Set Up SQL Developer
- Open Oracle SQL Developer.
- Go to Tools > Preferences > Database > Advanced and set the Use Wallet option to true.
- In the Connections pane, click on the green + icon to create a new connection.
- Set the connection type to Cloud Wallet, then specify:
- Connection Name: MyATPConnection
- Username: ADMIN
- Password: Your admin password
- Wallet Location: Path to the unzipped wallet directory
- Click Test to verify the connection, then click Save.
Step 4: Creating a Sample Schema and Table
Once connected to your database, execute the following SQL commands to create a sample schema and a table:
-- Create a new user/schema
CREATE USER sample_user IDENTIFIED BY SamplePassword;
GRANT ALL PRIVILEGES TO sample_user;
-- Connect as the new user
ALTER SESSION SET CURRENT_SCHEMA = sample_user;
-- Create a sample table
CREATE TABLE employees (
employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) NOT NULL UNIQUE,
hire_date DATE DEFAULT CURRENT_DATE
);
-- Insert sample data
INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');
INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane.smith@example.com');
COMMIT;
Querying the Data
To verify the data insertion, run:
SELECT * FROM employees;
Step 5: Using Autonomous Database Features
5.1: Auto-Scaling
ADB allows you to scale compute and storage resources automatically. To enable auto-scaling:
- Navigate to your Autonomous Database instance in the OCI console.
- Click on Edit.
- Enable Auto Scaling for both CPU and storage.
- Specify the minimum and maximum resources.
5.2: Monitoring Performance
Utilize the Performance Hub feature to monitor real-time database performance. You can view metrics like:
- Active Sessions
- Wait Events
- Resource Consumption
Regads
Osama
Technology Geek 