I am aggressively preparing for demos for my 2 hour deep dive session at IOUG Collaborate 2014: Session 974: 04/08/14 – 01:45 PM – 04:00 PM (Level 3, Lido 3101B) Extreme Oracle DB-Infrastructure-As-A-Service. Co-presenting with me with me will be Nitin Vengurlekar

We will cover all the topics from Linux as a Service to RAC as a Service to ASM as a Service and finish at Database as a Service.

From a RAC perspective, here’s a sample screen of what we will discuss. We have similar screen shots for ASM, Data Guard, RMAN and Linux:

[oracle@rac01 rac]$ ./rac
# ————————————————————————- #
# RAC Menu System – rac-clust
# ————————————————————————- #
# First Node: rac01 VIP: rac01-vip.viscosity.com
# Second Node: rac02 VIP: rac02-vip.viscosity.com
# ————————————————————————- #
# 00. Sync DBI Scripts Across All RAC Nodes #
# ————————————————————————- #
# 01. Prepare Source RAC Cluster for Cloning (sudo) #
# Will shutdown the Cluster and Unlock the /u01/app/12.1.0/grid Home
# 02. Lock the Grid Home: /u01/app/12.1.0/grid (sudo)
# ————————————————————————- #
# 03. Prepare Oracle Directories (sudo) #
# ————————————————————————- #
# 04. Extract GI Home from Tarball (sudo) #
# 05. Extract DB Home from Tarball (sudo) #
# ————————————————————————- #
# 20. Setup SSH User Equivalence for All RAC Nodes #
# ————————————————————————- #
# 30. Cleanup and Deconfig Submenu (sudo) #
# ————————————————————————- #
# 40. Clone Grid Infrastructure – /u01/app/12.1.0/grid
# 41. Run orainstRoot.sh and root.sh serially on all RAC nodes #
# ————————————————————————- #
# 50. Execute config.sh in silent mode #
# ————————————————————————- #
# 60. Create DATA and FRA diskgroups
# ————————————————————————- #
# 70. Clone Database Home – /u01/app/oracle/product/12.1.0/dbhome_1
# ————————————————————————- #
# 80. Create RAC Database – VPROD
# ————————————————————————- #
# 100. Post Database Tasks #
# ————————————————————————- #
# x. Exit #
# ————————————————————————- #
# Enter Task Number:


04/08/14 – 12:30pm (Level 3, Toscana 3701) IOUG Cloud SIG meeting

Session 974: 04/08/14 – 01:45 PM – 04:00 PM (Level 3, Lido 3101B) Extreme Oracle DB-Infrastructure-As-A-Service
This session will be a 2hr Deep-Dive. Co-presenting with me with me will be Nitin Vengurlekar

Session 623: 04/09/14 12:15 PM – 12:45 PM (Level 3, Lido 3101B) Zero to Complete DBaaS in 1 hour

04/10/14 – 11:00am (Level 3, Lido 3101B,) Best Practices and Skill Sets

Session 444: 4/11/14 11:00 AM – 12:00 PM (Level 3, Lido 3101B) Who? What? Why? Oracle on Oracle’s Public Cloud – 360 Degrees

Posted by Charles Kim, Oracle ACE Director


VExpert2014

Honored to make vExpert for the second consecutive year. Congratulations to all vExpert 2014. I am proud to be part of this group.

We have 754 vExperts this year, which is impressive! Each of these vExperts have demonstrated significant contributions to the community and a willingness to share their expertise with others.


It’s time for the the annual IOUG Collaborate Conference again, April 7-11 in Las Vegas at the Venetian and Sands Expo Center.

We have a line up of great tracks and speakers focused on Cloud Computing, and this is a mini-compilation of the sessions focused on Cloud Tracks.

Enjoy and look forward to meeting everyone at Collaborate (#C14LV).

Best Wishes,

Charles Kim and the Cloud SIG Team (George, Bert, Kai, Ron, Steve).


Oracle’s Application Express platform (APEX) has emerged as the most compelling RAD platform available in the Oracle ecosystem. It is easy to learn, provides broadly useful functionality and is free to develop and use for existing Oracle customers. However, it has never been positioned as a “serious” development platform for mission critical enterprise (and certainly not commercial) product development. After all, it’s free and it doesn’t support Java. How important a platform could it be whose language underpinnings are SQL and PL/SQL? Why would I trust a platform for critical product development that’s not encouraged or even considered for such things by its own sales force?

Yet development trends are telling a different story. Organizations across industry boundaries and of every size and profile increasingly rely on APEX’s predictable ROI to create not just internal one-off applications and utilities but full-scale, enterprise-critical and even commercial cloud-based offerings.

Come to this session and see how we are leveraging APEX with FOEX as the preferred enterprise development tool and how it can be used to create compelling applications across full spectrums of criticality and enterprise expectation.


Let’s look at the step-by-step procedures to create a VMware template from an existing golden image VM. The following URL to the PDF will demonstrate the the steps to templatize a VM and to provision a new VM from the newly created template:

VMware Clone to Template and Deploy Virtual Machine from this Template

Our concept of templatization does not stop at the VM. We have to create a template of the Grid Infrastructure and Database Home binaries. After we install Oracle Database 11.2.0.3 or 11.2.0.4, we will apply the latest (N-1) PSU to both the Grid Infrastructure and Database Homes and required one-off patches as needed. For example, customers who have implemented GoldenGate may have to apply patches for the ACFS and/or for Integrated Extracts. Once we establish what we conceive to be the golden image for the Grid Infrastructure and Database software stack, we will create a Tar archives of both homes.


We are assuming that you have already installed kmod-oracleasm and oracleasm-support RPMs with yum:

 
# yum install kmod-oracleasm -y
# yum install oracleasm-support -y

For Red Hat Linux, you can download kmod-oracleasm from their support site. Check out my previous blog on where to download kmod-oracleasm for Red Hat 6.4 and above.

oracleasmlib is not available from the default yum repository. You can pull the oracleasmlib RPM from Oracle’s ASMLIB page for Oracle Linux 6:

[root@rac01 software]# rpm -ihv oracleasmlib-2.0.4-1.el6.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]

After we install the RPMs, we need to configure ASMLIB to scan immediately and to re-start on reboot for the Oracle user.

[root@rac01 software]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: 
Writing Oracle ASM library driver configuration: done

As the final step in the process, we need to initialize ASMLIB and confirm that it was successfully started:

 
[root@rac01 software]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size 
Mounting ASMlib driver filesystem: /dev/oracleasm

[root@rac01 software]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

Posted by Charles Kim, Oracle ACE Director



SQL> alter session set container=PSOFT;

Session altered.

SQL>  @?/rdbms/admin/noncdb_to_pdb.sql
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> 
SQL> WHENEVER SQLERROR EXIT;
SQL> 
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01403: no data found"
DOC>   error if we're not in a PDB.
DOC>   This script is intended to be run right after plugin of a PDB,
DOC>   while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> 
SQL> VARIABLE cdbname VARCHAR2(128)
SQL> VARIABLE pdbname VARCHAR2(128)
SQL> BEGIN
  2    SELECT sys_context('USERENV', 'CDB_NAME')
  3      INTO :cdbname
  4      FROM dual
  5      WHERE sys_context('USERENV', 'CDB_NAME') is not null;
  6    SELECT sys_context('USERENV', 'CON_NAME')
  7      INTO :pdbname
  8      FROM dual
  9      WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> 
SQL> COLUMN pdbname NEW_VALUE pdbname
SQL> COLUMN pdbid NEW_VALUE pdbid
SQL> 
SQL> select :pdbname pdbname from dual;

PDBNAME
--------------------------------------------------------------------------------
PSOFT

1 row selected.

SQL> 
SQL> select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname';
old   1: select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname'
new   1: select TO_CHAR(con_id) pdbid from v$pdbs where name='PSOFT'

PDBID
----------------------------------------
4

1 row selected.

SQL> 
SQL> -- save pluggable database open mode
SQL> COLUMN open_state_col NEW_VALUE open_sql;
SQL> COLUMN restricted_col NEW_VALUE restricted_state;
SQL> SELECT decode(open_mode,
  2                'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
  3                'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')
  4           open_state_col,
  5         decode(restricted, 'YES', 'RESTRICTED', '')
  6           restricted_col
  7         from v$pdbs where name='&pdbname';
old   2:               'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
new   2:               'READ ONLY', 'ALTER PLUGGABLE DATABASE PSOFT OPEN READ ONLY',
old   3:               'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')
new   3:               'READ WRITE', 'ALTER PLUGGABLE DATABASE PSOFT OPEN', '')
old   7:        from v$pdbs where name='&pdbname'
new   7:        from v$pdbs where name='PSOFT'

OPEN_STATE_COL                                RESTRICTED
--------------------------------------------- ----------


1 row selected.

SQL> 
SQL> -- save value for _system_trig_enabled parameter
SQL> COLUMN sys_trig NEW_VALUE sys_trig_enabled  NOPRINT;
SQL> SELECT parm_values.ksppstvl as sys_trig
  2     FROM sys.x$ksppi parms, sys.x$ksppsv parm_values
  3     WHERE parms.ksppinm = '_system_trig_enabled' AND
  4           parms.inst_id = USERENV('Instance') AND
  5           parms.indx = parm_values.indx;




1 row selected.

SQL> 
SQL> -- if pdb was already closed, don't exit on error
SQL> WHENEVER SQLERROR CONTINUE;
SQL> 
SQL> alter pluggable database "&pdbname" close;
old   1: alter pluggable database "&pdbname" close
new   1: alter pluggable database "PSOFT" close
alter pluggable database "PSOFT" close
*
ERROR at line 1:
ORA-65020: pluggable database PSOFT already closed


SQL> 
SQL> WHENEVER SQLERROR EXIT;
SQL> 
SQL> alter session set container = CDB$ROOT;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter session set container = "&pdbname";
old   1: alter session set container = "&pdbname"
new   1: alter session set container = "PSOFT"

Session altered.

SQL> 
SQL> alter pluggable database "&pdbname" open restricted;
old   1: alter pluggable database "&pdbname" open restricted
new   1: alter pluggable database "PSOFT" open restricted


Warning: PDB altered with errors.

SQL> 
SQL> -- initial setup before beginning the script
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> alter session set "_NONCDB_TO_PDB"=true;

Session altered.

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

SQL> ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.

SQL> exec dbms_pdb.noncdb_to_pdb(1);

PL/SQL procedure successfully completed.

SQL> 
SQL> -- if we're plugging in a database that had been upgraded, we need to:
SQL> -- 1) generate signatures for common tables
SQL> -- 2) patch up tables that with column order differences. This can happen due
SQL> --    to db creation scripts adding columns to the middle of a table vs
SQL> --    upgrade scripts adding to the end via ALTER TABLE ADD
SQL> 
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> 
SQL> -- create temporary object-linked view to get list of objects marked as common
SQL> -- in CDB$ROOT
SQL> create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
  2  select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,
  3         o.subname object_subname, o.signature object_sig,
  4         decode(bitand(o.flags, 65536), 65536, 'MDL', 'OBL') sharing
  5    from sys.obj$ o, sys.user$ u where
  6    o.owner#=u.user# and bitand(o.flags, 196608) <> 0;
old   1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
new   1: create or replace view sys.cdb$common_root_objects4 sharing=object as

View created.

SQL> 
SQL> -- object-linked view for list of common users
SQL> create or replace view sys.cdb$common_users&pdbid sharing=object as
  2  select name from sys.user$ where bitand(spare1, 128) <> 0;
old   1: create or replace view sys.cdb$common_users&pdbid sharing=object as
new   1: create or replace view sys.cdb$common_users4 sharing=object as

View created.

SQL> 
SQL> -- object-linked view for accessing dependency$
SQL> create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#;
old   1: create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#
new   1: create or replace view sys.cdb$rootdeps4 sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#

View created.

SQL> 
SQL> -- switch into PDB
SQL> alter session set container="&pdbname";
old   1: alter session set container="&pdbname"
new   1: alter session set container="PSOFT"

Session altered.

SQL> 
SQL> create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
  2  select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,
  3         o.subname object_subname, o.signature object_sig,
  4         decode(bitand(o.flags, 65536), 65536, 'MDL', 'OBL') sharing
  5    from sys.obj$ o, sys.user$ u where
  6    o.owner#=u.user# and bitand(o.flags, 196608) <> 0;
old   1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
new   1: create or replace view sys.cdb$common_root_objects4 sharing=object as

View created.

SQL> 
SQL> create or replace view sys.cdb$common_users&pdbid sharing=object as
  2  select name from sys.user$ where bitand(spare1, 128) <> 0;
old   1: create or replace view sys.cdb$common_users&pdbid sharing=object as
new   1: create or replace view sys.cdb$common_users4 sharing=object as

View created.

SQL> 
SQL> create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#;
old   1: create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#
new   1: create or replace view sys.cdb$rootdeps4 sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#

View created.

SQL> 
SQL> create or replace view sys.cdb$objects&pdbid sharing=none as
  2  select u.name owner, o.name object_name, o.signature object_sig, o.namespace nsp,
  3         o.subname object_subname, o.obj# object_id, o.type# object_type, o.flags flags
  4    from sys.obj$ o, sys.user$ u
  5    where o.owner#=u.user#;
old   1: create or replace view sys.cdb$objects&pdbid sharing=none as
new   1: create or replace view sys.cdb$objects4 sharing=none as

View created.

SQL> 
SQL> create or replace view sys.cdb$tables&pdbid sharing=none as
  2  select * from sys.cdb$objects&pdbid where object_type=2;
old   1: create or replace view sys.cdb$tables&pdbid sharing=none as
new   1: create or replace view sys.cdb$tables4 sharing=none as
old   2: select * from sys.cdb$objects&pdbid where object_type=2
new   2: select * from sys.cdb$objects4 where object_type=2

View created.

SQL> 
SQL> 
SQL> ---------------------------------------------------------------------------
SQL> -- PRE-SCRIPT CHECKS GO HERE:
SQL> 
SQL> set serveroutput on
SQL> 
SQL> -- Check that we have no invalid table data
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>     The following statement will cause an "ORA-01722: invalid number"
DOC>     error, if the database contains invalid data as a result of type
DOC>     evolution which was performed without the data being converted.
DOC>
DOC>     To resolve this specific "ORA-01722: invalid number" error:
DOC>       Perform the data conversion (details below) in the pluggable database.
DOC>
DOC>     Please refer to Oracle Database Object-Relational Developer's Guide
DOC>     for more information about type evolution.
DOC>
DOC>     Data in columns of evolved types must be converted before the
DOC>     database can be converted.
DOC>
DOC>     The following commands, run inside the PDB, will perform the data
DOC>     conversion for Oracle supplied data:
DOC>
DOC>     @?/rdbms/admin/utluppkg.sql
DOC>     SET SERVEROUTPUT ON;
DOC>     exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA');
DOC>     SET SERVEROUTPUT OFF;
DOC>
DOC>     You should then confirm that any non-Oracle supplied data is also
DOC>     converted.  You should review the data and determine if it needs
DOC>     to be converted or removed.
DOC>
DOC>     To view the data that is affected by type evolution, execute the
DOC>     following inside the PDB:
DOC>
DOC>     SELECT rpad(u.name,128) TABLENAME, rpad(o.name,128) OWNER,
DOC>       rpad(c.name,128) COLNAME FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t,
DOC>         SYS.USER$ u
DOC>         WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL#
DOC>           AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256
DOC>           AND o.OWNER# = u.USER# AND o.OWNER# NOT IN
DOC>            (SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, SYS.REGISTRY$ r
DOC>               WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE='SERVER');
DOC>
DOC>     Once the data is confirmed, the following commands, run inside the PDB,
DOC>     will convert the data returned by the above query.
DOC>
DOC>     @?/rdbms/admin/utluppkg.sql
DOC>     SET SERVEROUTPUT ON;
DOC>     exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');
DOC>     SET SERVEROUTPUT OFF;
DOC>
DOC>     Depending on the amount of data involved, converting the evolved type
DOC>     data can take a significant amount of time.
DOC>
DOC>     After this is complete, please rerun noncdb_to_pdb.sql.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> 
SQL> declare
  2    do_abort boolean := false;
  3  begin
  4    if dbms_preup.condition_exists ('INVALID_SYS_TABLEDATA') then
  5      -- dump out the info
  6      dbms_preup.run_check('INVALID_SYS_TABLEDATA');
  7      do_abort := TRUE;
  8    end if;
  9    if dbms_preup.condition_exists ('INVALID_USR_TABLEDATA') THEN
 10      -- dump out the info
 11      dbms_preup.run_check('INVALID_USR_TABLEDATA');
 12      do_abort := TRUE;
 13    END IF;
 14    If do_abort THEN
 15      dbms_output.put_line ('Invalid table data.');
 16      dbms_output.put_line ('Non-CDB conversion aborting.');
 17      dbms_output.put_line ('For instructions, look for ORA-01722 in this script.');
 18      dbms_output.put_line ('Please resolve these and rerun noncdb_to_pdb.sql.');
 19      RAISE INVALID_NUMBER;
 20    end if;
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL> 
SQL> -- END PRE-SCRIPT CHECKS
SQL> ---------------------------------------------------------------------------
SQL> 
SQL> -- mark users and roles in our PDB as common if they exist as common in ROOT
SQL> DECLARE
  2    cursor c is
  3      select p.user# from sys.cdb$common_users&pdbid r, sys.user$ p
  4      where r.name=p.name and bitand(p.spare1, 128)=0;
  5  BEGIN
  6    FOR u in c
  7    LOOP
  8      BEGIN
  9        execute immediate 'update sys.user$ set spare1=spare1+128 where user#=' ||
 10                          u.user#;
 11      END;
 12    END LOOP;
 13    commit;
 14  END;
 15  /
old   3:     select p.user# from sys.cdb$common_users&pdbid r, sys.user$ p
new   3:     select p.user# from sys.cdb$common_users4 r, sys.user$ p

PL/SQL procedure successfully completed.

SQL> 
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DYDD-MO
------------------------
Tue 18-Feb-2014 11:55:34

1 row selected.

SQL> 
SQL> -- mark objects in our PDB as common if they exist as common in ROOT
SQL> DECLARE
  2    cursor c is
  3      select p.object_id, p.flags-bitand(p.flags, 196608) flags,
  4             decode(r.sharing, 'MDL', 65536, 131072) sharing_flag
  5        from sys.cdb$common_root_objects&pdbid r, sys.cdb$objects&pdbid p
  6      where r.owner=p.owner and r.object_name=p.object_name
  7        and r.object_type=p.object_type and r.nsp=p.nsp
  8        and (p.object_subname is null and r.object_subname is null
  9             or r.object_subname=p.object_subname)
 10        and decode(bitand(p.flags, 196608), 65536, 'MDL', 131072, 'OBL', 'NONE')<>r.sharing;
 11  BEGIN
 12    FOR obj in c
 13    LOOP
 14      BEGIN
 15        execute immediate 'update sys.obj$ set flags=' || (obj.flags + obj.sharing_flag) ||
 16                          ' where obj#=' || obj.object_id;
 17      END;
 18    END LOOP;
 19    commit;
 20  END;
 21  /
old   5:       from sys.cdb$common_root_objects&pdbid r, sys.cdb$objects&pdbid p
new   5:       from sys.cdb$common_root_objects4 r, sys.cdb$objects4 p

PL/SQL procedure successfully completed.

SQL> 
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DYDD-MO
------------------------
Tue 18-Feb-2014 11:55:35

1 row selected.

SQL> 
SQL> -- generate signatures for the common tables which don't have them
SQL> DECLARE
  2    cursor c is
  3      select r.owner, r.object_name
  4        from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
  5      where r.owner=p.owner and r.object_name=p.object_name
  6        and r.object_type=2 and p.object_sig is null
  7        and p.object_name not in ('OBJ$', 'USER$');
  8  BEGIN
  9    FOR tab in c
 10    LOOP
 11      BEGIN
 12        execute immediate 'ALTER TABLE ' || tab.owner || '."' ||
 13                          tab.object_name || '" UPGRADE';
 14      EXCEPTION
 15        WHEN OTHERS THEN
 16        BEGIN
 17          IF (sqlcode = -600 or sqlcode = -602 or sqlcode = -603) THEN
 18            raise;
 19          END IF;
 20        END;
 21      END;
 22    END LOOP;
 23    commit;
 24  END;
 25  /
old   4:       from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
new   4:       from sys.cdb$common_root_objects4 r, sys.cdb$tables4 p

PL/SQL procedure successfully completed.

SQL> 
SQL> -- for each table whose signature doesn't match ROOT's, mark its PL/SQL
SQL> -- dependents for local MCode
SQL> DECLARE
  2    cursor c is
  3      select obj#
  4        from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
  5      where o.type# <> 4 and u.name=ro.owner and u.user#=o.owner#
  6        and o.name=ro.object_name and o.type#=ro.object_type and obj# in
  7        (select d_obj# from sys.dependency$ where p_obj# in
  8          (select p.object_id from sys.CDB$common_root_objects&pdbid r,
  9                                   sys.cdb$tables&pdbid p
 10           where r.owner=p.owner and r.object_name=p.object_name
 11             and r.object_type=2 and r.object_sig <> p.object_sig));
 12  BEGIN
 13    FOR obj in c
 14    LOOP
 15      execute immediate
 16        'update sys.obj$ set flags=flags+33554432-bitand(flags, 33554432) where obj#=' || obj.obj#;
 17    END LOOP;
 18    commit;
 19  END;
 20  /
old   4:       from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
new   4:       from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects4 ro
old   8:         (select p.object_id from sys.CDB$common_root_objects&pdbid r,
new   8:         (select p.object_id from sys.CDB$common_root_objects4 r,
old   9:                                  sys.cdb$tables&pdbid p
new   9:                                  sys.cdb$tables4 p

PL/SQL procedure successfully completed.

SQL> 
SQL> select owner#, name from sys.obj$ where bitand(flags, 33554432)=33554432
  2    order by 1, 2;

no rows selected

SQL> 
SQL> -- Step (II)
SQL> --
SQL> -- Mark all metadata links as status 6
SQL> -- skip types w/ non-null subname
SQL> update sys.obj$ set status = 6
  2          where (type# not in (2, 28, 29, 30, 56))
  3          and (type# <> 13 or subname is null)
  4          and status not in (5,6)
  5          and bitand(flags, 65536)=65536;

49537 rows updated.

SQL> 
SQL> commit
  2  /

Commit complete.

SQL> 
SQL> -- Invalidate all synonym dependents of dbms_standard. If not we will end up
SQL> -- with a timestamp mismatch between dependency  and obj
SQL> 
SQL> update sys.obj$ set status=6 where obj# in
  2  (select d_obj# from sys.dependency$
  3   where p_obj# in (select obj# from sys.obj$ where name='DBMS_STANDARD' and
  4                    type# in ( 9, 11) and owner#=0)
  5  ) and type#=5
  6  /

1 row updated.

SQL> commit
  2  /

Commit complete.

SQL> 
SQL> alter system flush shared_pool
  2  /

System altered.

SQL> 
SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> 
SQL> -- Step (II)
SQL> --
SQL> -- Recreate package standard and dbms_standard. This is needed to execute
SQL> -- subsequent anonymous blocks
SQL> SET ECHO OFF

Session altered.


Session altered.


Package created.


Session altered.


Session altered.


Package body created.


Session altered.


Grant succeeded.


Session altered.


Session altered.


Package created.


Synonym created.


Grant succeeded.


Session altered.

SQL> -- Step (III)
SQL> --
SQL> -- Invalidate views and synonyms which depend (directly or indirectly) on
SQL> -- invalid objects.
SQL> begin
  2    loop
  3      update sys.obj$ o_outer set status = 6
  4      where     type# in (4, 5)
  5            and status not in (5, 6)
  6            and linkname is null
  7            and ((subname is null) or (subname <> 'DBMS_DBUPGRADE_BABY'))
  8            and exists (select o.obj# from sys.obj$ o, sys.dependency$ d
  9                        where     d.d_obj# = o_outer.obj#
 10                              and d.p_obj# = o.obj#
 11                              and (bitand(d.property, 1) = 1)
 12                              and o.status > 1);
 13      exit when sql%notfound;
 14    end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 
SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> 
SQL> -- normalize dependencies for classes.bin objects
SQL> delete from sys.dependency$ where d_obj# in (select obj# from sys.obj$ where bitand(flags,65600)=65600);

3054 rows deleted.

SQL> 
SQL> insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ po,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=referenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600);
old   1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ po,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=referenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)
new   1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ po,sys.user$ pu,sys.cdb$rootdeps4 rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=referenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)

3054 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> -- get rid of idl_ub1$ rows for MDL java objects
SQL> delete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56));

32729 rows deleted.

SQL> commit;

Commit complete.

SQL> 
SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> 
SQL> -- explicitly compile these now, before close/reopen. Otherwise they would
SQL> -- be used/validated within PDB Open, where such patching (clearing of dict
SQL> -- rows) can't be done.
SQL> alter public synonym ALL_OBJECTS compile;

Synonym altered.

SQL> alter view V_$PARAMETER compile;

View altered.

SQL> 
SQL> WHENEVER SQLERROR CONTINUE;
SQL> alter type ANYDATA compile;

Type altered.

SQL> WHENEVER SQLERROR EXIT;
SQL> 
SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> 
SQL> -- reopen the PDB
SQL> alter pluggable database "&pdbname" close;
old   1: alter pluggable database "&pdbname" close
new   1: alter pluggable database "PSOFT" close

Pluggable database altered.

SQL> alter pluggable database "&pdbname" open restricted;
old   1: alter pluggable database "&pdbname" open restricted
new   1: alter pluggable database "PSOFT" open restricted

Warning: PDB altered with errors.

SQL> ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.

SQL> 
SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL> 
SQL> select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

    STATUS   COUNT(*)
---------- ----------
         5          1
         6      49520

2 rows selected.

SQL> select count(*) from sys.view$;

  COUNT(*)
----------
      6177

1 row selected.

SQL> select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
  2    (select obj# from sys.view$);

  COUNT(*)
----------
      6030

1 row selected.

SQL> select count(*) from sys.procedure$;

  COUNT(*)
----------
      3614

1 row selected.

SQL> select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
  2    (select obj# from sys.procedure$);

  COUNT(*)
----------
      3601

1 row selected.

SQL> select count(*) from sys.dir$;

  COUNT(*)
----------
         9

1 row selected.

SQL> select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
  2    (select obj# from sys.dir$);

  COUNT(*)
----------
         4

1 row selected.

SQL> 
SQL> -- @@utlrp
SQL> 
SQL> select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

    STATUS   COUNT(*)
---------- ----------
         5          1
         6      49520

2 rows selected.

SQL> select count(*) from sys.view$;

  COUNT(*)
----------
      6177

1 row selected.

SQL> select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
  2    (select obj# from sys.view$);

  COUNT(*)
----------
      6030

1 row selected.

SQL> select count(*) from sys.procedure$;

  COUNT(*)
----------
      3614

1 row selected.

SQL> select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
  2    (select obj# from sys.procedure$);

  COUNT(*)
----------
      3601

1 row selected.

SQL> select count(*) from sys.dir$;

  COUNT(*)
----------
         9

1 row selected.

SQL> select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
  2    (select obj# from sys.dir$);

  COUNT(*)
----------
         4

1 row selected.

SQL> 
SQL> -- mark old version types as valid, as utlrp skips these
SQL> update sys.obj$ set status = 1
  2    where type#=13 and subname is not null and status > 1;

0 rows updated.

SQL> commit;

Commit complete.

SQL> 
SQL> alter pluggable database "&pdbname" close;
old   1: alter pluggable database "&pdbname" close
new   1: alter pluggable database "PSOFT" close

Pluggable database altered.

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter pluggable database "&pdbname" open restricted;
old   1: alter pluggable database "&pdbname" open restricted
new   1: alter pluggable database "PSOFT" open restricted

Warning: PDB altered with errors.

SQL> ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.

SQL> 
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> drop view sys.cdb$tables&pdbid;
old   1: drop view sys.cdb$tables&pdbid
new   1: drop view sys.cdb$tables4

View dropped.

SQL> drop view sys.cdb$objects&pdbid;
old   1: drop view sys.cdb$objects&pdbid
new   1: drop view sys.cdb$objects4

View dropped.

SQL> drop view sys.cdb$common_root_objects&pdbid;
old   1: drop view sys.cdb$common_root_objects&pdbid
new   1: drop view sys.cdb$common_root_objects4

View dropped.

SQL> drop view sys.cdb$common_users&pdbid;
old   1: drop view sys.cdb$common_users&pdbid
new   1: drop view sys.cdb$common_users4

View dropped.

SQL> drop view sys.cdb$rootdeps&pdbid;
old   1: drop view sys.cdb$rootdeps&pdbid
new   1: drop view sys.cdb$rootdeps4

View dropped.

SQL> 
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> drop view sys.cdb$common_root_objects&pdbid;
old   1: drop view sys.cdb$common_root_objects&pdbid
new   1: drop view sys.cdb$common_root_objects4

View dropped.

SQL> drop view sys.cdb$common_users&pdbid;
old   1: drop view sys.cdb$common_users&pdbid
new   1: drop view sys.cdb$common_users4

View dropped.

SQL> drop view sys.cdb$rootdeps&pdbid;
old   1: drop view sys.cdb$rootdeps&pdbid
new   1: drop view sys.cdb$rootdeps4

View dropped.

SQL> 
SQL> alter session set container="&pdbname";
old   1: alter session set container="&pdbname"
new   1: alter session set container="PSOFT"

Session altered.

SQL> 
SQL> -- handle Resource Manager plan conversions
SQL> exec dbms_rmin.rm$_noncdb_to_pdb;

PL/SQL procedure successfully completed.

SQL> 
SQL> -- delete SYS$BACKGROUND and SYS$USERS from service$
SQL> delete from sys.service$ where name in ('SYS$BACKGROUND', 'SYS$USERS');

2 rows deleted.

SQL> commit;

Commit complete.

SQL> 
SQL> -- reset the parameters at the end of the script
SQL> exec dbms_pdb.noncdb_to_pdb(2);

PL/SQL procedure successfully completed.

SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL> alter session set "_NONCDB_TO_PDB"=false;

Session altered.

SQL> ALTER SYSTEM SET "_system_trig_enabled"=&sys_trig_enabled SCOPE=MEMORY;
old   1: ALTER SYSTEM SET "_system_trig_enabled"=&sys_trig_enabled SCOPE=MEMORY
new   1: ALTER SYSTEM SET "_system_trig_enabled"=TRUE SCOPE=MEMORY

System altered.

SQL> 
SQL> alter pluggable database "&pdbname" close;
old   1: alter pluggable database "&pdbname" close
new   1: alter pluggable database "PSOFT" close

Pluggable database altered.

SQL> alter session set container = CDB$ROOT;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter session set container = "&pdbname";
old   1: alter session set container = "&pdbname"
new   1: alter session set container = "PSOFT"

Session altered.

SQL> 
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /
old   2:   execute immediate '&open_sql &restricted_state';
new   2:   execute immediate ' ';

PL/SQL procedure successfully completed.

SQL> 
SQL> WHENEVER SQLERROR CONTINUE;



Here’s an sample output of the PDB manifest XML file from the dbms_pdb.describe procedure. This XML output is referenced in the IOUG white papers and Database Infrastructure As A Service eBook.

	rac01:/u01/app/oracle/oradata
	PSOFT1 > cat psoft.xml 
	<?xml version="1.0" encoding="UTF-8"?>
	<PDB>
	  <pdbname>PSOFT</pdbname>
	  <cid>0</cid>
	  <byteorder>1</byteorder>
	  <vsn>202375168</vsn>
	  <dbid>1834925412</dbid>
	  <cdbid>1834925412</cdbid>
	  <guid>F2B1597C0F080518E0430A2910AC7076</guid>
	  <uscnbas>1813749</uscnbas>
	  <uscnwrp>0</uscnwrp>
	  <rdba>4194824</rdba>
	  <tablespace>
		<name>SYSTEM</name>
		<type>0</type>
		<tsn>0</tsn>
		<status>1</status>
		<issft>0</issft>
		<file>
		  <path>+DATA/PSOFT/DATAFILE/system.293.839843843</path>
		  <afn>1</afn>
		  <rfn>1</rfn>
		  <createscnbas>7</createscnbas>
		  <createscnwrp>0</createscnwrp>
		  <status>1</status>
		  <fileblocks>98560</fileblocks>
		  <blocksize>8192</blocksize>
		  <vsn>202375168</vsn>
		  <fdbid>1834925412</fdbid>
		  <fcpsw>0</fcpsw>
		  <fcpsb>1813748</fcpsb>
		  <frlsw>0</frlsw>
		  <frlsb>1720082</frlsb>
		  <frlt>839843942</frlt>
		</file>
	  </tablespace>
	  <tablespace>
		<name>SYSAUX</name>
		<type>0</type>
		<tsn>1</tsn>
		<status>1</status>
		<issft>0</issft>
		<file>
		  <path>+DATA/PSOFT/DATAFILE/sysaux.292.839843787</path>
		  <afn>3</afn>
		  <rfn>3</rfn>
		  <createscnbas>3922</createscnbas>
		  <createscnwrp>0</createscnwrp>
		  <status>1</status>
		  <fileblocks>89600</fileblocks>
		  <blocksize>8192</blocksize>
		  <vsn>202375168</vsn>
		  <fdbid>1834925412</fdbid>
		  <fcpsw>0</fcpsw>
		  <fcpsb>1813748</fcpsb>
		  <frlsw>0</frlsw>
		  <frlsb>1720082</frlsb>
		  <frlt>839843942</frlt>
		</file>
	  </tablespace>
	  <tablespace>
		<name>TEMP</name>
		<type>1</type>
		<tsn>3</tsn>
		<status>1</status>
		<issft>0</issft>
		<bmunitsize>128</bmunitsize>
		<file>
		  <path>+DATA/PSOFT/TEMPFILE/temp.299.839843947</path>
		  <afn>1</afn>
		  <rfn>1</rfn>
		  <createscnbas>1720203</createscnbas>
		  <createscnwrp>0</createscnwrp>
		  <status>1</status>
		  <fileblocks>7680</fileblocks>
		  <blocksize>8192</blocksize>
		  <vsn>202375168</vsn>
		  <autoext>1</autoext>
		  <maxsize>4194302</maxsize>
		  <incsize>80</incsize>
		</file>
	  </tablespace>
	  <tablespace>
		<name>USERS</name>
		<type>0</type>
		<tsn>4</tsn>
		<status>1</status>
		<issft>0</issft>
		<file>
		  <path>+DATA/PSOFT/DATAFILE/users.294.839843899</path>
		  <afn>6</afn>
		  <rfn>6</rfn>
		  <createscnbas>26029</createscnbas>
		  <createscnwrp>0</createscnwrp>
		  <status>1</status>
		  <fileblocks>640</fileblocks>
		  <blocksize>8192</blocksize>
		  <vsn>202375168</vsn>
		  <fdbid>1834925412</fdbid>
		  <fcpsw>0</fcpsw>
		  <fcpsb>1813748</fcpsb>
		  <frlsw>0</frlsw>
		  <frlsb>1720082</frlsb>
		  <frlt>839843942</frlt>
		</file>
	  </tablespace>
	  <optional>
		<csid>178</csid>
		<ncsid>2000</ncsid>
		<options>
		  <option>APS=12.1.0.1.0</option>
		  <option>CATALOG=12.1.0.1.0</option>
		  <option>CATJAVA=12.1.0.1.0</option>
		  <option>CATPROC=12.1.0.1.0</option>
		  <option>CONTEXT=12.1.0.1.0</option>
		  <option>DV=12.1.0.1.0</option>
		  <option>JAVAVM=12.1.0.1.0</option>
		  <option>OLS=12.1.0.1.0</option>
		  <option>ORDIM=12.1.0.1.0</option>
		  <option>OWM=12.1.0.1.0</option>
		  <option>RAC=12.1.0.1.0</option>
		  <option>SDO=12.1.0.1.0</option>
		  <option>XDB=12.1.0.1.0</option>
		  <option>XML=12.1.0.1.0</option>
		  <option>XOQ=12.1.0.1.0</option>
		</options>
		<olsoid>0</olsoid>
		<dv>0</dv>
		<ncdb2pdb>1</ncdb2pdb>
		<APEX>4.2.0.00.27:1</APEX>
		<parameters>
		  <parameter>processes=300</parameter>
		  <parameter>memory_target=843055104</parameter>
		  <parameter>db_block_size=8192</parameter>
		  <parameter>compatible=12.1.0.0.0</parameter>
		  <parameter>cluster_database=TRUE</parameter>
		  <parameter>open_cursors=300</parameter>
		</parameters>
		<tzvers>
		  <tzver>primary version:18</tzver>
		  <tzver>secondary version:0</tzver>
		</tzvers>
		<walletkey>0</walletkey>
	  </optional>
	</PDB>