Export Direct=Y

I am currently exporting an Oracle7.3 database every 6 hours. The .dmp file is now at 1.87gigs and takes an hour to export. AND, causes a performance hit to my ssystem.

While reviewing my backup and recovery manuals, I noticed that you can set DIRECT=Y on the export t. THis direct path option is suppose to be significantly faster and does not compete with other resources of the Instance.

It also says this is useful for exporting large large datbases.

Can anyone confirm this before I start testing?

thanks

It's true. Setting direct=true bypasses the sga, so it should be faster.

Frank

Seems to me like you are expecting a faster EXPORT , in turn the performance.

Would you post the parfile , which you are using to export the full DB ???

Genarally Huge Buffer value makes the export faster as well.

Ditect=Y , will ceratinly improve the speed of export , by bypassing the SQL communication Layer. But it is having some issues...

And its advisable to have the export dump file on a separate disk , where there is less or no I/O activity...

Here is the export parameters I am using.
file=nash%1.dmp log=nash%1.log full=y consistent=y

I am taking the default buffer values.

What issues are you seeing with DIRECT?

We are running a raid 5 and I am exporting the file to a seperate logical drive than my data and index files. Only thing on this logical drive is my exports and some historical files.

My box is running 2, p3-400's and i have about 120 users. I am exporting at 10AM which is a busy time, but not our busiest times. We do have no hardware and are going to be upgrading to 9i on it. Just waiting on the vendor for the app.

I just need to reduce the CPU hit and the time it takes to do the export.

If you choose conventional mode of export , you will have set huge buffer size than the default value eg: 8192000/4096000

Since you have CONSISTENT=Y and having high DML activity, it will be slow .

If you choose the DIRECT mode: ( DIRECT=Y ), as you asked me, you have the following limitations:

1. NO Interactive mode with DIRECT=Y
2. You Can not export in diect mode with TRANSPORT_TABLESPACES=Y
3. you could not use direct path Export for tables containing objects and LOBs ( In 8i + , uses conventional path )
4. Parametaer QUERY can not be used
5. BUFFER parameter has no effect on direct mode
6. Character set conversion ( session's character set should match with the database character set ).


As you are Exporting the FULL db , you can include the RECORDLENGTH=32*1024 ( 32KB ) along with DIRECT=Y,
This should give better perfromance.

And also DB_BLOCK_SIZE will also matter in export. Larger is the better.


Hope this helps !!!!!

I don't know what the default setting for the buffer is. according to my documentaion, its system dependant and is ususally set to a high value(>64000). are you saying I should include a buffer setting in my line of 8megs?

Tried the direct export and got the characeter set error. Trying to figure out now how to work around that.

how does the recordlength help?

items 1-4 of the limitations are not applicable in my 7.3.4 system. Only the recordset is an issue and I have to figure it out. The error I got was exp00041: Export done in serve's US7SASCII, different from user's character set WE8ISO8859P1. Which user character set?

For the charachter set errors you may want to try...

When there are character set errors try using
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export NLS_LANG
from a Unix prompt.


My understanding og the BUFFER parameter is that it should be used in conjunction with COMMIT, Ex..
BUFFER=4000000
COMMIT=Y

With your buffer at 4 million bytes you will be commiting every 4 megs (roughly). This is better than the oracle default which is to try and import the entire table before a commit. You will also run the risk of blowing out your rollback segs, or tablespace if it is too small.

Hop this helps
MH

The main question I see here is: Why on earth would someone want to take full export of a database every six hours? And with CONSISTENT=Y on top of that!!??

If this is your main mean of backup then your backup-recovery scenario simply sucks. If it is not, then why would you do it 4 times a day?

testing the speed of teh direct now.
To fix the error, I went into my Windows system environment and set two variables nls_lang=american, nls_nchar=US7ASCII.

I used these values because a select * from sys.prop$ showed them to be the databases current character set info.

Once I did that, the direct=y worked and database is exporting as I type this.

Interested to see the time comparisons. Granted, its on a new box(4, p3-700's) and I am the only user. Still..

Quote:

---

Originally posted by Mr.Hanky

COMMIT=Y

With your buffer at 4 million bytes you will be commiting every 4 megs (roughly). This is better than the oracle default which is to try and import the entire table before a commit. You will also run the risk of blowing out your rollback segs, or tablespace if it is too small.

---

Of course, you realize Mr.H is taking about import here and not export.

And, if we check the docs ( http://technet.oracle.com/docs/produ...ch01.htm#46045 ) we notice that BUFFER does not apply for DIRECT export.

Quote:

---

Originally posted by jmodic
The main question I see here is: Why on earth would someone want to take full export of a database every six hours? And with CONSISTENT=Y on top of that!!??

If this is your main mean of backup then your backup-recovery scenario simply sucks. If it is not, then why would you do it 4 times a day?

---

Now, now jmodic. You've got to learn to roll your eyes, sigh, and move on.

Quote:

---

Originally posted by marist89

Quote:

---

Originally posted by Mr.Hanky

COMMIT=Y

With your buffer at 4 million bytes you will be commiting every 4 megs (roughly). This is better than the oracle default which is to try and import the entire table before a commit. You will also run the risk of blowing out your rollback segs, or tablespace if it is too small.

---

Of course, you realize Mr.H is taking about import here and not export.

And, if we check the docs ( http://technet.oracle.com/docs/produ...ch01.htm#46045 ) we notice that BUFFER does not apply for DIRECT export.

---

I really have to break this habit of mine, it is a re-occuring problem. At least he will have a head start on the import.

MH

WOW

Okay, I have done some initial testing of this. Here is my results.

Machine Dell PowerEdge 8500, 4-p3'700s. Have opitmezed disk structures, multiple raid0+1's, basically built the ultimate dream system we theorized in our Oracle classes. I am only user on box at the moement.

Export file size, approx 1.8gigs

Did conventional export. consistent=y, full=y all other defaults take. Exp time was 24 minutes.

Did Direct=y export, taking defaults, consistent=y, full=y
Exp time was 3 minutes. yes, 3 minutes.

Wow..think I will be modifying my current production backups to do direct vs conventional.

Thoughts.

Quote:

---

Originally posted by DcsoBob
basically built the ultimate dream system

---

wet dream: http://www.sun.com/servers/highend/sunfire15k/

"Why the hell would I do a full export" Because my BOSS told me that we will do it that way. It is NOT our only backup scheme, it is just one piece. We are using autoarchive, we do hot backups, coldbackups, etc. The logical exports are just a piece of our scheme.

I am fully understanding the concept of archive logs, redo logs, full recoverys, etc, etc. AND, I explained these concepts in full detail to my boss.

I am a 24x7 jail. Our system has to be there to assist in inmate control. And, since we have been hit with one tornado already, we have to be prepared to go elsewhere. With an export, all I need is a box and oracle. up time is minimal.

And yes, the exports are copied away to a new location after every export.

Yes, once we get our upgrade to our system done, replication on a different geographcially seperated server will happen, thus negating the need for 6 hour exports.

It all boils down to this Mr. Moderator. When the boss says do it, you do it. Or find new work.

Thank you for the helpful input. One day, I hope to be an Oracle expert such as yourself. Until then, I will just keep learning.

Quote:

---

Originally posted by DcsoBob
Wow..think I will be modifying my current production backups to do direct vs conventional.

---

http://www.theunholytrinity.org/crac...ackeye/lol.gif

Hanky,
Would you explian how commit, is useful in export ,

i didnt find this parameter for exp in my DB, just kidding , may be you are confused...


DscoBOB,

As Hanky suggested you have to set the NLS_LANG ( this is OS environmental variable )

Now you query the database as
select * from v$nls_parameters;

and check for character set.. with that, now you have to do

set NLS_LANG=american_america.

To my understanding the RECORDLENGTH parameter will be used in conjunction with DIRECT=Y, as this specifies the size of the I/O buffer.. ( multiples of OS file system block size )
This improves the perfomrnace by working

Hope this time you will succeed.. And why dont you consider
Jurij Modic ideas ??/

I would be happy to consider Mr. mordics thoughts, if he had given one. His only input was criticism, not a suggestion.

As I said, I have a reason for what I am doing. "Directed" to do it.

I will be looking at the recordlength suggestion.

I did review the oracle docs refering to the limitations of DIRECT vs Conventional.

I did get the export to work after modifying the NLS parameter in my winodws env variables to do it. Now, just have to make sure that I can import the export!

I would like to thank all those that gave suggestions. It has helped me considerably.

Obviously I deeply offended you, Mr. Junior Member, Although it was not my intention to do so. From your initial post I concluded that export was your main backup scheme and that you might not be aware of the consequences. Obviously you are, however your boss is not, and you failed to explain to him why taking full exp 4 times a day is idiotic.

You say you are 7x24 shop. OK, but there are couple of things that contradict this. For example, your shop obviously can afford to loose 6 hours worth of data! And you said you also do cold backlups - how can you do them in 7x24 schedule? And in the case of failure, if you are relying on exports obviously you can afford to be down for about half a day (appart from loosing 6 hours data). If your (conventional) export is taking an hour to export, then it will take at least 3 hours to import it. And before that you will have to create the whole new database to import into. This might take another 3 hours or even more.

You say you are also doing hot backups, and that is the right way to go in 7x24, but if this is so, I realy can't understand the reason for doing full exp 4 times a day. Your boss might thing he has his valid reasons for this, but he definitelly is wrong, and you should put more effort to make him understand this.

Once again - taking full exp every 6 hours is ludicrous if you ask me. I can't find any valid reason for doing it in 7x24 environment. And I can find strong reasons aginst doing it. But, hey, it's your database.

Not deeply offended, was just having a rather bad day that day, and your post was the last straw. I look to this forum for help. Normally, flames just roll off my back; I don't take them personally, but .....

While we are a 24x7 shop, we do have a small maint window when things are slow. Thats 5-9am on a Sunday morning, the slowest time in our jail. So, we can go down once per month for cold backups, with nightly hot-backups. For legal reasons, data has to be available. Should our system go down, we go to manual operations. When we return, the officers will update the system with the manual records where possible.

I should also explain that while the export is large, the VAST bulk of the export is audit data for the application. I am exporting 1.8 gigs at this time. However, one table in my system is proably 1.6 gigs of that. It is currently at 9mil records. Its a table used to log user actions within the application. again, legal requirements mean we need to show who has done what with an inmate. I will be working this week to initate an archiving scheme for this table that will reduce this table down to about 60days worth of data. That will be about 1.5 million rows. That should drop the export down to about 3 or 400megs. which does not strain my system.

I cannot agree more that the exports are silly. However, someone prior to me ( pretty sure it was the vendor) convinced my boss that it was a safe form of backup and easy to recover from. Since our system went live a shade over a year ago, I have been in full development/bug fix mode. I have YET to be able to sit down and really adminster this system. Between righting pl/sql code to make the system do things that were not developed (because our users want more and we have limited budget!! ), app version testing, etc, etc, I have just not had the time to show the boss that the exports are unwarranted. Until I can setup a test server, copy the db and archive logs over, and show her it works, she will be unconvinced and continues to insist on it.

You think 6 hours as bad. When I was first hired and taking my first oracle classes, she had me doing 4 hours. It took me about 8mths to get that to 6 hours. I think I have about got her talked into 12. While I have not convinced her yet, I am slowly whittling her down.

SO, I continue my fight. As to having done enough to convince, I have argued the issue to the point of my boss saying -- I will not change this plan until such time as you can prove to me that the archives work as advertised. Don't bring it up again.-- Figure that is as far as I go until I can show her with timing test, etc.

Until then, I have to do these exports, so I am attempting to reduce the load on my system and users. It appears that DIRECT is the way to go.

Given the fact that you had to do these exports as well, what would you recommend.

Quote:

---

Originally posted by DcsoBob
Until then, I have to do these exports, so I am attempting to reduce the load on my system and users. It appears that DIRECT is the way to go.

Given the fact that you had to do these exports as well, what would you recommend.

---

If you realy have no choice but to bend a head and perform thosefrequent exports, then yes, DIRECT is probably the way to go. I would think again about CONSISTENT=Y, though. If you are sure your export must represent a snaphot of your database in a point of time *as a whole*, then CONSISTENT=Y is a must. But in that case make sure you have properly sized rollback segments, so that one day your export won't blow up with ORA-1555. On the other hand, if you realy are only interested in the contents of your application audit table (and not directly related to other exported tables within the same point in time), you should use CONSISTENT=N. It will be faster and much less ORA-1555 error prone.

However my main point still stands. I'm 100% sure that your boss does not realise how useless those frequent exports realy are. If I were you I would put all my efforts to enlighten here about that! You are saying "legal requirements mean we need to show who has done what with an inmate". That's fine, but how can you asure that with exports???? Only physical backup with archived logs can guaranty this, exports are useless here! If you are relying on exports you allway live with the danger to loose 6 hours worth of data in that audit table. Dangerous, from the legal point of view ;)! And let your boss know that exports + archlogs simply can't be used together for any kind of recovery - I suspect she isn't aware of that.

What can I said about your boss being suspicious about "that the archives work as advertised"? Only that she is totaly ignorant about Oracle database basic conceptsd and that she is making fool of herself with such doubts. Hot backups have been around with Oracle for more than a decade, they are so much prooven technology that no serious Oracle DBA in 24x7 environment is even considering anything else as their main backup/recovery scenario.

Jurij who have explained in a very good way . All the points have been put in a good way . Great !!!

jurij i have been great fan of ur's and jeff. I think i must have told this number of times but still i say it again and again :D

I will admit that I am still learning about the whole backup and recovery scheme. So, exuse the dumb question here.

Lets say that I do an export at 1200. An archive log switch occurs at 1300, 1400, and 1500. Database dies at 1530.

I have a script that goes into the instance and drops all the tables except sys and system. So, my database is empty. I then import the 1200 export.

Once import is finished, I mount the database and issue the recover command.

Question: Will this work? will it roll forward through the archive logs and redo's? If not, what prevents.

I think it will not work because SCN's in the control file and such are up to date with the numbers in the archive. I think what will happen is that the system will tell me that no recovery is needed.

????
I ask these questions as ammo for my argument.

thanks

that does not work, you have everything syncronized no recover is needed whatsoever

a export restores you the database but the only way to recover a database to point of failure is by restoring physical backups and applying archive logs

if you have archive logs what is the point exporting every 3, 4 hours??? instead of exporting e, 4 hours you better copy archive logs to other servers or tapes every 3, 4 hours if you are worried about a sevrer crash

Quote:

---

Originally posted by DcsoBob
I will admit that I am still learning about the whole backup and recovery scheme. So, exuse the dumb question here.

Lets say that I do an export at 1200. An archive log switch occurs at 1300, 1400, and 1500. Database dies at 1530.

I have a script that goes into the instance and drops all the tables except sys and system. So, my database is empty. I then import the 1200 export.

---

Full export does not include SYS's objects, but it does include SYSTEM's. So you'd better drop SYSTEM's tables too!

BTW, if your database dies, how would your script go into the database to drop those tables? In other words, if after crash you can open your database, then there is no need for any kind of restore or recovery whatsoever, so your whole described procedure is unnecessary. But if you can't open the database, you won't be able to anything with your export on it. In other words, you'll have to create a brand new database from scratch. Oh maybe I see now what you have in mind: you would first restore your database from one of your cold backups and then attack it with your script. Ok, let's say that is the scenario for further discussion.

Quote:

---

Once import is finished, I mount the database and issue the recover command.

Question: Will this work? will it roll forward through the archive logs and redo's? If not, what prevents.

I think it will not work because SCN's in the control file and such are up to date with the numbers in the archive. I think what will happen is that the system will tell me that no recovery is needed.

---

Yes, that's exactly what will happen. And you'll loose al the information from 12:00 until 15:30.

Quote:

---

????
I ask these questions as ammo for my argument.

---

Good luck.

Now, I have attemtpted to duplicate this on a seperate server.

I emptied the database and did an import (except for system). Only dropped all the user and application data. Did an import from the first of the month. Copied over the archive logs. Opened the database and issued the recover command. Got the message that the database did not need recovery.

Now, is this because the export produced an in-sync setup? I am still reading through the backup and recovery books, but I am thinking that it should have worked. If I undersand correctly, the SCN's in the control file, to show where we "are" in the process, would not match the header information in the datafiles, thus prompting a roll forward of the archive logs. But, I did not copy over the datafiles, I just emptied them and refilled them. So, will hte header information be the same.

I seem to be remember being told in my backup and recovery class that you could not create a new database on a new machine, copy over archive logs from old machine, and roll forward. He said the SCN's are machine dependent.

If I restore the database from a previous cold backup, then there is no need for the import. Once I issue the open command, the database will recover automatically. Might take it a little while to roll through the logs, but it will open.

I am reviewing the restore procedures from hot backups.

If i also drop the SYSTEM tablespaces and reimport them from the export, will that cause the archive logs to roll forward on the new box? What is "telling" the database that it has to roll forward. Is it a check of the current log number and SCN?

BTW, thanks for the patience and the answers. It's what I love about posting to this forum. Very helpful. Thanks !!

Quote:

---

Originally posted by DcsoBob
I emptied the database and did an import (except for system). Only dropped all the user and application data. Did an import from the first of the month. Copied over the archive logs. Opened the database and issued the recover command. Got the message that the database did not need recovery.

---

You are talking about two seperate databases now. The database you have wiped out has its own SCN and can not be synced up with an import.

Quote:

---

Now, is this because the export produced an in-sync setup?

---

You data is current only to the point of the export.

Quote:

---

I am still reading through the backup and recovery books, but I am thinking that it should have worked.

---

Nope, no way. Never. Archivelogs and import/export have nothing to do with each other.

Quote:

---

If I undersand correctly, the SCN's in the control file, to show where we "are" in the process, would not match the header information in the datafiles, thus prompting a roll forward of the archive logs. But, I did not copy over the datafiles, I just emptied them and refilled them. So, will hte header information be the same.

---

Nope, the datafiles will have different file#'s (probably) and different header information. You are talking about two different databases at this point.

Quote:

---

I seem to be remember being told in my backup and recovery class that you could not create a new database on a new machine, copy over archive logs from old machine, and roll forward.

---

Yup.

Quote:

---

He said the SCN's are machine dependent.

---

Think of it this way, SCN's are instance specific. Once you cross instances, the SCN numbers are no longer relevant.

Quote:

---

If I restore the database from a previous cold backup, then there is no need for the import. Once I issue the open command, the database will recover automatically. Might take it a little while to roll through the logs, but it will open.

---

This is the proper way to do a backup/recovery.

Quote:

---

I am reviewing the restore procedures from hot backups.

---

That's a good idea. Repeat this to yourself every 30 seconds as you are reading through your documentation: Export/import is not a backup methodology.

Quote:

---

If i also drop the SYSTEM tablespaces and reimport them from the export, will that cause the archive logs to roll forward on the new box? What is "telling" the database that it has to roll forward. Is it a check of the current log number and SCN?

---

You are talking about a new database. Forget about import/export.

Quote:

---

BTW, thanks for the patience and the answers. It's what I love about posting to this forum. Very helpful. Thanks !!

---

LOL...Jeff, I have a new mantra....

quote
"Export/import is not a backup methodology. "
"Export/import is not a backup methodology. "

I have just finished reading through about 4 chapters of the Oracle 7.3.4 Backaup and Recover handbook, and the book from the class. I see now how the SCN values are being used to ensure the files are in-sync, the role tha play in recovery, and why an export does not work.

News update: Meeting with teh boss about this in a few minutes. More info to follow. Stay tuned to this channel!!

Okay, after a meeting with the boss, I must give her more credit than I did in the previoius posts. Confusion was on my part as to her understanding of the export issue. Boss was fully understanding the fact that the exports were only an Instant-in-time recovery and that all info would be lost between exports. She viewed these exports as a worst-case disaster recovery, assuming no access to the production machine.

Did I mention we have had one tornado swipe the building in last couple years?

So, after discussion about the role of the archive logs, making these logs go to other destinations, what is really needed to recover, etc, I think we have made drastic improvements.

My intent now will be to change the exports to either once every 12 or once a day, opposite the hot backups. This export will be done in direct mode to reduce the load.

I will also be redirecting my archive logs to a secondary location. Thus, ensuring there recovery.

Question? can the online logs be placed accros machines?

I have never done a restore from hotbackup. How does that work? I am trying to find the docs on it now so I can read up on it.

I am going to use my cold backup from 2 days ago and copy the database to a new machine. I will then copy over the archive logs, and use the backup controlfile made last night to see if I can get the databae to recover to the point of the scn's in the control file. Is that workable? Plausbile?

Quote:

---

Originally posted by DcsoBob
Question? can the online logs be placed accros machines?

---

Online logs, no. Archived logs, kinda, if you're using standby database.

Quote:

---

I have never done a restore from hotbackup. How does that work? I am trying to find the docs on it now so I can read up on it.

---

You just restore the missing files, appropriate archived redo log files, and RECOVER DATABASE.

Quote:

---

I am going to use my cold backup from 2 days ago and copy the database to a new machine. I will then copy over the archive logs, and use the backup controlfile made last night to see if I can get the databae to recover to the point of the scn's in the control file. Is that workable? Plausbile?

---

[/B]
Yup, you got it now...

Quote:

---

Originally posted by DcsoBob
Okay, after a meeting with the boss, I must give her more credit than I did in the previoius posts. Confusion was on my part as to her understanding of the export issue. Boss was fully understanding the fact that the exports were only an Instant-in-time recovery and that all info would be lost between exports. She viewed these exports as a worst-case disaster recovery, assuming no access to the production machine.

---

Reason she boss is the, hmmm?

Hm, DcsoBob, at the end it seems it turned out that my initial post (the one that kinda upset you, remember? ;)) was not only a criticism as you initialy saw it. Let me repeat what I wrote few days ago:

Quote:

---

The main question I see here is: Why on earth would someone want to take full export of a database every six hours?

---

I think now you understand where I was pointing to. The main isue was not "How to speed up the export", it was "Do I realy need to do it?" (or at least "Do I realy need to do it so often"). And also "Am I aware of all the implication of my backup/recovery scenario and is it doable at all?".

And don't forget about your new mantra from Jeff....

well, we are not quite there yet. And I have been chanting the mantra. I am going to spend the day playing with this arena some more.

As a test of something you put in one of your replies, I also dropped the system tablespace. Big mistake. Now I get to practice full recovery. I am going to take my cold backup from the 3rd, restore the dbf files, control files, etc. Then, I am going to dump a trace of the current control file, copy over the archive logs since cold backup, and see what happens.

Now, will this work? If i restore the dbf's in conjunction iwth the control file, the SCN's and other information should jive right. Cause its not really a new instance, its a copy of the old.

Quote:

---

Originally posted by DcsoBob

Now, will this work? If i restore the dbf's in conjunction iwth the control file, the SCN's and other information should jive right. Cause its not really a new instance, its a copy of the old.

---

Yup, that's the idea. See http://metalink.oracle.com/metalink/...1&blackframe=0 for generalizations about backups and http://metalink.oracle.com/metalink/...T&p_id=69468.1 for your situation.

Quote:

---

Originally posted by DcsoBob
As a test of something you put in one of your replies, I also dropped the system tablespace.

---

What exactly do you mean by that????

You have misundestood something obviously. In one of my replies I said you should drop SYSTEM user before the full import, not the SYSTEM tablespace!!!!! I suggested that because it was obvious that you don't know that only SYS objects are excluded from full export, but the objects from user SYSTEM are exported as any other user.

So I guess it was entirely your fault. But on the other hand, I think what you did was a good thing. That way at least you are forced to practice real recovery. I guess your shop needed this the most.

And once again: do not read this message as a criticism, it is not ment as one. ;)

Quote:

---

Originally posted by DcsoBob
Now I get to practice full recovery. I am going to take my cold backup from the 3rd, restore the dbf files, control files, etc. Then, I am going to dump a trace of the current control file, copy over the archive logs since cold backup, and see what happens.

---

Be carefull with that! Two isues:

1.) Do not restore online redo logs from your cold backup!
2.) Do not restore controlfiles from your cold backup and do not backup your current control files to trace. No need for this, simply leave them as they are now and use them in the recovery.

It is obviously you will have to do point in time recovery (up to the point where you dropped the SYSTEM tablespace). So when you are done with the applying redo logs you will have to open your database with RESETLOGS option.

oops, I did not make something clear I think.

All of this dropping/testing/etc is being done a test server. My production machine is alive and well, chugging right along.

I did not drop the system tablespace, only the system user. If I type tablespace earlier....my bad.

Jurij, I am not taking it as anything other than helpful input. I can take criticism, good always, bad most of the time. The one from you just happen to catch me at a REALLY bad point in my day. Don't sweat it, water under the bridge. Think we have our repoire established here so I want you to give it to me with both barrels!!

After dropping the SYSTEM user, I executed an import of the DIRECT export. TONS of errors, failed miserably.

So, to provide a test for me, my plan is to do the following on my TEST server:
1) take the cold backup of my production system done 3 days ago and restore the dbfs, control files, redo logs, etc. Everything. Treat it as if I just installed oracle
2) issue a log switch on my PRODUCTION machine.
3) backup my PRODUCTION control file to trace.
4) edit this trace to show the new locations of my dbfs, control files, etc, since they are different on my TEST server.
5) copy over the archive logs from my PRODUCTION server to TEST server that were created since the shutdown and backup of PRODUCTION
6) Mount the TEST database using the backup controlfile
7) Roll forward the TEST database.
8) if all goes well, the last command (according to the doc) is to issue the alter database open resetlogs

Is my scenario workable? Pse, show me the errors. I am spending the today, tomorrow, and probably Friday working with this so I am comfortable.

The only restore scenario I have done so far is to blow away oracle, and reinstall. NOTHING on the instance side changed. Database started right up and away we went. THAT was on semi-production server.

Are we having fun yet?

Ah, the picture is much clearer now. With the scenario you described everything should go as expected. Only a couple of remarks:

- after your step 4) you need two additional steps:
4.a) Start the database in NOMOUNT mode
4.b) Run the "CREATE CONTROLFILE ..." script you have just edited in step 4) This will create a new controlfile(s), so there realy is no need to restore controlfiles from the cold backup in step 1 (but it doesn't hurt if you do, they will simply be overwritten if they are in the same locations as the new ones).

In step 7) you will have to specify that you are recovering with a controlfile that is not in sysnc with the database: RECOVER DATABASE USING BACKUP CONTROLFILE.....

You are correct, in step 8) you'll have to open the database with the RESETLOGS option. This will create brand new redolog files, so again there is no need to restore them in step 1) either, es they will be overwritten in any case.

Thanks. Forgot to put those steps in.

Will let ya know. I am also going to do another direct export and try importing it without dropping system user. If it works, I will then do an import where I drop the system user.

Stay tuned to this channel. HOpe others besides me are getting some useful info out of all of this. If nothing else, at least what NOT to do.

chants "import/export is not a backup scheme"
chants "import/export is not a backup scheme"