Monday, 25 June 2018

Datafiles Disk I/O

Summary 
The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O.

How I/O is spread per datafile 

SELECT NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs
WHERE df.FILE# = fs.FILE#
ORDER BY phyrds DESC;
Tip: ORDER BY phyrds, order by physical reads descending. ORDER BY phywrts, order by physical writes descending.

How I/O is spread per filesystem 

SELECT filesystem, ROUND((RATIO_TO_REPORT(READS) OVER ())*100, 2) || '%' PERC_READS,
ROUND((RATIO_TO_REPORT(WRITES) OVER ())*100, 2) || '%' PERC_WRITES,
ROUND((RATIO_TO_REPORT(TOTAL) OVER ())*100, 2) || '%' PERC_TOTAL
FROM (SELECT filesystem, SUM(Physical_READS) READS, SUM(Physical_WRITES) WRITES, SUM(total) TOTAL
FROM (SELECT SUBSTR(NAME, 0, 25) filesystem, phyrds Physical_READS,
ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs
WHERE df.FILE# = fs.FILE#
ORDER BY phyrds DESC) A
GROUP BY filesystem) B
ORDER BY ROUND((RATIO_TO_REPORT(total) OVER ())*100, 2) DESC;
Tip: To see the filesystems correct experiment with the SUBSTR(NAME, 0, 25)

How I/O is spread for the datafiles of a specific tablespace

SELECT df.NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs, ts$ t
WHERE df.FILE# = fs.FILE#
AND df.ts# = t.ts#
AND t.NAME = 'TABLESPACE_NAME'
ORDER BY phyrds DESC;


Source: Internet

Thursday, 21 June 2018

Delete Linux Audits Rules and Controls



For Directory Rules:

[root ~]# auditctl -l
LIST_RULES: exit,always watch=/etc/hosts perm=rwa key=hosts-file
LIST_RULES: exit,always watch=/etc/resolv.conf perm=wa key=resolv
[root ~]# auditctl -W /etc/hosts
Error sending delete rule data request (No such file or directory)

What am I doing wrong?

You have to match each field in the rule:

[root ~]# auditctl -w /etc/hosts -p wa -k hosts-file
[root ~]# auditctl -l
LIST_RULES: exit,always watch=/etc/hosts perm=wa key=hosts-file
[root ~]# auditctl -W /etc/hosts -p wa -k hosts-file
[root ~]# auditctl -l
No rules
[root@dbsc audit]# auditctl -l
LIST_RULES: exit,always watch=/etc/passwd perm=wa key=passwd_changes
LIST_RULES: exit,always watch=/etc/group perm=wa key=group_changes
LIST_RULES: exit,always watch=/etc/sudoers perm=wa key=sudoers_changes
LIST_RULES: exit,always dir=/u01/oracle/audit_backup (0x18) perm=rwa key=secret_backup
[root@dbsc audit]# ls
auditd.conf  audit.rules
[root@dbsc audit]# auditctl -l
LIST_RULES: exit,always watch=/etc/passwd perm=wa key=passwd_changes
LIST_RULES: exit,always watch=/etc/group perm=wa key=group_changes
LIST_RULES: exit,always watch=/etc/sudoers perm=wa key=sudoers_changes
LIST_RULES: exit,always dir=/u01/oracle/audit_backup (0x18) perm=rwa key=secret_backup
[root@dbsc audit]# auditctl -D
No rules
[root@dbsc audit]# auditctl -l
No rules

How to Configure PAM to Audit Logging Shell User Activity



This is our ongoing series on Linux Auditing, in this fourth part of this article, we will explain how to configure PAM for auditing of Linux TTY input (Logging Shell User Activity) for specific users using pam_tty_audit tool.
Linux PAM (Pluggable Authentication Modules) is a highly flexible method for implementing authentication services in applications and various system services; it emerged from the original Unix PAM.
It divides authentication functions into four major management modules, namely: account modules, authentication modules, password modules and session modules. The detailed explanation of theses management groups is beyond the scope of this tutorial.
The auditd tool uses the pam_tty_audit PAM module to enable or disable auditing of TTY input for specified users. Once a user is configured to be audited, pam_tty_audit works in conjunction with the auditd to track a user’s actions on the terminal and if configured, capture the exact keystrokes the user makes, then records them in the /var/log/audit/audit.log file.

Configuring PAM for Auditing User TTY Input in Linux

You can configure PAM for auditing a particular users TTY input in the /etc/pam.d/system-auth and /etc/pam.d/password-auth files, using the enable option. On the other hand, as expected, the disable turns it off for the specified users, in the format below:

session required pam_tty_audit.so disable=username,username2...  enable=username,username2..

To turn on logging of actual user keystrokes (including spaces, backspaces, return keys, the control key, delete key and others), add the log_passwd option together with the other options, using this form:
session required pam_tty_audit.so disable=username,username2...  enable=username log_passwd

But before you perform any configurations, note that:
§  As seen in the syntax above, you can pass many usernames to the enable or disable option.
§  Any disable or enable option overrides the previous opposite option that matches the same username.
§  After enabling TTY auditing, it is inherited by all processes initiated by the defined user.
§  If recording of keystrokes is activated, the input is not logged instantly, since TTY auditing first stores the keystrokes in a buffer and writes the buffer content at given intervals, or after the audited user logs out, into the /var/log/audit/audit.log file.
Let’s look at an example below, where we’ll configure pam_tty_audit to record the actions of the user tecmint including keystrokes, across all terminals, while we disable TTY auditing for all other system users.
Open these two following configuration files.
# vi /etc/pam.d/system-auth
# vi /etc/pam.d/password-auth

Add following line to the configuration files.
session required pam_tty_audit.so disable=* enable=
tecmint
And to capture all keystrokes entered by the user tecmint, we can add the log_passwd option a shown.

session required pam_tty_audit.so disable=*  enable=tecmint log_passwd

Now save and close the files. Afterwards, view the auditd log file for any TTY input recorded, using the aureport utility.

# aureport --tty


From the output above, you can see the user tecmint whose UID is 1000 used the vi/vim editor, created a directory called bin and moved into it, cleared the terminal and so on.
To search for TTY input logs recored with time stamps equal to or after a specific time, use the -ts to specify the start date/time and -te to set the end date/time.
The following are some example:
# aureport --tty -ts 03/25/2018 00:00:00 -te 03/26/2018 22:00:00
# aureport --tty -ts this-week

You can find more information, in the pam_tty_audit man page.

# man  pam_tty_audit

Check out following useful articles.

Source: Internet

Administrator Define Auditing For RHEL/CentOS/Linux


Learn Linux System Auditing with Auditd Tool on CentOS/RHEL 5,6,7
System auditing simply refers to in-depth analysis of a specific targeted system: an audit is made up of an examination of the various parts which comprise that system, with critical assessment (and testing if required) in different areas of interest.
One of the critical subsystems on RHEL/CentOS the Linux audit system commonly known as auditd. It implements a means to track security-relevant information on a system: it uses pre-configured rules to collect vast amounts of information about events that are happening on the system, and records them in a log file, thus creating an audit trial.
It can record information such as date and time, type, and result of an event; users who caused the event, any modifications made to files/databases; uses of system authentication mechanisms, such as PAM, LDAP, SSH, and others.
Auditd also registers any changes made to the audit configuration files or any attempts to access audit log files, and any efforts to import or export information into or from the system plus a lot of other security-related information.
Why is the Linux Audit System Important?
§  It doesn’t require any external programs or processes to run on a system making it self-reliant.
§  It is highly configurable therefore enables you to view any system operation(s) you want.
§  It helps in detecting or analyzing potential compromises of a system.
§  It is capable of working as an independent detection system.
§  It can work with Intrusion Detection Systems to enable intrusion detection.
§  It is a vital tool for auditing forensics investigations.
The Linux Audit System Components
The audit system has two core components, namely:
§  user-space applications and utilities/tools, and
§  kernel-side system call processing – this accepts system calls from user-space applications and passes them through three types of filters, namely: user, task, exit, or exclude.
The most important part is the user-space audit daemon (auditd) which gathers information based on pre-configured rules, from the kernel and generates entries in a log file: the default log is /var/log/audit/audit.log.
Additionally, the audispd (audit dispatcher daemon) is an event multiplexor that interacts with auditd and sends events to other programs that want to perform real time event processing.
There are a number of user-space tools for managing and retrieving information from the audit system:
§  auditctl – a utility for controlling the kernel’s audit system.
§  ausearch – a utility for searching audit log files for specific events.
§  aureport – a utility for creating reports of recorded events.
How to Install and Configure Audit Tool in RHEL/CentOS/Fedora
First make sure to verify that the audit tool is installed on your system using the rpm command and grep utilityas follows:

# rpm -qa | grep audit









If you do not have the above packages installed, run this command as the root user to install them.
# yum install audit
Next, check if auditd is enabled and running, issue the systemctl commands below on the terminal.

--------------- On CentOS/RHEL 7 ---------------
# systemctl is-enabled auditd
# systemctl status auditd
# systemctl start auditd   [Start]
# systemctl enable auditd  [Enable]

--------------- On CentOS/RHEL 6 ---------------
# service auditd status
# service auditd start       [Start]

# chkconfig auditd on      [Enable]


























Now we will see how to configure auditd using the main configuration file /etc/audit/auditd.conf. The parameters here allow you to control how the service runs, such as defining the location of the log file, maximum number of log files, log format, how to deal with full disks, log rotation and many more options.

# vi /etc/audit/auditd.conf

From the sample output below, the parameters are self-explanatory.

Understanding Audit Rules
As we mentioned earlier on, auditd uses rules to gather specific information from the kernel. These rules are basically auditctl options (see man page) that you can pre-configure rules in the /etc/audit/rules.d/audit.rulesfile (On CentOS 6, use the /etc/audit/audit.rules file), so that they are loaded at startup.
There are three kinds of audit rules you can define:
§  Control rules – these enable modification of the audit system’s behavior and a few of its configurations.
§  File system rules (also referred to as file watches) – enable auditing of access to a certain file or a directory.
§  System call rules – permits logging of system calls made by any program.
Now open the main configuration file for editing:
# vi /etc/audit/rules.d/audit.rules

Note that the first section of this file must contain control rules. Then add your audit rules (file watches and system call rules) in the middle section, and finally the last section contains immutability settings which are also control rules.
Examples of Auditd Control Rules
-D                           #removes all previous rules
-b  3074 #define buffer size
-f 4                         #panic on failure
-r 120                     #create at most 120 audit messages per second

Examples of Auditd File System Rules
You can define file watches using this syntax:
-w /path/to/file/or/directory -p permissions -k key_name

Where the option:
§  w – is used to specify a file or directory to watch over.
§  p – permissions to be logged, r – for read access, w – for write access, x – for execute access and a – for change of file or director attribute.
§  -k – allows you to set an optional string for identifying which rule (or a set of rules) created a specific log entry.
These rules allow auditing to watch events making changes to these critical system files.
-w /etc/passwd -p wa -k passwd_changes
-w /etc/group -p wa -k group_changes
-w /etc/shadow -p wa -k shadow_changes
-w /etc/sudoers -p wa -k sudoers_changes

Examples of Auditd System Call Rules
You can set a system call rule using the form below:
-a action,filter -S system_call -F field=value -k key_name
where:
§  action – has two possible values: always or never.
§  filter – specifies kernel rule-matching filter (task, exit, user and exclude) is applied to the event.
§  system call – system call name.
§  field – specifies additional options such as architecture, PID, GID etc to modify rule.
§   
Here are some rules you can define.
-a always,exit -F arch=b64 -S adjtimex -S settimeofday -k time_change
-a always,exit -S sethostname -S setdomainname -k system_locale

Then lastly add the immutability settings at the end of the file, for example:
-e 1        #enable auditing
-e 2        #make the configuration immutable -- reboot is required to change audit rules


Sample Auditd Rules Configuration File

How to Set Auditd Rules Using auditctl Utility

Alternatively, send the options to auditd while it’s running, using the auditctl as in the following examples. These commands can override rules in the configuration file.
To list all currently loaded audit rules, pass the -l flag:

# auditctl –l

Next, try to add a few rules:
# auditctl -w /etc/passwd -p wa -k passwd_changes
# auditctl -w /etc/group -p wa -k group_changes
# auditctl -w /etc/sudoers -p wa -k sudoers_changes

# auditctl –l


Understanding Auditd Log Files
All audit messages are recorded in /var/log/audit/audit.log file by default. To understand the log entry format, we’ll load a rule and check the log entry generated after an event matching the rule.
Assuming we have a secret backups directory, this audit rule will log any attempts to access or modify this directory:
# auditctl -w /backups/secret_files/ -p rwa -k secret_backup

Now, using another system account, try to move into the directory above and run the ls command:

$ cd /backups/secret_files/
$ ls

The log entry will look like so.




The above event is made up of three types of audit records. The first is type=SYSCALL:

type=SYSCALL msg=audit(1505784331.849:444): arch=c000003e syscall=257 success=yes exit=3 a0=ffffffffffffff9c a1=8ad5c0 a2=90800 a3=0 items=1 ppid=2191 pid=2680 auid=1000 uid=1000 gid=1000 euid=1000 suid=1000 fsuid=1000 egid=1000 sgid=1000 fsgid=1000 tty=pts1 ses=3 comm="ls" exe="/usr/bin/ls" subj=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 key="secret_backup"

The second is type=CWD.

type=CWD msg=audit(1505784331.849:444):  cwd="/backups/secret_files"

And the last one is type=PATH:

type=PATH msg=audit(1505784331.849:444): item=0 name="." inode=261635 dev=08:01 mode=040755 ouid=0 ogid=0 rdev=00:00 obj=unconfined_u:object_r:default_t:s0 objtype=NORMAL

You can find a complete list of all the event fields (such as msg, arch, ses etc..) and their meanings in the Audit System Reference.

That’s all for now. In the next article, we will look at how to use ausearch to query audit log files: we will explain how to search for specific information from the audit logs. If you have any questions, please reach us via the comment section below.


Source:Internet






Monday, 18 June 2018

For Oracle EBS R12 Enterprise Manager Start and Stop Scripts

Start Script
[oracle@dbsc ~]$ vi em_start.sh
echo '=================================================================='
echo 'Enterprise Manager has been started';date
echo '=================================================================='
source /u01/oracle/proddb/11.2.0/DEVE_dbsc.env
emctl start dbconsole
echo '=================================================================='

Stop Script
[oracle@dbsc ~]$ vi em_stop.sh
echo '=================================================================='
echo 'Enterprise Manager has been stop';date
echo '=================================================================='
source /u01/oracle/proddb/11.2.0/DEVE_dbsc.env
emctl stop dbconsole
echo '=================================================================='

How to Manage Temporary Tablespace

Step-01 ( Check Default tablespace)
-------------------------------------

SQL> select * from database_properties where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

SQL>


Step-02 ( set default temp tablespace)
--------------------------------------
SQL> alter database default temporary tablespace NILLTEMP;


Step-03 (Check free space of temp tablespace)
----------------------------------------------
Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP1                           859963392  188612608

SQL>  select TABLESPACE_NAME,  BYTES_USED/1024/1024 , BYTES_FREE/1024/1024  from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED/1024/1024 BYTES_FREE/1024/1024
------------------------------ -------------------- --------------------
TEMP1                                       820.125              179.875


Step-04 (find temp file path)
--------------------------------------
SQL> select * from dba_temp_files where tablespace_name='TEMP3';
or
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;


Step-05 ( alter temp tablespace)
--------------------------------------
SQL> ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/oracle/oradata/PROD/temp03.dbf' size 1024m;

Query For tablesspace Free Space in Oracle 10g,11g,12c



SELECT  a.tablespace_name,
    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
    c.BYTES / 1024 / 1024 space_allocated,
    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name,
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES,
                  SUM (BYTES) BYTES,
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name
    AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;


Source : Internet