On my last article, I wrote about how to configure your Oracle On-Prem database to be migrated to an AWS RDS Amazon (If you didn’t see this post check it out : https://www.techdatabasket.com/oracle-and-aws-rds-for-oracle-tip-how-to-configure-your-oracle-on-prem-to-be-migrated-to-aws-rds-oracle-a-guide-do-use-amazon-data-migration-service-dms/).
Some people may ask the version of my Oracle On-Prem or even the class or version of my AWS RDS instance but the fact is if you are going to perform a Homogeneous migration as it is in this example (From Oracle to AWS Oracle RDS) the steps are the same. However, as it is in all migrations you have to be aware of what you have to check as pre-requisite. For instance, once I migrate an Oracle Enterprise Edition 12c to an AWS Oracle RDS SE2 and for those who have been working with the Oracle database for many years know that when you migrate from an Enteprise Edition to a Standard Edition you may have some features disabled. In the last-mentioned case such features as Basic Compression , Deferred Segment and Oracle Enterprise User Security were options that wouldn’t work in my AWS RDS Oracle instance. So , once again, be aware of all features when you decide to migrate your database.
Apart from the topic that I covered above, another issue that you must be aware of is the limitations of the DMS service. However, I will be covering this topic in the next post where I will be writing exclusively about the DMS tool and how to start the tasks to start the migration. In this post the goal it will be cover the configuring of the AWS RDS Oracle instance. To configure an AWS RDS Oracle instance you have to create one but in this post, I won’t be covering this part because, believe me, once you are inside of the AWS Console it takes about 5 minutes to go through the options and create your instance. The steps can be summarized like this ” Services -> Amazon RDS -> Create Database (in this tab you will have to select the database creation method, the engine type, version, templates, settings (where you will to type the name of the instance and also the user owner of the database), class, storage, Multi-AZ deployment (I highly recommend you use this option in case of Production databases), Connectivity (the VPC you are going to use) and the database authentication method”. After all, at the end of the screen, you can see the total cost to use this service per month.
Besides of the information that I have written in the article above to configure your source, you do need to get the Data Definition Language (DDL) to create the structures of the tablespaces, users and profiles from your source to run in your target AWS RDS Oracle.
Anyway, here is how you can extract the DDL from your source: (https://www.techdatabasket.com/how-to-extract-all-tablespaces-ddl-on-oracle-database/):
— Source database:
SQL> set heading off;
SQL> set echo off;
SQL> set pages 2000
SQL> set lines 2000
SQL> set long 99999
SQL> spool ddl_tablespaces.sql
SQL> select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) from dba_tablespaces ;
##HERE WILL BE SHOWN ALL TABLESPACES DLL ABOUT YOUR DATABASE##
SQL> spool off
Extract the DLL of the profiles and create it in your target database:
— Source database:
select dbms_metadata.get_ddl('PROFILE', profile) as profile_ddl
from (select distinct profile
from dba_profiles)
where profile like upper('%&1%');
Extract the DLL of the users and create it in your target database: (https://www.techdatabasket.com/how-to-extract-ddl-for-all-users-on-oracle-database/)
— Source database:
SQL> set heading off;
SQL> set echo off;
SQL> set pages 2000
SQL> set long 99999
SQL> set lines 2000
SQL> spool ddl_users.sql
SQL> select dbms_metadata.get_ddl('USER',username) FROM dba_users;
##HERE WILL BE SHOWN ALL DLL USERS OF YOUR DATABASE##
SQL> spool off
Extract the DDL of the tables and create it in your target database:
(Example to list all tables that belongs to the user BRUNORS)
— Source database:
SQL> select owner, table_name from dba_tables where owner='BRUNORS; BRUNORS TECHDATB01 BRUNORS TECHDATB02
Creating a script that it will create the DDL of the tables TECHDATB01 and TECHDATB02 of the user BRUNORS:
— Source database:
set long 1000000;
set longchunksize 32000;
set linesize 32000;
set trimspool on;
set heading off;
set pages 0;
set newpage none;
set embedded on;
set tab off;
set feedback off;
set echo off;
SELECT dbms_metadata.get_ddl('TABLE','TECHDATB01','BRUNORS')||';' from dual ;
SELECT dbms_metadata.get_ddl('TABLE','TECHDATB02','BRUNORS')||';' from dual ;
set sqlblanklines on;
set define off;
set sqlprefix off;
set blockterminator OFF;
In the “as-is” migration, I like to get all the grants from the source and also apply it in the target and after work in the security for each user but this is just an optional step.
If you also would like to get all grants from your source and apply to your target, here it is:
— Source database:
SELECT t0.INSTANCE_NAME||' ' || t0.HOST_NAME||' ' || t0.VERSION|| ' '||t0.STATUS Comando from V$instance t0
union all
select 'grant '||t.privilege|| ' on '||t.owner||'.'||t.table_name||' to '||grantee
||case when t.grantable = 'YES' then ' with grant option' else null end
||';'
from dba_tab_privs t
WHERE grantee not in ('DBA','PUBLIC','IMP_FULL_DATABASE','SELECT_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE','ORACLE_OCM','SRVMON','AQ_ADMINISTRATOR_ROLE','APPQOSSYS','EXP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK','DBFS_ROLE','GATHER_SYSTEM_STATISTICS','SYSTEM','DELETE_CATALOG_ROLE','XDB','WMSYS','EXFSYS','DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','SYS','AQ_USER_ROLE','HS_ADMIN_SELECT_ROLE','OEM_MONITOR','LOGSTDBY_ADMINISTRATOR','DBSNMP','XDBADMIN','WM_ADMIN_ROLE','RESOURCE','CONNECT')
Union all
select 'grant '||t1.privilege||' to '||grantee
||case when t1.admin_option = 'YES' then ' with grant option' else null end
||';'
from dba_sys_privs t1
WHERE grantee not in ('DBA','PUBLIC','IMP_FULL_DATABASE','SELECT_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE','ORACLE_OCM','SRVMON','AQ_ADMINISTRATOR_ROLE','APPQOSSYS','EXP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK','DBFS_ROLE','GATHER_SYSTEM_STATISTICS','SYSTEM','DELETE_CATALOG_ROLE','XDB','WMSYS','EXFSYS','DATAPUMP_EXP_FULL_DATABASE',
'DATAPUMP_IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','SYS','AQ_USER_ROLE','HS_ADMIN_SELECT_ROLE','OEM_MONITOR','LOGSTDBY_ADMINISTRATOR','DBSNMP','XDBADMIN','WM_ADMIN_ROLE','RESOURCE','CONNECT');
To connect to your source database you must have a configured network between the source and the target and to connect to your target database you can use the following sintax:
"sqlplus 'user_name@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns_name)(PORT=port))(CONNECT_DATA=(SID=database_name)))' "
techdatabaskethost> sqlplus 'techdataowner@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx.xxxxxx.xx-region-1.rds.amazonaws.com)(PORT=1533))(CONNECT_DATA=(SID=techtarget)))'
The output will be something like this:
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 28 09:58:46 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
Once you are connected in your AWS RDS Oracle instance you must create the same tablespaces that exist in the source database. What you have to know is that Amazon recommends creating your tablespace using Big files, not Small files. Why? Because if you create your tablespace to use Small files you can’t resize this datafile instead you will have to create a new data file. For instance, if you have created a small file with 2MB you can’t resize this datafile afterwards. To increase the tablespace that contains the last datafile you must create a new Small file. However, if you create your tablespace as Bigfile tablespace, you create just 1 datafile and the AWS RDS Oracle will resize this data file automatically.
If you decide to follow Amazon’s recommendation to create your tablespace the sintax will be something like this:
— target:
CREATE BIGFILE TABLESPACE TECHDATABASKETP DATAFILE '/u01/oracle/data/techdatabasket01.dbf' SIZE 50G
— target:
Create the users, tablespaces and profiles in your target using the scripts extracted. Also, for full-load and CDC-enabled task, Amazon recommends that you drop primary key indexes, secondary indexes, referential integrity constraints, and data manipulation language (DML) triggers before the start of the task and create them once the full load phase has completed. If you do not perform this task the error ORA-02266: unique/primary keys in table referenced by enabled foreign keys will stop your task. So to disable the constraints in your target AWS RDS Oracle, use the script below:
Select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
from all_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and b.table_name = '&table_name';
The output will be something like this: (Example of the table TECHDATATB)
Enter value for table_name: TECHDATATB old 6: and b.table_name = '&table_name' new 6: and b.table_name = 'TECHDATATB' 'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME||';' ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- alter table BRUNO.TECHDATA_KRTSE disable constraint TECHDATA_KRTSE_ID_FK; alter table BRUNO.TECHDATA_RESW disable constraint FKTECHDATA_RESW7263s;
If you are running FULL LOAD and CDC right away, I would suggest you create the index in the target after the FULL LOAD to speed up the CDC process. To get the DDL of the indexes in your source database to run in your AWS RDS Oracle you can use the script below: (example user BRUNORS):
— source
set long 1000000;
set longchunksize 32000;
set linesize 32000;
set trimspool on;
set heading off;
set pages 0;
set newpage none;
set embedded on;
set tab off;
set feedback off;
set echo off;
select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in ('BRUNORS');
set sqlblanklines on;
set define off;
set sqlprefix off;
set blockterminator OFF; ;
Although I have covered many important topics about how to configure your AWS RDS Oracle to receive data from the target using DMS, I also recommend you to take a look at Amazon’s documentation to understand deep the procedure. Therefore, I have finished this article and the next one will be about the DMS tool, its tasks and all the limitation about the data types and options while using this tool.
References:
Common DBA Tasks for Oracle DB Instances (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
Creating and Sizing Tablespaces (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Database.html#Appendix.Oracle.CommonDBATasks.CreatingTablespacesAndDatafiles)
Connecting to a DB Instance Running the Oracle Database Engine (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToOracleInstance.html)
Using an Oracle Database as a Target for AWS Database Migration Service (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Oracle.html)
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)