This is the second part of the blog post series about AWS Custom Engine Versions for Amazon RDS Custom for Oracle. In this part we take a look how we can connect to the Oracle database host and to the database itself by a tool like the Oracle SQL Developer. The blog post series:
- Part 1: Gives you an overview about the setup
- Part 2: About connectivity and what you get from AWS
- Part 3: My personal opinion about this service and some words about pricing
Connect as Schema User
The username and the password for the SQl*Plus connect are the ones you have added as Master username and Master password during CEV RDB instance creation process. The database endpoint is visible in the RDS instance details. Example when the client has network access to the database instance host – routing and security group are set. Details how to connect: Creating an Oracle DB instance and connecting to a database on an Oracle DB instance – Amazon Relational Database Service (amazonaws.com)
SQL*Plus Connect on Command Line as ADMIN
ADMIN has roles like dba and datapump_exp_full_database, take care about this password.
[ec2-user@ip-10-192-20-83 ~]$ sqlplus 'admin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rds-cev-oracle-1915-db01.ch1234567.eu-central-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=DB01)))' SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 16 19:43:03 2022 Version 21.6.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL>
Secrets Manager
During the RDS CEV instance creation process, there is no option to add any SSH key.The only one password what you can set is the Master Password for the database instance for a first connect. From our first blog post, we know that the EC2 instance name is db-Q6ELCPBQUYBZXZSLTVTFZWZJLM. The second part of the string can be used as filter in the Secrets Manager.
Two secrets are listed:
| do-not-delete-rds-custom-ssh-privatekey-db-<db-random-id>-<random-id> | For EC2 instance SSH access as user ec2-user |
| do-not-delete-rds-custom-db-<db-random-id>-<random-id> | For Oracle database connect as user RDSADMIN (high privileged) |
To get the values, just select the secret and press the Release secret value button. Example for the password for the Oracle RDSADMIN user – for the SSH user you will get the private SSH key.
SQL*Plus Connect on Command Line as RDSADMIN
[ec2-user@ip-10-192-20-83 ~]$ sqlplus 'rdsadmin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rds-cev-oracle-1915-db01.ch1234567.eu-central-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=DB01)))' SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 16 19:48:15 2022 Version 21.6.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Enter password: Last Successful login time: Mon May 16 2022 19:48:14 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL>
SSH Access to the Oracle host as ec2-user, change to rdsdb and login AS SYSDBA
[ec2-user@ip-10-192-20-83 ~]$ ssh -i .ssh/id_rsa_oracle_cev rds-cev-oracle-1915-db01.ch1234567.eu-central-1.rds.amazonaws.com Last login: Mon May 16 20:01:59 2022 from ip-10-192-20-83.eu-central-1.compute.internal AWS RDS Custom Default AMI [ec2-user@ip-10-192-21-156 ~]$ sudo su - rdsdb Last login: Mon May 16 19:39:15 UTC 2022 on pts/0 -bash-4.2$ . oraenv ORACLE_SID = [DB01] ? The Oracle base has been set to Resetting ORACLE_BASE to its previous value or ORACLE_HOME The Oracle base has been set to /rdsdbbin/oracle -bash-4.2$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 16 20:02:49 2022 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL>
What you get
Login and db* DBBASENV Output
[ec2-user@ip-10-192-21-156 ~]$ sudo su - rdsdb Last login: Mon May 16 20:41:43 UTC 2022 on pts/2 TYPE (Cluster|DG) : SID/PROCESS STATUS HOME [2022-05-16 21:03:58] ----------------------------------------------------------------------------- Dummy rdbms_ee : rdbms19 n/a /rdsdbbin/oracle DB-instance (N|N) : DB01 open /rdsdbbin/oracle Listener : L_DB01_001 up /rdsdbbin/oracle rdsdb@ip-10-192-21-156:~/ [rdbms19] DB01 ----------------------------------------------------------- 2022-05-16 21:04:02 DB_NAME : DB01 DB_UNIQUE_NAME : DB01_A DBID : 1735959514 DATAFILE_SIZE : 1G MEMORY_SIZE : 11.41G SGA / .4G PGA (11.41G memory_target) FRA_SIZE : 1G UPTIME : 2022-05-14 21:03 (2d 0h 0m) INSTANCE_STATUS: NORMAL OPEN_MODE : READ WRITE USERS/SESSIONS : Non-Oracle: 1/2 , Oracle: 1/2 DATABASE_ROLE : PRIMARY LOG_MODE : ARCHIVELOG CHARACTERSET : US7ASCII ORACLE_HOME : /rdsdbbin/oracle ORACLE_VERSION : 19.15.0.0.0 -------------------------------------------------------------------------------
Architecture
Even with 19c, you will get a single instance database, no Multitenant Option is enabled.
SQL> show con_name CON_NAME ------------------------------ DB01 SQL> CREATE PLUGGABLE DATABASE PDB01 ADMIN USER pdbdmin IDENTIFIED BY kermitthefrog; CREATE PLUGGABLE DATABASE PDB01 ADMIN USER t1 IDENTIFIED BY t1 * ERROR at line 1: ORA-65090: operation only allowed in a container database
Characterset
The characterset is US7ASCII – I don’t see or found a way to change it during CEV instance creation process.
SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; VALUE$ -------------------------------------------------------------------------------- US7ASCII
Redo Log Files
Four redo log groups, each with one file only – no mirroring.
SQL> SELECT group#, member FROM v$logfile ORDER BY 1; GROUP# MEMBER ---------- ------------------------------------------------------------ 1 /rdsdbdata/db/DB01_A/onlinelog/o1_mf_1_k80674k8_.log 2 /rdsdbdata/db/DB01_A/onlinelog/o1_mf_2_k80674vb_.log 3 /rdsdbdata/db/DB01_A/onlinelog/o1_mf_3_k806755x_.log 4 /rdsdbdata/db/DB01_A/onlinelog/o1_mf_4_k80675vr_.log
Control File
One file only.
SQL> SELECT name FROM v$controlfile; NAME -------------------------------------------------------------------------------- /rdsdbdata/db/DB01_A/controlfile/control-01.ctl
Block Devices
File System
Oracle binaries are on mountpoint /rdsdbbin, the controlfile, datafile, ADR logs etc. on /rdsdbdata.
-bash-4.2$ df -m Filesystem 1M-blocks Used Available Use% Mounted on devtmpfs 7759 0 7759 0% /dev tmpfs 15566 7008 8558 46% /dev/shm tmpfs 7783 1 7783 1% /run tmpfs 7783 0 7783 0% /sys/fs/cgroup /dev/nvme0n1p1 9950 4020 5812 41% / /dev/nvme3n1 25071 13579 10196 58% /rdsdbbin /dev/mapper/dbdata01-lvdbdata01 40170 2580 37164 7% /rdsdbdata tmpfs 1557 0 1557 0% /run/user/61001 tmpfs 1557 0 1557 0% /run/user/61005
File Owner
Owner of the Oracle related files is OS user rdsdb.
-bash-4.2$ ls -la total 52 drwxr-xr-x 10 rdsdb database 4096 May 14 20:53 . dr-xr-xr-x 20 root root 4096 May 14 20:51 .. drwxr-xr-x 3 rdsdb database 4096 May 14 18:33 admin drwxr-x--- 3 rdsdb database 4096 May 14 18:46 audit drwxr-xr-x 3 rdsdb database 4096 May 14 18:54 cfgtoollogs drwxr-xr-x 2 rdsdb database 4096 May 14 18:26 checkpoints drwxrwxr-x 23 rdsdb database 4096 May 14 18:26 diag drwxr-xr-x 2 rdsdb database 16384 May 14 18:22 lost+found drwxr-xr-x 7 rdsdb database 4096 May 15 06:01 oraInventory lrwxrwxrwx 1 rdsdb database 34 May 14 18:26 oracle -> /rdsdbbin/oracle.19.custom.r1.EE.1 drwxr-xr-x 71 rdsdb database 4096 May 14 20:57 oracle.19.custom.r1.EE.1 lrwxrwxrwx 1 root root 15 May 14 20:53 scripts -> /etc/rds/dbbin/
Parameters
The database parameters are based on the AWS default.custom-oracle-ee-19 parameters group and cannot be changed in the AWS console.
Backup
AWS uses the general RDS backup mechanism with disk snapshots. Visible in the database alertlog. There are no RMAN information about database backups in the controlfile.
2022-05-16T21:22:36.923471+00:00 ALTER DATABASE BACKUP CONTROLFILE TO '/rdsdbdata/tmp/backup_control_file' REUSE Completed: ALTER DATABASE BACKUP CONTROLFILE TO '/rdsdbdata/tmp/backup_control_file' REUSE 2022-05-16T21:22:37.082329+00:00 ALTER DATABASE BEGIN BACKUP Completed: ALTER DATABASE BEGIN BACKUP 2022-05-16T21:22:38.030903+00:00 ALTER DATABASE END BACKUP Completed: ALTER DATABASE END BACKUP
RMAN> list backup of database; specification does not match any backup in the repository RMAN> list backup of controlfile; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 8.55M DISK 00:00:01 14-MAY-22 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20220514T211337 Piece Name: /rdsdbbin/oracle/dbs/c-1735959514-20220514-00 Control File Included: Ckp SCN: 746724 Ckp time: 14-MAY-22
OS Release
-bash-4.2$ cat /etc/oracle-release Oracle Linux Server release 7.9
Oracle Database Security Assessment
I did a quick run with dbsat after the initial setup. Here is the overview of the findings. There are no high risks. If you are interested in the details, the html file is on my github repository https://github.com/martinberger-ch/aws-cev-oracle.git.
Summary Part 2/3
To connect to a privileged database user like ADMIN which was defined during the CEV instance creation process is very easy. For all other connects you have to consider the Secret Manager and release password or SSH key. We have 2022, and AWS creates still single instances. C’mon guys, we want to have multi-tenancy in place. To create a snapshot instead an RMAN backup is the well know method for RDS. I am still searching where to change the initial NLS characterset. According the AWS EBS documentation, storage is replicated inside the Availability Zone to prevent data loss. The storage is encrypted by an user managed key. Good new from the database security front, DBSAT has no high risks found.