Dropping a datafile from test db (ORA-01111)

[Syltrem]

Hi

I copied (part of) a database into another db for a test environment.
Fact is, I only copied certain datafiles/tablespaces, created a new
controlfile to build a new database from these copied datafiles, and
performed a point-in-time recovery.

Of course the new database's alert log file contains messages like this one:
Tablespace 'HRDATA_IFB' #19 found in data dictionary,
but not in the controlfile. Adding to controlfile.

But that's ok.
Now that the db is open, I would like to get rid of those files, as I don't
need them in the controlfile (or anywhere at all).

SQL> drop tablespace HRDATA_IFB including contents;
drop tablespace HRDATA_IFB including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01135: file 10 accessed for DML/query is offline
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.'

Even after an
alter database datafile 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.' offline
drop;
I still cannot drop the tablespace.

What am I doing wrong, or can it be done at all ?
I need to create the new db with only a subset of the original db's
tablespaces.

Thanks

[Turkbear]

Post by Syltrem
Hi
I copied (part of) a database into another db for a test environment.
Fact is, I only copied certain datafiles/tablespaces, created a new
controlfile to build a new database from these copied datafiles, and
performed a point-in-time recovery.
Tablespace 'HRDATA_IFB' #19 found in data dictionary,
but not in the controlfile. Adding to controlfile.
But that's ok.
Now that the db is open, I would like to get rid of those files, as I don't
need them in the controlfile (or anywhere at all).
SQL> drop tablespace HRDATA_IFB including contents;
drop tablespace HRDATA_IFB including contents
*
ORA-00604: error occurred at recursive SQL level 2
ORA-01135: file 10 accessed for DML/query is offline
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.'
Even after an
alter database datafile 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.' offline
drop;
I still cannot drop the tablespace.
What am I doing wrong, or can it be done at all ?
I need to create the new db with only a subset of the original db's
tablespaces.
Thanks

You did most things wrong, IMHO, or at least the hard way - think of data not tablespaces...You do not want, or need, the
tablespaces, just the data that the tables. etc using that tablespace have..Unless you created them as transportable
tablespaces, copying them is not a good idea ( and copying only a part of the associated data files is even a worse idea).

Start Over..

Export just the tables you want from your original database and import them into the new one..

Precreate the tablespaces using the same name as the originals, and size them how you like..

The import will attempt to place the table data into the same tablespace name as it was exported from.

[Syltrem]

I copied ALL of the datafiles for the tablespaces I need in the cloned db.

I can't use EXPort, or trsnaportable tables.
I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
December, so what I want is to clone the db, then roll it forward until the
"cut" time.
--
Syltrem

OpenVMS 7.3-1 + Oracle 8.1.7.4
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---

Post by Turkbear

Post by Syltrem
Hi
I copied (part of) a database into another db for a test environment.
Fact is, I only copied certain datafiles/tablespaces, created a new
controlfile to build a new database from these copied datafiles, and
performed a point-in-time recovery.
Tablespace 'HRDATA_IFB' #19 found in data dictionary,
but not in the controlfile. Adding to controlfile.
But that's ok.
Now that the db is open, I would like to get rid of those files, as I don't
need them in the controlfile (or anywhere at all).
SQL> drop tablespace HRDATA_IFB including contents;
drop tablespace HRDATA_IFB including contents
*
ORA-00604: error occurred at recursive SQL level 2
ORA-01135: file 10 accessed for DML/query is offline
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.'
Even after an
alter database datafile 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.' offline
drop;
I still cannot drop the tablespace.
What am I doing wrong, or can it be done at all ?
I need to create the new db with only a subset of the original db's
tablespaces.
Thanks

You did most things wrong, IMHO, or at least the hard way - think of data

not tablespaces...You do not want, or need, the

Post by Turkbear
tablespaces, just the data that the tables. etc using that tablespace

have..Unless you created them as transportable

Post by Turkbear
tablespaces, copying them is not a good idea ( and copying only a part of

the associated data files is even a worse idea).

Post by Turkbear
Start Over..
Export just the tables you want from your original database and import

them into the new one..

Post by Turkbear
Precreate the tablespaces using the same name as the originals, and size them how you like..
The import will attempt to place the table data into the same tablespace

name as it was exported from.

[Steve Howard]

Post by Syltrem
I copied ALL of the datafiles for the tablespaces I need in the cloned db.
I can't use EXPort, or trsnaportable tables.
I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
December, so what I want is to clone the db, then roll it forward until the
"cut" time.
--
Syltrem

Maybe I am missing something, but why not hot clone your database
sometime before midnight on the 1st, and just apply redo to it until
midnight. At that time, archive the current online redo log in the
source database, and apply this newly archived log to the clone,
cancel recovery, and you are current...maybe not in one second, but
pretty close.

You could then drop whatever you wanted/didn't need.

Regards,

Steve

[Syltrem]

Post by Steve Howard

Post by Syltrem
I copied ALL of the datafiles for the tablespaces I need in the cloned db.
I can't use EXPort, or trsnaportable tables.
I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
December, so what I want is to clone the db, then roll it forward until the
"cut" time.
--
Syltrem

Maybe I am missing something, but why not hot clone your database
sometime before midnight on the 1st, and just apply redo to it until
midnight. At that time, archive the current online redo log in the
source database, and apply this newly archived log to the clone,
cancel recovery, and you are current...maybe not in one second, but
pretty close.
You could then drop whatever you wanted/didn't need.
Regards,
Steve

Hi

That's exactly what I want to do except:
a) The cloned db must have a different name (because on same machine)
b) I do not whish to clone all tablespaces. Don't even have sufficient disk
space for that.

That's why it gets complicated.
a) I have to do a CREATE CONTROLFILE and only specifiy the datafiles I moved
over
b) Oracle will not let me drop the unrestored (and offline) tablespaces
after the recovery is finished and the db open. And BEFORE it's open, it
just doesn't see the missing files yet. Only when I do OPEN does it realize
the dictionary know about files that do not exist in the controlfile. If
fixes that but I still cannot drop them.

Syltrem

[Joel Garry]

Post by Syltrem

Post by Steve Howard

Post by Syltrem
I copied ALL of the datafiles for the tablespaces I need in the cloned

db.

Post by Steve Howard

Post by Syltrem
I can't use EXPort, or trsnaportable tables.
I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
December, so what I want is to clone the db, then roll it forward until

the

Post by Steve Howard

Post by Syltrem
"cut" time.
--
Syltrem

Maybe I am missing something, but why not hot clone your database
sometime before midnight on the 1st, and just apply redo to it until
midnight. At that time, archive the current online redo log in the
source database, and apply this newly archived log to the clone,
cancel recovery, and you are current...maybe not in one second, but
pretty close.
You could then drop whatever you wanted/didn't need.
Regards,
Steve

Hi
a) The cloned db must have a different name (because on same machine)
b) I do not whish to clone all tablespaces. Don't even have sufficient disk
space for that.
That's why it gets complicated.
a) I have to do a CREATE CONTROLFILE and only specifiy the datafiles I moved
over
b) Oracle will not let me drop the unrestored (and offline) tablespaces
after the recovery is finished and the db open. And BEFORE it's open, it
just doesn't see the missing files yet. Only when I do OPEN does it realize
the dictionary know about files that do not exist in the controlfile. If
fixes that but I still cannot drop them.
Syltrem

You won't be able to roll forward without all tablespaces (unless the
ones you drop are read only).

I haven't totally thought it through, but it sounds like you need to
do something like:

create empty database
CTAS all desired tables
rebuild all referential integrity
rebuild indices
analyse everything.
merge update or somehow get records after CTAS to cutoff. (perhaps
get flat files of tables at ctas and cutoff time, use posix diff and
sort to get sqlloader-able files).
analyse again.

What happened when you tried to recreate empty files and then drop
them?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20041112/news_1b12intel.html

[Syltrem]

It does work. Just have to roll forward to the time I want.
I don't have referential integrity between tables in the tablespaces I keep
and those in the tablespaces I drop.

I'll do another test this weekend, and make sure I do have changes occurring
in the tablespace I plan to drop, after the backup but before the recovery
until time.
--
Syltrem

OpenVMS 7.3-1 + Oracle 8.1.7.4
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---

Post by Joel Garry

Post by Syltrem

Post by Steve Howard

Post by Syltrem
I copied ALL of the datafiles for the tablespaces I need in the cloned

db.

Post by Steve Howard

Post by Syltrem
I can't use EXPort, or trsnaportable tables.
I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
December, so what I want is to clone the db, then roll it forward until

the

Post by Steve Howard

Post by Syltrem
"cut" time.
--
Syltrem

Maybe I am missing something, but why not hot clone your database
sometime before midnight on the 1st, and just apply redo to it until
midnight. At that time, archive the current online redo log in the
source database, and apply this newly archived log to the clone,
cancel recovery, and you are current...maybe not in one second, but
pretty close.
You could then drop whatever you wanted/didn't need.
Regards,
Steve

Hi
a) The cloned db must have a different name (because on same machine)
b) I do not whish to clone all tablespaces. Don't even have sufficient disk
space for that.
That's why it gets complicated.
a) I have to do a CREATE CONTROLFILE and only specifiy the datafiles I moved
over
b) Oracle will not let me drop the unrestored (and offline) tablespaces
after the recovery is finished and the db open. And BEFORE it's open, it
just doesn't see the missing files yet. Only when I do OPEN does it realize
the dictionary know about files that do not exist in the controlfile. If
fixes that but I still cannot drop them.
Syltrem

You won't be able to roll forward without all tablespaces (unless the
ones you drop are read only).
I haven't totally thought it through, but it sounds like you need to
create empty database
CTAS all desired tables
rebuild all referential integrity
rebuild indices
analyse everything.
merge update or somehow get records after CTAS to cutoff. (perhaps
get flat files of tables at ctas and cutoff time, use posix diff and
sort to get sqlloader-able files).
analyse again.
What happened when you tried to recreate empty files and then drop
them?
jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20041112/news_1b12intel.html

[Syltrem]

Post by Joel Garry
You won't be able to roll forward without all tablespaces (unless the
ones you drop are read only).

Wrong !
Oracle lets me and does not complain. Actually, I have used this way of
recovering a single table 2-3 times already, by restoring just the
tablespace I needed (along with SYSTEM and ROLLBACK). But on these
occasions, after EXPorting the required table I just dropped the restored
db.
This time, I need to keep it, clean it up and keep it for production (it's a
long story).

I just made a test with tons of updates in the datafiles I did not copy over
(updates committed after the backup), and I can recover to a time later than
the commit time for those updates, open the database and drop those
unrestored tablespaces.

All is fine, and I can have the cloned db containing transactions up to a
certain time, and have it ready for production 1 minute after this time.
Exactly what I needed.

Syltrem

Post by Joel Garry
I haven't totally thought it through, but it sounds like you need to
create empty database
CTAS all desired tables
rebuild all referential integrity
rebuild indices
analyse everything.
merge update or somehow get records after CTAS to cutoff. (perhaps
get flat files of tables at ctas and cutoff time, use posix diff and
sort to get sqlloader-able files).
analyse again.
What happened when you tried to recreate empty files and then drop
them?
jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20041112/news_1b12intel.html

[Joel Garry]

Post by Syltrem
I copied ALL of the datafiles for the tablespaces I need in the cloned db.
I can't use EXPort, or trsnaportable tables.
I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
December, so what I want is to clone the db, then roll it forward until the
"cut" time.

Well then, use RMAN to set up a standby or duplicate.

Note: 109979.1 is pretty strange.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20041111/news_1b11oracle.html

[Syltrem]

I got it (for those who want to know)

The (somewhat misleading) message, when I tried a DROP TABLESPACE, said that
file `MISSING` file id nn, had to be recovered.
Since the name of the file did not really help here, I figured it belonged
to the tablespace to be dropped. But instead it belonged to the temp
tablespace, which I usually recreate right after a recovery, what I forgot
this time as I went on and directly tried dropping the unnecessary
tablespaces.

After recreating a temp tablespace properly I could drop all the tablespaces
that were not required in the cloned db. And all is well.

Thanks to those who responded.

Syltrem

Post by Joel Garry

Post by Syltrem
I copied ALL of the datafiles for the tablespaces I need in the cloned db.
I can't use EXPort, or trsnaportable tables.
I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
December, so what I want is to clone the db, then roll it forward until the
"cut" time.

Well then, use RMAN to set up a standby or duplicate.
Note: 109979.1 is pretty strange.
jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20041111/news_1b11oracle.html

[Joel Garry]

Post by Syltrem
Hi
I copied (part of) a database into another db for a test environment.
Fact is, I only copied certain datafiles/tablespaces, created a new
controlfile to build a new database from these copied datafiles, and
performed a point-in-time recovery.
Tablespace 'HRDATA_IFB' #19 found in data dictionary,
but not in the controlfile. Adding to controlfile.
But that's ok.
Now that the db is open, I would like to get rid of those files, as I don't
need them in the controlfile (or anywhere at all).

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=1060605.6&p_database_id=NOT
might give a clue on how to recreate so you can drop.

If you have the situation where all of a schema that you want to
propagate is in a [small number of] tablespace, look into the
transportable option.

jg
--
@home.com is bogus. "This=20e-mail=20is=20intended=20solely=20for=20the=20person=20or=20entity=20=
to=20which=20it=20is=20addressed=20and=20may=20contain=20confidential=20an=
d/or=20privileged=20information.=20Any=20review,=20dissemination,=20copyin=
g,=20printing=20or=20other=20use=20of=20this=20e-mail=20by=20persons=20or=20=
entities=20other=20than=20the=20addressee=20is=20prohibited.=20If=20you=20=
have=20received=20this=20e-mail=20in=20error,=20please=20contact=20the=20s=
ender=20immediately=20and=20delete=20the=20material.=20" - oracle-l