walkerdba
asked on
errorr
[oracle@term1 ~]$ echo $PATH
/u01/app/oracle/product/11 .2.0/db_1/ bin:/usr/s bin:/usr/l ib/qt-3.3/ bin:/usr/k erberos/bi n:/usr/loc al/bin:/us r/bin:/bin :/usr/X11R 6/bin:/hom e/oracle/b in
[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/oracl e/product/ 11.2.0/dbh ome_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/o rcl/system 01.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/o rcl/system 01.dbf'
SQL>
/u01/app/oracle/product/11
[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/oracl
[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/o
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/o
SQL>
ASKER
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/o rcl/system 01.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/o rcl/sysaux 01.dbf'
SQL>
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/o
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/o
SQL>
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.
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.
ASKER
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>
NAME FILE# STATUS
--------------------------
SYSTEM 1 UNKNOWN ERROR
SYSAUX 2 ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
EXAMPLE 5 ACTIVE
SQL>
>>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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SQL>alter database end backup;
then shutdown the database and startup.
ASKER
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/o rcl/users0 1.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/o rcl/exampl e01.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>
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/o
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/o
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>
>>none of the files are in backup
OK, now show the startup.
OK, now show the startup.
ASKER
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)(H OST=term1. localdomai n)(PORT=15 21))'
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=(PRO TOCOL=IPC) (KEY=EXTPR OC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PRO TOCOL=TCP) (HOST=term 1.localdom ain)(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
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)(H
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=(PRO
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PRO
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
>>[oracle@term1 ~]$ lsnrctl stop
Why did you stop the listener? It needs to be started.
Why did you stop the listener? It needs to be started.
ASKER
good
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.