ORACLE: Backup and Recovery

Mohanraj No comments


ORACLE: Backup and Recovery

To ensure safe operation of the database and prompt failover is needed to plan a backup strategy, backup and recovery, recovery, and it's useless to think that we are safe from such circumstances, and that's not I can happen to me. And the first step to take is to define the fundamental characteristics of the implementation, because we're going to get bad goals if they know or are undefined. The second step is to establish plans for backup and recovery that will enable us to secure the objectives.
 

1 Introduction to Backup and Recovery
Planning and testing backup procedures of the system is the only guarantee that exists against system crashes, the OS, software or any other circumstances.
The causes of error in a DB system can be grouped into the following categories:
  • Physical
      • are caused by hardware failures such as disk or CPU.
  • Design
      • are holes in the software, either OS or DBMS.
  • Operating
      • are caused by human intervention, due to failure of the DBA, inappropriate or wrong approach configurations of backup procedures.
  • Environment
      • such as natural disasters, power failures, overheating.
Of all these possibilities, the DBA can only influence and predict malfunctions, since the rest is generally not within their responsibilities and capabilities.
Given the complexity of current systems and the increasingly critical needs in the availability of systems, where a drop BD can cause millions in losses, may be worth considering the hardware security mechanisms and redundancy that technology gives us:
  • UPS or uninterrupted power supplies,
  • Disk mirroring or RAID technology,
  • Duplicate components,
  • Redundant systems.
One of the most important decisions that a DBA must make is whether to start the database in ARCHIVELOG mode or not. This decision has its advantages and disadvantages:
  • Advantages:
    • Although lost data files, you can always recover the database with an old copy of data files and archived redo log files.
    • You can make hot backups.
  • Disadvantages:
    • It will take more disk space.
    • The work of the DBA is increased by having to determine the fate of the redo log archiving.

Backup 1.1 Introduction
The backups can be classified into physical and logical. Physicists are performed when copying the files that support BD. These include OS backups, cold backups and hot backups.
The only logical backups extract data from tables using SQL commands and performed with the use export / import.
OS Backups
    This type of backup is the easiest to implement, although time consuming and makes it inaccessible to the system as it performs. Seize the OS to store backup files also all of the database. The steps in this type of backup are:
    1. Stop the BD and SO
    2. Starting in superuser mode.
    3. Back up all files in the filesystem
    4. Start the system in normal mode and then BD.
Backups of the Cold BD
    The mean cold backups the database to stop in normal mode and copy all the files on which it sits. Before stopping the database must also stop all applications that are working with the BD. Once the copy of the files, the database can be restarted.
DB Backups Hot
    The hot backup is performed while the database is open and running in ARCHIVELOG mode. Care should be taken to do when the load of the database is small. This type of backup is to copy all files relating to a particular tablespace, archived redo log files and control files. This for each tablespace of the database.
Logical Backups with Export / Import
    These utilities allow the DBA to back certain objects from the DB, and restore or move a DB to another. These tools use SQL commands to get the contents of the objects and write / read files
Once you have planned backup strategy and has been tested, it is automated to facilitate compliance.

1.2 Presentation of Recovery
Oracle provides several ways to recover a failure in the database, and it is important that the DBA know how each one of them to determine when to be used.
One of the major responsibilities of the DBA is to have the database ready, and prepare for the possibility that a failure occurs. So, after a failure, the DBA can recover the database in the shortest time possible. Recovery processes depend on the type of error and structures affected.
Thus, the types of errors that can occur are:
User Errors
    Such as a user deletes a row or deleting a table. These errors can be solved by importing a table from a previous logical copy. If there is no logical copy, you can recover the database in an auxiliary instance, export the table in question from the auxiliary instance in the instance and import operations.
Sentencing judgments
    Defined as the inability of the Oracle DBMS running any SQL statement. An example of this occurs when you try a selection from a table that does not exist. These faults are automatically retrieved by a rollback of the transaction containing the statement failed. The user will need to re-run the transaction again when you've fixed the cause of the problem.
Process failures
    It is an abnormal termination of a process. If the process was a process of user, an application server or the PMON fee recovery process. If the process was one of those background, the agency should be stopped and restarted, a process during which the fall is recovered by performing a forward roll and a rollback of uncommitted transactions.
Network Failures
    Sometimes network failures of process failures occur, they are treated by the PMON. If the network error is involved a distributed transaction, once the connection is reestablished, the RECO process automatically resolves conflicts.
Instance failures
    Failures may be due to physical or software design that make a background process and the body falls with it. Recovery is automatic when you get up the database, taking more or less time in recovery.
System Failures
    They are the most dangerous faults, not only because you can lose data, but because it takes longer to recover than the other faults. In addition, much depends on the experience of the DBA to lift the BD quickly and ordered (or most) of data.
There are three types of Oracle recovery: a block level of thread and physics.
Recovery blocks
    Recovery mechanism is simple and is done automatically. Occurs when a process dies just as he is changing a block, and uses the online redo log records to reconstruct the block and write to disk.
Recovery threads
    Oracle is done automatically when an instance dies he discovers that keeping open a thread, then restores the changed data blocks that were in the cache of the dead body, and closing the thread that was open. The recovery is performed automatically either when the BD is closed.
Physical recovery
    Is in response to a RECOVER command. It is used to convert the current backup files, or to restore the changes that were lost when a data file was put offline without a checkpoint, applying the archived redo log file and online.
2 Backup Principles
A valid backup is a copy of the database information necessary to rebuild the database from an unusable state thereof. Normally, if the backup strategy is based on the copy of data files and archived redo log files, you should have copies of data files, the control files, redo log files of assets and also the archived. If you lose one of the archived redo log files says it has a hole in the sequence of files. This invalidates the backup, but enables the BD to be carried to the top of the hole by an incomplete recovery.

2.1 Design of the BD and Backup Basic Rules
First of all, it is very important to understand certain rules that determine the location of files and other considerations that affect the backup scheme:
  • It is advisable to archive the redo log files on disk and then copy to tape, but always in a different drive that supports data files and redo log assets.
  • Copies files should not be in the same device as the original. Not always have to pass the copies to tape, because if left in accelerating recovery disk. Also, if copies are copied to tape and kept in the disk, it can survive several device failures.
  • It should keep multiple copies of control files, placed on different disks with different drivers.
  • The online redo log files should be multiplexed with a minimum of 2 members per group, each member residing on a different disk.
  • Whenever the structure of the BD changes due to the inclusion, deletion or renaming a data file or redo log, copy the control file by storing the structure of the database. In addition, every file added must also be copied. The control file can be copied while the database is open with the following command: 
     
  SVRMGR> alter database backup controlfile to 'target';
Given the above rules, the following can be considered an example of a backup strategy:
  1. Enable ARCHIVELOG mode.
  2. Make a backup at least once a week if the database can be stopped. Otherwise, hot backups every day.
  3. Copy all archived redo log files every four hours. The size and number of them depend on the rate of transactions.
  4. Make a weekly export of the database in restricted mode.
2.2 Physical Backups
Physical backups are physically copied those files from the database. There are two options: cold and hot. It is said that the cold backup when the files are copied to the database is stopped. Hot is when copying files with the database open and running.
Cold Backup
The first step is to stop the BD with normal shutdown. If the database must be stopped with immediate or abort is rebooted with the RESTRICT mode and stop mode back to normal. After copying the datafiles, the redo log and control, plus the archived redo log and not yet copied.
A good idea is to automate this process with appropriate scripts, so do not forget to copy any files.
Since this type of backup is a copy of the database files if they contain some kind of corruption, will push the backup without detecting it. Therefore it is important to check the backups.
Hot Backup
If the implementation of BD requires the same 24-hour availability. a day, 7 days a week can not be cold backups. To perform a hot backup should work with the database in ARCHIVELOG mode. The hot backup procedure is quite similar to the cold. There are two additional commands: begin backup and end before the end of the backup backup. For example, before and after making a backup of tablespace users should execute the statements: 
 
  SVRMGR> alter tablespace users begin backup; 
  SVRMGR> alter tablespace users end backup;
And allowed the cold backup to back all the time BD, hot backups on the treatment unit is the tablespace. The hot backup involves copying the data files (and tablespaces), the current control file and all archived redo log files created during the backup. It will also require all archived redo log files after the hot backup to make a full recovery.

2.3 Logical Backups
This type of backup copy the contents of the database without storing the physical location of data. Performed with the export tool to copy the data and the definition of BD in a file in Oracle internal format.
To make a export the database must be open. Export ensures consistency in the table, but not between tables. If consistency is required between all tables in the database then you should not make any transactions during the export process. This can be achieved if you open the database in restricted mode.
Among the advantages of carrying out an export are the following:
  • It can detect corruption in data blocks, as the export process will fail.
  • Protects user faults, for example if you delete a row or an entire table by mistake is easily retrieved by an import.
  • You can export the data to determine with great flexibility.
  • Exports can be made ​​full, incremental and cumulative.
  • Arisen I deem to export backups are portable and serve as an interchange format between BDs and data between machines.
One of the disadvantages of export Logical backups is that they are much slower than physical backups.
 
Export parameters
 

Parameter Default Description
USERID indefinite username / password of the user performing the export.
BUFFER OS dependent The size in bytes of the buffer used.
FILE expdat.dmp destination file name.
GRANTS Yes indicates whether the rights are also exported.
INDEXES Yes indicates whether the indices are also exported.
ROWS Yes indicating whether they are also exported rows of tables, or just the table definitions.
CONSTRAINTS Yes indicates whether the restrictions are also exported.
COMPRESS Yes indicates whether compressed mode is exported.
FULL No indicates whether to export the whole DB.
OWNER current user a list of users whose objects are to be exported.
TABLES indefinite the list of tables to export.
RecordLength OS dependent the length in bytes of the log file.
INCTYPE indefinite the type of incremental export.
RECORD Yes indicates whether to record the incremental export tables and SYS.INCEXP SYS.INCVID.
PARFILE indefinite parameter file.

 
Export Modes
 
There are three ways to make a data export:
Table Mode
Exporting table definitions, data, rights of the owner, owner's rates, restrictions on the table and triggers associated with the table.
User Mode
Exported all over Table mode clusters, BD links, views, private synonyms, sequences, procedures, etc. user.
Whole BD mode
Besides all the User, export roles, all synonyms, system privileges, the definitions of the tablespaces, tablespace quotas in the definitions of rollback segments, the system auditing options, all triggers and profiles.
The whole way BD can be divided into three cases: Full, cumulative and incremental. The latter two take less time than the complete, and can export only the changes in the data and definitions.
Full
Export all tables in the database and initializes the incremental export information from each table. After a full export are not needed files cumulative and incremental exports of the previous BD. 
 
  $ Exp userid = system / manager full = y constraints inctype = complete = Y 
  file = full_export_filename
Cumulative
Exports only tables that have been modified or created since the last cumulative or complete export and export records the details for each exported table. After a cumulative export are not needed incremental export files of previous BD. 
 
  $ Exp userid = system / manager full = y inctype = cumulative constraints = Y 
  file = cumulative_export_filename
Incremental
Exports all tables modified or created since the last export Incremental, Cumulative or complete, and records the details of export for each exported table. Are interesting in environments where many tables remain static for long periods of time, while others vary and need to be copied. This type of operation is useful when you need to quickly recover accidentally deleted a table. 
 
  $ Exp userid = system / manager full = y inctype = incremental constraints = Y 
  file = incremental_export_filename
Export policy can be the following: perform a full export on day 1 (ie Sunday), then do incremental exports the rest of the week. This Monday to Saturday only export those tables will be exported to save time in the process.
3 Principles of Recovery
To understand the principles of recovery, you need to understand the underlying data structures used in recovery.

3.1 Definitions and Concepts
Redo log files contain the changes made ​​to the database. Should present some concepts related to them.
Change Vector
describes a simple change in a data block DB. Among other data, contains the version number, transaction code, and address of the block in question.
Redo log record
is a set of change vectors describing an atomic change on the database. The transaction is also the unit of recovery.
Evolution of Redo log per day
can be calculated by running the command archive log list on two consecutive days and calculating the difference of the sequence number of redo log files, multiplied by the size of a redo log file: 
 
  SVRMGR> archive log list; 
  Database log mode No Archive Mode 
  Automatic archival Disabled 
  Archive destination / opt / app / oracle / admin / demo / arch / arch.log 
  Oldest online log sequence 3 
  Current log sequence 5
System Change Number, SCN
is data that defines the committed version of the database at this point in time. When a transaction is confirmed, is assigned a SCN that identifies it uniquely. Redo log files are marked with two SCN. When you open a new redo log file is marked with an SCN, SCN low, which is one more than the greater of the previous SNA redo log file, and its high SCN is set to infinity. The SCN also associated with control file, because when for a BD, a tablespace or data file is stored for each data file the stop SCN in control file.
Redo change log
is the process by which it ceases to use a redo log file and LGWR van to the next available redo log file. It can be done with the command alter system switch logfile;.
Checkpoints
are automatically activated during normal operation of the instance, but can be activated manually using the alter system command alter system checkpoint local or global checkpoint depending on whether we refer to the instance in which we, or you want to affect all active instances , respectively. Each implies a checkpoint SCN, and Oracle ensures that all changes with an SCN lower than the given checkpoint are written to disk.

3.2 Methods of Recovery
There are several methods of recovery, but all are based on the application of redo log records.
Application of Redo Log
When a BD is started with the startup command, the database passes nomount states, mount and open. In this third state, it verifies that you can open all log files and data. If the database is started for the first time after a fall, will necessitate a recovery that consists of two steps: moving forward using the database redo log records, to undo the uncommitted transactions.
Each data file has in his head made ​​the last checkpoint and the control file also holds the account. The checkpoint has included the SCN. The SCN is known as boot file. Associated with each data file control file has the final SNA, as initially infinite. The start SCN is incremented with each checkpoint.
When the database is in normal mode or to immediately stop equals the SCN for each data file to the SCN stored in each data file. When you open the database again there are two checks. The first is to see if the counter of checkpoints in the header of data files match the corresponding control file. If so, it compares the start SCN of each data file with the final SCN stored in control file. If they are equal no recovery is needed in this data file. As part of the opening is set to infinity, the final SNA data to that file.
If the database is stopped with abort mode was not implemented and the checkpoint SCN of the end for the data file is to infinity. Thus, during the database opens, and assuming that the counter matches checkpoints, comparing SCN start and end, as the latter is infinite recovery Shall applying the changes stored in the online redo log files to move the BD, and roll back logs of rollback segments to undo uncommitted transactions.
If after stopping the database to replace a data file for your backup, to start the Oracle BD meter detects that the checkpoints of the data file does not match the one stored in the control file. Thus, we have to dip into archived redo log files, starting with one whose sequence number appears in the data file header.

Physical Recovery 3.3
Using a backup copy of data files always need a physical recovery. It is also the case when a data file gets offline without a checkpoint.
Oracle detects that physical recovery is needed when the timer checkpoints of the data file header does not match the corresponding counter control file checkpoint. Then it becomes necessary to recover command. Recovery begins in the SCN under data files recovery, applying redo log records from him, and stopping on the final SNA greatest of all data files.
There are three options to make a physical recovery. The first is a recovery of BD where BD is restored whole. The second is a tablespace recovery where, as part of the database is open, you can retrieve a specific tablespace. This means that all files will be recovered associated with the tablespace data. The third type is the recovery of a specific data file while the rest of the database is open.
Use Requirements for Physical Recovery
The first condition to be able to recover physically put a DB is that it is being used in ARCHIVELOG mode. Otherwise, a full recovery may not be possible. By working with the BD NOARCHIVELOG mode, and makes a weekly copy of the database files, you should be prepared to lose, at worst, the work of the last week if a failure occurs. Since the redo log files contain a hole and could not move the database to pre intant failure. In this case the only way to rebuild the database is done from a full export, re-creating the schema of the database and importing all the data.
DB recovery
The BD must be mounted but not open. The recovery command is: 
 
  RECOVER [AUTOMATIC] [FROM 'location'] [BD] 
     [UNTIL CANCEL] 
     [UNTIL TIME date] 
     [UNTIL CHANGE integer] 
  [USING BACKUP CONTROLFILE]
The options in brackets are optional:
  • AUTOMATIC makes recovery is done automatically without prompting the DBA for the name of the redo log files. Can also be used for this purpose the set command Autorecovery on / off. Redo log files should be at the location specified in LOG_ARCHIVE_DEST and format of the filenames should be set at LOG_ARCHIVE_FORMAT.
  • FROM is used to determine the location where the redo log files, if different from fixed LOG_ARCHIVE_DEST.
  • UNTIL serves to indicate that you want to perform incomplete recovery, which means losing data. Only give when they are lost or archived redo log file control. When you have made ​​an incomplete recovery of BD should be opened with the command alter database open RESETLOGS, resulting in the redo log applied not ever to be applied and initialized the sequence of redo log file control. There are three options to stop the recovery:
    • UNTIL CANCEL to retrieve a redo log at a time, stopping when you type CANCEL.
    • TIME UNTIL recovery up to a given instant in a redo log file
    • CHANGE UNTIL recovery up to a given SCN.
    • USING BACKUP CONTROLFILE using a backup control file to govern the recovery.
Recover a tablespace
The BD should be open, but to recover the tablespace offline. The recovery command is: 
 
  RECOVER [AUTOMATIC] [FROM 'location']  
     Nombre_tablespace TABLESPACE [, nombre_tablespace]
Recovering a Data File
The BD should be open or closed, depending on the file to retrieve. If the file to retrieve is a user tablespace the database can be open, but with the file to recover offline. If the file is the SYSTEM tablespace the database must be closed, since there can be open with SYSTEM files offline. The recovery command is: 
 
  RECOVER [AUTOMATIC] [FROM 'location']  
     DATAFILE filename [, filename]
Creating a Control File
If the control file is damaged and lost can use a backup of the same or create a new one. The command to create a new control file CREATE CONTROLFILE is. This command can be executed only with the BD nomount state. The command execution produces a new control file and the automatic assembly of the database.
An interesting command which helps to maintain safe control files is as follows: 
 
  SVRMGR> alter database backup controlfile to trace;
it produces a script that can be used to generate a new control file and recover the database, if necessary. The trace file generated is as follows: 
 
  Dump file / opt/app/oracle/admin/demo/udump/demo_ora_515.trc 
  Oracle7 Server Release 7.3.2.3.0 - Production Release 
  With the Distributed, replication and Spatial Data options 
  PL / SQL Release 2.3.2.3.0 - Production 
  ORACLE_HOME = / opt/app/oracle/product/7.3.2 
  System name: SunOS 
  Node name: cartan 
  Release: 5.5 
  Version: Generic 
  Machine: sun4m 
  Instance name: demo 
  Redo thread mounted by this instance: 1 
  Oracle process number: 7 
  Unix process pid: 515, image: oracledemo 
 
  Fri May 15 11:41:19 1998 
  Fri May 15 11:41:19 1998 
  *** SESSION ID: (6.2035) 1998.05.15.11.41.19.000 
  # The Following commands will create a new control file and use it 
  # To open the database. 
  # No data log history Other Than Will Be Lost.  Additional logs May 
  # Be required for media recovery of offline data files.  Use this 
  # Only if the current version of all online logs Are available. 
  STARTUP NOMOUNT 
  CREATE CONTROLFILE REUSE DATABASE "DEMO" NORESETLOGS NOARCHIVELOG 
      MAXLOGFILES 16 
      MAXLOGMEMBERS 2 
      MAXDATAFILES 30 
      MaxInstance 1 
      MAXLOGHISTORY 100 
  LOGFILE 
    GROUP 1 '/ export/home/oradata/demo/redodemo01.log' SIZE 2M 
    GROUP 2 '/ export/home/oradata/demo/redodemo02.log' SIZE 2M 
    GROUP 3 '/ export/home/oradata/demo/redodemo03.log' SIZE 2M 
  DATAFILE 
    '/ Export/home/oradata/demo/system01.dbf' 
    '/ Export/home/oradata/demo/rbs01.dbf' 
    '/ Export/home/oradata/demo/rbs02.dbf' 
    '/ Export/home/oradata/demo/rbs03.dbf' 
    '/ Export/home/oradata/demo/temp01.dbf' 
    '/ Export/home/oradata/demo/tools01.dbf' 
    '/ Export/home/oradata/demo/users01.dbf' 
  ; 
  # Recovery is required if Are Any of the datafiles restored backups, 
  # Or if the last shutdown Immediate or not was normal. 
  RECOVER DATABASE 
  # Database Be Opened Normally dog now. 
  ALTER DATABASE OPEN;

3.4 Recovery Logic
Oracle import tool available to restore data from a BD from the results of an export file. Import reads data from export files and runs the statements that store creating tables and filling them with data.
Import Parameters

Parameter Default Description
USERID indefinite username / password of the user performing the import.
BUFFER OS dependent The size in bytes of the buffer used.
FILE expdat.dmp the name of the export file to import.
SHOW No Whether to show the contents of the export file, regardless of any data.
IGNORE Yes indicates whether to ignore errors that occur when you import an object that already exists in the database.
GRANTS Yes indicates whether the rights are also imported.
INDEXES Yes indicates whether the indices are also imported.
ROWS Yes matter also indicates whether the rows of tables.
FULL No indicates whether to import the entire file.
Fromuser Indefinite a list of users whose objects are exported.
TOUS Indefinite a list of users in whose name the objects are imported.
TABLES indefinite the list of tables to import.
RecordLength OS dependent the length in bytes of the log file.
INCTYPE indefinite incremental import type (SYSTEM or RESTORE).
COMMIT No indicates whether a commit is performed after importing each row. By default, importer makes a commit after loading each table.
PARFILE indefinite parameter file.

To import an incremental export can be performed the following sequence of steps:
  1. Using the latest copy of the import to restore the system definitions: 
     
$ Imp userid = sys / passwd inctype = system file = full = Y export_filename
  2. Put the rollback segments online.
  3. Import entire export file newest 
    $ Imp userid = sys / passwd inctype = restore full = Y file = filename
  4. Import export files in accumulation mode from the most recent full export, in chronological order: 
     
  $ Imp userid = sys / passwd inctype = restore full = Y file = filename
  5. Importar los ficheros de exportación en modo incremental desde la exportación completa o acumulativa más reciente, en orden cronológico: 
     
$ imp userid=sys/passwd inctype=restore full=Y file=filename

Posted in: ,,,,,

0 comments:

Post a Comment