Using Oracle Flex ASM with single instance database

Oracle Flex ASM was introduced in 12c version. This is one of the best features introduced with new version in my opinion.

I won’t speak in detail about Flex ASM because you can find more information in documentation. In this post I will concentrate on how Flex ASM handles crash of ASM instance.

For this test I’ve created 2 node cluster - 12c Grid Infrastructure with Flex ASM enabled.


$ asmcmd showclustermode
ASM cluster : (more...)

ORA-19909: datafile 1 belongs to an orphan incarnation

I love to read Oracle related blogs, forum posts and mailing lists much more often than books. Why? Because there many Oracle DBA’s and developers share their experiences, problems, "best practices",... which are very valuable to me.

It's great that we have so big and active Oracle community.

Today I noticed mail from Oracle-L list where someone asked for help with recovery after overwriting production controlfiles. Check Oracle-L for more info.

It reminded me that (more...)

DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE typo

I've played a little with Oracle SQL Plan Management and something caught my eye.

Let's create simple test.

SQL> select /* test_spm */ count(*) from admin.objekti;

COUNT(*)
----------
76378

SQL> set lines 200
SQL> col sql_text for a50 wrapped
SQL> select sql_id, sql_text from v$sql
2 where sql_text like '%test_spm%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------
cpzgdw9swdvzk select /* test_spm */ count(*) from admin.objekti
ap9td7vafq26n select sql_id, sql_text from v$sql where sql_text
like '%test_spm%'

I (more...)

RAC One Node 12cR1 Instance placement after failover

RAC One Node is Oracle active-passive solution very similar to RAC which is active-active solution.
Running active-passive solution using Oracle Clusterware was possible even before introduction of RAC One Node, but RAC One node brought configuration and managing of active-passive cluster to another level.

In my test environment I've created active-passive cluster using 12cR1 Grid Infrastructure with 12cR1 RAC One Node database.

I've tested failover for Oracle database and noticed interesting behavior with instance placement.


(more...)

Convert 12cR1 non-RAC database to RAC database using rconfig

I have recently configured Active/Passive Failover cluster using Grid Infrastructure 12cR1 and wrote document on that subject. Now I plan to use this environment and convert "test12" database from Single-Instance to RAC database.

There are several ways to convert non-RAC database to RAC database:
- using Database Configuration Assistant (DBCA)
- Oracle Enterprise Manager
- rconfig command line utility
- manually

In this post I will describe how to perform conversion using rconfig utility.


Before (more...)

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

Oracle Scheduler External Jobs and Credentials

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

RMAN Duplicate from Active Database – ASM to non-ASM

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

RMAN PITR – testing some scenarios

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

How to detach storage device from running virtual machine in VirtualBox

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

Recover corrupted blocks using Flashback Logs

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

Oracle Restart: srvctl fails to start database with error CRS-5010

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: 11.2.0.3.0
RDBMS: 11.2.0. (more...)

Change the SID of the Oracle database using DBNEWID

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

Transaction restrictions for Parallel DML on 11g

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

Kill stuck Oracle process from OS using DBMS_SCHEDULER

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” wait event.

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

Listener log missing when Oracle Clusterware starts listener resource script

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