Some standard recovery scenarios
Deleted or Corrupted Datafile
If a database datafile in the $ORACLE_HOME/oradata/XE is deleted then the database will no longer open. It is not possible to recover just the datafile from a backup since it will be out of sync with the other datafiles and data will have been lost. If the database has been setup in archivelog mode and backuped as described in Oracle XE Backup then the database can be recovered to the most recent archive log.
Backup
Run a backup on the database as oracle:
[oracle@localhost ~]$ $ORACLE_HOME/config/scripts/backup.sh Doing online backup of the database. Backup of the database succeeded. Log file is at /usr/lib/oracle/xe/oxe_backup_current.log. Press ENTER key to exit [oracle@localhost ~]$
Check the database is running and query the datafiles:
[oracle@localhost ~]$ sqlplus system SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 09:26:04 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> select substr(file_name,1,50) "File", bytes/(1024*1024) "Size (MB)" from dba_data_files; File Size (MB) -------------------------------------------------- ---------- /usr/lib/oracle/xe/oradata/XE/users.dbf 100 /usr/lib/oracle/xe/oradata/XE/sysaux.dbf 220 /usr/lib/oracle/xe/oradata/XE/undo.dbf 210 /usr/lib/oracle/xe/oradata/XE/system.dbf 340 SQL>
Corrupt
Delete the users.dbf datafile and create a new empty file:
[oracle@localhost ~]$ cd oradata/XE [oracle@localhost XE]$ ls -al total 898824 drwxr-x--- 2 oracle dba 4096 May 26 11:21 . drwxr-xr-x 3 oracle dba 4096 May 25 21:43 .. -rw-r----- 1 oracle dba 7061504 May 26 11:23 control.dbf -rw-r----- 1 oracle dba 230694912 May 26 11:21 sysaux.dbf -rw-r----- 1 oracle dba 356524032 May 26 11:21 system.dbf -rw-r----- 1 oracle dba 20979712 May 26 11:21 temp.dbf -rw-r----- 1 oracle dba 220209152 May 26 11:21 undo.dbf -rw-r--r-- 1 oracle dba 104865792 May 26 11:21 users.dbf [oracle@localhost XE]$ rm users.dbf [oracle@localhost XE]$ touch users.dbf [oracle@localhost XE]$ ls -al total 796312 drwxr-x--- 2 oracle dba 4096 May 26 11:23 . drwxr-xr-x 3 oracle dba 4096 May 25 21:43 .. -rw-r----- 1 oracle dba 7061504 May 26 11:23 control.dbf -rw-r----- 1 oracle dba 230694912 May 26 11:21 sysaux.dbf -rw-r----- 1 oracle dba 356524032 May 26 11:21 system.dbf -rw-r----- 1 oracle dba 20979712 May 26 11:21 temp.dbf -rw-r----- 1 oracle dba 220209152 May 26 11:21 undo.dbf -rw-r--r-- 1 oracle dba 0 May 26 11:23 users.dbf [oracle@localhost XE]$
Shutdown the database:
[oracle@localhost ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 11:26:37 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> shutdown immediate ORA-01110: data file 4: '/usr/lib/oracle/xe/oradata/XE/users.dbf' ORA-01115: IO error reading block from file 4 (block # 1) ORA-27072: File I/O error Additional information: 4 Additional information: 1 SQL>
The database is now corrupted and can not be restarted.
Restore
Run the restore.sh script
[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... Restore of the database succeeded. Log file is at /usr/lib/oracle/xe/oxe_restore.log. Press ENTER key to exit [oracle@localhost ~]$
Verify
Check that the users.dbf file is restored
[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... Restore of the database succeeded. Log file is at /usr/lib/oracle/xe/oxe_restore.log. Press ENTER key to exit [oracle@localhost ~]$ cd oradata/XE [oracle@localhost XE]$ ls -al total 898824 drwxr-x--- 2 oracle dba 4096 May 26 11:31 . drwxr-xr-x 3 oracle dba 4096 May 25 21:43 .. -rw-r----- 1 oracle dba 7061504 May 26 11:35 control.dbf -rw-r----- 1 oracle dba 230694912 May 26 11:31 sysaux.dbf -rw-r----- 1 oracle dba 356524032 May 26 11:31 system.dbf -rw-r----- 1 oracle dba 20979712 May 26 11:31 temp.dbf -rw-r----- 1 oracle dba 220209152 May 26 11:31 undo.dbf -rw-r--r-- 1 oracle dba 104865792 May 26 11:31 users.dbf [oracle@localhost XE]$
Verify that the database knows about the users.dbf datafile again:
[oracle@localhost ~]$ sqlplus system SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 09:50:28 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> select substr(file_name,1,50) "File", bytes/(1024*1024) "Size (MB)" from dba_data_files; File Size (MB) -------------------------------------------------- ---------- /usr/lib/oracle/xe/oradata/XE/users.dbf 100 /usr/lib/oracle/xe/oradata/XE/sysaux.dbf 220 /usr/lib/oracle/xe/oradata/XE/undo.dbf 210 /usr/lib/oracle/xe/oradata/XE/system.dbf 340 SQL>
Restart the database just to make sure:
[oracle@localhost ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 11:37:12 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> startup 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. Database opened. SQL>
Lost Database Server
This is a more sever 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 XE Backup, and being backed up to an external system on a daily basis.
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>
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>