A recent forums thread was about a Datafile that had been taken OFFLINE by the DBA and was in RECOVER mode.
When a DBA sees a datafile in 'RECOVER' mode (in DBA_DATAFILES) he shouldn't jump to the conclusion that he needs to RESTORE *and* RECOVER the datafile -- particularly if it does exist !
A RESTORE is required only if the file doesn't exist on disk (accessible to the database) OR is corrupted.
In "normal" circumstances (i.e. when a RESTORE has been issued), a RECOVER needs to roll-forward through all the ArchiveLogs that have been generated since the point in time of the datafile -- i.e. the backup of that datafile.
However, if the Datafile was taken OFFLINE, although Oracle marks it as requiring RECOVERy (as is seeen in DBA_DATA_FILES.STATUS), it doesn't really need all the ArchiveLogs. It only needs those ArchiveLogs that captured the Checkpoint of the Datafile and it's being taken OFFLINE. Subsequent ArchiveLogs (no matter how many they were) are not required. Thus, if the file was taken OFFLINE four days ago, as the DBA, I need only the ArchiveLogs that captured the last set of transactions not checkpointed into the Datafile and the ArchiveLog that captured the issuance of the ALTER DATABASE DATAFILE filename OFFLINE command. I do NOT need 4 days of ArchiveLogs.
Here, I first present one case where I do not have the ArchiveLog that captured the DATAFILE OFFLINE command. As is evident, datafile /usr/tmp/test_offline cannot be RECOVERed and brought ONLINE :
I needed ArchiveLog 2 to be able to issue the RECOVER command. However, as I had (seemingly inadvertently or because it is very old file) removed that ArchiveLog from disk, I cannot RECOVER the datafile. Note, however, that if I did have a Tape backup of ArchiveLogs 2 and 3 I would have been able to RECOVER that datfile and then bring it ONLINE (without requiring Sequences 4 and 5).
In this next scenario, I delete only the subsequent ArchiveLogs after the first one after the ALTER DATABASE DATAFILE filename OFFLINE command. (We can assume that I either preserved the required ArchiveLogs or restored them from backup).
Thus, the RECOVER command for /usr/tmp/t_o_2.dbf required only ArchiveLogs 6 and 7. I did NOT need ArchiveLogs 8, 9 and 10 even though they have been generated since after the particular Datafile was taken OFFLINE.
Therefore, although ArchiveLogs 8, 9 and 10 do capture transactions in *other* Datafiles (and, therefore, would be required if I were to RESTORE and/or RCOVER the other Datafiles), I do not need them for this particular Datafile that was "properly and normally" taken OFFLINE.
As further evidence, see these messages from the alert.log file :
Thus, Oracle needed only ArchiveLogs 6 and 7 even as I had deleted 8 and 9 from disk (and have no backups of 8 and 9).
When a DBA sees a datafile in 'RECOVER' mode (in DBA_DATAFILES) he shouldn't jump to the conclusion that he needs to RESTORE *and* RECOVER the datafile -- particularly if it does exist !
A RESTORE is required only if the file doesn't exist on disk (accessible to the database) OR is corrupted.
In "normal" circumstances (i.e. when a RESTORE has been issued), a RECOVER needs to roll-forward through all the ArchiveLogs that have been generated since the point in time of the datafile -- i.e. the backup of that datafile.
However, if the Datafile was taken OFFLINE, although Oracle marks it as requiring RECOVERy (as is seeen in DBA_DATA_FILES.STATUS), it doesn't really need all the ArchiveLogs. It only needs those ArchiveLogs that captured the Checkpoint of the Datafile and it's being taken OFFLINE. Subsequent ArchiveLogs (no matter how many they were) are not required. Thus, if the file was taken OFFLINE four days ago, as the DBA, I need only the ArchiveLogs that captured the last set of transactions not checkpointed into the Datafile and the ArchiveLog that captured the issuance of the ALTER DATABASE DATAFILE filename OFFLINE command. I do NOT need 4 days of ArchiveLogs.
Here, I first present one case where I do not have the ArchiveLog that captured the DATAFILE OFFLINE command. As is evident, datafile /usr/tmp/test_offline cannot be RECOVERed and brought ONLINE :
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> create tablespace test_offline datafile '/usr/tmp/test_offline' size 10M ;
Tablespace created.
SQL> alter system switch logfile;
System altered.
SQL> alter database datafile '/usr/tmp/test_offline' offline;
Database altered.
SQL> select * from dba_data_files where file_name like '/usr/tmp/test%';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/usr/tmp/test_offline
6 TEST_OFFLINE AVAILABLE
6
RECOVER
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/*
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 0
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 8
-rw-r----- 1 ora10204 dba 4608 Apr 22 22:33 1_5_684196024.dbf
SQL> alter database datafile '/usr/tmp/test_offline' online;
alter database datafile '/usr/tmp/test_offline' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/test_offline'
SQL> recover datafile 6;
ORA-00279: change 649615 generated at 04/22/2009 22:29:06 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_2_684196024.dbf
ORA-00280: change 649615 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database datafile '/usr/tmp/test_offline' online;
alter database datafile '/usr/tmp/test_offline' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/test_offline'
SQL> drop tablespace test_offline including contents and datafiles;
Tablespace dropped.
SQL>
I needed ArchiveLog 2 to be able to issue the RECOVER command. However, as I had (seemingly inadvertently or because it is very old file) removed that ArchiveLog from disk, I cannot RECOVER the datafile. Note, however, that if I did have a Tape backup of ArchiveLogs 2 and 3 I would have been able to RECOVER that datfile and then bring it ONLINE (without requiring Sequences 4 and 5).
In this next scenario, I delete only the subsequent ArchiveLogs after the first one after the ALTER DATABASE DATAFILE filename OFFLINE command. (We can assume that I either preserved the required ArchiveLogs or restored them from backup).
SQL> create tablespace t_o_2 datafile '/usr/tmp/t_o_2.dbf' size 10M;
Tablespace created.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> alter system switch logfile;
System altered.
SQL> alter database datafile '/usr/tmp/t_o_2.dbf' offline;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 44
-rw-r----- 1 ora10204 dba 4608 Apr 22 22:33 1_5_684196024.dbf
-rw-r----- 1 ora10204 dba 28160 Apr 22 22:36 1_6_684196024.dbf
-rw-r----- 1 ora10204 dba 6656 Apr 22 22:36 1_7_684196024.dbf
SQL> create table t_2 (col_1 number);
Table created.
SQL> insert into t_2 select object_id from dba_objects;
50601 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 900
-rw-r----- 1 ora10204 dba 4608 Apr 22 22:33 1_5_684196024.dbf
-rw-r----- 1 ora10204 dba 28160 Apr 22 22:36 1_6_684196024.dbf
-rw-r----- 1 ora10204 dba 6656 Apr 22 22:36 1_7_684196024.dbf
-rw-r----- 1 ora10204 dba 866304 Apr 22 22:38 1_8_684196024.dbf
-rw-r----- 1 ora10204 dba 1536 Apr 22 22:38 1_9_684196024.dbf
SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/1_8_*.dbf
SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/1_9_*.dbf
SQL> alter database datafile '/usr/tmp/t_o_2.dbf' online;
alter database datafile '/usr/tmp/t_o_2.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/t_o_2.dbf'
SQL> recover datafile 6;
ORA-00279: change 649846 generated at 04/22/2009 22:36:10 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf
ORA-00280: change 649846 for thread 1 is in sequence #6
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 649874 generated at 04/22/2009 22:36:28 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_7_684196024.dbf
ORA-00280: change 649874 for thread 1 is in sequence #7
ORA-00278: log file '/oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf' no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> select * from dba_data_files where file_name like '/usr/tmp/t_o%';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/usr/tmp/t_o_2.dbf
6 T_O_2 AVAILABLE
6
OFFLINE
SQL> alter database datafile '/usr/tmp/t_o_2.dbf' online;
Database altered.
SQL>
Thus, the RECOVER command for /usr/tmp/t_o_2.dbf required only ArchiveLogs 6 and 7. I did NOT need ArchiveLogs 8, 9 and 10 even though they have been generated since after the particular Datafile was taken OFFLINE.
Therefore, although ArchiveLogs 8, 9 and 10 do capture transactions in *other* Datafiles (and, therefore, would be required if I were to RESTORE and/or RCOVER the other Datafiles), I do not need them for this particular Datafile that was "properly and normally" taken OFFLINE.
As further evidence, see these messages from the alert.log file :
Wed Apr 22 22:36:42 2009
alter database datafile '/usr/tmp/t_o_2.dbf' offline
Wed Apr 22 22:36:42 2009
Completed: alter database datafile '/usr/tmp/t_o_2.dbf' offline
Wed Apr 22 22:36:47 2009
Thread 1 cannot allocate new log, sequence 8
Checkpoint not complete
Current log# 1 seq# 7 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Wed Apr 22 22:36:49 2009
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0: /oracle_fs/Databases/ORT24FS/redo02.dbf
Wed Apr 22 22:38:15 2009
Thread 1 cannot allocate new log, sequence 9
Checkpoint not complete
Current log# 2 seq# 8 mem# 0: /oracle_fs/Databases/ORT24FS/redo02.dbf
Wed Apr 22 22:38:16 2009
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /oracle_fs/Databases/ORT24FS/redo03.dbf
Thread 1 cannot allocate new log, sequence 10
Checkpoint not complete
Current log# 3 seq# 9 mem# 0: /oracle_fs/Databases/ORT24FS/redo03.dbf
Wed Apr 22 22:38:19 2009
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log# 1 seq# 10 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Wed Apr 22 22:39:19 2009
alter database datafile '/usr/tmp/t_o_2.dbf' online
Wed Apr 22 22:39:19 2009
ORA-1113 signalled during: alter database datafile '/usr/tmp/t_o_2.dbf' online...
Wed Apr 22 22:39:25 2009
ALTER DATABASE RECOVER datafile 6
Media Recovery Start
parallel recovery started with 2 processes
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 6 ...
Wed Apr 22 22:39:28 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Apr 22 22:39:28 2009
Media Recovery Log /oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Apr 22 22:39:31 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Apr 22 22:39:31 2009
Media Recovery Log /oracle_fs/ArchiveLogs/ORT24FS/1_7_684196024.dbf
Wed Apr 22 22:39:31 2009
Media Recovery Complete (ORT24FS)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Apr 22 22:40:15 2009
alter database datafile '/usr/tmp/t_o_2.dbf' online
Wed Apr 22 22:40:15 2009
Starting control autobackup
Control autobackup written to DISK device
handle '/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/c-4163910544-20090422-03'
Completed: alter database datafile '/usr/tmp/t_o_2.dbf' online
Thus, Oracle needed only ArchiveLogs 6 and 7 even as I had deleted 8 and 9 from disk (and have no backups of 8 and 9).
No comments:
Post a Comment