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 edo 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.
0 comments:
Post a Comment