Oracle Lost Database Recovery

This is a more severe loss of data when the database server goes down and can not be restarted.

The best solution is to create a new database server and restore the backups to the new database.  This solution assumes that the database was in archivelog mode, see Oracle Backup and Recovery, and is backed up to an external system on a daily basis.

The following is for Linux.  Windows is very much the same steps, just using the OracleXE.exe to install the new database.

Create a new Database

On a new server create a new Oracle XE database.

Install Oracle XE
Install Oracle XE on the new database server as root:
[root@localhost oracle]# rpm -ivh oracle-xe-10.2.0.1-1.0.i386.rpm 
Preparing... ########################################### [100%]
1:oracle-xe ########################################### [100%]
Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.

[root@localhost oracle]#
Configure the database, accept all the defaults and set the system password:

[root@localhost oracle]# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
[root@localhost oracle]#
Configure the Database
As oracle set the database to be archivelog mode

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 12:06:31 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1258488 bytes
Variable Size 92277768 bytes
Database Buffers 192937984 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>
Shutdown the database

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 14:21:43 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Restoring the Database

Restore Files from Backup
Restore the:
  • app/oracle/flash_recovery_area/XE/archivelog/
  • app/oracle/flash_recovery_area/XE/autobackup/
  • app/oracle/flash_recovery_area/XE/backupset/
  • app/oracle/flash_recovery_area/XE/onlinelog/ 
directories from backups.  Make sure that the oracle user owns all the restored files.

 

Restore the Database
Restore the database using the restore.sh script and enter the location of the restored files (/usr/lib/oracle/xe/app/oracle/flash_recovery_area/).

[oracle@localhost ~]$ $ORACLE_HOME/config/scripts/restore.sh
This operation will shut down and restore the database. Are you sure [Y/N]?Y
Restore in progress...
Enter the flash recovery area location:/usr/lib/oracle/xe/app/oracle/flash_recovery_area
Restore of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_restore.log.
Press ENTER key to exit
[oracle@localhost ~]$
Verify
Log into the database as a user:

[oracle@localhost ~]$ sqlplus em

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 14:34:58 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>