Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X'

Fix : Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X'

The issue:

I restored one of my prod database to one of pre-prod instance but I found frequently database was bouncing. But one change in my source and traget, that is Source is RAC and target is stand-alone database. I thought there may be issue with restore / recover. But after all cross verification, I found similar kind of issue in Oracle document as a bug (Bug 15850031)


From the ALERT log:
==============================

Fri Sep 04 14:09:47 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1036B65440000] [PC:0x1036B65440000, {empty}] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/PREPROD/trace/PREPROD_smon_6488324.trc (incident=164153):
ORA-07445: exception encountered: core dump [PC:0x1036B65440000] [SIGSEGV] [ADDR:0x1036B65440000] [PC:0x1036B65440000] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/PREPROD/incident/incdir_164153/PREPROD_smon_6488324_i164153.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 04 14:09:50 2015
Dumping diagnostic data in directory=[cdmp_20150904140950], requested by (instance=1, osid=6488324 (SMON)), summary=[incident=164153].
Fri Sep 04 14:09:51 2015
Sweep [inc][164153]: completed
Sweep [inc2][164153]: completed
Fri Sep 04 14:09:52 2015
PMON (ospid: 11075852): terminating the instance due to error 474

From the related INCIDENT FILE
==============================

Dump file /u01/app/oracle/diag/rdbms/PREPROD/incident/incdir_164153/PREPROD_smon_6488324_i164153.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home
System name:    AIX
Node name:    ehdb4
Release:    1
Version:    6
Machine:    00057919D600
Instance name: PREPROD
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 6488324, image: oracle@ehdb4 (SMON)


*** 2015-09-04 14:09:47.431
*** SESSION ID:(487.1) 2015-09-04 14:09:47.431
*** CLIENT ID:() 2015-09-04 14:09:47.431
*** SERVICE NAME:(SYS$BACKGROUND) 2015-09-04 14:09:47.431
*** MODULE NAME:() 2015-09-04 14:09:47.431
*** ACTION NAME:() 2015-09-04 14:09:47.431

Dump continued from file: /u01/app/oracle/diag/rdbms/PREPROD/trace/PREPROD_smon_6488324.trc
ORA-07445: exception encountered: core dump [PC:0x1036B65440000] [SIGSEGV] [ADDR:0x1036B65440000] [PC:0x1036B65440000] [Address not mapped to object] []

========= Dump for incident 164153 (ORA 7445 [PC:0x1036B65440000]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1036B65440000] [PC:0x1036B65440000, {empty}] [flags: 0x0, count: 1]
Registers:
iar: 0x0001036b65440000 lr: 0x0001036b65440000
msr: 0xa00000000000d032 cr: 0x00000000444a8824
r00: 0x0001036b65440000 r01: 0x0ffffffffffeefe0 r02: 0x0000000110659e40
r03: 0x0000000000000000 r04: 0x000000000000000e r05: 0x0000000000000000
r06: 0x0700009851025060 r07: 0x0700009c6c5a5338 r08: 0x0000000000000008
r09: 0x000000000000000e r10: 0x0700009c0234f6a8 r11: 0x0700009c6c5a5338
r12: 0x00000000484a8828 r13: 0x00000001106c2448 r14: 0x0000000000001fe8
r15: 0x0000000002680043 r16: 0x0000000110000298 r17: 0x0700000000013600
r18: 0x0700009c6c5a5a58 r19: 0x00000000444a8883 r20: 0x0000000000000000
r21: 0x0000000000000110 r22: 0x0000000000000000 r23: 0x0700009c6c5a5758
r24: 0x0000000000000003 r25: 0x0ffffffffffef8f0 r26: 0x0700009c6c5a5828
r27: 0x0ffffffffffefa78 r28: 0x0ffffffffffefe20 r29: 0x0700009bdfbed598
r30: 0x0700009c6c5a5338 r31: 0x0000000000000003

*** 2015-09-04 14:09:47.433
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5ansr7r9htpq3) -----
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1

----- Call Stack Trace -----

skdstdst()+40 bl 107c953f0 000000000 ? 000000001 ?
000000003 ? 000000001 ?
000000000 ? 000000001 ?
000000003 ? 000000001 ?
ksedst1()+112 call skdstdst() 1400B9A249DAA178 ?
484A384100000000 ?
110853E30 ? 000002004 ?
1106D0680 ? 10A72B2BC ?
000000000 ? 1106D0680 ?
ksedst()+40 call ksedst1() 30301CA5FE0 ? 002050033 ?
.....



============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | UPDATE STATEMENT | | | | 2 | |
| 1 | UPDATE | UNDO$ | | | | |
| 2 | INDEX UNIQUE SCAN | I_UNDO1 | 1 | 69 | 1 | 00:00:01 |
--------------------------------------+-----------------------------------+

----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=32(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
kxsbbbfp=700009c3fddb7da bln=32 avl=21 flg=09
value="_SYSSMU32_1350726082$"
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1109c1fe8 bln=24 avl=02 flg=05
value=3
Bind#2
.....
.....

=========================
Workaround with solution
=========================

1) I removed thread 2 redo logs ( source 2 node RAC)
2) Analyzed structure undo table and index:

ANALYZE TABLE UNDO$ VALIDATE STRUCTURE CASCADE ONLINE;
ANALYZE INDEXI_UNDO1 VALIDATE STRUCTURE ONLINE;

3) I found similar issue in Oracle Doc ID : 15850031.8, The issue is related to "Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X' ". For this issue one patch I applied. i.e., patch - 15850031

Now the issue has been fixed.

Thanks you.

Rename database using nid (newdb id) utility

Rename database using newdb id utility ( Standalone database)
-- Before Rename

$ . oraenv
ORACLE_SID = [prod] ? prod
The Oracle base remains unchanged with value /u01/app/oracle

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      READ WRITE           PRIMARY

SQL> shutdown immediate;
SQL> startup mount;

Note: If you are doing for the first time, then take a full backup for security purpose.

$ cd $ORACLE_HOME/dbs
$ nid target=system/manager dbname=prod1 setname=yes

bash-4.2$ nid target=system/manager dbname=prod1 setname=yes

DBNEWID: Release 11.2.0.3.0 - Production on Sat Sep 12 14:40:51 2015

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

Connected to database PROD (DBID=302068115)

Connected to server version 11.2.0

Control Files in database:
    /u02/oradata/prod/control01.ctl
    /u02/oradata/prod/control02.ctl

Change database name of database PROD to PROD1? (Y/[N]) => Y

Proceeding with operation
Changing database name from PROD to PROD1
    Control File /u02/oradata/prod/control01.ctl - modified
    Control File /u02/oradata/prod/control02.ctl - modified
    Datafile /u02/oradata/prod/system01.db - wrote new name
    Datafile /u02/oradata/prod/sysaux01.db - wrote new name
    Datafile /u02/oradata/prod/undotbs01.db - wrote new name
    Datafile /u02/oradata/prod/users01.db - wrote new name
    Datafile /u02/oradata/prod/example01.db - wrote new name
    Datafile /u02/oradata/prod/temp01.db - wrote new name
    Control File /u02/oradata/prod/control01.ctl - wrote new name
    Control File /u02/oradata/prod/control02.ctl - wrote new name
    Instance shut down

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

$ . oraenv
ORACLE_SID = [prod] ? prod1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/db_home
The Oracle base remains unchanged with value /u01/app/oracle

Note : Now create your parameter file, password file and ensure an entry in /etc/oratab.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 12 14:45:29 2015

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size                  2233480 bytes
Variable Size            6476008312 bytes
Database Buffers         6341787648 bytes
Redo Buffers                7340032 bytes
Database mounted.
SQL>

SQL> alter database open;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD1     READ WRITE           PRIMARY

SQL>


Thanks!!!

Export backup via database link - Fix ORA-39149 & ORA-31631:

Export backup via database link ( Oracle 10g and above)

Pr-requisites:

1) Both remote and local databases should communicate each other. Add both instance entry in tnsnames.ora file.
2) Create one user in Remote database( PROD_RMT) like below:

create user DBLINK_USER
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT;
grant CONNECT to DBLINK_USER;
grant EXP_FULL_DATABASE to DBLINK_USER;


3) Create database link:
CREATE DATABASE LINK remote_dblink CONNECT TO DBLINK_USER IDENTIFIED BY DBLINK_USER USING 'PROD_RMT';

4) Example o export script:

expdp
network_link=remote_dblink
directory=EXPORT
dumpfile=HR_PROD_RMT.dmp
logfile=HR_PROD_RMT_exp.log
schemas=HR

Issues:

1) When grant not available, you may face below error. I have created the above export script in a .par file and executed with giving "EXP_FULL_DATABASE" grant to the user.


$ expdp parfile=test_dblink.par

Export: Release 11.2.0.3.0 - Production on Wed Sep 2 17:51:38 2015

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user


Solution:


grant EXP_FULL_DATABASE to DBLINK_USER;


Note:

In the case of exports, the NETWORK_LINK parameter identifies the database link pointing to the source server. The objects are exported from the source server in the normal manner, but written to a directory object on the local server, rather than one on the source server. Both the local and remote users require the EXP_FULL_DATABASE role granted to them.

For imports, the NETWORK_LINK parameter also identifies the database link pointing to the source server. The difference here is the objects are imported directly from the source into the local server without being written to a dump file. Although there is no need for a DUMPFILE parameter, a directory object is still required for the logs associated with the operation. Both the local and remote users require the IMP_FULL_DATABASE role granted to them.

Search from my post for export and import:
1) Export or Import - Taking consistent backup using expdp
2) Export or Import - Taking full backup using expdp excluding unused big tables
3) Export or Import - Taking backup to import in downgrade oracle version
4) Export or Import - Remap / overwrite schema using impdp

Check progress Export / Import: See the sample example:

-- Import progress

SQL> select sid, serial#, sofar, totalwork
  2  from v$session_longops;

       SID    SERIAL#      SOFAR  TOTALWORK
---------- ---------- ---------- ----------
       587          3       1054      38723

SQL> /

       SID    SERIAL#      SOFAR  TOTALWORK
---------- ---------- ---------- ----------
       587          3       2101      38723

SQL> /

       SID    SERIAL#      SOFAR  TOTALWORK
---------- ---------- ---------- ----------
       587          3      38718      38723


Thanks .