ERROR: ORA-00257: archiver error. Connect internal only, until freed.

user10674559

    Hi All,

    OS: OEL 5.4

    DB: 11gR2 RAC 3 nodes

    I have installed RAC to practice. I have created ASM diskgroup FRA to store backups and archive logs. I turned on archive yesterday and as I didn't allocate much space, I am getting below error for any user operations.

    ERROR:

    ORA-00257: archiver error. Connect internal only, until freed.

    now when I try to delete it from RMAN I get error. What is the best way to delete archive logs and get system up and running.

    RMAN> crosscheck archivelog all;

     

     

    RMAN-00571: ===========================================================

    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

    RMAN-00571: ===========================================================

    RMAN-03002: failure of crosscheck command at 07/01/2016 07:42:36

    RMAN-12010: automatic channel allocation initialization failed

    RMAN-06403: could not obtain a fully authorized session

    ORA-01034: ORACLE not available

    ORA-27101: shared memory realm does not exist

    Linux Error: 2: No such file or directory

     

     

    RMAN> delete noprompt expired archivelog all;

     

     

    RMAN-00571: ===========================================================

    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

    RMAN-00571: ===========================================================

    RMAN-03002: failure of delete command at 07/01/2016 07:42:53

    RMAN-06403: could not obtain a fully authorized session

    ORA-01034: ORACLE not available

    ORA-27101: shared memory realm does not exist

    Linux Error: 2: No such file or directory

     

    Thanks

      • 1. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
        JuanM

        First, is at least one instance available?

         

        [oracle]$ srvctl status database -d <db_unique_name>

        • 2. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
          rchem

          ORA-01034: ORACLE not available

          ORA-27101: shared memory realm does not exist

          Linux Error: 2: No such file or directory

           

          Your database is not up, check the status of database.

           

          srvctl status database -d <dbname>

          • 3. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
            user10674559

            srvctl status database -d orcl

            Instance orcl1 is running on node host01

            Instance orcl2 is running on node host02

            Instance orcl3 is running on node host03

            • 4. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
              rchem

              Did you set the correct ORACLE_SID , also check the ORACLE_HOME setting as well.

              1 位用户发现它有用
              • 5. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
                JuanM

                user10674559 wrote:

                 

                srvctl status database -d orcl

                Instance orcl1 is running on node host01

                Instance orcl2 is running on node host02

                Instance orcl3 is running on node host03

                Connect to any instance as sysdba and show the results of:

                 

                [oracle]$ export ORACLE_SID=orcl1

                 

                [oracle]$ sqlplus /nolog

                 

                SQL> connect / as sysdba

                 

                SQL> show parameters area

                 

                SQL> select name, round(space_limit/1048576),round(space_used/1048576), round((round(space_used/1048576)*100)/(round(space_limit/1048576))) pct_used

                from  v$RECOVERY_FILE_DEST;

                 

                SQL>SELECT * FROM   V$RECOVERY_AREA_USAGE;

                 

                Regards,

                Juan M

                • 6. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
                  user10674559

                  Thanks Juan M and rchem for update. Here is what you asked:

                   

                  export ORACLE_SID=orcl1

                  sqlplus /nolog

                   

                   

                  SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 1 08:28:02 2016

                   

                  Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                   

                  SQL> connect / as sysdba

                  Connected.

                  SQL> show parameters area

                   

                   

                  NAME                                 TYPE        VALUE

                  ------------------------------------ ----------- ------------------------------

                  bitmap_merge_area_size               integer     1048576

                  create_bitmap_area_size              integer     8388608

                  hash_area_size                       integer     131072

                  sort_area_retained_size              integer     0

                  sort_area_size                       integer     65536

                  workarea_size_policy                 string      AUTO

                   

                  SQL>  select name, round(space_limit/1048576),round(space_used/1048576), round((round(space_used/1048576)*100)/(round(space_limit/1048576))) pct_used

                  from  v$RECOVERY_FILE_DEST;  2 

                   

                  NAME

                  --------------------------------------------------------------------------------

                  ROUND(SPACE_LIMIT/1048576) ROUND(SPACE_USED/1048576)   PCT_USED

                  -------------------------- ------------------------- ----------

                  +FRA

                                        2000                      1980         99

                   

                  SQL>SELECT * FROM   V$RECOVERY_AREA_USAGE;

                  FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

                  -------------------- ------------------ ------------------------- ---------------

                  CONTROL FILE                         .9                         0               1

                  REDO LOG                           15.3                         0               6

                  ARCHIVED LOG                       19.7                        .2              13

                  BACKUP PIECE                         63                         0               6

                  IMAGE COPY                            0                         0               0

                  FLASHBACK LOG                         0                         0               0

                  FOREIGN ARCHIVED LOG                  0                         0               0

                   

                   

                  7 rows selected.

                  • 7. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
                    JuanM

                    user10674559 wrote:

                     

                    Thanks Juan M and rchem for update. Here is what you asked:

                     

                    export ORACLE_SID=orcl1

                    sqlplus /nolog

                     

                     

                    SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 1 08:28:02 2016

                     

                    Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                     

                    SQL> connect / as sysdba

                    Connected.

                    SQL> show parameters area

                     

                     

                    NAME                                 TYPE        VALUE

                    ------------------------------------ ----------- ------------------------------

                    bitmap_merge_area_size               integer     1048576

                    create_bitmap_area_size              integer     8388608

                    hash_area_size                       integer     131072

                    sort_area_retained_size              integer     0

                    sort_area_size                       integer     65536

                    workarea_size_policy                 string      AUTO

                     

                    SQL>  select name, round(space_limit/1048576),round(space_used/1048576), round((round(space_used/1048576)*100)/(round(space_limit/1048576))) pct_used

                    from  v$RECOVERY_FILE_DEST;  2

                     

                    NAME

                    --------------------------------------------------------------------------------

                    ROUND(SPACE_LIMIT/1048576) ROUND(SPACE_USED/1048576)   PCT_USED

                    -------------------------- ------------------------- ----------

                    +FRA

                                          2000                      1980         99

                     

                    SQL>SELECT * FROM   V$RECOVERY_AREA_USAGE;

                    FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

                    -------------------- ------------------ ------------------------- ---------------

                    CONTROL FILE                         .9                         0               1

                    REDO LOG                           15.3                         0               6

                    ARCHIVED LOG                       19.7                        .2              13

                    BACKUP PIECE                         63                         0               6

                    IMAGE COPY                            0                         0               0

                    FLASHBACK LOG                         0                         0               0

                    FOREIGN ARCHIVED LOG                  0                         0               0

                     

                     

                    7 rows selected.

                    Hi,

                     

                    If you have space in +FRA asm disk group, try increasing the FRA like:

                     

                    SQL>ALTER SYSTEM SET db_recovery_file_dest_size=10G SCOPE=BOTH SID='*';

                     

                    Then, connect to RMAN and purge some archived redo logs (because you said "RAC to practice").

                    RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'TRUNC(SYSDATE)';

                     

                    Then, work in how to backup your database and to administer the FRA space usage.

                     

                    (edited entry):

                    Also your current backups of database use 63% of your FRA, you can drop the backups if not needed or make the db_recovery_file_dest_size bigger.

                     

                     

                    Regards,

                    Juan M

                    • 8. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
                      user10674559

                      Thanks Juan M and rchem. I was able to delete some backups and freeup space. I think not setting up proper SID when connecting to RMAN was the main issue to begin with.

                       

                      Thanks,

                      Jigisha

                      • 9. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
                        user10674559

                        so what are other ways to free up space in FRA other than RMAN?

                        • 10. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
                          JuanM

                          user10674559 wrote:

                           

                          so what are other ways to free up space in FRA other than RMAN?

                          There is none.

                           

                          You can configure your RMAN environment in order to help you to identify what files are candidates to delete and free space.

                          This could be done at automatic way managed by Oracle. BUT you have to make a proper configuration.

                           

                          I advise to spend time reading carefully the following documentation:

                          Maintaining RMAN Backups and Repository Records - 11g Release 2 (11.2)

                           

                          Cheers,

                          Juan M

                          • 12. Re: ERROR: ORA-00257: archiver error. Connect internal only, until freed.
                            Peter.Sorger

                            I think you need to stop the database, start it in mount, resize the db_recovery_file_dest_size parameter and alter database open.. later you should backup your archlogs like backup as compressed backupset archivelog all delete input format '/tmp/to_disk/%d_%U.bck' ;

                            and the last step would be to delete archivelog all backed up 1 times to device type disk completed before 'SYSDATE-1' (I have 30 days of archlogs in my FRA, the DB is not producing a lot of archlogs)