Skip to content

How to Resolve ORA-01220: file based sort illegal before database is open

  • by

When I queried the archive gap on the standby database which is a 9i database, I got this error:

SQL> select * from v$archive_gap;
select * from v$archive_gap
ERROR at line 1:
ORA-01220: file based sort illegal before database is open

SQL> show parameter workarea_size_policy;

NAME                  TYPE              VALUE
--------------------- ----------------- ---------------
workarea_size_policy  string            MANUAL

Normally, WORKAREA_SIZE_POLICY is AUTO. Has anyone ever changed the parameter? I don't think so. I think it's a run-time bug or some kind of memory restriction. Also, SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE are all set correctly.

I tried to set the parameter back to AUTO, but PMON shutdown abort (or say "terminate") the database.

SQL> alter system set workarea_size_policy=AUTO;
alter system set workarea_size_policy=AUTO
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04032: pga_aggregate_target must be set before switching to auto mode

In the alert log:

PMON: terminating instance due to error 4032

I restart the database after that, and the parameter is back to AUTO.

SQL> show parameter workarea_size_policy

NAME                  TYPE              VALUE
--------------------- ----------------- ---------------
workarea_size_policy  string            AUTO

The key reaction to the error in this whole case is to restart the standby database.

Leave a Reply

Your email address will not be published.