Combining schedules with DBMS_SCHEDULER

Suppose I want to create job which will execute every 30 minutes in “working” hours, and at the rest of the day every hour.

For example:
8:00
8:30
9:00
9:30
..
..
18:00
19:00
20:00



With DBMS_JOB I would write something like this in repeat interval:


(CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) IN (8,9,10,11,12,13,14,15,16,17)
THEN TRUNC(SYSDATE,'HH')+30/1440
ELSE TRUNC(sysdate,'HH')+1/24 END)

In DBMS_SCHEDULER I will create two schedules (“WORK”,”NIGHT”) and main schedule ("MAIN_DAY") which will combine those two for job schedule.

(more...)

How to configure Active/Passive Failover cluster using Grid Infrastructure 12cR1

I'm big fan of tools like VirtualBox or VmWare because they offer you opportunity to try various "enterprise" configurations, installations and scenarios on your notebook. Maybe the biggest benefit for my learning lies in these tools.

In this post I will share with you document about creation of Active/Passive failover cluster using Oracle Grid Infrastructure 12cR1 on OEL 6.4. There you can find screenshots taken during my playing in virtual environment packed in PDF (more...)

RMAN Catalog backuppiece located on Tape

I've recorded backups on tape to RMAN repository several times already, but every next time I needed to do that I was searching through notes to find proper procedure.

This time I will note procedure in form of the blog post.

Note!
Test is performed on Oracle version 11.1.0.7.


These were my unsuccessful attempts:


RMAN> run
2> {
3> allocate channel c1 device type 'sbt_tape';
4> send 'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
5> catalog backuppiece 'ARCH_ORCL_rep2dod5_s128878_p1';
(more...)

How to use index to get NULL values

I have learned something new today and this blog post will be (primary) reminder to myself. I am sure that most Oracle DBA’s or Developers are familiar with this trick - so please skip this post if you are one of them :)


My test table will have 1000000 rows with 1000 NULL values for “OBJECT_NAME” column.

Tests are performed on 11gR1 version.

Table/column info:

select c.table_name, c.column_name, c.data_type, c.num_nulls,
t. (more...)

Wrong Result Bug using group-by placement optimization

Last week I’ve mentioned on Twitter that we ran into wrong result bug. We found workaround quickly but I’ve decided to spend some time to reproduce error and write blog post to warn you about this optimizer behavior.
Special thanks to my colleague who spotted odd results which led us to this finding.

My test (virtual) environment is:
OS: Oracle Enterprise Linux 5.8
DB: Oracle EE 11.1.0.7.12

In test I (more...)