因为那个日志组是当前日志组,正在使用的,造成数据库不能启动,这时不管时添加日志组成员还是删除,由于数据库不能启动到open,因些使用alter database switch logfile;alter database clear logfile group 1;等都不行
这时只能重建 控制文件,就alter database backup controlfile to trace;
到$ORACLE_BASE/admin/arvin/udump/下找到一个trace文件,一般是8k左右大小,这时用记事本打开,找到类似于
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\issuser>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 8 10:41:53 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 570425344 bytes
Fixed Size 1250188 bytes
Variable Size 213912692 bytes
Database Buffers 348127232 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 570425344 bytes
Fixed Size 1250188 bytes
Variable Size 213912692 bytes
Database Buffers 348127232 bytes
Redo Buffers 7135232 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ARVIN" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8
SQL> GROUP 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO02.LOG' SIZE 50M,
SP2-0734: unknown command beginning "GROUP 2 'E..." - rest of line ignored.
SQL> GROUP 3 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO03.LOG' SIZE 50M,
SP2-0734: unknown command beginning "GROUP 3 'E..." - rest of line ignored.
SQL> GROUP 4 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO04.LOG' SIZE 50M
SP2-0734: unknown command beginning "GROUP 4 'E..." - rest of line ignored.
SQL> -- STANDBY LOGFILE
SQL> DATAFILE
SP2-0042: unknown command "DATAFILE" - rest of line ignored.
SQL> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\SYSTEM01.DBF',
SP2-0734: unknown command beginning "'E:\ORACLE..." - rest of line ignored.
SQL> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\UNDOTBS01.DBF',
SP2-0734: unknown command beginning "'E:\ORACLE..." - rest of line ignored.
SQL> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\SYSAUX01.DBF',
SP2-0734: unknown command beginning "'E:\ORACLE..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\USERS01.DBF',
SP2-0734: unknown command beginning "'E:\ORACLE..." - rest of line ignored.
SQL> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\EXAMPLE01.DBF',
SP2-0734: unknown command beginning "'E:\ORACLE..." - rest of line ignored.
SQL> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\ARVIN.DBF'
SP2-0734: unknown command beginning "'E:\ORACLE..." - rest of line ignored.
SQL> CHARACTER SET AL32UTF8
SP2-0734: unknown command beginning "CHARACTER ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> ;
1 CREATE CONTROLFILE REUSE DATABASE "ARVIN" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7* LOGFILE
SQL> CREATE CONTROLFILE REUSE DATABASE "ARVIN" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO02.LOG' SIZE 50M,
9 GROUP 3 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO03.LOG' SIZE 50M,
10 GROUP 4 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO04.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\SYSTEM01.DBF',
14 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\UNDOTBS01.DBF',
15 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\SYSAUX01.DBF',
16 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\USERS01.DBF',
17 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\EXAMPLE01.DBF',
18 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\ARVIN.DBF'
19 CHARACTER SET AL32UTF8
20 ;
CREATE CONTROLFILE REUSE DATABASE "ARVIN" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread
SQL> CREATE CONTROLFILE REUSE DATABASE "ARVIN" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO02.LOG' SIZE 50M,
9 GROUP 3 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO03.LOG' SIZE 50M,
10 GROUP 4 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\REDO04.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\SYSTEM01.DBF',
14 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\UNDOTBS01.DBF',
15 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\SYSAUX01.DBF',
16 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\USERS01.DBF',
17 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\EXAMPLE01.DBF',
18 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\ARVIN.DBF'
19 CHARACTER SET AL32UTF8
20 ;
Control file created.
SQL> alter database mount
2 ;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database open'
2
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
- Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\issuser>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 8 10:45:56 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL>
数据库修复好之后,做个冷备,因为没有启用归档