Oracle recommends that you connect to the database via custom services. In your connect string, don’t connect:
- Directly to the
SID - Or to the database’s default service (the service with the same name as the database).
When you move a database around, in some situations, the database does not retain these services, for instance, when you:
- Migrate a non-CDB to PDB using refreshable clone PDB
- Upgrade a PDB using refreshable clone PDB
- Move a PDB to a different CDB using refreshable clone PDB
- Migrating a database using Full Transportable Export/Import or transportable tablespaces
The services are important because your application and clients connect to the database through that service. Also, the service might define important properties for things like Application Continuity or set default drain timeout.
Here’s how to recreate such services.
Database Managed Services
A database-managed service is one that you create directly in the database using dbms_service:
begin
dbms_service.create_service(
service_name=>'SALESGOLD',
network_name=>'SALESGOLD');
dbms_service.start_service('SALESGOLD');
end;
/
After the migration, you must manually recreate the service in the target database.
dbms_metadata does not support services. So, you must query v$services in the source database to find the service’s defition. Then, construct a call to dbms_service.create_service and dbms_serice.start_service.
Clusterware Managed Services
I recommend defining services in Grid Infrastructure if you are using Oracle RAC or using Oracle Restart to manage your single instance database. Luckily, Grid Infrastructure supports exporting and importing service defitions.
-
You export all the services defined in the source database:
srvctl config service \ -db $ORACLE_UNQNAME \ -exportfile my_services.json \ -S 2 -
You edit the JSON file.
- Remove the default services. Keep only your custom services.
- Remove the
dbunique_nameattribute for all services. - If you are renaming the PDB, you must update the
pluggable_databaseattribute. - Update the
res_nameattribute so it matches the resource name of the target database. Probably you just need to exchange thedb_unique_namepart of the resource name. You can find the resource name as grid when you executecrsctl stat resource -t.
-
You can now import the services into the target database:
srvctl add service \ -db $ORACLE_UNQNAME \ -importfile my_services.json -
Finally, you start the service(s):
export ORACLE_SERVICE_NAME=SALESGOLD srvctl start service \ -db $ORACLE_UNQNAME \ -service $ORACLE_SERVICE_NAME
Additional Information
- The export/import features work from Oracle Database 19c, Release Update 19 and beyond.
- You can also export/import the definition of:
- Database:
srvctl config database -db $ORACLE_UNQNAME -S 2 -exportfile my_db.json.json - PDB:
srvctl config pdb -db $ORACLE_UNQNAME -S 2 -exportfile my_pdb.json - ACFS filesystem:
srvctl config filesystem -S 2 -exportfile /tmp/my_filesystem.json
- Database:
- At time of writing, this functionality hasn’t made it into the documentation yet. Consider yourself lucky knowing this little hidden gem.
Final Words
Remember to recreate your custom services after a migration. Your application needs the service to connect in a proper way.
Databases Are Fun 