Sunday, October 24, 2021
    HomeOracle TroubleshootingTroubleshooting of ORA-01116 ORA-27041 and ORA-01110

    Troubleshooting of ORA-01116 ORA-27041 and ORA-01110

    The article explains Troubleshooting of ORA-01116 ORA-27041 and ORA-01110 with the proper solution using with RMAN

    Troubleshooting of ORA-01116 ORA-27041 and ORA-01110 with solution

    RMAN is basically a system by which we can recover our database up to point in time. Till now we have IMP, EXP backup utility by which we were taking our backups. Which
    Have some disadvantages. Let us have taken our data backup at 10 a.m. by using IMP utility and my database gets corrupt at 5 p.m. we can restore is from the 10 a.m. backup but after restoring the data available up to 10 a.m. only i.e. the transaction between 10 a.m. to 5 p.m. are lost. This is not affordable in case of financial transactions.

    To overcome this problem we use RMAN. By using RMAN we can recover our database up to the time.

    - Advertisement -

    Some technical requirements for RMAN:

    •  To configure RMAN database must be in archive log mode. By default which is in non archive mode.
    • Flash_recover_area must have enough space to store archive log files.
      Steps to recover database using RMAN

    1. Whenever any datafile gets corrupt you will get a message like.

    SQL> select count(*) from emp;
    select count(*) from emp
    ERROR at line 1:
    ORA-01116: error in opening database file 6
    ORA-01110: data file 6: ‘/sa5/bkp/apst/APST/datafile/users1.dbf’
    ORA-27041: unable to open file
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3

    This shows our users1.dbf data file is corrupt. Now we need to recover this data file.

    - Advertisement -

    2. Use commands like.

    C:\Documents and Settings\dbametrix>sqlplus sys as sysdba

    SQL*Plus: Release – Production on Wed Sep 11 06:05:26 2019
    Copyright (c) 1982, 2009, Oracle. All rights reserved.
    Enter password:
    Connected to:
    Oracle Database 11g Enterprise Edition Release – Production
    With the Partitioning, OLAP and Data Mining options

    SQL> shutdown immediate;
    SQL> startup mount;

    3. Now, this step is to restore and recover a database. We have two ways to do it.

    • 1) Recovery using command prompt.
    • 2) Recovery using Oracle Enterprise manager database control.
      We will discuss each one by one.

    4. Recovery using command prompt.

    Use commands like.

    C:\Documents and Settings\dbametrix>rman target sys
    Recovery Manager: Release – Production on Wed Sep 11 07:10:32 2019
    Copyright (c) 1982, 2009, Oracle. All rights reserved.
    target database Password:
    connected to target database: TEST (DBID=1962560248, not open)
    RMAN> restore database;
    (This command will restore data files from a backup).
    RMAN> recover database;
    (This command will recover your database up to the time.)

    And your errors resolved with database recovery.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    Consider Reading to these articles:

    - Advertisement -
    - Advertisment -

    Most Popular

    Recent Comments