Export Direct=Y

[jmodic]

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.

[pravin]

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

[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.

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

[pando]

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

[jmodic]

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.

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.

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

Good luck.

[DcsoBob]

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 !!

[marist89]

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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

[DcsoBob]

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!!

[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.

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?

[marist89]

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.

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.

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...