tablespace autoextend Off

[former_member259363]

Hello expert

we are using oracle 10.2.0.4 on HP-UX for SAP ECC6.0.

currently SAP Tablespace auto extend is ON but we want to OFF this coz when we try to backup Full+offline backup sometimes

its terminated unsuccessfully due to auto extend property & some times we felt slow response coz of this.

could you plz suggest me how can i OFF this autoextend, or in future how can i add DATAFILE on tablespace, how can i extend tablespace size or last what precaution should we have to make on DR server.

kindly help me, I am not DBA person and facing so much problem due to this.

plz could you suggest me any document to over come my oracle knowledge.

Best Regards

Deepak Singh

[former_member199632 · Accepted solution]

Hello Deepak,

You can turned off autoextend on all data files for more control of space.

But make sure to keep eye on the growth.

You can use the Oracle command:

alter tablespace ... autoextend off

SQL : you need to do this for all datafiles of the tablespace one by one which are auto extendable.

alter database datafile '/oracle/PRD/sapdata11/sr_11/data11.dbf' autoextend off

Or

Brtools

Space Mangement - Alter Data File - Alter Data File Action

Turn off autoextend

When adding datafile, you can use brtools

brtools -> space management -> extend tablespace -> enter the tablespace name.... etc

If you adding , new datafile to the primary server, make sure to set the DR or SB parameter to AUTO

STANDBY_FILE_MANAGEMENT = AUTO

> when we try to backup Full+offline backup sometimes  its terminated unsuccessfully due to auto extend property

You may increase the extending amount, depending on the growth

If its a small value then, it may trying to extend in shorter periods. So after considering the workload , select a value to auto extend.

how you says , that backup fails due to auto-extending property. Whats the logs implies

regards,

Zerandib

[former_member259363]

thanks to all

now we did autoextend OFF,

sorry to late reply, due to some production backup issue not able to reply.

hi Volker,

you can see the below text those we got, the reason was at the beginning catalog has different size those not match when the backup were going to complete its due to AUTOEXTEND ON, coz during backup period the size was extended.

"ORA-19665: size 322560 in file header does not match actual file size of 262149

ORA-19664: file type: datafile copy, file name: F:\ORACLE\PRD\SAPBACKUP\BEHPEHFQ.SPA\PRD.DATA10"

plz clear me, is it necessary to first OFF all datafiles autoextend or we can do its on tablespace. or if we can dose it safe on ONLINE database.

thanks

Deepak

[volker_borowski2]

Thanks for that message example.

Looks like I have been lucky for quite awhile.

Volker

[Former Member]

alter database datafile '/oracle/PRD/sapdata11/sr_11/data11.dbf' autoextend off

is the one way to do autoextend off

[volker_borowski2]

... when we try to backup Full+offline backup sometimes 
its terminated unsuccessfully due to auto extend property & some times we felt slow response coz of this.

Hi,

can you give a message that shows an error due to an active autoextend during backup.

Never heard of that. Esp. as we turned on autoextend to be able to let the

DB grow while it is being backed up!

In earlier releases brspace simply did not extend the DB while a backup was active.

That can be very anoying for a multi TB DB (meanwhile it allows an extend during backup).

But I never had an error with autoextend on. But we did backup with rman_util.

Your second observation is obvious. When the DB is extending, it needs to format the new space first.

if you have a huge extension size, this might cost some time.

Nevertheless, I'd not consider AUTOEXTEND as a feature that takes you completely out of monitoring

growth and usage. I only do it this way for slow growing and non-busy (like development) systems.

For production, you need of course to keep track and extend beforehand to avoid the waits for the

extension occur in times not wanted. But just in case you miss it, I'd rather like the DB to do some bumpy jumps,

but carry on, instead of killing transactions because of lack of freespace.

Volker

[former_member204080]

Hi ,

Use BRtools to add a datafile

BRtools ->select option 2 space management->Extend tablespace->c for continue->Extend tablespace->then it will show all tables present->give the tablename where u want to add data file.It gets added there

After that goto db02old->refresh ->perform DB checksand DB checks and update histories then it added size will be reflected.

For Oracle go through tadm51

[former_member266290]

Dear Deepak,

You can use brtools for activities related tablesapces.

Login with OS user orasid and run brtools. In brtools menu use option space management which will give you

further options regarding tablespaces.

Also check following link:

[http://help.sap.com/saphelp_nwpi71/helpdata/en/46/e42438f63966c6e10000000a1553f7/content.htm|http://help.sap.com/saphelp_nwpi71/helpdata/en/46/e42438f63966c6e10000000a1553f7/content.htm]

-Vijay