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
- 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
No comments:
Post a Comment