.JPG)
Backup Strategies
-------------------
Have you ever been in a state of confusion with the various backing up strategies like cold/hot or archive/no archive ?
At-least that was the case with me always . Very lately i found out that the main distinction is not between hot or cold but it is between archive mode and non archive mode .
You can check the mode in which your database is running :-
SQL> select log_mode from v$database; LOG_MODE ------------ NOARCHIVELOG SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Current log sequence 1
My database here is running in non-archive mode. And hence , we will deal with non-archive mode first.
Now coming to hot/cold backup ; hot backup is when you take the backup when your database is online and cold is when your database is offline i.e. properly shutdown . Hot backup is not possible in case of nonarchive mode .A proper shutdown of the database is required for nonarchive backup , even shutdown abort brings your system in an inconsistent state , hence shouldn't be used for cold backup.
NO-ARCHIVE MODE
What to Backup ?
-------------------
If you are not sure about the location of the datafiles and the control files , check the same by using this :-
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /scratch/aime1/app/aime1/oradata/orcl/system01.dbf /scratch/aime1/app/aime1/oradata/orcl/sysaux01.dbf /scratch/aime1/app/aime1/oradata/orcl/undotbs01.dbf /scratch/aime1/app/aime1/oradata/orcl/users01.dbf /scratch/aime1/app/aime1/oradata/orcl/example01.dbf SQL> show parameter control_files; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /scratch/aime1/app/aime1/orada ta/orcl/control01.ctl, /scratc h/aime1/app/aime1/flash_recove ry_area/orcl/control02.ctl
Redo log files should not be backed up in no-archive mode since they are continuously over-written without archiving.
How to take Backup ?
---------------------
Shutdown the database properly forcing a checkpoint in the files.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
Go to the terminal and make a backup of all the files in another folder :-
bash-3.00$ pwd
/scratch/aime1/app/aime1/oradata/orcl
bash-3.00$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
bash-3.00$ ls *.dbf
example01.dbf system01.dbf undotbs01.dbf
sysaux01.dbf temp01.dbf users01.dbf
bash-3.00$ cp *.dbf ~/app/aime1/Backup/
bash-3.00$ cd control01.ctl ~/app/aime1/Backup/
bash: cd: control01.ctl: Not a directory
bash-3.00$ cp control01.ctl ~/app/aime1/Backup/
bash-3.00$ cd ~/app/aime1/Backup/
bash-3.00$ ls
control01.ctl sysaux01.dbf temp01.dbf users01.dbf
example01.dbf system01.dbf undotbs01.dbf
Why to recover?
------------------------------
Now go back to the sqlplus and start the database again
To check the default tablespace for a user , use
SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where username ='SCOTT'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ SCOTT USERS SQL> create table test(a number); Table created SQL> insert into test values(1); 1 row created.
Hence the dbf file for the users tablespace i.e. USERS01.dbf is modified . Now perform a shutodwn again.
How to Recover?
----------------------
If we assume now that the dbf file for the users tablespace is lost/ corrupted because of some problem , the question is how do we recover it now with the available backup that we have.
There are different possibilities and going stepwise :-
STEP 1:- Copy the dbf file from the backup location to the original location.
First take a backup of the original dbf file (Best practice) and then copy the backup dbf file.
bash-3.00$ pwd
/scratch/aime1/app/aime1/Backup
bash-3.00$ cd ../oradata/orcl/
bash-3.00$ cp users01.dbf users01.dbf.bak
bash-3.00$ cp ~/app/aime1/Backup/users01.dbf users01.dbf
Now try to start the database.
SQL> startup; ORACLE instance started. Total System Global Area 2042241024 bytes Fixed Size 1337548 bytes Variable Size 1056966452 bytes Database Buffers 973078528 bytes Redo Buffers 10858496 bytes Database mounted. ORA-01113: file 4 needs media recovery ORA-01110: data file 4: '/scratch/aime1/app/aime1/oradata/orcl/users01.dbf'
It throws an error saying "Media Recovery is needed" . The database state at this point is "MOUNTED" but it could not be started.
Now if check the checkpoint number for the datafiles and the control files :-
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER; FILE# CHECKPOIN CHECKPOINT_CHANGE# ---------- --------- ------------------ 1 01-SEP-10 1419740 2 01-SEP-10 1419740 3 01-SEP-10 1419740 4 01-SEP-10 1416309 5 01-SEP-10 1419740 SQL> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 1419740
The datafile #4 (Users01.dbf) is behind in SCN number than the other files and the control files.
If we try to recover database by running this ,
SQL> recover database Media recovery complete.
It says that " Media Recovery is completed" .
Checking the alert_SID.log file :-
Contents:-
ALTER DATABASE RECOVER database
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
Wed Sep 01 02:30:24 2010
Recovery of Online Redo Log: Thread 1 Group 1 Seq 40 Reading mem 0
Mem# 0: /scratch/aime1/app/aime1/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 41 Reading mem 0
Mem# 0: /scratch/aime1/app/aime1/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER database
It shows that redo log files were used for the recovery of the database . That was possible because there wasnt much log switch happened after the backup , hence the SCN number was present in the log files for the recovery .
SQL> alter database open; Database altered. SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER; FILE# CHECKPOIN CHECKPOINT_CHANGE# ---------- --------- ------------------ 1 01-SEP-10 1419743 2 01-SEP-10 1419743 3 01-SEP-10 1419743 4 01-SEP-10 1419743 5 01-SEP-10 1419743 SQL> select * from SCOTT.test; A ---------- 1
Suppose that there were many log switches after we took of cold backup of all the files.
SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
Now in this case , if we try to run "Recover Database" then
QL> recover database;
ORA-00279: change 1416309 generated at 09/01/2010 01:48:54 needed for thread 1
ORA-00289: suggestion :
/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1_
40_%u_.arc
ORA-00280: change 1416309 for thread 1 is in sequence #40
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1
_40_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Now media recovery is not possible in this case .
It asks us about the location of the archived log files but our database is running in noarchivelog mode , hence we dnt have any archived logs. Hence, it gives an error that media recovery is not possible.
In this case, goto STEP 2.
STEP 2:- Using Backup Control File
Copy the backup control file to the original location .
bash-3.00$ cp control01.ctl control01.ctl.bak
bash-3.00$ cp ~/app/aime1/Backup/control01.ctl control01.ctl
Now try to start the database
SQL> startup; ORACLE instance started. Total System Global Area 2042241024 bytes Fixed Size 1337548 bytes Variable Size 1056966452 bytes Database Buffers 973078528 bytes Redo Buffers 10858496 bytes ORA-00214: control file '/scratch/aime1/app/aime1/flash_recovery_area/orcl/control02.ctl' version 3557 inconsistent with file '/scratch/aime1/app/aime1/oradata/orcl/control01.ctl' version 3503
It gives an error of the version mismatch between both the control files . Hence we need to copy the backup control file to other location as well.
bash-3.00$ cp ~/app/aime1/Backup/control01.ctl control01.ctl
bash-3.00$ cp /scratch/aime1/app/aime1/oradata/orcl/control01.ctl /scratch/aime1/app/aime1/flash_recovery_area/orcl/control02.ctl
Again starting the database :
SQL> startup; ORACLE instance started. Total System Global Area 2042241024 bytes Fixed Size 1337548 bytes Variable Size 1056966452 bytes Database Buffers 973078528 bytes Redo Buffers 10858496 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/scratch/aime1/app/aime1/oradata/orcl/system01.dbf' ORA-01207: file is more recent than control file - old control file SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER; FILE# CHECKPOIN CHECKPOINT_CHANGE# ---------- --------- ------------------ 1 01-SEP-10 1423110 2 01-SEP-10 1423110 3 01-SEP-10 1423110 4 01-SEP-10 1416309 5 01-SEP-10 1423110 SQL> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 1416309
Checkpoint number in the control file is less than those in other dbf files .
Now if we try to recover saying "using backup controlfile" , it gives an error "RECOVER succeeded but OPEN RESETLOGS would get error below" :-
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1416309 generated at 09/01/2010 01:48:54 needed for thread 1
ORA-00289: suggestion :
/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1_
40_%u_.arc
ORA-00280: change 1416309 for thread 1 is in sequence #40
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/scratch/aime1/app/aime1/flash_recovery_area/ORCL/archivelog/2010_09_01/o1_mf_1
_40_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/scratch/aime1/app/aime1/oradata/orcl/system01.dbf'
Trying to open the database :-
SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open noresetlogs; alter database open noresetlogs * ERROR at line 1: ORA-01588: must use RESETLOGS option for database open SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/scratch/aime1/app/aime1/oradata/orcl/system01.dbf'
Hence its not possible to do this without the redo log files .
In this case , go to STEP 3
STEP 3 : Copy all the files
Copy all the backup dbf files and ctl file to the original location
bash-3.00$ cp ~/app/aime1/Backup/*.dbf .
Now try to open the database with resetlogs :-
SQL> alter database open resetlogs; Database altered.
ALert_SID. log file contents :-
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 1416309
Resetting resetlogs activation ID 1255868088 (0x4adb06b8)
Wed Sep 01 03:27:09 2010
Now check the checkpoint number :-
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER; FILE# CHECKPOIN CHECKPOINT_CHANGE# ---------- --------- ------------------ 1 01-SEP-10 1416313 2 01-SEP-10 1416313 3 01-SEP-10 1416313 4 01-SEP-10 1416313 5 01-SEP-10 1416313 SQL> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 1416313
All the files have reached to the same checkpoint number .
If we try to access the tets table now , it gives an error that it doesnt exist .
SQL> select * from Scott.test 2 ; select * from Scott.test * ERROR at line 1: ORA-00942: table or view does not exist
This error comes because our database is restored to the time when last backup was taken and that time test table wasn't created.
CONCLUSION :-
------------------
From the above scenario , we can conclude that the media recovery is not possible in case of no-archive log, only instance recovery can be done. That also, our instance is recovered back to the time when last backup was taken , and all the changes made to the database after that are lost. Archiving redo logs i.e. archive mode is solution to this.
Very well explained. I simply loved this post. I will definitely share this post with all my friends too. Thanks and Keep up the good work.
ReplyDelete