!! oracle backup/restore interview questions : !!
*. What is the difference between cold and hot backup?
a.COLD backup will be taken by shutting down the database, where as HOT backup will be taken while database is up and running
*. What happens when a database or tablespace is placed in begin backup mode?
Datafile header will get freezed i.e CKPT will not update latest SCN
DBWR still will write data into datafiles
When end backup, CKPT will update the latest SCN to datafile header
*. Why more redo will generate during hot backup?
It is to avoid fractured block as oracle will copy entire block as redo entry
*. What is fractured block?
A block which might contain inconsistent data. This happens because the speed of DBWR is different than OS copy during hot backup
*. What is the difference between complete and incomplete recovery?
No data loss in complete recovery whereas some data loss is observed in incomplete recovery
*. What will happen if we use resetlogs?
It will create new redolog files (if not already there)
It will reset log sequence number to 1,2…etc
*. Out of 100 datafiles, I lost 29 files. How you will identify which files to recover?
a.Using the view v$recovery_file
*. How to check if incomplete recovery is performed in the database?
SQL> select RESETLOGS_TIME,RESETLOGS_CHANGE from v$database;
*. I placed a tablespace in hot backup mode and datafile which is being backup is lost. How you will recover it?
We can restore from old backup and apply all the archives till now
Yesterday night backup is successful. Today morning we added a datafile at 11 AM. After noon 3’o clock the new
*. added datafile is lost. Can I recover that datafile? If so, how?
Yes we can recover it. We need to create that datafile using “alter database create datafile ‘path’;” command and then apply all the archives
*. What is the importance of archives during recovery process?
As they will store all the changes happened in the database, always we can do complete recovery if we have a perfect backup
*. How you will recover database when all copies of control file are lost?
We can do a complete recovery if we have a latest trace of it. Or else, we need to do incomplete recovery by restoring controlfile from last successful backup
*. Application team informed that an important table is dropped. How you will recover it?
We can recover it by doing until time recovery. But this will affect other user transactions, so we need to get approval for this first
*. What are the pre-requisite factors you will consider before performing until time recovery?
We need to see if other users are not getting any affect by doing this
*. You need to restore the database and then realized there are no control files. How you will proceed?
Either we can resotre controlfiles first and do recovery (but a data loss is there as its incomplete recovery) or we can create new controlfile if we have a latest trace
*. 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 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.
*. 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.
commands :
RMAN> CONNECT CATALOG rman/pwd@catdb;
RMAN> CONNECT AUXILIARY rman/pwd@auxdb;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;
RMAN> IMPORT CATALOG rman/rman@catdb1 DB_NAME=prod1 NO UNREGISTER;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR SBT TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR SBT TO 1; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; # default -- Oracle 11g R2
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '.../dbs/snapcf_sid.f'; # default
%F = dbid, day, month, year and sequence
%U = %u_%p_%c
%u = eight characters of the backup set and time ...
%p = piece number within the backupset
%c = copy number of the backup piece ...
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=mml_env_settings';
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT 'SYS/pwd@node2' PARMS 'ENV=(NSR_SERVER=bksrv2)';
RMAN> SET DBID=4240978820;
RMAN> SET COMMAND ID TO 'rman';
BACKUP FULL Options
BACKUP FULL AS (COPY | BACKUPSET) Options
BACKUP INCREMENTAL LEVEL [=] integer Options
BACKUP INCREMENTAL LEVEL [=] integer AS (COPY | BACKUPSET) Options
BACKUP AS (COPY | BACKUPSET) Options
BACKUP AS (COPY | BACKUPSET) (FULL | INCREMENTAL LEVEL [=] integer) Options
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
BACKUP VALIDATE DATABASE;
BACKUP VALIDATE CHECK LOGICAL DATABASE;
BACKUP AS COPY TABLESPACE test;
LIST INCARNATION OF DATABASE;
listBackupOption::=
[[BY BACKUP] [VERBOSE] | SUMMARY | BY {BACKUP SUMMARY|FILE}]
RMAN> REPORT OBSOLETE;
RMAN> REPORT NEED BACKUP;
RMAN> REPORT NEED BACKUP DAYS=5;
RMAN> REPORT NEED BACKUP REDUNDANCY=3;
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS;
RMAN> REPORT NEED BACKUP DATABASE;
RMAN> REPORT NEED BACKUP INCREMENTAL 1;
RMAN> REPORT UNRECOVERABLE;
RMAN> REPORT SCHEMA;
RMAN> REPORT SCHEMA AT TIME 'sysdate-20/1440'
CROSSCHECK BACKUP OF DATABASE;
CROSSCHECK BACKUP OF TABLESPACE userd COMPLETED BEFORE 'SYSDATE-14';
RESTORE SPFILE;
RESTORE ARCHIVELOG ALL VALIDATE;
RESTORE ARCHIVELOG ALL PREVIEW;
DUPLICATE TARGET DATABASE TO dwhdb;
SWITCH DATABASE TO COPY;
*. What is the difference between cold and hot backup?
a.COLD backup will be taken by shutting down the database, where as HOT backup will be taken while database is up and running
*. What happens when a database or tablespace is placed in begin backup mode?
Datafile header will get freezed i.e CKPT will not update latest SCN
DBWR still will write data into datafiles
When end backup, CKPT will update the latest SCN to datafile header
*. Why more redo will generate during hot backup?
It is to avoid fractured block as oracle will copy entire block as redo entry
*. What is fractured block?
A block which might contain inconsistent data. This happens because the speed of DBWR is different than OS copy during hot backup
*. What is the difference between complete and incomplete recovery?
No data loss in complete recovery whereas some data loss is observed in incomplete recovery
*. What will happen if we use resetlogs?
It will create new redolog files (if not already there)
It will reset log sequence number to 1,2…etc
*. Out of 100 datafiles, I lost 29 files. How you will identify which files to recover?
a.Using the view v$recovery_file
*. How to check if incomplete recovery is performed in the database?
SQL> select RESETLOGS_TIME,RESETLOGS_CHANGE from v$database;
*. I placed a tablespace in hot backup mode and datafile which is being backup is lost. How you will recover it?
We can restore from old backup and apply all the archives till now
Yesterday night backup is successful. Today morning we added a datafile at 11 AM. After noon 3’o clock the new
*. added datafile is lost. Can I recover that datafile? If so, how?
Yes we can recover it. We need to create that datafile using “alter database create datafile ‘path’;” command and then apply all the archives
*. What is the importance of archives during recovery process?
As they will store all the changes happened in the database, always we can do complete recovery if we have a perfect backup
*. How you will recover database when all copies of control file are lost?
We can do a complete recovery if we have a latest trace of it. Or else, we need to do incomplete recovery by restoring controlfile from last successful backup
*. Application team informed that an important table is dropped. How you will recover it?
We can recover it by doing until time recovery. But this will affect other user transactions, so we need to get approval for this first
*. What are the pre-requisite factors you will consider before performing until time recovery?
We need to see if other users are not getting any affect by doing this
*. You need to restore the database and then realized there are no control files. How you will proceed?
Either we can resotre controlfiles first and do recovery (but a data loss is there as its incomplete recovery) or we can create new controlfile if we have a latest trace
*. 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 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.
*. 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.
commands :
RMAN> CONNECT CATALOG rman/pwd@catdb;
RMAN> CONNECT AUXILIARY rman/pwd@auxdb;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;
RMAN> IMPORT CATALOG rman/rman@catdb1 DB_NAME=prod1 NO UNREGISTER;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR SBT TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR SBT TO 1; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; # default -- Oracle 11g R2
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '.../dbs/snapcf_sid.f'; # default
%F = dbid, day, month, year and sequence
%U = %u_%p_%c
%u = eight characters of the backup set and time ...
%p = piece number within the backupset
%c = copy number of the backup piece ...
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=mml_env_settings';
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT 'SYS/pwd@node2' PARMS 'ENV=(NSR_SERVER=bksrv2)';
RMAN> SET DBID=4240978820;
RMAN> SET COMMAND ID TO 'rman';
BACKUP FULL Options
BACKUP FULL AS (COPY | BACKUPSET) Options
BACKUP INCREMENTAL LEVEL [=] integer Options
BACKUP INCREMENTAL LEVEL [=] integer AS (COPY | BACKUPSET) Options
BACKUP AS (COPY | BACKUPSET) Options
BACKUP AS (COPY | BACKUPSET) (FULL | INCREMENTAL LEVEL [=] integer) Options
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
BACKUP VALIDATE DATABASE;
BACKUP VALIDATE CHECK LOGICAL DATABASE;
BACKUP AS COPY TABLESPACE test;
LIST INCARNATION OF DATABASE;
listBackupOption::=
[[BY BACKUP] [VERBOSE] | SUMMARY | BY {BACKUP SUMMARY|FILE}]
RMAN> REPORT OBSOLETE;
RMAN> REPORT NEED BACKUP;
RMAN> REPORT NEED BACKUP DAYS=5;
RMAN> REPORT NEED BACKUP REDUNDANCY=3;
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS;
RMAN> REPORT NEED BACKUP DATABASE;
RMAN> REPORT NEED BACKUP INCREMENTAL 1;
RMAN> REPORT UNRECOVERABLE;
RMAN> REPORT SCHEMA;
RMAN> REPORT SCHEMA AT TIME 'sysdate-20/1440'
CROSSCHECK BACKUP OF DATABASE;
CROSSCHECK BACKUP OF TABLESPACE userd COMPLETED BEFORE 'SYSDATE-14';
RESTORE SPFILE;
RESTORE ARCHIVELOG ALL VALIDATE;
RESTORE ARCHIVELOG ALL PREVIEW;
DUPLICATE TARGET DATABASE TO dwhdb;
SWITCH DATABASE TO COPY;
