Home » RDBMS Server » Backup & Recovery » alter database open; ORA-01092 (WINDOWS-XP 10.2.0.1.0)
icon4.gif  alter database open; ORA-01092 [message #529691] Wed, 02 November 2011 09:32 Go to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
SQL> ALTER DATABASE MOUNT;

数据库已更改。

SQL> COL ERROR FOR A10
SQL> SELECT *FROM V$RECOVER_FILE

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------- ---------- --------------
2 OFFLINE OFFLINE 766823 02-11? -11

SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;

CHECKPOINT_CHANGE#
------------------
927312

SQL> COL NAME FOR A67
SQL> SELECT NAME,STATUS FROM V$DATAFILE WHERE FILE#=2;

NAME STATUS
------------------------------------------------------------------- -------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF RECOVER

SQL>


SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

alert_orcl.log report this:
Wed Nov 02 22:18:59 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1188.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Wed Nov 02 22:18:59 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_4968.trc:
ORA-00604: error occurred at recursive SQL level

Wed Nov 02 22:19:00 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_6416.trc:
ORA-00604: error occurred at recursive SQL level

Wed Nov 02 22:19:00 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_reco_5156.trc:
ORA-00604: error occurred at recursive SQL level

Wed Nov 02 22:19:00 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_2120.trc:
ORA-00604: error occurred at recursive SQL level

Wed Nov 02 22:19:01 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_4788.trc:
ORA-00604: error occurred at recursive SQL level

Wed Nov 02 22:19:01 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mman_1716.trc:
ORA-00604: error occurred at recursive SQL level

Wed Nov 02 22:19:01 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_7480.trc:
ORA-00604: error occurred at recursive SQL level

Instance terminated by USER, pid = 1188
ORA-1092 signalled during: alter database open...



-----------------------------------
the datafile 2 is miss.

i add underside parameters for db to open, but i unsuccess.
SQL> alter system set undo_management=manual scope=spfile;
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL> alter system set "_allow_error_simulation"=true scope=spfile;
SQL> alter system set "_offline_rollback_segments"=true scope=spfile;
SQL> alter system set "_offline_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSMU3$','_SYSMU4$','_S
YSMU5$','_SYSMU6$','_SYSMU7$','_SYSMU8$','_SYSMU9$','_SYSMU10$' scope=spfile;

undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1




i can't open the db. anyone can help me. thanks.

Re: alter database open; ORA-01092 [message #529692 is a reply to message #529691] Wed, 02 November 2011 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
open Service Request with MOS
Re: alter database open; ORA-01092 [message #529693 is a reply to message #529691] Wed, 02 November 2011 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01092: ORACLE instance terminated. Disconnection forced
 *Cause:  The instance this process was connected to was terminated
            abnormally, probably via a shutdown abort. This process
            was forced to disconnect from the instance.
 *Action: Examine the alert log for more details. When the instance has been
          restarted, retry action.


Now that you did what you did we can no more help.
Next time, ask us BEFORE applying underscore parameters that you don't understand and screw the database.

Also next time, set your language to english so we could understand the error messages.

Regards
Michel

[Updated on: Wed, 02 November 2011 09:44]

Report message to a moderator

Re: alter database open; ORA-01092 [message #529696 is a reply to message #529693] Wed, 02 November 2011 09:50 Go to previous messageGo to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
it's a test DB.
i only want to know if have a method to open the db.

Re: alter database open; ORA-01092 [message #529697 is a reply to message #529696] Wed, 02 November 2011 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe... before you used underscore parameters.

Regards
Michel
Re: alter database open; ORA-01092 [message #529700 is a reply to message #529697] Wed, 02 November 2011 10:19 Go to previous messageGo to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
can you tell me what to do.
i want to try it.
Re: alter database open; ORA-01092 [message #529706 is a reply to message #529700] Wed, 02 November 2011 10:47 Go to previous messageGo to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
Dead horse as live horse to medical treatment
Re: alter database open; ORA-01092 [message #529720 is a reply to message #529706] Wed, 02 November 2011 12:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
huh?
Re: alter database open; ORA-01092 [message #529721 is a reply to message #529720] Wed, 02 November 2011 12:06 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The duck flies at midnight. The hen is in the nest, i repeat, the hen is in the nest.
Re: alter database open; ORA-01092 [message #529722 is a reply to message #529721] Wed, 02 November 2011 12:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The spy convention is round the corner.
Re: alter database open; ORA-01092 [message #530088 is a reply to message #529722] Fri, 04 November 2011 10:54 Go to previous messageGo to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
i had open the db.

1.change the parameter file.
*.undo_management='manual'

*._offline_rollback_segments='true'

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSS MU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19 $','_SYSSMU20$'

SQL>startup mount pfile='xxx';

SQL> alter database datafile 2 offline drop;


SQL> alter database open;
Re: alter database open; ORA-01092 [message #530101 is a reply to message #530088] Fri, 04 November 2011 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And now your database is in an inconsistent state.

People reading this DO NOT DO THAT UNLESS YOUR COMPLETLY UNDERSTAND WHAT ARE THE CONSEQUENCES.

Regards
Michel
Re: alter database open; ORA-01092 [message #530143 is a reply to message #530101] Fri, 04 November 2011 20:57 Go to previous messageGo to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
yes , i understand the consequence.

the uncommit datas will miss.

because my db close with abort and the undotbs01.dbf is deleted, and noarchivelog, so i only do this to open the DB.

----------
the result is miss some datas, but the DB is open.

In addition, I have no other way.

now the db is consistent state.

Re: alter database open; ORA-01092 [message #530148 is a reply to message #530143] Sat, 05 November 2011 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the uncommit datas will miss.

No! You don't understand what you did.
The problem is that some NOT commited data may be in the database (and others no).

Quote:
the result is miss some datas, but the DB is open

The DB is open but in an inconsistent state.

Quote:
now the db is consistent state.

NO!

Regards
Michel

[Updated on: Sat, 05 November 2011 01:17]

Report message to a moderator

Re: alter database open; ORA-01092 [message #530155 is a reply to message #530148] Sat, 05 November 2011 04:15 Go to previous messageGo to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
how to understand the db in an inconsistent state or a consistent state?

How do I get to the test for inconsistent state or consistent state?
Re: alter database open; ORA-01092 [message #530159 is a reply to message #530155] Sat, 05 November 2011 05:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Good morning - your question "How do I get to the test for inconsistent state or consistent state?" goes to the heart of the problem: as far I know (and I would be interested to know if this correct) in 10g there is no way to tell if the database is consistent. User data doesn't matter so much, but you could have an incomplete transaction in the data dictionary which could destroy the database at any time. Imagine the consequences of an incomplete space management transaction, for instance. This is why Oracle says that if you have used any of the unsupported techniques for opening a damaged database, you should do a full export immediately, remove the entire database, and import into a new one.
11g includes the DBMS_HM package that lets you run a range of tests, including the Dictionary Integrity Check which might help in these circumstances.
Re: alter database open; ORA-01092 [message #530163 is a reply to message #530159] Sat, 05 November 2011 10:00 Go to previous messageGo to next message
OracGPS
Messages: 17
Registered: April 2011
Junior Member
hi John Watson, thans your reply.
Re: alter database open; ORA-01092 [message #530165 is a reply to message #530163] Sat, 05 November 2011 10:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>the uncommit datas will miss.
I contend just the opposite is the result.
If DML was done without any COMMIT just prior to the DB crashing or SHUTDOWN ABORT,
the changed data can exist in the DB datafiles.
Normally when the instance starts, a ROLLBACK of this data would occur based upon contents of UNDO.
Since DB was started bypassing the UNDO, inconsistent data may exist within the DB.
I am not concerned about user/application data.
I am concerned about the integrity of the Data Dictionary itself.
Re: alter database open; ORA-01092 [message #530166 is a reply to message #530159] Sat, 05 November 2011 11:03 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Data dictionary transactions are protected by SYSTEM rollback segment, so I should not really be concerned by this one when you bypass UNDO tablespace (although I'm not sure Oracle did not introduce some operations that are protected by "normal" undo segments in the latest versions).
But, unless there was no running transaction on user data, these latter ones are in an inconsistent state.

Anyway, John is right, in this case the actions are:
1/ Export all application accounts
2/ Destroy the database
3/ Create a new one
4/ Import the previously exported accounts
5/ Run a consistent check on application data (and the import already made some like checking for missing foreign keys)

It may not be possible you can directly make the second step because some export tables may be in wrong state. Then you have to manually export your data.

Regards
Michel
Previous Topic: what to do after deleting a database without deleting its rman backups
Next Topic: 11g Active Database Duplication
Goto Forum:
  


Current Time: Fri Mar 29 04:36:03 CDT 2024