Saturday, October 1, 2016

Moving PDB from RAC CDB to Single Instance CDB

The RAC CDB and the non-RAC CDB are both 12.1.0.2 SE2 instances. Currently the single instance CDB doesn't have any PDB plugged on it. SE2 CDBs only support single tenancy due to license restriction. The RAC CDB has a PDB available on both instances.
INSTANCE     NAME         OPEN_MODE  STATUS    RES
------------ ------------ ---------- --------- ---
stdcdb1      PDB$SEED     READ ONLY  NORMAL    NO
stdcdb1      STDPDB       READ WRITE NORMAL    NO
stdcdb2      PDB$SEED     READ ONLY  NORMAL    NO
stdcdb2      STDPDB       READ WRITE NORMAL    NO
Both CDBs has same components except for RAC components which is set not available on the single instance CDB. DB registry of the RAC CDB
SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         VALID
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID
DB Registry of the non-RAC CDB
SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         OPTION OFF
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID
Process of moving the PDB is similar to earlier post except for few RAC and ASM related tasks. To summarize, before the PDB could be moved from currently plugged in CDB, it must be stopped, unplugged and dropped. The unplugging will create a PDB descriptor file and on in the DB file names will appear in lower case while rest of the path appear upper case.
cat stdpdb_desc.xml | grep path
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/system.291.894734477
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/sysaux.292.894734481
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/TEMPFILE/temp.295.922121143
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/users.293.894734495
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/tapp.294.894734495
However when data files are copied out of ASM using asmcmd they will have the names in upper case (e.g. SYSTEM.291.894734477). As a result plugging will fail as it is unable to identify the file name
create pluggable database stdpdb as clone using '/home/oracle/stdcdb/datafiles/stdpdb_desc.xml'
*
ERROR at line 1:
ORA-19505: failed to identify file "/home/oracle/stdcdb/datafiles/system.291.894734477"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

SQL> ! ls "/home/oracle/stdcdb/datafiles/system.291.894734477"
ls: /home/oracle/stdcdb/datafiles/system.291.894734477: No such file or directory

SQL> ! ls -l /home/oracle/stdcdb/datafiles/*
-rw-r--r-- 1 oracle oinstall      6925 Sep 12 10:47 /home/oracle/stdcdb/datafiles/stdpdb_desc.xml
-rw-r----- 1 oracle oinstall 550510592 Sep 12 10:44 /home/oracle/stdcdb/datafiles/SYSAUX.292.894734481
-rw-r----- 1 oracle oinstall 293609472 Sep 12 10:45 /home/oracle/stdcdb/datafiles/SYSTEM.291.894734477
-rw-r----- 1 oracle oinstall  20979712 Sep 12 10:45 /home/oracle/stdcdb/datafiles/TAPP.294.894734495
-rw-r----- 1 oracle oinstall   5251072 Sep 12 10:45 /home/oracle/stdcdb/datafiles/USERS.293.894734495
So before the plugging rename the file names same as on descriptor file.
 mv SYSTEM.291.894734477 system.291.894734477
 mv SYSAUX.292.894734481 sysaux.292.894734481
 mv TAPP.294.894734495 tapp.294.894734495
 mv USERS.293.894734495 users.293.894734495
Also in ASM datafiles and tempfiles have separate paths, as a result the tempfile location must also specified in the source_file_name convert even though file itself is not necessary to be copied to new location. Temp file get dropped when the PDB is dropped even though keep data file options is used.
drop pluggable database stdpdb keep datafiles
Deleted Oracle managed file +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/TEMPFILE/temp.295.922364799
Completed: drop pluggable database stdpdb keep datafiles


So the final command to plug the PDB into single instance will have two file name convert entires, one for data files and another for temp files
create pluggable database stdpdb as clone using '/home/oracle/stdcdb/datafiles/stdpdb_desc.xml' 
source_file_name_convert=(
'+DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE','/home/oracle/stdcdb/datafiles',
'+DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/TEMPFILE','/home/oracle/stdcdb/datafiles') move;
Once the PDB is plugged open it
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 STDPDB                         MOUNTED

SQL> alter pluggable database stdpdb open;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 STDPDB                         READ WRITE NO

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------------------------------------
/ssdoracle/oradata/STDCDB/datafile/o1_mf_undotbs1_cx2j8d05_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_system_cxf6fp5g_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_sysaux_cxf6fp5h_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_users_cxf6fp5j_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_tapp_cxf6fp5k_.dbf
Looking at the PDB violation view will show the warning related to missing RAC option.
SQL> select name,status,action,message from PDB_PLUG_IN_VIOLATIONS where cause='OPTION';

NAME     STATUS    ACTION                                             MESSAGE
-------- --------- -------------------------------------------------- -------------------------------------------------------------------------------------------
STDPDB   PENDING   Fix the database option in the PDB or the CDB      Database option RAC mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
Login into the PDB and turn off the RAC options
SQL> alter session set container=stdpdb;

SQL> show con_name

CON_NAME
---------
STDPDB

SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         VALID
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID

11 rows selected.

SQL> exec dbms_registry.OPTION_OFF('RAC');

SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         OPTION OFF
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID
Close and open the PDB. The PDB violation view will show the issue has been resolved.
SQL> select name,status,action,message from PDB_PLUG_IN_VIOLATIONS where cause='OPTION';

NAME     STATUS    ACTION                                             MESSAGE
-------- --------- -------------------------------------------------- -------------------------------------------------------------------------------------------
STDPDB   RESOLVED  Fix the database option in the PDB or the CDB      Database option RAC mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
This concludes the moving of PDB from RAC CDB to single instance CDB.

Related Posts
Move a PDB Between Servers
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link

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