(Another complication in that scenario was that the CURRENT Redo Log, while still available as an Online Redo Log, hadn't yet been archived. So I queried V$LOG and V$LOGFILE -- in the MOUNT state -- to identify the Redo Log and applied it in the RECOVERy).
Thus, the steps and errors and resolutions were :
1. Cold Backup of database.
2. Startup and CREATE TABLESPACE.
3. Shutdown.
4. Restore only controlfile.
5. Startup OPEN fails on finding that datafiles are newer than the controlfile. (Oracle always expects the controlfile to be "current")
6. RECOVER fails because the database files are "newer" than the last log file -- Oracle expects Log Sequence#44 to match the datafile headers.
7. I identify that #44 is an Online Redo Log (ie was the CURRENT one, not yet Archived)
8. Applying #44 also fails because Oracle finds that there is a mismatch in the physical structure as recorded by the controlfile and the database -- one new datafile is present.
9. I use ALTER DATABASE CREATE DATAFILE to "add" the new datafile to the controlfile
10. RECOVER succeeds this time.
11. I must OPEN RESETLOGS as I've used a 'backup controlfile'
ora10204>sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 18 22:31:24 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ora10204>ls
DUPDB ORT21FS ORT24FS ORT24FS_coldbackup
ora10204>pwd
/oracle_fs/Databases
ora10204>cp -rp ORT24FS/* ORT24FS_coldbackup/
ora10204>
ora10204>sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 18 22:35:27 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 192938952 bytes
Database Buffers 679477248 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL> create tablespace test_a_tbs datafile '/oracle_fs/Databases/ORT24FS/test_a_tbs.dbf' size 100M;
Tablespace created.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ora10204>
ora10204>pwd
/oracle_fs/Databases
ora10204>cp ORT24FS_coldbackup/*ctl ORT24FS/
ora10204>sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 18 22:38:20 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 192938952 bytes
Database Buffers 679477248 bytes
Redo Buffers 6299648 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SQL> recover database using backup controlfile;
ORA-00279: change 936870 generated at 05/18/2009 22:28:28 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_44_685060711.dbf
ORA-00280: change 936870 for thread 1 is in sequence #44
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'
SQL> col member format a45
SQL> select l.sequence#, f.group#, f.member, l.status
2 from v$logfile f, v$log l
3 where f.group#=l.group#
4 order by f.group#, f.member
5 /
Seq# Grp MEMBER Status
------- ---- --------------------------------------------- ---------
43 1 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE
44 2 /oracle_fs/Databases/ORT24FS/redo02.dbf CURRENT
42 3 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE
SQL>
SQL> recover database using backup controlfile;
ORA-00279: change 936870 generated at 05/18/2009 22:28:28 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_44_685060711.dbf
ORA-00280: change 936870 for thread 1 is in sequence #44
Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/Databases/ORT24FS/redo02.dbf
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oracle_fs/Databases/ORT24FS/test_a_tbs.dbf'
ORA-01112: media recovery not started
SQL> alter database create datafile 6 as '/oracle_fs/Databases/ORT24FS/test_a_tbs.dbf';
Database altered.
SQL>
SQL> recover database using backup controlfile;
ORA-00279: change 937136 generated at 05/18/2009 22:36:23 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_44_685060711.dbf
ORA-00280: change 937136 for thread 1 is in sequence #44
Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/Databases/ORT24FS/redo02.dbf
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
The ALTER DATABASE ADD DATAFILE is useful. This can be used to synchronise the database back to the controlfile -- which typically happens in Standby Database scenarios where the Primary has added a new Datafile but it isn't available on the Standby (the standby controlfile isn't aware of it !).
.
.
.
9 comments:
Hemant, I recently came across your blog and I really liked it. I have a question about this recovery scenario though. When you add the same datafile /oracle_fs/Databases/ORT24FS/test_a_tbs.dbf' again to make controlfile aware about it, don't we have to use "reuse" clause at the end of add datafile command since the same file exists at O/S level.
Thanks!!!
See the datafile spec at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses004.htm#g1057086
"If you specify filename only, or with the REUSE clause but without the SIZE clause, then the file must already exist."
Hemant,
"If you specify filename only, or with the REUSE clause but without the SIZE clause, then the file must already exist."
ahhh, that makes sense. I was reading through your output and didn't quite understand how oracle was supposed to know how big to make the datafile that you were creating.
BTW, Nice blog...I'm working towards my OCP and am constantly finding my way back to your blog and OTN posts as I make my way through the material.
I was running through this exercise again and stumbled upon an interesting twist when FLASHBACK is enabled.
Is it because flashback logs aren't in sync with the controlfile that was restored from coldbackup?
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 306187140 bytes
Database Buffers 297795584 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> create tablespace TEST123 datafile 'D:\oracle\ora1020\oradata\ocp10g\TEST123.dbf' size 10M;
Tablespace created.
SQL> create table test tablespace TEST123 as select * from dba_users;
Table created.
SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 306187140 bytes
Database Buffers 297795584 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
SQL> alter database flashback off;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'D:\ORACLE\ORA1020\ORADATA\OCP10G\SYSTEM01.DBF'
ORA-01207: file is more recent than control file - old control file
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-38727: FLASHBACK DATABASE requires a current control file.
SQL> alter database flashback off;
Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 2733199 generated at 09/02/2009 14:26:45 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\ORA1020\FLASH_RECOVERY_AREA\OCP10G\ARCHIVELOGS\ARCHIVE2\ARCH_D2CEE939_
1_696522405_1.LOG
ORA-00280: change 2733199 for thread 1 is in sequence #1
Specify log: {[RET]=suggested | filename | AUTO | CANCEL}
D:\ORACLE\ORA1020\ORADATA\OCP10G\REDO02.LOG
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: 'D:\ORACLE\ORA1020\ORADATA\OCP10G\TEST123.DBF'
ORA-01112: media recovery not started
SQL> alter database create datafile 6 as 'D:\ORACLE\ORA1020\ORADATA\OCP10G\TEST123.DBF';
Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 2733456 generated at 09/02/2009 16:10:45 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\ORA1020\FLASH_RECOVERY_AREA\OCP10G\ARCHIVELOGS\ARCHIVE2\ARCH_D2CEE939_
1_696522405_1.LOG
ORA-00280: change 2733456 for thread 1 is in sequence #1
Specify log: {[RET]=suggested | filename | AUTO | CANCEL}
D:\ORACLE\ORA1020\ORADATA\OCP10G\REDO02.LOG
Log applied.
Media recovery complete.
SQL> alter database flashback on;
Database altered.
SQL> alter database open resetlogs;
Database altered.
SQL>
Did you get the "ORA-38760: This database instance failed to turn on flashback database" on restoring a controlfile backup ? (the output you posted didn't show if a controlfile backup had been taken and/or restored).
If it is a controlfile that "older" than the flashback logs, I would agree that Oracle should complain.
I'll try to test this scenario in a few days.
Hemant
Optimus Prime,
I need to know what it is that you did between the SHUTDOWN and the STARTUP.
Hemant
Hi Hemant;
Thanks for answering my previous questions.
I have a couple of questions, I appreciate if you also clear my doubts in this issue.
If I take the datafile offline, I will need recovery until stop scn of the datafile.
The reason behind this, checkpoint is not issued.
I know the duty of checkpoint (write dirty blocks to disk,etc) but what is the relation between checkpoint and recovery?
1-)
Since dirty blocks remain in buffer cache, they can be written when I bring up the datafile.
or
Suppose there is no dirty blocks of that datafile and I take it offline.
Again why do I need recovery?
2-)
Suppose,I forced checkpoint and perfom offline immediate immediately? Why do I again need recovery?
3-)
Suppose I have a dirty block in buffer cache and I perform offline immediate, does this dirty
block remain in
buffer cache until I bring up the datafile?
Hemant;
I reallt appreciate if you answe my above question.
We havent seen you for a while in OTN forums :)
Post a Comment