Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

Image Removed

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

Image Removed

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. 

Image Removed

 

Step 3 is where you will need to provide more details about your RDS database. 

Image Removed

 

From the above screen you an specify the multiple options for your RDS instance:

  1. Specify the license type you want to use
  2. Specify the database version
  3. Select an instance class,  More details here:  http://aws.amazon.com/rds/
  4. Specify if you want to use Multi-AZ deployment
  5. Auto minor version updates
  6. This is the additional database storage that will be required
  7. If you want to use Provisioned IOPS (recommended for Production systems)
  8. Specify your Database SID
  9. 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:

Image Removed 

Step 5 allows you to configure backups if required.  For production systems this is recommended.

Image Removed 

 

Step 6 is a summary screen from where you can review and then start the creation of the RDS instance.

Image Removed 

Once you have started the instance creation the following will be displayed.

Image Removed 

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.

 Image Removed

 

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:

Image Removed 

 

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.

Image Removed

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
languagetext
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
languagetext
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
languagetext
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
languagetext
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:

 

...

languagetext
collapsetrue

...

TNS Entry in tnsnames.ora

 

Code Block
languagetext
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
languagetext
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
languagetext
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.

...