Ошибка ora 3113

I have a 400 line sql query which is throwing exception withing 30 seconds

ORA-03113: end-of-file on communication channel

Below are things to note:

  1. I have set the timeout as 10 mins
  2. There is one last condition when removed resolves this error.
  3. This error came only recently when I analyzed indexes.

The troubling condition is like this:

AND UPPER (someMultiJoin.someColumn) LIKE UPPER ('%90936%')

So my assumption is that the query is getting terminated from the server side apparently because its identified as a resource hog.

Is my assumption appropriate ? How should I go about to fix this problem ?

EDIT: I tried to get the explain plan of faulty query but the explain plan query also gives me an ORA-03113 error. I understand that my query is not very performant but why should that be a reason for ORA-03113 error. I am trying to run the query from toad and there are no alert log or trace generated, my db version is
Oracle9i Enterprise Edition Release 9.2.0.7.0 — Production

asked Jul 28, 2010 at 7:01

Ravi Gupta's user avatar

Ravi GuptaRavi Gupta

4,47813 gold badges55 silver badges85 bronze badges

4

One possible cause of this error is a thread crash on the server side. Check whether the Oracle server has generated any trace files, or logged any errors in its alert log.

You say that removing one condition from the query causes the problem to go away. How long does the query take to run without that condition? Have you checked the execution plans for both versions of the query to see if adding that condition is causing some inefficient plan to be chosen?

answered Jul 28, 2010 at 12:32

Dave Costa's user avatar

Dave CostaDave Costa

47.4k8 gold badges58 silver badges72 bronze badges

2

You can safely remove the «UPPER» on both parts if you are using the like with numbers (that are not case sensitive), this can reduce the query time to check the like sentence

AND UPPER (someMultiJoin.someColumn) LIKE UPPER ('%90936%')

Is equals to:

AND someMultiJoin.someColumn LIKE '%90936%'

Numbers are not affected by UPPER (and % is independent of character casing).

answered Aug 11, 2010 at 15:50

Dubas's user avatar

DubasDubas

2,8551 gold badge25 silver badges37 bronze badges

I’ve had similar connection dropping issues with certain variations on a query. In my case connections dropped when using rownum under certain circumstances. It turned out to be a bug that had a workaround by adjusting a certain Oracle Database configuration setting. We went with a workaround until a patch could be installed. I wish I could remember more specifics or find an old email on this but I don’t know that the specifics would help address your issue. I’m posting this just to say that you’ve probably encountered a bug and if you have access to Oracle’s support site (support.oracle.com) you’ll likely find that others have reported it.

Edit:
I had a quick look at Oracle support. There are more than 1000 bugs related to ORA-03113 but I found one that may apply:

Bug 5015257: QUERY FAILS WITH ORA-3113 AND COREDUMP WHEN QUERY_REWRITE_ENABLED=’TRUE’

To summarize:

  • Identified in 9.2.0.6.0 and fixed in 10.2.0.1
  • Running a particular query
    (not identified) causes ORA-03113
  • Running explain on query does the
    same
  • There is a core file in
    $ORACLE_HOME/dbs
  • Workaround is to set
    QUERY_REWRITE_ENABLED to false: alter
    system set query_rewrite_enabled =
    FALSE;

Another possibility:

Bug 3659827: ORA-3113 FROM LONG RUNNING QUERY

  • 9.2.0.5.0 through 10.2.0.0
  • Problem: Customer has long running query that consistently produces ORA-3113 errros.
    On customers system they receive core.log files but do not receive any errors
    in the alert.log. On test system I used I receivded ORA-7445 errors.
  • Workaround: set «_complex_view_merging»=false at session level or instance level.

answered Aug 13, 2010 at 1:10

jlpp's user avatar

jlppjlpp

1,5645 gold badges23 silver badges36 bronze badges

From the information so far it looks like an back-end crash, as Dave Costa suggested some time ago. Were you able to check the server logs?

Can you get the plan with set autotrace traceonly explain? Does it happen from SQL*Plus locally, or only with a remote connection? Certainly sounds like an ORA-600 on the back-end could be the culprit, particularly if it’s at parse time. The successful run taking longer than the failing one seems to rule out a network problem. I suspect it’s failing quite quickly but the client is taking up to 30 seconds to give up on the dead connection, or the server is taking that long to write trace and core files.

Which probably leaves you the option of patching (if you can find a relevant fix for the specific ORA-600 on Metalink) or upgrading the DB; or rewriting the query to avoid it. You may get some ideas for how to do that from Metalink if it’s a known bug. If you’re lucky it might be as simple as a hint, if the extra condition is having an unexpected impact on the plan. Is someMultiJoin.someColumn part of an index that’s used in the successful version? It’s possible the UPPER is confusing it and you could persuade it back on to the successful plan by hinting it to use the index anyway, but that’s obviously rather speculative.

answered Aug 10, 2010 at 11:21

Alex Poole's user avatar

Alex PooleAlex Poole

184k11 gold badges180 silver badges319 bronze badges

0

It means you have been disconnected. This not likely to be due to being a resource hog.

I have seen where the connection to the DB is running over a NAT and because there is no traffic it closes the tunnel and thus drops the connection. Generally if you use connection pooling you won’t get this.

answered Jul 28, 2010 at 7:31

Daniel's user avatar

1

As @Daniel said, the network connection to the server is being broken. You might take a look at End-of-file on communication channel to see if it offers any useful suggestions.

Share and enjoy.

Community's user avatar

answered Jul 28, 2010 at 11:27

Bob Jarvis - Слава Україні's user avatar

This is often a bug in the Cost Based Optimizer with complex queries.

What you can try to do is to change the execution plan. E.g. use WITH to pull some subquerys out. Or use the SELECT /*+ RULE */ hint to prevent Oracle from using the CBO. Also dropping the statistics helps, because Oracle then uses another execution plan.

If you can update the database, make a test installation of 9.2.0.8 and see if the error is gone there.

Sometimes it helps to make a dump of the schema, drop everything in it and import the dump again.

answered Aug 6, 2010 at 6:45

andrem's user avatar

andremandrem

4012 silver badges4 bronze badges

I was having the same error, in my case what was causing it was the length of the query.

By reducing said length, I had no more problems.

answered Sep 2, 2022 at 12:10

Alexander Martins's user avatar

I have a 400 line sql query which is throwing exception withing 30 seconds

ORA-03113: end-of-file on communication channel

Below are things to note:

  1. I have set the timeout as 10 mins
  2. There is one last condition when removed resolves this error.
  3. This error came only recently when I analyzed indexes.

The troubling condition is like this:

AND UPPER (someMultiJoin.someColumn) LIKE UPPER ('%90936%')

So my assumption is that the query is getting terminated from the server side apparently because its identified as a resource hog.

Is my assumption appropriate ? How should I go about to fix this problem ?

EDIT: I tried to get the explain plan of faulty query but the explain plan query also gives me an ORA-03113 error. I understand that my query is not very performant but why should that be a reason for ORA-03113 error. I am trying to run the query from toad and there are no alert log or trace generated, my db version is
Oracle9i Enterprise Edition Release 9.2.0.7.0 — Production

asked Jul 28, 2010 at 7:01

Ravi Gupta's user avatar

Ravi GuptaRavi Gupta

4,47813 gold badges55 silver badges85 bronze badges

4

One possible cause of this error is a thread crash on the server side. Check whether the Oracle server has generated any trace files, or logged any errors in its alert log.

You say that removing one condition from the query causes the problem to go away. How long does the query take to run without that condition? Have you checked the execution plans for both versions of the query to see if adding that condition is causing some inefficient plan to be chosen?

answered Jul 28, 2010 at 12:32

Dave Costa's user avatar

Dave CostaDave Costa

47.4k8 gold badges58 silver badges72 bronze badges

2

You can safely remove the «UPPER» on both parts if you are using the like with numbers (that are not case sensitive), this can reduce the query time to check the like sentence

AND UPPER (someMultiJoin.someColumn) LIKE UPPER ('%90936%')

Is equals to:

AND someMultiJoin.someColumn LIKE '%90936%'

Numbers are not affected by UPPER (and % is independent of character casing).

answered Aug 11, 2010 at 15:50

Dubas's user avatar

DubasDubas

2,8551 gold badge25 silver badges37 bronze badges

I’ve had similar connection dropping issues with certain variations on a query. In my case connections dropped when using rownum under certain circumstances. It turned out to be a bug that had a workaround by adjusting a certain Oracle Database configuration setting. We went with a workaround until a patch could be installed. I wish I could remember more specifics or find an old email on this but I don’t know that the specifics would help address your issue. I’m posting this just to say that you’ve probably encountered a bug and if you have access to Oracle’s support site (support.oracle.com) you’ll likely find that others have reported it.

Edit:
I had a quick look at Oracle support. There are more than 1000 bugs related to ORA-03113 but I found one that may apply:

Bug 5015257: QUERY FAILS WITH ORA-3113 AND COREDUMP WHEN QUERY_REWRITE_ENABLED=’TRUE’

To summarize:

  • Identified in 9.2.0.6.0 and fixed in 10.2.0.1
  • Running a particular query
    (not identified) causes ORA-03113
  • Running explain on query does the
    same
  • There is a core file in
    $ORACLE_HOME/dbs
  • Workaround is to set
    QUERY_REWRITE_ENABLED to false: alter
    system set query_rewrite_enabled =
    FALSE;

Another possibility:

Bug 3659827: ORA-3113 FROM LONG RUNNING QUERY

  • 9.2.0.5.0 through 10.2.0.0
  • Problem: Customer has long running query that consistently produces ORA-3113 errros.
    On customers system they receive core.log files but do not receive any errors
    in the alert.log. On test system I used I receivded ORA-7445 errors.
  • Workaround: set «_complex_view_merging»=false at session level or instance level.

answered Aug 13, 2010 at 1:10

jlpp's user avatar

jlppjlpp

1,5645 gold badges23 silver badges36 bronze badges

From the information so far it looks like an back-end crash, as Dave Costa suggested some time ago. Were you able to check the server logs?

Can you get the plan with set autotrace traceonly explain? Does it happen from SQL*Plus locally, or only with a remote connection? Certainly sounds like an ORA-600 on the back-end could be the culprit, particularly if it’s at parse time. The successful run taking longer than the failing one seems to rule out a network problem. I suspect it’s failing quite quickly but the client is taking up to 30 seconds to give up on the dead connection, or the server is taking that long to write trace and core files.

Which probably leaves you the option of patching (if you can find a relevant fix for the specific ORA-600 on Metalink) or upgrading the DB; or rewriting the query to avoid it. You may get some ideas for how to do that from Metalink if it’s a known bug. If you’re lucky it might be as simple as a hint, if the extra condition is having an unexpected impact on the plan. Is someMultiJoin.someColumn part of an index that’s used in the successful version? It’s possible the UPPER is confusing it and you could persuade it back on to the successful plan by hinting it to use the index anyway, but that’s obviously rather speculative.

answered Aug 10, 2010 at 11:21

Alex Poole's user avatar

Alex PooleAlex Poole

184k11 gold badges180 silver badges319 bronze badges

0

It means you have been disconnected. This not likely to be due to being a resource hog.

I have seen where the connection to the DB is running over a NAT and because there is no traffic it closes the tunnel and thus drops the connection. Generally if you use connection pooling you won’t get this.

answered Jul 28, 2010 at 7:31

Daniel's user avatar

1

As @Daniel said, the network connection to the server is being broken. You might take a look at End-of-file on communication channel to see if it offers any useful suggestions.

Share and enjoy.

Community's user avatar

answered Jul 28, 2010 at 11:27

Bob Jarvis - Слава Україні's user avatar

This is often a bug in the Cost Based Optimizer with complex queries.

What you can try to do is to change the execution plan. E.g. use WITH to pull some subquerys out. Or use the SELECT /*+ RULE */ hint to prevent Oracle from using the CBO. Also dropping the statistics helps, because Oracle then uses another execution plan.

If you can update the database, make a test installation of 9.2.0.8 and see if the error is gone there.

Sometimes it helps to make a dump of the schema, drop everything in it and import the dump again.

answered Aug 6, 2010 at 6:45

andrem's user avatar

andremandrem

4012 silver badges4 bronze badges

I was having the same error, in my case what was causing it was the length of the query.

By reducing said length, I had no more problems.

answered Sep 2, 2022 at 12:10

Alexander Martins's user avatar

Issue

How to troubleshoot Oracle Connection errors.

Oracle drivers require very specific connection statements in a unique format, though a TNSNames.ora file may not always be required. For instance, if you have installed only the Tableau-provided Oracle files and do not have a stand-alone Oracle client, the Oracle error messages will still refer to the TNSNames.ora file, making troubleshooting complicated.

    Environment

    • Tableau Desktop
    • Tableau Server
    • Oracle

    Resolution

    Often, correcting route or naming syntax in the Advanced Oracle Connection dialog box or using your full .WORLD database name resolves most Oracle connection issues. If your connection error requires more troubleshooting, refer to the five common connection errors listed below.

    • ORA-03113: end-of-file on communication channel
    • ORA-12154: TNS: could not resolve the connect identifier specified: HOST value incorrect or Global name incorrect or unknown
    • ORA-12514: TNS listener does not currently know of service requested in connect descriptor: SERVICE value incorrect
    • ORA-12541: TNS: no listener: PORT value incorrect
    • ORA-00932: inconsistent data types

      ORA-03113: end-of-file on communication channel

      ORA-03113 is a catch-all type error for any problem interrupting an Oracle session. There can be numerous causes for this error. Please refer to the list below for some troubleshooting guidance.

      • Refer to Oracle documentation specific to this error: My Oracle Support.
        • Refer to Oracle’s B Troubleshooting Common Errors page.
      • Oracle recommends that you check for network problems and review the SQL*Net setup.
      • If you are connecting to Oracle 9.2.0.5, in many cases the primary cause of this error is Oracle bug 3010227. Ask your Oracle database administrator to apply Oracle patch 9.2.0.6 or another patch appropriate for your server.
      • Set the Oracle initialization parameter ‘STAR_TRANSFORMATION_ENABLED’ to FALSE.
      • Test changing the scheduled time of the extract refresh
      • Alternatively, if you would like to test this issue further follow the optional procedure listed below.

      Step 1 

      From the Tableau Desktop start page, select Connect to Data.

      Step 2 

      On the Connect page, click Oracle, then click OK.

      For more information about completing the connection steps, refer to the Oracle Database topic in the Desktop Help.

      Step 3 

      1. In the join area, hover over the Custom SQL table until the edit icon displays, and then click the icon.
      2. Copy the query in the Edit Custom SQL dialog box.

      SELECT "NumericBins", "Key" as "Key",
      "NumericBins", "Measure E-2" AS "Measure E-2",
      "NumericBins", "Measure E-1" AS "Measure E-1",
      "NumericBins", "Measure E+0" AS "Measure E+0",
      "NumericBins", "Measure E+1" AS "Measure E+1",
      "NumericBins", "Measure E+4" AS "Measure E+4",
      "NumericBins", "Measure E+7" AS "Measure E+7"
      FROM "TestV1", "NumericBins" "NumericBins"

       

      Where «TestV1» is the name of your connection in Tableau.

      Step 4 

      In a SQL session connected to this database, paste and run the query. The expected response is error ORA-7445: exception encountered: core dump, which confirms that the problem is ORA-3113, as expected.

      ORA-12154: TNS: could not resolve the connect identifier specified

      ORA-12154 occurs when the transparent network substrate (TNS) cannot resolve the service name. The service name is specified in the TNSNames.ora file, which is located in your %ORACLE_HOME%\network\admin\ folder. Most often, this error occurs when information in the TNSNames.ora file is incorrect. For example:

      • The .world extension is not included on the database name.
      • The SERVICE_NAME or HOST variable is incorrect.

      To resolve this issue, try one of the three following troubleshooting options, in the order listed below.

      • Option 1: Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora
      • Option 2: Error «ORA-12154» Connecting to Oracle When Not Using TNSNames.ora

      Option 1: Edit TNSNames.ora

       Provide the full database name, including the .world extension in both of the following locations:

      • The TNSNames.ora file.

        And

      • The Server text box of the Connect page.

      Option 2: Ensure that Tableau Server Run As User account has permissions to TNSNames.ora (Tableau Server only)

      If you have Tableau Server installed, complete the procedure below to ensure that the Tableau Server Run As user account has permissions to the location of the TNSNames.ora file. If the Run As user account does not have permissions, Tableau Server is unable to access the Oracle Data source details.

      Step 1 

      Verify the location of the TNSNames.ora file, or the equivalent SQLNET.ora and LDAP.ora files on the machine.

      Note: By default, the TNSNames.ora file is located in <oracle-directory>\network\admin directory. For example, C:\Oracle_Client\network\admin.

      Step 2 

      Confirm that the TNS_ADMIN variable points to the location of the file or files described in step 1.

      Note: To check the TNS_ADMIN variable, click the Start button, and select Control Panel > System. Click Advanced system settings, click the Advanced tab, and click Environmental Variables button.
      The system variable file path must be in UNC format.

      Step 3 

      Open TSM in a browser: https://<tsm-computer-name>:8850 For more information, see Sign in to Tableau Services Manager Web UI.

      Step 4 

      Click the Security tab, and then click the Run As Service Account tab.
      Under Server Run As User, copy the information in the Username field.

      Step 5 

      Go to the folder where the TNSNames.ora file is located.

      Step 6 

      Right-click the folder and select Properties. Click the Security tab and click the Edit button.

      Step 7 

      Under Group or user names, click the Add button.

      Step 8 

      In the Enter the object names to select text box, paste the details of the Run As User account you copied in step 6.

      Step 9 

      When finished, click OK.

      Step 10 

      In the Permissions area,ensure that the Full control and Modify check boxes are selected.

      Step 11 

      Click OK to close the dialog boxes.

        Option 3: Verify that all information in TNSNames.ora is correct

         If the above troubleshooting steps do not resolve the issue, continue reading and complete the procedure to verify the other information in the TNSNames.ora file is provided correctly.

        An example of a TNSNames.ora file is shown here:

        QAORCL10.world =

        (DESCRIPTION =

        (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = MY_HOST_NAME)(PORT = 1521))

        )

        (CONNECT_DATA =

        (SERVICE_NAME = MY_SERVICE_NAME)

        )

        )

        The three variables of interest in the file are HOST, PORT, and SERVICE_NAME. Copy these variables from the TNSNames.ora file and keep them available. These variables are case sensitive.The following steps describe how to provide these variables for your connection.

        Step 1 

        From the Tableau Desktop start page, select Connect to Data.

        Step 2 

        On the Connect page, click Oracle.

        Step 3 

        Provide the following information from the TNSNames.ora file:

        • In the Server name text box, type the HOST name.
        • In the Service text box, type the SERVICE_NAME.
        • In the Port text box, type the PORT number.
        • Specify whether to use Windows Authentication or a specific user name and password, and then click Connect.

        Note: Variables are case sensitive.

        Step 4 

        Select a schema from the Schema drop-down list, drag a table to the join area, and then click Go to Worksheet.

        Step 5 

        Complete the steps in the Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora article.

        Important: 

        • Make sure that you save the TNSNames.ora file you use in ASCII encoding. Any other encoding besides ASCII, for example UTF-8 or Unicode, causes the ORA-12154 error message.
        • These steps are usually required even if the Oracle software is already installed on the machine.

        Step 6

        Download and install the appropriate Oracle drivers from the Tableau Drivers page. Even if an Oracle driver is installed on your computer, it could be incompatible with Tableau and will require the version specified on the Drivers page.

        ORA-12514: TNS listener does not currently know of service requested in connect descriptor

        Typically this error occurs when the SERVICE value is incorrect.

        To resolve this issue, find out what the correct SERVICE value is, open the TNSNames.ora file located in your %ORACLE_HOME%\network\admin\ folder. Refer to the steps under ORA_12154 if necessary.

        ORA-12541: TNS: no listener

        Typically this error occurs when the PORT value is incorrect.

        To resolve this issue, replace the PORT value with either 1521 or 1526. Try the value that is currently not in use.

        ORA-00932: inconsistent data types

        This error occurs when connecting to Oracle or when creating an extract from an Oracle data source. Typically this error is caused by the installation of incorrect Oracle drivers.

        To resolve this issue, install the correct Oracle drivers from the Drivers page for the version of Tableau you are using.

        In addition to the above common errors, if you are using Tableau Desktop/Server 2020.2 or later and experiencing performance issues e.g. extract refresh taking long time, you can try downloading and installing the Oracle OCI driver. Refer to the article in Related Links. Driver can be downloaded from here.

          Additional Information

          Suggestions

          If you do not have an Oracle Client installed on your machine, be sure to get the necessary files from your database administrator. If the Oracle data connection errors persist, do the following:

          • Check the TNSNames.ora folder path used to create the TNS_ADMIN variable.
          • Restart your machine to ensure that the TNS_ADMIN variable is recognized.
          • Check that the Oracle connection name used in Tableau exactly matches the TNSNames.ora Net Service Name entry. This name is case sensitive.
          • In some cases Windows will need to be restarted before the Oracle driver will pick up the TNS_ADMIN system variable
          • Contact local IT to verify that the TNSNames.ora file is current.
          • If the Oracle connection uses LDAP, make sure to include the SQLNet.ora file as well as the TNSNames.ora file.




          After hours of misdirection from official Oracle support, I dove into this on my own and fixed it. I am documenting it here in case someone else has this problem.

          To do any of this, you must be the oracle user:

          $ su - oracle
          

          Step 1: You need to look at the alert log. It isn’t in /var/log as expected. You have to run an Oracle log reading program:

          $ adrci
          ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013
          Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
          ADR base = "/u01/app/oracle"
          adrci>
          

          Notice the ADR base. That is not the install. You need to see the homes so you can connect to the one that you use.

          adrci> show homes
          ADR Homes:
          diag/rdbms/cci/CCI
          diag/tnslsnr/cci/listener
          diag/tnslsnr/cci/start
          diag/tnslsnr/cci/reload
          

          CCI is the home. Set that.

          adrci> set home diag/rdbms/cci/CCI
          adrci>
          

          Now, you can look at the alert logs. It would be very nice if they were in /var/log so you could easily parse the logs. Just stop wanting and deal with this interface. At least you can tail (and I hope you have a scrollback buffer):

          adrci> show alert -tail 100
          

          Scroll back until you see errors. You want the FIRST error. Any errors after the first error are likely being caused by the first error. In my case, the first error was:

          ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.
          

          This is caused by transactions. Oracle is not designed to be used. If you do push a lot of data into it, it saves transaction logs. Those go into the recovery file area. Once that is full (50GB full in this case). Then, Oracle just dies. By design, if anything is messed up, Oracle will respond by shutting down.

          There are two solutions, the proper one and the quick and dirty one. The quick and dirty one is to increase db_recovery_file_dest_size. First, exit adrci.

          adrci> exit
          

          Now, go into sqlplus without opening the database, just mounting it (you may be able to do this without mounting the database, but I mount it anyway).

          $ sqlplus /nolog
          SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013
          Copyright (c) 1982, 2009, Oracle. All rights reserved.
          SQL> connect / as sysdba
          Connected.
          SQL> startup mount
          

          Now, you can increase your current db_recovery_file_dest_size, increased to 75G in my case:

          SQL> alter system set db_recovery_file_dest_size = 75G scope=both
          

          Now, you can shutdown and startup again and that previous error should be gone.

          The proper fix is to get rid of the recovery files. You do that using RMAN, not SQLPLUS or ADRCI.

          $ rman
          Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013
          Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
          RMAN> backup archivelog all delete input;
          

          If you’ve got RMAN-06171: not connected to target database, than try to use rman target / instead of just rman

          Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your database and be back in business.

          Имеем тестовую СУБД Oracle XE (11.2.0.1.x86_64), на Oracle Linux 5.2 x86_64.
          При попытке открыть БД из sqlplus получаем ошибку:

          ORA-03113: end-of-file on communication channel
          Process ID: 4862
          Session ID: 91 Serial number: 3

          Гугл намекает, что возможны какие-либо проблемы со свободным местом. Смотрим, что с ним:
          $ df -h

          Видим, что на уровне операционной системы все в порядке.
          Смотрим, что в alert.log
          $ view /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log

          Видим такую запись после попытки открыть БД:
          ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
          ************************************************************************
          You have following choices to free up space from recovery area:
          1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
             then consider changing RMAN ARCHIVELOG DELETION POLICY.
          2. Back up files to tertiary device such as tape using RMAN
             BACKUP RECOVERY AREA command.
          3. Add disk space and increase db_recovery_file_dest_size parameter to
             reflect the new space.
          4. Delete unnecessary files using RMAN DELETE command. If an operating
             system command was used to delete files, then use RMAN CROSSCHECK and
             DELETE EXPIRED commands.
          ************************************************************************
          ARCH: Error 19809 Creating archive log file to ‘/u01/app/oracle/fast_recovery_area/XE/archivelog/2015_04_21/o1_mf_1_243_%u_.arc’
          Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_5607.trc:

          Т.е. вся область восстановления чем-то забита.
          Попутно понятно, куда пишутся archivelog:
          /u01/app/oracle/fast_recovery_area/XE/archivelog/

          Смотрим, чем забита fast recovery area:
          du -sh /u01/app/oracle/fast_recovery_area/XE/*
          10G    /u01/app/oracle/fast_recovery_area/XE/archivelog

          Видим, что 10Гб «съели» archivelog. Надо почистить.
          Запускаем Recovery manager:
          $ rman TARGET /
          RMAN> list backup;

          RMAN-03002: failure of list command at 04/21/2015 14:47:28
          ORA-01507: database not mounted

          БД должна быть примонтирована, чтобы RMAN показал бэкапы.
          Монтируем:
          $ sqlplus / as sysdba
          SQL> alter database mount;

          Database altered.

          Смотрим бэкапы:
          $ rman TARGET /
          RMAN> list backup;

          specification does not match any backup in the repository

          Нет ни одного бэкапа. Что само по себе печально, но для тестовой БД устраивает.

          Зато видим порядка 300 архивных логов:
          $ rman TARGET /
          RMAN> list archivelog all;

          Чтобы исправить ситуацию, временно увеличиваем db_recovery_file_dest_size, создаем бэкап и удаляем ненужные архивные логи.
          $ sqlplus / as sysdba
          SQL> alter system set db_recovery_file_dest_size=12G;

          System altered.

          Делаем бэкап БД, controlfile и архивных логов.
          $ rman TARGET /
          RMAN> backup as compressed backupset database plus archivelog delete input;

          Возвращаем обратно значение параметра инициализации db_recovery_file_dest_size:
          $ sqlplus / as sysdba
          SQL> alter system set db_recovery_file_dest_size=10G;

          System altered.

          It works!

        • Ошибка ora 01422
        • Ошибка ora 31011
        • Ошибка ora 01403 данные не найдены
        • Ошибка ora 28547
        • Ошибка ora 01110