Page History

...

• Prerequisites for configuring the PMDB in an SQL Server
• Prerequisites for configuring the PMDB in an Oracle Server
• Additional DBA settings
• How to disable the recycle bin in Oracle version 10g
• Enabling Automatic Undo Management (AUM)
• About backing up the database and creating archiving
• About maintenance operations
• About changing the size of tables and index extents

...

Install the Precise environment before configuring the SQL Server-based PMDB. Before applying the information in this section, refer to the Precise Installation Guide.
NOTE    SQL Server 2000 is not supported in v9.0, so an upgrade is not available for such installations Installing Precise Components.

For an SQL Server-based PMDB, use an MS-SQL dedicated instance.

To avoid using the MS-SQL fully automatic configuration function, change the following parameters:•    Maximum

• Maximum server memory (MB). Should be 80% of the server's RAM.

...

• Minimum memory per query (KB). Should be 512 instead of 1024.

An example to configure a parameter is the following command from the Query Analyzer:

EXEC sp_configure 'max server memory (MB)', [new value] RECONFIGURE WITH OVERRIDE
GO

Maintenance operations

Check the table and index status by running the following command:

dbcc showcontig ({table name}) with all_indexes

The results should be that the " Extent Scan Fragmentation " is less than 50 and the " Logical Scan Fragmentation " is low; otherwise, it will damage the index scans.

If one of the above conditions is confirmed, perform a reorganization on the indexes with the following command:Dbcc

dbcc dbreindex ('{databasename.tableName}')

...

...

Backing up the database and creating archiving

Use the Database Maintenance Plan function to create a backup plan for the database. Verify that the SQL Server agent is automatically started at database startup.

If you choose the simple recovery mode for the database (the transaction log can be deleted after a commit or checkpoint), the recovery will not be able to cannot work to a point in time, but only to the backup time.

If you choose a full recovery mode for the database, verify that you also backup the transaction log and shrink the database periodically, to stop the transaction log from growing infinitely.

If you do not create a backup, information will be is lost when you have a database crash and you will not be able to cannot recover data.

Additional DBA settings for an SQL Server-based PMDB

To improve performance, the DBA on site must check that the settings in the following table contain the correct values.

Table 6-1    Additional 1 Additional DBA settings for the SQL Server-based PMDBSetting    Description    Value

...

...

...

...

...

...

...

...

Anchor
PreqFor ConfigOracle PreqFor ConfigOracle

Prerequisites for configuring the PMDB in an Oracle Server

Install the Precise environment before configuring the Oracle-based PMDB. Before applying the information in this section, refer to the Precise Installation Guide. For an Oracle-based PMDB, use an Oracle dedicated instance.

Verify that you are creating the database with the proper NLS_CHARACTERSET. When the monitored Oracle instances use a multiple byte character set, use UTF8. AL32UTF8 is not supported by the PMDB.

The configuration that is recommended when the PMDB is based on an Oracle database can be found in the following paragraphs. For updated information, also see: http://my.precise.com/

If you install the PMDB on an Oracle legacy database, verify that the database block size is at least 8 KB. If not, try to install the PMDB on a different Oracle instance.

The following table specifies the database block size prerequisite.

Table 6-2    Oracle 2 Oracle Server Database block size prerequisite
Parameter    Value    Improvement

...

...

...

...

...

Required INIT.ORA changes

To improve performance, change some parameter values and verify that the values match those listed in the appropriate table.

The parameter values can be changed on a new and on an existing Oracle instance. The following tables describe the general and Oracle version specific parameters.

Table 6-3    General 3 General parameters for the Oracle-based PMDBParameter    Value    Improvement

...

...

...

...

...

INIT.ORA example

The following is an example of the init.ora file for a an Oracle 912.2 server with 2 GB RAM and 2 disksinstance.

db_

...

name='ORCL' memory_target=1G processes=300 db_block_size=8192
db_domain='' db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300

...

remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
undo_retention=900 # 15 minutes


...

# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='12.0.0'

Anchor
DisableRecycleBin DisableRecycleBin

How to disable the recycle bin in Oracle

...

Our recommendation is to disable the recycle bin and this section describes the background and the action on how to disable the recycle bin.

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

The following information is taken from the Oracle Database Administration Guide. In version 10.0 and 10.1 use the following command:

alter system set "_recyclebin"=FALSE scope=BOTH;

In version 10.2, use the following command:

alter system set recyclebin=off scope=BOTH

Anchor
BackingUpCreatingArchiving BackingUpCreatingArchiving

About backing up the database and creating archiving

The PMDB holds information for up to three years by default. If you want to backup this information, you should enable the Redo log archiving, and create a backup using RMAN. For more information, refer to the official Oracle Backup and Recovery documentation. If you do not create a backup, information will be lost when you have a database crash and you will not be able to recover data. If you create a backup without using the archive mode, you will be able to recover data to the time of the backup. All new operations (after the logs were archived) will be lost.

Anchor
EAM EAM

Enabling Automatic Undo Management (AUM)

To enable Automatic Undo Management (AUM), you must create a new tablespace and set the init.ora parameters. For additional information on how to enable AUM, see the Oracle Database Administrator's Guide, “Managing "Managing Undo Space.”"

To enable Automatic Undo Management (AUM)1.    Create

1. Create the undo tablespace by running the following command:
   CREATE UNDO TABLESPACE tablespace_name DATAFILE 'datafile name' SIZE 32M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL

...

1. Set the following init.ora parameters:

...

1. • undo_management = AUTO

...

1. • undo_tablespace = tablespace_name

...

1. • undo_retention = 900

If the undo tablespace size is too large, you may decrease the number of seconds.

...

The PMDB holds information for up to 3 years by default. If you want to backup this information, you should enable the Redo log archiving, and create a backup using RMAN or the Precise backup utility. For more information, refer to the official Oracle Backup and Recovery documentation.

If you do not create a backup, information will be lost when you have a database crash and you will not be able to recover data. If you create a backup without using the archive mode, you will be able to recover data to the time of the backup. All new operations (after the logs were archived) will be lost.

Anchor
AboutMaintenance AboutMaintenance

About maintenance operations

...

The following table shows the default extents sizes.

Table 6-8 Default 4 Default extents sizes

...

Precise. Performance intelligence from click to storage. Learn more > >

...

...