Advertisements

Oracle Exadata Interview Questions and Answers:

  • 1) What are the advantages of Exadata?
    The Exadata cluster allows for consistent performance while allowing for increased throughput. As load increases on the cluster the performance remains consistent by utilizing inter-instance and intra-instance parallelism.
    It should not be expected that just moving to Exadata will improve performance. In most cases it will especially if the current database host is overloaded.
    2) What is the secret behind Exadata’s higher throughput?
    Exadata ships less data through the pipes between the storage and the database nodes and other nodes in the RAC cluster.
    Also it’s ability to do massive parallelism by running parallel processes across all the nodes in the cluster provides it much higher level of throughput.
    It also has much bigger pipes in the cluster using Infiniband interconnect for inter-instance data block transfers as high as 5X of fiberchannel networks.
    3) What are the key Hardware components?
    DB Server
    Storage Server Cells
    High Speed Infiniband Switch
    Cisco Switch
  • 4) What are the Key Software Features?
    Smart Scan,
    Smart Flash Cache
    Storage Index
    Exadata Hybrid Columnar Compression (EHCC)
    IORM (I/O Resource Manager)
    5) What is a Cell and Grid Disk?
    Cell and Grid Disk are a logical component of the physical Exadata storage. A cell or Exadata Storage server cell is a combination of Disk Drives put together to store user data. Each Cell Disk corresponds to a LUN (Logical Unit) which has been formatted by the Exadata Storage Server Software. Typically, each cell has 12 disk drives mapped to it.
    Grid Disks are created on top of Cell Disks and are presented to Oracle ASM as ASM disks. Space is allocated in chunks from the outer tracks of the Cell disk and moving inwards. One can have multiple Grid Disks per Cell disk.
  • 6) What is IORM?
    IORM stands for I/O Resource Manager.
    It manages the I/O demand based on the configuration, with the amount of resources available. It ensures that none of the I/O cells become oversubscribed with the I/O requests. This is achieved by managing the incoming requests at a consumer group level.
    Using IORM, you can divide the I/O bandwidth between multiple databases.
    To implement IORM resource groups, consumers and plans need to be created first.
    7) What is hybrid columnar compression?
  • Hybrid Columnar compression, also called HCC, is a feature of Exadata which is used for compressing data at column level for a table.
    It creates compression data units which consist of logical grouping of columns values typically having several data blocks in it. Each data block has data from columns for multiple rows.
    This logarithm has the potential to reduce the storage used by the data and reduce disk I/O enhancing performance for the queries.
    The different types of HCC compression include:
    •  Query Low
    •  Query High
    •  Archive High
    •  Archive Low
    8) What is Flash cache?
    Four 96G PCIe flash memory cards are present on each Exadata Storage Server cell which provide very fast access to the data stored on it.
    This is further achieved by also provides mechanism to reduces data access latency by retrieving data from memory rather than having to access data from disk. A total flash storage of 384GB per cell is available on the Exadata appliance.
    9) What is Smart Scan?
    It is a feature of the Exadata Software which enhances the database performance many times over. It processes queries in an intelligent way, retrieving specific rows rather than the complete blocks.
    It applies filtering criteria at the storage level based on the selection criteria specified in the query.
    It also performs column projection which is a process of sending only required columns for the query back to the database host/instance.
    10) What are the Parallelism instance parameter used in Exadata?
  • The parameter PARALLEL_FORCE_LOCAL can be specified at the session level for a particular job.
    11) How do you Test performance of Exadata?
    You can use the “calibrate” commands at the cellcli command line.
    12)What are the ways to migrate onto Exadata?
  • Depending on the downtime allowed there are several options:
    Oracle DataGuard
    Traditional Export/Import
    Tablespace transportation
    Goldengate Replication after a data restore onto Exadata.
    13) What types of operations does Exadata “offload”?
    Some of the operations that are offloaded from the database host to the cell servers are:
    Predicate filtering
    Column project filtering
    Join processing
    Backups
    14) What is cellcli?
    This is the command line utility used to managed the cell storage.
    15) How do you create obtain info on the Celldisks?
    At the cellcli command line you can issue the “list celldisk” command.
    16) How would you create a grid disk?
    At the cellcli command you would need to issue the “create grididsk all ..” command.
    16) What are the cellinit.ora and the cellip.ora files used for?
    These files have the hostnames and the ip address of all the nodes in the cluster. They are used to run commands on remote database and cellserver nodes from a local host.
    17) Which package can be used to estimate the compression ration of table?
    DBMS_COMPRESSION
    18) Background services of Cell Server
    MS- Management Server
    cellsrv – Cell Server
    RS – Restart Server
    19) How many disk comes with in a storage cell?
                          12
    20) What is the purpose of spine switch?
    Spine switch is used to connect or add more Exadata machine in the cluster
    21) How to migrate database from normal setup to Exadata ?
    There many methods we can use to migrate DB to Exadata. Below are some of them.
    1. Export/Import
    2. Physical Standby
    3. Logical Standby
    4. Transportable Tablespace
    5. Transportable Database
    6. Golden gate
    7. RMAN cold and hot backup restoration
    8. Oracle Streams
    22) Can we use flash disk as ASM disk?
    Yes
    23) Which protocol used for communication between database server and storage server?
    iDB protocol
    24) which OS is supports in Exadata?
    Database servers has two option for OS either Linux or Solaris which can be finalized at the time of configuration Cell storage comes with Linux only
    25) What is ASR?
    ASR is the tool to manage the Oracle hardware. Full form of ASR is Auto Service Request. Whenever any hardware fault occurs ASR automatically raise SR in Oracle Support and send notification to respective customer.
    26) How to upgrade firmware of Exadata components?
    It can be done through ILOM of DB or Cell server.
    27) Where we can define which cell storage can be used by particular database server?
    CELLIP.ORA file contains the list of storage server which is accessed by DB server.
    28) What are the Exadata Health check tools?
    1. Exacheck
    2. sundiagtest
    3. oswatcher
    4. OEM 12c
    29) What is EHCC?
    EHCC is Exadata Hybrid Columnar Compression which is used to compress data in the Database.
    30) What is offloading and how it works?
    It refers to the fact that part of the traditional SQL processing done by the database can be “offloaded” from the database layer to the storage layer
    The primary benefit of Offloading is the reduction in the volume of data that must be returned to the database server. This is one of the major bottlenecks of most  large databases.
    31) What is the difference between cellcli and dcli?
    Cellcli can be used on respective cell storage only.
    DCLi (Distributed command Line Utility) – DCLI can be used to replicate command on multipla storage as well as DB servers.
    32) What is IORM and what is its role in Exadata?
    IORM stand for I/O Resource Manager which manages the I/Os of multiple database on storage cell.
    33) How we can check whether oracle best practice has been configured on Exadata?
    We can execute Exacheck and verify the best practice setup on Exadata machine.
    34) How many networks required in Exadata?
    1. Public/Client Network — For Application Connectivity
    2. Management Network — For Exadata H/W management
    3. Private Network — For cluster inter connectivity and Storage connectivity
    35) What is the command to enable query high compression on table?
    SQL>alter table table_name move compress for query high;
    36) How to take cell storage software backup?
    It is not required to take a backup as it happens automatically. Exadata use internal USB drive called the Cellboot Flash Drive to take backup of software.
    37) What is the difference between wright-through and write-back flashcache mode?
    1. writethrough –> Falshcache will be used only for reading purpose
    2. writeback –> Flashcache will be used for both reading and writing
    38) Which feature of Exadata is used to eliminate disk IO?
    Flash Cache
    39) What is the capacity of Infiniband port ?
    40 Gbps
    40) What is the difference between high capacity and high performance disk?
    1. High capacity disk comes with more storage space and less rpm (7.5k)
    2. High Performance disk comes with less storage and high rpm (15k)
    41) When one should execute Exacheck?
    Before and after any configuration change in Database Machine
    42) What is grid disk?
    Grid Disks are created on top of Cell Disks and are presented to Oracle ASM as ASM disks.
    Space is allocated in chunks from the outer tracks of the Cell disk and moving inwards. One can have multiple Grid Disks per Cell disk.
    43) Which network is used for RAC inter-connectivity?
    Infiniband Network
    44) What is Smart Scan?
    It is a feature of the Exadata Software which enhances the database performance many times over. It processes queries in an intelligent way, retrieving specific rows rather than the complete blocks. It applies filtering criteria at the storage level based on the selection criteria specified in the query. It also performs column projection which is a process of sending only required columns for the query back to the database host/instance.
    45) What are the Parallelism instance parameter used in Exadata?
    The parameter PARALLEL_FORCE_LOCAL can be specified at the session level for a particular job.
    46) Which statistic can be used to check flash hit ration on database level?
    Cell flash cache read hits
    47) Which disk group is used to keep OCR files on Exadata?
    +DBFS_DG
    48) How many Exadata wait events contained in 11.2.0.3 release?
    There are 53 wait events are exadata specific events.
    49) What is the difference between DBRM and IORM?
    DBRM is the feature of database while IORM is the feature of storage server software.
    50) Which ASM parameters are responsible for Auto disk management in Exadata?
    _AUTO_MANAGE_MAX_ONLINE_TRIES — It controls maximum number of attempts to make disk Online
    _AUTO_MANAGE_EXADATA_DISKS — It control auto disk management feature
    _AUTO_MANAGE_NUM_TRIES    — It controls maximum number of attempt to perform an automatic operation
    51) How to enable Flashcache compression?
    CellCLI> ALTER CELL flashCacheCompress=true
    52) How many Exadata Storage Server Nodes are included in Exadata Database Machine X4-8?
    14 storage nodes
    53) What is client or public network in exadata?
    Client or public network is used to established connectivity between database and application.
    54) What are the steps involved for initial Exadata configuration?
    Initial network preparation
    Configure Exadata servers
    Configure Exadata software
    Configure database hosts to use Exadata
    Configure ASM and database instances
    Configure ASM disk group for Exadata
    55) What is iDB protocol?
    iDB stands for intelligent database protocol. It is a network based protocol which is responsible to communicate between storage cell and database server.
    56) What is LIBCELL?
    Libcell stands for Library Cell which is linked with Oracle kernel. It allows oracle kernel to talk with the storage server via network based instead of operating system reads and writes.
    57) Which packaged is used by compression adviser utility?
    DBMS_COMPRESSION package
    58) What is the primary goal of storage index?
    Storage indexes are a feature unique to the Exadata Database Machine whose primary goal is to reduce the amount of I/O required to service I/O requests for Exadata Smart Scan.
    59) What is smart scan offloading?
    Offloading and Smart Scan are two terms that are used somewhat interchangeably. Exadata Smart
    Scan offloads processing of queries from the database server to the storage server.
    Processors on the Exadata Storage Server process the data on behalf of the database SQL query. Only the data requested in the query is returned to the database server.
    60) What is checkip and what the use of it?
    Checkip is the OS level script which contains IP address and hostname which will be used by Exadata in configuration phase. It checks network readiness like proper DNS configuration, it also checks there is no IP duplication in the network by pinging it which not supposed to ping initially.
    61) Which script is used to reclaim the disk space of unused operating system?
    For Linux: reclaimdisks.sh
    For Solaris: reclaimdisks.pl
    62) How database server communicates to storage cell?
    Database server communicates with storage cell through infiniband network.
    63) Can I have multiple celldisk for one grid disk?
    No. Celldisk can have multiple griddisk but griddisk cannot have multiple celldisk
    64) How many FMods available on each flash card?
    Four FMods (Flash Modules) are available on each flash card.
    65) What is smart flash log?
    Smart flash log is a temporary storage area on Exadata smart flash cache to store redoes log data.
    66) Which parameter is used to enable and disable the smart scan?
    cell_offload_processing
    67) How to check infiniband topology?
    We can verify infiniband switch topology by executing verify-topology script from one of our database server.
    68) Can we use HCC on non-exadata environment?
    No, HCC is only available data stored on Exadata storage server.
    69) What is resource plan?
    It is collection of plan directives that determines how database resources are to be allocated.
    70) What is DBFS?
    DBFS stands for Database File system which can be built on ASM disk group using database tablespace.
    71) What is the purpose of infiniband spine switch?
    Spine switch is used to connect multiple exadata database machines.
    72) What is offload block filtering?
    Exadata storage server filters out the blocks that are not required for the incremental backup in progress so only the blocks that are required for the backup are sent to the database.
    73) Which protocol used by ASR to send notification?
    SNMP
    74) Is manually intervance possible in storage index?
           No
    75) What are the options to update cell_flashcache for any object?
    KEEP
    DEFAULT
    NONE
    76) What is the default size of smart flash log?
    512MB per module.
    Each storage cell having 4 modules so its 4X512 MB per CELL
    77) What is flash cache and how it works?
    The flash cache is a hardware component configured in the exadata storage cell server which delivers high performance in read and write operations.
    Primary task of smart flash cache is to hold frequently accessed data in flash cache so next time if same data required than physical read can be avoided by reading the data from flash cache.
Advertisements

Soft parsing of SQL statements was consuming significant database time.

Every query needs to be parsed before it got executed. If some queries are quite frequently used, a good design will be reuse the parsed query. That means, it will not parse the same query again (provided that we are using bind variables), rather will reuse the existing parsed query and then execute it. In an ideal world, execute should be more than the parse. More parsing requires more CPU.

Ideally when execute is more than parse, the figure will be positive. The ratio goes down if there are more parses than the execute. Then some of the queries are parsed, but less or never executed!.

Causes could be one of the followings:

  • There might be no “Prepared Statement caching” in java layer or in jdbc connection pool.

Solution:

Enable Statement Caching in Java:

// Enable statement caching

((OracleConnection)connection).setStatementCacheSize(x);

((OracleConnection)connection).setImplicitCachingEnabled(true);
  • There might be the case, before the execution of a query, the cursor was closed.
  • There might not be enough “session cached cursors” to hold the queries to reuse.

 

Fallback if you cannot change the application(java) to use statement caching :

session_cached_cursors = X
  • There might be the case where new queries were coming to play quite frequently.
  • There might be connection interruptions, this need to be investigate from network end.

Below SQL query will help to identify, the SQL’s which is parsed but not executed or less executed:

set linesize 200;
set pagesize 1000;
col sql_text format a40;
SELECT sq.sql_text, st.executions_total, st.parse_calls_total
, round( 100*(1-( st.parse_calls_total / st.executions_total )),2) execute_to_parse
, st.executions_delta, st.parse_calls_delta
, round( 100*(1-( st.parse_calls_delta / st.executions_delta )),2) delta_ratio
FROM DBA_HIST_SQLSTAT st
, DBA_HIST_SQLTEXT sq
, DBA_HIST_SNAPSHOT s
WHERE s.snap_id = st.snap_id
AND s.begin_interval_time >= to_date('2018-10-19 01:00 pm','YYYY-MM-DD HH:MI pm')
AND s.end_interval_time <= to_date('2018-10-19 03:00 pm','YYYY-MM-DD HH:MI pm') AND st.sql_id = sq.sql_id AND st.parsing_schema_name='<schema_owner>' AND st.executions_total !=0 AND st.executions_delta !=0 ORDER BY delta_ratio;

DBCA error while creating CDB database.

Attempt to create a database in Oracle Restart environment fails and the following error

dbca_error_1.JPG

ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/multisrv1/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.cdb1.db' on 'multisrv1' failed
ORA-01017: invalid username/password; logon denied
oracle.cluster.impl.crs.cops.CRSNativeResult.createException(CRSNativeResult.java:617)
oracle.cluster.impl.crs.cops.CRSNative.doStartResource(Native Method)
oracle.cluster.impl.crs.cops.CRSNative.genericStartResource(CRSNative.java:567)
oracle.cluster.impl.crs.cops.EntityOperations.startResource(EntityOperations.java:678)
oracle.cluster.impl.crs.CRSResourceImpl.start(CRSResourceImpl.java:883)
oracle.cluster.impl.crs.CRSResourceImpl.start(CRSResourceImpl.java:858)
oracle.cluster.impl.crs.CRSResourceImpl.start(CRSResourceImpl.java:846)
oracle.cluster.impl.common.SoftwareModuleImpl.start(SoftwareModuleImpl.java:525)
oracle.sysman.assistants.util.hasi.HADatabaseUtils.start(HADatabaseUtils.java:1476)
oracle.sysman.assistants.dbca.backend.PostDBCreationStep.executeImpl(PostDBCreationStep.java:1145)
oracle.sysman.assistants.util.step.BasicStep.execute(BasicStep.java:278)
oracle.sysman.assistants.util.step.Step.execute(Step.java:135)
oracle.sysman.assistants.util.step.StepContext$ModeRunner.run(StepContext.java:2941)
java.lang.Thread.run(Thread.java:745)
[Thread-291] [ 2018-05-01 10:46:38.078 GST ] [PostDBCreationStep.executeImpl:1156] Exception while Starting with HA Database Resource PRCR-1079 : Failed to start resource ora.cdb1.db
CRS-5017: The resource action "ora.cdb1.db start" encountered the following error:
ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/multisrv1/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.cdb1.db' on 'multisrv1' failed
ORA-01017: invalid username/password; logon denied

 

Cause:

grid user is not under dba group.

[root@multisrv1 ~]# id grid
uid=54323(grid) gid=54321(oinstall) groups=54321(oinstall),492(vboxsf),54323(asmadmin),54324(asmdba)

Solution:

Add grid user to dba group and relink the oracle binary

 
[root@multisrv1 ~]# usermod -a -G dba grid
[root@multisrv1 ~]# id grid
uid=54323(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),492(vboxsf),54323(asmadmin),54324(asmdba)

[oracle@multisrv1 bin]$ ./relink all
writing relink log to: /u01/app/oracle/product/12.2.0.1/db_1/install/relink.log
[oracle@multisrv1 bin]$

 

 

 

Upgrade the Oracle RAC Grid Infrastructure from release 12.1.0.2 to release 12.2.0.1.

Upgrade Oracle Grid Infrastructure:

Note: Oracle Grid Infrastructure 12.2 has added new features that require plenty of disk space in the CRS disk group. Therefore, you must expand the size of the CRS disk group before you can upgrade Oracle Grid to the new release.

Expand the CRS disk group in ASM.
## Shut down srv1 and srv2.
## Add a new fixed-size sharable disk to srv1. Give it the name DISK4 and set its size to 40 GB.

up21.JPG
In Oracle VirtualBox, click on srv1 >> click on Settings >> click on Storage in the right pane >> click on Controller: SATA >> click on Add Hardisk >> click on Add a New Disk >> select VDI option >> make it fixed-size >> enter the full-path filename of the disk >> set its size to 40 GB

up22.JPG
## Once the disk is created, make it sharable.
File menu item >> Virtual Media Manager >> select the created disk DISK4 >> click on the Modify button >> choose the option to make this file shareable >> press Ok >> press Close
## Link the new disk to srv2.
click on srv2 >> click on Settings >> click on Storage in the right pane >> click on Controller: SATA >> click on Add Hardisk >> click on an Existing Disk button>> navigate to the new disk file and press OK

 ## Create the directory of the new Oracle Grid home. Make grid the owner of the home directory.

Start srv1 and wait for its OS to load.
## Start Putty and login to srv1 as root user. Format the added disk.
## display all the available disks:
ls -l /dev/sd*
# format the disk:
# answer "n", "p","1", default, default, "w" when prompted
fdisk /dev/sde

## Add the partitioned disk to the ASM recognized disk list.
oracleasm listdisks
oracleasm createdisk DISK4 /dev/sde1
oracleasm listdisks

Start srv2 and wait for its OS to load.
## Start Putty and login to srv2 as root user.
## Scan the ASM disks and make sure DISK4 is seen by srv2.

oracleasm scandisks
oracleasm listdisks

## Login to the VirtualBox window of srv1 as grid.

## Start asmca utility and add DISK4 to the CRS disk group.
Right-click on the CRS disk group >> select Add Disks >> select DISK4 then click on OK button
You should see the CRS disk group size increased to nearly 50GB, as shown in the following screenshot:

up20.JPG

## Make sure you have Putty sessions connected to srv1 and srv2 as root.
## Create the directory of the new Oracle Grid home. Make grid the owner of the home directory.

##srv1
mkdir -p /u01/app/12.2.0/grid
chown grid:oinstall /u01/app/12.2.0/grid

##Create the same directory in srv2.

ssh srv2
mkdir -p /u01/app/12.2.0/grid
chown grid:oinstall /u01/app/12.2.0/grid
exit

##In the VirtualBox window of srv1, login as grid user

##Unzip the linuxx64_12201_grid_home.zip file to the new Grid home directory.

unzip linuxx64_12201_grid_home.zip -d /u01/app/12.2.0/grid

##In the terminal windows, change the current directory to the new Oracle Grid directory.

cd /u01/app/12.2.0/grid
export ORACLE_HOME=/u01/app/12.2.0/grid
./gridSetup.sh
up1

up2.JPG

up3.JPG

up4

 

Click on SSH Connectivity button

Enter the OS grid user password.

Click on Test button. If it reports that the SSH connectivity is not configured, click on Setup button.

up5.JPG

up6.JPG

up7

 

Make sure asmadmin and asmdba are selected.

up8

Make sure /u01/app/grid is selected.

up9Mark the check box “Automatically run configuration scripts

Enter the root password

up10

Set the upgrade on srv2 to Batch 2.

If you keep it in Batch 1, the system will not be available while the upgrade is going on.

up11

up12

up13.JPG

If you receive error: “cvuqdisk-1.0.10-1” being unavailable on the system, click on “Fix and Check Again” button.

The following warning can be ignored (for a production system they must be addressed):

– Memory is less than 8 GB

– resolv.conf Integrity

– (Linux)resolv.conf Integrity

Select Ignore All check box then click on Next button

Note: You could have run the Cluster Verification Utility (CVU) before running the installer. Running it from within the installer gives the same results.

up14.JPG

up15.JPG

click on Install button

up16.JPG

up17.JPG

up18.JPG

up19.JPG

In the Putty window, verify the upgrade has been successfully concluded:

/u01/app/12.2.0/grid/bin/crsctl check cluster -all
/u01/app/12.2.0/grid/bin/crsctl query crs activeversion

Installing and Using Swingbench 2.5

Swingbench is a free easy-to-use stress testing utility on Oracle databases.download

Click the link above to download the full document.

 

How to Reconfigure Oracle Restart | ASM startup fail with ORA-29701.

How to Reconfigure Oracle Restart :
Cause: Today I faced one issue while cloning my VM for making a dataguard server,
I done the server name change while crs was up and running and end up in ASM startup failure.

Solution:

The solution is to reconfigure Oracle Restart.

[root@srv2 grid]# /u01/app/12.1.0/grid/crs/install/roothas.pl -deconfig
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2016/07/21 19:20:30 CLSRSC-39: Oracle Restart stack is not active on this node

2016/07/21 19:20:30 CLSRSC-312: Failed to verify HA resources

Died at /u01/app/12.1.0/grid/crs/install/crsdeconfig.pm line 1358.
***********************************************************************************
######OK LOOKS LIKE WE NEED TO ADD FORCE OPTION ###################
***********************************************************************************

[root@srv2 grid]# /u01/app/12.1.0/grid/crs/install/roothas.pl -deconfig -force
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
2016/07/21 19:22:03 CLSRSC-337: Successfully deconfigured Oracle Restart stack

[root@srv2 grid]# ./root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME=  /u01/app/12.1.0/grid
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user ‘grid’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node srv2 successfully pinned.
2016/07/21 19:23:17 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.conf’

srv2     2016/07/21 19:23:37     /u01/app/12.1.0/grid/cdata/srv2/backup_20160721_192337.olr     0
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘srv2’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘srv2’
CRS-2677: Stop of ‘ora.evmd’ on ‘srv2’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘srv2′ has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2016/07/21 19:25:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

2016/07/21 19:35:56 CLSRSC-352: CRS is already configured on this node for the CRS home location /u01/app/12.1.0/grid

[root@srv2 grid]# sudo su – grid
[grid@srv2 ~]$ srvctl add asm
[grid@srv2 ~]$ srvctl start asm
[grid@srv2 ~]$ ps -ef|grep pmon
grid      4902     1  0 19:37 ?        00:00:00 asm_pmon_+ASM
grid      4996  4778  0 19:37 pts/0    00:00:00 grep pmon
[grid@srv2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 19:38:11 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> create spfile=’+CRS’ from pfile=’/u01/test.ora’;
create spfile=’+CRS’ from pfile=’/u01/test.ora’
*
ERROR at line 1:
ORA-17635: failure in obtaining physical sector size for ‘+CRS’
ORA-15001: diskgroup “CRS” does not exist or is not mounted

SQL> alter diskgroup CRS mount;
alter diskgroup CRS mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “CRS” cannot be mounted
ORA-15040: diskgroup is incomplete

SQL>  alter diskgroup DATA mount;
alter diskgroup DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “DATA” cannot be mounted
ORA-15040: diskgroup is incomplete

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> create spfile=’+CRS’ from pfile=’/u01/test.ora’;
create spfile=’+CRS’ from pfile=’/u01/test.ora’
*
ERROR at line 1:
ORA-17635: failure in obtaining physical sector size for ‘+CRS’
ORA-15001: diskgroup “CRS” does not exist or is not mounted

SQL>
Broadcast message from root@srv2.localdomain
(unknown) at 19:42 …

The system is going down for halt NOW!

Broadcast message from root@srv2.localdomain
(unknown) at 19:42 …

The system is going down for halt NOW!
login as: oracle
oracle@192.168.1.144’s password:
Last login: Thu Jul 21 19:01:31 2016 from 192.168.1.5
-bash: Page:: command not found
[oracle@srv2 ~]$ ps -ef|grep pmon
oracle    5157  5129  0 19:48 pts/0    00:00:00 grep pmon
[oracle@srv2 ~]$ sudo su – grid
[sudo] password for oracle:
[grid@srv2 ~]$ srvctl add asm
PRCA-1095 : Unable to create ASM resource because it already exists.
[grid@srv2 ~]$ srvctl start asm
PRCC-1014 : asm was already running
PRCR-1004 : Resource ora.asm is already running
PRCR-1079 : Failed to start resource ora.asm
CRS-5702: Resource ‘ora.asm’ is already running on ‘srv2′
[grid@srv2 ~]$ ps -ef|grep pmon
grid      5159     1  0 19:48 ?        00:00:00 asm_pmon_+ASM
grid      5437  5299  0 19:49 pts/0    00:00:00 grep pmon
[grid@srv2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 19:49:38 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> alter diskgroup CRS mount;
alter diskgroup CRS mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “CRS” cannot be mounted
ORA-15040: diskgroup is incomplete

SQL> !
[grid@srv2 ~]$ asmcmd
ASMCMD> mount all
ORA-15110: no diskgroups mounted (DBD ERROR: OCIStmtExecute)
ASMCMD> exit
[grid@srv2 ~]$ ls -l /dev/mapper/HDD* |wc -l
ls: cannot access /dev/mapper/HDD*: No such file or directory
0
[grid@srv2 ~]$ oakcli show disk
bash: oakcli: command not found
[grid@srv2 ~]$ sudo su –
[sudo] password for grid:
[root@srv2 ~]# oakcli show disk
-bash: oakcli: command not found
[root@srv2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@srv2 ~]# /etc/init.d/oracleasm listdisks
CRSDISK1
DATADISK1
FRADISK1
[root@srv2 ~]# sudo su – grid
[grid@srv2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 20:06:13 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option

SQL> shutdown immediate
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started

Total System Global Area 1140850688 bytes
Fixed Size                  2933400 bytes
Variable Size            1112751464 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted

SQL> shutdown
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup pfile=/u01/test.ora
ASM instance started

Total System Global Area 1140850688 bytes
Fixed Size                  2933400 bytes
Variable Size            1112751464 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> create spfile=’+CRS’ from pfile=’/u01/test.ora’;
create spfile=’+CRS’ from pfile=’/u01/test.ora’
*
ERROR at line 1:
ORA-17635: failure in obtaining physical sector size for ‘+CRS’

SQL> create spfile from pfile=’/u01/test.ora’;

File created.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> shutdown
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 1140850688 bytes
Fixed Size                  2933400 bytes
Variable Size            1112751464 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
—————————— ———- ———-
FRA                                 51199      47878
DATA                                51199      44783
CRS                                     0          0

SQL> alter diskgroup CRS mount;

Diskgroup altered.

SQL> select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
—————————— ———- ———-
FRA                                 51199      47878
DATA                                51199      44783
CRS                                  2047       1985

SQL>  create spfile=’+CRS’ from pfile=’/u01/test.ora’;

File created.

SQL> !ps -ef|grep pmon
grid      9401     1  0 20:09 ?        00:00:00 asm_pmon_+ASM
grid     10068  8903  0 20:12 pts/0    00:00:00 /bin/bash -c ps -ef|grep pmon
grid     10070 10068  0 20:12 pts/0    00:00:00 grep pmon

SQL>

*(/etc/host) (/etc/hosts)

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


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