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.
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;
  /

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
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 PDB
SQL> select name from v$pdbs@pdb1k_link;

NAME
------------------------------
PDB1K
If 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                       MOUNTED
Finally open the PDB
SQL> 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 NO
Verify the PDB data files are created in the intended location
SQL>  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_.dbf
Using 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                           ONLINE
Only 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/MISSING00122
Offline 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 list
SQL> 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_.dbf
Undo 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 use
Offline 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
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.0
Options 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.0
Both 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 imm
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 compatible
As 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.08
Plug 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                         MOUNTED
Chane to the PDB container and run noncdb_to_pdb.sql script
SQL> ALTER SESSION SET CONTAINER=se2pdb;
SQL> show con_name;

CON_NAME
------------------------------
SE2PDB

SQL> @?/rdbms/admin/noncdb_to_pdb.sql
When 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 NO
PDB 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_.dbf
The 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 DB
SQL> select instance_name from v$instance@std_link;

INSTANCE_NAME
----------------
std12c1
As 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                         MOUNTED
Change to the newly created PDB, run the noncdb_to_pdb script and finally open the PDB
SQL> 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 NO
Similar 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                       OFFLINE
This 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_.dbf
Useful 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