In a recent email discussion thread on ORACLE-L, I outlined methods
of "recovery" from loss of control files(s) and online redo logs IF
the last shutdown had been a Normal Shutdown.
(eg if ControlFiles and/or RedoLog files have been deleted after the
shutdown --- such a strategy could also be used where the database has
been cloned by copying only the datafiles from cold backup (shutdown normal/immediate).
13 comments:
This is good stuff. However, why are DBA's not using RMAN for backup and recovery as it sure makes it a lot easier?
There are a lot of databases out there not yet using RMAN. I had been using Export in V5 and V6 and Scripted Hot Backups since 7.0. EBU in 7.3 was incomprehensible. RMAN became usable in 8.1 and I have begun using RMAN since 9.2
If you have a scripted hot backup you DO KNOW that you must backup the controlfile. With RMAN, many new DBAs may not know about how controlfile backups and being able to recreate the controlfile is useful.
That post to oracle-l was great; thanks for putting it together!
Thanks for the appreciation. One of these days, I will also cover what I specified as an "exercise" : How I can "roll-forward" {obviously, using the RECOVER command}
from Monday's (cold) backup to Wednesday's last available ArchiveLog.
That is something that you might do easier with the SQL command line than with RMAN.
Hemant
See http://hemantoracledba.blogspot.com/2007/05/rollforward-from-cold-backup.html for my posting on doing a RollForward from a Cold Backup.
The idiocy is that these kinds of backup and restore isn't in the documentation anymore. Every thing links in 10g to RMAN...
Yes, that's unfortunate.
I didn't like EBU and never used it.
I didn't trust RMAN and only began to use it hesitatingly in 8.1.7.
Unfortunately, new DBAs who brandish their OCP certificates know only RMAN. The SQL commands are still the backbone. It is like the old command-line v GUI argument. GUI is fine but to know command-line is better.
Dear Hemant,
This is just regarding your post on freelist.
http://www.freelists.org/archives/oracle-l/05-2007/msg00440.html
The case you told
"One more test :. I do not have OnlineRedo logs. Can I
do without RESETLOGS ? Do I need to issue RECOVER commands ? "
can be done in simple manner without any recovery command or create control file command...(with respect to SHUTDOWN IMMEDIATE) like..
[pre]
===================
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORANT\ORADATA\TESTDB\REDO03.LOG'
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 125683740 bytes
Fixed Size 75804 bytes
Variable Size 35123200 bytes
Database Buffers 90406912 bytes
Redo Buffers 77824 bytes
Database mounted.
SQL> alter database clear logfile 'D:\ORANT\ORADATA\TESTDB\REDO03.LOG';
Database altered.
SQL> alter database open;
Database altered.
SQL>
=====================
[/pre]
of course the thread is related to various recoveries scenarios.
i just thought,i should share this option with you.
Nitin
You didn't remove *ALL* the Online Redo Logs in your "test". Your test would work if the Redo Log that you deleted was NOT the last ACTIVE Redo Log.
If the deleted file was the last ACTIVE Redo Log, you can't use the CLEAR LOGFILE.
Also, note that I was testing for the case where "I do not have Online Redo Logs" -- meaning that *none* of the Online Redo Logs are available. In such a case you can't use the CLEAR LOGFILE.
See :
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1291652 bytes
Variable Size 339741308 bytes
Database Buffers 192937984 bytes
Redo Buffers 2899968 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\OR10G2DB\REDO01.DBF'
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1291652 bytes
Variable Size 339741308 bytes
Database Buffers 192937984 bytes
Redo Buffers 2899968 bytes
Database mounted.
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\OR10G2DB\REDO01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> REM Remove only REDO03.DBF (Group 3)
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1291652 bytes
Variable Size 339741308 bytes
Database Buffers 192937984 bytes
Redo Buffers 2899968 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'C:\OR10G2DB\REDO03.DBF'
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
Hi Hemant,
Excellent.
Thanks for the PRECISE reply.
Yes. i completely forgot about the REDOs STATUS.
Just one more doubt...
============
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\OR10G2DB\REDO01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
==============================
Here, how oracle do recovery without any ONLINE REDOs?
If shut Immediate/Normal/Transactional has done then Oracle checks only Checkpoint SCN of CONTROLFILE & DATAFILES and recreate REDO Files? Does it do INSTANCE + MEDIA recovery?
In a Bewilder situation. ;-)
Regards!
Nitin Joshi
You'd have to "walk the stack trace" to understand this.
In my test I had deleted all the Online Redo Logs. Therefore, to OPEN the database, I must use an OPEN RESETLOGS.
However, a pre-requisite of the OPEN RESETLOGS command is that this command can be issued only after an Incomplete Recovery. Go ahead and try doing an OPEN RESETLOGS on a normal/immedaite shutdown database and see the error.
OK, now that the error indicates that I must have done an Incomplete Recovery, I use the "RECOVER DATABASE UNTIL CANCEL" command. The "UNTIL CANCEL" signals an Incomplete Recovery.
However, the "RECOVER DATABASE" portion of the command identifies (from the datafile headers) that the database *was* shutdown properly and so no reovery is required. I am actually "simulating" an Incomplete Recovery.
This then sets the status to Incomplete Recovery.
And this now allows me to OPEN RESETLOGS !
See http://www.freelists.org/archives/oracle-l/07-2008/msg00120.html
Hi Hemant,
Perfect.
The key here is,as you said
" a pre-requisite of the OPEN RESETLOGS command is that this command can be issued only after an Incomplete Recovery. "
yes, its SIMULATION of incomplete recovery.
Thanks for the detail explanation.
Regards!
Nitin Joshi.
Post a Comment