Category Archives: Oracle RAC

Oracle datapump Import (IMPDP) fails due to Error ORA-31693,ORA-31640,ORA-19505,ORA-27037 Oracle 11gR2

Leave a reply


Error Details:
==============
ORA-31693: Table data object “COMMON”.”LOC_ITEM_DSPTCH_SPEC” failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp” for read
ORA-19505: failed to identify file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Solution Description:
=====================
Issue is due to the mount/file system was not being accessible from the second node in the RAC.
Then I made Cluster=N to force Data Pump to use only the instance where the job is started and ran again data pump job using below parfile

userid=”/ as sysdba”
CLUSTER=N
directory=MYDIR
JOB_NAME=JOB24_10thFeb2015
dumpfile=OMS_O_CUST_COMMON.dmp
logfile=imp_OMS_O_CUST_COMMON_10thFeb.log
TABLE_EXISTS_ACTION=REPLACE
EXCLUDE=db_link
schemas=OMS_O,CUST,COMMON

Advertisement

Step by Step Guide for Oracle 12c 2 Nodes RAC environment on laptop version 1.0

2 Replies

Short Introduction:
===================
It is my humble effort to help “me” like people to setup their own RAC environment on Laptop for practice. In second version I am planning to include 12c Dataguard and Pluggable Database.

Please don’t hesitate to give me your valuable feedback…for further improvement of this document.

2nodesoracle12c

CRS-2800 | Cannot start resource

Leave a reply

set lines 190
set pages 150
select host_name,name,instance_name,database_role,status,open_mode,logins from v$instance,v$database;

HOST_NAME NAME INSTANCE_NAME DATABASE_ROLE STATUS OPEN_MODE LOGINS
—————————————————————- ——— —————- —————- ———— ——————– ———-
shanojrac1 ORCL ORCL1 PRIMARY OPEN READ WRITE ALLOWED

[grid@shanojrac1 root]$ srvctl status database -d orcl
Instance ORCL1 is not running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3

[oracle@shanojrac1 ~]$ srvctl start instance -i ORCL1 -d ORCL
PRCR-1013 : Failed to start resource ora.orcl.db
PRCR-1064 : Failed to start resource ora.orcl.db on node shanojrac1
CRS-2800: Cannot start resource ‘ora.OCR_DATA.dg’ as it is already in the INTERMEDIATE state on server ‘shanojrac1’
[oracle@shanojrac1 ~]$ srvctl stop instance -i ORCL1 -d ORCL
PRCC-1017 : ORCL was already stopped on shanojrac1

[oracle@shanojrac1 ~]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE INTERMEDIATE shanojrac1 CHECK TIMED OUT
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE OFFLINE Instance Shutdown
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

=======================================
FIX:
=======================================

1) When you use the Oracle VM RAC templates with 11.2.0.2 and make use of role separation, you will not be able to build and RDBMS unless you
perform the following steps. During the creation of the database you will receive messages, Oracle not available. This is caused by the incorrect
setasmgid group used during the initiel configuration of you RAC 11.2.0.2 cluster.

Note:
—–
Please keep in mind that below work around is only applicable
when using role separation for the GRID infrastructure and uses the ORACLE RAC 11gR2 VM templates.

# cd /u01/app/11.2.0/grid/rdbms/lib
# cp -a ins_rdbms.mk ins_rdbms.mk.orig
# vi +1099 ins_rdbms.mk

Change line 1099 in ins_rdbms.mk, from this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) $(CONFIG)

to this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) config.$(OBJ_EXT)

2) Then tried to stop and start the ora.crsd to come out of INTERMEDIATE state.

[grid@shanojrac1 lib]$ crsctl stop res ora.crsd -init
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘shanojrac1’
CRS-2677: Stop of ‘ora.crsd’ on ‘shanojrac1’ succeeded
[grid@shanojrac1 lib]$ crsctl start res ora.crsd -init
CRS-2672: Attempting to start ‘ora.crsd’ on ‘shanojrac1’
CRS-2676: Start of ‘ora.crsd’ on ‘shanojrac1’ succeeded

[grid@shanojrac1 bin]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE ONLINE shanojrac1 Open
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

[grid@shanojrac1 bin]$ srvctl status database -d orcl
Instance ORCL1 is running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3