Transparent Data Encryption in Oracle 11g

Oracle Transparent Data Encryption (TDE) enables the organizations to encrypt sensitive application data on storage media completely transparent to the application. TDE addresses encryption requirements associated with public and private privacy and security regulations such as PCI DSS. TDE column encryption was introduced in Oracle Database 10g Release 2, enabling encryption of table columns containing sensitive information. The TDE tablespace encryption and the support for hardware security modules (HSM) were introduced in Oracle Database 11gR1.

TDE is protecting the data at rest. It is encrypting the data in the datafiles so that in case they are obtained by other parties it will not be possible to access the clear text data.  TDE cannot be used to obfuscate the data for the users who have privileges to access the tables. In the databases where TDE is configured any user who has access on an encrypted table will be able to see the data in clear text because Oracle will transparently decrypt the data for any user having the necessary privileges.

TDE is using a two tier encryption key architecture consisting of  :

• a master encryption key - this is the encryption key used to encrypt secondary keys used for column encryption and tablespace encryption.
• one or more table and/or tablespace keys - these are the keys that are used to encrypt one or more specific columns or the keys used to encrypt  tablespaces. There is only one table key regardless of the number of encrypted columns in a table and it will be stored in the data dictionary. The tablespace key is stored in the header of each datafile of the encrypted tablespace. 

The table and tablespace keys are encrypted using the master key. The master key is stored in an external security module (ESM) that can be one of the following:

• an Oracle Wallet - a secure container outside of the database. It is encrypted with a password. 
• a Hardware Security Module (HSM) - a device used to secure keys and perform cryptographic operations. 

To start using  TDE the following operations have to be performed:

1.) Make sure that the wallet location exists. If a non default wallet location must be used then specify it in the sqlnet.ora file :

ENCRYPTION_WALLET_LOCATION =
   (SOURCE = (METHOD = FILE)
     (METHOD_DATA =
      (DIRECTORY = C:\app\neerajs\admin\orcl\wallet)
     )
   )

Note : The default encryption wallet location is $ORACLE_BASE/admin/<global_db_name>/wallet. If we want to let Oracle manage a wallet in the default location then there is no need to set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.

It is important to check that the location specified in sqlnet.ora or the default location exists and can be read/written by the Oracle processes.

2.) Generate a master key :

SQL> alter system set encryption key identified by "wallet_password" ;
system altered

This command will do the following :

A.) If there is no wallet currently in the wallet location then a new wallet with the password "wallet_password" will be generated. The password is enclosed in double quotes to preserve the case of the characters. If the double quotes are not used then the characters of the password will be all in upper case. This command will also cause the new wallet to be opened and ready for use.

B.) A new master key will be generated and will be written to the wallet. This newly generated master key will become the active master key. The old master keys (if there were any) will still be kept in the wallet but they will not be active. They are kept there to be used when decrypting data that was previously encrypted using them .

To see the status of an wallet run the following query:

SQL> select * from v$encryption_wallet;
WRL_TYPE             WRL_PARAMETER                      STATUS
-----------    ------------------------------         -----------
file                C:\app\neerajs\admin\orcl\wallet         OPEN

3.)  Enable encryption for a column or for an entire tablespace:

3.1) Create a table by specifying the encrypt option:

SQL> create table test(col1 number, col2 varchar2(100) encrypt using 'AES256' NO SALT) ;

3.2) Encrypt the column(s) of an existing table :

SQL> alter  table  test  modify( col2 encrypt SALT ) ;

Note : If the table has many rows then this operation might take some time since all the values stored in col2 must be replaced by encrypted strings. If the access to the table during this operations is needed then useOnline Table Redefinition

3.3)  Create an encrypted tablespace : The syntax is the same as creating a normal tablespace except for two clauses:

• We specify the encryption algorithm – in this case ‘AES256′. If we do not specify this, it will default to ‘AES128′. At the time of tablespace creation specify the encryption and default storage clause.

Define the encryption algorithem as " using 'algorithm' " along with the encryption clause. We can use the following algorithms while creating an encrypted tablespace.

AES128
AES192
AED256
3DES168

If we don't specify any algorithm with the encryption clause it will use AES128 as default.

•  The DEFAULT STORAGE (ENCRYPT) clause.

SQL> create tablespace encryptedtbs  datafile 'C:\app\neerajs\oradata\orcl\encryptedtbs01.dbf'  size 100M encryption using  'AES256'  default storage(encrypt) ;

Note: An existing  non encrypted tablespace cannot be encrypted. If we must encrypt the data from an entire tablespace then create a new encrypted tablespace and then move the data from the old tablespace to the new one TDE Master Key and Wallet Management .

The wallet is a critical component and should be backed up in a secure location (different to the location where the database backups are stored!). If the wallet containing the master keys is lost or if its password is forgotten then the encrypted data will not be accessible anymore.  Make sure that the wallet is backed up in the following scenarios: 

Immediately after creating it.
1. When regenerating the master key
2. When backing up the database. Make sure that the wallet backup is not stored in the same location with the database backup
3. Before changing the wallet password

Make sure that the wallet password is complex but at the same time easy to remember. When it is possible split knowledge about wallet password .If needed, the wallet password can be changed within Oracle Wallet Manager or with the following command using orapki  (starting from 11.1.0.7):

c:\> orapki wallet change_pwd -wallet <wallet_location>

Oracle recommends that the wallet files are placed outside of the $ORACLE_BASE directory to avoid having them backed up to same location as other Oracle files. Furthermore it is recommended to restrict the access to the directory and to the wallet files to avoid accidental removals. 

we can identify encrypted tablespaces in the database by using the below query :
SQL>SELECT ts.name, es.encryptedts, es.encryptionalg FROM v$tablespace ts
INNER JOIN v$encrypted_tablespaces es  ON es.ts# = ts.ts# ; 

The following are supported with encrypted tablespaces

• Move table back and forth between encrypted tablespace and non-encrypted tablespace .
• Datapump is supported to export/import encrypted content/tablespaces. 
• Transportable tablespace is supported using datapump. 

The following are not supported with encrypted tablespaces

• Tablespace encryption cannot be used for SYSTEM, SYSAUX, UNDO and TEMP tablespaces .
• Existing tablespace cannot be encrypted . 
• Traditional export/import utilities for encrypted content.

To check the example  of the TDE click here

Enjoy     :-) 

Enable block change tracking in oracle 11g

The block change tracking (BCT) feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a block change tracking file. This file is a small binary file called block change tracking (BCT) file stored in the database area. RMAN tracks changed blocks as redo is generated.If we enable block change tracking, then RMAN uses the change tracking file(BCT)  to identify changed blocks for an incremental backup, thus avoiding the need to scan every block in the datafile. RMAN only uses block change tracking when the incremental level is greater than 0 because a level 0 incremental backup includes all blocks. 

Enable block change tracking (BCT) 

SQL> alter database enable block change tracking  using file 'C:\app\neerajs\admin\noida\bct.dbf' ;

When data blocks change, shadow processes track the changed blocks in a private area of memory at the same time they generate redo . When a commit is issued, the BCT information is copied to a shared area in Large Pool called 'CTWR dba buffer' . At the checkpoint, a new background process, Change Tracking Writer (CTWR) , writes the information from the buffer to the change-tracking file . If contention for space in the CTWR dba buffer occurs, a wait event called , 'Block Change Tracking Buffer Space'  is recorded. Several causes for this wait event are poor I/O performance on the disk where the change-tracking file resides , or the CTWR dba buffer is too small to record the number of concurrent block changes .By default, the CTWR process is disabled because it can introduce some minimal performance overhead on the database. 

The v$block_change_tracking  views contains the name and size of the block change tracking file plus the status of change tracking: We can check by the below command :  

SQL> select filename, status, bytes from v$block_change_tracking;

To check whether the block change tracking file is being used or not, use the below command .

SQL> select  file#,  avg(datafile_blocks), avg(blocks_read),  avg(blocks_read/datafile_blocks) * 100 as  "% read for backup"  from v$backup_datafile  where incremental_level > 0  and  used_change_tracking = 'YES'  group by file#   order by file# ;

To disable Block Change Tracking (BCT)   issue the below command : 
SQL> alter database disable block change tracking  ;



Enjoy    :-) 

ORA-39082 ".... created with compilation warnings" while Importing

ORA-39082   generally  occur during  the  import . The error  message  states that  the  object in  the  SQL statement  following  this  error was  created with  compilation errors. If  this  error  occurred  for a  view,  it  is  possible  that  the base  table of  the view  was  missing . Here  is  an scenario  of  ora-39082 error ....

C:\>impdp  system/xxxx@xe  remap_schema=shaik9sep:shaik9sep11g dumpfile=SHAIK9SEP10G.DMP logfile=shaik9sep10g_import.log 

Import: Release 10.2.0.1.0 - Production on Wednesday, 30 November, 2011 14:40:06
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_04" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_04":  system/********@xe  remap_schema=shaik9sep:shaik9sep11g dumpfile=SHAIK9SEP10G.DMP logfile=shaik9sep10g_import.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SHAIK9SEP11G" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SHAIK9SEP11G"."AD_ARCHIVE"                       188.3 MB    1785 rows
. . imported "SHAIK9SEP11G"."T_REPORTSTATEMENT"          54.13 MB    952472 rows
. . imported "SHAIK9SEP11G"."FACT_ACCT"                        23.79 MB   115203 rows
. . imported "SHAIK9SEP11G"."AD_QUERYLOG"                     20.35 MB   78411 rows
. . imported "SHAIK9SEP11G"."T_TRIALBALANCE"                 12.54 MB   55310 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"SHAIK9SEP11G"."DSI_FUNC_PRODUCTREP" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"SHAIK9SEP11G"."INVOICEOPEN" created with compilation warnings
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"SHAIK9SEP11G"."T_INVENTYVALUE_CREATE" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SHAIK9SEP11G"."AD_SYNCHRONIZE" created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39082: Object type VIEW:"SHAIK9SEP11G"."M_STORAGE_V" created with compilation warning

All the above error ORA-30082 is a warning . This error occurs due to improper or re-order the sequence of importing the objects or due to the dependency on others objects . For example  , in above case data pump import create procedures before views, if our procedure have dependency on views then we will have the ORA-39082 compilation errors . There are various ways to solve this issues . 

1.)  Run  utlrp.sql  to recompile all invalid objects within the database after the import is complete. This script is in the $ORACLE_HOME\rdbms\admin  directory or alternatively we can use the built-in  DBMS_RECOMP package . This will usually clean up all the invalid objects. utlrp.sql  will compile objects in the database across schemas. In  case of  Re-mapping objects from one schema to another and utlrp.sql  won't be able to compile them .

2.) After the import is completed, recompile the every errors . This is useful when you have few errors. The below command are used to recompile the objects as 

SQL> ALTER PACKAGE <SchemaName>.<PackageName> COMPILE;

SQL> ALTER PACKAGE <SchemaName>.<PackageName> COMPILE BODY;

SQL> ALTER PROCEDURE my_procedure COMPILE;

SQL> ALTER FUNCTION my_function COMPILE;

SQL> ALTER TRIGGER my_trigger COMPILE;

SQL> ALTER VIEW my_view COMPILE;

SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'shaik9sep11g') ; or 

SQL> EXEC UTL_RECOMP.recomp_serial('shaik9sep11g') ;

In case of synonym, we need to recreate the synonym.

3.) Use SQLFILE option from impdp to generate the DDL from the export dump and replace the schema name globally , edit and execute the script from sqlplus. This should resolve most of the errors. If we still have errors, proceed  with utlrp.sql. This is the one of good option to deal with this type of error .

4.) There are Bugs which return similar error during import( i.e, impdp). Check metalink  ORA-39082 When Importing Wrapped Procedures [ID 460267.1]

Check the below link for more info and examples about this errors  : 

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:286816015990

Enjoy   :-) 

RMAN Tablespace Point-in-Time Recovery(TSPIR) in Oracle 11gR2

Recovery Manager (RMAN) Automatic TSPITR enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

RMAN TSPITR is most useful for the following situations:

• We want to recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database. For example, we maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or DML statement corrupts the data in only one of the tablespaces.
• We want to recover data lost after DDL operations that change the structure of tables. We cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation.
• We want to recover a table after it has been dropped with the PURGE option.
• We want to recover from the logical corruption of a table.
• We want to recover dropped tablespaces. In fact, RMAN can perform TSPITR on dropped tablespaces even when a recovery catalog is not used.
• We can also use Flashback Database to rewind data, but we must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to our earliest recoverable backup.

TSPIR was there in  earleir release but have some limitation i.e, we cannot recover a dropped tablespace .  Oracle 11gr2 performs a fully automated managed  TSPIR. It automatically creates and start the auxiliary instance and restores the datafiles it requires and the files pertaining to the dropped tablespace. It will first 

perform a recovery of the tablespace on the auxiliary instance and then use Data Pump and Transportable Tablespace technology to extract and import the tablespace meta data into the original source database .

Here we will illustrate the Concept of TSPIR with an example .

We will create a user say  "TSPIR"  and assign the default tablespace say "tspir"  and create  tables in this tablespace. We take the full backup of the database and further drop the tablespace "tspir" . Before dropping we we note the scn and use this scn to do TSPIR . Below are steps for the TSPIR

Step 1 :  Clean the previous failed TSPIR 

SQL> exec dbms_backup_restore.manageauxinstance ('TSPITR',1) ;
PL/SQL procedure successfully completed.

Step 2 :  Create tablespace and Users and Create tables

SQL> create tablespace tspir datafile 'C:\app\neerajs\oradata\orcl\tspir.dbf' size 150m autoextend on;
Tablespace created.

SQL> create user tspir identified by tspir
  2  default tablespace tspir
  3  quota unlimited on tspir;
User created.

SQL> grant resource,connect to tspir;
Grant succeeded.

SQL> connect tspir/tspir@orcl
Connected.

SQL> create table test(id number);
Table created.

SQL> insert into test values(12);
1 row created.

SQL> insert into test values(121);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test;
        ID
----------
        12
       121

SQL> create table emp as select * from user_objects;
Table created.

SQL> select count(*) from emp;
  COUNT(*)
----------
         2

SQL> conn / as sysdba
Connected.

Step  3  :   Take the fresh backup of database 

SQL> host   rman   target   sys@orcl
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 30 14:35:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1296005542)

RMAN> backup database plus archivelog;
Starting backup at 30-NOV-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=768238310
input archived log thread=1 sequence=4 RECID=2 STAMP=768238310
input archived log thread=1 sequence=5 RECID=3 STAMP=768238311
input archived log thread=1 sequence=6 RECID=4 STAMP=768238314
input archived log thread=1 sequence=7 RECID=5 STAMP=768239453
input archived log thread=1 sequence=8 RECID=6 STAMP=768239455
input archived log thread=1 sequence=9 RECID=7 STAMP=768305386
input archived log thread=1 sequence=10 RECID=8 STAMP=768334227
input archived log thread=1 sequence=11 RECID=9 STAMP=768393025
input archived log thread=1 sequence=12 RECID=10 STAMP=768454251
input archived log thread=1 sequence=13 RECID=11 STAMP=768521484
input archived log thread=1 sequence=14 RECID=12 STAMP=768580566
channel ORA_DISK_1: starting piece 1 at 30-NOV-11
channel ORA_DISK_1: finished piece 1 at 30-NOV-11
piece handle=F:\RMAN_BKP\01MSV6UP_1_1 tag=TAG20111130T143608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 30-NOV-11

Starting backup at 30-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\NEERAJS\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\NEERAJS\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00006 name=C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF
input datafile file number=00005 name=C:\APP\NEERAJS\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00003 name=C:\APP\NEERAJS\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00004 name=C:\APP\NEERAJS\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 30-NOV-11
channel ORA_DISK_1: finished piece 1 at 30-NOV-11
piece handle=F:\RMAN_BKP\02MSV70H_1_1 tag=TAG20111130T143705 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 30-NOV-11

Starting backup at 30-NOV-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=13 STAMP=768580741
channel ORA_DISK_1: starting piece 1 at 30-NOV-11
channel ORA_DISK_1: finished piece 1 at 30-NOV-11
piece handle=F:\RMAN_BKP\04MSV746_1_1 tag=TAG20111130T143901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-NOV-11

Starting Control File and SPFILE Autobackup at 30-NOV-11
piece handle=F:\RMAN_BKP\CF\C-1296005542-20111130-01 comment=NONE
Finished Control File and SPFILE Autobackup at 30-NOV-11

Step 4  :  Note the SCN and drop the tablespace 

SQL> select current_scn from v$database;
CURRENT_SCN
-----------------
    5659022

SQL> drop tablespace tspir including contents and datafiles;
Tablespace dropped.

Step  5  :  Connect with rman and perform TSPIR  

Here we have used the auxiliary destination with the recover tablespace command because auxiliary  destination is an  optional disk location where RMAN uses to temporarily store the auxiliary set files. The auxiliary destination is used only when using a RMAN-managed auxiliary instance. Specifying an auxiliary destination with a user-managed auxiliary instance results in an error.

C:\>rman target sys/xxxx@orcl
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 30 14:58:11 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1296005542)

RMAN>  recover tablespace tspir  until scn 5659022  auxiliary  destination 'F:\';
Starting recover at 30-NOV-11
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='nume'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=nume_tspitr_ORCL
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=F:\
log_archive_dest_1='location=F:\'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area     292933632 bytes
Fixed Size                     1374164 bytes
Variable Size                100665388 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6344704 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace tspir
contents of Memory Script:
{
# set requested point in time
set until  scn 5659022;
# 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';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 30-NOV-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=59 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 F:\RMAN_BKP\CF\C-1296005542-20111130-01
channel ORA_AUX_DISK_1: piece handle=F:\RMAN_BKP\CF\C-1296005542-20111130-01 tag=TAG20111130T143903
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=F:\ORCL\CONTROLFILE\O1_MF_7FD0QK8S_.CTL
Finished restore at 30-NOV-11
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:
{
# set requested point in time
set until  scn 5659022;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  6 to
 "C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 6;
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
executing command: SET NEWNAME
renamed tempfile 1 to F:\ORCL\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
Starting restore at 30-NOV-11
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 F:\ORCL\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to F:\ORCL\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to F:\ORCL\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF
channel ORA_AUX_DISK_1: reading from backup piece F:\RMAN_BKP\02MSV70H_1_1
channel ORA_AUX_DISK_1: piece handle=F:\RMAN_BKP\02MSV70H_1_1 tag=TAG20111130T143705
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 30-NOV-11
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=768585055 file name=F:\ORCL\DATAFILE\O1_MF_SYSTEM_7FD0QYNZ_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=768585056 file name=F:\ORCL\DATAFILE\O1_MF_UNDOTBS1_7FD0QYRF_.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=768585056 file name=F:\ORCL\DATAFILE\O1_MF_SYSAUX_7FD0QYPG_.DBF
contents of Memory Script:
{
# set requested point in time
set until  scn 5659022;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  6 online";
# recover and open resetlogs
recover clone database tablespace  "TSPIR", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile  1 online
sql statement: alter database datafile  3 online
sql statement: alter database datafile  2 online
sql statement: alter database datafile  6 online
Starting recover at 30-NOV-11
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 15 is already on disk as file D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000015_0768224813.0001
archived log for thread 1 with sequence 16 is already on disk as file D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000016_0768224813.0001
archived log file name=D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000015_0768224813.0001 thread=1 sequence=15
archived log file name=D:\ARCHIVE\ORCL_ARCHIVE\ARC0000000016_0768224813.0001 thread=1 sequence=16
media recovery complete, elapsed time: 00:00:04
Finished recover at 30-NOV-11
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TSPIR read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
F:\''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
F:\''";
}
executing Memory Script
sql statement: alter tablespace  TSPIR read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''F:\''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''F:\''
Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_nume":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_nume" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_nume is:
   EXPDP>   F:\TSPITR_NUME_43731.DMP
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TSPIR:
   EXPDP>   C:\APP\NEERAJS\ORADATA\ORCL\TSPIR.DBF
   EXPDP> Job "SYS"."TSPITR_EXP_nume" successfully completed at 16:20:28
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_nume" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_nume":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_nume" successfully completed at 16:21:48
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  TSPIR read write';
sql 'alter tablespace  TSPIR offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace  TSPIR read write
sql statement: alter tablespace  TSPIR offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_TEMP_7FD0Y3PY_.TMP deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_4_7FD0XROZ_.LOG deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_3_7FD0XK9R_.LOG deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_2_7FD0X9RF_.LOG deleted
auxiliary instance file F:\ORCL\ONLINELOG\O1_MF_1_7FD0X2LK_.LOG deleted
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_SYSAUX_7FD0QYPG_.DBF deleted
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_UNDOTBS1_7FD0QYRF_.DBF deleted
auxiliary instance file F:\ORCL\DATAFILE\O1_MF_SYSTEM_7FD0QYNZ_.DBF deleted
auxiliary instance file F:\ORCL\CONTROLFILE\O1_MF_7FD0QK8S_.CTL deleted
Finished recover at 30-NOV-11
RMAN>

Step  6  : Check the tablepsace status and existance 

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                 STATUS
-------------------------                -----------
SYSTEM                                      ONLINE
SYSAUX                                      ONLINE
UNDOTBS1                                 ONLINE
TEMP                                           ONLINE
USERS                                         ONLINE
EXAMPLE                                   ONLINE
TSPIR                                          OFFLINE

Since, we find the tablepspace "TSPIR" is offline . So bring the tablespace online .

SQL> alter tablespace tspir online;
Tablespace altered.

SQL> alter database datafile 'C:\app\neerajs\oradata\orcl\tspir.dbf' online;
Database altered.

SQL> select table_name from dba_tables where tablespace_name='TSPIR';
TABLE_NAME
-----------------
TEST
EMP

SQL> select * from tspir.test;
        ID
----------
        12
       121

SQL> select count(*) from tspir.emp;
  COUNT(*)
----------
         2

Hence , we find that both the tables are recovered



Enjoy     :-) 

ORA-19852: Error creating services for auxiliary instance

This error  generally occur when we go for TSPIR(tablespace point-in-time recovery) . When we issue the "recover tablespace ... "  command we get this error as

RMAN> recover tablespace tspir  until scn 5659022 auxiliary destination 'F:\';
Starting recover at 30-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
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='tAbD'
RMAN-00571: ========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===========
RMAN-00571: ========================================================
RMAN-03002:failure of recover command at 11/30/2011 14:56:13
ORA-19852:error creating services for auxiliary instance tAbD (error 0)
ORA-27302: failure occurred at:
ORA-27303:additional information: failed to start instance

This error occur because the previous failed TSPITR attempt wasn't cleaned up properly . Therefore to solve this issue, we have to clean the failed TSPIR attempts .Issue the following procedure to clean up the previous failed auxiliary creation attempt:

SQL> exec dbms_backup_restore.manageauxinstance ('TSPITR',1) ;
PL/SQL procedure successfully completed.



Enjoy      :-)