Troubleshooting DB issues for Amazon RDS Custom for Oracle

The shared responsibility model of RDS Custom provides OS shell–level access and database administrator access. RDS Custom runs resources in your account, unlike Amazon RDS, which runs resources in a system account. With greater access comes greater responsibility. In the following sections, you can learn how to troubleshoot issues with Amazon RDS Custom DB instances.

Note

This section explains how to troubleshoot RDS Custom for Oracle. For troubleshooting RDS Custom for SQL Server, see Troubleshooting DB issues for Amazon RDS Custom for SQL Server.

Viewing RDS Custom events

The procedure for viewing events is the same for RDS Custom and Amazon RDS DB instances. For more information, see Viewing Amazon RDS events.

To view RDS Custom event notification using the AWS CLI, use the describe-events command. RDS Custom introduces several new events. The event categories are the same as for Amazon RDS. For the list of events, see Amazon RDS event categories and event messages.

The following example retrieves details for the events that have occurred for the specified RDS Custom DB instance.

aws rds describe-events \
    --source-identifier my-custom-instance \
    --source-type db-instance

Subscribing to RDS Custom events

The procedure for subscribing to events is the same for RDS Custom and Amazon RDS DB instances. For more information, see Subscribing to Amazon RDS event notification.

To subscribe to RDS Custom event notification using the CLI, use the create-event-subscription command. Include the following required parameters:

• --subscription-name

• --sns-topic-arn

The following example creates a subscription for backup and recovery events for an RDS Custom DB instance in the current AWS account. Notifications are sent to an Amazon Simple Notification Service (Amazon SNS) topic, specified by --sns-topic-arn.

aws rds create-event-subscription \
    --subscription-name my-instance-events \
    --source-type db-instance \
    --event-categories '["backup","recovery"]' \
    --sns-topic-arn arn:aws:sns:us-east-1:123456789012:interesting-events

Troubleshooting custom engine version creation for RDS Custom for Oracle

When CEV creation fails, RDS Custom issues RDS-EVENT-0198 with the message Creation failed for custom engine version major-engine-version.cev_name, and includes details about the failure. For example, the event prints missing files.

CEV creation might fail because of the following issues:

• The Amazon S3 bucket containing your installation files isn't in the same AWS Region as your CEV.

• When you request CEV creation in an AWS Region for the first time, RDS Custom creates an S3 bucket for storing RDS Custom resources (such as CEV artifacts, AWS CloudTrail logs, and transaction logs).

  CEV creation fails if RDS Custom can't create the S3 bucket. Either the caller doesn't have S3 permissions as described in Step 5: Grant required permissions to your IAM user or role, or the number of S3 buckets has reached the limit.

• The caller doesn't have permissions to get files from your S3 bucket that contains the installation media files. These permissions are described in Step 7: Add necessary IAM permissions.

• Your IAM policy has an aws:SourceIp condition. Make sure to follow the recommendations in AWS Denies access to AWS based on the source IP in the AWS Identity and Access Management User Guide. Also make sure that the caller has the S3 permissions described in Step 5: Grant required permissions to your IAM user or role.

• Installation media files listed in the CEV manifest aren't in your S3 bucket.

• The SHA-256 checksums of the installation files are unknown to RDS Custom.

  Confirm that the SHA-256 checksums of the provided files match the SHA-256 checksum on the Oracle website. If the checksums match, contact AWS Support and provide the failed CEV name, file name, and checksum.

• The OPatch version is incompatible with your patch files. You might get the following message: OPatch is lower than minimum required version. Check that the version meets the requirements for all patches, and try again. To apply an Oracle patch, you must use a compatible version of the OPatch utility. You can find the required version of the Opatch utility in the readme file for the patch. Download the most recent OPatch utility from My Oracle Support, and try creating your CEV again.

• The patches specified in the CEV manifest are in the wrong order.

You can view RDS events either on the RDS console (in the navigation pane, choose Events) or by using the describe-events AWS CLI command. The default duration is 60 minutes. If no events are returned, specify a longer duration, as shown in the following example.

aws rds describe-events --duration 360

Currently, the MediaImport service that imports files from Amazon S3 to create CEVs isn't integrated with AWS CloudTrail. Therefore, if you turn on data logging for Amazon RDS in CloudTrail, calls to the MediaImport service such as the CreateCustomDbEngineVersion event aren't logged.

However, you might see calls from the API gateway that accesses your Amazon S3 bucket. These calls come from the MediaImport service for the CreateCustomDbEngineVersion event.

Fixing unsupported configurations in RDS Custom for Oracle

In the shared responsibility model, it's your responsibility to fix configuration issues that put your RDS Custom for Oracle DB instance into the unsupported-configuration state. If the issue is with the AWS infrastructure, you can use the console or the AWS CLI to fix it. If the issue is with the operating system or the database configuration, you can log in to the host to fix it.

Note

This section explains how to fix unsupported configurations in RDS Custom for Oracle. For information about RDS Custom for SQL Server, see Fixing unsupported configurations in RDS Custom for SQL Server.

In the following table, you can find descriptions of the notifications and events that the support perimeter sends and how to fix them. These notifications and the support perimeter are subject to change. For background on the support perimeter, see RDS Custom support perimeter. For event descriptions, see Amazon RDS event categories and event messages.

Event ID	Configuration	RDS event message	Action
SP-O0000	Manual unsupported configuration	The RDS Custom DB instance status is set to [Unsupported configuration] because of: reason.	To resolve this issue, create an AWS Support case.
AWS resources (infrastructure)
SP-O1001	Amazon Elastic Block Store (Amazon EBS) volumes	The following EBS volumes were added to EC2 instance ec2_id: volume_id. To resolve the issue, detach the specified volumes from the instance.	RDS Custom creates two types of EBS volume, besides the root volume created from the Amazon Machine Image (AMI), and associates them with the EC2 instance: The binary volume where the database software binaries are located The data volumes where database files are located When you create your DB instance, the storage configurations that you specify configure the data volumes. The support perimeter monitors the following: The initial EBS volumes created with the DB instance are still associated with the instance. The initial EBS volumes still have the same configurations as initially set: storage type, size, Provisioned IOPS, and storage throughput. No additional EBS volumes are attached to the DB instance. Use the following CLI command to compare the volume type of the EBS volume details and the RDS Custom for Oracle DB instance details: aws rds describe-db-instances \ --db-instance-identifier db-instance-name | grep StorageType
SP-O1002	Amazon Elastic Block Store (Amazon EBS) volumes	EBS volume volume_id has been detached from EC2 instance [ec2_id]. You can't detach the original volume from this instance. To resolve the issue, re-attach volume_id to ec2_id.	RDS Custom creates two types of EBS volume, besides the root volume created from the Amazon Machine Image (AMI), and associates them with the EC2 instance: The binary volume where the database software binaries are located The data volumes where database files are located When you create your DB instance, the storage configurations that you specify configure the data volumes. The support perimeter monitors the following: The initial EBS volumes created with the DB instance are still associated with the instance. The initial EBS volumes still have the same configurations as initially set: storage type, size, Provisioned IOPS, and storage throughput. No additional EBS volumes are attached to the DB instance. Use the following CLI command to compare the volume type of the EBS volume details and the RDS Custom for Oracle DB instance details: aws rds describe-db-instances \ --db-instance-identifier db-instance-name | grep StorageType
SP-O1003	Amazon Elastic Block Store (Amazon EBS) volumes	The original EBS volume volume_id attached to EC2 instance ec2_id has been modified as follows: size [X] to [Y], type [N] to [M], or IOPS [J] to [K]. To resolve the issue, revert the modification.	RDS Custom creates two types of EBS volume, besides the root volume created from the Amazon Machine Image (AMI), and associates them with the EC2 instance: The binary volume where the database software binaries are located The data volumes where database files are located When you create your DB instance, the storage configurations that you specify configure the data volumes. The support perimeter monitors the following: The initial EBS volumes created with the DB instance are still associated with the instance. The initial EBS volumes still have the same configurations as initially set: storage type, size, Provisioned IOPS, and storage throughput. No additional EBS volumes are attached to the DB instance. Use the following CLI command to compare the volume type of the EBS volume details and the RDS Custom for Oracle DB instance details: aws rds describe-db-instances \ --db-instance-identifier db-instance-name | grep StorageType
SP-O1004	Amazon EC2 instance state	Automated recovery left EC2 instance [ec2_id] in an impaired state. To resolve the issue, see Troubleshooting instance recovery failures.	To check the status of a DB instance, use the console or run the following AWS CLI command: aws rds describe-db-instances \ --db-instance-identifier db-instance-name |grep DBInstanceStatus
SP-O1005	Amazon EC2 instance attributes	EC2 instance [ec2_id] was modified as follows: attribute [att1] changed from [val-old] to [val-new], attribute [att2] changed from [val-old] to [val-new]. To resolve the issue, revert to the original value.
SP-O1006	Amazon EC2 instance state	EC2 instance [ec2_id] was terminated or can't be found. To resolve the issue, delete the RDS Custom DB instance.	The support perimeter monitors EC2 instance state-change notifications. The EC2 instance must always be running. To delete your DB instance To check the status of a DB instance, use the console or run the following AWS CLI command: aws rds describe-db-instances \ --db-instance-identifier db-instance-name |grep DBInstanceStatus Delete your RDS Custom for Oracle DB instance.
SP-O1007	Amazon EC2 instance state	EC2 instance [ec2_id] was stopped. To resolve the issue, start the instance.	The support perimeter monitors EC2 instance state-change notifications. The EC2 instance must always be running. To restart your DB instance To check the status of a DB instance, use the console or run the following AWS CLI command: aws rds describe-db-instances \ --db-instance-identifier db-instance-name |grep DBInstanceStatus Start your DB instance. Remount the binary and data volumes.
Operating system
SP-O2001	RDS Custom agent status	The RDS Custom agent isn't running on EC2 instance [ec2_id]. Make sure the agent is running on [ec2_id].	On RDS Custom for Oracle, the DB instance goes outside the support perimeter if the RDS Custom agent stops. The agent publishes the IamAlive metric to Amazon CloudWatch every 30 seconds. An alarm is triggered if the metric hasn't been published for 30 seconds. The support perimeter also monitors the RDS Custom agent process state on the host every 30 minutes. To restart the RDS Custom agent Log in to your host and make sure that the RDS Custom agent is running. Run the following command to find the status of the agent. service rdscustomagent status Use the following command to start the agent. service rdscustomagent start When the RDS Custom agent is running again, the IamAlive metric is published to Amazon CloudWatch, and the alarm switches to the OK state. This switch notifies the support perimeter that the agent is running.
SP-O2002	AWS Systems Manager agent (SSM agent) status	The Systems Manager agent on EC2 instance [ec2_id] is unreachable. Make sure that you that have correctly configured the network, agent, and IAM permissions.	SSM Agent must always be running. The RDS Custom agent is responsible for making sure that the Systems Manager agent is running. If SSM Agent was terminated and then restarted, the RDS Custom agent publishes a metric to CloudWatch. The RDS Custom agent has an alarm on the metric set to trigger when there has been a restart in each of the previous three minutes. The support perimeter also monitors the process state of SSM Agent on the host every 30 minutes. For more information, see Troubleshooting SSM Agent.
SP-O2003	AWS Systems Manager agent (SSM agent) status	The Systems Manager agent on EC2 instance [ec2_id] crashed multiple times. For more information, see the SSM Agent troubleshooting documentation.	For more information, see Troubleshooting SSM Agent.
SP-O2004	OS time zone	The time zone on EC2 instance [ec2_id] was changed. To resolve this issue, revert the timezone to the previous setting of [previous-time-zone]. Then use an RDS options group to change the time zone.	RDS automation detected that the time zone on the host was changed without the use of an option group. This host-level change can cause RDS automation failures, so the EC2 instance is placed in the unsupported-configuration state. To fix the time zone setting Log in to your EC2 host and check the OS time zone as follows: timedatectl Pause RDS Custom automation. For more information, see Pausing and resuming your RDS Custom DB instance. Stop the DB instance. Revert the time zone change on the operating system. Start the DB instance. Resume RDS Custom automation. Your DB instance becomes available within 30 minutes. To prevent moving out of perimeter in the future, modify your timezone through an options group. For more information, see Oracle time zone.
SP-O2005	sudo configurations	The sudo configurations on EC2 instance [ec2_id] lack necessary permissions. To resolve this issue, revert the recent changes to the sudo configurations.	The support perimeter monitors that certain OS users are allowed to run certain commands on the box. It monitors sudo configurations against the supported state. When the sudo configurations aren't supported, the RDS Custom tries to overwrite them back to the previous supported state. If that is successful, the following notification is sent: RDS Custom successfully overwrote your configuration. To investigate changes to the sudo configurations Log in to your host. Run the following command. visudo -c -f /etc/sudoers.d/individual_sudo_files Modify the sudo configurations as necessary. After the support perimeter determines that the sudo configurations are supported, your RDS Custom for Oracle DB instance becomes available within 30 minutes.
SP-O2006	S3 bucket accessibility	RDS Custom automation can't download files from the S3 bucket on EC2 instance [ec2_id]. Check your networking configuration and make sure the instance allows connections to and from S3.
Database
SP-O3001	Database archive lag target	The ARCHIVE_LAG_TARGET parameter on EC2 instance [ec2_id] is out of the recommended range value_range. To resolve the issue, set the parameter to a value within value_range.	The support perimeter monitors the ARCHIVE_LAG_TARGET database parameter to verify that the latest restorable time of the DB instance is within reasonable bounds. To change the lag target for archived redo logs Log in to your EC2 host Connect to your RDS Custom for Oracle DB instance Change the ARCHIVE_LAG_TARGET parameter to a value from 60–7200. For example, use the following SQL statement. ALTER SYSTEM SET ARCHIVE_LAG_TARGET=300 SCOPE=BOTH; Your DB instance becomes available within 30 minutes.
SP-O3002	Oracle Data Guard role	The database role [role_name] isn't supported for Oracle Data Guard on EC2 instance [ec2_id]. To resolve the issue, set the DATABASE_ROLE parameter to either PRIMARY or PHYSICAL STANDBY.	The support perimeter monitors the current database role every 15 seconds and sends a CloudWatch notification if the database role has changed. The Oracle Data Guard DATABASE_ROLE parameter must be either PRIMARY or PHYSICAL STANDBY. To restore your Oracle Data Guard database role to a supported value Check the Oracle Data Guard role by running the following statement: SELECT DATABASE_ROLE FROM V$DATABASE; If your DB instance is standalone, use either of the following statements to change it back to the PRIMARY role: ALTER DATABASE COMMIT TO SWITCHOVER PRIMARY; ALTER DATABASE ACTIVATE STANDBY DATABASE; If your DB instance is a replica, use the following statement to change it back to the PHYSICAL STANDBY role: ALTER DATABASE CONVERT TO PHYSICAL STANDBY; After the support perimeter determines that the database role is supported, your RDS Custom for Oracle DB instance becomes available within 15 seconds.
SP-O3003	Database health	The SMON process of the Oracle database is in a zombie state. To resolve the issue, manually recover the database on EC2 instance [ec2_id], open the database, and then immediately back it up. For more help, contact AWS Support.	The support perimeter monitors the DB instance state. It also monitors how many restarts occurred during the previous hour and day. You're notified when the instance is in a state where it still exists, but you can't interact with it. To make the support perimeter evaluate your instance state Log in to your host and determine the database state. ps -eo pid,state,command | grep smon If necessary, restart your DB instance. If the restart fails, proceed to the next step. If necessary, restart your EC2 host. After your DB instance restarts, the RDS Custom agent detects that your DB instance is no longer in an unresponsive state. It then notifies the support perimeter to reevaluate your DB instance state.
SP-O3004	Database log mode	The database log mode on EC2 instance [ec2_id] was changed to [value_b]. To resolve the issue, set the log mode to [value_a].	To change your DB instance log mode to ARCHIVELOG Log in to your EC2 host. Connect to your database and run the following statement: SELECT LOG_MODE FROM V$DATABASE; Or you can run the follow command in SQL*Plus: ARCHIVE LOG LIST Run the following SQL*Plus command to initiate a consistent shutdown. SHUTDOWN IMMEDIATE The RDS Custom agent automatically restarts your DB instance and sets the log mode to ARCHIVELOG. Your DB instance becomes available within 30 minutes.
SP-O3005	Oracle home path	The Oracle home on EC2 instance [ec2_id] was changed to new_path. To resolve the issue, revert the setting to old_path.
SP-O3006	Database unique name	The database unique name on EC2 instance [ec2_id] was changed to new_value. To resolve the issue, revert the name to old_value.	To change the database unique name for your DB instance Log in to your EC2 host. Connect to the database and run the following statement: SELECT DB_UNIQUE_NAME FROM V$DATABASE; Specify the original database unique name using the command ALTER SYSTEM SET DB_UNIQUE_NAME. Run the following SQL statement to initiate a consistent shutdown. SHUTDOWN IMMEDIATE; The RDS Custom agent automatically restarts your DB instance and sets the log mode to ARCHIVELOG. Your DB instance becomes available within 30 minutes.

Troubleshooting upgrades for RDS Custom for Oracle

Your upgrade of an RDS Custom for Oracle instance might fail. Following, you can find techniques that you can use during upgrades of RDS Custom DB for Oracle DB instances:

• Examine the upgrade output log files in the /tmp directory on your DB instance. The names of the logs depend on your DB engine version. For example, you might see logs that contain the strings catupgrd or catup.

• Examine the alert.log file located in the /rdsdbdata/log/trace directory.

• Run the following grep command in the root directory to track the upgrade OS process. This command shows where the log files are being written and determine the state of the upgrade process.

  ps -aux | grep upg

  The following shows sample output.

  root     18884  0.0  0.0 235428  8172 ?        S<   17:03   0:00 /usr/bin/sudo -u rdsdb /rdsdbbin/scripts/oracle-control ORCL op_apply_upgrade_sh RDS-UPGRADE/2.upgrade.sh
  rdsdb    18886  0.0  0.0 153968 12164 ?        S<   17:03   0:00 /usr/bin/perl -T -w /rdsdbbin/scripts/oracle-control ORCL op_apply_upgrade_sh RDS-UPGRADE/2.upgrade.sh
  rdsdb    18887  0.0  0.0 113196  3032 ?        S<   17:03   0:00 /bin/sh /rdsdbbin/oracle/rdbms/admin/RDS-UPGRADE/2.upgrade.sh
  rdsdb    18900  0.0  0.0 113196  1812 ?        S<   17:03   0:00 /bin/sh /rdsdbbin/oracle/rdbms/admin/RDS-UPGRADE/2.upgrade.sh
  rdsdb    18901  0.1  0.0 167652 20620 ?        S<   17:03   0:07 /rdsdbbin/oracle/perl/bin/perl catctl.pl -n 4 -d /rdsdbbin/oracle/rdbms/admin -l /tmp catupgrd.sql
  root     29944  0.0  0.0 112724  2316 pts/0    S+   18:43   0:00 grep --color=auto upg

• Run the following SQL query to verify the current state of the components to find the database version and the options installed on the DB instance.

  SET LINESIZE 180
  COLUMN COMP_ID FORMAT A15
  COLUMN COMP_NAME FORMAT A40 TRUNC
  COLUMN STATUS FORMAT A15 TRUNC
  SELECT COMP_ID, COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY ORDER BY 1;

  The output resembles the following.

  COMP_NAME                                STATUS               PROCEDURE
  ---------------------------------------- -------------------- --------------------------------------------------
  Oracle Database Catalog Views            VALID                DBMS_REGISTRY_SYS.VALIDATE_CATALOG
  Oracle Database Packages and Types       VALID                DBMS_REGISTRY_SYS.VALIDATE_CATPROC
  Oracle Text                              VALID                VALIDATE_CONTEXT
  Oracle XML Database                      VALID                DBMS_REGXDB.VALIDATEXDB
  
  4 rows selected.

• Run the following SQL query to check for invalid objects that might interfere with the upgrade process.

  SET PAGES 1000 LINES 2000
  COL OBJECT FOR A40
  SELECT SUBSTR(OWNER,1,12) OWNER,
         SUBSTR(OBJECT_NAME,1,30) OBJECT,
         SUBSTR(OBJECT_TYPE,1,30) TYPE, STATUS,
         CREATED
  FROM   DBA_OBJECTS 
  WHERE  STATUS <>'VALID' 
  AND    OWNER IN ('SYS','SYSTEM','RDSADMIN','XDB');

Troubleshooting replica promotion for RDS Custom for Oracle

You can promote managed Oracle replicas in RDS Custom for Oracle using the console, promote-read-replica AWS CLI command, or PromoteReadReplica API. If you delete your primary DB instance, and all replicas are healthy, RDS Custom for Oracle promotes your managed replicas to standalone instances automatically. If a replica has paused automation or is outside the support perimeter, you must fix the replica before RDS Custom can promote it automatically. For more information, see Replica promotion limitations for RDS Custom for Oracle.

The replica promotion workflow might become stuck in the following situation:

• The primary DB instance is in the state STORAGE_FULL.

• The primary database can't archive all of its online redo logs.

• A gap exists between the archived redo log files on your Oracle replica and the primary database.

To respond to the stuck workflow

1. Synchronize the redo log gap on your Oracle replica DB instance.

2. Force the promotion of your read replica to the latest applied redo log. Run the following commands in SQL*Plus:

   ALTER DATABASE ACTIVATE STANDBY DATABASE;
   SHUTDOWN IMMEDIATE
   STARTUP

3. Contact AWS Support and ask them to move your DB instance to available status.