Combining schedules with DBMS_SCHEDULER

Wednesday, April 16, 2014 2 comments
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.
BEGIN

DBMS_SCHEDULER.create_schedule ('WORK',repeat_interval => 'FREQ=DAILY;BYHOUR=8,9,10,11,12,13,14,15,16,17;BYMINUTE=0,30');

DBMS_SCHEDULER.create_schedule ('NIGHT',repeat_interval => 'FREQ=DAILY;BYHOUR=0,1,2,3,4,5,6,7,18,19,20,21,22,23;' );

DBMS_SCHEDULER.create_schedule ('MAIN_DAY', repeat_interval =>'WORK, NIGHT');

END;

/


To test schedule I will create simple table and job which will insert sysdate into table.

create table admin.test_jobs_exec (run_date date);

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"ADMIN"."J_TEST_JOBS_EXEC"',
            schedule_name => '"ADMIN"."MAIN_DAY"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin
                           insert into test_jobs_exec values (sysdate);
                           commit;
                           end;',
            number_of_arguments => 0,
            enabled => FALSE,
            auto_drop => FALSE
);
    
    DBMS_SCHEDULER.enable(
             name => '"ADMIN"."J_TEST_JOBS_EXEC"');
END;
/


Let’s query test_jobs_exec table to see if scheduler used wanted job schedule.

select run_date from test_jobs_exec
order by run_date;


RUN_DATE          
-------------------
...
- 15.04.2014 16:00:32 
- 15.04.2014 16:30:32 
- 15.04.2014 17:00:32 
- 15.04.2014 17:30:32 
15.04.2014 18:00:32 
15.04.2014 19:00:33 
15.04.2014 20:00:32 
15.04.2014 21:00:32 
15.04.2014 22:00:32 
15.04.2014 23:00:32 
16.04.2014 00:00:32 
16.04.2014 01:00:32 
16.04.2014 02:00:32 
16.04.2014 03:00:32 
16.04.2014 04:00:33 
16.04.2014 05:00:32 
16.04.2014 06:00:32 
16.04.2014 07:00:32 
- 16.04.2014 08:00:32 
- 16.04.2014 08:30:32 
- 16.04.2014 09:00:32 
- 16.04.2014 09:30:32 
...



If you are still using old DBMS_JOB package replace it with DBMS_SCHEDULER.
You will get better logging, simple scheduling syntax, stored/reusable schedules and many more nice features.

Read More...

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

Wednesday, April 2, 2014 0 comments
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 document.

You will probably find many configuration/installation mistakes. This is why I set this document to be v1.0 version – I plan to change many things based on your suggestions.
I’m not native English speaker so there are probably lots grammatical or spelling errors also.

But still, as I already wrote document for myself, I have decided to share it with Oracle community for educational purposes.


If you are interested in this subject, and if you take some time to read document, please send me your suggestions and objections.

PDF document: Active Passive Failover Cluster 12c Configuration for Oracle Databases


Thank you,
Marko

Read More...

RMAN Catalog backuppiece located on Tape

Thursday, March 6, 2014 0 comments
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';
6> }

allocated channel: c1
channel c1: SID=321 device type=SBT_TAPE
channel c1: NMDA Oracle v1.1.0

sent command to channel: c1

ORA-19870: error while restoring backup piece /u01/app/orcl11/product/11.1.0/db_1/dbs/ARCH_ORCL_rep2dod5_s128878_p1
ORA-19505: failed to identify file "/u01/app/orcl11/product/11.1.0/db_1/dbs/ARCH_ORCL_rep2dod5_s128878_p1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 03/05/2014 15:42:23
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /u01/app/orcl11/product/11.1.0/db_1/dbs/ARCH_ORCL_rep2dod5_s128878_p1

and

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 device type 'sbt_tape' backuppiece 'ARCH_ORCL_rep2dod5_s128878_p1';
6> }

allocated channel: c1
channel c1: SID=321 device type=SBT_TAPE
channel c1: NMDA Oracle v1.1.0

sent command to channel: c1

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 03/05/2014 15:36:57
RMAN-06470: DEVICE TYPE is supported only when automatic channels are used



How to catalog backuppiece on tape...


Add SBT_TAPE configuration:
RMAN> configure default device type to 'SBT_TAPE';
RMAN> configure channel device type 'SBT_TAPE' send '[MML PARAMETERS]';

Catalog backuppiece:
RMAN> catalog device type 'SBT_TAPE' backuppiece '[BACKUP NAME]';

To clear configuration:
RMAN> configure channel device type ‘SBT_TAPE’ clear;


My example:

Set configuration:
RMAN> configure default device type to 'SBT_TAPE';
RMAN> configure channel device type 'SBT_TAPE'
2> send 'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' SEND  'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
new RMAN configuration parameters are successfully stored

Catalog backuppiece:
RMAN> catalog device type 'SBT_TAPE' backuppiece 'ARCH_ORCL_rep2dod5_s128878_p1';

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=321 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: NMDA Oracle v1.1.0
cataloged backup piece
backup piece handle=ARCH_ORCL_rep2dod5_s128878_p1 RECID=127894 STAMP=841419878

Clear configuration:
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' SEND  'NSR_ENV=(NSR_SERVER=backup_server,NSR_CLIENT=oracle_client,NSR_DATA_VOLUME=OrclPool)';
old RMAN configuration parameters are successfully deleted



Read More...

How to use index to get NULL values

Tuesday, February 18, 2014 0 comments
I have learned new trick today which I want to share and keep as reminder to myself. I am sure that most Oracle DBA’s or Developers are familiar with this - 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.num_rows table_num_rows
from dba_tab_columns c, dba_tables t
where 1=1
and c.table_name = t.table_name
and c.table_name = 'OBJECTS'
and c.column_name = 'OBJECT_NAME';



TABLE_NAME    COLUMN_NAME     DATA_TYPE     NUM_NULLS TABLE_NUM_ROWS
------------- --------------- ------------ ---------- --------------
OBJECTS       OBJECT_NAME     VARCHAR2           1000        1000000 


Goal:
Count how many OBJECT_NAMEs are NULL.


Index object_name column using classic B-tree index.

create index idx_objname on objects(object_name);


select count(*)
from objects
where object_name is null;

  COUNT(*)
----------
      1000

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |  4252 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| OBJECTS |  1000 | 25000 |  4252   (1)| 00:00:52 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME" IS NULL)
   
   
drop index idx_objname;

FULL SCAN is used because NULL values are not indexed for single column indexes.


For a workaround you could create function based index to help yourself. This was (till now) my preferred option.

create index idx_objname_f on objects(nvl(object_name,'XX'));

select count(*)
from objects
where nvl(object_name,'XX')='XX';

  COUNT(*)
----------
      1000

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_OBJNAME_F | 10000 |   166K|     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECTS"."SYS_NC00017$"='XX')

Now INDEX is used but as you can see I had to modify initial query using function to get number of NULLs.

Besides that, this index is unusable if I want to get other values then NULL.

select count(*)
from objects
where object_name='TEST';

  COUNT(*)
----------
        16


------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |  4253 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| OBJECTS |    28 |   700 |  4253   (1)| 00:00:52 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME"='TEST')
   
      
drop index idx_objname_f;


The trick that I wanted to show.
In composite index NULL values are stored for leading column when second column is not NULL. I will use dummy value for second column.

create index idx_objname_d on objects(object_name,'X');

select count(*)
from objects
where object_name is null;

  COUNT(*)
----------
      1000

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     8 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |    25 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_OBJNAME_D |  1000 | 25000 |     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME" IS NULL)


Index is used to count NULL values.


You can also use this index to get other values.


select count(*)
from objects
where object_name='TEST';

  COUNT(*)
----------
        16

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |    25 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_OBJNAME_D |    28 |   700 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='TEST')


drop index idx_objname_d;

This trick was so nice discovery for me that deserved blog post.


It is worth to mention that bitmap index also stores NULL values.

create bitmap index idx_objname_d on objects(object_name);

select count(*)
from objects
where object_name is null;

  COUNT(*)
----------
      1000
   
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE             |               |     1 |    25 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |               |  1000 | 25000 |     3   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| IDX_OBJNAME_D |       |       |            |          |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_NAME" IS NULL)



Clean up!

drop table objects;

Read More...

Wrong Result Bug using group-by placement optimization

Wednesday, February 5, 2014 0 comments
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 will use three tables:
CONT
Name    Null Type          
------- ---- ------------- 
CUST_ID      NUMBER(38)    
CODE         VARCHAR2(100) 

CUST
Name    Null     Type       
------- -------- ---------- 
CUST_ID NOT NULL NUMBER(38) 

DRAG
Name    Null Type      
------- ---- --------- 
DRAG_ID      NUMBER(6) 


To gather fresh statistics for the tables:
begin
  dbms_stats.gather_table_stats(ownname=>user,tabname=>'CONT',estimate_percent=>100, cascade=>TRUE);
  dbms_stats.gather_table_stats(ownname=>user,tabname=>'CUST',estimate_percent=>100, cascade=>TRUE);
  dbms_stats.gather_table_stats(ownname=>user,tabname=>'DRAG',estimate_percent=>100, cascade=>TRUE);
end;
/

More details about tables:
select table_name, num_rows, blocks, partitioned, last_analyzed
from dba_tables
where table_name in ('CONT','CUST','DRAG');


TABLE_NAME     NUM_ROWS     BLOCKS PARTITIONED LAST_ANALYZED     
------------ ---------- ---------- ----------- -------------------
CONT            1181949       2892 NO          04.02.2014 14:49:24 
DRAG                314          5 NO          04.02.2014 14:49:25 
CUST             576233        902 NO          04.02.2014 14:49:25 

Information about indexes:
select index_name, table_name, uniqueness, distinct_keys, clustering_factor
from dba_indexes
where table_name in ('CONT','CUST','DRAG');


INDEX_NAME     TABLE_NAME   UNIQUENESS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- ------------ ---------- ------------- -----------------
I_CUST_ID      CONT         NONUNIQUE         468738            753983 
PK_CUST_ID     CUST         UNIQUE            576233               878 

We have three small and simple tables with just two indexes. CUST table has primary key on “cust_id” column.


After this little introduction it is time for some tests.

I will flush buffer cache and shared pool before every query execution.
SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

First query execution and execution plan:
select /*+ gather_plan_statistics */ 
       count(co.code) as cnt
from drag t, 
     cust cus, 
     cont co
where 1=1
and t.drag_id = cus.cust_id
and cus.cust_id = co.cust_id(+) 
group by t.drag_id;

            CNT
---------------
              2
              2
              2
              2
              1
              2
              2
              2
              2
...
303 rows
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  gpnrgy2vawafq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */        count(co.code) as cnt from
drag t,      cust cus,      cont co where 1=1 and t.drag_id =
cus.cust_id and cus.cust_id = co.cust_id(+) group by t.drag_id

Plan hash value: 3989628059

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |    303 |00:00:00.62 |    3734 |   3724 |       |       |          |
|   1 |  HASH GROUP BY       |      |      1 |    303 |    303 |00:00:00.62 |    3734 |   3724 |  1096K|  1096K| 1264K (0)|
|*  2 |   HASH JOIN OUTER    |      |      1 |    792 |   1084 |00:00:00.16 |    3734 |   3724 |  1206K|  1206K| 1244K (0)|
|*  3 |    HASH JOIN         |      |      1 |    314 |    314 |00:00:00.04 |     890 |    885 |  1452K|  1452K| 1470K (0)|
|   4 |     TABLE ACCESS FULL| DRAG |      1 |    314 |    314 |00:00:00.01 |       7 |      6 |       |       |          |
|   5 |     TABLE ACCESS FULL| CUST |      1 |    576K|    576K|00:00:00.02 |     883 |    879 |       |       |          |
|   6 |    TABLE ACCESS FULL | CONT |      1 |   1181K|   1181K|00:00:00.01 |    2844 |   2839 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUS"."CUST_ID"="CO"."CUST_ID")
   3 - access("T"."DRAG_ID"="CUS"."CUST_ID")

Check result of the query - this is correct query result.

Now to simulate what we experienced in production.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.   

With hint I want to force PK_CUST_ID index usage because this was preferred plan in production.
select /*+ gather_plan_statistics index(cus PK_CUST_ID) */ 
       count(co.code) as cnt
from drag t, 
     cust cus, 
     cont co
where 1=1
and t.drag_id = cus.cust_id
and cus.cust_id = co.cust_id(+) 
group by t.drag_id;

            CNT
---------------
              0
              0
              0
              0
              0
              0
              0
              0
              0
              0
... 303 rows
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9vf9uf7mhdmdz, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(cus PK_CUST_ID) */
count(co.code) as cnt from drag t,      cust cus,      cont co where
1=1 and t.drag_id = cus.cust_id and cus.cust_id = co.cust_id(+) group
by t.drag_id

Plan hash value: 3263881209

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |    303 |00:00:00.70 |    3459 |   3094 |       |       |          |
|   1 |  HASH GROUP BY         |            |      1 |    303 |    303 |00:00:00.70 |    3459 |   3094 |   934K|   934K| 1267K (0)|
|*  2 |   HASH JOIN OUTER      |            |      1 |    764 |   1046 |00:00:00.22 |    3459 |   3094 |  1134K|  1134K| 1198K (0)|
|   3 |    NESTED LOOPS        |            |      1 |    303 |    303 |00:00:02.02 |     615 |    255 |       |       |          |
|   4 |     VIEW               | VW_GBC_9   |      1 |    303 |    303 |00:00:00.01 |       7 |      6 |       |       |          |
|   5 |      HASH GROUP BY     |            |      1 |    303 |    303 |00:00:00.01 |       7 |      6 |  1012K|  1012K| 1249K (0)|
|   6 |       TABLE ACCESS FULL| DRAG       |      1 |    314 |    314 |00:00:00.01 |       7 |      6 |       |       |          |
|*  7 |     INDEX UNIQUE SCAN  | PK_CUST_ID |    303 |      1 |    303 |00:00:00.22 |     608 |    249 |       |       |          |
|   8 |    TABLE ACCESS FULL   | CONT       |      1 |   1181K|   1181K|00:00:00.01 |    2844 |   2839 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUS"."CUST_ID"="CO"."CUST_ID")
   7 - access("ITEM_1"="CUS"."CUST_ID")

Check result of the query!
Count is displaying all 0 values because it received only NULLs to count.
Other functions like max and min are also affected by this error.

Check steps 4,5 and 6 in execution plan.

Instead of quick full scan on DRAG table Oracle transformed query and created inline view using smart group-by optimization.

In 10053 trace I could easily find what Oracle was doing.
SELECT
  /*+ INDEX ("CUS" "PK_CUST_ID") */
  SUM("VW_GBC_9"."ITEM_2") "CNT"
FROM
  (SELECT "T"."DRAG_ID" "ITEM_1",
    COUNT("CO"."CODE") "ITEM_2",
    "T"."DRAG_ID" "ITEM_3"
  FROM "ADMIN"."DRAG" "T"
  WHERE 1=1
  GROUP BY "T"."DRAG_ID",
    "T"."DRAG_ID"
  ) "VW_GBC_9",
  "ADMIN"."CUST" "CUS",
  "ADMIN"."CONT" "CO"
WHERE "VW_GBC_9"."ITEM_1"="CUS"."CUST_ID"
AND "CUS"."CUST_ID"      ="CO"."CUST_ID"(+)
GROUP BY "VW_GBC_9"."ITEM_3";


Quick workaround to fix this bug:
- Set "_optimizer_group_by_placement"=FALSE.

You could check in 10053 trace value of this parameter.
In my case: _optimizer_group_by_placement = true

SQL> alter session set "_optimizer_group_by_placement"=FALSE;

Session altered.

select /*+ gather_plan_statistics index(cus PK_CUST_ID) */ 
       count(co.code) as cnt
from drag t, 
     cust cus, 
     cont co
where 1=1
and t.drag_id = cus.cust_id
and cus.cust_id = co.cust_id(+) 
group by t.drag_id;

       CNT
----------
         2
         2
         2
         2
         1
         2
         2
         2
         2
...
303 rows
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  a91bzhvupzquh, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(cus PK_CUST_ID)*/
count(co.code) as cnt from drag t,      cust cus,      cont co where
1=1 and t.drag_id = cus.cust_id and cus.cust_id = co.cust_id(+) group
by t.drag_id

Plan hash value: 2460166079

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |    303 |00:00:00.16 |    3481 |       |       |          |
|   1 |  HASH GROUP BY       |            |      1 |    303 |    303 |00:00:00.16 |    3481 |  1096K|  1096K| 1232K (0)|
|*  2 |   HASH JOIN OUTER    |            |      1 |    792 |   1084 |00:00:00.01 |    3481 |  1206K|  1206K| 1529K (0)|
|   3 |    NESTED LOOPS      |            |      1 |    314 |    314 |00:00:00.01 |     637 |       |       |          |
|   4 |     TABLE ACCESS FULL| DRAG       |      1 |    314 |    314 |00:00:00.01 |       7 |       |       |          |
|*  5 |     INDEX UNIQUE SCAN| PK_CUST_ID |    314 |      1 |    314 |00:00:00.01 |     630 |       |       |          |
|   6 |    TABLE ACCESS FULL | CONT       |      1 |   1181K|   1181K|00:00:00.01 |    2844 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUS"."CUST_ID"="CO"."CUST_ID")
   5 - access("T"."DRAG_ID"="CUS"."CUST_ID")


27 rows selected.  



Oracle Support note associated with "_optimizer_group_by_placement" parameter.
Note.8945586.8 Ext/Pub Bug 8945586 - Wrong results using GROUP BY placement:
Description
Wrong results can occur when using GROUP BY placement where the aggregate column gets pruned from select list.


I’ve even found that “_optimizer_group_by_placement” parameter was mentioned in "Oracle® Fusion Middleware Oracle WebCenter Analytics Installation and Upgrade Guide".

Oracle 11g (11.1.0.6 and above) in default or Oracle Real Application Clusters (RAC) configuration

When running Oracle 11g versions prior to 11.1.0.7.0 the Oracle system parameter _optimizer_group_by_placement must be set to false. This can either be set in the init.ora file of the respective database instances or by by issuing an ALTER SYSTEM command as follows:

SQLPLUS /nolog
CONNECT / AS SYSDBA
ALTER SYSTEM SET "_optimizer_group_by_placement"=false


If you are running affected Oracle version - consider changing this parameter ;-)

Read More...

Oracle Scheduler External Jobs and Credentials

Friday, October 25, 2013 5 comments
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 is executing external (operating system) jobs it is using lowly privileged user and group for your platform. In my environment (OEL 5.8) this is “nobody” and “nobody”. As you must kill OS process you must specify more privileged user. There is “externaljob.ora” configuration file in “$ORACLE_HOME/rdbms/admin”. Edit this file as root and specify privileged user and group.

During my preparation for “Oracle Database 11g: New Features for Administrators” I’ve learned that this is not the best way to grant privileges to external jobs.

In 11g, to improve security, Oracle strongly recommends to assign a credential to a external job for this task. Default credentials are included only for backward compatibility and may be deprecated in future releases.


This will be very simple example to demonstrate how to use credentials.


Let’s create simple script:
$ cat test_script.sh
#!/bin/bash

/bin/echo "TEST SCRIPT!" > /home/oracle/test_script.out

Grant execute privileges to OS user:
$ chmod u+x test_script.sh

Create test user and grant necessary privileges. To execute external jobs you will need "CREATE JOB" and "CREATE EXTERNAL JOB" privileges.
SQL> create user testusr identified by testusr;

User created.

SQL> grant create session to testusr;

Grant succeeded.

SQL> grant create job to testusr;

Grant succeeded.

SQL> grant create external job to testusr;

Grant succeeded.

Create simple job.
SQL> conn testusr/testusr
Connected.
SQL>
begin
   dbms_scheduler.create_job(
   job_name => 'test_script_job',
   job_type => 'EXECUTABLE',
   job_action => '/home/oracle/test_script.sh',
   start_date => SYSDATE,
   enabled => FALSE,
   repeat_interval => NULL);
   end;
 10  /

PL/SQL procedure successfully completed.

Try to run job without assigning credentials.
SQL> begin
  2  dbms_scheduler.run_job(job_name => 'test_script_job', use_current_session=> TRUE);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: 274662
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

Now to create credential using CREATE_CREDENTIAL procedure in the DBMS_SCHEDULER package.
OS user specified in credential must have necessary privileges to run script.
SQL> begin
  2  dbms_scheduler.create_credential(
  3  credential_name => 'ORACLE_CRED',
  4  username => 'oracle',
  5  password => ‘os_password’);
  6  end;
  7  /

PL/SQL procedure successfully completed.


BE AWARE!

It is noted in documentation, passwords are stored obfuscated, and are not displayed in the *_SCHEDULER_CREDENTIALS views. But as SYS user you could extract password with DBMS_ISCHED.GET_CREDENTIAL_PASSWORD function.
SQL>
SELECT u.name CREDENTIAL_OWNER, O.NAME CREDENTIAL_NAME, C.USERNAME,
  DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd
FROM SYS.SCHEDULER$_CREDENTIAL C, SYS.OBJ$ O, SYS.USER$ U
WHERE U.USER# = O.OWNER#
  5    AND C.OBJ#  = O.OBJ# ;

CREDENTIAL_OWNER     CREDENTIAL_NAME                USERNAME             PWD
-------------------- ------------------------------ -------------------- ----------
TESTUSR              ORACLE_CRED                    oracle               os_password

Thanks Martin Berger for this information: http://berxblog.blogspot.com/2012/02/restore-dbmsschedulercreatecredential.html

You already know that SYS user is very powerful and should be well protected - this is just another reason.


As I have created credential lets assign it to my external job.
SQL> begin
  2  dbms_scheduler.set_attribute('test_script_job','credential_name','ORACLE_CRED');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Everything should work fine now.
SQL> begin
  2  dbms_scheduler.run_job(job_name => 'test_script_job', use_current_session=> TRUE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

Quick check.
SQL> !cat /home/oracle/test_script.out
TEST SCRIPT!

Cleanup:
SQL> drop user testusr cascade;

User dropped.


$ rm /home/oracle/test_script.sh /home/oracle/test_script.out


REFERENCES:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedover004.htm#BGBHJCHH

Read More...

RMAN Duplicate from Active Database - ASM to non-ASM

Monday, October 14, 2013 10 comments
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 duplication from active database as this subject is not widely covered.


Active duplication from ASM to non-ASM (or other duplications) can be little tricky. You could easily lose quite of time on troubleshooting if you don't comply all prerequisites during preparation. I will show you examples of some errors you could experience.

I haven't used active database duplication in production and practiced much with this feature. But still was confident that I will perform task successfully from the first time. After few hours of troubleshooting and few failed attempts I ended up on detailed reading Oracle Documentation - what I should do at the first place.
Learn on my mistakes :)


So let’s start with little demo.


- Create password file for auxiliary instance

The easiest method would be to copy password file from the source database.
$ scp orapwora11gr2 ora11gr2@192.168.56.107:/u01/app/ora11gr2/product/11.2.0/dbhome_1/dbs
ora11gr2@192.168.56.107's password:
orapwora11gr2                                                                                             100% 1536     1.5KB/s   00:00


- Network setup

For auxiliary database create static instance registration to listener.
Add to listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora11gr2)
      (ORACLE_HOME = /u01/app/ora11gr2/product/11.2.0/dbhome_1)
      (SID_NAME = ora11gr2)
    )
  )

For both databases tnsnames.ora would be:
duplicate =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
      (UR=A)
    )
  )

ora11gr2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.109)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
      (UR=A)
    )
  )

Start both listeners.


- Create needed directories on target host
$ mkdir -p /u01/app/ora11gr2/admin/ora11gr2/adump
$ mkdir -p /u01/oradata/fra
$ mkdir -p /u01/oradata/ora11gr2/datafiles
$ mkdir -p /u01/oradata/ora11gr2/redologs


- Create simple parameter file and test connectivity
$ cd $ORACLE_HOME/dbs
$ cat initora11gr2.ora
db_name=ora11gr2

Start NOMOUNT auxiliary database using specified parameter file
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 12 00:26:18 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initora11gr2.ora
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
SQL>

Test connectivity.
Target:
$ sqlplus sys/oracle@ora11gr2 as sysdba

Source:
$ sqlplus sys/oracle@duplicate as sysdba


I am executing this commands on target host but you could also execute them on source.

Run this command to establish connection between databases.
$ rman target sys/oracle@ora11gr2 auxiliary sys/oracle@duplicate

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 12 00:39:00 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=138112863)
connected to auxiliary database: ORA11GR2 (not mounted)


- We are ready to test duplicate scenarios


Let's start with this simple script.
run
{
         DUPLICATE TARGET DATABASE TO “ora11gr2”
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump';
}

I get error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:39:29
RMAN-06136: ORACLE error from auxiliary database: ORA-00200: control file could not be created
ORA-00202: control file: '+data1'
ORA-17502: ksfdcre:4 Failed to create file +data1
ORA-15001: diskgroup "DATA1" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Oracle wants to create control file is on '+DATA1' but I don't have ASM instance running on target host. I will change location for control file adding "SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'" to the script.

As directory structure on target host is completely different I will change location for DIAGNOSTIC_DEST also.
run
{
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';
}

This time I get:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:50:24
RMAN-05501: aborting duplication of target database
RMAN-05517: tempfile +DATA1/ora11gr2/tempfile/temp.280.821541379 conflicts with file used by target database
With specified DB_FILE_NAME_CONVERT parameter location for temporary file is not changed. My temporary file is not in "+DATA1/ora11gr2/datafile/", but in "+DATA1/ora11gr2/tempfile/".


I will use "SET NEWNAME FOR TEMPFILE" to change location.
run
{
         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';
}

Again error:
...
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA1/ora11gr2/archivelog/2013_10_12/thread_1_seq_222.1249.828580067" auxiliary format
 "+FRA1"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 12.10.2013 01:07:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=222 RECID=247 STAMP=828580069
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 01:07:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/12/2013 01:07:52
ORA-17628: Oracle error 19505 returned by remote Oracle server
Oracle cannot perform recovery because '+FRA' diskgroup is missing on target host.

Now it is obvious that I could save some time if I had checked parameter file on the source host before duplication.

In my case RMAN copies parameter file from source to the target host. As I have different directory structure on the target host I have to update directory locations using SET commands.
run
{
         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'
         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'
         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';
}
Excerpt from RMAN log:
…
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 12.10.2013 09:34:14

Ah, finally!
Finished duplication without errors.

Let's check locations of database files on the target host.
SQL> select name from v$datafile
  2  union all
  3  select name from v$tempfile
  4  union all
  5  select name from v$controlfile
  6  union all
  7  select member from v$logfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/ora11gr2/datafiles/system.268.824220237
/u01/oradata/ora11gr2/datafiles/sysaux.283.824220237
/u01/oradata/ora11gr2/datafiles/undotbs1.279.824220239
/u01/oradata/ora11gr2/datafiles/users.270.824220237
/u01/oradata/ora11gr2/datafiles/example.297.824220239
/u01/oradata/ora11gr2/datafiles/secure1.277.825195489
/u01/oradata/ora11gr2/datafiles/insecure1.267.825195681
/u01/oradata/ora11gr2/datafiles/lobdata.266.827849207
/u01/oradata/ora11gr2/datafiles/dbfstbs.265.827850825

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/group_3.282.821541363
/u01/oradata/ora11gr2/redologs/group_3.281.821541367
/u01/oradata/ora11gr2/redologs/group_2.292.821541357
/u01/oradata/ora11gr2/redologs/group_2.291.821541361
/u01/oradata/ora11gr2/redologs/group_1.294.821541349
/u01/oradata/ora11gr2/redologs/group_1.293.821541355

17 rows selected.

Hm... I don't like this ASM file naming for my duplicate database.

Again, modify script.
run
{
  SET NEWNAME FOR DATAFILE 1 TO '/u01/oradata/ora11gr2/datafiles/system01.dbf'; 
  SET NEWNAME FOR DATAFILE 2 TO '/u01/oradata/ora11gr2/datafiles/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/u01/oradata/ora11gr2/datafiles/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/u01/oradata/ora11gr2/datafiles/users01.dbf'; 
  SET NEWNAME FOR DATAFILE 5 TO '/u01/oradata/ora11gr2/datafiles/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/u01/oradata/ora11gr2/datafiles/secure1.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '/u01/oradata/ora11gr2/datafiles/insecure1.dbf';
  SET NEWNAME FOR DATAFILE 9 TO '/u01/oradata/ora11gr2/datafiles/lobdata.dbf';
  SET NEWNAME FOR DATAFILE 10 TO '/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf'; 
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
                     LOGFILE
      GROUP 1 ('/u01/oradata/ora11gr2/redologs/redo01a.log', 
               '/u01/oradata/ora11gr2/redologs/redo01b.log') SIZE 50M REUSE, 
      GROUP 2 ('/u01/oradata/ora11gr2/redologs/redo02a.log', 
               '/u01/oradata/ora11gr2/redologs/redo02b.log') SIZE 50M REUSE,
      GROUP 3 ('/u01/oradata/ora11gr2/redologs/redo03a.log', 
               '/u01/oradata/ora11gr2/redologs/redo03b.log') SIZE 50M REUSE
         SPFILE
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'
         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'
         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';
}

Check database files.
SQL> select name from v$datafile
  2  union all
  3  select name from v$tempfile
  4  union all
  5  select name from v$controlfile
  6  union all
  7  select member from v$logfile;


NAME
--------------------------------------------------------------------------------
/u01/oradata/ora11gr2/datafiles/system01.dbf
/u01/oradata/ora11gr2/datafiles/sysaux01.dbf
/u01/oradata/ora11gr2/datafiles/undotbs01.dbf
/u01/oradata/ora11gr2/datafiles/users01.dbf
/u01/oradata/ora11gr2/datafiles/example01.dbf
/u01/oradata/ora11gr2/datafiles/secure1.dbf
/u01/oradata/ora11gr2/datafiles/insecure1.dbf
/u01/oradata/ora11gr2/datafiles/lobdata.dbf
/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/redo03a.log
/u01/oradata/ora11gr2/redologs/redo03b.log
/u01/oradata/ora11gr2/redologs/redo02a.log
/u01/oradata/ora11gr2/redologs/redo02b.log
/u01/oradata/ora11gr2/redologs/redo01a.log
/u01/oradata/ora11gr2/redologs/redo01b.log


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
ora11gr2
Much better!


I am currently preparing for “Oracle Database 11g: New Features for Administrators” exam and this is how I practice/learn more about new features.

Pursuing Oracle Certification path forces me to learn more about new features that I don't use much in my daily work. I would recommend to any Oracle expert to choose one of the exams and prepare themselves for that exam. You don't have to take exam (if it's too expensive for you) - after all, all the fun is in preparation ;-)


REFERENCES: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmdupdb.htm

Read More...

RMAN PITR - testing some scenarios

Friday, August 30, 2013 2 comments
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 publish them online.
I have backup from 20.08.2013 and I want to perform database point in time recovery to that time.

Scenarios:
1. I don’t have old backup (autobackup) of control files.
2. I have incorrect incarnation and getting RMAN-20207 error.
3. I just have controlfile SQL script and suddenly lost current control files.

This is virtual machine on my notebook running OEL 5.8 and Oracle EE 11.1.0.7.


First scenario - I don’t have old backup (autobackup) of control files:

Simulate loss of all control file backups:
SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/oradata/fra
db_recovery_file_dest_size           big integer 5000M

$ rm -rf /u01/oradata/fra/autobackup

Check backups of control files:
$ export NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"

RMAN> crosscheck backup of controlfile;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/oradata/fra/ORAKL/autobackup/2013_08_20/o1_mf_s_823967119_916wqnk5_.bkp RECID=35 STAMP=823967124
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/oradata/fra/ORAKL/autobackup/2013_08_29/o1_mf_s_824745009_91yndoyo_.bkp RECID=39 STAMP=824745013
Crosschecked 2 objects


RMAN> list backup of controlfile;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
35      Full    9.36M      DISK        00:00:06     20.08.2013 15:45:25
        BP Key: 35   Status: EXPIRED  Compressed: NO  Tag: TAG20130820T154519
        Piece Name: /u01/oradata/fra/ORAKL/autobackup/2013_08_20/o1_mf_s_823967119_916wqnk5_.bkp
  Control File Included: Ckp SCN: 597418       Ckp time: 20.08.2013 15:45:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
39      Full    9.36M      DISK        00:00:04     29.08.2013 15:50:14
        BP Key: 39   Status: EXPIRED  Compressed: NO  Tag: TAG20130829T155010
        Piece Name: /u01/oradata/fra/ORAKL/autobackup/2013_08_29/o1_mf_s_824745009_91yndoyo_.bkp
  Control File Included: Ckp SCN: 598295       Ckp time: 29.08.2013 15:50:09

As you can see backups of control files are missing - they have EXPIRED flag.


List backup of database.
RMAN> list backup of database;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
33      Full    772.44M    DISK        00:00:36     20.08.2013 15:45:08
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20130820T154432
        Piece Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
  List of Datafiles in backup set 33
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/system01.dbf
  2       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/sysaux01.dbf
  3       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/undotbs01.dbf
  4       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/users01.dbf

Restore/recover database to 20.08.2013 15:45:08.
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     626327552 bytes

Fixed Size                     2162280 bytes
Variable Size                171966872 bytes
Database Buffers             448790528 bytes
Redo Buffers                   3407872 bytes




RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }



executing command: SET until clause

Starting restore at 30.08.2013 09:09:53
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orakl/orakl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orakl/orakl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orakl/orakl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orakl/orakl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
channel ORA_DISK_1: piece handle=/u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp tag=TAG20130820T154432
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 30.08.2013 09:11:39

Starting recover at 30.08.2013 09:11:39
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
archived log file name=/u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 30.08.2013 09:11:40

sql statement: alter database open resetlogs


Second scenario - I have incorrect incarnation and getting RMAN-20207 error:

As I opened database with resetlogs statement new incarnation of the target database has been created. What will happen if I decide to perform PITR in this situation.

Again I don't have backup of control files.

RMAN> list backup of controlfile;


RMAN> list backup of database;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
33      Full    772.44M    DISK        00:00:36     20.08.2013 15:45:08
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20130820T154432
        Piece Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
  List of Datafiles in backup set 33
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/system01.dbf
  2       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/sysaux01.dbf
  3       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/undotbs01.dbf
  4       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/users01.dbf

  

RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 08/30/2013 09:29:30
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

I am getting RMAN-20207 error because now my target recovery time is time before database was last opened with RESETLOGS. Remember, in previous scenario I have opened database with RESETLOGS and created new incarnation.
To overcome this issue I have to set different incarnation which is sufficiently old.

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORAKL    3724279545       PARENT  1          07.07.2013 16:06:17
2       2       ORAKL    3724279545       PARENT  588882     07.07.2013 17:11:19
3       3       ORAKL    3724279545       ORPHAN  588882     07.07.2013 17:27:20
4       4       ORAKL    3724279545       PARENT  590385     07.07.2013 18:04:42
5       5       ORAKL    3724279545       CURRENT 597410     30.08.2013 09:11:40


RMAN> reset database to incarnation 4;

database reset to incarnation 4

Now to perform restore/recovery.
RMAN> run
2> {
3>  set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause

Starting restore at 30.08.2013 09:31:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orakl/orakl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orakl/orakl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orakl/orakl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orakl/orakl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
channel ORA_DISK_1: piece handle=/u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp tag=TAG20130820T15                                                                                        4432
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:24
Finished restore at 30.08.2013 09:33:05

Starting recover at 30.08.2013 09:33:05
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
archived log file name=/u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 30.08.2013 09:33:07

sql statement: alter database open resetlogs


SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE


Third scenario - I just have controlfile SQL script and suddenly lost current controlfiles


Create SQL script of controlfile and delete all current controlfiles.
SQL> alter database backup controlfile to trace as '/home/oracle/backup/ctlfile_orkl.sql';

Database altered.

SQL> !rm /u01/oradata/orakl/orakl/control01.ctl /u01/oradata/orakl/orakl/control02.ctl /u01/oradata/orakl/orakl/control03.ctl

Check if controlfiles are really missing:
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             171966872 bytes
Database Buffers          448790528 bytes
Redo Buffers                3407872 bytes
ORA-00205: error in identifying control file, check alert log for more info

Edit controlfile SQL script to get executable SQL script for controlfile creation.
This is my controlfile SQL script:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORAKL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orakl/orakl/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/oradata/orakl/orakl/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/oradata/orakl/orakl/redo03.log'  SIZE 50M
DATAFILE
  '/u01/oradata/orakl/orakl/system01.dbf',
  '/u01/oradata/orakl/orakl/sysaux01.dbf',
  '/u01/oradata/orakl/orakl/undotbs01.dbf',
  '/u01/oradata/orakl/orakl/users01.dbf'
CHARACTER SET UTF8
;

Startup nomount and create controlfile from script.
SQL> @/home/oracle/backup/ctlfile_orkl.sql
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             171966872 bytes
Database Buffers          448790528 bytes
Redo Buffers                3407872 bytes

Control file created.

In my newly created controlfile I don’t have any information about backups.
RMAN> list backup;

using target database control file instead of recovery catalog

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORAKL    3724279545       CURRENT 597410     30.08.2013 09:33:07


Let’s catalog backups from my FRA directory:

RMAN> catalog start with '/u01/oradata/fra';

searching for all files that match the pattern /u01/oradata/fra

List of Files Unknown to the Database
=====================================
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154518_916wqg5b_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154431_916wozoy_.bkp
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_4_916wojn0_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_5_916wozks_.arc

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154518_916wqg5b_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154431_916wozoy_.bkp
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_4_916wojn0_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_5_916wozks_.arc


In this case also I don’t have backups of controlfile.

RMAN> list backup of controlfile;


RMAN> list backup of database;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4       Full    772.44M    DISK        00:00:00     20.08.2013 15:44:32
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20130820T154432
        Piece Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/system01.dbf
  2       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/sysaux01.dbf
  3       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/undotbs01.dbf
  4       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/users01.dbf


What if I now try to perform PITR.
RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 08/30/2013 09:45:09
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Again incorrect incarnation.

When I change incarnation PITR successfully completes.
RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       2       ORAKL    3724279545       PARENT  590385     07.07.2013 18:04:42
1       1       ORAKL    3724279545       CURRENT 597410     30.08.2013 09:33:07



RMAN> reset database to incarnation 2;

database reset to incarnation 2



RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5>  recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause

Starting restore at 30.08.2013 09:46:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orakl/orakl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orakl/orakl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orakl/orakl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orakl/orakl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
channel ORA_DISK_1: piece handle=/u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp tag=TAG20130820T15                                                                                        4432
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:04
Finished restore at 30.08.2013 09:47:41

Starting recover at 30.08.2013 09:47:41
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
archived log file name=/u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 30.08.2013 09:47:41

sql statement: alter database open resetlogs


SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/orakl/orakl/temp01.dbf'
     SIZE 50331648  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;  2

Tablespace altered.

Read More...

How to detach storage device from running virtual machine in VirtualBox

Saturday, July 6, 2013 0 comments
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 virtual environment on my notebook is completely different environment than production. Please let me know if this test is not even similar with situations that could happen in the production. Goal is to test OCFS2 node fencing after device is detached.


My environment is HP notebook with Windows 7, VirtualBox 4.2.16 and two Oracle Linux 64bit virtual machines.

Let the test begin...

Shutdown virtual machines and create shareable virtual disk.
C:\>cd "Program Files\Oracle\VirtualBox"

> VBoxManage.exe createhd --filename D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi ^
--size 1024 --format VDI --variant Fixed

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: 862b1d65-eb04-42b2-8a1e-eafafb5bbcd3

Connect disk to virtual machines Cluster1 and Cluster2.
> VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable

> VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable

Start virtual machines and partition newly added disk on Cluster1 node.
[root@cluster1 ~]# fdisk /dev/sdf
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-130, default 130): 
Using default value 130

Command (m for help): p

Disk /dev/sdf: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdf1               1         130     1044193+  83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Inform the OS of partition table changes using partprobe.
[root@cluster1 ~]# /sbin/partprobe /dev/sdf

[root@cluster1 ~]# ssh -l root cluster2
root@cluster2's password: 
Last login: Sat Jul  6 10:12:58 2013 from 192.168.56.101

[root@cluster2 ~]# /sbin/partprobe /dev/sdf

As I am using OCFS2 for shared-disk cluster file system I must create OCFS2 file system on a device. Execute this command on just one node.

[root@cluster1 ~]# mkfs.ocfs2 -b 4K -C 128K -N 4 -L disk3 /dev/sdf1
mkfs.ocfs2 1.6.3
Cluster stack: classic o2cb
Label: disk3
Features: sparse backup-super unwritten inline-data strict-journal-super
Block size: 4096 (12 bits)
Cluster size: 131072 (17 bits)
Volume size: 1069154304 (8157 clusters) (261024 blocks)
Cluster groups: 1 (tail covers 8157 clusters, rest cover 8157 clusters)
Extent allocator size: 4194304 (1 groups)
Journal size: 16777216
Node slots: 4
Creating bitmaps: done
Initializing superblock: done
Writing system files: done
Writing superblock: done
Writing backup superblock: 0 block(s)
Formatting Journals: done
Growing extent allocator: done
Formatting slot map: done
Formatting quota files: done
Writing lost+found: done
mkfs.ocfs2 successful

Create directories and mount virtual storage device on both nodes.
[root@cluster1 ~]# mkdir /disk3
[root@cluster1 ~]# mount -t ocfs2 -o datavolume,nointr,noatime -L "disk3" /disk3

[root@cluster1 ~]# ssh -l root cluster2
root@cluster2's password: 
Last login: Sat Jul  6 10:13:14 2013 from 192.168.56.101

[root@cluster2 ~]# mkdir /disk3
[root@cluster2 ~]# mount -t ocfs2 -o datavolume,nointr,noatime -L "disk3" /disk3

Now I want to test what will happen if I simply detach specified storage device.

Using "del" command to delete file that represents shared storage for virtual machines won’t work.
C:\>del d:\VirtualneMasine\ClusterSharedDisks\disk3.vdi

d:\VirtualneMasine\ClusterSharedDisks\disk3.vdi
The process cannot access the file because it is being used by another process.


So, how to detach device while virtual machines are running.

Detach device using VBoxManage:
> VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 --medium none
> VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 --medium none


Check /var/log/messages on Cluster2.

Jul  6 10:33:37 cluster2 kernel: ata6: exception Emask 0x10 SAct 0x0 SErr 0x10000 action 0xe frozen
Jul  6 10:33:37 cluster2 kernel: ata6: irq_stat 0x80400000, PHY RDY changed
Jul  6 10:33:37 cluster2 kernel: ata6: SError: { PHYRdyChg }
Jul  6 10:33:37 cluster2 kernel: ata6: hard resetting link
Jul  6 10:33:38 cluster2 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:33:38 cluster2 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:33:43 cluster2 kernel: ata6: hard resetting link
Jul  6 10:33:43 cluster2 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:33:43 cluster2 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:33:48 cluster2 kernel: ata6: hard resetting link
Jul  6 10:33:49 cluster2 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:33:49 cluster2 kernel: ata6.00: disabled
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: SCSI error: return code = 0x00010000
Jul  6 10:33:49 cluster2 kernel: end_request: I/O error, dev sdf, sector 2879
Jul  6 10:33:49 cluster2 kernel: (kjournald,415,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: sd 5:0:0:0: rejecting I/O to offline device
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:49 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:49 cluster2 kernel: ata6: EH complete
Jul  6 10:33:49 cluster2 kernel: ata6.00: detaching (SCSI 5:0:0:0)
Jul  6 10:33:51 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:51 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:51 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:53 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5
Jul  6 10:33:53 cluster2 kernel: scsi 5:0:0:0: rejecting I/O to dead device
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_bio_end_io:241 ERROR: IO Error -5
Jul  6 10:33:53 cluster2 kernel: (o2hb-28851B89F3,9129,0):o2hb_do_disk_heartbeat:772 ERROR: status = -5

Notice heartbeat errors due to missing device. Every 2 secs we will get error until timeout is reached then it's time for self-fencing.


In another test I will unmount device prior detaching.
# Cluster1
[root@cluster1 ~]# umount -t ocfs2 /disk3

# Cluster2
[root@cluster2 ~]# umount -t ocfs2 /disk3

Detach using VBOxManage:
> VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 --medium none
> VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 --medium none

Check /var/log/messages on Cluster1:
Jul  6 10:30:52 cluster1 kernel: ocfs2: Unmounting device (8,81) on (node 0)
Jul  6 10:31:38 cluster1 kernel: ata6: exception Emask 0x10 SAct 0x0 SErr 0x10000 action 0xe frozen
Jul  6 10:31:38 cluster1 kernel: ata6: irq_stat 0x80400000, PHY RDY changed
Jul  6 10:31:38 cluster1 kernel: ata6: SError: { PHYRdyChg }
Jul  6 10:31:38 cluster1 kernel: ata6: hard resetting link
Jul  6 10:31:39 cluster1 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:31:39 cluster1 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:31:44 cluster1 kernel: ata6: hard resetting link
Jul  6 10:31:44 cluster1 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:31:44 cluster1 kernel: ata6: failed to recover some devices, retrying in 5 secs
Jul  6 10:31:49 cluster1 kernel: ata6: hard resetting link
Jul  6 10:31:50 cluster1 kernel: ata6: SATA link down (SStatus 0 SControl 300)
Jul  6 10:31:50 cluster1 kernel: ata6.00: disabled
Jul  6 10:31:50 cluster1 kernel: ata6: EH complete
Jul  6 10:31:50 cluster1 kernel: ata6.00: detaching (SCSI 5:0:0:0)

Both nodes stayed up and running without heartbeat errors.
I can conclude from this test - if you unmount OCFS2 device from both nodes prior detaching device everything should continue to work without sudden reboots.


If you want to attach virtual storage again just shutdown virtual machines and connect device using commands from the beginning of the post.
>VBoxManage.exe storageattach Cluster1 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable

>VBoxManage.exe storageattach Cluster2 --storagectl "SATA" --port 5 --device 0 ^
--type hdd --medium D:\VirtualneMasine\ClusterSharedDisks\disk3.vdi --mtype shareable


If you have the opportunity to perform tests on a real hardware this should be always your first choice. But in case you are unable to do that it is better to perform tests in virtual environment then nothing.


Read More...

Recover corrupted blocks using Flashback Logs

Friday, May 17, 2013 4 comments
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 is flushed to disk (flashback logs). It is important to notice that flashback logging is not getting log of a changes but complete block images.


Enable flashback database.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;

ORACLE instance started.

Total System Global Area  467652608 bytes
Fixed Size                  2160752 bytes
Variable Size             180357008 bytes
Database Buffers          281018368 bytes
Redo Buffers                4116480 bytes
Database mounted.
SQL>
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

Create small table and simulate block corruption.
SQL> create table test_corrupt (id number, text varchar2(100)) tablespace users;

Table created.

SQL> insert into test_corrupt values (5,'Marko');

1 row created.

SQL> commit;

Commit complete.

SQL> select id,text from test_corrupt;
 
        ID TEXT
---------- ----------
         5 Marko

Simulate data block corruption.
set heading off
set lines 113
SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
       to_number(S.HEADER_BLOCK + 1) || ' << EOF',
       'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
       'EOF'
  FROM DBA_SEGMENTS S, dba_data_files f
 WHERE f.tablespace_name = 'USERS'
   and S.SEGMENT_NAME = 'TEST_CORRUPT';


dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=523 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

-bash-3.2$ dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=523 << EOF
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.0265188 seconds, 4.2 kB/s

Check block corruption.
SQL> alter system flush buffer_cache;

System altered.

SQL> select id,text from test_corrupt;
select id,text from test_corrupt
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 523)
ORA-01110: data file 4: '/u01/app/oradata/testdb/users01.dbf'

We can try to perform block media recovery using RECOVER command.
RMAN will search flashback logs for good past image of the block. After good copy of the block is found, it is restored and recovered with information from redo logs.

But this will not work always.
In some cases flashback logs could be used to speed up block media recovery, but you shouldn't rely only on flashback logs.

RMAN> recover datafile 4 block 523;

Starting recover at 17-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
searching flashback logs for block images
finished flashback log search, restored 0 blocks

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/17/2013 10:30:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore

In this case Oracle tried to find good block image in flashback logs but it failed in that attempt. We need valid backup to perform block media recovery.


Let’s try another scenario.

SQL> create table test_corrupt (id number, text varchar2(100)) tablespace users;

Table created.

SQL>  insert into test_corrupt values (5,'Marko');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> update test_corrupt set text='Okram' where id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.


set heading off
set lines 113
SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
       to_number(S.HEADER_BLOCK + 1) || ' << EOF',
       'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
       'EOF'
  FROM DBA_SEGMENTS S, dba_data_files f
 WHERE f.tablespace_name = 'USERS'
   and S.SEGMENT_NAME = 'TEST_CORRUPT';

dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=523 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

-bash-3.2$ dd of=/u01/app/oradata/testdb/users01.dbf bs=8192 conv=notrunc seek=131 << EOF
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF

0+1 records in
0+1 records out
112 bytes (112 B) copied, 7.5846e-05 seconds, 1.5 MB/s

In this scenario I have different block corrupted.
SQL> select id, text from test_corrupt;
select id, text from test_corrupt
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 131)
ORA-01110: data file 4: '/u01/app/oradata/testdb/users01.dbf'


Perform block media recovery.
RMAN> recover datafile 4 block 131;

Starting recover at 17-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
searching flashback logs for block images
finished flashback log search, restored 1 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 17-MAY-13

You can notice that this time Oracle found good block image in flashback logs and it completed recovery successfully.

SQL> select id, text from test_corrupt;

ID TEXT
-- --------------------
 5 Okram

Flashback database is nice opinion that enables faster point in time recoveries, or quicker block recoveries, but it cannot completely replace good valid backup.

Read More...

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

Sunday, April 14, 2013 0 comments
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.3.0
OS: OEL 5.8

After rebooting server I’ve noticed that database is not up so I’ve tried to run startup.
# Display status of the database
[grid@l01test1 ~]$ srvctl status database -d testdb
Database is not running.

# Startup
[grid@l01test1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-5010: Update of configuration file "/u02/app/orcl/product/11.2.0/dbhome_1/dbs/inittestdb.ora" failed: details at "(:CLSN00014:)" in "/u02/app/11.2.0/grid/log/l01test1/agent/ohasd/oraagent_grid/oraagent_grid.log"
CRS-5017: The resource action "ora.testdb.db start" encountered the following error:
CRS-5010: Update of configuration file "/u02/app/orcl/product/11.2.0/dbhome_1/dbs/inittestdb.ora" failed: details at "(:CLSN00014:)" in "/u02/app/11.2.0/grid/log/l01test1/agent/ohasd/oraagent_grid/oraagent_grid.log"
. For details refer to "(:CLSN00107:)" in "/u02/app/11.2.0/grid/log/l01test1/agent/ohasd/oraagent_grid/oraagent_grid.log".

CRS-2674: Start of 'ora.testdb.db' on 'l01test1' failed

Hm, startup procedure failed because inittestdb.ora was missing.

Let’s display configuration of the database and check spfile location.
# Display database configuration
[grid@l01test1 ~]$ srvctl config database -d testdb -a

Database unique name: testdb
Database name: testdb
Oracle home: /u02/app/orcl/product/11.2.0/dbhome_1
Oracle user: orcl
Spfile: +DATA/testdb/spfiletestdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: testdb
Disk Groups: DATA,FRA,DISK2
Services:
Database is enabled

[grid@l01test1 ~]$ asmcmd
ASMCMD> cd +DATA/testdb/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
mydir/
spfiletestdb.ora

Everything seemed fine to me.

Later I’ve found out why startup sequence failed.
Oracle tried to create "inittestdb.ora" file as grid user in $ORACLE_HOME/dbs folder of another user. It failed due to insufficient privileges.

To overcome this error I’ve changed permissions on $ORACLE_HOME/dbs folder.
[orcl@l01test1 dbhome_1]$ chmod 775 $ORACLE_HOME/dbs

Now when we start database everything works fine.
# Start
[grid@l01test1 tmp]$ srvctl start database -d testdb

# Check status
[grid@l01test1 tmp]$ srvctl status database -d testdb
Database is running.


With changed privileges grid user managed to create “inittestdb.ora” file in $ORACLE_HOME/dbs folder. This “inittestdb.ora” file has only one row - pointer to server parameter file.
[orcl@l01test1 dbs]$ ls -l inittestdb.ora
-rw-r--r-- 1 grid oinstall 62 Apr 14 10:37 inittestdb.ora

[orcl@l01test1 dbs]$ cat inittestdb.ora
SPFILE='+DATA/testdb/spfiletestdb.ora'          # line added by Agent



REFERENCES
Oracle Restart: srvctl fails to start database with error CRS-5010 if RDBMS and Grid under different users [ID 1335607.1]
http://docs.oracle.com/cd/E11882_01/server.112/e25494/restart005.htm#i1009665


Read More...

Change SID of the Oracle database using DBNEWID

Tuesday, April 9, 2013 5 comments
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 to use database utility, then manually editing control file script which is task more prone to errors.

I’m aware that I can perform this task using RMAN DUPLICATE feature, but in this case I don’t want to use active database for duplication. As this is 11gR1 database Backup-Based duplication is not available.

So I will restore production database from backup to development server and change SID using DBNEWID.

I’ve successfully restored and recovered database.

Let’s check DBID and instance name.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
crmp

SQL> select dbid,open_mode from v$database;

      DBID OPEN_MODE
---------- ----------
1597268947 READ WRITE

CRMP is production database and I want to change SID from CRMP to CRMD.

First step is to shutdown database and start in mount mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2163280 bytes
Variable Size             192941488 bytes
Database Buffers          528482304 bytes
Redo Buffers                7127040 bytes
Database mounted.

Now let’s run nid to change database name.
$ nid target=sys/oracle@test_crmp dbname=crmd setname=YES

DBNEWID: Release 11.1.0.7.0 - Production on Tue Apr 9 11:59:56 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database CRMP (DBID=1597268947)

Connected to server version 11.1.0

Control Files in database:
    /u01/app/devcrmd/oradata/control01.ctl
    /u01/app/devcrmd/oradata/control02.ctl

Change database name of database CRMP to CRMD? (Y/[N]) => Y

Proceeding with operation
Changing database name from CRMP to CRMD
    Control File /u01/app/devcrmd/oradata/control01.ctl - modified
    Control File /u01/app/devcrmd/oradata/control02.ctl - modified
    Datafile /u01/app/devcrmd/oradata/datafiles/system01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/sysaux01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/users01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/data01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/indx01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/tools01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/undotbs02.db - wrote new name

NID-00111: Oracle error reported from target database while executing
    begin       dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,                                        :nmchged);    end;
ORA-01116: error in opening database file /u01/app/crmp/oradata/datafiles/tmp01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6694
ORA-06512: at line 1



Change of database name failed.
Must finish change or REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.

If you are changing DBID or database name of your production database it is very important to backup database prior doing anything. As these are very sensitive operations it is very important to think carefully before performing any action. If you are stuck and not sure what to do I advise you to contact Oracle Support immediately.

You can see that I have received error after running nid because my tempfile was missing. I forgot to recreate it after database restore.

To fix this error I will drop tempfile and run nid again.
$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 9 15:59:19 2013

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/crmp/oradata/datafiles/tmp01.dbf


SQL> alter database tempfile '/u01/app/crmp/oradata/datafiles/tmp01.dbf' drop including datafiles;

Database altered.

Run nid again.
$ nid target=sys/oracle@test_crmp dbname=crmd setname=YES

DBNEWID: Release 11.1.0.7.0 - Production on Tue Apr 9 16:09:07 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database CRMP (DBID=1597268947)
Operation already in progress, continuing

Connected to server version 11.1.0

Control Files in database:
    /u01/app/devcrmd/oradata/control01.ctl
    /u01/app/devcrmd/oradata/control02.ctl

Change database name of database CRMP to CRMD? (Y/[N]) => Y

Proceeding with operation
Changing database name from CRMP to CRMD
    Control File /u01/app/devcrmd/oradata/control01.ctl - modified
    Control File /u01/app/devcrmd/oradata/control02.ctl - modified
    Datafile /u01/app/devcrmd/oradata/datafiles/system01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/sysaux01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/users01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/data01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/indx01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/tools01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/undotbs02.db - already changed
    Control File /u01/app/devcrmd/oradata/control01.ctl - wrote new name
    Control File /u01/app/devcrmd/oradata/control02.ctl - wrote new name
    Instance shut down

Database name changed to CRMD.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

Now operation completed successfully.

Create parameter file for development database and change database name.
$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 9 16:19:31 2013

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initcrmp.ora';

File created.


SQL> startup nomount;
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2163280 bytes
Variable Size             192941488 bytes
Database Buffers          528482304 bytes
Redo Buffers                7127040 bytes
SQL>
SQL>
SQL> alter system set db_name=crmd scope=spfile;

System altered.

Startup database in mount mode.
SQL> startup mount force;
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2163280 bytes
Variable Size             192941488 bytes
Database Buffers          528482304 bytes
Redo Buffers                7127040 bytes
Database mounted.


Create new password file.
$ orapwd file=$ORACLE_HOME/dbs/orapwcrmd password=oracle entries=10

Open database.
$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 9 16:23:58 2013

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

Let’s check DBID and instance name.
SQL> select dbid,open_mode from v$database;

      DBID OPEN_MODE
---------- ----------
1597268947 READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
crmd

Notice that DBID is kept unchanged as I changed only database name. This is the reason why RESETLOGS operation wasn’t needed.

Post operations...

Add missing tempfile for temporary tablespace:
SQL> alter tablespace TMP add tempfile '/u01/app/devcrmd/oradata/datafiles/tmp01.dbf' size 2048M;
Change oratab entry:
$ vi /etc/oratab
...
#### set
...
crmd:/u01/app/devcrmd/product/11.1.0/db_1:N

Change listener.ora and tnsnames.ora files using new SID.


REFERENCES:
http://docs.oracle.com/cd/B28359_01/server.111/b28319/dbnewid.htm


Read More...