How to change the start time for a maintenance window

For example, if you want to change the start time for the MONDAY_WINDOW from the default 22:00 to 23:59, use the following syntax:

BEGIN
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=23;BYMINUTE=59;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
END;
/

More on finding failed autostats jobs

In a previous post, I showed how to check if an autostats job had succeeded.

Recently I had to unlock table stats on a very large partitioned table, and then lock down older partition stats explicitly, to allow Oracle to gather statistics for the most recent partitions automatically with the auto stats job.

I wanted to know if Oracle was able to analyze the partitions within the designated 4 hours of weekday window.
When you read the code snippet below, please keep in mind that I have defined my weekday windows to start at 23:59 every evening.

I found the following query to be useful:

select distinct 
       opid, 
       target,
       target_type, 
       to_char(start_Time,'dd.mm.yyyy hh24:mi:ss') "starttime",
       to_char(end_time,'dd.mm.yyyy hh24:mi:ss')   "endtime",
       status
from dba_optstat_operation_tasks 
where opid IN (
            SELECT ID
            FROM DBA_OPTSTAT_OPERATIONS
            WHERE OPERATION LIKE 'gather_database_stats (auto)'
            AND TO_CHAR(START_TIME,'dd.mm.yyyy hh24:mi') = '09.04.2018 23:59'
)
and target like '%SALES%';

The result:

OPID	TARGET	TARGET_TYPE	starttime	endtime	STATUS
47815	SH.SALES	TABLE	09.04.2018 23:59:39	10.04.2018 03:25:17	COMPLETED
47815	SH.SALES.P_SALES_DATA_2016	TABLE PARTITION	10.04.2018 03:25:41	10.04.2018 03:59:00	TIMED OUT
47815	SH.SALES.P_SALES_DATA_MAX	TABLE PARTITION	10.04.2018 03:25:33	10.04.2018 03:25:33	COMPLETED

All in all, the autostat job started on April 9th, 2018 at 23:59, had the following outcome:

select status,count(*)
from dba_optstat_operation_tasks 
where opid IN (
            SELECT ID
            FROM DBA_OPTSTAT_OPERATIONS
            WHERE OPERATION LIKE 'gather_database_stats (auto)'
            AND TO_CHAR(START_TIME,'dd.mm.yyyy hh24:mi') = '09.04.2018 23:59'
)
group by status;

STATUS	COUNT(*)
FAILED	1
TIMED OUT	12
COMPLETED	339

From the output you can now proceed to find out why the analysis failed or timed out. The query below will list these objects for you:

select distinct opid, 
       target,
       target_type, 
    to_char(start_Time,'dd.mm.yyyy hh24:mi:ss') "starttime",
    to_char(end_time,'dd.mm.yyyy hh24:mi:ss')   "endtime",
    status
from dba_optstat_operation_tasks 
where opid IN (
            SELECT ID
            FROM DBA_OPTSTAT_OPERATIONS
            WHERE OPERATION LIKE 'gather_database_stats (auto)'
            AND TO_CHAR(START_TIME,'dd.mm.yyyy hh24:mi') = '09.04.2018 23:59'
)
and STATUS IN ('TIMED OUT','FAILED');

The error

SP2-0552: Bind variable "B2" not declared.

can arise when using incorrect declarations in your SQL scripts.

For example, I had the follwing in a script:

var B2 DATE;
EXEC :B2 := to_date('22.02.2018','dd.mm.yyyy');

For sqlplus to accept this string, you need to declare your string as VARCHAR2, even though you intend to use datatype DATE in your query.

Declare it as VARCHAR instead:

var B2 VARCHAR2(10);

How to load a plan from the cursor cache into the SMB

set serveroutput on
VARIABLE cnt NUMBER

-- Specifying both the SQL ID and the plan hash value:
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
                    sql_id => '5abzqhtfcvr73' -
                    ,plan_hash_value =>7104589 -
                    ,fixed => 'YES' -
                    ,enabled=>'YES');


-- without a specific plan hash value:
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
                    sql_id => '5abzqhtfcvr73' -
                    ,fixed => 'YES' -
                    ,enabled=>'YES');
print :cnt;
exit

How to recursively zip a folder and its subfolders and add password protetion+encryption

Below I am compressing all files and subfolders in the folder /home/oracle/outputfiles:

cd /home/oracle/outputfiles
zip -r --encrypt myzipfile.zip *

You will be prompted for a password, which has to be verified.
If you are located in the parent directory, use

zip -r -q myzipfile mydir

where myzipfile is the name of the resulting zip file, and mydir is the name of the directory.
The .zip extension will be added to myzipfile automatically.

How to install APEX in an existing database

I have based my receipe on Tim Hall's instructions found at www.oracle-base.com, and I do not want to take credit for this. But the installation steps was executed by myself, and my notes may come in handy for others.

In my case, a developer needed access to the APEX_JSON package, and as confirmed with Oracle Development, the package is not available outside the APEX software stack. However, when APEX is installed in an existing database, you can start using the functionality in this package even if you don't need anything else that comes with APEX.

Here is how I performed the minimal installation:


1. Create a dedicated tablespace:

create bigfile tablespace apex datafile '/u02/oradata/testdb01/apex.dbf' size 128M autoextend on next 32M maxsize unlimited;

2. Download the latest APEX installation bundle from http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html#close, and upload it to your server

3. Unzip the file

4. change directory to the apex folder

5. Install the software.
The header of the apexins.sql file states its usage:

Arguments:
Position 1: Name of tablespace for Application Express application user
Position 2: Name of tablespace for Application Express files user
Position 3: Name of temporary tablespace or tablespace group
Position 4: Virtual directory for APEX images

sqlplus / as sysdba @apexins.sql APEX APEX TEMP /i/

6. Set the admin password:

sqlplus / as sysdba @apxchpwd.sql

7. Create the APEX_LISTENER and schema APEX_REST_PUBLIC_USER:

sqlplus / as sysdba @apex_rest_config.sql

There are additional, optional steps to be configured, depending on your needs. See this article at Oracle-base.com

How to set up End-to-End tracing

Based on Performing Application Tracing in Oracle Database 12.1 SQL Tuning Guide

Add the following to your application code:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'PublishInfoModule';
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>'BroadcastPublish');
exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'Broadcast', action_name=>NULL);

Right before a SELECT statement:

exec DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'GetRows');

Right before an UPDATE statement:

exec DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'MarkAsPublished');

Enable statistics gathering for End-to-End Tracing. Note that the names of service, module and actions are case sensitive, and need to be referred to as they are found in v$sessions and v$services:

exec DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name=>'BroadcastPublish');
exec  DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name   =>'MarkAsPublished', WAITS=>TRUE,binds=>TRUE,plan_stat=>'FIRST_EXECUTION');

Go to the trace file location, found as a subdirectory in the location specified by diagnostic_dest:

show parameter diagnostic_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/oracle

In my case:

cd /u01/oracle/diag/rdbms/proddb01/proddb01/trace

You should now find files named according to your SET TRACEFILE_IDENTIFIER command previously:

-rw-r-----  1 oracle dba 732258822 Feb 26 07:49 alert_proddb01.log
-rw-r-----  1 oracle dba     24398 Feb 26 08:21 proddb01_ora_54248.trc
-rw-r-----  1 oracle dba    120418 Feb 26 08:21 proddb01_ora_54248.trm
-rw-r-----  1 oracle dba   3077570 Feb 26 08:26 proddb01_ora_35967_PublishInfoModule.trm
-rw-r-----  1 oracle dba 133643579 Feb 26 08:26 proddb01_ora_35967_PublishInfoModule.trc

Disable statistics gathering, and disable the trace:

exec DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name=>'BroadcastPublish');
exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'proddb01',module_name=>'Broadcast',action_name   =>'MarkAsPublished');

You can now format these using tkprof:

tkprof /u01/oracle/diag/rdbms/proddb01/proddb01/trace/proddb01_ora_35967_PublishInfoModule.trc
       /u01/oracle/admin/proddb01/sql/tuning/tkprof2/tkprof_2.out 
       waits=yes 
       sort=EXEDSK 
       insert=insert.sql 
       sys=no 
       explain=broadcaster/**** 
       record=tkprof_sqlstmts.sql 
       table=sys.PLAN_TABLE$
  

The output will be the file /u01/oracle/admin/proddb01/sql/tuning/tkprof2/tkprof_2.out

tkprof has many options and you should take some time to get familiarized with the tool before you start. A key point is to minimize the information so that you find what's relevant to you.

For a shortlist of commands, see this post.


If you have many trace files generated by multiple sessions, you need to use another tool called trcsess to merge all these into one single trace file that you then format with tkprof, which I will demonstrate next.

In the following example, I am creating tracefiles for a specific action set in the application code.
The application code uses this code:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'Reporter';

BEGIN 
  DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'getNextOutlook/actions/report');
END;
/

So I will execute

cd trace
trcsess output=getNextOutlook.out action='getNextOutlook/actions/report' pks_*report*.trc

Which will search all trace files matching the wildkard pks_*report*.trc and generate the file getNextOutlook.out