...
| Code Block | ||
|---|---|---|
| ||
SOURCE_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sourceserver.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SOURCE_DB)
)
)
EC2_TARGET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = awsdb.cu1fmi2nxxxx.us-east-1.amazonaws.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EC2_TARGET)
)
) |
Pre-Create Swingbench OE Schema in EC2 (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 EC2 Database, only the user and permissions, no table structures are pre-created as this will be done as part of the data instantiation.
USER USER In this example we are doing the OE schema
| 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:
edit below.
| Code Block | ||||
|---|---|---|---|---|
| ||||
SQL> select owner, segment_name, segment_type, bytes from dba_segments where owner='OE';
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
-------------- --------------------------- ------------------------------ ----------
OE CUSTOMERS TABLE 27262976
OE INVENTORIES TABLE 19922944
OE LOGON TABLE 3145728
OE ORDERENTRY_METADATA TABLE 65536
OE ORDERS TABLE 23068672
OE ORDER_ITEMS TABLE 33554432
OE PRODUCT_DESCRIPTIONS TABLE 327680
OE PRODUCT_INFORMATION TABLE 262144
OE WAREHOUSES TABLE 65536
OE CUSTOMERS_PK INDEX 6291456
OE LOGON_PK INDEX 2097152
OE ORDERS_PK INDEX 7340032
OE PRODUCT_DESCRIPTIONS_PK INDEX 65536
OE PRODUCT_INFORMATION_PK INDEX 65536
OE WAREHOUSES_PK INDEX 65536
15 rows selected.
SQL> select owner, object_type, count(1) from dba_objects where owner='OE' group by owner, object_type;
OWNER OBJECT_TYPE COUNT(1)
---------------------------- ------------------- ----------
OE INDEX 6
OE SEQUENCE 8
OE TABLE 9
OE VIEW 2
OE PACKAGE BODY 1
OE PACKAGE 1
6 rows selected.
|
Creating Amazon EC2 Instances (Apply)
In this example we created one AWS EC2 instances instance to show the concept of loading data into EC2 using Replicate. The Source database could be onis on-premise. If this example I created the “SOURCE_DB” on premise. The The EC2 target instance (EC2_TARGET) will be configured to perform the APPLY inside the AWS environment.
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:SOURCE_DB SOURCE
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
SOURCE_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbvrds01sourceserver.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XESOURCE_DB)
)
)
EC2_TARGET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = awsdb.cu1fmi2n7nvjcu1fmi2xxxx.us-east-1.rds.amazonaws.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = awsdbEC2_TARGET)
)
) |
Ensure the firewall on both environments (IPTABLES) as well as the AWS security groups is configures to allow communication between these systems. Example:
...