Friday, September 18, 2015

Multitenant : PDB Cloning [remote and Non-CDB] 12c (12.1.0.2)

Multitenant : PDB Clone a Remote PDB or Non-CDB in Oracle Database

From Oracle 12.1.0.1 was it possible to de cloning of PDB's in a local Container database. Since version 12.1.0.2 Oracle has extended this functionality to remote PDB cloning. Cloning between two separted on-premises Oracle databases 12c non-CDBs as PDBs to an on-premises Oracle Database 12c container database. This method works also on Oracle Database 12c DBaaS database - Oracle Database Cloud Service (DBCS). An 12c RDBMS migration from On-Premise to Premise and back.



The user in the local database must have the CREATE PLUGGABLE DATABASE privilege in the root container. The remote database (PDB or non-CDB) must be open in read-only mode. The local database must have a database link to the remote database. If the remote database is a PDB, the database link can point to the remote CDB using a common user, or the PDB using a local or common user.

The user in the remote database, that the database link connects to, must have the CREATE PLUGGABLE DATABASE privilege. The local and remote databases must have the same endianness, options installed and character sets. When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher.

Purpose
In this blog I describe the steps for cloning method to migrate on-premises Oracle Database 12c non-CDBs or PDBs to an remote on-premises Oracle Database 12c database.

Introduction
In this blog I describe the steps for remote cloning method to migrate on-premises Oracle Database 12c non-CDBs or PDBs to an on-premises Oracle Database 12c database.

Prerequisites

  • Have a container database operational without PDBs DB name = joords01
  • Have a non-CDB database 12c operational db name = joords02
  • Have a container database operational with a PDB db name = cdb1
For remote Cloning to a Premise solutions:
  • Oracle Database Cloud Service (DBCS), it may be helpful to have followed the Signing Up for a Database Cloud Service, Connecting to a Database Cloud Service (DBaaS) Instance Through an SSH Tunnel and Creating a Database Cloud Service (DBaaS) Instance tutorials.


export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba

select name, open_mode from v$PDBs;
NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
ORCL          READ WRITE
ALTER SESSION SET CONTAINER=orcl;
CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;
ALTER PLUGGABLE DATABASE orcl CLOSE;
ALTER PLUGGABLE DATABASE orcl OPEN READ ONLY;
select name, open_mode from v$PDBs;
NAME          OPEN_MODE
------------------------------ ----------
ORCL          READ ONLY
export ORAENV_ASK=NO
export ORACLE_SID=joords01
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba

select name, open_mode from v$pdbs;
NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
DROP DATABASE LINK joords_clone_link;

CREATE DATABASE LINK joords_clone_link
  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user 
USING '(description=(address=(protocol=tcp)(host=127.0.0.1)(port=1521))(connect_data=(service_name=orcl)))';

select  sysdate from dual@joords_clone_link;
SYSDATE
---------
15-SEP-15
CREATE PLUGGABLE DATABASE db01_clone
    FROM orcl@joords_clone_link
    CREATE_FILE_DEST = '/home/oracle/app/oracle/data01/joords01';
select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
DB01_CLONE         MOUNTED
ALTER PLUGGABLE DATABASE db01_clone OPEN;
select name, open_mode from v$pdbs;
NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
DB01_CLONE         READ WRITE
SELECT message, action
FROM pdb_plug_in_violations
WHERE name = 'DB01_CLONE';
no rows found

Cloning a Remote Non-CDB in Oracle Database (12.1.0.2)

The steps are the same as for PDB's. Only this action has one additional action. This action needs a conversion of a non-CDB to PDB. The PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. Run noncdb_to_pdb.sql.


export ORAENV_ASK=NO
export ORACLE_SID=joords02
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba

select cdb from v$database;
CDB
---
NO
CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

EXIT;
export ORAENV_ASK=NO
export ORACLE_SID=joords01
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba

DROP DATABASE LINK noncdb_clone_link;

CREATE DATABASE LINK noncdb_clone_link
  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user 
  USING '(description=(address=(protocol=tcp)(host=127.0.0.1)(port=1521))(connect_data=(service_name=joords02)))';

 
DESC user_tables@noncdb_clone_link;

Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME       NOT NULL VARCHAR2(128)
 TABLESPACE_NAME        VARCHAR2(30)
 CLUSTER_NAME         VARCHAR2(128)
 IOT_NAME         VARCHAR2(128)
 STATUS          VARCHAR2(8)
 PCT_FREE         NUMBER
 PCT_USED         NUMBER
 INI_TRANS         NUMBER
 MAX_TRANS         NUMBER
 INITIAL_EXTENT         NUMBER
 NEXT_EXTENT         NUMBER
 MIN_EXTENTS         NUMBER
 MAX_EXTENTS         NUMBER
 PCT_INCREASE         NUMBER
 FREELISTS         NUMBER
 FREELIST_GROUPS        NUMBER
 LOGGING         VARCHAR2(3)
 BACKED_UP         VARCHAR2(1)
 NUM_ROWS         NUMBER
 BLOCKS          NUMBER
 EMPTY_BLOCKS         NUMBER
 AVG_SPACE         NUMBER
 CHAIN_CNT         NUMBER
 AVG_ROW_LEN         NUMBER
 AVG_SPACE_FREELIST_BLOCKS       NUMBER
 NUM_FREELIST_BLOCKS        NUMBER
 DEGREE          VARCHAR2(10)
 INSTANCES         VARCHAR2(10)
 CACHE          VARCHAR2(5)
 TABLE_LOCK         VARCHAR2(8)
 SAMPLE_SIZE         NUMBER
 LAST_ANALYZED         DATE
 PARTITIONED         VARCHAR2(3)
 IOT_TYPE         VARCHAR2(12)
 TEMPORARY         VARCHAR2(1)
 SECONDARY         VARCHAR2(1)
 NESTED          VARCHAR2(3)
 BUFFER_POOL         VARCHAR2(7)
 FLASH_CACHE         VARCHAR2(7)
 CELL_FLASH_CACHE        VARCHAR2(7)
 ROW_MOVEMENT         VARCHAR2(8)
 GLOBAL_STATS         VARCHAR2(3)
 USER_STATS         VARCHAR2(3)
 DURATION         VARCHAR2(15)
 SKIP_CORRUPT         VARCHAR2(8)
 MONITORING         VARCHAR2(3)
 CLUSTER_OWNER         VARCHAR2(128)
 DEPENDENCIES         VARCHAR2(8)
 COMPRESSION         VARCHAR2(8)
 COMPRESS_FOR         VARCHAR2(30)
 DROPPED         VARCHAR2(3)
 READ_ONLY         VARCHAR2(3)
 SEGMENT_CREATED        VARCHAR2(3)
 RESULT_CACHE         VARCHAR2(7)
 CLUSTERING         VARCHAR2(3)
 ACTIVITY_TRACKING        VARCHAR2(23)
 DML_TIMESTAMP         VARCHAR2(25)
 HAS_IDENTITY         VARCHAR2(3)
 CONTAINER_DATA         VARCHAR2(3)
 INMEMORY         VARCHAR2(8)
 INMEMORY_PRIORITY        VARCHAR2(8)
 INMEMORY_DISTRIBUTE        VARCHAR2(15)
 INMEMORY_COMPRESSION        VARCHAR2(17)
 INMEMORY_DUPLICATE        VARCHAR2(13)
CREATE PLUGGABLE DATABASE  db02_clone FROM joords02@noncdb_clone_link
CREATE_FILE_DEST = '/home/oracle/app/oracle/data01/joords01';
SELECT name, open_mode FROM v$pdbs ;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
DB01_CLONE         READ WRITE
DB02_CLONE         MOUNTED
SELECT message, action
FROM pdb_plug_in_violations
WHERE name = 'DB02_CLONE';
MESSAGE
--------------------------------------------------------------------------------
ACTION
--------------------------------------------------------------------------------
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
Run noncdb_to_pdb.sql.
ALTER SESSION SET CONTAINER=db02_clone;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
ALTER PLUGGABLE DATABASE db02_clone OPEN;
select name , open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
DB02_CLONE                     READ WRITE
ALTER SESSION SET CONTAINER=CDB$ROOT;
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
DB01_CLONE                     READ WRITE
DB02_CLONE                     READ WRITE