Oracle Apps R12: DBA Analysis Of TableSpaces, Log, And Control Files


Oracle Apps R12: More DBA Analysis Of TableSpaces, Log, And Control Files

Lots of tablespaces! 

select TABLESPACE_NAME
from dba_tablespaces
order by TABLESPACE_NAME

TABLESPACE_NAME
------------------------------
APPS_CALCLIP
APPS_OMO
APPS_TS_ARCHIVE

APPS_TS_DISCO
APPS_TS_DISCO_OLAP
APPS_TS_INTERFACE
APPS_TS_MEDIA
APPS_TS_NOLOGGING
APPS_TS_QUEUES
APPS_TS_SEED
APPS_TS_SUMMARY
APPS_TS_TX_DATA
APPS_TS_TX_IDX
B2B_DT
B2B_IDX
B2B_LOB
B2B_RT
BAM
BIA_RTL
CTXSYS
CWMLITE
DATA
DCM
DEMANTRA
DISCO_PTM5_CACHE
DISCO_PTM5_META
DSGATEWAY_TAB
DW_AGGREGATE_IDX
DW_AGGREGATE_TBS
DW_BASE_IDX
DW_BASE_TBS
DW_DERIVED_IDX
DW_DERIVED_TBS
DW_DM_TBS
DW_LOOKUP_TBS
DW_MVLOG_TBS
DW_REFERENCE_IDX
DW_REFERENCE_TBS
GEOR_TBS
HTMLDB
IAS_META
MTR
OCATS
ODM_DATA
OLAP_BAAD
OLTS_ATTRSTORE
OLTS_BATTRSTORE
OLTS_CT_STORE
OLTS_DEFAULT
OLTS_SVRMGSTORE
OPMOR
ORABPEL
OWAPUB
RBS_MIG
RE
SYNCSERVER
SYSAUX
SYSTEM
TEMP1
TEMP2
TS_DP
TS_SALES_DATA
TS_SALES_DATA_ENGINE
TS_SALES_DATA_ENGINE_X
TS_SALES_DATA_X
TS_SIM
TS_SIM_X
UDDISYS_TS
UNDO_TBS
WCRSYS_TS
XDB

71 rows selected.

-------------------------

Where are the files for the tablespaces?

find /oapps/oracle/VIS/db/apps_st/data  -type f| sort

/oapps/oracle/VIS/db/apps_st/data/apps_calclip1.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_calclip2.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_calclip3.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_calclip.dbf
/oapps/oracle/VIS/db/apps_st/data/APPS_OMO02.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_ts_interface10.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_ts_interface11.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_ts_interface12.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_ts_interface13.dbf
/oapps/oracle/VIS/db/apps_st/data/apps_ts_interface14.dbf
...

/oapps/oracle/VIS/db/apps_st/data/ias_meta01.dbf
/oapps/oracle/VIS/db/apps_st/data/IAS_META02.dbf
/oapps/oracle/VIS/db/apps_st/data/log1.dbf
/oapps/oracle/VIS/db/apps_st/data/log2.dbf
/oapps/oracle/VIS/db/apps_st/data/log3.dbf
/oapps/oracle/VIS/db/apps_st/data/media1.dbf
/oapps/oracle/VIS/db/apps_st/data/media2.dbf
/oapps/oracle/VIS/db/apps_st/data/media3.dbf
/oapps/oracle/VIS/db/apps_st/data/media4.dbf
/oapps/oracle/VIS/db/apps_st/data/media5.dbf

...

/oapps/oracle/VIS/db/apps_st/data/sys9.dbf
/oapps/oracle/VIS/db/apps_st/data/sysaux01.dbf
/oapps/oracle/VIS/db/apps_st/data/SYSTEM01.dbf
/oapps/oracle/VIS/db/apps_st/data/SYSTEM02.dbf
/oapps/oracle/VIS/db/apps_st/data/temp1_01.dbf
/oapps/oracle/VIS/db/apps_st/data/temp2_01.dbf
/oapps/oracle/VIS/db/apps_st/data/ts_dp.dbf

...

/oapps/oracle/VIS/db/apps_st/data/tx_idx8.dbf
/oapps/oracle/VIS/db/apps_st/data/tx_idx9.dbf
/oapps/oracle/VIS/db/apps_st/data/uddisys01.dbf
/oapps/oracle/VIS/db/apps_st/data/undotbs_01.dbf
/oapps/oracle/VIS/db/apps_st/data/undotbs_02.dbf
/oapps/oracle/VIS/db/apps_st/data/wcrsys01.dbf

242 files.

Includes:

Log files
Control files
Files for Undo tablespaces
Temp tablespaces

--------------------

Total size of files: 

du -hs  /oapps/oracle/VIS/db/apps_st/data

187G    /oapps/oracle/VIS/db/apps_st/data

--------------------

Log files:

@loginfo

Select *
from      v$log,
v$logfile
where      v$log.group#  = v$logfile.group#

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIME                        GROUP# STATUS  TYPE
----------------------------- ---------- ------- -------
MEMBER
----------------------------------------------------------------------------------------------------
IS_
---
3          1          6  314572800          1 NO  CURRENT             1.0132E+13
2010-NOV-21 17:43:42                   3         ONLINE
/oapps/oracle/VIS/db/apps_st/data/log1.dbf
NO

2          1          5  314572800          1 NO  INACTIVE            1.0132E+13
2010-APR-22 14:38:36                   2         ONLINE
/oapps/oracle/VIS/db/apps_st/data/log2.dbf
NO

1          1          4  314572800          1 NO  INACTIVE            1.0132E+13
2010-FEB-05 20:35:05                   1         ONLINE
/oapps/oracle/VIS/db/apps_st/data/log3.dbf
NO

--------------------

Control files:

@ctlfile

Select *
from v$controlfile

STATUS
-------
NAME
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/oapps/oracle/VIS/db/apps_st/data/cntrl01.dbf
NO       16384           1042

/oapps/oracle/VIS/db/apps_st/data/cntrl02.dbf
NO       16384           1042

/oapps/oracle/VIS/db/apps_st/data/cntrl03.dbf
NO       16384           1042

--------------------

Temp Tablespace:

@ lttsinfo.sql

select *
from v$TEMPFILE

FILE# CREATION_CHANGE# CREATION_TIME                        TS#     RFILE# STATUS  ENABLED
---------- ---------------- ----------------------------- ---------- ---------- ------- ----------
BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
----------------------------------------------------------------------------------------------------
1       1.0132E+13 2010-JAN-28 00:14:33                  75          1 ONLINE  READ WRITE
2118123520     258560   1048576000       8192
/oapps/oracle/VIS/db/apps_st/data/temp1_01.dbf

2       1.0132E+13 2010-JAN-28 00:14:34                  76          1 ONLINE  READ WRITE
2118123520     258560   1048576000       8192
/oapps/oracle/VIS/db/apps_st/data/temp2_01.dbf

------------------------------

UNDO tablespace:

select distinct ts.name
from  v$undostat    u,
v$tablespace  ts
where u.undotsn = ts.TS#

- this script will only find UNDO tablespaces that are online,
and have active undo extents

NAME
------------------------------
UNDO_TBS

--------------------

Which tablespaces have the most datafiles???

ltsu.sql:

Select TABLESPACE_NAME
, to_char ( (sum (BYTES ) / 1024 / 1024), '999,999,999.99' ) as Size_in_megs
, count(*)  Number_Of_Files
from dba_data_files
group by TABLESPACE_NAME
order by 3, sum (BYTES ), 1

TABLESPACE_NAME                SIZE_IN_MEGS    NUMBER_OF_FILES
------------------------------ --------------- ---------------
DISCO_PTM5_META                           3.00               1
OCATS                                     3.00               1
OLTS_DEFAULT                              3.00               1
OLTS_SVRMGSTORE                           3.00               1
WCRSYS_TS                                 3.00               1
MTR                                       4.00               1
OPMOR                                     6.00               1
DSGATEWAY_TAB                             7.00               1
BAM                                       8.00               1
OLTS_CT_STORE                             8.00               1
RE                                        8.00               1
OWAPUB                                   10.00               1
B2B_LOB                                  12.00               1
ORABPEL                                  12.00               1
OLTS_ATTRSTORE                           15.00               1
OLTS_BATTRSTORE                          15.00               1
B2B_IDX                                  16.00               1
UDDISYS_TS                               21.00               1
ODM_DATA                                 40.00               1
B2B_RT                                   42.00               1
RBS_MIG                                  43.00               1
TS_DP                                    50.00               1
TS_SALES_DATA                            50.00               1
TS_SALES_DATA_ENGINE                     50.00               1
TS_SALES_DATA_ENGINE_X                   50.00               1
TS_SALES_DATA_X                          50.00               1
TS_SIM                                   50.00               1
TS_SIM_X                                 50.00               1
SYNCSERVER                               54.00               1
XDB                                      61.00               1
B2B_DT                                   68.00               1
CTXSYS                                   79.00               1
DW_AGGREGATE_IDX                        100.00               1
DW_AGGREGATE_TBS                        100.00               1
DW_BASE_IDX                             100.00               1
DW_BASE_TBS                             100.00               1
DW_DERIVED_IDX                          100.00               1
DW_DERIVED_TBS                          100.00               1
DW_DM_TBS                               100.00               1
DW_LOOKUP_TBS                           100.00               1
DW_MVLOG_TBS                            100.00               1
DW_REFERENCE_IDX                        100.00               1
DISCO_PTM5_CACHE                        108.00               1
DW_REFERENCE_TBS                        133.94               1
APPS_TS_DISCO                           856.00               1
BIA_RTL                               1,000.00               1
SYSAUX                                1,327.00               1
APPS_TS_DISCO_OLAP                    1,350.00               1
CWMLITE                                  23.00               2
DCM                                     198.00               2
IAS_META                                221.00               2
HTMLDB                                  287.00               2
APPS_OMO                              1,150.00               2
GEOR_TBS                              2,136.00               2
UNDO_TBS                              3,686.41               2
APPS_TS_NOLOGGING                       575.00               3
APPS_TS_ARCHIVE                       1,101.00               3
OLAP_BAAD                             1,500.00               3
APPS_TS_SEED                          3,803.00               3
APPS_TS_QUEUES                        4,299.00               3
APPS_CALCLIP                          4,770.00               4
DEMANTRA                              5,400.00               4
DATA                                  7,200.00               5
APPS_TS_MEDIA                         5,211.25               7
SYSTEM                               19,297.00              12
APPS_TS_SUMMARY                      22,369.00              15
APPS_TS_TX_IDX                       32,471.00              26
APPS_TS_INTERFACE                     2,404.00              27
APPS_TS_TX_DATA                      62,692.25              57

69 rows selected.

System tablespace is HUGE, compared to other installations I've done!
12 data files.  Over 19 gigs!

Tablespace APPS_TS_TX_DATA has 57 files, and is about 62 gigs in size.
Wow! 

-------------------

How full are the tablespaces?

Ltsu.sql

TABLESPACE_NAME                FREE_MEG USED_MEG TOTAL_ME PERCENT
------------------------------ -------- -------- -------- -------
TS_DP                                50        0       50     .00
TS_SALES_DATA_ENGINE_X               50        0       50     .00
DW_REFERENCE_TBS                    134        0      134     .00
DEMANTRA                          5,400        0    5,400     .00
DW_REFERENCE_IDX                    100        0      100     .00
BIA_RTL                           1,000        0    1,000     .00
DW_LOOKUP_TBS                       100        0      100     .00
DW_AGGREGATE_IDX                    100        0      100     .00
DW_MVLOG_TBS                        100        0      100     .00
OLAP_BAAD                         1,500        0    1,500     .00
TS_SIM_X                             50        0       50     .00
OWAPUB                               10        0       10     .00
TS_SALES_DATA_X                      50        0       50     .00
TS_SALES_DATA_ENGINE                 50        0       50     .00
DW_BASE_IDX                         100        0      100     .00
DW_DERIVED_TBS                      100        0      100     .00
DW_DM_TBS                           100        0      100     .00
TS_SALES_DATA                        50        0       50     .00
DW_DERIVED_IDX                      100        0      100     .00
TS_SIM                               50        0       50     .00
DW_AGGREGATE_TBS                    100        0      100     .00
DW_BASE_TBS                         100        0      100     .00
DATA                              7,193        7    7,200     .10
APPS_TS_DISCO_OLAP                1,334       16    1,350    1.19
OLTS_BATTRSTORE                      15        0       15    1.67
OLTS_ATTRSTORE                       15        0       15    1.67
UNDO_TBS                          3,164      522    3,686   14.16
APPS_OMO                            853      297    1,150   25.82
MTR                                   2        2        4   40.63
OLTS_DEFAULT                          2        2        3   50.00
WCRSYS_TS                             1        2        3   54.17
OCATS                                 1        2        3   56.25
APPS_CALCLIP                      2,040    2,730    4,770   57.24
CTXSYS                               30       49       79   62.34
DISCO_PTM5_META                       1        2        3   62.50
APPS_TS_NOLOGGING                   200      375      575   65.17
OLTS_SVRMGSTORE                       1        2        3   70.83
ODM_DATA                             10       30       40   75.94
OPMOR                                 1        5        6   76.04
DSGATEWAY_TAB                         2        5        7   77.68
BAM                                   2        6        8   78.13
RE                                    2        7        8   81.25
APPS_TS_ARCHIVE                     202      899    1,101   81.66
SYSAUX                              205    1,122    1,327   84.52
APPS_TS_QUEUES                      613    3,686    4,299   85.75
APPS_TS_TX_IDX                    3,918   28,553   32,471   87.93
B2B_LOB                               1       11       12   88.54
APPS_TS_SEED                        433    3,370    3,803   88.62
APPS_TS_TX_DATA                   7,061   55,631   62,692   88.74
B2B_IDX                               2       14       16   89.06
OLTS_CT_STORE                         1        7        8   89.06
B2B_DT                                7       61       68   89.43
XDB                                   6       55       61   89.75
APPS_TS_DISCO                        86      770      856   89.92
GEOR_TBS                            215    1,922    2,136   89.96
DCM                                  20      178      198   89.96
HTMLDB                               29      258      287   89.98
APPS_TS_SUMMARY                   2,239   20,130   22,369   89.99
SYSTEM                            1,927   17,370   19,297   90.01
IAS_META                             22      199      221   90.05
APPS_TS_INTERFACE                   236    2,168    2,404   90.18
SYNCSERVER                            5       49       54   90.21
DISCO_PTM5_CACHE                     11       97      108   90.22
B2B_RT                                4       38       42   90.33
APPS_TS_MEDIA                       497    4,715    5,211   90.47
RBS_MIG                               4       39       43   90.70
UDDISYS_TS                            2       19       21   90.77
CWMLITE                               2       21       23   91.85
ORABPEL                               1       11       12   95.31

69 rows selected.

Elapsed: 00:24:10.53

(Does not include temp tablespaces.)
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: