?
Solved

orcle_troubleshootiing..

Posted on 2011-10-19
12
Medium Priority
?
326 Views
Last Modified: 2012-05-12
I was assigned to assist a group with some 11gR2 issues.
They say they keep getting tablespace and segment errors.

I am not familiar with their database or the client they use.
The DB schema is created by an SAP metadata client application used to collect and analyze data.

What would be a good starting point to get familiar with the DB configuration and setup.

would you request a copy of the recent oracle alert log?
would you ask them to print the all the v$parameter to get familiarsized how things are configured?
would you check for system trace files to see if there are issues with backgroung processes?

Can you provide some steps on checking things out (more related to storage).


0
Comment
Question by:sam15
  • 6
  • 4
  • 2
12 Comments
 
LVL 8

Accepted Solution

by:
Chakravarthi Ayyala earned 1000 total points
ID: 36998108
Ask them to give you
  - each tablespace and its size (select tablespace_name, sum(bytes)/1024/1024/1024 "Size in GB" from dba_data_files group by tablespace_name)
  - number of datafiles per tablespace and their sizes (select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 from dba_data_files)
  - are the datafiles autoextensible (the above query will tell you this)
  - do they have sufficient disk space, to grow the tablespace at once?  (once you know the mount point from dba_data_files, do a df -k at the OS level)
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 1000 total points
ID: 36998339
- you can ask for the exact error message.

- my assumption is that one of the tablespace unable to extent some segment therefore reliabledba's have pointed for you to ask for those information.
- you can also use the following query to check for the available space for each tablespace you have. then extend the tablespace that being mention in the exact error message:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;
0
 

Author Comment

by:sam15
ID: 37003643
How do you know the mount point from dba_data_files? I did not find info for that.

I requested info per the queries but i  ran them on my own DB and i am wondering how can this tell you where the issue is.

If the tablespace is LMT (locally managed tablespace) is not supposed to autoexend automatically?

Would looking at parameters in V$parameter help to see how things are configured for storage.

 
 Query1
   
   TABLESPACE_NAME                Size in GB
   ------------------------------ ----------
   MESGDATA                        6.98382568
   TICDATA                        3.68041992
   SYSTEM                           .6640625
   TOOLS                          1.22265625
   UNDOTBS1                        .29296875
   XDB                            .043334961
   
   
   
   TABLESPACE_NAME                FILE_NAME                                AUT BYTES/1024/1024/1024
   ------------------------------ ---------------------------------------- --- --------------------
   SYSTEM                         /ora920/oradata/ticp/system01.dbf        YES             .6640625
   UNDOTBS1                       /ora920/oradata/ticp/undotbs01.dbf       YES            .29296875
   TICDATA                        /ora920/oradata/ticp/users01.dbf         YES           3.28979492
   TICDATA                        /ora920/oradata/ticp/users02.dbf         NO               .390625
   TOOLS                          /ora920/oradata/ticp/tools01.dbf         YES           1.22265625
   XDB                            /ora920/oradata/ticp/xdb01.dbf           YES           .043334961
   MESGDATA                        /ora920/oradata/ticp/mesgdata01.dbf       YES           6.98382568
   

Query 3

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
UNDOTBS1                              300   269.9375         90         10
SYSTEM                                680     5.1875          1         99
XDB                                44.375      .1875          0        100
MESGDATA                         7151.4375       17.5          0        100
TEMP                                 5840          0          0        100
TICDATA                           3768.75          1          0        100
TOOLS                                1252      1.125          0        100

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:Chakravarthi Ayyala
ID: 37004050
/ora920 is your mount point in this case.
If the tablespace is LMT (locally managed tablespace) is not supposed to autoexend automatically?
 => They can be autoextendable.

What are the Oracle errors the group is reporting, whom you are assisting?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37004114
sam15,
- as per my query (3rd), you can see the percent free space and percent used for each tablespace. therefore you need to extend the size (add autoextend option) of the tablespace that marked USED as 100% or nearly to 100%. i believe you can find the syntax by google.
0
 

Author Comment

by:sam15
ID: 37006078
so would i do

df -k  /ora920 to check the storage available for oracle in this case?

I think i was confused about LMT before. This only records information about the tablespace size. it does not  mean tablespace will uatoextend automatically. correct?

so the secret for autoextend data file size id turning AUTOEXTEND ON? Would this resolve all the tablespace issues (as long as there is enough storage on the disk).

ALTER TABLESPACE users
    ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
      AUTOEXTEND ON
      NEXT 512K
      MAXSIZE 250M;
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37006173
"df -k  /ora920 to check the storage available for oracle in this case? "
- i'm not linux/unix person. maybe other expert can verify that for you

"This only records information about the tablespace size. it does not  mean tablespace will uatoextend automatically. correct?"
- yes. the query just display the tablespace information and for you to see which tablespace is full and need to be extended the size

"so the secret for autoextend data file size id turning AUTOEXTEND ON?"
- yes. however based on your ALTER command, it will autoextend up to 250M only "MAXSIZE 250M;"

"as long as there is enough storage on the disk"
- for autoextend "as long as there is enough storage on the disk", you need to issue "MAXSIZE UNLIMITED".




0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37006189
- as per my response above, you need to decide whether to set autoextend with a limit on maximum size or unlimited. i would recommend to limit the size for each tablespace rather than unlimited unless you have a very big storage available.
0
 

Author Comment

by:sam15
ID: 37007055
Is this statement correct?

If autoextend is ON with Unlimited space and there is enough disk storage avialable for the oracle mount point, we will never get any table space errors and i do not need to ever autoextend tablesapce manually?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37007096
- true, that is correct until the disk is full.
0
 

Author Closing Comment

by:sam15
ID: 37022150
Excellent answers!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question