Bind Variable Peeking Prior to 11g

Oracle has a feature called “bind variable peeking” whereby an execution plan for an SQL is generated based on the bind variable values used in the SQL. This technique sometimes created suboptimal execution plan because the plan might be perfect for the SQL containing bind variables based on which this plan was generated, but if same SQL is executed later with different values in bind variables, this current plan may not be optimal for those values, and might cause performance degradation for the SQL. I have discusses a real scenario in this article.

Restricting Access/Connections to the Database

In order to restrict the connections to the database we can add some parameters in the sqlnet.ora file under ORACLE_HOME form where listener is running. This would make sure that sessions to the database are coming only from specific list of hosts, and any session from all other hosts is not allowed to connect to the database. Following are the parameters we can use for such restriction.

Archivelog Deletion Policy for RMAN

RMAN archive deletion policy is used to set when we want archived logs to be deleted when DELETE ARCHIVELOG command is executed from the RMAN prompt, or if we are using Fast Recovery Area for archived logs, and are not deleting archived logs manually and relying on automatic feature of Oracle for deletion of archived logs (archive deletion would happen automatically if space crunch is observed by Oracle under fast recovery area).

ORA-12546: TNS:permission denied

I recently faced this error message while trying to connect with the database (running on my local Windows host) using a TNS service. Although connecting without TNS service was working fine, however, connection was failing with ORA-12546 when I tried to connect using TNS service. Following is what I faced.

ORA-01511 and ORA-01523

While renaming a datafile or redo log file you might face these both errors together and command would fail as shown below.

SQL> ALTER DATABASE RENAME FILE ‘d:\oracle\oradata\db\users01.dbf’ to ‘e:\oracle\oradata\users01.dbf’; ALTER DATABASE RENAME FILE ‘d:\oracle\oradata\db\users01.dbf’ to ‘e:\oracle\oradata\users01.dbf’; * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01523: cannot rename datafile to d:\oracle\oradata\db\users01.dbf’ – File already part of database

ORA-01000: maximum open cursors exceeded

Cursor is a pointer to a memory area which is opened to execute an SQL statement issued by the end user/application. It means that every SQL (SELECT/DML) you want to execute would require utilizing this memory area to execute the SQL.

ORA-01186 ORA-01157 ORA-01111 ORA-01110

Managed Standby Recovery starting Real Time Apply
Thu Sep 19 04:05:35 2019
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/rgb1p117_dbw0_10657.trc:
ORA-01186: file 166 failed verification tests
ORA-01157: cannot identify/lock data file 166 - see DBWR trace file
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00166'
Thu Sep 19 04:05:35 2019
File 166 not verified due to error ORA-01157
Thu Sep 19 04:05:35 2019

ORA-01438: value larger than specified precision allowed for this column

You may also want to see this article about the ORA-12899 which is returned if a value larger than column’s width is inserted in the column. Similarly, ORA-01438 is returned if value being inserted is larger than what is defined for the NUMBER datatype column. Number datatype columns are defined in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(5,2)”, it would mean that maximum width of the data could be 5 digits, out of which 2 will be decimal part (for example 123.45). Following are some examples that explain this concept further.

ORA-12899: value too large for column...

You may want to learn about a similar error message ORA-01438 caused by larger values being inserted in NUMBER columns. 
Message displayed with error ORA-12899 is self-explained. This error would come if you are trying to insert into a table’s column which is not big enough to hold the data being inserted. Suppose you define a column with a width of 10 characters (VARCHAR2(10)), and later if you try to insert a value longer than 10 characters, ORA-12899 would be returned. To solve this problem you can either reduce the data width being inserted, or alternatively increase the column width. If you have a multi-byte database characterset,  a VARCHAR2 column with a width of 10 may not store exactly 10 characters. Following is one example to produce ORA-12899.

DBUA Does not Find parameter file to Start Database for Upgrade

While upgrading my database form using database upgrade assistant (DBUA), it showed me error that said following

The Upgrade Assistant failed in bringing up the database <db_name>. Oracle Home <ORACLE_HOME> obtained from file /etc/oratab was used to connect to the database. Either database is not running from Oracle Home <ORACLE_HOME> or correct initialization Parameter file (pfile) was not found. To start the database, Oracle needs the pfile. Provide the full path name of the init file for the database <db_name>

Screenshot of DBUA can be seen bellow

PING[ARC1]: Heartbeat failed to connect to standby 'my_standby'. Error is 12154

If your primary database is not able to ship archived logs to the standby database with error ORA-12154, there is something wrong with the TNS entry you are using in LOG_ARCHIVE_DEST_n parameter that points to your standby database. If you check the alert log file of the primary database you will see error messages similar to the following.

kkjcre1p: unable to spawn jobq slave process, slot 2, error 1089

If you have initiated SHUTDOWN command to stop the database, new sessions cannot connect with the database. This means that even oracle scheduled jobs also cannot be initiated internally. After initiation of SHUTDOWN, if any scheduled jobs get initiated (because scheduled time of any job has reached), job would not be started and an error message will be logged in the alert log file. Following is how alert log would look like after that. Since this is just an information message, therefore  no action is needed.

ORA-39700: database must be opened with UPGRADE option

This message would appear if you have opened a database from a newer version ORACLE_HOME instead of the current version of the database. Alert logs shows messages similar to the following when you open your database with “startup open” command. Instance would immediately crash after logging these messages in the alert log file.

Poor Database Performance After Setting Huge Pages

I recently faced a problem whereby full memory of system got occupied after the database startup and eventually system was hung and reboot was inevitable. The recent change was to setting huge pages on the host. Eventually we found the reason that “soft memlock” was not set (oracle software owner name was wrongly spelled when specifying memlock in /etc/security/limits.conf file). Bu default “soft memlock” was set to a default value 64KB and none of huge pages was in use by the Oracle (SGA). As a result, SGA was allocated from the remaining memory (Huge pages would remain unused, yet allocated) and huge pages were merely consuming memory without any use. Following message in alert log let us understand the problem

Setting Huge Pages in Linux

If your Linux based database server has huge amount of physical memory, it will be a good idea to enable huge pages so that memory could be used efficiently by Oracle instance. Setting huge pages means that you have bigger sized (2 MB) memory blocks in memory to allocate to Oracle SGA. Bigger memory block size would mean fewer number of total memory blocks, and this is where managing memory becomes efficient by the OS. I would recommend to use/enable huge pages if host’s physical memory size goes beyond 128G. But you can also set huge pages even for a small amount of memory.

Migrating from Filesystem to ASM

While migrating from file system to ASM, we create ASM instance on the current host before we could stamp the disks and create asm diskgroups to migrate existing datafiles from the file system to the ASM. Before we create/start ASM instance, we need to start “Oracle Cluster Synchronization Service” (CSS). So, we initiate “dbca” to create/start ASM instance as a first step. If you are using Windows, invoke dbca application using right click and “Run as Administrator”.

Why Opatch Auto is not applying PSU on Database Home?

To apply a PSU we can download the required PSU and apply using “opatch apply” command of OPATCH utility. In RAC environment or Oracle restart environment (single node Grid Infrastructure installation), we would need to download “GI PSU” to patch the Grid Infrastructure home. We have option to download and apply “GI PSU” patch to both GI and RDBMS homes as well because GI PSU contains patch for both homes. In this case we use “opatch auto” (for 11g) or “opatchauto” (for 12c and above) command to be run as root user so that path can be applied on both homes. 

ORA-00245: control file backup failed; target is likely on a local file system

Your RMAN backup command may fail with ORA-00245 while backing up current control file as part of database backup. RMAN output along with the error message may look similar to the following. The highlighted in red is the actual cause of this error which I will explain later in this article and provide the solution for this.

ORA-17503 ORA-27140 ORA-27300 ORA-27301 ORA-27302

ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/mydb/controlfile/current.257.918811113'
ORA-17503: ksfdopn:2 Failed to open file +DATA/mydb/controlfile/current.257.918811113
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1000 (oinstall), current egid = 1031 (dba)
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/mydb/controlfile/current.256.918811111'
ORA-17503: ksfdopn:2 Failed to open file +DATA/mydb/controlfile/current.256.918811111
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1000 (oinstall), current egid = 1031 (dba)

Managing ASM Power Limit During Disk Re-balance

While adding or removing disks to/from a diskgroup, ASM does a re-balance of allocation units (ASM extents); we can specify a power limit of this re-balance. Power limit is used to handle the workload of re-balancing in a way that it does not interfere the normal operation of our database and does not slow down IO operations. Following are some important points to note about rebalance power limit.

ORA-01503: CREATE CONTROLFILE failed with ORA-01192

There could be several reasons for facing ORA-01503 while creating a new controlfile. When we plan to create a controlfile, we can write our own script, but creating a script using “ALTER DATABASE BACKUP CONTROLFILE TO TRACE” command generates a perfect script for us and we do not need to write script ourselves. In the following I will discuss a scenario where I ORA-01503 and how I solved it.

ORA-01126: database must be mounted in this instance and not open in any instance

There are certain administration tasks that need database to be mounted, not open, while performing the task, otherwise ORA-01126 would be returned. In the following I am explaining one of those operations that returned me ORA-01126 because I had my database open while converting my database to archivelog mode.

RMAN-03009 and ORA-19510: failed to set size of 92079 blocks for file

If you RMAN backup commands are failing with RMAN-03009 with ORA-19502 and ORA-19510, it is because your destination file system has no space left on it for the backup. You need to make sure that file system where you are storing backup should have sufficient space to host the backup. Output of command may be similar to the following

ORA-39087: directory name directory_name is invalid

This error may be returned if you are trying to export or import using oracle data pump, but invalid directory name has been provided during this process. Following is an example of this.

$expdp directory=mydir dumpfile=mydump.dmp logfile=mylog.log schemas=c##salman Export: Release 12.1.0.2.0 - Production on Tue Jun 6 12:05:46 2017

ORA-15075: disk(s) are not visible cluster-wide

While adding a disk to a diskgroup in a RAC, you might face ORA-15075. Following is an example of this error.

SQL> alter diskgroup DATA add disk ‘ORCL:DATA4’, ‘ORCL:DATA5’; alter diskgroup DATA add disk ‘ORCL:DATA4’, ‘ORCL:DATA5’ * ERROR at line 1: ORA-15032: not all alterations performed ORA-15075: disk(s) are not visible cluster-wide

Deinstalling or Removing Oracle Software

We need to use “deinstall” utility to remove Oracle software from a system. In the following I will show how to deinstall Oracle grid infrastructure software. If you have an Oracle RDBMS home dependent on the GI home, then you should deinstall RDBMS software before deinstaling GI software. Process of deinstalling RDBMS home is similar to the deinstallation of grid infrastructure software.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Temporary tablespace is needed when Oracle needs to perform sorting, and doing so is not possible in the memory, therefore it utilizes temporary tablespace. ORA-01652 means that oracle needs to allocate space from temp tablespace and it is not able to do so because temp tablespace is full. Alert log file would log error as follows.

RMAN-08138: WARNING: archived log not deleted

Usually we do not need a backup of archived logs on standby site if backup is already configured on the primary database. In such scenario, if we try to delete old archived logs which have already been applied on the standby database my return “RMAN-08138: WARNING: archived log not deleted - must create more backups” if archive deletion policy is not properly set in the standby database.

ORA-01654 and ORA-03234 for index tablespace

When tables grow in size because of new data coming in, indexes also grow, and sometime indexes can grow even bigger as compare to table itself. There is a possibility that you see ORA-01654 while inserting/updating data because tablespace holding the index cannot be further extended and needs manual intervention to extend the tablespace so that index could further grow. Error would be returned to the application as well as reported in the alert log file.