DBA Hub

📋Steps in this guide1/3

Multitenant : Metadata Only PDB Clones in Oracle Database 12c Release 1 (12.1.0.2)

Make structure-only copies of PDBs using the NO DATA clause added in Oracle Database 12c Release 1 (12.1.0.2).

oracle 12cconfigurationintermediate
by OracleDba
11 views
1

Setup

Create a clean PDB, then add a new user and a test table with some data.

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
CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb10 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb10/');

ALTER PLUGGABLE DATABASE pdb10 OPEN;

ALTER SESSION SET CONTAINER = pdb10;

CREATE TABLESPACE users
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb10/users01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users;

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

SELECT COUNT(*) FROM test.t1;

  COUNT(*)
----------
         1

SQL>
2

Metadata Clone

Perform a metadata-only clone of the PDB using the clause. Checking the contents of the test table in the new PDB show the table is present, but it is empty.

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
CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb10 CLOSE;
ALTER PLUGGABLE DATABASE pdb10 OPEN READ ONLY;

CREATE PLUGGABLE DATABASE pdb11 FROM pdb10
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb10/','/u01/app/oracle/oradata/cdb1/pdb11/')
NO DATA
;

ALTER PLUGGABLE DATABASE pdb11 OPEN READ WRITE;

-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb10 CLOSE;
ALTER PLUGGABLE DATABASE pdb10 OPEN READ WRITE;

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb11;

SELECT COUNT(*) FROM test.t1;

  COUNT(*)
----------
         0

SQL>
3

Restrictions

The clause is only valid is the the source PDB doesn't contain any of the following. - Index-organized tables - Advanced Queue (AQ) tables - Clustered tables - Table clusters 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
SQL> CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb1/pdb11/')
   NO DATA;
CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
*
ERROR at line 1:
ORA-65161: Unable to create pluggable database with no data

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!