Suppose I want to create job which will execute every 30 minutes in “working” hours, and at the rest of the day every hour.
With DBMS_JOB I would write something like this in repeat interval:
In DBMS_SCHEDULER I will create two schedules (“WORK”,”NIGHT”) and main schedule ("MAIN_DAY") which will combine those two for job schedule.
(CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) IN (8,9,10,11,12,13,14,15,16,17)
ELSE TRUNC(sysdate,'HH')+1/24 END)
I'm big fan of tools like VirtualBox
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...)
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.
Test is performed on Oracle version 22.214.171.124.
These were my unsuccessful attempts:
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';
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.
select c.table_name, c.column_name, c.data_type, c.num_nulls,
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 126.96.36.199.12
In test I (more...)
A few months ago I wrote a blog post - Kill stuck Oracle process from OS using DBMS_SCHEDULER
? We had some problems with stuck Oracle processes and I have created external job to kill stuck processes for a quick workaround.
At the end of that post I wrote:
When dbms_scheduler (more...)
In this post I want to explain how to create duplicate database from active 11gR2 database using RMAN. There are many blog posts covering that subject but most of them are covering non-ASM to non-ASM, ASM to ASM or non-ASM to ASM duplications.
I want to cover ASM to non-ASM (more...)
I am regular follower of Oracle-l
mailing list which is great source of knowledge for Oracle experts. Two days ago one Oracle DBA posted question “RMAN restore/recover problem
” which induced me to re-check my knowledge about some RMAN PITR scenarios.
So I’ve performed some tests and decided to (more...)
In this post I want to show how to test what will happen with your cluster if you simply detach shared storage device from it. VirtualBox is great tool for that purpose because you can test such scenario without involving many people or causing any damage.
I am aware that (more...)
Starting with 11g Oracle can perform block media recovery using flashback logs to get good copies of the blocks.
Flashback database is not enabled by default so first step would be to enable it. When enabled a process RVWR (Recovery Writer) copies modified blocks to flashback buffer. Later this buffer (more...)
I’ve installed Oracle Grid Infrastructure on one of my standalone test servers. This software includes Oracle ASM as volume manager and Oracle Restart for managing Oracle components.
As recommended, Grid Infrastructure and RDBMS software are installed under different users.
Grid Infrastructure: 188.8.131.52.0
RDBMS: 11.2.0. (more...)
In this post I will describe how to change Oracle SID using utility DBNEWID. As I can see DBNEWID is available from 9i version but I’ve never heard about it. Till now, I have used procedure where I’m manually editing and re-creating control file.
But I think it’s much better (more...)
This will be just short post about restriction on parallel DML that I wasn't aware of.
Last week we had to perform quick update of several million rows in very big table. Parallel DML has proven to be the best tool to speed up large DML operation.
A DML can (more...)
Last month we had annoying problems caused by stuck Oracle process holding locks and not releasing them. Every few days one process hangs with status “ACTIVE”
and “SQL*Net message from client”
In that state it holds locks and never releases them. As this is active OLTP database, new sessions arrive wanting those locked resources but they could not get them. So after some time you get lots of waiters and if you don’t react quickly maximum number of processes could be reached. Then your database refuses new connections and you're system becomes unresponsive.
To complicate a problem little (more...)
This post could be interesting for those who are running active/passive failover clusters with Oracle Clusterware used for managing resources. In this case owner of the Clusterware installation is not the same as owner of the Oracle RDBMS installation. Among others, Clusterware is assigned for managing listener resource (start,stop,check).
Till 11g version listener logs were by default written in “$ORACLE_HOME/network/log”
but with 11g ADR (Automatic Diagnostic Repository) is introduced and location for listener log is changed. From 11g default location is “$ORACLE_BASE/diag/tnslsnr/<hostname>/listener_<dbname>/trace”
I’ve noticed that when you start listener as owner of Oracle RDBMS everything is working fine. Listener log (more...)
To create database link in another schema you must execute “create database link” logged in that schema or you can use workaround noted in this post:
How to Create a Database Link in Another User’s Schema
Neil Johnson wrote nice post on that subject and I’m using his method for creating database links in another user’s schema. So check it out - link is above.
Recently I had situation when I didn’t know password of the user on the local and remote database. But I had to create database link to compile some views and procedures.
In that case my (more...)
Yesterday colleague reported that he’s receiving wrong/odd result from query so we decided to examine closely what's happening. Query had more then 150 lines with inline view, group by clause, several outer joins, function, decodes and cases included. So the first step was to exclude all the stuff that wasn’t important for clearer diagnosis.
Environment: Oracle EE 184.108.40.206 on OEL 5.7
I will post shortened version of the query with fake table names and columns.
FROM table_1 r,
MAX ( DECODE (ri.c_name, 'SPACE', ri. (more...)
My DBA friend Dejan from Baze Podataka
database related site wrote post
about Oracle Bug 8943287 which reminded me how dangerous bug this is. I was completely unaware about this bug till few months ago.
For this blog post I want to write about “ORA-01578: ORACLE data block corrupted”
error and recovery. It is easy to recover from such error if you have RMAN backup - you, of course, have RMAN backup? (if not, create one) Even if you don’t have RMAN backup you can perform block recovery from OS based backups (datafile copies).
But can you perform block recovery (more...)
Two weeks ago a colleague DBA asked me what would be the best way to move SYSTEM tablespace with minimum downtime. There are several ways to perform that task and I will note what would be the best way in my opinion.
Messing with SYSTEM tablespace is always tricky so my goal would be minimum risk
and minimum downtime
My testing environment is 11gR1 database on Linux 32bit OS.
After quick search on that subject I’ve noticed that DBA’s are comfortable with moving/renaming other datafiles but SYSTEM or UNDO are making problems. Some tablespaces are essential and cannot be taken (more...)
I’ve just realized that I’m never fully satisfied after tuning queries with database links. Yes, I manage to improve performance significantly but I’m always missing some answers.
Case explained in this blog post is not exception also.
Source DB (Oracle 220.127.116.11 on Solaris 64bit)
Remote DB (Oracle 10.2.0.4 on Solaris 64bit)
(Table names and values are changed )
One of the developers reported me problem with this simple query:
To get better output click "View Source".
FROM tab1 cc,
WHERE cc.c_id = cfp.b_id
AND cc.code = (more...)