DBA Hub

📋Steps in this guide1/4

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.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

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 .
2

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. Now create a new PDB base on the existing PDB. Notice when we switch to the PDB the service names have been duplicated. Connecting to the service always connects us to the original PDB, and we can't switch to the service in the new PDB.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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>

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>

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>
3

Rename Services During PDB Creation

The solution to this is to rename the services during PDB creation, which we do by using the clause of the 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. With the new services in place, we can decide which PDB to connect to based on the service.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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;
/

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>
4

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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# 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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!