OraFAQ Forum: Backup & Recovery » I can not make the tablespace online?

Home » RDBMS Server » Backup & Recovery » I can not make the tablespace online?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

I can not make the tablespace online? [message #301898]

Fri, 22 February 2008 03:37

trantuananh24hg
Messages: 744
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

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

Report message to a moderator

Re: I can not make the tablespace online? [message #301910 is a reply to message #301898]

Fri, 22 February 2008 04:27

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: I can not make the tablespace online? [message #301915 is a reply to message #301910]

Fri, 22 February 2008 04:36

trantuananh24hg
Messages: 744
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!

Report message to a moderator

Re: I can not make the tablespace online? [message #301958 is a reply to message #301898]

Fri, 22 February 2008 08:28

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

Report message to a moderator

Re: I can not make the tablespace online? [message #301964 is a reply to message #301958]

Fri, 22 February 2008 08:56

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: I can not make the tablespace online? [message #302529 is a reply to message #301964]

Tue, 26 February 2008 01:05

trantuananh24hg
Messages: 744
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!

Report message to a moderator

Re: I can not make the tablespace online? [message #302536 is a reply to message #302529]

Tue, 26 February 2008 01:18

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: I can not make the tablespace online? [message #302553 is a reply to message #302536]

Tue, 26 February 2008 02:26

trantuananh24hg
Messages: 744
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!

Report message to a moderator

Re: I can not make the tablespace online? [message #302557 is a reply to message #302553]

Tue, 26 February 2008 02:38

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: I can not make the tablespace online? [message #302558 is a reply to message #302557]

Tue, 26 February 2008 02:44

trantuananh24hg
Messages: 744
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!

Report message to a moderator

Previous Topic:	backup set size
Next Topic:	Kind of Confused

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 01:40:55 CST 2024