Environment

Make sure ORACLE_HOME, ORACLE_SID and PATH are set correctly

– rman also comes with X11R6 so make sure that ORACLE_HOME/bin is first in your PATH

Log Into RMAN

rman target /

Find the mismatched objects

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           1681   01-FEB-10         
  Backup Piece       1681   01-FEB-10          /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/backupset/2010_02_01/o1_mf_nnndf_TAG20100201T040227_5pfjpoo0_.bkp

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/backupset/2010_02_01/o1_mf_nnndf_TAG20100201T040227_5pfjpoo0_.bkp

 

The /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/backupset/2010_02_01/o1_mf_nnndf_TAG20100201T040227_5pfjpoo0_.bkp file is missing.

Run CROSSCHECK to set its status

RMAN> crosscheck backuppiece ‘/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/backupset/2010_02_01/o1_mf_nnndf_TAG20100201T040227_5pfjpoo0_.bkp’ ;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/backupset/2010_02_01/o1_mf_nnndf_TAG20100201T040227_5pfjpoo0_.bkp recid=1681 stamp=709790549
Crosschecked 1 objects

 

Now delete the objects

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           1681   01-FEB-10         
  Backup Piece       1681   01-FEB-10          /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/backupset/2010_02_01/o1_mf_nnndf_TAG20100201T040227_5pfjpoo0_.bkp

deleted backup piece
backup piece handle=/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/backupset/2010_02_01/o1_mf_nnndf_TAG20100201T040227_5pfjpoo0_.bkp recid=1681 stamp=709790549
Deleted 1 objects

 

Problem

ORA-01654: unable to extend index SYS.I_FILE#_BLOCK# by 128 in tablespace SYSTEM

Solution

Check the size and maxsize of the data files in the SYSTEM tablespace

select file_name, bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name=’SYSTEM’;

Increase the size of the data file;

alter database datafile ‘<Path to data file>’ resize <larger size>

First make sure the database is in archive log mode and running backups on a daily basis. See Oracle XE Backup for instructions on how to do this.

Reverting to a Previous Backup

If something has gone wrong with the database, such as deleting or corrupting a database file, and you want to restore it to the last backup run:

# su – oracle

# $ORACLE_HOME/config/scripts/recover.sh

If the database is in archivelog mode then it will restore from the last backup and a complete recovery of all the archive log files is attempted.

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>

Setup Bash

Log into the system as root and su to oracle

#su – oracle

Copy over the bash files from the /etc/skel directory

cp /etc/skel/.bash* .

Edit .bash_profile and add ORACLE_HOME, ORACLE_SID and add $ORACLE_HOME/bin to PATH

vi .bash_profile

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

Log out and back in again and the environment will be setup with bash.  Now its easy to access sqlplus

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 25 01:23:43 2008

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

SQL> connect / as sysdba
Connected.
SQL> show sga

Total System Global Area  289406976 bytes
Fixed Size                  1258488 bytes
Variable Size              92277768 bytes
Database Buffers          192937984 bytes
Redo Buffers                2932736 bytes
SQL>

Problem

When going to http://servername:port/content/app you get a http 503 error.

The log file <mid-tier>/j2ee/OC4J_Content/application-deployments/content/OC4J_Content_default_island_1 has the following error message:

java.sql.SQLException: ORA-12705: invalid or unknown NLS parameter value specified

Solution

Change the IFS.SERVICE.JDBC.DriveType to thin for the Service Configuration you are using (default is SmallServiceConfiguration).

To do this log into EM for the mid-tier and go to Content, select Service Configurations at the bottom of the page and then select your service configuration.  Search for JDBC and change the value of IFS.SERVICE.JDBC.DriveType to thin.

Restart Content and try the website again.

 

Problem

If you try and access a ESB webservice on a webserver other than localhost and port 8888 the following error message is returned and the ESB flow is not run:

java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send failed: Connection refused

 

Resolution

The ESB System that the Web Service is running under has not been configured to use the correct host and port.

In ESB Control click on the System that is running the Web Service (ie DefaultSystem).

Change:
Virtual Host = <hostname of the application server>
Port = Port number OC4J is running on

Oracle XE Connection String

username/password@[//]host[:port][/service_name]

• port is required if the port number is not 1521.
• service_name defaults to XE.

Problem

The database will not mount when started

SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1263200 bytes
Variable Size             406849952 bytes
Database Buffers          192937984 bytes
Redo Buffers                7122944 bytes
Database mounted.
ORA-16038: log 3 sequence# 1933 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1:
‘/home/oracle/oracle/product/10.2.0/oradata/mca10gR2/redo03.log’

Solution

Increase the size of the DB_RECOVERY_FILE_DEST_SIZE

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest_size           big integer 8G
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 10G;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1263200 bytes
Variable Size             411044256 bytes
Database Buffers          188743680 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
SQL>