PRCR-1065: Failed to stop resource ora.asm

Error PRCR-1065 and CRS-2529 can be returned while stopping the ASM using srvctl command on Grid Infrastructure 11.2 and above as can be seen in the following.

[grid@salman1 ~]$ srvctl stop asm PRCR-1065 : Failed to stop resource ora.asm

Oracle Database 12c Patch/Upgrade from 12.1.0.1 to 12.1.0.2

For manual upgrade guide from 11g to 12c, click here. Same guide can also be followed to manually upgrade from 12.1.0.1 to 12.1.0.2.
Before going through this upgrade guide, you may be interested to view the guide to patch/upgrade standalone Grid Infrastructure from 12.1.0.1 to 12.1.0.2. 

ORA-38500 While Enabling Real-Time Redo Apply

If you are trying to enable real time redo apply whereby LGWR process of primary database writes directly to the standby redo log file of the standby database, you might face ORA-38500 error message. Following is an example of this error message.

SQL> ALTER DATABASE RECOVER MANAGED STAND

Grid Infrastructure 12c Patch/Upgrade from 12.1.0.1 to 12.1.0.2

In this post, I will be demonstrating the steps to upgrade/patch the Grid Infrastructure (GI) 12.1.0.1 to 12.1.0.2. Since ASM runs from GI home, ASM would be upgraded while upgrade of GI home. As we know that Oracle now provides full installation binaries for patchset releases so the binaries are required to be downloaded from MOS. You would need to download Patch

Error: "Message 279 not found" while starting Listener

One of my customers has Oracle 11.2.0.4 on windows 2008 R2 (x64). After reboot of this database server, listener did not come up automatically and even manually starting the listener was returning following error at the command prompt.

ORA-12514 During Switchover to Standby

Recently I was performing a switchover from primary to standby database using DGMGRL utility (My Dataguard environment utilizes Dataguard broker). I logged into the server which was hosting current standby database and initiated the DGMGRL command line interface. When I issued the switchover statement, DG Broker was able to convert

ORA-12514 During OEM Database Control Configuration for RAC

While configuring OEM Database Control for 2 nodes 11.2.0.3 RAC (On windows), I faced error "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor", about which I would be discussing in the following that what was the reason for this error and how I resolved it. 

Issues with Bind Variables and Bind Peeking

I would explain bellow that how bind peeking played a disastrous role in one of my databases. For usage of bind variables, a good post by Arup Nanda is available here

From one of my OLTP databases (10.2.0.4), suddenly I started receiving OEM alerts for “run queue length” as follows. (My server has total 24 CPUs)

Privilege Analysis

In Oracle 12c, we can do an analysis of privileges used by the users. This analysis can actually help to know if there are some privileges granted to some user which were never used by that user and hence can be revoked. This can be even more useful in a situation where DBA realizes that someone (probably previous DBA) has granted a lot of privileges to some users which are

WARNING: inbound connection timed out (ORA-3136)

Warning message inbound connection timed out (ORA-3136) can be seen in the alert log file if a client was failed to authenticate itself within a specified period of time, since connection was initiated first time. sqlne.ora parameter called "SQLNET.INBOUND_CONNECT_TIMEOUT" is used to specify a value in number of seconds, database would

ORA-01113 when Tablespace is in Backup Mode

On a 9.2.0.4 database, while logging in to the database, it was throwing error "ORA-01033: ORACLE initialization or shutdown in progress". This error means that database is either no-mount or mount state. So I logged in as SYSDBA and tried to open the database.

Excessive PGA Memory Usage by Sessions and System Hang

In this article I will explain how to handle memory leak by an oracle session process or if because of a bug in a code if an oracle processes consumes all available memory because of growth of PGA. Starting 12c, we can limit the amount of memory to which PGA of an Oracle session can grow. Click here to find out how.

ORA-00942 During upgrade to 11.2.0.3 or later on Windows

Problem
On Windows platform (32 bit or 64 bit), while upgrading 10.2.0.4 (or earlier version) database to 11.2.0.3 (with bundle patch applied), ORA-00942 error is expected to appear during the process, regardless of whether upgrade is being done manually or using DBUA.

ORA-29702 While Starting 10.2.0.4 Instance(s) on 11.2.0.3 RAC/Clusterware

I was in process of upgrading my 3 nodes 10.2.0.4 RAC database to 11.2.0.3 RAC on Windows 2008. Since we had new hardware, we decided to build a new 11.2.0.3 RAC and create a 10.2.0.4 standby on the new RAC and then do a switch-over and immediately after that,

ORA-04030: Out of Process Memory

This error comes because of shortage of memory available to be provided to a new session’s server process which wants to connect to the database.

Windows 32-bit
This error is more common on

ORA-4031: unable to allocate "#XXXX#" bytes of shared memory

Most commonly this error comes because of smaller SHARED POOL - which is also mentioned with the error message as follows

ERROR at line 1:
ORA-04031: unable to allocate 4018 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","kglsim heap")

ORA-00376: "file#" cannot be read at this time.

I have experienced this error message (message contains file# of the file which has problems) several times, but only on Windows platform (But this may come on any platform). After this error, all data in the mentioned datafile is no longer available and status of this datafile become "Recover" if we run query:

ORA-12154: TNS:could not resolve the connect identifier specified in RAC (with Server Side Load Balancing)

"ORA-12154: TNS:could not resolve the connect identifier specified" is a very generic and common error message; faced while connecting to an Oracle database. If you "google" this error, you would find a lot of different reasons and solutions for this error message;

ORA-03113: end-of-file on communication channel

There could be several reasons for this error message returned to a session - for example, session is killed; network disconnection occurred, database is shutdown etc. Sometimes none of this happens but still end users complain about having disconnected from the database

Tablespace Reorganization

Tablespace Reorganization

Tablespace reorganization is a process of reorganizing the contents of a tablespace to reclaim free space from the datafiles of a tablespace. When a datafiles is resized (shrink), the size of a datafiles can only be reduced till the high water mark (last allocated extent to

ORA-01031 insufficient privileges - While logging in AS SYSDBA

OS Authentication is a setup whereby we can log into the oracle database as SYSDBA or SYSOPER without supplying any password because OS user (currently logged in) is already authenticated to log into the database as a privileged user. This is handy specially if you have forgotten the SYS password (another way to recover SYS password is

Oracle IO Consumption - Throughput (IOPS) and Transfer Rate (MBPS)

Oracle Database IO Requirements (Throughput/IOPS and Transfer Rate/MBPS)

If we plan to migration an Oracle database to a new/different hardware, planning for storage is also a very important thing. It is good to find out the IO requirements of the database so that a better decision could be made while purchasing the storage. Storage speed is

Online Partition Move

As you know that Oracle partition is a database object used to partition the table’s data into multiple segments which might be stored in different tablespace for the manageability purposes. There are several partition operations that can be performed on the partitions which are called partition maintenance operations and one of them is the “partition move” which means: to move partition from one

Online Datafile Move or Rename

Here I am going to discuss one of my favourite new feature of Oracle 12c which has taken the down time away from the DBAs while moving or renaming a datafile online. There is no requirement of any downtime during this operation

Oracle Segment Adviser Recommendations and Redo/Undo Generation During Segment Shrink

Since Oracle 10g, Oracle provided facility to reclaim the unused space from Tables and Indexes by using"ALTER TABLE <TABLE_NAME> SHRINK SPACE | COMPACT"  command.
Before we go through details of Oracle Segment Adviser Recommendations, it should be noted that during shrink space operation, a lot of undo and redo data is

Implementing Class of Secure Transport (COST)

To safeguard against security vulnerability (CVE-2012-1675) as published here, COST (Class of Secure Transport) parameter is used to restrict any remote instance to get registered with the listener running on a database server. This is done by adding a parameter SECURE_REGISTER_<LISTENER_NAME> = (TCP) in the listener.ora file

RMAN Backup Performance

Finishing an RMAN backup on time (fast) is usually what every DBA looks for. Here I would be discussing my experience of RMAN backup scenarios which I tested for speed and performance. Environment details are as follows.

Converting a Failed Over Standby Database Back to Standby Database

I have seen some articles on the internet for reinstating a primary database after a failover (using flashback database technology – if FLASHBACK RETENTION time has not exceeded), but I was not able to find a document to revert or reinstate a “failed-over” standby database (which has now become primary) back to a standby database (using FLASHBACK technology).

ORA-01624 log needed for crash recovery of instance

It is quite common to see the error ORA-01624 while dropping a redo log group. The full error text is similar to “log <log_group#> needed for crash recovery of instance <instance_name (thread #)”.

Reason
As we know that Log Writer (LGWR) has the responsibility to write

Tablespace Growth History and Forecast for 10g and 11g

Finding space usage of tablespaces and database is what many DBAs want to find. In this article I will explain how to find out space usage history and forecasting future growth of tablespaces. For 10g and 11g database growth history and forecast see script Database Growth History and Forecast for 10g and 11g.
For 12c and above; Tablespace and Database growth history and forecast, please use following scripts
Tablespace Growth History and Forecast for 12c and Above.
Database Growth History and Forecast for 12c and Above.

Error "[NS-20802] Grid Infrastructure Configuration Failed" During 11.2 RAC installation on Windows 2008R2

I faced error "[NS-20802] Grid Infrastructure Configuration Failed" during a 2 node Oracle 11.2 RAC installation on Windows 2008 R2 (64 bit). All prerequisites were checked and cluvfy also did not show any problem, but during installation,

Using Oracle Orion for IO Calibration

ORION is a tool provided by Oracle to calibrate the IO of storage you are planning to use for database server. Some salient features of this tool are as follows

·         Oracle provided tool which can be downloaded separately