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.