Introduction
Amazon AWS has set values to certain Oracle Database parameters. You can refer to this blog post if want to know ahead of time what the values will be before you create the instance.
Parameters that have values
Oracle Database parameters that AWS RDS has set to values are listed here. The values come from parameter group default.oracle-ee-19:
| ParameterName | ParameterValue |
| archive_lag_target | 300 |
| audit_file_dest | /rdsdbdata/admin/{dbName}/adump |
| compatible | 19.0.0 |
| control_files | /rdsdbdata/db/{dbName}_{DBUniqueNameSuffix}/controlfile/control-01.ctl |
| db_block_checking | MEDIUM |
| db_create_file_dest | /rdsdbdata/db |
| dbfips_140 | FALSE |
| db_name | {dbName} |
| db_recovery_file_dest_size | 1073741824 |
| db_unique_name | {dbName}_{DBUniqueNameSuffix} |
| dg_broker_config_file1 | /rdsdbdata/config/dr1{dbName}.dat |
| dg_broker_config_file2 | /rdsdbdata/config/dr2{dbName}.dat |
| diagnostic_dest | /rdsdbdata/log |
| enable_pluggable_database | FALSE |
| filesystemio_options | setall |
| heat_map | OFF |
| job_queue_processes | 50 |
| local_listener | (address=(protocol=tcp)(host=)(port={EndPointPort})) |
| log_archive_dest_1 | location=”/rdsdbdata/db/{dbName}_{DBUniqueNameSuffix}/arch/redolog”, valid_for=(ALL_LOGFILES,ALL_ROLES) |
| log_archive_format | -%s-%t-%r.arc |
| max_string_size | STANDARD |
| memory_max_target | IF({DBInstanceClassHugePagesDefault}, 0, {DBInstanceClassMemory*3/4}) |
| memory_target | IF({DBInstanceClassHugePagesDefault}, 0, {DBInstanceClassMemory*3/4}) |
| open_cursors | 300 |
| pga_aggregate_target | IF({DBInstanceClassHugePagesDefault}, {DBInstanceClassMemory*1/8}, 0) |
| processes | LEAST({DBInstanceClassMemory/9868951}, 20000) |
| recyclebin | OFF |
| sga_target | IF({DBInstanceClassHugePagesDefault}, {DBInstanceClassMemory*3/4}, 0) |
| spfile | /rdsdbbin/oracle/dbs/spfile{dbName}.ora |
| standby_file_management | AUTO |
| undo_tablespace | UNDO_T1 |
| use_large_pages | {DBInstanceClassHugePagesDefault} |
Variables
Notice that some parameter values depend on variables.
The list of variables is:
DBInstanceClassHugePagesDefault
Depends on RDS size. RDS uses hugepages for RAM 16 GB and up.
DBInstanceClassMemory
The amount of RAM, in bytes, in the EC2 instance type that you select.
DBUniqueNameSuffix
Usually “_A”
EndPointPort
1521, for example
dbName
The value that you specify, or ORCL.
Actual memory-related parameter values
Here are some actual, observed memory-related parameter values:
| Host memory | |||||
| db.t3 instance size | small | medium | large | xlarge | 2xlarge |
| RAM | 2 GB | 4 GB | 8 GB | 16 GB | 32 GB |
| Parameter Name | |||||
| memory_max_target | 1328 MB | 2768 MB | 5670 MB | 0 | 0 |
| memory_target | 1328 MB | 2768 MB | 5670 MB | 0 | 0 |
| pga_aggregate_target | 0 | 0 | 0 | 2 GB | 4 GB |
| processes | 187 | 392 | 815 | 1663 | 3359 |
| sga_target | 0 | 0 | 0 | 12 GB | 23 GB |
| use_large_pages | FALSE | FALSE | FALSE | ONLY | ONLY |
Generally, you should not set these parameters to your own values. You should not create parameter groups that set these values. Doing so defeats the simplifying design of RDS.
RDS disables hugepages, and memory management is AMM, for RAM size 8 GB and smallerl however, RDS enforces hugepages, and memory management is ASMM, for RAM size 16 GB and up. If you modify the instance size, the memory-related parameters get changed.
Files
Control files
Control files are not multiplexed. Example:
/rdsdbdata/db/ORCL_A/controlfile/control-01.ctl
Data files
Tablespaces are bigfile. Example datafile:
/rdsdbdata/db/ORCL_A/datafile/o1_mf_users_hbl3j3yn_.dbf
Log files
Logs are not multiplexed. Example logfile:
/rdsdbdata/db/ORCL_A/onlinelog/o1_mf_1_hbl3kbbx_.log
Example tempfile:
/rdsdbdata/db/ORCL_A/datafile/o1_mf_temp_hfdtzrr6_.tmp
Operations
Because parameters db_create_file_dest and db_unique_name are set, tablespace creation is simplified. This command:
create tablespace TSQ;
results in creating a datafile such as:
/rdsdbdata/db/ORCL_A/datafile/o1_mf_tsq_hffymmyr_.dbf
Services
Out of the box, RDS boots with two services, namely db_name and db_unique_name. For example, ORCL and ORCL_A.
There are at least two ways to create additional services. Grid infrastructure tool srvctl is not be available to manage services in RDS.
SID
Your db_name is also your SID, and it is registered with the listener as a service. Check your SID:
select sys_context('userenv','instance_name') from dual;
or
select instance_name from v$instance;
ORCL
db_unique_name
Check your db_unique_name:
show parameter db_unique_name;
NAME TYPE VALUE -------------- ------ ------ db_unique_name string ORCL_A
You could override the default db_unique_name by creating a parameter group with your new value.
Managing service_names
You could create a new AWS RDS parameter group and set service_names there. If you do that, you may find over time that you have created an unmanageable number of parameter groups.
Running dbms_service
You can create and start a service using dbms_service.
begin
dbms_service.create_service(
service_name => 'ESB',
network_name => 'ESB'
);
end;
/
begin
dbms_service.start_service(
service_name => 'ESB'
);
end;
/
However, the service state would not survive a restart. To start the service automatically, you could try creating a database trigger.
create or replace trigger
system.start_svc
after startup on database
begin
dbms_service.start_service(
service_name => 'ESB'
);
exception when others then
if sqlcode != -44305
then
raise;
end if;
end;
/
However, you will get this error:
ORA-20900: RDS restricted DDL found: CREATE TRIGGER SYS.START_SVC
In case you set db_domain
Suppose, because of a tradition, you decide to set db_domain. You could create a new parameter group and set db_domain there.
Then you could apply the parameter group to your RDS instance. Now you have two connection options:
host:port/orcl
or
host:port/orcl_a.its.gvu.edu
If you try:
host:port/orcl.its.gvu.edu
you get:
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor
This means that all your connections need suffix “_a.its.gvu.edu”. If you head down this road, your environment is going to be cluttered with “_a.its.gvu.edu”. “_A” is for “_Appendix”, a vestigial organ that serves no current purpose, and can cause pain at unexpected times. If you don’t want so much clutter, do not set db_domain in RDS.
Services conclusion
Services were useful on on-prem deployments for managing or monitoring workload. Configuring services in AWS RDS is impractical.
I have these recommendations regarding services:
- Do not set db_domain
- Do not attempt to manage extra database services
- Connect using host:port/sid
AWS RDS is not very service-friendly.
Conclusion
Amazon AWS RDS sets certain Oracle Database initialization parameters. Memory and file-related parameters depend on RDS variables, which depend on variables. Memory-related variables are adjusted proportional to the amount of EC2 RAM. RDS sets parameters that control file creation locations. RDS does not support the service functionality that you could be accustomed to with on-prem.