RMAN duplicate database problems

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:

Code:

---

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:

Code:

---

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

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.

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

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

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

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

Chucks

can you see tablespaces corresponding to those datafiles after re-creating your your control file. If yes then its worth trying offline drop now on those tablespaces.

Hi Hrishy,

I just re-did the procedure with the same results. Here is what i did:

1/ Edited the init.ora so that it points just to the ASM disk group:

Code:

---

*.control_files='+TEST/'

---

2/ Shutdown the instance and nomounted with the edited pfile.
3/ Ran the CREATE_CONTROL file script without the MISSING datafiles.
4/ Control file created and opened the db

Code:

---

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @ctl_recreate.sql

Control file created.

SQL> alter database open;

Database altered.

SQL> exit

SQL> show parameter control

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer    30
control_files                        string      +TEST/bof2test/controlfile/cur
                                                rent.1371.651057637

---

5/ In the alert log i saw this when the database was opening:

Code:

---

Tablespace 'HLMUR_2008_03_03' #310 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'CAP_2008_03_10' #311 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OVR_2008_03_03' #312 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OVR_2008_03_10' #313 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'HLMR_2008_03_03' #314 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'PLATE_2008_03_10' #315 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'PLATE_2008_03_03' #316 found in data dictionary,
but not in the controlfile. Adding to controlfile.

---

6/ Now its the same as before, i can still see the missing datafiles when i do select name from v$datafile.

Any thoughts on this, is my procedure wrong?

Thanks in advance,
Chucks

Hi Parag,

The problem here is that these tablespaces contain partitions of a table. Hence i tried this after what i did below:

Code:

---

SQL> select tablespace_name from dba_data_files where file_name='/oracle/product/10.2.0/dbs/MISSING00310';

TABLESPACE_NAME
------------------------------
HLMUR_2008_03_03


SQL> select partition_name,table_name from dba_tab_partitions where tablespace_name='HLMUR_2008_03_03';

PARTITION_NAME                TABLE_NAME
------------------------------ ------------------------------
HLMUR_2008_03_09              HOTLISTMATCHEDUSERRESPONSE
HLMUR_2008_03_08              HOTLISTMATCHEDUSERRESPONSE
HLMUR_2008_03_07              HOTLISTMATCHEDUSERRESPONSE
HLMUR_2008_03_06              HOTLISTMATCHEDUSERRESPONSE
HLMUR_2008_03_05              HOTLISTMATCHEDUSERRESPONSE
HLMUR_2008_03_04              HOTLISTMATCHEDUSERRESPONSE
HLMUR_2008_03_03              HOTLISTMATCHEDUSERRESPONSE

7 rows selected.

SQL> alter table BOF2.HOTLISTMATCHEDUSERRESPONSE drop partition HLMUR_2008_03_09;
alter table BOF2.HOTLISTMATCHEDUSERRESPONSE drop partition HLMUR_2008_03_09
*
ERROR at line 1:
ORA-00376: file 310 cannot be read at this time
ORA-01111: name for data file 310 is unknown - rename to correct file
ORA-01110: data file 310: '/oracle/product/10.2.0/dbs/MISSING00310'

---

This will obviously not work due the error above as its a missing file.
I hope i dont have to restart the whole duplicate process as its going to take a long time!

Thanks in advance

just a thinking ...

can you try
alter tablespace HLMUR_2008_03_03 offline immediate; and then try and drop it including contents.

Hi Chucks

Try this

Mount the database
drop the tablespace including contents
open the database

regards
Hrishy

Hi Hrishy,

I didnt think you could drop a tablespace whilst the db is in mount state. I tried it anyway:

Code:

---

SQL> drop tablespace HLMUR_2008_03_03 including contents;
drop tablespace HLMUR_2008_03_03 including contents
*
ERROR at line 1:
ORA-01109: database not open

---

Any thing I have done wrong?

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.

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

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