SystemGuardian for Oracle

Outstanding Problems

(by SystemGuard, a division of Skyridge Systems Inc.)

Customer:   ACME Widgets Inc.
Period:   2003/02/01 through 2003/03/01

Databases in this report:

  1. dev
  2. prod

Database: dev

Test Message Quantity
OTS-1-1 Constraint does not have desired status. 2
OTS-5-1 Object status is not VALID. 1
OTS-10-1 Index is in the SYSTEM tablespace. 2
OTS-12-1 Synonym is invalid. 1
OTS-57-1 Segment has too many extents. 4
OTS-74-1 Privileges on object granted directly to PUBLIC. 1
OTS-81-1 Table resides in the SYSTEM tablespace. 2
OTS-108-1 Temporary tablespace has a PCTINCREASE value that is not 0. 1

Test: OTS-1-1
Message: Constraint does not have desired status.
Background: Constraints are used on tables within the database to enforce data integrity. They can be enabled or disabled. Constraints enforce data integrity by insuring that data being inserted into a table is valid against one or more rules. For instance, a unique constraint insures that all rows inserted in a table can by uniquely identified by the columns that the unique constraint is on. If a constraint is disabled, the database will allow data to be inserted into the table even though it violates the rules of the constraint.
Recommendation: Change the status of the constraint to the desired status and resolve any data integrity issues resulting from the status being incorrect.

Constraint has a status of DISABLED, desired value is ENABLED.
Item: constraintName=R_ABU_TRANS_C3, owner=APP_ABU
Parameters: desiredValue=ENABLED
Additional Info: ConstraintStatus=DISABLED
Detected at: 2003/01/11 23:56:21
Database: dev
Test: OTS-1-1
Ref. #: 4-00040937


Constraint has a status of DISABLED, desired value is ENABLED.
Item: constraintName=X_APK_C1, owner=ADMIN
Parameters: desiredValue=ENABLED
Additional Info: ConstraintStatus=DISABLED
Detected at: 2003/01/13 23:56:21
Database: dev
Test: OTS-1-1
Ref. #: 4-00040936

Test: OTS-5-1
Message: Object status is not VALID.
Background: Objects within the database can be invalid, and therefore not available to be used. An invalid object will usually be a view, trigger or package that needs to be recompiled because another object that it references has changed.
Recommendation: Validate the specified object by recompiling or recreating the object as appropriate.

Object status is INVALID, desired status is VALID.
Item: objectName=PAYROLL_VIEW, objectType=VIEW, owner=FINANCE
Parameters: None.
Additional Info: ObjectStatus=INVALID
Detected at: 2003/02/14 23:41:46
Database: dev
Test: OTS-5-1
Ref. #: 4-00067700

Test: OTS-10-1
Message: Index is in the SYSTEM tablespace.
Background: Indexes contain selected columns from tables and therefore are added to, deleted from and updated whenever the table they index is added to, deleted from or updated. For this reason, it is generally accepted that indexes should not reside in the SYSTEM tablespace, unless owned by an Oracle built-in user. This is to help ensure that needless fragmentation of the SYSTEM tablespace does not occur.
Recommendation: Move the index to an appropriate tablespace, if possible.

Index is in the SYSTEM tablespace.
Item: indexName=DR1_I, owner=DROY
Parameters: None.
Additional Info: None.
Detected at: 2002/11/18 23:41:34
Database: dev
Test: OTS-10-1
Ref. #: 4-00091735


Index is in the SYSTEM tablespace.
Item: indexName=DR2_I, owner=DROY
Parameters: None.
Additional Info: None.
Detected at: 2002/11/18 23:41:34
Database: dev
Test: OTS-10-1
Ref. #: 4-00091736

Test: OTS-12-1
Message: Synonym is invalid.
Background: Synonyms are logical names that refer to objects, such as tables or views, that exist within the database. Synonyms become invalid when the object they reference becomes inaccessible for any reason. Although invalid synonyms do not cause database problems, they may indicate that the referenced object has been inadvertently dropped or the privileges granted on the referenced object have changed. In the case of a chain of synonyms in which the final synonym in the chain is invalid, only the final synonym is reported.
Recommendation: Drop the synonym if it is no longer required, or fix the problem with the referenced object so that the synonym becomes valid.

Synonym is invalid.
Item: owner=ADMIN, synonymName=XYZ_SYN
Parameters: None.
Additional Info: ReferencedObject=XYZ_OBJ, ReferencedObjectOwner=ADMIN
Detected at: 2002/11/19 23:52:37
Database: dev
Test: OTS-12-1
Ref. #: 4-00104852

Test: OTS-57-1
Message: Segment has too many extents.
Background: Segments, such as tables or indexes, are stored in logical containers in the database called tablespaces. When a segment fills and requires additional space to store data, the database allocates a number of blocks in the tablespace to the segment according to the segment's next extent size. Although there is no problem associated with a segment that has many extents, it is often desirable to know. For example, if a segment acquires a large number of extents in a relatively short period of time, the segment could be in danger of reaching its maximum number of extents. Also, a large number of segments that are obtaining new extents very frequently could adversely affect database performance. The NEXT storage parameter for the segment indicates the size of the extent that will be allocated by the segment the next time it extends.
Recommendation: Optimize the value of the NEXT storage parameter for the segment and unload and reload the segment.

Segment currently has 121 extents, limit is 100.
Item: owner=MATT, segmentName=XYZ, segmentType=TABLE
Parameters: extentsLimit=100
Additional Info: SegmentExtents=121
Detected at: 2002/11/07 23:48:14
Database: dev
Test: OTS-57-1
Ref. #: 4-00113546


Segment currently has 112 extents, limit is 100.
Item: owner=JOEC, segmentName=CUSTOMER, segmentType=TABLE
Parameters: extentsLimit=100
Additional Info: SegmentExtents=112
Detected at: 2002/11/12 23:43:18
Database: dev
Test: OTS-57-1
Ref. #: 4-00113760


Segment currently has 107 extents, limit is 100.
Item: owner=RBAKER, segmentName=TEMP, segmentType=TABLE
Parameters: extentsLimit=100
Additional Info: SegmentExtents=107
Detected at: 2002/11/14 23:48:22
Database: dev
Test: OTS-57-1
Ref. #: 4-00113809


Segment currently has 139 extents, limit is 100.
Item: owner=BOBF, segmentName=CUSTOMER, segmentType=TABLE
Parameters: extentsLimit=100
Additional Info: SegmentExtents=139
Detected at: 2002/11/28 23:57:08
Database: dev
Test: OTS-57-1
Ref. #: 4-00047411

History:
  Date: 2003/02/26 23:23:01
  (current)
 
  Date: 2003/02/25 23:58:14
  Message: Segment currently has 127 extents, limit is 100.
  Parameters: extentsLimit=100
  Additional Info: SegmentExtents=127
 
  Date: 2002/11/28 23:57:08
  Message: Segment currently has 115 extents, limit is 100.
  Parameters: extentsLimit=100
  Additional Info: SegmentExtents=115

Test: OTS-74-1
Message: Privileges on object granted directly to PUBLIC.
Background: All privileges granted to PUBLIC are inherently granted to all users in the database. While privileges granted to PUBLIC do not pose a direct security threat in a database that is reasonably secure, the list of privileges granted to PUBLIC should be frequently reviewed to ensure that inappropriate privileges have not been inadvertently granted to all users. By default, objects owned by Oracle built-in users are not checked, since many objects owned by those users have grants to PUBLIC.
Recommendation: Review privileges on database objects that are granted to PUBLIC.

Privileges on object granted directly to PUBLIC: SELECT.
Item: objectName=USER_PRIMARY_KEY, objectType=VIEW, owner=ADMIN
Parameters: None.
Additional Info: ListOfPrivileges=SELECT
Detected at: 2003/02/11 23:57:48
Database: dev
Test: OTS-74-1
Ref. #: 4-00041328

Test: OTS-81-1
Message: Table resides in the SYSTEM tablespace.
Background: It is generally accepted that user and application tables should not reside in the SYSTEM tablespace. This is to help ensure that needless fragmentation of the SYSTEM tablespace does not occur. Tables owned by Oracle built-in users are ignored.
Recommendation: Move the table to an appropriate tablespace, if possible.

Table resides in the SYSTEM tablespace.
Item: owner=DROY, tableName=DR1_T
Parameters: None.
Additional Info: None.
Detected at: 2002/11/04 23:49:34
Database: dev
Test: OTS-81-1
Ref. #: 4-00129710


Table resides in the SYSTEM tablespace.
Item: owner=DROY, tableName=DR2_T
Parameters: None.
Additional Info: None.
Detected at: 2002/11/04 23:49:35
Database: dev
Test: OTS-81-1
Ref. #: 4-00129711

Test: OTS-108-1
Message: Temporary tablespace has a PCTINCREASE value that is not 0.
Background: Temporary tablespaces are used when large sorts are required to service statements issued by database users. These sorts create temporary segments for the duration of the sort and then destroy these segments when the sort is done. If the storage parameters for the temporary tablespace are incorrect, it can cause heavy fragmentation of the tablespace. Any tablespace specified as the temporary tablespace for one or more users, except for the SYSTEM tablespace, as well as all tablespaces flagged as temporary, are checked. The SYSTEM tablespace is not checked because it is generally accepted that no user should have a temporary tablespace of SYSTEM.

The PCTINCREASE default storage parameter specifies, as a percentage, how much larger the next extent allocated for the segment is than the last extent that was allocated for the segment. It is generally accepted that this value should always be 0 for temporary tablespaces so that each extent allocated is the same size as the last. This results in greatly reducing the rate at which the temporary tablespace becomes fragmented.
Recommendation: Set the PCTINCREASE default storage parameter for the tablespace to 0.

The temporary tablespace has a PCTINCREASE value of 50, desired value is 0.
Item: tablespaceName=TEMP3
Parameters: None.
Additional Info: PctIncrease=50
Detected at: 2003/02/03 23:51:02
Database: dev
Test: OTS-108-1
Ref. #: 4-00129712


Database: prod

Test Message Quantity
OTS-5-1 Object status is not VALID. 2
OTS-6-1 Data block buffer cache hit ratio is too low. 1
OTS-7-2 Storage available for too few additional extents. 1
OTS-12-1 Synonym is invalid. 2
OTS-14-3 User's default tablespace does not have the desired status. 2
OTS-60-2 Tablespace has too little free space. 1
OTS-78-1 DBA level privileges granted directly to user. 1
OTS-96-1 Table has no primary key. 3
OTS-101-1 User still has default password. 1

Test: OTS-5-1
Message: Object status is not VALID.
Background: Objects within the database can be invalid, and therefore not available to be used. An invalid object will usually be a view, trigger or package that needs to be recompiled because another object that it references has changed.
Recommendation: Validate the specified object by recompiling or recreating the object as appropriate.

Object status is INVALID, desired status is VALID.
Item: objectName=V_MJM_WARD_STOCK, objectType=VIEW, owner=ADMIN
Parameters: None.
Additional Info: ObjectStatus=INVALID
Detected at: 2002/11/14 23:41:46
Database: prod
Test: OTS-5-1
Ref. #: 4-00067708


Object status is INVALID, desired status is VALID.
Item: objectName=V_REMOTE_WARD, objectType=VIEW, owner=ADMIN
Parameters: None.
Additional Info: ObjectStatus=INVALID
Detected at: 2002/11/14 23:41:46
Database: prod
Test: OTS-5-1
Ref. #: 4-00067709

Test: OTS-6-1
Message: Data block buffer cache hit ratio is too low.
Background: The data block buffer cache is a component of the SGA (System Global Area) used to hold data blocks that are read from segments in the database. When data is accessed, it must be read from disk and stored in the data block buffer cache before it is passed on to the process that requested it, unless the requested data is already in the cache. The data block buffer cache is managed using a least recently used (LRU) algorithm. This means that the most frequently requested data should always remain in the data block buffer cache. A low data block buffer hit ratio indicates that the cache is not large enough to store the most frequently accessed data in the database, resulting in re-reading it from disk.
Recommendation: Add additional block buffers to the data block buffer cache by increasing the value of DB_BLOCK_BUFFERS in the init.ora file and restart the database for the change to take effect.

Data block buffer cache hit ratio is 72.53782%, limit is 95.0%.
Parameters: behavior=AUTO, bufferHitRatioLimit=95.0%
Additional Info: ConsistentGets=1, DBBlockGets=1, DataBlockBufferCacheHitRatio=72.53782%, PhysicalReads=1
Detected at: 2003/02/02 23:55:28
Database: prod
Test: OTS-6-1
Ref. #: 4-00040364

History:
  Date: 2003/02/04 23:48:27
  (current)
 
  Date: 2003/02/03 23:45:32
  Message: Data block buffer cache hit ratio is 77.83819%, limit is 95.0%.
  Parameters: behavior=AUTO, bufferHitRatioLimit=95.0%
  Additional Info: ConsistentGets=1, DBBlockGets=1, DataBlockBufferCacheHitRatio=77.83819%, PhysicalReads=1
 
  Date: 2003/02/02 23:55:28
  Message: Data block buffer cache hit ratio is 82.28919%, limit is 95.0%.
  Parameters: behaviour=AUTO, bufferHitRatioLimit=95.0%
  Additional Info: ConsistentGets=2236389, DBBlockGets=766941, DataBlockBufferCacheHitRatio=82.28919%, PhysicalReads=1332419

Test: OTS-7-2
Message: Storage available for too few additional extents.
Background: Segments, such as tables or indexes, are stored in logical containers in the database called tablespaces. When a segment fills its allocated space, the database allocates a new extent to the segment in the tablespace in which it resides. An extent consists of a number of database blocks from a tablespace and can only be used by the segment to which it has been allocated. The size of extent allocated to the segment is dependent on the segment's NEXT_EXTENT and PCT_INCREASE storage parameter values.

The segment specified is not able to obtain the specified number of extents because there is not sufficient contiguous free space within the tablespace.
Recommendation: Review the segment's storage parameters and either reduce the size of subsequent extents or allocate additional space to the tablespace, as appropriate.

Storage available for 2 additional extents, limit is 5.
Item: owner=APP1, segmentName=COMPANY_INFO, segmentType=TABLE
Parameters: availableExtentsLimit=5
Additional Info: AdditionalExtents=2, CurrentExtents=5, NextExtent=1024000, PercentIncrease=0, TablespaceName=APP1_T
Detected at: 2003/02/16 23:57:28
Database: prod
Test: OTS-7-2
Ref. #: 4-00044638

History:
  Date: 2003/02/18 23:58:57
  (current)
 
  Date: 2003/02/16 23:57:28
  Message: Storage available for 3 additional extents, limit is 5.
  Parameters: availableExtentsLimit=5
  Additional Info: AdditionalExtents=3, CurrentExtents=5, NextExtent=1024000, PercentIncrease=0, TablespaceName=APP1_T

Test: OTS-12-1
Message: Synonym is invalid.
Background: Synonyms are logical names that refer to objects, such as tables or views, that exist within the database. Synonyms become invalid when the object they reference becomes inaccessible for any reason. Although invalid synonyms do not cause database problems, they may indicate that the referenced object has been inadvertently dropped or the privileges granted on the referenced object have changed. In the case of a chain of synonyms in which the final synonym in the chain is invalid, only the final synonym is reported.
Recommendation: Drop the synonym if it is no longer required, or fix the problem with the referenced object so that the synonym becomes valid.

Synonym is invalid.
Item: owner=SYS, synonymName=DEF$_SCHEDULE
Parameters: None.
Additional Info: ReferencedObject=DEF$_SCHEDULE, ReferencedObjectOwner=SYSTEM
Detected at: 2001/02/09 00:04:44
Database: prod
Test: OTS-12-1
Ref. #: 4-00046007


Synonym is invalid.
Item: owner=ADAM, synonymName=ABU_USER_OLD
Parameters: None.
Additional Info: ReferencedObject=ABU_USER_OLD, ReferencedObjectOwner=APP_ABU
Detected at: 2001/02/09 00:04:44
Database: prod
Test: OTS-12-1
Ref. #: 4-00046008

Test: OTS-14-3
Message: User's default tablespace does not have the desired status.
Background: A user's default tablespace is the tablespace in which all tables and indexes are created, unless another tablespace is specified at the object's creation time.

In order for a user to read and write data to a segment in their default tablespace, the tablespace must have a status of ONLINE. If the tablespace's status is READ ONLY, data can only be read from segments within it. If the tablespace has a status of OFFLINE, the user will be unable to access any data within the tablespace.
Recommendation: Alter the user's default tablespace to have the desired status or assign the user a new default tablespace with the appropriate status.

User's default tablespace of USERS_T has a status of READ_ONLY, desired status is ONLINE.
Item: username=MARTIN
Parameters: desiredStatus=ONLINE
Additional Info: DefaultTablespaceName=USERS_T, DefaultTablespaceStatus=READ_ONLY
Detected at: 2003/02/17 23:48:36
Database: prod
Test: OTS-14-3
Ref. #: 4-00129720


User's default tablespace of USERS_T has a status of READ_ONLY, desired status is ONLINE.
Item: username=JAMESQ
Parameters: desiredStatus=ONLINE
Additional Info: DefaultTablespaceName=USERS_T, DefaultTablespaceStatus=READ_ONLY
Detected at: 2003/02/17 23:48:36
Database: prod
Test: OTS-14-3
Ref. #: 4-00129721

Test: OTS-60-2
Message: Tablespace has too little free space.
Background: As part of normal database operation, segments grow within their tablespaces as data is added to them. When a tablespace does not have sufficient free space to allocate an extent to a segment, that segment will not be able to grow further until the tablespace is enlarged.
Recommendation: Allocate additional space to the tablespace by adding a new datafile or increasing the size of one or more existing datafiles.

14.572088% of space in the tablespace is free space, limit is 15.0%.
Item: tablespaceName=SYSTEM
Parameters: freeSpaceLimit=15.0%
Additional Info: TablespaceFreeSpace=14.572088%, TablespaceSize=184549376 bytes
Detected at: 2003/02/15 23:56:41
Database: prod
Test: OTS-60-2
Ref. #: 4-00043950

Test: OTS-78-1
Message: DBA level privileges granted directly to user.
Background: DBA level privileges are usually administrative privileges within the database. These privileges are considered a security risk and should only be granted to those users who require them. As such, care should be taken when granting these privileges to users. The list of privileges that are considered to be DBA level, by default, is documented and available upon request. Privileges granted to Oracle built-in users are not reported.
Recommendation: Revoke DBA level privileges from this user.

DBA level privileges granted directly to user: ALTER USER, CREATE ANY SYNONYM, CREATE USER, DROP USER, SELECT ANY TABLE, UNLIMITED TABLESPACE.
Item: username=ADMIN
Parameters: additionalPrivileges=null, excludedPrivileges=null
Additional Info: ListOfDBALevelPrivilegesHeld=ALTER USER, CREATE ANY SYNONYM, CREATE USER, DROP USER, SELECT ANY TABLE, UNLIMITED TABLESPACE
Detected at: 2003/01/19 23:57:27
Database: prod
Test: OTS-78-1
Ref. #: 4-00044956

History:
  Date: 2003/02/12 23:07:13
  (current)
 
  Date: 2003/01/19 23:57:27
  Message: DBA level privileges granted directly to user: ALTER USER, CREATE ANY SYNONYM, CREATE USER, DROP USER, SELECT ANY TABLE.
  Parameters: None.
  Additional Info: ListOfDBALevelPrivilegesHeld=ALTER USER, CREATE ANY SYNONYM, CREATE USER, DROP USER, SELECT ANY TABLE

Test: OTS-96-1
Message: Table has no primary key.
Background: Primary keys are a column or a group of columns that uniquely identify the rows in a table. It is generally accepted, with few exceptions, that all application tables should have primary keys. The process of defining a primary key helps to ensure that table data is represented in a normalized fashion. Tables owned by Oracle built-in users are ignored.
Recommendation: Determine which columns uniquely identify each row of the table and create a primary key using those columns.

Table has no primary key.
Item: owner=MSTEEVES, tableName=X1
Parameters: None.
Additional Info: None.
Detected at: 2003/02/08 00:02:27
Database: prod
Test: OTS-96-1
Ref. #: 4-00129722


Table has no primary key.
Item: owner=MSTEEVES, tableName=X2
Parameters: None.
Additional Info: None.
Detected at: 2003/02/08 00:02:27
Database: prod
Test: OTS-96-1
Ref. #: 4-00129723


Table has no primary key.
Item: owner=MSTEEVES, tableName=X3
Parameters: None.
Additional Info: None.
Detected at: 2003/02/08 00:02:27
Database: prod
Test: OTS-96-1
Ref. #: 4-00129724

Test: OTS-101-1
Message: User still has default password.
Background: When an Oracle database is created, accounts are created in the database for administrative and operational purposes. The default passwords for these accounts are widely known, so it is a substantial security risk to leave the default passwords in place. The most common and potentially dangerous of these accounts are SYS and SYSTEM. Other accounts with default passwords may exist, depending on the version of Oracle and the options installed.
Recommendation: Change the user's password to be something other than the default password.

User still has default password.
Item: username=DBSNMP
Parameters: None.
Additional Info: None.
Detected at: 2001/01/11 23:57:35
Database: prod
Test: OTS-101-1
Ref. #: 4-00041267

  SystemGuard, a division of Skyridge Systems Inc. SystemGuard, a division of Skyridge Systems Inc.  
196 Robinson Street Tel: 506-854-7997
Suite 201 Email: info@systemguard.com
Moncton, NB Web: www.systemguard.com
E1C 5C4
Canada