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*Plus 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. |
|
|
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 |
|
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. |
|
|
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. |
|
|
Parameter groups If your DB instance is going to require specific database parameters, create a parameter group before you create the DB instance. |
|
|
Option groups If your DB instance requires specific database options, create an option group before you create the DB instance. |
|
|
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*Plus. |
|
|
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. |
|
|
Monitoring You can monitor an Oracle DB instance by using CloudWatch Amazon RDS metrics, events, and enhanced monitoring. |
|
|
Log files You can access the log files for your Oracle DB instance. |
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:
-
Amazon RDS method:
rdsadmin.rdsadmin_util.disconnectOracle method:
alter system disconnect sessionAmazon RDS method:
rdsadmin.rdsadmin_util.killOracle method:
alter system kill sessionCanceling a SQL statement in a session
Amazon RDS method:
rdsadmin.rdsadmin_util.cancelOracle method:
alter system cancel sqlEnabling and disabling restricted sessions
Amazon RDS method:
rdsadmin.rdsadmin_util.restricted_sessionOracle method:
alter system enable restricted sessionAmazon RDS method:
rdsadmin.rdsadmin_util.flush_shared_poolOracle method:
alter system flush shared_poolAmazon RDS method:
rdsadmin.rdsadmin_util.flush_buffer_cacheOracle method:
alter system flush buffer_cacheGranting SELECT or EXECUTE privileges to SYS objects
Amazon RDS method:
rdsadmin.rdsadmin_util.grant_sys_objectOracle method:
grantRevoking SELECT or EXECUTE privileges on SYS objects
Amazon RDS method:
rdsadmin.rdsadmin_util.revoke_sys_objectOracle method:
revokeManaging RDS_X$ views for Oracle DB instances
Amazon RDS method:
rdsadmin.rdsadmin_util.create_sys_x$_viewOracle method:
CREATE VIEWGranting privileges to non-master users
Amazon RDS method:
grantCreating custom functions to verify passwords
Amazon RDS method:
rdsadmin.rdsadmin_password_verify.create_verify_functionAmazon RDS method:
rdsadmin.rdsadmin_password_verify.create_passthrough_verify_fcnListing allowed system diagnostic events
Amazon RDS method:
rdsadmin.rdsadmin_util.list_allowed_system_eventsOracle method: —
Setting system diagnostic events
Amazon RDS method:
rdsadmin.rdsadmin_util.set_allowed_system_eventsOracle method:
ALTER SYSTEM SET EVENTS'set_event_clause'Listing system diagnostic events that are set
Amazon RDS method:
rdsadmin.rdsadmin_util.list_set_system_eventsOracle method:
ALTER SESSION SET EVENTS 'IMMEDIATE EVENTDUMP(SYSTEM)'Unsetting system diagnostic events
Amazon RDS method:
rdsadmin.rdsadmin_util.unset_system_eventOracle method:
ALTER SYSTEM SET EVENTS'unset_event_clause'
-
Changing the global name of a database
Amazon RDS method:
rdsadmin.rdsadmin_util.rename_global_nameOracle method:
alter database renameCreating and sizing tablespaces in RDS for Oracle
Amazon RDS method:
create tablespaceOracle method:
alter databaseSetting the default tablespace in RDS for Oracle
Amazon RDS method:
rdsadmin.rdsadmin_util.alter_default_tablespaceOracle method:
alter database default tablespaceSetting the default temporary tablespace in RDS for Oracle
Amazon RDS method:
rdsadmin.rdsadmin_util.alter_default_temp_tablespaceOracle method:
alter database default temporary tablespaceCreating a temporary tablespace on the instance store
Amazon RDS method:
rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspaceOracle method:
create temporary tablespaceAmazon RDS method:
rdsadmin.rdsadmin_util.checkpointOracle method:
alter system checkpointAmazon RDS method:
rdsadmin.rdsadmin_util.enable_distr_recoveryOracle method:
alter system enable distributed recoverySetting the database time zone
Amazon RDS method:
rdsadmin.rdsadmin_util.alter_db_time_zoneOracle method:
alter database set time_zoneGenerating performance reports with Automatic Workload Repository (AWR)
Amazon RDS method:
rdsadmin.rdsadmin_diagnostic_utilproceduresOracle method:
dbms_workload_repositorypackageSetting the default edition for a DB instance
Amazon RDS method:
rdsadmin.rdsadmin_util.alter_default_editionOracle method:
alter database default editionEnabling auditing for the SYS.AUD$ table
Amazon RDS method:
rdsadmin.rdsadmin_master_util.audit_all_sys_aud_tableOracle method:
auditDisabling auditing for the SYS.AUD$ table
Amazon RDS method:
rdsadmin.rdsadmin_master_util.noaudit_all_sys_aud_tableOracle method:
noauditCleaning up interrupted online index builds
Amazon RDS method:
rdsadmin.rdsadmin_dbms_repair.online_index_cleanOracle method:
dbms_repair.online_index_cleanAmazon RDS method: Several
rdsadmin.rdsadmin_dbms_repairproceduresOracle method:
dbms_repairpackageResizing tablespaces, data files, and tempfiles in RDS for Oracle
Amazon RDS method:
rdsadmin.rdsadmin_util.resize_temp_tablespace,rdsadmin.rdsadmin_util.resize_tempfile, orrdsadmin.rdsadmin_util.autoextend_tempfileproceduresrdsadmin.rdsadmin_util.resize_datafileorrdsadmin.rdsadmin_util.autoextend_datafileprocedureOracle method: —
Amazon RDS method:
EXEC rdsadmin.rdsadmin_util.purge_dba_recyclebinOracle method:
purge dba_recyclebinSetting the default displayed values for full redaction
Amazon RDS method:
EXEC rdsadmin.rdsadmin_util.dbms_redact_upd_full_rdct_valOracle method:
exec dbms_redact.UPDATE_FULL_REDACTION_VALUES
-
Amazon RDS method:
rdsadmin.rdsadmin_util.force_loggingOracle method:
alter database force loggingAmazon RDS method:
rdsadmin.rdsadmin_util.alter_supplemental_loggingOracle method:
alter database add supplemental logAmazon RDS method:
rdsadmin.rdsadmin_util.switch_logfileOracle method:
alter system switch logfileAmazon RDS method:
rdsadmin.rdsadmin_util.add_logfileAmazon RDS method:
rdsadmin.rdsadmin_util.drop_logfileAmazon RDS method:
rdsadmin.rdsadmin_util.set_configurationDownloading archived redo logs from Amazon S3
Amazon RDS method:
rdsadmin.rdsadmin_archive_log_download.download_log_with_seqnumAmazon RDS method:
rdsadmin.rdsadmin_archive_log_download.download_logs_in_seqnum_rangeAccessing online and archived redo logs
Amazon RDS method:
rdsadmin.rdsadmin_master_util.create_archivelog_dirAmazon RDS method:
rdsadmin.rdsadmin_master_util.create_onlinelog_dir
-
Validating database files in RDS for Oracle
Amazon RDS method:
rdsadmin_rman_util.procedureOracle method:
RMAN VALIDATEEnabling and disabling block change tracking
Amazon RDS method:
rdsadmin_rman_util.procedureOracle method:
ALTER DATABASECrosschecking archived redo logs
Amazon RDS method:
rdsadmin_rman_util.crosscheck_archivelogOracle method:
RMAN BACKUPBacking up archived redo log files
Amazon RDS method:
rdsadmin_rman_util.procedureOracle method:
RMAN BACKUPPerforming a full database backup
Amazon RDS method:
rdsadmin_rman_util.backup_database_fullOracle method:
RMAN BACKUPPerforming an incremental database backup
Amazon RDS method:
rdsadmin_rman_util.backup_database_incrementalOracle method:
RMAN BACKUPAmazon RDS method:
rdsadmin_rman_util.backup_database_tablespaceOracle method:
RMAN BACKUP
-
Amazon RDS method:
dbms_scheduler.set_attributeOracle method:
dbms_scheduler.set_attributeModifying AutoTask maintenance windows
Amazon RDS method:
dbms_scheduler.set_attributeOracle method:
dbms_scheduler.set_attributeSetting the time zone for Oracle Scheduler jobs
Amazon RDS method:
dbms_scheduler.set_scheduler_attributeOracle method:
dbms_scheduler.set_scheduler_attributeTurning off Oracle Scheduler jobs owned by SYS
Amazon RDS method:
rdsadmin.rdsadmin_dbms_scheduler.disableOracle method:
dbms_scheduler.disableTurning on Oracle Scheduler jobs owned by SYS
Amazon RDS method:
rdsadmin.rdsadmin_dbms_scheduler.enableOracle method:
dbms_scheduler.enableModifying the Oracle Scheduler repeat interval for jobs of CALENDAR type
Amazon RDS method:
rdsadmin.rdsadmin_dbms_scheduler.set_attributeOracle method:
dbms_scheduler.set_attributeModifying the Oracle Scheduler repeat interval for jobs of NAMED type
Amazon RDS method:
rdsadmin.rdsadmin_dbms_scheduler.set_attributeOracle method:
dbms_scheduler.set_attributeTurning off autocommit for Oracle Scheduler job creation
Amazon RDS method:
rdsadmin.rdsadmin_dbms_scheduler.set_no_commit_flagOracle method:
dbms_isched.set_no_commit_flag
-
Amazon RDS method:
rdsadmin.rdsadmin_adrci_util.list_adrci_incidentsOracle method: ADRCI command
show incidentAmazon RDS method:
rdsadmin.rdsadmin_adrci_util.list_adrci_problemOracle method: ADRCI command
show problemAmazon RDS method:
rdsadmin.rdsadmin_adrci_util.create_adrci_packageOracle method: ADRCI command
ips create packageAmazon RDS method:
rdsadmin.rdsadmin_adrci_util.show_adrci_tracefileOracle method: ADRCI command
show tracefile
-
Creating and dropping directories in the main data storage space
Amazon RDS method:
rdsadmin.rdsadmin_util.create_directoryOracle method:
CREATE DIRECTORYAmazon RDS method:
rdsadmin.rdsadmin_util.drop_directoryOracle method:
DROP DIRECTORYListing files in a DB instance directory
Amazon RDS method:
rdsadmin.rds_file_util.listdirOracle method: —
Reading files in a DB instance directory
Amazon RDS method:
rdsadmin.rds_file_util.read_text_fileOracle method: —
Amazon RDS method:
rdsadmin.rds_file_util.read_text_fileorrdsadmin.tracefile_listingOracle method:
opatchSetting parameters for advisor tasks
Amazon RDS method:
rdsadmin.rdsadmin_util.advisor_task_set_parameterOracle method: Various stored package procedures
Disabling AUTO_STATS_ADVISOR_TASK
Amazon RDS method:
rdsadmin.rdsadmin_util.advisor_task_dropOracle method: —
Re-enabling AUTO_STATS_ADVISOR_TASK
Amazon RDS method:
rdsadmin.rdsadmin_util.dbms_stats_initOracle method: —
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 and Performing database tasks with the Management Agent.
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;
Warning Javascript is disabled or is unavailable in your browser.
To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.