11 September 2017

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.

22 June 2017

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

17 May 2017

Controlfile autobackup location: default

I am very confused on the location of controlfile autobackups. Now I know how it works, but there seems to be some inconsistency.

Please have a look at the following examples to see what I mean.

RMAN> show all;
(Output trimmed for readability)

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default


So the autobackup is ON and the format is set to '%F' default. Let us backup any recent archivelog, to trigger the autobackup.


RMAN> backup archivelog sequence 51;
(Backup output deleted for readability)

Starting Control File and SPFILE Autobackup at 17-MAY-17
piece handle=/data/backup/DEMODB12/autobackup/2017_05_17/o1_mf_s_944207636_dkqsco2v_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-17


Note that the autobackup is written in the FRA. This follows the manual https://docs.oracle.com/database/121/BRADV/rcmconfb.htm#GUID-98BA7492-7108-4DF9-B5A2-21957072E308


Let us add a small string in the format, 'CTRL_%F'. Make sense right?


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'CTRL_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'CTRL_%F';
new RMAN configuration parameters are successfully stored

RMAN> backup archivelog sequence 51;
(Backup output deleted for readability)

Starting Control File and SPFILE Autobackup at 17-MAY-17
piece handle=/opt/oracle/product/12.1.0/dbhome_2/dbs/CTRL_c-2980223472-20170517-06 comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-17 


So hold on, the backup is now in $ORACLE_HOME/dbs, but we only changed the name of the backup. Tricky. Let us set it back to the default value (the wrong way).


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'CTRL_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
new RMAN configuration parameters are successfully stored

RMAN> backup archivelog sequence 51;
(Backup output deleted for readability)

Starting Control File and SPFILE Autobackup at 17-MAY-17
piece handle=/opt/oracle/product/12.1.0/dbhome_2/dbs/c-2980223472-20170517-07 comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-17


Now it is set to '%F' again, but still appearing in $ORACLE_HOME/dbs. This is very strange. So we will clear the setting.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
RMAN configuration parameters are successfully reset to default value

RMAN> backup archivelog sequence 51;
(Backup output deleted for readability)

Starting Control File and SPFILE Autobackup at 17-MAY-17
piece handle=/data/backup/DEMODB12/autobackup/2017_05_17/o1_mf_s_944207733_dkqsgo4v_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-17

Now it's back to the FRA again.


So what we learn?
ANY change in the format of the autobackup will move it to the $ORACLE_HOME/dbs. You can set it to your custom path '/u01/mybackup/CTRL_%F' for instance, but if you change it, and provide no path, it will end up in $ORACLE_HOME/dbs.
If you really need it in your FRA, use the clear option to remove the adapted setting.

This may be very important, if you backup the FRA to external media. You may be losing your controlfile autobackup here.

Cheers,
Paul

01 March 2017

Export the jobs from Cloud Control

In case you need to backup your jobs from Cloud Control, you can use emcli for that. This will give you a quick way to backup or export the job for use elsewhere.

You can setup a script like:

EXPJOB=/home/oracle/export_jobs.sh
echo repdir=/backup/jobs > $EXPJOB

$OMSHOME/bin/emcli login -username=sysman -password=PassWord01

# One command to create the job
$OMSHOME/bin/emcli export_jobs -preview | awk '{ print "./emcli 
export_jobs -export_file=$repdir/" $1 ".zip -name=\x27" $1 "\x27" }' | sort >> $EXPJOB

#make it executable
chmod 740 $EXPJOB


# run the export
$EXPJOB


The EXPJOB that you create, will look like:

export repdir=/backup/jobs
./emcli export_jobs -export_file=$repdir/MYJOB01.zip -name='MYJOB01'
./emcli export_jobs -export_file=$repdir/MYJOB02.zip -name='MYJOB02'
./emcli export_jobs -export_file=$repdir/MYJOB03.zip -name='MYJOB03'
./emcli export_jobs -export_file=$repdir/MYJOB04.zip -name='MYJOB04'

If you run it, (from $OMSHOME/bin), it wil generate a seperate zip file for every job.
The zip file contains a single xml file for that job.

If you decide to import it, it will become available in the Job Library.

The import is easily done on the zip file, like:

 ./emcli import_jobs -file=$repdir/MYJOB01.zip

Hope this helps.