ORA-27092: size of file exceeds file size limit of the process

Database restore was needed.  SYSADMIN restored the backup to a filesystem.  I mounted the database and started checking the available backups.

RMAN> list datafilecopy all;
using target database control file instead of recovery catalog
List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
4806    1    A 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_system_4sdvwqr5_.dbf
4807    2    X 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_undo_4sdvw8lh_.dbf
4805    3    A 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_sysaux_4sdvxwt4_.dbf
4809    4    X 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_orcl_dat_4sdvwyro_.dbf
4808    5    X 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_orcl_ind_4sdvx5wn_.dbf
4810    6    X 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_orcl_lob_4sdvxf23_.dbf

Backup was a datafile imagecopy.  But few of the backup files were showing status as expired, value ‘X’ in S column. S column may normally have 3 values

A (available)
U (unavailable)
X (backup pieces is expired)

But files were physically present at the location & there was no reason why it should be showing them as expired.

So I tried cataloging them manually. But I got following error

RMAN>CATALOG DATAFILECOPY
'/u02/orcl/datafile/o1_mf_orcl_dat_4sdvwyro_.dbf';
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of catalog command on default channel
at 04/05/2011 10:57:32
ORA-19625: error identifying file
/u02/orcl/datafile/o1_mf_orcl_dat_4sdvwyro_.dbf
ORA-27092: size of file exceeds file size limit of the process
Additional information: 131071
Additional information: 1651201

This means 131071 oracle blocks is the file size limit of the user trying to startup db and 1651201 is the original data file size in blocks and hence the problem.
So what was really happening?

SYSADMIN copied the file using privileged user. But the login I’m using, had restricted resources (checked ulimit -a). Found the problem as data seg size & file size were not ‘unlimited’. So asked SYSADMIN to change the limits. 
After changing the limit, logged-in again (so that ulimit settings will come into effect). Then re-cataloged the file

RMAN> CATALOG DATAFILECOPY
'/u02/orcl/datafile/o1_mf_orcl_dat_4sdvwyro_.dbf';
RMAN> CATALOG DATAFILECOPY
'/u02/orcl/datafile/o1_mf_undo_4sdvw8lh_.dbf';
RMAN> CATALOG DATAFILECOPY
'/u02/orcl/datafile/o1_mf_orcl_ind_4sdvx5wn_.dbf';
RMAN> CATALOG DATAFILECOPY
'/u02/orcl/datafile/o1_mf_orcl_lob_4sdvxf23_.dbf';
RMAN> list datafilecopy all;

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
4806    1    A 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_system_4sdvwqr5_.dbf
4816    2    A 05-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_undo_4sdvw8lh_.dbf
4805    3    A 03-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_sysaux_4sdvxwt4_.dbf
4815    4    A 05-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_orcl_dat_4sdvwyro_.dbf
4817    5    A 05-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_orcl_ind_4sdvx5wn_.dbf
4818    6    A 05-APR-11       208667312  30-MAR-11      
/u02/orcl/datafile/o1_mf_orcl_lob_4sdvxf23_.dbf

After successfully cataloging the files, restored & recovered the database.

 

RMAN> restore database;
Starting restore at 05-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1088 devtype=DISK
channel ORA_DISK_1: restoring datafile 00001
input datafile copy recid=4806 stamp=747523947
filename=/u02/orcl/datafile/o1_mf_system_4sdvwqr5_.dbf
destination for restore of datafile 00001:
+ORADATA1/oradata/orcl/system_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output filename=
+ORADATA1/oradata/orcl/system_01.dbf recid=4819 stamp=747664568
channel ORA_DISK_1: restoring datafile 00002
input datafile copy recid=4816 stamp=747664415
filename=/u02/orcl/datafile/o1_mf_undo_4sdvw8lh_.dbf
destination for restore of datafile 00002:
+ORADATA1/oradata/orcl/undo_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output filename=
+ORADATA1/oradata/orcl/undo_01.dbf recid=4820 stamp=747664648
channel ORA_DISK_1: restoring datafile 00003
input datafile copy recid=4805 stamp=747523947
filename=/u02/orcl/datafile/o1_mf_sysaux_4sdvxwt4_.dbf
destination for restore of datafile 00003:
+ORADATA1/oradata/orcl/sysaux_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output filename=+ORADATA1/oradata/orcl/sysaux_01.dbf
recid=4821 stamp=747664656
channel ORA_DISK_1: restoring datafile 00004
input datafile copy recid=4815 stamp=747664398
filename=/u02/orcl/datafile/o1_mf_orcl_dat_4sdvwyro_.dbf
destination for restore of datafile 00004:
+ORADATA1/oradata/orcl/db_data_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output filename=+ORADATA1/oradata/orcl/db_data_01.dbf
recid=4822 stamp=747664945
channel ORA_DISK_1: restoring datafile 00005
input datafile copy recid=4817 stamp=747664431
filename=/u02/orcl/datafile/o1_mf_orcl_ind_4sdvx5wn_.dbf
destination for restore of datafile 00005:
+ORADATA1/oradata/orcl/db_index_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output filename=+ORADATA1/oradata/orcl/db_index_01.dbf
recid=4823 stamp=747665143
channel ORA_DISK_1: restoring datafile 00006
input datafile copy recid=4818 stamp=747664445
filename=/u02/orcl/datafile/o1_mf_orcl_lob_4sdvxf23_.dbf
destination for restore of datafile 00006:
+ORADATA1/oradata/orcl/db_lob_data_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output filename=+ORADATA1/oradata/orcl/db_lob_data_01.dbf
recid=4824 stamp=747665766
Finished restore at 05-APR-11

Recover database  

RMAN> recover database noredo;
Starting recover at 05-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001:
+ORADATA1/oradata/orcl/system_01.dbf
destination for restore of datafile 00002:
+ORADATA1/oradata/orcl/undo_01.dbf
destination for restore of datafile 00003:
+ORADATA1/oradata/orcl/sysaux_01.dbf
destination for restore of datafile 00004:
+ORADATA1/oradata/orcl/db_data_01.dbf
destination for restore of datafile 00005:
+ORADATA1/oradata/orcl/db_index_01.dbf
destination for restore of datafile 00006:
+ORADATA1/oradata/orcl/db_lob_data_01.dbf
channel ORA_DISK_1: reading from backup piece
/u02/orcl/backupset/2011_04_02/o1_mf_nnnd1_TAG20110402T214142_6shn2r6k_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/orcl/backupset/2011_04_02/o1_mf_nnnd1_TAG20110402T214142_
6shn2r6k_.bkp tag=TAG20110402T214142
channel ORA_DISK_1: restore complete, elapsed time: 00:02:06
Finished recover at 05-APR-11

Open database using resetlogs

RMAN> alter database open resetlogs;
database opened
Advertisements
This entry was posted in Oracle Database Backup, Oracle Recovery Manager and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s