IT博客汇 | Oracle Multitenant - PDB Clone New Features (12.1.0.2.0)

Oracle Multitenant - PDB Clone New Features (12.1.0.2.0) - Part III

Mahir M. Quluzade (noreply@blogger.com)发表于 2014-07-31 15:55:49

As you know, Oracle Database 12c Release 1 (12.1.0.2.0) already released at July 22, 2014. You can download new release from here.

With this new release coming some new features. This article series covers PDB Clone new features of Oracle Multitenant option of Oracle Database 12c Release 1 (12.1.0.2.0).

This article is in four parts:

Part III. PDB Subset Cloning

Sometimes we using many schemas that each supported a different application in a database (Schema Consolidation). With Oracle Database 12c coming new option Oracle Multitenant, which is helpful for simplify consolidate databases.Database Consolidation means, each pluggable database (PDB) support a different application and all pluggable databases adopted in same Multitenant Container Database (CDB).

If we move a non-CDB to a PDB, and the non-CDB had different schemas that each supported a different application, then we need database consalidation. In other words we must separate schemas to defferent PDB. We can use USER_TABLESPACES, which is new clause in 12.1.0.2.0, to separate the data belonging to each schema into a separate PDB, assuming that each schema used a separate tablespace in the non-CDB.

USER_TABLESPACES clause to specify one of the following options:

List one or more tablespaces to include.
Specify ALL, the default, to include all of the tablespaces.
Specify ALL EXCEPT to include all of the tablespaces, except for the tablespaces listed.
Specify NONE to exclude all of the tablespaces.

The tablespaces that are excluded by this clause are offline in the new PDB, and all data files that belong to these tablespaces are unnamed and offline.This clause does not apply to the SYSTEM, SYSAUX, or TEMP tablespaces. Do not include these tablespaces in a tablespace list for this clause.

Follow my examples:

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 25 10:02:58 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select cdb, name from v$database;

CDB NAME
--- ---------
YES PRMCDB

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
NONCDB2PDB         READ WRITE

SQL> alter session set container=noncdb2pdb;

Session altered.

SQL> select tablespace_name from dba_tablespaces
  2 where tablespace_name like 'APP%';

TABLESPACE_NAME
------------------------------
APP1DATA
APP2DATA

SQL> select username, default_tablespace from dba_users
  2  where username like 'APP%U%';

USERNAME    DEFAULT_TABLESPACE
---------  ----------------------
APP2USER    APP2DATA
APP1USER    APP1DATA

SQL> conn app1user/app1user@noncdb2pdb
Connected.
SQL> 
SQL> select count(1) from t;

  COUNT(1)
----------
   1000000

SQL> conn app2user/app2user@noncdb2pdb 
Connected.
SQL> 
SQL>  select count(1) from dt;

  COUNT(1)
----------
    100000

In my case app1user, app2user is defferent users and supports different applications. My goal is separate this schemas into 2 different PDB.

Note: We must include users tablespace to user_tablespace clause because some system users default tablespace is users tablespace. Otherwise you will get missing data file error, becuase users data files will exclude during PDB cloning.

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 25 10:02:58 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database noncdb2pdb close;

Pluggable database altered.

SQL> alter pluggable database noncdb2pdb open read only;

Pluggable database altered.

SQL> create pluggable database app2pdb from noncdb2pdb
   2 file_name_convert=('noncdb2pdb','app2pdb') 
   3 user_tablespaces=('app2data','users');

Pluggable database created.

SQL> alter pluggable database app1pdb open;

Pluggable database altered.

SQL> alter session set container=app1pdb; 

Session altered.

SQL> conn app1user/app1user@app1pdb
Connected.
SQL> 
SQL> select count(1) from t;

  COUNT(1)
----------
   1000000

New PDB is cloned from PDB (noncdb2pdb). It means, app2user also cloned but without tablespaces, in other word without schema objects.

SQL> conn app2user/app2user@app1pdb;
Connected.
SQL> 
SQL> select count(1) from dt;
select count(1) from dt
                     *
ERROR at line 1:
ORA-00376: file 57 cannot be read at this time
ORA-01111: name for data file 57 is unknown - rename to correct file
ORA-01110: data file 57:
'/u01/app/oracle/product/12.1.0.2/dbhome/dbs/MISSING00057'

We can drop not needed existing users and tablespaces, such as app2user user, app2data tablespace.

SQL> conn / as sysdba
Connected.
SQL> 
SQL> alter session set container=app1pdb;

Session altered.

SQL> drop user app2user cascade;

User dropped.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
APP1DATA
APP2DATA

6 rows selected.

SQL> drop tablespace app2data including contents and datafiles;

Tablespace dropped.

Create second PDB for second application:

SQL> conn / as sysdba
Connected.
SQL>             
SQL> create pluggable database app2pdb from noncdb2pdb 
  2 file_name_convert=('noncdb2pdb','app2pdb') 
  3 user_tablespaces=('app2data','users');

Pluggable database created.

SQL> alter pluggable database app2pdb open;

Pluggable database altered.

SQL> conn app2user/app2user@app2pdb
Connected.
SQL> 
SQL> select count(1) from dt;

  COUNT(1)
----------
    100000

SQL> conn sys@app2pdb as sysdba
Enter password: 
Connected.
SQL> 
SQL> drop user app1user cascade; 

User dropped.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
APP1DATA
APP2DATA

6 rows selected.

SQL> drop tablespace app1data including contents and datafiles;

Tablespace dropped.

Now in my CDB have 3 PDB. My first PDB separeted to 2 different PDB.

SQL> alter session set container = CDB$ROOT;

Session altered.

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
NONCDB2PDB       READ ONLY
APP1PDB          READ WRITE
APP2PDB          READ WRITE

For connection of applications, you must only add service description to tnsnames.ora file as below:

APP2PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel62-ora12c2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = app2pdb)
    )
  )

APP1PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel62-ora12c2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = app1pdb)
    )
  )

If in your cases a user schema objects separated to 2 or more tablespaces, then you must include all tablespaces to user_tablespaces clause value like as below:

... user_tablespace= ('apptbs1','apptbs2','apptbs3');

Regards
Mahir M. Quluzade