Welcome to Day-10 preparation.
Today we are going to prepare interview questions on Backups.
A database is running in
NOARCHIVELOG mode then which type of backups you can take?
Cold backup and logical backups
Can you take partial backups if
the Database is running in NOARCHIVELOG mode?
No. partial backups (differential
and cumulative backups possible only when database is in archive log mode)
Can you take Online Backups if
the database is running in NOARCHIVELOG mode?
Yes. Only logical backup not
physical
You cannot shutdown the database for even some minutes, then in which mode you should run the database?
Archive log mode. because online
backups dont required downtime
Where should you place Archive
logfiles, in the same disk where DB is or another disk?
it is suggested to place archived
logs in another disk , but not in same disk where CRD files
stored(control,redo,datafiles)
Can you take online backup of a
Control file if yes, how?
yes .
alter database backup controlfile
to '/u01/app/oradata/backup/control.ctl' -- to get binary control
file
alter database backup controlfile
to tace as '/tmp/control.sql' -- to get control file in text format
How do you bring the database in ARCHIVELOG mode from
NOARCHIVELOG mode?
click here for
answer
Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
yes. they are required for transaction recovery
What is the advantage of RMAN utility?
1)copies only the filled blocks i.e. ignores
empty blocks
2)incremental and accumulative backup.
3)catalog and no catalog option.
4)detection of corrupted blocks during backup;
5)can create and store the backup and recover scripts.
6)increase performance through automatic parallelization(
allocating channels), less redo generation.
How RMAN improves backup time?
backup can be parallelized by increasing number of channels.
Can you take Offline backups using RMAN?
No. DB should be in nomount state
How do you see information about backups in RMAN?
we can check the backup in RMAN prompt using command
RMAN > list backp
RMAN > crosscheck backup
or we can check in RMAN related views i.e. v$backup,v$backup_piece
What is a Recovery Catalog?
Recovery catalog maintains the target database backup details and configuration.If the target database controlfiles are lost recovery can become difficult if not impossible.Having recovery catalog makes the DBA life easier at the time of critical scenario. Even for larger system the use of a recovery catalog can increase the backup performance.
Should you place Recovery Catalog in the Same DB?
No
Can you use RMAN without Recovery catalog?
yes.
~] rman TARGET SYS@prod NOCATALOG
Can you take Image Backups using RMAN?
Yes. using RMAN> backup as copy database; command
Can you use Backupsets created by RMAN with any other utility?
No
Where RMAN keeps information of backups if you are using RMAN without Catalog?
controlfile
You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
it can't be used by RMAN
You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
RMAN>configure retention policy to redundancy 3;
Which is more efficient Incremental Backups using RMAN or Incremental Export?
incremental backup using RMAN. dataloss wont be there.
Can you start and shutdown DB using RMAN?
Yes.
RMAN>shutdown immediate
RMAN>startup mount
How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?
You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?
restore datafile and recover datafile
You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?
SQL>shut abort;
SQL>startup mount;
SQL>flashback database to timestamp sysdate -1/24;
You loss controlfile how do you recover from this?
click here for answer
What is a Complete Recovery?
In a complete database restore, the goal is to restore the whole database. The whole database is offline for the duration of the restore. Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.
What is Cancel Based, Time based and Change Based Recovery?
A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command (a SQL*Plus command that is used to recover a database).
The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command.
similarly Time based, SCN based are recovering the database until particular time ,particluar SCN
Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?
yes.
we need to perform flashback table(FLASHBACK TABLE flashback_drop_test TO BEFORE DROP) or else we need to perform point intime recovery(PITR). but PITR is risky and time taking.
Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?
yes. Example: RESTORE DATAFILE 6;
A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?
Yes. use the old previous backup and apply archived logs to recover it.
How do you recover from the loss of a controlfile if you have backup of controlfile?
click here for answer
Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?
Yes.
check the corrupted blocks:COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A30
SELECT DISTINCT owner, segment_name FROM v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;
and then validate the datafile
rman> validate datafile 4;
Now recover the corrupted blocks:
BLOCKRECOVER DATAFILE 3 BLOCK 121;
At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.
SQL> create spfile from pfile
or
create a pfile with values from alert log
What is Consistent Backup?
What are the steps to performing complete recovery on the whole database?
What are the steps to perform complete recovery on a tablespace or datafile?
How RMAN improves backup time?
backup can be parallelized by increasing number of channels.
Can you take Offline backups using RMAN?
No. DB should be in nomount state
How do you see information about backups in RMAN?
we can check the backup in RMAN prompt using command
RMAN > list backp
RMAN > crosscheck backup
or we can check in RMAN related views i.e. v$backup,v$backup_piece
What is a Recovery Catalog?
Recovery catalog maintains the target database backup details and configuration.If the target database controlfiles are lost recovery can become difficult if not impossible.Having recovery catalog makes the DBA life easier at the time of critical scenario. Even for larger system the use of a recovery catalog can increase the backup performance.
Should you place Recovery Catalog in the Same DB?
No
Can you use RMAN without Recovery catalog?
yes.
~] rman TARGET SYS@prod NOCATALOG
Can you take Image Backups using RMAN?
Yes. using RMAN> backup as copy database; command
Can you use Backupsets created by RMAN with any other utility?
No
Where RMAN keeps information of backups if you are using RMAN without Catalog?
controlfile
You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
it can't be used by RMAN
You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
RMAN>configure retention policy to redundancy 3;
Which is more efficient Incremental Backups using RMAN or Incremental Export?
incremental backup using RMAN. dataloss wont be there.
Can you start and shutdown DB using RMAN?
Yes.
RMAN>shutdown immediate
RMAN>startup mount
How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?
recovery is not possible if the
database is in NOARCHIVELOG mode.
You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?
restore datafile and recover datafile
You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?
SQL>shut abort;
SQL>startup mount;
SQL>flashback database to timestamp sysdate -1/24;
SQL> alter database open
resetlogs;
You loss controlfile how do you recover from this?
click here for answer
What is a Complete Recovery?
In a complete database restore, the goal is to restore the whole database. The whole database is offline for the duration of the restore. Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.
What is Cancel Based, Time based and Change Based Recovery?
A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command (a SQL*Plus command that is used to recover a database).
The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command.
similarly Time based, SCN based are recovering the database until particular time ,particluar SCN
Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?
yes.
we need to perform flashback table(FLASHBACK TABLE flashback_drop_test TO BEFORE DROP) or else we need to perform point intime recovery(PITR). but PITR is risky and time taking.
Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?
yes. Example: RESTORE DATAFILE 6;
A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?
Yes. use the old previous backup and apply archived logs to recover it.
How do you recover from the loss of a controlfile if you have backup of controlfile?
click here for answer
Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?
Yes.
check the corrupted blocks:COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A30
SELECT DISTINCT owner, segment_name FROM v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;
and then validate the datafile
rman> validate datafile 4;
Now recover the corrupted blocks:
BLOCKRECOVER DATAFILE 3 BLOCK 121;
At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.
SQL> create spfile from pfile
or
create a pfile with values from alert log
What is Consistent Backup?
A Consistent backup is one in
which the files being backed up contain all changes upto the same system change
number (SCN). cold backup is complete backup.
What are the steps to performing complete recovery on the whole database?
·
Mount the database
·
Ensure that all datafiles you want to recover are online
·
Restore a backup of the whole database
·
Apply online or archived redo logs, or a combination of the two.
What are the steps to perform complete recovery on a tablespace or datafile?
·
Take the tablespace or datafile to be recovered offline if the
database is open
·
Restore a backup of the datafiles you want to recover
·
Apply online or archived redo logs, or a combination of the two
No comments:
Post a Comment