What is the best way to backup a database? Is there a special command within SQL OR PL/SQL?

    Requires Free Membership to View

There are different ways to backup a database. You can a take an export of the database, a schema, or you can take a hot-backup or a cold backup. Hot backup refers to taking the backup of a database while it is still running. Cold backup refers to taking a backup of a database after the instance has been shut down.

1. Export:

You can take export of a schema, one or many tables within a schema (with or without data) or the entire database. You can do so from the command line by executing the following statement.

1. Schema export
exp system@<database_sid> user=< the_schema_you_want_to_export>file=Schema_date_of_export_database_sid.dmp log=Schema_date_of_export_database_sid.log

2. TABLE export

exp system@ <database_sid> user=< the_schema_you_want_to_export> file=Schema_tables_to_export_database_sid.dmp log=Schema_ Schema_tables_to_export_database.log parfile=my_table_list.par

The my_table_list.par should have entries for the tables to exports. Below is an example of the parameter file

TABLES=(TABLE1,
TABLE2,
TABLE3,
TABLE4,
..
..
TABLEN)

3. FULL export

exp system@<database_sid> full= yest file=FULL_date_of_export_database_sid.dmp log=FULL_date_of_export_database_sid.log

For all three cases you will be prompted for a password. I do not like to type password on the command line for security reasons as it may be viewable by other people logged on to the server.

Secondly, you should avoid exporting STATISTICS for the database, by specifying STATISTICS=NONE in the parameter file. However, if you recreate the database from the export file do not forget to analyze the schemas that you have imported.

You can create shell scripts or batch file to automate the export process through OS job schedulers like Cron in UNIX.

4. Hot Backup

Hot backup requires the database to run in archive log mode. You can take a hot-backup of the database while the database is still online.

There are a few steps involved in creating a backup of a database. Using the backup files you can recover and clone a database much faster that you would with the export file.

If the database is running on archivelog, then from SQLPLUS you need to connect to SYS as SYSDBA or connect internal as SYSDBA.

SQLPLUS> connect sys@ AS SYSDBA
SQLPLUS> ALTER DATABASE begin backup

When the database is in backup mode, ORACLE will not write to the DBF files. During the backup mode, you need to copy all the archive files (.arc), redo log files (.log) and databse(.dbf) files along with the init.ora file to your backup destination using operating system copy commands. When all the files are copied the files should be compressed (zipped) to optimize space.

When all files are copies from the SQLPLUS from you can end the database backup.

SQLPLUS> Alter database end backup.

5. Cold Backup

Cold backup requires to shutdown the database. You connect as SYSDBA and shutdown the database using the shutdown command from SQLPLUS. You may need to notify everyone that the database is being shutdown ahead of time. You may need to kill processes that are still running and no one has notified you of not to kill the processes after ample notice to the end users.

SQLPLUS> shutdown [immediate] or [abort]

One the database is shutdown you can copy all the database, redo and init files to the backup directory. Once everything is copied you can bring the database up with a startup command from SQLPLUS.

SQLPLUS> startup

This was first published in January 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: