RE: Cloning Problems

Digging into my own problem here, I did find this message from some years back between Jonathen Lewis and Tom Kyte on Usenet, this probably explains why I need to do a redo log switch before shutting down...

>"If the database administrator shuts down an instance (NORMAL
>or IMMEDIATE), ORACLE forces a database checkpoint to complete
>before the instance is shut down."

Yes it does.

I will try to find out EXACTLY what is missing during the shutdown immediate. I
know that SOMETHING is missing since sometimes recovery is performed on my database and sometimes not. Review of my alert files shows this.

What it might be (and I have to research this) is that whilst the checkpoint is
occurring, rollback is also taking place pretty much at the same time. A checkpoint in Oracle doesn't stop other work from taking place. The only time a
checkpoint will hold up the instance is when the logs attempt to wrap around and
the checkpoint hasn't completed. Just becase a checkpoint completes doesn't imply the datafiles are perfectly consistent, just fairly close. We allow other
work to go on while the checkpoint takes place.

This is ME hypothesising here but perhaps:

shutdown normal (steps performed in roughly this order)

1. users not allowed to log in
2. users logged in waited on to finsish
3. when last user logs out, checkpoint starts (database is single user now)
4. when checkpoint completes database is shutdown for real (and is consistent since no work done during the checkpoint

shutdown immediate (steps....)

1. users not allowed to log in
2. users logged in are rolled back AND a checkpoint is initiated
3. when the last user is Logged off AND the checkpoint is complete the database is shutdown for real. But since the checkpoint and rolllbacks took place concurrently, database MAY be inconsistent

Again, I AM GUESSING at the above ( an educated guess, but a guess none the less). I will be out of the office till september and won't be able to look at
this in detail till then. If anyone else finds out more, let us know.

>--
>Jonathan Lewis

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

-----Original Message-----
Sent: Monday, July 29, 2002 11:49 AM
To: Multiple recipients of list ORACLE-L

Having some trouble cloning a database. The database was shutdown with the immediate option and all file where copied to new location. I usually shutdown immediate, open then shutdown normal myself but this was outside my control. I did the standard stuff with the trace file, reset logs etc...done this many times before. When I tried to open the database with the resetlogs option it told me I needed to do recovery. I have been doing this a lot lately with hot files so recovery is usually required but it has been a while since I have used cold files. Any ideas what I have done wrong here? Was it the shutdown immediate? My bet is if I had switched through the redo logs before the shutdown I probably wouldn't have this problem but I also thought that the shutdown immediate would have issued a checkpoint and all the changes in the redo would have been made to the datafiles. Thanks!

10:15:55 SQL> 10:15:55 SQL> alter database open; alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

10:16:05 SQL> alter database open resetlogs; alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/db04/oracle/DM8/system01.dbf'

10:16:12 SQL> recover database using backup controlfile until cancel ORA-00279: change 1806063 generated at 07/25/2002 05:18:01 needed for thread 1
ORA-00289: suggestion : /archive/DM8arch_0001_0000000095.dbf ORA-00280: change 1806063 for thread 1 is in sequence #95

10:16:28 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/archive/DM8arch_0001_0000000095.dbf' ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/db04/oracle/DM8/system01.dbf'

10:16:36 SQL> exit
Disconnected

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).