Amazon RDS Oracle
Below link to refer for hevodata
https://docs.hevodata.com/sources/databases/oracle/amazon-rds-oracle/
Oracle on Amazon RDS
Amazon RDS supports DB instances that run the following versions and editions of Oracle Database:
· Oracle Database 21c (21.0.0.0)
· Oracle Database 19c (19.0.0.0)
· Oracle Database 12c Release 2 (12.2.0.1)
· Oracle Database 12c Release 1 (12.1.0.2)
Note: Oracle Database 12c is on a deprecation path. Oracle Corporation will no longer provide patches for Oracle Database 12c Release 1 (12.1) and Oracle Database 12c Release 2 (12.2) after their end-of-support dates. For more information, see Oracle Database 12c with Amazon RDS.
RDS for Oracle Database 11g and Oracle Database 18c (18.0.0.0) are legacy versions that are no longer supported.
You can't use SYS, SYSTEM, or other Oracle-supplied administrative accounts.
Prerequisites
· Oracle database version is 12c and above.
· Redo Log replication is enabled, if Pipeline mode is Redo Log.
o Set up Redo Logs for Replication à A redo log is a collection of log files that record information about modifications made to data objects on an Oracle server instance. Oracle LogMiner uses redo logs to track these modifications and determine the rows requiring updates in the Destination system.
· Hevo’s IP addresses are whitelisted. The database user must have CREATE/MANAGE SECURITY GROUPS privileges in Amazon RDS to do this.
· SELECT privileges are granted to the database user.
· Database hostname and port number of the Source instance are available.
To set up redo logs for replication, connect to your Oracle server and perform the following steps:
You need to enable archiving for redo logs.
To do this:
Check the current log mode. This should be ARCHIVELOG.
SELECT LOG_MODE FROM "V$DATABASE";
Enable ARCHIVELOG mode if the current log mode is NOARCHIVELOG.
BEGIN
rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 72);
END;
Note: The minimum value for archive log retention hours is 72. The archive log retention must be 72 hours at a minimum. This avoids any data loss that may occur due to downtimes in the Source database.
Supplemental logging ensures that the Oracle server logs all the columns of every changed Event.
1 Check if supplemental logging is enabled:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM "V$DATABASE"
This returns either of the following values:
YES: Represents that the supplemental logging is enabled.
IMPLICIT: Represents that the supplemental logging is disabled.
2 If the value returned in the previous step is IMPLICIT, enable supplemental logging of primary key columns :
BEGIN
rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');
END;
3 Optionally, if you’re using Oracle 12, grant the following permission:
GRANT LOGMINING TO "<user_name>";
Runs the following query to fetch the data periodically:
SELECT * FROM some_table WHERE updated_timestamp_column > last_polled_time
AND updated_timestamp_column < Now() - delay
ORDER BY updated_timestamp_column ASC
LIMIT 500000
And, you want to fetch data using the following query and query mode as Delta - Timestamp and timestamp column name as updated_ts (from the table employee):
SELECT u.id, u.name, u.updated_ts, e.user_id, e.dept_name, e.updated_ts FROM user u INNER JOIN employee e ON u.id = e.id
Then, you must specify the query as:
https://docs.hevodata.com/data-ingestion/pipeline-modes/#redo-log
Data Replication requires Oracle minimal global supplemental logging to be enabled.
To enable supplemental logging, execute one of the following SQL statements:
Adding Supplmental Logging
For regular Oracle database installations:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
For Amazon RDS for Oracle:
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
-- Adding supplemental logging.
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD');
-- The following example enables supplemental logging for all fixed-length maximum size columns.
begin
rdsadmin.rdsadmin_util.alter_supplemental_logging(
p_action => 'ADD',
p_type => 'ALL');
end;
/
-- To check currently supplemental logging enabled status
select name,
supplemental_log_data_min,
supplemental_log_data_all, -- ALL
supplemental_log_data_fk, -- FOREIGN KEY
supplemental_log_data_pk, -- PRIMARY KEY
supplemental_log_data_ui, -- UNIQUE
supplemental_log_data_pl, -- PROCEDURAL
supplemental_log_data_sr -- Indicates whether the database is enabled for subset database replication
from v$database;
select minimal, all_column, foreign_key, primary_key, unique_index, procedural, subset_rep
from dba_supplemental_logging;
-- Dropping supplemental logging.
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'DROP');
-- Adding supplemental logging for Primary Keys
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD', p_type='PRIMARY KEY');
-- Adding supplemental logging for All Fixed Length Maximum Size Columns
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD', p_type='ALL');
https://www.hvr-software.com/docs/6/requirements/source-and-target-requirements/oracle-requirements/oracle-as-source/capture-from-oracle-using-logminer/amazon-rds-for-oracle
-- find tables which have a primary key.
select at.TABLE_NAME from all_tables at where not exists (select 1 from all_constraints ac where ac.owner = at.owner and ac.table_name = at.table_name and ac.constraint_type = 'P') and at.owner = '<SCHEMA>';
-- This will list the tables with a primary key, you can then construct your supplemental logging statement based on the column which has the primay key.
-- ALTER TABLE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS
SELECT DISTINCT (a.table_name) FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE not in('P') and a.owner ='<SCHEMA>';
-- This will list all the tables without a primary key, these tables need supplemental logging on all columns.
-- If you run the following and then execute all statements generated, ensure that supplemental logging is enabled on ALL TABLES and COLUMNS.
-- Which will cause a greater performance hit. YMMV.
-- select 'ALTER TABLE '||table_name||' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;' from all_tables where owner = ''
Constraints can be turned on and off.
declare
begin
for c1 in (select y1.table_name, y1.constraint_name from user_constraints y1, user_tables x1 where x1.table_name = y1.table_name order by y1.r_constraint_name nulls last) loop
begin
dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
execute immediate ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
end;
end loop;
-- Uncomment to truncate the table after disabling the constraint.
-- for t1 in (select table_name from user_tables) loop
-- begin
-- dbms_output.put_line('truncate table '||t1.table_name || ';');
-- execute immediate ('truncate table '||t1.table_name);
-- end;
-- end loop;
for c2 in (select y2.table_name, y2.constraint_name from user_constraints y2, user_tables x2 where x2.table_name = y2.table_name order by y2.r_constraint_name nulls first) loop
begin
dbms_output.put_line('alter table '||c2.table_name||' enable constraint '||c2.constraint_name || ';');
execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
end;
end loop;
end;
Disable/Enable Triggers
CREATE OR REPLACE PROCEDURE ALTER_ALL_TRIGGERS(status VARCHAR2) IS
CURSOR c_tr IS (SELECT 'ALTER TRIGGER ' || trigger_name AS stmnt FROM user_triggers);
BEGIN
IF status NOT IN ('ENABLE', 'enable', 'DISABLE', 'disable') THEN
DBMS_OUTPUT.PUT_LINE('ONLY ''ENABLEDISABLE'' ACCEPTED AS PARAMETERS');
RAISE VALUE_ERROR;
END IF;
FOR tr IN c_tr LOOP
EXECUTE IMMEDIATE tr.stmnt || ' ' || status;
END LOOP;
END;
https://dev.to/aws-builders/dms-configuration-for-oracle-to-rds-migration-1gfm
In general, use Oracle LogMiner for migrating your Oracle database unless you have one of the following situations:
You need to run several migration tasks on the source Oracle database.
The volume of changes or the redo log volume on the source Oracle database is high, or you have changes and are also using Oracle ASM.
For an Oracle source endpoint to connect to the database for a change data capture (CDC) task, you might need to specify extra connection attributes. This can be true for either a full-load and CDC task or for a CDC-only task.
To use Binary Reader to access the redo logs, add the following extra connection attributes.
Use extra connection attributes to define additional endpoint settings for AWS DMS
useLogMinerReader=N;useBfile=Y;
useLogMinerReader=N;useBfile=Y;exposeViews=true;connectionTimeout=300;
Use the following format for the extra connection attributes to access a server that uses ASM with Binary Reader. useLogMinerReader=N;useBfile=Y;asm_user=asm_username;asm_server=RAC_server_ip_address:port_number/+ASM;
Set the source endpoint Password request parameter to both the Oracle user password and the ASM password, separated by a comma as follows.
oracle_user_password,asm_user_password
Where the Oracle source uses ASM, you can work with high-performance options in Binary Reader for transaction processing at scale. These options include extra connection attributes to specify the number of parallel threads (parallelASMReadThreads) and the number of read-ahead buffers (readAheadBlocks). Setting these attributes together can significantly improve the performance of the CDC task. The following settings provide good results for most ASM configurations.
useLogMinerReader=N;useBfile=Y;asm_user=asm_username;asm_server=RAC_server_ip_address:port_number/+ASM; parallelASMReadThreads=6;readAheadBlocks=150000;
Error:[TASK_MANAGER ]E: Archived Redo log with the sequence 241** does not exist, thread 1 [1022316]
Soloution :
#Cause: It failed due to a missing archive at source. The archive was not present in dest_id 1 which caused DMS to fail.
#Fix: To remedy this, you can add an extra connection attribute (archivedLogDestId=1;ExtraArchivedLogDestIds=[2,3,4]} to the source endpoint of the task and resumed the task.
--To Find the distinct archive log dest id, use below query select distinct dest_id from v$archived_log;
SELECT name, dest_id, thread#, sequence#, first_time, deleted, status FROM v$archived_log WHERE sequence#='&sequence_number';
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html
To access the redo logs using the AWS DMS Binary Reader, grant the following privileges to the Oracle user specified in the Oracle endpoint connection settings.
GRANT SELECT on v_$transportable_platform to db_user;
-– Grant this privilege if the redo logs are stored in Oracle Automatic Storage Management (ASM) and AWS DMS accesses them from ASM.
GRANT CREATE ANY DIRECTORY to db_user;
-– Grant this privilege to allow AWS DMS to use Oracle BFILE read file access in certain cases. This access is required when the replication instance doesn't have file-level access to the redo logs and the redo logs are on non-ASM storage.
GRANT EXECUTE on DBMS_FILE_TRANSFER to db_user;
-– Grant this privilege to copy the redo log files to a temporary folder using the CopyToTempFolder method.
GRANT EXECUTE on DBMS_FILE_GROUP to db_user;