Home » RDBMS Server » Backup & Recovery » I can not make the tablespace online?
I can not make the tablespace online? [message #301898] Fri, 22 February 2008 03:37 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!
My DB is 10.2.0
My OS is Solaris 10

I made the tablespace EXPIRED_1 offline, and I copied the datafile associated to the other system, windows machine for 3 days ago.
And then, when my business need to query some tables which was on EXPIRED tablespace, I have to made a copy datafile to the DB's machine by FTP.

SQL> set pagesize 3000
SQL> set linesize 300
SQL> col name format a30
SQL> col status format a30
SQL> select vt.name, vd.name, vd.status
  2  from v$tablespace vt, v$datafile vd
  3  where vt.ts#=vd.ts#
  4  ;

NAME                           NAME                           STATUS
------------------------------ ------------------------------ ------------------
------------
SYSTEM                         /u01/app/oracle/oradata/TINHCU SYSTEM
                               OC/system01.dbf

UNDOTBS1                       /u01/app/oracle/oradata/TINHCU ONLINE
                               OC/undotbs01.dbf

SYSAUX                         /u01/app/oracle/oradata/TINHCU ONLINE
                               OC/sysaux01.dbf

USERS                          /u01/app/oracle/oradata/TINHCU ONLINE
                               OC/users01.dbf

USERS                          /u02/oradata/users03.dbf       ONLINE
USERS                          /u02/oradata/users07.dbf       ONLINE
USERS                          /u02/oradata/users06.dbf       ONLINE
USERS                          /u01/app/oracle/oradata/TINHCU ONLINE
                               OC/users02.dbf

USERS                          /u02/oradata/users04.dbf       ONLINE
USERS                          /u02/oradata/users05.dbf       ONLINE
EXAMPLE                        /u01/app/oracle/oradata/TINHCU ONLINE
                               OC/example01.dbf

TOOL                           /u02/oradata/tool.dbf          ONLINE
EXPIRED_1                      /u02/oradata/EXPIRED_1.DBF     OFFLINE

13 rows selected.

SQL>


When I finish coping, I change the owner and group for the datafile
# chown -R oracle:oinstall /u02/oradata/expired_1.dbf
# 
# 
# ls -l /u02/oradata/expired_1.dbf
-----w--w-   1 oracle   oinstall 10485768192 Feb 22 16:02 /u02/oradata/expired_1.dbf
# 
# exit


But when I made it online, it returned the error
SQL> alter tablespace expired_1 online;
alter tablespace expired_1 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01110: data file 15: '/u02/oradata/EXPIRED_1.DBF'


Would you like to help me?
Thank you very much!

[Updated on: Fri, 22 February 2008 04:24] by Moderator

Report message to a moderator

Re: I can not make the tablespace online? [message #301906 is a reply to message #301898] Fri, 22 February 2008 04:16 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
do you check the file prmissions of the other dbf-files ??? The expires has only write permissions for all, I guess read-write is okay
Re: I can not make the tablespace online? [message #301910 is a reply to message #301898] Fri, 22 February 2008 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle is expecting a name in UPPER case and you have a file name in lower case. So it can't work, change the name.
In addition, if Oracle can't read and write the file, how can it work with it?
Also change the permissions.

Regards
Michel
Re: I can not make the tablespace online? [message #301915 is a reply to message #301910] Fri, 22 February 2008 04:36 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
oh, I love you, Uwe and Michel!
It's my mistake
Thank you very much!
Re: I can not make the tablespace online? [message #301958 is a reply to message #301898] Fri, 22 February 2008 08:28 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Yes, thos UPPER Case problems with case insensitive Oracle ..... but it is true. Sometimes Oracle is strictly Case sensitive
Re: I can not make the tablespace online? [message #301964 is a reply to message #301958] Fri, 22 February 2008 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For file names, Oracle follows the OS.
If it is case sensitive, like Unix then Oracle is.
If OS is not case sensitive like Windows then Oracle is not.

Regards
Michel
Re: I can not make the tablespace online? [message #302529 is a reply to message #301964] Tue, 26 February 2008 01:05 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michel and Uwe.
I had an accident two days ago when I tried to make these datafiles matched the tablespace EXPIRED. Some one dropped it, he/she dropped it by one SQL alter tablespace EXPIRED including contents and datafiles.

I have had a copy of those datafiles at the other storage, I can copy them into the DB's machine, I can change owner, change operation to oracle user, but, I am feeling that I can not restore it by following action:
- Create one tablespace named as EXPIRED, it will be associated with old right path /u02/oradata/EXPIRED.DBF
- Shutdown database, use the statement
SQL> recover database; 
SQL> alter database open resetlogs; 


Am I right or wrong? Please make me clarify more.

Thank you very much!


Re: I can not make the tablespace online? [message #302536 is a reply to message #302529] Tue, 26 February 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/?SQ=648dd4bed7e9220143f59369b1df3100&t=search&srch=recover+drop+tablespace&btn_submit=Search& amp;field=all&forum_limiter=&search_logic=AND&sort_order=DESC&author=

Regards
Michel
Re: I can not make the tablespace online? [message #302553 is a reply to message #302536] Tue, 26 February 2008 02:26 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yes, thanks for your help, Michel!

I've just read some articles and threads there, however, I am sorry for some thing was forgotten that:
- My DB is not ARCHIVELOG
- I did not use any tool such like RMAN to make one backup with it. The one that I had got is EXPIRED's datafiles.

I'am going to continue searching at here.

Thank you!
Re: I can not make the tablespace online? [message #302557 is a reply to message #302553] Tue, 26 February 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your database is not in archive log mode, you can't recover the tablespace.
The only thing you can do is to restore the whole database on other location at the time of your last backup and export all objects in the tablespace and reimport them in the current database.

Regards
Michel
Re: I can not make the tablespace online? [message #302558 is a reply to message #302557] Tue, 26 February 2008 02:44 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yeap! I've to accept the truth. It's lucky for me, some tables was on this tablespace can be created by some packages and modules, it's just history table.

I've to define one policy to backup after this accident.

By anyway, thank you very much!
Previous Topic: backup set size
Next Topic: Kind of Confused
Goto Forum:
  


Current Time: Thu Dec 08 14:31:47 CST 2016

Total time taken to generate the page: 0.10936 seconds