How can I back up / restore the Oracle Database 11g Express Edition?

The Oracle database shortcut folder has two options for backing up and restoring the database, for example:

• backup script:

“Start Menu” -> “Programs” -> “Oracle Database 10g Express Edition” -> “Backup Database”

• script to restore the LAST backup version:

“Start Menu” -> “Programs” -> “Oracle Database 10g Express Edition” -> “Restore Database”

By default, the Oracle Database 11g Express Edition works in NOARCHIVELOG mode, which does not allow restoring data together with database transactions that took place between subsequent archives.

The “Backup Database” script running for Oracle working in the default NOARCHIVELOG mode returns a warning about this option. Giving consent to make a backup in this mode results in creating (after automatically stopping and restarting the database via the script) a copy of the data without the possibility of restoring the above transaction.

Switching the database in ARCHIVELOG mode causes that the database creation process is performed in the background and archiving restore logs are performed in the so-called required FRA restore (FLASH RECOVERY AREA).

In this mode, the database allows you to make backups without having to stop the database engine, but the consumption of the FRA area increases significantly. In this case, the creators of the Oracle database recommend moving the area in question to a separate partition, setting its size to at least 15 GB and regularly performing “backups” to prevent overflowing of this area and loss of log data.

The transaction recovery log archive mode avoids data loss, also as a result of critical media errors, the default NOARCHIVELOG option is sufficient to provide protection against operating system errors and errors of the database instance itself.

More information on the above issues can be found in the official online documentation of the ORACLE XE database:

http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25107/toc.htm

If you need to create and restore copies of Oracle databases with user-defined settings, Oracle distributions also include a standard copy management tool, available from the installation directory:

– subdirectory „oraclexeapporacleproduct10.2.0serverBIN” -> „rman.exe”

– example command „rman nocatalog target XE” allows you to connect the tool to a database instance called XE (after entering the SYS user password) to make a copy

– rman command:

backup full database format „d:/backup/rman_%T_%s_%p.bus”;

allows you to create a full copy of the database in the target directory d:/backup.

Note: if the database does not work in ARCHIVELOG mode, it must be manually stopped and “mounted” via the command string of the rman program or SQL interpreter:

shutdown immediate
startup mount

After successful backup, the SQL command “alter database open;” allows you to restart the base engine.

• The basic process of restoring the last copy of the database using the rman program is limited to issuing the command string:

rman nocatalog target XE (next the SYS user password)
shutdown immediate
startup mount
restore database;
recover database;
alter database open;
exit

Rman command “restore database;” restores the last database archive existing in the current control file (command ‘list backup;’ – displays the content of the control file)

the recover command allows you to check the consistency of the database.

After completing the restoration process and starting the database, it is again recommended to check if all necessary system spaces (tablespaces) and data files are online:

SQL > select TABLESPACE_NAME,STATUS from dba_tablespaces; SQL> select FILE#,STATUS,ENABLED,NAME from v$datafile;

Operations related to extracting control files from total copies, restoring specific arrays or so-called backup sets are more complicated issues, the description of which can be found in the source Oracle database documentation.