8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Assumptions

The examples in this article assume the following.

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 \
 -automaticMemoryManagement false \
 -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:

Hope this helps. Regards Tim...

Back to the Top.