Oracle Database Manual Backup and Recovery.

Here is the most important task for a Database learner that how to Backup and Recovery .Hope you find your task.


COLD BACKUP AND RECOVERY SCENARIOS.

Cold backup of Database in Noarchivelog Mode.

1) analyze the space requirements.


SQL> select sum(sum_bytes)/1024/11024 m_bytes
2 from (
3 select sum(bytes) sum_bytes from v$datafile
4 union
5 select sum(bytes) sum_bytes from v$tempfile
6 union
7 select (sum(bytes) * members) sum_bytes from v$log
8 group by members);

2) Determine location and names of files to be backed up.
SQL> select name from v$datafile
union
select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile;



3) shutdown database

SQL> shutdown immediate;

4) Do OS level backup

Note: I have also backed up the Online redo Log files.


cp -ivr /u01/app/oracle/oradata/orcl/* /u02/app/oracle/backup/


SQL> startup mount

Do binary backup of controlfile.

SQL> alter database backup controlfile to '/u02/app/oracle
/controlbackup/control.bkp';

Database altered.

Or trace file backup

SQL> alter database backup controlfile to trace as '/u02/app
/oracle/controlback/controltrace';

SQL> alter database open;



Restoring from cold backup in Noarchivelog.

a) when Online redo logs backup is there.


Shutdown your database if its open.

Remove the old files

restore files from backup

$ cp -ivr /u02/app/oracle/backup/* /u01/app/oracle/oradata/orcl/


startup up your database

SQL> startup


b) when Online redo logs backup is not there.


Shutdown your database if its open.


restore files from backup

$ cp -ivr /u02/app/oracle/backup/* /u01/app/oracle/oradata/orcl/


startup up your database

SQL> startup mount

SQL> recover database using backup controlfile until cancel;

when prompted

cancel

SQL> alter database open resetlogs;

This command will clear and recreate the online redo log files
and also will be a new incarnation of the database.

That means old archive logs if any cannot
be used for the purpose of recovery.

So its importatnt that you make a FRESH BACKUP of your database.


HOT BACKUP AND RECOVERY SCENARIOS.

BACKUP

1) Make sure that your database is in archivelog mode.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled


2) analyze space requirements.

SQL> select sum(sum_bytes)/1024/11024 m_bytes
from (
select sum(bytes) sum_bytes from v$datafile
union
select sum(bytes) sum_bytes from v$tempfile
union
select (sum(bytes) * members) sum_bytes from v$log
group by members);


3) Determine the files that need backup;

SQL> select name from v$datafile
union
select name from v$controlfile
union
select name from v$tempfile;


4) check max sequence from online redo logfiles.
SQL> select max(sequence#)
from v$log;

MAX(SEQUENCE#)
--------------
7

5) Put the database or tablespace in begin backup mode.

For database

SQL> alter database begin backup;

For Tablespace

SQL> alter tablespace users begin backup;


SQL> select file#,status from v$backup;

FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
5 ACTIVE


6) Copy all the datafiles with OS utility.

7) Take the database or tablespace out of backup mode.

For database

SQL> alter database end backup;

For tablespace

SQL> alter tablespace users end backup;


SQL> select file#,status from v$backup;

FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
5 NOT ACTIVE



8) archive current logfile and check latest max sequence
number.

SQL> alter system archive log current;

System altered.

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)
--------------
19


9) backup the controlfile

Binary backup

SQL> alter database backup controlfile to '/u01/app/oracle
/backup/control.bk';

Database altered.

Trace backup

SQL> alter database backup controlfile to trace as '/u01/app
/oracle/backup/trace';

Database altered.

10) backup all the archived logs generated during backup7
identifying them by log sequence number.





Recovery Scenarios when database is in archivelog with hot backup.


Case 1: Lost all the controlfiles.

shutdown the database if its open.

SQL> shutdown abort

Copy the controlfiles from backup.


SQL>startup mount

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 808116 generated at 01/23/2012 23:55:11
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_12_%u_.arc
ORA-00280: change 808116 for thread 1 is in sequence #12





Open another terminal and identify the online redo log file
for the sequence number.

SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------
IS_
---

1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 1 YES
INACTIVE 807834 23-JAN-12 807837 23-JAN-12

3 1 12 52428800 512 1 NO
CURRENT 808116 23-JAN-12 2.8147E+14

2 1 11 52428800 512 1 YES
ACTIVE 807837 23-JAN-12 808116 23-JAN-12


SQL>


apply the relevant path.

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo03.log
Log applied.
Media recovery complete.

In some cases querying v$log is not helpful as it does
not show the correct sequence number, In that case apply
the online logfiles one by one until media recovery is complete.

SQL> alter database open resetlogs;

Database altered.



CASE 2: Lost System Datafile

SQL> shutdown abort;
ORACLE instance shut down.


Copy the system datafile from backup.


SQL> startup mount

SQL> recover tablespace system;
ORA-00279: change 807565 generated at 01/24/2012 01:57:07
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_7kvk24yl_.arc
ORA-00280: change 807565 for thread 1 is in sequence #11


Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.
Media recovery complete.

SQL> alter database open;

Database altered.

CASE 3: Lost Non-System Datafiles.

Example: Users tablespace lost
due to media failure.


Take the tablespace offline.

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01191: file 4 is already offline - cannot do a normal offline
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'


SQL> alter tablespace users offline immediate;

Tablespace altered.

Copy apprpriate datafiles from backup for users tablespace.

SQL> select file_name from dba_data_files where
tablespace_name='USERS';

FILE_NAME
---------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf


SQL> recover tablespace users;

ORA-00279: change 807565 generated at 01/24/2012
01:57:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_7kvk24yl_.arc
ORA-00280: change 807565 for thread 1 is in sequence #11


Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.
Media recovery complete.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select file_name,status from dba_data_files
where tablespace_name='USERS';

FILE_NAME
------------------------------------------------------
STATUS
---------
/u01/app/oracle/oradata/orcl/users01.dbf
AVAILABLE

CASE 4: A added data file was lost after hot backup.

Example: Lost test tablespace


SQL> alter tablespace test offline;

Tablespace altered.

SQL> alter database create datafile '/u01/app/oracle
/oradata/orcl/test01.dbf';

Database altered.

SQL> alter database create datafile '/u01/app/oracle
/oradata/orcl/test02.dbf';

Database altered.

SQL> recover tablespace test;
Media recovery complete.
SQL> alter tablespace test online;

Tablespace altered.
SQL> select file_name,status from dba_data_files where
tablespace_name='TEST';

FILE_NAME
--------------------------------------------------------
STATUS
---------
/u01/app/oracle/oradata/orcl/test01.dbf
AVAILABLE

/u01/app/oracle/oradata/orcl/test02.dbf
AVAILABLE




CASE 5: Datafile lost and needs to be restored to
different location.

Example: lost my users tablespace.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> select file_name from dba_data_files where
tablespace_name='USERS';

FILE_NAME
-------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf


Copy appropriate data files from backup to new location.

Rename datafiles.

1 alter database rename file '/u01/app/oracle/oradata
/orcl/users01.dbf'
2 to
3* '/u01/app/oracle/oradata/users01.dbf'


SQL> recover tablespace users;
ORA-00279: change 807565 generated at 01/24/2012 01:57:07
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_7kvk24yl_.arc
ORA-00280: change 807565 for thread 1 is in sequence #11


Specify log: {=suggested | filename | AUTO | CANCEL}

auto


Log applied.
Media recovery complete.

SQL> alter tablespace users online;

Tablespace altered.


SQL> select file_name,status from dba_data_files where
tablespace_name = 'USERS';

FILE_NAME
------------------------------------------------------------
STATUS
---------
/u01/app/oracle/oradata/users01.dbf
AVAILABLE



CASE 6: Lost everything including datafiles and controlfiles
except for online redo logs.

Note: There was no backup of online redo log files.

Restore all the files from backup except for online redo log files.

SQL> startup mount
ORACLE instance started.

Total System Global Area 619360256 bytes
Fixed Size 1338280 bytes
Variable Size 398459992 bytes
Database Buffers 213909504 bytes
Redo Buffers 5652480 bytes
Database mounted.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 807805 generated at 01/24/2012 14:50:13
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_10_7kwyqqnb_.arc
ORA-00280: change 807805 for thread 1 is in sequence #10

identify the correct online redo log file.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 1 YES
ACTIVE 807803 24-JAN-12 808486 24-JAN-12

3 1 9 52428800 512 1 YES
INACTIVE 807800 24-JAN-12 807803 24-JAN-12

2 1 11 52428800 512 1 NO
CURRENT 808486 24-JAN-12 2.8147E+14



SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
---------------------------------------------------------------
IS_
---

1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO




Apply logfiles sequence number wise.

First give path for sequence 10 then 11.


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
ORA-00279: change 808486 generated at 01/24/2012
15:03:43 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_%u_.arc
ORA-00280: change 808486 for thread 1 is in sequence #11
ORA-00278: log file '/u01/app/oracle/oradata/orcl
/redo01.log' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

CASE 7: Lost a read only tablespace which was read only when backup
was taken.

Suppose I lost my read only users tablespace.

SQL> alter tablespace users offline;

Tablespace altered.

Restore from Backup.

SQL> alter tablespace users online;

Tablespace altered.

CASE 8: Creating new Control File After Losing All Current
and Backup Control Files.

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 619360256 bytes
Fixed Size 1338280 bytes
Variable Size 398459992 bytes
Database Buffers 213909504 bytes
Redo Buffers 5652480 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SET WE8MSWIN1252;


Control file created.

SQL> recover database;


Media recovery complete.


-- All logs need archiving and a log switch is needed.

SQL> alter system archive log all;

System altered.


SQL> alter database open;

Database altered.

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.