declare file_name varchar2(100) := 'data.log'; db_dir_name varchar2(100) := 'LOADF'; dest_loc BLOB := empty_blob(); src_loc BFILE := BFILENAME(db_dir_name, file_name); destoff number := 1; srcoff number :=1; begin DBMS_LOB.OPEN(src_loc, DBMS_LOB.FILE_READONLY); DBMS_LOB.CREATETEMPORARY(lob_loc => dest_loc, cache => false); DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADBLOBFROMFILE (dest_loc,src_loc,DBMS_LOB.getLength(src_loc),destoff,srcoff); insert into lobins values (dest_loc); commit; DBMS_LOB.CLOSE(dest_loc); DBMS_LOB.CLOSE(src_loc); end; /
Wednesday, September 28, 2016
Insert a File as BLOB Using DBMS_LOB
Following script could be used to insert a file as a BLOB into a table. The file to be inserted must reside in the location referred by the database directory LOADF. File name in this case data.log. File is inserted to table called lobins which only has single column which is of BLOB type.
Sunday, September 18, 2016
Remote Cloning of a PDB
Similar to non-CDB, PDB too could cloned over a remote link. In this case both source and remote DBs are CDBs and one PDB is cloned on the local DB. As the first step create a TNS entry and a link on the local DB. The remote PDB is called PDB1K
Put the source PDB into read only mode. Refer oracle doc for full list of pre-reqs. Create the PDB, the new PDB is named PDB1KRMT.
This clause does not apply to the SYSTEM, SYSAUX, or TEMP tablespaces.
Related Posts
Move a PDB Between Servers
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link
PDB1KTNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1k)
)
)
SQL> create database link pdb1k_link connect to system identified by system using 'PDB1KTNS';Validate the link by querying a view on the remote PDBSQL> select name from v$pdbs@pdb1k_link; NAME ------------------------------ PDB1KIf OMF is used nothing else is needed and PDB could be cloned. However in this case a data files of the remotely cloned PDBs are stored separately. To achieve that set the db_create_dest parameter to desired location with scope set to memory.
SQL> alter system set db_create_file_dest='/opt/app/oracle/oradata/remoteclones' scope=memory; SQL> show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /opt/app/oracle/oradata/remoteclones
Put the source PDB into read only mode. Refer oracle doc for full list of pre-reqs. Create the PDB, the new PDB is named PDB1KRMT.
SQL> create pluggable database pdb1krmt from pdb1k@pdb1k_link;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 PDB1KRMT MOUNTEDFinally open the PDBSQL> alter pluggable database pdb1krmt open;
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 PDB1KRMT READ WRITE NOVerify the PDB data files are created in the intended locationSQL> select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------ /opt/app/oracle/oradata/CGCDB/datafile/o1_mf_undotbs1_cvchzywd_.dbf /opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_system_cwfq6dd5_.dbf /opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_sysaux_cwfq6ddt_.dbf /opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_pdb1ktbs_cwfq6ddv_.dbfUsing the USER_TABLESPACES clause available on 12.1.0.2 it is possible to clone the new PDB only with a subset of tablespaces. Assume that original PDB has 3 application specific tablespaces.
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE TEMP ONLINE APP1 ONLINE APP2 ONLINE APP3 ONLINEOnly 2 of them are wanted in the newly cloned PDB. It is possible to include just these two tablespaces in the user_tablespaces clause excluding all other tablespaces.
create pluggable database pdb1krmt from pdb1k@pdb1k_link USER_TABLESPACES=('APP1','APP3');Tablespace name exists but status will be offline with data file missing as well.SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE TEMP ONLINE APP1 ONLINE APP2 OFFLINE APP3 ONLINE SQL> select tablespace_name,status,file_name from dba_data_files; TABLESPACE_NAME STATUS FILE_NAME ------------------------------ --------- ---------------------------------------------------------------------------------------------------- SYSTEM AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_system_cx0bpkwo_.dbf SYSAUX AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_sysaux_cx0bpkwy_.dbf APP1 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_app1_cx0bpkwz_.dbf APP3 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_app3_cx0bpkx1_.dbf APP2 AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00122Offline tablespace could be dropped to clean up the new PDB
SQL> drop tablespace app2 including contents and datafiles cascade constraints;Same could be done when plugging non-CDB as PDBs as well.
SQL> create pluggable database stdpdb from std12c1@std_link USER_TABLESPACES=('APP1','APP3');Run the post cloning steps and verify the tablespace listSQL> select tablespace_name,status from dba_tablespaces order by 2,1; TABLESPACE_NAME STATUS ------------------------------ --------- APP2 OFFLINE TOOLS OFFLINE UNDOTBS1 OFFLINE USERS OFFLINE APP1 ONLINE APP3 ONLINE SYSAUX ONLINE SYSTEM ONLINE TEMP ONLINE SQL> select tablespace_name,status,file_name from dba_data_files; TABLESPACE_NAME STATUS FILE_NAME ------------------------------ --------- ---------------------------------------------------------------------------------------------------- SYSTEM AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_system_cx060v17_.dbf SYSAUX AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_sysaux_cx060v18_.dbf USERS AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00114 TOOLS AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00115 APP1 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_app1_cx060v1b_.dbf APP2 AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00117 APP3 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_app3_cx060v1c_.dbfUndo tablespace within the PDB cannot be removed (2067414.1).
SQL> drop tablespace UNDOTBS1 including contents and datafiles cascade constraints; drop tablespace UNDOTBS1 including contents and datafiles cascade constraints * ERROR at line 1: ORA-30013: undo tablespace 'UNDOTBS1' is currently in useOffline undo tablespace in this case is the undo tablespace on non-CDB. This is because the CDB undo tablespace name and cloned non-CDB tablespace name is the same and undo is not local to PDB but common to entire CDB. It was not possible to get rid of the undotbs1 offline status even after switching the default undo tablespace of the CDB to a different undo tablespace.
This clause does not apply to the SYSTEM, SYSAUX, or TEMP tablespaces.
Related Posts
Move a PDB Between Servers
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link
Thursday, September 8, 2016
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link
This post list two methods for plugging a non-CDB on standard edition 2 (SE2) as a pluggable database on enterprise edition (EE) CDB. One is using a XML description file which also includes copying the data files to (if EE database reside in another server). Other method is using a remote link. Reverse of this process, that is EE plugged into SE2 is not possible according to 1631260.1. This is because SE2 has less options than EE. But as long as the options in the SE2 are a subset of EE, moving from SE2 to EE is possible (2020172.1). Current options on the SE2 are
Method 1. File Copying
Start the SE2 in read only mode, create the non-CDB descriptor file and shutdown.
Method 2. Remote Link
In this method a remote link created from the EE CDB to SE2 non-CDB and used to create the PDB.
Unlike the method 1, in method 2 the undo tablespace within the PDB will be offline.
"OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS [ID 2020172.1]
Unplug an Enterprise Edition PDB and Plug into a Standard Edition CDB says not compatible when using DBMS_PDB.CHECK_PLUG_COMPATIBILITY [ID 1631260.1]
Related Post
Move a PDB Between Servers
SQL> select comp_id,comp_name,status,version from dba_registry; COMP_ID COMP_NAME STATUS VERSION ---------- ---------------------------------------- -------- ---------- CONTEXT Oracle Text VALID 12.1.0.2.0 OWM Oracle Workspace Manager VALID 12.1.0.2.0 XDB Oracle XML Database VALID 12.1.0.2.0 CATALOG Oracle Database Catalog Views VALID 12.1.0.2.0 CATPROC Oracle Database Packages and Types VALID 12.1.0.2.0Options on the EE are
SQL> select comp_id,comp_name,status,version from dba_registry; COMP_ID COMP_NAME STATUS VERSION ---------- ---------------------------------------- ---------- --------------- DV Oracle Database Vault VALID 12.1.0.2.0 APEX Oracle Application Express VALID 4.2.5.00.08 OLS Oracle Label Security VALID 12.1.0.2.0 SDO Spatial VALID 12.1.0.2.0 ORDIM Oracle Multimedia VALID 12.1.0.2.0 CONTEXT Oracle Text VALID 12.1.0.2.0 OWM Oracle Workspace Manager VALID 12.1.0.2.0 XDB Oracle XML Database VALID 12.1.0.2.0 CATALOG Oracle Database Catalog Views VALID 12.1.0.2.0 CATPROC Oracle Database Packages and Types VALID 12.1.0.2.0 JAVAVM JServer JAVA Virtual Machine VALID 12.1.0.2.0 XML Oracle XDK VALID 12.1.0.2.0 CATJAVA Oracle Database Java Packages VALID 12.1.0.2.0 APS OLAP Analytic Workspace VALID 12.1.0.2.0 XOQ Oracle OLAP API VALID 12.1.0.2.0 RAC Oracle Real Application Clusters OPTION OFF 12.1.0.2.0Both SE2 and EE has been patched to the same level.
Method 1. File Copying
Start the SE2 in read only mode, create the non-CDB descriptor file and shutdown.
SQL> startup mount exclusive; SQL> alter database open read only; SQL> exec dbms_pdb.describe(pdb_descr_file=>'/home/oracle/backup/se2_noncdb_desc.xml'); SQL> shutdown immediate;Copy the descriptor file the data files to the location where EE CDB resides
scp -C se2_noncdb_desc.xml 192.168.0.99:/home/oracle/backup/ cd /data/oradata/STD12C1/datafile/ scp -C * 192.168.0.99:/home/oracle/backup/ o1_mf_sysaux_byjg3ypy_.dbf 100% 550MB 25.0MB/s 00:22 o1_mf_system_byjg3q5k_.dbf 100% 700MB 25.0MB/s 00:28 ...On the EE CDB run a compatibility check against the descriptor file
set serveroutput on
declare
compa boolean;
begin
compa := dbms_pdb.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/home/oracle/backup/se2_noncdb_desc.xml');
if compa = true then
dbms_output.put_line('compatible');
else
dbms_output.put_line('no compatible');
end if;
end;
/
no compatibleAs seen from the output the compatibility fails. Look in the PDB_PLUG_IN_VIOLATIONS and if the cause for warnings are due to missing options then the incompatibility is ignore-able (2020172.1). SQL> select name,cause,status,type,action from PDB_PLUG_IN_VIOLATIONS where name='STDPDB' AND STATUS <> 'RESOLVED'; NAME CAUSE STATUS TYPE ACTION -------- ---------- --------- --------- ------------------------------------------------------------ STDPDB OPTION PENDING WARNING Fix the database option in the PDB or the CDB STDPDB OPTION PENDING WARNING Fix the database option in the PDB or the CDB STDPDB APEX PENDING WARNING Please contact Oracle Support. SQL> select status,message from PDB_PLUG_IN_VIOLATIONS where name='STDPDB' AND STATUS <> 'RESOLVED'; STATUS MESSAGE --------- ---------------------------------------------------------------------------------------------------- PENDING Database option APS mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option CATJAVA mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option DV mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option JAVAVM mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option OLS mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option ORDIM mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option XML mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING Database option XOQ mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0. PENDING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08Plug the SE2 non-CDB using the descriptor file
SQL> create pluggable database se2pdb as clone using '/home/oracle/backup/se2_noncdb_desc.xml'
source_file_name_convert=('/data/oradata/STD12C1/datafile','/home/oracle/backup') move;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 SE2PDB MOUNTEDChane to the PDB container and run noncdb_to_pdb.sql scriptSQL> ALTER SESSION SET CONTAINER=se2pdb; SQL> show con_name; CON_NAME ------------------------------ SE2PDB SQL> @?/rdbms/admin/noncdb_to_pdb.sqlWhen the script completes open the PDB
SQL> alter pluggable database open;
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 SE2PDB READ WRITE NOPDB opens without any restriction and is ready to use. EE CDB was setup using OMF, as such the plugged SE2 DB's datafile and tempfile will be created in a OMF structure with a new GUID. SQL> select name from v$tempfile; NAME ---------------------------------------------------------------------------------------------------- /opt/app/oracle/oradata/CGCDB/3B61A02E318577C4E0536300A8C051DC/datafile/o1_mf_temp_cwg1lqd0_.dbf 1 row selected. SQL> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- /opt/app/oracle/oradata/CGCDB/datafile/o1_mf_undotbs1_cvchzywd_.dbf /opt/app/oracle/oradata/CGCDB/3B61A02E318577C4E0536300A8C051DC/datafile/o1_mf_system_cwg1lqcv_.dbf /opt/app/oracle/oradata/CGCDB/3B61A02E318577C4E0536300A8C051DC/datafile/o1_mf_sysaux_cwg1lqcz_.dbf /opt/app/oracle/oradata/CGCDB/3B61A02E318577C4E0536300A8C051DC/datafile/o1_mf_users_cwg1lqd0_.dbf /opt/app/oracle/oradata/CGCDB/3B61A02E318577C4E0536300A8C051DC/datafile/o1_mf_tools_cwg1lqd1_.dbfThe files associated with the undo and temp tablespaces on the non-CDB will not be used in the plugged in PDB.
Method 2. Remote Link
In this method a remote link created from the EE CDB to SE2 non-CDB and used to create the PDB.
stdtns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.66)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std12c1)
)
)
SQL> create database link std_link connect to system identified by system using 'STDTNS';
Database link created.Validate the link by running a query against the remote SE2 DBSQL> select instance_name from v$instance@std_link; INSTANCE_NAME ---------------- std12c1As OMF is used no other steps are needed to create the PDB. Put the source non-CDB in to read only mode and then run the create PDB statement
SQL> create pluggable database stdpdb from std12c1@std_link;
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 STDPDB MOUNTEDChange to the newly created PDB, run the noncdb_to_pdb script and finally open the PDBSQL> ALTER SESSION SET CONTAINER=stdpdb;
SQL> show con_name;
CON_NAME
------------------------------
STDPDB
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> alter pluggable database open;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 STDPDB READ WRITE NOSimilar to method 1 and plugging violation due to missing options are ignore-able. Unlike the method 1, in method 2 the undo tablespace within the PDB will be offline.
SQL> select tablespace_name,status from dba_tablespaces order by 2,1; TABLESPACE_NAME STATUS ------------------------------ --------- UNDOTBS1 OFFLINEThis is due to the fact that both non-CDB and CDB where it's plugged having the same name for undo tablespace and undo tablespaces are not local to PDB but common to entire CDB. Unfortunately it's not possible to drop this within the PDB (2067414.1). However querying data files within the PDB shows the CDB's undo tablespace's data file
SQL> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- /opt/app/oracle/oradata/CGCDB/datafile/o1_mf_undotbs1_cvchzywd_.dbf /opt/app/oracle/oradata/CGCDB/3BEEE6493046715CE0536300A8C0B4F0/datafile/o1_mf_system_cx0l5krb_.dbf /opt/app/oracle/oradata/CGCDB/3BEEE6493046715CE0536300A8C0B4F0/datafile/o1_mf_sysaux_cx0l5krf_.dbfUseful Metlink Notes
"OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS [ID 2020172.1]
Unplug an Enterprise Edition PDB and Plug into a Standard Edition CDB says not compatible when using DBMS_PDB.CHECK_PLUG_COMPATIBILITY [ID 1631260.1]
Related Post
Move a PDB Between Servers
Thursday, September 1, 2016
Move a PDB Between Servers
This post list the steps for moving a PDB from it's current CDB to another CDB on a different server. The detonation CDB and the PDB are patched to the same level.
Destination CDB patches
The source CDB has three PDBs and the PDB called "PDBTHREE" will be moved.
The destination uses OMF but different paths for PDBs and the root container files. This path is also different from the OMF path at the source CDB. Login to the CDB and set the db_create_file_dest used for PDBs (if a separate OMF path is used for PDBs) and create the PDB with clone and move clause. source_file_name_convert has been used to specify the file mapping where ('/opt/app/oracle/oradata/APCDB/3AE6245AE81713AFE0536300A8C02D00/datafile') refers to PDB data file path at source and ('/home/oracle/backup/pdbthree') refers to the location where data files were copied into.
Related Posts
Remote Cloning of a PDB
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link
Destination CDB patches
SQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 1 order by action_time;
CON_ID PATCH_ID ACTION STATUS DESCRIPTION
---------- ---------- --------------- --------------- ----------------------------------------------------------------------------------------------------
1 21555660 APPLY SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
1 21359755 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.5 (21359755)
1 21555660 ROLLBACK SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
1 22674709 APPLY SUCCESS Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
1 22291127 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127)PBD patchesSQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 5 order by action_time;
CON_ID PATCH_ID ACTION STATUS DESCRIPTION
---------- ---------- --------------- --------------- ----------------------------------------------------------------------------------------------------
5 21555660 APPLY SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
5 21359755 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.5 (21359755)
5 21555660 ROLLBACK SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
5 22674709 APPLY SUCCESS Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
5 22291127 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127)As seen from above outputs both destination CDB and PDB has gone through the same set of patches and are currently at the same patch level.The source CDB has three PDBs and the PDB called "PDBTHREE" will be moved.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 PDBTHREE READ WRITE NOClose the PBD and unplug it from the source CDB by specifying the metadata xml file.SQL> alter pluggable database pdbthree close immediate;
Pluggable database altered.
SQL> alter pluggable database pdbthree unplug into '/home/oracle/backup/pdbthree_desc.xml';
Pluggable database altered.
SQL> select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
---------- ---------
PDBTHREE UNPLUGGED
PDB$SEED NORMAL
ONEPDB NORMAL
TWOPDB NORMAL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 PDBTHREE MOUNTEDDrop the PDB with keep file option (default)SQL> drop pluggable database pdbthree keep datafiles;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NOCopy the files over to the destination server. The location of the file is not the same as source location.[oracle@hpc5 datafile]$ pwd /opt/app/oracle/oradata/APCDB/3AE6245AE81713AFE0536300A8C02D00/datafile [oracle@hpc5 datafile]$ scp -C * hpc1:/home/oracle/backup/pdbthree/ oracle@192.168.0.66's password: o1_mf_pdbthree_cvy3w4f7_.dbf 100% 10MB 10.0MB/s 00:00 o1_mf_sysaux_cvxvnvnw_.dbf 100% 580MB 24.2MB/s 00:24 o1_mf_system_cvxvnvnl_.dbf 100% 260MB 18.6MB/s 00:14On the destination CDB run the compatibility check. In this case the output says that PDB is not compatible.
SQL> SET SERVEROUTPUT ON DECLARE SQL> 2 compatible CONSTANT VARCHAR2(3) := 3 CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/backup/pdbthree_desc.xml', pdb_name => 'PDBTHREE') 4 WHEN TRUE THEN 'YES' 5 ELSE 'NO' 6 END; 7 BEGIN 8 DBMS_OUTPUT.PUT_LINE(compatible); 9 END; 10 / NOChecking the plugin violation view shows the following (last two lines has been shortened)
SQL> select name,cause,status,type,action from PDB_PLUG_IN_VIOLATIONS; NAME CAUSE STATUS TYPE ACTION ---------- --------------- --------- --------- -------------------------------------------------- PDBTHREE SQL patch error PENDING ERROR Call datapatch to reinstall PDBTHREE SQL patch error PENDING ERROR Call datapatch to reinstall PDBTHREE SQL Patch PENDING ERROR Call datapatch to install in the PDB or the CDB PDBTHREE SQL Patch PENDING ERROR Call datapatch to install in the PDB or the CDB SQL> select status,message from PDB_PLUG_IN_VIOLATIONS; STATUS MESSAGE --------- ------------------------------------------------------------------------------------------------------------------------------------------------------ PENDING (PSU bundle patch 160419 (Database Patch Set Update : 12.1.0.2.160419 (22291127)): APPLY SUCCESS): with status in the PDB. PENDING (SQL patch ID/UID 22674709/20057886 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)): APPLY SUCCESS): with status in the PDB. PENDING ... Update : 12.1.0.2.160419 (22291127)): Installed in the CDB but not in the PDB. PENDING ... Oracle JavaVM Component (Apr2016)): Installed in the CDB but not in the PDB.Since it has been confirmed that both destination CDB and PDB being plugged in are both patched to same level this violation is ignored.
The destination uses OMF but different paths for PDBs and the root container files. This path is also different from the OMF path at the source CDB. Login to the CDB and set the db_create_file_dest used for PDBs (if a separate OMF path is used for PDBs) and create the PDB with clone and move clause. source_file_name_convert has been used to specify the file mapping where ('/opt/app/oracle/oradata/APCDB/3AE6245AE81713AFE0536300A8C02D00/datafile') refers to PDB data file path at source and ('/home/oracle/backup/pdbthree') refers to the location where data files were copied into.
SQL> alter system set db_create_file_dest='/ssdoracle/oradata/pdbs' scope=memory;
System altered.
SQL> create pluggable database pdbthree as clone using '/home/oracle/backup/pdbthree_desc.xml'
source_file_name_convert=('/opt/app/oracle/oradata/APCDB/3AE6245AE81713AFE0536300A8C02D00/datafile','/home/oracle/backup/pdbthree') move;
Pluggable database created.
SQL> select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
--------------- ---------
PDBTHREE NEW
PDB$SEED NORMAL
ONEPDB NORMAL
TWOPDB NORMAL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 PDBTHREE MOUNTED
SQL> select con_id,name from v$datafile where con_id=5;
CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
5 /ssdoracle/oradata/pdbs/APCDB/3AE70A7E4DC630F7E0534200A8C0F281/datafile/o1_mf_system_cvxzo517_.dbf
5 /ssdoracle/oradata/pdbs/APCDB/3AE70A7E4DC630F7E0534200A8C0F281/datafile/o1_mf_sysaux_cvxzo529_.dbf
5 /ssdoracle/oradata/pdbs/APCDB/3AE70A7E4DC630F7E0534200A8C0F281/datafile/o1_mf_pdbthree_cvxzo52b_.dbfFinally open the PDBSQL> alter pluggable database pdbthree open;
Pluggable database altered.
SQL> select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
--------------- ---------
PDBTHREE NORMAL
PDB$SEED NORMAL
ONEPDB NORMAL
TWOPDB NORMAL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONEPDB READ WRITE NO
4 TWOPDB READ WRITE NO
5 PDBTHREE READ WRITE NOThe CDB doesn't list any more plugin violationsSQL> select name,cause,status,type,action from PDB_PLUG_IN_VIOLATIONS; no rows selectedVerify the patch levels are reflected
SQL> alter session set container=pdbthree;
Session altered.
SQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 5 order by action_time;
CON_ID PATCH_ID ACTION STATUS DESCRIPTION
---------- ---------- ---------- --------------- ----------------------------------------------------------------------------------------------------
5 21555660 APPLY SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
5 21359755 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.5 (21359755)
5 21555660 ROLLBACK SUCCESS Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
5 22674709 APPLY SUCCESS Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
5 22291127 APPLY SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127)This concludes the steps for moving a PDB between servers.Related Posts
Remote Cloning of a PDB
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link
Subscribe to:
Posts (Atom)
