8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Rename Services During PDB Creation in Oracle Database 12c Release 2 (12.2)
In Oracle 12c Release 2 it is possible to rename PDB services during PDB creation to prevent clashes.
Related articles.
- Multitenant : All Articles
- Services in Oracle Database 10g
- DBMS_SERVICE : Define Database Services in a Single Instance Database
Assumptions
The examples in this article assume the following.
- We are using Oracle Managed Files (OMF), so no file name conversion is needed. If you are not using OMF, you will need to include the file name conversion.
- The root container is in archived redo log mode, so we can do hot clones. If you are not running in archived redo log mode, you will need to switch the source PDB into read-only mode before performing a clone operation.
You can see how to enable OMF and archived redo log in the appendix.
The Problem
If we create services within a PDB, those services are recreated in any new PDB based on the original. That can present a problem if multiple PDBs sharing the same listener try to use the same service name.
To demonstrate this, create some new services in the existing PDB.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; BEGIN DBMS_SERVICE.create_service('my_new_service_1','my_new_service_1'); DBMS_SERVICE.start_service('my_new_service_1'); DBMS_SERVICE.create_service('my_new_service_2','my_new_service_2'); DBMS_SERVICE.start_service('my_new_service_2'); END; / SELECT name FROM dba_services ORDER BY name; NAME ---------------------------------------------------------------- PDB1 my_new_service_1 my_new_service_2 SQL>
Now create a new PDB base on the existing PDB. Notice when we switch to the PDB the service names have been duplicated.
CONN / AS SYSDBA CREATE PLUGGABLE DATABASE pdb2 FROM pdb1; ALTER PLUGGABLE DATABASE pdb2 OPEN; ALTER SESSION SET CONTAINER=pdb2; SELECT name FROM dba_services ORDER BY name; NAME ---------------------------------------------------------------- PDB2 my_new_service_1 my_new_service_2 SQL>
Connecting to the service always connects us to the original PDB, and we can't switch to the service in the new PDB.
CONN sys/SysPassword1@//localhost:1521/my_new_service_1 AS SYSDBA SHOW CON_NAME CON_NAME ------------------------------ PDB1 SQL> CONN sys/SysPassword1@//localhost:1521/my_new_service_2 AS SYSDBA SHOW CON_NAME CON_NAME ------------------------------ PDB1 SQL> ALTER SESSION SET CONTAINER=pdb2 SERVICE=my_new_service_1; ERROR: ORA-44787: Service cannot be switched into. SQL>
Rename Services During PDB Creation
The solution to this is to rename the services during PDB creation, which we do by using the SERVICE_NAME_CONVERT
clause of the CREATE PLUGGABLE DATABASE
statement. Like most of the convert parameters, we provide a comma-separated list representing "from" and "to" conversion values.
Recreate the pluggable database, but this time rename the services.
CONN / AS SYSDBA -- Remove the PDB. ALTER PLUGGABLE DATABASE pdb2 CLOSE; DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES; -- Create the PDB, renaming the services. CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 SERVICE_NAME_CONVERT=('my_new_service_1','my_new_service_1b','my_new_service_2','my_new_service_2b'); ALTER PLUGGABLE DATABASE pdb2 OPEN; -- Check the services. ALTER SESSION SET CONTAINER=pdb2; SELECT name FROM dba_services ORDER BY name; NAME ---------------------------------------------------------------- PDB2 my_new_service_1b my_new_service_2b SQL> -- Start the services. BEGIN DBMS_SERVICE.start_service('my_new_service_1b'); DBMS_SERVICE.start_service('my_new_service_2b'); END; /
With the new services in place, we can decide which PDB to connect to based on the service.
CONN sys/SysPassword1@//localhost:1521/my_new_service_1 AS SYSDBA SHOW CON_NAME CON_NAME ------------------------------ PDB1 SQL> CONN sys/SysPassword1@//localhost:1521/my_new_service_1b AS SYSDBA SHOW CON_NAME CON_NAME ------------------------------ PDB2 SQL> ALTER SESSION SET CONTAINER=pdb2 SERVICE=my_new_service_1b; Session altered. SQL>
Appendix
The container database instance was built using the following commands. The command to remove the instance is included, so you can clean up when you are finished.
# Create FRA location. mkdir -p /u01/app/oracle/fast_recovery_area # Container (cdb1) with pluggable database (pdb1). dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \ -characterSet AL32UTF8 \ -sysPassword SysPassword1 \ -systemPassword SysPassword1 \ -createAsContainerDatabase true \ -numberOfPDBs 1 \ -pdbName pdb1 \ -pdbAdminPassword SysPassword1 \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 2048 \ -storageType FS \ -datafileDestination "/u02/oradata/" \ -redoLogFileSize 50 \ -emConfiguration NONE \ -ignorePreReqs export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES # Set required parameters. sqlplus / as sysdba <<EOF alter pluggable database pdb1 save state; alter system set db_create_file_dest = '/u02/oradata'; alter system set db_recovery_file_dest_size = 10G; alter system set db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area'; exit; EOF # Enable ARCHIVELOG mode. sqlplus / as sysdba <<EOF shutdown immediate; startup mount; alter database archivelog; alter database open; exit; EOF # Delete the instance. #dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
For more information see:
- Service Name Conversion
- Multitenant : All Articles
- Services in Oracle Database 10g
- DBMS_SERVICE : Define Database Services in a Single Instance Database
Hope this helps. Regards Tim...