Recovering Database

[Former Member]

Hi Experts,

I have On issue here..Someone has created to datafiles with name

1. F:\oracle\sid\sapdata1\system_1\.Ora

2. F:\oracle\sid\system_1\sapdata1\Btabi.data1

for the tablespace PSAPBTABI ..long ago..now the table space is almost full ..i tried to add new datafile thru SAPDBA...but it is not creating new datafile for table space PSAPBTABI, it shows that this tablespace is not following SAP cinventional datafile name...as one datafile has the name simply, <b>.ORA</b> and more over these two datafiles are created in the directiory SYSTEM_1 instead of F:\oracle\sid\sapdata4\BTABI\...

So the thing goes like this..i tried to change the name and path of these two datafiles as below;

svrmgrl>Alter tablespace psapbtabi offline;

Moved the two datafiles to new location and rename it as .ORA ->BTABI.DATA5

and other to BTABI.DAT

svrmgrl>alter tablespace psapbtabi rename datafile '' to '';

svrmgrl>Alter tablespace psapbtabi online;

then it shows that these two fileneeds media recovery..

My question is whether i shud create a crontrolfile by

svrmgrl>alter databse backup controlfile to trace;

then make appropriate changes and then do recover the database ..or is there any other option..Please Suggest me some solution....

Enviroment : 4.6c,ora817,winnt

Kind regards

Umesh K

[Former Member · Accepted solution]

Umesh,

Dont recreate control file.It is not required.

When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tablespace. Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level. Oracle saves rollback data corresponding to those completed statements in a deferred rollback segment in the SYSTEM tablespace. When the tablespace is brought back online, Oracle applies the rollback data to the tablespace, if needed.

The following statement performs tablespace recovery using parallel recovery processes:

ALTER DATABASE

RECOVER TABLESPACE <name>

PARALLEL;

Or recover tablespace <name>;

Or recover datafile '.....' => name of file or file no

Or recover database;

And just bring it online that s it....

Let me know if u need more help..

Regards

Vinod

Message was edited by:

Vinod U

Message was edited by:

Vinod U

[Former Member · Accepted solution]

Hi Vinod,

So only i need to do this one

svrmgrl>recover tablespace psapbtabi;

>alter tablespace psapbtabi online;

regards

Umesh K

[Former Member · Accepted solution]

Umesh,

Yes.

Regards

Vinod

[Former Member · Accepted solution]

Hi,

Thanks,i will surely let u know once i did this, as it is development server..

Regards

Umesh K

[Former Member · Accepted solution]

Umesh,

You can do it while database is up and running.It does not matter DEV or PROD.

Until tablespace is not available to users they will not be able to access data present in this tablespace.You should bring it online immediately.It will not disturb your Env.

Regards

Vinod

[Former Member · Accepted solution]

Hi,

I have one more doubt...

Why shud we issue this SQL after the datafile rename command

sql>restore tablespace <name>; and

sql>switch datafile all;

if possible plz give me some idea..

Regards

Umesh K

[Former Member · Accepted solution]

Umesh,

To restore files from backups or image copies. By default, RMAN restores files to their default location. You can use the SET NEWNAME command to restore files to nondefault locations. RMAN restores backups from disk or tape and restores images copies from disk only.

Typically, you restore when a media failure has damaged a current datafile, control file, or archived log or prior to performing a point-in-time recovery. The RESTORE command restores full backups, incremental backups (level 0 only), or copies of datafiles, control files, and archived redo logs.

To specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file. A switch is equivalent to using the ALTER DATABASE RENAME FILE statement: Oracle renames the files in the control file but does not actually rename them on the operating system.

Hope this clears all your doubts

Regards

Vinod

[Former Member · Accepted solution]

Hi Vinod,

Yep, sure..This is the first time i ever faced this type of situation,.this is a new information..thanks a lot..I will let u know the result soon...

Vinod,One more thing,Here i need to change the extension of the datafile ie from <b>.ORA to BTABI.DATA5</b> , Is it posible thru alter tablespace rename datafile ... command...

Regards

Umesh K

[Former Member · Accepted solution]

Umesh,

Yes U can but usually Std is .dbf extn for datafiles .ctl for control files .log for redolog files .arc for archive log files.

While working with SAP and Oracle Dont try to play aroung with files or names or extensions.

If u still want to change name u can change with same command.

Regards

Vinod

[Former Member · Accepted solution]

Vinod,

The thing is some one has created the datafile thru oracle DBA STUDIO in ora817,instead of using SAPDBA..So now i am unable to add ne datafile thru SAPDBA, so i was forced to do these changes, for the time being i made autoextend on..and i thought to change the extension name of .ORA. The default name with SAP for datafiles is *.DATA.

Thanks Vinod

Kind Regards

Umesh K

[Former Member · Accepted solution]

Hi Vinod,

I did the changes, problem solved ...thanks a lot

Kind Regards

Umesh K

[Former Member · Accepted solution]

Umesh,

Many thanks for your reward points.

Regards

Vinod