Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DB Backup 1
- Thread starter tedi1
- Start date
- Thread starter
- #3
- Thread starter
- #5
Alex,
I our company we use SQL SERVER but there is one software that uses ORACLE. the software sits in some kind of a black box (no one know what will happen in case there will be a collapse). I took it on my self to try and recreate the installation of the software. I seem to managed to do this besides one "small" problem connected to the oracle (no user but the DB owner can login). I think it something to do with the oracle Authentication. But as I said I'm not a DBA... there for I asking how to do this in order to see if I can manage without a DBA.
Yaniv
I our company we use SQL SERVER but there is one software that uses ORACLE. the software sits in some kind of a black box (no one know what will happen in case there will be a collapse). I took it on my self to try and recreate the installation of the software. I seem to managed to do this besides one "small" problem connected to the oracle (no user but the DB owner can login). I think it something to do with the oracle Authentication. But as I said I'm not a DBA... there for I asking how to do this in order to see if I can manage without a DBA.
Yaniv
The first thing I suggest you do is to read the Oracle8 Backup and Recovery manual. I know that you may think this is a bad suggestion - it is not. If you want to achieve what you have set out to do, you must read this book and understand it. In particular, how to backup the system, how it is backed up at the moment(?), whether this method is suitable for your situation, how to recover from any failure, and how to transfer database between servers.
As you are working on a production system it may be a GOOD IDEA to get Oracle on your laptop / desktop and practivce with that.
I'm not the person to answer your original query as all my production systems are on UNIX and I've never actually transferred a database from NT to NT, but the principles are basically the same.
Good Luck
Alex
PS By the way 7 years ago I was in a similar position to you. Our DBA left and I (as a UNIX / NT admin) had to pick up the pieces.
As you are working on a production system it may be a GOOD IDEA to get Oracle on your laptop / desktop and practivce with that.
I'm not the person to answer your original query as all my production systems are on UNIX and I've never actually transferred a database from NT to NT, but the principles are basically the same.
Good Luck
Alex
PS By the way 7 years ago I was in a similar position to you. Our DBA left and I (as a UNIX / NT admin) had to pick up the pieces.
All good advice from Alex, but the manual is ONLY 200 pages (short by Oracle standards!). I think you might get bogged down . . . and I think you need some "first-aid" type help.
Here are the phases I would go through (the gurus will object but we need to keep this simple):
1) COLD backups: (you must have some way of shutting down and starting up the application INCLUDING shutting down the db and the Oracle NT Services) EITHER schedule a .bat that does a shutdown, backup of all the disks and startup OR use a backup software with it's own scheduler and has the possibility to include the shutdown & startup scripts. You now have the opportunity to restore you application as it was last night (shutdown, restore the files, startup) - not good but better than nothing. (Don't forget to test it on your test machine!)
Now take the time to read and think a bit (all that stuff Alex mentioned) -
2) Read about ARCHIVELOGS - these are life-blood, insurance and security blanket all rolled into one. Once you are generating these and copying them to another machine you can start to sleep again at night.
3) Read about HOT backups - these avoid shutting down the db - you might not want to go that far.
If you run 24x7 you have to start with (2) plus (3) (I guessed that you don't ? ?).
Good luck - hate to see you learning to swim in the deep-end. DaPi - no silver bullet
Here are the phases I would go through (the gurus will object but we need to keep this simple):
1) COLD backups: (you must have some way of shutting down and starting up the application INCLUDING shutting down the db and the Oracle NT Services) EITHER schedule a .bat that does a shutdown, backup of all the disks and startup OR use a backup software with it's own scheduler and has the possibility to include the shutdown & startup scripts. You now have the opportunity to restore you application as it was last night (shutdown, restore the files, startup) - not good but better than nothing. (Don't forget to test it on your test machine!)
Now take the time to read and think a bit (all that stuff Alex mentioned) -
2) Read about ARCHIVELOGS - these are life-blood, insurance and security blanket all rolled into one. Once you are generating these and copying them to another machine you can start to sleep again at night.
3) Read about HOT backups - these avoid shutting down the db - you might not want to go that far.
If you run 24x7 you have to start with (2) plus (3) (I guessed that you don't ? ?).
Good luck - hate to see you learning to swim in the deep-end. DaPi - no silver bullet
-
1
- #8
as administrator on nt or oracle on unix go to the command line and execute 'exp help=y' which will give you the parameters to run the export utility. 'imp help=y' gives you the parameters to run import. Go to the oracle documentation disk and in the utilities section read about export and import. These are simple utilities to run which you can use to do a fairly quick backup or restore of the db. You can run the exp from one box and then run imp on another box providing you have a username and tablespaces sestablished. There's more to it than that but this is a fairly easy way to get started. Read about how to run the exp and imp using a parfile so you can set up a batch job to do this for you.
raygg, I must disagree with you.
exp/imp is no better than a COLD backup AND requires a working db to do the import. If you trash a db file, you need to recreate the db before imp will work (and imp is slower than a file restore). Tedi1 is going to need to backup the whole machine in any case - so unless it is 24x7, why not shutdown?
exp/imp is a dead-end for backup. The path I outlined above will get tedi1 to the point where data is safe up to the last log switch (i.e. phase 2).
exp/imp does have a place for recovering bits of data lost by "programmer" accident (e.g. a dropped table). I do a daily exp for that reason. But with a "black box" this is very unlikely to be needed.
Yes, it is a good way to copy from production to test.
(BTW there is an alert going round for exp generating bad data with big buffer sizes - basically if more than 32K rows fit in the exp buffer.) DaPi - no silver bullet
exp/imp is no better than a COLD backup AND requires a working db to do the import. If you trash a db file, you need to recreate the db before imp will work (and imp is slower than a file restore). Tedi1 is going to need to backup the whole machine in any case - so unless it is 24x7, why not shutdown?
exp/imp is a dead-end for backup. The path I outlined above will get tedi1 to the point where data is safe up to the last log switch (i.e. phase 2).
exp/imp does have a place for recovering bits of data lost by "programmer" accident (e.g. a dropped table). I do a daily exp for that reason. But with a "black box" this is very unlikely to be needed.
Yes, it is a good way to copy from production to test.
(BTW there is an alert going round for exp generating bad data with big buffer sizes - basically if more than 32K rows fit in the exp buffer.) DaPi - no silver bullet
tedi1,
Just in case it is not obvious (and even if it is) you can see the files of the database by running the following in SQL*Plus or Server Manager (svrmgrl):
select * from sys.v_$dbfile;
select * from sys.v_$logfile;
select * from sys.v_$controlfile;
A phase 1 (COLD) recovery would require all those files to be restored. DaPi - no silver bullet
Just in case it is not obvious (and even if it is) you can see the files of the database by running the following in SQL*Plus or Server Manager (svrmgrl):
select * from sys.v_$dbfile;
select * from sys.v_$logfile;
select * from sys.v_$controlfile;
A phase 1 (COLD) recovery would require all those files to be restored. DaPi - no silver bullet
It is a matter of perspective. Someone has to walk before they can run. Tedi1 needs something that will work. In a perfect world every one takes an image backup of every box and in case of a failure has another identical piece of hardware to reimage the whole server onto over a wideband network. The LAN guy is ready to drop everything and the whole restore is done in 1 hour on a spanking new machine that was sitting in the closet.
In the imperfect world I see every day, another machine identical to or with the same capacity as the first is never available, no one reads installation instructions, the LAN guy cannot get around to locating the backup tape for two days, and when he does it takes 18 hours to restore 4 gb because of a slow backup device and the network is busy. And half the time some hardware failure occurs or something has to be jiggled to get it to work right.
Everything you say may be true, but it is equally true that Tedi1 is Oracle challenged and he needs something that will work RIGHT NOW. Granted you need a working db to use imp - but the whole purpose of exp/imp is to backup the DATA - if he reads the book then he will know he has to also backup some other files as well.
First things first. Do the export and the data is backed up right now. He does not have to go thru a long learning curve to do that. He can move on to more complex backup and recovery methods after he reads the book and someone trains him. But in the meantime he has a tool that can operate at the macro level of backing up all data incl database system schema level, but also has the granularity to restore just a table or a portion thereof without the need to get the LAN guy and the UNIX admin involved in a restore of the files shipped off to Iron Mountain.
In the imperfect world I live in - which is a development software company - we just have development databases and no 24 x 7 production systems. In this world - export and import works just fine. Yes - programmers lose tables- and I can get them back up and running with my db exports in 1-2 hours - whereas the LAN guy with his image backups has to order the tape, search the tape for a couple of hours and maybe in 24 to 72 hours a datafile can be restored - but he does not have the granularity capability to restore the missing table that someone on a tight schedule for devlopment is desperate to get back up and running. With archiving turned on I can restore right up to the moment of the loss - the image backup can only restore to the last date of backup.
Given that Tedi1 describes this as the only Oracle db is a sea of SQLServers, I would venture this is a somewhat smallish db with low traffic that is not necessarilty mission critical - and likely does not have the 32k rowsize you talk about - otherwise a DBA would be under contract to keep tabs on it. With these assumptions, the exp/imp and backup of the control files and init.ora would probably be sufficient on a regular basis. If something drastic occurs where the machine is lost - the local disaster recovery planning should kick in for that kind of recovery. If needed, they can get a contract DBA to come in for a day or two and create a new Oracle instance - and rebuild the database - with or without the control files and init.ora -from the imp file. Given the circumstances - I would suspect the management would probably will go for the low-budget solution.
In the imperfect world I see every day, another machine identical to or with the same capacity as the first is never available, no one reads installation instructions, the LAN guy cannot get around to locating the backup tape for two days, and when he does it takes 18 hours to restore 4 gb because of a slow backup device and the network is busy. And half the time some hardware failure occurs or something has to be jiggled to get it to work right.
Everything you say may be true, but it is equally true that Tedi1 is Oracle challenged and he needs something that will work RIGHT NOW. Granted you need a working db to use imp - but the whole purpose of exp/imp is to backup the DATA - if he reads the book then he will know he has to also backup some other files as well.
First things first. Do the export and the data is backed up right now. He does not have to go thru a long learning curve to do that. He can move on to more complex backup and recovery methods after he reads the book and someone trains him. But in the meantime he has a tool that can operate at the macro level of backing up all data incl database system schema level, but also has the granularity to restore just a table or a portion thereof without the need to get the LAN guy and the UNIX admin involved in a restore of the files shipped off to Iron Mountain.
In the imperfect world I live in - which is a development software company - we just have development databases and no 24 x 7 production systems. In this world - export and import works just fine. Yes - programmers lose tables- and I can get them back up and running with my db exports in 1-2 hours - whereas the LAN guy with his image backups has to order the tape, search the tape for a couple of hours and maybe in 24 to 72 hours a datafile can be restored - but he does not have the granularity capability to restore the missing table that someone on a tight schedule for devlopment is desperate to get back up and running. With archiving turned on I can restore right up to the moment of the loss - the image backup can only restore to the last date of backup.
Given that Tedi1 describes this as the only Oracle db is a sea of SQLServers, I would venture this is a somewhat smallish db with low traffic that is not necessarilty mission critical - and likely does not have the 32k rowsize you talk about - otherwise a DBA would be under contract to keep tabs on it. With these assumptions, the exp/imp and backup of the control files and init.ora would probably be sufficient on a regular basis. If something drastic occurs where the machine is lost - the local disaster recovery planning should kick in for that kind of recovery. If needed, they can get a contract DBA to come in for a day or two and create a new Oracle instance - and rebuild the database - with or without the control files and init.ora -from the imp file. Given the circumstances - I would suspect the management would probably will go for the low-budget solution.
Hi raygg, looks like we will continue disagree on this . . . makes life rich!
"Tedi1 is Oracle challenged and he needs something that will work RIGHT NOW." Exactly - he has had to learn the hard way how to shutdown & startup the db and I guess he knows how to backup an NT machine. So all the pieces are there to do a cold backup. I didn't see the point of adding any new ones!
Tedi1, "Don't Panic!" either way would be lots better than nothing . . . . (both is best? I do both.)
(BTW, my BTW was not an objection - just a warning to anyone looking in - apparently it hits tables with short rows in big exp buffeers, when the number of rows per buffer gets to 32,000 approx. 32K is NOT the row length, Sorry if that wasn't clear. There is a fix:
set ORA_OCI_NO_OPTIMIZED_FETCH=1
REM this is workaround for bug 2598387
exp etc parfile=etc etc
) DaPi - no silver bullet
"Tedi1 is Oracle challenged and he needs something that will work RIGHT NOW." Exactly - he has had to learn the hard way how to shutdown & startup the db and I guess he knows how to backup an NT machine. So all the pieces are there to do a cold backup. I didn't see the point of adding any new ones!
Tedi1, "Don't Panic!" either way would be lots better than nothing . . . . (both is best? I do both.)
(BTW, my BTW was not an objection - just a warning to anyone looking in - apparently it hits tables with short rows in big exp buffeers, when the number of rows per buffer gets to 32,000 approx. 32K is NOT the row length, Sorry if that wasn't clear. There is a fix:
set ORA_OCI_NO_OPTIMIZED_FETCH=1
REM this is workaround for bug 2598387
exp etc parfile=etc etc
) DaPi - no silver bullet
- Thread starter
- #15
Thax all for your replies. I think I will start with the EXP/IMP thing first to backup something and in the mean time I'll learn some more about Backup and Recovery in Oracle.
Yaniv
P.s On the last issue (Iraq/France) I must Agree with raygg. They should be the cherry on top of the cream.
Yaniv
P.s On the last issue (Iraq/France) I must Agree with raygg. They should be the cherry on top of the cream.
>> In the imperfect world I see every day, another machine identical to or with the same capacity as the first is never available, no one reads installation instructions, the LAN guy cannot get around to locating the backup tape for two days, and when he does it takes 18 hours to restore 4 gb because of a slow backup device and the network is busy. And half the time some hardware failure occurs or something has to be jiggled to get it to work right.
Wow...you just described my life precisely!
Wow...you just described my life precisely!
Raygg or Others,
I find myself in a similiar situation regarding Imp/Exp.
What is the minimum Create Database ect. that has to be done to use IMP? Do I create all of the log,redo and data files, then connect as System and run IMP??
I have connected using System and created 3 dmp files for the 3 DB that I need to copy to another machine. I'm creating a Production, Dev and Test DB on the new machine.
Since I may have to do this often, I want to write scripts that will help automate the process.
Thank you,
Mike
I find myself in a similiar situation regarding Imp/Exp.
What is the minimum Create Database ect. that has to be done to use IMP? Do I create all of the log,redo and data files, then connect as System and run IMP??
I have connected using System and created 3 dmp files for the 3 DB that I need to copy to another machine. I'm creating a Production, Dev and Test DB on the new machine.
Since I may have to do this often, I want to write scripts that will help automate the process.
Thank you,
Mike
During the create database, you only need redo logs and the system tablespace to import a "FULL" db export. The size of the system tablespace depends on the database that you will be importing.
This thread is interesting... Robbie
"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War
I was just doing a full export and import into a new database on a new machine Im setting up as a "development" database. I ran a full export of the existing database and then after creating the tablespaces and schema owners in the new database I did an import of the schema owners tables. This may be a simple question, but the only errors I got during the import had to do with grants, due to roles not being there.. why dont my roles import?
- Status
Similar threads
