Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> AUTOEXTEND, Auto extensible settings for our tablespaces
jenkins
post Mar 28 2017, 03:06 PM
Post #1


Member
**

Group: Members
Posts: 12
Joined: 3-March 16
Member No.: 51,207



We have had several occurrences in the last few weeks of tables not being able to extend.
We have found out that the auto extensible settings for D_CQM has been turned off.
We have recently lost our DBA and are in the process of acquiring DBA support through Cerner.
BUT in the interim can someone check all our auto-extensible settings and make sure they are all turned on?
This has created multiple downtimes!
Issue Impact: Created several unscheduled downtimes leading to order and result delays for patient care.



below are the datafiles that needs to be extended
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024 AUT STATUS
------------------------------ ---------- --- ---------
+PROD_DG1/prod/datafile/d_cqm.621.936232125
D_CQM 33553408 NO AVAILABLE

+PROD_DG1/prod/datafile/d_cqm.623.939214877
D_CQM 33553408 NO AVAILABLE


+PROD_DG1/prod/datafile/d_cqm.622.937919389
D_CQM 33553408 NO AVAILABLE







SQL> ALTER DATABASE DATAFILE '/+PROD_DG1/prod/datafile/d_cqm.621.936232125.dbf' AUTOEXTEND ON;

ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/+PROD_DG1/prod/datafile/d_cqm.621.936232125.dbf"
Go to the top of the page
 
+Quote Post
burleson
post Mar 30 2017, 11:40 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,566
Joined: 26-January 04
Member No.: 13



Hi Jenkins,

You always want to make all tablespaces with "autoextend on" and "mexextents unlimited".

Then you just need to monitor the filesystem for filling:

http://www.dba-oracle.com/t_alter_autoextend_on.htm

You need to use these steps for an ASM file:

http://www.dba-oracle.com/t_ora_01516_none...temp_string.htm




--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Vishok
post Apr 17 2017, 08:10 AM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 17-April 17
From: bangalore
Member No.: 51,911



Hi Jenkins,

The issue I see from your error is that, you have given a wrong datafile name (since its in ASM, it doesn't require a '/' in the beginning and the ".dbf" extension in the end) and the syntax needs a little change.

You must be giving it as below:

SQL> ALTER DATABASE DATAFILE '+PROD_DG1/prod/datafile/d_cqm.621.936232125' AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

"NEXT 50M" - tells the database that the segments should be allocated in 50M chunks as the datafile grows -> if not specified it will increment by 1 by default

"MAXSIZE UNLIMITED" - tells the database that the maximum size of the datafile can be up to the allowed size based on the formula (4194303 * db_block_size parameter). --> this is a default value even if you don't specify MAX_SIZE

for example:

if db_block_size parameter is set to 8kb - then your datafile size can be up to 31.99 GB => 4194303 * 8192 = 34359730176 bytes ~ 31.99 GB


And you can refer the links suggested by Burleson for more accurate understanding.


PS: I am a DBA from CERNER wink.gif


Regards,

Vishok


--------------------
Regards,
VISH
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 18th October 2017 - 12:49 AM