ORA-27211: Failed to load Media Management Library

Introduction:
We had interesting but painfull issue for Oracle backups which are done with Veritas Netbackup. There was activity of patching on Netbackup. And after the patching there was issues with Oracle Backups. All the Oracle backups were failing with following error messages....

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

Troubleshooting:

We tried different steps to resolve the error. Following are the steps which we did.

1- Checked with the "sbttest" command, to see whether Netbackup is doing successfull test backups. and we got the following errors.

$>sbttest test

The sbt function pointers are loaded from oracle.static library.libobk.so could not be loaded. Check that it is installed.

2- We tested with simple rman commands to allocate a tape channel and the release. and following was the error messages.

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:40:43
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

3- The above error messages were showing that MML is not able to load, and that libobk.so was being used as static from Oracle binaries.

We explicitly used the Netbackup /usr/openv/netbackup/bin/libobk.so64.1, inside the RMAN script, but we got the same error as of step 2. Following were the error messages

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1';
3> release channel ch1;
4> }

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

The above all steps showed that there was strange kind of problem with Netbackup. As we were not able to allocate one channel with "sbt_tape" type, and all the times MML was not able to be loaded.

We also check in the udump of the Oracle Database server, and following were the error messages in udump trace file.

*** SESSION ID:(47.58746) 2009-02-25 13:40:43.872
SKGFQ OSD: Error in function sbtinit on line 2384
SKGFQ OSD: Look for SBT Trace messages in file /usr/oracle/home/oracle/admin/ORA/u
dump1/sbtio.log
SBT Initialize failed for oracle.static
*** 2009-02-25 13:41:11.006
Failed to load SBT library /usr/openv/netbackup/bin/libobk.so64.1

The above error messages of udump trace file, was clearly showing that the libobk.so64.1 was not able to load. And also we found valid symbolic link from $ORALCE_HOME/lib/libobk.so to /usr/openv/netbackup/bin/libobk.so64.1

Resolution:

After trying every way, finally our system admin did one thing and got resolved the issue. What he did, he renamed the /usr/openv/netbackup directory and again pushed the netbackup client from Netbackup Server, and we again tested the Oracle Backups and it was all working. Following is the simple allocation of channel of 'sbt_tape'



RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
allocated channel: ch1
channel ch1: sid=35 devtype=SBT_TAPE
channel ch1: VERITAS NetBackup for Oracle - Release 6.0 (2005090703)

released channel: ch1

RMAN>

Partitioned Index rebuild with "Parallel" and without "Parallel" clause

Introduction:
In this article we will see, the impact of "Parallel" clause of rebuilding a partition index. We would also enable tracing on the session which execute the Alter index rebuild statement, and also all those parallel sessions, which are invoked automatically when we use "Parallel" clause.

Steps:
For this test, we can make one partitioned index unusable, we will use exchange partition clause of Oracle 9i, without specifying "Update global indexes", this will cause index unusable. And then we will rebuild both with "Parallel" and without "Parallel" clause.

1- Check the status of partitioned index.

SQL> select substr(index_name,1,20) index_name,substr(partition_name,1,20) part_name , status from dba_ind_partitions
where index_name like '%TEST%'
;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE



2- Exchange the partition with table.

a- we need to make an empty table of the partitioned table, and then we will exchange the partition with that empty table.

sql>create table TEST_PARTITIONED_TABLE_Test as select * from TEST_PARTITIONED_TABLE ;

Table created.

B- We will now exchange the partition JAN2008_02 with table TEST_PARTITIONED_TAB LE_Test.

SQL> alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

c- check the number of rows in the partition JAN2008_02, to verify that rows has been moved to the TEST_PARTITIONED_TABLE.

SQL>select count(1) from TEST_PARTITIONED_TABLE partition (jan2008_02);

COUNT(1)
----------
0

d- check the number of rows in teh TEST_PARTITIONED_TABLE

SQL>select count(1) from TEST_PARTITIONED_TABLE_test;

COUNT(1)
----------
14140873


e- get the data back in the partition JAN2008_02.

SQL>alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

3- Check index status after exchange partition.

INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 UNUSABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE

4- Rebuild unusable index partition JAN2008_02 without "Parallel"

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online;

Index altered.

5- We can check the trace file for the above statement, and can estimate the time taken to rebuild the above index.

From the trace file for the above session.

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 230.33 845.32 614267 522610 6455 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 230.33 845.32 614267 522610 6455 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 27213 0.13 56.25
direct path write 1113 0.73 169.77
direct path read 11273 0.53 232.59
db file sequential read 30 0.01 0.10
rdbms ipc reply 117 0.04 0.10
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00



The elapsed time is 845.32 seconds (approx. 14.08 min.)

Now to check the impact of the parallel clause, we need to repeat steps 1 to 3.

5- after completing the steps 1 - 3, now we can rebuild the index with parallel clause.

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel;

Index altered.

6- From the trace file,

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 1 0 0
Execute 1 1.58 765.66 599831 527410 12060 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.60 765.68 599831 527411 12060 0

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ksu process alloc latch yield 3 0.09 0.19
process startup 6 0.04 0.24
PX Deq: Join ACK 4 0.00 0.00
PX Deq: Execute Reply 683 1.96 761.48
PX Deq: Parse Reply 3 0.01 0.01
PX qref latch 23 0.01 0.30
PX Deq: Table Q qref 2 0.00 0.00
db file sequential read 9 0.01 0.01
rdbms ipc reply 98 0.04 0.12
log file sync 2 0.01 0.01
PX Deq: Signal ACK 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2443.89 2443.89


The elapsed time is 765.68 second. (approx. 12.76 min)

Result:

The rebuild with parallel clause is faster then normal rebuild of the index.