Diagnosing problems with RDS for Oracle DB instances
Oracle Database includes a fault diagnosability infrastructure that you can use to
investigate database problems. In Oracle terminology, a problem is
a critical error such as a code bug or data corruption. An incident
is the occurrence of a problem. If the same error occurs three times, then the
infrastructure shows three incidents of this problem. For more information, see Diagnosing and resolving problems
The Automatic Diagnostic Repository Command Interpreter (ADRCI) utility is an Oracle command-line tool that you use to manage diagnostic data. For example, you can use this tool to investigate problems and package diagnostic data. An incident package includes diagnostic data for an incident or all incidents that reference a specific problem. You can upload an incident package, which is implemented as a .zip file, to Oracle Support.
To deliver a managed service experience, Amazon RDS doesn't provide shell access to ADRCI.
To perform diagnostic tasks for your RDS for Oracle DB instance, use the Amazon RDS package
rdsadmin.rdsadmin_adrci_util.
By using the functions in rdsadmin_adrci_util, you can list and package
problems and incidents, and also show trace files. All functions return a task ID. This ID
forms part of the name of log file that contains the ADRCI output, as in
dbtask-. The log file resides in
the BDUMP directory. You can download the log file by following the procedure described in
Downloading a database log file.task_id.log
Common parameters for diagnostic procedures
To perform diagnostic tasks, use functions in the Amazon RDS package
rdsadmin.rdsadmin_adrci_util. The package has the following common
parameters.
| Parameter name | Data type | Valid values | Default | Required | Description |
|---|---|---|---|---|---|
|
|
number |
A valid incident ID or null |
Null |
No |
If the value is null, the function shows all incidents. If the value isn't null and represents a valid incident ID, the function shows the specified incident. |
|
|
number |
A valid problem ID or null |
Null |
No |
If the value is null, the function shows all problems. If the value isn't null and represents a valid problem ID, the function shows the specified problem. |
|
|
number |
A valid integer greater than 0 or null |
Null |
No |
If the value is null, then the function displays at most 50 items. If the value isn't null, the function displays the specified number. |
Listing incidents
To list diagnostic incidents for Oracle, use the Amazon RDS function
rdsadmin.rdsadmin_adrci_util.list_adrci_incidents. You can list
incidents in either basic or detailed mode. By default, the function lists the 50
most recent incidents.
This function uses the following common parameters:
-
incident_id -
problem_id -
last
If you specify incident_id and problem_id, then incident_id
overrides problem_id. For more information, see Common
parameters for diagnostic procedures.
This function uses the following additional parameter.
| Parameter name | Data type | Valid values | Default | Required | Description |
|---|---|---|---|---|---|
|
|
boolean |
TRUE or FALSE |
|
No |
If |
To list all incidents, query the
rdsadmin.rdsadmin_adrci_util.list_adrci_incidents function without
any arguments. The query returns the task ID.
SQL> SELECT rdsadmin.rdsadmin_adrci_util.list_adrci_incidents AS task_id FROM DUAL; TASK_ID ------------------ 1590786706158-3126
Or call the rdsadmin.rdsadmin_adrci_util.list_adrci_incidents
function without any arguments and store the output in a SQL client variable. You
can use the variable in other statements.
SQL> VAR task_id VARCHAR2(80); SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.list_adrci_incidents; PL/SQL procedure successfully completed.
To read the log file, call the Amazon RDS procedure
rdsadmin.rds_file_util.read_text_file. Supply the task ID as part
of the file name. The following output shows three incidents: 53523, 53522, and
53521.
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')); TEXT ------------------------------------------------------------------------------------------------------------------------- 2020-05-29 21:11:46.193 UTC [INFO ] Listing ADRCI incidents. 2020-05-29 21:11:46.256 UTC [INFO ] ADR Home = /rdsdbdata/log/diag/rdbms/orcl_a/ORCL: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME ----------- ----------------------------------------------------------- ---------------------------------------- 53523 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_003 2020-05-29 20:15:20.928000 +00:00 53522 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_002 2020-05-29 20:15:15.247000 +00:00 53521 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_001 2020-05-29 20:15:06.047000 +00:00 3 rows fetched 2020-05-29 21:11:46.256 UTC [INFO ] The ADRCI incidents were successfully listed. 2020-05-29 21:11:46.256 UTC [INFO ] The task finished successfully. 14 rows selected.
To list a particular incident, specify its ID using the incident_id
parameter. In the following example, you query the log file for incident 53523
only.
SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.list_adrci_incidents(incident_id=>53523); PL/SQL procedure successfully completed. SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')); TEXT ------------------------------------------------------------------------------------------------------------------ 2020-05-29 21:15:25.358 UTC [INFO ] Listing ADRCI incidents. 2020-05-29 21:15:25.426 UTC [INFO ] ADR Home = /rdsdbdata/log/diag/rdbms/orcl_a/ORCL: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME -------------------- ----------------------------------------------------------- --------------------------------- 53523 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_003 2020-05-29 20:15:20.928000 +00:00 1 rows fetched 2020-05-29 21:15:25.427 UTC [INFO ] The ADRCI incidents were successfully listed. 2020-05-29 21:15:25.427 UTC [INFO ] The task finished successfully. 12 rows selected.
Listing problems
To list diagnostic problems for Oracle, use the Amazon RDS function
rdsadmin.rdsadmin_adrci_util.list_adrci_problems.
By default, the function lists the 50 most recent problems.
This function uses the common parameters problem_id and last. For more
information, see Common
parameters for diagnostic procedures.
To get the task ID for all problems, call the
rdsadmin.rdsadmin_adrci_util.list_adrci_problems function without
any arguments, and store the output in a SQL client variable.
SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.list_adrci_problems; PL/SQL procedure successfully completed.
To read the log file, call the rdsadmin.rds_file_util.read_text_file
function, supplying the task ID as part of the file name. In the following output,
the log file shows three problems: 1, 2, and 3.
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')); TEXT ---------------------------------------------------------------------------------------------------------------------- 2020-05-29 21:18:50.764 UTC [INFO ] Listing ADRCI problems. 2020-05-29 21:18:50.829 UTC [INFO ] ADR Home = /rdsdbdata/log/diag/rdbms/orcl_a/ORCL: ************************************************************************* PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME ---------- ----------------------------------------------------------- ------------- --------------------------------- 2 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_003 53523 2020-05-29 20:15:20.928000 +00:00 3 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_002 53522 2020-05-29 20:15:15.247000 +00:00 1 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_001 53521 2020-05-29 20:15:06.047000 +00:00 3 rows fetched 2020-05-29 21:18:50.829 UTC [INFO ] The ADRCI problems were successfully listed. 2020-05-29 21:18:50.829 UTC [INFO ] The task finished successfully. 14 rows selected.
In the following example, you list problem 3 only.
SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.list_adrci_problems(problem_id=>3); PL/SQL procedure successfully completed.
To read the log file for problem 3, call
rdsadmin.rds_file_util.read_text_file. Supply the task ID as part
of the file name.
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')); TEXT ------------------------------------------------------------------------- 2020-05-29 21:19:42.533 UTC [INFO ] Listing ADRCI problems. 2020-05-29 21:19:42.599 UTC [INFO ] ADR Home = /rdsdbdata/log/diag/rdbms/orcl_a/ORCL: ************************************************************************* PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME ---------- ----------------------------------------------------------- ------------- --------------------------------- 3 ORA 700 [EVENT_CREATED_INCIDENT] [942] [SIMULATED_ERROR_002 53522 2020-05-29 20:15:15.247000 +00:00 1 rows fetched 2020-05-29 21:19:42.599 UTC [INFO ] The ADRCI problems were successfully listed. 2020-05-29 21:19:42.599 UTC [INFO ] The task finished successfully. 12 rows selected.
Creating incident packages
You can create incident packages using the Amazon RDS function
rdsadmin.rdsadmin_adrci_util.create_adrci_package. The output is a
.zip file that you can supply to Oracle Support.
This function uses the following common parameters:
-
problem_id -
incident_id
Make sure to specify one of the preceding parameters. If you specify both
parameters, incident_id overrides problem_id. For more
information, see Common
parameters for diagnostic procedures.
To create a package for a specific incident, call the Amazon RDS function
rdsadmin.rdsadmin_adrci_util.create_adrci_package with the
incident_id parameter. The following example creates a package for
incident 53523.
SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.create_adrci_package(incident_id=>53523); PL/SQL procedure successfully completed.
To read the log file, call the rdsadmin.rds_file_util.read_text_file.
You can supply the task ID as part of the file name. The output shows that you
generated incident package
ORA700EVE_20200529212043_COM_1.zip.
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')); TEXT -------------------------------------------------------------------------------------------------------------------------------------- 2020-05-29 21:20:43.031 UTC [INFO ] The ADRCI package is being created. 2020-05-29 21:20:47.641 UTC [INFO ] Generated package 1 in file /rdsdbdata/log/trace/ORA700EVE_20200529212043_COM_1.zip, mode complete 2020-05-29 21:20:47.642 UTC [INFO ] The ADRCI package was successfully created. 2020-05-29 21:20:47.642 UTC [INFO ] The task finished successfully.
To package diagnostic data for a particular problem, specify its ID using the
problem_id parameter. In the following example, you package data
for problem 3 only.
SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.create_adrci_package(problem_id=>3); PL/SQL procedure successfully completed.
To read the task output, call rdsadmin.rds_file_util.read_text_file,
supplying the task ID as part of the file name. The output shows that you generated
incident package ORA700EVE_20200529212111_COM_1.zip.
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')); TEXT ------------------------------------------------------------------------------------------------------------------------------------------------------------ 2020-05-29 21:21:11.050 UTC [INFO ] The ADRCI package is being created. 2020-05-29 21:21:15.646 UTC [INFO ] Generated package 2 in file /rdsdbdata/log/trace/ORA700EVE_20200529212111_COM_1.zip, mode complete 2020-05-29 21:21:15.646 UTC [INFO ] The ADRCI package was successfully created. 2020-05-29 21:21:15.646 UTC [INFO ] The task finished successfully.
You can also download the log file. For more information, see Downloading a database log file.
Showing trace files
You can use the Amazon RDS function
rdsadmin.rdsadmin_adrci_util.show_adrci_tracefile to list trace
files under the trace directory and all incident directories under the current ADR
home. You can also show the contents of trace files and incident trace files.
This function uses the following parameter.
| Parameter name | Data type | Valid values | Default | Required | Description |
|---|---|---|---|---|---|
|
|
varchar2 |
A valid trace file name |
Null |
No |
If the value is null, the function shows all trace files. If it isn't null, the function shows the specified file. |
To show the trace file, call the Amazon RDS function
rdsadmin.rdsadmin_adrci_util.show_adrci_tracefile.
SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.show_adrci_tracefile; PL/SQL procedure successfully completed.
To list the trace file names, call the Amazon RDS procedure
rdsadmin.rds_file_util.read_text_file, supplying the task ID as
part of the file name.
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')) WHERE TEXT LIKE '%/alert_%'; TEXT --------------------------------------------------------------- diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-28 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-27 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-26 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-25 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-24 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-23 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-22 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log.2020-05-21 diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log 9 rows selected.
In the following example, you generate output for
alert_ORCL.log.
SQL> EXEC :task_id := rdsadmin.rdsadmin_adrci_util.show_adrci_tracefile('diag/rdbms/orcl_a/ORCL/trace/alert_ORCL.log'); PL/SQL procedure successfully completed.
To read the log file, call rdsadmin.rds_file_util.read_text_file.
Supply the task ID as part of the file name. The output shows the first 10 lines of
alert_ORCL.log.
SQL> SELECT * FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-'||:task_id||'.log')) WHERE ROWNUM <= 10; TEXT ----------------------------------------------------------------------------------------- 2020-05-29 21:24:02.083 UTC [INFO ] The trace files are being displayed. 2020-05-29 21:24:02.128 UTC [INFO ] Thu May 28 23:59:10 2020 Thread 1 advanced to log sequence 2048 (LGWR switch) Current log# 3 seq# 2048 mem# 0: /rdsdbdata/db/ORCL_A/onlinelog/o1_mf_3_hbl2p8xs_.log Thu May 28 23:59:10 2020 Archived Log entry 2037 added for thread 1 sequence 2047 ID 0x5d62ce43 dest 1: Fri May 29 00:04:10 2020 Thread 1 advanced to log sequence 2049 (LGWR switch) Current log# 4 seq# 2049 mem# 0: /rdsdbdata/db/ORCL_A/onlinelog/o1_mf_4_hbl2qgmh_.log Fri May 29 00:04:10 2020 10 rows selected.
You can also download the log file. For more information, see Downloading a database log file.
