How To Create Oracle Database In Aws

Deploy Oracle database in Amazon RDS

Create database Security Group

First, create a Security Group for your database:

Navigate to AWS console -> Services.
Go to the Compute section and select EC2.
Go to the Network & Security section and select Security Groups.
Click Create Security Group.
After created, select the Security Group and go to Actions -> Add/Edit Tags.
In the Key text box type Name, and in the Value text box enter OracleDB.
Click Save.

Create option group

If you would like to make a SSL connection to your Oracle DB, you should first create an Option group:

Navigate to AWS console -> Services.
Go to the Database section and select RDS.
Go to Option groups and click Create group.
After the group is created, select it and click Add option.
On the Option drop-down list, select SSL.
Specify the SSL Port.
Select Security Group for which this option is enabled - select the previously created Database Security Group.
On the Apply immediately options, select Yes.

Create Oracle database

Navigate to AWS Console -> Services.
Go to the Database section and select RDS.
Go to Instances and click Launch DB Instance.
Select Oracle -> Enterprise Edition.
For Use Case, select Production.
Specify the DB Details.
Add your DB Instance Identifier and Master user credentials.

Click Next.
Configure the Advanced Settings:
- Launch the Database in your VPC.
- Choose whether your database to be publicly accessible or no.
  Select Yes if you want to allow EC2 instances and devices outside the VPC that hosts the DB instance to connect to this DB instance. If you select No, Amazon RDS will not assign a public IP address to the DB instance, and no EC2 instance or devices outside of the VPC will be able to connect. If you select Yes, you must also select one or more VPC security groups that specify which EC2 instances and devices can connect to the DB instance. Click here to learn more.
- Select the Availability Zone from the current region in which you want the DB instance created.
- Select the Database Security Group you created as described in the Create database Security Group subtopic.
Add your Database Options:
- Set a Database name.
- Specify a Database port.
- Select the previously created SSL Option Group.
- Leave Character set name to the default value: AL32UTF8.
- Select Enable Encryption and follow the instructions to supply your Master key IDs and aliases.

Add your preferences for Monitoring and Maintenance.

When you finish with your setup, click Launch DB instance.

Parameter Groups

You manage your DB engine configuration through the use of parameters in a DB parameter group.

DB parameter groups act as a container for engine configuration values that are applied to one or more DB instances.

You cannot modify the parameter settings of a default DB parameter group; you must create your own DB parameter group to change parameter settings from their default value according to the Requirements for Oracle Databases section in theSecureTransport 5.4 Installation Guide.

Create a parameter group

Navigate to AWS console -> Services.
Go to the Database section and select RDS.
Go to Parameter groups and click Create parameter group.
Fill in the fields and click Create.
After creation, select the parameter group and go to Parameter group actions and click Edit.
Find the parameter that you would like to change and click Edit parameters.
Insert the desired value and then click Save changes.
Change the following parameters according to the database requirements:
- db_cache_size: 1GB or larger
- open_cursors: at least 1000
- processes: 1000 or more

Learn more about DB Parameter Groups.

Assign the parameter group to your database

Navigate to RDS Instances.
Select your database and then go to Instance actions -> Modify.
Go to the Database options section and select your DB parameter group from the drop-down list.
Save and apply changes immediately.
Restart your database.

Connect to your Oracle database

Navigate to AWS console -> Services.
Go to the Database section and select RDS.
Navigate to Instances.
Select your newly created Database and then go to Instance Actions ->See Details.
Under the Security and network section, see Endpoint: you will need to copy & paste it in the next step.
Use Oracle SQL Developer on your Administration Host.
Add new connection and provide values for the following options:
- Connection Name: type a name that describes the connection
- Username: name of the database administrator
- Password: password for the database administrator
- Hostname: paste the Endpoint
- Port: 1521
- SID: ORCL

For further reference, see Connecting to Oracle DB.

Create tables and set ownership of the Oracle database

Use the following script:

CREATE SMALLFILE TABLESPACE "ST_DATA"

DATAFILE SIZE 5000M AUTOEXTEND ON NEXT 12K MAXSIZE 8000M

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE SMALLFILE TABLESPACE "ST_FILETRACKING"

DATAFILE SIZE 5000M AUTOEXTEND ON NEXT 12K MAXSIZE 8000M

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE SMALLFILE TABLESPACE "ST_SERVERLOG"

DATAFILE SIZE 5000M AUTOEXTEND ON NEXT 12K MAXSIZE 8000M

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER ST IDENTIFIED BY ST;

grant connect to ST;

grant create operator to ST;

grant create procedure to ST;

grant create sequence to ST;

grant create session to ST;

grant create table to ST;

alter user ST quota unlimited on ST_DATA;

alter user ST quota unlimited on ST_FILETRACKING;

alter user ST quota unlimited on ST_SERVERLOG;

alter user ST quota unlimited on USERS;

Obtain the Database certificate and a Distinguished Name

Execute the following command from one of your RHEL Instances which have access to the database:

openssl s_client -connect <host>:<ssl_port>

where <host> is the Endpoint.

Create directories for the exported files

If you would like to run Maintenance applications and export logs, you will have to create a directory on the RDS service. This directory will contain the exported files.

To create a new directory, you can use the Amazon RDS procedure dsadmin.rdsadmin_util.create_directory.

The following example creates a new directory named ST_DMPDIR:

exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'ST_DMPDIR');

You can list the directories by querying DBA_DIRECTORIES. The system chooses the actual host pathname automatically. The following example gets the directory path for the directory named ST_DMPDIR:

select DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME='ST_DMPDIR';

The master user for the DB instance has read and write privileges in the new directory, and can grant access to other users. You will need to grant read and write privileges to your SecureTransport user.

Execute privileges are not available for directories on a DB instance. Directories are created in your main data storage space and will consume space and I/O bandwidth.

List Files in a DB Instance Directory

You can use the Amazon RDS procedure rdsadmin.rds_file_util.listdir to list the files in a directory.

The following example lists the files in the directory named ST_DMPDIR:

select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'ST_DMPDIR'));

Learn more about Creating directories in RDS.