.patch_storage growing in Oracle Home

Starting with Oracle 18, the Oracle Home is growing more than expected. It appears that the .patch_storage folder is taking up quite some storage. There are notes on this on MOS and one of them is mentioning that you can also check if there are subfolders, which are of patches that are not installed (anymore). (Doc ID 550522.1)

You will see under section 3) of that note a description.

The regular opatch util cleanup does not clean all that you want it seems.

I have written the following script, which you may use at your OWN RISK.

#!/bin/sh
# cleanup the patch storage for Opatch
# not all things are cleaned, so you may want to delete old patch directories that are backed up but not installed

# parameter: $ORACLE_HOME

if [ $# -ne 1 ];then
  echo "Usage: clean_opatch_storage.sh "
  exit
else
  OH=$1
fi

# where to put the rm commands
rmscript=/tmp/rmdirs.sh
echo > $rmscript

# show size before
du -s $OH/.patch_storage

# list contents
ls -l $OH/.patch_storage

# save inventory
$OH/OPatch/opatch lsinventory > /tmp/lsinv.log
export LOG=/tmp/lsinv.log

# list folders that are of patches:
cd $OH/.patch_storage

# make a list of the patch numbers in this directory
mylist=$(find .  -mindepth 1 -maxdepth 1 -type d -name '*[0-9]*_*' -printf '%P\n' | awk '{split($0,a,"_"); print a[1]}' )

# check if those patches are in inventory log, if not remove the directory
for var1 in $mylist
do
  FOUND=$(cat $LOG | grep -w $var1  | wc -l)
  if [ $FOUND -eq 0 ]; then
    echo $var1  Not found in inventory, so this directory should go
    mydel=$(find .  -mindepth 1 -maxdepth 1 -type d -name $var1'_*' -printf '%P\n' )
    echo rm -rf $mydel >> $rmscript
  else
    echo $var1  Found in inventory, so this directory should be kept
  fi;
done

# cat the script
echo This will be done:
cat $rmscript

read -r -p "Are you sure? [y/N] " response
case "$response" in
    [yY][eE][sS]|[yY])
        chmod 740 $rmscript
        $rmscript
        ;;
    *)
        echo "Cancelled deletion!"
        ;;
esac 

create a manual backup for an Autonomous Transaction Processing database

When creating a manual backup for Autonomous Transaction Database (on Oracle Cloud Infrastructure) you will encounter a number of requirements. Once setup, it will be pretty straightforward. I will walk you through all steps. 

Note that I'm not an Oracle employee, and things will change in this world. Always consult the documentation to verify the steps you need to take.

Create een Autonomous Transaction Processing Database

• Login to you Oracle Cloud Infrastructure
• Go to Autonomous Transaction Processing
• Click the button "[ Create Autonomous Transaction Processing Database ]"
• Supply a name, password (1) and select your licensing model
• Creating the database will take a few minutes, once it shows "Available" you can go on to the next step.

Download Client Credentials

• Click the name of the database
• Click on "[ Service Console ]"
• Click on "[ Administration ]"
• Click on "Download a Connection Wallet" and safe it in Downloads folder 
• Note down the password (2) you create here

Connect with SQL Developer

• Start up SQL Developer
• Create a new connection
• Username is Admin and password (1) 
• Connection type is Cloud PDB
• Configuration File is the wallet zip you downloaded (don't unzip)
• Keystore password is password (2)
• Service can be a service as contained in the wallet, e.g. the dbname_low

Create a Service User

• In Oracle Cloud Infrastructure, go to Menu>Identity>Users
• Create a user, sa_backup  (or some name for backups)
• Select that user when created, and click Groups
• Add the user to group Administrators
• Click Authority Tokens, create token
• Note down the token password (3)

Settings for defaults

• Create credential in SQL Developer

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'DEF_CRED_NAME',
username => 'sa_backup',password => 'password as set above in (3)');
END;
/

alter database property set default_credential = 'ADMIN.DEF_CRED_NAME';

• Set the default bucket, change according to your environment:

ALTER DATABASE PROPERTY SET default_bucket='https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/tenancy_name';


Create a bucket

• Create a bucket with the correct name:
• Menu>Object Storage>Object Storage>"[ Create Bucket ]"
• Give the bucket the exact name backup_databasename, for example backup_mydb01 

Now go back to the ATP database in the Oracle Cloud Infrastructure, and create a manual backup with the button, this will run for a few minutes.

Create a 12.2 database with dbca silent - with minimal components

We recently worked on an upgrade from 11.2 to 12.2. This made us think about the environment and the options we needed - or not needed. So we tried to setup the environment with a minimum amount of options. Why? simply because we don't need these components and will never use them in the future. And, as Mike Dietrich states - and I agree on him, future upgrades will run faster.

Default, we get a whole bunch of options installed. But the following gave us a small environment, with XML and JAVA only.

 $ORACLE_HOME/bin/dbca -silent -createDatabase -responseFile SMALLDB.rsp   

This SMALLDB.rsp responsefile looks like (no, these are not my passwords or db name).

 responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0  
 gdbName=SMALLDB01  
 databaseConfigType=SI  
 createAsContainerDatabase=false  
 templateName=SMALLDB.dbt  
 sysPassword=PassWord#123  
 systemPassword=PassWord#123  
 dvConfiguration=false  
 olsConfiguration=false  
 dbOptions=JSERVER:true,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,APEX:false,DV:false  
 datafileDestination=/data/oradata/  
 recoveryAreaDestination=/data/fra/  
 storageType=FS  
 characterSet=AL32UTF8  
 redoLogFileSize=400  
 listeners=LISTENER  
 initParams=db_recovery_file_dest_size=200G,processes=1400,sga_max_size=8G,sga_target=2G  

What we need, is to setup the template in $ORACLE_HOME/assistants/dbca/templates/SMALLDB.dbt .
You can do this with a graphical dbca, or copy and change an existing template. Note that the important part is in the 'CommonAttributes'. Where we use dbOptions in the responsefile, these options will be not-activated (v$option shows them as FALSE). But the components get installed. If you want them to not be installed, you will need to put that in the template file as shown below.

 <DatabaseTemplate name="SMALLDB" description="Small install for database" version="12.2.0.1.0">  
   <CommonAttributes>  
    <option name="OMS" value="false"/>  
    <option name="JSERVER" value="true"/>  
    <option name="SPATIAL" value="false"/>  
    <option name="IMEDIA" value="false"/>  
    <option name="ORACLE_TEXT" value="false">  
      <tablespace id="SYSAUX"/>  
    </option>  
    <option name="SAMPLE_SCHEMA" value="false"/>  
    <option name="CWMLITE" value="false">  
      <tablespace id="SYSAUX"/>  
    </option>  
    <option name="APEX" value="false"/>  
    <option name="DV" value="false"/>  
   </CommonAttributes>  
   <Variables/>  
   <CustomScripts Execute="false"/>  
   <InitParamAttributes>  
    <InitParams>  
      <initParam name="db_name" value=""/>  
      <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE=XDB)"/>  
      <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>  
      <initParam name="compatible" value="12.2.0"/>  
      <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>  
      <initParam name="processes" value="300"/>  
      <initParam name="undo_tablespace" value="UNDOTBS1"/>  
      <initParam name="control_files" value="(&quot;/data/oradata/{DB_UNIQUE_NAME}/control01.ctl&quot;, &quot;/data/fra/{DB_UNIQUE_NAME}/control02.ctl&quot;)"/>  
      <initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>  
      <initParam name="db_recovery_file_dest" value="/data/fra/{DB_UNIQUE_NAME}"/>  
      <initParam name="audit_trail" value="db"/>  
      <initParam name="nls_territory" value="AMERICA"/>  
      <initParam name="db_block_size" value="8192"/>  
      <initParam name="open_cursors" value="300"/>  
      <initParam name="nls_language" value="AMERICAN"/>  
      <initParam name="db_recovery_file_dest_size" value="200" unit="GB"/>  
    </InitParams>  
    <MiscParams>  
      <databaseType>MULTIPURPOSE</databaseType>  
      <maxUserConn>20</maxUserConn>  
      <percentageMemTOSGA>20</percentageMemTOSGA>  
      <customSGA>false</customSGA>  
      <characterSet>AL32UTF8</characterSet>  
      <nationalCharacterSet>AL16UTF16</nationalCharacterSet>  
      <archiveLogMode>false</archiveLogMode>  
      <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>  
    </MiscParams>  
    <SPfile useSPFile="true">/opt/oracle/product/12.2.0/dbhome_1/dbs/spfile{SID}.ora</SPfile>  
   </InitParamAttributes>  
   <StorageAttributes>  
    <ControlfileAttributes id="Controlfile">  
      <maxDatafiles>100</maxDatafiles>  
      <maxLogfiles>16</maxLogfiles>  
      <maxLogMembers>3</maxLogMembers>  
      <maxLogHistory>1</maxLogHistory>  
      <maxInstances>8</maxInstances>  
      <image name="control01.ctl" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
      <image name="control02.ctl" filepath="/data/fra/{DB_UNIQUE_NAME}"/>  
    </ControlfileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf" con_id="1">  
      <tablespace>SYSAUX</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">550</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">10240</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/system01.dbf" con_id="1">  
      <tablespace>SYSTEM</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">700</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">10240</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/temp01.dbf" con_id="1">  
      <tablespace>TEMP</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">20</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">640</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf" con_id="1">  
      <tablespace>UNDOTBS1</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">200</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">5120</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <DatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/users01.dbf" con_id="1">  
      <tablespace>USERS</tablespace>  
      <temporary>false</temporary>  
      <online>true</online>  
      <status>0</status>  
      <size unit="MB">5</size>  
      <reuse>true</reuse>  
      <autoExtend>true</autoExtend>  
      <increment unit="KB">1280</increment>  
      <maxSize unit="MB">-1</maxSize>  
    </DatafileAttributes>  
    <TablespaceAttributes id="SYSAUX" con_id="1">  
      <temporary>false</temporary>  
      <defaultTemp>false</defaultTemp>  
      <undo>false</undo>  
      <local>true</local>  
      <blockSize>-1</blockSize>  
      <allocation>1</allocation>  
      <uniAllocSize unit="KB">-1</uniAllocSize>  
      <initSize unit="KB">64</initSize>  
      <increment unit="KB">64</increment>  
      <incrementPercent>50</incrementPercent>  
      <minExtends>1</minExtends>  
      <maxExtends>4096</maxExtends>  
      <minExtendsSize unit="KB">64</minExtendsSize>  
      <logging>true</logging>  
      <recoverable>false</recoverable>  
      <maxFreeSpace>0</maxFreeSpace>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="SYSTEM" con_id="1">  
      <temporary>false</temporary>  
      <defaultTemp>false</defaultTemp>  
      <undo>false</undo>  
      <local>true</local>  
      <blockSize>-1</blockSize>  
      <allocation>3</allocation>  
      <uniAllocSize unit="KB">-1</uniAllocSize>  
      <initSize unit="KB">64</initSize>  
      <increment unit="KB">64</increment>  
      <incrementPercent>50</incrementPercent>  
      <minExtends>1</minExtends>  
      <maxExtends>-1</maxExtends>  
      <minExtendsSize unit="KB">64</minExtendsSize>  
      <logging>true</logging>  
      <recoverable>false</recoverable>  
      <maxFreeSpace>0</maxFreeSpace>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/system01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="TEMP" con_id="1">  
      <temporary>true</temporary>  
      <defaultTemp>true</defaultTemp>  
      <undo>false</undo>  
      <local>true</local>  
      <blockSize>-1</blockSize>  
      <allocation>1</allocation>  
      <uniAllocSize unit="KB">-1</uniAllocSize>  
      <initSize unit="KB">64</initSize>  
      <increment unit="KB">64</increment>  
      <incrementPercent>0</incrementPercent>  
      <minExtends>1</minExtends>  
      <maxExtends>0</maxExtends>  
      <minExtendsSize unit="KB">64</minExtendsSize>  
      <logging>true</logging>  
      <recoverable>false</recoverable>  
      <maxFreeSpace>0</maxFreeSpace>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/temp01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="UNDOTBS1" con_id="1">  
      <temporary>false</temporary>  
      <defaultTemp>false</defaultTemp>  
      <undo>true</undo>  
      <local>true</local>  
      <blockSize>-1</blockSize>  
      <allocation>1</allocation>  
      <uniAllocSize unit="KB">-1</uniAllocSize>  
      <initSize unit="KB">512</initSize>  
      <increment unit="KB">512</increment>  
      <incrementPercent>50</incrementPercent>  
      <minExtends>8</minExtends>  
      <maxExtends>4096</maxExtends>  
      <minExtendsSize unit="KB">512</minExtendsSize>  
      <logging>true</logging>  
      <recoverable>false</recoverable>  
      <maxFreeSpace>0</maxFreeSpace>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <TablespaceAttributes id="USERS" con_id="1">  
      <temporary>false</temporary>  
      <defaultTemp>false</defaultTemp>  
      <undo>false</undo>  
      <local>true</local>  
      <blockSize>-1</blockSize>  
      <allocation>1</allocation>  
      <uniAllocSize unit="KB">-1</uniAllocSize>  
      <initSize unit="KB">128</initSize>  
      <increment unit="KB">128</increment>  
      <incrementPercent>0</incrementPercent>  
      <minExtends>1</minExtends>  
      <maxExtends>4096</maxExtends>  
      <minExtendsSize unit="KB">128</minExtendsSize>  
      <logging>true</logging>  
      <recoverable>false</recoverable>  
      <maxFreeSpace>0</maxFreeSpace>  
      <autoSegmentMgmt>true</autoSegmentMgmt>  
      <bigfile>false</bigfile>  
      <datafilesList>  
       <TablespaceDatafileAttributes id="/data/oradata/{DB_UNIQUE_NAME}/users01.dbf"/>  
      </datafilesList>  
    </TablespaceAttributes>  
    <RedoLogGroupAttributes id="1">  
      <reuse>false</reuse>  
      <fileSize unit="KB">204800</fileSize>  
      <Thread>1</Thread>  
      <member ordinal="0" memberName="redo01.log" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
    </RedoLogGroupAttributes>  
    <RedoLogGroupAttributes id="2">  
      <reuse>false</reuse>  
      <fileSize unit="KB">204800</fileSize>  
      <Thread>1</Thread>  
      <member ordinal="0" memberName="redo02.log" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
    </RedoLogGroupAttributes>  
    <RedoLogGroupAttributes id="3">  
      <reuse>false</reuse>  
      <fileSize unit="KB">204800</fileSize>  
      <Thread>1</Thread>  
      <member ordinal="0" memberName="redo03.log" filepath="/data/oradata/{DB_UNIQUE_NAME}/"/>  
    </RedoLogGroupAttributes>  
   </StorageAttributes>  
 </DatabaseTemplate>  

Let the dbca run, which took me some 20mins. After that, I only manually de-install the Oracle Workspace Manager:

 $ORACLE_HOME/bin/sqlplus -s "/as sysdba" @?/rdbms/admin/owmuinst.plb   

Now we have the following installed, which is just fine for our environment:

 SQL> select comp_id, comp_name from dba_registry;  

 COMP_ID   COMP_NAME  
 --------- ----------------------------------------  
 CATALOG   Oracle Database Catalog Views  
 CATPROC   Oracle Database Packages and Types  
 JAVAVM    JServer JAVA Virtual Machine  
 XML       Oracle XDK  
 CATJAVA   Oracle Database Java Packages  
 XDB       Oracle XML Database  

 6 rows selected.  

If you skip JAVA as well (in your responsefile and template), you will get only CATALOG, CATPROC and XDB (after removing OWS manually as seen above). That is the minimum you can get.

Cloud Control 13c - graph your job history

I wanted to see all my Cloud Control 13c jobs that ran during the last day.
How long did they take, did they run parallel ?

The SYSMAN.MGMT$JOB_EXECUTION_HISTORY contains all info needed, but it may be hard to interpret an draw your conclusions.
If you like to see when job overlap (and possible a struggle for CPU or I/O), you will need a graph.

With the following query you will get a (text-based, very eighties) graph from SQL.

Hope it helps in your environment.

 set lines 999 trimspool on pages 999  
 col job_name format a25  
 col status format a11  
 col mins format 9999  
 col graph format a80  
 SELECT distinct job_name,  
     to_char(CAST((FROM_TZ(CAST(START_TIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Brussels') AS DATE),'YYYY.MM.DD-HH24:MI:SS') "Start time",  
     to_char(CAST((FROM_TZ(CAST( END_TIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Brussels') AS DATE),'YYYY.MM.DD-HH24:MI:SS') "End time",  
     STATUS,  
     round((end_time - start_time) * 24 * 60 ) "Mins",  
     rpad('-',round(80*(start_time - (sysdate -1))),'-') ||  
     rpad('X',round(80*(end_time - start_time  )),'X') ||  
     rpad('X',  
     80-(round(80*(start_time-(sysdate-1))))  
      -(round(80*( end_time-start_time)))  
     ,'-') "graph"  
 FROM sysman.MGMT$JOB_EXECUTION_HISTORY  
 where start_time > sysdate - 1  
 and status not in ('Waiting','Scheduled')  
 and (end_time - start_time) * 24 * 60  > 10  
 order by 2 ;  

It shows jobs running longer than 10 minutes only. The graph will be exactly 80 char width, you may change that to your liking.
Due to the rounding, it may be that there seems no overlap, but it can be overlapping a few minutes. Bear this in mind.

Sample Output:

 
 JOB_NAME                 Start time           End time            STATUS       Mins graph  
 ------------------------- ------------------- ------------------- ----------- ----- --------------------------------------------------------------------------------  
 BACKUP_DATABASE01        2017.06.21-14:40:03 2017.06.21-14:55:09 Succeeded       15 ---------XX---------------------------------------------------------------------  
 CLEANUP_JOB_DAILY        2017.06.21-19:00:05 2017.06.21-19:20:54 Succeeded       21 -----------------------XX-------------------------------------------------------  
 DEMO_BATCH_PROCES        2017.06.21-20:00:05 2017.06.22-00:02:13 Succeeded      242 ---------------------------XXXXXXXXXXXXXX---------------------------------------  
 BACKUP_DATABASE02_INCR   2017.06.21-22:00:05 2017.06.22-01:02:19 Succeeded      182 ---------------------------------XXXXXXXXXXX------------------------------------  
 JUST_ANOTHER_BACKUP      2017.06.21-23:00:02 2017.06.22-05:10:00 Succeeded      370 -------------------------------------XXXXXXXXXXXXXXXXXXXXXX---------------------  
 SMALL_REPORT_DAILY       2017.06.22-01:30:02 2017.06.22-01:49:44 Succeeded       20 ---------------------------------------------XX---------------------------------  
 RUN_REPORT_JOB           2017.06.22-02:00:05 2017.06.22-03:10:25 Succeeded       70 -----------------------------------------------XXXXX----------------------------  
 CREATE_MONTHLY_OVERVIEW  2017.06.22-04:00:01 2017.06.22-04:13:07 Succeeded       13 -----------------------------------------------------XX-------------------------