...
- Amazon Oracle RDS instance created and configured
- Tablespaces created as required, with sufficient space to hold replicated data
- Dbvisit Replicate repository is small and can be stored in the default USERS tablespace. Or if required can be stored in a different tablespace.
- Make sure that you pre-create all the required tablespaces before you start the Dbvisit Replicate configuration. Dbvisit Replicate will not create tablespaces and datafiles; this should be done as pre-requisite.
- DBA User Account and Password of DBA user in RDS database
- TNS Alias details required (tnsnames.ora entries to RDS instance should be available and added to the Mine and Apply server’s tnsnames.ora files and tested to ensure connections to the DBA user account (dbvdba) can be established.
NOTE: The rest of this document will provide you with an overview of the creation of an example environment. Note that in this example the source environment will be simulated by using an Oracle Database server in AWS (making use of an Amazon EC2 instance).
Creating the Amazon RDS (Target) Database
The first step is to create an RDS Amazon Database. This can be done from the AWS management console.
First login to AWS management console, and select the RDS service. You will now be taken to the RDS dashboard as shown above. We would like to create an RDS Oracle Instance. This is basically an Oracle Database environment running on an EC2 instance, but you do not get access to the EC2 instance. You will only get a DBA account for the database. You will also not get the SYS or SYSTEM passwords. Click on Launch a DB instance from the RDS dashboard to start the creation process
The screen above will show you the instances you can create. In this example I will create a Oracle Standard Edition One RDS environment. So Click on Select next to the Oracle-SE1 as indicated by the number 1 above.
In the next screen I am specifying that this is not a production system, but for production system. For more details on Multi-AZ deployment options please see the RDS online documentation.
Step 3 is where you will need to provide more details about your RDS database.
From the above screen you an specify the multiple options for your RDS instance:
- Specify the license type you want to use
- Specify the database version
- Select an instance class, More details here: http://aws.amazon.com/rds/
- Specify if you want to use Multi-AZ deployment
- Auto minor version updates
- This is the additional database storage that will be required
- If you want to use Provisioned IOPS (recommended for Production systems)
- Specify your Database SID
- The Master username and password will be your Administration (DBA) account that can be used to perform DBA based tasks on the RDS database.
Once the above is filed in click on “Next Step” and you will be presented with Step 4, from where you can provide additional configuration details:
Step 5 allows you to configure backups if required. For production systems this is recommended.
Step 6 is a summary screen from where you can review and then start the creation of the RDS instance.
Once you have started the instance creation the following will be displayed.
When you close the above screen, you will be taken back to the RDS management console from where you can monitor your RDS instance creation.
Note. You will not get access to the Operating System (OS) running the RDS instance nor will you get the SYS/SYSTEM user passwords. Do take not of the Admin account you provided – in this case dbvadmin. This is you DBA account.
Example summary of RDS Database following creation:
Another security group – DB Security Group option needs to be configured to allow your connections. You can either add your IP address. So if you are connecting from home use your ADSL external IP or you can make use of the EC2 Security Groups if already defined. Below is an example.
The “EndPoint” as can be seen above in the Description can be used for creating a TNS alias entry for the RDS database. Below is an example:
Endpoint Details: awsdb.cu1fmi2n7nvj.us-east-1.rds.amazonaws.com:1521
TNS Entry in tnsnames.ora
Code Block | ||
---|---|---|
| ||
AWSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = awsdb.cu1fmi2n7nvj.us-east-1.rds.amazonaws.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = awsdb)
)
) |
Testing Connection
Code Block | ||
---|---|---|
| ||
sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 30 12:11:14 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect dbvadmin@awsdb
Enter password:
Connected.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES YES YES
1 SYSAUX YES YES YES
2 UNDO_T1 YES YES YES
4 USERS YES YES YES
5 RDSADMIN YES YES YES
3 TEMP NO YES YES
|
Preparing the Amazon RDS (Target) Database
This step is required to prepare the RDS database for the data to be loaded from the Source database. In this example we will load the Swingbench OE schema from the PROD database into the RDS database DBVRDS into the tablespace DATA. The first step is to create the DATA tablespace. In the example below we added a 5G datafile.
Code Block | ||
---|---|---|
| ||
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- --------------------------- --- --- --- ---
0 SYSTEM YES YES YES
1 SYSAUX YES YES YES
2 UNDO_T1 YES YES YES
4 USERS YES YES YES
5 RDSADMIN YES YES YES
3 TEMP NO YES YES
6 rows selected.
SQL> create tablespace DATA datafile size 5G autoextend on next 200M maxsize 10G;
Tablespace created.
SQL> select tablespace_name, file_name, bytes/1024/1024 from dba_data_files order by 1;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
---------------- --------------------------------------------------------- ---------------
DATA /rdsdbdata/db/AWSDB_A/datafile/o1_mf_data_94kfndhh_.dbf 5120
RDSADMIN /rdsdbdata/db/AWSDB_A/datafile/o1_mf_rdsadmin_8r2stzcm_.dbf 1
SYSAUX /rdsdbdata/db/AWSDB_A/datafile/o1_mf_sysaux_8r2sb6jm_.dbf 100
SYSTEM /rdsdbdata/db/AWSDB_A/datafile/o1_mf_system_8r2sb398_.dbf 300
UNDO_T1 /rdsdbdata/db/AWSDB_A/datafile/o1_mf_undo_t1_8r2sb91t_.dbf 200
USERS /rdsdbdata/db/AWSDB_A/datafile/o1_mf_users_8r2sb9sl_.dbf 100
6 rows selected. |
Pre-Create Swingbench OE Schema in RDS (Target)
In this test example we will be using Swingbench to generate transactions in the OE (Order Entry) schema on the source database.
The next step is to create the OE schema in the Amazon RDS Database, only the user and permissions, no table structures are pre-created as this will be done as part of the data instantiation.
Code Block | ||
---|---|---|
| ||
create user oe identified by oe default tablespace data temporary tablespace temp;
GRANT QUERY REWRITE TO "OE";
GRANT CREATE MATERIALIZED VIEW TO "OE";
GRANT CREATE VIEW TO "OE";
GRANT UNLIMITED TABLESPACE TO "OE";
GRANT ALTER SESSION TO "OE";
GRANT "RESOURCE" TO "OE";
GRANT create session to oe; |
The OE schema in the source (XE) database consist out of the following:
...
language | text |
---|---|
collapse | true |
...
TNS Entry in tnsnames.ora
Code Block | ||
---|---|---|
| ||
AWSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = awsdb.cu1fmi2n7nvj.us-east-1.rds.amazonaws.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = awsdb)
)
) |
Testing Connection
Code Block | ||
---|---|---|
| ||
sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 30 12:11:14 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect dbvadmin@awsdb
Enter password:
Connected.
|
Preparing the Amazon RDS (Target) Database
This step is required to prepare the RDS database for the data to be loaded from the Source database. In this example we will load the Swingbench OE schema from the PROD database into the RDS database DBVRDS into the tablespace DATA. The first step is to create the DATA tablespace. In the example below we added a 5G datafile.
Code Block | ||
---|---|---|
| ||
SQL> select * from v$tablespace; TS# NAME 65536 OE INC BIG FLA ENC ---------- --------------------------- --- PRODUCT_INFORMATION_PK --- --- --- 0 SYSTEM INDEX 65536 OE WAREHOUSES_PK YES YES YES INDEX1 SYSAUX YES YES YES 65536 15 rows selected. SQL>2 selectUNDO_T1 owner, object_type, count(1) from dba_objects where owner='OE' group by owner, object_type; OWNER YES YES YES 4 USERS OBJECT_TYPE COUNT(1) ---------------------------- ------------------- ---------- OE YES YES YES 5 RDSADMIN INDEX YES YES YES 63 OETEMP NO YES SEQUENCEYES 6 rows selected. SQL> create tablespace DATA datafile size 5G autoextend on next 200M maxsize 810G; OETablespace created. SQL> select tablespace_name, file_name, bytes/1024/1024 from dba_data_files order by 1; TABLESPACE_NAME FILE_NAME TABLE 9 OE BYTES/1024/1024 ---------------- --------------------------------------------------------- --------------- DATA /rdsdbdata/db/AWSDB_A/datafile/o1_mf_data_94kfndhh_.dbf VIEW 5120 RDSADMIN /rdsdbdata/db/AWSDB_A/datafile/o1_mf_rdsadmin_8r2stzcm_.dbf 1 SYSAUX 2 OE /rdsdbdata/db/AWSDB_A/datafile/o1_mf_sysaux_8r2sb6jm_.dbf PACKAGE BODY 100 SYSTEM /rdsdbdata/db/AWSDB_A/datafile/o1_mf_system_8r2sb398_.dbf 1 OE 300 UNDO_T1 /rdsdbdata/db/AWSDB_A/datafile/o1_mf_undo_t1_8r2sb91t_.dbf PACKAGE200 USERS /rdsdbdata/db/AWSDB_A/datafile/o1_mf_users_8r2sb9sl_.dbf 1100 6 rows selected. |
Creating Amazon EC2
...
Instances
In this example we created an AWS EC2 “dbvrds02” will be configured to perform the APPLY related tasks inside the AWS environment. In an ideal configuration this instance should be in the same region and availability zone as the RDS database environment. Also ensure there is sufficient resources on this system.
Note this is an example, where the source database is Oracle Express with the Swingbench Order Entry environment loaded to simulate transaction load.
Instance 1: dbvrds01
Environment Summary:
...
.
...
Instance 2: dbvrds02
This environment will only run an Oracle Instant Client installation with Dbvisit Replicate installed.
...