Tag: open

ORA-01110 When trying to Open Database

Posted on by Osama Mustafa in Database
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 5: ‘ /u01/test/users02.dbf’

This Error Maybe Appear On Different Database Files Because DBF Not Exists .

Solution
1-Restore From Rman if its Not Found .

Or

SQL> select file#,name, status from v$datafile where file#=5;
FILE# NAME STATUS
———- ————————————————- ———–
5 /u01/test/users02.dbf ONLINE

SQL>
SQL> alter database datafile ‘/u01/test/users02.dbf’ offline drop;
Database altered.

SQL> alter database open;
Database altered.
SQL>

Thank you
Osama Mustafa

LEARN HOW OPEN RESETLOGS WITHOUT RECOVERY

Posted on by Osama Mustafa in Database
Amazing Oracle Trick I found today while i am Browsing OTN , I see to post it here .

What is Open resetlog mode : 
 
Online redo logs are re-created . The log sequence is reset to 1.
If the databaseis running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose filename already exists. 

First i will show its Normal Database this mean not in recovery mode :

sqlplus / as sysdba
Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit  

Now Lets Open it :

sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
 
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>