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.

A workaround for ORA-12008 and ORA-14400 during online redefinition

To introduce partitions in one of my tables, I was using online redefinition of a rather large table.
The following error was thrown after about 30 minutes after having started the redefinition with dbms_redefintion.start_redef:

begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P004
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 75
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459
ORA-06512: at line 2

It turned out that there were NULL values in the column that would be the future partition key.
Everything was explained very well in Doc ID 2103273.1 "ORA-14400: inserted partition key does not map to any partition, with possible ORA-12008: error in materialized view refresh path".

You can solve this in two ways:

a) find the rows with null values, and update them

or

b) use an overflow partition which will work as a "catch all" basket for rows that can't be mapped to a specific partition. NULL values sort in this category.

Since I was using interval range partitioning, I had to choose the former of the two options. If this is not possible, you can't use interval partitioning, and need to explicitly define every partition + your overflow partition in your interim table.

How to explain a SQL statement using bind variables

Sometimes I am asked to analyze an SQL statement picked up from Enterprise Manager Cloud Control.
These SQL statements only reveal the bind variables used, and not their actual values.

To get a more realistic explain plan, ask your developers to provide you with the actual literal values used in the SQL statement, and then prepare a script that contains the following:

connect username/password
SET TIMING ON
SET LINESIZE 200
SET PAGESIZE 0

var b9 number;
var b8 number;
var b7 number;
var b6 varchar2(3);
var b5 varchar2(3);
var b4 varchar2(4);
var b3 varchar2(1);
var b2 varchar2(2);
var b1 varchar2(2);

exec :B9 := 12345;
exec :B8 := 6127737;
exec :B7 := 2013;
exec :B6 := 'KOC';
exec :B5 := 'PPQ';
exec :B4 := 'OOPQ';
exec :B3 := '7';
exec :B2 := 'YM';
exec :B1 := 'WT';

EXPLAIN PLAN FOR
  SELECT ...
  FROM ...
  WHERE COL1 NOT IN (:B6, :B5, :B4)
  AND COL2 = :8
  AND COL3 = :9;


SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Run the script using sqlplus.

Such a script can also come in handy if you're asked to perform changes to the tables' structure, and then execute the actual script to see how much time it takes to execute.
In this case, remove the EXPLAIN PLAN clause and the call to DBMS_XPLAN.DISPLAY, and you'll have all you need to execute the actual SQL statements you're tuning.

How to move a partition online from Oracle 12.1 and onwards

From Oracle version 12.1 and ownwards, moving partitions and subpartitions becomes non-blocking DDL operations. DML against the table can continue while the subobject is being moved. Oracle guarantees that global indexes are maintained during the move operation, so you no longer have to specify the "UPDATE INDEXES ONLINE"-clause.

Remember that skipping this clause will trigger Asynchronous Global Index Maintenance during the nightly maintenance window.

To move a subpartition to a new tablespace:

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312 ONLINE
TABLESPACE MYTABSPC;

To compress the subpartition data at the same time, use

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312_P_OPLSUM ONLINE 
TABLESPACE MYTABSPC 
ROW STORE COMPRESS ADVANCED;

To move a partition to a new tablespace using a DOP of 4 while updating any global indexes defined on the table:

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_05 ONLINE
TABLESPACE NEWTABLESPACE
PARALLEL 4;

To compress the partition data at the same time, use

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_06 ONLINE
TABLESPACE NEWTABLESPACE
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Remember to change the properties for the partition and the table, too:

ALTER TABLE MYUSER.MYTABLE
MODIFY DEFAULT ATTRIBUTES FOR PARTITION P201312
TABLESPACE MYTABSPC;

ALTER TABLE MYUSER.MYTABLE 
MODIFY DEFAULT ATTRIBUTES 
TABLESPACE MYTABSPC;

A simple query to find how much space the partitions are using would be:

select partition_name,tablespace_name, round(sum(bytes)/1024/1024/1024,1) "GB"
from dba_segments
where segment_name='MYTABLE'
group by partition_name,tablespace_name
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	GB
SYS_SUBP102302	DATA2	8,9
SYS_SUBP102303	DATA2	2
SYS_SUBP102304	DATA2	0
SYS_SUBP120071	DATA2	7,9
SYS_SUBP120072	DATA	1,8
SYS_SUBP120073	DATA2	0
SYS_SUBP12401	DATA	129,5

Source: Oracle Documentation, New Features list, Version 12.1.0.1. See section "2.2.3.4 ONLINE Move Partition"