Archive

Archive for the ‘Oracle 11g’ Category

Oracle – Disable dbms gather_stats_job or optimizer stats


To disable gather_stats_job in 10g:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

To disable gather_stats_job (also known as optimizer stats) in 11g:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

Oracle – MMON


MMON (Memory Monitor) is an Oracle background process that gathers memory statistics (snapshots) and stores this information in the AWR.

MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

MMON is available from Oracle 10g.

The default for recording an AWR snapshot is hourly.

Oracle – RMAN 11g List Failure


The 11g version of RMAN has a useful feature for diagnosing and repairing any failures.

Find any database failures:

RMAN> list failure;

Determine manual or automatic repair:

RMAN> advise failure;

From the output of the advise, try to manually fix the failure. If this fails to work, then try the repair command:

RMAN> repair failure;

Further Reading

Oracle® Database Backup and Recovery User’s Guide

Oracle – Find users with DBA privilege

July 20, 2011 2 comments

If you wish to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema.

This role tells you the grantee, granted_role, whether they have admin option granted, and whether the role is their default role:

SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Further Reading

http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_2291.htm

Oracle – Check Auditing Policy


To check what auditing has been set on your Oracle database you can query the following:

SQL> SELECT *
FROM dba_stmt_audit_opts;

Further Reading

DBA_STMT_AUDIT_OPTS
Auditing in Oracle 10g Release 2

AIX – Error when installing Fusion Middleware 11.1.1.5.0 on AIX 7.1

July 15, 2011 2 comments

If you get the below error message when going to run the installer for fusion middleware 11.1.1.5.0:

Checking Operating System Certification:
Expected result : One of 5300.08, 6100.02
Actual Result: 7100.00
Check Complete: The overall result of this check is Failed <<<<
Problem : This Oracle Software is not certified on the current operating system.
Recommendation: Make sure you are installing the software on the correct platform.
-Checking recommended operating system patches:
Check complete. The overall result of this check is : Not executed

Then you can either ignore the error by pressing continue, or you can quit and run the installer with the following:

./runInstaller -ignoreSysPrereqs

Follow

Get every new post delivered to your Inbox.

Join 31 other followers