Part 1 - Goldengate supplemental logging , keycols, logallsupcols testcase

My goldengate set up is a 11.2.0.4 oracle database on a goldengate 12c .
I am running a series of tests to understand how goldengate writes data into the trail file and how replicat behaves based on supplemental logging (all columns vs few columns), no PK/UK defined on the tables, effect of keycols in the extract and replicat parameter file, LOGALLSUPCOLS(a 12c GG parameter).

I hope to document a bunch of them and will be writing multiple testcases.

TEST CASE 1
=============

Table has no PK or unique key defined on both source and target and table structure is identical.
Supplemental logging is done for 3 columns out of 6 columns.
Extract has no KEYCOLS defined.
EXTRACT has no LOGALLSUPCOLS.
replicat has no KEYCOLS defined.

SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number)
  2  /

Table created.


GGSCI (titan.gautham.com) 119> add trandata scott.junk, cols(col1,col2,col3),nokey

2014-08-09 13:07:44  WARNING OGG-06439  No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SCOTT.JUNK.
TRANDATA for scheduling columns has been added on table 'SCOTT.JUNK'.
GGSCI (titan.gautham.com) 120> info trandata scott.junk

Logging of supplemental redo log data is enabled for table SCOTT.JUNK.

Columns supplementally logged for table SCOTT.JUNK: COL1, COL2, COL3.

Extract parameter file

..
--LOGALLSUPCOLS -->commented out
table SCOTT.junk; -->no keycols mentioned here

Perform the following from sqlplus at the source-
SQL> insert into junk values(100,100,100,100,100,100);

1 row created.

SQL> commit;

Commit complete.

SQL> update junk set col4=500 where col1=100;

1 row updated.

SQL> commit;

Commit complete.

The below logdump portion is relevant only to the update statement .I have not included the logdump portion for the insert as it does not serve us any purpose for this discussion.

logdump of trail file

1.Has info on col1, col2,col3 (as they are added in add trandata)
2.Has info on col4 as it was changed
3.shows GGSPKUpdate (for the update) with before and after image

Logdump 692 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    90  (x005a)   IO Time    : 2014/08/09 13:23:00.000.000
IOType     :   115  (x73)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      10578       AuditPos   : 230433296
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/08/09 13:23:00.000.000 GGSPKUpdate          Len    90 RBA 4260
Name: SCOTT.JUNK
After  Image:                                             Partition 4   G  s
 002c 0000 0007 0000 0003 3130 3000 0100 0700 0000 | .,........100.......
 0331 3030 0002 0007 0000 0003 3130 3000 0300 0700 | .100........100.....
 0000 0331 3030 0000 0007 0000 0003 3130 3000 0100 | ...100........100...
 0700 0000 0331 3030 0002 0007 0000 0003 3130 3000 | .....100........100.
 0300 0700 0000 0335 3030                          | .......500
Before Image          Len    46 (x0000002e)
KeyLen    44 (x0000002c)
KeyCol     0 (x0000), Len     7 (x0007)
 0000 0003 3130 30                                 | ....100
KeyCol     1 (x0001), Len     7 (x0007)
 0000 0003 3130 30                                 | ....100
KeyCol     2 (x0002), Len     7 (x0007)
 0000 0003 3130 30                                 | ....100
KeyCol     3 (x0003), Len     7 (x0007)
 0000 0003 3130 30                                 | ....100

After Image           Len    44 (x0000002c)
Column     0 (x0000), Len     7 (x0007)
 0000 0003 3130 30                                 | ....100
Column     1 (x0001), Len     7 (x0007)
 0000 0003 3130 30                                 | ....100
Column     2 (x0002), Len     7 (x0007)
 0000 0003 3130 30                                 | ....100
Column     3 (x0003), Len     7 (x0007)
 0000 0003 3530 30                                 | ....500

GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4155 4751 4141 4641 4141 4149 7641 4141 0001 | AAAUGQAAFAAAAIvAAA..
TokenID x4c 'L' LOGCSN           Info x00  Length    9
 3539 3130 3932 3935 36                            | 591092956
TokenID x36 '6' TRANID           Info x00  Length   11
 352e 3132 2e32 3138 3937 35                       | 5.12.218975


replicat parameter file
=========================
DDL INCLUDE MAPPED &
EXCLUDE OBJNAME GGADM.EXCEPTIONS
ASSUMETARGETDEFS
GETTRUNCATES
REPERROR (-1, EXCEPTION)
REPERROR (-1403, EXCEPTION)
..
..
map scott.junk, target scott1.junk, mapexception (TARGET ggadm.exceptions
, INSERTALLRECORDS
-- , EXCEPTIONSONLY
, colmap ( rep_name = 'rphvn1d'
, table_name = @GETENV ('GGHEADER', 'TABLENAME')
, errno = @GETENV ('LASTERR', 'DBERRNUM')
, dberrmsg = @GETENV ( 'LASTERR', 'DBERRMSG')
, optype = @GETENV ( 'LASTERR', 'OPTYPE')
, errtype = @GETENV ( 'LASTERR', 'ERRTYPE')
, logrba = @GETENV ( 'GGHEADER', 'LOGRBA')
, logposition = @GETENV ( 'GGHEADER', 'LOGPOSITION')
, TRAILRBA = @GETENV('RECORD', 'FILERBA')
, TRAILSEQUENCE = @GETENV('RECORD', 'FILESEQNO')
, committimestamp = @GETENV ( 'GGHEADER', 'COMMITTIMESTAMP')
) );

EFFECT ON REPLICAT
==================

REPLICAT CRASHES

ggserr.log
------------

2014-08-09 13:23:05  WARNING OGG-01151  Oracle GoldenGate Delivery for Oracle, rphvn1d.prm:  Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:05  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rphvn1d.prm:  Repositioning to rba 4260 in seqno 14.
2014-08-09 13:23:05  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rphvn1d.prm:  Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:14  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rphvn1d.prm:  PROCESS ABENDING.

report file
-----------
2014-08-09 13:40:18  WARNING OGG-06439  No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
  COL1, COL2, COL3, COL4, COL5, COL6
Using the following key columns for target table SCOTT1.JUNK: COL1, COL2, COL3, COL4, COL5, COL6.



From above error message , it is clear that at replicat side since it does not see any PK (on the target)or KEYCOL defined (in replicat parameter file)
, it needs all the 6 columns in the trail file. But the trail file has before and after image for only first 4 columns (col1,col2.col3 as it is defined by supplemental logging and col4 as it was the changed column).

So to remediate, temporarily add a PK to target-
SQL> alter table junk add constraint pk_junk primary key(col1);

Table altered.

And restart the replicat

GGSCI (titan.gautham.com) 137> start rphvn1d

Sending START request to MANAGER ...
REPLICAT RPHVN1D starting


GGSCI (titan.gautham.com) 138> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                      
JAGENT      STOPPED                                      
EXTRACT     RUNNING     XTHVN1D     00:00:09      00:00:07
REPLICAT    RUNNING     RPHVN1D     02:18:58      00:00:02

--Check the data on the target
SQL> select * from junk;

      COL1       COL2       COL3       COL4       COL5       COL6
---------- ---------- ---------- ---------- ---------- ----------
       100        100        100        500        100        100

Now since the testing is complete, get the source and target ready for the next testcase by dropping junk table at the source and make sure the DDL got replicated to target -

source
--------
SQL> drop table junk;

Table dropped.

target
------

SQL> select * from junk;
select * from junk
              *
ERROR at line 1:
ORA-00942: table or view does not exist

GG Procedure calling

--begin character setissue handler
--MAP HIGHERONE.tblemailqueue ,target HIGHERONE.tblemailqueue ,
--SQLEXEC(SPNAME higherone.p_string_transform,ID x,PARAMS(in_str=sbody),TRACE ALL),
--COLMAP(
--usedefaults,
--sbody=@getval(x.out_str));
--end character setissue handler

Convert Single Instance Database to Oracle RAC One Node Database / RAC 1 node database

I had the recent need to convert a single instance DB to a RAC 1-node. I haven't seen many documents
which talks about the steps involved As most docs focus on conversion from single instance to a regular RAC.

In the past, I have used DBCA to create a 1-Node RAC and always used the "Generate database creation scripts" at the end. Then I have had manually edited the scripts generated to suit my purpose and this approach has served me very well as allowing dbca to create a DB directly has caused many unnecessary schemas to creep in.

I drove into the scripts and to my best abilities, tried to reproduce what it does and have written the below blog.


Briefly here are the steps to convert a single instance DB to 1 Node RAC.

Before you start, ensure
+++++++++++++++++++++

I assume you just have single instance init.ora parameters enabled.

No RAC init.ora parameters at this point should be enabled especially CLUSTER_DATABASE.

You might want to consider setting the below init.ora parameters to valid ASM disk group

db_create_file_dest="+DATA_DEV"
db_recovery_file_dest="+FRA_DEV"

Please make sure that your control files, data files, redo logs are already in ASM before you perform these steps.



I also recommend that you use init.ora file which internally points to a spfile as shown below.

Also make sure, you have x init.ora files (where x is the number of nodes)in each node's ORACLE_HOME/dbs directory
with the naming convention init<DB Name>_1.ora, init<DB Name>_2.ora, init<DB Name>_3.ora etc

For example,since my setup is a 2-Node RAC, I have 2 init.ora files with the name init<DB Name>_1.ora and init<DB Name>_2.ora in
ORACLE_HOME/dbs directory of both the RAC nodes.

$ cat initTESTDB_1.ora
SPFILE='+DATA_DEV/TESTDB/spfileTESTDB.ora'          


$ cat initTESTDB_2.ora
SPFILE='+DATA_DEV/TESTDB/spfileTESTDB.ora'          





My Oracle Home is /u02/app/oracle/product/11.2.0.4/db_1 and DB Name is TESTDB.

The Grid Home is /u02/app/11.2.0/grid .


1)Create the password file if it does not exist (recommended)  on 1 node and copy it to other nodes.
Please note that there is NO suffix to password file name.

/u02/app/oracle/product/11.2.0.4/db_1/bin/orapwd file=/u02/app/oracle/product/11.2.0.4/db_1/dbs/orapwTESTDB force=y

Make the following change in your init.ora file(If using spfile perform an alter system set)
remote_login_passwordfile=exclusive

Please shutdown the DB at this point.



2)This can be skipped if already performed(please note dbca automatically does it if you have ever created a DB on this
OH in the past) but again no harm in doing this again.

/u02/app/11.2.0/grid/bin/setasmgidwrap o=/u02/app/oracle/product/11.2.0.4/db_1/bin/oracle

3)Add the database to CRS.

/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl add database -d TESTDB -o /u02/app/oracle/product/11.2.0.4/db_1 -p +DATA_DEV/TESTDB/spfileTESTDB.ora -n TESTDB -c RACONENODE -e node1,node2 -i TESTDB -a "DATA_DEV,FRA_DEV"

Please pay particular attention to
(a)The option "-c RACONENODE"
(b)The places which will need you to plug in your ASM disk group.In my case , I have two of them called DATA_DEV,FRA_DEV

(4) Add a service.
/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl add service -s TESTDB.gautham.com -d TESTDB -P BASIC

(5)Disable the database
/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl disable database -d TESTDB

(6)Startup the database normally via

sqlplus '/ as sysdba'
startup

(7) Add the redo log groups for thread 2.

I assume that GROUP 1 and GROUP 2 are the only 2 groups in the single instance DB belonging to thread 1.By default, thread 1 should already exist. If there
are more than 2 groups in thread 1, then change the group name below accordingly .

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3  SIZE 250m,
 GROUP 4  SIZE 250m;

ALTER DATABASE ENABLE PUBLIC THREAD 2;

Repeat it with THREAD 3 , if it is a 3 node RAC and so on.


(8) There is no need to add a 2nd Tablespace as this is a 1-Node RAC and only 1 instance will be acive at any point.

(9) Set the 2 parameters in the spfile to


ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
ALTER SYSTEM SET remote_listener='<scan name>:1521' scope=spfile ;


(10)Shutdown the DB via a normal "shutdown immediate" command.

(11) /u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl enable database -d TESTDB;
(12) /u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl start database -d TESTDB;

Extraction of Grants from a schema

The following should be able to extract all the grants given from a particular schema to the other schemas.

This is particular useful when you have a bunch of schemas (with cross grants) and you are planning to move schema by schema and then finally apply the grants when all schemas are moved over.

expdp \'/ as sysdba\' schemas=scott dumpfile=scott_role.dmp nologfile=yes  include=owner_grant


impdp \'/ as sysdba\' dumpfile=scott_role.dmp sqlfile=scott_role.sql nologfile=yes

Script to monitor Alert log files for an Oracle Database

Recently I was being asked to write a script to monitor the Oracle Alert Logs for "ORA-" errors and send out an automated email to the DBA team.

Here are the challenges :
1.How do I make the script scan only the lines which were not scanned by the previous scan .
An other way to reframe the question - if scan x happened at 3pm, how do I ensure that the Scan (x+1) picked up only the new lines generated after 3pm. Should I store the time stamp of the last scan or should I store the high-water line count . I finally decided to go with the HWM line number.

2.Also how do I deal when a alert log is recycled?Will my script be able to find out that alert log has been recycled and then automatically set by line HWM to start from line 1 of the new alert log.
Looks like checking the stored HWM line count and the current line count of the alert log would help me in finding this out .If the alert log line count is smaller than the stored HWM line count then it would be safe to assume the alert log was recycled. Again in extraordinary circumstances where an alert log was recycled and the subsequent script ran so late(say after a couple of weeks or so) that new alert log line count exceeded the stored HWM line count ,then my logic would make it skip the first stored HWM lines of the alert log for that particular run only (but then again regaing normal operations for the subsequent scans)but I choose to ignore this possibility as this is extremely very remote and even if so, only the first scan would fail to detect the ORA- errors.

#!/bin/bash
#Alert Log Monitoring Script
#Usage: alert_monitor.sh
#Author:Gautham Chandrasekaran
#Version 1.0 06 NOVEMBER 2013

EMAIL_LIST=gautha@hotmail.com
ALERT_LOG_FILE=/u01/app/oracle/diag/rdbms/prod/PROD_1/trace/alert_PROD_1.log
EMAIL_FILE=/tmp/alert_mail.log

#Error out if Alert Log is missing
if [  ! -f $ALERT_LOG_FILE ] ; then
 echo "**** $ALERT_LOG_FILE FILE MISSING FROM ALERT LOG MONITOR ******" > $EMAIL_FILE
 cat $EMAIL_FILE|mailx -s "Alert Log Errors" $EMAIL_LIST
 exit 1
fi



ALERT_LOG_LINE_CNT_FILE=${ALERT_LOG_FILE}.ctr

#First run of the script or somebody deleted it.Therefore start from zero.
if [  ! -f $ALERT_LOG_LINE_CNT_FILE ] ; then
echo 0 > $ALERT_LOG_LINE_CNT_FILE
fi

STORED_LINE_CTR=`cat $ALERT_LOG_LINE_CNT_FILE`


ALERT_LOG_LINE_CTR=`cat $ALERT_LOG_FILE|wc -l`

#check to see whether somebody has recycled alert log file.
#in this case the STORED_LINE_CTR will be higher than ALERT_LOG_LINE_CTR
#If so just reset STORED_LINE_CTR to 0.
if [ $ALERT_LOG_LINE_CTR -lt $STORED_LINE_CTR ] ; then
STORED_LINE_CTR=0
fi


LINES_TO_MONITOR=`expr $ALERT_LOG_LINE_CTR - $STORED_LINE_CTR`


tail -n $LINES_TO_MONITOR $ALERT_LOG_FILE|grep -i "ora-"  > /tmp/alert_mail.log

#Reset line number to ctr file to skip the scanned rows in the next run
echo $ALERT_LOG_LINE_CTR > $ALERT_LOG_LINE_CNT_FILE


#Email only if the file is not empty
if [ -s $EMAIL_FILE ] ; then
 cat $EMAIL_FILE|mailx -s "Alert Log Errors" $EMAIL_LIST
fi

ASM Persistent Device Mapping for Oracle 11gR2 RAC for RHEL 6

Recently when I was setting up a RAC setup at home using some dell desktop servers , I hit a unique problem.
Before going into that , here was my setup details

RAC Node Names : siddhu.gautham.com, surya.gautham.com (CentOS 6.4)
Openfiler : openfiler.gautham.com (Openfiler 2.3 or 2.4 (not sure) 32-bit) (ip 192.168.1.195(eth0),
                 192.168.2.195(eth1)) ->Used to provide shared storage to the RAC Nodes.

The openfiler server was deployed to provide iscsi storage to the other RAC Nodes.

After the install of the 3 servers and provisioning of the storage to the 2 RAC nodes, I reached a point where i needed to install the ASMLib libraries for device persistance. That is when I  realized that Oracle Corp had stopped supplying ASMLib packages starting RHEL 6.

So now I had to look at other alternatives for device persistence.

The below should tell you what I mean by device persistence
=========================================


[root@siddhu by-path]# pwd
/dev/disk/by-path



[root@siddhu by-path]# ls -lt |grep 192.168.2

lrwxrwxrwx 1 root root  9 May  6 23:27 ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.crs1-lun-0 -> ../../sdb
lrwxrwxrwx 1 root root  9 May  6 23:27 ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.data1-lun-0 -> ../../sde
lrwxrwxrwx 1 root root  9 May  6 23:27 ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.fra1-lun-0 -> ../../sdc

The reason I had to filter with 192.168.2 was because openfiler had 2 network interfaces and I want to use storage presented only via eth1 which was 192.168.2.195.

The problem above is the crs lun is available as /dev/sdb, data lun as /dev/sde and fra lun is /dev/sdc.
The moment the system is rebooted this would change to a different mapping.

Also if you go the other nac rode i.e. surya.gautham.com , you would see the above mappings as completely different i.e. CRS lun would not be presented as the same as /dev/sdb but as something else.

Therefore what i needed was a reliable mapping like say /dev/asm-crs-disk1 which would always map to crs lun and  /dev/asm-data-disk1 which would map to data lun and so on across all nodes of the RAC.

The way we get this is to use udev rules.

Before you do this, please partition the the 3 devices above using fdisk command.
Create just 1 primary partition (I am not going to the details of this).

fdisk /dev/sdb
fdisk /dev/sde
fdisk /dev/sdc

Also perform partprobe on the other node to see the resulting /dev/sd[x]1 partition.

PLEASE NOTE THAT I HAD TO REBOOT BOTH THE RAC NODES AND THERFORE THE MAPPING CHANGED FROM ABOVE.PLEASE DO NOT LET THE MISMATCH ABOVE AND BELOW CONFUSE YOU.

From above we identified that /dev/sdd points to CRS in siddhu and /dev/sdc points to CRS in surya
[root@siddhu ~]# /sbin/scsi_id -g -u -d /dev/sdd
14f504e46494c45526834373035642d374d73772d794c386b

[root@surya iscsi]# /sbin/scsi_id -g -u -d /dev/sdc
14f504e46494c45526834373035642d374d73772d794c386b

The above shows that identifier is same irrespective of whether it is /dev/sdc(surya) or /dev/sdd(siddhu)
and this identifier will always be the same for CRS.

For FRA
=======
[root@surya iscsi]#  /sbin/scsi_id -g -u -d /dev/sdf
14f504e46494c455252635354667a2d4a6153492d6c307a66

[root@siddhu ~]# /sbin/scsi_id -g -u -d /dev/sde
14f504e46494c455252635354667a2d4a6153492d6c307a66



For DATA
========
[root@siddhu ~]# /sbin/scsi_id -g -u -d /dev/sdf
14f504e46494c45523473334e397a2d70347a612d554a394f

[root@surya iscsi]# /sbin/scsi_id -g -u -d /dev/sdg
14f504e46494c45523473334e397a2d70347a612d554a394f


vi /etc/udev/rules.d/99-oracle-asmdevices.rules



KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c45526834373035642d374d73772d794c386b", NAME="asm-crs-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c455252635354667a2d4a6153492d6c307a66", NAME="asm-fra-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c45523473334e397a2d70347a612d554a394f", NAME="asm-data-disk1", OWNER="oracle", GROUP="dba", MODE="0660"



After reboot
+++++++++++++

[root@surya dev]# (cd /dev/disk/by-path; ls -l *openfiler* |grep -i 192.168.2| awk '{FS=" "; print $9 " " $10 " " $11}')
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.crs1-lun-0 -> ../../sde
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.crs1-lun-0-part1 -> ../../asm-crs-disk1
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.data1-lun-0 -> ../../sdg
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.data1-lun-0-part1 -> ../../asm-data-disk1
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.fra1-lun-0 -> ../../sdc
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.fra1-lun-0-part1 -> ../../asm-fra-disk1
[root@surya dev]#


[root@surya dev]# ls -lt /dev/*asm*
brw-rw---- 1 oracle dba 8, 65 May  1 00:50 /dev/asm-crs-disk1
brw-rw---- 1 oracle dba 8, 33 May  1 00:50 /dev/asm-fra-disk1
brw-rw---- 1 oracle dba 8, 97 May  1 00:50 /dev/asm-data-disk1
[root@surya dev]# 

Restore and recover from cold backup using rman

Many a times before a application upgrade, we would like to take a cold backup of the database just to take care of some "mess-up" and would just want to restore it if something goes wrong.

The following is the preferred way for me to take a RMAN cold backup.





Take the BACKUP
===============
Put the DB in mount mode.

rman target / nocatalog<<EOF>backup.log

run{
allocate channel d1  device type disk format  '/oracle/arch/rmanbkp/HOMEDB_FULL_%d_%T_%U';

backup as compressed backupset tag rman_backfulcold_HOMEDB database include current controlfile ;
}
EOF

Query from RMAN to see where the control file resides


list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    39.13M     DISK        00:00:06     04-MAR-13  
        BP Key: 13   Status: AVAILABLE  Compressed: YES  Tag: RMAN_BACKFULCOLD_HOMEDB
        Piece Name: /oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0do3ltu7_1_1
  List of Datafiles in backup set 13

..


..
..
..

        Piece Name: /oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0no3ltuo_1_1
  List of Datafiles in backup set 23
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  28      Full 301818     04-MAR-13 /oracle/oradata02/HOMEDB/data/forumindextabspace01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24      Full    1.03M      DISK        00:00:01     04-MAR-13  
        BP Key: 24   Status: AVAILABLE  Compressed: YES  Tag: RMAN_BACKFULCOLD_HOMEDB
        Piece Name: /oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0oo3ltup_1_1
  Control File Included: Ckp SCN: 301818       Ckp time: 04-MAR-13





STEPS TO RESTORE
================

startup nomount

Restore the CONTROL FILE from the backup piece identified above.

run
{
allocate channel c1 device type disk format '/oracle/arch/rmanbkp/HOMEDB_FULL_%d_%T_%U';
restore controlfile from '/oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0oo3ltup_1_1';
}

alter database mount;

run
{
allocate channel c1 device type disk format '/oracle/arch/rmanbkp/HOMEDB_FULL_%d_%T_%U';
restore database from tag='rman_backfulcold_HOMEDB';
}