July 15, 2008 at 10:54 pm
Hi,
I am new to oracle, could anyone tell me how to take the backup for oracle database with sybtax / using GUI( & how to use it ?) ?
Regards,
Abhijit More
August 1, 2008 at 5:56 am
I being working with Oracle for 10+ years now and never heard of something called "sybtax", what's that?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2008 at 2:56 pm
Hi PaulB,
Apart from this 'sybtax', could you share a little about the database backup methodology in Oracle. Being an Oracle developer for long in past and now SQL Server developer did not have exposeure to backups. What I know about and use is just exporting schemas to .dmp and importing back.
Sincerely,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 1, 2008 at 3:25 pm
Oracle doc Oracle® Database Backup and Recovery Basics may be a good staring point.
And then Oracle® Database Backup and Recovery Advanced User's Guide 🙂
Gints Plivna
Gints Plivna
http://www.gplivna.eu
August 2, 2008 at 4:09 am
Hi gints,
What I know about and use is just exporting schemas to .dmp and importing back.
Is it not the logical backup ?
Thanks,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 2, 2008 at 10:20 am
Okay... here we go with some info about Oracle backups.
COLD BACKUP
Usually database is in noarchivlog mode; no point in time recovery.
You shutdown your database then you copy -operating system copy - all your datafiles, control files, etc.
If you have to restore you just copy everything back and startup your instance.
HOT BACKUP
Usually database is in archivelog mode; allows for point in time recovery.
You put your tablespaces in backup mode then copy all your datafiles and archivelogs -operating system copy - plus you create a control file and copy it too.
If you have to restore you copy everything back, startup nomount your instance, rollforward archivelogs and then open your database.
RMAN
Allows for point in time recovery and database clonning.
This is Oracle's native backup tool, imposible to tell you in five lines all RMAN is capable to do but think RMAN knows everything about your database and you just have to tell it what you want to do either backup or restore.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2008 at 1:23 pm
I think logical backup and recovery using the exp and imp utilities are also very useful and important as it allows to take backup of selected objets in the schema (with or without data).
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 2, 2008 at 1:38 pm
BTW if you are on at least 10g then check data pump (impdp and expdp) - they have wayyyyyy more capabilities than original imp/exp.
Gints Plivna
Gints Plivna
http://www.gplivna.eu
August 3, 2008 at 2:13 am
Mazharuddin Ehsan (8/2/2008)
I think logical backup and recovery using the exp and imp utilities are also very useful and important as it allows to take backup of selected objets in the schema (with or without data).
I'm sorry but I have to strongly disagree, not just me... the whole Oracle community would if they find their way to this forum.
Here is why, you cannot recover a database from an export dump therefore export is not a form of backup. Oracle does not considers exp/imp a form of backup, just a tool to move data around like you would do via BCP in the SQL Server world.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 3, 2008 at 10:31 am
1.
I'm sorry but I have to strongly disagree, not just me... the whole Oracle community
I wonder why to disagree when Oracle itself is defining logical backup as a valid backup method.
Oracle® Database Backup and Recovery Basics
Logical backups contain logical data (for example, tables or stored procedures) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.
And from the Oracle community:
What is import/export and why does one need it?
Oracle's export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.
2.
You cannot equate BCP to exp/imp. One quick reason is exp/imp is to move data between Oracle databases while BCP is an ETL tool. The counterpart of BCP in the Oracle is the good ol' SQL*loader. This is the primitive tool that Oracle has got while SQL Server has far more sophisticated DTS and SSIS which can be programmed to do a lot of ETL automations.
3.
Thanks for your valuable info on Oracle backup 🙂
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 3, 2008 at 10:39 am
Unfortunately you do not understand Oracle jargon, "logical backup" doesn't mean "valid backup method".
You cannot recover an Oracle database if all you have is an export dump file. Period.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 3, 2008 at 11:22 am
Thanks again for your feedback on the subject. No matter what is the jargon, most important is to understand how things work and get things done.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 5, 2008 at 2:24 pm
PaulB (8/3/2008)
Unfortunately you do not understand Oracle jargon, "logical backup" doesn't mean "valid backup method".You cannot recover an Oracle database if all you have is an export dump file. Period.
Hi,
I stronlgy dissagree.
If you do not know how to do it, you don't do it. If you do not know how to do it, don't say it cannot be done and post as if you were the whole Oracle community, because you meslead people.
Export and import is one of the methods of migrating a database, you create one from scratch with a new Oracle version and run a full import from a dump made in an older version.
I myself used exp/imp (logical backups) to restore databases on a regular basis as an alternative - but you must know how to do it and you can with a similar success as with a physical backup. However, you need to first have the database created. The disadvantage is you may need to wait much longer for the import to take place. Yet, if you do not have a physical backup and do have an export dump, all you do is recreate the database, you may also recreate the tablespaces.
It's good to save your scripts to create a database from scratch just in case you need it.
If you do not know the structure you simply run the following command as SYSTEM:
alter system backup controlfile to trace;
You then look into the place where Oracle places a trace file (called udump) and edit that file. It's normally used to recreate a lost control file, but you can save it every day as part of the backup.
After the database is created (SYSTEM tablespace and the Oracle dictionary loaded), you run a full import. Also you may need to check all the objects if they are invalid and recompile some of them. A very good knowledge of what the database is made of is also crucial. But you cannot say this is not possible!
As for backing up, there are different methods. Methods slightly differ on Windows and UNIX boxes, because if you want to use the HOT backup method on Windows you cannot use the "copy.exe" command, instead you must use the Oracle utility "ocopy.exe" or preferrably RMAN. Any Windows utility will lock the file and cause a backed up file unusable. You may then run into a problem, when a database will set the status of the file to "recover" and you will need to know some tricks to recover from such a situation. This is very dangerous and you may even run into a situation when you will not be able to start such a database without the support of Oracle. So be carefull what you do on Windows.
Up till now I personally do not use RMAN, because we use a backup library run by Tivoli.
For the COLD backups we simply shutdown the database and backup all files.
To make a list of files you simply run the following SQLs:
select name from v$database;
select name from v$controlfile;
select member from v$logfile;
To switch to HOT backups additional space is needed to store the archived log files. You then need to back them up and it's requires a bit of more work to prepare such a procedure. And good knowledge is needed to do it properly.
Sybtax - never heard of it.
On Windows you can also use NTBACKUP.exe, this you can not only use with the GUI, but also using the command line scripts.
UNIX - just use "cp", "dd", "tar" , wichever you prefer. And those tools can be used to backup a data file in the HOT backup.
Hope this helps,
Richard
August 6, 2008 at 1:22 am
Richard -- you are adorable 😀
If you have to pre-create your instance -as I appointed in my posts that means you cannot recover from your exp dump. What part of it you do not understand?
In the Oracle world a valid backup is a single set of files you carry to an island where the only thing you have is a box with Oracle binaries installed, you point to that single set of files and you are able to recover your database.
Can you do that with an Oracle export dump file?
Following your line of thinking you can also say a text file dump of everything you have in your database including DDL and Code would be also a backup... you just have to pre-create your instance, create all your users, create all your objects, populate all your tables via SQL*Loader, etc. etc... isn't it?
If you do not know what you are saying, just say nothing.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 6, 2008 at 1:54 am
PaulB (8/6/2008)
Richard -- you are adorable 😀If you have to pre-create your instance -as I appointed in my posts that means you cannot recover from your exp dump? What part of it you do not understand?
In the Oracle world a valid backup is a single set of files you carry to an island where the only thing you have is a box with Oracle binaries installed, you point to that single set of files and you are able to recover your database.
Can you do that with an Oracle export dump file?
If you do not know what you are saying, just say nothing.
Hi PaulB,
well, if you take your backup or export to an island where there is no internet connection and you don't have any way of downloading and installing Oracle, so there is no point of restoring anything. Right? Whichever you have, export or backup?
Besides:
http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ
What strategies are available for backing-up an Oracle database?
The following methods are valid for backing-up an Oracle database:
* Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. See the Import/ Export FAQ for more details.
* Cold or Off-line Backups - shut the database down and backup up ALL data, log, and control files.
* Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
* RMAN Backups - while the database is off-line or on-line, use the "rman" utility to backup the database.
If you are clever, you always have two types of backups every day. A COLD/HOT backup and a full export. That's what I always have. The only most impotant thing is to run this export at a time of the lowest user activity and run it with the CONSISTENT=Y mode. The same is with a HOT backup.
By the way, if you have a database in ARHIVELOG mode, do you have a copy of all your 200 last files on that island to restore the database to a point in time?
:hehe:
What you are asuming is a total disaster recovery, so the question is, what kind of a backup do we need? Let's asume your last backup failed and you ONLY HAVE A FULL EXPORT 😉
Let's also asume you magically have a backup of your INIT.ORA file with you as part of the documentation (I always have) you carry with you. Even if you haden't, if you are a good DBA, you will be able to recreate one from a sample and you will remember all the main/basic necessary settings. Even if you don't remember the DB_BLOCK_SIZE, it doesn't matter, because you are creating a new database.
If you have the possibility to download the Oracle software or have it with you, you install it, and then create the database. Scripts are fully available, you can always find something on the web, especially on http://www.oracle.com.
Metalink has tons of sample scripts.
Of course, you may need to go through some guesses, because someone would have created a database with options you are not now aware of. But let's asume you are already prepared for such a situtaion and tested this scenario, riight?
Of course it's a matter of efficienncy and it would take a whole day maybe two to import, but still you cannot say this is not achievable.
Following your line of thinking you can also say a text file dump of everything you have in your database including DDL and Code would be also a backup... you just have to pre-create your instance, create all your users, create all your objects, populate all your tables via SQL*Loader, etc. etc... isn't it?
PaulB, if you have ever done a full import you would have known that's what exactly a full import does, recreates everything, users, including restoring passwords, schemas, everything. There is no need to store all the DDL's in text files. Use the import utility "imp" with the options "FULL=y SHOW=y LOG=imp_struct.log". The log file is a text file with all the create statements including all missing tablespaces (CREATE TABLESPACE, CREATE TABLE, CREATE USER, etc.), with the file locations. So in theory if you have your export from a UNIX box and you have to recreate the database on a Windows box you can take that log file and use it as a source to create your tablespace on Windows partitions. You can create a new database, write a new script to create the tablespaces and then run a full import.
Of course there are limitations, but once you get to know the import utility, everything is possible. The better the chances for you when you know the application and the majority of the database objects you have.
Don't forget to run a FULL export on a daily basis (very small file) with the option "ROWS=n", this is the full DB structure and many times you will thank me for this suggestion because this will save your life sometimes.
If you do not know what you are saying, just say nothing.
Yes, I know what I am saying, because I've done it at least 2000 times on various occasions on UNIX, Windows during the past 16 years.
Any more doubts?
😛
Regards,
Richard
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply