Standby Database Cannot RESYNC Recovery Catalog (Sort)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Standby Database Cannot RESYNC Recovery Catalog (Sort)

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    Standby Database Cannot RESYNC Recovery Catalog (Sort)

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    9i uses pga_aggregate_target not sort_area_size

  3. #3
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    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!

    :-)

  4. #4
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    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.

  5. #5
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width