Oracle Database 12c Release 1 (12.1) Upgrade New Features
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1.0 and laterOracle Database - Standard Edition - Version 12.1.0.1.0 and later
Information in this document applies to any platform.
Purpose
To describe the new upgrade features for Oracle Database 12c Release 1 (12.1)
Details
The DBUA interface has changed changed significantly in release 12cR1 (12.1.0.1).
DBUA is now panel based (same as OUI) and the upgrade is divided into the following phases:
- Pre Upgrade Steps
- Database Upgrade Steps
- Post Upgrade Steps
Enhanced Upgrade Automation with New Pre-Upgrade Information Tool
- Oracle provides a new Pre-Upgrade Information tool/script preupgrd.sql.
- The Pre-Upgrade Information Tool consists of preupgrd.sql and utluppkg.sql.
- These scripts must be copied from the new 12c $ORACLE_HOME/rdbms/admin to the $ORACLE_HOME/rdbms/admin directory of the database being upgrade.
- Preupgrd.sql must be run from the environment of the database being upgraded.
- It generates the fix-up scripts and log file in $ORACLE_HOME/cfgtoollogs/$ORACLE_SID/preupgrade.
- The preupgrade_fixups.sql script is created to address issues that can be fixed using SQL*Plus in the source database.
- The postupgrade_fixups.sql script is created to address issues that can be fixed after the database has been upgraded.
- A log file, preupgrade.log, is created containing the output of the Pre-Upgrade Information Tool
- Pre-Upgrade Information Tool displays recommendations but does not execute the recommendations automatically in order to ensure that you have control over how and when the fix-up scripts are run.
Parallel Processing for Database Upgrade
- Upgrade parallelism is a new feature introduced in Oracle database 12cR1 where upgrade scripts and processes can run in parallel.
- Parallelism is designed to take full advantage of the CPU capacity resulting in reduced time to upgrade .
- Parallelism will break down each SQL file into stand-alone (independent) components.
If the SQL file is stand-alone then it can be run in parallel with another SQL file.
This is not always the case for all SQL scripts as some SQL scripts depend on each other and can call other utilities.
In this case, Oracle ensures those dependencies are satisfied before calling the next script or reference to them. - The purpose of dividing the upgrade into phases is due to the upgrade parallelism.
Phases are how Oracle upgrade will load sql files in parallel.
One phase has to complete before moving onto the next phase. - By default, the upgrade runs with a parallelism of 4.
Improvements have been observed even with a single CPU by setting the count to 4.
The upgrade can get more CPU cycles to do the work even though there is only a single process/CPU.
New Upgrade Utility catctl.pl
- The Upgrade Utility (catctl.pl), has been introduced in Oracle Database 12cR1.
- It loads the data dictionary and components in parallel thus reducing the overall upgrade time.
- It enables components that do not have to be upgraded in a specific order to be upgraded at the same time e.g. In parallel.
- Here are a few parameters available to the catctl.pl script:
- -n : Specifies the number of processes to use for parallel operations . Default is 4. Maximum is 8. For serial mode set it to 0.
- -u : Username (prompts for password)
- -d : directory containing the files to be run
- -t : table name for phase logging
- -l : directory to use for spool log files
- -s : SQL script to initialize sessions
- -e : sets echo off while running the scripts
- -p : restart phase (skip successful phases on a rerun)
- -i : identifier to use when creating spool log files
- -y : display phases only
To run catctl.pl on Linux:
: cd $ORACLE_HOME/rdbms/admin
: $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
To run catctl.pl on Windows:
: cd %ORACLE_HOME%\rdbms\admin
: %ORACLE_HOME%\perl\bin\perl catctl.pl catupgrd.sql
: cd $ORACLE_HOME/rdbms/admin
: $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
To run catctl.pl on Windows:
: cd %ORACLE_HOME%\rdbms\admin
: %ORACLE_HOME%\perl\bin\perl catctl.pl catupgrd.sql
Example: Where parallelism is 6 ( n=6)
: cd $ORACLE_HOME/rdbms/admin_
: $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics -t phase_tab -i V112 catupgrd.sql
: cd $ORACLE_HOME/rdbms/admin_
: $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics -t phase_tab -i V112 catupgrd.sql
Oracle XML Database is now mandatory with installation
- Oracle XML Database (Oracle XML DB) is now a mandatory component of Oracle Database.
If during the upgrade, Oracle XML DB is not found to be installed, then Oracle XML DB is automatically loaded into the SYSAUX tablespace.
The upgraded Oracle Database will include Oracle XML DB.
Upgrade log files
- If environment variable ORACLE_BASE is set, then DIAGNOSTIC_DEST
defaults to the directory designated by ORACLE_BASE then the generated
scripts and log files are created under:
$ORACLE_BASE/cfgtoollogs/tool_name/SID/upgrade<n>
- If ORACLE_BASE is not defined, then the generated scripts and log files are created under:
$ORACLE_HOME/cfgtoollogs/tool_name/SID/upgrade<n>
where n represents the incarnation number of the upgrade that been performed on this system for a particular ORACLE_SID.
- One log file for each parallel thread.The catctl.pl script will
create multiple logfiles depending on the degree of parallelism selected
while running the catctl.pl and catupgrd.sql scripts.
For example:
catupgrd1.log
catupgrd2.log
catupgrd3.log
- Default log location is the current working directory.
- Use –l parameter of catcctl.pl to specify the log location .
Oracle EM Express
• EM DB control is superseded in 12c by Oracle EM Express .
- Therefore no repository is needed anymore .- If the EM repository is not removed up front ,it will be automatically removed during the catuppst.sql post-upgrade phase.
OR
- Remove Enterprise Manager Database Control repository.
$emctl stop dbcontrol
SQL> @ ?/rdbms/admin/emremove.sql
References
Oracle Database 12c Release 1 (12.1) Upgrade New Features [ID 1515747.1]
--------------------------------------------------------------------------------------------------------
Tuesday, 6 August 2013
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.....
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.....
No comments:
Post a Comment