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
- Creating and sizing tablespaces
- Setting the default tablespace
- Setting the default temporary tablespace
- Creating a temporary tablespace on the instance store
- Adding a tempfile to the instance store on a read replica
- Dropping tempfiles on a read replica
- Checkpointing a database
- Setting distributed recovery
- Setting the database time zone
- Working with Oracle external tables
- Generating performance reports with Automatic Workload Repository (AWR)
- Adjusting database links for use with DB instances in a VPC
- Setting the default edition for a DB instance
- Enabling auditing for the SYS.AUD$ table
- Disabling auditing for the SYS.AUD$ table
- Cleaning up interrupted online index builds
- Skipping corrupt blocks
- Resizing tablespaces, data files, and temp files
- Purging the recycle bin
- Setting the default displayed values for full redaction
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 |
---|---|---|---|---|
|
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
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
');
Creating and sizing tablespaces
Amazon RDS only supports Oracle Managed Files (OMF) for data files, log files, and control files. When you create data files and log files, you can't specify the physical file names.
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 SIZE1G
;
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 SIZE1G
AUTOEXTEND ON MAXSIZE10G
;
The following example creates a temporary tablespace named
temp01
.
CREATE TEMPORARY TABLESPACE
temp01
;
We recommend that you don't use smallfile tablespaces because you can't
resize smallfile tablespaces with RDS for Oracle. However, you can add a data file to a
smallfile tablespace. To determine whether a tablespace is bigfile or smallfile,
query DBA_TABLESPACES
as follows.
SELECT TABLESPACE_NAME, BIGFILE FROM DBA_TABLESPACE;
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_bf
to 200 MB.
ALTER TABLESPACE
users_bf
RESIZE200M
;
The following example adds an additional data file to a smallfile tablespace named
users_sf
.
ALTER TABLESPACE
users_sf
ADD DATAFILE SIZE100000M
AUTOEXTEND ON NEXT250m
MAXSIZEUNLIMITED
;
Setting the default tablespace
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 |
---|---|---|---|---|
|
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
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 |
---|---|---|---|---|
|
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 |
---|---|---|---|---|
|
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.
For more information, see Storing temporary data in an RDS for Oracle instance store.
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.
-
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 |
---|---|---|---|---|
|
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.
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:
-
Drop the current tempfiles in the temporary tablespace on the read replica.
-
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 |
---|---|---|---|---|
|
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.
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
optionThe
Timezone
option changes the time zone at the host level and affects all date columns and values such asSYSDATE
. For more information, see Oracle time zone. -
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 changeSYSDATE
. There are additional restrictions on setting the time zone listed in the Oracle documentation.
Note
You can also set the default time zone for Oracle Scheduler. For more information, see Setting the time zone for Oracle Scheduler jobs.
The alter_db_time_zone
procedure has the following parameters.
Parameter name | Data type | Default | Required | Description |
---|---|---|---|---|
|
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 +14: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. For information about upgrading time
zones, see Time zone considerations.
Working with Oracle external tables
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
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_PUMP_DIR directory. For information about creating directory objects, see Creating and dropping directories in the main data storage space. You can query the ALL_DIRECTORIES 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_FILE_TRANSFER
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_DIR1 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_PUMP_DIR directory.
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 and Amazon S3 integration.
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
-
12.2.0.1.ru-2020-04.rur-2020-04.r1 and higher Oracle Database 12c Release 2 (12.2) versions
-
12.1.0.2.v20 and higher Oracle Database 12c Release 1 (12.1) 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
Common parameters for the diagnostic utility package
You typically use the following parameters when managing AWR and ADDM with the
rdsadmin_diagnostic_util
package.
Parameter | Data type | Default | Required | Description |
---|---|---|---|---|
|
|
— |
Yes |
The ID of the beginning snapshot. |
|
|
— |
Yes |
The ID of the ending snapshot. |
|
|
|
No |
The directory to write the report or export file to. If
you specify a nondefault directory, the user that runs the
|
|
|
— |
No |
A string that can be used to distinguish between backups to indicate the purpose or
usage of backups, such as You can specify up to 30 characters. Valid characters are Tags don't need to be unique, so multiple backups can have the same tag. If you
don't specify a tag, RMAN assigns a default tag automatically using the format
The
|
|
|
|
No |
The format of the report. Valid values are
|
|
|
— |
No |
A valid database identifier (DBID) shown in the
|
You typically use the following parameters when managing ASH with the rdsadmin_diagnostic_util package.
Parameter | Data type | Default | Required | Description |
---|---|---|---|---|
|
|
— |
Yes |
The beginning time of the ASH analysis. |
|
|
— |
Yes |
The ending time of the ASH analysis. |
|
|
|
No |
The duration of the slots (in seconds) used in the "Top
Activity" section of the ASH report. If this parameter isn't
specified, the time interval between |
|
|
Null |
No |
The session ID. |
|
|
Null |
No |
The SQL ID. |
|
|
Null |
No |
The wait class name. |
|
|
Null |
No |
The service name hash. |
|
|
Null |
No |
The module name. |
|
|
Null |
No |
The action name. |
|
|
Null |
No |
The application-specific ID of the database session. |
|
|
Null |
No |
The PL/SQL entry point. |
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.
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
ashrpt
,
where begin_time
end_time
.txt
and
begin_time
use the format
end_time
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.
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.
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.
For more information about using database links with Oracle Data Pump, see Importing using Oracle Data Pump.
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
Enabling auditing for the SYS.AUD$ 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)
-
Oracle Database 12c Release 2 (12.2)
-
Oracle Database 12c Release 1 (12.1.0.2.v14) and later
The audit_all_sys_aud_table
procedure has the following
parameters.
Parameter name | Data type | Default | Required | Description |
---|---|---|---|---|
|
boolean |
true |
No |
Set to |
Note
In a single-tenant CDB, the following operations work, but no customer-visible mechanism can detect the current status of the operations. Auditing information isn't available from within the PDB. For more information, see Limitations of RDS for Oracle CDBs.
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)
Disabling auditing for the SYS.AUD$ 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)
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 |
---|---|---|---|---|
|
binary_integer |
|
No |
The object ID of the index. Typically, you can use the object ID from the ORA-08104 error text. |
|
binary_integer |
|
No |
Specify Specify |
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_INDEX_CLEAN function
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_REPAIR
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.
Important
Before attempting to repair corrupt blocks, review the DBMS_REPAIR
To skip corrupt blocks during index and table scans
-
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;
-
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;
-
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';
-
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';
-
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;
Resizing tablespaces, data files, and temp files
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 |
---|---|---|---|---|
|
number |
— |
Yes |
The identifier of the data file to resize. |
|
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 |
---|---|---|---|---|
|
number |
— |
Yes |
The identifier of the data file to resize. |
|
varchar2 |
— |
Yes |
The state of the autoextension feature. Specify
|
|
varchar2 |
— |
No |
The size of the next data file increment. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). |
|
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
|
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 |
---|---|---|---|---|
|
varchar2 |
— |
Yes |
The name of the temporary tablespace to resize. |
|
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 |
---|---|---|---|---|
|
number |
— |
Yes |
The identifier of the temp file to resize. |
|
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 |
---|---|---|---|---|
|
number |
— |
Yes |
The identifier of the temp file to resize. |
|
varchar2 |
— |
Yes |
The state of the autoextension feature. Specify
|
|
varchar2 |
— |
No |
The size of the next temp file increment. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). |
|
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
|
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.
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 |
---|---|---|---|---|
|
number |
Null |
No |
Modifies the default value for columns of the
|
|
binary_float |
Null |
No |
Modifies the default value for columns of the
|
|
binary_double |
Null |
No |
Modifies the default value for columns of the
|
|
char |
Null |
No |
Modifies the default value for columns of the
|
|
varchar2 |
Null |
No |
Modifies the default value for columns of the
|
|
nchar |
Null |
No |
Modifies the default value for columns of the
|
|
nvarchar2 |
Null |
No |
Modifies the default value for columns of the
|
|
date |
Null |
No |
Modifies the default value for columns of the
|
|
timestamp |
Null |
No |
Modifies the default value for columns of the
|
|
timestamp with time zone |
Null |
No |
Modifies the default value for columns of the |
|
blob |
Null |
No |
Modifies the default value for columns of the
|
|
clob |
Null |
No |
Modifies the default value for columns of the
|
|
nclob |
Null |
No |
Modifies the default value for columns of the
|
The following example changes the default redacted value to * 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.