Q

SQL*Plus not working

My SQL*Plus is not working. I tried the below (but it was of no use).

My SQL*Plus is not working. I tried the below (but it was of no use).
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Jul 21 10:17:13 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: scott
Enter password: *****
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Enter user-name: / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  118255568 bytes
Fixed Size                   282576 bytes
Variable Size              83886080 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


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: 'D:ORACLEORADATAOEMREPSYSTEM01.DBF'


SQL> recover datafile 'D:ORACLEORADATAOEMREPSYSTEM01.DBF'
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00279: change 11224276 generated at 05/09/2006 10:25:22 needed for thre
ORA-00289: suggestion : D:ORACLEORA90RDBMSARC00543.001
ORA-00280: change 11224276 for thread 1 is in sequence #543


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I gave <RET>

ORA-00308: cannot open archived log 'D:ORACLEORA90RDBMSARC00543.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1        542  104857600          1 NO  INACTIVE
     11204071 09-MAY-06

         2          1        543  104857600          1 NO  INVALIDATED
     11224275 09-MAY-06

         3          1        541  104857600          1 NO  INACTIVE
     11183879 09-MAY-06


======
I am at my wit's end. Kindly help me out. Note: I am just in the learning phase (not an expert).
Everything looked good up to this point in your process:
SQL> recover database using backup controlfile;
ORA-00279: change 11224276 generated at 05/09/2006 10:25:22 needed for thre
ORA-00289: suggestion : D:ORACLEORA90RDBMSARC00543.001
ORA-00280: change 11224276 for thread 1 is in sequence #543


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I gave <RET>

ORA-00308: cannot open archived log 'D:ORACLEORA90RDBMSARC00543.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
When you perform recovery using a backup controlfile, Oracle has no idea about any of your archived redo logs, so it starts at the first number if knows of and keeps increasing the sequence number. But it does not know when to stop. The correct option at this point is to reply CANCEL when asked for the next archived redo log. Once that is done, open the database with RESETLOGS. Then immediately shut down the database and take a good backup!
This was first published in July 2006

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close