Tag: sql

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)

1. Navigate to the Networking Section.
2. 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

1. 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

1. In the ADB console, navigate to your database and click on DB Connection.
2. Download the Client Credentials (Wallet). This will be a zip file containing the wallet and connection files.

3.2: Set Up SQL Developer

1. Open Oracle SQL Developer.
2. Go to Tools > Preferences > Database > Advanced and set the Use Wallet option to true.
3. In the Connections pane, click on the green + icon to create a new connection.
4. 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
5. 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:

1. Navigate to your Autonomous Database instance in the OCI console.
2. Click on Edit.
3. Enable Auto Scaling for both CPU and storage.
4. 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