few days ago I found working with RMAN with the message described in the title. It was still pre-production base and less than 50 GB in size with a little over 30 datafiles. Activity in that time was to restore the more than 30 datafiles with the control files, that is, to simulate the worst case scenario where "all" the database files are lost. With all quotes I am referring only to files that can be restored from RMAN, because the database includes other files that are not considered critical to jump-start a backup.
The full message is: RMAN-00571
After deleting the control files with more than 30 filesystem datafiles, the command to restore from RMAN simply threw the previous message.
According to Oracle documentation, the message ORA-01220 concerns that tried to run a query, but the buffer (memory) for systems not large enough to carry it out. It deals specifically with the parameters and SORT_AREA_SIZE SORT_AREA_RETAINED_SIZE . TYPE NAME
by default in 10g, the SORT_AREA_SIZE parameter has a value of 64K and SORT_AREA_RETAINED_SIZE parameter is 0 because RMAN requires management to make a buffer size but not enough to do, go to the second resource is TEMP (temporary tablespace) However, as this database far only been found in MOUNT can not use the TEMP, hence the only recourse is to increase the value of these two parameters.
10 Mb should be sufficient to launch the restore without problems. Once recovered and found to support the instance opens without problems is necessary to return the settings to their original values. As a dba does not take long to realize what it is and its possible solution, as implementer of solutions without knowledgeable support RMAN or Oracle, such as HP Data Protector or EMC Avamar creating custom scripts to launch them from the Recovery Manager could be a headache because they have no access to modify parameters, which in fact they should not only permission - or worse still take hours or days to find what it is about the problem and implement the possible solution.
The full message is: RMAN-00571
: ===================================== ======================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==== ===========
RMAN-00571: =============================================
============== RMAN-03002: failure of restore command at 11/08/2010 16:36:39
ORA-01220: file based sort illegal database is open
Before RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==== ===========
RMAN-00571: =============================================
============== RMAN-03002: failure of restore command at 11/08/2010 16:36:39
ORA-01220: file based sort illegal database is open
After deleting the control files with more than 30 filesystem datafiles, the command to restore from RMAN simply threw the previous message.
According to Oracle documentation, the message ORA-01220 concerns that tried to run a query, but the buffer (memory) for systems not large enough to carry it out. It deals specifically with the parameters and SORT_AREA_SIZE SORT_AREA_RETAINED_SIZE . TYPE NAME
; VALUE ------------------------- -------- -------
nls_sort string Sort_area_retained_size
SORT_AREA_SIZE integer integer 0 65536
nls_sort string Sort_area_retained_size
SORT_AREA_SIZE integer integer 0 65536
by default in 10g, the SORT_AREA_SIZE parameter has a value of 64K and SORT_AREA_RETAINED_SIZE parameter is 0 because RMAN requires management to make a buffer size but not enough to do, go to the second resource is TEMP (temporary tablespace) However, as this database far only been found in MOUNT can not use the TEMP, hence the only recourse is to increase the value of these two parameters.
SQL> alter system set SORT_AREA_SIZE = 10485760 scope = spfile;
System altered.
SQL> alter system set sort_area_retained_size = 10485760 scope = spfile;
System altered.
SQL> shutdown Immediate;
ORA-01109: database not open Database
dismount.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL> show parameter sort
NAME TYPE VALUE
------------------------- -------- -------
nls_sort string Sort_area_retained_size
SORT_AREA_SIZE integer integer 10485760 10485760
System altered.
SQL> alter system set sort_area_retained_size = 10485760 scope = spfile;
System altered.
SQL> shutdown Immediate;
ORA-01109: database not open Database
dismount.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL> show parameter sort
NAME TYPE VALUE
------------------------- -------- -------
nls_sort string Sort_area_retained_size
SORT_AREA_SIZE integer integer 10485760 10485760
10 Mb should be sufficient to launch the restore without problems. Once recovered and found to support the instance opens without problems is necessary to return the settings to their original values. As a dba does not take long to realize what it is and its possible solution, as implementer of solutions without knowledgeable support RMAN or Oracle, such as HP Data Protector or EMC Avamar creating custom scripts to launch them from the Recovery Manager could be a headache because they have no access to modify parameters, which in fact they should not only permission - or worse still take hours or days to find what it is about the problem and implement the possible solution.
0 comments:
Post a Comment