Before using AWS DMS for your Oracle source database, you perform the following basic tasks on the source Oracle database:
Set up Oracle DMS user privileges – You must provide an Oracle user account for AWS DMS user. The user account must have read/write privileges on the Oracle database. Be aware of these considerations:
When granting privileges, use the actual name of objects (for example, V_$OBJECT including the underscore), not the synonym for the object (for example, V$OBJECT without the underscore). For information about access privileges to grant to the DMS user, see Using an Oracle Database as a Source for AWS DMS in the DMS documentation.
Also make sure that the IAM user that you log in to use the DMS service has all the permissions necessary to view the logs. For more information on these permissions, see IAM Permissions Needed to Use AWS DMS in the DMS documentation.
Enable archive logs – To use Oracle with AWS DMS, the source database must be in ARCHIVELOG mode.
Enable supplemental logs – If you are planning to use a full load plus CDC task, set up supplemental logging to capture the changes for replication.
When working with an on-premises Oracle source, be aware of the following
An Oracle database can run in one of two modes—NOARCHIVELOG or ARCHIVELOG. By default, the database is created in NOARCHIVELOG mode. In ARCHIVELOG mode, the database makes copies of all ONLINE REDO logs after they are filled up
The commands following check to see if the database has been altered to run in ARCHIVELOG mode.
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter database archivelog;
SQL> startup nomount;
ORACLE instance started.
SQL> alter database mount;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
sql> select log_mode from v$database
ARCHIVELOG
-----------------------------------LOB CHECKING SIZE IN ORACLE----------------------------------------
SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER = 'ORDEV1' AND SEGMENT_NAME= '<lob segment name>' ;
set pages 999
set lines 400
select col.owner as schema_name,col.table_name,count(*) as column_count from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner and col.table_name = t.table_name
where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE') -- excluding some Oracle maintained schemas
and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') group by col.owner, col.table_name order by col.owner, col.table_name;