Errors & Workaround

ORA-01591: lock held by in-doubt distributed transaction

solution:

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID
------------------------------------------------------------------
6.33.124689

SQL> rollback force '6.33.124689';

Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry('6.33.124689');

PL/SQL procedure successfully completed.

SQL> select local_tran_id from dba_2pc_pending;

******************************************************************************

ERROR:
ORA-12534: TNS:operation not supported

Work around

set WALLET_LOCATION parameter in sqlnet.ora instead of ENCRYPTION_WALLET_LOCATION

*****************************************************************************

ORA-01017: invalid username/password; logon denied


[oracle@vm6 admin]$ sqlplus /@hat  ==>even if correct password

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jan 18 19:17:24 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:



Solution : Remove encryption from sqlnet.ora

Before (sqlnet.ora)

ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
   (METHOD=file)
     (METHOD_DATA=
       (DIRECTORY=/u01/app/oracle/admin/hat/wallet)))

After:

[oracle@vm6 admin]$ cat sqlnet.ora
WALLET_LOCATION =
 (SOURCE=
   (METHOD=file)
     (METHOD_DATA=
       (DIRECTORY=/u01/app/oracle/admin/hat/wallet)))

SQLNET.WALLET_OVERRIDE=TRUE
[oracle@vm6 admin]$ sqlplus /@hat

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jan 18 19:20:14 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL>

**********************************************************************

Error :

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 01/24/2013 21:34:10
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 01/24/2013 21:34:10
ORA-06502: PL/SQL: numeric or value error
                              
Solution :

Change the compatible parameter to 11.2.0.2 and try again
*************************************************************************************
Error:

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found


Solution:


connect to catalog database and run

select 'EXEC DBMS_RCVCAT.UNREGISTERDATABASE('||DB_KEY||','||DBID||');'
 command, resetlogs_time
from rc_database
where name = '&db_name';


COMMAND                                                                                                                 RESETLOGS
------------------------------------------------------------------------------------------------------------ ---------
EXEC DBMS_RCVCAT.UNREGISTERDATABASE(110446,2179054324);                                                                 11-MAY-11


SQL> EXEC DBMS_RCVCAT.UNREGISTERDATABASE(110446,2179054324);

PL/SQL procedure successfully completed.

************************************************************************************************
Error:

P: [INS-40420] The current installation user is not the same as the installation owner of the existing Oracle Grid Infrastructure software.

Workaround:

While upgrading 10g RAC to 11gR2 RAC, GI(Grid Infrastructure) should be installed as existing 10g crs owner not grid user.

Relink New Feature on 11gR2

Normal relink method is $ORACLE_HOME/bin/relink all

We can use runInstaller to relink oracle binaries like below

$ORACLE_HOME/oui/bin/runInstaller -relink -waitForCompletion -maketargetsxml  $ORACLE_HOME/inventory/make/makeorder.xml  -logLocation $ORACLE_HOME/install ORACLE_HOME=$ORACLE_HOME > $ORACLE_HOME/install/relink.log 2>&1

Rename ASM disk group on 11gR2

1.First unmount the diskgroup
 
[oracle@vm6 ~]$ asmcmd umount DATA1

2.Verify whether diskgroup has been mounted or not

[oracle@vm6 ~]$ asmcmd lsdg
 
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      5114     1525                0            1525              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10236    10186                0           10186              0             N  DATA2/

3.Rename diskgroup

[oracle@vm6 ~]$ renamedg phase=both dgname=DATA1 newdgname=DATA3 verbose=true

Parsing parameters..

Parameters in effect:

         Old DG name       : DATA1
         New DG name          : DATA3
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=both dgname=DATA1 newdgname=DATA3 verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/32/asm/orcl/1/libasm.so:ORCL:VOL2 with disk number:0 and timestamp (32970697 -482714624)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/32/asm/orcl/1/libasm.so:ORCL:VOL2 with disk number:0 and timestamp (32970697 -482714624)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:VOL2
Modifying the header
Completed phase 2
Terminating kgfd context 0xb7e70050

above command fails use asm_diskstring command

 renamedg phase=both dgname=DATA1 newdgname=DATA3 verbose=true asm_diskstring='ORCL:VOL2'

4.Mount the new diskgroup

[oracle@vm6 ~]$ asmcmd mount DATA3

5.Verify the diskgroup
 
[oracle@vm6 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      5114     1525                0            1525              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10236    10186                0           10186              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  1048576      1019      967                0             967              0             N  DATA3/
[oracle@vm6 ~]$

Reconfigure HAS and CSS on non RAC ASM 11gR2

run below script as root user

/perl/bin/perl -I/perl/lib -I/crs/install /crs/install/roothas.pl -deconfig

/perl/bin/perl -I/perl/lib -I/crs/install /crs/install/roothas.pl 

above commands give error .use force option

/perl/bin/perl -I/perl/lib -I/crs/install /crs/install/roothas.pl -deconfig -force
/perl/bin/perl -I/perl/lib -I/crs/install /crs/install/roothas.pl


disable/enable auto start

# cd /bin
# ./crsctl stop has
# ./crsctl start has
# ./crsctl disable has
# ./crsctl enable has

catrelod script gives error while downgrading database from 11.2.0.3 to 11.2.0.2

Issue:

catrelod scripts gives error

ERROR at line 1:
ORA-20000: Upgrade from version 11.2.0.2.0 cannot be downgraded to version 11.2.0.1
ORA-06512: at line 7

Workaroud:

Refer Metalink ID : 1367333.1.this is due to unpublished bug

Configure TAF

Configure  the server side failover parameters to the service:


srvctl add service -d car -s car-srv -r "car1,car2" -P BASIC --->10g

srvctl add service -d car -s car-srv -l PRIMARY -e SELECT -m BASIC -q TRUE -r car1,car2 -w 5 -z 180 -P BASIC --->11gR2

srvctl start service -d car -s car-srv

srvctl config service -d car

Configure Local_Listener and Remote_Listener parameters on db side.

Show parameter listener

Below statement only for 10g 

execute dbms_service.modify_service (service_name => 'car-srv' -
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries => 180 -
, failover_delay => 5 -
, clb_goal => dbms_service.clb_goal_long);

Verify status:

select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 3

NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
------ ----------- ---------- -------- ---------- -------- -----
car-srv BASIC SELECT 180 NONE LONG YES

Check Listener services:

lsnrctl services


Client side connect time failover :

This is done by tnsentry.Don't need update on db level.

CAR=
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vm1-vip.domain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = vm2.vip.domain.com)(PORT = 1521))
     )
    (LOAD_BALANCE=YES)
    (CONNECT_DATA =
      (SERVICE_NAME = car-srv)
      (FAILOVER_MODE =
       (TYPE=SELECT)
       (METHOD=BASIC)
      )
    )
  )

Virtual IP:

VIP address is public ip because it's attached with public ip interface. VIP is managd by oracle clusterware.
We should use vip instead of public ip .Because vip is doing Fast Failover.Private IP  won't visible from outside of server and it should not be used.

For ex: If we will public ip address and one of the rac node will be crased then the client will make a request to the node on the public IP and will wait for a TCP timeout (usually 60 seconds) before moving to the next host in the connection list.

If we will user vip ,the client does not have to wait for a TCP timeout to move to the next node in the connection list



Failover Method:

BASIC               :session will not reconnect to a surviving node until the failover occurs.
PRECONNECT :shadow process will be  created on a backup instance to reduce failover time.

Failover Type:

SELECT : session will be authenticated on a surviving node but as well as SELECT statements will be re-executed
Session    :  In a SESSION failover, only the users session will be  re-authenticated.

11gR2 TAF Example:

TAF is a client side features that allows clients to reconnect on surviving node in the event of failure on instance.Reconnect happens automatically from with in OCI library.
*Any uncommited transactions will be rolled back.
*Server side program variables and session properties will be lost
*The select statements automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover.

srvctl add service -d ten -s tenpp -q TRUE -P BASIC -j LONG -e SELECT -m BASIC -z 5 -w 5 -r ten1,ten2

Options:
-d  db_unique name
-s  Service name
-r  preferred instances
-a available instances.specify available instances if preferred instances are not specified
-g Serverpool name
-c uniform,singleton
-p failover method (None,Basic,Preconnect)
-e fileover type(Session,Select,None)
-y specify service startup (Manaul,Automatic)
-j connection load balancing goal(Short,Long)
-B enable or disable load balancing advisory(none,service_time,throughput)
-m Failover method optin(none,basic)
-z specify number of times to attempt to connect after a failover (default 5 attempts)
-w Specify the amount of time in seconds to wait between connect attempts (default 1 sec)



[oracle@vm12 ~]$ srvctl config service -d ten -s tenpp
Service name: tenpp
Service is enabled
Server pool: ten_tenpp
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: ten1,ten2
Available instances: