//
you're reading...
Oracle Database backup and recovery

Oracle Database Recovery – in summary

Oracle Database Recovery

  • Determine whether you need to perform a recovery
  • Access different interfaces (such as Enterprise Manager and the command line)
  • Describe and use the available programs such as Recovery Manager (RMAN) and data recovery guide
  • perform recovery for the following files:
    • Control file
    • Redo log files
    • Data File

Open Database

To open an Oracle Database, you must meet the following criteria:

  • All control files must exist and synchronized
  • All online data file must exist and synchronized
  • Each redo log group must have at least one member of the presence

When the database from closing phase to a fully open stage, the database will have the following stages of the implementation of internal consistency checks:

  1. NOMOUNT: Examples To achieve NOMOUNT (also known STARTED) status, it must read the initialization parameter file. Enter NOMOUNT state instance, does not check any database file.
  2. MOUNT: MOUNT status when entering instance, will check that all control files listed in the initialization parameter file are present and synchronized. Even if there is a control file is missing or damaged, the instance will return an error to the administrator
    (Specified control file is missing) and remains NOMOUNT state.
  3. OPEN: MOUNT instance from the OPEN state to state, it performs the following actions:
    • Check all known control, redo log file group is at least one member present. Any member missing will be recorded in the alert log.
    • Verify all known control file, data file exists, but does not validate the offline files.
    • Before the administrator attempts to bring an offline file online, you do not check the files. If the data file does not belong to the SYSTEM or UNDO table space, the administrator can make the data file offline and open instances. If any files are missing Member, the administrator returns an error indicating the first missing file, then instance remains MOUNT state. When an instance in finding the missing file, an error message displays only the first file is causing the problem. To find the need to restore all the files, administrators can get a complete list of files that need attention by checking the v$recover_file dynamic performance views:

      				SQL> startup
      				ORACLE instance started.
      				Total System Global Area 271866464 bytes
      				Fixed Size 775608 bytes
      				Variable Size 145762888 bytes
      				Database Buffers 25165824 bytes
      				Redo Buffers 262144 bytes
      				Database mounted.
      				ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
      				ORA-01110: data file 4: '/data01/oradata/testdb/users01.dbf'
      				SQL> SELECT name, error FROM v$datafile JOIN v$recover_file USING (file#);
      				NAME ERROR
      				----------------------------------- ------------------
      				/data01/oradata/testdb/users01.dbf FILE NOT FOUND
      				/data01/oradata/testdb/example01.dbf FILE NOT FOUND
      				
    • Verify that all outstanding offline data files or read-only data files synchronized with the control file. When necessary, the instance will automatically perform recovery.
    • However, if a file is not synchronized, resulting in not redo using Online
      Chi group for recovery, the administrator must perform media recovery. If any document required media recovery, then the administrator returns an error message indicating a need to restore the file first, then the instance remains MOUNT status:

      			ORA-01113: file 4 needs media recovery
      			ORA-01110: data file 4: '/data01/oradata/testdb/users01.dbf'
      			

      In addition, v$recover_file will provide a complete list of note files. Which lists and the need for media recovery file exists, but does not display an error message.

Leave database in an open state

After opening the database, if there is the following entry is missing, the database will fail:

  • Any control files
  • belong to the system table space or table space restore data files
  • The entire redo log group(as long as there is at least one member of the group is available, the instance will remain open).

Leave database in an open state

After opening the database, the following media failure may cause instance failure: loss of control files, redo log is missing the whole group, or lost belonging SYSTEM or UNDO table space data file. Even the loss of an inactive redo log group, the database will eventually fail because of a log switch.

In many cases, the failed instance and it is not completely closed, but can not continue to work. Close up of the database must be in the situation to recover from these types of media failure. Therefore, the administrator must perform the SHUTDOWN ABORT command, then we can start back to work.

Lost belonging to other table space data file does not cause instance failure, and can recover the database while the database is open, then the other table space work can proceed.

Check the alert log file or by using a data recovery instructions, can detect these errors.

Data Recovery Advisor (data recovery guide)

  • Rapid detection, analysis and repair faults
  • downtime and runtime
  • Users will minimize interference
  • User Interface:
    • Enterprise ManagerGUI (multiple paths)
    • RMAN Command Line
  • Support for database configuration:
    • Single Instance
    • Non-RAC
    • Support for failover to a standby database, but it does not support the analysis and repair backup database

Data Recovery Advisor (data recovery guide)

When an error occurs, the data automatically collects data recovery guidance fault information. In addition, it can take the initiative to check the fault. In this mode, it is possible to process damage found in the database and noted that failure to detect and analyze the data before the error. (Please note that repair is always under artificially controlled).

Data failure may be severe. For example, if the current log file is missing, you can not open the database. Some data problem (such as corrupted data blocks in the file) is not catastrophic, because they do not crash the database or Oracle database could not be opened.

Data recovery instructions can handle the following two situations: one is because the necessary database files are missing, inconsistent or damaged and can not start the database, the other is found at runtime file is damaged.

The preferred method of addressing the serious data problem is as follows:

  1. If you are in the Data Guard configuration, the failover to the standby database. This allows users to back online as soon as possible.
  2. The main reason for the failure to correct data (Fortunately, this will not impact on user).

User interface

You can use data recovery instructions from Enterprise Manager (EM) Database Control and Grid Control. In case of failure to access the data using a variety of methods to guide recovery. The following examples are from the “Database Instance” Home Start Date:

  • “Availability” tab page> Perform Recover> Advise and Recover
  • Click “Active Incidents” link to access the “Support Workbench” “Problems” page: “Checker Findings” option Cards page> Launch Recovery Advisor
  • Navigate to “Database Instance Health”, then click “Incidents” section in particular linked (ORA 1578), visit the “Support Workbench” The” Problems Detail “page, then click the “Data Recovery Advisor”
  • Database Instance Health> “Related Links” section: Support Workbench> “Checker Findings” tab page: Launch Recovery Advisor
  • Related Link: Advisor Central> “Advisors” tab page: Data Recovery Advisor
  • Related Link: Advisor Central> “Checkers” tab page: Details> “Run Detail” tab page: Launch Recovery Advisor

You can also restore through RMAN command line using the data to guide:

rman target /
rman> list failure all;

Supported database configuration

In the current version, data recovery guide supports single-instance database, does not support Oracle Real Application Cluster
Database.

Data recovery guide can not be used to repair the primary database failure from the block or file transfer from the standby database. In addition,
Data recovery instructions, but not to diagnose and repair faults in the standby database. However, the data does support recovery guide
Failover to the standby database (as a rehabilitation program, as described above).

Lost control file

If the control file is lost or damaged, the instance is usually aborts.

  • If the control files are stored in ASM disk group, the recovery program are as follows:
    • Use Enterprise Manager to perform Directed recovery.
    • Put the database NOMOUNT mode, then use RMAN command to restore the control file from an existing control file.
  • If the control files as regular file system, then:
    • Close the database.
    • Copy an existing control file to replace the missing control file.

After successfully recover the control files, open the database.

RMAN> restore controlfile from '+TESTDBDATA/testdb/controlfile/current.260.895172463';

Lost control file, optionally recovery program depends on the storage configuration control files, and there is at least one control file or all files missing.

If you use ASM, and at least one control file copy, you can use Enterprise Manager to perform Directed recovery, or perform a manual recovery using RMAN as follows:

  • Place the database in NOMOUNT mode.
  • Connect to RMAN and issue restore controlfile command to restore the control file from an existing control file, for example:
  • 		restore controlfile from '+TESTDBDATA/testdb/controlfile/current.260.895172463';
    		
  • After successfully recover the control files, open the database.

If the control file is stored as a regular file system files and at least one control file copy, so that when the database is closed, only the remaining control file is copied to the location of a missing file. If the media failure is due to lack of disk drive or controller caused, then the rest of the control file a copy to a different location, and then update the instance parameter file to point to the new location. Or, for a reference to the missing control file can be deleted from the initialization parameter file. Note: Oracle recommends that you always keep at least two control files.

Lost redo log files

If you lose the redo log file group of a member, and there is at least one member of the group, pay attention to the consequences as follows:

  • will not affect the normal operation of the instance.
  • alert log will receive a message informing that a member can not be found.
  • You can delete the lost redo log members and add new members to restore the missing log files.
  • If you lose the log file group that contains archived, you can remove the log set to re-create the missing file.

Lost redo log files

Will not affect the running instance is lost if a single redo log group member, during the recovery.

To perform this restoration, follow these steps:

  1. Check the alert log to determine if there are missing log files.
  2. recover lost files, first remove the lost redo log members:
  3. 		SQL> ALTER DATABASE DROP LOGFILE MEMBER '+TESTDBDATA/testdb/onlinelog/group_1.261.256672387';
    		

    Then add a new member to replace lost log member:

    		SQL> ALTER DATABASE ADD LOGFILE MEMBER '+TESTDBDATA'TO GROUP 2;
    		

    To delete and re-create the log file members can use Enterprise Manager.

    NOTE: If redo log files using OMF, and using the above syntax to add a new redo log member to an existing group, then the new redo log file members will not be OMF files. If you want to ensure that the new redo log member file is OMF file, the easiest solution is to create a new recovery redo log group, then delete the group that contains the missing redo log redo log members.

  4. If the media failure is due to lack of disk drive or controller caused, rename the missing files.
  5. If the archived redo log group, or is in NOARCHIVELOG mode, you can choose to re-create the missing file after clearing the log group to resolve the issue. Select the appropriate group, and then select “Clear Logfile” Operation You can also use the following command to manually remove the affected groups:
  6. 		SQL> ALTER DATABASE CLEAR LOGFILE GROUP#;
    		

    Note: Database Control does not allow clear yet archived log group. Doing so destroys redo information chain. If you must remove undocumented log group, it should immediately perform a full backup of the entire database. Otherwise, in the event of another failure will result in data loss. To clear unarchived log group, use the following command:

    		SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP #;
    		

In NOARCHIVELOG mode data file is missing

When the database is in NOARCHIVELOG mode, if you lose any data files, perform the following tasks:

  1. If the instances are not closed, shut down the instance.
  2. From a backup to restore the entire database, including all data files and control files.
  3. Open the database.
  4. Allow the user to re-enter all changes made since since the last backup.

If you lose any data in the database file in NOARCHIVELOG mode, you need to completely restore the database, including control files, and all data files.

When the database is in NOARCHIVELOG mode, it can only be restored to the state when the last backup. Therefore, the user must re-enter all changes made since the last backup.

To perform this type of recovery, do the following:

  1. If the instances are not closed, shut down the instance.
  2. On the “Maintenance (Maintenance)” properties page, click “Perform Recovery (implementation of recovery).”
  3. Select “Whole Database (entire database)” as the restoration type.
  • If the database is in NOARCHIVELOG mode with incremental backup strategy, then RMAN will first restore the most recent level 0 backup, then RMAN recovery process before applying incremental backups.
  • In ARCHIVELOG mode non-critical data file is missing

    If a data file is lost or corrupted, and the file does not belong to the SYSTEM or UNDO table space, only to restore and recover the missing data file.

    When the database is in ARCHIVELOG mode, if you lose any data file does not belong to the SYSTEM or UNDO tablespace affects only objects in the missing files. Users can still use the rest of the database continues to work. To restore and recover the missing data file, follow these steps:

    1. 1. On the “Maintenance (Maintenance)” properties page, click “Perform Recovery (Executive recovery).”
    2. 2. Select “Datafiles (data files)” as the restoration type, then select “Restore to current time (reduction to the current time)
    3. 3. Add all need to restore data files.
    4. 4. Determine is to restore files to a default location or a new location (if a disk or controller deletions).
    5. 5. Submit RMAN job, restore and recover the missing files.

    Using following script:

    # Restore and recover datafile
    sql 'alter database datafile 10 offline';
    restore datafile 10;
    recover datafile 10;
    

    In ARCHIVELOG mode system-critical data file is missing

    If a data file is missing or damaged, and that the document belongs to the SYSTEM or UNDO table space, perform the following tasks:

    1. The examples may or may not turn off automatically. If it does not shut down, use the SHUTDOWN ABORT shutdown instance.
    2. Mount the database.
    3. Restore and recover the missing data file.
    4. Open the database.

    Belongs SYSTEM table space or data files contain UNDO data systems are considered critical data files. If you lose one of these files, we need to restore the database from MOUNT state (unlike other data files to restore when the database is open).

    To perform this restoration, follow these steps:

    1. If the instances are not closed, shut down the instance.
    2. Mount the database.
    3. On the “Maintenance” properties page, click “Perform Recovery (implementation of recovery).”
    4. Select “Datafiles” as the restoration type, then select “Restore to current time (reduction to the current time).”
    5. Add all need to restore data files.
    6. Determine is to restore files to a default location or a new location (if a disk or controller deletions).
    7. Submit RMAN job, restore and recover the missing files.
    8. Open the database. Time because it will be restored to the last commit, so users do not have to re-enter data.
    9. 		sql 'alter database datafile 10 online';
      		

    Because the database is in ARCHIVELOG mode, so the time to resume recently submitted, and users do not need to re-enter any data.

    Advertisements

    About daviewning

    I am an Oracle DBA

    Discussion

    No comments yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: