Running a Command or Script in the Background on Linux

Many times we (DBAs) are in a situation when we need to run some command or script from the command prompt on the Linux, and during command/script execution; we face disconnection of session and our process stops in the middle leaving a lot of mess for us to clear. To avoid this kind of situation, it is advised to execute long running scripts or commands in the background using Linux

Using scp command on Linux to copy Files and Directories

DBAs need to move files from one host to the other in Linux environment. In this article, I would explain how to use scp (secure copy) command to copy files or directories from one host to the other.

Currently I am on 192.168.20.20 (MYDBSERVER is the hostname in this case) and I have a file /u01/oracle.tar on second host 192.168.20.30 that I want to transfer to /u01 on my current host,

ORA-19909: datafile 1 belongs to an orphan incarnation

Your MRP (Managed Recovery Process) may stop on your standby database with ORA-19909 error as can be seen in bellow excerpt from alert log file

Warning: Recovery target destination is in a sibling branch of the controlfile checkpoint. Recovery will only recover changes to datafiles.

ORA-17627 and ORA-12154 Returned by RMAN Duplicate Command

During RMAN DUPLICATE command, you might face this error message returned by RMAN.

C:\Users\administrator>rman target sys/pass11g@pri auxiliary sys/pass11g@aux Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 9 12:35:18 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ORA-12154 and TNS-03505

You may receive error “ORA-12154: TNS: could not resolve the connect identifier specified” while trying to connect with the database, and also “TNS-03505: Failed to resolve name” while using TNSPING to test the connection. Reasons for both of these error is that you have not created TNS service correctly

Creating ASM Diskgroups

In this article I would explain how to create new ASM diskgroups. Process is same for Unix based platforms or Windows systems, and also for other Oracle versions.
Before we move forward, you may have a look at my articles related to stamping ASM disks on windows and creating ASM disks using ASMLib on Linux to know how we create disks which are used for ASM diskgroups.

ORA-00354: corrupt redo log block header

If you see ORA-00354 in your alert log file, probably along with "ORA-00353: log corruption near block" as can be seen bellow

Tue Nov 24 14:36:25 2015 Thread 1 cannot allocate new log, sequence 16492 Private strand flush not complete   Current log# 9 seq# 16491 mem# 0: D:\ORACLE\ORADATA\MYDB\REDO9_01.RDO Thread 1 advanced to log sequence 16492

RMAN-00600: internal error, arguments [9222] [] [] [] []

I faced following error message while creating a standby database from live database using RMAN DUPLICATE command.

C:\Users\administrator>rman target sys/pass11g@pri auxiliary sys/pass11g@aux Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 9 12:35:18 2015

RMAN-05001 auxiliary filename string conflicts with a file used by the target database

If this error message is being returned during RMANDUPLICATE DATABASE command, it is because RMAN would try to created destination files at the same location where source database files exist; and since it cannot overwrite existing files, so it and returns error message. To avoid this error, NOFILENAME check option should be used with DUPLICATE command. Otherwise use one of the following to specify different file names

Heartbeat failed to connect to standby

If Primary database alert log is showing error message similar to the following,

PING[ARC1]: Heartbeat failed to connect to standby 'standby-db'. Error is 16058.

it means that primary database is not able to ship redo log data to

Terminating the instance due to error 481

If you are trying to start a RAC instance and it is terminating immediately after STARTUP command, and alert log is showing following messages

MMNL started with pid=26, OS id=7068 NOTE: initiating MARK startup Starting background process MARK

Using DBMS_STATS to Gather Statistics

Collection of statistics on the database objects is very important for the performance of the database. DBMS_STATS package is used to collect these statistics.
To collect system statistics, use following procedure

SQL> exec dbms_stats.gather_system_stats

ORA-20005: object statistics are locked

Error “ORA-20005: object statistics are locked” means that table statistics are locked and hence stats can’t be gathered on this table. Stats are locked if we don’t want stats to be gathered on some or all of our tables.

Following example explains stats locking and unlocking process.

ORA-15410: Disks in disk group DISKGROUP_NAME do not have equal size

If you are trying to create a diskgroup (suppose diskgroup name is DATA) and you see errors as bellow

ORA-15018: diskgroup cannot be created ORA-15410: Disks in disk group DATA do not have equal size

following would be the reason of it.

Error: OSWatcher uses the OS utility Logman to gather its data

Starting Operating System Watcher for Windows (OSWatcher) may return following error message.

c:\oswfw_2_5.1\oswfw_2_5.1>oswatcher 20 120 Error: OSWatcher uses the OS utility Logman to gather its data.

TNS-12531: TNS:cannot allocate memory

I found this message in my listener.ora file while investigating a ORA-00600 error which was causing the database to slow down very much.

24-FEB-2015 11:55:00 * 12531 TNS-12531: TNS:cannot allocate memory

TNS-12560: TNS:protocol adapter error

This is one of the most common and generic errors you may face while trying to connect with the database. There could be a lot of reasons for this. To troubleshoot this, you may want to go through following checklist

Check your TNS entry in TNSNAMES.ORA file, if it is set correctly and there is no syntax error and it is pointing to correct server and instance

ORA-24247: network access denied by access control list (ACL)

This error message means that no access control list has been assigned to the host you (your application) are trying to access, or no required privileges have been granted to the user by adding user to the ACL. Following is an example executed on 12c (12.1.0.2) to show how we create ACL and how to test it.  

Using PGA_AGGREGATE_LIMIT to Limit PGA Memory

Before 12c, there was no hard limit to restrict PGA memory usage by an Oracle session. It means that if a session keeps growing in memory, it would eventually allocate all available memory which would lead to a system slowness/hang and eventually system crash, I have discussed this scenario here.

Generating Bulk of Data for Testing Purpose

Sometimes we need to generate a huge amount of data to perform some testing. Type of data to be generated depends on the scenario you want to test. I will show here a very simple example to create a very huge table. I usually use it if I need to have a huge tablespace or table to perform some test. For example, to test backup

Changing AWR Snapshot Retention and Interval

By default AWR snapshot interval is set to 60 minutes and retention of snapshots is set to 8 days. For better and precise investigation of problems, I recommend to use an interval of 15 minutes and retention of 35 days. If database is huge in size and operations, also RAC, your SYSAUX tablspace would certainly grow in size with these settings. I have observed size of SYSAUX tablespace for a 3 nodes RAC database, around 14G with aforementioned settings.

Oracle Deferred Segment Creation

Starting 11g Release 2, we can create a table/index/LOB without space allocated to it (no segment creation) until first row is inserted into the table. Init parameter DEFERRED_SEGMENT_CREATION is used to setup the default behavior. By default value of this parameter is set to TRUE, which means that whenever a table will be created,

Checkpoint not Complete

This is one of the most common messages a DBA could see in alert log file of a database. Exact message should be similar to the following

Wed Mar 04 12:02:01 2015 Thread 1 cannot allocate new log, sequence 201 Checkpoint not complete

Finding Sessions with High CPU Usage

If database server CPU usage is showing 100%, or high 90%, DBA needs to find out which session is hogging the CPU(s) and take appropriate action. I will explain in the following that how can we find out the session(s) which are excessively using CPU.

ORA-03135: connection lost contact

This error may be accompanied by ORA-03113: end-of-file on communication channel. This actually means that your session has been dropped and there could be different reasons which might have caused this. Most commonly; a firewall between client and server could have caused this session/connection drop, if firewall senses some session to

Testing RMAN Restore

Starting 10g, we can test our backups whether backups are valid and can be used for a successful restore/recovery of the database or not. We can do so by using “RESTORE VALIDATE” command from RMAN. RESTORE VALIDATE command would simulate a restore of the backups by reading backup pieces. If it completes successfully, it would mean that backup is good and can be used for restoration and recovery of database in case of media failure or a disaster.

Warning: PDB altered with errors and PDB_PLUG_IN_VIOLATIONS

If you open your pluggable database and you receive following warning,

SQL> alter pluggable database pdb2 open; Warning: PDB altered with errors.

It would mean that when sync operation of pluggable database was performed while opening it, there was some error and synchronization was not able to complete successfully. Pluggable database would be open, but it will be in restricted mode (as you will see in bellow example). In this case, we should immediately query PDB_PLUG_IN_VIOLATIONS to see what has gone wrong.

ORA-00214: control file version inconsistent with file

If there is a server crash, there could be a possibility that you see ORA-00214 error; once server is up again, and you try to start (mount) the database. Error could be similar to the following.

ORA-00214: control file 'D:\ORACLE\ORADATA\PRODDB\CONTROL02.CTL'
version
857358007 inconsistent with file 'G:\ORACLE\ORADATA\PRODDB\CONTROL01.CTL'
version 857358000

CDB_TABLESPACES not Showing Tablespaces of Pluggable Database

Starting 12c, along with DBA_*, ALL_* and USER_* views, there is another time of data dictionary views available which are CDB_* views. For example, along with DBA_TABLESPACES view there is also CDB_TABLESPACES view. These CDB_* views in a container database show all information of all containers (container + all pluggable databases)

Tablespace Growth History and Forecast for 12c and Above

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, 11g, you can use Tablespace Growth History and Forecast (10g, 11g) and Database Growth History and Forecast (10g, 11g) scripts.

For 12c container/pluggable Database Growth History and Forecast, see script Database Growth History and Forecast (12c and Above). 

Database Growth History and Forecast for 12c and Above

For 10g, 11g, you can use Tablespace Growth History and Forecast for 10g and 11g and Database Growth History and Forecast for 10g and 11g) scripts.
For 12c and above tablespace growth history and forecast, you can use Tablespace Growth History and Forecast for 12c and Above script.

RMAN Duplicate Database using Live/Active Database

RMAN duplicate command is a very useful automated way to create a copy/clone of a database rather than manually creating a clone of a database. Duplicate database can be created on the same server were source database is running or on a different server. Duplicate database can be created using RMAN backup of source database,

Heartbeat failed to connect to standby. Error is 16009

On one of my 11.2.0.4 dataguard setup on Windows 64 bit, following errors were being reported every minute, in alert log file of standby database.

PING[ARC2]: Heartbeat failed to connect to standby 'proddb'. Error is 16009. Thu Jun 25 07:01:10 2015

ORA-29701: unable to connect to Cluster Synchronization Service

On 11.2.0.3 single server grid infrastructure on Windows 2008, I faced ORA-29701 error while starting ASM instance.

C:\>sqlplus SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 17 12:44:08 2015

Killing a process using orakill.exe on Windows

If you kill an oracle session using ALTER SYSTEM KILL SESSION command, status of this session in v$session view is set to “KILLED”, and after some time you no longer see this session. Sometimes it may happen that status of this session remains “KILLED” for a very long time and resources taken by this session are still not released. I have observed this especially in

DDL Logging in Oracle

Starting Oracle 12c, DDL (Data Definition Language) command are logged in a log file which is located under <diag_location>/rdbms/<db_unique_name>/<sid>/log. In my case, it is located under c:\app\oracle\diag\rdbms\db12c\db12c\log directory, and log file name is ddl_<db_name>.log. XML version of this DDL log file is stored under

Finding Cache Hit Ratios History

Many DBAs consider values of cache hit ratios as mythical, but I personally don’t think so as these have guided me to the right direction many times. But this is really true that by looking at cache hit ratios solely and making conclusions might really lead you to the wrong direction. These values can only be seen and analysed in some context.

ORA-15001: diskgroup does not exist or is not mounted

If you are having role separation for Grid Infrastructure (RAC or standalone) and Database software, you would need to have an OS user (normally named “grid”) for Grid Infrastructure installation and management, and an OS user (normally names “oracle”) for database software installation and management.

Universal Unique Identifier (UUID) error during 12c RAC Installation

During installation of 12.1.0.1 (12c) grid infrastructure (RAC or standalone), prerequisite check screen may show a warning for “Device Checks for ASM”, stating:
Cannot verify the shared state for device /dev/oracleasm/disk/<ASM Disk> due to Universally Unique Identifiers (UUIDs) not being found, o

Performing Block Media Recovery

While running SQL statements on the database, suddenly SQLs may start failing with error messages showing that there is database block corruption in some datafile(s). Error messages in the Alert Log could be similar to the following.

... ORA-01578: ORACLE data block corrupted (file # 4, block # 36321)

Installing Oracle 12c Release 1 RAC on Linux 6

This article explains how to install a 2 nodes Oracle 12c release 1 Real Application Cluster (RAC) on Oracle Linux 6. I did this installation on Oracle Virtual Box by creating 2 virtual machines with shared storage. OS platform I used is Oracle Enterprise Linux 6 (update 3), and Oracle GI and RDBMS version is 12.1.0.2. Same installation guide should work for Redhat Linux 6 also

Creating Virtual Machine using Oracle Virtual Box

DBAs can use Oracle Virtual Box to create virtual machines for testing purposes. Even Oracle RAC installation practice can be done using virtual machines by creating virtual shared storage for the virtual RAC nodes.

Following are the steps with screen shots for how to create a virtual machine using Oracle

Installing Oracle Linux 6 for DBAs

Being a DBA, it is good to be independent of system admin as much as you can because many times you could be in a situation where you need to install and/or manage the OS yourself and also need to suggest some OS tuning or parameter setting to run Oracle successfully on your system. Here I will be explaining how to install Oracle Linux 6 on a server

ORA-01653: unable to extend table by 8192 in tablespace

ORA-01653: unable to extend table <SCHEMA_NAME>.<SEGMENT_NAME> by 8192 in tablespace <TABLESPACE_NAME>

This error is quite common and it clearly means oracle was not able to allocate space to a segment and returned this error. Alert log file will show error similar to the following.

Wed May 31 17:15:28 2013 ORA-1653: unable to extend table SCOTT.TEST by 8192 in tablespace DATA_TBS

Creating Shared Disks for Oracle RAC on Oracle Virtual Box

If you want to do a test Oracle RAC (12c R1, 12c R2) installation on virtual machines you would need to create shared disks to be used across all the RAC nodes to store OCR/voteDisk and other ASM disks for database storage.
In the following, I will explain how we can created shared disks in Oracle Virtual Box.

Installing Oracle 12c Release 1 RAC on Linux 7

This article explains how to install a 2 nodes Oracle 12cR1 Real Application Cluster (RAC) on Oracle Linux 7. I did this installation on Oracle Virtual Box by creating 2 virtual machines with shared storage. OS platform I used is Oracle Enterprise Linux 7 (update 1), and Oracle GI and RDBMS version is 12.1.0.2. Same installation guide should work for Redhat Linux 7 also.

Configuring SSH for Oracle RAC installation

User equivalence is required on all nodes during Oracle RAC installation which provides OUI to log into all remote notes to perform copy/install the software and perform other configuration tasks. We can use different approaches to setup the passwordless ssh connectivity.

Using Cluster Verification Utility (cluvfy)

While installing RAC, cluster verification utility (cluvfy) is used to check whether all required prerequisites have been met, before proceeding to the Grid Infrastructure for RAC installation. 

We also use it after the installation to check if everything is working fine after the installation.

ORA-01157: cannot identify/lock data file

While starting up a database, you may face following error messages which would not allow you to open the database.

ORA-01157: cannot identify/lock data file <file#> - see DBWR trace file
ORA-01110: data file <file#>: <file_name>

Manual upgrading Oracle database 11g to 12c

If you want to know how we upgrade an 11g database to 12c using DBUA, click here. 

For upgrading 12.1.0.1 to 12.1.0.2 using DBUA, click here.

This article explains how to upgrade 11.2.0.2 and above, and 12.1.0.1 to 12.1.0.2 manually.

For details of each step, take a look at official 12c

ORACLE.EXE (SHAD) Processes Keep Increasing

On Windows based Oracle installations, if you hit an issue whereby you see that processes/sessions keep increasing unabated, and you keep receiving “ORA-00020: maximum number of processes exceeded” error while connecting to the database and increasing the value of parameter “processes” remains insufficient always,

ORA-00020: maximum number of processes exceeded

You may face this error message while connecting to the database instance which will also hinder you from connecting with the database instance. The reason of this error is the value of init parameter “processes” which requires to be increased to allow Oracle instance to span a new process for new session request. For example, if value of this parameter is set to 100,

The OracleService service terminated unexpectedly

On Windows platform, the OS level service which has a name like “OracleService<SID>”, needs to be running for the database to work. If this service stops, database also stops. You may be in a situation where you receive complaints about database sudden crash, which requires DBA intervention to start this service and database.

ORA-06553: PLS-801: internal error [56319]

You will face this error while connecting to a database which has word size (32-bit or 64-bit) different than the actual installation binaries (32-bit or 64-bit). It means that if database was created (and running) on a 64-bit platform and then you move/restore same database to a 32-bit platform, or vice versa, you will be facing this problem.

OEM Database Control Setup fails with ACCESS DENIED error

On Windows platform, if OEM Database Control configuration fails with “SEVERE: Error instantiating EM configuration files” message, this could be because of permission issue of the OS user doing the OEM configuration. See bellow

C:\Users>emca -config dbcontrol db -repos create

Table Point in Time Recovery using RMAN

Prior to 12c, in case a table or partition is dropped accidentally, there are certain ways to perform the table/partition recovery which includes Flashback database, database point in time recovery and tablespace point in time recovery. 

Starting 12c we can perform point-in-time recovery for a single table

Oracle Snapshot Standby Database

Starting 11g, we can convert a physical standby database into a snapshot standby database. It actually means that we can open a standby database in read-write mode for transactions processing and then later we can convert this database back to standby database. This is accomplished by enabling the database flashback on standby database;

Basic Linux Commands for DBAs

DBAs need some basic commands on Linux to better administer the database and server. Here I will be discussing a few of these commands. 

Checking Uptime
`uptime` command shows; since when server is up and running. It also shows current load average (1 minute, 5 minute and 15 minute)

Installing Oracle Linux 7 for DBAs

Being a DBA, it is good to be independent of system admin as much as you can because many times you could be in a situation where you need to install and/or manage the OS yourself and also need to suggest some OS tuning or parameter setting to run Oracle successfully on your system. Here I will be explaining how to install Oracle Linux 7 on a server.

ORA-01555 Snapshot Too Old

ORA-01555 is one of the most common error messages faced by the DBAs when they receive complains of failing SQLs with this error message. Alert log may contain a message as follows

############################
ORA-01555 caused by SQL statement below (SQL ID: 1f345hftyx6hq,

ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired

While altering or dropping CONSTRAINTS, COLUMNS or INDEXES, you may face error ORA-00054 message and the reason of this error message is current ongoing transaction(s) on the table.

Following are the examples

SQL>  alter table test_tab set unused (name);

ORA-65026 XML metadata file error : LPX-00202: could not open

In the process of converting a non-CDB database to a PDB database, once you describe a non-CDB database into and xml file and then try creating a PDB using this xml file, your CREATE PLUGGABLE DATABASE command may fail with this error message.

OEM 12c Job Failing With "ERROR: Failed to create command process"

I received OEM alert that one of my OEM 12c Cloud Control jobs is failing with message Error: Failed to create command process. Job activity page was showing details as follows

ORA-01031 insufficient privileges during switchover using DGMGRL

If you have Dataguard broker configured in your Dataguard environment and you are doing a switchover using DGMGRL, you may see ORA-013031 as you can see in the following.

[oracle@SGPvGMSIMDG01 ~]$ dgmgrl sys /

ORA-01031 While Creating or Accessing a View

ORA-01031: insufficient privileges

If your CREATE VIEW, CREATE MATERIALIZED VIEW or SELECT statement on a view is failing with this error message, it means that you are using a table in your view which is in another schema and there are some privilege issues. Reason for this error could

ORA-00942 table or view does not exist

ORA-00942:

table or view does not exist

This is one of the most common errors which end users faces, returned by the Oracle RDBMS, and the meanings of this error message is that table, view, materialized view or cluster you are trying to access (SELECT, INSERT. UPDATE or DELETE).

Error 1031 received logging on to the standby

In a Dataguard environment, if you see messages similar to the following in the alert log file of your Primary database, there could be multiple reasons for these errors.

#########################################
Error 1031 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'mydb_standby'. Error is 1031.

ASM Disks Stamping on Windows

When we start the installer for the Grid Infrastructure installation, and select either “Install and configure Oracle Grid Infrastructure for a Cluster” or “Install and configure Oracle Grid Infrastructure for a Standalone Server” option on the first screen

ORA-65139: Mismatch between XML metadata file and data file

While converting a non-CDB database to a PDB database, sometimes I was able to do this, but sometimes I was facing the following error.

################################

SQL> create  pluggable database pdb2 using '/u01/oracle/noncdb.xml';

Autostart of Pluggable Databases

In Oracle 12.1.0.1, you would need to start all pluggable database manually after the startup of the Container Database. To automate the startup of pluggable databases, you have 2 options. First option is to create a system trigger (having pluggable database “open”commands for the pluggable databases) which fires at container database  startup and opens the pluggable database(s). Trigger code would be something like this.

Creating ASM Disks using ASMLib

While installing oracle 11.2.0.4 grid infrastructure (same is true for 12c) on Oracle Linux 6 (x86_64), “Create ASM Disk Group” screen of installation wizard prompted to select the disks for the creation of a diskgroup, as you see in bellow screenshot. This installation screen would appear if you select “Install and Configure Grid Infrastructure for a Cluster

Configuring RMAN Backup

Here I will explain how to configure very basic and very simple RMAN backup job to take daily backup (incremental level 0), which can be used by anyone at any OS platform and for any version of Oracle Database - difference will only be the file system paths which will be different in Windows and Unix based platforms.

ORA-03264 cannot drop offline datafile of locally managed tablespace

On one of my customer’s site, I was told that they want to drop a datafile which is offline for past one year. One year ago some novice person tried to add the datafile with a name which was not correct according to him and while trying to rename it,

Configuring Oracle Dataguard for RAC

Before we start, first you should check my article Configuring Oracle Dataguard, for a single node primary database and single node standby database; because configuring Dataguard for RAC is not much different than setting up Dataguard for single node database. During this article, I would be referring to the aforementioned document rather than

Configuring Oracle Dataguard

Following is a step by step guide to configure Oracle Dataguard using physical standby database for Oracle database. If you have RAC environment, you can follow this document to configure dataguard. OS platform for this example is Oracle Linux 6 (x86_64), but same steps can be followed to configure dataguard for other platforms. 

Database Growth History and Forecast for 10g and 11g

For 12c container/pluggable Database, you can use Database Growth History and Forecast for 12c and Above, and Tablespace Growth History and Forecast for 12c and Above scripts.

For 10g, 11g tablespace growth history and forecast, you can use Tablespace Growth History and Forecast for 10g and 11g) script. 

Performing Disaster Recovery using RMAN

Here I will be explaining how to perform disaster recovery of a database server where server has been lost because of hardware failure and needs everything to be prepared from scratch. This would require that a same type of server has been provisioned which has matching resources as we had on the actual production server. Before moving forward,

Converting a non-CDB database to a PDB

Here I would explain how do we convert a non-CDB (non-container database) to a PDB (pluggable database) in an existing CDB (container database) on the same host as well as on a different host.  For this example, I used Oracle 12.1.0.2 software version and this example assumes that there is already a container database with name “mycdb”

Upgrading Grid Infrastructure 11g to 12c

In this post I will be demonstrating the steps to upgrade Oracle Grid Infrastructure (GI) 11g (11.2.0.4) to Grid Infrastructure 12c (12.1.0.2). Since ASM runs from GI home, ASM would be upgraded automatically while the upgrade of GI. First release version of 12c was 12.1.0.1 and for this example, I would be using 12.1.0.2. As we know that

PRCA-1057 Failed to retrieve the password file location used by ASM asm

PRCA-1057 : Failed to retrieve the password file location used by ASM asm PRCR-1097 : Resource attribute not found: PWFILE

After upgrading your grid infrastructure 11g to 12c, you will notice that executing command “srvctl config asm” returns abovementioned error messages.

Upgrading Oracle 11g Database to 12c using DBUA

For manual upgrade guide from 11g to 12c or 12.1.0.1 to 12.1.0.2, click here

Here I would be discussing how to upgrade Oracle 11g (11.2.0.4) database to 12c (12.1.0.2) on the same server. This upgrade process was done on Linux 6 (x86_64). I used DBUA (Database Upgrade Assistant) to perform this upgrade. DBUA