datapatch failed with catconInit failed, exiting

start_processes: failed to open (Resource temporarily unavailable) pipe to SQL*Plus

catconInit failed, exiting 

Facing the issue with datapatch -verbose, once we executed the datapatch after successful completion of  Bundle patch on Oracle restart (standalone ASM using Grid 12.2.0.1) home. In sqlpatch_invocation.log file we found the message as like below 

start_processes: failed to open (Resource temporarily unavailable) pipe to SQL*Plus

catconInit failed, exiting

to fix this issue we did below workaround, 

Verified the cpu_count parameter,

show parameter cpu_count, if incase cpu_count is 48, alter that count to 4 .

alter system set cpu_count=4;

run the datapatch now, it will run successfully. Once completeion of datapatch set back the cpu_count to 48.

alter system set cpu_count=48;

for complete details check the DOC ID 2540548.1

Read More

How to Create Oracle ASM disks in windows

In windows environment ASM disks have to be created using asmtoolg.exe file, before that we have to create the partitions of given RAW disk using DISKPART utility in windows.

Once raw partition added to the target server, check the RAW partition available or not.

1. Connect to the command prompt and use below command to check the disk.

C:\diskpart 

DISKPART> list disk

2. Select the disk which you want to made partitions for ASM disk.

DISKPART> select disk 1

Read More

Top 5 I/O wait events in oracle AWR report

Top 5 I/O wait events in oracle:

“Db file sequential read” wait event occur on I/O operations on datafiles.

This wait event related to single block read, for index data blocks are table data blocks accessed through an index but can also be seen for datafile header blocks.

Troubleshooting:

Find the top sql statements in physical reads from

1. Statspack

2. AWR report under Sql order by reads or

3. From v$sql

Reasons for db file sequential read:

1.       If index range scans are involved, more blocks than necessary could be being visited if the index is unselective. By forcing and enabling the more selective index, we can access the same table data by visiting the fewer index blocks.

2.       If the indexes are fragmented, then again, we have to visit more blocks because there is a less index data per block.

3.       If the index being used has a large clustering factor, then more table data blocks have to be visited in order to get the rows in each index block.

Db file scattered read:

This wait even indicates that we are waiting for the read operation which reads number of blocks from disk. Most of the cases it indicates there is full table scan or index fast full scan.

It occurs when oracle performs multiblock reads from disk into non-contiguous buffers in the buffer cache. Such reads are issues for up to DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time. These typically happen for full table scans and fast full index scans.

Fast full index scans: When the data in the index is not in particular order.

Read More

What are the AWR, ADDM and ASH reports in Oracle? How to Analyse?

AWR, ASH and ADDM reports are tools to analyse the Oracle database performance bottlenecks of intervals.

ADDM Report:

How to generate the ADDM report?

To generate the ADDM report by non-sys users, they need below privileges. As a sys user no privileges are required.

How to grant the ADDM report generation privileges to non-sys users:

1.       Login as a sys user.

2.       Sql> grant advisor to ;

3.       Sql> grant select_catalog_role to ;

4.       Sql> grant execute on dbms_workload_repository to ;

Note: From 12c ADDM report also comes with AWR report.

ADDM generate script is in $ORACLE_HOME/RDBMS/admin/addmrpt.sql

Sql>@addmrpt.sql

ADDM report suggests potential resolution options for many issues. ADDM reports gives recommendations on various areas like CPU load, Memory Usage, I/O usage, Object contention, Application Issues, DB configuration issues, high load sql statements..etc.

Read More

What are the Oracle database and Grid/RAC health check/Monitoring tool’s?

Hang Manger

Quality Of service Management

TAF (Trace file analyser) (1513912.1)

Exacheck

Memory Guard

Cluster Healthcheck advisor

Cluster Health monitor

Cluster Verification utility

ORAchk

DBSAT (Database Security Assessment tool)

OSwatcher (301137.1)

Read More

Oracle Data guard Protection modes

Maximum Protection

In the Maximum Protection mode a transaction is only confirmed as “Committed”, when the data has been written both local and into at least one Standby Redolog file. If the Standby Database or the network between the databases breaks down, transactions could not be performed any longer, the primary database shuts down automatically. Oracle recommends to only use Maximum Protection mode, if at least two Standby databases exist.

For the Maximum Protection mode the following parameters must be set for the Redo transport:

AFFIRM

SYNC

Maximum Availibility

This mode is a compromise between data security and performance. First the Maximum Availability mode works just like the Maximum Protection mode. That means all transactions are transmitted simultaneously and the commit of the transaction is only sent, when the transaction is saved both local and on at least one Standby Redolog file. With the 12c Oracle feature “FastSync” the performance can be increased a bit by already confirming the transaction, when the data reached the Standby side, so when it is located in that memory.

Unlike the Maximum Protection mode, the primary database continues working after a short time, if the Standby side breaks down or a network error occurs. By this it switches to the Maximum Performance mode automatically, that means transactions are committed immediately. Once the Standby database is available again it automatically switches back to the Maximum Protection mode.

Following parameters are responsible for the Redolog transport in Maximum Availability mode:

AFFIRM

SYNC

NET_TIMEOUT

The parameter NET_TIMEOUT (default 30) tells the time (in seconds) after that the database shall switch to the Maximum Performance mode.

Maximum Performance

The Maximum Performance mode is used, when the primary database must not be compromised. That means transactions are confirmed, once they are saved into the local Redolog files and asynchronously transmitted to the Standby database. So in case of a breakdown of the primary database you can expect a loss of transactions.

Following parameters are responsible for the Redolog transport here:

NOAFFIRM

ASYNC

Read More

Oracle RAC GRID Daemons and Background process

Oracle Cluster Specific Daemons:

Crsd :

The CRS daemon (crsd) manages cluster resources based on configuration information that is stored in Oracle Cluster Registry (OCR) for each resource. This includes start, stop, monitor, and failover operations. The crsd process generates events when the status of a resource changes.

Cssd :

 Cluster Synchronization Service (CSS): Manages the cluster configuration by controlling which nodes are members of the cluster and by notifying members when a node joins or leaves the cluster. If you are using certified third-party clusterware, then CSS processes interfaces with your clusterware to manage node membership information. CSS has three separate processes: the CSS daemon (ocssd), the CSS Agent (cssdagent), and the CSS Monitor (cssdmonitor). The cssdagent process monitors the cluster and provides input/output fencing. This service formerly was provided by Oracle Process Monitor daemon (oprocd), also known as OraFenceService on Windows. A cssdagent failure results in Oracle Clusterware restarting the node. 

Diskmon :

Disk Monitor daemon (diskmon): Monitors and performs input/output fencing for Oracle Exadata Storage Server. As Exadata storage can be added to any Oracle RAC node at any point in time, the diskmon daemon is always started when ocssd is started. 

Evmd :

Event Manager (EVM): Is a background process that publishes Oracle Clusterware events 

Mdnsd :

Multicast domain name service (mDNS): Allows DNS requests. The mDNS process is a background process on Linux and UNIX, and a service on Windows. 

Gnsd :

Oracle Grid Naming Service (GNS): Is a gateway between the cluster mDNS and external DNS servers. The GNS process performs name resolution within the cluster. 

Ons :

Oracle Notification Service (ONS): Is a publish-and-subscribe service for communicating Fast Application Notification (FAN) events 

Oraagent :

oraagent: Extends clusterware to support Oracle-specific requirements and complex resources. It runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g Release 1 (11.1). 

Orarootagent :

Oracle root agent (orarootagent): Is a specialized oraagent process that helps CRSD manage resources owned by root, such as the network, and the Grid virtual IP address 

Oclskd :

Cluster kill daemon (oclskd): Handles instance/node evictions requests that have been escalated to CSS .

Gipcd :

Grid IPC daemon (gipcd): Is a helper daemon for the communications infrastructure 

Ctssd :

Cluster time synchronisation daemon(ctssd) to manage the time syncrhonization between nodes, rather depending on NTP. 

 RAC Background Process:

LMSn — Global Cache Service Process: It can mainly handle the cache fusion part. It handles the consistent copies of blocks that are transferred between instances. It receives the request from the LMD to perform lock requests. It rolls back any uncommitted transactions. There can be up to 10 LMS process running and can be started dynamically if demand requires. It also handles the global deadlock detections and monitors for the lock conversion timeouts.

LMON    — Global Enqueue Service Monitor: This process manages the GES, it maintains the consistency of GCSmemory in case of process death. It also responsible for cluster reconfiguration and locks reconfiguration.

LMD     — Global Enqueue Service Daemon: This manages eneque manager service request for the GCS. It also handles the deadlock detections and remote resource requests the other instances.

LCK0    — Instance Enqueue Process: Manages the instance resource requests and cross instance call operations for shared resources. It builds a list of invalid lock elements and validates the lock elements during recovery.

DIAG    — Diagnosability Daemon

GCS ensures a single system image of the data even though the data is accessed by multiple instances.

GES maintains or handles the synchronization of the dictionary cache, library cache, transaction locks, and DDL locks. In other words, GES manages enqueues other than data blocks. To synchronize access to the data dictionary cache, latches are used in exclusive (X) mode and in single-node cluster databases. Global enqueues are used in cluster database mode

Read More

RAC/Grid startup sequence in Oracle 11gR2

OHASD has access to OLR (oracle local registry). OHASD then reads the OLR content and initialize accordingly. 

OHASD brings up GPnP (ora.gpnpd)Daemon and CSS (ora.cssd) Daemon. 

CSS Daemon has access to the GPNP Profile stored on the local file system. I even found a copy of GPNP Profile directly stored  in OLR (in Oracle 12c release 2)

The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.

OHASD starts an ASM instance. The ASM instance uses special code to locate the contents of the ASM SPFILE, if it is stored in a Diskgroup. 

With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRS.

OHASD then starts CRSD (ora.crsd)damon with access to the OCR in an ASM Diskgroup.

And thus Clusterware completes initialization and brings up other cluster managed resources defined in OCR.

Level 1: OHASD Spawns:

• cssdagent – Agent responsible for spawning CSSD.
• orarootagent – Agent responsible for managing all root owned ohasd resources.
• oraagent – Agent responsible for managing all oracle owned ohasd resources.
• cssdmonitor – Monitors CSSD and node health (along wth the cssdagent).

Level 2: OHASD rootagent spawns:

• CRSD – Primary daemon responsible for managing cluster resources.
• CTSSD – Cluster Time Synchronization Services Daemon
• Diskmon
• ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

• MDNSD – Used for DNS lookup
• GIPCD – Used for inter-process and inter-node communication
• GPNPD – Grid Plug & Play Profile Daemon
• EVMD – Event Monitor Daemon
• ASM – Resource for monitoring ASM instances

Level 3: CRSD spawns:

• orarootagent – Agent responsible for managing all root owned crsd resources.
• oraagent – Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

• Network resource – To monitor the public network
• SCAN VIP(s) – Single Client Access Name Virtual IPs
• Node VIPs – One per node
• ACFS Registery – For mounting ASM Cluster File System
• GNS VIP (optional) – VIP for GNS

Level 4: CRSD oraagent spawns:

• ASM Resouce – ASM Instance(s) resource
• Diskgroup – Used for managing/monitoring ASM diskgroups.
• DB Resource – Used for monitoring and managing the DB and instances
• SCAN Listener – Listener for single client access name, listening on SCAN VIP
• Listener – Node listener listening on the Node VIP
• Services – Used for monitoring and managing services
• ONS – Oracle Notification Service
• eONS – Enhanced Oracle Notification Service
• GSD – For 9i backward compatibility
• GNS (optional) – Grid Naming Service – Performs name resolution

Read More

Oracle 12c Database mandatory background processes, new processes

Process: Oracle database having several types of processes, those are Oracle process, Server process, background processes and client process.

Oracle has introduced new mandatory background process in oracle 12c release and increased no.of process in existing mandatory BG process.

Mandatory Background process in Oracle 12c:

PMON (Process Monitor process): The process Monitor (PMON) monitors the background processes and performs process recovery when a server process or dispatcher process terminates abnormally.

PMON is responsible for cleaning up the database buffer cache and freeing the resources that the client process was using.

For example, PMON will resets the values of active transaction table, releases the locks that are no longer required, and removes the process ID from the list of active processes.

Transactional Table: The data structure with in an undo segment that holds transactional identifiers of the transactions using undo segments.

LERG (Listener registration process): The Listener registration process registers the information about database instance and dispatcher’s processes with oracle net listener.

LERG provides the listener with information about the following:

1.       Names of the database services provided by the database.

2.       Name of the database instance associated with the services and its current and maximum load.

3.       Service handlers (Dispatchers and dedicated servers) available for the instance, including their type, protocol addresses and current and maximum load.

SMON (System Monitor Process):

DBW (Database writer process):

LGWR (Log writer process):

CKPT (Check point process):

MMON and MMNL (Manageability Monitor process):

Recoverer Process (RECO):

Newly added background processes:

BWnn (Database writer process):

FENC (Fence Monitor Process):

IPCO (IPC service background process):

LDDn (Global enqueue service Daemon helper slave):

LGnn (Log writer worker):

LREG (Listener registration process):

OFSD (Oracle File server background process):

RPOP (Instant recovery repopulation daemon):

SAnn (SGA Allocator):

SCRB (ASM Disk Scrubbing Master process):

SCRn (ASM disk scrubbing slave repair process):

SCVn (ASM disk scrubbing slave verify process):

Read More

Oracle Dataguard background process, log apply service internals

Dataguard Background process:

1.       Logwriter process

2.       Archiver process

3.       Logwriter network server (LNS)

4.       Fetch archive logs (FAL) For client server mechanism

5.       Remote file server

6.       Managed recovery process for physical standby

7.       Logical standby process for logical standby

8.       Data guard broker monitor (DMON) – If incase dg broker enabled

Apply services at High Level: -          Data guard apply services comprises of two parts

o   Redo Apply for physical standby

o   Sql Apply for logical standby

-        The Physical standby and logical standby databases utilize the same redo transport and role management services, only apply service is different.

Redo apply: A physical standby database applies redo data received from the primary database using oracle media recovery process.

1.    Redo applies uses a specialized process called the managed recovery process.
2.   As the RFS process is writing redo data to standby redo logs (SRL’s). MRP process reads the redo data and applies it to the physical standby database.
3.    MRP is started in the physical standby database by mounting the database using below command.

-          Sql > alter database recover managed standby database using current logfile disconnect from the session;

Sql Apply: Sql apply uses a collection of background process that perform the task of applying changes from the primary database to the logical standby database.

 Logical standby database:

• -          Logical standby database contains the same logical information at standby DB.
• -          Physical organization and structure of the data can be different.
• -          Synchronization with the primary database through sql apply.
• -          Can be used for queries and reporting at any time.

-         Service to be involved for logical standby : Log transport services, Log apply services

Log Transport services:  Transmit the redo data from the primary database to standby. Enforce the data protection modes.

Log apply services: Automatically apply the archived redo logs on the standby database. Maintains transitional synchronization with primary database. Allows transitionally consistent read-only access to the data.

Read More

What are the Oracle Database upgradation method’s?

What is database upgradation:

Database upgradation means moving existing version of database to higher release version. In the process of upgradation of database dictionary compatible with a newer oracle database version. Below actions has included in the part of upgrading.

1.        Adding, dropping, or modifying columns in system tables and views.
2.        Creating new system packages or procedures.
3.        Modifying existing system packages or procedures.
4.        Creating, modifying, or dropping database users, roles, and privileges.
5.        Modifying seed data that is used by Oracle database components.

     Why go for upgradation:

    There are numerous features releasing in oracle version by version. Those newly released features will simplifying the DBA tasks and time.

Database upgradation methods:

1.        DBUA and command line
2.        Transportable tablespace or Full Transportable Export/Import
3.        Oracle Datapump Export / Import
4.        Traditional export and import methods.

For upgradation of from lower version to higher, must verify the Database upgrade compatibility matrix and fall back strategy.

Database upgrade/downgrade compatibility matrix Doc ID 551141.1

• Prerequisites for the database upgradation / check lists:
• Cleanup: Purge the recycle bin, check the invalid and duplicate objects in SYS and SYSTEM users.
• Components: Check for mandatory, invalid components and remove the obsolete components.
• Check the materialized views: Verify the all materialized view status, refresh in-case any materialized views that are not fresh.
• Create baseline: Preserve the performance statistics.
• Backup: Take the full backup of database, binaries backup.
• Dictionary statistics: Gather the dictionary statistics, it fasts the upgradation and reduce downtime.
• Time zone:  Verify the source database timezone and target database timezone, if source database timezone is higher version of target apply the timezone patch to match the source and target databases.

Note: Must be empty the recycle bin during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time.

Read More

What is the PGA in Oracle? What is the use of it?

In Oracle PGA is specific to operation system process or thread that is not shared by the other process or threads and it is process specific, it could not establish in SGA.

PGA is a heap that contains session dependent variables required by a dedicated or shard server process, server process allocates memory structures that requires in PGA.

Private Sql Area:

A private sql area contains the information about parsed sql statements and other session specific information for processing. When a server process executes a sql or pl/sql statements/codes the process uses the private sql area to store the bind variable values, query execution state information and query execution work areas.

Private sql area can be placed Program global are (PGA) in dedicated server architecture, In shared server architecture private sql area creates at SGA.

Read More

What are the bind variable in oracle? What is the use of bind variables?

How to create the bind variable in oracle, types of bind variables.

A bind variable is a placeholder of a sql statement that must be replaced with the valid value or value address for the statement to execute successfully. Using the bind variables can write the sql statements that accepts inputs or parameters at run time.

Below example will explain in detail about bind variable:

Select * from emp where emp_id= :v_empid; In this statement v_empid is bind varaiable.

Referencing bind variables:

We can reference bind variable in pl/sql by typing : colon and immediate followed by the variable.

Ex: :retrive_val :=1;

To change this bind variable in sql*plus you must enter pl/sql block

Begin

  :retrive_val := 4;

End;

/

Displaying the bind variable

Sql> print retrive_val;

Read More

Standby database creation steps for oracle standard edition in windows

Manual standby creation in oracle standard edition, manual log shipping method for creation standby database

In general, we use Data guard for Oracle standby database creation in enterprise edition, comes to standard edition we are unable to create the Disaster Recovery site using Data guard, because this feature disabled in oracle standard edition.

In Oracle standard edition standby database can creates using log shipping method, here we go with the complete steps.

For standby database creation proceed with rman recovery and user managed recovery process.

Steps to create standby DB in windows: 

1.       Make sure primary database is in archive log mode.

2.       Install oracle binaries at standby side and create same folder structure at standby server as like primary.

3.       Create pfile from spfile and send it to standby server ‘C:\app\oracle\product\11.2.0\database\’.

4.       Create standby control file send standby controlfile to DR server.

Sql> alter database create standby controlfile as ‘Ç:\app\standbycontrol.ctl’;

5.       Update the controlfile information in pfile.

6.       Create oracle service using oradim command and start the service.

oradim -NEW -SID ORCL -STARTMODE manual -PFILE C:\app\oracle\product\11.2.0\database\INITORCL.ORA"

7.       Take the backup of database from primary side.

Sql> alter database begin backup;

Copy all datafiles to standby server

Sql> alter database end backup;

8.       Start the standby database in mount state.

Sql> shut immediate

Sql> startup nomount

Sql> alter database mount standby database;

9.       Create standby archive log folder as a network drive at primary database side.

10.   Schedule the archives copy script at primary server and recover database script at standby server side.

11.   Archives_copy.bat

copy C:\app\oracle\oradata\ORCL\archive\*.0001 E:\dba\backup\arc_backup

12.   Recover_database.bat

@echo off

C:

cd \oracle\product\11202\BIN

set ORACLE_HOME=C:\oracle\product\11202

set ORACLE_SID=orcl

sqlplus / as sysdba @D:\oradata\orcl\archive\recover.sql

3. recover.sql

spool D:\oradata\orcl\archive\log.recovery replace

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

SELECT MAX(RECID) "Log id now" FROM V$LOG_HISTORY;

recover standby database;

auto

spool off

exit;

Read More