Backup and Recovery
Types:
- Physical backup – i. Cold backup ii. Hot backup & RMAN
- Logical backup – i. Export ii. Import
| Cold Backup | Hot Backup | RMAN |
| Offline / consistent backup | Online / partial / inconsistent backup, only a read consistent and no active transactions backup | consistent |
| DB down | Can Backup online. | Can Backup online. |
| Both modes | Database should be in archive mode | Any Mode |
| All physical files | Tablespace, control file | All physical + logical |
Cold Backup:
It’s an offline backup/ consistent backup.
Database needs to be down by – Shutdown immediate /normal
Oracle will run checkpoint and write a SCN number.
If you use, shutdown abort for cold backup, you cannot recover the database.
We can run this in both archive and no archive mode. But, there will be a different recovery for each.
Note: In No archive mode, Recovery needs all the files incase if the redo log files are not overwritten, we can only restore/recovery particular file.
What are the files we need to backup by cold backup?
Data file, Redo log file, Control file – Mandatary
Password file, Parameter file – Optional
$mkdir –p /backup/coldbackup/test1/11092015
Note the location of files:
Sql> select name from v$datafile;
Sql> select member from v$logfile;
Sql> select name from v$controlfile;
Optional: Sql> Create pfile =’\opt\init.ora’ from spfile;
Sql> Shutdown immediate;
$cd /opt/oradata/
$ cp –r *.dbf /backup/coldbackup/test1/11092015
$ cp –r *.log /backup/coldbackup/test1/11092015
$ cp –r *.ctl /backup/coldbackup/test1/11092015
Note: You can corrupt the data file by > Data file name
Sql> Startup
Note: If you take a backup in archive log mode, it should be recovered in archive mode only and no archive log — no archive mode.
Recovery scenario
In No archive mode vs Archive mode
| No Archive mode | Archive Mode |
| DB needs to be down | Recovery can be done in online |
| We need to restore all files | We can only restore corrupted file |
| Data loss | No data loss |
We have a backup on Sunday 10PM — cold backup — one tablespace crashed on Wednedsday — emp table has 1500 rows.
No archive mode
Database needs to down, but oracle will try to write SCN and it will fail for normal shutdown immediate, Since the file is tablespace is not available.
Sql> shutdown abort
$ cp –r *.dbf /opt/oracle/oradata/test/
$ cp –r *.log /opt/oracle/oradata/test
$ cp –r *.ctl /opt/oracle/oradata/test/
Check the table, it only have 500 rows
Archive mode
Sql> Alter database datafile ‘/opt/oracle/oradata/test/tools.dbf’ offline drop;
$ cp tools.dbf /opt/oracle/oradata/test/
Sql> Recover datafile ‘/opt/oracle/oradata/test/tools.dbf’;
Sql> Alter database datafile ‘/opt/oracle/oradata/test/tools.dbf’ online;
Check the table, it only have 1500 rows, since the oracle will take the data between dates from archive log backup.
Ex: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1892210373157
Recovery Scenario Transaction & data file lost:
Backup taken in archive mode — committed data all comes from archive log, even if the backup is too. Old.
Shutdown Normal:
Case 1
No Archive log — commit 2+ un commit 2 rows — shutdown & Backup — Startup & commit 2 data leave 2 data — reboot VM — BB 2 AB 2 = 4 all committed data will come.
No Archive log — commit 2+ un commit 2 rows — shutdown & Backup — Startup & commit 2 data leave 2 data —
- corrupt data file —flush all — copy a data file from old backup — (it shutdown automatically ORA-01110: data file 12: ‘/opt/oracle/oradata/test1/muthu01.dbf’)
It brings all committed data before & after backup.
- Drop offline data file & corrupt –flush all — copy a data file from old backup —
Case 2
Archive log — commit 2+ un commit 2 rows — shutdown & Backup — Startup & commit 2 data leave 2 data — reboot VM — BB 2 AB 2 = 4 all committed data will come.
Archive log — commit 2+ un commit 2 rows — shutdown & Backup — Startup & commit 2 data leave 2 data — reboot VM & delete datafile — restore a tbs from old backup — (It will say data file not available, so shutdown and restore it Error: ORA-01113: file 4 needs media recovery)
Recover database — check the row count: BB 2 AB 2 = 4 here media recovery only recover the committed data will come.
| Log Mode | Transaction Lost | Data file lost |
| Archive | all committed | media recovery only recover the committed |
| No Archive | all committed | media recovery, will recovery the uncommitted |
Hot Backup
It’s an online backup / partial backup / inconsistent backup, only a read consistent and no active transactions can be backup.
Can backup online.
Database should be in archive mode.
What are the files we need to backup by hot backup?
Tablespace
Control files
We can take archive log backup in any mode and anytime. Since, it’s an offline copy of redo log.
Tablespace Backup
$ mkdir –p /backup/hotbackup/test/11092015
Sql> select * from v$tablespace;
Sql> select file_name from dba_data_file where tablespace_name =’tools’;
Note down all the data file info, which are belongs to the tablespace.
Sql> Alter tablespace <tablespace> begin backup;
Sql> select * from v$backup;
| File # | Stauts |
| 1 | not active |
| 2 | active |
| 3 | cannot read header |
Not active – Default state.
Active – Waiting for hot backup
Cannot read header – File corrupted
$ cd /opt/oracle/oradata/test
$ cp –r toolso1.dbf /backup/hotbackup/test/11092015
Sql> Alter tablespace <tablespace> end backup;
Now, check the Sql> select * from v$backup;
Note: Without begin backup if you copy a file, it will be corrupted, in case any transaction is running on the time. If no transaction is running at the time, there will be no problem.
What will happen internally, after you started the begin backup:
Checkpoint will run and update the SCN in all the data files except the backup tablespace.
Backup tablespace header will be freezed, this is the reason it is called inconsistent backup.
All the new entries will not written, until the backup ends. It will be written in redo and archive log files. Archive log may generate more.
When backup ends, it will refer the other data file and update the SCN.
Same Recovery scenario: Power failure between begin and end backup
Database will be stopped at mount stage, since it’s not consistent backup and SCN will differ.
Sql> startup mount
Sql> alter database end backup;
Sql> alter database open;
Sql> alter tablespace tools offline;
$ cd backup location
$ cp –r toolso1.dbf /opt/oracle/oradata/test
Sql> recover tablespace tools;
All data taken from the archive log file.
Sql> alter tablespace tools online;
Data file recovery – Same as what we have done last course
Conculsion: If the tablespace have more data files, then tablespace recovery will be good, if it has only one file data file recovery will be good.
System tablespace corruption and recovery
We can take online backup, but the recovery should be done in DB down state.
Sql> shutdown abort – Since SCN not updated
$ cd backup location
$ cp –r system01.dbf /opt/oracle/oradata/test
Sql> startup mount
Sql> recover database;
Sql> alter database open resetlogs;/ noresetlogs
Noresetlog – default
Resetlog
It will start the LSN from the scratch 0,1…
It is like a new database and all the archive log backups are absolute, cannot be use in the recovery purpose, Since the LSN resets.
Noresetlog – It will read the redo log and take the next LSN.
Control file backup and recovery
Two types of backup 1. Trace file 2. Binary file
Trace file method:
First check the udump location
Sql> show parameter user_dump_dest;
Sql> Alter database backup controlfile to trace; — Default location
Sql> Alter database backup controlfile to trace as ‘\opt\control01.txt’; — non default location
(Note the system time and open a file from udump location)
$cat filename
Copy the script from ‘create control file …to 😉 and save it to a notepad.
Recovery from trace file:
Sql> startup nomount;
Sql> create controlfile…;
Sql> alter database open…;
Binary file method:
Sql> Alter database backup controlfile to trace as ‘\opt\control01.ctl’;
Sql> create pfile from spfile;
Note: Whenever we backup control file using binary mode backup pfile also.
Scenario: We have a backup of control file on Sunday and Wednesday it got corrupted.
Recovery
Replace the pfile control file location on pfile.
$vi /opt/inittest.ora
SQL> Startup pfile =’ /opt/inittest.ora’ nomount
SQL> Alter database mount;
- SQL> Recover database using backup controlfile
- Yes
The Recover DB will take a all missing info and update it to the old control files.
SQL> Alter database open resetlogs; – We always use, this for binary control file recovery
SQL> Create spfile from pfile= ’ /opt/inittest.ora’;
Best Practice: Trace file backup method, since the binary abosolute the backups.
Witout Backup and recovery
We have condition for this.
- Database should be in archive log mode
- We can only recover non-system tablespaces
We can recover without data loss.
Scenario
A tablespace corrupted and size is 2GB.
Sql> Alter database datafile ‘/opt/oracle/Muthu/tools01.dbf’ offline drop;
Sql> shutdown immediate
Copy the corrupted file from original to somewhere. So that we can use and point the same file as new file with data.
$ cd /opt/oracle/Muthu
$ cp tools01.dbf /data/oracle/oradata
Sql> startup mount
Sql> alter database create datafile ‘/opt/oracle/Muthu/tools.dbf’ as ‘/data/oracle/oradata/tools.dbf’;
It will create a same file size as a old file.
Sql> alter database datafile ‘/data/oracle/oradata/tools.dbf’ online;
It will go and get the data from all archive log files.
- Sql> recover database;
- auto – Oracel will take a data from archive logs.
Sql> alter database open resetlogs – optional;
Sql> alter database open;
Note: We need to do this in online state, Since, the data is getting from archive logs.
