RMAN duplicate database problems

[Chucks_k]

Hi all,

O/S: RHEL 3
DB: 10.2.0.3

We have been trying to create a clone of a VLDB (3TB) using the RMAN duplicate cmd but been having problems. On TEST server we had a smaller disk allocation for our ASM diskgroup, hence in our duplicate script we added the skip tablespace clause so that the database would fit into the diskgroup. Below is the script we used:

connect target sys/***@***
connect auxiliary /
duplicate target database to bof2test
db_file_name_convert ('+DGGROUP/bof2v2','+TEST/bof2test')
skip tablespace HLMUR_2008_03_03, CAP_2008_03_10, OVR_2008_03_03, OVR_2008_03_10, HLMR_2008_03_03, PLATE_2008_03_10, PLATE_2008_03_03, HLMUR_2008_03_10, CAP_2008_03_03, HLMR_2008_03_10, HLM_2008_03_03, HLM_2008_03_10, PLATE_2008_03_17, HLM_2008_03_17, HLMUR_2008_03_17, OVR_2008_03_17, HLMR_2008_03_17, CAP_2008_03_17, CAP_2008_03_24, OVR_2008_03_24, PLATE_2008_03_24, HLMR_2008_03_24, HLMUR_2008_03_24, HLM_2008_03_24;
exit

Now during the duplicate the test server diskgroup got exhausted. So we are able to allocate more diskspace and let the restore complete. I issued a recover database, this in hindsight was a mistake as i should have added the skip tablespace clause. Basically RMAN is complaining now of missing files (which were not in the orginal backup) e.g:

/oracle/product/10.2.0/dbs/UNNAMED00323 -> pertains to HLMUR_2008_03_17

These files pertain to those tablespaces in orginal skip clause. Now we are happy with losing all the data from the unamed files, i have tried offline drop which is fine but open resetlogs fails with the following error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+TEST/bof2test/datafile/system.325.649072473'

We just want to open the database with the current dataset. Can we do this?

Thanks in advance,
Chucks

[PAVB]

I don't think you are going to be able to open the database in the current scenario. Your second "recover database" -issued after space issue, most certainly iniitated the recovery of the offending datafiles hence, big troubles.

I can't see any scenario other than starting from scratch.

Also, if you are planning to drop the unwanted tablespaces on target database you may want to use the FOREVER option on "skip tablespace" command.

[Chucks_k]

Hi all,

I have managed to open the database via open resetlogs. The skipped files from the duplicate are showing up as MISSING. Now I tried to re-create the control file aftering editing the trace file to remove all the missing entries of databafiles. The control file was successfully created. I then opened the database normally without recovery. However upon checking the alert log i saw the 'missing' files where remembered in the data dictionary and added into the control file, which is obviously not what I wanted. Is there a way around this? Can i make the data dict 'forget' about those missing files and open the database without these missing files?

Thanks in advance,
Chucks

[hrishy]

Hi

Soory couldn't help myself from jumping in.

Chucks if you edited the controlfiles deleted the missing datafiles and then recreated the controlfile and used that controlfile to mount the database then in no way those files should appear in the datadictonary what you are talking is highly impossible as per my knowledge.

Can you please double check this?

regards
Hrishy

[Chucks_k]

Hi Hrishy,

Thanks for that, this is what i thought as well, i just realised could it be because i nomounted the database with the pfile that contained referenece to the old control file (this is all in ASM)?

Thanks in advance

Chucks

[Chucks_k]

Sorry, just to calrify the above : nomounted the database using ref to the old pfile to create the new controlfile.

[PAVB]

If I'm Oracle I wouldn't be happy if somebody tries to drop a tablespace containing some table partitions but not all of them.

[hrishy]

Hi

Try this
startup mount
alter database datafile 'complete path' offline drop;
alter database open
drop tablespace table-space-name including contents

regards
Hrishy

Check also if you fall in the condition mentioned by PAVB

regards
Hrishy

[Chucks_k]

Hi all,

Thanks for all your help. I managed to solve the problem by exchanging the partitions from the 'dud' tablespaces in temporary tables and also rebuild the the index partitions into another tablespace. I was then able to drop the missing tablespaces.

Thanks again.
Chucks