1) What is a backup? how many types of backups you can take in Oracle?
For any database to avoid the points of failures we need to take a backup. It can be a personal file or the database related.
In oracle there are two types of backup
1) User managed backup:-
Physical backup
Where User(DBA) will take manually by using OS commands cp
In USer managed backup there are again two types
Offline backup: (Cold backup)
Database shutdown
Datafiles,ControlFiles,Redologfiles,Temp Files,Undo Files, Archiveslogs, Pfile(spfile) and also pwd file
How to take cold backup and which all files should be taken?
——————————————————————————–
/u01/oradata/test/undotbs4.dbs
/u01/oradata/test/system.dbf
/u01/oradata/test/sysaux.dbf
/u01/oradata/test/undotbs1.dbf
/u01/oradata/test/undotbs2.dbf
/u01/oradata/test/undotbs3.dbf
/u01/oradata/test/redo05a.log
/u01/oradata/test/redo06a.log
/u01/oradata/test/redo07a.log
/u01/oradata/test/temp01.dbf
/u01/oradata/test/temp02.dbf
/u01/oradata/test/temp03.dbf
/u01/oradata/test/control01.ctl, /u01/oradata/test/control02.ctl, /u01/oradata
/test/control03.ctl
/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletest.ora
14 rows selected.
Hot backup
RMAN is the tool to take backup of database when the database is up and running, in order to take the database backup the database must be in archivelog mode
It takes the backup as two types
1) Backup pieces: Reads the blocks and take the backups of used blocks only, the size of backups can be less than datafile/database
2) Backup copies: Take the copy of the datafiles (image) as is, so the size of backup is same as datafile
2) What is the advantage of rman backup over user managed backup?
In user Managed backup the DBA has to manually take the records of the backup by issuing os cp command.
In Rman backup all the information of the backups will stores in control files.
2) What is Consistent hot backup? What ways you can take hot backup?
The backup will be taken when the database is up and running.Here we have to take backup of datafiles using CP command (oracle will not take)
by using
Option : 1
SQL>alter database begin backup;
CP datafiles;redolog,archives etc etc
SQL> alter database end backup
Another way is RMAN backup
RMAN> Connect target /
RMAN> backup database;
3) What is export backup?
Export backup is a logical backup which creates the statements in the dumpfile.(create table,alter table)
5) What is cold backup or offline backup, how you take it and what are all files to be included?
Cold backup is a consistent backup where we can take the back up while the database is down.Generally when the database is in the no archive log mode.
SQL>shut immediate;
cp all files
SQL>startup;
6) What is the advantage and disadvantage of having rman backup?
RMAN will update the control file about backup information and locations etc once the backup completes, DBA does not need to remember it unlike user managed recovery.
Only the disadvantage is table level recovery is not possible or its not pretty straight.
8) What is the prerequisite to take a rman backup?
The database must be in the archive log mode
9) How does rman backup will run?
we have to connect to database using rman tool , rman will create a server session in the database
10) How to take full database backup using rman?
rman target /
rman>backup database;
RMAN> backup database format disk ‘location'(specific location)
11) How many types of rman backups available? Differentiate them
Backup pieces and backup copies
These are al will be in the full backup,incremental or cummulative incremental
12) My database is generating archives hugely, how to take a backup and clean up?
Take archive backups periodically say 1 hour and delete them
rman>backup archivelog all delete input
13) Does controlfile and spfile includes in backup?
Yes
and the location of the files will be located in the by default at ORACLE_HOME/dbs directory
14) Does redolog files includes in rman backup?
No
becoz rman will take the archive log files rather than the redo log files.
15) What are all the files included in rman backup?
archive log files ,datafiles,control files,undo files and spfile
16) I want to speed up the rman backup what i can do?
Increase more backup channels
rman> configure device type disk parallelism 4;
17) Suppose i have taken archive backup at 6.00 AM and scheduled this job every hour, when taking backup at 7.00 AM the backup of archives generated 6.00AM also comes into 7.00AM? how to avoid this?
or
I dont want to backup archives duplicates as my job is scheduled every hour what i can do?
RMAN> configure backup optimization on;
Turning opitimize on will not take the backups to tape again if that backup is already there in the tape.
18) Suppose i have /u02/backup as my backup location and it is reached 90% , what the next step you take?
its depends on the retention, suppose u have 7 days retention, now u r in 8th day
RMAN> crosscheck backup;
RMAN> Report obsolete;
RMAN> Delete obsolete;
it will delete the the 7th day backup as its obsoleted now.
19) what are the types of export backups you can take
database/full
user/schema
object/table
20) Differentiate incremental/full/cumulative incremental backups
Full-backup of whole database
Incremental backup – The changes that happen from last full backup will be taken (or) The changes that contains after the full backup will take
Today Full backup
TOmorrow – incremental (contain only the changes that happens after full backup) so the size is very less.
Day after – incremental
Day aftet – cumulative increment(changes that happen after last full backup)so this backup contains two days changes(rather 1 day), this is use ful when recovery, recovery will be faster.
21) Whatis your companies backup strategy?
we have 2 strategies
logical back up via export , just in case if need of any table level recovery. though up to date recovery is not possible.
rman based backup every sunday we will take full backup and every day we will take incremental backup and every once in 4 hours we will take archive backup
22) What is retention?
This will defines,how many days that backup can be retained
23) what is redundancy?
How many copies of the same backup can be maintained
24) Differentiate retention and redundancy?
Various Recovery Scenarious
1) User managed recovery (in case of no rman backups only manual backups taken)
Database is in no archivelog mode = Incomplete recovery
a) Missing of system datafile
b) Missing of normal datafile
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
Database is in archivelog mode and all archives exists until point of failure (Complete recovery)
a) Missing of system datafile and sysaux and undo
Your database will crash (db is down)
copy the backup of failed file to the original location using OS CP
startup mount
recover datafile 1;
alter database open;
b) Missing of normal datafile
User will get an application error
copy the backup file to the original location
make the datafile offline
recover the datafile`
make the datafile online
c) Missing of control file (you have multiplexed controlfiles)
Though we have multiple control files if once control file will lost then the DB will crash
copy of control file from one of the location where the control file exist to the failed location
startup mount
alter database open
d) Missing of controlfile (backup exists 1 day before)
copy the backup of controlfile to the failed control file location
startup mount
recover database using backup controlfile
alter database open resetlogs;
e) Missing of controlfile (no backup exists)
If you know the datafiles/redolog locations
Create the controlfile manually by keeping location
Create controfile reuse DBNAME
logfile group 1 ‘/u01/
datafile 1 ‘/u01/test/system01.dbf’
datafile 2 ‘/u11/test/sysaux01.dbf’
datafile 3 ‘/u09/test/user01.dbf’
datafile 4 ‘/u04/test/undo01.dbf’;
SQL> startup nomount
SQL> run the above statement it will create the controlf file
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
d) missing of redolog file (backups are not applicable)
If the redo log file is current status (DB will crash)
If the redo log file is active (that means db will not crash since archiver keep retrying, you may lost that archives)
If the redo log file in inactive(means db will not crash, and there is no harm, since this redolog is already copied as archive)
All solutions should be add the redo log group in mount state.
depends on the status you will have to act accordingly.
SQL> alter database add logfile ‘location’ size 100m to group 5; example
e) Missing of undo file (undo file belfongs to ? undo tablepsace ? undo image of block before it get modified)
SQL> create undo tablepsace undotbs2 datafle ‘location’ size 1000m ;
SQL> alter system set undo_tablespace=undotbs2 scope=spfile;
SQL> shut immeidate (if its up and running)
SQL> startup
Db will now pick new undo tablespace
g) Missing of temp file (DB will not crash , statements that use sorting will fail with temp not availble)
Create a temporary tablespace new
SQL> alter database default temporary tablespace temp3;
Database is in archivelog mode and all archives DOES NOT exists until point of failure (Incomplete recovery)
a) Missing of system datafile
b) Missing of normal datafile
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
2) RMAN based recovery
Case : 1 (Latest backup exists and archives exists in backup) (Complete recovery)
a) Missing of system datafile
SQL> startup nomount
RMAN> restore datafile ‘/u01/test/system01.dbf’;
SQL> alter database mount
SQL> recover datafile 1;
SQL> alter database open;
b) Missing of normal datafile
RMAN> restore datafile ‘/u01/test/users01.dbf’
SQL> alter datafile ‘/u01/test/users01.dbf’ offline;
SQL> recover datafile ‘/u01/test/users01.dbf’;
SQL> alter datafile ‘/u01/test/user01.dbf’ online;
c) Missing of control file (no multiplex only rman backup exists)
RMAN> restore controfile from ‘backuplocation’;
SQL> startup mount
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
d) missing of redolsog file
Not applicable, manual creation of redolog groups appropriately
e) Missing of undo file
Not applicable, ust create new undo tbs and assign to database
g) Missing of temp file
Not applicable, create new temp tablespace
Case : 2 (Latest backup exists but archives missing or corrupted) (Incomplete recovery)
a) Missing of system datafile
Backup exists until 8.30 evening
Datfile missed at 8.30 morning
Archives available morning 6.30
Rest of the datafiles are available in locations
As you cannot restore or bring the system datafile until 8.30, you will have to take your entire database to 6.30 (upto archives avaibale)
If not the db is up and running , stop it
Delete all files, datafiles, controlfiles, redologs etc from OS location
Copy all the files datafiles,controlfiles,redologs from backup location to their respective locations
Startup mount
recover database until ‘2013-10-06 06:30:00’;
alternatively if you dont know time
recover database until cancel; (oracle read archives how many it can i.e 6.30 and then fails)
alter database open resetlogs;
b) Missing of normal datafile
Same as above,
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
3) Block corruption (Only a block in a file corrupted)
User Managed – block level not applicable
a)
Rman Managed
RMAN> recover block blocknum:
4) Table missing (one table is missing or dropped by mistake)
Export backup exists? Yes, you can import a single table from export dump, but the data is not until the table dropped, If no ask next
RMAN Backup Exists? Yes, You will need to create a seperate database and then log to that database, export that table and import in the old database to get that table. If no ask next
Flashback enabled? (10g onwards) Yes, If flashback is enabled and undo retention is good enough, you can just issue flashback table to before drop. If no ask next
Recycle bin on? (10g onwards) Yes, If no ask next
For any database to avoid the points of failures we need to take a backup. It can be a personal file or the database related.
In oracle there are two types of backup
1) User managed backup:-
Physical backup
Where User(DBA) will take manually by using OS commands cp
In USer managed backup there are again two types
Offline backup: (Cold backup)
Database shutdown
Datafiles,ControlFiles,Redologfiles,Temp Files,Undo Files, Archiveslogs, Pfile(spfile) and also pwd file
How to take cold backup and which all files should be taken?
SQL> select file_name from dba_data_files
2 union all
3 select member from v$logfile
4 union all
5 select file_name from dba_temp_files
6 union all
7 select value from v$parameter where name=’control_files’
8 union all
9 select value from v$parameter where name=’spfile’
10 ;
FILE_NAME2 union all
3 select member from v$logfile
4 union all
5 select file_name from dba_temp_files
6 union all
7 select value from v$parameter where name=’control_files’
8 union all
9 select value from v$parameter where name=’spfile’
10 ;
——————————————————————————–
/u01/oradata/test/undotbs4.dbs
/u01/oradata/test/system.dbf
/u01/oradata/test/sysaux.dbf
/u01/oradata/test/undotbs1.dbf
/u01/oradata/test/undotbs2.dbf
/u01/oradata/test/undotbs3.dbf
/u01/oradata/test/redo05a.log
/u01/oradata/test/redo06a.log
/u01/oradata/test/redo07a.log
/u01/oradata/test/temp01.dbf
/u01/oradata/test/temp02.dbf
/u01/oradata/test/temp03.dbf
/u01/oradata/test/control01.ctl, /u01/oradata/test/control02.ctl, /u01/oradata
/test/control03.ctl
/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletest.ora
14 rows selected.
Hot backup
Physical Backup
Take backup of datafiles only when the database is up and running by using begin backup and end backup
SQL> alter database begin backup;
CP all afiles
SQL> alter database end bakcup;
Logical Backup:- (metadata create statement and insert statements)
Export/ – Database/Schema/Table
2) Oracle Managed Backup using RMAN:-SQL> alter database begin backup;
CP all afiles
SQL> alter database end bakcup;
Logical Backup:- (metadata create statement and insert statements)
Export/ – Database/Schema/Table
RMAN is the tool to take backup of database when the database is up and running, in order to take the database backup the database must be in archivelog mode
It takes the backup as two types
1) Backup pieces: Reads the blocks and take the backups of used blocks only, the size of backups can be less than datafile/database
2) Backup copies: Take the copy of the datafiles (image) as is, so the size of backup is same as datafile
2) What is the advantage of rman backup over user managed backup?
In user Managed backup the DBA has to manually take the records of the backup by issuing os cp command.
In Rman backup all the information of the backups will stores in control files.
2) What is Consistent hot backup? What ways you can take hot backup?
The backup will be taken when the database is up and running.Here we have to take backup of datafiles using CP command (oracle will not take)
by using
Option : 1
SQL>alter database begin backup;
CP datafiles;redolog,archives etc etc
SQL> alter database end backup
Another way is RMAN backup
RMAN> Connect target /
RMAN> backup database;
3) What is export backup?
Export backup is a logical backup which creates the statements in the dumpfile.(create table,alter table)
5) What is cold backup or offline backup, how you take it and what are all files to be included?
Cold backup is a consistent backup where we can take the back up while the database is down.Generally when the database is in the no archive log mode.
SQL>shut immediate;
cp all files
SQL>startup;
6) What is the advantage and disadvantage of having rman backup?
RMAN will update the control file about backup information and locations etc once the backup completes, DBA does not need to remember it unlike user managed recovery.
Only the disadvantage is table level recovery is not possible or its not pretty straight.
8) What is the prerequisite to take a rman backup?
The database must be in the archive log mode
9) How does rman backup will run?
we have to connect to database using rman tool , rman will create a server session in the database
10) How to take full database backup using rman?
rman target /
rman>backup database;
RMAN> backup database format disk ‘location'(specific location)
11) How many types of rman backups available? Differentiate them
Backup pieces and backup copies
These are al will be in the full backup,incremental or cummulative incremental
12) My database is generating archives hugely, how to take a backup and clean up?
Take archive backups periodically say 1 hour and delete them
rman>backup archivelog all delete input
13) Does controlfile and spfile includes in backup?
Yes
and the location of the files will be located in the by default at ORACLE_HOME/dbs directory
14) Does redolog files includes in rman backup?
No
becoz rman will take the archive log files rather than the redo log files.
15) What are all the files included in rman backup?
archive log files ,datafiles,control files,undo files and spfile
16) I want to speed up the rman backup what i can do?
Increase more backup channels
rman> configure device type disk parallelism 4;
17) Suppose i have taken archive backup at 6.00 AM and scheduled this job every hour, when taking backup at 7.00 AM the backup of archives generated 6.00AM also comes into 7.00AM? how to avoid this?
or
I dont want to backup archives duplicates as my job is scheduled every hour what i can do?
RMAN> configure backup optimization on;
Turning opitimize on will not take the backups to tape again if that backup is already there in the tape.
18) Suppose i have /u02/backup as my backup location and it is reached 90% , what the next step you take?
its depends on the retention, suppose u have 7 days retention, now u r in 8th day
RMAN> crosscheck backup;
RMAN> Report obsolete;
RMAN> Delete obsolete;
it will delete the the 7th day backup as its obsoleted now.
19) what are the types of export backups you can take
database/full
user/schema
object/table
20) Differentiate incremental/full/cumulative incremental backups
Full-backup of whole database
Incremental backup – The changes that happen from last full backup will be taken (or) The changes that contains after the full backup will take
Today Full backup
TOmorrow – incremental (contain only the changes that happens after full backup) so the size is very less.
Day after – incremental
Day aftet – cumulative increment(changes that happen after last full backup)so this backup contains two days changes(rather 1 day), this is use ful when recovery, recovery will be faster.
21) Whatis your companies backup strategy?
we have 2 strategies
logical back up via export , just in case if need of any table level recovery. though up to date recovery is not possible.
rman based backup every sunday we will take full backup and every day we will take incremental backup and every once in 4 hours we will take archive backup
22) What is retention?
This will defines,how many days that backup can be retained
23) what is redundancy?
How many copies of the same backup can be maintained
24) Differentiate retention and redundancy?
Various Recovery Scenarious
1) User managed recovery (in case of no rman backups only manual backups taken)
Database is in no archivelog mode = Incomplete recovery
a) Missing of system datafile
b) Missing of normal datafile
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
Database is in archivelog mode and all archives exists until point of failure (Complete recovery)
a) Missing of system datafile and sysaux and undo
Your database will crash (db is down)
copy the backup of failed file to the original location using OS CP
startup mount
recover datafile 1;
alter database open;
b) Missing of normal datafile
User will get an application error
copy the backup file to the original location
make the datafile offline
recover the datafile`
make the datafile online
c) Missing of control file (you have multiplexed controlfiles)
Though we have multiple control files if once control file will lost then the DB will crash
copy of control file from one of the location where the control file exist to the failed location
startup mount
alter database open
d) Missing of controlfile (backup exists 1 day before)
copy the backup of controlfile to the failed control file location
startup mount
recover database using backup controlfile
alter database open resetlogs;
e) Missing of controlfile (no backup exists)
If you know the datafiles/redolog locations
Create the controlfile manually by keeping location
Create controfile reuse DBNAME
logfile group 1 ‘/u01/
datafile 1 ‘/u01/test/system01.dbf’
datafile 2 ‘/u11/test/sysaux01.dbf’
datafile 3 ‘/u09/test/user01.dbf’
datafile 4 ‘/u04/test/undo01.dbf’;
SQL> startup nomount
SQL> run the above statement it will create the controlf file
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
d) missing of redolog file (backups are not applicable)
If the redo log file is current status (DB will crash)
If the redo log file is active (that means db will not crash since archiver keep retrying, you may lost that archives)
If the redo log file in inactive(means db will not crash, and there is no harm, since this redolog is already copied as archive)
All solutions should be add the redo log group in mount state.
depends on the status you will have to act accordingly.
SQL> alter database add logfile ‘location’ size 100m to group 5; example
e) Missing of undo file (undo file belfongs to ? undo tablepsace ? undo image of block before it get modified)
SQL> create undo tablepsace undotbs2 datafle ‘location’ size 1000m ;
SQL> alter system set undo_tablespace=undotbs2 scope=spfile;
SQL> shut immeidate (if its up and running)
SQL> startup
Db will now pick new undo tablespace
g) Missing of temp file (DB will not crash , statements that use sorting will fail with temp not availble)
Create a temporary tablespace new
SQL> alter database default temporary tablespace temp3;
Database is in archivelog mode and all archives DOES NOT exists until point of failure (Incomplete recovery)
a) Missing of system datafile
b) Missing of normal datafile
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
2) RMAN based recovery
Case : 1 (Latest backup exists and archives exists in backup) (Complete recovery)
a) Missing of system datafile
SQL> startup nomount
RMAN> restore datafile ‘/u01/test/system01.dbf’;
SQL> alter database mount
SQL> recover datafile 1;
SQL> alter database open;
b) Missing of normal datafile
RMAN> restore datafile ‘/u01/test/users01.dbf’
SQL> alter datafile ‘/u01/test/users01.dbf’ offline;
SQL> recover datafile ‘/u01/test/users01.dbf’;
SQL> alter datafile ‘/u01/test/user01.dbf’ online;
c) Missing of control file (no multiplex only rman backup exists)
RMAN> restore controfile from ‘backuplocation’;
SQL> startup mount
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
d) missing of redolsog file
Not applicable, manual creation of redolog groups appropriately
e) Missing of undo file
Not applicable, ust create new undo tbs and assign to database
g) Missing of temp file
Not applicable, create new temp tablespace
Case : 2 (Latest backup exists but archives missing or corrupted) (Incomplete recovery)
a) Missing of system datafile
Backup exists until 8.30 evening
Datfile missed at 8.30 morning
Archives available morning 6.30
Rest of the datafiles are available in locations
As you cannot restore or bring the system datafile until 8.30, you will have to take your entire database to 6.30 (upto archives avaibale)
If not the db is up and running , stop it
Delete all files, datafiles, controlfiles, redologs etc from OS location
Copy all the files datafiles,controlfiles,redologs from backup location to their respective locations
Startup mount
recover database until ‘2013-10-06 06:30:00’;
alternatively if you dont know time
recover database until cancel; (oracle read archives how many it can i.e 6.30 and then fails)
alter database open resetlogs;
b) Missing of normal datafile
Same as above,
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
3) Block corruption (Only a block in a file corrupted)
User Managed – block level not applicable
a)
Rman Managed
RMAN> recover block blocknum:
4) Table missing (one table is missing or dropped by mistake)
Export backup exists? Yes, you can import a single table from export dump, but the data is not until the table dropped, If no ask next
RMAN Backup Exists? Yes, You will need to create a seperate database and then log to that database, export that table and import in the old database to get that table. If no ask next
Flashback enabled? (10g onwards) Yes, If flashback is enabled and undo retention is good enough, you can just issue flashback table to before drop. If no ask next
Recycle bin on? (10g onwards) Yes, If no ask next
No comments:
Post a Comment