# Administering your RDS for Oracle DB instance
Following are the common management tasks that you perform with an RDS for Oracle DB instance. Some tasks are the same for all RDS DB instances. Other tasks are specific to RDS for Oracle.
The following tasks are common to all RDS databases, but Oracle Database has special considerations. For example, you connect to an Oracle database using the Oracle clients SQL\$1Plus and SQL Developer.
****
| Task area | Relevant documentation |
| --- | --- |
| **Instance classes, storage, and PIOPS** If you are creating a production instance, learn how instance classes, storage types, and Provisioned IOPS work in Amazon RDS. | [RDS for Oracle DB instance classes](Oracle.Concepts.InstanceClasses.md) [Amazon RDS storage types](CHAP_Storage.md#Concepts.Storage) |
| **Multi-AZ deployments** A production DB instance should use Multi-AZ deployments. Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. | [Configuring and managing a Multi-AZ deployment for Amazon RDS](Concepts.MultiAZ.md) |
| **Amazon VPC** If your AWS account has a default virtual private cloud (VPC), then your DB instance is automatically created inside the default VPC. If your account doesn't have a default VPC, and you want the DB instance in a VPC, create the VPC and subnet groups before you create the instance. | [Working with a DB instance in a VPC](USER_VPC.WorkingWithRDSInstanceinaVPC.md) |
| **Security groups** By default, DB instances use a firewall that prevents access. Make sure that you create a security group with the correct IP addresses and network configuration to access the DB instance. | [Controlling access with security groups](Overview.RDSSecurityGroups.md) |
| **Parameter groups** If your DB instance is going to require specific database parameters, create a parameter group before you create the DB instance. | [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md) |
| **Option groups** If your DB instance requires specific database options, create an option group before you create the DB instance. | [Adding options to Oracle DB instances](Appendix.Oracle.Options.md) |
| **Connecting to your DB instance** After creating a security group and associating it to a DB instance, you can connect to the DB instance using any standard SQL client application such as Oracle SQL\$1Plus. | [Connecting to your Oracle DB instance](USER_ConnectToOracleInstance.md) |
| **Backup and restore** You can configure your DB instance to take automated backups, or take manual snapshots, and then restore instances from the backups or snapshots. | [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md) |
| **Monitoring** You can monitor an Oracle DB instance by using CloudWatch Amazon RDS metrics, events, and enhanced monitoring. | [Viewing metrics in the Amazon RDS console](USER_Monitoring.md) [Viewing Amazon RDS events](USER_ListEvents.md) |
| **Log files** You can access the log files for your Oracle DB instance. | [Monitoring Amazon RDS log files](USER_LogAccess.md) |
Following, you can find a description for Amazon RDS–specific implementations of common DBA tasks for RDS Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances. Also, RDS restricts access to certain system procedures and tables that require advanced privileges. In many of the tasks, you run the `rdsadmin` package, which is an Amazon RDS–specific tool that enables you to administer your database.
The following are common DBA tasks for DB instances running Oracle:
+ [System tasks](Appendix.Oracle.CommonDBATasks.System.md)
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
+ [Database tasks](Appendix.Oracle.CommonDBATasks.Database.md)
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
+ [Log tasks](Appendix.Oracle.CommonDBATasks.Log.md)
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
+ [RMAN tasks](Appendix.Oracle.CommonDBATasks.RMAN.md)
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
+ [Oracle Scheduler tasks](Appendix.Oracle.CommonDBATasks.Scheduler.md)
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
+ [Diagnosing problems](Appendix.Oracle.CommonDBATasks.Diagnostics.md)
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
+ [Other tasks](Appendix.Oracle.CommonDBATasks.Misc.md)
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html)
You can also use Amazon RDS procedures for Amazon S3 integration with Oracle and for running OEM Management Agent database tasks. For more information, see [Amazon S3 integration](oracle-s3-integration.md) and [Performing database tasks with the Management Agent](Oracle.Options.OEMAgent.md#Oracle.Options.OEMAgent.DBTasks).
# Performing common system tasks for Oracle DB instances
Following, you can find how to perform certain common DBA tasks related to the system on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and restricts access to certain system procedures and tables that require advanced privileges.
**Topics**
+ [Disconnecting a session](Appendix.Oracle.CommonDBATasks.DisconnectingSession.md)
+ [Terminating a session](Appendix.Oracle.CommonDBATasks.KillingSession.md)
+ [Canceling a SQL statement in a session](Appendix.Oracle.CommonDBATasks.CancellingSQL.md)
+ [Enabling and disabling restricted sessions](Appendix.Oracle.CommonDBATasks.RestrictedSession.md)
+ [Flushing the shared pool](Appendix.Oracle.CommonDBATasks.FlushingSharedPool.md)
+ [Granting SELECT or EXECUTE privileges to SYS objects](Appendix.Oracle.CommonDBATasks.TransferPrivileges.md)
+ [Revoking SELECT or EXECUTE privileges on SYS objects](Appendix.Oracle.CommonDBATasks.RevokePrivileges.md)
+ [Managing RDS\$1X\$1 views for Oracle DB instances](Appendix.Oracle.CommonDBATasks.X-dollar.md)
+ [Granting privileges to non-master users](Appendix.Oracle.CommonDBATasks.PermissionsNonMasters.md)
+ [Creating custom functions to verify passwords](Appendix.Oracle.CommonDBATasks.CustomPassword.md)
+ [Setting up a custom DNS server](#Appendix.Oracle.CommonDBATasks.CustomDNS)
+ [Setting and unsetting system diagnostic events](Appendix.Oracle.CommonDBATasks.SystemEvents.md)
# Disconnecting a session
To disconnect the current session by ending the dedicated server process, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.disconnect`. The `disconnect` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `sid` | number | — | Yes | The session identifier. |
| `serial` | number | — | Yes | The serial number of the session. |
| `method` | varchar | 'IMMEDIATE' | No | Valid values are `'IMMEDIATE'` or `'POST_TRANSACTION'`. |
The following example disconnects a session.
```
begin
rdsadmin.rdsadmin_util.disconnect(
sid => sid,
serial => serial_number);
end;
/
```
To get the session identifier and the session serial number, query the `V$SESSION` view. The following example gets all sessions for the user `AWSUSER`.
```
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'AWSUSER';
```
The database must be open to use this method. For more information about disconnecting a session, see [ALTER SYSTEM](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#SQLRF53166) in the Oracle documentation.
# Terminating a session
To terminate a session, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.kill`. The `kill` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `sid` | number | — | Yes | The session identifier. |
| `serial` | number | — | Yes | The serial number of the session. |
| `method` | varchar | null | No | Valid values are `'IMMEDIATE'` or `'PROCESS'`. If you specify `IMMEDIATE`, it has the same effect as running the following statement: ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE
If you specify `PROCESS`, you terminate the processes associated with a session. Only specify `PROCESS` if terminating the session using `IMMEDIATE` was unsuccessful. |
To get the session identifier and the session serial number, query the `V$SESSION` view. The following example gets all sessions for the user *AWSUSER*.
```
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'AWSUSER';
```
The following example terminates a session.
```
BEGIN
rdsadmin.rdsadmin_util.kill(
sid => sid,
serial => serial_number,
method => 'IMMEDIATE');
END;
/
```
The following example terminates the processes associated with a session.
```
BEGIN
rdsadmin.rdsadmin_util.kill(
sid => sid,
serial => serial_number,
method => 'PROCESS');
END;
/
```
# Canceling a SQL statement in a session
To cancel a SQL statement in a session, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.cancel`.
**Note**
This procedure is supported for Oracle Database 19c (19.0.0) and all higher major and minor versions of RDS for Oracle.
The `cancel` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `sid` | number | — | Yes | The session identifier. |
| `serial` | number | — | Yes | The serial number of the session. |
| `sql_id` | varchar2 | null | No | The SQL identifier of the SQL statement. |
The following example cancels a SQL statement in a session.
```
begin
rdsadmin.rdsadmin_util.cancel(
sid => sid,
serial => serial_number,
sql_id => sql_id);
end;
/
```
To get the session identifier, the session serial number, and the SQL identifier of a SQL statement, query the `V$SESSION` view. The following example gets all sessions and SQL identifiers for the user `AWSUSER`.
```
select SID, SERIAL#, SQL_ID, STATUS from V$SESSION where USERNAME = 'AWSUSER';
```
# Enabling and disabling restricted sessions
To enable and disable restricted sessions, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.restricted_session`. The `restricted_session` procedure has the following parameters.
****
| Parameter name | Data type | Default | Yes | Description |
| --- | --- | --- | --- | --- |
| `p_enable` | boolean | true | No | Set to `true` to enable restricted sessions, `false` to disable restricted sessions. |
The following example shows how to enable and disable restricted sessions.
```
/* Verify that the database is currently unrestricted. */
SELECT LOGINS FROM V$INSTANCE;
LOGINS
-------
ALLOWED
/* Enable restricted sessions */
EXEC rdsadmin.rdsadmin_util.restricted_session(p_enable => true);
/* Verify that the database is now restricted. */
SELECT LOGINS FROM V$INSTANCE;
LOGINS
----------
RESTRICTED
/* Disable restricted sessions */
EXEC rdsadmin.rdsadmin_util.restricted_session(p_enable => false);
/* Verify that the database is now unrestricted again. */
SELECT LOGINS FROM V$INSTANCE;
LOGINS
-------
ALLOWED
```
# Flushing the shared pool
To flush the shared pool, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.flush_shared_pool`. The `flush_shared_pool` procedure has no parameters.
The following example flushes the shared pool.
```
EXEC rdsadmin.rdsadmin_util.flush_shared_pool;
```
## Flushing the buffer cache
To flush the buffer cache, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.flush_buffer_cache`. The `flush_buffer_cache` procedure has no parameters.
The following example flushes the buffer cache.
```
EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;
```
## Flushing the database smart flash cache
To flush the database smart flash cache, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.flush_flash_cache`. The `flush_flash_cache` procedure has no parameters. The following example flushes the database smart flash cache.
```
EXEC rdsadmin.rdsadmin_util.flush_flash_cache;
```
For more information about using the database smart flash cache with RDS for Oracle, see [Storing temporary data in an RDS for Oracle instance store](CHAP_Oracle.advanced-features.instance-store.md).
# Granting SELECT or EXECUTE privileges to SYS objects
Usually you transfer privileges by using roles, which can contain many objects. To grant privileges to a single object, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.grant_sys_object`. The procedure grants only privileges that the master user has already been granted through a role or direct grant.
The `grant_sys_object` procedure has the following parameters.
**Important**
For all parameter values, use uppercase unless you created the user with a case-sensitive identifier. For example, if you run `CREATE USER myuser` or `CREATE USER MYUSER`, the data dictionary stores `MYUSER`. However, if you use double quotes in `CREATE USER "MyUser"`, the data dictionary stores `MyUser`.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_obj_name` | varchar2 | — | Yes | The name of the object to grant privileges for. The object can be a directory, function, package, procedure, sequence, table, or view. Object names must be spelled exactly as they appear in `DBA_OBJECTS`. Most system objects are defined in uppercase, so we recommend that you try that first. |
| `p_grantee` | varchar2 | — | Yes | The name of the object to grant privileges to. The object can be a schema or a role. |
| `p_privilege` | varchar2 | null | Yes | — |
| `p_grant_option` | boolean | false | No | Set to `true` to use the with grant option. |
The following example grants select privileges on an object named `V_$SESSION` to a user named `USER1`.
```
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$SESSION',
p_grantee => 'USER1',
p_privilege => 'SELECT');
end;
/
```
The following example grants select privileges on an object named `V_$SESSION` to a user named `USER1` with the grant option.
```
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$SESSION',
p_grantee => 'USER1',
p_privilege => 'SELECT',
p_grant_option => true);
end;
/
```
To be able to grant privileges on an object, your account must have those privileges granted to it directly with the grant option, or via a role granted using `with admin option`. In the most common case, you may want to grant `SELECT` on a DBA view that has been granted to the `SELECT_CATALOG_ROLE` role. If that role isn't already directly granted to your user using `with admin option`, then you can't transfer the privilege. If you have the DBA privilege, then you can grant the role directly to another user.
The following example grants the `SELECT_CATALOG_ROLE` and `EXECUTE_CATALOG_ROLE` to `USER1`. Since the `with admin option` is used, `USER1` can now grant access to SYS objects that have been granted to `SELECT_CATALOG_ROLE`.
```
GRANT SELECT_CATALOG_ROLE TO USER1 WITH ADMIN OPTION;
GRANT EXECUTE_CATALOG_ROLE to USER1 WITH ADMIN OPTION;
```
Objects already granted to `PUBLIC` do not need to be re-granted. If you use the `grant_sys_object` procedure to re-grant access, the procedure call succeeds.
# Revoking SELECT or EXECUTE privileges on SYS objects
To revoke privileges on a single object, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.revoke_sys_object`. The procedure only revokes privileges that the master account has already been granted through a role or direct grant.
The `revoke_sys_object` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_obj_name` | varchar2 | — | Yes | The name of the object to revoke privileges for. The object can be a directory, function, package, procedure, sequence, table, or view. Object names must be spelled exactly as they appear in `DBA_OBJECTS`. Most system objects are defined in upper case, so we recommend you try that first. |
| `p_revokee` | varchar2 | — | Yes | The name of the object to revoke privileges for. The object can be a schema or a role. |
| `p_privilege` | varchar2 | null | Yes | — |
The following example revokes select privileges on an object named `V_$SESSION` from a user named `USER1`.
```
begin
rdsadmin.rdsadmin_util.revoke_sys_object(
p_obj_name => 'V_$SESSION',
p_revokee => 'USER1',
p_privilege => 'SELECT');
end;
/
```
# Managing RDS\$1X\$1 views for Oracle DB instances
You might need to access `SYS.X$` fixed tables, which are only accessible by `SYS`. To create `SYS.RDS_X$` views on eligible `X$` tables, use the procedures in the `rdsadmin.rdsadmin_util` package. Your master user is automatically granted the privilege `SELECT … WITH GRANT OPTION` on the `RDS_X$` views.
The `rdsadmin.rdsadmin_util` procedures are available in the following cases:
+ Existing DB instances that have never been upgraded and use the following releases:
+ `21.0.0.0.ru-2023-10.rur-2023-10.r1` and higher 21c releases
+ `19.0.0.0.ru-2023-10.rur-2023-10.r1` and higher 19c releases
+ Any new DB instance that you create
+ Any existing DB instance that you have upgraded
**Important**
Internally, the `rdsadmin.rdsadmin_util` package creates views on `X$` tables. The `X$` tables are internal system objects that aren’t described in the Oracle Database documentation. We recommend that you test specific views in your non-production database and only create views in your production database under the guidance of Oracle Support.
## List X\$1 fixed tables eligible for use in RDS\$1X\$1 views
To list X\$1 tables that are eligible for use in `RDS_X$` views, use the RDS procedure `rdsadmin.rdsadmin_util.list_allowed_sys_x$_views`. This procedure accepts no parameters. The following statements lists all eligible `X$` tables (sample output included).
```
SQL> SET SERVEROUTPUT ON
SQL> SELECT * FROM TABLE(rdsadmin.rdsadmin_util.list_allowed_sys_x$_views);
'X$BH'
'X$K2GTE'
'X$KCBWBPD'
'X$KCBWDS'
'X$KGLLK'
'X$KGLOB'
'X$KGLPN'
'X$KSLHOT'
'X$KSMSP'
'X$KSPPCV'
'X$KSPPI'
'X$KSPPSV'
'X$KSQEQ'
'X$KSQRS'
'X$KTUXE'
'X$KQRFP'
```
The list of eligible `X$` tables can change over time. To make sure that your list of eligible `X$` fixed tables is current, rerun `list_allowed_sys_x$_views` periodically.
## Creating SYS.RDS\$1X\$1 views
To create an `RDS_X$` view on an eligible `X$` table, use the RDS procedure `rdsadmin.rdsadmin_util.create_sys_x$_view`. You can only create views for the tables listed in the output of `rdsadmin.rdsadmin_util.list_allowed_sys_x$_views`. The `create_sys_x$_view` procedure accepts the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_x$_tbl` | varchar2 | Null | Yes | A valid `X$` table name. The value must be one of the `X$` tables reported by `list_allowed_sys_x$_views`. |
| `p_force_creation` | Boolean | FALSE | No | A value indicating whether to force creation of an `RDS_X$` view that already exists for an `X$` table. By default, RDS won't create a view if it already exists. To force creation, set this parameter to `TRUE`. |
The following example creates the `SYS.RDS_X$KGLOB` view on the table `X$KGLOB`. The format for the view name is `RDS_X$tablename`.
```
SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.create_sys_x$_view('X$KGLOB');
PL/SQL procedure successfully completed.
```
The following data dictionary query lists the view `SYS.RDS_X$KGLOB` and shows its status. Your master user is automatically granted the privilege `SELECT ... WITH GRANT OPTION` on this view.
```
SQL> SET SERVEROUTPUT ON
SQL> COL OWNER FORMAT A30
SQL> COL OBJECT_NAME FORMAT A30
SQL> COL STATUS FORMAT A30
SQL> SET LINESIZE 200
SQL> SELECT OWNER, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'SYS' AND OBJECT_NAME = 'RDS_X$KGLOB';
OWNER OBJECT_NAME STATUS
------------------------------ ------------------------------ ------------------------------
SYS RDS_X$KGLOB VALID
```
**Important**
`X$` tables aren't guaranteed to stay the same before and after an upgrade. RDS for Oracle drops and recreates the `RDS_X$` views on `X$` tables during an engine upgrade. Then it grants the `SELECT ... WITH GRANT OPTION` privilege to the master user. After an upgrade, grant privileges to database users as needed on the corresponding `RDS_X$` views.
## Listing SYS.RDS\$1X\$1 views
To list existing `RDS_X$` views, use the RDS procedure `rdsadmin.rdsadmin_util.list_created_sys_x$_views`. The procedure lists only views that were created by the procedure `create_sys_x$_view`. The following example lists `X$` tables that have corresponding `RDS_X$` views (sample output included).
```
SQL> SET SERVEROUTPUT ON
SQL> COL XD_TBL_NAME FORMAT A30
SQL> COL STATUS FORMAT A30
SQL> SET LINESIZE 200
SQL> SELECT * FROM TABLE(rdsadmin.rdsadmin_util.list_created_sys_x$_views);
XD_TBL_NAME STATUS
------------------------------ ------------------------------
X$BH VALID
X$K2GTE VALID
X$KCBWBPD VALID
3 rows selected.
```
## Dropping RDS\$1X\$1 views
To drop a `SYS.RDS_X$` view, use the RDS procedure `rdsadmin.rdsadmin_util.drop_sys_x$_view`. You can only drop views listed in the output of `rdsadmin.rdsadmin_util.list_allowed_sys_x$_views`. The `drop_sys_x$_view` procedure accepts the following parameter.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_x$_tbl` | varchar2 | Null | Yes | A valid `X$` fixed table name. The value must be one of the `X$` fixed tables reported by `list_created_sys_x$_views`. |
The following example drops the `RDS_X$KGLOB` view, which was created on the table `X$KGLOB`.
```
SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.drop_sys_x$_view('X$KGLOB');
PL/SQL procedure successfully completed.
```
The following example shows that the view `SYS.RDS_X$KGLOB` has been dropped (sample output included).
```
SQL> SET SERVEROUTPUT ON
SQL> COL OWNER FORMAT A30
SQL> COL OBJECT_NAME FORMAT A30
SQL> COL STATUS FORMAT A30
SQL> SET LINESIZE 200
SQL> SELECT OWNER, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'SYS' AND OBJECT_NAME = 'RDS_X$KGLOB';
no rows selected
```
# Granting privileges to non-master users
You can grant select privileges for many objects in the `SYS` schema by using the `SELECT_CATALOG_ROLE` role. The `SELECT_CATALOG_ROLE` role gives users `SELECT` privileges on data dictionary views. The following example grants the role `SELECT_CATALOG_ROLE` to a user named `user1`.
```
GRANT SELECT_CATALOG_ROLE TO user1;
```
You can grant `EXECUTE` privileges for many objects in the `SYS` schema by using the `EXECUTE_CATALOG_ROLE` role. The `EXECUTE_CATALOG_ROLE` role gives users `EXECUTE` privileges for packages and procedures in the data dictionary. The following example grants the role `EXECUTE_CATALOG_ROLE` to a user named *user1*.
```
GRANT EXECUTE_CATALOG_ROLE TO user1;
```
The following example gets the permissions that the roles `SELECT_CATALOG_ROLE` and `EXECUTE_CATALOG_ROLE` allow.
```
SELECT *
FROM ROLE_TAB_PRIVS
WHERE ROLE IN ('SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE')
ORDER BY ROLE, TABLE_NAME ASC;
```
The following example creates a non-master user named `user1`, grants the `CREATE SESSION` privilege, and grants the `SELECT` privilege on a database named *sh.sales*.
```
CREATE USER user1 IDENTIFIED BY PASSWORD;
GRANT CREATE SESSION TO user1;
GRANT SELECT ON sh.sales TO user1;
```
# Creating custom functions to verify passwords
You can create a custom password verification function in the following ways:
+ To use standard verification logic, and to store your function in the `SYS` schema, use the `create_verify_function` procedure.
+ To use custom verification logic, or to avoid storing your function in the `SYS` schema, use the `create_passthrough_verify_fcn` procedure.
# The create\$1verify\$1function procedure
You can create a custom function to verify passwords by using the Amazon RDS procedure `rdsadmin.rdsadmin_password_verify.create_verify_function`. The `create_verify_function` procedure is supported for all versions of RDS for Oracle.
The `create_verify_function` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_verify_function_name` | varchar2 | — | Yes | The name for your custom function. This function is created for you in the SYS schema. You assign this function to user profiles. |
| `p_min_length` | number | 8 | No | The minimum number of characters required. |
| `p_max_length` | number | 256 | No | The maximum number of characters allowed. |
| `p_min_letters` | number | 1 | No | The minimum number of letters required. |
| `p_min_uppercase` | number | 0 | No | The minimum number of uppercase letters required. |
| `p_min_lowercase` | number | 0 | No | The minimum number of lowercase letters required. |
| `p_min_digits` | number | 1 | No | The minimum number of digits required. |
| `p_min_special` | number | 0 | No | The minimum number of special characters required. |
| `p_min_different_chars` | number | 3 | No | The minimum number of different characters required between the old and new password. |
| `p_disallow_username` | boolean | true | No | Set to `true` to disallow the user name in the password. |
| `p_disallow_reverse` | boolean | true | No | Set to `true` to disallow the reverse of the user name in the password. |
| `p_disallow_db_name` | boolean | true | No | Set to `true` to disallow the database or server name in the password. |
| `p_disallow_simple_strings` | boolean | true | No | Set to `true` to disallow simple strings as the password. |
| `p_disallow_whitespace` | boolean | false | No | Set to `true` to disallow white space characters in the password. |
| `p_disallow_at_sign` | boolean | false | No | Set to `true` to disallow the @ character in the password. |
You can create multiple password verification functions.
There are restrictions on the name of your custom function. Your custom function can't have the same name as an existing system object. The name can be no more than 30 characters long. Also, the name must include one of the following strings: `PASSWORD`, `VERIFY`, `COMPLEXITY`, `ENFORCE`, or `STRENGTH`.
The following example creates a function named `CUSTOM_PASSWORD_FUNCTION`. The function requires that a password has at least 12 characters, 2 uppercase characters, 1 digit, and 1 special character, and that the password disallows the @ character.
```
begin
rdsadmin.rdsadmin_password_verify.create_verify_function(
p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION',
p_min_length => 12,
p_min_uppercase => 2,
p_min_digits => 1,
p_min_special => 1,
p_disallow_at_sign => true);
end;
/
```
To see the text of your verification function, query `DBA_SOURCE`. The following example gets the text of a custom password function named `CUSTOM_PASSWORD_FUNCTION`.
```
COL TEXT FORMAT a150
SELECT TEXT
FROM DBA_SOURCE
WHERE OWNER = 'SYS'
AND NAME = 'CUSTOM_PASSWORD_FUNCTION'
ORDER BY LINE;
```
To associate your verification function with a user profile, use `ALTER PROFILE`. The following example associates a verification PL/SQL function named `CUSTOM_PASSWORD_FUNCTION` with the `DEFAULT` user profile. `PASSWORD_VERIFY_FUNCTION` is the Oracle profile resource name.
```
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION CUSTOM_PASSWORD_FUNCTION;
```
To see which user profiles are associated with which verification functions, query `DBA_PROFILES`. The following example gets the profiles that are associated with the custom verification function named `CUSTOM_PASSWORD_FUNCTION`.
```
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION' AND LIMIT = 'CUSTOM_PASSWORD_FUNCTION';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------- -------------------------------- -------- ------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD CUSTOM_PASSWORD_FUNCTION
```
The following example gets all profiles and the password verification functions that they are associated with.
```
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------- -------------------------------- -------- ------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD CUSTOM_PASSWORD_FUNCTION
RDSADMIN PASSWORD_VERIFY_FUNCTION PASSWORD NULL
```
# The create\$1passthrough\$1verify\$1fcn procedure
The `create_passthrough_verify_fcn` procedure is supported for all versions of RDS for Oracle.
You can create a custom function to verify passwords by using the Amazon RDS procedure `rdsadmin.rdsadmin_password_verify.create_passthrough_verify_fcn`. The `create_passthrough_verify_fcn` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_verify_function_name` | varchar2 | — | Yes | The name for your custom verification function. This is a wrapper function that is created for you in the SYS schema, and it doesn't contain any verification logic. You assign this function to user profiles. |
| `p_target_owner` | varchar2 | — | Yes | The schema owner for your custom verification function. |
| `p_target_function_name` | varchar2 | — | Yes | The name of your existing custom function that contains the verification logic. Your custom function must return a boolean. Your function should return `true` if the password is valid and `false` if the password is invalid. |
The following example creates a password verification function that uses the logic from the function named `PASSWORD_LOGIC_EXTRA_STRONG`.
```
begin
rdsadmin.rdsadmin_password_verify.create_passthrough_verify_fcn(
p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION',
p_target_owner => 'TEST_USER',
p_target_function_name => 'PASSWORD_LOGIC_EXTRA_STRONG');
end;
/
```
To associate the verification function with a user profile, use `alter profile`. The following example associates the verification function with the `DEFAULT` user profile.
```
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION CUSTOM_PASSWORD_FUNCTION;
```
## Setting up a custom DNS server
Amazon RDS supports outbound network access on your DB instances running Oracle. For more information about outbound network access, including prerequisites, see [Configuring UTL\$1HTTP access using certificates and an Oracle wallet](Oracle.Concepts.ONA.md).
Amazon RDS Oracle allows Domain Name Service (DNS) resolution from a custom DNS server owned by the customer. You can resolve only fully qualified domain names from your Amazon RDS DB instance through your custom DNS server.
After you set up your custom DNS name server, it takes up to 30 minutes to propagate the changes to your DB instance. After the changes are propagated to your DB instance, all outbound network traffic requiring a DNS lookup queries your DNS server over port 53.
To set up a custom DNS server for your Amazon RDS for Oracle DB instance, do the following:
+ From the DHCP options set attached to your virtual private cloud (VPC), set the `domain-name-servers` option to the IP address of your DNS name server. For more information, see [DHCP options sets](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_DHCP_Options.html).
**Note**
The `domain-name-servers` option accepts up to four values, but your Amazon RDS DB instance uses only the first value.
+ Ensure that your DNS server can resolve all lookup queries, including public DNS names, Amazon EC2 private DNS names, and customer-specific DNS names. If the outbound network traffic contains any DNS lookups that your DNS server can't handle, your DNS server must have appropriate upstream DNS providers configured.
+ Configure your DNS server to produce User Datagram Protocol (UDP) responses of 512 bytes or less.
+ Configure your DNS server to produce Transmission Control Protocol (TCP) responses of 1024 bytes or less.
+ Configure your DNS server to allow inbound traffic from your Amazon RDS DB instances over port 53. If your DNS server is in an Amazon VPC, the VPC must have a security group that contains inbound rules that permit UDP and TCP traffic on port 53. If your DNS server is not in an Amazon VPC, it must have appropriate firewall allow-listing to permit UDP and TCP inbound traffic on port 53.
For more information, see [Security groups for your VPC](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_SecurityGroups.html) and [Adding and removing rules](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_SecurityGroups.html#AddRemoveRules).
+ Configure the VPC of your Amazon RDS DB instance to allow outbound traffic over port 53. Your VPC must have a security group that contains outbound rules that allow UDP and TCP traffic on port 53.
For more information, see [Security groups for your VPC](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_SecurityGroups.html) and [Adding and removing rules](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_SecurityGroups.html#AddRemoveRules).
+ The routing path between the Amazon RDS DB instance and the DNS server has to be configured correctly to allow DNS traffic.
+ If the Amazon RDS DB instance and the DNS server are not in the same VPC, a peering connection has to be set up between them. For more information, see [What is VPC peering?](https://docs.aws.amazon.com/vpc/latest/peering/Welcome.html)
# Setting and unsetting system diagnostic events
To set and unset diagnostic events at the session level, you can use the Oracle SQL statement `ALTER SESSION SET EVENTS`. However, to set events at the system level you can't use Oracle SQL. Instead, use the system event procedures in the `rdsadmin.rdsadmin_util` package. The system event procedures are available in the following engine versions:
+ All Oracle Database 21c versions
+ 19.0.0.0.ru-2020-10.rur-2020-10.r1 and higher Oracle Database 19c versions
For more information, see [Version 19.0.0.0.ru-2020-10.rur-2020-10.r1](https://docs.aws.amazon.com/AmazonRDS/latest/OracleReleaseNotes/oracle-version-19-0.html#oracle-version-RU-RUR.19.0.0.0.ru-2020-10.rur-2020-10.r1) in the *Amazon RDS for Oracle Release Notes*
**Important**
Internally, the `rdsadmin.rdsadmin_util` package sets events by using the `ALTER SYSTEM SET EVENTS` statement. This `ALTER SYSTEM` statement isn't documented in the Oracle Database documentation. Some system diagnostic events can generate large amounts of tracing information, cause contention, or affect database availability. We recommend that you test specific diagnostic events in your nonproduction database, and only set events in your production database under guidance of Oracle Support.
## Listing allowed system diagnostic events
To list the system events that you can set, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.list_allowed_system_events`. This procedure accepts no parameters.
The following example lists all system events that you can set.
```
SET SERVEROUTPUT ON
EXEC rdsadmin.rdsadmin_util.list_allowed_system_events;
```
The following sample output lists event numbers and their descriptions. Use the Amazon RDS procedures `set_system_event` to set these events and `unset_system_event` to unset them.
```
604 - error occurred at recursive SQL level
942 - table or view does not exist
1401 - inserted value too large for column
1403 - no data found
1410 - invalid ROWID
1422 - exact fetch returns more than requested number of rows
1426 - numeric overflow
1427 - single-row subquery returns more than one row
1476 - divisor is equal to zero
1483 - invalid length for DATE or NUMBER bind variable
1489 - result of string concatenation is too long
1652 - unable to extend temp segment by in tablespace
1858 - a non-numeric character was found where a numeric was expected
4031 - unable to allocate bytes of shared memory ("","","","")
6502 - PL/SQL: numeric or value error
10027 - Specify Deadlock Trace Information to be Dumped
10046 - enable SQL statement timing
10053 - CBO Enable optimizer trace
10173 - Dynamic Sampling time-out error
10442 - enable trace of kst for ORA-01555 diagnostics
12008 - error in materialized view refresh path
12012 - error on auto execute of job
12504 - TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
14400 - inserted partition key does not map to any partition
31693 - Table data object failed to load/unload and is being skipped due to error:
```
**Note**
The list of the allowed system events can change over time. To make sure that you have the most recent list of eligible events, use `rdsadmin.rdsadmin_util.list_allowed_system_events`.
## Setting system diagnostic events
To set a system event, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.set_system_event`. You can only set events listed in the output of `rdsadmin.rdsadmin_util.list_allowed_system_events`. The `set_system_event` procedure accepts the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_event` | number | — | Yes | The system event number. The value must be one of the event numbers reported by `list_allowed_system_events`. |
| `p_level` | number | — | Yes | The event level. See the Oracle Database documentation or Oracle Support for descriptions of different level values. |
The procedure `set_system_event` constructs and runs the required `ALTER SYSTEM SET EVENTS` statements according to the following principles:
+ The event type (`context` or `errorstack`) is determined automatically.
+ A statement in the form `ALTER SYSTEM SET EVENTS 'event LEVEL event_level'` sets the context events. This notation is equivalent to `ALTER SYSTEM SET EVENTS 'event TRACE NAME CONTEXT FOREVER, LEVEL event_level'`.
+ A statement in the form `ALTER SYSTEM SET EVENTS 'event ERRORSTACK (event_level)'` sets the error stack events. This notation is equivalent to `ALTER SYSTEM SET EVENTS 'event TRACE NAME ERRORSTACK LEVEL event_level'`.
The following example sets event 942 at level 3, and event 10442 at level 10. Sample output is included.
```
SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.set_system_event(942,3);
Setting system event 942 with: alter system set events '942 errorstack (3)'
PL/SQL procedure successfully completed.
SQL> EXEC rdsadmin.rdsadmin_util.set_system_event(10442,10);
Setting system event 10442 with: alter system set events '10442 level 10'
PL/SQL procedure successfully completed.
```
## Listing system diagnostic events that are set
To list the system events that are currently set, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.list_set_system_events`. This procedure reports only events set at system level by `set_system_event`.
The following example lists the active system events.
```
SET SERVEROUTPUT ON
EXEC rdsadmin.rdsadmin_util.list_set_system_events;
```
The following sample output shows the list of events, the event type, the level at which the events are currently set, and the time when the event was set.
```
942 errorstack (3) - set at 2020-11-03 11:42:27
10442 level 10 - set at 2020-11-03 11:42:41
PL/SQL procedure successfully completed.
```
## Unsetting system diagnostic events
To unset a system event, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.unset_system_event`. You can only unset events listed in the output of `rdsadmin.rdsadmin_util.list_allowed_system_events`. The `unset_system_event` procedure accepts the following parameter.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_event` | number | — | Yes | The system event number. The value must be one of the event numbers reported by `list_allowed_system_events`. |
The following example unsets events 942 and 10442. Sample output is included.
```
SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.unset_system_event(942);
Unsetting system event 942 with: alter system set events '942 off'
PL/SQL procedure successfully completed.
SQL> EXEC rdsadmin.rdsadmin_util.unset_system_event(10442);
Unsetting system event 10442 with: alter system set events '10442 off'
PL/SQL procedure successfully completed.
```
# Performing common database tasks for Oracle DB instances
Following, you can find how to perform certain common DBA tasks related to databases on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances. Amazon RDS also restricts access to some system procedures and tables that require advanced privileges.
**Topics**
+ [Changing the global name of a database](Appendix.Oracle.CommonDBATasks.RenamingGlobalName.md)
+ [Working with tablespaces in RDS for Oracle](Appendix.Oracle.CommonDBATasks.TablespacesAndDatafiles.md)
+ [Working with tempfiles in RDS for Oracle](Appendix.Oracle.CommonDBATasks.using-tempfiles.md)
+ [Resizing tablespaces, data files, and tempfiles in RDS for Oracle](Appendix.Oracle.CommonDBATasks.ResizeTempSpaceReadReplica.md)
+ [Moving data between storage volumes in RDS for Oracle](Appendix.Oracle.CommonDBATasks.MovingDataBetweenVolumes.md)
+ [Working with external tables in RDS for Oracle](Appendix.Oracle.CommonDBATasks.External_Tables.md)
# Changing the global name of a database
To change the global name of a database, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.rename_global_name`. The `rename_global_name` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_new_global_name` | varchar2 | — | Yes | The new global name for the database. |
The database must be open for the name change to occur. For more information about changing the global name of a database, see [ALTER DATABASE](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1004.htm#SQLRF52547) in the Oracle documentation.
The following example changes the global name of a database to `new_global_name`.
```
EXEC rdsadmin.rdsadmin_util.rename_global_name(p_new_global_name => 'new_global_name');
```
# Working with tablespaces in RDS for Oracle
You can use tablespaces with RDS for Oracle, which is a logical storage unit that stores the database's data.
**Important**
If your DB instance has replicas, we recommend using parameter group settings instead of session-level changes to manage default file locations. Session-level changes to default file locations in the primary instance are not automatically reflected in the replicas. Using parameter group settings ensures consistent file locations across your primary and replica instances.
**Topics**
+ [Specifying database file locations in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.DatabaseFileLocations)
+ [Creating and sizing tablespaces in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.CreatingTablespacesAndDatafiles)
+ [Creating tablespaces on additional storage volumes in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.CreatingTablespacesWithFileLocations)
+ [Setting the default tablespace in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.SettingDefaultTablespace)
+ [Setting the default temporary tablespace in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.SettingDefTempTablespace)
+ [Creating a temporary tablespace on the instance store](#Appendix.Oracle.CommonDBATasks.creating-tts-instance-store)
## Specifying database file locations in RDS for Oracle
RDS for Oracle uses Oracle Managed Files (OMF) to name database files. When you create database files the database derives the setting based on the current setting of the `DB_CREATE_FILE_DEST` initialization parameter.
The default value of the `DB_CREATE_FILE_DEST` initialization parameter is `/rdsdbdata/db` for standalone databases and `/rdsdbdata/db/pdb` for containerized (CDB/MT) architecture. If your DB instance has additional storage volumes, then you can set `DB_CREATE_FILE_DEST` to your volume locations. For example, if your instance has a volume mounted on `/rdsdbdata/db`, you can set `DB_CREATE_FILE_DEST` to this value.
You can modify the `DB_CREATE_FILE_DEST` parameter at either the session level or Oracle database instance level.
### Modifying DB\$1CREATE\$1FILE\$1SET at the instance level
To modify the parameter at the instance level, update the parameter in the parameter group assigned to your DB instance and apply it. For more information, see [RDS for Oracle initialization parameters](Oracle.Concepts.FeatureSupport.Parameters.md) and [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md).
### Modifying DB\$1CREATE\$1FILE\$1DEST at the session level
You can modify the parameter at the session level by executing an `ALTER SESSION` statement. This approach is useful when you want to create database files in a specific location for a particular session without affecting the entire instance.
The following example shows how to check the current parameter value and modify it for the session:
```
SHOW PARAMETER db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /rdsdbdata/db
ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';
Session altered.
SHOW PARAMETER db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /rdsdbdata2/db
```
## Creating and sizing tablespaces in RDS for Oracle
When you create tablespaces, the database creates the data files in the storage volume specified by the `DB_CREATE_FILE_DEST` initialization parameter at the time of creation. By default, if you don't specify a data file size, tablespaces are created with the default of `AUTOEXTEND ON`, and no maximum size. In the following example, the tablespace *users1* is autoextensible.
```
CREATE TABLESPACE users1;
```
Because of these default settings, tablespaces can grow to consume all allocated storage. We recommend that you specify an appropriate maximum size on permanent and temporary tablespaces, and that you carefully monitor space usage.
The following example creates a tablespace named *users2* with a starting size of 1 gigabyte. Because a data file size is specified, but `AUTOEXTEND ON` isn't specified, the tablespace isn't autoextensible.
```
CREATE TABLESPACE users2 DATAFILE SIZE 1G;
```
The following example creates a tablespace named *users3* with a starting size of 1 gigabyte, autoextend turned on, and a maximum size of 10 gigabytes.
```
CREATE TABLESPACE users3 DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 10G;
```
The following example creates a temporary tablespace named *temp01*.
```
CREATE TEMPORARY TABLESPACE temp01;
```
You can resize a bigfile tablespace by using `ALTER TABLESPACE`. You can specify the size in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T). The following example resizes a bigfile tablespace named *users\$1bf* to 200 MB.
```
ALTER TABLESPACE users_bf RESIZE 200M;
```
The following example adds an additional data file to a smallfile tablespace named *users\$1sf*.
```
ALTER TABLESPACE users_sf ADD DATAFILE SIZE 100000M AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;
```
## Creating tablespaces on additional storage volumes in RDS for Oracle
To create a tablespace on an additional storage volume, modify the `DB_CREATE_FILE_DEST` parameter to the volume location. The following example sets the file location to `/rdsdbdata2/db`.
```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';
Session altered.
```
In the following example, you create a tablespace on the additional volume `/rdsdbdata2/db`.
```
CREATE TABLESPACE new_tablespace DATAFILE SIZE 10G;
Tablespace created.
SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'NEW_TABLESPACE';
TABLESPACE_NAME FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
NEW_TABLESPACE 7 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_newtable_a123b4c5_.dbf
```
To create a smallfile tablespace and spread its data files across different storage volumes, add data files to the tablespace after you create it. In the following example, you create a tablespace with the data files in the default location of `/rdsdbdata/db`. Then you set the default destination to `/rdsdbdata/db2`. When you add a data file to your newly created tablespace, the database stores the file in `/rdsdbdata/db2`.
```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata/db';
Session altered.
CREATE SMALLFILE TABLESPACE smalltbs DATAFILE SIZE 10G;
Tablespace created.
SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'SMALLTBS';
TABLESPACE_NAME FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
SMALLTBS 8 /rdsdbdata/db/ORCL_A/datafile/o1_mf_smalltbs_n563yryk_.dbf
ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';
Session altered.
ALTER TABLESPACE smalltbs ADD DATAFILE SIZE 10G;
Tablespace altered.
SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'SMALLTBS';
TABLESPACE_NAME FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
SMALLTBS 8 /rdsdbdata/db/ORCL_A/datafile/o1_mf_smalltbs_n563yryk_.dbf
SMALLTBS 9 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_smalltbs_n564004g_.dbf
```
## Setting the default tablespace in RDS for Oracle
To set the default tablespace, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.alter_default_tablespace`. The `alter_default_tablespace` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `tablespace_name` | varchar | — | Yes | The name of the default tablespace. |
The following example sets the default tablespace to *users2*:
```
EXEC rdsadmin.rdsadmin_util.alter_default_tablespace(tablespace_name => 'users2');
```
## Setting the default temporary tablespace in RDS for Oracle
To set the default temporary tablespace, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.alter_default_temp_tablespace`. The `alter_default_temp_tablespace` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `tablespace_name` | varchar | — | Yes | The name of the default temporary tablespace. |
The following example sets the default temporary tablespace to *temp01*.
```
EXEC rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp01');
```
## Creating a temporary tablespace on the instance store
To create a temporary tablespace on the instance store, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspace`. The `create_inst_store_tmp_tblspace` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_tablespace_name` | varchar | — | Yes | The name of the temporary tablespace. |
The following example creates the temporary tablespace *temp01* in the instance store.
```
EXEC rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspace(p_tablespace_name => 'temp01');
```
**Important**
When you run `rdsadmin_util.create_inst_store_tmp_tblspace`, the newly created temporary tablespace is not automatically set as the default temporary tablespace. To set it as the default, see [Setting the default temporary tablespace in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.SettingDefTempTablespace).
For more information, see [Storing temporary data in an RDS for Oracle instance store](CHAP_Oracle.advanced-features.instance-store.md).
# Working with tempfiles in RDS for Oracle
## Adding a tempfile to the instance store on a read replica
When you create a temporary tablespace on a primary DB instance, the read replica doesn't create tempfiles. Assume that an empty temporary tablespace exists on your read replica for either of the following reasons:
+ You dropped a tempfile from the tablespace on your read replica. For more information, see [Dropping tempfiles on a read replica](Appendix.Oracle.CommonDBATasks.dropping-tempfiles-replica.md).
+ You created a new temporary tablespace on the primary DB instance. In this case, RDS for Oracle synchronizes the metadata to the read replica.
You can add a tempfile to the empty temporary tablespace, and store the tempfile in the instance store. To create a tempfile in the instance store, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.add_inst_store_tempfile`. You can use this procedure only on a read replica. The procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_tablespace_name` | varchar | — | Yes | The name of the temporary tablespace on your read replica. |
In the following example, the empty temporary tablespace *temp01* exists on your read replica. Run the following command to create a tempfile for this tablespace, and store it in the instance store.
```
EXEC rdsadmin.rdsadmin_util.add_inst_store_tempfile(p_tablespace_name => 'temp01');
```
For more information, see [Storing temporary data in an RDS for Oracle instance store](CHAP_Oracle.advanced-features.instance-store.md).
# Dropping tempfiles on a read replica
You can't drop an existing temporary tablespace on a read replica. You can change the tempfile storage on a read replica from Amazon EBS to the instance store, or from the instance store to Amazon EBS. To achieve these goals, do the following:
1. Drop the current tempfiles in the temporary tablespace on the read replica.
1. Create new tempfiles on different storage.
To drop the tempfiles, use the Amazon RDS procedure `rdsadmin.rdsadmin_util. drop_replica_tempfiles`. You can use this procedure only on read replicas. The `drop_replica_tempfiles` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_tablespace_name` | varchar | — | Yes | The name of the temporary tablespace on your read replica. |
Assume that a temporary tablespace named *temp01* resides in the instance store on your read replica. Drop all tempfiles in this tablespace by running the following command.
```
EXEC rdsadmin.rdsadmin_util.drop_replica_tempfiles(p_tablespace_name => 'temp01');
```
For more information, see [Storing temporary data in an RDS for Oracle instance store](CHAP_Oracle.advanced-features.instance-store.md).
# Resizing tablespaces, data files, and tempfiles in RDS for Oracle
By default, Oracle tablespaces are created with auto-extend turned on and no maximum size. Because of these default settings, tablespaces can sometimes grow too large. We recommend that you specify an appropriate maximum size on permanent and temporary tablespaces, and that you carefully monitor space usage.
## Resizing permanent tablespaces
To resize a permanent tablespace in an RDS for Oracle DB instance, use any of the following Amazon RDS procedures:
+ `rdsadmin.rdsadmin_util.resize_datafile`
+ `rdsadmin.rdsadmin_util.autoextend_datafile`
The `resize_datafile` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_data_file_id` | number | — | Yes | The identifier of the data file to resize. |
| `p_size` | varchar2 | — | Yes | The size of the data file. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). |
The `autoextend_datafile` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_data_file_id` | number | — | Yes | The identifier of the data file to resize. |
| `p_autoextend_state` | varchar2 | — | Yes | The state of the autoextension feature. Specify `ON` to extend the data file automatically and `OFF` to turn off autoextension. |
| `p_next` | varchar2 | — | No | The size of the next data file increment. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). |
| `p_maxsize` | varchar2 | — | No | The maximum disk space allowed for automatic extension. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). You can specify `UNLIMITED` to remove the file size limit. |
The following example resizes data file 4 to 500 MB.
```
EXEC rdsadmin.rdsadmin_util.resize_datafile(4,'500M');
```
The following example turns off autoextension for data file 4. It also turns on autoextension for data file 5, with an increment of 128 MB and no maximum size.
```
EXEC rdsadmin.rdsadmin_util.autoextend_datafile(4,'OFF');
EXEC rdsadmin.rdsadmin_util.autoextend_datafile(5,'ON','128M','UNLIMITED');
```
## Resizing temporary tablespaces
To resize a temporary tablespaces in an RDS for Oracle DB instance, including a read replica, use any of the following Amazon RDS procedures:
+ `rdsadmin.rdsadmin_util.resize_temp_tablespace`
+ `rdsadmin.rdsadmin_util.resize_tempfile`
+ `rdsadmin.rdsadmin_util.autoextend_tempfile`
The `resize_temp_tablespace` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_temp_tablespace_name` | varchar2 | — | Yes | The name of the temporary tablespace to resize. |
| `p_size` | varchar2 | — | Yes | The size of the tablespace. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). |
The `resize_tempfile` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_temp_file_id` | number | — | Yes | The identifier of the temp file to resize. |
| `p_size` | varchar2 | — | Yes | The size of the temp file. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). |
The `autoextend_tempfile` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_temp_file_id` | number | — | Yes | The identifier of the temp file to resize. |
| `p_autoextend_state` | varchar2 | — | Yes | The state of the autoextension feature. Specify `ON` to extend the temp file automatically and `OFF` to turn off autoextension. |
| `p_next` | varchar2 | — | No | The size of the next temp file increment. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). |
| `p_maxsize` | varchar2 | — | No | The maximum disk space allowed for automatic extension. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). You can specify `UNLIMITED` to remove the file size limit. |
The following examples resize a temporary tablespace named `TEMP` to the size of 4 GB.
```
EXEC rdsadmin.rdsadmin_util.resize_temp_tablespace('TEMP','4G');
```
```
EXEC rdsadmin.rdsadmin_util.resize_temp_tablespace('TEMP','4096000000');
```
The following example resizes a temporary tablespace based on the temp file with the file identifier `1` to the size of 2 MB.
```
EXEC rdsadmin.rdsadmin_util.resize_tempfile(1,'2M');
```
The following example turns off autoextension for temp file 1. It also sets the maximum autoextension size of temp file 2 to 10 GB, with an increment of 100 MB.
```
EXEC rdsadmin.rdsadmin_util.autoextend_tempfile(1,'OFF');
EXEC rdsadmin.rdsadmin_util.autoextend_tempfile(2,'ON','100M','10G');
```
For more information about read replicas for Oracle DB instances see [Working with read replicas for Amazon RDS for Oracle](oracle-read-replicas.md).
# Moving data between storage volumes in RDS for Oracle
You can move data files and database objects between your primary and additional storage volumes. Before you move data, consider the following points:
+ The source and target volumes must have sufficient free space.
+ Data movement operations consume I/O on both volumes.
+ Large data movements can impact database performance.
+ If you restore a snapshot, moving data between storage volumes might be slow if it is affected by EBS lazy loading.
**Topics**
+ [Moving data files between volumes in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.MovingDatafiles)
+ [Moving table data and indexes between volumes in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.MovingTableData)
+ [Managing LOB storage using additional volumes](#Appendix.Oracle.CommonDBATasks.ManagingLargeLOBStorage)
## Moving data files between volumes in RDS for Oracle
To move data files between storage volumes, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.move_datafile`. Note the following requirements:
+ You must use Oracle Enterprise Edition to run the `move_datafile` procedure.
+ You can't move tablespace `SYSTEM` and `RDSADMIN`.
The `move_datafile` procedure has the following parameters.
****
| Parameter name | Data type | Required | Description |
| --- | --- | --- | --- |
| `p_data_file_id` | number | Yes | The ID of the data file to be moved. |
| `p_location` | varchar2 | Yes | The storage volume to which you want to move the data file. |
The following example moves a tablespace from the default volume `rdsdbdata` to the additional volume `rdsdbdata2`.
```
SQL> SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'MYNEWTABLESPACE';
TABLESPACE_NAME FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
MYNEWTABLESPACE 6 /rdsdbdata/db/ORCL_A/datafile/o1_mf_mynewtab_n123abcd_.dbf
EXECUTE rdsadmin.rdsadmin_util.move_datafile( 6, 'rdsdbdata2');
PL/SQL procedure successfully completed.
SQL> SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'MYNEWTABLESPACE';
TABLESPACE_NAME FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
MYNEWTABLESPACE 6 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_mynewtab_n356efgh_.dbf
```
## Moving table data and indexes between volumes in RDS for Oracle
You can optimize database storage by creating tablespaces on additional storage volumes. Then you can move objects such as tables, indexes, and partitions to these tablespaces using standard Oracle SQL. This approach is valuable for performance tuning when your database contains data with different access patterns. For example, you could store frequently accessed operational data on high-performance storage volumes while moving rarely accessed historical data to lower-cost storage volumes.
In the following example, you create a new tablespace on high-performance volume `rdsdbdata2`. Then you move a table to your additional storage volume while the table is online. You also move the index to the same volume. Moving tables and rebuilding indexes while online requires Oracle Enterprise Edition.
```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';
CREATE TABLESPACE perf_tbs DATAFILE SIZE 10G;
ALTER TABLE employees
MOVE TABLESPACE perf_tbs ONLINE;
ALTER INDEX employees_idx
REBUILD ONLINE TABLESPACE perf_tbs;
```
In the following example, you create a tablespace on a low-cost volume. Then you move a table partition to your low-cost storage volume using an online operation.
```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata3/db';
CREATE TABLESPACE hist_tbs DATAFILE SIZE 10G;
ALTER TABLE orders
MOVE PARTITION orders_2022
TABLESPACE hist_tbs ONLINE;
```
In the following example, you query active sessions long operations.
```
SELECT sid,opname,sofar,totalwork,time_remaining,elapsed_seconds
FROM v$session_longops
WHERE time_remaining > 0;
```
You can check your tablespaces usage with the following query.
```
SELECT tablespace_name, used_percent
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;
```
## Managing LOB storage using additional volumes
Your database might contains tables with BLOB or CLOB objects that consume substantial storage but are infrequently accessed. To optimize storage, you can relocate these LOB segments to a tablespace on an additional storage volume.
In the following example, you create a tablespace for LOB data on a low-cost volume that is intended for low-access data. Then you create a table that stores data on this volume.
```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata3/db';
CREATE TABLESPACE lob_data DATAFILE SIZE 5G AUTOEXTEND ON NEXT 1G;
CREATE TABLE documents (
doc_id NUMBER PRIMARY KEY,
doc_date DATE,
doc_content CLOB
) TABLESPACE user_data
LOB(doc_content) STORE AS (TABLESPACE lob_data);
```
# Working with external tables in RDS for Oracle
*Oracle external tables *are tables with data that is not in the database. Instead, the data is in external files that the database can access. By using external tables, you can access data without loading it into the database. For more information about external tables, see [Managing external tables](http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN01507) in the Oracle documentation.
With Amazon RDS, you can store external table files in directory objects. You can create a directory object, or you can use one that is predefined in the Oracle database, such as the DATA\$1PUMP\$1DIR directory. For information about creating directory objects, see [Creating and dropping directories in the main data storage space](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.NewDirectories). You can query the ALL\$1DIRECTORIES view to list the directory objects for your Amazon RDS Oracle DB instance.
**Note**
Directory objects point to the main data storage space (Amazon EBS volume) used by your instance. The space used—along with data files, redo logs, audit, trace, and other files—counts against allocated storage.
You can move an external data file from one Oracle database to another by using the [ DBMS\$1FILE\$1TRANSFER](https://docs.oracle.com/database/121/ARPLS/d_ftran.htm#ARPLS095) package or the [UTL\$1FILE](https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS069) package. The external data file is moved from a directory on the source database to the specified directory on the destination database. For information about using `DBMS_FILE_TRANSFER`, see [Importing using Oracle Data Pump](Oracle.Procedural.Importing.DataPump.md).
After you move the external data file, you can create an external table with it. The following example creates an external table that uses the `emp_xt_file1.txt` file in the USER\$1DIR1 directory.
```
CREATE TABLE emp_xt (
emp_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
user_name VARCHAR2(20)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY USER_DIR1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(emp_id,first_name,last_name,user_name)
)
LOCATION ('emp_xt_file1.txt')
)
PARALLEL
REJECT LIMIT UNLIMITED;
```
Suppose that you want to move data that is in an Amazon RDS Oracle DB instance into an external data file. In this case, you can populate the external data file by creating an external table and selecting the data from the table in the database. For example, the following SQL statement creates the `orders_xt` external table by querying the `orders` table in the database.
```
CREATE TABLE orders_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR
LOCATION ('orders_xt.dmp')
)
AS SELECT * FROM orders;
```
In this example, the data is populated in the `orders_xt.dmp` file in the DATA\$1PUMP\$1DIR directory.
# Checkpointing a database
To checkpoint the database, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.checkpoint`. The `checkpoint` procedure has no parameters.
The following example checkpoints the database.
```
EXEC rdsadmin.rdsadmin_util.checkpoint;
```
# Setting distributed recovery
To set distributed recovery, use the Amazon RDS procedures `rdsadmin.rdsadmin_util.enable_distr_recovery` and `disable_distr_recovery`. The procedures have no parameters.
The following example enables distributed recovery.
```
EXEC rdsadmin.rdsadmin_util.enable_distr_recovery;
```
The following example disables distributed recovery.
```
EXEC rdsadmin.rdsadmin_util.disable_distr_recovery;
```
# Setting the database time zone
You can set the time zone of your Amazon RDS Oracle database in the following ways:
+ The `Timezone` option
The `Timezone` option changes the time zone at the host level and affects all date columns and values such as `SYSDATE`. For more information, see [Oracle time zone](Appendix.Oracle.Options.Timezone.md).
+ The Amazon RDS procedure `rdsadmin.rdsadmin_util.alter_db_time_zone`
The `alter_db_time_zone` procedure changes the time zone for only certain data types, and doesn't change `SYSDATE`. There are additional restrictions on setting the time zone listed in the [Oracle documentation](http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006705).
**Note**
You can also set the default time zone for Oracle Scheduler. For more information, see [Setting the time zone for Oracle Scheduler jobs](Appendix.Oracle.CommonDBATasks.Scheduler.md#Appendix.Oracle.CommonDBATasks.Scheduler.TimeZone).
The `alter_db_time_zone` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_new_tz` | varchar2 | — | Yes | The new time zone as a named region or an absolute offset from Coordinated Universal Time (UTC). Valid offsets range from -12:00 to \$114:00. |
The following example changes the time zone to UTC plus three hours.
```
EXEC rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => '+3:00');
```
The following example changes the time zone to the Africa/Algiers time zone.
```
EXEC rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => 'Africa/Algiers');
```
After you alter the time zone by using the `alter_db_time_zone` procedure, reboot your DB instance for the change to take effect. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). For information about upgrading time zones, see [Time zone considerations](USER_UpgradeDBInstance.Oracle.OGPG.md#USER_UpgradeDBInstance.Oracle.OGPG.DST).
# Generating performance reports with Automatic Workload Repository (AWR)
To gather performance data and generate reports, Oracle recommends Automatic Workload Repository (AWR). AWR requires Oracle Database Enterprise Edition and a license for the Diagnostics and Tuning packs. To enable AWR, set the `CONTROL_MANAGEMENT_PACK_ACCESS` initialization parameter to either `DIAGNOSTIC` or `DIAGNOSTIC+TUNING`.
## Working with AWR reports in RDS
To generate AWR reports, you can run scripts such as `awrrpt.sql`. These scripts are installed on the database host server. In Amazon RDS, you don't have direct access to the host. However, you can get copies of SQL scripts from another installation of Oracle Database.
You can also use AWR by running procedures in the `SYS.DBMS_WORKLOAD_REPOSITORY` PL/SQL package. You can use this package to manage baselines and snapshots, and also to display ASH and AWR reports. For example, to generate an AWR report in text format run the `DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT` procedure. However, you can't reach these AWR reports from the AWS Management Console.
When working with AWR, we recommend using the `rdsadmin.rdsadmin_diagnostic_util` procedures. You can use these procedures to generate the following:
+ AWR reports
+ Active Session History (ASH) reports
+ Automatic Database Diagnostic Monitor (ADDM) reports
+ Oracle Data Pump Export dump files of AWR data
The `rdsadmin_diagnostic_util` procedures save the reports to the DB instance file system. You can access these reports from the console. You can also access reports using the `rdsadmin.rds_file_util` procedures, and you can access reports that are copied to Amazon S3 using the S3 Integration option. For more information, see [Reading files in a DB instance directory](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.ReadingFiles) and [Amazon S3 integration](oracle-s3-integration.md).
You can use the `rdsadmin_diagnostic_util` procedures in the following Amazon RDS for Oracle DB engine versions:
+ All Oracle Database 21c versions
+ 19.0.0.0.ru-2020-04.rur-2020-04.r1 and higher Oracle Database 19c versions
For a blog that explains how to work with diagnostic reports in a replication scenario, see [Generate AWR reports for Amazon RDS for Oracle read replicas](https://aws.amazon.com/blogs/database/generate-awr-reports-for-amazon-rds-for-oracle-read-replicas/).
## Common parameters for the diagnostic utility package
You typically use the following parameters when managing AWR and ADDM with the `rdsadmin_diagnostic_util` package.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.AWR.html)
You typically use the following parameters when managing ASH with the rdsadmin\$1diagnostic\$1util package.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.AWR.html)
## Generating an AWR report
To generate an AWR report, use the `rdsadmin.rdsadmin_diagnostic_util.awr_report` procedure.
The following example generates a AWR report for the snapshot range 101–106. The output text file is named `awrrpt_101_106.txt`. You can access this report from the AWS Management Console.
```
EXEC rdsadmin.rdsadmin_diagnostic_util.awr_report(101,106,'TEXT');
```
The following example generates an HTML report for the snapshot range 63–65. The output HTML file is named `awrrpt_63_65.html`. The procedure writes the report to the nondefault database directory named `AWR_RPT_DUMP`.
```
EXEC rdsadmin.rdsadmin_diagnostic_util.awr_report(63,65,'HTML','AWR_RPT_DUMP');
```
## Extracting AWR data into a dump file
To extract AWR data into a dump file, use the `rdsadmin.rdsadmin_diagnostic_util.awr_extract` procedure. You can use this function only at the PDB level.
The following example extracts the snapshot range 101–106. The output dump file is named `awrextract_101_106.dmp`. You can access this file through the console.
```
EXEC rdsadmin.rdsadmin_diagnostic_util.awr_extract(101,106);
```
The following example extracts the snapshot range 63–65. The output dump file is named `awrextract_63_65.dmp`. The file is stored in the nondefault database directory named `AWR_RPT_DUMP`.
```
EXEC rdsadmin.rdsadmin_diagnostic_util.awr_extract(63,65,'AWR_RPT_DUMP');
```
## Generating an ADDM report
To generate an ADDM report, use the `rdsadmin.rdsadmin_diagnostic_util.addm_report` procedure.
The following example generates an ADDM report for the snapshot range 101–106. The output text file is named `addmrpt_101_106.txt`. You can access the report through the console.
```
EXEC rdsadmin.rdsadmin_diagnostic_util.addm_report(101,106);
```
The following example generates an ADDM report for the snapshot range 63–65. The output text file is named `addmrpt_63_65.txt`. The file is stored in the nondefault database directory named `ADDM_RPT_DUMP`.
```
EXEC rdsadmin.rdsadmin_diagnostic_util.addm_report(63,65,'ADDM_RPT_DUMP');
```
## Generating an ASH report
To generate an ASH report, use the `rdsadmin.rdsadmin_diagnostic_util.ash_report` procedure.
The following example generates an ASH report that includes the data from 14 minutes ago until the current time. The name of the output file uses the format `ashrptbegin_timeend_time.txt`, where `begin_time` and `end_time` use the format `YYYYMMDDHH24MISS`. You can access the file through the console.
```
BEGIN
rdsadmin.rdsadmin_diagnostic_util.ash_report(
begin_time => SYSDATE-14/1440,
end_time => SYSDATE,
report_type => 'TEXT');
END;
/
```
The following example generates an ASH report that includes the data from November 18, 2019, at 6:07 PM through November 18, 2019, at 6:15 PM. The name of the output HTML report is `ashrpt_20190918180700_20190918181500.html`. The report is stored in the nondefault database directory named `AWR_RPT_DUMP`.
```
BEGIN
rdsadmin.rdsadmin_diagnostic_util.ash_report(
begin_time => TO_DATE('2019-09-18 18:07:00', 'YYYY-MM-DD HH24:MI:SS'),
end_time => TO_DATE('2019-09-18 18:15:00', 'YYYY-MM-DD HH24:MI:SS'),
report_type => 'html',
dump_directory => 'AWR_RPT_DUMP');
END;
/
```
## Accessing AWR reports from the console or CLI
To access AWR reports or export dump files, you can use the AWS Management Console or AWS CLI. For more information, see [Downloading a database log file](USER_LogAccess.Procedural.Downloading.md).
# Adjusting database links for use with DB instances in a VPC
To use Oracle database links with Amazon RDS DB instances inside the same virtual private cloud (VPC) or peered VPCs, the two DB instances should have a valid route between them. Verify the valid route between the DB instances by using your VPC routing tables and network access control list (ACL).
The security group of each DB instance must allow ingress to and egress from the other DB instance. The inbound and outbound rules can refer to security groups from the same VPC or a peered VPC. For more information, see [Updating your security groups to reference peered VPC security groups](https://docs.aws.amazon.com/vpc/latest/peering/working-with-vpc-peering.html#vpc-peering-security-groups).
If you have configured a custom DNS server using the DHCP Option Sets in your VPC, your custom DNS server must be able to resolve the name of the database link target. For more information, see [Setting up a custom DNS server](Appendix.Oracle.CommonDBATasks.System.md#Appendix.Oracle.CommonDBATasks.CustomDNS).
For more information about using database links with Oracle Data Pump, see [Importing using Oracle Data Pump](Oracle.Procedural.Importing.DataPump.md).
# Setting the default edition for a DB instance
You can redefine database objects in a private environment called an edition. You can use edition-based redefinition to upgrade an application's database objects with minimal downtime.
You can set the default edition of an Amazon RDS Oracle DB instance using the Amazon RDS procedure `rdsadmin.rdsadmin_util.alter_default_edition`.
The following example sets the default edition for the Amazon RDS Oracle DB instance to `RELEASE_V1`.
```
EXEC rdsadmin.rdsadmin_util.alter_default_edition('RELEASE_V1');
```
The following example sets the default edition for the Amazon RDS Oracle DB instance back to the Oracle default.
```
EXEC rdsadmin.rdsadmin_util.alter_default_edition('ORA$BASE');
```
For more information about Oracle edition-based redefinition, see [About editions and edition-based redefinition](https://docs.oracle.com/database/121/ADMIN/general.htm#ADMIN13167) in the Oracle documentation.
# Enabling auditing for the SYS.AUD\$1 table
To enable auditing on the database audit trail table `SYS.AUD$`, use the Amazon RDS procedure `rdsadmin.rdsadmin_master_util.audit_all_sys_aud_table`. The only supported audit property is `ALL`. You can't audit or not audit individual statements or operations.
Enabling auditing is supported for Oracle DB instances running the following versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
The `audit_all_sys_aud_table` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_by_access` | boolean | true | No | Set to `true` to audit `BY ACCESS`. Set to `false` to audit `BY SESSION`. |
The following query returns the current audit configuration for `SYS.AUD$` for a database.
```
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER='SYS' AND OBJECT_NAME='AUD$';
```
The following commands enable audit of `ALL` on `SYS.AUD$` `BY ACCESS`.
```
EXEC rdsadmin.rdsadmin_master_util.audit_all_sys_aud_table;
EXEC rdsadmin.rdsadmin_master_util.audit_all_sys_aud_table(p_by_access => true);
```
The following command enables audit of `ALL` on `SYS.AUD$` `BY SESSION`.
```
EXEC rdsadmin.rdsadmin_master_util.audit_all_sys_aud_table(p_by_access => false);
```
For more information, see [AUDIT (traditional auditing)](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/AUDIT-Traditional-Auditing.html#GUID-ADF45B07-547A-4096-8144-50241FA2D8DD) in the Oracle documentation.
# Disabling auditing for the SYS.AUD\$1 table
To disable auditing on the database audit trail table `SYS.AUD$`, use the Amazon RDS procedure `rdsadmin.rdsadmin_master_util.noaudit_all_sys_aud_table`. This procedure takes no parameters.
The following query returns the current audit configuration for `SYS.AUD$` for a database:
```
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER='SYS' AND OBJECT_NAME='AUD$';
```
The following command disables audit of `ALL` on `SYS.AUD$`.
```
EXEC rdsadmin.rdsadmin_master_util.noaudit_all_sys_aud_table;
```
For more information, see [NOAUDIT (traditional auditing)](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/NOAUDIT-Traditional-Auditing.html#GUID-9D8EAF18-4AB3-4C04-8BF7-37BD0E15434D) in the Oracle documentation.
# Cleaning up interrupted online index builds
To clean up failed online index builds, use the Amazon RDS procedure `rdsadmin.rdsadmin_dbms_repair.online_index_clean`.
The `online_index_clean` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `object_id` | binary\$1integer | `ALL_INDEX_ID` | No | The object ID of the index. Typically, you can use the object ID from the ORA-08104 error text. |
| `wait_for_lock` | binary\$1integer | `rdsadmin.rdsadmin_dbms_repair.lock_wait` | No | Specify `rdsadmin.rdsadmin_dbms_repair.lock_wait`, the default, to try to get a lock on the underlying object and retry until an internal limit is reached if the lock fails. Specify `rdsadmin.rdsadmin_dbms_repair.lock_nowait` to try to get a lock on the underlying object but not retry if the lock fails. |
The following example cleans up a failed online index build:
```
declare
is_clean boolean;
begin
is_clean := rdsadmin.rdsadmin_dbms_repair.online_index_clean(
object_id => 1234567890,
wait_for_lock => rdsadmin.rdsadmin_dbms_repair.lock_nowait
);
end;
/
```
For more information, see [ONLINE\$1INDEX\$1CLEAN function](https://docs.oracle.com/database/121/ARPLS/d_repair.htm#ARPLS67555) in the Oracle documentation.
# Skipping corrupt blocks
To skip corrupt blocks during index and table scans, use the `rdsadmin.rdsadmin_dbms_repair` package.
The following procedures wrap the functionality of the `sys.dbms_repair.admin_table` procedure and take no parameters:
+ `rdsadmin.rdsadmin_dbms_repair.create_repair_table`
+ `rdsadmin.rdsadmin_dbms_repair.create_orphan_keys_table`
+ `rdsadmin.rdsadmin_dbms_repair.drop_repair_table`
+ `rdsadmin.rdsadmin_dbms_repair.drop_orphan_keys_table`
+ `rdsadmin.rdsadmin_dbms_repair.purge_repair_table`
+ `rdsadmin.rdsadmin_dbms_repair.purge_orphan_keys_table`
The following procedures take the same parameters as their counterparts in the `DBMS_REPAIR` package for Oracle databases:
+ `rdsadmin.rdsadmin_dbms_repair.check_object`
+ `rdsadmin.rdsadmin_dbms_repair.dump_orphan_keys`
+ `rdsadmin.rdsadmin_dbms_repair.fix_corrupt_blocks`
+ `rdsadmin.rdsadmin_dbms_repair.rebuild_freelists`
+ `rdsadmin.rdsadmin_dbms_repair.segment_fix_status`
+ `rdsadmin.rdsadmin_dbms_repair.skip_corrupt_blocks`
For more information about handling database corruption, see [DBMS\$1REPAIR](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REPAIR.html#GUID-B8EC4AB3-4D6A-46C9-857F-4ED53CD9C948) in the Oracle documentation.
**Example Responding to corrupt blocks**
This example shows the basic workflow for responding to corrupt blocks. Your steps will depend on the location and nature of your block corruption.
Before attempting to repair corrupt blocks, review the [DBMS\$1REPAIR](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REPAIR.html#GUID-B8EC4AB3-4D6A-46C9-857F-4ED53CD9C948) documentation carefully.
**To skip corrupt blocks during index and table scans**
1. Run the following procedures to create repair tables if they don't already exist.
```
EXEC rdsadmin.rdsadmin_dbms_repair.create_repair_table;
EXEC rdsadmin.rdsadmin_dbms_repair.create_orphan_keys_table;
```
1. Run the following procedures to check for existing records and purge them if appropriate.
```
SELECT COUNT(*) FROM SYS.REPAIR_TABLE;
SELECT COUNT(*) FROM SYS.ORPHAN_KEY_TABLE;
SELECT COUNT(*) FROM SYS.DBA_REPAIR_TABLE;
SELECT COUNT(*) FROM SYS.DBA_ORPHAN_KEY_TABLE;
EXEC rdsadmin.rdsadmin_dbms_repair.purge_repair_table;
EXEC rdsadmin.rdsadmin_dbms_repair.purge_orphan_keys_table;
```
1. Run the following procedure to check for corrupt blocks.
```
SET SERVEROUTPUT ON
DECLARE v_num_corrupt INT;
BEGIN
v_num_corrupt := 0;
rdsadmin.rdsadmin_dbms_repair.check_object (
schema_name => '&corruptionOwner',
object_name => '&corruptionTable',
corrupt_count => v_num_corrupt
);
dbms_output.put_line('number corrupt: '||to_char(v_num_corrupt));
END;
/
COL CORRUPT_DESCRIPTION FORMAT a30
COL REPAIR_DESCRIPTION FORMAT a30
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM SYS.REPAIR_TABLE;
SELECT SKIP_CORRUPT
FROM DBA_TABLES
WHERE OWNER = '&corruptionOwner'
AND TABLE_NAME = '&corruptionTable';
```
1. Use the `skip_corrupt_blocks` procedure to enable or disable corruption skipping for affected tables. Depending on the situation, you may also need to extract data to a new table, and then drop the table containing the corrupt block.
Run the following procedure to enable corruption skipping for affected tables.
```
begin
rdsadmin.rdsadmin_dbms_repair.skip_corrupt_blocks (
schema_name => '&corruptionOwner',
object_name => '&corruptionTable',
object_type => rdsadmin.rdsadmin_dbms_repair.table_object,
flags => rdsadmin.rdsadmin_dbms_repair.skip_flag);
end;
/
select skip_corrupt from dba_tables where owner = '&corruptionOwner' and table_name = '&corruptionTable';
```
Run the following procedure to disable corruption skipping.
```
begin
rdsadmin.rdsadmin_dbms_repair.skip_corrupt_blocks (
schema_name => '&corruptionOwner',
object_name => '&corruptionTable',
object_type => rdsadmin.rdsadmin_dbms_repair.table_object,
flags => rdsadmin.rdsadmin_dbms_repair.noskip_flag);
end;
/
select skip_corrupt from dba_tables where owner = '&corruptionOwner' and table_name = '&corruptionTable';
```
1. When you have completed all repair work, run the following procedures to drop the repair tables.
```
EXEC rdsadmin.rdsadmin_dbms_repair.drop_repair_table;
EXEC rdsadmin.rdsadmin_dbms_repair.drop_orphan_keys_table;
```
## Purging the recycle bin
When you drop a table, your Oracle database doesn't immediately remove its storage space. The database renames the table and places it and any associated objects in a recycle bin. Purging the recycle bin removes these items and releases their storage space.
To purge the entire recycle bin, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.purge_dba_recyclebin`. However, this procedure can't purge the recycle bin of `SYS` and `RDSADMIN` objects. If you need to purge these objects, contact AWS Support.
The following example purges the entire recycle bin.
```
EXEC rdsadmin.rdsadmin_util.purge_dba_recyclebin;
```
# Setting the default displayed values for full redaction
To change the default displayed values for full redaction on your Amazon RDS Oracle instance, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.dbms_redact_upd_full_rdct_val`. Note that you create a redaction policy with the `DBMS_REDACT` PL/SQL package, as explained in the Oracle Database documentation. The `dbms_redact_upd_full_rdct_val` procedure specifies the characters to display for different data types affected by an existing policy.
The `dbms_redact_upd_full_rdct_val` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_number_val` | number | Null | No | Modifies the default value for columns of the `NUMBER` data type. |
| `p_binfloat_val` | binary\$1float | Null | No | Modifies the default value for columns of the `BINARY_FLOAT` data type. |
| `p_bindouble_val` | binary\$1double | Null | No | Modifies the default value for columns of the `BINARY_DOUBLE` data type. |
| `p_char_val` | char | Null | No | Modifies the default value for columns of the `CHAR` data type. |
| `p_varchar_val` | varchar2 | Null | No | Modifies the default value for columns of the `VARCHAR2` data type. |
| `p_nchar_val` | nchar | Null | No | Modifies the default value for columns of the `NCHAR` data type. |
| `p_nvarchar_val` | nvarchar2 | Null | No | Modifies the default value for columns of the `NVARCHAR2` data type. |
| `p_date_val` | date | Null | No | Modifies the default value for columns of the `DATE` data type. |
| `p_ts_val` | timestamp | Null | No | Modifies the default value for columns of the `TIMESTAMP` data type. |
| `p_tswtz_val` | timestamp with time zone | Null | No | Modifies the default value for columns of the `TIMESTAMP WITH TIME ZONE` data type. |
| `p_blob_val` | blob | Null | No | Modifies the default value for columns of the `BLOB` data type. |
| `p_clob_val` | clob | Null | No | Modifies the default value for columns of the `CLOB` data type. |
| `p_nclob_val` | nclob | Null | No | Modifies the default value for columns of the `NCLOB` data type. |
The following example changes the default redacted value to \$1 for the `CHAR` data type:
```
EXEC rdsadmin.rdsadmin_util.dbms_redact_upd_full_rdct_val(p_char_val => '*');
```
The following example changes the default redacted values for `NUMBER`, `DATE`, and `CHAR` data types:
```
BEGIN
rdsadmin.rdsadmin_util.dbms_redact_upd_full_rdct_val(
p_number_val=>1,
p_date_val=>to_date('1900-01-01','YYYY-MM-DD'),
p_varchar_val=>'X');
END;
/
```
After you alter the default values for full redaction with the `dbms_redact_upd_full_rdct_val` procedure, reboot your DB instance for the change to take effect. For more information, see [Rebooting a DB instance](USER_RebootInstance.md).
# Performing common log-related tasks for Oracle DB instances
Following, you can find how to perform certain common DBA tasks related to logging on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and restricts access to certain system procedures and tables that require advanced privileges.
For more information, see [Amazon RDS for Oracle database log files](USER_LogAccess.Concepts.Oracle.md).
**Topics**
+ [Setting force logging](#Appendix.Oracle.CommonDBATasks.SettingForceLogging)
+ [Setting supplemental logging](#Appendix.Oracle.CommonDBATasks.AddingSupplementalLogging)
+ [Switching online log files](#Appendix.Oracle.CommonDBATasks.SwitchingLogfiles)
+ [Adding online redo logs](#Appendix.Oracle.CommonDBATasks.RedoLogs)
+ [Dropping online redo logs](#Appendix.Oracle.CommonDBATasks.DroppingRedoLogs)
+ [Resizing online redo logs](Appendix.Oracle.CommonDBATasks.ResizingRedoLogs.md)
+ [Retaining archived redo logs](Appendix.Oracle.CommonDBATasks.RetainRedoLogs.md)
+ [Accessing online and archived redo logs](Appendix.Oracle.CommonDBATasks.Log.Download.md)
+ [Downloading archived redo logs from Amazon S3](Appendix.Oracle.CommonDBATasks.download-redo-logs.md)
## Setting force logging
In force logging mode, Oracle logs all changes to the database except changes in temporary tablespaces and temporary segments (`NOLOGGING` clauses are ignored). For more information, see [Specifying FORCE LOGGING mode](https://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN11096) in the Oracle documentation.
To set force logging, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.force_logging`. The `force_logging` procedure has the following parameters.
****
| Parameter name | Data type | Default | Yes | Description |
| --- | --- | --- | --- | --- |
| `p_enable` | boolean | true | No | Set to `true` to put the database in force logging mode, `false` to remove the database from force logging mode. |
The following example puts the database in force logging mode.
```
EXEC rdsadmin.rdsadmin_util.force_logging(p_enable => true);
```
## Setting supplemental logging
If you enable supplemental logging, LogMiner has the necessary information to support chained rows and clustered tables. For more information, see [Supplemental logging](https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL1582) in the Oracle documentation.
Oracle Database doesn't enable supplemental logging by default. To enable and disable supplemental logging, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.alter_supplemental_logging`. For more information about how Amazon RDS manages the retention of archived redo logs for Oracle DB instances, see [Retaining archived redo logs](Appendix.Oracle.CommonDBATasks.RetainRedoLogs.md).
The `alter_supplemental_logging` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_action` | varchar2 | — | Yes | `'ADD'` to add supplemental logging, `'DROP'` to drop supplemental logging. |
| `p_type` | varchar2 | null | No | The type of supplemental logging. Valid values are `'ALL'`, `'FOREIGN KEY'`, `'PRIMARY KEY'`, `'UNIQUE'`, or `PROCEDURAL`. |
The following example enables supplemental logging.
```
begin
rdsadmin.rdsadmin_util.alter_supplemental_logging(
p_action => 'ADD');
end;
/
```
The following example enables supplemental logging for all fixed-length maximum size columns.
```
begin
rdsadmin.rdsadmin_util.alter_supplemental_logging(
p_action => 'ADD',
p_type => 'ALL');
end;
/
```
The following example enables supplemental logging for primary key columns.
```
begin
rdsadmin.rdsadmin_util.alter_supplemental_logging(
p_action => 'ADD',
p_type => 'PRIMARY KEY');
end;
/
```
## Switching online log files
To switch log files, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.switch_logfile`. The `switch_logfile` procedure has no parameters.
The following example switches log files.
```
EXEC rdsadmin.rdsadmin_util.switch_logfile;
```
## Adding online redo logs
An Amazon RDS DB instance running Oracle starts with four online redo logs, 128 MB each. To add additional redo logs, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.add_logfile`.
The `add_logfile` procedure has the following parameters.
**Note**
The parameters are mutually exclusive.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `bytes` | positive | null | No | The size of the log file in bytes. Use this parameter only if the size of the log is under 2147483648 bytes (2 GiB). Otherwise, RDS issues an error. For log sizes above this byte value, use the `p_size` parameter instead. |
| `p_size` | varchar2 | — | Yes | The size of the log file in kilobytes (K), megabytes (M), or gigabytes (G). |
The following command adds a 100 MB log file.
```
EXEC rdsadmin.rdsadmin_util.add_logfile(p_size => '100M');
```
## Dropping online redo logs
To drop redo logs, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.drop_logfile`. The `drop_logfile` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `grp` | positive | — | Yes | The group number of the log. |
The following example drops the log with group number 3.
```
EXEC rdsadmin.rdsadmin_util.drop_logfile(grp => 3);
```
You can only drop logs that have a status of unused or inactive. The following example gets the statuses of the logs.
```
SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
```
# Resizing online redo logs
An Amazon RDS DB instance running Oracle starts with four online redo logs, 128 MB each. The following example shows how you can use Amazon RDS procedures to resize your logs from 128 MB each to 512 MB each.
```
/* Query V$LOG to see the logs. */
/* You start with 4 logs of 128 MB each. */
SELECT GROUP#, BYTES, STATUS FROM V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 134217728 INACTIVE
2 134217728 CURRENT
3 134217728 INACTIVE
4 134217728 INACTIVE
/* Add four new logs that are each 512 MB */
EXEC rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
EXEC rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
EXEC rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
EXEC rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912);
/* Query V$LOG to see the logs. */
/* Now there are 8 logs. */
SELECT GROUP#, BYTES, STATUS FROM V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 134217728 INACTIVE
2 134217728 CURRENT
3 134217728 INACTIVE
4 134217728 INACTIVE
5 536870912 UNUSED
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
/* Drop each inactive log using the group number. */
EXEC rdsadmin.rdsadmin_util.drop_logfile(grp => 1);
EXEC rdsadmin.rdsadmin_util.drop_logfile(grp => 3);
EXEC rdsadmin.rdsadmin_util.drop_logfile(grp => 4);
/* Query V$LOG to see the logs. */
/* Now there are 5 logs. */
select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
2 134217728 CURRENT
5 536870912 UNUSED
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
/* Switch logs so that group 2 is no longer current. */
EXEC rdsadmin.rdsadmin_util.switch_logfile;
/* Query V$LOG to see the logs. */
/* Now one of the new logs is current. */
SQL>SELECT GROUP#, BYTES, STATUS FROM V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
2 134217728 ACTIVE
5 536870912 CURRENT
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
/* If the status of log 2 is still "ACTIVE", issue a checkpoint to clear it to "INACTIVE". */
EXEC rdsadmin.rdsadmin_util.checkpoint;
/* Query V$LOG to see the logs. */
/* Now the final original log is inactive. */
select GROUP#, BYTES, STATUS from V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
2 134217728 INACTIVE
5 536870912 CURRENT
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
# Drop the final inactive log.
EXEC rdsadmin.rdsadmin_util.drop_logfile(grp => 2);
/* Query V$LOG to see the logs. */
/* Now there are four 512 MB logs. */
SELECT GROUP#, BYTES, STATUS FROM V$LOG;
GROUP# BYTES STATUS
---------- ---------- ----------------
5 536870912 CURRENT
6 536870912 UNUSED
7 536870912 UNUSED
8 536870912 UNUSED
```
# Retaining archived redo logs
You can retain archived redo logs locally on your DB instance for use with products like Oracle LogMiner (`DBMS_LOGMNR`). After you have retained the redo logs, you can use LogMiner to analyze the logs. For more information, see [Using LogMiner to analyze redo log files](http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm) in the Oracle documentation.
To retain archived redo logs, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.set_configuration`. If you use this procedure on a primary instance in Oracle Data Guard, RDS changes the archive log retention setting on the primary instance and open read replicas, but not on mounted replicas. RDS retains the latest archive redo logs on mounted replicas for a short period of time. RDS automatically deletes older logs downloaded to mounted replicas.
The `set_configuration` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `name` | varchar | — | Yes | The name of the configuration to update. To change the archived redo log retention hours, set the name to `archivelog retention hours`. |
| `value` | varchar | — | Yes | The value for the configuration. Set the value the number of hours to retain the logs. |
The following example retains 24 hours of redo logs.
```
begin
rdsadmin.rdsadmin_util.set_configuration(
name => 'archivelog retention hours',
value => '24');
end;
/
commit;
```
**Note**
The commit is required for the change to take effect.
To view how long archived redo logs are kept for your DB instance, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.show_configuration`.
The following example shows the log retention time.
```
set serveroutput on
EXEC rdsadmin.rdsadmin_util.show_configuration;
```
The output shows the current setting for `archivelog retention hours`. The following output shows that archived redo logs are kept for 48 hours.
```
NAME:archivelog retention hours
VALUE:48
DESCRIPTION:ArchiveLog expiration specifies the duration in hours before archive/redo log files are automatically deleted.
```
Because the archived redo logs are retained on your DB instance, ensure that your DB instance has enough allocated storage for the retained logs. To determine how much space your DB instance has used in the last X hours, you can run the following query, replacing X with the number of hours.
```
SELECT SUM(BLOCKS * BLOCK_SIZE) bytes
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME >= SYSDATE-(X/24) AND DEST_ID=1;
```
RDS for Oracle only generates archived redo logs when the backup retention period of your DB instance is greater than zero. By default the backup retention period is greater than zero.
When the archived log retention period expires, RDS for Oracle removes the archived redo logs from your DB instance. To support restoring your DB instance to a point in time, Amazon RDS retains the archived redo logs outside of your DB instance based on the backup retention period. To modify the backup retention period, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
**Note**
In some cases, you might be using JDBC on Linux to download archived redo logs and experience long latency times and connection resets. In such cases, the issues might be caused by the default random number generator setting on your Java client. We recommend setting your JDBC drivers to use a nonblocking random number generator.
# Accessing online and archived redo logs
You might want to access your online and archived redo log files for mining with external tools such as GoldenGate, Attunity, Informatica, and others. To access these files, do the following:
1. Create directory objects that provide read-only access to the physical file paths.
Use `rdsadmin.rdsadmin_master_util.create_archivelog_dir` and `rdsadmin.rdsadmin_master_util.create_onlinelog_dir`.
1. Read the files using PL/SQL.
You can read the files by using PL/SQL. For more information about reading files from directory objects, see [Listing files in a DB instance directory](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.ListDirectories) and [Reading files in a DB instance directory](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.ReadingFiles).
Accessing transaction logs is supported for the following releases:
+ Oracle Database 21c
+ Oracle Database 19c
The following code creates directories that provide read-only access to your online and archived redo log files:
**Important**
This code also revokes the `DROP ANY DIRECTORY` privilege.
```
EXEC rdsadmin.rdsadmin_master_util.create_archivelog_dir;
EXEC rdsadmin.rdsadmin_master_util.create_onlinelog_dir;
```
The following code drops the directories for your online and archived redo log files.
```
EXEC rdsadmin.rdsadmin_master_util.drop_archivelog_dir;
EXEC rdsadmin.rdsadmin_master_util.drop_onlinelog_dir;
```
The following code grants and revokes the `DROP ANY DIRECTORY` privilege.
```
EXEC rdsadmin.rdsadmin_master_util.revoke_drop_any_directory;
EXEC rdsadmin.rdsadmin_master_util.grant_drop_any_directory;
```
# Downloading archived redo logs from Amazon S3
You can download archived redo logs on your DB instance using the `rdsadmin.rdsadmin_archive_log_download` package. If archived redo logs are no longer on your DB instance, you might want to download them again from Amazon S3. Then you can mine the logs or use them to recover or replicate your database.
**Note**
You can't download archived redo logs on read replica instances.
## Downloading archived redo logs: basic steps
The availability of your archived redo logs depends on the following retention policies:
+ Backup retention policy – Logs inside of this policy are available in Amazon S3. Logs outside of this policy are removed.
+ Archived log retention policy – Logs inside of this policy are available on your DB instance. Logs outside of this policy are removed.
If logs aren't on your instance but are protected by your backup retention period, use `rdsadmin.rdsadmin_archive_log_download` to download them again. RDS for Oracle saves the logs to the `/rdsdbdata/log/arch` directory on your DB instance.
**To download archived redo logs from Amazon S3**
1. Configure your retention period to ensure your downloaded archived redo logs are retained for the duration you need them. Make sure to `COMMIT` your change.
RDS retains your downloaded logs according to the archived log retention policy, starting from the time the logs were downloaded. To learn how to set the retention policy, see [Retaining archived redo logs](Appendix.Oracle.CommonDBATasks.RetainRedoLogs.md).
1. Wait up to 5 minutes for the archived log retention policy change to take effect.
1. Download the archived redo logs from Amazon S3 using `rdsadmin.rdsadmin_archive_log_download`.
For more information, see [Downloading a single archived redo log](#Appendix.Oracle.CommonDBATasks.download-redo-logs.single-log) and [Downloading a series of archived redo logs](#Appendix.Oracle.CommonDBATasks.download-redo-logs.series).
**Note**
RDS automatically checks the available storage before downloading. If the requested logs consume a high percentage of space, you receive an alert.
1. Confirm that the logs were downloaded from Amazon S3 successfully.
You can view the status of your download task in a bdump file. The bdump files have the path name `/rdsdbdata/log/trace/dbtask-task-id.log`. In the preceding download step, you run a `SELECT` statement that returns the task ID in a `VARCHAR2` data type. For more information, see similar examples in [Monitoring the status of a file transfer](oracle-s3-integration.using.md#oracle-s3-integration.using.task-status).
## Downloading a single archived redo log
To download a single archived redo log to the `/rdsdbdata/log/arch` directory, use `rdsadmin.rdsadmin_archive_log_download.download_log_with_seqnum`. This procedure has the following parameter.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `seqnum` | number | — | Yes | The sequence number of the archived redo log. |
The following example downloads the log with sequence number 20.
```
SELECT rdsadmin.rdsadmin_archive_log_download.download_log_with_seqnum(seqnum => 20)
AS TASK_ID
FROM DUAL;
```
## Downloading a series of archived redo logs
To download a series of archived redo logs to the `/rdsdbdata/log/arch` directory, use `download_logs_in_seqnum_range`. Your download is limited to 300 logs per request. The `download_logs_in_seqnum_range` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `start_seq` | number | — | Yes | The starting sequence number for the series. |
| `end_seq` | number | — | Yes | The ending sequence number for the series. |
The following example downloads the logs from sequence 50 to 100.
```
SELECT rdsadmin.rdsadmin_archive_log_download.download_logs_in_seqnum_range(start_seq => 50, end_seq => 100)
AS TASK_ID
FROM DUAL;
```
# Performing common RMAN tasks for Oracle DB instances
In the following section, you can find how you can perform Oracle Recovery Manager (RMAN) DBA tasks on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances. It also restricts access to certain system procedures and tables that require advanced privileges.
Use the Amazon RDS package `rdsadmin.rdsadmin_rman_util` to perform RMAN backups of your Amazon RDS for Oracle database to disk. The `rdsadmin.rdsadmin_rman_util` package supports full and incremental database file backups, tablespace backups, and archived redo log backups.
After an RMAN backup has finished, you can copy the backup files off the Amazon RDS for Oracle DB instance host. You might do this for the purpose of restoring to a non-RDS host or for long-term storage of backups. For example, you can copy the backup files to an Amazon S3 bucket. For more information, see using [Amazon S3 integration](oracle-s3-integration.md).
The backup files for RMAN backups remain on the Amazon RDS DB instance host until you remove them manually. You can use the `UTL_FILE.FREMOVE` Oracle procedure to remove files from a directory. For more information, see [FREMOVE procedure](https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70924) in the Oracle Database documentation.
You can't use the RMAN to restore RDS for Oracle DB instances. However, you can use RMAN to restore a backup to an on-premises or Amazon EC2 instance. For more information, see the blog article [Restore an Amazon RDS for Oracle instance to a self-managed instance](https://aws.amazon.com/blogs/database/restore-an-amazon-rds-for-oracle-instance-to-a-self-managed-instance/).
**Note**
For backing up and restoring to another Amazon RDS for Oracle DB instance, you can continue to use the Amazon RDS backup and restore features. For more information, see [Backing up, restoring, and exporting data](CHAP_CommonTasks.BackupRestore.md).
**Topics**
+ [Prerequisites for RMAN backups](Appendix.Oracle.CommonDBATasks.RMAN-requirements.md)
+ [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md)
+ [Validating database files in RDS for Oracle](Appendix.Oracle.CommonDBATasks.ValidateDBFiles.md)
+ [Enabling and disabling block change tracking](Appendix.Oracle.CommonDBATasks.BlockChangeTracking.md)
+ [Crosschecking archived redo logs](Appendix.Oracle.CommonDBATasks.Crosscheck.md)
+ [Backing up archived redo log files](Appendix.Oracle.CommonDBATasks.BackupArchivedLogs.md)
+ [Performing a full database backup](Appendix.Oracle.CommonDBATasks.BackupDatabaseFull.md)
+ [Performing a full backup of a tenant database](Appendix.Oracle.CommonDBATasks.BackupTenantDatabaseFull.md)
+ [Performing an incremental database backup](Appendix.Oracle.CommonDBATasks.BackupDatabaseIncremental.md)
+ [Performing an incremental backup of a tenant database](Appendix.Oracle.CommonDBATasks.BackupTenantDatabaseIncremental.md)
+ [Backing up a tablespace](Appendix.Oracle.CommonDBATasks.BackupTablespace.md)
+ [Backing up a control file](Appendix.Oracle.CommonDBATasks.backup-control-file.md)
+ [Performing block media recovery](Appendix.Oracle.CommonDBATasks.block-media-recovery.md)
# Prerequisites for RMAN backups
Before backing up your database using the `rdsadmin.rdsadmin_rman_util` package, make sure that you meet the following prerequisites:
+ Make sure that your RDS for Oracle database is in `ARCHIVELOG` mode. To enable this mode, set the backup retention period to a non-zero value.
+ When backing up archived redo logs or performing a full or incremental backup that includes archived redo logs, and when backing up the database, make sure that redo log retention is set to a nonzero value. Archived redo logs are required to make database files consistent during recovery. For more information, see [Retaining archived redo logs](Appendix.Oracle.CommonDBATasks.RetainRedoLogs.md).
+ Make sure that your DB instance has sufficient free space to hold the backups. When back up your database, you specify an Oracle directory object as a parameter in the procedure call. RMAN places the files in the specified directory. You can use default directories, such as `DATA_PUMP_DIR`, or create a new directory. For more information, see [Creating and dropping directories in the main data storage space](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.NewDirectories).
You can monitor the current free space in an RDS for Oracle instance using the CloudWatch metric `FreeStorageSpace`. We recommend that your free space exceeds the current size of the database, though RMAN backs up only formatted blocks and supports compression.
# Common parameters for RMAN procedures
You can use procedures in the Amazon RDS package `rdsadmin.rdsadmin_rman_util` to perform tasks with RMAN. Several parameters are common to the procedures in the package. The package has the following common parameters.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_directory_name` | varchar2 | A valid database directory name. | — | Yes | The name of the directory to contain the backup files. |
| `p_label` | varchar2 | `a-z`, `A-Z`, `0-9`, `'_'`, `'-'`, `'.'` | — | No | A unique string that is included in the backup file names. The limit is 30 characters. |
| `p_owner` | varchar2 | A valid owner of the directory specified in `p_directory_name`. | — | Yes | The owner of the directory to contain the backup files. |
| `p_tag` | varchar2 | `a-z`, `A-Z`, `0-9`, `'_'`, `'-'`, `'.'` | NULL | No | A string that can be used to distinguish between backups to indicate the purpose or usage of backups, such as daily, weekly, or incremental-level backups. The limit is 30 characters. The tag is not case-sensitive. Tags are always stored in uppercase, regardless of the case used when entering them. Tags don't need to be unique, so multiple backups can have the same tag. If you don't specify a tag, then RMAN assigns a default tag automatically using the format `TAGYYYYMMDDTHHMMSS`, where *YYYY* is the year, *MM* is the month, *DD* is the day, *HH* is the hour (in 24-hour format), *MM* is the minutes, and *SS* is the seconds. The date and time refer to when RMAN started the backup. For example, a backup might receive a tag `TAG20190927T214517` for a backup that started on 2019-09-27 at 21:45:17. The `p_tag` parameter is supported for the following Amazon RDS for Oracle DB engine versions: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.CommonParameters.html) |
| `p_compress` | boolean | `TRUE`, `FALSE` | `FALSE` | No | Specify `TRUE` to enable BASIC backup compression. Specify `FALSE` to disable BASIC backup compression. |
| `p_include_archive_logs` | boolean | `TRUE`, `FALSE` | `FALSE` | No | Specify `TRUE` to include archived redo logs in the backup. Specify `FALSE` to exclude archived redo logs from the backup. If you include archived redo logs in the backup, set retention to one hour or greater using the `rdsadmin.rdsadmin_util.set_configuration` procedure. Also, call the `rdsadmin.rdsadmin_rman_util.crosscheck_archivelog` procedure immediately before running the backup. Otherwise, the backup might fail due to missing archived redo log files that have been deleted by Amazon RDS management procedures. |
| `p_include_controlfile` | boolean | `TRUE`, `FALSE` | `FALSE` | No | Specify `TRUE` to include the control file in the backup. Specify `FALSE` to exclude the control file from the backup. |
| `p_optimize` | boolean | `TRUE`, `FALSE` | `TRUE` | No | Specify `TRUE` to enable backup optimization, if archived redo logs are included, to reduce backup size. Specify `FALSE` to disable backup optimization. |
| `p_parallel` | number | A valid integer between `1` and `254` for Oracle Database Enterprise Edition (EE) `1` for other Oracle Database editions | `1` | No | Number of channels. |
| `p_rman_to_dbms_output` | boolean | `TRUE`, `FALSE` | `FALSE` | No | When `TRUE`, the RMAN output is sent to the `DBMS_OUTPUT` package in addition to a file in the `BDUMP` directory. In SQL\$1Plus, use `SET SERVEROUTPUT ON` to see the output. When `FALSE`, the RMAN output is only sent to a file in the `BDUMP` directory. |
| `p_section_size_mb` | number | A valid integer | `NULL` | No | The section size in megabytes (MB). Validates in parallel by dividing each file into the specified section size. When `NULL`, the parameter is ignored. |
| `p_validation_type` | varchar2 | `'PHYSICAL'`, `'PHYSICAL+LOGICAL'` | `'PHYSICAL'` | No | The level of corruption detection. Specify `'PHYSICAL'` to check for physical corruption. An example of physical corruption is a block with a mismatch in the header and footer. Specify `'PHYSICAL+LOGICAL'` to check for logical inconsistencies in addition to physical corruption. An example of logical corruption is a corrupt block. |
# Validating database files in RDS for Oracle
You can use the Amazon RDS package `rdsadmin.rdsadmin_rman_util` to validate Amazon RDS for Oracle database files, such as data files, tablespaces, control files, and server parameter files (SPFILEs).
For more information about RMAN validation, see [ Validating database files and backups](https://docs.oracle.com/database/121/BRADV/rcmvalid.htm#BRADV90063) and [ VALIDATE](https://docs.oracle.com/database/121/RCMRF/rcmsynta2025.htm#RCMRF162) in the Oracle documentation.
**Topics**
+ [Validating a database](#Appendix.Oracle.CommonDBATasks.ValidateDB)
+ [Validating a tenant database](#Appendix.Oracle.CommonDBATasks.ValidateTenantDB)
+ [Validating a tablespace](#Appendix.Oracle.CommonDBATasks.ValidateTablespace)
+ [Validating a control file](#Appendix.Oracle.CommonDBATasks.ValidateControlFile)
+ [Validating an SPFILE](#Appendix.Oracle.CommonDBATasks.ValidateSpfile)
+ [Validating an Oracle data file](#Appendix.Oracle.CommonDBATasks.ValidateDataFile)
## Validating a database
To validate all of the relevant files used by an Oracle database in RDS for Oracle, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.validate_database`.
This procedure uses the following common parameters for RMAN tasks:
+ `p_validation_type`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_rman_to_dbms_output`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
The following example validates the database using the default values for the parameters.
```
EXEC rdsadmin.rdsadmin_rman_util.validate_database;
```
The following example validates the database using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.validate_database(
p_validation_type => 'PHYSICAL+LOGICAL',
p_parallel => 4,
p_section_size_mb => 10,
p_rman_to_dbms_output => FALSE);
END;
/
```
When the `p_rman_to_dbms_output` parameter is set to `FALSE`, the RMAN output is written to a file in the `BDUMP` directory.
To view the files in the `BDUMP` directory, run the following `SELECT` statement.
```
SELECT * FROM table(rdsadmin.rds_file_util.listdir('BDUMP')) order by mtime;
```
To view the contents of a file in the `BDUMP` directory, run the following `SELECT` statement.
```
SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','rds-rman-validate-nnn.txt'));
```
Replace the file name with the name of the file you want to view.
## Validating a tenant database
To validate the data files of the tenant database in a container database (CDB), use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.validate_tenant`.
This procedure applies only to the current tenant database and uses the following common parameters for RMAN tasks:
+ `p_validation_type`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_rman_to_dbms_output`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md). This procedure is supported for the following DB engine versions:
+ Oracle Database 21c (21.0.0) CDB
+ Oracle Database 19c (19.0.0) CDB
The following example validates the current tenant database using the default values for the parameters.
```
EXEC rdsadmin.rdsadmin_rman_util.validate_tenant;
```
The following example validates the current tenant database using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.validate_tenant(
p_validation_type => 'PHYSICAL+LOGICAL',
p_parallel => 4,
p_section_size_mb => 10,
p_rman_to_dbms_output => FALSE);
END;
/
```
When the `p_rman_to_dbms_output` parameter is set to `FALSE`, the RMAN output is written to a file in the `BDUMP` directory.
To view the files in the `BDUMP` directory, run the following `SELECT` statement.
```
SELECT * FROM table(rdsadmin.rds_file_util.listdir('BDUMP')) order by mtime;
```
To view the contents of a file in the `BDUMP` directory, run the following `SELECT` statement.
```
SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','rds-rman-validate-nnn.txt'));
```
Replace the file name with the name of the file you want to view.
## Validating a tablespace
To validate the files associated with a tablespace, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.validate_tablespace`.
This procedure uses the following common parameters for RMAN tasks:
+ `p_validation_type`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_rman_to_dbms_output`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure also uses the following additional parameter.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_tablespace_name` | varchar2 | A valid tablespace name | — | Yes | The name of the tablespace. |
## Validating a control file
To validate only the control file used by an Amazon RDS Oracle DB instance, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.validate_current_controlfile`.
This procedure uses the following common parameter for RMAN tasks:
+ `p_validation_type`
+ `p_rman_to_dbms_output`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
## Validating an SPFILE
To validate only the server parameter file (SPFILE) used by an Amazon RDS Oracle DB instance, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.validate_spfile`.
This procedure uses the following common parameter for RMAN tasks:
+ `p_validation_type`
+ `p_rman_to_dbms_output`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
## Validating an Oracle data file
To validate a data file, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.validate_datafile`.
This procedure uses the following common parameters for RMAN tasks:
+ `p_validation_type`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_rman_to_dbms_output`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure also uses the following additional parameters.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_datafile` | varchar2 | A valid datafile ID number or a valid datafile name including complete path | — | Yes | The datafile ID number (from `v$datafile.file#`) or the full datafile name including the path (from `v$datafile.name`). |
| `p_from_block` | number | A valid integer | `NULL` | No | The number of the block where the validation starts within the data file. When this is `NULL`, `1` is used. |
| `p_to_block` | number | A valid integer | `NULL` | No | The number of the block where the validation ends within the data file. When this is `NULL`, the maximum block in the data file is used. |
# Enabling and disabling block change tracking
Block changing tracking records changed blocks in a tracking file. This technique can improve the performance of RMAN incremental backups. For more information, see [Using Block Change Tracking to Improve Incremental Backup Performance](https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/backing-up-database.html#GUID-4E1F605A-76A7-48D0-9D9B-7343B4327E2A) in the Oracle Database documentation.
RMAN features aren't supported in a read replica. However, as part of your high availability strategy, you might choose to enable block tracking in a read-only replica using the procedure `rdsadmin.rdsadmin_rman_util.enable_block_change_tracking`. If you promote this read-only replica to a source DB instance, block change tracking is enabled for the new source instance. Thus, your instance can benefit from fast incremental backups.
Block change tracking procedures are supported in Enterprise Edition only for the following DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
**Note**
In a single-tenant CDB, the following operations work, but no customer-visible mechanism can detect the current status of the operations. See also [Limitations of RDS for Oracle CDBs](Oracle.Concepts.CDBs.md#Oracle.Concepts.single-tenant-limitations).
To enable block change tracking for a DB instance, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.enable_block_change_tracking`. To disable block change tracking, use `disable_block_change_tracking`. These procedures take no parameters.
To determine whether block change tracking is enabled for your DB instance, run the following query.
```
SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;
```
The following example enables block change tracking for a DB instance.
```
EXEC rdsadmin.rdsadmin_rman_util.enable_block_change_tracking;
```
The following example disables block change tracking for a DB instance.
```
EXEC rdsadmin.rdsadmin_rman_util.disable_block_change_tracking;
```
# Crosschecking archived redo logs
You can crosscheck archived redo logs using the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.crosscheck_archivelog`.
You can use this procedure to crosscheck the archived redo logs registered in the control file and optionally delete the expired logs records. When RMAN makes a backup, it creates a record in the control file. Over time, these records increase the size of the control file. We recommend that you remove expired records periodically.
**Note**
Standard Amazon RDS backups don't use RMAN and therefore don't create records in the control file.
This procedure uses the common parameter `p_rman_to_dbms_output` for RMAN tasks.
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure also uses the following additional parameter.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_delete_expired` | boolean | `TRUE`, `FALSE` | `TRUE` | No | When `TRUE`, delete expired archived redo log records from the control file. When `FALSE`, retain the expired archived redo log records in the control file. |
This procedure is supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
The following example marks archived redo log records in the control file as expired, but does not delete the records.
```
BEGIN
rdsadmin.rdsadmin_rman_util.crosscheck_archivelog(
p_delete_expired => FALSE,
p_rman_to_dbms_output => FALSE);
END;
/
```
The following example deletes expired archived redo log records from the control file.
```
BEGIN
rdsadmin.rdsadmin_rman_util.crosscheck_archivelog(
p_delete_expired => TRUE,
p_rman_to_dbms_output => FALSE);
END;
/
```
# Backing up archived redo log files
You can use the Amazon RDS package `rdsadmin.rdsadmin_rman_util` to back up archived redo logs for an Amazon RDS Oracle DB instance.
The procedures for backing up archived redo logs are supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
**Topics**
+ [Backing up all archived redo logs](#Appendix.Oracle.CommonDBATasks.BackupArchivedLogs.All)
+ [Backing up an archived redo log from a date range](#Appendix.Oracle.CommonDBATasks.BackupArchivedLogs.Date)
+ [Backing up an archived redo log from an SCN range](#Appendix.Oracle.CommonDBATasks.BackupArchivedLogs.SCN)
+ [Backing up an archived redo log from a sequence number range](#Appendix.Oracle.CommonDBATasks.BackupArchivedLogs.Sequence)
## Backing up all archived redo logs
To back up all of the archived redo logs for an Amazon RDS Oracle DB instance, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_archivelog_all`.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
The following example backs up all archived redo logs for the DB instance.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_archivelog_all(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_parallel => 4,
p_tag => 'MY_LOG_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
## Backing up an archived redo log from a date range
To back up specific archived redo logs for an Amazon RDS Oracle DB instance by specifying a date range, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_archivelog_date`. The date range specifies which archived redo logs to back up.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure also uses the following additional parameters.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_from_date` | date | A date that is between the `start_date` and `next_date` of an archived redo log that exists on disk. The value must be less than or equal to the value specified for `p_to_date`. | — | Yes | The starting date for the archived log backups. |
| `p_to_date` | date | A date that is between the `start_date` and `next_date` of an archived redo log that exists on disk. The value must be greater than or equal to the value specified for `p_from_date`. | — | Yes | The ending date for the archived log backups. |
The following example backs up archived redo logs in the date range for the DB instance.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_archivelog_date(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_from_date => '03/01/2019 00:00:00',
p_to_date => '03/02/2019 00:00:00',
p_parallel => 4,
p_tag => 'MY_LOG_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
## Backing up an archived redo log from an SCN range
To back up specific archived redo logs for an Amazon RDS Oracle DB instance by specifying a system change number (SCN) range, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_archivelog_scn`. The SCN range specifies which archived redo logs to back up.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure also uses the following additional parameters.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_from_scn` | number | An SCN of an archived redo log that exists on disk. The value must be less than or equal to the value specified for `p_to_scn`. | — | Yes | The starting SCN for the archived log backups. |
| `p_to_scn` | number | An SCN of an archived redo log that exists on disk. The value must be greater than or equal to the value specified for `p_from_scn`. | — | Yes | The ending SCN for the archived log backups. |
The following example backs up archived redo logs in the SCN range for the DB instance.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_archivelog_scn(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_from_scn => 1533835,
p_to_scn => 1892447,
p_parallel => 4,
p_tag => 'MY_LOG_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
## Backing up an archived redo log from a sequence number range
To back up specific archived redo logs for an Amazon RDS Oracle DB instance by specifying a sequence number range, use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_archivelog_sequence`. The sequence number range specifies which archived redo logs to back up.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure also uses the following additional parameters.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_from_sequence` | number | A sequence number an archived redo log that exists on disk. The value must be less than or equal to the value specified for `p_to_sequence`. | — | Yes | The starting sequence number for the archived log backups. |
| `p_to_sequence` | number | A sequence number of an archived redo log that exists on disk. The value must be greater than or equal to the value specified for `p_from_sequence`. | — | Yes | The ending sequence number for the archived log backups. |
The following example backs up archived redo logs in the sequence number range for the DB instance.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_archivelog_sequence(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_from_sequence => 11160,
p_to_sequence => 11160,
p_parallel => 4,
p_tag => 'MY_LOG_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
# Performing a full database backup
You can perform a backup of all blocks of data files included in the backup using Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_database_full`.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_include_archive_logs`
+ `p_optimize`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure is supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
The following example performs a full backup of the DB instance using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_database_full(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_parallel => 4,
p_section_size_mb => 10,
p_tag => 'FULL_DB_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
# Performing a full backup of a tenant database
You can perform a backup of all data blocks included a tenant database in a container database (CDB). Use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_tenant_full`. This procedure applies only to the current database backup and uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_include_archive_logs`
+ `p_optimize`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
The `rdsadmin_rman_util.backup_tenant_full` procedure is supported for the following RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0) CDB
+ Oracle Database 19c (19.0.0) CDB
The following example performs a full backup of the current tenant database using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_tenant_full(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_parallel => 4,
p_section_size_mb => 10,
p_tag => 'FULL_TENANT_DB_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
# Performing an incremental database backup
You can perform an incremental backup of your DB instance using the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_database_incremental`.
For more information about incremental backups, see [Incremental backups](https://docs.oracle.com/database/121/RCMRF/rcmsynta006.htm#GUID-73642FF2-43C5-48B2-9969-99001C52EB50__BGBHABHH) in the Oracle documentation.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_include_archive_logs`
+ `p_include_controlfile`
+ `p_optimize`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure is supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
This procedure also uses the following additional parameter.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_level` | number | `0`, `1` | `0` | No | Specify `0` to enable a full incremental backup. Specify `1` to enable a non-cumulative incremental backup. |
The following example performs an incremental backup of the DB instance using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_database_incremental(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_level => 1,
p_parallel => 4,
p_section_size_mb => 10,
p_tag => 'MY_INCREMENTAL_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
# Performing an incremental backup of a tenant database
You can perform an incremental backup of the current tenant database in your CDB. Use the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_tenant_incremental`.
For more information about incremental backups, see [Incremental backups](https://docs.oracle.com/database/121/RCMRF/rcmsynta006.htm#GUID-73642FF2-43C5-48B2-9969-99001C52EB50__BGBHABHH) in the Oracle Database documentation.
This procedure applies only to the current tenant database and uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_include_archive_logs`
+ `p_include_controlfile`
+ `p_optimize`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure is supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0) CDB
+ Oracle Database 19c (19.0.0) CDB
This procedure also uses the following additional parameter.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_level` | number | `0`, `1` | `0` | No | Specify `0` to enable a full incremental backup. Specify `1` to enable a non-cumulative incremental backup. |
The following example performs an incremental backup of the current tenant database using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_tenant_incremental(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_level => 1,
p_parallel => 4,
p_section_size_mb => 10,
p_tag => 'MY_INCREMENTAL_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
# Backing up a tablespace
You can back up a tablespace using the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_tablespace`.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_parallel`
+ `p_section_size_mb`
+ `p_include_archive_logs`
+ `p_include_controlfile`
+ `p_optimize`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure also uses the following additional parameter.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_tablespace_name` | varchar2 | A valid tablespace name. | — | Yes | The name of the tablespace to back up. |
This procedure is supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
The following example performs a tablespace backup using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_tablespace(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_tablespace_name => 'MYTABLESPACE',
p_parallel => 4,
p_section_size_mb => 10,
p_tag => 'MYTABLESPACE_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
# Backing up a control file
You can back up a control file using the Amazon RDS procedure `rdsadmin.rdsadmin_rman_util.backup_current_controlfile`.
This procedure uses the following common parameters for RMAN tasks:
+ `p_owner`
+ `p_directory_name`
+ `p_label`
+ `p_compress`
+ `p_rman_to_dbms_output`
+ `p_tag`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure is supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
The following example backs up a control file using the specified values for the parameters.
```
BEGIN
rdsadmin.rdsadmin_rman_util.backup_current_controlfile(
p_owner => 'SYS',
p_directory_name => 'MYDIRECTORY',
p_tag => 'CONTROL_FILE_BACKUP',
p_rman_to_dbms_output => FALSE);
END;
/
```
# Performing block media recovery
You can recovery individual data blocks, known as block media recovery, using the Amazon RDS procedures `rdsadmin.rdsadmin_rman_util.recover_datafile_block`. You can use this overloaded procedure to recover either an individual data block or a range of data blocks.
This procedure uses the following common parameter for RMAN tasks:
+ `p_rman_to_dbms_output`
For more information, see [Common parameters for RMAN procedures](Appendix.Oracle.CommonDBATasks.CommonParameters.md).
This procedure uses the following additional parameters.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `p_datafile` | `NUMBER` | A valid data file ID number. | — | Yes | The data file containing the corrupt blocks. Specify the data file in either of the following ways: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.block-media-recovery.html) |
| `p_block` | `NUMBER` | A valid integer. | — | Yes | The number of an individual block to be recovered. The following parameters are mutually exclusive: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.block-media-recovery.html) |
| `p_from_block` | `NUMBER` | A valid integer. | — | Yes | The first block number in a range of blocks to be recovered. The following parameters are mutually exclusive: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.block-media-recovery.html) |
| `p_to_block` | `NUMBER` | A valid integer. | — | Yes | The last block number in a range of blocks to be recovered. The following parameters are mutually exclusive: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.block-media-recovery.html) |
This procedure is supported for the following Amazon RDS for Oracle DB engine versions:
+ Oracle Database 21c (21.0.0)
+ Oracle Database 19c (19.0.0)
The following example recovers block 100 in data file 5.
```
BEGIN
rdsadmin.rdsadmin_rman_util.recover_datafile_block(
p_datafile => 5,
p_block => 100,
p_rman_to_dbms_output => TRUE);
END;
/
```
The following example recovers blocks 100 to 150 in data file 5.
```
BEGIN
rdsadmin.rdsadmin_rman_util.recover_datafile_block(
p_datafile => 5,
p_from_block => 100,
p_to_block => 150,
p_rman_to_dbms_output => TRUE);
END;
/
```
# Performing common scheduling tasks for Oracle DB instances
Some scheduler jobs owned by `SYS` can interfere with normal database operations. In such cases, Oracle Support recommends that you modify the schedule. If you need to enable or disable `SYS` jobs, test the operation on scheduled jobs in a test environment before implementing it in a production environment. To perform tasks for Oracle Scheduler jobs owned by `SYS`, use the Amazon RDS package `rdsadmin.rdsadmin_dbms_scheduler`.
The `rdsadmin.rdsadmin_dbms_scheduler` procedures are supported for the Amazon RDS for Oracle DB engine versions shown in the following table. When using this package, you can specify the `SYS` jobs listed in the table.
| Database release | Jobs enabled by default | Jobs disabled by default |
| --- | --- | --- |
| Oracle Database 19c | BSLN_MAINTAIN_STATS_JOB
CLEANUP_NON_EXIST_OBJ
CLEANUP_ONLINE_IND_BUILD
CLEANUP_ONLINE_PMO
CLEANUP_TAB_IOT_PMO
CLEANUP_TRANSIENT_PKG
CLEANUP_TRANSIENT_TYPE
DRA_REEVALUATE_OPEN_FAILURES
FILE_SIZE_UPD
ORA$AUTOTASK_CLEAN
PMO_DEFERRED_GIDX_MAINT_JOB
PURGE_LOG
RSE$CLEAN_RECOVERABLE_SCRIPT
SM$CLEAN_AUTO_SPLIT_MERGE
| FGR$AUTOPURGE_JOB
FILE_WATCHER
HM_CREATE_OFFLINE_DICTIONARY
LOAD_OPATCH_INVENTORY
ORA$PREPLUGIN_BACKUP_JOB
XMLDB_NFS_CLEANUP_JOB
|
| Oracle Database 21c | BSLN_MAINTAIN_STATS_JOB
CLEANUP_NON_EXIST_OBJ
CLEANUP_ONLINE_IND_BUILD
CLEANUP_ONLINE_PMO
CLEANUP_TAB_IOT_PMO
CLEANUP_TRANSIENT_PKG
CLEANUP_TRANSIENT_TYPE
DRA_REEVALUATE_OPEN_FAILURES
FILE_SIZE_UPD
ORA$AUTOTASK_CLEAN
PMO_DEFERRED_GIDX_MAINT_JOB
PURGE_LOG
| FGR$AUTOPURGE_JOB
FILE_WATCHER
HM_CREATE_OFFLINE_DICTIONARY
LOAD_OPATCH_INVENTORY
ORA$PREPLUGIN_BACKUP_JOB
ORA$_ATSK_AUTOSTS
XMLDB_NFS_CLEANUP_JOB
|
## Common parameters for Oracle Scheduler procedures
To perform tasks with Oracle Scheduler, use procedures in the Amazon RDS package `rdsadmin.rdsadmin_dbms_scheduler`. Several parameters are common to the procedures in the package. The package has the following common parameters.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `name` | varchar2 | The procedures listed in the table in [Performing common scheduling tasks for Oracle DB instances](#Appendix.Oracle.CommonDBATasks.Scheduler) | — | Yes | The name of the job to modify. |
| `attribute` | varchar2 | `'REPEAT_INTERVAL'`,`'SCHEDULE_NAME'` | – | Yes | Attribute to modify. To modify the repeat interval for the job, specify `'REPEAT_INTERVAL'`. To modify the schedule name for the job, specify `'SCHEDULE_NAME'`. |
| `value` | varchar2 | A valid schedule interval or schedule name, depending on attribute used. | – | Yes | The new value of the attribute. |
## Modifying DBMS\$1SCHEDULER jobs
To modify certain components of Oracle Scheduler, use the Oracle procedure `dbms_scheduler.set_attribute`. For more information, see [DBMS\$1SCHEDULER](https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235) and [SET\$1ATTRIBUTE procedure](https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72399) in the Oracle documentation.
When working with Amazon RDS DB instances, prepend the schema name `SYS` to the object name. The following example sets the resource plan attribute for the Monday window object.
```
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'SYS.MONDAY_WINDOW',
attribute => 'RESOURCE_PLAN',
value => 'resource_plan_1');
END;
/
```
## Modifying AutoTask maintenance windows
Amazon RDS for Oracle instances are created with default settings for maintenance windows. Automated maintenance tasks such as optimizer statistics collection run during these windows. By default, the maintenance windows turn on Oracle Database Resource Manager.
To modify the window, use the `DBMS_SCHEDULER` package. You might need to modify the maintenance window settings for the following reasons:
+ You want maintenance jobs to run at a different time, with different settings, or not at all. For example, might want to modify the window duration, or change the repeat time and interval.
+ You want to avoid the performance impact of enabling Resource Manager during maintenance. For example, if the default maintenance plan is specified, and if the maintenance window opens while the database is under load, you might see wait events such as `resmgr:cpu quantum`. This wait event is related to Database Resource Manager. You have the following options:
+ Ensure that maintenance windows are active during off-peak times for your DB instance.
+ Disable the default maintenance plan by setting the `resource_plan` attribute to an empty string.
+ Set the `resource_manager_plan` parameter to `FORCE:` in your parameter group. If your instance uses Enterprise Edition, this setting prevents Database Resource Manager plans from activating.
**To modify your maintenance window settings**
1. Connect to your database using an Oracle SQL client.
1. Query the current configuration for a scheduler window.
The following example queries the configuration for `MONDAY_WINDOW`.
```
SELECT ENABLED, RESOURCE_PLAN, DURATION, REPEAT_INTERVAL
FROM DBA_SCHEDULER_WINDOWS
WHERE WINDOW_NAME='MONDAY_WINDOW';
```
The following output shows that the window is using the default values.
```
ENABLED RESOURCE_PLAN DURATION REPEAT_INTERVAL
--------------- ------------------------------ ---------------- ------------------------------
TRUE DEFAULT_MAINTENANCE_PLAN +000 04:00:00 freq=daily;byday=MON;byhour=22
;byminute=0; bysecond=0
```
1. Modify the window using the `DBMS_SCHEDULER` package.
The following example sets the resource plan to null so that the Resource Manager won't run during the maintenance window.
```
BEGIN
-- disable the window to make changes
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
-- specify the empty string to use no plan
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"', attribute=>'RESOURCE_PLAN', value=>'');
-- re-enable the window
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
END;
/
```
The following example sets the maximum duration of the window to 2 hours.
```
BEGIN
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"', attribute=>'DURATION', value=>'0 2:00:00');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
END;
/
```
The following example sets the repeat interval to every Monday at 10 AM.
```
BEGIN
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"', attribute=>'REPEAT_INTERVAL', value=>'freq=daily;byday=MON;byhour=10;byminute=0;bysecond=0');
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
END;
/
```
## Setting the time zone for Oracle Scheduler jobs
To modify the time zone for Oracle Scheduler, you can use the Oracle procedure `dbms_scheduler.set_scheduler_attribute`. For more information about the `dbms_scheduler` package, see [DBMS\$1SCHEDULER](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html) and [SET\$1SCHEDULER\$1ATTRIBUTE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-2AB97BF7-7154-4E6C-933F-B2659B18A907) in the Oracle documentation.
**To modify the current time zone setting**
1. Connect to the database using a client such as SQL Developer. For more information, see [Connecting to your DB instance using Oracle SQL developer](USER_ConnectToOracleInstance.SQLDeveloper.md).
1. Set the default time zone as following, substituting your time zone for `time_zone_name`.
```
BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
attribute => 'default_timezone',
value => 'time_zone_name'
);
END;
/
```
In the following example, you change the time zone to Asia/Shanghai.
Start by querying the current time zone, as shown following.
```
SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME='DEFAULT_TIMEZONE';
```
The output shows that the current time zone is ETC/UTC.
```
VALUE
-------
Etc/UTC
```
Then you set the time zone to Asia/Shanghai.
```
BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
attribute => 'default_timezone',
value => 'Asia/Shanghai'
);
END;
/
```
For more information about changing the system time zone, see [Oracle time zone](Appendix.Oracle.Options.Timezone.md).
## Turning off Oracle Scheduler jobs owned by SYS
To disable an Oracle Scheduler job owned by the SYS user, use the `rdsadmin.rdsadmin_dbms_scheduler.disable` procedure.
This procedure uses the `name` common parameter for Oracle Scheduler tasks. For more information, see [Common parameters for Oracle Scheduler procedures](#Appendix.Oracle.CommonDBATasks.Scheduler.CommonParameters).
The following example disables the `SYS.CLEANUP_ONLINE_IND_BUILD` Oracle Scheduler job.
```
BEGIN
rdsadmin.rdsadmin_dbms_scheduler.disable('SYS.CLEANUP_ONLINE_IND_BUILD');
END;
/
```
## Turning on Oracle Scheduler jobs owned by SYS
To turn on an Oracle Scheduler job owned by SYS, use the `rdsadmin.rdsadmin_dbms_scheduler.enable` procedure.
This procedure uses the `name` common parameter for Oracle Scheduler tasks. For more information, see [Common parameters for Oracle Scheduler procedures](#Appendix.Oracle.CommonDBATasks.Scheduler.CommonParameters).
The following example enables the `SYS.CLEANUP_ONLINE_IND_BUILD` Oracle Scheduler job.
```
BEGIN
rdsadmin.rdsadmin_dbms_scheduler.enable('SYS.CLEANUP_ONLINE_IND_BUILD');
END;
/
```
## Modifying the Oracle Scheduler repeat interval for jobs of CALENDAR type
To modify the repeat interval to modify a SYS-owned Oracle Scheduler job of `CALENDAR` type, use the `rdsadmin.rdsadmin_dbms_scheduler.disable` procedure.
This procedure uses the following common parameters for Oracle Scheduler tasks:
+ `name`
+ `attribute`
+ `value`
For more information, see [Common parameters for Oracle Scheduler procedures](#Appendix.Oracle.CommonDBATasks.Scheduler.CommonParameters).
The following example modifies the repeat interval of the `SYS.CLEANUP_ONLINE_IND_BUILD` Oracle Scheduler job.
```
BEGIN
rdsadmin.rdsadmin_dbms_scheduler.set_attribute(
name => 'SYS.CLEANUP_ONLINE_IND_BUILD',
attribute => 'repeat_interval',
value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0;bysecond=0');
END;
/
```
## Modifying the Oracle Scheduler repeat interval for jobs of NAMED type
Some Oracle Scheduler jobs use a schedule name instead of an interval. For this type of jobs, you must create a new named schedule in the master user schema. Use the standard Oracle `sys.dbms_scheduler.create_schedule` procedure to do this. Also, use the `rdsadmin.rdsadmin_dbms_scheduler.set_attribute procedure` to assign the new named schedule to the job.
This procedure uses the following common parameter for Oracle Scheduler tasks:
+ `name`
+ `attribute`
+ `value`
For more information, see [Common parameters for Oracle Scheduler procedures](#Appendix.Oracle.CommonDBATasks.Scheduler.CommonParameters).
The following example modifies the repeat interval of the `SYS.BSLN_MAINTAIN_STATS_JOB` Oracle Scheduler job.
```
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'rds_master_user.new_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=0;byminute=0;bysecond=0',
end_date => NULL,
comments => 'Repeats daily forever');
END;
/
BEGIN
rdsadmin.rdsadmin_dbms_scheduler.set_attribute (
name => 'SYS.BSLN_MAINTAIN_STATS_JOB',
attribute => 'schedule_name',
value => 'rds_master_user.new_schedule');
END;
/
```
## Turning off autocommit for Oracle Scheduler job creation
When `DBMS_SCHEDULER.CREATE_JOB` creates Oracle Scheduler jobs, it creates the jobs immediately and commits the changes. You might need to incorporate the creation of Oracle Scheduler jobs in the user transaction to do the following:
+ Roll back the Oracle Schedule job when the user transaction is rolled back.
+ Create the Oracle Scheduler job when the main user transaction is committed.
You can use the procedure `rdsadmin.rdsadmin_dbms_scheduler.set_no_commit_flag` to turn on this behavior. This procedure takes no parameters. You can use this procedure in the following RDS for Oracle releases:
+ 21.0.0.0.ru-2022-07.rur-2022-07.r1 and higher
+ 19.0.0.0.ru-2022-07.rur-2022-07.r1 and higher
The following example turns off autocommit for Oracle Scheduler, creates an Oracle Scheduler job, and then rolls back the transaction. Because autocommit is turned off, the database also rolls back the creation of the Oracle Scheduler job.
```
BEGIN
rdsadmin.rdsadmin_dbms_scheduler.set_no_commit_flag;
DBMS_SCHEDULER.CREATE_JOB(job_name => 'EMPTY_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'begin null; end;',
auto_drop => false);
ROLLBACK;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='EMPTY_JOB';
no rows selected
```
# 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-8DEB1BE0-8FB9-4FB2-A19A-17CF6F5791C3) in the Oracle Database documentation.
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-task_id.log`. 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](USER_LogAccess.Procedural.Downloading.md).
## 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 |
| --- | --- | --- | --- | --- | --- |
| `incident_id` | 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. |
| `problem_id` | 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. |
| `last` | 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](#Appendix.Oracle.CommonDBATasks.CommonDiagParameters).
This function uses the following additional parameter.
****
| Parameter name | Data type | Valid values | Default | Required | Description |
| --- | --- | --- | --- | --- | --- |
| `detail` | boolean | TRUE or FALSE | `FALSE` | No | If `TRUE`, the function lists incidents in detail mode. If `FALSE`, the function lists incidents in basic mode. |
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](#Appendix.Oracle.CommonDBATasks.CommonDiagParameters).
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](#Appendix.Oracle.CommonDBATasks.CommonDiagParameters).
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](USER_LogAccess.Procedural.Downloading.md).
## 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 |
| --- | --- | --- | --- | --- | --- |
| `filename` | 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\$1ORCL.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](USER_LogAccess.Procedural.Downloading.md).
# Performing miscellaneous tasks for Oracle DB instances
Following, you can find how to perform miscellaneous DBA tasks on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and restricts access to certain system procedures and tables that require advanced privileges.
**Topics**
+ [Creating and dropping directories in the main data storage space](#Appendix.Oracle.CommonDBATasks.NewDirectories)
+ [Listing files in a DB instance directory](#Appendix.Oracle.CommonDBATasks.ListDirectories)
+ [Reading files in a DB instance directory](#Appendix.Oracle.CommonDBATasks.ReadingFiles)
+ [Accessing Opatch files](#Appendix.Oracle.CommonDBATasks.accessing-opatch-files)
+ [Managing advisor tasks](#Appendix.Oracle.CommonDBATasks.managing-advisor-tasks)
+ [Transporting tablespaces](rdsadmin_transport_util.md)
## Creating and dropping directories in the main data storage space
To create directories, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.create_directory`. You can create up to 10,000 directories, all located in your main data storage space. To drop directories, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.drop_directory`.
The `create_directory` and `drop_directory` procedures have the following required parameter.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_directory_name` | VARCHAR2 | — | Yes | The name of the directory. |
The following example creates a new directory named `PRODUCT_DESCRIPTIONS`.
```
EXEC rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'product_descriptions');
```
The data dictionary stores the directory name in uppercase. You can list the directories by querying `DBA_DIRECTORIES`. The system chooses the actual host pathname automatically. The following example gets the directory path for the directory named `PRODUCT_DESCRIPTIONS`:
```
SELECT DIRECTORY_PATH
FROM DBA_DIRECTORIES
WHERE DIRECTORY_NAME='PRODUCT_DESCRIPTIONS';
DIRECTORY_PATH
----------------------------------------
/rdsdbdata/userdirs/01
```
The master user name for the DB instance has read and write privileges in the new directory, and can grant access to other users. `EXECUTE` privileges are not available for directories on a DB instance. Directories are created in your main data storage space and will consume space and I/O bandwidth.
The following example drops the directory named `PRODUCT_DESCRIPTIONS`.
```
EXEC rdsadmin.rdsadmin_util.drop_directory(p_directory_name => 'product_descriptions');
```
**Note**
You can also drop a directory by using the Oracle SQL command `DROP DIRECTORY`.
Dropping a directory doesn't remove its contents. Because the `rdsadmin.rdsadmin_util.create_directory` procedure can reuse pathnames, files in dropped directories can appear in a newly created directory. Before you drop a directory, we recommend that you use `UTL_FILE.FREMOVE` to remove files from the directory. For more information, see [FREMOVE procedure](https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70924) in the Oracle documentation.
## Listing files in a DB instance directory
To list the files in a directory, use the Amazon RDS procedure `rdsadmin.rds_file_util.listdir`. This procedure isn't supported on an Oracle replica. The `listdir` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_directory` | varchar2 | — | Yes | The name of the directory to list. |
The following example grants read/write privileges on the directory `PRODUCT_DESCRIPTIONS` to user `rdsadmin`, and then lists the files in this directory.
```
GRANT READ,WRITE ON DIRECTORY PRODUCT_DESCRIPTIONS TO rdsadmin;
SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'PRODUCT_DESCRIPTIONS'));
```
## Reading files in a DB instance directory
To read a text file, use the Amazon RDS procedure `rdsadmin.rds_file_util.read_text_file`. The `read_text_file` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_directory` | varchar2 | — | Yes | The name of the directory that contains the file. |
| `p_filename` | varchar2 | — | Yes | The name of the file to read. |
The following example creates the file `rice.txt` in the directory `PRODUCT_DESCRIPTIONS`.
```
declare
fh sys.utl_file.file_type;
begin
fh := utl_file.fopen(location=>'PRODUCT_DESCRIPTIONS', filename=>'rice.txt', open_mode=>'w');
utl_file.put(file=>fh, buffer=>'AnyCompany brown rice, 15 lbs');
utl_file.fclose(file=>fh);
end;
/
```
The following example reads the file `rice.txt` from the directory `PRODUCT_DESCRIPTIONS`.
```
SELECT * FROM TABLE
(rdsadmin.rds_file_util.read_text_file(
p_directory => 'PRODUCT_DESCRIPTIONS',
p_filename => 'rice.txt'));
```
## Accessing Opatch files
Opatch is an Oracle utility that enables the application and rollback of patches to Oracle software. The Oracle mechanism for determining which patches have been applied to a database is the `opatch lsinventory` command. To open service requests for Bring Your Own Licence (BYOL) customers, Oracle Support requests the `lsinventory` file and sometimes the `lsinventory_detail` file generated by Opatch.
To deliver a managed service experience, Amazon RDS doesn't provide shell access to Opatch. Instead, the `lsinventory-dbv.txt` in the BDUMP directory contains the patch information related to your current engine version. When you perform a minor or major upgrade, Amazon RDS updates `lsinventory-dbv.txt` within an hour of applying the patch. To verify the applied patches, read `lsinventory-dbv.txt`. This action is similar to running the `opatch lsinventory` command.
**Note**
The examples in this section assume that the BDUMP directory is named `BDUMP`. On a read replica, the BDUMP directory name is different. To learn how to get the BDUMP name by querying `V$DATABASE.DB_UNIQUE_NAME` on a read replica, see [Listing files](USER_LogAccess.Concepts.Oracle.md#USER_LogAccess.Concepts.Oracle.WorkingWithTracefiles.ViewingBackgroundDumpDest).
The inventory files use the Amazon RDS naming convention `lsinventory-dbv.txt` and `lsinventory_detail-dbv.txt`, where *dbv* is the full name of your DB version. The `lsinventory-dbv.txt` file is available on all DB versions. The corresponding `lsinventory_detail-dbv.txt` is available on 19.0.0.0, ru-2020-01.rur-2020-01.r1 or later.
For example, if your DB version is 19.0.0.0.ru-2021-07.rur-2021-07.r1, then your inventory files have the following names.
```
lsinventory-19.0.0.0.ru-2021-07.rur-2021-07.r1.txt
lsinventory_detail-19.0.0.0.ru-2021-07.rur-2021-07.r1.txt
```
Ensure that you download the files that match the current version of your DB engine.
### Console
**To download an inventory file using the console**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Databases**.
1. Choose the name of the DB instance that has the log file that you want to view.
1. Choose the **Logs & events** tab.
1. Scroll down to the **Logs** section.
1. In the **Logs** section, search for `lsinventory`.
1. Select the file that you want to access, and then choose **Download**.
### SQL
To read the `lsinventory-dbv.txt` in a SQL client, you can use a `SELECT` statement. For this technique, use either of the following `rdsadmin` functions: `rdsadmin.rds_file_util.read_text_file` or `rdsadmin.tracefile_listing`.
In the following sample query, replace *dbv* with your Oracle DB version. For example, your DB version might be 19.0.0.0.ru-2020-04.rur-2020-04.r1.
```
SELECT text
FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'lsinventory-dbv.txt'));
```
### PL/SQL
To read the `lsinventory-dbv.txt` in a SQL client, you can write a PL/SQL program. This program uses `utl_file` to read the file, and `dbms_output` to print it. These are Oracle-supplied packages.
In the following sample program, replace *dbv* with your Oracle DB version. For example, your DB version might be 19.0.0.0.ru-2020-04.rur-2020-04.r1.
```
SET SERVEROUTPUT ON
DECLARE
v_file SYS.UTL_FILE.FILE_TYPE;
v_line VARCHAR2(1000);
v_oracle_home_type VARCHAR2(1000);
c_directory VARCHAR2(30) := 'BDUMP';
c_output_file VARCHAR2(30) := 'lsinventory-dbv.txt';
BEGIN
v_file := SYS.UTL_FILE.FOPEN(c_directory, c_output_file, 'r');
LOOP
BEGIN
SYS.UTL_FILE.GET_LINE(v_file, v_line,1000);
DBMS_OUTPUT.PUT_LINE(v_line);
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
END LOOP;
END;
/
```
Or query `rdsadmin.tracefile_listing`, and spool the output to a file. The following example spools the output to `/tmp/tracefile.txt`.
```
SPOOL /tmp/tracefile.txt
SELECT *
FROM rdsadmin.tracefile_listing
WHERE FILENAME LIKE 'lsinventory%';
SPOOL OFF;
```
## Managing advisor tasks
Oracle Database includes a number of advisors. Each advisor supports automated and manual tasks. You can use procedures in the `rdsadmin.rdsadmin_util` package to manage some advisor tasks.
The advisor task procedures are available in the following engine versions:
+ Oracle Database 21c (21.0.0)
+ Version 19.0.0.0.ru-2021-01.rur-2021-01.r1 and higher Oracle Database 19c versions
For more information, see [Version 19.0.0.0.ru-2021-01.rur-2021-01.r1](https://docs.aws.amazon.com/AmazonRDS/latest/OracleReleaseNotes/oracle-version-19-0.html#oracle-version-RU-RUR.19.0.0.0.ru-2021-01.rur-2021-01.r1) in the *Amazon RDS for Oracle Release Notes*.
**Topics**
+ [Setting parameters for advisor tasks](#Appendix.Oracle.CommonDBATasks.setting-task-parameters)
+ [Disabling AUTO\$1STATS\$1ADVISOR\$1TASK](#Appendix.Oracle.CommonDBATasks.dropping-advisor-task)
+ [Re-enabling AUTO\$1STATS\$1ADVISOR\$1TASK](#Appendix.Oracle.CommonDBATasks.recreating-advisor-task)
### Setting parameters for advisor tasks
To set parameters for some advisor tasks, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.advisor_task_set_parameter`. The `advisor_task_set_parameter` procedure has the following parameters.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_task_name` | varchar2 | — | Yes | The name of the advisor task whose parameters you want to change. The following values are valid: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Misc.html) |
| `p_parameter` | varchar2 | — | Yes | The name of the task parameter. To find valid parameters for an advisor task, run the following query. Substitute *p\$1task\$1name* with a valid value for `p_task_name`: COL PARAMETER_NAME FORMAT a30
COL PARAMETER_VALUE FORMAT a30
SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_ADVISOR_PARAMETERS
WHERE TASK_NAME='p_task_name'
AND PARAMETER_VALUE != 'UNUSED'
ORDER BY PARAMETER_NAME;
|
| `p_value` | varchar2 | — | Yes | The value for a task parameter. To find valid values for task parameters, run the following query. Substitute *p\$1task\$1name* with a valid value for `p_task_name`: COL PARAMETER_NAME FORMAT a30
COL PARAMETER_VALUE FORMAT a30
SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_ADVISOR_PARAMETERS
WHERE TASK_NAME='p_task_name'
AND PARAMETER_VALUE != 'UNUSED'
ORDER BY PARAMETER_NAME;
|
The following PL/SQL program sets `ACCEPT_PLANS` to `FALSE` for `SYS_AUTO_SPM_EVOLVE_TASK`. The SQL Plan Management automated task verifies the plans and generates a report of its findings, but does not evolve the plans automatically. You can use a report to identify new SQL plan baselines and accept them manually.
```
BEGIN
rdsadmin.rdsadmin_util.advisor_task_set_parameter(
p_task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
p_parameter => 'ACCEPT_PLANS',
p_value => 'FALSE');
END;
```
The following PL/SQL program sets `EXECUTION_DAYS_TO_EXPIRE` to `10` for `AUTO_STATS_ADVISOR_TASK`. The predefined task `AUTO_STATS_ADVISOR_TASK` runs automatically in the maintenance window once per day. The example sets the retention period for the task execution to 10 days.
```
BEGIN
rdsadmin.rdsadmin_util.advisor_task_set_parameter(
p_task_name => 'AUTO_STATS_ADVISOR_TASK',
p_parameter => 'EXECUTION_DAYS_TO_EXPIRE',
p_value => '10');
END;
```
### Disabling AUTO\$1STATS\$1ADVISOR\$1TASK
To disable `AUTO_STATS_ADVISOR_TASK`, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.advisor_task_drop`. The `advisor_task_drop` procedure accepts the following parameter.
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_task_name` | varchar2 | — | Yes | The name of the advisor task to be disabled. The only valid value is `AUTO_STATS_ADVISOR_TASK`. |
The following command drops `AUTO_STATS_ADVISOR_TASK`.
```
EXEC rdsadmin.rdsadmin_util.advisor_task_drop('AUTO_STATS_ADVISOR_TASK')
```
You can re-enable `AUTO_STATS_ADVISOR_TASK` using `rdsadmin.rdsadmin_util.dbms_stats_init`.
### Re-enabling AUTO\$1STATS\$1ADVISOR\$1TASK
To re-enable `AUTO_STATS_ADVISOR_TASK`, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.dbms_stats_init`. The `dbms_stats_init` procedure takes no parameters.
The following command re-enables `AUTO_STATS_ADVISOR_TASK`.
```
EXEC rdsadmin.rdsadmin_util.dbms_stats_init()
```
# Transporting tablespaces
Use the Amazon RDS package `rdsadmin.rdsadmin_transport_util` to copy a set of tablespaces from an on-premises Oracle database to an RDS for Oracle DB instance. At the physical level, the transportable tablespace feature incrementally copies source data files and metadata files to your target instance. You can transfer the files using either Amazon EFS or Amazon S3. For more information, see [Migrating using Oracle transportable tablespaces](oracle-migrating-tts.md).
**Topics**
+ [Importing transported tablespaces to your DB instance](rdsadmin_transport_util_import_xtts_tablespaces.md)
+ [Importing transportable tablespace metadata into your DB instance](rdsadmin_transport_util_import_xtts_metadata.md)
+ [Listing orphaned files after a tablespace import](rdsadmin_transport_util_list_xtts_orphan_files.md)
+ [Deleting orphaned data files after a tablespace import](rdsadmin_transport_util_cleanup_incomplete_xtts_import.md)
# Importing transported tablespaces to your DB instance
Use the procedure `rdsadmin.rdsadmin_transport_util.import_xtts_tablespaces` to restore tablespaces that you have previously exported from a source DB instance. In the transport phase, you back up your read-only tablespaces, export Data Pump metadata, transfer these files to your target DB instance, and then import the tablespaces. For more information, see [Phase 4: Transport the tablespaces](oracle-migrating-tts.md#oracle-migrating-tts.final-br-phase).
## Syntax
```
FUNCTION import_xtts_tablespaces(
p_tablespace_list IN CLOB,
p_directory_name IN VARCHAR2,
p_platform_id IN NUMBER DEFAULT 13,
p_parallel IN INTEGER DEFAULT 0) RETURN VARCHAR2;
```
## Parameters
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_tablespace_list` | `CLOB` | — | Yes | The list of tablespaces to import. |
| `p_directory_name` | `VARCHAR2` | — | Yes | The directory that contains the tablespace backups. |
| `p_platform_id` | `NUMBER` | `13` | No | Provide a platform ID that matches the one specified during the backup phase. To find a list of platforms, query `V$TRANSPORTABLE_PLATFORM`. The default platform is Linux x86 64-bit, which is little endian. |
| `p_parallel` | `INTEGER` | `0` | No | The degree of parallelism. By default, parallelism is disabled. |
## Examples
The following example imports the tablespaces *TBS1*, *TBS2*, and *TBS3* from the directory *DATA\$1PUMP\$1DIR*. The source platform is AIX-Based Systems (64-bit), which has the platform ID of `6`. You can find the platform IDs by querying `V$TRANSPORTABLE_PLATFORM`.
```
VAR task_id CLOB
BEGIN
:task_id:=rdsadmin.rdsadmin_transport_util.import_xtts_tablespaces(
'TBS1,TBS2,TBS3',
'DATA_PUMP_DIR',
p_platform_id => 6);
END;
/
PRINT task_id
```
# Importing transportable tablespace metadata into your DB instance
Use the procedure `rdsadmin.rdsadmin_transport_util.import_xtts_metadata` to import transportable tablespace metadata into your RDS for Oracle DB instance. During the operation, the status of the metadata import is shown in the table `rdsadmin.rds_xtts_operation_info`. For more information, see [Step 5: Import tablespace metadata on your target DB instance](oracle-migrating-tts.md#oracle-migrating-tts.transport.import-dmp).
## Syntax
```
PROCEDURE import_xtts_metadata(
p_datapump_metadata_file IN SYS.DBA_DATA_FILES.FILE_NAME%TYPE,
p_directory_name IN VARCHAR2,
p_exclude_stats IN BOOLEAN DEFAULT FALSE,
p_remap_tablespace_list IN CLOB DEFAULT NULL,
p_remap_user_list IN CLOB DEFAULT NULL);
```
## Parameters
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_datapump_metadata_file` | `SYS.DBA_DATA_FILES.FILE_NAME%TYPE` | — | Yes | The name of the Oracle Data Pump file that contains the metadata for your transportable tablespaces. |
| `p_directory_name` | `VARCHAR2` | — | Yes | The directory that contains the Data Pump file. |
| `p_exclude_stats` | `BOOLEAN` | `FALSE` | No | Flag that indicates whether to exclude statistics. |
| `p_remap_tablespace_list` | `CLOB` | NULL | No | A list of tablespaces to be remapped during the metadata import. Use the format `from_tbs:to_tbs`. For example, specify `users:user_data`. |
| `p_remap_user_list` | `CLOB` | NULL | No | A list of user schemas to be remapped during the metadata import. Use the format `from_schema_name:to_schema_name`. For example, specify `hr:human_resources`. |
## Examples
The example imports the tablespace metadata from the file *xttdump.dmp*, which is located in directory *DATA\$1PUMP\$1DIR*.
```
BEGIN
rdsadmin.rdsadmin_transport_util.import_xtts_metadata('xttdump.dmp','DATA_PUMP_DIR');
END;
/
```
# Listing orphaned files after a tablespace import
Use the `rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files` procedure to list data files that were orphaned after a tablespace import. After you identify the data files, you can delete them by calling `rdsadmin.rdsadmin_transport_util.cleanup_incomplete_xtts_import`.
## Syntax
```
FUNCTION list_xtts_orphan_files RETURN xtts_orphan_files_list_t PIPELINED;
```
## Examples
The following example runs the procedure `rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files`. The output shows two data files that are orphaned.
```
SQL> SELECT * FROM TABLE(rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files);
FILENAME FILESIZE
-------------- ---------
datafile_7.dbf 104865792
datafile_8.dbf 104865792
```
# Deleting orphaned data files after a tablespace import
Use the `rdsadmin.rdsadmin_transport_util.list_xtts_orphan_files` procedure to delete data files that were orphaned after a tablespace import. Running this command generates a log file that uses the name format `rds-xtts-delete_xtts_orphaned_files-YYYY-MM-DD.HH24-MI-SS.FF.log` in the `BDUMP` directory. Use the procedure `rdsadmin.rdsadmin_transport_util.cleanup_incomplete_xtts_import` to find the orphaned files. You can read the log file by calling the procedure `rdsadmin.rds_file_util.read_text_file`. For more information, see [Phase 6: Clean up leftover files](oracle-migrating-tts.md#oracle-migrating-tts.cleanup).
## Syntax
```
PROCEDURE cleanup_incomplete_xtts_import(
p_directory_name IN VARCHAR2);
```
## Parameters
****
| Parameter name | Data type | Default | Required | Description |
| --- | --- | --- | --- | --- |
| `p_directory_name` | `VARCHAR2` | — | Yes | The directory that contains the orphaned data files. |
## Examples
The following example deletes the orphaned data files in *DATA\$1PUMP\$1DIR*.
```
BEGIN
rdsadmin.rdsadmin_transport_util.cleanup_incomplete_xtts_import('DATA_PUMP_DIR');
END;
/
```
The following example reads the log file generated by the previous command.
```
SELECT *
FROM TABLE(rdsadmin.rds_file_util.read_text_file(
p_directory => 'BDUMP',
p_filename => 'rds-xtts-delete_xtts_orphaned_files-2023-06-01.09-33-11.868894000.log'));
TEXT
--------------------------------------------------------------------------------
orphan transported datafile datafile_7.dbf deleted.
orphan transported datafile datafile_8.dbf deleted.
```