ORACLE

PRESENTATION OF DATA PROTECTION A key responsibility of the database administrator (DBA) is to ensure the availability of the database. For this, he may take the following measures in order to reduce downtime of the database: • Multiplex control files; • Create Redo log files groups with several members; • Distribution of control files and files members Redo logfiles on several different disks. Despite these precautions, an incident may still occur interrupting production. The DBA must then restore the continuity of the service as soon as possible with minimal loss of data. Types of incidents The incidents that may occur have various causes:  User error;  Instance failure;  block corruption;  Physical failure  User error A user may intentionally (or not): - delete a table or a table space; - truncate a table; - Validate errors in a table. To limit this type of error, the DBA must train users to manipulate the database and grant them only the required privileges for their work.  Block Corruption There are 2 types of corruptions: - physical corruption - logical corruption A logical corruption corresponds to an oracle internal error. Once an inconsistency in the structure of a block is detected, it is marked as damaged. This block can in some cases be read by low-level tools. On the other side in case of a physical corruption, the block has an incorrect format. Even with a low level tool, this block can’t be read. The corruptions are generally due to faulty hardware (disks, disk controllers) or problems of operating systems. To limit this type of errors, the DBA to its level ,- in addition to the choice of hard disks, disk controllers, power quality and redundancy implementation thereof - made by the system administrator can prevent corruption by assigning appropriate values to certain initialization parameters. In addition, it is not recommended to run check disk utility on Oracle drives.  Instance failures An instance failure can occur for one of the following reasons: - The server becomes unavailable due to a power failure or a failure of one of its hardware components other than the hard disk (CPU, RAM ...) - Failure of the operating system - Failure in a background process of the Oracle server. This type of incident does not require any action of recovery (from the backups from the DBA). He should only after resolution of the incident restart the instance and open the database. The recovery process is made by the SMON process.  Physical failures The physical failure, defined here as the inability to access a file needed to run the database, is the most severe type of incident, since it usually requires the intervention of the DBA that depending on the nature of the failure, may go to the full restoration of the database backup. BACKUP/RESTORE STRATEGIES The backup strategies vary according to operational requirements of each organism. An organization chart operating 24 hours out of 24, 7 days on 7, 365 days per year and an outage of a few minutes would result in a considerable financial loss, will not apply the same strategy that an organization chart operating five days on 7, 8 hours per day and capable of supporting shortages of more than one hour. In the first case, one has to resort to an ONLINE backup, database open , whereas in the latter case, one might settle for a closed base backup (the data created between backups being lost and to be re-entered manually) . In all cases, the strategies put in place depend on the QoS (quality of service), whose main parameters are: - MTTR (Mean Time To Repair), expresses the time of restart of the database after incident - MTBF (Mean time Between Failure) In the first case of our example of continuous operation (MTTR close to 0 and MTBF near infinity), the DBA should: • Perform ONLINE backups; • Distribute the files in the database on mirror disk or RAID systems if possible; • Enable the prevention of physical and logical block corruption (see below); • Minimize instance time recovery ; • Have a database backup on a remote site (see the 2nd part of this book). While in the second case, the DBA could settle for a backup ,database closed at the end of each operation knowing that he runs the risk of losing data from a day of use. BACKUPS CLASSIFICATION Backups can be classified into two types: - Logical backups - Physical backups Logical backup Logical backup is performed by a utility (OS level) EXPORT (or Data Pump since version 10g). This utility extracts the contents of the specified tables and dependent objects and stores them in a dump file whose format is independent of the operating system. There are 5 modes of EXPORT: • Table; • User; • Tablespace; • Transport tablespace; • Full; These modes will be explained in detail further in this book. ► Caution: The objects belonging to SYS schema are never copied no matter the EXPORT mode. SYS being the owner of the database dictionary and containing essential tables necessary for the functioning of the database, a copy of these objects, then its IMPORT into another database could compromise its integrity. It is not recommended to use EXPORT as the only type of backup for the following reasons: • EXPORT can ‘t copy contents of offline tablespaces even in full mode; • The image of the database may no longer be the same after IMPORT, if there has been an incident requiring the recreation of the database after applying patches on the Oracle server, the objects owned by SYS are not exported • EXPORT and IMPORT can only be used as database open. In case of loss or damage of a vital file for the database (control, system, current log or undo) the database can’t be opened, our EXPORT dump file can only be used after recreating the database. In general, we use EXPORT in addition to the backup in the case of specific operations such as: - Moving objects from one database to another for testing purposes - Transport of tablespace - Reorganization of the space in the tables (method commonly used in versions prior to 10g) Physical backup The physical backup consists in copying the files from the database. There are 2 types of physical backups: - backups managed by the user - backups managed by RMAN We use a command of the operating system (cp, Copy ...) to copy the files from the database within the backup managed by the user. RMAN (Recovery Manager) is an Oracle utility to save and recover files from the database. TERMINONOLOGIES USED IN BACKUPS Full backup / Incremental backup The full backup consists of backing up all blocks of all specified source files of the database (modified or not). The incremental backup copies only the modified blocks since the previous backup. Two levels (0 and 1) of incremental backups are supported since Oracle Database version 10g; in 9i versions, we can have more than 2. A level