Backup and recovery

In general there are three steps involved in restoring files:

Ensure that the target database is started in the appropriate mode for the restoration operation. For lost control files, this will be nomount mode.

If the entire database needs to be restored, this will be mount mode. If datafiles that don't belong to the SYSTEM tablespace are damaged, you have

the option of keeping the database open and taking only the tablespace(s)/datafile(s) that needs to be restored offline.
Start RMAN and connect to the target and recovery catalog if one is being used.
Run the appropriate RMAN RESTORE command to bring back required files. The requested files and the appropriate archived redo log files will be restored.
Once the necessary files are restored, you need to recover your database and open it for use. You can recover the database from either RMAN or SQL*Plus.


Labels: RECOVERY, RMAN
July 24, 2013
Oracle DBA Interview Questions and Answers - Backup and Recovery
Oracle Backup and Recovery Interview Questions and Answers


How would you decide your backup strategy and timing for backup?In fact backup strategy is purely depends upon your organization business need.
If no downtime then database must be run on archivelog mode and you have to take frequently or daily backup.
If sufficient downtime is there and loss of data would not affect your business then you can run your database in noarchivelog mode and backup can be

taken in-frequently or weekly or monthly.
In most of the case in an organization when no downtime then frequent inconsistent backup needed (daily backup), multiplex online redo log files

(multiple copies), different location for redo log files, database must run in archivelog mode and dataguard can be implemented for extra bit of

protection.


What is difference between Restoring and Recovery of database?
Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply

the database object copied earlier (roll forward) in order to bring the database into consistent state.

What is the difference between complete and incomplete recovery?An incomplete database recovery is a recovery that it does not reach to the point of

failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog

failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.

What is the benefit of running the DB in archivelog mode over no archivelog mode?
When a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this, redo

logs must be archived. This can be achieved by configuring the database in archivelog mode.


If an oracle database is crashed? How would you recover that transaction which is not in backup?If the database is in archivelog we can recover that

transaction otherwise we cannot recover that transaction which is not in backup.

What is the difference between HOTBACKUP and RMAN backup?
For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. RMAN is

faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode.

Can we use Same target database as Catalog database?
No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted

state.

Incremental backup levels:
Level 0 – full backup that can be used for subsequent incrementals
RMAN> backup incremental level 0 database;
Differential Level 1–only the blocks that have changed since the last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential database;
Cumulative Level 1 – all changes since the last level 0 incremental backup
RMAN> backup incremental level 1 cumulative database;
A full backup cannot be used for a cumulative level 1 backup.
A cumulative level 1 backup must be done on top of an incremental level 0 backup.



Why RMAN incremental backup fails even though full backup exists?If you have taken the RMAN full backup using the command ‘Backup database’, where as

a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the

RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ can not be considered as a parent

backup from which you can take level 1 backup.


Can we perform RMAN level 1 backup without level 0?If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle

version).
If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup.
If the compatibility is greater than 10.0.0, RMAN copies all block changes since the file was created, and stores the results as level 1 backup.

How to put Manual/User managed backup in RMAN?In case of recovery catalog, you can put by using catalog command:
RMAN> CATALOG START WITH ‘/oracle/backup.ctl’;



How to check RMAN version in oracle?If you want to check RMAN catalog version then use the below query from SQL*plus
SQL> Select * from rcver;

What happens actually in case of instance Recovery?While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is

being re-started. Instance recovery occurs in 2 steps:
Cache recovery: Changes being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. When there are

enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before these data are written to data files,

Oracle uses online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.
Transaction recovery: When a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is

used to restore the original values in case the transaction is rolled back). At the time of an instance failure, the database may have uncommitted

transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency,

Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to

accomplish this. This process is called transaction recovery.

My Database has Level 1 backup, tell me what are all backed up ? with Example?


Database is UP and has taken Level 0 backup, is the backup taken is Consistent or Inconsistent?

How do you say a backup is Consistent or Inconsistent, Oracle Terminology?

Can we take backup when the Database is down?

If i have a RMAN full backup Level 0 of Sun @9PM, on Mon 9PM taken incremental Level 1 backup.What type of backup do you get and what is actually

backedup?

If i have a RMAN full backup of Sun @9PM, on Mon 9PM taken incremental Level 1 backup.On Tuesday Database Crashed.What type of backup do you get and

what is actually backedup?

There is no Backup available, Can we take a Level 1 backup?

A table got dropped between 9AM - 11AM how to get the Table backup using RMAN,
DB size 500GB available mount point space for table recovery is 15GB?

Sys Admin has changed the time from 10:00 AM to 9:30 AM, table dropped, How do you recover the Table?

A DATAFILE is corrupted and there is no backup, How to recover the datafile?



Labels: BACKUP, Oracle DBA Interview Questions and Answers, RECOVERY
June 21, 2013
RMAN Backup and Recovery Scenario Point-In-Time Recovery
Backup and Restore Scenario from existing Level 0 backup
-------------------------------------------------------
steps to recover the database:-

1. Take Level 0 backup.
2. Lost SYSTEM datafile.
3. Start the instance without mounting the database.
4. Restore Controlfile.
5. Mount the database.
6. Restore database.
7. Recovery database.
8. Open the database and reset logs.

1. Take Level 0 backup:

run
{
allocate channel channel1 type disk;
allocate channel channel2 type disk;
allocate channel channel3 type disk;
backup incremental level=0 database;
backup archivelog all;
backup current controlfile;
}

2. Lost SYSTEM datafile.

Note:
----
Need controlfile, backup and parameter file for recoverying database.

3. Start the instance without mounting the database.

$sqlplus "/as sysdba"

SQL> startup nomount;
SQL> exit

4. Restore Controlfile.

$rman target /
RMAN> restore controlfile;

5. Mount the database.
RMAN> alter database mount;

6. Restore and recover & Point-In-Time Recovery of all data back to a particular date/time in the past.
run
{
set until time "to_date('2013-03-20:14:40:00','YYYY-MM-DD:hh24:mi:ss')";
restore database;
recovery database;
}

7.Open the database and reset logs.

SQL> alter database open resetlogs;
This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

NOTE:
----
As soon as you have done a resetlogs run a full backup, this is important as should you suffer a second failure you will not be able to perform a

second recovery because after resetting the logs the SCN numbers will no longer match any older backup files.

http://ss64.com/ora/rman_pitr_example.html


Labels: BACKUP, ISSUE, RECOVERY, RMAN
June 13, 2013
RMAN Troubleshooting
RMAN Troubleshooting Queries at Database Level

SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999
COL TABLESPACE_NAME FORMAT A50
COL FILE_NAME FORMAT A50
COL NAME FORMAT A50
COL MEMBER FORMAT A50
col DFILE_CHKP_CHANGE format a40
col DFILE_HED_CHKP_CHANGE format a40

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

ARCHIVE LOG LIST;

SELECT * FROM gv$instance;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
select distinct status from v$backup;
select distinct(status) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile_header;

select * from v$backup;

SELECT dbid,
name,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode,
log_mode,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
TO_CHAR(controlfile_change#, '999999999999999') as controlfile_change#,
TO_CHAR(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
FROM v$database;

SELECT f.name, b.status, b.change#, b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';

SELECT name,
file#,
status,
enabled,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(offline_change#, '999999999999999') as offline_change#,
TO_CHAR(online_change#, '999999999999999') as online_change#,
TO_CHAR(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile
where status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);

SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
WHERE status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);

SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
to_char(dd.checkpoint_change#,'999999999999999') dfile_chkp_change,
to_char(dh.checkpoint_change#,'999999999999999') dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;

SELECT name file_name,
recover,
fuzzy,
checkpoint_change#
FROM v$datafile_header
ORDER BY checkpoint_change#;

SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhrba_seq sequence
FROM x$kcvfh;

SELECT group#,
thread#,
sequence#,
members,
archived,
status,
TO_CHAR(first_change#, '999999999999999') as first_change#
FROM v$log;

SELECT group#,member
FROM v$logfile;

SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;


Recover Standby DB from a missing archivelog
A Physical Standby database relies on continuous application of archivelogs from a Primary Database to be in synch with it. In Oracle Database

versions prior to 10g in the event of an archivelog gone missing or corrupt you had to rebuild the standby database from scratch.

In 10g you can use an incremental backup from SCN and recover the standby using the same to compensate for the missing archivelogs as shown below

Step 1: On the standby database check the current scn.
STDBY> set numwidth 30;
STDBY> select current_scn from v$database;

CURRENT_SCN
-----------
123456789

Step 2: On the primary database create the needed incremental backup from the above SCN
rman target /
RMAN> {
allocate channel c1 type disk;
BACKUP INCREMENTAL FROM SCN 123456789 DATABASE
}
Step 3: SCP the backup files to standby server to /tmp/incr_bkp folder.

searching for all files that match the pattern /tmp/incr_bkp/

List of Files Unknown to the Database
=====================================

Step 4: Catalog the Incremental Backup Files at the Standby Database
/tmp/incr_bkp > rman target /

RMAN> CATALOG START WITH '/tmp/incr_bkp/';

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

Step 5: Apply the Incremental Backup to the Standby Database
RMAN> RECOVER DATABASE NOREDO;
Media recovery complete.

Step 6: Put the standby database back to managed recovery mode.
STDBY>> recover managed standby database disconnect;


From the alert.log you will notice that the standby database is still looking for the old log files

*************************************************
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence ....
**************************************************
This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

Step 7: On the primary create new standby controlfile
PRIM>alter database create standby controlfile as ‘/tmp/incr_bkp/standby01.ctl’;
System altered.

Step 8: At Standby .. Replace standby controlfile at all location as shown by controle_files parameter.
Copy the standby control file to the standby site. Shutdown the stanby database and replace the stanby controlfiles and restart the standby database

in managed recovery mode...


Note: - FOR STANDBY DATABASES ON ASM additional steps is required after replacing the stanby control file. Like renaming datafiles