The process of solve any type of database failures, quickly and without data loss and keep database high available is called database recovery.The main elements of database recovery is the most recent database backup.If you maintains database backup efficiently, then database recovery is very straight forward process.
The components and technologies of database is to configure to minimize or no data loss and keep database for open, includes
* Checkpoints
* Control file
* Online redolog files
* Archived redo log file
* Flash Recovery Area
Recovery Technologies
* Database Flash back technology
* Open database in Archive log mode
* Implement database high availability features like RAC, DATAGUARD,steams etc.
Components of database recovery
Checkpoints:- Checkpoint (CKPT) in oracle database is a mandatory background process. This process works in concert with the database writer process (DBWn) to manage the amount of time required for instance recovery. Checkpoint in oracle occurs when manual or automatic log switch occurs. FIRST_START_MTTR_TARGET parameter can be adjusted to control checkpoints as a result to improve instance recovery.
Control file – Control file is a metadata repository for database. It keeps all information about structure of the database. Control file is a very critical for database operation, so as being a DBA,you should maintain at minimum two copies of control file ( Oracle recommend three ) and save these in a different disk to same the control file disk failures.
Multiplexing control file
* Through Init.ora
* Through SPFILE
Multiplexing control file on init.ora file though CONTROL_FILE initialization parameter
CONTROL_FILES =(‘/u01/app/oracle/oradata/prod/ctlorcl01.ctl’,’/u02/app/oracle/oradata/prod/ctlorcl02.ctl’,’/u03/app/oracle/oradata/prod/ctlorcl03.ctl’)
Storing control file into multiple location,it protect control files from single point of failure. In multiplexing control file configuration, Oracle updates all the control files at a time, but uses only one,first one listed in the CONTROL_FILES parameter.When a single controlfile is corrupt, then DBA can simply copy a good one to that location or other location and make changing in the CONTROl_FILE parameter in init.ora file, resolve the issues.
Through SPFILE
We can dynamically set or change the location of the control file using SPFILE concept using the following command.
SQL> ALTER SYSTEM SET CONTROL_FILES =
‘/u01/app/oracle/oradata/prod/ctlorcl01.ctl’,
‘/u02/app/oracle/oradata/prod/ctlorcl02.ctl’,
‘/u03/app/oracle/oradata/prod/ctlorcl03.ctl’) SCOPE=SPFILE;
The parameter will get effects when restart the oracle instance.
Multiplexing redo log files
The information in the online redolog files are very important for database recovery. For safe guard these files against any kind of damage you can configure multiple copies of online redolog files in different disk location. All multiplex copies of redolog files are same in size and keep in a group. Each redolog file in a group is called members. LGWR background process, writes redo information to all identical members of a group,It is a best practice to protect redolog file from single point of failure. When multiplexing redolog files, members of a group should keep in different disks so that one disk failure will not affect the normal database operation.For normal database operation at least two redo log groups is required.
Creating new logfile group
We can create new redolog groups using the following command.
SQL > ALTER DATABASE ADD LOGFILE
GROUP 4 (‘/u01/app/oracle/oradata/prod/redo0401.log’,
‘/u01/app/oracle/oradata/prod/redo0402.log’
‘/u01/app/oracle/oradata/prod/redo0403.log’ ) SIZE 10M;
We can add new member to an existing group using the following command.
SQL> ALTER DATABASE ADD LOGFILE MEMBER
‘/u01/app/oracle/oradata/prod/redo0401.log’ TO GROUP 3;
Renaming Log members
Following steps to be follow to rename log members
1. Shutdown database.
2. Copy/rename redolog file to new location with operating system command
3. startup database with STARTUP MOUNT command
4. Execute SQL> ALTER DATABSE RENAME FILE ‘oldfilename’ TO ‘newfilename’
5. ALTER DATABASE OPEN
Dropping redolog groups
SQL> ALTER DATABSE DROP LOGFILE GROUP 3