AWS Database Blog

Recreate an Amazon RDS Custom for Oracle database: Part 2

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:

  1. Part 1 covers customizing the time zone and character set of the database
  2. Part 2 (this post) discusses customizations such as changing the default block size, which requires the database to be recreated
  3. 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:

  1. On the Amazon RDS console, in the navigation pane, choose Databases, then choose the database to recreate.
  2. Choose the Configuration tab and note the resource ID for the instance (it will be in the format db-ABCDEFGHIJKLMNOPQRS0123456).
  3. On the Secrets Manager console, choose the secret that has the name do-not-delete-custom-<resource_id>.
  4. 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:

$sudo su - rdsdb
$sqlplus / as sysdba
SQL>

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:

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%CHARACTERSET%';

SQL>select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

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:

SQL>select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views                      19.0.0.0.0                     VALID
Oracle Database Packages and Types                 19.0.0.0.0                     VALID
Oracle Real Application Clusters                   19.0.0.0.0                     OPTION OFF
Oracle XML Database                                19.0.0.0.0                     VALID
Oracle Text                                        19.0.0.0.0                     VALID

You can run the following query to check if any non-default objects are created in the database:

SQL>select owner,count(1) from dba_objects where owner  in(select username from dba_users where oracle_maintained!='Y') group by owner;

no rows selected

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:

define v_username = "'RDSADMIN','ADMIN'"

Copy the following script as recreate_custom_resources_input.sql in the working directory and run it:

************************ COPY FROM NEXT LINE ***************************
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
define v_username = "'RDSADMIN','ADMIN'"
spool recreate_custom_resources_output.sql
select dbms_metadata.get_ddl('DIRECTORY','OPATCH_INST_DIR') as ddl from dual;
select dbms_metadata.get_ddl('DIRECTORY','OPATCH_LOG_DIR') as ddl from dual;
select dbms_metadata.get_ddl('DIRECTORY','OPATCH_SCRIPT_DIR') as ddl from dual;
select dbms_metadata.get_ddl('PROFILE', 'RDSADMIN') AS ddl
from   dual;
select 'CREATE BIGFILE TABLESPACE "RDSADMIN" DATAFILE SIZE 50M AUTOEXTEND ON MAXSIZE 33554431M LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;' from dual;
select 'CREATE BIGFILE TABLESPACE "USERS" DATAFILE SIZE 100M AUTOEXTEND ON MAXSIZE 33554431M LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;' from dual;
select dbms_metadata.get_ddl('TABLESPACE','TEMP' ) AS ddl from dual;
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username in (&v_username)
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username in (&v_username)
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.username) AS ddl
from   dba_users rp
where  rp.username in (&v_username)
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.username) AS ddl
from   dba_users sp
where  sp.username in (&v_username)
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.username) AS ddl
from   dba_users tp
where  tp.username in (&v_username)
and exists (select 'x' from dba_tab_privs tabpri where tabpri.grantee=tp.username)
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', lp.username) AS ddl
from   dba_users lp
where  lp.username in (&v_username)
/
set linesize 80 pagesize 14 feedback on trimspool on verify on
spool off
************************ COPY UNTIL PREVIOUS LINE ***************************

Run the preceding script from SQL*Plus as sysdba:

$sqlplus / as sysdba
SQL>@recreate_custom_resources_input.sql

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.

  1. 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. 
    $sqlplus / as sysdba
SQL>create pfile from spfile;
  2. Find the original location of the SPFILE to recreate the SPFILE in the same location after the database is created: 
    ls -l $ORACLE_HOME/dbs/spfileORCL.ora

    It will show the soft link to the original file in /rdsdbdata/admin/ (for example, /rdsdbdata/admin/ORCL/pfile/spfileORCL.ora).

  3. Drop the existing database with restricted mode enabled on the database: 
    SQL>shutdown immediate
SQL> startup restrict mount
SQL>drop database;
SQL>exit
  4. 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:

    *.db_block_size=16384

    Replace ORCL in the following commands with the actual database name:

    $sqlplus / as sysdba
SQL> create spfile='/rdsdbdata/admin/ORCL/pfile/spfileORCL.ora' from pfile;
SQL>exit

    Create a symbolic link for spfile:

    $ ln -s /rdsdbdata/admin/ORCL/pfile/spfileORCL.ora $ORACLE_HOME/dbs/
  5. 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.

    $sqlplus / as sysdba
SQL> startup nomount
SQ> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /rdsdbdata/db/ORCL_A/controlfile/control-01.ctl
SQL>exit

$mkdir -p /rdsdbdata/db/ORCL_A/controlfile

    Use the parent directory of the control_files parameter in the preceding command.

  6. Create the database manually (substitute your-password with the DB user password retrieved from Secrets Manager): 
    $sqlplus / as sysdba

-- Below script creates the database with database character set JA16EUCTILDE and national character set UTF8

SQL>CREATE DATABASE ORCL
USER SYS IDENTIFIED BY "your-password"
USER SYSTEM IDENTIFIED BY "your-password"
SET DEFAULT BIGFILE TABLESPACE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE users
MAXLOGFILES 50
MAXDATAFILES 1000
UNDO TABLESPACE "UNDO_T1"
CHARACTER SET JA16EUCTILDE
NATIONAL CHARACTER SET UTF8;

-- Below script creates the database with database character set AL32UTF8 and database Time Zone GMT+4

SQL>CREATE DATABASE ORCL
USER SYS IDENTIFIED BY "your-password"
USER SYSTEM IDENTIFIED BY "your-password"
SET DEFAULT BIGFILE TABLESPACE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE users
MAXLOGFILES 50
MAXDATAFILES 1000
UNDO TABLESPACE "UNDO_T1"
SET TIME_ZONE = '+04:00'
CHARACTER SET AL32UTF8
  7. 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.

    SQL>@?/rdbms/admin/catalog.sql
SQL>@?/rdbms/admin/catproc.sql
  8. If you use PRODUCT_USER_PROFILE in SQL*Plus, create necessary objects by running pupbld.sql as SYSTEM user: 
    SQL>connect system/<your password> -- Password used for SYSTEM in create database command
SQL>@?/sqlplus/admin/pupbld.sql
SQL>exit
  9. Convert the database to archivelog mode: 
    SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;

Run post-database creation scripts

Complete the following steps:

  1. Create a default profile with password verification functions and adjust the settings for the DEFAULT profile: 
    SQL>@?/rdbms/admin/utlpwdmg.sql

SQL>ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;
  2. Create RDS Custom specific resources using the script you created earlier: 
    SQL>@recreate_custom_resources_output.sql
  3. Assign the RDSADMIN profile to SYS, SYSTEM, and DBSNMP users and run userlock.sql to create the USER_LOCK package: 
    SQL>alter user SYS profile RDSADMIN;
SQL>alter user SYSTEM profile RDSADMIN;
SQL>alter user DBSNMP profile RDSADMIN;

SQL>@?/rdbms/admin/userlock.sql

Run datapatch to update SQL registry with Release Update (RU) details

Enter the following code:

$ORACLE_HOME/OPatch/datapatch -verbose

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:

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%CHARACTERSET%';

select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

You can also query dba_registry to check the status of Oracle internal registry components to confirm the database recreation process was successful:

select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views                      19.0.0.0.0                     VALID
Oracle Database Packages and Types                 19.0.0.0.0                     VALID
Oracle Real Application Clusters                   19.0.0.0.0                     OPTION OFF
Oracle XML Database                                19.0.0.0.0                     VALID
Oracle Text                                        19.0.0.0.0                     VALID

Verify that there are no INVALID objects in the database:

SQL>
   Select substr(owner,1,12) owner,
           substr(object_name,1,30) object,
           substr(object_type,1,30) type, status,
           created
    from
          dba_objects where status <>'VALID'
SQL> 

no rows selected

Resume automation

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:

aws rds describe-db-instances --db-instance-identifier demo-2-replica
"LatestRestorableTime": "2022-05-18T11:21:27+00:00"

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:

aws rds describe-db-snapshots --db-instance-identifier  demo-2-replica --query="max_by(DBSnapshots, &SnapshotCreateTime)"


"DBSnapshotIdentifier": "rds:demo-2-replica-2022-05-18-11-16",
 "DBInstanceIdentifier": "demo-2-replica",
 "SnapshotCreateTime": "2022-05-18T11:17:33.417000+00:00",

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.

Conclusion

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.