| 17 Hrs since oracle is trying to recover the database [message #315164] |
Sat, 19 April 2008 09:41  |
me_maddy
Messages: 31
Registered: April 2008
|
Member |
|
|
Hello Everybody,
We have 650GB export files which we are importing on new Sun box but oracle hunged up.
We have taken a circle wise export from the production DB and tried importing the export dump into new solaris 10 machine.
Import was going on fine on the new server till 388GB, but somehow everything hunged up and our DBA was forced to kill the sessions at oracle level, when oracle kill didnt work he issue OS kill, still sessions were not getting released.
So, the database was shutdown using shutdown abort, and OS was restarted.
After OS restart we tried to bring up the database, but the .profile was corrupted, and we were unable to logon to oracle using ora10(oracle owner),
Also there was inode I/O error recorded in OS logs.
we re-created the profile then and then database was bought back online using startup.
The database is in open mode now, but alert log show the below messages.
We dont have any archive log for the database, and it is now almost 17 Hrs since oracle is trying to recover the database.
Checkpoint not complete
Current log# 1 seq# 16899 mem# 0: /nodal-vol01/oradata/analyst/redo01a.log
Current log# 1 seq# 16899 mem# 1: /nodal-vol01/oradata/analyst/redo01b.log
Sat Apr 19 14:01:56 2008
Thread 1 advanced to log sequence 16900
Current log# 2 seq# 16900 mem# 0: /nodal-vol01/oradata/analyst/redo02a.log
Current log# 2 seq# 16900 mem# 1: /nodal-vol01/oradata/analyst/redo02b.log
Thread 1 cannot allocate new log, sequence 16901
Checkpoint not complete
Current log# 2 seq# 16900 mem# 0: /nodal-vol01/oradata/analyst/redo02a.log
Current log# 2 seq# 16900 mem# 1: /nodal-vol01/oradata/analyst/redo02b.log
Sat Apr 19 14:02:39 2008
OS was restarted and there is no archive logs , on what basis oracle is trying to recover.
PLease help in analyzing and resolving the issue.
Thanks
Maddy.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: 17 Hrs since oracle is trying to recover the database [message #315184 is a reply to message #315164] |
Sat, 19 April 2008 15:07  |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
I have read those pages.
| Quote: | Oracle performs this recovery automatically on two occasions:
At the first database open after the failure of a single-instance database or all instances of an Oracle Real Applications Cluster database (crash recovery).
|
It says at first the database open.
Where does it say rolling backward occurs after database open?
I use to think recovery[cache or transaction]is a process that occurs after mount state.Once this phase is over,oracle opens the database.
But now isee its only rolling forward that occurs before database open and rolling backward is done after database open.
Everyday there is a twist in the concepts.
|
|
|
|
|
|
| Re: 17 Hrs since oracle is trying to recover the database [message #315217 is a reply to message #315164] |
Sun, 20 April 2008 02:01 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Let me put it in my words.
Redo log files contain committed and uncommitted transactions.
Instance crashed.
SQL>startup
....
when oracle mounts the database,it applies the changes in the redolog files to the datafiles since the last checkpoint.
So when redo is applied,the rolling forward phase makes the database consistent with the controlfile.
But the same redo also generates the rollback segments that contains uncommitted data.
Now oracle opens the consistent database.
It applies the undo data[rollback segments] to the datafiles.
So even if the database is opened,the media/crash recovery is still in progress.
So when would database be available for normal use?
How would oracle notify that rolling back phase is over?
|
|
|
|
|
|
| Re: 17 Hrs since oracle is trying to recover the database [message #315225 is a reply to message #315164] |
Sun, 20 April 2008 02:28 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
SQL>shutdown abort
SQL>startup
| Quote: | ALTER DATABASE MOUNT
Sun Apr 20 00:18:26 2008
Setting recovery target incarnation to 4
Sun Apr 20 00:18:26 2008
Successful mount of redo thread 1, with mount id 836892558
Sun Apr 20 00:18:26 2008
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun Apr 20 00:18:26 2008
ALTER DATABASE OPEN
Sun Apr 20 00:18:27 2008
Beginning crash recovery of 1 threads
Sun Apr 20 00:18:27 2008
Started redo scan
Sun Apr 20 00:18:27 2008
Completed redo scan
17 redo blocks read, 2 data blocks need recovery
Sun Apr 20 00:18:27 2008
Started redo application at
Thread 1: logseq 1, block 82
Sun Apr 20 00:18:28 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem 0
Mem# 0 errs 0: K:\ORAREP92\ORADATA\ORCL10G\REDO02.LOG
Sun Apr 20 00:18:28 2008
Completed redo application
Sun Apr 20 00:18:28 2008
Completed crash recovery at
Thread 1: logseq 1, block 99, scn 4189493
2 data blocks read, 2 data blocks written, 17 redo blocks read
Sun Apr 20 00:18:29 2008
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=15, OS id=2004
ARC1 started with pid=17, OS id=824
Sun Apr 20 00:18:29 2008
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
Current log# 1 seq# 2 mem# 0: K:\ORAREP92\ORADATA\ORCL10G\REDO01.LOG
Successful open of redo thread 1
Sun Apr 20 00:18:29 2008
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Apr 20 00:18:29 2008
ARC0: STARTING ARCH PROCESSES
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC2 started with pid=18, OS id=2420
Sun Apr 20 00:18:29 2008
ARC1: Becoming the heartbeat ARCH
Sun Apr 20 00:18:30 2008
SMON: enabling cache recovery
Sun Apr 20 00:18:30 2008
db_recovery_file_dest_size of 3072 MB is 36.99% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Apr 20 00:18:32 2008
Successfully onlined Undo Tablespace 1.
Sun Apr 20 00:18:32 2008
SMON: enabling tx recovery
Sun Apr 20 00:18:32 2008
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=2412
Sun Apr 20 00:18:42 2008
Completed: ALTER DATABASE OPEN
|
Here crash recovery starts after the mount state.
Then oracle signals
ALTER DATABASE OPEN
The instance/crash/media recovery occurs during the process of database opening.
When its over
Oracle signals the messages
Completed: ALTER DATABASE OPEN
Crash recovery finishes before the database open.
WHere is it written:
COmpleted: ALTER DATABASE OPEN
MEDIA/INSTANCE/CRASH RECOVERY COMPLETE
?
[Updated on: Sun, 20 April 2008 02:42] Report message to a moderator |
|
|
|
|
|
| Re: 17 Hrs since oracle is trying to recover the database [message #315236 is a reply to message #315164] |
Sun, 20 April 2008 03:09 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
The point is not how many block needed recovery.
The point is crash recovery was over before ALTER DATABASE OPEN.
Even if there were 1000 blocks,oracle would have taken that much time to finish crash recovery before signalling ALTER DATABASE OPEN.
SO where is
| Quote: | (After the database opens, any changes that were part of uncommitted transactions at the time of the crash are rolled back.)
|
justified?
|
|
|
|
|
|
| Re: 17 Hrs since oracle is trying to recover the database [message #315238 is a reply to message #315164] |
Sun, 20 April 2008 04:23 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
| Quote: | The sequence of actions are:
1/ Mount
2/ start recovery -> roll forward
3/ after roll forward is done
3a/ open database
3b/ in parallel, roll back
So the one of open database or roll back first finished (and for this latter one, instance recovery finished) depends on how much work they both have to do. The one that has less work first finishes.
|
Thats a new thing to me.
Let me try to practically implement it.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
