 alter database open; ORA-01092 [message #529691] |
Wed, 02 November 2011 09:32  |
 |
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 #529693 is a reply to message #529691] |
Wed, 02 November 2011 09:43  |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #530088 is a reply to message #529722] |
Fri, 04 November 2011 10:54 |
|
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 #530148 is a reply to message #530143] |
Sat, 05 November 2011 01:16 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #530159 is a reply to message #530155] |
Sat, 05 November 2011 05:57 |
John Watson
Messages: 8964
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 #530166 is a reply to message #530159] |
Sat, 05 November 2011 11:03 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
