Home » RDBMS Server » Backup & Recovery » 17 Hrs since oracle is trying to recover the database (10.2.0.3.0/Solaris 10)
17 Hrs since oracle is trying to recover the database [message #315164] Sat, 19 April 2008 09:41 Go to next message
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 #315165 is a reply to message #315164] Sat, 19 April 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Checkpoint not complete

This message just means that the database is doing its work but DBWR is not able to write it fast enough.
You can try add more DBWR processes but if this is your IO subsystem that is overloaded you can do nothing but wait.

This is for normal case, as you had many corruptions, maybe Oracle entered in a loop.

Ate you sure you are still in recovery phase?
If your database is open, add new larger log files.

Regards
Michel
Re: 17 Hrs since oracle is trying to recover the database [message #315174 is a reply to message #315164] Sat, 19 April 2008 13:01 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
What kind of recovery it could be when your database has been opened?

Oracle does media recovery at mount stage.
Re: 17 Hrs since oracle is trying to recover the database [message #315177 is a reply to message #315174] Sat, 19 April 2008 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What kind of recovery it could be when your database has been opened?

Instance recovery.

Regards
Michel
Re: 17 Hrs since oracle is trying to recover the database [message #315181 is a reply to message #315164] Sat, 19 April 2008 14:26 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
But the database is in open state so all the instance recovery should be over.
Re: 17 Hrs since oracle is trying to recover the database [message #315183 is a reply to message #315181] Sat, 19 April 2008 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But the database is in open state so all the instance recovery should be over.

No, Oracle open the database after the rollforward step and before rollback one.

Database Concepts
Chapter 15 Backup and Recovery
Section Overview of Instance and Crash Recovery

Regards
Michel
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 Go to previous messageGo to next message
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 #315187 is a reply to message #315184] Sat, 19 April 2008 15:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where does it say rolling backward occurs after database open?

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/intro004.htm#i1022212
Quote:
The database applies any pending updates in the online redo logs to the online datafiles of your database. The result is that, whenever the database is restarted after a crash, the datafiles reflect all committed changes up to the moment when the haven't said failure occurred. (After the database opens, any changes that were part of uncommitted transactions at the time of the crash are rolled back.)

Regards
Michel
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 Go to previous messageGo to next message
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 #315220 is a reply to message #315217] Sun, 20 April 2008 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So when would database be available for normal use?

As soon as database is open.

Quote:
How would oracle notify that rolling back phase is over?


It would not, it does not need it.
Just like when a process is killed, PMON rolls back the current transaction, it notifies nobody that it ends cleaning the process, it just completes the rollback.
Or even simpler, just like when you execute "rollback", you don't notify anyone that your rollback is over.

Regards
Michel
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 Go to previous messageGo to next message
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 #315235 is a reply to message #315225] Sun, 20 April 2008 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe there was nothing or few things to roll back (see "2 data blocks need recovery").

Regards
Michel
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 Go to previous messageGo to next message
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 #315237 is a reply to message #315236] Sun, 20 April 2008 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your point.
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.

Regards
Michel
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 Go to previous messageGo to next message
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.
Re: 17 Hrs since oracle is trying to recover the database [message #315239 is a reply to message #315164] Sun, 20 April 2008 04:43 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I tried to run a long dml and from another session shutdown abort.
But i don't see the difference.

Can you show me this happening practically?
Re: 17 Hrs since oracle is trying to recover the database [message #315240 is a reply to message #315239] Sun, 20 April 2008 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Easy, execute a very long transaction, say 1 hour of work, or many smaller transactions that cumulate 1 hour of work.
Shutdown abort.
Restart.
You can start to work quickly after restart, and far before roll back is over, so the database is open.

Regards
Michel
Re: 17 Hrs since oracle is trying to recover the database [message #315255 is a reply to message #315164] Sun, 20 April 2008 09:11 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

...We have 650GB export files ...
first place export or import is not backup method.
and second for 650gb size database please don't use export/import for backup method.

use proper backup method which oracle recommended :RMAN or USER MANAGED.

Regards
Taj
Previous Topic: logs syncing
Next Topic: excluding tablespace from backup
Goto Forum:
  


Current Time: Wed Dec 07 20:36:08 CST 2016

Total time taken to generate the page: 0.16432 seconds