Hi Friends.I am writing here because I had seen this scenario where current redo log
has got corrupted .We all know that point in time recovery is the solution in this case but following points are to be noted
- Database size is almost 25 TB.
- When alter system checkpoint is not working.
- We are ready to loose some of the data but not willing to go for the outage that big for point in time recovery.
I heard that somebody did something with the control file and without actual restore he was able to go past the time in current log corruption.I did some testing on my test database .It might not be 100% correct so your suggestions are invited but it worked in my case.To simulate the case we will perform corruption in the current redo log.
SQL> select
GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS from v$log;
GROUP#
SEQUENCE# MEMBERS ARC STATUS
----------
---------- ---------- --- ----------------
1 24 1 YES INACTIVE
2 23 1 YES INACTIVE
3 25 1 NO
CURRENT
So 3 is the current redolog.
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
Here we want to corrupt the file /u01/app/oracle/oradata/orcl/redo03.log.Lets do the following.
[root@localhost ~]#
dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo03.log count=1 seek=1
bs=8192
0+0 records in
0+0 records out
0 bytes (0 B)
copied, 4.9343e-05 seconds, 0.0 kB/s
shutdown and startup the database
SQL> startup
ORACLE instance
started.
Total System Global
Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 545263184 bytes
Database
Buffers 297795584 bytes
Redo Buffers 5132288 bytes
Database mounted.
ORA-03113:
end-of-file on communication channel
Process ID: 3523
Session ID: 1 Serial
number: 5
select
GROUP#,substr(member,1,60) from v$logfile;
GROUP#
THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
----------
---------- ---------- ---------- --- ---------------- -------------
1 1 27 1 NO
CURRENT 1128975
3 1
25 1 NO
INACTIVE 1127602
2 1 26 1 NO
INACTIVE 1128972
So group 3 is not archived as seen from above
Now we want to create as control file manually
SQL> alter
database backup controlfile to trace;
Database altered.
It looks like following
CREATE CONTROLFILE
REUSE DATABASE "ORCL" RESETLOGS
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
-- STANDBY LOGFILE
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
;
-- Commands to
re-create incarnation table
-- Below log names
MUST be changed to existing filenames on
-- disk. Any one log
file from each branch can be used to
-- re-create
incarnation records.
-- ALTER DATABASE
REGISTER LOGFILE
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_17/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE
REGISTER LOGFILE
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_17/o1_mf_1_1_%u_.arc';
-- Recovery is
required if any of the datafiles are restored backups,
-- or if the last
shutdown was not normal or immediate.
#RECOVER DATABASE
-- All logs need
archiving and a log switch is needed.
#ALTER SYSTEM
ARCHIVE LOG ALL;
-- Database can now
be opened normally.
#ALTER DATABASE
OPEN;
-- Commands to add
tempfiles to temporary tablespaces.
-- Online tempfiles
have complete space information.
-- Other tempfiles
may require adjustment.
Move both the control files to backup
[oracle@localhost
orcl]$ mv control02.ctl control02.ctl_bkp
startup nomount the instance
SQL*Plus: Release
11.2.0.1.0 Production on Fri Aug 17 03:30:37 2012
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @control.sql
Control file
created.
ORA-00283: recovery
session canceled due to errors
ORA-01610: recovery
using the BACKUP CONTROLFILE option must be done
ALTER SYSTEM ARCHIVE
LOG ALL
*
ERROR at line 1:
ORA-01649: operation
not allowed with a backup control file
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use
RESETLOGS or NORESETLOGS option for database open
SQL> alter
database open resetlogs;
Database altered.
SQL> select
open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
so the database has created the control files at the 2 locations described in the spfile.After opening the database with the resetlogs we have the database open for read and write.The questions is thet I have not performed point in time recovery and I am ready to loose the data in the redo log files.That will be a compromise but think about the time in restoring the 25TB database.How much outage it will cause.I still don't know is it is the right way to do it or not but still there is a way.If you know a better way or if this is not correct please let me know.
No comments:
Post a Comment