After a number of database switches into ASM (which i discussed here )it was inevitable that i would try and switch a database into ASM that was already in ASM (too many similar sounding database names).
The first stage of the switch process is just to take an image copy of the database and store it in ASM,
So you just run
backup as copy database format '+DATA';
So if the DB is already in ASM when you run that, the command still works - you just end up with another complete copy of the database inside ASM. Now i didn't want this as it's eating up a lot of space and my backups are generally not image ones but rman backupsets on tape.
So now I've created this additional copy - how do i get rid of it to free up space in ASM?
Well in a journey through little used commands here is what i did.
First up i need to see what full (image) copies we have ( i removed a lot of the output here as it is just too much to paste)
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
36 1 A 11-NOV-14 8697152184577 11-NOV-14
Name: +DATA/DB_sw/datafile/system.407.863340131
Tag: TAG20141111T084112
21 1 A 27-OCT-14 8697151276482 27-OCT-14
Name: /oracle/DB/oradata/DB_SW/datafile/o1_mf_system_7bhnffbq_.dbf
Now what we have here is 2 complete copies of the database mentioned (I'm only displaying the system datafile but it was all there). One copy is the old copy from the filesystem before i switched into ASM (which has since been removed from disk - but the db doesn't know that). The other is the copy i just created. The copy i just created has the tag TAG20141111T084112 whereas the original filesystem copy has nothing at all.
I actually wanted to get rid of both complete copies so i could have just run
RMAN> delete copy of database;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=592 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=685 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=104 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
36 1 A 11-NOV-14 8697152184577 11-NOV-14
Name: +DATA/DB_sw/datafile/system.407.863340131
Tag: TAG20141111T084112
21 1 A 27-OCT-14 8697151276482 27-OCT-14
Name: /oracle/DB/oradata/DB_SW/datafile/o1_mf_system_7bhnffbq_.dbf
Do you really want to delete the above objects (enter YES or NO)? NO
But i said no as i was interested to see how i could just delete one of the copies (which could be something you wanted to do). So how could that be done?
Deleting the tagged one is easy enough
RMAN> delete copy of database tag "TAG20141111T084112";
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=592 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=685 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=104 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
36 1 A 11-NOV-14 8697152184577 11-NOV-14
Name: +DATA/DB_sw/datafile/system.407.863340131
Tag: TAG20141111T084112
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=+DATA/DB_sw/datafile/system.407.863340131 RECID=36 STAMP=863340143
Deleted 10 objects
What if i wanted to delete the other one only though?
With a bit of logic we can use the fact of when it was created and do this
RMAN> delete copy of database completed before "sysdate -7";
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=592 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=685 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=104 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
21 1 A 27-OCT-14 8697151276482 27-OCT-14
Name: /oracle/ETPOP/oradata/ETPOP_SW/datafile/o1_mf_system_7bhnffbq_.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
RMAN-06207: WARNING: 10 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy /oracle/ETPOP/oradata/ETPOP_SW/datafile/o1_mf_system_7bhnffbq_.dbf
Now it's complaining as i already moved it from the filesystem without telling Oracle
Now i could mess about here and crosscheck etc but i don't want to do that so i just force it
RMAN> delete force copy of database completed before "sysdate -7";
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=592 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=685 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=104 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
21 1 A 27-OCT-14 8697151276482 27-OCT-14
Name: /oracle/DB/oradata/DB_SW/datafile/o1_mf_system_7bhnffbq_.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/oracle/DB/oradata/DB_SW/datafile/o1_mf_system_7bhnffbq_.dbf RECID=21 STAMP=862089142
Deleted 10 objects
And there you go - a lesson in rarely used rman commands......now back to switching the rest of the databases - making sure to double check the ORACLE_SID.......
WtrorcuVcred-pe-Paterson Kevin Islam https://wakelet.com/wake/Y7Q3fIDEBwCgefAIOfUgv
ReplyDeletehuntsymwatchdrum