How do I change the service name and SID of my Amazon RDS for Oracle instance?

Short description

By default, the SID value for an Amazon RDS for Oracle instance is ORCL. You can't change the SID or SERVICE_NAMES parameter of an existing Amazon RDS for Oracle instance.

To view your service name, run one of the following commands:

select value from v$parameter where name='service_names';

show parameter service_names;

Note: The service name of an Amazon RDS for Oracle instance is SID_A, where SID is the DB name. For a default DB name, SERVICE_NAMES is ORCL_A.

Resolution

Note: You can't change the SERVICE_NAMES parameter in the Amazon RDS custom parameter group.

To change the service name of the existing Amazon RDS for Oracle instance, use one of the following methods:

• Restore a DB instance from a DB snapshot, and use a new DB name.
• Create a new Amazon RDS instance with the your SID, and migrate the data to the new instance.
• Create a custom service name.

Restore a DB instance from a DB snapshot, and use a new DB name

1.    Create a DB snapshot.

2.    Restore the DB instance from the DB snapshot.

3.    Under Database options, specify a new DB name to use for the SID of the Oracle DB instance.

4.    Check that the new DB name is updated:

Note: Replace example-new-database-name with the name of your updated database.

select name, value from v$parameter where name=example-new-database-name;

5.    Confirm the instance connectivity with the new service name or SID.

Create a new Amazon RDS instance with your SID, and migrate the data to the new instance

1. Create an Amazon RDS Oracle instance with a new DB name.
2. Use either Oracle Data Pump or AWS Data Migration Service (AWS DMS) to migrate data from the old instance to the new Amazon RDS for Oracle instance.

Create a custom service name

To create a custom service name, use the DBMS_SERVICE package. After the Oracle database restarts, turn on the auto-start of the created custom service. For more information, see DBMS_SERVICE on the Oracle website.

Note: You can create custom service names for application connections to an Amazon RDS for Oracle database. To deliver a managed service, you don't have permission to alter the SERVICE_NAMES parameter or have server access to use srvctl to create the service. As a result, the custom service doesn't start when the database restarts. Instead, you must manually restart the database.

Create a startup trigger to start the service after a database restart so that you don't have to manually start the custom service:

1.    Create a new service, and then start the service:

Note: Replace example-service-name with the name of your new service.

begin  
dbms_service.create_service('example-service-name','example-service-name');  
dbms_service.start_service('example-service-name');  
end;  
/

2.    Check the status of the new service:

select * from dba_services;

3.    Use Oracle client tools, such as sqlplus and SQL developer, to test the database connectivity with the new service.

4.    Create a trigger to start the services automatically after a database restart.

To create the trigger, complete the following steps:

1.    Grant access to the owner of a procedure or trigger to run dbms_service:

Note: Replace example-username to the procedure or trigger owner's name.

begin  
rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SERVICE','example-username');  
end;  
/

2.    Create the custom procedure to start the service that's in the stopped state after a database restart:

Note: Replace example-procedure-name with the name of the custom procedure.

CREATE OR REPLACE PROCEDURE example-procedure-name
IS
cursor tempvar is select name from sys.service$ where network_name is not null and name not in (select value from v$parameter where name='service_names');
loopvar tempvar%ROWTYPE;
begin
OPEN tempvar;
loop
fetch tempvar into loopvar;
EXIT WHEN tempvar%NOTFOUND;
begin
DBMS_SERVICE.start_service(loopvar.name);
end;
end loop;
close tempvar;
end;
/

3.    Create a startup trigger to start the database services at the database startup time:

Note: Replace example-trigger-name with the name of the startup trigger. Replace example-procedure-name with the name of the custom procedure.

CREATE OR REPLACE TRIGGER example-trigger-name  
after startup on database  
begin  
example-procedure-name;  
end;  
/

Related information

Renaming a DB instance