Sunday, December 27, 2015

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

Friday, December 25, 2015

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,

Sunday, December 13, 2015

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.

Tuesday, December 8, 2015

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

Wednesday, December 2, 2015

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.

Thursday, November 26, 2015

ORA-00354: corrupt redo log block header

If you see ORA-00354 in your alert log file, 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

Sunday, November 22, 2015

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

Sunday, November 15, 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

Wednesday, November 11, 2015

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

Thursday, November 5, 2015

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

Wednesday, October 28, 2015

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

Monday, October 26, 2015

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.

Tuesday, October 20, 2015

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.

Thursday, September 17, 2015

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.

Monday, September 14, 2015

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

Tuesday, September 1, 2015

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

Tuesday, August 25, 2015

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.  

Monday, August 17, 2015

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.

Monday, August 10, 2015

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

Tuesday, August 4, 2015

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.

Monday, August 3, 2015

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,

Tuesday, July 28, 2015

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

Thursday, July 23, 2015

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.

Monday, July 20, 2015

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

Wednesday, July 15, 2015

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.

Sunday, July 12, 2015

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.

Wednesday, July 8, 2015

ORA-00214: control file 'string' version string inconsistent with file 'string' version string

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'

Monday, July 6, 2015

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)

Tuesday, June 30, 2015

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,

Friday, June 26, 2015

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

Wednesday, June 17, 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

Sunday, June 14, 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

Thursday, June 11, 2015

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

Wednesday, May 20, 2015

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.

Monday, May 18, 2015

ASM Diskgroup Not Accessible by oracle user

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.

Thursday, May 14, 2015

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

Tuesday, May 12, 2015

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)

Thursday, May 7, 2015

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

Sunday, May 3, 2015

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

Wednesday, April 29, 2015

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

Monday, April 27, 2015

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.

Sunday, April 26, 2015

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.

Wednesday, April 15, 2015

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.

Monday, April 13, 2015

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.

Sunday, March 29, 2015

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>

Tuesday, March 24, 2015

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

Wednesday, March 18, 2015

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 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,

Tuesday, March 17, 2015

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. If you open Windows event viewer, it would show a message as follows.

The OracleService<SID> service terminated unexpectedly.  It has done this <n> time(s).  The following corrective action will be taken in <n>milliseconds:
Restart the service.

Most common reason of this error is resource contention. If you face this issue, you should start monitoring the system resources (CPU, RAM, Page file) usage. OSWatcher is a good tool to accomplish this. Once this issue happens, the monitoring tool should show you which resources consumption is the actual cause of this service crash – and you can take corrective actions.

Once I faced this error; and OSWatcher showed me that at the time of service crash, RAM/Pagefile was fully consumed, and the reason for this full RAM consumption was huge memory consumption by a service called “Oracle <SID> VSS Writer Service”. This service is part of Oracle starting 11g, and in 12c, the display name of this service is like “OracleVssWriter<SID>”. For this particular case, this is because of a bug (memory leek by this service) which will be resolved in 12.2. As a workaround, you should disable/stop this service if you are not using this service for Volume Shadow Copy, or stop this service just after you finish your shadow copy snapshot and then start it again when you next time need to create a snapshot.


I also faced this issue at a customer site where resource consumption was OK. Only one issue I found was that Oracle 10.2 was installed on Windows 2008 R2, which is an unsupported platform for Oracle 10.2. So I could just suspect that this unsupported installation might be causing this service crash. I suggested them to do a re-install on Windows 2008 which is supported for Oracle 10.2

Monday, March 16, 2015

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.

You may also face this error if you have a standby database running on a platform which has Oracle binaries different than the binaries of primary database platform, and you perform a switchover. After the switchover, you might be facing same error.

Reason of this error is that PL/SQL objects (functions, procedures, packages) were compiled using a different word size (suppose 32-bit) when database was created/running, and now you restore/move this database to a 64-bit platform and hence these objects code is inconsistent with the binaries.

To solve this problem, you first need to invalidate all PL/SQL objects and then re-compile them so that new compiled code becomes consistent with the available binaries.

1.      Startup database with STARTUP UPGRADE option
2.      Invalidate the PL/SQL objects by running $ORACLE_HOME/rdbms/admin/utlip.sql
3.      Validate the PL/SQL objects by running $ORACLE_HOME/rdbms/utlrp.sql
4.      Shutdown the database
5.      Startup the database

Tuesday, March 10, 2015

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

STARTED EMCA at Mar 11, 2015 11:46:03 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: mydb
Listener ORACLE_HOME [ D:\ORACLE\1120 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\ORACLE\1120

Local hostname ................ MYSERVER
Listener ORACLE_HOME ................ D:\ORACLE\1120
Listener port number ................ 1521
Database SID ................ mydb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error instantiating EM configuration files
Refer to the log file at D:\ORACLE\cfgtoollogs\emca\mydb\emca_2015_03_11_11_46_02.log for more details.
Could not complete the configuration. Refer to the log file at D:\ORACLE\cfgtoollogs\emca\mydb\emca_2015_03_11_11_46_02.log for more details.

C:\Users>


Screenshot of command prompt could be as follows















If we open the log file mentioned above, it would show messages as follows. Highlighted in red is actually a hint that were actual problem is.
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: Exit value of 255
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: Creating shared install...
Source location: D:\ORACLE\1120
Destination (shared install) : D:\ORACLE\1120\PMS03198DBB_mydb
DeployMode : dbconsole

Creating directories...
Setting console properties ...
Setting log and trace files locations for Console ...
Setting log and trace files locations for Agent ...

Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: OpenSCManager FAILED: Access is denied.

Service creation failed. Aborting...

Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing CMD /C D:\ORACLE\1120\bin\emctl.bat deploy dbconsole -d mydb D:\ORACLE\1120\PMS03198DBB_mydb PMS03198DBB:3938 PMS03198DBB mydb
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMAgentConfig instantiateEMConfigFiles
CONFIG: Failed to deploy state dirs
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error instantiating EM configuration files
Refer to the log file at D:\ORACLE\cfgtoollogs\emca\mydb\emca_2015_03_11_11_46_02.log for more details.
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Error instantiating EM configuration files
            at oracle.sysman.emcp.EMAgentConfig.updateAgentConfigFiles(EMAgentConfig.java:3481)
            at oracle.sysman.emcp.EMAgentConfig.performConfiguration(EMAgentConfig.java:1465)
            at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:300)
            at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:278)
            at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:259)
            at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:599)
            at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1482)
            at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:583)
            at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:529)
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig restoreOuiLoc
CONFIG: Restoring oracle.installer.oui_loc to D:\ORACLE\1120\oui

Solution
Actual problem is related to the permission of the user, which is not allowing the configuration command to create the OS level service for OEM. To solve the problem, the command prompt (to execute the OEM configuration command) should be opened with Administrator privileges, as follows.

Tuesday, February 24, 2015

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 or partition; using RMAN. This new feature enables us to avoid any full database or tablespace point-in-time recovery to recover a dropped table. We need less time, space and resources to perform table recovery using this method.

One point to note is that in case of tablespace point in time recovery, it can be done only if tablepsace is self-contained - and in case of a single table/partition point-in-time recovery, obviously this restriction does not apply.

Flashback drop table may not work if recycle bin has been purged.

I will explain a scenario of a table drop and then its recovery in the following

Create a new user and a table in the schema with some data
SQL> create user c##salman identified by salman;

User created.

SQL> grant dba to c##salman;

Grant succeeded.

Enter user-name: c##salman/salman
Last Successful login time: Thu Jan 15 2015 15:49:25 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create table test as select * from dba_objects;

Table created.

SQL> alter table test move tablespace users;

Table altered.

SQL> insert into test select * from test;

91103 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
    182206

SQL>

Take Backup
Assuming database is in archivelog mode. Take a backup of tablespace USERS which has this table in it. Alternatively a full database backup can also be taken.
RMAN> BACKUP FORMAT '/u01/bakcup/back_%u.bak' TABLESPACE USERS;

Drop the Table
Drop the table and note the time. Current SCN can also be noted as we can perform table point in time recovery based on time or SCN. In real time scenario, exact time of table drop may not be known so we would need to be careful in guessing the time.
SQL> drop table test;

Table dropped.

SQL> select sysdate from dual;

SYSDATE
------------------
16-JAN-15 12:25:02

Perform the recovery using RMAN.
RMAN> RECOVER TABLE 'C##SALMAN'.'TEST'  until time  "to_date('16-01-15 12:23:00','DD-MM-YY HH24:MI:SS')" auxiliary destination '/u01/backup';

Starting recover at 16-JAN-15
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='iasB'

initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=iasB_pitr_CDB
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u02/oracle
_system_trig_enabled=FALSE
sga_target=1584M
processes=200
db_create_file_dest=/u01/backup
log_archive_dest_1='location=/u01/backup'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDB

Oracle instance started

Total System Global Area    1660944384 bytes

Fixed Size                     2925072 bytes
Variable Size                419433968 bytes
Database Buffers            1224736768 bytes
Redo Buffers                  13848576 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16-01-15 12:23:00','DD-MM-YY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 16-JAN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DATA/CDB/AUTOBACKUP/2015_01_16/s_869142027.312.869142029
channel ORA_AUX_DISK_1: piece handle=+DATA/CDB/AUTOBACKUP/2015_01_16/s_869142027.312.869142029 tag=TAG20150116T122027
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:12
output file name=/u01/backup/CDB/controlfile/o1_mf_bckg857l_.ctl
Finished restore at 16-JAN-15

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16-01-15 12:23:00','DD-MM-YY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone tempfile  1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/backup/CDB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 16-JAN-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/CDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/db_0vpss33u_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/backup/db_0vpss33u_1_1.bak tag=TAG20150116T121350
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:16
Finished restore at 16-JAN-15

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=869152403 file name=/u01/backup/CDB/datafile/o1_mf_system_bckg8n3n_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=869152403 file name=/u01/backup/CDB/datafile/o1_mf_undotbs1_bckg8n49_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=869152403 file name=/u01/backup/CDB/datafile/o1_mf_sysaux_bckg8n44_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16-01-15 12:23:00','DD-MM-YY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

Starting recover at 16-JAN-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 43 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_43.313.869142101
archived log for thread 1 with sequence 44 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_44.306.869142107
archived log for thread 1 with sequence 45 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_45.314.869142107
archived log for thread 1 with sequence 46 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_46.315.869142107
archived log for thread 1 with sequence 47 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_47.316.869151219
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_43.313.869142101 thread=1 sequence=43
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_44.306.869142107 thread=1 sequence=44
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_45.314.869142107 thread=1 sequence=45
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_46.315.869142107 thread=1 sequence=46
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_47.316.869151219 thread=1 sequence=47
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-JAN-15

sql statement: alter database open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/u01/backup/CDB/controlfile/o1_mf_bckg857l_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1660944384 bytes

Fixed Size                     2925072 bytes
Variable Size                419433968 bytes
Database Buffers            1224736768 bytes
Redo Buffers                  13848576 bytes

sql statement: alter system set  control_files =   ''/u01/backup/CDB/controlfile/o1_mf_bckg857l_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1660944384 bytes

Fixed Size                     2925072 bytes
Variable Size                419433968 bytes
Database Buffers            1224736768 bytes
Redo Buffers                  13848576 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16-01-15 12:23:00','DD-MM-YY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  6;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 16-JAN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=178 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/IASB_PITR_CDB/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/db_0vpss33u_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/backup/db_0vpss33u_1_1.bak tag=TAG20150116T121350
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 16-JAN-15

datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=869152483 file name=/u01/backup/IASB_PITR_CDB/datafile/o1_mf_users_bckglvqp_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16-01-15 12:23:00','DD-MM-YY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  6 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

Starting recover at 16-JAN-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 43 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_43.313.869142101
archived log for thread 1 with sequence 44 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_44.306.869142107
archived log for thread 1 with sequence 45 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_45.314.869142107
archived log for thread 1 with sequence 46 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_46.315.869142107
archived log for thread 1 with sequence 47 is already on disk as file +DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_47.316.869151219
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_43.313.869142101 thread=1 sequence=43
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_44.306.869142107 thread=1 sequence=44
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_45.314.869142107 thread=1 sequence=45
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_46.315.869142107 thread=1 sequence=46
archived log file name=+DATA/CDB/ARCHIVELOG/2015_01_16/thread_1_seq_47.316.869151219 thread=1 sequence=47
media recovery complete, elapsed time: 00:00:06
Finished recover at 16-JAN-15

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/backup''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/backup''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup''

Performing export of tables...
   EXPDP>
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

   EXPDP> Starting "SYS"."TSPITR_EXP_iasB_Fvns":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 49 MB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "C##SALMAN"."TEST"                          41.53 MB  364412 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_iasB_Fvns" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_iasB_Fvns is:
   EXPDP>   /u01/backup/tspitr_iasB_97417.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_iasB_Fvns" successfully completed at Fri Jan 16 15:16:30 2015 elapsed 0 00:00:41
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP>
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

   IMPDP> Master table "SYS"."TSPITR_IMP_iasB_zEmF" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_iasB_zEmF":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "C##SALMAN"."TEST"                          41.53 MB  364412 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_iasB_zEmF" successfully completed at Fri Jan 16 15:18:41 2015 elapsed 0 00:01:30
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/backup/CDB/datafile/o1_mf_temp_bckgjvpn_.tmp deleted
auxiliary instance file /u01/backup/IASB_PITR_CDB/onlinelog/o1_mf_3_bckgmrjw_.log deleted
auxiliary instance file /u01/backup/IASB_PITR_CDB/onlinelog/o1_mf_2_bckgmkjx_.log deleted
auxiliary instance file /u01/backup/IASB_PITR_CDB/onlinelog/o1_mf_1_bckgmckt_.log deleted
auxiliary instance file /u01/backup/IASB_PITR_CDB/datafile/o1_mf_users_bckglvqp_.dbf deleted
auxiliary instance file /u01/backup/CDB/datafile/o1_mf_sysaux_bckg8n44_.dbf deleted
auxiliary instance file /u01/backup/CDB/datafile/o1_mf_undotbs1_bckg8n49_.dbf deleted
auxiliary instance file /u01/backup/CDB/datafile/o1_mf_system_bckg8n3n_.dbf deleted
auxiliary instance file /u01/backup/CDB/controlfile/o1_mf_bckg857l_.ctl deleted
auxiliary instance file tspitr_iasB_97417.dmp deleted
Finished recover at 16-JAN-15

“AUXILIARY DESTINATION” clause is used to mention the directory where restoration from the backup will be done for auxiliary database, and it should have enough space available to house the database files. In the following we can see that during the recovery process, datafiles were restored at this location

[oracle@salman1 backup]$pwd
/u01/backup
[oracle@salman1 backup]$ ls -ltr
total 2434800
-rw-r----- 1 oracle asmadmin 1232535552 Jan 16 12:16 db_0vpss33u_1_1.bak
-rw-r----- 1 oracle asmadmin  637501440 Jan 16 12:17 db_10pss39o_1_1.bak
-rw-r----- 1 oracle asmadmin  623181824 Jan 16 12:19 db_11pss3d1_1_1.bak
drwxr-x--- 3 oracle asmadmin       4096 Jan 16 14:55 CDB
[oracle@salman1 backup]$ cd CDB
[oracle@salman1 CDB]$ ls -ltr
total 4
drwxr-x--- 2 oracle asmadmin 4096 Jan 16 14:55 controlfile
[oracle@salman1 CDB]$ ls -ltr
total 4
drwxr-x--- 2 oracle asmadmin 4096 Jan 16 14:57 controlfile
[oracle@salman1 CDB]$ ls -ltr
total 12
drwxr-x--- 2 oracle asmadmin 4096 Jan 16 14:57 controlfile
drwxr-x--- 2 oracle asmadmin 4096 Jan 16 14:57 onlinelog
drwxr-x--- 2 oracle asmadmin 4096 Jan 16 14:57 datafile

Check if table has been recovered
SQL> conn c##salman/salman
Connected.
SQL> select count(*) from test;

  COUNT(*)
----------
    182206

SQL>

Following Oracle document also explains some more parameters which can be used in recovery command
http://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV699

If table belongs to a pluggable database, recovery command would look like following
RMAN> RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups'
REMAP TABLE 'HR'.'PDB_EMP':'EMP_RECVR';

DUMP FILE and DATAPUMP DESTINATION
Specifies the name of the export dump file containing recovered tables or table partitions and the location in which it must be stored. This dump file can later be used to import the table

NOTABLEIMPORT
Indicates that the recovered tables or table partitions must not be imported into the target database. Later we can use export dump file created during recovery(by specifying DUMPFILE clause) to import the table.

REMAP TABLE
Renames the recovered tables or table partitions in the target database.

REMAP TABLESPACE
Recovers the tables or table partitions into a tablespace that is different from the one in which these objects originally existed.