column FIRST_CHANGE# format 999999999999999999
column NEXT_CHANGE# format 999999999999999999
column CURRENT_SCN format 999999999999999999
prompt _______________________________________________________________
prompt AWS DMS Support Collector for Oracle
prompt
prompt Version 2.1
prompt
prompt
prompt This script will collect information on your database to help troubleshoot an issue you are having with the DMS service.
prompt
prompt Before running the script, you should read and understand the sql which will be executed from both a performance and security perspective.
prompt
prompt If you are not comfortable executing or sharing the data from any of the sql, you may comment/remove that SQL.
prompt
prompt Once the script is complete, it will display the html output file name.
prompt
prompt Please review the information which you are sending to aws and if comfortable, upload it using the instructions found on the following link...
prompt https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SupportScripts.html
prompt
prompt
prompt
prompt
pause Press ENTER to continue to ctrl-C to abort
prompt
prompt
accept v_owner prompt 'Please enter the Schema name you wish to Migrate/Replicate : '
accept v_connector prompt 'Please enter the Oracle User Name which DMS will use to connect to your database : '
accept v_days prompt 'Please enter the number of days data you wish to examine [default 3] : ' DEFAULT 3
prompt Executing script...
prompt
prompt
set termout off
set linesize 420
set pagesize 100
set markup html on spool on entmap off
set verify off
-- Get date/time and db name for output file.
column dt new_val X
select to_char(sysdate,'yyyy-mm-dd-hh24-mi-ss') dt from dual;
column v_dbname new_val Y
select name v_dbname from v$database;
spool dms_support_oracle-&&X-&&Y..html
--spool dms_support_script_oracle.html
set markup html off
--
-- This Section contains the table of contents.
--
--
-- prompt
DMS Oracle Support Bundle
prompt DMS Oracle Support Bundle
prompt
--
-- This Section contains the actual sql to extract the information.
--
-- Please review and feel free to comment out anything you are not comfortable sharing, or running against your database.
--
prompt AWS DMS Support bundle script for Oracle
-- New Major heading :
prompt
prompt Overview :
prompt
prompt This is the output from the DMS Support script for Oracle.
prompt
prompt Please upload to AWS Support via a Customer Case.
prompt
prompt
set heading off
select 'Current Database Time : ' || sysdate || '' from dual;
prompt
prompt
prompt Schema name being Migrated/Replicated : &v_owner
prompt
prompt
prompt Oracle User Name which DMS will use to connect : &v_connector
prompt
prompt
prompt Number of days to analyse : &v_days
prompt
prompt
set heading on
-- New Major heading :
prompt
prompt Database Configuration
-- New Minor heading and block :
set markup html off
prompt Database Name :
prompt previous : top : next
set markup html on spool on entmap off
-- START SQL
select NAME,OPEN_MODE,DATABASE_ROLE,CURRENT_SCN,DB_UNIQUE_NAME from v$database;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Database Version :
prompt previous : top : next
prompt
prompt Please check the following documentation for supported database versions...
prompt Oracle Source
prompt Oracle Target
set markup html on spool on entmap off
-- START SQL
select * from v$version;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Operating System version
prompt previous : top : next
set markup html on spool on entmap off
-- START SQL
select dbms_utility.port_string from dual;
-- END SQL
set markup html off
prompt
prompt
column VALUE format 999,999,999,999,999,999
-- New Minor heading and block :
prompt SGA Size :
prompt previous : top : next
set markup html on spool on entmap off
-- START SQL
select INST_ID,NAME,VALUE from gv$sga order by 1,2;
prompt values of db memory component :
select name,sum(value)/1024/1024/1024 "SIZE in Gb" from v$parameter where name in ('sga_max_size','sga_target','shared_pool_size','db_cache_size','java_pool_size','large_pool_size','streams_pool_size','pga_aggregate_target','memory_max_target','memory_target')
GROUP BY name order by 1 desc;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Is this a RAC database :
prompt previous : top : next
prompt
prompt If more than one row is returned below then it is a RAC system.
prompt If this is the case, then the following documentation may be of use...
prompt Best practices for migrating an Oracle database
prompt
set markup html on spool on entmap off
-- START SQL
select INSTANCE_NUMBER,INSTANCE_NAME,STATUS,THREAD#,ARCHIVER,DATABASE_STATUS,INSTANCE_ROLE,
ACTIVE_STATE from gv$instance order by 1,2;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Is Dataguard enabled :
prompt previous : top : next
prompt If Active Dataguard, or a physical standby opened in read only mode is in use, it is possible to to use the standby as a source for DMS.
prompt
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
prompt AWS DMS now supports Binary Reader for Amazon RDS for Oracle and Oracle Standby as a source
prompt
set markup html on spool on entmap off
-- START SQL
select * from GV$DATAGUARD_CONFIG;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Redo log sizes :
prompt previous : top : next
set markup html on spool on entmap off
-- START SQL
select INST_ID,GROUP#,THREAD#,SEQUENCE#,(bytes/1024/1024) "SIZE_MB",ARCHIVED,STATUS,FIRST_CHANGE#, to_char(NEXT_CHANGE#) AS "NEXT_CHANGE#" from gv$log order by 1,2,3;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Supplemental logging and forced logging at database level :
prompt previous : top : next
prompt To capture change data, AWS DMS requires supplemental logging to be enabled on your source database for AWS DMS.
prompt Minimal supplemental logging must be enabled at the database level.
prompt AWS DMS also requires that identification key logging be enabled.
prompt This option causes the database to place all columns of a row's primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).
prompt You can set this option at the database or table level.
prompt
prompt For additional details please see the following section of our documentation...
prompt Configure Your Oracle Source Database
prompt Using an Oracle database as a source
prompt Best practices for migrating an Oracle database
prompt Troubleshooting
set markup html on spool on entmap off
-- START SQL
select NAME,RESETLOGS_TIME,OPEN_MODE,DATABASE_ROLE,DATAGUARD_BROKER,GUARD_STATUS,
SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
FORCE_LOGGING,CURRENT_SCN,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_PL
from v$database;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Forced logging at tablespace level :
prompt previous : top : next
prompt In Force logging mode Oracle database must write the redo records even when NOLOGGING is used with DDL Statements.
prompt It will force the write of REDO records even when no-logging is specified.
set markup html on spool on entmap off
-- START SQL
select TABLESPACE_NAME,FORCE_LOGGING from dba_tablespaces order by 1;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Is ASM in use :
prompt previous : top : next
prompt If the database is using Oracle ASM to store its redo and archive log files, ongoing replication is enabled,
prompt and DMS binary reader is enabled, certain non-default configuration settings need to be configured.
prompt i.e. you need to correctly configure your Extra Connection Attributes to replicate.
prompt e.g. useLogminerReader=N;copyToTempFolder=/backups/dms;archivedLogDestId=1;accessTempFolderDirectly=N;useBfile=Y;asm_user=bjanshego;asm_server=10.61.4.41/+ASM;deleteProccessedArchiveLogs=Y;archivedLogsOnly=Y
prompt
prompt In addition to this, ff you are using DMS versions 3.x or later, pay particular attention to settings such as parallelASMReadThreads and readAheadBlocks for performance.
prompt
prompt For additional details please see the following section of our documentation...
prompt How to Migrate from Oracle ASM to AWS using AWS DMS
prompt Using an Oracle database as a source for AWS DMS
prompt Best practices for migrating an Oracle database
prompt Step 6: Create AWS DMS Source and Target Endpoints
set markup html on spool on entmap off
-- START SQL
select count(*) from GV$ASM_DISK_STAT;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt NLS settings :
prompt previous : top : next
prompt If NLS settings differ between the source and target database, this can occasionally cause issue.
prompt e.g.
prompt The error "ORA-12899: value too large for column column-name" is often caused by a mismatch in the character sets used by the source and target databases or when NLS settings differ between the two databases.
prompt A common cause of this error is when the source database NLS_LENGTH_SEMANTICS parameter is set to CHAR and the target database NLS_LENGTH_SEMANTICS parameter is set to BYTE.
prompt
set markup html on spool on entmap off
-- START SQL
select PARAMETER,VALUE from V$NLS_PARAMETERS order by 1;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Is it a container databases :
prompt previous : top : next
prompt AWS DMS doesn't support multi-tenant container databases (CDB).
prompt
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
prompt Note : This sql may fail on pre 12C databases.
set markup html on spool on entmap off
-- START SQL
SELECT CDB FROM V$DATABASE;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Is it a pluggable database :
prompt previous : top : next
prompt AWS DMS does not support connections to a pluggable database (PDB) using Oracle LogMiner. To connect to a PDB, access the redo logs using Binary Reader.
prompt
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
prompt Note : This sql may fail on pre 12C databases.
set markup html on spool on entmap off
-- START SQL
SELECT count(*) FROM DBA_PDBS ORDER BY PDB_ID;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Is GoldenGate enabled :
prompt previous : top : next
prompt To check whether GoldenGate is enabled on source db or not. As Oracle source with GG enabled having HCC compression would not be supported for prior DMS v3.5.
prompt
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
prompt Note : This sql may fail on pre 12C databases.
set markup html on spool on entmap off
-- START SQL
SELECT NAME, VALUE FROM v$parameter WHERE NAME ='enable_goldengate_replication';
-- END SQL
set markup html off
prompt
prompt
--------------------------------------------------------------------------
-- New Major heading :
prompt
prompt Size Details
-- New Minor heading and block :
prompt Database size :
prompt previous : top : next
prompt This section shows the total size of all datafiles in the database. (not all data will be getting replicated)
prompt
prompt If there is a large amount of data to be moved, several options can help to improve the speed.
prompt Firstly, the number of tables being migrated at the same time can be increased from the default of 8 up to 49, using the MaxFullLoadSubTasks setting.
prompt
prompt Other options include using DMS parallel features against large tables, by either utalising native oracle partitioning if possible, or the DMS range-segmentation option.
prompt
prompt Another tool that can be used, is to set the table load order, to force the largest table to be processed in the first batch of tables.
prompt For additional details please see the following section of our documentation...
prompt AWS Database Migration Service improves migration speeds by adding support for parallel full load
prompt AWS Database Migration Service Improves Migration Speeds by Adding Support for Parallel Full Load and New LOB Migration Mechanisms
prompt Best practices for AWS Database Migration Service
prompt Full-load task settings
set markup html on spool on entmap off
-- START SQL
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Schema Sizes :
prompt previous : top : next
prompt This section shows the total size of all SCHEMAS in the database. (not all data will be getting replicated)
prompt
prompt If there is a large amount of data to be moved, several options can help to improve the speed.
prompt Firstly, the number of tables being migrated at the same time can be increased from the default of 8 up to 49, using the MaxFullLoadSubTasks setting.
prompt
prompt Other options include using DMS parallel features against large tables, by either utalising native oracle partitioning if possible, or the DMS range-segmentation option.
prompt
prompt Another tool that can be used, is to set the table load order, to force the largest table to be processed in the first batch of tables.
prompt For additional details please see the following section of our documentation...
prompt AWS Database Migration Service improves migration speeds by adding support for parallel full load
prompt AWS Database Migration Service Improves Migration Speeds by Adding Support for Parallel Full Load and New LOB Migration Mechanisms
prompt Best practices for AWS Database Migration Service
prompt Full-load task settings
set markup html on spool on entmap off
-- START SQL
select owner,round(sum(bytes)/1024/1024/1024) "SIZE IN GB" from dba_segments
where owner not in('ANONYMOUS','APEX_030200','OLAPSYS' ,'APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','BI','CTXSYS','DBSNMP','DIP','DVF','DVSYS','EXFSYS','FLOWS_FILES','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','HR','IX','LBACSYS','MDDATA','MDSYS','OE','ORACLE_OCM' ,'ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSBACKUP','SYSDG','SYSKM','SYSTEM','WMSYS','XDB','SYSMAN','RMAN','RMAN_BACKUP','MT')
group by owner order by 2 desc;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Total size of migration schema tables :
prompt previous : top : next
prompt This section shows the total size of the SCHEMA being migrated.
prompt
prompt If there is a large amount of data to be moved, several options can help to improve the speed.
prompt Firstly, the number of tables being migrated at the same time can be increased from the default of 8 up to 49, using the MaxFullLoadSubTasks setting.
prompt
prompt Other options include using DMS parallel features against large tables, by either utalising native oracle partitioning if possible, or the DMS range-segmentation option.
prompt
prompt Another tool that can be used, is to set the table load order, to force the largest table to be processed in the first batch of tables.
prompt For additional details please see the following section of our documentation...
prompt AWS Database Migration Service improves migration speeds by adding support for parallel full load
prompt AWS Database Migration Service Improves Migration Speeds by Adding Support for Parallel Full Load and New LOB Migration Mechanisms
prompt Best practices for AWS Database Migration Service
prompt Full-load task settings
set markup html on spool on entmap off
-- START SQL
select SEGMENT_TYPE, round(sum(BYTES)/1024/1024/1024,2) "Total Size Gigs" from dba_segments where owner=upper('&v_owner')
and SEGMENT_TYPE in('TABLE SUBPARTITION','TABLE PARTITION','NESTED TABLE','LOB PARTITION','TABLE')
group by SEGMENT_TYPE order by 2;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Total size of migration schema LOB Segments :
prompt previous : top : next
prompt This section shows the total size of lob segments which are owned by the Schema being migrated.
prompt
prompt Lobs can often be the source of performance issues with a migration.
prompt Understanding the databases lob sizes and the various options within DMS is key to their migration performance.
prompt
prompt To help get optimal settings, read the documentation below and also consider the following...
prompt Decide between the 3 modes available to move lobs (Inline LOB,Full LOB,Limited LOB)
prompt Consider using Per table LOB settings.
prompt Consider using Tables load order during full load, to make sure large lob tables are loaded first.
prompt
prompt For additional details please see the following section of our documentation...
prompt Setting LOB support for source databases in an AWS DMS task
prompt How can I improve the speed of an AWS DMS task that has LOB data?
prompt AWS Database Migration Service improves migration speeds by adding support for parallel full load and new LOB migration mechanisms
prompt Best practices for AWS Database Migration Service
set markup html on spool on entmap off
-- START SQL
select SEGMENT_TYPE, round(sum(BYTES)/1024/1024/1024,2) "Total Size Gigs" from dba_segments where owner=upper('&v_owner')
and SEGMENT_TYPE in('LOBSEGMENT')
group by SEGMENT_TYPE order by 2;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Top objects by size :
prompt previous : top : next
prompt This section lists the tables owned by the Schema being migrated, including and ordered by size.
prompt The column named L, indicates if the table contains a lob column.
prompt
prompt Note : The size column includes the size of associated lob segments.
set markup html on spool on entmap off
-- START SQL
select TABLE_NAME,contain_lob "Lob Segment", sum("Total Size Gigs") "Total Size Gigs" from(
select (case when TABLE_NAME is null then TAB_NAME else TABLE_NAME end) TABLE_NAME,contain_lob, "Total Size Gigs"
from(
select size_list.tab_name, lob_list.table_name ,lob_list.contain_lob,sum(size_list."Total Size Gigs")/1024/1024/1024 "Total Size Gigs"
from
(select SEGMENT_NAME, table_name , 'Y' as contain_lob from dba_lobs where OWNER=upper('&v_owner') ) lob_list
right outer join
(select segment_name, segment_name as tab_name, 'N' as contain_lob , sum(bytes) "Total Size Gigs" from dba_segments where owner=upper('&v_owner') and SEGMENT_TYPE in('TABLE SUBPARTITION','TABLE PARTITION','NESTED TABLE','LOB PARTITION','LOBSEGMENT','TABLE') group by segment_name, segment_name) size_list
on lob_list.segment_name = size_list.segment_name
group by size_list.tab_name, lob_list.table_name ,lob_list.contain_lob
)
) group by TABLE_NAME,contain_lob order by 3 desc;
-- END SQL
set markup html off
prompt
prompt
------------------------------------------------------------------------------------------
-- New Major heading :
prompt
prompt Database Load
-- New Minor heading and block :
prompt Redo generated per day :
prompt previous : top : next
prompt The database redo generation rate is important when performing ongoing replication.
prompt If CDC source latency is being encountered, it may be caused by the redo rate.
prompt
prompt If this is the case, there are several things which can be investigated to help DMS scale as required.
prompt e.g.
prompt Spread your tables across multiple DMS tasks.
prompt Decide between LogMiner and BInary reader.
prompt If the database in general has a high redo rate, but DMS is only replicating a low % of the data, then logminer may be better.
set markup html on spool on entmap off
-- START SQL
select trunc(completion_time) rundate ,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from v$archived_log
where completion_time > sysdate- &v_days
group by trunc(completion_time) order by 1;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Redo rate per hour :
prompt previous : top : next
prompt The database redo generation rate is important when performing ongoing replication.
prompt If CDC source latency is being encountered, it may be caused by the redo rate.
prompt
prompt If this is the case, there are several things which can be investigated to help DMS scale as required.
prompt e.g.
prompt Spread your tables across multiple DMS tasks.
prompt Decide between LogMiner and BInary reader.
prompt If the database in general has a high redo rate, but DMS is only replicating a low % of the data, then logminer may be better.
set markup html on spool on entmap off
-- START SQL
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24';
select to_date(completion_time,'DD-MM-YYYY HH24') rundate ,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER HOUR (MB)"
from v$archived_log
where completion_time > sysdate- &v_days
group by to_date(completion_time,'DD-MM-YYYY HH24') order by 1;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt CDC SQL performance :
prompt previous : top : next
prompt This section contains statistics on how sql issued by DMS is performing inside the database.
prompt
prompt The first 50 characters of the sql text are retrieved. This is enough to identify which sql command is being executed. But will not contain any customer information.
set markup html on spool on entmap off
-- START SQL
select * from(
select inst_id,sql_id,sum(ROWS_PROCESSED),sum(EXECUTIONS), (sum(ROWS_PROCESSED)/sum(EXECUTIONS)) "Rows/Execution",
avg(round((ROWS_PROCESSED) / (FETCHES ) )) "Rows per fetch",
avg(round((FETCHES) / (EXECUTIONS ) )) "Fetches per execution",
avg(BUFFER_GETS/executions) "BuffGets/exec",
avg(ELAPSED_TIME/executions) "Elaps/Exec",
substr(trim(sql_text),1,50) sqlText
from gv$sql where PARSING_SCHEMA_NAME=upper('&v_connector')
and module like('repctl%')
and sql_id is not null
and executions > 0
and fetches > 0
group by inst_id,sql_id,substr(trim(sql_text),1,50)
order by 4 desc
)
where rownum < 51;
-- END SQL
set markup html off
prompt
prompt
-------------------------------------------------------------------------------------------------
-- New Major heading :
prompt
prompt Table Load
-- New Minor heading and block :
prompt Table modifications breakdown (insert/update/delete) :
prompt previous : top : next
prompt This section reads from the dba_tab_modifications view. This contains a cumulative number of all of the DML (inserts updates and deletes) for a specific table.
prompt The values are reset after the table is analysed and the views data is not updated immediately.
prompt The output is included here as a lightweight way to see the type of operations being performed on the tables and to help get an idea of which tables receive the most amount of DML.
set markup html on spool on entmap off
-- START SQL
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,(INSERTS + UPDATES + DELETES) "TOTAL CHANGES"
from dba_tab_modifications where TABLE_OWNER =upper('&v_owner') and (TABLE_NAME not like 'BIN$%' and TABLE_NAME not like 'DR$%')
order by 7 desc;
-- END SQL
set markup html off
prompt
prompt
-------------------------------------------------------------------------------------------------
-- New Major heading :
prompt
prompt Table Details
-- New Minor heading and block :
prompt Lob information :
prompt previous : top : next
prompt This section shows details for the lob segments which are owned by the Schema being migrated. The data is taken from dba_lobs.
prompt
prompt Lobs can often be the source of performance issues with a migration.
prompt Understanding the databases lob sizes and the various options within DMS is key to their migration performance.
prompt
prompt To help get optimal settings, read the documentation below and also consider the following...
prompt Decide between the 3 modes available to move lobs (Inline LOB,Full LOB,Limited LOB)
prompt Consider using Per table LOB settings.
prompt Consider using Tables load order during full load, to make sure large lob tables are loaded first.
prompt
prompt For additional details please see the following section of our documentation...
prompt Setting LOB support for source databases in an AWS DMS task
prompt How can I improve the speed of an AWS DMS task that has LOB data?
prompt AWS Database Migration Service improves migration speeds by adding support for parallel full load and new LOB migration mechanisms
prompt Best practices for AWS Database Migration Service
set markup html on spool on entmap off
-- START SQL
select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,CHUNK,IN_ROW,SECUREFILE
from dba_lobs where owner =upper('&v_owner')
order by OWNER,TABLE_NAME;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Table stastics (num of rows,avg row length etc.) :
prompt previous : top : next
prompt This section provides information from dba_tables and gives an idea of the number of rows in a table, among other data.
prompt The data in this table is only as good as the table statistics collected by the databaase. If they are out of date, then the data will be less accurate.
set markup html on spool on entmap off
-- START SQL
column owner format a15
column DEGREE format a8
select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED,PARTITIONED,LOGGING,trim(DEGREE) DEGREE,COMPRESSION
from dba_tables where OWNER =upper('&v_owner')
order by 1,2,3;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Partition count per table :
prompt previous : top : next
prompt This section shows if any of the tables being migrated are using Oracle partitioning.
prompt If so, then it is possible to use the DMS parallel features to move large tables faster and more efficiently.
prompt
prompt For additional details please see the following section of our documentation...
prompt AWS Database Migration Service improves migration speeds by adding support for parallel full load
prompt AWS Database Migration Service Improves Migration Speeds by Adding Support for Parallel Full Load and New LOB Migration Mechanisms
prompt Best practices for AWS Database Migration Service
prompt Full-load task settings
set markup html on spool on entmap off
-- START SQL
select * from(
SELECT
TABLE_OWNER,
TABLE_NAME,
count(*)
FROM
DBA_TAB_PARTITIONS
where TABLE_OWNER =upper('&v_owner')
group by TABLE_OWNER,TABLE_NAME
ORDER BY 3 desc)
where rownum < 101;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt SUB Partition count per partition :
prompt previous : top : next
prompt Sub Partitions can be used in the same way as partitions. Please see the section above for more details.
set markup html on spool on entmap off
-- START SQL
select * from(
SELECT
TABLE_OWNER,
TABLE_NAME,
count(*) "Partition Count",
sum(SUBPARTITION_COUNT) "SUB Partition Count"
FROM
DBA_TAB_PARTITIONS
where TABLE_OWNER =upper('&v_owner')
group by TABLE_OWNER,TABLE_NAME
ORDER BY 3 desc)
where rownum < 101;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Existing MViews :
prompt previous : top : next
prompt AWS DMS does not support the ROWID data type or materialized views based on a ROWID column.
set markup html on spool on entmap off
-- START SQL
select OWNER,MVIEW_NAME from dba_mviews
where OWNER =upper('&v_owner')
order by 1,2;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Object Type count :
prompt previous : top : next
prompt THis section identifies the number of columns are defined as which object types.
prompt Note, not all object types can be migrated. And also DMS will not migrate sequences.
set markup html on spool on entmap off
-- START SQL
select OWNER,OBJECT_TYPE,count(*) from dba_objects
where OWNER =upper('&v_owner')
group by OWNER,OBJECT_TYPE order by 3 desc;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Data Type count :
prompt previous : top : next
prompt This section shows a count for each datatype being migrated.
prompt DMS will not migrate certain data types.
prompt
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
select OWNER,DATA_TYPE,count(*)
from dba_tab_columns where
OWNER =upper('&v_owner')
group by OWNER,DATA_TYPE order by 3 desc;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Supplemental logging details :
prompt previous : top : next
prompt To capture change data, AWS DMS requires supplemental logging to be enabled on your source database for AWS DMS.
prompt Minimal supplemental logging must be enabled at the database level.
prompt AWS DMS also requires that identification key logging be enabled.
prompt This option causes the database to place all columns of a row's primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).
prompt You can set this option at the database or table level.
prompt
prompt For additional details please see the following section of our documentation...
prompt Configure Your Oracle Source Database
prompt Using an Oracle database as a source
prompt Best practices for migrating an Oracle database
prompt Troubleshooting
set markup html on spool on entmap off
-- START SQL
select LOG_GROUP_NAME,TABLE_NAME,LOG_GROUP_TYPE,ALWAYS from DBA_LOG_GROUPS where owner=upper('&v_owner') order by 2;
select LOG_GROUP_NAME,TABLE_NAME,COLUMN_NAME,POSITION,LOGGING_PROPERTY from DBA_LOG_GROUP_COLUMNS where OWNER=upper('&v_owner') order by 2,3;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Compressed tables and partitions :
prompt previous : top : next
prompt The following shows any tables for schema &v_owner which have compression enabled.
prompt
prompt Logminer and Binary Reader support different types of compression.
prompt For additional details please see the following section of our documentation...
prompt Supported compression methods for using Oracle as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
prompt Check for compressed table :
select OWNER, TABLE_NAME, COMPRESSION, COMPRESS_FOR from dba_tables where owner=upper('&v_owner') and COMPRESSION !='DISABLED';
prompt Check for compressed partitions :
select TABLE_OWNER, table_name, PARTITION_NAME, COMPRESSION, COMPRESS_FOR from dba_tab_partitions where TABLE_OWNER=upper('&v_owner') and COMPRESSION !='DISABLED';
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Table Encryption :
prompt previous : top : next
prompt AWS DMS has support for transparent data encryption (TDE).
prompt Note : AWS DMS does not support transparent data encryption (TDE) when using Binary Reader with an Amazon RDS Oracle source.
prompt
prompt For additional details please see the following section of our documentation...
prompt Supported encryption methods for using Oracle as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
prompt Checking for encrypted columns :
select owner, table_name, column_name from DBA_ENCRYPTED_COLUMNS where OWNER=upper('&v_owner');
prompt Checking for encrypted tablespaces :
SELECT TABLESPACE_NAME, ENCRYPTED FROM dba_tablespaces WHERE ENCRYPTED ='YES';
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Clustered tables :
prompt previous : top : next
prompt LogMiner supports table clusters for use by AWS DMS. Binary Reader does not. .
prompt
prompt For additional details please see the following section of our documentation...
prompt Oracle Source
set markup html on spool on entmap off
-- START SQL
select table_name,CLUSTER_NAME from dba_tables where owner=upper('&v_owner') and CLUSTER_NAME is not null order by 1;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Nested tables :
prompt previous : top : next
prompt As of version 3.3.1, AWS DMS supports the replication of Oracle tables containing columns that are nested tables or defined types..
prompt
prompt For additional details please see the following section of our documentation...
prompt Replicating nested tables using Oracle as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
select table_name,CLUSTER_NAME from dba_tables where owner=upper('&v_owner') and CLUSTER_NAME is not null order by 1;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt IOT with Overflow tables :
prompt previous : top : next
prompt When you use AWS DMS Binary Reader to access the redo logs, AWS DMS does not support CDC for index-organized tables with an overflow segment.
prompt Alternatively, you can consider using LogMiner for such tables.
prompt
prompt If the table is an index-organized table, then IOT_TYPE is IOT, IOT_OVERFLOW, or IOT_MAPPING.
prompt If the table is not an index-organized table, then IOT_TYPE is NULL.
set markup html on spool on entmap off
-- START SQL
SELECT table_name, iot_type, iot_name FROM dba_tables where owner=upper('&v_owner') and IOT_TYPE is not null;
-- END SQL
set markup html off
prompt
prompt
------------------------------------------------------------------------------
-- New Major heading :
prompt
prompt Archival Information
-- New Minor heading and block :
prompt Archive Destinations :
prompt previous : top : next
set markup html on spool on entmap off
-- START SQL
select DEST_ID,DEST_NAME,STATUS,TARGET,ARCHIVER,DESTINATION,AFFIRM,TYPE,DB_UNIQUE_NAME,APPLIED_SCN from V$ARCHIVE_DEST;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Archive log status :
prompt previous : top : next
prompt DMS recommends that archive logs be kept on disk for 24 hours before being deleted.
set markup html on spool on entmap off
-- START SQL
select NAME,DEST_ID,THREAD#,SEQUENCE#,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME from V$ARCHIVED_LOG where COMPLETION_TIME > sysdate -1;
-- END SQL
set markup html off
prompt
prompt
------------------------------------------------------------------------------
-- New Major heading :
prompt
prompt Permissions
-- New Minor heading and block :
prompt System privileges :
prompt previous : top : next
prompt DMS has different minimum permission requirements depending on if a task is full load, performs CDC, performs validation, is on premise vrs an RDS database.
prompt
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
prompt Is the database RDS Instance?
set markup html on spool on entmap off
-- START SQL
SELECT CASE WHEN F1>0 THEN 'AWS RDS INSTANCE' ELSE 'NON RDS INSTANCE' END AS INSTANCE_TYPE
FROM (
SELECT COUNT(*) AS F1
FROM DBA_PROCEDURES
WHERE OWNER='RDSADMIN' AND OBJECT_NAME='RDSADMIN_UTIL' AND PROCEDURE_NAME='SHOW_CONFIGURATION'
);
-- END SQL
set markup html off
prompt
prompt
prompt Common privileges
set markup html on spool on entmap off
-- START SQL
SELECT required_privilege, is_granted FROM (
with rprivs as (
select column_value as required_privilege
from table(
sys.odcivarchar2list(
'CREATE SESSION',
'SELECT ANY TRANSACTION',
'LOGMINING',
'SYSASM'
)
)
),
asmprivs as (
select count(1) as is_asm from v$asm_disk
)
select
rp.required_privilege,
case
when rp.required_privilege = 'SYSASM' and 0 = (select is_asm from asmprivs) then 'NOT REQUIRED'
when dsp.privilege is null then 'NOT GRANTED'
else 'GRANTED'
end as is_granted
from rprivs rp
left outer join dba_sys_privs dsp
on dsp.privilege = rp.required_privilege and dsp.grantee=upper('&v_connector')
)
UNION
SELECT required_privilege, is_granted FROM (
WITH rprivs2 as (
select column_value as required_privilege
from table(
sys.odcivarchar2list(
'DBA_TABLESPACES',
'DBA_OBJECTS',
'ALL_VIEWS',
'ALL_TAB_PARTITIONS',
'ALL_INDEXES',
'ALL_OBJECTS',
'ALL_TABLES',
'ALL_USERS',
'ALL_CATALOG',
'ALL_CONSTRAINTS',
'ALL_CONS_COLUMNS',
'ALL_TAB_COLS',
'ALL_IND_COLUMNS',
'ALL_LOG_GROUPS',
'V_$ARCHIVED_LOG',
'V_$LOG',
'V_$LOGFILE',
'V_$DATABASE',
'V_$THREAD',
'V_$PARAMETER',
'V_$INSTANCE',
'GV_$PARAMETER',
'V_$NLS_PARAMETERS',
'V_$TIMEZONE_NAMES',
'V_$TRANSACTION',
'V_$CONTAINERS',
'DBA_REGISTRY',
'OBJ$',
'ALL_ENCRYPTED_COLUMNS',
'V_$LOGMNR_LOGS',
'V_$LOGMNR_CONTENTS',
'DBMS_LOGMNR',
'REGISTRY$SQLPATCH',
'V_$STANDBY_LOG',
'ENC$',
'GV_$TRANSACTION',
'DBMS_CRYPTO',
'V_$DATAGUARD_STATS',
'V_$VERSION',
'GV_$ASM_DISKGROUP',
'GV_$DATABASE',
'DBA_DB_LINKS',
'GV_$LOG_HISTORY',
'GV_$LOG',
'DBA_TYPES',
'V_$TRANSPORTABLE_PLATFORM',
'RDSADMIN_UTIL'
)
)
)
select rp.required_privilege, case when dtp.table_name is null then 'NOT GRANTED' ELSE 'GRANTED' end as is_granted
from rprivs2 rp
left outer join dba_tab_privs dtp
on dtp.table_name = rp.required_privilege and dtp.grantee=upper('&v_connector')
)
ORDER BY is_granted desc, required_privilege;
-- END SQL
set markup html off
prompt
prompt
prompt Logminer privileges
set markup html on spool on entmap off
-- START SQL
SELECT required_privilege, is_granted FROM (
with rprivs as (
select column_value as required_privilege
from table(
sys.odcivarchar2list(
'LOGMINING'
)
)
)
select rp.required_privilege, case when dsp.privilege is null then 'NOT GRANTED' ELSE 'GRANTED' end as is_granted
from rprivs rp
left outer join dba_sys_privs dsp
on dsp.privilege = rp.required_privilege and dsp.grantee=upper('&v_connector')
)
UNION
SELECT required_privilege, is_granted FROM (
WITH rprivs2 as (
select column_value as required_privilege
from table(
sys.odcivarchar2list(
'V_$LOGMNR_LOGS',
'DBMS_LOGMNR',
'V_$LOGMNR_CONTENTS'
)
)
)
select rp.required_privilege, case when dtp.table_name is null then 'NOT GRANTED' ELSE 'GRANTED' end as is_granted
from rprivs2 rp
left outer join dba_tab_privs dtp
on dtp.table_name = rp.required_privilege and dtp.grantee=upper('&v_connector')
)
ORDER BY is_granted desc, required_privilege;
-- END SQL
set markup html off
prompt
prompt
prompt Binary reader privileges
set markup html on spool on entmap off
-- START SQL
SELECT required_privilege, is_granted FROM (
with rprivs as (
select column_value as required_privilege
from table(
sys.odcivarchar2list(
'CREATE ANY DIRECTORY'
)
)
),
rdsprivs as (
SELECT COUNT(*) AS is_rds
FROM DBA_PROCEDURES
WHERE OWNER='RDSADMIN' AND OBJECT_NAME='RDSADMIN_UTIL' AND PROCEDURE_NAME='SHOW_CONFIGURATION'
)
select
rp.required_privilege,
case
when rp.required_privilege = 'CREATE ANY DIRECTORY' and 1 = (select is_rds from rdsprivs) then 'NOT REQUIRED'
when dsp.privilege is null then 'NOT GRANTED'
else 'GRANTED'
end as is_granted
from rprivs rp
left outer join dba_sys_privs dsp
on dsp.privilege = rp.required_privilege and dsp.grantee=upper('&v_connector')
)
UNION
SELECT required_privilege, is_granted FROM (
WITH rprivs2 as (
select column_value as required_privilege
from table(
sys.odcivarchar2list(
'DBMS_FILE_TRANSFER',
'DBMS_FILE_GROUP',
'V_$TRANSPORTABLE_PLATFORM',
'DBA_DIRECTORIES'
)
)
)
select rp.required_privilege, case when dtp.table_name is null then 'NOT GRANTED' ELSE 'GRANTED' end as is_granted
from rprivs2 rp
left outer join dba_tab_privs dtp
on dtp.table_name = rp.required_privilege and dtp.grantee=upper('&v_connector')
)
ORDER BY is_granted desc, required_privilege;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Table Grants :
prompt previous : top : next
set markup html on spool on entmap off
-- START SQL
select TABLE_NAME,PRIVILEGE from DBA_TAB_PRIVS where OWNER=upper('&v_owner') and GRANTEE=upper('&v_connector') order by 1;
-- END SQL
set markup html off
prompt
prompt
------------------------------------------------------------------------------
-- New Major heading :
prompt
prompt Potential Issues
-- New Minor heading and block :
prompt Unsupported data types :
prompt previous : top : next
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from dba_tab_columns
where owner=upper('&v_owner')
and ( DATA_TYPE in('BFILE','ROWID','REF','UROWID','ANYDATA','UNDEFINED')
or
( DATA_TYPE in(select TYPE_NAME from dba_types where OWNER=upper('&v_owner') ) )
)
;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Tables with No PK :
prompt previous : top : next
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
select table_name from dba_tables
where OWNER=upper('&v_owner')
minus
select table_name
from dba_constraints
where OWNER=upper('&v_owner')
and constraint_type in ('P','U');
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Lob columns with NOT NULL set :
prompt previous : top : next
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
-- LOB columns with NOT NULL set
select TABLE_NAME,COLUMN_NAME from dba_tab_columns
where OWNER=upper('&v_owner') and DATA_TYPE in('CLOB','NCLOB','BLOB') and NULLABLE='N' order by 1,2;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt Oldest transaction with block changes :
prompt previous : top : next
set markup html on spool on entmap off
-- START SQL
-- Oldest transaction with block changes
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
select sysdate "Current Time",min(to_date(START_TIME,'MM/DD/YY HH24:MI:SS')) "Oldest Transaction Start time",
(sysdate - min(to_date(START_TIME,'MM/DD/YY HH24:MI:SS'))) "Transaction duration" from v$transaction where CR_CHANGE > 0;
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt PK AND Unique key on the same table :
prompt previous : top : next
prompt For additional details please see the following section of our documentation...
prompt Using an Oracle database as a source for AWS DMS
set markup html on spool on entmap off
-- START SQL
-- Oldest transaction with block changes
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
-- ????????????????????? Need sql for this
-- END SQL
set markup html off
prompt
prompt
-- New Minor heading and block :
prompt DMS User Session Waits :
prompt previous : top : next
prompt
prompt The following contains the current session wait event and timing for the DMS user &v_connector.
prompt This is a point in time snapshot of the session waits, but can sometimes help to identify contention inside the database.
prompt
prompt For futher details on these wait events, please refer to the Oracle documentation.
set markup html on spool on entmap off
-- START SQL
select distinct s.inst_id as "INST_ID", LAST_CALL_ET,s.username as "USERNAME",s.sid as "SID",BLOCKING_SESSION,s.row_wait_obj# as "ROW_WAIT_OBJ#",
s.program as "PROGRAM", s.logon_time as "LOGON_TIME", s.state as "STATE", s.sql_id as "SQL_ID",event as "EVENT",SQL_TEXT
from gv$session s,gv$sql q
where s.sql_hash_value=q.hash_value(+)
and s.username=upper('&v_connector')
order by 1 desc;
-- END SQL
set markup html off
prompt
prompt
set markup html off
spool off;
set termout on
set verify on
prompt
prompt Script complete.
prompt
prompt The output is saved to dms_support_oracle-&&X-&&Y..html
prompt
prompt Please review this file and upload to your AWS support case.
prompt
prompt