In this series, we discuss best practices and step-by-step instructions to perform common customizations on Amazon Relational Database Service (Amazon RDS) Custom for Oracle without breaking the support perimeter:
Part 1 covers customizing the time zone and character set of the database
Part 2 (this post) discusses customizations such as changing the default block size, which requires the database to be recreated
Part 3 discusses more customization scenarios, such as enabling the flashback database, modifying TNS configurations and database options, and best practices to apply patches in your RDS Custom for Oracle instance.
There are few settings in an Oracle database that can’t be changed after the database is created. The default block size of the database is one of those settings that requires the database to be recreated for modification. In this post, we discuss how to recreate an RDS Custom for Oracle database for certain use cases:
Change the standard block size of the database – An RDS Custom for Oracle instance is created with a default database block size (DB_BLOCK_SIZE) of 8 K, which is appropriate for most use cases. This default block size can’t be changed after database creation except by recreating the database. Typically, a smaller block size (4 K or 8 K) is preferred for Online Transaction Processing (OLTP) systems, and a larger block size (8 K, 16 K, 32 K) is preferred for Decision Support Systems (DSS). Although you can have tablespaces of nonstandard block sizes, management of tablespaces with different block size configurations in a database can be difficult, especially memory management, because subcaches must be configured within the buffer cache area of the System Global Area (SGA) for all the nonstandard block sizes that you intend to use.
Change the database character set (NLS_CHARACTERSET) to a character set that isn’t a superset of the current database character set – As discussed in the first part of this series, you can change the database character set of your RDS Custom for Oracle database using the ALTER DATABASE command if the desired character set is a superset of the current character set used by the database. Otherwise, you can recreate the database to choose any character set.
Change the national character set (NLS_NCHAR_CHARACTERSET) – As discussed in Part 1, changing the national character set of an Oracle database can be complex due to NCHAR data types existing in the internal schemas. It’s simpler to recreate the existing database to change the national character set from AL16UTF16 (default) to UTF8.
Make multiple changes at the same time – If you want to change multiple configurations, such as time zone, NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET, and block size of the database, it’s simpler to recreate the default database with your desired configurations.
Instead of recreating the database, you may also choose to replace the existing RDS Custom for Oracle database with a copy of another database that is already configured with your desired settings. Refer to Physical migration of Oracle databases to Amazon RDS Custom using RMAN duplication and Physical migration of Oracle databases to Amazon RDS Custom using Data Guard for further details.
Requirements and limitations
When you recreate your RDS Custom database, you must follow the requirements as documented in Requirements and limitations for Amazon RDS Custom for Oracle. The following restrictions apply:
You must use the same database name (default is ORCL) as mentioned at the time of provisioning the instance. If you intend of change the database name, you must provision a new RDS Custom for Oracle instance with the desired database name.
As of this writing, RDS Custom for Oracle doesn’t support a multi-tenant architecture. Therefore, the recreated database must be of traditional non-CDB architecture.
The physical location of the database files (datafile, control_file, and redolog) should remain unchanged, because the automation framework has a dependency on the physical location of the database.
The database must be in archivelog mode.
Recreate your RDS Custom for Oracle database
In this section, we discuss the step-by-step instructions to recreate the database provisioned by an RDS Custom for Oracle instance, meeting the requirements for the RDS Custom framework. These steps should be applied immediately after provisioning the instance, before populating it with application data. If you intend to make the configuration changes discussed in this post to a database that already contains application data, you need to create another RDS Custom for Oracle instance with the desired configurations and logically migrate the data. You may also use replication tools like Oracle GoldenGate or AWS Database Migration Service (AWS DMS) if you want to achieve such migrations with a reduced outage window.
Pause RDS Custom automation
Before you customize your RDS Custom for Oracle database, you need to pause the automation to ensure that your customizations don’t interfere with the RDS Custom automation framework. You can pause the automation using either the Amazon RDS console or the AWS Command Line Interface (AWS CLI). For instructions, refer to Pausing and resuming RDS Custom automation. The whole process is expected to be complete in less than 60 minutes. However, you can further extend the pause period if you think this activity needs more time.
Retrieve database passwords
To recreate a database for RDS Custom for Oracle, you need to preserve passwords for SYS, SYSTEM, and RDSADMIN users. These database users share the same password, which you can retrieve from AWS Secrets Manager following these steps:
On the Amazon RDS console, in the navigation pane, choose Databases, then choose the database to recreate.
Choose the Configuration tab and note the resource ID for the instance (it will be in the format db-ABCDEFGHIJKLMNOPQRS0123456).
On the Secrets Manager console, choose the secret that has the name do-not-delete-custom-<resource_id>.
Choose Retrieve secret value and note the password for the database users.
Connect to the EC2 instance
To recreate the database, you need to connect to the underlying Amazon Elastic Compute Cloud (Amazon EC2) instance using SSH keys or AWS Systems Manager (for instructions, refer to Connecting to your RDS Custom DB instance using AWS Systems Manager).
After you’re logged in as ec2-user, you can switch to rdsdb user, which owns database binaries. Then you should be able to connect to the instance with sysdba privileges. See the following code:
Verify current database settings, that there is no application data in the database, and that it’s safe to drop and create an empty database
Depending on the configuration changes you intend to make during the database recreation process, you can verify the current settings by querying database dictionary views and then comparing it with the settings post-database creation:
You can also query dba_registry to check the status of Oracle internal registry components to compare the output of the same query post-database creation:
You can run the following query to check if any non-default objects are created in the database:
Prepare a SQL script for recreating RDS Custom specific resources after the database is recreated
These resources include database users such as RDSADMIN and the RDS Custom Master user, tablespaces, and user profile.
Find the Master user name that needs to be recreated (on the Configuration tab on the Amazon RDS console). If the Master user name is different from ADMIN, replace the keyword ADMIN with correct username in the following line in the script:
Copy the following script as recreate_custom_resources_input.sql in the working directory and run it:
Run the preceding script from SQL*Plus as sysdba:
This creates an output file in the current working directory named recreate_custom_resources_output.sql, which you need after recreating the database.
Recreate the database from the session connected to the EC2 instance
These steps assume the default database name is ORCL. You need to replace ORCL in the following steps with the actual database name based on your configuration. You can find the database name on the Configuration tab of the RDS Custom for Oracle DB instance on the Amazon RDS console.
Connect to the database as sysdba from the rdsdb OS user and create a parameter file (PFILE) from a server parameter file (SPFILE). This is to save the current parameters because the drop database command removes the SPFILE used by the instance.
Find the original location of the SPFILE to recreate the SPFILE in the same location after the database is created:
It will show the soft link to the original file in /rdsdbdata/admin/ (for example, /rdsdbdata/admin/ORCL/pfile/spfileORCL.ora).
Drop the existing database with restricted mode enabled on the database:
Create a SPFILE from the PFILE that was saved prior to the drop database command and create the soft link in the $ORACLE_HOME/dbs directory.
If you intend to make any changes to the configuration that require the parameter file to be modified prior to creating the database, it must be done at this stage. For example, if you intend to change the block size of the database, you must edit the parameter file ($ORACLE_HOME/dbs/initORCL.ora) to reflect the new block size. Add the following entry to the parameter file to change block size to 16 K:
Replace ORCL in the following commands with the actual database name:
Create a symbolic link for spfile:
Start the instance in mount and create the controlfile directory.
The parent directory of controlfile is removed by the drop database command. Recreate the directory prior to running the create database command.
Use the parent directory of the control_files parameter in the preceding command.
Create the database manually (substitute your-password with the DB user password retrieved from Secrets Manager):
Create database dictionary views, synonyms, and packages.
The following two scripts should finish in less than 15 minutes under normal conditions. You may choose to run them in background using the nohup utility.
If you use PRODUCT_USER_PROFILE in SQL*Plus, create necessary objects by running pupbld.sql as SYSTEM user:
Convert the database to archivelog mode:
Run post-database creation scripts
Complete the following steps:
Create a default profile with password verification functions and adjust the settings for the DEFAULT profile:
Create RDS Custom specific resources using the script you created earlier:
Assign the RDSADMIN profile to SYS, SYSTEM, and DBSNMP users and run userlock.sql to create the USER_LOCK package:
Run datapatch to update SQL registry with Release Update (RU) details
Enter the following code:
Verify settings of the recreated database
Depending on the configuration changes you made during the database recreation process, you can verify the current setting by querying database dictionary views:
You can also query dba_registry to check the status of Oracle internal registry components to confirm the database recreation process was successful:
Verify that there are no INVALID objects in the database:
Resume automation for the instance to enable the automation and monitoring framework of RDS Custom.
Verify the RDS Custom automation framework
Upon resuming the automation after you finish the customization, the RDS Custom instance starts an automated backup, after which the status of the instance on the Amazon RDS console changes to available if the steps are followed correctly. When the backup is complete, you can verify Latest restore time in the maintenance and backup section of the Amazon RDS console or using the AWS CLI as follows:
You can also verify the latest snapshot created by the instance from the Amazon RDS console (choose Automated backups, locate the database, and choose System snapshots) or using the AWS CLI as follows:
In the preceding examples, demo-2-replica is the instance identifier of the RDS Custom for Oracle instance.
If you see a latest automated snapshot created after the automation was resumed, and the latest restorable time is pointing to a time after you paused the automation and is advancing when you query it after 10 minutes, you can confirm that the instance is in healthy status.
In this post, we discussed the step-by-step instructions and best practices to recreate the default database created by an RDS Custom for Oracle instance to make configuration changes, which requires the database to be recreated, such as changing the default block size.
Refer to Part 1 of this series to learn more about customizing the time zone and character set of an RDS Custom for Oracle instance. In Part 3, we discuss more customization scenarios, such as enabling a flashback database, modifying TNS configurations and database options, and best practices to apply patches on your RDS Custom for Oracle instance.
If you have any comments or questions, please leave them in the comments section.
About the authors
Jobin Joseph is a Senior Database Specialist Solution Architect based in Dubai. With a focus on relational database engines, he assists customers in migrating and modernizing their database workloads to AWS. He is an Oracle Certified Master with 20 years of experience with Oracle databases.
Nitin Saxena is a Senior Database Engineer in RDS DBS Managed Commercial Engines with Amazon Web Services. He focuses on services like RDS Oracle and RDS Custom for Oracle . He enjoys designing and developing new features on RDS Oracle and RDS Custom to solve customer problems.
Dwarka Rao is a Database Migration Specialist Solutions Architect with the Amazon Database Migration Accelerator team at Amazon Web Services. He helps customers and partners build highly scalable, available, and secure databases solutions on cloud migration projects from on-premises to AWS.
Read MoreAWS Database Blog