AWS (Amazon Web Services) provides several services and tools to deploy enterprise solutions at a very fast and reliable manner, and of course, with an advantageous cost. Many solutions are being migrated to the cloud right now all around the globe. And in this case, it was our turn to migrate a complex and advanced Oracle schema to the most used cloud platform, AWS RDS databases (Relational Database Service).
Many enterprises prefer to use Amazon RDS for Oracle, because RDS automates time-consuming database administration tasks and leaves engineers to use precious time for development purposes. Some of those tasks are provisioning, backups, monitoring, and hardware scaling, simplifying infrastructure, and software maintenance.
So, I had in my hands a set of well-structured SQL files, that included from schema creation to tables, users, synonyms, grant permissions, etc. and, what’s more, I had the famous open-source tool Flyway, that allows us to migrate and version our database in seconds.
Flyway has seven main operations:
- Migrate
- Clean
- Info
- Validate
- Undo
- Baseline
- Repair
Hence, migration files can be written in SQL, as in our case.
As a result, I had the SQL files and the migration tool prepared.
The first step was to configure Flyway to use RDS. Once I had Flyway installed into a Centos server (that it had been already connected to the cloud), I prepared the Flyway configuration file, where we determine the URL string connection and some other parameters:
flyway.url → URL string to RDS database
flyway.user → RDS user flyway.password → RDS password flyway.locations → location where sql files are flyway.sqlMigrationPrefix=SUITE- → file prefix name flyway.baselineVersion=00000 → baseline version, 00000 preferable flyway.schemas=IWAYVERSION → main schema name
Thus now it was time to try to migrate the schema into RDS AWS.
During the migration process, some SQL statements resulted in error, and that happened because RDS is not exactly the same as Oracle, so some commands must be reformulated into commands that RDS allows. That is because RDS is restrictive in order to maintain its automated processes consistent, like backups, failover, patch, etc.
Consequently, I needed to translate many SQL common statements into RDS compatible.
I am going to summarize some of them.
SYSDBA privileges
First of all, statements like GRANT SYSDBA to USER returns errors like ORA-01031: insufficient privileges. But why is that?
Currently, it is not possible to assign SYSDBA privileges to any user because it can interfere with RDS automatic tasks as I commented before.
The Amazon documentation for Oracle on Amazon RDS says you have to use a master user which has the DBA privilege and other users like SYS and SYSTEM are locked by default and cannot be used. In response, AWS had provided a list of SYS commands to run into RDS meant as a workaround.
So, to avoid that, all migration was made with the user admin provided by Amazon.
ALTER SYSTEM
Commands like ALTER SYSTEM SET xxxx, returned error ORA-01031: insufficient privileges, too.
In RDS we can’t invoke ALTER SYSTEM statements, but instead, we can alter parameters using parameter groups. AWS created internal procedures so we can still execute those actions but in a different manner.
So, as an example, look at these statements in pure SQL
ALTER SYSTEM SET processes=200 scope=spfile;
ALTER SYSTEM SET sessions=400 scope=spfile;
ALTER SYSTEM SET transactions=450 scope=spfile;
To begin, we need to list all parameter families and identify Oracle’s, using AWS Management Console (AWS Cli):
aws --profile symphonica-dev rds describe-db-engine-versions --query "DBEngineVersions[].DBParameterGroupFamily"
The result could be something like oracle-se2-12.2
Secondly, we need to create a parameter group in the database, with create-db-parameter-group command, for example, iwparametergroup, indicating the family group found in the previous step.
aws --profile symphonica-dev rds create-db-parameter-group --db-parameter-group-name iwparametergroup --db-parameter-group-family oracle-se2-12.2 --description "IW parameter group" { "DBParameterGroup": { "DBParameterGroupName": "iwparametergroup", "DBParameterGroupFamily": "oracle-se2-12.2", "Description": "IW parameter group", "DBParameterGroupArn": "arn:aws:rds:us-east-1:009443737847:pg:iwparametergroup" } }
And finally, we can modify iwparametergroup parameters using modify-db-parameter-group command
aws --profile symphonica-dev rds modify-db-parameter-group \ > --db-parameter-group-name iwparametergroup \ > --parameters > "ParameterName=processes,ParameterValue=200,ApplyMethod=immediate"\ > "ParameterName=sessions,ParameterValue=400,ApplyMethod=immediate"\ > "ParameterName=transactions,ParameterValue=450,ApplyMethod=immediate"
Not every parameter can be modified, so it is necessary but not mandatory to list all parameters before and check which can be changed before using the modify-db-parameter-group command.
aws rds describe-db-parameters --db-parameter-group-name iwparametergroup Partial example, for parameter db_create_file_dest { "ParameterName": "db_create_file_dest", "ParameterValue": "/rdsdbdata/db", "Description": "default database location", "Source": "system", "ApplyType": "dynamic", "DataType": "string", "IsModifiable": false, "ApplyMethod": "pending-reboot" }
As we can see, db_create_file_dest cannot be modified.
GRANT SELECT ON SYS.
Statements like GRANT SELECT ON SYS.xxx will lead to ORA-01031 error too.
For example, when we have:
GRANT SELECT ON SYS.DBA_OBJECTS TO USERX
As a solution, we can change those lines with RDS operation rdsadmin.rdsadmin_util directly into the sql file:
begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'DBA_OBJECTS', p_grantee => 'USERX', p_privilege => 'SELECT', p_grant_option => true); end; /
With rdsadmin.rdsadmin_util we can perform common DBA system tasks related to the system in RDS, and those are:
- Disconnecting a Session
- Killing a Session
- Canceling a SQL Statement in a Session
- Enabling and Disabling Restricted Sessions
- Flushing the Shared Pool
- Flushing the Buffer Cache
- Granting SELECT or EXECUTE Privileges to SYS Objects
- Revoking SELECT or EXECUTE Privileges on SYS Objects
- Granting Privileges to Non-Master Users
- Creating Custom Functions to Verify Passwords
- Setting Up a Custom DNS Server
You can see them in detail here.
In conclusion, migrating from Oracle schemas to AWS RDS needed some particular care and work, mainly to transform the SQL into RDS command compatible, but finally, every step was successful and we achieved a well-structured database in the cloud.
