Steps to move an oracle 11.2.0.3 'non cdb' into a 12c container database

This post describes how to move an 11.2.0.3 database into a 12.1 PDB. Anything that wants to plug in to the new style architecture has to already be at oracle 12.1 or you have to logically migrate
it into 12.1 (using datapump for example). You cannot plug in an 11.2 (or any other earlier version) into 12.1. An upgrade to 12.1 in the 'normal' way is required. Actually even the 'normal' upgrade from 11.1 to 12.1 is slightly different to what you may be used to (at least when using command line as i am here).

To start with lets create an 11.2 database in the simplest way possible (be prepared to be amazed - i was the first time i ran this in its simplest syntax form). As always i alias s as "sqlplus / as sysdba"

So first up lets create a (very) basic pfile for the database i'll call TEST2


[oracle@server-name]:TEST2:[/oracle/11.2.0.3.1.DB/dbs]# vi initTEST2.ora
[oracle@server-name]:TEST2:[/oracle/11.2.0.3.1.DB/dbs]# cat initTEST2.ora
*.control_files='/oracle/TEST2/oradata/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oracle/TEST2/oradata'
*.db_name='TEST2'
*.db_recovery_file_dest='/oracle/TEST2/recovery_area'
*.db_recovery_file_dest_size=4G
*.pga_aggregate_target=209715200
*.sga_target=800M
*.sga_max_size=800M


As you can see there is very little in it. I then create some directories it will need (note the -p which creates all directories in the tree if the dont exist - quite a useful flag)

[oracle@server-name]:TEST2:[/oracle/11.2.0.3.1.DB/dbs]# mkdir -p /oracle/TEST2/oradata
[oracle@server-name]:TEST2:[/oracle/11.2.0.3.1.DB/dbs]# mkdir -p /oracle/TEST2/recovery_area

Now we log on and startup nomount to just read the pfile and create the instance (processes and memory)

[oracle@server-name]:TEST2:[/oracle/11.2.0.3.1.DB/dbs]# s

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 5 16:21:53 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@TEST2>startup nomount
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             230690176 bytes
Database Buffers          595591168 bytes
Redo Buffers                6590464 bytes
SYS@TEST2>


Then we create the database (and yes this really is all you type - quite a surprise when you first see it this condensed)

SYS@TEST2>create database;

Database created.

SYS@TEST2>


Then we run the normal catalog/catproc scripts to create dba views etc

SYS@TEST2>@?/rdbms/admin/catalog

SYS@TEST2>@?/rdbms/admin/catproc



Now we have our source system created we want to be able to plug it in but first we need to upgrade to 12c. I've done the minimum possible in the example below there may well be a lot more you need

to go through for real..... First we run the pre upgrade check:


[oracle@server-name]:TEST2:[~]# s

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 6 15:58:00 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SYS@TEST2>@/oracle/12.0.0/rdbms/admin/preupgrd
Loading Pre-Upgrade Package...
WARNING: Failed to open preupgrade.log for write access
    script will generate terminal output only
WARNING: Failed to open preupgrade_fixups.sql for write access
     script will not generate fixup scripts.
Executing Pre-Upgrade Checks...
Oracle Database Pre-Upgrade Information Tool 08-06-2013 15:58:39
Script Version: 12.1.0.1.0 Build: 006
**********************************************************************
   Database Name:  TEST2
         Version:  11.2.0.3.0
      Compatible:  11.2.0
       Blocksize:  8192
        Platform:  Linux x86 64-bit
   Timezone file:  V14
**********************************************************************
                          [Renamed Parameters]
                     [No Renamed Parameters in use]
**********************************************************************
**********************************************************************
                    [Obsolete/Deprecated Parameters]
             [No Obsolete or Desupported Parameters in use]
**********************************************************************
                            [Component List]
**********************************************************************
--> Oracle Catalog Views                   [upgrade]  VALID
--> Oracle Packages and Types              [upgrade]  VALID
**********************************************************************
                              [Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
     minimum required size: 641 MB
--> SYSAUX tablespace is adequate for the upgrade.
     minimum required size: 500 MB
--> SYS_UNDOTS tablespace is adequate for the upgrade.
     minimum required size: 400 MB

                      [No adjustments recommended]

**********************************************************************
**********************************************************************
                          [Pre-Upgrade Checks]
**********************************************************************
INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 11.2.0.3.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.


**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

**********************************************************************
                   ************  Summary  ************

 0 ERRORS exist in your database.
 0 WARNINGS exist in your database.
 1 INFORMATIONAL message that should be reviewed prior to your upgrade.

 After your database is upgraded and open in normal mode you must run
 rdbms/admin/catuppst.sql which executes several required tasks and completes
 the upgrade process.

 You should follow that with the execution of rdbms/admin/utlrp.sql, and a
 comparison of invalid objects before and after the upgrade using
 rdbms/admin/utluiobj.sql

 If needed you may want to upgrade your timezone data using the process
 described in My Oracle Support note 977512.1
                   ***********************************
Pre-Upgrade Checks Complete.
      ************************************************************

Results of the checks are located at:
 *** Scripts/Logs are not being Generated ***preupgrade.log


      ************************************************************
                   ====>> USER ACTION REQUIRED  <<====
      ************************************************************

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.

           You MUST resolve the above errors prior to upgrade

      ************************************************************

SYS@TEST2>


Then we shut it down

SYS@TEST2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@TEST2>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

Then we update oratab to 12.1 and reset the environment

[oracle@server-name]:TEST2:[~]# vi /etc/oratab
[oracle@server-name]:TEST2:[~]# . oraenv
ORACLE_SID = [TEST2] ? TEST2
The Oracle base remains unchanged with value /oracle
[oracle@server-name]:TEST2:[~]#
[oracle@server-name]:TEST2:[~]#
[oracle@server-name]:TEST2:[~]# echo $ORACLE_HOME
/oracle/12.0.0


ok now lets bring it up using v12 software (few trial and error things going here - remember the basics.....)

[oracle@server-name]:TEST2:[~]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 16:02:32 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/12.0.0/dbs/initTEST2.ora'
SQL> !cp /oracle/11.2.0.3.1.DB/dbs/initTEST2.ora /oracle/12.0.0/dbs/initTEST2.ora

SQL>  startup upgrade
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2293880 bytes
Variable Size             243273608 bytes
Database Buffers          583008256 bytes
Redo Buffers                6529024 bytes
ORA-00723: Initialization parameter COMPATIBLE must be explicitly set


SQL> !vi /oracle/12.0.0/dbs/initTEST2.ora

SQL> startup upgrade
ORA-01078: failure in processing system parameters
ORA-00400: invalid release value 12.0 for parameter compatible
SQL> !vi /oracle/12.0.0/dbs/initTEST2.ora

SQL>  startup upgrade
ORA-01078: failure in processing system parameters
ORA-00400: invalid release value 12.1 for parameter compatible
SQL>  !vi /oracle/12.0.0/dbs/initTEST2.ora

SQL>  startup upgrade
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort;
ORACLE instance shut down.
SQL>  startup upgrade
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2293880 bytes
Variable Size             243273608 bytes
Database Buffers          583008256 bytes
Redo Buffers                6529024 bytes
Database mounted.
Database opened.
SQL>



OK now database is ready to be upgraded - and here is where thigns are a little different from 11.2 and earlier - the scripts are now executed via perl (i think largely to help support containers

but also to speed things up as the work seems to be able to be parallelized at various points based on some metadata oracle have created in the sql files).

[oracle@server-name]:TEST2:[~]# cd $ORACLE_HOME/rdbms/admin
[oracle@server-name]:TEST2:[/oracle/12.0.0/rdbms/admin]# $ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l$ORACLE_HOME/diagnostics catupgrd.sql

Analyzing file catupgrd.sql
Log files in /oracle/12.0.0/diagnostics
14 scripts found in file catupgrd.sql
Next path: catalog.sql
32 scripts found in file catalog.sql
Next path: catproc.sql
37 scripts found in file catproc.sql
Next path: catptabs.sql
61 scripts found in file catptabs.sql
Next path: catpdbms.sql
205 scripts found in file catpdbms.sql
Next path: catpdeps.sql
77 scripts found in file catpdeps.sql
Next path: catpprvt.sql
260 scripts found in file catpprvt.sql
Next path: catpexec.sql
26 scripts found in file catpexec.sql
Next path: cmpupgrd.sql
16 scripts found in file cmpupgrd.sql

[Phase 0] type is 1 with 1 Files
catupstr.sql

[Phase 1] type is 1 with 3 Files
cdstrt.sql       cdfixed.sql      cdcore.sql

[Phase 2] type is 1 with 1 Files
ora_restart.sql

[Phase 3] type is 2 with 18 Files
cdplsql.sql      cdsqlddl.sql     cdmanage.sql     cdtxnspc.sql
cdenv.sql        cdrac.sql        cdsec.sql        cdobj.sql
cdjava.sql       cdpart.sql       cdrep.sql        cdaw.sql
cdsummgt.sql     cdtools.sql      cdexttab.sql     cddm.sql
catldr.sql       cdclst.sql

[Phase 4] type is 1 with 1 Files
ora_restart.sql

[Phase 5] type is 1 with 5 Files
cdoptim.sql      catsum.sql       catexp.sql       cddst.sql
cdend.sql

[Phase 6] type is 1 with 1 Files
catpstrt.sql

[Phase 7] type is 1 with 3 Files
catptyps.sql     catpgrants.sql   catgwm.sql

[Phase 8] type is 1 with 1 Files
ora_restart.sql

[Phase 9] type is 2 with 60 Files
catnodpt.sql     catbac.sql       prvtrctv.plb     catactx.sql
prvtuttv.plb     catsvrmg.sql     prvtlsis.plb     prvtlsss.plb
cattrans.sql     catrule.sql      catsnap.sql      catpitr.sql
catdip.sql       catrls.sql       catar.sql        catfga.sql
catamgt.sql      catidxu.sql      cattsm.sql       catchnf.sql
catodm.sql       catkppls.sql     catsscr.sql      catqueue.sql
cathae.sql       catadvtb.sql     catrm.sql        catsch.sql
catol.sql        catdpb.sql       catcrc.sql       dbmscr.sql
dbmsutil.sql     catdbfus.sql     catalrt.sql      catatsk.sql
catmntr.sql      catsqlt.sql      catawrtv.sql     catsmbvw.sql
catwrrtb.sql     catsumat.sql     catrep.sql       catlmnr.sql
catdef.sql       catadrvw.sql     catrepv.sql      catpexe.sql
cattlog.sql      catcapi.sql      catpspi.sql      catts.sql
catnacl.sql      catredact.sql    catproftab.sql   catpstdy.sql
catrupg.sql      catratmask.sql   catqitab.sql     catappcont.sql


[Phase 10] type is 1 with 1 Files
ora_restart.sql

[Phase 11] type is 1 with 1 Files
catpspec.sql

[Phase 12] type is 1 with 1 Files
ora_restart.sql

[Phase 13] type is 2 with 199 Files
utlinad.sql      utlsmtp.sql      utlurl.sql       utlenc.sql
utlgdk.sql       utlcstk.sql      utlcomp.sql      utli18n.sql
utllms.sql       dbmsplsw.sql     utlnla.sql       dbmspdb.sql
dbmstrns.sql     dbmsrwid.sql     dbmspclx.sql     dbmserlg.sql
dbmsspu.sql      dbmsapin.sql     dbmssyer.sql     dbmspipe.sql
dbmsalrt.sql     dbmsdesc.sql     dbmspexp.sql     dbmsjob.sql
dbmsstat.sql     dbmsstts.sql     dbmsddl.sql      dbmsedu.sql
dbmspp.sql       prvthddl.plb     prvthjob.plb     prvthsye.plb
prvtzhlp.plb     dbmsidxu.sql     prvthidx.plb     dbmspsp.sql
dbmstran.sql     dbmsxa.sql       dbmstxfm.sql     dbmsread.sql
prvtreut.plb     dbmspb.sql       dbmspbt.sql      dbmsplts.sql
dbmspitr.sql     utlrefld.sql     utlcoll.plb      dbmstrst.sql
dbmsrlsa.sql     dbmsrpr.sql      dbmsobtk.sql     dbmshtdb.sql
dbmslm.sql       dbmslmd.sql      prvtlmes.plb     utlcxml.sql
dbmsfga.sql      dbmsamgt.sql     dbmstypu.sql     dbmsres.sql
dbmstxin.sql     dbmsdrs.sql      dbmsdg.sql       dbmssum.sql
dbmshord.sql     dbmsxfr.sql      dbmsmap.sql      dbmsfi.sql
dbmsdbv.sql      dbmstcv.sql      dbmscoll.sql     dbmscdcu.sql
dbmscdcp.sql     dbmscdcs.sql     dbmspbp.sql      dbmshpro.sql
dbmssrv.sql      dbmschnf.sql     dbmsxpln.sql     utlmatch.sql
dbmsdbvn.sql     dbmspool.sql     dbmsrcad.sql     prvthcrc.plb
prvtkpps.plb     dbmsaq.plb       dbmsaqad.sql     dbmsaq8x.plb
dbmsaqem.plb     prvtaqxi.plb     dbmsslrt.sql     dbmsmntr.sql
dbmshm.sql       catsqltk.sql     dbmsir.sql       prvtsss.plb
dbmsocm.sql      dbmslobu.sql     dbmsmp.sql       dbmsaddm.sql
prvttxfs.plb     dbmsrmin.plb     dbmsrmad.sql     dbmsrmpr.sql
dbmsrmpe.plb     dbmsrmge.plb     dbmsrmpa.plb     prvtrmie.plb
prvthjob.plb     prvthesh.plb     dbmsol.sql       prvtdputh.plb
dbmsmeta.sql     dbmsmetb.sql     dbmsmetd.sql     dbmsmet2.sql
dbmsdp.sql       prvthpp.plb      prvthpd.plb      prvthpdi.plb
prvthpvi.plb     prvtdtde.plb     prvtsum.plb      prvtjdbs.plb
dbmsslxp.sql     prvssmgu.plb     dbmsawr.sql      prvsemxi.plb
prvsemx_admin.plb prvsemx_dbhome.plb prvsemx_memory.plb prvsemx_perf.plb
dbmsperf.sql     prvsrept.plb     prvsrepr.plb     prvshdm.plb
prvsrtaddm.plb   prvs_awr_data_cp.plb prvscpaddm.plb   prvsadv.plb
prvsawr.plb      prvsawri.plb     prvsawrs.plb     prvsash.plb
prvsawrv.plb     dbmssqlu.sql     prvssqlf.plb     dbmswrr.sql
dbmsfus.sql      prvsfus.plb      dbmsuadv.sql     dbmsrepl.sql
dbmsspm.sql      prvsspmi.plb     prvssmb.plb      prvssmbi.plb
dbmsstr.sql      dbmssqlt.sql     dbmsspa.sql      prvsautorepi.plb
dbmsautorep.sql  dbmsratmask.sql  dbmsdiag.sql     dbmsobj.sql
dbmskzxp.sql     dbmscu.sql       dbmsdst.sql      dbmscomp.sql
dbmsilm.sql      dbmspexe.sql     prvthpexei.plb   dbmscapi.sql
dbmsfuse.sql     dbmsfspi.sql     dbmspspi.sql     dbmsdnfs.sql
dbmsadr.sql      dbmsadra.sql     prvsadri.plb     xsrs.sql
xssc.sql         xsacl.sql        xsds.sql         xsns.sql
xsdiag.sql       xssess.sql       dbmsredacta.sql  dbmssqll.sql
dbmsgwm.sql      dbmsappcont.sql  dbmsspd.sql      prvsspdi.plb
dbmsfs.sql       dbmssqlm.sql     catprofp.sql     prvtsys.plb
dbmspart.sql     dbmsrupg.sql     dbmstsdp.sql

[Phase 14] type is 1 with 1 Files
ora_restart.sql

[Phase 15] type is 1 with 3 Files
dbmsmeti.sql     dbmsmetu.sql     dbmsqopi.sql

[Phase 16] type is 1 with 1 Files
ora_restart.sql

[Phase 17] type is 2 with 33 Files
catmettypes.sql  prvthdbu.plb     catost.sql       dbmshae.sql
catxpend.sql     prvtotpt.plb     prvthlut.plb     prvthlin.plb
prvthsdp.plb     dbmsrman.sql     dbmsbkrs.sql     dbmstrig.sql
dbmsrand.sql     dbmsjdwp.sql     catxs.sql        dbmssnap.sql
prvtxrmv.plb     depsaq.sql       prvthlrt.plb     catadv.sql
dbmscred.sql     catcredv.sql     cataqsch.sql     catrssch.sql
catplug.sql      prvtsql.plb      prvtssql.plb     prvtlmd.plb
prvtlmcs.plb     prvtlmrs.plb     dbmslms.sql      prvthpu.plb
prvthpv.plb

[Phase 18] type is 1 with 1 Files
ora_restart.sql

[Phase 19] type is 1 with 3 Files
prvtkupc.plb     prvtaqiu.plb     catlsby.sql

[Phase 20] type is 1 with 1 Files
ora_restart.sql

[Phase 21] type is 2 with 23 Files
catmetviews.sql  prvthpw.plb      prvthpm.plb      prvthpfi.plb
prvthpf.plb      dbmsodm.sql      prvtitrg.plb     prvtsms.plb
depssvrm.sql     deptxn.sql       catstr.sql       prvthsts.plb
prvthfgr.plb     prvthfie.plb     prvthcmp.plb     catpexev.sql
depscapi.sql     depspspi.sql     catwrrvw.sql     dbmsjdcu.sql
dbmsjdmp.sql     prvthpc.plb      prvt_awr_data.plb

[Phase 22] type is 1 with 1 Files
ora_restart.sql

[Phase 23] type is 2 with 11 Files
catmetgrant1.sql catldap.sql      prvtocm.sql      prvtrepl.sql
catpstr.sql      prvthpci.plb     catilm.sql       catemxv.sql
catnaclv.sql     dbmsnacl.sql     dbmswlm.sql

[Phase 24] type is 1 with 1 Files
ora_restart.sql

[Phase 25] type is 1 with 1 Files
catcdbviews.sql

[Phase 26] type is 1 with 1 Files
ora_restart.sql

[Phase 27] type is 2 with 0 Files

[Phase 28] type is 1 with 1 Files
ora_load_without_comp.sql

[Phase 29] type is 2 with 130 Files
prvtfile.plb     prvtrawb.plb     prvttcp.plb      prvtinad.plb
prvtsmtp.plb     prvthttp.plb     prvturl.plb      prvtenc.plb
prvtgdk.plb      prvtlob.plb      prvtlobu.plb     prvtcstk.plb
prvtcomp.plb     prvti18n.plb     prvtlms2.plb     prvtnla.plb
prvttrns.plb     prvtsess.plb     prvtrwid.plb     prvtpclx.plb
prvterlg.plb     prvtapin.plb     prvtsyer.plb     prvtlock.plb
prvtpipe.plb     prvtalrt.plb     prvtdesc.plb     prvtpexp.plb
prvtzexp.plb     prvtstts.plb     prvtddl.plb      prvtpp.plb
prvtscrp.plb     prvtkppb.plb     prvtutil.plb     prvtpsp.plb
prvttran.plb     prvtxa.plb       prvtany.plb      prvtread.plb
prvtpb.plb       prvtpbt.plb      prvtxpsw.plb     prvtcoll.plb
prvttrst.plb     prvtrlsa.plb     prvtodci.plb     prvtrpr.plb
prvtobtk.plb     prvthtdb.plb     prvtxmlt.plb     prvturi.plb
prvtxml.plb      prvtcxml.plb     prvtemxi.plb     prvtemx_admin.plb
prvtemx_dbhome.plb prvtemx_memory.plb prvtemx_perf.plb prvtperf.plb
prvtrep.plb      prvtrept.plb     prvtrepr.plb     prvtfga.plb
prvtamgt.plb     prvttypu.plb     prvtjdwp.plb     prvtjdmp.plb
prvtres.plb      prvtcr.plb       prvttxin.plb     prvtdrs.plb
prvtdg.plb       prvtfi.plb       prvtmap.plb      prvthpui.plb
prvtdbv.plb      prvttcv.plb      prvtpbp.plb      prvthpro.plb
prvtbdbu.plb     prvtsrv.plb      prvtpool.plb     prvtkzxs.plb
prvtkzxp.plb     prvtcrc.plb      prvtrc.plb       prvtaq.plb
prvtaqdi.plb     prvtaqxe.plb     prvtaqis.plb     prvtaqim.plb
prvtaqad.plb     prvtaq8x.plb     prvtaqin.plb     prvtaqal.plb
prvtaqjm.plb     prvtaqmi.plb     prvtaqme.plb     prvtaqem.plb
prvtaqip.plb     prvtaqds.plb     prvtsqdi.plb     prvtsqds.plb
prvtsqis.plb     prvthm.plb       prvtwlm.plb      prvtsqtk.plb
prvtkjhn.plb     prvtir.plb       prvtssb.plb      prvttxfm.plb
prvtrmin.plb     prvtrmad.plb     prvtrmpr.plb     prvtrmpe.plb
prvtrmge.plb     prvtrmpa.plb     prvtjob.plb      prvtbsch.plb
prvtesch.plb     prvtcred.plb     prvtol.plb       prvtlm.plb
prvtlmcb.plb     prvtlmrb.plb     prvtlms.plb      prvtlmeb.plb
prvtbpu.plb      prvtwrr.plb

[Phase 30] type is 1 with 1 Files
ora_load_with_comp.sql

[Phase 31] type is 1 with 1 Files
ora_restart.sql

[Phase 32] type is 1 with 1 Files
ora_load_without_comp.sql

[Phase 33] type is 2 with 122 Files
prvtbpui.plb     prvtdput.plb     prvtmeta.plb     prvtmeti.plb
prvtmetu.plb     prvtmetb.plb     prvtmetd.plb     prvtmet2.plb
prvtdp.plb       prvtbpc.plb      prvtbpci.plb     prvtbpw.plb
prvtbpm.plb      prvtbpfi.plb     prvtbpf.plb      prvtbpp.plb
prvtbpd.plb      prvtbpdi.plb     prvtbpv.plb      prvtbpvi.plb
prvtdpcr.plb     prvtplts.plb     prvtpitr.plb     prvtreie.plb
prvtrwee.plb     prvtidxu.plb     prvtrcmp.plb     prvtchnf.plb
prvtedu.plb      prvtlsby.plb     prvtlsib.plb     prvtlssb.plb
prvtsmv.plb      prvtsma.plb      prvtbxfr.plb     prvtbord.plb
prvtjdbb.plb     prvtslrt.plb     prvtslxp.plb     prvtatsk.plb
prvtmntr.plb     prvtsmgu.plb     prvtdadv.plb     prvtadv.plb
prvtawr.plb      prvtawrs.plb     prvtawri.plb     prvtash.plb
prvtawrv.plb     prvtsqlf.plb     prvtsqli.plb     prvtsqlt.plb
prvtautorepi.plb prvtautorep.plb  prvtfus.plb      prvtmp.plb
prvthdm.plb      prvtaddm.plb     prvtrtaddm.plb   prvt_awr_data_cp.plb
prvtcpaddm.plb   prvtuadv.plb     prvtsqlu.plb     prvtspai.plb
prvtspa.plb      prvtratmask.plb  prvtspmi.plb     prvtspm.plb
prvtsmbi.plb     prvtsmb.plb      prvtfus.plb      catfusrg.sql
prvtwrk.plb      prvtsmaa.plb     prvtxpln.plb     prvtstat.plb
prvtstai.plb     prvtsqld.plb     prvtspcu.plb     prvtodm.plb
prvtkcl.plb      prvtdst.plb      prvtcmpr.plb     prvtilm.plb
prvtpexei.plb    prvtpexe.plb     prvtcapi.plb     prvtfuse.plb
prvtfspi.plb     prvtpspi.plb     prvtdnfs.plb     prvtfs.plb
prvtadri.plb     prvtadr.plb      prvtadra.plb     prvtadmi.plb
prvtutils.plb    prvtxsrs.plb     prvtsc.plb       prvtacl.plb
prvtds.plb       prvtns.plb       prvtdiag.plb     prvtkzrxu.plb
prvtnacl.plb     prvtredacta.plb  prvtpdb.plb      prvttlog.plb
prvtsqll.plb     prvtappcont.plb  prvtspd.plb      prvtspdi.plb
prvtpprof.plb    prvtsqlm.plb     prvtpart.plb     prvtrupg.plb
prvtrupgis.plb   prvtrupgib.plb   prvtpstdy.plb    prvttsdp.plb
prvtqopi.plb     prvtlog.plb

[Phase 34] type is 1 with 1 Files
ora_load_with_comp.sql

[Phase 35] type is 1 with 1 Files
ora_restart.sql

[Phase 36] type is 1 with 4 Files
catmetinsert.sql catpcnfg.sql     utluppkg.sql     catdph.sql


[Phase 37] type is 1 with 1 Files
ora_restart.sql

[Phase 38] type is 2 with 13 Files
catmetgrant2.sql execemx.sql      execcr.sql       caths.sql
catemini.sql     execaq.sql       execsvrm.sql     exechae.sql
execsec.sql      execbsln.sql     dbmspump.sql     olappl.sql
execrep.sql

[Phase 39] type is 1 with 1 Files
ora_restart.sql

[Phase 40] type is 2 with 10 Files
execstr.sql      execsvr.sql      execstat.sql     catsnmp.sql
wpiutil.sql      owainst.sql      catilmini.sql    execocm.sql
exectsdp.sql     execqopi.sql

[Phase 41] type is 1 with 1 Files
ora_restart.sql

[Phase 42] type is 1 with 1 Files
catpend.sql

[Phase 43] type is 1 with 1 Files
ora_restart.sql

[Phase 44] type is 1 with 1 Files
catupprc.sql

[Phase 45] type is 1 with 1 Files
cmpupstr.sql

[Phase 46] type is 1 with 1 Files
ora_restart.sql

[Phase 47] type is 1 with 2 Files
cmpupjav.sql     cmpupnjv.sql

[Phase 48] type is 1 with 1 Files
ora_restart.sql

[Phase 49] type is 1 with 2 Files
cmpupxdb.sql     cmpupnxb.sql

[Phase 50] type is 1 with 1 Files
ora_restart.sql

[Phase 51] type is 1 with 2 Files
cmpupord.sql     cmpupmsc.sql

[Phase 52] type is 1 with 1 Files
ora_restart.sql

[Phase 53] type is 1 with 1 Files
cmpupend.sql

[Phase 54] type is 1 with 1 Files
catupend.sql

[Phase 55] type is 1 with 1 Files
catuppst.sql

[Phase 56] type is 1 with 1 Files
catshutdown.sql

Using 6 processes.
Serial   Phase #: 0 Files: 1      Time: 72s
Serial   Phase #: 1 Files: 3      Time: 30s
Restart  Phase #: 2 Files: 1      Time: 0s
Parallel Phase #: 3 Files: 18     Time: 4s
Restart  Phase #: 4 Files: 1      Time: 0s
Serial   Phase #: 5 Files: 5      Time: 14s
Serial   Phase #: 6 Files: 1      Time: 9s
Serial   Phase #: 7 Files: 3      Time: 7s
Restart  Phase #: 8 Files: 1      Time: 0s
Parallel Phase #: 9 Files: 60     Time: 15s
Restart  Phase #:10 Files: 1      Time: 0s
Serial   Phase #:11 Files: 1      Time: 10s
Restart  Phase #:12 Files: 1      Time: 0s
Parallel Phase #:13 Files: 199    Time: 14s
Restart  Phase #:14 Files: 1      Time: 0s
Serial   Phase #:15 Files: 3      Time: 1s
Restart  Phase #:16 Files: 1      Time: 0s
Parallel Phase #:17 Files: 33     Time: 13s
Restart  Phase #:18 Files: 1      Time: 0s
Serial   Phase #:19 Files: 3      Time: 5s
Restart  Phase #:20 Files: 1      Time: 0s
Parallel Phase #:21 Files: 23     Time: 78s
Restart  Phase #:22 Files: 1      Time: 0s
Parallel Phase #:23 Files: 11     Time: 33s
Restart  Phase #:24 Files: 1      Time: 0s
Serial   Phase #:25 Files: 1      Time: 34s
Restart  Phase #:26 Files: 1      Time: 1s
Parallel Phase #:27 Files: 0      Time: 0s
Serial   Phase #:28 Files: 1      Time: 0s
Parallel Phase #:29 Files: 130    Time: 2s
Serial   Phase #:30 Files: 1      Time: 0s
Restart  Phase #:31 Files: 1      Time: 0s
Serial   Phase #:32 Files: 1      Time: 0s
Parallel Phase #:33 Files: 122    Time: 4s
Serial   Phase #:34 Files: 1      Time: 0s
Restart  Phase #:35 Files: 1      Time: 0s
Serial   Phase #:36 Files: 4      Time: 46s
Restart  Phase #:37 Files: 1      Time: 0s
Parallel Phase #:38 Files: 13     Time: 41s
Restart  Phase #:39 Files: 1      Time: 0s
Parallel Phase #:40 Files: 10     Time: 5s
Restart  Phase #:41 Files: 1      Time: 0s
Serial   Phase #:42 Files: 1      Time: 6s
Restart  Phase #:43 Files: 1      Time: 0s
Serial   Phase #:44 Files: 1      Time: 3s
Serial   Phase #:45 Files: 1      Time: 1s
Restart  Phase #:46 Files: 1      Time: 0s
Serial   Phase #:47 Files: 2      Time: 1s
Restart  Phase #:48 Files: 1      Time: 0s
Serial   Phase #:49 Files: 2      Time: 205s
Restart  Phase #:50 Files: 1      Time: 0s
Serial   Phase #:51 Files: 2      Time: 1s
Restart  Phase #:52 Files: 1      Time: 0s
Serial   Phase #:53 Files: 1      Time: 1s
Serial   Phase #:54 Files: 1      Time: 99s
Serial   Phase #:55 Files: 1      Time: 117s
Serial   Phase #:56 Files: 1      Time: 12s
Grand Total Time: 884s
You have new mail in /var/mail/oracle
[oracle@server-name]:TEST2:[/oracle/12.0.0/rdbms/admin]s


And were done - total time about 14 minutes which seems very similar to 11.2 from what i remember but i guess there is more in 12 so maybe the parallel stuff is helping.

Few more post upgrade scripts now:

SQL> startup
SQL> @utlu121s.sql
.
Oracle Database 12.1 Post-Upgrade Status Tool           08-06-2013 16:26:21
.
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
.
Oracle Server
.                                      UPGRADED      12.1.0.1.0  00:07:26
Oracle XML Database
.                                         VALID      12.1.0.1.0  00:03:25
Final Actions
.                                                                00:01:05
Total Upgrade Time: 00:11:59

PL/SQL procedure successfully completed.

SQL>

SQL> @catuppst.sql

Session altered.


Session altered.


Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2013-08-06 16:27:11


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2013-08-06 16:27:11


PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt     = TRUE
catrequtlmg: b_SelProps    = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig    = FALSE

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2013-08-06 16:27:12

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


This script will migrate the Baseline data on a pre-11g database
to the 11g database.

...                                       ...
... Completed Moving the Baseline Data    ...
...                                       ...
... If there are no Move BL Data messages ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...
...                                       ...
... Completed the Dropping of the         ...
... Renamed Baseline Tables               ...
...                                       ...
... If there are no Drop Table messages   ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


0 rows created.


Commit complete.


Table created.


2 rows created.


1 row updated.


2 rows updated.


0 rows updated.


Table dropped.


Commit complete.


0 rows updated.


Commit complete.


0 rows updated.


Commit complete.


0 rows updated.


Commit complete.


0 rows created.


Commit complete.


0 rows created.


Commit complete.


PL/SQL procedure successfully completed.






PL/SQL procedure successfully completed.






PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2013-08-06 16:27:13


PL/SQL procedure successfully completed.






Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.










Generating apply and rollback scripts...
Check the following file for errors:
/oracle/cfgtoollogs/catbundle/catbundle_PSU_TEST2_GENERATE_2013Aug06_16_27_13.log
Apply script: /oracle/12.0.0/rdbms/admin/catbundle_PSU_TEST2_APPLY.sql
Rollback script: /oracle/12.0.0/rdbms/admin/catbundle_PSU_TEST2_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...




SQL> alter session set "_ORACLE_SCRIPT" = true;

Session altered.

SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE,

'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;




SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '12.1.0.1',
  9     0,
 10     'PSU',
 11     'Patchset 12.1.0.0.0');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/oracle/cfgtoollogs/catbundle/catbundle_PSU_TEST2_APPLY_2013Aug06_16_27_14.log

Session altered.


Session altered.

SQL>


Now the standard recompile

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-08-06 16:28:33

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-08-06 16:30:33

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>


create the spfile now and restart

SQL>
SQL> create spfile from pfile;

File created.

SQL> startup force
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2293880 bytes
Variable Size             243273608 bytes
Database Buffers          583008256 bytes
Redo Buffers                6529024 bytes
Database mounted.
Database opened.
SQL>


SO now we have a 12.1 database up and running - it's perfectly usable in this format but we want to plug in and move into the future.....


First thing to do is open the database read only - some more trial and error here trying to be lazy and ending up taking longer...

[oracle@server-name]:TEST2:[~]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:30:15 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt                                       

                                                                           ions

SQL> startup force read only;
SP2-0714: invalid combination of STARTUP options
SQL> startup force mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2293880 bytes
Variable Size             243273608 bytes
Database Buffers          583008256 bytes
Redo Buffers                6529024 bytes
Database mounted.
SQL> alter database open readonly;
alter database open readonly
                    *
ERROR at line 1:
ORA-02288: invalid OPEN mode


SQL>  alter database open read only;
 alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery


SQL> recover database;
Media recovery complete.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery


SQL> alter database open;

Database altered.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2293880 bytes
Variable Size             243273608 bytes
Database Buffers          583008256 bytes
Redo Buffers                6529024 bytes
Database mounted.
SQL> alter database open read only;

Database altered.


OK finally - now we create an xml file that describes our db:

SQL> begin
  2  dbms_pdb.describe(pdb_descr_file=>'/tmp/test2.xml');
  3  end;
  4  /

PL/SQL procedure successfully completed.


Now shut everything down ready to plug in

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


The generated file has the following content

[oracle@server-name]:TEST2:[/tmp]# cat test2.xml
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
  <pdbname>TEST2</pdbname>
  <cid>0</cid>
  <byteorder>1</byteorder>
  <vsn>202375424</vsn>
  <dbid>906690115</dbid>
  <cdbid>906690115</cdbid>
  <guid>E349F1E1851D01E3E0433201EC0AA1BA</guid>
  <uscnbas>867460</uscnbas>
  <uscnwrp>0</uscnwrp>
  <rdba>4226235</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/oracle/TEST2/oradata/TEST2/datafile/o1_mf_system_8zzjsosr_.dbf</path>
      <afn>1</afn>
      <rfn>1</rfn>
      <createscnbas>7</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>38400</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375424</vsn>
      <fdbid>906690115</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>867459</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1</frlsb>
      <frlt>822673347</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>SYSAUX</name>
    <type>0</type>
    <tsn>1</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/oracle/TEST2/oradata/TEST2/datafile/o1_mf_sysaux_8zzjsqvc_.dbf</path>
      <afn>2</afn>
      <rfn>2</rfn>
      <createscnbas>2173</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>38400</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375424</vsn>
      <fdbid>906690115</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>867459</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1</frlsb>
      <frlt>822673347</frlt>
    </file>
  </tablespace>
  <optional>
    <csid>1</csid>
    <ncsid>2000</ncsid>
    <options>
      <option>CATALOG=12.1.0.1.0</option>
      <option>CATPROC=12.1.0.1.0</option>
      <option>XDB=12.1.0.1.0</option>
    </options>
    <dv>0</dv>
    <ncdb2pdb>1</ncdb2pdb>
    <APEX>NULL</APEX>
    <parameters>
      <parameter>sga_max_size=838860800</parameter>
      <parameter>sga_target=838860800</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible=12.1.0.1</parameter>
      <parameter>pga_aggregate_target=209715200</parameter>
    </parameters>
    <tzvers>
      <tzver>primary version:14</tzver>
      <tzver>secondary version:0</tzver>
    </tzvers>
    <walletkey>0</walletkey>
  </optional>
</PDB>

Now we switch to the main container database so we can plug in

[oracle@server-name]:TEST2:[/tmp]# . oraenv
ORACLE_SID = [TEST2] ? ED12G
The Oracle base remains unchanged with value /oracle


Now we just need to plug the thing in using the xml file we generated in the last step.
[oracle@server-name]:ED12G:[/tmp]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:36:55 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt

SQL> create pluggable database test2 using '/tmp/test2.xml' nocopy;

Pluggable database created.

SQL>

SO now we are plugged in - now we have to run a conversion script to change all the dictionary so it spans the pdb and the cdb.This is done using noncdb_to_pdb.sql. Lets log on (switch to the

correct container) and run it.

[oracle@server-name]:TEST2:[/tmp]# cd $ORACLE_HOME/rdbms/admin

[oracle@server-name]:ED12G:[/oracle/12.0.0/rdbms/admin]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 6 20:43:16 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=test2;

Session altered.

SQL> @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
--------------------------------------------------------------------------------
TEST2

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='TEST2'

PDBID
----------------------------------------
5

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 TEST2 OPEN READ ONLY',
old   3:               'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')
new   3:               'READ WRITE', 'ALTER PLUGGABLE DATABASE TEST2 OPEN', '')
old   7:        from v$pdbs where name='&pdbname'
new   7:        from v$pdbs where name='TEST2'

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 "TEST2" close
alter pluggable database "TEST2" close
*
ERROR at line 1:
ORA-65020: pluggable database TEST2 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 = "TEST2"

Session altered.

SQL>
SQL> alter pluggable database "&pdbname" open restricted;
old   1: alter pluggable database "&pdbname" open restricted
new   1: alter pluggable database "TEST2" 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_objects5 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_users5 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$rootdeps5 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="TEST2"

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_objects5 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_users5 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$rootdeps5 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$objects5 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$tables5 sharing=none as
old   2: select * from sys.cdb$objects&pdbid where object_type=2
new   2: select * from sys.cdb$objects5 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_users5 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 06-Aug-2013 20:43:52

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_objects5 r, sys.cdb$objects5 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 06-Aug-2013 20:44:06

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_objects5 r, sys.cdb$tables5 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_objects5 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_objects5 r,
old   9:                                  sys.cdb$tables&pdbid p
new   9:                                  sys.cdb$tables5 p

PL/SQL procedure successfully completed.

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

    OWNER#
----------
NAME
--------------------------------------------------------------------------------
         0
DBMS_APPLY_ADM_INTERNAL

         0
DBMS_APPLY_ERROR

         0
DBMS_APPLY_HANDLER_INTERNAL

         0
DBMS_CAPTURE_SWITCH_INTERNAL

         0
DBMS_DBFS_CONTENT

         0
DBMS_DBFS_CONTENT_ADMIN

         0
DBMS_DBFS_SFS_ADMIN

         0
DBMS_ISCHED

         0
DBMS_LOGREP_IMP_INTERNAL

         0
DBMS_PICKLER

         0
DBMS_REGISTRY_SYS

         0
DBMS_REPCAT_UTL4

         0
DBMS_SCHED_MAIN_EXPORT

         0
DBMS_SPACE

         0
DBMS_SPM_INTERNAL

         0
DBMS_SQLTUNE_INTERNAL

         0
DBMS_SQLTUNE_INTERNAL

         0
DBMS_SQLTUNE_UTIL1

         0
DBMS_STATS

         0
DBMS_STATS_INTERNAL

         0
DBMS_STATS_INTERNAL

         0
DBMS_STREAMS_ADM_IVK

         0
DBMS_STREAMS_ADM_UTL

         0
DBMS_STREAMS_HANDLER_INTERNAL

         0
DBMS_WORKLOAD_REPLAY

         0
DBMS_WRR_INTERNAL

         0
DBMS_XSTREAM_ADM_UTL

         0
PRVTPARENTCHILD

         0
PRVT_ADVISOR

         0
PRVT_COMPRESSION

         0
PRVT_SQLPA

         0
PRVT_WORKLOAD

    OWNER#
----------
NAME
--------------------------------------------------------------------------------

         0
WRI$_ADV_HDM_T

         0
WRI$_ADV_OBJSPACE_TREND_T

         0
WRI$_ADV_SQLTUNE

         0
XS_ACL_INT

         0
XS_DATA_SECURITY_INT

         0
XS_DIAG_INT

         0
XS_NAMESPACE_INT

        53
DBMS_CSX_INT

        53
DBMS_XDB_ADMIN

        53
DBMS_XMLSTORAGE_MANAGE

        53
PRVT_DBMS_MANAGE_XMLSTORAGE

        53
XDB$PATCHUPDELETESCHEMA

        53
XDB$PATCHUPSCHEMA

        53
XDBCONFIG_VALIDATE


46 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;

13645 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);

0 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$rootdeps5 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)

0 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));

0 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 "TEST2" close

Pluggable database altered.

SQL> alter pluggable database "&pdbname" open restricted;
old   1: alter pluggable database "&pdbname" open restricted
new   1: alter pluggable database "TEST2" 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(*)
---------- ----------
         6      14277

1 row selected.

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

  COUNT(*)
----------
      5398

1 row selected.

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

  COUNT(*)
----------
      5242

1 row selected.

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

  COUNT(*)
----------
      1677

1 row selected.

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

  COUNT(*)
----------
      1645

1 row selected.

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

  COUNT(*)
----------
        10

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> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation.  All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem     This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem     When run as one of the last steps during upgrade or downgrade,
SQL> Rem     this script will validate all remaining invalid objects. It will
SQL> Rem     also run a component validation procedure for each component in
SQL> Rem     the database. See the README notes for your current release and
SQL> Rem     the Oracle Database Upgrade book for more information about
SQL> Rem     using utlrp.sql
SQL> Rem
SQL> Rem     Although invalid objects are automatically re-validated when used,
SQL> Rem     it is useful to run this script after an upgrade or downgrade and
SQL> Rem     after applying a patch. This minimizes latencies caused by
SQL> Rem     on-demand recompilation. Oracle strongly recommends running this
SQL> Rem     script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem   NOTES
SQL> Rem      * This script must be run using SQL*PLUS.
SQL> Rem      * You must be connected AS SYSDBA to run this script.
SQL> Rem      * There should be no other DDL on the database while running the
SQL> Rem        script.  Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem   MODIFIED   (MM/DD/YY)
SQL> Rem    gviswana    06/26/03 - Switch default to parallel if appropriate
SQL> Rem    gviswana    06/12/03 - Switch default back to serial
SQL> Rem    gviswana    05/20/03 - 2814808: Automatic parallelism tuning
SQL> Rem    rburns      04/28/03 - timestamps and serveroutput for diagnostics
SQL> Rem    gviswana    04/13/03 - utlrcmp.sql load -> catproc
SQL> Rem    gviswana    06/25/02 - Add documentation
SQL> Rem    gviswana    11/12/01 - Use utl_recomp.recomp_serial
SQL> Rem    rdecker     11/09/01 - ADD ALTER library support FOR bug 1952368
SQL> Rem    rburns      11/12/01 - validate all components after compiles
SQL> Rem    rburns      11/06/01 - fix invalid CATPROC call
SQL> Rem    rburns      09/29/01 - use 9.2.0
SQL> Rem    rburns      09/20/01 - add check for CATPROC valid
SQL> Rem    rburns      07/06/01 - get version from instance view
SQL> Rem    rburns      05/09/01 - fix for use with 8.1.x
SQL> Rem    arithikr    04/17/01 - 1703753: recompile object type# 29,32,33
SQL> Rem    skabraha    09/25/00 - validate is now a keyword
SQL> Rem    kosinski    06/14/00 - Persistent parameters
SQL> Rem    skabraha    06/05/00 - validate tables also
SQL> Rem    jdavison    04/11/00 - Modify usage notes for 8.2 changes.
SQL> Rem    rshaikh     09/22/99 - quote name for recompile
SQL> Rem    ncramesh    08/04/98 - change for sqlplus
SQL> Rem    usundara    06/03/98 - merge from 8.0.5
SQL> Rem    usundara    04/29/98 - creation (split from utlirp.sql).
SQL> Rem                           Mark Ramacher (mramache) was the original
SQL> Rem                           author of this script.
SQL> Rem
SQL>
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL> @@utlprp.sql 0
SQL> Rem Copyright (c) 2003, 2011, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      utlprp.sql - Recompile invalid objects in the database
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem      This script is typically used to recompile invalid objects
SQL> Rem      remaining at the end of a database upgrade or downgrade.
SQL> Rem
SQL> Rem      Although invalid objects are automatically recompiled on demand,
SQL> Rem      running this script ahead of time will reduce or eliminate
SQL> Rem      latencies due to automatic recompilation.
SQL> Rem
SQL> Rem      This script is a wrapper based on the UTL_RECOMP package.
SQL> Rem      UTL_RECOMP provides a more general recompilation interface,
SQL> Rem      including options to recompile objects in a single schema. Please
SQL> Rem      see the documentation for package UTL_RECOMP for more details.
SQL> Rem
SQL> Rem    INPUTS
SQL> Rem      The degree of parallelism for recompilation can be controlled by
SQL> Rem      providing a parameter to this script. If this parameter is 0 or
SQL> Rem      NULL, UTL_RECOMP will automatically determine the appropriate
SQL> Rem      level of parallelism based on Oracle parameters cpu_count and
SQL> Rem      parallel_threads_per_cpu. If the parameter is 1, sequential
SQL> Rem      recompilation is used. Please see the documentation for package
SQL> Rem      UTL_RECOMP for more details.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      * You must be connected AS SYSDBA to run this script.
SQL> Rem      * There should be no other DDL on the database while running the
SQL> Rem        script.  Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kquinn      11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL
SQL> Rem    cdilling    05/15/10 - fix bug 9712478 - call local enquote_name
SQL> Rem    anighosh    02/19/09 - #(8264899): re-enabling of function based indexes
SQL> Rem                           not needed.
SQL> Rem    cdilling    07/21/08 - check bitand for functional index - bug 7243270
SQL> Rem    cdilling    01/21/08 - add support for ORA-30552
SQL> Rem    cdilling    08/27/07 - check disabled indexes only
SQL> Rem    cdilling    05/22/07 - add support for ORA-38301
SQL> Rem    cdilling    02/19/07 - 5530085 - renable invalid indexes
SQL> Rem    rburns      03/17/05 - use dbms_registry_sys
SQL> Rem    gviswana    02/07/05 - Post-compilation diagnostics
SQL> Rem    gviswana    09/09/04 - Auto tuning and diagnosability
SQL> Rem    rburns      09/20/04 - fix validate_components
SQL> Rem    gviswana    12/09/03 - Move functional-index re-enable here
SQL> Rem    gviswana    06/04/03 - gviswana_bug-2814808
SQL> Rem    gviswana    05/28/03 - Created
SQL> Rem
SQL>
SQL> SET VERIFY OFF;
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-08-06 20:44:24

1 row selected.

SQL>
SQL> DOC
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
SQL>
SQL> DECLARE
  2     threads pls_integer := &&1;
  3  BEGIN
  4     utl_recomp.recomp_parallel(threads);
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-08-06 20:47:11

1 row selected.

SQL>
SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist
SQL> Rem here, is no longer needed.
SQL>
SQL> DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
SQL> select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;

OBJECTS WITH ERRORS
-------------------
                  0

1 row selected.

SQL>
SQL>
SQL> DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

ERRORS DURING RECOMPILATION
---------------------------
                          0

1 row selected.

SQL>
SQL>
SQL> Rem
SQL> Rem Declare function local_enquote_name to pass FALSE
SQL> Rem into underlying dbms_assert.enquote_name function
SQL> Rem
SQL> CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)
  2   return varchar2 is
  3     begin
  4          return dbms_assert.enquote_name(str, FALSE);
  5     end local_enquote_name;
  6  /

Function created.

SQL> Rem
SQL> Rem If sys.enabled$index table exists, then re-enable
SQL> Rem list of functional indexes that were enabled prior to upgrade
SQL> Rem The table sys.enabled$index table is created in catupstr.sql
SQL> Rem
SQL> SET serveroutput on
SQL> DECLARE
  2     TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  3     commands tab_char;
  4     p_null   CHAR(1);
  5     p_schemaname  VARCHAR2(30);
  6     p_indexname   VARCHAR2(30);
  7     rebuild_idx_msg BOOLEAN := FALSE;
  8     non_existent_index exception;
  9     recycle_bin_objs exception;
 10     cannot_change_obj exception;
 11     no_such_table  exception;
 12     pragma exception_init(non_existent_index, -1418);
 13     pragma exception_init(recycle_bin_objs, -38301);
 14     pragma exception_init(cannot_change_obj, -30552);
 15     pragma exception_init(no_such_table, -942);
 16     type cursor_t IS REF CURSOR;
 17     reg_cursor   cursor_t;
 18
 19  BEGIN
 20     -- Check for existence of the table marking disabled functional indices
 21
 22     SELECT NULL INTO p_null FROM DBA_OBJECTS
 23     WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
 24              object_type = 'TABLE' and rownum <=1;
 25
 26        -- Select indices to be re-enabled
 27        EXECUTE IMMEDIATE q'+
 28           SELECT 'ALTER INDEX ' ||
 29                   local_enquote_name(e.schemaname) || '.' ||
 30                   local_enquote_name(e.indexname) || ' ENABLE'
 31              FROM   enabled$indexes e, ind$ i
 32              WHERE  e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
 33                     bitand(i.property, 16) != 0+'
 34        BULK COLLECT INTO commands;
 35
 36        IF (commands.count() > 0) THEN
 37           FOR i IN 1 .. commands.count() LOOP
 38              BEGIN
 39              EXECUTE IMMEDIATE commands(i);
 40              EXCEPTION
 41                 WHEN NON_EXISTENT_INDEX THEN NULL;
 42                 WHEN RECYCLE_BIN_OBJS THEN NULL;
 43                 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
 44              END;
 45           END LOOP;
 46        END IF;
 47
 48        -- Output any indexes in the table that could not be re-enabled
 49        -- due to ORA-30552 during ALTER INDEX...ENBLE command
 50
 51        IF  rebuild_idx_msg THEN
 52         BEGIN
 53           DBMS_OUTPUT.PUT_LINE
 54  ('The following indexes could not be re-enabled and may need to be rebuilt:');
 55
 56           OPEN reg_cursor FOR
 57               'SELECT e.schemaname, e.indexname
 58                FROM   enabled$indexes e, ind$ i
 59                WHERE  e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
 60
 61           LOOP
 62             FETCH reg_cursor INTO p_schemaname, p_indexname;
 63             EXIT WHEN reg_cursor%NOTFOUND;
 64             DBMS_OUTPUT.PUT_LINE
 65                ('.... INDEX ' || p_schemaname || '.' || p_indexname);
 66           END LOOP;
 67           CLOSE reg_cursor;
 68
 69         EXCEPTION
 70              WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
 71              WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
 72              WHEN OTHERS THEN CLOSE reg_cursor; raise;
 73         END;
 74
 75        END IF;
 76
 77        EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
 78
 79     EXCEPTION
 80        WHEN NO_DATA_FOUND THEN NULL;
 81
 82  END;
 83  /

PL/SQL procedure successfully completed.

SQL>
SQL> DROP function local_enquote_name;

Function dropped.

SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL> select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

    STATUS   COUNT(*)
---------- ----------
         6        164

1 row selected.

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

  COUNT(*)
----------
       159

1 row selected.

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

  COUNT(*)
----------
         8

1 row selected.

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

  COUNT(*)
----------
        80

1 row selected.

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

  COUNT(*)
----------
        48

1 row selected.

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

  COUNT(*)
----------
        10

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;

164 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL> alter pluggable database "&pdbname" 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;

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;

View dropped.

SQL> drop view sys.cdb$objects&pdbid;

View dropped.

SQL> drop view sys.cdb$common_root_objects&pdbid;

View dropped.

SQL> drop view sys.cdb$common_users&pdbid;

View dropped.

SQL> drop view sys.cdb$rootdeps&pdbid;

View dropped.

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

Session altered.

SQL> drop view sys.cdb$common_root_objects&pdbid;

View dropped.

SQL> drop view sys.cdb$common_users&pdbid;

View dropped.

SQL> drop view sys.cdb$rootdeps&pdbid;

View dropped.

SQL>
SQL> alter session set container="&pdbname";

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;

System altered.

SQL>
SQL> alter pluggable database "&pdbname" 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";

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  /

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>


OK no errors all looks good lets fire it up from mount mode into open mode

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 TEST2                          MOUNTED
SQL> alter pluggable database test2 open;

Warning: PDB altered with errors.


Hmm hold on that's not in the script - what went wrong?

SQL> sho errors
No errors.
SQL> alter pluggable database test2 open read write;
alter pluggable database test2 open read write
*
ERROR at line 1:
ORA-65019: pluggable database TEST2 already open


SQL>  alter pluggable database test2 close;

Pluggable database altered.

SQL> alter pluggable database test2 open read write;

Warning: PDB altered with errors.

SQL>


It's not having it - lets check the alert log

[oracle@server-name]:ED12G:[/oracle/12.0.0/rdbms/admin]# adrci

ADRCI: Release 12.1.0.1.0 - Production on Tue Aug 6 20:50:27 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/oracle"
adrci> show alert -tail
DIA-48449: Tail alert can only apply to single ADR home


adrci> set home diag/rdbms/orcl/ED12G
adrci> show alert -tail
2013-08-06 20:49:02.657000 +01:00
Completed:  alter pluggable database test2 close
2013-08-06 20:49:06.685000 +01:00
alter pluggable database test2 open read write
Database Characterset is US7ASCII
 SYS.EXTERNAL_TAB$ (PARAM_CLOB) - CLOB populated
********************************************** ******************
WARNING: Pluggable Database TEST2 with pdb id - 5 is altered with warnings.
Please look into PDB_PLUG_IN_VIOLATIONS view for more details.
****************************************************************
Opening pdb TEST2 (5) with no Resource Manager plan active
2013-08-06 20:49:07.715000 +01:00
Warning: Pluggable database TEST2 altered with errors
Pluggable database TEST2 opened read write
Completed: alter pluggable database test2 open read write
adrci>

OK lets check this new view to see what the problem is

  1* select status,MESSAGE from PDB_PLUG_IN_VIOLATIONS where name='TEST2'
SQL> /
RESOLVED   CDB parameter sga_max_size mismatch: Previous 838860800 Current 3388997632
RESOLVED   CDB parameter sga_target mismatch: Previous 838860800 Current 3388997632
RESOLVED   CDB parameter compatible mismatch: Previous 12.1.0.1 Current 12.1.0.0.0
RESOLVED   CDB parameter pga_aggregate_target mismatch: Previous 209715200 Current 1127219200
RESOLVED   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
PENDING    Character set mismatch: PDB character set US7ASCII CDB character set AL32UTF8.
PENDING    Database option APS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option CATJAVA mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option CONTEXT mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option DV mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option JAVAVM mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option OLS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option ORDIM mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option OWM mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option XML mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    Database option XOQ mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.
PENDING    APEX mismatch: PDB installed version NULL CDB installed version 4.2.0.00.27

18 rows selected.

SQL>

At this point i thought of Monty Python and 'what have the romans ever done for us?'. SO apart from the xml,sdo,catjava,aps,dv etc what has the pdb done for us. Oh well i digress. SO what to do next - can i use test2?

SQL> sho pdbs
         2 PDB$SEED                       READ ONLY  NO
         3 ORA12GCONTAINER                READ WRITE NO
         4 TEST                           READ WRITE NO
         5 TEST2                          READ WRITE YES

SQL> alter session set container=test2;

Session altered.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to testl
  2  .
SQL> grant connect,resource to test;

Grant succeeded.



SQL> create table test.testtab(col1 number);
create table test.testtab(col1 number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'


SQL> grant unlimited tablespace to test;

Grant succeeded.

SQL>  create table test.testtab(col1 number);

Table created.

SQL> insert into test.testtab values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>


It would seem it is usable and all of the above issues can be fixed by just running the relevant .sql files against the pdb to install those options. The characterset also needs converting and I
can't remember if its an easy convert for those two or not. Anyway this post has got long enough already. I might add an update when i get round to fixing the above.

It's a good idea to check characterset/options before you start though. I guess it says that in the documentation some where - guess i should read that.....

Comments

  1. Hi -

    Can you please let us know what have you done to resolve below warnings..

    PENDING Database option JAVAVM mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.

    Thanks-

    ReplyDelete
  2. Hi,
    The documentation does not seem clear on this...
    Personally i think this may be an 'ignorable' error as in most cases if this option wasn't installed before plugging in then it's not needed by the application. Whether this somehow causes a problem in the new multitenant design I'm not sure. I can;t seem to find a statement from Oracle which confirms one way or the other if the pdb has to have all the options of the cdb. If you want to get rid of the warning you can install the java options using catcon.pl.

    I would assume you have warning for all the other options too?

    Regards,
    Rich

    ReplyDelete
  3. Yes Rich..we have warnings for other options like APS,CONTEXT,DV,OLS,SDO,XOQ and APEX. Yeah checked in metalink and documentation but couldn't find a solid statement from Oracle. Thanks for your reply.Will update if I find any references.Being an employee of Oracle it seems little strange searching for solutions out of documentation.

    ReplyDelete
  4. Hi,
    had a chat to one of the senior oracle managers at the UK oracle conference last week - seems each PDB needs all the options as far as he knows - it is a requirement - nothing actually seems to be documented to that effect though.

    Rich

    ReplyDelete

Post a Comment