Different ways to back up a database

There are several ways to back up a database, including schema, table and full exports, as well as hot and cold backups. Here are descriptions of each method.

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

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 [email protected]<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 [email protected] <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


3. FULL export

exp [email protected]<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 [email protected] AS SYSDBA

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

Dig Deeper on Oracle database backup and recovery