DBA Hub

📋Steps in this guide1/2

Multitenant : PDB Subset Cloning in Oracle Database 12c Release 1 (12.1.0.2)

Use subset cloning to limit the amount of tablespaces you bring across to your new PDB.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Setup

To see this feature working we will create a clean PDB, then add 3 new tablespaces, each with a default user and a single object in them. This will mimic a situation where a single database has been used to consolidate three different applications. We can see the separation between the schema in the following query.

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
70
71
72
73
CONN / AS SYSDBA

-- Create a new PDB
CREATE PLUGGABLE DATABASE pdb20 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb20/');

ALTER PLUGGABLE DATABASE pdb20 OPEN;

ALTER SESSION SET CONTAINER = pdb20;

-- Create first TS, User, Table.
CREATE TABLESPACE ts1
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb20/ts101.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test1 IDENTIFIED BY test1
  DEFAULT TABLESPACE ts1
  QUOTA UNLIMITED ON ts1;

CREATE TABLE test1.t1 (
  id NUMBER
);
INSERT INTO test1.t1 VALUES (1);
COMMIT;


-- Create second TS, User, Table.
CREATE TABLESPACE ts2
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb20/ts201.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test2 IDENTIFIED BY test2
  DEFAULT TABLESPACE ts2
  QUOTA UNLIMITED ON ts2;

CREATE TABLE test2.t2 (
  id NUMBER
);
INSERT INTO test2.t2 VALUES (1);
COMMIT;


-- Create third TS, User, Table.
CREATE TABLESPACE ts3
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb20/ts301.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test3 IDENTIFIED BY test3
  DEFAULT TABLESPACE ts3
  QUOTA UNLIMITED ON ts3;

CREATE TABLE test3.t3 (
  id NUMBER
);
INSERT INTO test3.t3 VALUES (1);
COMMIT;

COLUMN owner FORMAT A20
COLUMN table_name FORMAT A20
COLUMN tablespace_name FORMAT A20

SELECT owner, table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('T1','T2','T3')
ORDER BY owner;

OWNER                TABLE_NAME           TABLESPACE_NAME
-------------------- -------------------- --------------------
TEST1                T1                   TS1
TEST2                T2                   TS2
TEST3                T3                   TS3

SQL>
2

PDB Subset Cloning

PDB subset cloning is made possible using the clause, which allows you to specify the user-defined tablespaces to be included in the clone in one of several ways. - One of more named tablespaces in a comma separated list. - NONE : No user-defined tablespaces are included in the clone. - ALL : All user-defined tablespaces are included in the clone. This is the same as omitting the clause completely. - ALL EXCEPT : Exclude one or more named user-defined tablespaces as a comma separated list. The following example creates a clone including a named list of tablespaces. If we query the list of tablespaces, it appears all of them are present. If we try to access the objects from each schema, we see this is not the case. As requested, the datafile for the TS3 tablespace has not been cloned, so we should do some post-clone clean up to make the PDB look consistent. The following example creates a clone with none of the user-defined tablespaces present. The following example clones all the user-defined tablespaces, which is the same as omitting the clauses. The variant allows you to list those tablespaces to be excluded. In all cases, you will need to perform the post-clone clean up operations to make the databases look consistent. 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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb21 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb21/')
USER_TABLESPACES=('ts1', 'ts2')
;

ALTER PLUGGABLE DATABASE pdb21 OPEN;

ALTER SESSION SET CONTAINER = pdb21;

SELECT tablespace_name from dba_tablespaces;

TABLESPACE_NAME
--------------------
SYSTEM
SYSAUX
TEMP
TS1
TS2
TS3

6 rows selected.

SQL>

SQL> SELECT * FROM test1.t1;

        ID
----------
         1

SQL> SELECT * FROM test2.t2;

        ID
----------
         1

SQL> SELECT * FROM test3.t3;
SELECT * FROM test3.t3
                    *
ERROR at line 1:
ORA-00376: file 30 cannot be read at this time
ORA-01111: name for data file 30 is unknown - rename to correct file
ORA-01110: data file 30:
'/u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00030'


SQL>

DROP TABLESPACE ts3 INCLUDING CONTENTS AND DATAFILES;
DROP USER test3 CASCADE;

CREATE PLUGGABLE DATABASE pdb22 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb22/')
USER_TABLESPACES=NONE
;

ALTER PLUGGABLE DATABASE pdb22 OPEN;

CREATE PLUGGABLE DATABASE pdb23 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb23/')
USER_TABLESPACES=ALL
;

ALTER PLUGGABLE DATABASE pdb23 OPEN;

CREATE PLUGGABLE DATABASE pdb24 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb24/')
USER_TABLESPACES=ALL EXCEPT('ts3')
;

ALTER PLUGGABLE DATABASE pdb24 OPEN;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!