For training purposes you can stop your Oracle service and manually append some text to C:\oracle\oradata\ORCL\SYSTEM01.DBF, then start it again – remember: do it only on machine which belongs to you and before any changes please make a backup of the SYSTEM01.DBF
This article was written for Oracle 19c but the same steps can be applied to Oracle 12c or 11g.
First of all your database should not respond for calls which are using listener, so any application or a database management tool which depends on the Oracle listener will fail during the connection step. Here is the example of SQL Developer error:
Or error like this:
This problem might happen due to some power outage or inappropriate database administration. I would be really happy if this short article will help you with resolving it. 🙂
Next you have to try to log in to SQL Plus console, to do that you have to be logged on the same machine where is the database. Then type in CMD:
Microsoft Windows [Version 10.0.19041.804] (c) 2020 Microsoft Corporation. All rights reserved. C:\Users\mateu>sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 23:09:15 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL>
You can also try to login using the following commands:
c:\>sqlplus /nolog SQL> conn / as sysdba
When the login as sysdba user was successful let’s try to check the current status of Oracle instances on your machine:
SQL> SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE; INSTANCE_NAME STATUS DATABASE_STATUS ---------------- ------------ ----------------- orcl MOUNTED ACTIVE
When the status of your instance is OPEN then this case is not connected to corrupted system database files but if it’s MOUNTED then you can go further. Try to ALTER DATABASE OPEN on the current database instance:
SQL> ALTER DATABASE OPEN RESETLOGS; ALTER DATABASE OPEN RESETLOGS * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'
In this case SYSTEM01.DBF needs recovery, to do so you need to locate your REDO log files:
SQL> SELECT MEMBER FROM V$LOGFILE; MEMBER -------------------------------------------------------------------------------- C:\ORACLE\ORADATA\ORCL\REDO03.LOG C:\ORACLE\ORADATA\ORCL\REDO02.LOG C:\ORACLE\ORADATA\ORCL\REDO01.LOG
When you know where your REDO files are located you can proceed with system file recovery. After each use of RECOVER DATABASE USING BACKUP CONTROLFILE; you will be prompted to provide the path to one of your REDO files. Once the file will contain missing data, then the recovery will be finished:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 1952369 generated at 03/07/2021 21:32:08 needed for thread 1
ORA-00289: suggestion :
C:\USERS\MATEU\DOWNLOADS\WINDOWS.X64_193000_DB_HOME\RDBMS\ARC0000000001_10665989
39.0001
ORA-00280: change 1952369 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\ORACLE\ORADATA\ORCL\REDO03.LOG
ORA-00339: the archived log does not contain any duplicates
ORA-00334: zthe archived log: "C:\ORACLE\ORADATA\ORCL\REDO03.LOG"
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 1952369 generated at 03/07/2021 21:32:08 needed for thread 1
ORA-00289: suggestion :
C:\USERS\MATEU\DOWNLOADS\WINDOWS.X64_193000_DB_HOME\RDBMS\ARC0000000001_10665989
39.0001
ORA-00280: change 1952369 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\ORACLE\ORADATA\ORCL\REDO02.LOG
ORA-00339: the archived log does not contain any duplicates
ORA-00334: the archived log: "C:\ORACLE\ORADATA\ORCL\REDO02.LOG"
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 1952369 generated at 03/07/2021 21:32:08 needed for thread 1
ORA-00289: suggestion :
C:\USERS\MATEU\DOWNLOADS\WINDOWS.X64_193000_DB_HOME\RDBMS\ARC0000000001_10665989
39.0001
ORA-00280: change 1952369 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\ORACLE\ORADATA\ORCL\REDO01.LOG
Log applied.
Media recovery complete.
Next let’s try to ALTER the database and make it OPEN.
SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. SQL> SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE; INSTANCE_NAME STATUS DATABASE_STATUS ---------------- ------------ ----------------- orcl OPEN ACTIVE
Last step is to check whether your application or SQL Developer can connect to your database:
Hope you enjoyed the reading and the provided instructions were clear and useful. Don’t hesitate to leave a comment below. This will encourage me to post more often in database topic.