Amazon Relational Database Service
User Guide (API Version 2013-09-09)
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.Did this page help you?  Yes | No |  Tell us about it...

Appendix: Options for Oracle DB Engine

This appendix describes options, or additional features, that are available for Amazon RDS instances running the Oracle database engine. To enable these options, you can add them to an option group, and then associate the option group with your DB instance. Note that some options are permanent and persistent; permanent means that an option cannot be removed from an option group and persistent means that once an option group with this option is assigned to a DB instance, the option group cannot be removed from the DB instance. For more information about working with options, see Things You Should Know About Option Groups.

The following options are currently supported for Oracle:

Note

Some of these options may require additional memory in order to run on your DB instance. For example, Oracle Enterprise Manager Database Control uses about 300 MB of RAM; if you enable this option for a small DB instance, you might encounter performance problems due to memory constraints.

Before you enable these options, please consider whether your DB instance has enough available memory. You can adjust the Oracle parameters so that the database requires less RAM; alternatively, you can scale up to a larger DB instance.

Oracle Enterprise Manager Database Control

Oracle Enterprise Manager (OEM) Database Control is a web-based interface for Oracle database administration. Note that OEM cannot be run on DB instances that use the db.t1.micro or db.t1.small instance classes.

The default port number for OEM Database Control is 1158; you can accept this port number, or choose a different one, when you enable the OEM Database Control option for your DB instance. You can then go to your web browser and begin using OEM Database Control.

The following example shows how to access OEM Database Control from your web browser. Suppose that the endpoint for your Amazon RDS instance is mydb.f9rbfa893tft.us-east-1.rds.amazonaws.com, and that you specified port 1158. The URL to access OEM Database Control would be:

https://mydb.f9rbfa893tft.us-east-1.rds.amazonaws.com:1158/em

The OEM Database Control login window appears, prompting you for a username and password. Enter the master username and master password for your DB instance. You can now manage your database using the OEM Database Control console.

Oracle XML DB

Oracle XML DB adds native XML support to your DB instance. With the Amazon RDS XMLDB option, DB instances running the Oracle engine can store and retrieve structured or unstructured XML, in addition to relational data.

After you apply the XMLDB option to your DB instance, you have full access to the Oracle XML DB repository; no post-installation tasks are required.

Note

The Amazon RDS XMLDB option does not provide support for the Oracle XML DB Protocol Server.

Oracle Application Express (APEX)

Oracle Application Express (APEX) is a development and runtime environment for web-based applications. Using APEX, developers can build applications entirely within the web browser, and customers can run these applications without installing any additional software.

Note

Amazon RDS supports Oracle APEX version 4.1.1.

Oracle APEX consists of two main components:

  • A repository that stores the metadata for APEX applications and components. The repository consists of tables, indexes, and other objects that are installed in your Amazon RDS DB instance.

  • A listener that manages HTTP communications with APEX clients. The listener accepts incoming connections from web browsers and forwards them to the Amazon RDS instance for processing, and then sends results from the repository back to the browsers.

When you add the APEX option for your Oracle DB instance, Amazon RDS installs the APEX repository only. You must install the listener on a separate host — an Amazon EC2 instance, an on-premises server at your company, or your desktop computer.

The following sections explain how to configure the Oracle APEX repository and listener for use with Amazon RDS.

Repository Configuration

To configure the APEX repository

  1. Create a new Amazon RDS instance running the Oracle engine, or choose an existing instance. The version number for the Oracle engine must be 11.2.0.2.v4 or newer.

  2. Create a new option group, or select an existing option group. Apply the following options to this option group:

    • XMLDB

    • APEX

    • APEX_DEV

    (If you only want to deploy the APEX runtime environment, you can remove the APEX_DEV option at a later time. This option must be present during this configuration procedure, however.)

  3. Apply the option group to your DB instance. Amazon RDS will install the repository components in your DB instance; this process takes a few minutes to complete.

  4. After the option group is successfully applied, you will need to change the password for the APEX_PUBLIC_USER database account and unlock it. You can do this using the Oracle SQL*Plus command line utility: Connect to your DB instance as the master user and issue the following commands:

    alter user APEX_PUBLIC_USER identified by newpass;
    alter user APEX_PUBLIC_USER account unlock;
    

    Replace newpass with a password of your choice.

Listener Configuration

You are now ready to configure a listener for use with Oracle APEX. You can use either of these products for this purpose:

  • Oracle Application Express Listener

  • Oracle HTTP Server and mod_plsql

Note

Amazon RDS does not support the Oracle XML DB HTTP server with the embedded PL/SQL gateway; you cannot use this as an APEX listener. This restriction is in line with Oracle's recommendation against using the embedded PL/SQL gateway for applications that run on the Internet.

The listener must be installed on a separate host, such as an Amazon EC2 instance or a server that you own. You also must have the following prerequisite software installed on the separate host acting as the listener:

  • Java Runtime Environment (JRE) — Oracle APEX Listener is a Java application.

  • Oracle Net Services, to enable the APEX listener to connect to your Amazon RDS instance.

  • SQL*Plus, to perform administrative tasks from the command line.

The following procedure shows how to configure the Oracle Application Express Listener product. We will assume that the name of your APEX host is myapexhost.example.com, and that this host is running Linux.

To configure an APEX listener

  1. Log in to myapexhost.example.com as root.

  2. We recommend that you create a nonprivileged OS user to own the APEX listener installation. The following command will create a new user named apexuser:

    useradd -d /home/apexuser apexuser
                   

    Now assign a password to apexuser:

    passwd apexuser
                   
  3. Log in to myapexhost.example.com as apexuser, and download the APEX and APEX Listener installation files from Oracle:

  4. Open the APEX file:

    unzip apex_4.1.1.zip 
    

  5. Create a new directory and open the APEX Listener file:

    mkdir /home/apexuser/apexlistener
    cd /home/apexuser/apexlistener 
    unzip ../apex_listener.1.1.3.243.11.40.zip 
    

  6. While you are still in the apexlistener directory, run the APEX Listener program:

    java -Dapex.home=./apex -Dapex.images=/home/apexuser/apex/images -Dapex.erase -jar ./apex.war
    

    The program will prompt you for the following:

    • The APEX Listener Administrator username — the default is adminlistener

    • A password for the APEX Listener Administrator.

    • The APEX Listener Manager username — the default is managerlistener

    • A password for the APEX Listener Administrator.

    The program will print a URL that you will need in order to complete the configuration:

    INFO: Please complete configuration at: http://localhost:8080/apex/listenerConfigure
    Database is not yet configured
    

    Leave the APEX Listener running. It needs to continue running in order for you to use Oracle Application Express. (When you have finished this configuration procedure, you can run the listener in the background.)

  7. From your web browser, go to the URL provided by the APEX Listener program. The Oracle Application Express Listener administration window appears. Enter the following information:

    • UsernameAPEX_PUBLIC_USER

    • Password — the password for APEX_PUBLIC_USER. (This is the password that you specified earlier, when you configured the APEX repository.)

    • Connection Type— Basic

    • Hostname— the endpoint of your Amazon RDS instance, such as mydb.f9rbfa893tft.us-east-1.rds.amazonaws.com

    • Port— 1521

    • SID— the name of the database on your Amazon RDS instance, such as mydb

    Click Apply button. The APEX administration window appears.

  8. You will need to set a password for the APEX admin user. To do this, use SQL*Plus to connect to your DB instance as the master user and issue the following commands:

    grant APEX_ADMINISTRATOR_ROLE to master;
    @/home/apexuser/apex/apxchpwd.sql
    

    Replace master with your master user name. Enter a new admin password when the apxchpwd.sql script prompts you.

  9. Return to the APEX administration window in your browser and click Administration. Next, click Application Express Internal Administration. You will be prompted for APEX internal administration credentials. Enter the following information:

    • Usernameadmin

    • Password— the password you set using the apxchpwd.sql script.

    Click Login. You will be required to set a new password for the admin user.

Oracle Application Express is now ready for use.

Oracle Native Network Encryption

Amazon RDS supports Oracle native network encryption, a feature of the Oracle Advanced Security option available in Oracle Enterprise Edition. With native network encryption, you can encrypt data as it moves to and from a DB instance.

To use Oracle native network encryption with a DB instance, you add the NATIVE_NETWORK_ENCRYPTION option to an option group and associate that option group with the DB instance. You should first determine if the DB instance is associated with an option group that has the NATIVE_NETWORK_ENCRYPTION option. To view the option group that a DB instance is associated, you can use the RDS console, the rds-describe-db-instance CLI command, or the API action DescribeDBInstances. Amazon RDS supports Oracle native network encryption for any DB instance class larger than db.t1.micro.

A detailed discussion of Oracle native network encryption is beyond the scope of this guide, but you should understand the strengths and weaknesses of each algorithm and key before you decide on a solution for your deployment. Note that non-default TDE encryption algorithms only work with Oracle version 11.202.v7 and later. For information about the algorithms and keys that are available through Oracle Advanced Security, see Oracle Advanced Security in the Oracle documentation. For more information about AWS security, see the AWS Security Center.

The process for using Oracle native network encryption with Amazon RDS is as follows:

  1. If the DB instance is not associated with an option group that has the network encryption option (NATIVE_NETWORK_ENCRYPTION), you must either modify an existing option group to add the NATIVE_NETWORK_ENCRYPTION option or create a new option group and add the NATIVE_NETWORK_ENCRYPTION option to it. For information about creating or modifying an option group, see Working with Option Groups. For information about adding an option to an option group, see Adding an Option to an Option Group.

  2. Specify the NATIVE_NETWORK_ENCRYPTION option settings for the option group. For information about modifying option settings, see Modifying an Option Setting.

    These settings include:

    • SQLNET.ENCRYPTION_SERVER–Specifies the encryption behavior when a client, or a server acting as a client, connects to the DB instance. Allowable values are Accepted, Rejected, Requested (the default), and Required. Requested indicates that the DB instance does not require traffic from the client to be encrypted.

    • SQLNET.CRYPTO_CHECKSUM_SERVER–Specifies the data integrity behavior when a client, or a server acting as a client, connects to the DB instance. Allowable values are Accepted, Rejected, Requested (the default), and Required. Requested indicates that the DB instance does not require the client to perform a checksum.

    • SQLNET.ENCRYPTION_TYPES_SERVER–Specifies a list of encryption algorithms used by the DB instance. The DB instance will use each algorithm, in order, to attempt to decrypt the client input until an algorithm succeeds or until the end of the list is reached. Amazon RDS uses the following default list from Oracle. You can change the order or limit the algorithms that the DB instance will accept.

      1. RC4_256: RSA RC4 (256-bit key size)

      2. AES256: AES (256-bit key size)

      3. AES192: AES (192-bit key size)

      4. 3DES168: 3-key Triple-DES (168-bit effective key size)

      5. RC4_128: RSA RC4 (128-bit key size)

      6. AES128: AES (128-bit key size)

      7. 3DES112: 2-key Triple-DES (112-bit effective key size)

      8. RC4_56: RSA RC4 (56-bit key size)

      9. DES: Standard DES (56-bit key size)

      10. RC4_40: RSA RC4 (40-bit key size)

      11. DES40: DES40 (40-bit key size)

    • SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER–Specifies the checksum algorithm. The default is sha-1, but md5 is also supported.

  3. List the options in the option group to ensure that you have added the NATIVE_NETWORK_ENCRYPTION option and specified the correct settings. You can view the options in an option group using the RDS console, the CLI command rds-describe-option-group-options, or the Amazon RDS API action DescribeOptionGroupOptions.

  4. Associate the DB instance with the option group that has the NATIVE_NETWORK_ENCRYPTION option. For information about associating a DB instance with an option group, see Modifying a DB Instance Running the Oracle Database Engine.

With Oracle native network encryption, you can also specify network encryption on the client side. On the client (the computer used to connect to the DB instance), you can use the sqlnet.ora file to specify the following client settings: SQLNET.CRYPTO_CHECKSUM_CLIENT , SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT, SQLNET.ENCRYPTION_CLIENT,  and SQLNET.ENCRYPTION_TYPES_CLIENT. For information, see Configuring Network Data Encryption and Integrity for Oracle Servers and Clients in the Oracle documentation.

Sometimes, the DB instance will reject a connection request from an application, for example, if there is a mismatch between the encryption algorithms on the client and on the server.

To test Oracle native network encryption , add the following lines to the sqlnet.ora file on the client:

DIAG_ADR_ENABLED=off   
TRACE_DIRECTORY_CLIENT=/tmp   
TRACE_FILE_CLIENT=nettrace   
TRACE_LEVEL_CLIENT=16    
            

These lines generate a trace file on the client called /tmp/nettrace* when the connection is attempted. The trace file contains information on the connection. For more information about connection-related issues when you are using Oracle Native Network Encryption, see About Negotiating Encryption and Integrity in the Oracle documentation.

Oracle Transparent Data Encryption (TDE)

Amazon RDS supports Oracle Transparent Data Encryption (TDE), a feature of the Oracle Advanced Security option available in Oracle Enterprise Edition. This feature automatically encrypts data before it is written to storage and automatically decrypts data when the data is read from storage.

Note

The TDE option is a permanent option that cannot be removed from an option group, and that option group cannot be removed from a DB instance once it is associated with a DB instance. You cannot disable TDE from a DB instance once that instance is associated with an option group with the Oracle TDE option.

Oracle Transparent Data Encryption is used in scenarios where you need to encrypt sensitive data in case data files and backups are obtained by a third party or when you need to address security-related regulatory compliance issues.

A detailed explanation about Oracle Transparent Data Encryption is beyond the scope of this guide. For information about using Oracle Transparent Data Encryption, see Securing Stored Data Using Transparent Data Encryption. For more information about Oracle Advanced Security, see Oracle Advanced Security in the Oracle documentation. For more information on AWS security, see the AWS Security Center.

Oracle Transparent Data Encryption supports two encryption modes: TDE tablespace encryption and TDE column encryption. TDE tablespace encryption is used to encrypt entire application tables. TDE column encryption is used to encrypt individual data elements that contain sensitive data. You can also apply a hybrid encryption solution that uses both TDE tablespace and column encryption. For information about TDE best practices, see Oracle Advanced Security Transparent Data Encryption Best Practices.

Once the option is enabled, you can check the status of the Oracle Wallet by using the following command:

SELECT * FROM v$encryption_wallet;            
        

To create an encrypted tablespace, use the following command:

CREATE TABLESPACE encrypt_ts ENCRYPTION DEFAULT STORAGE (ENCRYPT);                
            

To specify the encryption algorithm (for versions 11.2.0.2.v7 or later), use the following command:

CREATE TABLESPACE encrypt_ts ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);            
        

Note that the previous commands for encrypting a tablespace are the same as the commands you would use with an Oracle installation not on Amazon RDS, and the ALTER TABLE syntax to encrypt a column is also the same as the commands you would use for an Oracle installation not on Amazon RDS.

You should determine if your DB instance is associated with an option group that has the TDE option. To view the option group that a DB instance is associated with, you can use the RDS console, the rds-describe-db-instance CLI command, or the API action DescribeDBInstances.

Amazon RDS manages the Oracle Wallet and TDE master key for the DB instance. To comply with several security standards, Amazon RDS is working to implement automatic periodic master key rotation.

The process for using Oracle Transparent Data Encryption (TDE) with Amazon RDS is as follows:

  1. If the DB instance is not associated with an option group that has the TDE option enabled, you must either create an option group and add the TDE option or modify the associated option group to add the TDE option. For information about creating or modifying an option group, see Working with Option Groups. For information about adding an option to an option group, see Adding an Option to an Option Group.

  2. Associate the DB instance with the option group with the TDE option. For information about associating a DB instance with an option group, see Modifying a DB Instance Running the Oracle Database Engine.

If you no longer want to use the TDE option with a DB instance, you must decrypt all your data on the DB instance, copy the data to a new DB instance that is not associated with an option group with TDE enabled, and then delete the original instance. You can rename the new instance to be the same name as the previous DB instance if you prefer.

Using TDE with Data Pump

You can use Oracle Data Pump to import or export encrypted dump files; however. Amazon RDS supports the password encryption mode (ENCRYPTION_MODE=PASSWORD) for Oracle Data Pump. Amazon RDS does not support transparent encryption mode (ENCRYPTION_MODE=TRANSPARENT) for Oracle Data Pump. For more information about using Oracle Data Pump with Amazon RDS, see Oracle Data Pump.

Oracle Statspack

The Oracle Statspack option (STATSPACK) installs and enables the Oracle Statspack performance statistics feature. Oracle Statspack is a collection of SQL, PL/SQL, and SQL*Plus scripts that collect, store, and display performance data. For information about using Oracle Statspack, see Oracle Statspack in the Oracle documentation.

Note

Oracle Statspack is no longer supported by Oracle and has been replaced by the more advanced Automatic Workload Repository (AWR). AWR is available only for Oracle Enterprise Edition customers who have purchased the Diagnostics Pack. Oracle Statspack can be used with any Oracle DB engine on Amazon RDS.

The following steps show you how to work with Oracle Statspack on Amazon RDS:

  1. Add the Statspack option to an option group and then associate that option group with your DB instance. Amazon RDS installs the Statspack scripts on the DB instance and then sets up the PERFSTAT user account, the account you use to run the Statspack scripts.

    If you have an existing DB instance that has the PERFSTAT account already created and you want to use Oracle Statspack with it, you must drop the PERFSTAT account before adding the Statspack option to the option group associated with your DB instance. If you attempt to add the Statspack option to an option group associated with a DB instance that already has the PERFSTAT account created, you will get an error and the RDS event RDS-Event-0058 will be generated.

    You can drop the PERFSTAT account by running the following command:

    DROP USER perfstat CASCADE;
  2. After Amazon RDS has installed Statspack on your DB instance, you must log in to the DB instance using your master user name and master password. You must then reset the PERFSTAT password from the randomly generated value Amazon RDS created when Statspack was installed. After you have reset the PERFSTAT password, you can log in using the PERFSTAT user account and run the Statspack scripts.

    Use the following command to reset the password:  

    ALTER USER perfstat IDENTIFIED BY <new_password> ACCOUNT UNLOCK;
  3. After you have logged on using the PERFSTAT account, you can either manually create a Statspack snapshot or create a job that will take a Statspack snapshot after a given time interval. For example, the following job creates a Statspack snapshot every hour:  

    variable jn number;
    execute dbms_job.submit(:jn, 'statspack.snap;',sysdate,'trunc(SYSDATE+1/24,''HH24'')');
    commit;
                        
  4. Once you have created at least two Statspack snapshots, you can view them using the following query:  

    select snap_id, snap_time from stats$snapshot order by 1;
  5. To create a Statspack report, you choose two snapshots to analyze and run the following Amazon RDS command:

    exec RDSADMIN.RDS_RUN_SPREPORT(<begin snap>,<end snap>);

    For example, the following Amazon RDS command would create a report based on the interval between Statspack snapshots 1 and 52:

    exec RDSADMIN.RDS_RUN_SPREPORT(1,52);

The file name of the Statspack report that is generated includes the number of the two Statspack snapshots used. For example, a report file created using Statspack snapshots 1 and 52 would be named ORCL_spreport_1_52.lst. You can download the Statspack report by selecting the report in the Log section of the RDS console and clicking Download or you can use the trace file procedures explained in Working with Oracle Trace Files.

If an error occurs when producing the report, an error file is created using the same naming conventions but with an extension of .err. For example, if an error occurred while creating a report using Statspack snapshots 1 and 52, the report file would be named ORCL_spreport_1_52.err. You can download the error report by selecting the report in the Log section of the RDS console and clicking Download or use the trace file procedures explained in Working with Oracle Trace Files.

Oracle Statspack does some basic checking before running the report, so you could also see error messages displayed at the command prompt. For example, if you attempt to generate a report based on an invalid range, such as the beginning Statspack snapshot value is larger than the ending Statspack snapshot value, the error message will be displayed at the command prompt and no error file is created.

exec RDSADMIN.RDS_RUN_SPREPORT(2,1);
*
ERROR at line 1:
ORA-20000: Invalid snapshot IDs. Find valid ones in perfstat.stats$snapshot.            
        

If you use an invalid number for one of the Statspack snapshots, the error message will also be displayed at the command prompt. For example, if you have 20 Statspack snapshots but request that a report be run using Statspack snapshots 1 and 50, the command prompt will display an error.

exec RDSADMIN.RDS_RUN_SPREPORT(1,50);
*
ERROR at line 1:
ORA-20000: Could not find both snapshot IDs            
        

For more information about how to use Oracle Statspack, including information on adjusting the amount of data captured by adjusting the snapshot level, go to the Oracle Statspack documentation page.

To remove Oracle Statspack files, use the following command:

execute statspack.purge(<begin snap>, <end snap>);    

Oracle Time Zone

The Timezone option lets you change the system time zone used by Oracle databases in a DB instance. You might need to change the time zone for a DB instance if you need to have time compatibility with an on-premises environment or a legacy application. This option changes the time zone at the host level and impacts all date columns and values including SYSDATE and SYSTIMESTAMP. This option can only be applied once to a DB instance. You should take a DB snapshot of your DB instance before applying this option to a DB instance so that you can recover the instance if the time zone option is set incorrectly.

Note

Applying the Timezone option to option groups used by existing DB instances could cause problems with tables that use system date to add dates or time, so you should analyze your data to determine what impact a time zone change will have. We strongly urge you to test setting this option on a test DB instance before setting it on your production instances.

The Timezone option is a permanent and persistent option that cannot be removed from an option group once it is added and the option group cannot be disassociated from a DB instance. This option can be applied immediately by selecting Apply Immediately or it can be applied at the next maintenance window.

There are three ways that you can add the Timezone option to an option group. You can use the Amazon RDS console, the rds-add-option-to-option-group Amazon RDS CLI command, or the ModifyOptionGroup API action.

The following example uses the Amazon RDS CLI command rds-add-option-to-option-group to add the Timezone option and the TIME_ZONE option setting to an option group called myoptiongroup. The time zone is set to Asia/Japan.

rds-add-option-to-option-group myoptiongroup --option-name Timezone --settings "TIME_ZONE=Asia/Tokyo"
        

The Timezone option differs from the rdsadmin_util.alter_db_time_zone command. The rdsadmin_util.alter_db_time_zone command only changes the time zone for certain data types, while the Timezone option changes the time zone at the host level and impacts all date columns and values such as SYSDATE.

The following values can be used for the TIME_ZONE option setting:

Africa/Cairo, Africa/Casablanca, Africa/Harare, Africa/Monrovia, Africa/Nairobi, Africa/Tripoli, Africa/Windhoek, America/Araguaina, America/Asuncion, America/Bogota, America/Caracas, America/Chihuahua, America/Cuiaba, America/Denver, America/Fortaleza, America/Guatemala, America/Halifax, America/Manaus, America/Matamoros, America/Monterrey, America/Montevideo, America/Phoenix, America/Santiago, America/Tijuana, Asia/Amman, Asia/Ashgabat, Asia/Baghdad, Asia/Baku, Asia/Bangkok, Asia/Beirut, Asia/Calcutta, Asia/Damascus, Asia/Dhaka, Asia/Irkutsk, Asia/Jerusalem, Asia/Kabul, Asia/Karachi, Asia/Kathmandu, Asia/Krasnoyarsk, Asia/Magadan, Asia/Muscat, Asia/Novosibirsk, Asia/Riyadh, Asia/Seoul, Asia/Shanghai, Asia/Singapore, Asia/Taipei, Asia/Tehran, Asia/Tokyo, Asia/Ulaanbaatar, Asia/Vladivostok, Asia/Yakutsk, Asia/Yerevan, Atlantic/Azores, Australia/Adelaide, Australia/Brisbane, Australia/Darwin, Australia/Hobart, Australia/Perth, Australia/Sydney, Canada/Newfoundland, Canada/Saskatchewan, Europe/Amsterdam, Europe/Athens, Europe/Dublin, Europe/Helsinki, Europe/Istanbul, Europe/Kaliningrad, Europe/Moscow, Europe/Paris, Europe/Prague, Europe/Sarajevo, Pacific/Auckland, Pacific/Fiji, Pacific/Guam, Pacific/Honolulu, Pacific/Samoa, US/Alaska, US/Eastern, US/East-Indiana, US/Pacific, UTC.