Setting up Consumer Groups and Categories with PL/SQL in the Database

How to set up Consumer Groups and Categories with PL/SQL in the Database

BEGIN

  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_CATEGORY(
     CATEGORY => 'dss',
     COMMENT => 'DSS consumer groups');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'critical_dss',
     CATEGORY => 'dss',
     COMMENT => 'performance-critical DSS queries');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'normal_dss',
     CATEGORY => 'dss',
     COMMENT => 'non performance-critical DSS queries');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'etl',
     CATEGORY => 'maintenance',
     COMMENT => 'data import operations');

  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

In addition to the consumer groups that you set up, the database contains predefined consumer groups. The DBA_RSRC_CONSUMER_GROUPS view displays information about consumer groups, and the DBA_RSRC_CATEGORIES view displays information about categories in the database.

Consumer Groups and Categories in an Oracle Database

SQL> SELECT consumer_group, category FROM DBA_RSRC_CONSUMER_GROUPS where
     consumer_group not like 'ORA%' ORDER BY category;

CONSUMER_GROUP                                   CATEGORY
---------------------------------------------  ---------------------------------------------
SYS_GROUP                                                   ADMINISTRATIVE
ETL_GROUP                                                   BATCH
BATCH_GROUP                                             BATCH
DSS_GROUP                                                   BATCH
CRITICAL_DSS                                              DSS
NORMAL_DSS                                               DSS
DSS_CRITICAL_GROUP                              INTERACTIVE
INTERACTIVE_GROUP                               INTERACTIVE
ETL                                                                  MAINTENANCE
LOW_GROUP                                                OTHER
OTHER_GROUPS                                          OTHER
AUTO_TASK_CONSUMER_GROUP          OTHER
DEFAULT_CONSUMER_GROUP               OTHER

13 rows selected

SQL Plan Management (Part 1) Tutorial

Just posted a video tutorial on YOUTUBE for SQL PLAN MANAGEMENT (Part 1) Click here to see it. The rest parts will be posted soon, For the moment enjoy the part 1 by clicking here

See SQL Plan Management (Part 1) Tutorial  

Managing Users and Authentication

Managing Users and Authentication

When it comes to security of users and authentication often Linux Admins can overlook and invoke a big security hole. Check the system for unused and unlocked user accounts on a regular basis, and set passwords on any accounts that aren't protected. Make sure that no non-root user accounts have the user ID of 0.

When you install software like oracle or any other software that creates a default user account and password, be sure to change the vendor's default password immediately. A centralized user authentication method (such as OpenLDAP or other LDAP implementations) can help to simplify user authentication and management tasks, which might help to lower the risk of unused accounts or accounts with null passwords.

To tell exactly who has performed a privileged administrative action, set up the system so it is not possible to log in directly as root. Instead, all administrators should log in to the system first as a named user and then use the su or sudo commands to perform tasks as root. To prevent users from logging in as root directly, edit the /etc/passwd file, changing the shell from /bin/bash to /sbin/nologin. Modify the /etc/sudoers file using visudo to grant specific users authority to perform administrative tasks.

Oracle Linux supports PAM, which makes it easier to enforce strong user authentication and password policies, including password complexity, length, age, expiration rules. PAM also prevents the use of previous passwords. It can be configured to block user access after too many failed login attempts, after normal working hours, or if too many concurrent sessions are opened. PAM is highly customizable by adding different modules, and you can add external password integrity checkers to test password strength.

Linux NTPD - Network Time Protocol (NTP) daemon

Linux NTPD  - Network Time Protocol (NTP) daemon

SYNOPSIS
       ntpd [ -46aAbdDgLnNqx ] [ -c conffile ] [ -f driftfile ] [ -i jaildir ] [ -k keyfile ] [ -l logfile ] [ -p pidfile ] [ -P priority ] [ -r broad-
       castdelay ] [ -s statsdir ] [ -t key ] [ -u user[:group] ] [ -v variable ] [ -V variable ]

DESCRIPTION

       The ntpd program is an operating system daemon which sets and maintains the system time of  day  in  synchronism  with  Internet  standard  time  servers. It is a complete implementation of the Network Time Protocol (NTP) version 4, but also retains compatibility with version 3, as defined
       by RFC-1305, and version 1 and 2, as defined by RFC-1059 and RFC-1119, respectively. ntpd does most computations in 64-bit floating point arith- metic  and  does  relatively clumsy 64-bit fixed point operations only when necessary to preserve the ultimate precision, about 232 picoseconds.
       While the ultimate precision is not achievable with ordinary workstations and networks of today, it may be required with  future  gigahertz  CPU  clocks and gigabit LANs.

HOW NTP OPERATES

       The ntpd program operates by exchanging messages with one or more configured servers at designated poll intervals. When started, whether for the first or subsequent times, the program requires several exchanges from the majority of these servers so the  signal  processing  and  mitigation  algorithms  can accumulate and groom the data and set the clock. In order to protect the network from bursts, the initial poll interval for each server is delayed an interval randomized over a few seconds. At the default initial poll interval of 64s, several minutes can elapse before  the
       clock  is set. The initial delay to set the clock can be reduced using the iburst keyword with the server configuration command, as described on the Configuration Options page.

       Most operating systems and hardware of today incorporate a time-of-year (TOY) chip to maintain the time during periods when the  power  is  off. When  the  machine  is booted, the chip is used to initialize the operating system time. After the machine has synchronized to a NTP server, the
       operating system corrects the chip from time to time. In case there is no TOY chip or for some reason its time  is  more  than  1000s  from  the  server  time,  ntpd  assumes something must be terribly wrong and the only reliable action is for the operator to intervene and set the clock by
       hand. This causes ntpd to exit with a panic message to the system log. The -g option overrides this check and the  clock  will  be  set  to  the server  time  regardless  of  the  chip  time. However, and to protect against broken hardware, such as when the CMOS battery fails or the clock  counter becomes defective, once the clock has been set, an error greater than 1000s will cause ntpd to exit anyway.


   Under ordinary conditions, ntpd adjusts the clock in small steps so that the timescale is effectively continuous  and  without  discontinuities.  Under  conditions  of extreme network congestion, the roundtrip delay jitter can exceed three seconds and the synchronization distance, which is
       equal to one-half the roundtrip delay plus error budget terms, can become very large. The ntpd algorithms discard sample offsets  exceeding  128  ms,  unless the interval during which no sample offset is less than 128 ms exceeds 900s. The first sample after that, no matter what the offset,       steps the clock to the indicated time. In practice this reduces the false alarm rate where the clock is stepped in error to  a  vanishingly  low incidence.

       As  the  result  of this behavior, once the clock has been set, it very rarely strays more than 128 ms, even under extreme cases of network path  congestion and jitter. Sometimes, in particular when ntpd is first started, the error might exceed 128 ms. This may on occasion cause the  clock
       to  be set backwards if the local clock time is more than 128 s in the future relative to the server. In some applications, this behavior may be   unacceptable. If the -x option is included on the command line, the clock will never be stepped and only slew corrections will be used.

       The issues should be carefully explored before deciding to use the -x option. The maximum slew rate possible is limited to 500 parts-per-million  (PPM)  as a consequence of the correctness principles on which the NTP protocol and algorithm design are based. As a result, the local clock can
       take a long time to converge to an acceptable offset, about 2,000 s for each second the clock is  outside  the  acceptable  range.  During  this  interval  the  local  clock  will not be consistent with any other network clock and the system cannot be used for distributed applications that  require correctly synchronized network time.

       In spite of the above precautions, sometimes when large frequency errors are present the resulting time offsets stray outside the  128-ms  range  and  an  eventual step or slew time correction is required. If following such a correction the frequency error is so large that the first sample
       is outside the acceptable range, ntpd enters the same state as when the ntp.drift file is not present. The intent of this behavior is to quickly  correct  the  frequency  and  restore operation to the normal tracking mode. In the most extreme cases (time.ien.it comes to mind), there may be  occasional step/slew corrections and subsequent frequency corrections. It helps in these cases to use the burst  keyword  when  configuring  the  server.

Audit_sys_operations in Oracle 12C Database

A very important update in Oracle 12C database is that audit_sys_operations is set to true, that means all actions performed by super user sys will be audited. So the actions would be going to and being recorded at location specified by initialization paramter audit_file_dest.


SQL> show parameter audit_sys_operations

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     TRUE


The explanation of both these initialization parameters is given below :-

AUDIT_SYS_OPERATIONS enables or disables the auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges. The audit records are written to the operating system's audit trail. The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to XML.

On UNIX platforms, if the AUDIT_SYSLOG_LEVEL parameter has also been set, then it overrides the AUDIT_TRAIL parameter and SYS audit records are written to the system audit log using the SYSLOG utility.


AUDIT_FILE_DEST specifies the operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os, xml, or xml,extended. The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to XML. It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_SYS_OPERATIONS initialization parameter, audit records for user SYS.

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/prd/adum
                                                 p
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle

[oracle@node4 ~]$ cd /u01/app/oracle/admin/prd/adump/

[oracle@node4 adump]$ ls -ltr
total 52
-rw-r----- 1 oracle oinstall  772 Jul 26 22:39 prd_ora_10764_1.aud
-rw-r----- 1 oracle oinstall  772 Jul 26 22:40 prd_ora_10929_1.aud
-rw-r----- 1 oracle oinstall  980 Jul 26 22:40 prd_ora_10574_1.aud
-rw-r----- 1 oracle oinstall  755 Jul 26 22:40 prd_ora_10943_1.aud
-rw-r----- 1 oracle oinstall  762 Jul 26 22:40 prd_ora_10943_2.aud
-rw-r----- 1 oracle oinstall 1463 Jul 26 22:41 prd_ora_11017_1.aud
-rw-r----- 1 oracle oinstall 2384 Jul 26 22:42 prd_ora_11086_1.aud
-rw-r----- 1 oracle oinstall 3227 Jul 26 22:58 prd_ora_11216_1.aud
-rw-r----- 1 oracle oinstall 2140 Jul 26 22:59 prd_ora_14010_1.aud
-rw-r----- 1 oracle oinstall  755 Jul 26 22:59 prd_ora_14052_1.aud
-rw-r----- 1 oracle oinstall  768 Jul 26 22:59 prd_ora_14126_1.aud
-rw-r----- 1 oracle oinstall  762 Jul 26 22:59 prd_ora_14052_2.aud
-rw-r----- 1 oracle oinstall  772 Jul 26 22:59 prd_ora_14226_1.aud

[oracle@node4 adump]$
[oracle@node4 adump]$ cat prd_ora_14226_1.aud
....
......
........
..............
...................
System name:    Linux
Node name:      node4.oracle.com
Release:        2.6.32-100.26.2.el5
Version:        #1 SMP Tue Jan 18 20:11:49 EST 2011
Machine:        x86_64
Instance name: prd
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 14226, image: oracle@node4.oracle.com (TNS V1-V3)

Sun Jul 26 22:59:07 2015 +05:30
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '1924794093'


## Above is a audit record which specifies that mandatory auditing of the sysdba and its a login record

Preventing the Audit of Analyze Index in Oracle

As audit records become very huge sometimes it becomes very important to ignore analyze index auditing and make sure to audit create and drop index only instead of analyze index which consumes too much space sometimes.

SQL> audit analyze index;
audit analyze index
      *
ERROR at line 1:
ORA-00956: missing or invalid auditing option

The meaning of the error is

[oracle@node4 ~]$ oerr ora 956
00956, 00000, "missing or invalid auditing option"
// *Cause:  AUDIT or NOAUDIT statement contains an invalid auditing option.
// *Action:  Use a valid option.

As we know that the records consume a lot of space in the SYS.AUD$ table. So it would make sense that you just audit the creation of indexes and drop of the indexes.

    SQL> audit index;
    Audit succeeded.
 
    SQL> connect orauser/orauser
    Connected.
    SQL> analyze index scott.SYS_C001031 compute statistics;
    Index analyzed.
 
    SQL> connect system/manager
    Connected.
 
    SQL> select USERNAME,OWNER,OBJ_NAME,ACTION,ACTION_NAME,PRIV_USED
         from dba_audit_trail where username='orauser';
 
    USERNAME   OWNER   OBJ_NAME      ACTION ACTION_NAME    PRIV_USED            
    ---------- ------- ------------ ------- -------------- --------------
    orauser    SCOTT   SYS_C001031       63 ANALYZE INDEX  ANALYZE ANY

 
So the INDEX statement includes the ANALYZE ANY privilege to be audited.

So how to do it ?

Audit the system privileges CREATE ANY INDEX and DROP ANY INDEX instead of auditing the INDEX statement that includes the ANALYZE ANY privilege:
 
1. Disable the INDEX auditing:

    SQL> noaudit index;
    Noaudit succeeded.

2. Audit the CREATE ANY INDEX and DROP ANY INDEX system privileges:

    SQL> audit create any index by orauser;
    Audit succeeded.
 
    SQL> audit drop any index by orauser;
    Audit succeeded.
 
    SQL> connect orauser/orauser;
    Connected.
 
    SQL> analyze index scott.SYS_C001031 compute statistics;
    Index analyzed.
 
    SQL> connect system/manager
    Connected.
 
    SQL> select * from dba_audit_trail;
    no rows selected