Motivation

As many database configuration changes are applied at the moment by hand, this can lead and actually leads to configuration divergence which is definitely not good from the service maintenance cost point of view. This is especially true for 10g databases for which Oracle provides many new features that should be enabled and are not or should be disabled as a potential source of problems and are not. The following section provides list of desired settings together with rationale and commands.

Desired configuration

Patch level

Rationale:

To minimize maintenance effort we should aim in keeping the service as homogenous as possible. In principle for each major Oracle release we should have only one patch(set) level. If there is a special need to keep somewhere an older version of the software or to install additional interim patches, it should be explicitly documented (e.g. on the machine list).

Commands:

$ sqlplus "/ as sysdba"
SQL> SELECT VERSION FROM V$INSTANCE; (10.1.0.4/9.2.0.6)
SQL> exit
$ $ORACLE_HOME/OPatch/opatch lsinventory (10g: opatch 10.0.0.52 / patch: 4210374) (9i: )

CERN roles, profiles and the password verification function

Rationale:

Some time ago customized CERN roles and profiles as well as password verification function have been introduced. All those features should be at least installed and possibly assigned to database users. Details concerning installation of mentioned features can be found here.

Commands:

SQL> select * from dba_roles where role like 'CERN%'; (two roles (CERN_APP_ROLE and CERN_DEV_ROLE) should be returned)
SQL> select distinct profile from dba_profiles where profile like 'CERN%'; (two profiles should be returned)
SQL> select profile, limit from dba_profiles where RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' and profile like 'CERN%'; ("VERIFY_FUNCTION" should be returned in the "LIMIT" column)

Monitoring

Rationale:

Currently we have few home-made monitoring tools. Not all of them are deployed everywhere. In principle is what we need is schema-level and quotas monitoring deployed in the USERMON schema. For information how to setup the monitoring look here.

Commands:

To check whether usermon account is deployed:
$ sqlplus "/ as sysdba"
SELECT * FROM DBA_USERS WHERE USERNAME='USERMON';

To determine whether schema-level resource consumption monitoring is enabled:
SQL> SHOW PARAMETER audit_trail (this parameter should be set to "DB")
SQL> SELECT count(*) FROM AUD$; (If this statement returns 0 it means that auditing is not enabled. Enable auditing with the AUDIT SESSION command.)
SQL> SELECT job, next_date, what FROM DBA_JOBS WHERE SCHEMA_USER='USERMON' AND WHAT LIKE 'daily_usermon_job%';

To determine whether quotas monitoring is deployed:
SQL> SELECT job, next_date, what FROM DBA_JOBS WHERE SCHEMA_USER='USERMON' AND WHAT LIKE 'cern_tools.ts_quotas_monitoring%';

Automatic shared memory management (10g only)

Rationale:

Automatic shared memory management optimized SGA allocations to different pools/caches. As this feature works quite well it is desired to enable it. Instructions how to setup the memory properly can be found here.

Commands:

SQL> show parameter pool; (parameters with underscore at the beginning should have values bigger than 0)

Recycle bin as problem-prune should be disabled (10g only)

Rationale:

As the recycle bin in current Oracle releases brings more problems than benefits it is better to disable it;

Commands:

SQL> select value from v$parameter where name='_recyclebin'; (should be set to 'false')
or
SELECT a.ksppinm Parameter, b.ksppstvl Val, b.ksppstdf Def
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm ;
To disable: SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH; (and re-start the database)

Standard accounts' passwords

Rationale:

To make the service maintenance easier passwords of all standard Oracle/Physics Services accounts should be the same for all databases. The following accounts should be taken into account: USERMON, DBSNMP, SYS, SYSTEM, PDB_ADMIN. Additionally DBSNMP account should be unlocked (by default it is locked).

Commands:

select username, account_status from dba_users where username in('USERMON','DBSNMP','SYS','SYSTEM','PDB_ADMIN') or account_status!='OPEN';

Session Manager

Rationale:

In the current service model Session Manager is the only way users can access information about their database session. It is also important for production managers who with Session Manager can monitor and eliminate troublesome sessions. Installation notes concerning Session Manager installation can be found here.

Commands:

To check whether Session Manager is configured:
SQL> select username from dba_users where username='WEBINSTANCE'; (if exists then should be configured)

Or go to the following page: http://it-des.web.cern.ch/IT-DES/DIS/oracle/session.html

AWR retention

Rationale:

Thanks to AWR snapshots it is possible to analyze historical load, wait events and etc for example from EM. As this feature is very useful and the default 7-days retention seems to be to short in many cases, the retention should be set to 30 days (43200 minutes).

Commands:

To check current AWR retention:
SQL> select retention from dba_hist_wr_control;
To set new retention:
SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 60, retention => 43200);

EM Configuration

Rationale:

Although all existing targets are automatically detected and registered in the EM repository after agent installation, some additional configuration is still required:

• names of the target have to be changed according to naming convention (to be discussed),
• proper password for the DBSNMP account needs to be set,
• targets have to be added to appropriate groups and registered to the e-mail notification service.

Commands:

To set the OEM agent environment run the following script which is created while agent is being configured:
. /ORA/dbs00/oracle/admin/scripts/oma_10.1.0
The ORACLE_HOME should be set to:
/ORA/dbs01/oracle/product/10.1.0/emagent10g
To check the agent version run: emctl status agent
To configure the agent with the current server, check if the /ORA/dbs01/oracle/product/10.1.0/emagent10g/sysman/config/emd.properties has the following values set:
REPOSITORY_URL=http://oms:4889/em/upload/
emdWalletSrcUrl=http://oms:4889/em/wallets/emd
If necessary edit this file and restart the agent:
emctl stop agent
emctl start agent
You can also upload the pending files to check it works:
emctl upload
To upgrade to 10.1.0.4 download the /ORADIST/product/linux/emgrid10g/p3731593_10104_LINUX.zip from oracata machine, unzip, stop agent and execute the following command:
cd 3731593/Disk1
./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc

Machine list consistency

Rationale:

The machine list should be correctly updated and consistent with the usage given to the machines. We should check anyway for:

• existing alias for the node
• responsible DBA
• description of the service

Data Guard compatibility

Rationale:

Taking advantage of databases' configuration verification also database ability to have a logical standby database should be checked.

Commands:

To check for objects with unsupported datatypes:
SQL> select * from dba_logstdby_unsupported;
To check for all tables that have no primary and no non-null unique indexes. Most of the tables displayed by this view are supported because their columns contain enough information to be maintained in a logical standby database:
SQL> select * from dba_logstdby_not_unique;