# Working with diagnostic support scripts in AWS DMS
If you encounter an issue when working with AWS DMS, your support engineer might need more information about either your source or target database. We want to make sure that AWS Support gets as much of the required information as possible in the shortest possible time. Therefore, we developed scripts to query this information for several of the major relational database engines.
If a support script is available for your database, you can download it using the link in the corresponding script topic described following. After verifying and reviewing the script (described following), you can run it according to the procedure described in the script topic. When the script run is complete, you can upload its output to your AWS Support case (again, described following).
Before running the script, you can detect any errors that might have been introduced when downloading or storing the support script. To do this, compare the checksum for the script file with a value provided by AWS. AWS uses the SHA256 algorithm for the checksum.
**To verify the support script file using a checksum**
1. Open the latest checksum file provided to verify these support scripts at [https://d2pwp9zz55emqw.cloudfront.net/sha256Check.txt](https://d2pwp9zz55emqw.cloudfront.net/sha256Check.txt). For example, the file might have content like the following.
```
MYSQL dfafd0d511477c699f96c64693ad0b1547d47e74d5c5f2f2025b790b1422e3c8
ORACLE 6c41ebcfc99518cfa8a10cb2ce8943b153b2cc7049117183d0b5de3d551bc312
POSTGRES 6ccd274863d14f6f3146fbdbbba43f2d8d4c6a4c25380d7b41c71883aa4f9790
SQL_SERVER 971a6f2c46aec8d083d2b3b6549b1e9990af3a15fe4b922e319f4fdd358debe7
```
1. Run the SHA256 validation command for your operating system in the directory that contains the support file. For example, on the macOS operating system you can run the following command on an Oracle support script described later in this topic.
```
shasum -a 256 awsdms_support_collector_oracle.sql
```
1. Compare the results of the command with the value shown in the latest `sha256Check.txt` file that you opened. The two values should match. If they don't, contact your support engineer about the mismatch and how you can obtain a clean support script file.
If you have a clean support script file, before running the script make sure to read and understand the SQL from both a performance and security perspective. If you aren't comfortable running any of the SQL in this script, you can comment out or remove the problem SQL. You can also consult with your support engineer about any acceptable workarounds.
Upon successful completion and unless otherwise noted, the script returns output in a readable HTML format. The script is designed to exclude from this HTML any data or security details that might compromise your business. It also makes no modifications to your database or its environment. However, if you find any information in the HTML that you are uncomfortable sharing, feel free to remove the problem information before uploading the HTML. When the HTML is acceptable, upload it using the **Attachments** in the **Case details** of your support case.
Each of the following topics describes the scripts available for a supported AWS DMS database and how to run them. Your support engineer will direct you to a specific script documented following.
**Topics**
+ [Oracle diagnostic support scripts](CHAP_SupportScripts.Oracle.md)
+ [SQL Server diagnostic support scripts](CHAP_SupportScripts.SQLServer.md)
+ [Diagnostic support scripts for MySQL-compatible databases](CHAP_SupportScripts.MySQL.md)
+ [PostgreSQL diagnostic support scripts](CHAP_SupportScripts.PostgreSQL.md)
# Oracle diagnostic support scripts
Following, you can find the diagnostic support scripts available to analyze an on-premises or Amazon RDS for Oracle database in your AWS DMS migration configuration. These scripts work with either a source or target endpoint. The scripts are all written to run in the SQL\$1Plus command-line utility. For more information on using this utility, see [A Using SQL Command Line](https://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/xedev_sqlplus.htm) in the Oracle documentation.
Before running the script, ensure that the user account that you use has the necessary permissions to access your Oracle database. The permissions settings shown assume a user created as follows.
```
CREATE USER script_user IDENTIFIED BY password;
```
For an on-premises database, set the minimum permissions as shown following for `script_user`.
```
GRANT CREATE SESSION TO script_user;
GRANT SELECT on V$DATABASE to script_user;
GRANT SELECT on V$VERSION to script_user;
GRANT SELECT on GV$SGA to script_user;
GRANT SELECT on GV$INSTANCE to script_user;
GRANT SELECT on GV$DATAGUARD_CONFIG to script_user;
GRANT SELECT on GV$LOG to script_user;
GRANT SELECT on DBA_TABLESPACES to script_user;
GRANT SELECT on DBA_DATA_FILES to script_user;
GRANT SELECT on DBA_SEGMENTS to script_user;
GRANT SELECT on DBA_LOBS to script_user;
GRANT SELECT on V$ARCHIVED_LOG to script_user;
GRANT SELECT on DBA_TAB_MODIFICATIONS to script_user;
GRANT SELECT on DBA_TABLES to script_user;
GRANT SELECT on DBA_TAB_PARTITIONS to script_user;
GRANT SELECT on DBA_MVIEWS to script_user;
GRANT SELECT on DBA_OBJECTS to script_user;
GRANT SELECT on DBA_TAB_COLUMNS to script_user;
GRANT SELECT on DBA_LOG_GROUPS to script_user;
GRANT SELECT on DBA_LOG_GROUP_COLUMNS to script_user;
GRANT SELECT on V$ARCHIVE_DEST to script_user;
GRANT SELECT on DBA_SYS_PRIVS to script_user;
GRANT SELECT on DBA_TAB_PRIVS to script_user;
GRANT SELECT on DBA_TYPES to script_user;
GRANT SELECT on DBA_CONSTRAINTS to script_user;
GRANT SELECT on V$TRANSACTION to script_user;
GRANT SELECT on GV$ASM_DISK_STAT to script_user;
GRANT SELECT on GV$SESSION to script_user;
GRANT SELECT on GV$SQL to script_user;
GRANT SELECT on DBA_ENCRYPTED_COLUMNS to script_user;
GRANT SELECT on DBA_PDBS to script_user;
GRANT EXECUTE on dbms_utility to script_user;
```
For an Amazon RDS database, set the minimum permissions as shown following.
```
GRANT CREATE SESSION TO script_user;
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$VERSION','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('GV_$SGA','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('GV_$INSTANCE','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('GV_$DATAGUARD_CONFIG','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('GV_$LOG','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TABLESPACES','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_DATA_FILES','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_SEGMENTS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_LOBS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_MODIFICATIONS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TABLES','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_PARTITIONS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_MVIEWS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_LOG_GROUPS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_LOG_GROUP_COLUMNS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVE_DEST','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_SYS_PRIVS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_PRIVS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TYPES','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_CONSTRAINTS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TRANSACTION','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('GV_$ASM_DISK_STAT','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('GV_$SESSION','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('GV_$SQL','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ENCRYPTED_COLUMNS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_PDBS','script_user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_UTILITY','script_user','EXECUTE');
```
Following, you can find descriptions how to download, review, and run each SQL\$1Plus support script available for Oracle. You can also find how to review and upload the output to your AWS Support case.
**Topics**
+ [awsdms\$1support\$1collector\$1oracle.sql script](#CHAP_SupportScripts.Oracle.Awsdms_Support_Collector_Oracle_Script)
## awsdms\$1support\$1collector\$1oracle.sql script
Download the [https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_oracle.sql](https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_oracle.sql) script.
This script collects information about your Oracle database configuration. Remember to verify the checksum on the script, and if the checksum verifies, review the SQL code in the script to comment out any of the code that you are uncomfortable running. After you are satisfied with the integrity and content of the script, you can run it.
**To run the script and upload the results to your support case**
1. Run the script from your database environment using the following SQL\$1Plus command line.
```
SQL> @awsdms_support_collector_oracle.sql
```
The script displays a brief description and a prompt to either continue or abort the run. Press [Enter] to continue.
1. At the following prompt, enter the name of only one of the schemas that you want to migrate.
1. At the following prompt, enter the name of the user (*script\$1user*) that you have defined to connect to the database.
1. At the following prompt, enter the number of days of data you want to examine, or accept the default. The script then collects the specified data from your database.
After the script completes, it displays the name of the output HTML file, for example `dms_support_oracle-2020-06-22-13-20-39-ORCL.html`. The script saves this file in your working directory.
1. Review this HTML file and remove any information that you are uncomfortable sharing. When the HTML is acceptable for you to share, upload the file to your AWS Support case. For more information on uploading this file, see [Working with diagnostic support scripts in AWS DMS](CHAP_SupportScripts.md).
# SQL Server diagnostic support scripts
Following, you can find a description of the diagnostic support scripts available to analyze an on-premises or Amazon RDS for SQL Server database in your AWS DMS migration configuration. These scripts work with either a source or target endpoint. For an on-premises database, run these scripts in the sqlcmd command-line utility. For more information on using this utility, see [sqlcmd - Use the utility](https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-the-utility?view=sql-server-ver15) in the Microsoft documentation.
For an Amazon RDS database, you can't connect using the sqlcmd command-line utility. Instead, run these scripts using any client tool that connects to Amazon RDS SQL Server.
Before running the script, ensure that the user account that you use has the necessary permissions to access your SQL Server database. For both an on-premises and an Amazon RDS database, you can use the same permissions you use to access your SQL Server database without the `SysAdmin` role.
**Topics**
+ [Setting up minimum permissions for an on-premises SQL Server database](#CHAP_SupportScripts.SQLServer.onprem)
+ [Setting up minimum permissions for an Amazon RDS SQL Server database](#CHAP_SupportScripts.SQLServer.rds)
+ [SQL Server Support Scripts](#CHAP_SupportScripts.SQLServer.Scripts)
## Setting up minimum permissions for an on-premises SQL Server database
**To set up the minimum permissions to run for an on-premises SQL Server database**
1. Create a new SQL Server account with password authentication using SQL Server Management Studio (SSMS), for example `on-prem-user`.
1. In the **User Mappings** section of SSMS, choose the **MSDB** and **MASTER** databases (which gives public permission), and assign the `DB_OWNER` role to the database where you want to run the script.
1. Open the context (right-click) menu for the new account, and choose **Security** to explicitly grant the `Connect SQL` privilege.
1. Run the grant commands following.
```
GRANT VIEW SERVER STATE TO on-prem-user;
USE MSDB;
GRANT SELECT ON MSDB.DBO.BACKUPSET TO on-prem-user;
GRANT SELECT ON MSDB.DBO.BACKUPMEDIAFAMILY TO on-prem-user;
GRANT SELECT ON MSDB.DBO.BACKUPFILE TO on-prem-user;
```
## Setting up minimum permissions for an Amazon RDS SQL Server database
**To run with the minimum permissions for an Amazon RDS SQL Server database**
1. Create a new SQL Server account with password authentication using SQL Server Management Studio (SSMS), for example `rds-user`.
1. In the **User Mappings** section of SSMS, choose the **MSDB** database (which gives public permission), and assign the `DB_OWNER` role to the database where you want to run the script.
1. Open the context (right-click) menu for the new account, and choose **Security** to explicitly grant the `Connect SQL` privilege.
1. Run the grant commands following.
```
GRANT VIEW SERVER STATE TO rds-user;
USE MSDB;
GRANT SELECT ON MSDB.DBO.BACKUPSET TO rds-user;
GRANT SELECT ON MSDB.DBO.BACKUPMEDIAFAMILY TO rds-user;
GRANT SELECT ON MSDB.DBO.BACKUPFILE TO rds-user;
```
## SQL Server Support Scripts
The following topics describe how to download, review, and run each support script available for SQL Server. They also describe how to review and upload the script output to your AWS Support case.
**Topics**
+ [awsdms\$1support\$1collector\$1sql\$1server.sql script](#CHAP_SupportScripts.SQLServer.Awsdms_Support_Collector_SQLServer_Script)
### awsdms\$1support\$1collector\$1sql\$1server.sql script
Download the [https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_sql_server.sql](https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_sql_server.sql) script.
**Note**
Run this SQL Server diagnostic support script on SQL Server 2014 and higher versions only.
This script collects information about your SQL Server database configuration. Remember to verify the checksum on the script, and if the checksum verifies, review the SQL code in the script to comment out any of the code that you are uncomfortable running. After you are satisfied with the integrity and content of the script, you can run it.
**To run the script for an on-premises SQL Server database**
1. Run the script using the following sqlcmd command line.
```
sqlcmd -Uon-prem-user -Ppassword -SDMS-SQL17AG-N1 -y 0
-iC:\Users\admin\awsdms_support_collector_sql_server.sql -oC:\Users\admin\DMS_Support_Report_SQLServer.html -dsqlserverdb01
```
The specified sqlcmd command parameters include the following:
+ `-U` – Database user name.
+ `-P` – Database user password.
+ `-S` – SQL Server database server name.
+ `-y` – Maximum width of columns output from the sqlcmd utility. A value of 0 specifies columns of unlimited width.
+ `-i` – Path of the support script to run, in this case `awsdms_support_collector_sql_server.sql`.
+ `-o` – Path of the output HTML file, with a file name that you specify, containing the collected database configuration information.
+ `-d` – SQL Server database name.
1. After the script completes, review the output HTML file and remove any information that you are uncomfortable sharing. When the HTML is acceptable for you to share, upload the file to your AWS Support case. For more information on uploading this file, see [Working with diagnostic support scripts in AWS DMS](CHAP_SupportScripts.md).
With Amazon RDS for SQL Server, you can't connect using the sqlcmd command line utility, so use the following procedure.
**To run the script for an RDS SQL Server database**
1. Run the script using any client tool that allows you to connect to RDS SQL Server as the `Master` user and save the output as an HTML file.
1. Review the output HTML file and remove any information that you are uncomfortable sharing. When the HTML is acceptable for you to share, upload the file to your AWS Support case. For more information on uploading this file, see [Working with diagnostic support scripts in AWS DMS](CHAP_SupportScripts.md).
# Diagnostic support scripts for MySQL-compatible databases
Following, you can find the diagnostic support scripts available to analyze an on-premises or Amazon RDS for MySQL-compatible database in your AWS DMS migration configuration. These scripts work with either a source or target endpoint. The scripts are all written to run on the MySQL SQL command line.
For information about installing the MySQL client, see [ Installing MySQL Shell](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html) in the MySQL documentation. For information about using the MySQL client, see [ Using MySQL Shell Commands](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-configuring.html) in the MySQL documentation.
Before running a script, ensure that the user account that you use has the necessary permissions to access your MySQL-compatible database. Use the following procedure to create a user account and provide the minimum permissions needed to run this script.
**To set up a user account with the minimum permissions to run these scripts**
1. Create the user to run the scripts.
```
create user 'username'@'hostname' identified by password;
```
1. Grant the `select` command on databases to analyze them.
```
grant select on database-name.* to username;
grant replication client on *.* to username;
```
1.
```
grant execute on procedure mysql.rds_show_configuration to username;
```
The following topics describe how to download, review, and run each support script available for a MySQL-compatible database. They also describe how to review and upload the script output to your AWS Support case.
**Topics**
+ [awsdms\$1support\$1collector\$1MySQL.sql script](#CHAP_SupportScripts.MySQL.Awsdms_Support_Collector_MySQL_Script)
## awsdms\$1support\$1collector\$1MySQL.sql script
Download the [https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_MySQL.sql](https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_MySQL.sql) script.
This script collects information about your MySQL-compatible database configuration. Remember to verify the checksum on the script, and if the checksum verifies, review the SQL code in the script to comment out any of the code that you are uncomfortable running. After you are satisfied with the integrity and content of the script, you can run it.
Run the script after connecting to your database environment using the command line.
**To run this script and upload the results to your support case**
1. Connect to your database using the following `mysql` command.
```
mysql -p -h hostname -P port -u username database-name
```
1. Run the script using the following mysql `source` command.
```
source awsdms_support_collector_MySQL.sql
```
Review the generated report and remove any information that you are uncomfortable sharing. When the content is acceptable for you to share, upload the file to your AWS Support case. For more information on uploading this file, see [Working with diagnostic support scripts in AWS DMS](CHAP_SupportScripts.md).
**Note**
If you already have a user account with required privileges described in [Diagnostic support scripts for MySQL-compatible databases](#CHAP_SupportScripts.MySQL), you can use the existing user account as well to run the script.
Remember to connect to your database before running the script.
The script generates its output in text format.
Keeping security best practices in mind, if you create a new user account only to execute this MySQL diagnostic support script, we recommend that you delete this user account after successful execution of the script.
# PostgreSQL diagnostic support scripts
Following, you can find the diagnostic support scripts available to analyze any PostgreSQL RDBMS (on-premises, Amazon RDS, or Aurora PostgreSQL) in your AWS DMS migration configuration. These scripts work with either a source or target endpoint. The scripts are all written to run in the psql command-line utility.
Before running these scripts, ensure that the user account that you use has the following necessary permissions to access any PostgreSQL RDBMS:
+ PostgreSQL 10.x or higher – A user account with execute permission on the `pg_catalog.pg_ls_waldir` function.
+ PostgreSQL 9.x or earlier – A user account with default permissions.
We recommend using an existing account with the appropriate permissions to run these scripts.
If you need to create a new user account or grant permissions to an existing account to run these scripts, you can execute the following SQL commands for any PostgreSQL RDBMS based on the PostgreSQL version.
**To grant account permissions to run these scripts for a PostgreSQL databases version 10.x or higher**
+ Do one of the following:
+ For a new user account, run the following.
```
CREATE USER script_user WITH PASSWORD 'password';
GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_waldir TO script_user;
```
+ For an existing user account, run the following.
```
GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_waldir TO script_user;
```
**To grant account permissions to run these scripts for a PostgreSQL 9.x or earlier database**
+ Do one of the following:
+ For a new user account, run the following with default permissions.
```
CREATE USER script_user WITH PASSWORD password;
```
+ For an existing user account, use the existing permissions.
**Note**
These scripts do not support certain functionality related to finding WAL size for PostgreSQL 9.x and earlier databases. For more information, work with AWS Support.
The following topics describe how to download, review, and run each support script available for PostgreSQL They also describe how to review and upload the script output to your AWS Support case.
**Topics**
+ [awsdms\$1support\$1collector\$1postgres.sql script](#CHAP_SupportScripts.PostgreSQL.Awsdms_Support_Collector_PostgreSQL_Script)
## awsdms\$1support\$1collector\$1postgres.sql script
Download the [https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_postgres.sql](https://d2pwp9zz55emqw.cloudfront.net/scripts/awsdms_support_collector_postgres.sql) script.
This script collects information about your PostgreSQL database configuration. Remember to verify the checksum on the script. If the checksum verifies, review the SQL code in the script to comment out any of the code that you are uncomfortable running. After you are satisfied with the integrity and content of the script, you can run it.
**Note**
You can run this script with psql client version 10 or higher.
You can use the following procedures to run this script either from your database environment or from the command line. In either case, you can then upload your file to AWS Support later.
**To run this script and upload the results to your support case**
1. Do one of the following:
+ Run the script from your database environment using the following psql command line.
```
dbname=# \i awsdms_support_collector_postgres.sql
```
At the following prompt, enter the name of only one of the schemas that you want to migrate.
At the following prompt, enter the name of the user (`script_user`) that you have defined to connect to the database.
+ Run the following script directly from the command line. This option avoids any prompts prior to script execution.
```
psql -h database-hostname -p port -U script_user -d database-name -f awsdms_support_collector_postgres.sql
```
1. Review the output HTML file and remove any information that you are uncomfortable sharing. When the HTML is acceptable for you to share, upload the file to your AWS Support case. For more information on uploading this file, see [Working with diagnostic support scripts in AWS DMS](CHAP_SupportScripts.md).