Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, September 28, 2012

ORA-08104: this index object is being online built or rebuilt



alter index sameer.myindex rebuild online
*
ERROR at line 1:
ORA-08104: this index object 59081 is being online built or rebuilt


As long as the index is not rebuild all access to the index will result in ORA-8104 or ORA-8106.

In case you are not performing the DBMS_REPAIR.ONLINE_INDEX_CLEAN operation , SMON will eventually cleanup the locked index so no actions are actually needed. However, letting SMON do the cleanup can be a bit of 'hit and miss' as SMON will try to cleanup every 60 minutes and if it cannot get a lock on the object with NOWAIT it will just try again later. In a highly active database with many transactions this can cause the rebuild to take a long time as SMON won't get the lock with NOWAIT. Other cases like uncommitted transactions against the table will also result in SMON not rebuilding the index.

Thus, you have 2 options:

1. Let SMON automatically performing the cleanup
or

SQL> select obj#,flags from ind$ where obj#=75350;

      OBJ#      FLAGS
---------- ----------
     59081       3742


2. Run DBMS_REPAIR.ONLINE_INDEX_CLEAN. This operation is faster, but will put load on the system.
declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/


SQL> select obj#,flags from ind$ where obj#=75350;

      OBJ#      FLAGS
---------- ----------
     59081       3251


If the current load on the system is not affected but this broken index, I would suggest you to wait the quiet period of the database and then run DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);.

Sunday, September 16, 2012

ORA-38305: object not in RECYCLE BIN

Perils of having recyclebin turned off in your database.

For me it was a requirement to have the recyclebin turned off, since my application creates temporary staging tables and drops them daily and I cannot afford to keep them in my database.
If  you have recyclebin turned off at the database level then the dropped table will no longer reside in the database i.e you cannot flashback the table as the table segments will be dropped ( un allocated and released to database for reuse)

SQL>show parameter recyclebin

recyclebin                           string      OFF



SQL> create table test123 tablespace users  as select * from dba_tables ;

Table created.

SQL> commit;

Commit complete.



SQL> select count(*) from test123 ;

  COUNT(*)
----------
      3068

SQL> drop table test123;

Table dropped.

SQL> select OWNER,OBJECT_NAME from dba_recyclebin;

no rows selected


SQL> flashback table test123 to  before drop;
flashback table test123 to  before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Saturday, September 15, 2012

Active data guard in 11g - How to activate "Active Data guard" feature in 11g.

How to activate Active Data guard feature in 11g.

Here I am converting an existing physical standby database to Read only standby database with real time redo apply.

Given you have a UP and running physical standby if not please follow the below Note:
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE [ID 1075908.1]

Standby Redo Logs - what are they and when to use them ??
-------------------------------------------------------------

Starting Oracle 9i you have to opportunity to add Standby Rodo Log Groups to
your Online Redo Log Groups. These Standby Redo Logs then store the information
received from the Primary Database. In case of a Failover situation, you will
have less data loss than without Standby Redo Logs.

Standby Redo Logs are only supported for the Physical Standby Database in
Oracle 9i and as well for Logical Standby Databases in 10g. Standby Redo Logs
are only used if you have the LGWR activated for archival to the Remote Standby
Database.

The great Advantage of Standby Redo Logs is that every Entry written into
the Online RedoLogs of the Primary Database is transfered to the Standby
Site and written into the Standby Redo Logs at the same time; threfore, you
reduce the probability of Data Loss on the Standby Database.

Starting with 10g it is possible to start Real-Time Apply with Physical and
Logical Standby Databases. With Real-Time Apply, the Redo is applied to the
Strandby Database from the Standby RedoLog instead of waiting until an Archive
Log is created. So Standby Redo Logs are required for Real-Time Apply.


On Primary:

SQL> show parameter unique

NAME                  PRODX

It is important you have the standby logfile size at a minimum of the primary redo logfile size.

SQL> SELECT distinct(to_char((bytes*0.000001),'9990.999')) size_mb
FROM v$log; 

SIZE_MB
---------
  262.144



SQL> select group#,thread#,members from v$log;

    GROUP#    THREAD#    MEMBERS
---------- ---------- ----------
         1          1          2
         3          1          2
         2          1          2


On Standby:


QL> show parameter unique

NAME                  STANDX

Cancel the recovery:

SQL> alter database recover managed standby database cancel;

Database altered.

Add the standby redo logfile groups: ( 4-6 here )

SQL> alter database add standby logfile group 4 ('/u01/u0090/oradata/STANDX/stndby_redo_1a.log') size 265m;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/u0091/oradata/STANDX/stndby_redo_2a.log') size 265m;

Database altered.

SQL> alter database add standby logfile group 6('/u01/u0092/oradata/STANDX/stndby_redo_3a.log') size 265m;

Database altered.

Now add the second member to the above groups if would like or add these while creating the groups itself:


SQL> alter database add standby logfile member '/u01/u0092/oradata/STANDX/stndby_redo_1b.log' reuse to group 4;

Database altered.

SQL>  alter database add standby logfile member '/u01/u0091/oradata/STANDX/stndby_redo_3b.log' reuse to group 6;

Database altered.

SQL> alter database add standby logfile member '/u01/u0090/oradata/STANDX/stndby_redo_2b.log' reuse to group 5;

Database altered.

Now open the standby database in Read only mode:

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2990735360 bytes
Fixed Size                  2106176 bytes
Variable Size             872422592 bytes
Database Buffers         2097152000 bytes
Redo Buffers               19054592 bytes
Database mounted.
Database opened.
SQL>


Now start the media recovery on the standby database:

SQL>  recover managed standby database using current logfile disconnect;
Media recovery complete.


Example to make sure we can use the standby database:
Here the standby is waiting for the logseq 5865#

SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS                     STATUS                                  THREAD#  SEQUENCE#
--------------------------- ------------------------------------ ---------- ----------
ARCH                        CONNECTED                                     0          0
ARCH                        CONNECTED                                     0          0
ARCH                        CONNECTED                                     0          0
ARCH                        CONNECTED                                     0          0
MRP0                        WAIT_FOR_GAP                                  1       5865
RFS                         IDLE                                          0          0

6 rows selected.




On Primary current sequence is 5865#

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /crm01/u0085/oradata/HRCRMPRD
Oldest online log sequence     5863
Next log sequence to archive   5865
Current log sequence           5865

Create a sample user on primary, create some test tables and then switch the logfiles to reflect the changes at the standby.

SQL> create user myuser identified by standbytest ;

User created.

SQL> grant dba to myuser;

Grant succeeded.

SQL> GRANT CONNECT TO myuser;

Grant succeeded.

SQL> exit


Connect as the new user on Primary:

>sqlplus myuser/standbytest

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Sep 16 02:19:40 2012


SQL> create table readonly_standby as select  * from dba_tables;

Table created.

SQL> select count(*) from readonly_standby;

  COUNT(*)
----------
      8511

SQL> create table index_dba as select * from dba_indexes;

Table created.

SQL> select count(*) from index_dba;

  COUNT(*)
----------
     12423

SQL> commit;

Commit complete.

Now switch the log sequence:

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

On Standby the 5865 sequence is being applied now:

SQL>
SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS                     STATUS                                  THREAD#  SEQUENCE#
--------------------------- ------------------------------------ ---------- ----------
ARCH                        CLOSING                                       1       5868
ARCH                        CONNECTED                                     0          0
ARCH                        CLOSING                                       1       5866
ARCH                        CLOSING                                       1       5867
MRP0                        APPLYING_LOG                                  1       5865
RFS                         IDLE                                          0          0
RFS                         IDLE                                          1       5865
RFS                         IDLE                                          0          0

8 rows selected.




PROCESS                     STATUS                                  THREAD#  SEQUENCE#
--------------------------- ------------------------------------ ---------- ----------
ARCH                        CLOSING                                       1       5868
ARCH                        CONNECTED                                     0          0
ARCH                        CLOSING                                       1       5865
ARCH                        CLOSING                                       1       5867
MRP0                        WAIT_FOR_GAP                                  1       5869
RFS                         IDLE                                          0          0
RFS                         IDLE                                          0          0
RFS                         IDLE                                          0          0

8 rows selected.

Verify if the got created or not:


SQL> select username,account_status,created from dba_users where username='myuser';

USERNAME                                                                                   ACCOUNT_STATUS                        

                                                          CREATED
------------------------------------------------------------------------------------------

myuser                                                      16-SEP-12                       OPEN                                  

       

SQL> select owner,table_name from dba_tables where OWNER='myuser';

OWNER                                                                                      TABLE_NAME

myuser                                                                                    READONLY_STANDBY
myuser                                                                                    INDEX_DBA


Tuesday, September 11, 2012

How to change SCAN VIPs in 11gR2


Below process is testing and worked when the SCAN vips are configured via the DNS.

Backup:
Hosts file
ocrconfig -manualbackup
 voting disks using "dd"

following command can be used:
$ nslookup dbhost-sv1

Name:   dbhost-sv1.mydomain.com
Address: 10.ns.ip.133
Name:   dbhost-sv1.mydomain.com
Address: 10.ns.ip.135
Name:   dbhost-sv1.mydomain.com
Address: 10.ns.ip.134


Stop the scan resources:

# $GRID_HOME/bin/srvctl stop scan_listener
# $GRID_HOME/bin/srvctl stop scan
# $GRID_HOME/bin/srvctl status scan
>srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node dbhostdb02
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node dbhostdb01
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node dbhostdb01



Confirm whether they are really down:


Make the change in the DNS:
# nslookup dbhost-sv1  << should be new IPs
>/usr/sbin/nslookup dbhostdb01-sv1


Name:   dbhostdb01-sv1.mydomain.com
Address: 10.ip.ip.156
Name:   dbhostdb01-sv1.mydomain.com
Address: 10.ip.ip.158
Name:   dbhostdb01-sv1.mydomain.com
Address: 10.ip.ip.157

Now tell CRS to update the SCAN VIP resources:
# $GRID_HOME/bin/srvctl modify scan –n dbhost-sv1  ( This will refresh the new IPs for SCAN)
# $GRID_HOME/bin/srvctl config scan ( verify the new IP's are reflected. )
>srvctl config scan
SCAN name: dbhostdb01-sv1, Network: 1/10.ip.ip.0/255.255.255.0/bge0
SCAN VIP name: scan1, IP: /dbhostdb01-sv1.mydomain.com/10.ip.ip.156
SCAN VIP name: scan2, IP: /dbhostdb01-sv1.mydomain.com/10.ip.ip.158
SCAN VIP name: scan3, IP: /dbhostdb01-sv1.mydomain.com/10.ip.ip.157



# $GRID_HOME/bin/srvctl status scan
>srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node dbhostdb02
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node dbhostdb01
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node dbhostdb01


# $GRID_HOME/bin/srvctl start scan
# $GRID_HOME/bin/srvctl start scan_listener

Reference:952903.1

Monday, September 10, 2012

cursor: mutex X cursor: mutex S

Symtons:  Bad Performance, Query running long. Not using optimal plan so on..or  New upgrade to 11g

In AWR you see.


It is evident that most of the db time is spent on Mutex X and S

What is a Mutex:



Mutexes are a lighter-weight and more granular concurrency mechanism than latches. Mutexes take advantage of CPU architectures that offer the compare and swap instructions (or similar). The reason for obtaining a mutex in the first place, is to ensure that certain operations are properly managed for concurrency. E.g., if one session is changing a data structure in memory, then another session must wait to acquire the mutex before it can make a similar change - this prevents unintended changes that would lead to corruptions or crashes if not serialized. 



·  The library cache mutex is acquired for similar purposes that the library cache latches were acquired in prior versions of Oracle. In 10g, mutexes were introduced for certain operations in the library cache.  Starting with 11g, the library cache latches were replaced by mutexes, hence this new wait event. 



·  This wait event is present whenever a library cache mutex - X is held in exclusive mode by a session and other sessions need to wait for it to be released.  There are many different operations in the library cache that will require a mutex.

In my case: 

I was seeing huge wait events on Cursor Mutex X exclusive so on further analysis. 


In Library cache analysis we can clearly see there 53% Misses, ahaa.......



 In my version count I can clearly see there are 4000 different plans or versions for one single query which is very bad and this will cause library cache contention and eventually exhaustion and too much CPU use so on..
 
In the above screen shot we can see that 45% of the DB time is spent on parsing which is bad..  

From the above it is evident that some thing is not right and its time to work with Oracle Support.
 Since patch for Bug 10187168 is already applied. we were told make some parameter changes.


alter system set "_cursor_features_enabled"=1026 scope=spfile SID='*';
alter system set event="106001 trace name context forever,level 50" scope=spfile SID='*';
shared_pool_reserved_size 268435456 # set to 10% of SHARED_POOL_SIZE or 512M explicitly
"_memory_broker_stat_interval"=999

These parameter changes fixed the Mutex and hard parsing issues for us.
 
Known Bugs:

      Bug 10270888 - ORA-600[kgxEndExamine-Bad-State] / mutex waits after a self deadlock
      Bug 9591812 - Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X")
      Bug 9499302 - Improve concurrent mutex request handling
      Bug 7441165 - Prevent preemption while holding a mutex (fix only works on Solaris)
      Bug 8575528 - Missing entries in V$MUTEX_SLEEP.location