I don't think it is possible for you to restore only the backups that are created with format "%U" and this is what is your requirement if I've understood your question correctly. Normally RMAN backup pieces are created uniquely with the %U format. Look out for the most recent L0 backup and the corresponding L1 backups to restore it to have the most recent data.
Refer this on the steps of how to perform the restoration http://shivanandarao.wordpress.com/2012/04/19/duplicating-primary-database-to-a-new-host-without-connecting-to-the-primary-database-in-oracle-10g11g/
THe reason I have been asking is :
during my duplication to target db , until time, I kept getting the following error for every datafile:
RMAN-06023: no backup or copy of datafile 27 found to restore
RMAN-06023: no backup or copy of datafile 26 found to restore
RMAN-06023: no backup or copy of datafile 25 found to restore
RMAN-06023: no backup or copy of datafile 24 found to restore
RMAN-06023: no backup or copy of datafile 23 found to restore
RMAN-06023: no backup or copy of datafile 22 found to restore
However If I take set until time off, the duplicate worked fine.
How do I fix this issue?
It may be a reason where in the backups that you have do not fall under the time that you mention in the "set until time" and the backups might the most recent ones which causes the duplication to fail. With just the duplicate, RMAN duplicates the target database to auxiliary with the most recent data on the target database.
Also, might be that even if you have the backups that fall under the "set until time", they might have turned out to be obsolete. You can try cataloging the backups and then perform the duplication by setting the "until time" clause.
Catalog the backup pieces that have become obsolete and fall under the specified "until time" period on the target database and not on the auxiliary database. The target database would become aware of these backups and when you start the duplicate by connecting the target and the auxiliary instance (specifying "until time"), RMAN would look out for these backups and perform the duplication.
I am doing backup based dupliation. which means I made a rman backup first on the source, then move the backup pieces over to the auxilary, then do duplicate.
Here is the one from oracle doc:
Backup-Based Duplication Without a Target and a Recovery Catalog Connection: Example
In the variation of Example 24-1, shown in Example 24-5, RMAN does not use a TARGET connection to the source database or a CATALOG connection to a recovery catalog. All backup and copies necessary for duplication until November 11 of 2007 at 2:00 PM, including a control file backup or copy, have been placed under /prod_backups.
Example 24-5 Duplicating a Database Without a Target and Recovery Catalog Connection (Backup-Based)
DUPLICATE DATABASE TO dupdb
UNTIL TIME "TO_DATE('11/01/2007 14:00:00', 'MM/DD/YYYY HH24:MI:SS')"
BACKUP LOCATION '/prod_backups'
Note the following characteristics of Example 24-5:
The database name is not specified. By not specifying a database name with the DATABASE keyword, DUPLICATE obtains the database name and DBID from the backups. An error is displayed if backups for more than one database were found in the BACKUP LOCATION.
Use of the BACKUP LOCATION clause identifies the type of duplication as backup-based with neither a target connection nor recovery catalog.
The UNTIL TIME option is specified. It is the only UNTIL subclause permitted with the BACKUP LOCATION clause.
The NOFILENAMECHECK option check is specified because it is necessary when the duplicate database files use the same names as the source database files.
Not sure about your exact error, but if you want a RMAN refresh step by step I have one here :
http://www.visi.com/~mseberg/rman/restore_database_without_catalog.html Note 130724.1 RMAN-06023 Recover from Missing Datafile that is Never Backed Up Note 782317.1 Rman-06023 encountered during duplicate to point in time after datafile was added Note 135630.1 RMAN-6026 RMAN-6023 Restoring Database Note 779558.1 Cannot restore incremental backups using tag when datafile has been added
I have a rule :
If something is giving me too much resistance its time to try something else.
While the exact answer is interesting you have to ask yourself what your time is worth and balance that against getting the job now.
I have given you a tested second option. Please consider it.