For those who attended the Oracle Open World 2019 in San Francisco, California in the USA (if you didn’t check out my compilation video about the event : https://www.youtube.com/watch?v=8bOLbXOJHmAh ) and have had the experience to also attend the KeyNotes probably would prefer to migrate their Oracle database On-Prem to Oracle Cloud Ed2 instead of AWS Amazon EC2 or RDS.
However, as an IT professional, we have to do our best to deliver a high quality of service independently of the provider . So the purpose of this post is to help DBAs to migrate their Oracle Database On-Prem to AWS RDS Amazon.
When you decide to move your database to AWS to be used as an RDS service, you have 3 options to migrate your database which are 2 Oracle products : DataPump and Golden Gate and 1 Amazon product: Data Migration Service.
It is not hard to figure out what is the Amazon’s recommendation and I will show you how to configure your Oracle On-Prem to it.
One of the good thing about the DMS is that you can perform your migration by users. So imagine that you just want to migrate your APPTECH database user into this new AWS RDS Oracle and start to use it from the scratch. . The migration will be according to the Amazon’s option of FULL LOAD + CAPTURE AND APPLY CHANGES which means that after, the user will be fully migrated to the DMS and will keep sending all modifications through archives files to the AWS RDS Oracle instance.
To be able to perform this , one of the pre-requisite that Amazon requires is to enable Supplemental logging in all tables of the chosen user and believe me if you don’t do it the table will not load and it will receive the status “NOT ENABLED” in the stage of the DMS migration tasks and it won’t be migrated.
To perform this requirement you can use these scripts below:
techdatabasket:techdata/scripts/mig> cat add_log_wopk.sql
-- table with primary key
set pages 200
set lines 200
select distinct'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;' CMD FROM DBA_CONSTRAINTS where CONSTRAINT_TYPE <>'P' and OWNER IN('APPTECH');
techdatabasket:techdata/scripts/mig> cat add_log.sql
--table without primary key
set pages 200
set lines 200
select distinct 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;' CMD FROM DBA_CONSTRAINTS where CONSTRAINT_TYPE='P' and OWNER IN('APPTECH');
— Minimal Supplemental Logging (database level):
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Another requirement is to create and give all the permissions to the user so that it will be connected to this source database as Endpoint in the DMS tool. I have chosen the name MIGUSERSOURCE:
SQL> GRANT SELECT ANY TRANSACTION to MIGUSERSOURCE; GRANT SELECT on V_$ARCHIVED_LOG to MIGUSERSOURCE; GRANT SELECT on V_$LOG to MIGUSERSOURCE; GRANT SELECT on V_$LOGFILE to MIGUSERSOURCE; GRANT SELECT on V_$DATABASE to MIGUSERSOURCE; GRANT SELECT on V_$THREAD to MIGUSERSOURCE; GRANT SELECT on V_$PARAMETER to MIGUSERSOURCE; GRANT SELECT on V_$NLS_PARAMETERS to MIGUSERSOURCE; GRANT SELECT on V_$TIMEZONE_NAMES to MIGUSERSOURCE; GRANT SELECT on V_$TRANSACTION to MIGUSERSOURCE; GRANT SELECT on ALL_INDEXES to MIGUSERSOURCE; GRANT SELECT on ALL_OBJECTS to MIGUSERSOURCE; GRANT SELECT on DBA_OBJECTS to MIGUSERSOURCE; (required if the Oracle version is earlier than 11.2.0.3) GRANT SELECT on ALL_TABLES to MIGUSERSOURCE; GRANT SELECT on ALL_USERS to MIGUSERSOURCE; GRANT SELECT on ALL_CATALOG to MIGUSERSOURCE; GRANT SELECT on ALL_CONSTRAINTS to MIGUSERSOURCE; GRANT SELECT on ALL_CONS_COLUMNS to MIGUSERSOURCE; GRANT SELECT on ALL_TAB_COLS to MIGUSERSOURCE; GRANT SELECT on ALL_IND_COLUMNS to MIGUSERSOURCE; GRANT SELECT on ALL_LOG_GROUPS to MIGUSERSOURCE; GRANT SELECT on SYS.DBA_REGISTRY to MIGUSERSOURCE; GRANT SELECT on SYS.OBJ$ to MIGUSERSOURCE; GRANT SELECT on DBA_TABLESPACES to MIGUSERSOURCE; GRANT SELECT on ALL_TAB_PARTITIONS to MIGUSERSOURCE; GRANT SELECT on ALL_ENCRYPTED_COLUMNS to MIGUSERSOURCE; GRANT EXECUTE ON SYS.DBMS_CRYPTO TO MIGUSERSOURCE;
This one is a very important grant to the DMS to be able to validate BLOB data types in the DMS tool:
SQL> GRANT EXECUTE ON SYS.DBMS_CRYPTO TO MIGUSERSOURCE;
You also need to give a grant of SELECT on all tables to be migrated to the user MIGUSERSOURCE and for this permission, I have created this procedure:
CREATE PROCEDURE GIVEGRANTSELECT( username VARCHAR2, grantee VARCHAR2) AS BEGIN FOR tech IN ( SELECT owner, table_name FROM all_tables WHERE owner = username ) LOOP EXECUTE IMMEDIATE 'GRANT SELECT ON '||tech.owner||'.'||tech.table_name||' to ' || grantee; END LOOP; END; /
You will be asked to provide the name of the user that you want these grants to be given and in our case, it is the user: MIGUSERSOURCE.
The output will be something like this:
EXEC grant_select('TABLE1','MIGUSERSOURCE');
EXEC grant_select('TABLE2','MIGUSERSOURCE');
This other grant is just my personal choice. As I want that the user has full permission and to avoid any other permission problem I would give the DBA grant and revoke it once the migration is done:
SQL> GRANT DBA TO MIGUSERSOURCE;
For the capture and apply changes (CDC) you also need the following privileges:
SQL> EXECUTE on DBMS_LOGMNR TO MIGUSERSOURCE; SELECT on V_$LOGMNR_LOGS TO MIGUSERSOURCE; SELECT on V_$LOGMNR_CONTENTS TO MIGUSERSOURCE;
LOGMINING /* For Oracle 12c and higher. */:
SQL> GRANT LOGMINING TO MIGUSERSOURCE;
The next grant is up to you if you have already given the supplemental logging in all tables of the user but if you want that DMS to add supplemental logging instead you can use the following procedure below:
SQL> CREATE PROCEDURE GRANTALTERTB( username VARCHAR2, grantee VARCHAR2) AS BEGIN FOR tech IN ( SELECT owner, table_name FROM all_tables WHERE owner = username ) LOOP EXECUTE IMMEDIATE 'GRANT ALTER ON '||tech.owner||'.'||tech.table_name||' to ' || grantee; END LOOP; END; /
The output will be something like this:
EXEC grant_alterTB('TABLE1','MIGUSERSOURCE');
EXEC grant_alterTB('TABLE2','MIGUSERSOURCE');
Or simply by:
SQL> grant ALTER ANY TABLE to MIGUSERSOURCE;
It is also important to determine the maximum LOB column of the migrated tables by running below command if you want to migrate the user using the option “LIMITED LOB size” during the DMS migration task stage:
SQL> select table_name,column_name,data_type,'select (max(length(' || COLUMN_NAME || '))/(1024)) as "Size in KB" from '
|| owner || '.' || TABLE_NAME ||';' "querytogetlobsize" from dba_tab_cols where owner='&SCHEMA' and data_type in ('CLOB'','BLOB','NCLOB');
The last recommendation now is to configure a backup routine of your archives On-Prem that must be able to keep the archives time enough to be shipped to the AWS RDS Oracle instance. If you don’t do it you will face the following problem during the migration phase:
Last failure message Last Error Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:xxx] [xxxx] Oracle CDC stopped; Error executing source loop; Stream component failed at subtask 0, component st_0_XXXXXXXXXXXX ; Stream component 'xxxxxxx' terminated [reptask/replicationtask.c:xxx] [xxxxxxx] Stop Reason FATAL_ERROR Error Level FATAL
Note: DMS will only push Table DDL and PK , all other additional objects may be created either before the CDC part , for instance indexes, or after the whole operation.
Now the next post it will be about the target AWS RDS Oracle instance.
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)