...
| 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@RDSTGT 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. 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. |
Creating Amazon EC2 Instances
In this example we created an AWS EC2 that 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.
...
Ensure you have the correct TNS_ADMIN details in the APPLY ddc file. In this test example environment Oracle Express is used on the APPLY server. The location of the TNS_ADMIN variable in the RDSPOCTEST_RDS-APPLY.ddc is:
| Code Block | ||
|---|---|---|
| ||
memory_set TNS_ADMIN /u01/app/oracle/product/11.2.0/xedb_1/network/admin |
Execute prod-all.sh script on dbvrdsrep01
Run the RDSPOCTEST_RDS-all.sh script to initialize Dbvisit Replicate and load the dbvrep repository in both the source and target. It is important that the steps above be executed before running this step.
...
| Code Block | ||
|---|---|---|
| ||
[oracle@dbvrds01 RDSPOC]TEST_RDS]$ $ ./RDSPOCTEST_RDS-all.sh Setting up Dbvisit Replicate configuration Configure database XESOURCEDB... This check fails if the DBID is not the expected one... Ok, check passed. Configure database AWSDBRDSTGT... This check fails if the DBID is not the expected one... Ok, check passed. Object grants for database XESOURCEDB... Object grants for database AWSDBRDSTGT... Setting up the configuration Initializing......done WARN-1850: No DDC DB available, dictionary table does not exist. DDC loaded from database (0 variables). Dbvisit Replicate version 2.56.0801.33413900 Copyright (C) Dbvisit Software Limited. All rights reserved. DDC file /home/oracle/RDSPOCTEST_RDS/config/RDSPOCTEST_RDS-onetime.ddc loaded. MINE: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists) APPLY: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists) dbvrep> #clear the no-DDC-DB-available warning dbvrep> engine clear previous warnings dbvrep> set ON_WARNING SKIP Variable ON_WARNING set to SKIP for process *. dbvrep> set ON_ERROR EXIT Variable ON_ERROR set to EXIT for process *. dbvrep> ENGINE SWITCH_REDOLOG dbvrep> ENGINE SETUP MINE DROP DICTIONARY 0 dictionary objects dropped. dbvrep> ENGINE SETUP MINE CREATE DICTIONARY dbvrep> ENGINE SETUP MINE LOAD DICTIONARY Supplemental logging on database set. Loading dictionary table DBRSCOL$ Loading dictionary table DBRSIND$ Loading dictionary table DBRSOBJ$ Loading dictionary table DBRSTAB$ Loading dictionary table DBRSUSER$ Loading dictionary table DBRSV_$DATABASE ID of mine procesprocess is 5B57FC602E363D28-2978AAD3-11E3-A0ADB759-58D8E5C48113C37E51CB262D. If not using DDC in database, set MINE_UNIQUE_ID to this value. dbvrep> ENGINE SETUP APPLY DROP DICTIONARY 0 dictionary objects dropped. dbvrep> ENGINE SETUP APPLY CREATE DICTIONARY dbvrep> ENGINE SETUP APPLY LOAD DICTIONARY dbvrep> ENGINE PREPARE_DP SETUP CLEAR dbvrep> ENGINE SETUP PAIR MINE AND APPLY 1 applier SCN set. dbvrep> SET APPLY.INSTANTIATE_SCN NOW Variable INSTANTIATE_SCN set to NOW for process APPLY. dbvrep> ENGINE SUPPLEMENTAL LOGGING SCHEMA OESCOTT ENABLE PRIMARY KEY dbvrep> ENGINE SWITCH_REDOLOG dbvrep> ENGINE WAIT_SCN_FLIP Waited 2 seconds until scn_to_timestamp changed. dbvrep> #single-scn instantiation: lock all tables and schemas dbvrep> ENGINE LOCK SCHEMAS OESCOTT Locking all schemas. Lock done. dbvrep> #single-scn instantiation: unlock all tables and schemas, but keep the SCN dbvrep> ENGINE LOCK RELEASE LOCKS dbvrep> dbvrep> #prepare the tables (we use OFFLINE as neither MINE not APPLY is running; with OFFLINE we won't wait on network timeout) dbvrep> PREPARE OFFLINE SCHEMA OESCOTT Table OESCOTT.CUSTOMERSBONUS instantiated at SCN 12298271034905 Table OESCOTT.INVENTORIESDEPT instantiated at SCN 12298271034905 Table OESCOTT.LOGONEMP instantiated at SCN 1229827 Table OE.ORDERENTRY_METADATA instantiated at SCN 1229827 Table OE.ORDERS instantiated at SCN 1229827 Table OE.ORDER_ITEMS instantiated at SCN 1229827 Table OE.PRODUCT_DESCRIPTIONS instantiated at SCN 12298271034905 Table OESCOTT.PRODUCT_INFORMATIONLOADTEST instantiated at SCN 12298271034905 Table OESCOTT.WAREHOUSESSALGRADE instantiated at SCN 12298271034905 dbvrep> #single-scn instantiation: unlock all tables and schemas, forget the SCN (so it does not affect any further PREPARE statements) dbvrep> ENGINE LOCK CLEAR SCN dbvrep> ENGINE SWITCH_REDOLOG dbvrep> #prepare script for instantiation dbvrep> ENGINE PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR FILE /home/oracle/RDSPOCTEST_RDS/APPLY.sh DBLINK XESOURCEDB USERID dbvadmin/xxxx@AWSDBrds_admin/dbvisit2014@RDSTGT Created Data Pump script /home/oracle/RDSPOCTEST_RDS/APPLY.sh, using network import. dbvrep> create ddcdb from ddcfile DDC loaded into database (266319 variables). dbvrep> set ON_WARNING SKIP Variable ON_WARNING set to SKIP for process *. dbvrep> set ON_ERROR SKIP Variable ON_ERROR set to SKIP for process *. OK-0: Completed, but some operation(s) raised warnings and/or errors. successfully. These steps are required after the RDSPOCTEST_RDS-all.sh script runs: 1) Create the necessary directory(ies) on the servers: dbvrds02 ec2-54-82-47-176.compute-1.amazonaws.com: /home/oracle/RDSPOCTEST_RDS 2) Copy the DDC files to the server(s) where the processes will run: dbvrds02 ec2-54-82-47-176.compute-1.amazonaws.com: /home/oracle/RDSPOC/RDSPOCTEST_RDS/TEST_RDS-APPLY.ddc dbvrds01dbvlin210.dbvisit.co.nz: /home/oracle/RDSPOC/RDSPOCTEST_RDS/TEST_RDS-MINE.ddc 3) Review that path to dbvrep executable is correct in the run scripts: /home/oracle/RDSPOC/RDSPOCTEST_RDS/TEST_RDS-run-dbvrds01dbvlin210.dbvisit.co.nz.sh /home/oracle/RDSPOC/RDSPOC-run-dbvrds02.sh TEST_RDS/TEST_RDS-run-ec2-54-82-47-176.compute-1.amazonaws.com.sh 4) Copy the run script to the server(s) where the processes will run: dbvrds01dbvlin210.dbvisit.co.nz: /home/oracle/RDSPOC/RDSPOCTEST_RDS/TEST_RDS-run-dbvrds01dbvlin210.dbvisit.co.nz.sh dbvrds02ec2-54-82-47-176.compute-1.amazonaws.com: /home/oracle/RDSPOC/RDSPOC-run-dbvrds02/TEST_RDS/TEST_RDS-run-ec2-54-82-47-176.compute-1.amazonaws.com.sh 5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes. 6) Make sure the data on apply are in sync as of time when setup was run. Scripts for Data Pump/export/DDL were created as requested: /home/oracle/RDSPOCTEST_RDS/APPLY.sh Create referenced database links (if any) before running the scripts. 7) Start the replication processes on all servers: dbvrds01dbvlin210.dbvisit.co.nz: /home/oracle/RDSPOC/RDSPOCTEST_RDS/TEST_RDS-run-dbvrds01dbvlin210.dbvisit.co.nz.sh dbvrds02ec2-54-82-47-176.compute-1.amazonaws.com: /home/oracle/RDSPOC/RDSPOCTEST_RDS/TEST_RDS-run-dbvrds02ec2-54-82-47-176.compute-1.amazonaws.com.sh 8) Start the console to monitor the progress: /home/oracle/RDSPOCTEST_RDS/start-console.sh The above list is stored in /home/oracle/RDSPOCTEST_RDS/Nextsteps.txt. |
Once the above scripts are created, copy the RDSPOC-rundbvrds02.sh and RDSPOC-APPLY.ddc to the dbvrds02 server, example:
...