Troubleshooting DB issues for Amazon RDS Custom for Oracle - Amazon Relational Database Service

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

Viewing 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.

Configuration RDS event message Description Action
Database

Database health

You need to manually recover the database on EC2 instance [i-xxxxxxxxxxxxxxxxx].

The DB instance restarted.

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.

Log in to your host and examine the database state.

ps -eo pid,state,command | grep smon

Restart your RDS Custom for Oracle DB instance if necessary to get it running again. Sometimes it's necessary to reboot the host.

After the restart, 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.

Oracle Data Guard role

The database role [LOGICAL STANDBY] isn't supported. Validate the Oracle Data Guard configuration for the database on Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx].

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.

Restore your Oracle Data Guard database role to a supported value.

RDS Custom only supports PRIMARY and PHYSICAL STANDBY roles. You can use the following statement to check the role:

SELECT DATABASE_ROLE FROM V$DATABASE;

If your RDS Custom for Oracle DB instance is standalone, you can 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, you can 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.

Database archive lag target

The monitored Archive Lag Target database parameter on Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] has changed from [300] to [0].

The RDS Custom instance is using an unsupported configuration because of the following [1] issue(s): (1) The archive lag target database parameter on Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] is out of desired range {"lowerbound":60,"upperbound":7200}.

The support perimeter monitors the ARCHIVE_LAG_TARGET database parameter to verify that the DB instance's latest restorable time is within reasonable bounds.

Log in to your host, connect to your RDS Custom for Oracle DB instance, and change the ARCHIVE_LAG_TARGET parameter to a value from 60–7200.

For example, use the following SQL command.

ALTER SYSTEM SET ARCHIVE_LAG_TARGET=300 SCOPE=BOTH;

Your DB instance becomes available within 30 minutes.

Database log mode

The monitored log mode of the database on Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] has changed from [ARCHIVELOG] to [NOARCHIVELOG].

The DB instance log mode must be set to ARCHIVELOG.

Log in to your host and shut down your RDS Custom for Oracle DB instance. Use the following SQL statement to initiate a consistent shutdown.

SHUTDOWN IMMEDIATE;

The RDS Custom agent restarts your DB instance and sets the log mode to ARCHIVELOG.

Your DB instance becomes available within 30 minutes.

Operating system

RDS Custom agent status

The monitored state of the RDS Custom agent on EC2 instance [i-xxxxxxxxxxxxxxxxx] has changed from RUNNING to STOPPED.

The RDS Custom agent must always be running. 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.

On RDS Custom for Oracle, the DB instance goes outside the support perimeter if the RDS Custom agent stops.

Log in to your host and make sure that the RDS Custom agent is running.

You can use the following command to find the status of the agent.

service rdscustomagent status

You can 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.

AWS Systems Manager agent (SSM agent) status

The AWS Systems Manager agent on EC2 instance [i-xxxxxxxxxxxxxxxxx] is currently unreachable. Make sure you have correctly configured the network, agent, and IAM permissions.

The SSM agent must always be running. The RDS Custom agent is responsible for making sure that the Systems Manager agent is running.

If the SSM agent was down and 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 SSM agent process state on the host every 30 minutes.

For more information, see Troubleshooting SSM Agent.

sudo configurations

The sudo configurations on EC2 instance [i-xxxxxxxxxxxxxxxxx] have changed.

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.

If the overwrite is unsuccessful, you can log in to your host and investigate why recent changes to the sudo configurations aren't supported.

You can use the following command.

visudo -c -f /etc/sudoers.d/individual_sudo_files

After the support perimeter determines that the sudo configurations are supported, the your RDS Custom for Oracle DB instance becomes available within 30 minutes.

OS time zone

The time zone on Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] changed from [%s] to [%s].

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 issue:

  1. Pause RDS Custom automation. For more information, see Pausing and resuming your RDS Custom DB instance.

  2. Stop the DB instance.

  3. Revert the time zone change on the operating system.

  4. Start the DB instance.

  5. 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.

Amazon EC2 and Amazon EBS resources

Amazon EC2 instance state

The state of the EC2 instance [i-xxxxxxxxxxxxxxxxx] has changed from [RUNNING] to [STOPPING].

The Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] has been terminated and can't be found. Delete the database instance to clean up resources.

The Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] has been stopped. Start the instance, and restore the host configuration. For more information, see the troubleshooting documentation.

The support perimeter monitors EC2 instance state-change notifications. The EC2 instance must always be running.

If your EC2 instance is stopped, start it and remount the binary and data volumes.

If your EC2 instance is terminated, delete your RDS Custom for Oracle DB instance.

Amazon EC2 instance attributes

The attributes of Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] have changed.

The support perimeter monitors the instance type of the EC2 instance where the RDS Custom DB instance is running. The EC2 instance type must stay the same as when you set it up during RDS Custom DB instance creation.

Change the EC2 instance type back to the original type using the EC2 console or CLI.

To change the instance type because of scaling requirements, begin point-in-time recovery and specify the new instance type and class. This action results in a new RDS Custom DB instance with a new host and Domain Name System (DNS) name.

Amazon Elastic Block Store (Amazon EBS) volumes

The following Amazon EBS volumes are attached to Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx]: [[vol-01234abcd56789ef0, vol-0def6789abcd01234]].

The original Amazon EBS volumes attached to Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] have been detached or modified. You can’t attach or modify the initial EBS volumes attached to an RDS Custom 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 is where the database software binaries are located. The data volumes are where database files are located. The storage configurations that you set when creating the DB instance are used to configure the data volumes.

The support perimeter monitors the following:

  • The initial EBS volumes created with the DB instance are still associated.

  • 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.

If you detached any initial EBS volumes, contact AWS Support.

If you modified the storage type, Provisioned IOPS, or storage throughput of an EBS volume, revert the modification to the original value.

If you modified the storage size of an EBS volume, contact AWS Support.

(RDS Custom for Oracle only) If you attached any additional EBS volumes, do either of the following:

  • Detach the additional EBS volumes from the RDS Custom DB instance.

  • Contact AWS Support.

EBS-optimized state

The EBS-optimized attribute of Amazon EC2 instance [i-xxxxxxxxxxxxxxxxx] has changed from [enabled] to [disabled].

Amazon EC2 instances should be EBS optimized.

If the EBS-optimized attribute is turned off (disabled), the support perimeter doesn't put the DB instance into the unsupported-configuration state.

To turn on the EBS-optimized attribute:

  1. Stop the EC2 instance.

  2. Set the EBS-optimized attribute to enabled.

  3. Start the EC2 instance.

  4. Remount the binary and data volumes.

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 DB 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, complete the following steps:

  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 request it to move your DB instance to available status.