2016/04/14

how to clear copy/multiple restored datafile copies

Let’s say you tried a restore , some files have been restored but operation has interrupted.
All  files in ASM and generated names differ during eact restore attempt. Or you copied files manually via asmcmd ,
but you are not sure which files in use, which ones are interrupted restore sessions ruin ?
A clear way is to catalog copies , and manage files via rman. 
Othervise checkin all files one by one is high risk and takes much effort.
Let’s do it ..


Facts:
1- RMAN treats all user-managed backups as image copies. Note that during cataloging, RMAN does not check whether the file was correctly copied by the operating system utility: it just checks the header.
2- You cannot use CATALOG to catalog a file that belongs to a different database.


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ORCLDG/orcl/datafile/system.256.900898563
+ORCLDG/orcl/datafile/sysaux.257.900898563
+ORCLDG/orcl/datafile/undotbs1.258.900898563
+ORCLDG/orcl/datafile/users.259.900898563
+ORCLDG/orcl/datafile/tbs1.270.900911509
+ORCLDG/orcl/datafile/tbs2.271.900911533
+ORCLDG/orcl/datafile/tbs2.273.900937577
+ORCLDG/orcl/datafile/tbs1.274.900937597
+ORCLDG/orcl/datafile/tbstelco.314.907164167

SQL> shu immediate;
SQL> startup mount ;    

RMAN> backup as copy datafile 1;   -- datafile image copy via rman

Starting backup at 14.04.2016 23.11.54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+ORCLDG/orcl/datafile/system.256.900898563
output file name=+ORCLDG/orcl/datafile/system.322.909184317 tag=TAG20160414T231155 RECID=1 STAMP=909184386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+ORCLDG/orcl/controlfile/backup.324.909184393 tag=TAG20160414T231155 RECID=2 STAMP=909184406
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14.04.2016 23.13.39
channel ORA_DISK_1: finished piece 1 at 14.04.2016 23.13.46
piece handle=+ORCLDG/orcl/backupset/2016_04_14/nnsnf0_tag20160414t231155_0.325.909184421 tag=TAG20160414T231155 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 14.04.2016 23.13.46

RMAN>

--- manual copy ; controlfile is not aware of operation (simulation for interrupted restore .. )
ASMCMD [+ORCLDG/orcl/datafile] > cp SYSTEM.256.900898563 system.copybytamer
copying +ORCLDG/orcl/datafile/SYSTEM.256.900898563 -> +ORCLDG/orcl/datafile/system.copybytamer


--- mis usage , everytime I make that mistake J
RMAN> catalog start with '+ORCLDG/orcl/*';    à do not use like in ls -* ; syntax error

searching for all files that match the pattern +ORCLDG/orcl/*
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 04/14/2016 23:17:21
ORA-00600: internal error code, arguments: [krbmosptr_not_osm], [+ORCLDG/orcl/*], [], [], [], [], [], [], [], [], [], []
ORA-15122: ASM file name '+ORCLDG/orcl/*' contains an invalid file number

RMAN> catalog start with '+ORCLDG/orcl';    -- correct usage

searching for all files that match the pattern +ORCLDG/orcl/

List of Files Unknown to the Database
=====================================
File Name: +orcldg/ORCL/spfileORCL.ora
File Name: +orcldg/ORCL/DATAFILE/system.copybytamer

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +orcldg/ORCL/DATAFILE/system.copybytamer

RMAN> list copy of datafile 1;

List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time           
------- ---- - ------------------- ---------- -------------------
3       1    A 14.04.2016 23.19.52 1754346    14.04.2016 23.09.26
        Name: +ORCLDG/orcl/datafile/system.copybytamer

1       1    A 14.04.2016 23.13.06 1754346    14.04.2016 23.09.26
        Name: +ORCLDG/orcl/datafile/system.322.909184317
        Tag: TAG20160414T231155

You can delete whole copies or defined ones via name or tag.
RMAN>  delete copy of datafile 1 tag=TAG20160414T231155;
RMAN> delete copy of datafile 1  like '+ORCLDG/orcl/datafile/system.copybytamer%';
RMAN>  delete copy of datafile 1 ;