Solved

errorr

Posted on 2013-05-15
13
564 Views
Last Modified: 2013-05-31
[oracle@term1 ~]$ echo $PATH
/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/usr/lib/qt-3.3/bin:/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin
[oracle@term1 ~]$ cd /oratab
bash: cd: /oratab: No such file or directory
[oracle@term1 ~]$ vi /etc/oratab
[oracle@term1 ~]$ . oraenv
ORACLE_SID = [DB11G] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@term1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 15 17:44:33 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  636100608 bytes
Fixed Size                  1338392 bytes
Variable Size             478151656 bytes
Database Buffers          150994944 bytes
Redo Buffers                5615616 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


SQL> sh
SP2-0042: unknown command "sh" - rest of line ignored.
SQL> shu
ORA-01109: database not open


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

Total System Global Area  636100608 bytes
Fixed Size                  1338392 bytes
Variable Size             478151656 bytes
Database Buffers          150994944 bytes
Redo Buffers                5615616 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


SQL>
0
Comment
Question by:walkerdba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39170109
From the SQL prompt try:
alter tablespace system end backup;

From the docs:
http://docs.oracle.com/cd/B28359_01/server.111/b28278/e9858.htm

ORA-10873: file string needs to be either taken out of backup mode or media recovered
    Cause: An attempt was made to open a database after an instance failure or SHUTDOWN ABORT interrupted an online backup.
    Action: If the indicated file is not a restored backup, then issue the ALTER DATABASE END BACKUP command and open the database. If the file is a restored online backup, then apply media recovery to it and open the database.
0
 

Author Comment

by:walkerdba
ID: 39170283
Then I did this..

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  636100608 bytes
Fixed Size                  1338392 bytes
Variable Size             478151656 bytes
Database Buffers          150994944 bytes
Redo Buffers                5615616 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


SQL> alter system end backup;
alter system end backup
             *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> alter tablespace system end backup;

Tablespace altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  636100608 bytes
Fixed Size                  1338392 bytes
Variable Size             478151656 bytes
Database Buffers          150994944 bytes
Redo Buffers                5615616 bytes
Database mounted.
ORA-10873: file 2 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'


SQL>
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 39170436
first check which are the tablespace are in backup mode by executing the below script.

SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

 see that status of all the tablespace should be in NOT ACTIVE, if it is in ACTIVE mode then you have to issue the command from your previous post to stop backup.

i.e alter tablespace <tablespace_name> end backup;

then open the database.
0
WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

 

Author Comment

by:walkerdba
ID: 39171085
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

NAME                                FILE# STATUS
------------------------------ ---------- ------------------
SYSTEM                                  1 UNKNOWN ERROR
SYSAUX                                  2 ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
EXAMPLE                                 5 ACTIVE

SQL>
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39171142
>>SYSTEM                                  1 UNKNOWN ERROR

It appears you need to recover the system tablespace.

That is the second part of the error message:
ORA-10873: file 2 needs to be either taken out of backup mode or media
recovered
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 39171559
I don't think you need media recovery yet.  As the first part of that statement says taken out of backup mode, which is precisely the first issue here.

You need to do an end backup on those other 4 tablespaces.

alter tablespace sysaux end backup;
alter tablespace undotbs end backup;
alter tablespace users end backup;
alter tablespace example end backup;

Then try opening the database.  As the database appears to be have been shut down while files were in backup mode, instance recovery should be all that is required, not media recovery.
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 39173886
It clearly states that you have started the backup and its not ended. so first try to end the backup.

SQL>alter database end backup;

then shutdown the database and startup.
0
 

Author Comment

by:walkerdba
ID: 39174588
This is what is happening


SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs end backup;
alter tablespace users end backup;
alter tablespace example end backup;
alter tablespace undotbs end backup
*
ERROR at line 1:
ORA-00959: tablespace 'UNDOTBS' does not exist


SQL>
Tablespace altered.

SQL>
Tablespace altered.

SQL>  alter tablespace undotbs end backup;
 alter tablespace undotbs end backup
*
ERROR at line 1:
ORA-00959: tablespace 'UNDOTBS' does not exist


SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users end backup;
alter tablespace users end backup
*
ERROR at line 1:
ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0
ORA-01199: file 4 is not in online backup mode
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'


SQL> alter tablespace example end backup;
alter tablespace example end backup
*
ERROR at line 1:
ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0
ORA-01199: file 5 is not in online backup mode
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'


SQL>

SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01142: cannot end online backup - none of the files are in backup


SQL>
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39174593
>>none of the files are in backup

OK, now show the startup.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39174599
Also before the startup, run the status select posted in http:#a39170436 again.

Post the results.
0
 

Author Comment

by:walkerdba
ID: 39175137
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

NAME                                FILE# STATUS
------------------------------ ---------- ------------------
SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 NOT ACTIVE
USERS                                   4 NOT ACTIVE
EXAMPLE                                 5 NOT ACTIVE

SQL> shu
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=term1.localdomain)(PORT=1521))'
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@term1 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2013 08:19:46

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=term1.localdomain)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
   Linux Error: 111: Connection refused
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39175147
>>[oracle@term1 ~]$ lsnrctl stop

Why did you stop the listener?  It needs to be started.
0
 

Author Closing Comment

by:walkerdba
ID: 39211790
good
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question