AWS -Best practices for migrating an Oracle database to Amazon RDS PostgreSQL

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.

On-premises Oracle sources

When working with an on-premises Oracle source, be aware of the following

Enable archive log mode

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;