This is the result:
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
CREATE CONTROLFILE REUSE DATABASE "LAP2" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01507: database not mounted
ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\TEMP01
.DBF' REUSE
*
ERROR at line 1:
ORA-01109: database not open
ORA-01081: cannot start already-running ORACLE - shut it down first
CREATE CONTROLFILE REUSE DATABASE "LAP2" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\CONTROL01.CTL'
ORA-27086: unable to lock file - already in use
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
ORA-01507: database not mounted
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01507: database not mounted
ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\TEMP01
.DBF' REUSE
*
ERROR at line 1:
ORA-01109: database not open
SQL>
Here is the script:
- parameters and can be included in the database initialization file.
--
- LOG_ARCHIVE_DEST=''
- LOG_ARCHIVE_DUPLEX_DEST=''
--
- LOG_ARCHIVE_FORMAT=ARC%S_%R.%T
--
- DB_UNIQUE_NAME="LAP2"
--
- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
- LOG_ARCHIVE_MAX_PROCESSES=2
- STANDBY_FILE_MANAGEMENT=MANUAL
- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS
- FAL_CLIENT=''
- FAL_SERVER=''
--
- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED
NODB_UNIQUE_NAME'
- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
- Below are two sets of SQL statements, each of which creates a new
- control file and uses it to open the database. The first set opens
- the database with the NORESETLOGS option and should be used only if
- the current versions of all online logs are available. The second
- set opens the database with the RESETLOGS option and should be used
- if online logs are unavailable.
- The appropriate set of statements can be copied from the trace into
- a script file, edited as necessary, and executed when there is a
- need to re-create the control file.
--
- Set #1. NORESETLOGS case
--
- The following commands will create a new control file and use it
- to open the database.
- Data used by Recovery Manager will be lost.
- Additional logs may be required for media recovery of offline
- Use this only if the current versions of all online logs are
- available.
- After mounting the created controlfile, the following SQL
- statement will place the database in the appropriate
- protection mode:
- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "LAP2" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\REDO01.LOG' SIZE 10M,
GROUP 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\REDO02.LOG' SIZE 10M
- GROUP 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\REDO03.LOG' SIZE 10M
- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\EXAMPLE01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\PB1001',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\BMS001',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\CU001'
CHARACTER SET WE8MSWIN1252
;
- Commands to re-create incarnation table
- Below log names MUST be changed to existing filenames on
- disk. Any one log file from each branch can be used to
- re-create incarnation records.
- ALTER DATABASE REGISTER LOGFILE
'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\LAP2\ARCHIVELOG\2005_05_17\O1_MF_1_1_%U_.ARC';
- ALTER DATABASE REGISTER LOGFILE
'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\LAP2\ARCHIVELOG\2005_05_17\O1_MF_1_1_%U_.ARC';
- Recovery is required if any of the datafiles are restored backups,
- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
- Database can now be opened normally.
ALTER DATABASE OPEN;
- Commands to add tempfiles to temporary tablespaces.
- Online tempfiles have complete space information.
- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\TEMP01.DBF' REUSE;
- End of tempfile additions.
--
- Set #2. RESETLOGS case
--
- The following commands will create a new control file and use it
- to open the database.
- Data used by Recovery Manager will be lost.
- The contents of online logs will be lost and all backups will
- be invalidated. Use this only if online logs are damaged.
- After mounting the created controlfile, the following SQL
- statement will place the database in the appropriate
- protection mode:
- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "LAP2" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\REDO01.LOG' SIZE 10M,
GROUP 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\REDO02.LOG' SIZE 10M
- GROUP 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\REDO03.LOG' SIZE 10M
- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\EXAMPLE01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\PB1001',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\BMS001',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\CU001'
CHARACTER SET WE8MSWIN1252
;
- Commands to re-create incarnation table
- Below log names MUST be changed to existing filenames on
- disk. Any one log file from each branch can be used to
- re-create incarnation records.
- ALTER DATABASE REGISTER LOGFILE
'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\LAP2\ARCHIVELOG\2005_05_17\O1_MF_1_1_%U_.ARC';
- ALTER DATABASE REGISTER LOGFILE
'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\LAP2\ARCHIVELOG\2005_05_17\O1_MF_1_1_%U_.ARC';
- Recovery is required if any of the datafiles are restored backups,
- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
- Commands to add tempfiles to temporary tablespaces.
- Online tempfiles have complete space information.
- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\TEMP01.DBF' REUSE;
- End of tempfile additions.
--
fitzjarrell_at_cox.net wrote:
> Deke wrote:
>
>>I started up a Oracle 10g database running on a WinXP machine and
>
> found
>
>>this:
>>
>>ORACLE instance started.
>>
>>Total System Global Area 171966464 bytes
>>Fixed Size 787988 bytes
>>Variable Size 145750508 bytes
>>Database Buffers 25165824 bytes
>>Redo Buffers 262144 bytes
>>Database mounted.
>>ORA-00316: log 3 of thread 1, type 0 in header is not log file
>>ORA-00312: online log 3 thread 1:
>>'C:\ORACLE\PRODUCT\10.1.0\ORADATA\LAP2\REDO03.LOG'
>>
>>
>>Can any one help in resolving this matter?
>>
>>
>>Thanks
>
>
> You could have hellped yourself with this and a quick trip to
> http:/tahiti.oracle.com. The redo log reported is corrupt. Since you
> haven't stated whether you've multiplexed your redo logs you may not
> have the option to replace it with one of the multiplexed copies. You
> can, however, do this:
>
> SQL> startup nomount
> ORACLE instance started.
>
> Total System Global Area 171966464 bytes
> Fixed Size 787988 bytes
> Variable Size 145750508 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 262144 bytes
> SQL> alter database mount exclusive;
> Database mounted.
> SQL> alter database open resetlogs;
> Database opened. <=== Hopefully this is all Oracle will report
>
> If the above doesn't resolve the issue then you'll need to recreate
> your control files to eliminate the errant redo log:
>
> SQL> startup nomount
> ORACLE instance started.
>
> Total System Global Area 171966464 bytes
> Fixed Size 787988 bytes
> Variable Size 145750508 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 262144 bytes
> SQL> alter database mount exclusive;
> Database mounted.
> SQL> alter database backup controlfile to trace;
>
> Database altered.
>
> SQL>
>
> in %ORACLE_BASE%\admin\<sid>\udump will be a trace file containing the
> commands necesary to retuild a controlfile. Edit this text to remove
> the corrupt redo log and the information above the actual commands and
> then execute the script through SQL*Plus after shutting the database
> down. The text below will be similar to the script you'll generate:
>
> STARTUP NOMOUNT
> CREATE CONTROLFILE REUSE DATABASE "DEV9" NORESETLOGS ARCHIVELOG
> -- SET STANDBY TO MAXIMIZE PERFORMANCE
> MAXLOGFILES 32
> MAXLOGMEMBERS 3
> MAXDATAFILES 1024
> MAXINSTANCES 1
> MAXLOGHISTORY 113
> LOGFILE
> GROUP 1 '/oraunix/oradata/dev9/redo01.log' SIZE 100M,
> GROUP 2 '/oraunix/oradata/dev9/redo02.log' SIZE 100M,
> GROUP 3 '/oraunix/oradata/dev9/redo03.log' SIZE 100M
> -- STANDBY LOGFILE
> DATAFILE
> .....
>
>
> There will be two cases listed; removing the errant redo log should
> allow you to use the first case (since you've already tried to open the
> database resetlogs and the corrupt log would not clear). After the
> database is open you can recreate the third redo log; I'd rename the
> old file and keep it in place if the issue is due to a bad disk block
> or sector.
>
> I hope this helps.
>
>
> David Fitzjarrell
>
Received on Tue May 17 2005 - 10:45:25 CDT