How to use the dbms_explan.display_cursor function to find the execution plan of a query in the Cursor Cache

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor( '0syc4upspr638',3,'TYPICAL'));
exit

Where
- the first argument is the SQL id
- the second argument cursor child number
- the third argument is the format string. TYPICAL is the default value.


See the Oracle Documentation for more information on how to use dbms_xplan

How to migrate a non-CDB database to a PDB on the same host

There are many ways to migrate your non-cdb Oracle databases to the new Multitenant Architecture. Here I will show you how to clone a non-cdb database to a PDB running in a container database

Asumptions:
You have two Oracle databases of version 12.1 or higher running on the same server:

1. Your original, non-cdb database called db01
2. Your new container database called cdb01
Both of these databases are running out of Oracle Home installe in /u01/oracle/product/12c

Step 1: For Oracle 12.1, open your non-CDB in read only mode (not needed from version 12.2 and onwards):

shutdown immedate
startup mount
alter database open read only;

Step 2: create an xml file that describes the non-CDB database using the package dbms_pdb:

export ORACLE_SID=db01
sqlplus / as sysdba

Generate the file:

set serveroutput on
begin
  dbms_pdb.describe( pdb_descr_file => '/tmp/ncdb.xml');
end;
/

Step 3: Create the pluggable database

Connect to your CDB, and create the PDB using the script you created in step 2.

export ORACLE_SID=cdb01
sqlplus / as sysdba

create pluggable database pdb01
using '/tmp/ncdb.xml'
copy
file_name_convert = ('/u02/oradata/db01/', '/u02/oradata/cdb01/PDBS/pdb01/');

Note that I am choosing to copy the files from the original location of the non-CDB database, to a brand new one, using the directive file_name_convert. There are other options, too: MOVE and NOCOPY

Step 4: execute $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

sqlplus / as sysdba
alter session set container=PDB01;
@?/rdbms/admin/noncdb_to_pdb.sql

I did receive errors during this phase:

ORA-65172: cannot run noncdb_to_pdb.sql unless pluggable database is an
unconverted non-container database
ORA-06512: at "SYS.DBMS_PDB", line 154
ORA-06512: at line 1

The error message seemed to be harmless:

oracle@myserver.mydomain.com:[cdb1]# oerr ora 65172
65172, 00000, "cannot run noncdb_to_pdb.sql unless pluggable database is an unconverted non-container database"
// *Cause:  An attempt was made to run 'noncdb_to_pdb.sql' on a pluggable
//          database (PDB) that was not an unconverted non-container database.
// *Action: 'noncdb_to_pdb.sql' is not necessary for this PDB.
//

Further research showed 1) the Pdb was indeed created, and 2) that there were errors in PDB_PLUG_IN_VIOLATIONS:

select PDB_ID,PDB_NAME,STATUS,CON_ID from cdb_pdbs

    PDB_ID PDB_NAME                       STATUS                          CON_ID
---------- ------------------------------ --------------------------- ----------
         2 PDB01                          NEW                                  2

alter session set container=cdb$root;


SELECT TO_CHAR(TIME,'dd.mm.yyyy hh24:mi') "time",NAME,STATUS,MESSAGE 
FROM PDB_PLUG_IN_VIOLATIONS;

Result:

time	NAME	STATUS	MESSAGE
20.09.2017 14:17	PDB01	PENDING	Database option CATJAVA mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option CONTEXT mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option JAVAVM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option ORDIM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Database option XML mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17	PDB01	PENDING	Sync PDB failed with ORA-65177 during 'alter user sys identified by *'
20.09.2017 14:17	PDB01	PENDING	Sync PDB failed with ORA-65177 during 'alter user system identified by *'

A quick search on Oracles support site revealed that these errors can be ignored. See Doc ID 2020172.1 "OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS"

Finally, remember to open your pdb in read write mode. It was in MIGRATE mode after the noncdb_to_pdb.sql script had been run and failed:

 select con_id,name,open_mode from v$containers;

    CON_ID NAME      OPEN_MODE
---------- --------- -----------------------------
         1 CDB$ROOT  READ WRITE
         2 PDB01     MIGRATE

alter session set container=PDB01;

alter pluggable database close;

Pluggable database altered.

alter pluggable database open;

 select con_id,name,open_mode from v$containers;

    CON_ID NAME      OPEN_MODE
---------- --------- -----------------------------
         2 PDB01     READ WRITE

Oracle Multitentant licensing

For Oracle 12c, you don't need to purchase an additional license when running a Multitenant database shop with only 1 - one - PDB. This configuration is also refered to as "single tenant".

Franck Pachot is pointing this out in a community discussion:


You don't need an option when you have only one pluggable database.
You can detect violation with:

select name, aux_count from dba_feature_usage_statistics where name like '%Pluggable%' or name like '%Multitenant%';

If AUX_COUNT is >1 then you need to buy the option, or drop the additional PDBs

In 12.2 you can set MAX_PDBS to 1 to be sure nobody creates more than allowed.
Here are the features you can use in this configuration (know as 'single-tenant'): http://www.slideshare.net/pachot/12cr2-singletenant-multitenant-features-for-all-editions


In Oracle 19c, the MAX_PDBS can be set to 3 without breaking the license.

A workaround for ORA-06553: PLS-213: package STANDARD not accessible when using datapatch in a CDB

Short background:

I was having trouble applying "patch 26550023 - COMBO of OJVM Component 12.1.0.2.170718 DB PSU + DB PSU 12.1.0.2.170814" in my Multitenant environment. The container database only had one PDB at the time, the PDB$SEED.

After having successfully applied opatch apply for both patches, I ran datapatch -verbose to load modified SQL into the database. I had already opened my container database in upgrade mode, and also opened the PDB$SEED in upgrade mode by executing

alter pluggable database all open upgrade;

The state of the PDB$SEED could be confirmed in the alert log, as well as from v$pdb:

SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
--------- --------------
PDB$SEED   MIGRATE

Still, I kept getting weird errors like


Bootstrapping registry and package to current versions...done
Error in bootstrap log /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_17273_2017_09_13_13_29_29/bootstrap1_CDBVEG_PDBSEED.log:
Error at line 7: ORA-06553: PLS-213: package STANDARD not accessible
Error at line 17: ORA-06553: PLS-213: package STANDARD not accessible
Error at line 25: SP2-0310: unable to open file "/u01/oracle/product/12102/sqlpatch/FALSE.sql"
Prereq check failed, exiting without installing any patches.


There was little information about the problem and potential workarounds to be found on the internet.

After trying different options without success, I could find no other solution than to drop the PDB$SEED container, so that patching could continue.

Here's how:

SQL> 
-- necessarry to avoid "ORA-65017: seed pluggable database may not be dropped or altered"
alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database PDB$SEED close;

Pluggable database altered.

SQL> drop pluggable database pdb$seed including datafiles;

Pluggable database dropped.

SQL> alter session set "_oracle_script"=FALSE;

Session altered.

SQL> select * from cdb_pdbs;

no rows selected

After this point, run datapatch again:

oracle@myserver:[cdbveg]# datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Wed Sep 13 13:34:05 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_18072_2017_09_13_13_34_05/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)):
  Installed in the binary registry only
Bundle series PSU:
  ID 170814 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT
    Nothing to roll back
    The following patches will be applied:
      26027162 (Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017))
      26609783 (DATABASE PATCH SET UPDATE 12.1.0.2.170814)

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 26027162 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26027162/21319014/26027162_apply_CDBVEG_CDBROOT_2017Sep13_13_34_18.log (no errors)
Patch 26609783 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26609783/21481899/26609783_apply_CDBVEG_CDBROOT_2017Sep13_13_34_18.log (no errors)
SQL Patching tool complete on Wed Sep 13 13:34:52 2017

Verification that the patches are applied:

SQL> select ACTION,DESCRIPTION,STATUS,BUNDLE_SERIES from registry$sqlpatch;

ACTION     DESCRIPTION                                                            STATUS               BUNDLE_SERIES
---------- ---------------------------------------------------------------------- -------------------- --------------------
APPLY      Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)        SUCCESS
APPLY      DATABASE PATCH SET UPDATE 12.1.0.2.170814                              SUCCESS              PSU

How to attach an ORACLE_HOME to an existing inventory

I wanted to check the patchlevel in one of my Oracle installations, and the following errow was returned:

oracle@tsl0map-dbteam-sandbox-db04:[vegdb01]# opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/12102
Central Inventory : /home/oracle/oraInventory
   from           : /u01/oracle/product/12102/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/oracle/product/12102/cfgtoollogs/opatch/opatch2017-09-12_15-30-47PM_1.log

List of Homes on this system:

  Home name= agent13c2, Location= "/u01/oracle/product/agent13c/agent_13.2.0.0.0"
  Home name= 11204, Location= "/u01/oracle/product/11204"
  Home name= OraHome3, Location= "/u01/oracle/product/gg121"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Turned out my inventory had not been updated with my new Oracle Home. When I looked in the inventory.xml file on my server, there was no entry for the installation there.

To fix this, add the new OH to your inventory. From your OH that is missing, do the following:

cd $ORACLE_HOME/oui/bin
./runInstaller -invPtrLoc /u01/oracle/product/12102/oraInst.loc -attachHome ORACLE_HOME=/u01/oracle/product/12102 ORACLE_HOME_NAME="Ora12cHome"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8191 MB    Passed
The inventory pointer is located at /u01/oracle/product/12102/oraInst.loc
'AttachHome' was successful.

Check the inventory file now, and you'll see a new entry for the OH (in my case the Ora12cHome):

<HOME NAME="agent13c2" LOC="/u01/oracle/product/agent13c/agent_13.2.0.0.0" TYPE="O" IDX="8"/>
<HOME NAME="11204" LOC="/u01/oracle/product/11204" TYPE="O" IDX="1"/>
<HOME NAME="Ora12cHome" LOC="/u01/oracle/product/12102" TYPE="O" IDX="9"/>

How to create a container database in Oracle 12c

Create an initialization file in your $ORACLE_HOME/dbs folder:

enable_pluggable_database=true
processes=300
control_files="/u02/oradata/cdbveg/control01.ctl","/u03/oradata/cdbveg/control02.ctl"
audit_file_dest="/u01/oracle/admin/cdbveg/adump"
audit_trail=DB
compatible=12.1.0.2
db_block_size=8192
db_domain=""
db_name="cdbveg"
db_recovery_file_dest_size=429496729600
db_recovery_file_dest="/u04/fra"
diagnostic_dest=/u01/oracle
dispatchers="(PROTOCOL=TCP) (SERVICE=cdbvegXDB)"
local_listener = "(ADDRESS = (PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521))"
open_cursors=300
remote_login_passwordfile=EXCLUSIVE
undo_tablespace=UNDOTBS1
# use ASMM (Automatic shared memory managaement)
sga_target=2G
sga_max_size=2G
pga_aggregate_target=128M
# Disable AMM (Automatic Memory Managament)
memory_target=0
memory_max_target=0

Create the necessarry directories:

mkdir -p /u02/oradata/cdbveg
mkdir -p /u03/oradata/cdbveg
mkdir -p /u01/oracle/admin/cdbveg/adump
mkdir -p /u02/oradata/cdbveg/seed

Set your environment variables correcly, in my case they were:

export ORACLE_SID=cdbveg
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/product/12102

Start an instance:

sqlplus / as sysdba 
statup nomount

Create your container database:

create database cdbveg
user sys identified by MySecretPasswd
user system identified by MySecretPasswd
logfile group 1(
 '/u02/oradata/cdbveg/red01a.log',
 '/u03/oradata/cdbveg/red01b.log'
) size 128M,
group 2 (
 '/u02/oradata/cdbveg/red02a.log',
 '/u03/oradata/cdbveg/red02b.log'
) size 128M
character set al32utf8 national character set al16utf16
extent management local datafile '/u02/oradata/cdbveg/system01.dbf' size 256M autoextend on next 16M maxsize unlimited
sysaux datafile '/u02/oradata/cdbveg/sysaux01.dbf' size 128M autoextend on next 16M maxsize unlimited
default temporary tablespace temp tempfile '/u02/oradata/cdbveg/temp.dbf' size 256M
undo tablespace undotbs1 datafile '/u02/oradata/cdbveg/undotbs01.dbf' size 256M
enable pluggable database
seed
file_name_convert = ('/u02/oradata/cdbveg','/u02/oradata/cdbveg/seed')
system datafiles size 128M autoextend on next 8M maxsize 2048M
sysaux datafiles size 64M
USER_DATA tablespace users datafile '/u02/oradata/cdbveg/seed/users.dbf' size 128M reuse autoextend on next 32M maxsize unlimited;

The above statement will create a container database, and a seed database which will follow the specifications listed under the keyword "seed" in the create-statement above.
The /u02/oradata/cdbveg/seed directory will contain the datafiles used for future creations of PDBs based on the seed template.
In addition to the mandatory system and sysaux tablespaces, I add a tablespace for user data, too.

Finally, run the necessary scripts:

sqlplus / as sysdba 

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catblock.sql
SQL> @?/rdbms/admin/catproc.sql

SQL> alter session set "_ORACLE_SCRIPT"=false;

SQL> @?/rdbms/admin/catoctk.sql
SQL> @?/rdbms/admin/owminst.plb
SQL> @?/sqlplus/admin/pupbld.sql

Verify creation:

select name,con_id,dbid,open_mode from v$containers

NAME                     CON_ID       DBID OPEN_MODE
-------------------- ---------- ---------- ------------------------------
CDB$ROOT                      1 3469467854 READ WRITE
PDB$SEED                      2  682995139 READ ONLY

Why is my PDB seemingly stuck in RESTRICTED mode?

Check out the view pdb_plug_in_violations - it will point you in the right direction.

In my case, I had created a pluggable database some days after my CDB was finished.

I had completely forgotten about a common user that I had created at that time:

-- create a common user for all CONTAINERS
-- common users must use the prefix c##
create user C##dba identified by MySecretPasswor
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
container=all;

The new PDB was created without the USERS tablespace, which prevented the new PDB to be synchronized with the parent container.
The pdb_plug_in_violations contained the following message:

Sync PDB failed with ORA-959 during 'create user C##dba identified by *default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
container=all'

To resolve the situation, connect to the container with the missing tablespace:

alter session set container=pdbveg2;

Make sure my session OMF parameter is correctly set:

select name,value,DEFAULT_VALUE,ISDEFAULT,ISPDB_MODIFIABLE, ISSES_MODIFIABLE, DESCRIPTION
from V$PARAMETER where name like '%create_file_dest%';

NAME	VALUE	DEFAULT_VALUE	ISDEFAULT	ISPDB_MODIFIABLE	ISSES_MODIFIABLE	DESCRIPTION
db_create_file_dest	/u02/oradata/cdbveg/pdbveg2	NONE	TRUE	TRUE	TRUE	default database location

Create the missing tablespace:

create tablespace USERS
datafile size 8M autoextend on next 2M maxsize 2G;

Finally, close and reopen your pluggable database:

alter pluggable database pdbveg2 close;
alter pluggable database pdbveg2 open read write;

Check status:

select CON_ID,name,OPEN_MODE, RESTRICTED
from v$containers;

CON_ID	NAME	OPEN_MODE	RESTRICTED
4	PDBVEG2	READ WRITE	NO

How to create and drop a pluggable database in Oracle 12c

This method uses OMF (Oracle Managed Files):

1. Create directory on host:

mkdir -p /u02/oradata/cdbveg/pdbveg2

2. Set session parameter to enable OMF:

alter session set db_create_file_Dest='/u02/oradata/cdbveg/pdbveg2';

3. Create a pluggable database with a new and local PDBA:

create PLUGGABLE database PDBVEG2
admin user VEGARD
identified by "MySecretPassword"
roles=(DBA);

The statement above copies data files from the seed PDB to the target directory specified in step 2.
It will also grant the local role PDB_DBA to the new PDBA, Vegard.

4. Check the status of the newly created PDB:

select pdb_name,status,con_id from CDB_PDBS;

PDB_NAME	STATUS	CON_ID
PDBVEG1	NORMAL	3
PDB$SEED	NORMAL	2
PDBVEG2	NEW	4

5. Open the pluggable database:

alter pluggable database pdbveg2 open;

6. Check status again:

select pdb_name,status,con_id from CDB_PDBS;

PDB_NAME	STATUS	CON_ID
PDBVEG1	NORMAL	3
PDB$SEED	NORMAL	2
PDBVEG2	NORMAL	4

To drop a pluggable database, perform the following steps:

alter pluggable database PDB1 close;
-- Unplug the database. If you try to drop it before unplugging it, Oracle report
-- ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged
alter pluggable database PDB1 unplug into '/tmp/PDB1.xml';
drop pluggable database PDB1;

If you want Oracle to remove the physical files from disk for you in the same operation, use the following syntax:

drop pluggable database PDB1 including datafiles;

What is the "datapatch" utility released in Oracle 12c?

From Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1):

Datapatch is the new tool that enables automation of post-patch SQL actions for RDBMS patches.

and

Datapatch can be executed to complete post-patch SQL actions after the database is restarted following patch application.
For patches that do not have post-patch SQL actions to be performed, calling datapatch is a no-op.
For patches that do have post-patch SQL instructions to be invoked on the database instance, datapatch will automatically detect ALL pending actions (from one installed patch or multiple installed patches) and complete the actions as appropriate.

How does Oracle detect ALL pending actions?

Datapatch determines the requisite apply/rollback actions by matching an internal repository with the patch inventory.


When should datatach be invoked?

Datapatch should be invoked when the database is restarted after a patching session.

From 12.1.0.2 and onwards, Oracle Corporation points out that they have made some change to the datapatch utility. The most important in my opinion is that datapatch now assumes "the role of catbundle when applying bundles/PSU."

This means that

catbundle.sql has been deprecated and patch registry is maintained only in registry$sqlpatch.
Application of the PSU does not update registry$history table anymore.
The status for patch application is now entirely maintained in registry$sqlpatch


Here's an abbreviated output from using datapatch as part of applying Oracles latest PSU, Database Patch Set Update 12.1.0.2.170814 (Includes CPUJul2017):

1. Download and unpack the patch

[oracle@lx01oric software]$ unzip p26609783_121020_Linux-x86-64.zip

2. Shutdown your oracle instance and the listener
3. Check for conflicts:

[oracle@lx01oric software]$ cd 26609783
oracle@lx01oric 26609783]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2017, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/oracle/product/db/12.1/12.1.0.2.0
Central Inventory : /u01/oraInventory
   from           : /u01/oracle/product/db/12.1/12.1.0.2.0/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/oracle/product/db/12.1/12.1.0.2.0/cfgtoollogs/opatch/opatch2017-09-06_13-41-14PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

4. Apply the patch:

[oracle@lx01oric 26609783]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.9
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/db/12.1/12.1.0.2.0
Central Inventory : /u01/oraInventory
   from           : /u01/oracle/product/db/12.1/12.1.0.2.0/oraInst.loc
OPatch version    : 12.2.0.1.9
OUI version       : 12.1.0.2.0
Log file location : /u01/oracle/product/db/12.1/12.1.0.2.0/cfgtoollogs/opatch/opatch2017-09-06_14-16-35PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   19769480  20299023  20831110  21359755  21948354  22291127  23054246  24006101  24732082  25171037  25755742  26609783  

Do you want to proceed? [y|n]

... output abbreviated.

Composite patch 26609783 successfully applied.
Log file location: /u01/oracle/product/db/12.1/12.1.0.2.0/cfgtoollogs/opatch/opatch2017-09-06_14-16-35PM_1.log

OPatch succeeded.

5. Start your database. When opened, execute datapatch:

[oracle@lx01oric] cd $ORACLE_HOME/OPatch
[oracle@lx01oric OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Wed Sep  6 14:54:49 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_16001_2017_09_06_14_54_50/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...
Bootstrap timed out after 240 seconds
Retrying bootstrap after retryable errors....done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 170814 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    Nothing to roll back
    The following patches will be applied:
      26609783 (DATABASE PATCH SET UPDATE 12.1.0.2.170814)

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 26609783 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26609783/21481899/26609783_apply_CDBVEG_CDBROOT_2017Sep06_15_01_26.log (no errors)
Patch 26609783 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/oracle/cfgtoollogs/sqlpatch/26609783/21481899/26609783_apply_CDBVEG_PDBSEED_2017Sep06_15_09_34.log (no errors)
SQL Patching tool complete on Wed Sep  6 15:18:53 2017
[oracle@lx01oric OPatch]$ vi /u01/oracle/cfgtoollogs/sqlpatch/26609783/21481899/26609783_apply_CDBVEG_PDBSEED_2017Sep06_15_09_34.log

6. If applicable, activate the OJVM Mitigation patch.

According to Oracle, this patch is applicable in "situations where the latest OJVM PSU cannot be installed immediately... The "Mitigation Patch" is an interim solution to protect against all currently known (Jul 2015) Oracle JavaVM security vulnerabilities in the database until such time as the OJVM PSU can be installed."

Since this is my situation, I install the mitigation patch:

[oracle@lx01oric 26609783]$ cd $ORACLE_HOME/rdbms/admin
[oracle@lx01oric admin]$ sqlplus / as sysdba @dbmsjdev.sql
[oracle@lx01oric admin]$ sqlplus / as sysdba
SQL> exec dbms_java_dev.disable;
PL/SQL procedure successfully completed.
SQL> 

How to solve OUI-10197:Unable to create a new Oracle Home during cloning

Short background:

After a failed attempt to clone an Oracle 12c installation, using Oracle's perl script clone.pl, like this:

oracle@myserver:[DBSID]# cd /u01/oracle/product/12102/clone/bin
oracle@myserver:[DBSID]# perl clone.pl ORACLE_HOME=/u01/oracle/product/12102 ORACLE_HOME_NAME=12102 ORACLE_BASE=/u01/oracle OSDBA_GROUP=dba

the following error was thrown:

OUI-10197:Unable to create a new Oracle Home at /u01/oracle/product/12102. Oracle Home already exists at this location. Select another location.
SEVERE:OUI-10197:Unable to create a new Oracle Home at /u01/oracle/product/12102. Oracle Home already exists at this location. Select another location.

The solution is to detach the Oracle Home from the Inventory:

oracle@myserver:[DBSID]# ./runInstaller -detachHome ORACLE_HOME=/u01/oracle/product/12102 invPtrLoc=/u01/oracle/product/12102/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 7814 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'DetachHome' was successful.

You can now rerun clone.pl.
Note that you do not need to physically remove files from disk and unzip new ones, since your Inventory is now unaware of the previously failed installation attempt.

Simply run your clone again. Log output below.

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 1810 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 7814 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-09-07_09-28-15AM. Please wait ...You can find the log of this install session at:
 /home/oracle/oraInventory/logs/cloneActions2017-09-07_09-28-15AM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..........
Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The cloning of 12102 was successful.
Please check '/home/oracle/oraInventory/logs/cloneActions2017-09-07_09-28-15AM.log' for more details.
Setup Oracle Base in progress.
Setup Oracle Base successful.
..................................................   95% Done.
As a root user, execute the following script(s):
        1. /u01/oracle/product/12102/root.sh
..................................................   100% Done.

How to open and close a pluggable database in 12c multitenant database

Connect to the container database:

sqlplus sys@cdbveg as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 6 13:22:57 2017

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

Enter password:

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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Check status:

select con_id, dbid, name,open_mode from v$containers;

CON_ID	DBID	NAME	OPEN_MODE
1	3465911448	CDB$ROOT	READ WRITE
2	374976711	PDB$SEED	READ ONLY
3	53565692	PDBVEG1	MOUNTED

Open the pluggable database;

alter pluggable database pdbveg1 open;

Check status again:

CON_ID	DBID	NAME	OPEN_MODE
1	3465911448	CDB$ROOT	READ WRITE
2	374976711	PDB$SEED	READ ONLY
3	53565692	PDBVEG1	READ WRITE

To close the PDB and bring it back to a mounted state:

alter pluggable database pdbveg1 close;

Solution for ORA-01442: column to be modified to NOT NULL is already NOT NULL during online redefinition

When trying to execute dbms_redefinition.copy_table_dependents, like this:

whenever sqlerror exit
set serveroutput on
set feedback off
set verify   off
set timing on

DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'USER1',
orig_table=>'DOCUMENTS',
int_table=>'DOCUMENTS_INTERIM',
copy_indexes=>0,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

You get the following error:

ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646

Cause:
Your interim table has NOT NULL constraints. This is easy to overlook, particulary if you create your interim table using CTAS ("Create Table As Select") statement.
Your interim table should not have any constraints before you execute the copy_table_dependents procedure.

Solution:
Drop the NOT NULL constraint, and retry the operation:

SQL> alter table user1.documents_interim drop constraint SYS_C0018782;

Table altered.

Note that you do not have to abort the redefinion procedure at this point, and start all over again.

Simply drop the constraint, and retry your operation.