Standby Database Cannot RESYNC Recovery Catalog (Sort)

[AJW_ID01]

Hi,

I have a standby database from which I am taking backups, and I am wanting to sync the backup information to a Recovery Catalog. Sadly, I am getting a sort error problem (ORA-01220: file based sort illegal before database is open).

Now, I imagined this was due to low sort_area_[retained]_size, and promptly looked (I inherited some databases recently). Sure enough, the sort/retained was pretty low at 100,000. I then proceeded to cancel recovery, shut instance, restart with successively larger values of sort. (This is 9.2.0.4 version btw).

Everytime I try a resync, it almost immediately returns this 01220 error, even with sort_area_size and the retained variant set at 1GB.

I can't believe it is exhausting this, and am loathe to try increasing it still further.

Anyone encountered this problem before, have any suggestions?

Best regards,

- Tony.

[davey23uk]

9i uses pga_aggregate_target not sort_area_size

[AJW_ID01]

Aaaah god yeah!

What a n00b to forget that. Just got thrown with the RMAN session dynamically trying to resize 'sort_area_size' on it's own. (Tries about 5 times, incrementing by 10 MB per time before erroring).

aggregate_target might not be set, but it certainly sounds like the only possibility to investigate...

Thanks for the pointer!

:-)

[AJW_ID01]

Phooey!

Still no joy with this. Was pretty certain this would sort it but no:

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: MEDPRD (DBID=3971709480)
connected to recovery catalog database

RMAN> resync catalog;

resync got ORA-1220, retrying with sort_area_size = 1010MB
resync got ORA-1220, retrying with sort_area_size = 1020MB
resync got ORA-1220, retrying with sort_area_size = 1030MB
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 10/18/2008 12:08:13
ORA-01220: file based sort illegal before database is open
RMAN-11003: failure during parse/execution of SQL statement: alter session set sort_area_size=1080033280
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00600: internal error code, arguments: [925], [hash_area_size], [-2134900736], [], [], [], [], []

RMAN> exit


Recovery Manager complete.
[oracle@contentstby2 oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 18 12:08:42 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> show parameter sort

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
sort_area_retained_size integer 1048576000
sort_area_size integer 1048576000
SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 2097152000
SQL>

So, seem to have plenty of sort set, would presume that this ought to be enough. With PGA AT set at this level, it should still ( as far as I know ) be able to avail of 400M for sort operations.

Any more ideas anyone?

T.

[AJW_ID01]

I've managed to get a little further. Seems it DID actually require more sort area than the 400M, surprisingly!! Setting workarea_size_policy to manual allowed it to make progress (so must have used the sort_area value thereafter and succeeded!).

Hit with another snag now, but am at least past first base.

Many thanks.

- Tony.