Unable to get valid context for username - selinux

rm -rf /etc/selinux/targeted/modules
yum reinstall selinux-policy-targeted
touch /.autorelabel; reboot

NUMBER OF USERS LOGGED INTO ORACLE E-BUSINESS SUITE

Number of active users currently logged into the application 

select to_char(START_TIME,'DD-MON-YYYY') Login_Time, count(*) cnt from fnd_logins where START_TIME > (select to_date('25-JAN-2008 00:00:00','DD-MON-YYYY HH24:MI:SS') from dual) and login_type is not null and end_time is null
group by to_char(START_TIME,'DD-MON-YYYY');

Number of sessions for the application using ICX_SESSIONS table

select ((select sysdate from dual)),(select  ' user sessions : ' || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and counter < limit_connects) from dual

Validating Database Files and Backups

VALIDATE command to manually check for physical and logical corruptions in database files

VALIDATE DATABASE; -- validate all datafiles and control files (and the server parameter file if one is in use)
VALIDATE BACKUPSET 22;
VALIDATE DATAFILE 1 BLOCK 10;

Validating Database Files with BACKUP VALIDATE

BACKUP VALIDATE DATABASE ARCHIVELOG ALL; -- validate that all database files and archived logs can be backed up
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL; -- check for logical corruptions in addition to physical corruptions

Validating Backups Before Restoring Them

RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

RMAN - Delete backups

#Deleting backups using primary keys from LIST output:

DELETE BACKUPPIECE 101;

#Deleting backups by filename on disk:

DELETE CONTROLFILECOPY '/tmp/control02.ctl';

#Deleting archived redo logs from disk:

DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 300;

#Deleting backups based on tags:

DELETE BACKUP TAG='before_upgrade';

#Delete backups based on the objects backed up and the media or disk location where the backup is stored:

DELETE BACKUP OF TABLESPACE users DEVICE TYPE sbt; # delete only from tape
DELETE COPY OF CONTROLFILE LIKE '/tmp/%';  # 

#Delete all backups for this database recorded in the RMAN repository:

DELETE BACKUP; 

#Delete backups and archived redo logs from disk based on whether they are backed up on tape:

DELETE ARCHIVELOG ALL
BACKED UP 3 TIMES TO sbt; 

#If you run RMAN interactively, then RMAN asks for confirmation before deleting any files. You can suppress these confirmations by using the NOPROMPT keyword with any form of the BACKUP command:

DELETE NOPROMPT ARCHIVELOG ALL; 

#If you have not performed a crosscheck recently, then issue a CROSSCHECK command. For example, issue:

CROSSCHECK BACKUP; # checks backup sets and copies on configured channels

#Delete the expired backups. For example, issue:

DELETE EXPIRED BACKUP;

#If you use the DELETE command with the optional FORCE keyword, RMAN deletes the specified backups, but ignores any I/O errors, including those that occur when a backup is missing from disk or tape. It then updates the RMAN repository to reflect the fact that the backup is deleted, regardless of whether RMAN was able to delete the file or whether the file was already missing. For example:

DELETE FORCE NOPROMPT BACKUPSET TAG 'weekly_bkup';

#If you specify the DELETE OBSOLETE command with no arguments, then RMAN deletes all obsolete backups defined by the currently configured retention policy For example:

DELETE OBSOLETE;

#You can also use the REDUNDANCY or RECOVERY WINDOW clauses with DELETE to delete backups obsolete under a specific retention policy instead of the configured default:

DELETE OBSOLETE REDUNDANCY = 3;
DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS;

Source - Oracle 

RMAN - LIST & REPORT

LIST BACKUP;           # lists backup sets, image copies, and proxy copies
LIST BACKUPSET;    # lists only backup sets and proxy copies
LIST COPY;                # lists only disk copies

LIST EXPIRED BACKUP;
LIST BACKUP BY FILE; # shows backup sets, proxy copies, and image copies
LIST COPY BY FILE;      # shows only disk copies

LIST EXPIRED BACKUP BY FILE;

LIST BACKUP SUMMARY;  # lists backup sets, proxy copies, and disk copies
LIST EXPIRED BACKUP SUMMARY;

# lists backups of all files in database

LIST BACKUP OF DATABASE; 

# lists copy of specified datafile

LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf'; 

# lists specified backup set

LIST BACKUPSET 213; 

# lists datafile copy

LIST DATAFILECOPY '/tmp/tools01.dbf';

# specify a backup set by tag

LIST BACKUPSET TAG 'weekly_full_db_backup';

# specify a backup or copy by device type

LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt;

# specify a backup by directory or path

LIST BACKUP LIKE '/tmp/%';

# specify a backup or copy by a range of completion dates

LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2002' AND '17-DEC-2002';

# specify logs backed up at least twice to tape

LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;

#List Incarnations of DB

LIST INCARNATION;
LIST INCARNATION OF DATABASE prod3;
LIST INCARNATION OF DATABASE;

# Reports which database files need to be backed up to meet a configured or specified retention policy

REPORT NEED BACKUP

#Reports which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path INSERT

REPORT UNRECOVERABLE

#Full backups, datafile copies, and archived redo logs recorded in the RMAN repository that can be deleted because they are no longer needed.

REPORT OBSOLETE

#The names of all datafiles (permanent and temporary) and tablespaces for the target database at the specified point in time

REPORT SCHEMA

#Displays objects requiring backup to satisfy a recovery window-based retention policy.

REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS

#Displays objects requiring backup to satisfy a redundancy-based retention policy.

REPORT NEED BACKUP REDUNDANCY n

#Displays files that require more than n days' worth of archived redo log files for recovery.

REPORT NEED BACKUP DAYS n

#Displays files that require application of more than n incremental backups for recovery.

REPORT NEED BACKUP INCREMENTAL n
REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE TBS_2;
REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 1;
REPORT NEED BACKUP TABLESPACE TBS_3; # uses configured retention policy
REPORT NEED BACKUP INCREMENTAL 2; # checks entire database

REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE sbt;
REPORT NEED BACKUP DEVICE TYPE DISK;
REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE sbt;

REPORT UNRECOVERABLE;

Use RSYNC to copy files

Check number of files in source folder

tree /source/folder | wc -l

Copy files from source to target folder

rsync -Pavzh --log-file=/tmp/copylog.log /source/folder /target/folder &

Display copy log

tail -f /tmp/copylog.log

Check number of files in target folder

tree /target/folder | wc -l

If the copy process being interrupted, repeat step 2 and the copying process will continue from the last state before interruption.

List symbolic links in current directory

ls -1 | xargs -l readlink
ls -la | grep ^l
find /was61 -type l

R12 Enable/Disable Maintanance Mode script

Enable-Disable maintanance mode

sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql enable
sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql disable

Increase SWAP size in Oracle Linux

Let's say you need another 500M:

dd if=/dev/zero of=/tmp/swapfile bs=1M count=500
mkswap /tmp/swapfile
swapon /tmp/swapfile

After you're done installing, you can turn it off.

swapoff /tmp/swapfile
rm /tmp/swapfile

Linux Debian/Ubuntu version

cat /etc/issue
lsb_release -a
cat /etc/lsb-release

List tar archive content

tar ztf filename
tar ztf filename |less

List only directories:

gunzip -c mytarfile.tar.gz | tar tvf - |grep "^d" |more

List the contents of a tar.gz file

tar -ztvf file.tar.gz

List the contents of a tar.bz2 file

tar -jtvf file.tar.bz2

Find all symbolic links on system

find / -type l -exec ls -l {} \;
find / -type l -exec ls -l {} \; > /home/admin/symlinks.txt

Grep examples

grep "text string to search" directory-path
grep [option] "text string to search" directory-path
grep -r "text string to search" directory-path
grep -r -H "text string to search" directory-path
egrep -R "word-1|word-2" directory-path
egrep -w -R "word-1|word-2" directory-path

 Recursively Search All Files For A String:

cd /path/to/dir
grep -r "word" .
grep -r "string" .

Ignore case distinctions:

grep -ri "word" .

To display print only the filenames with GNU grep, enter:

grep -r -l "foo" .

You can also specify directory name:

grep -r -l "foo" /path/to/dir/*.c

Password protect ZIP archive

zip -e -r test test

List network cards

List network cards(different methods):

lspci | egrep -i --color 'network|ethernet'

 lshw -class network

 ifconfig -a

 ip link show

 ip a

 cat /proc/net/dev

Ping monitor script

Ping script for monitoring servers:

#!/bin/bash
# Simple SHELL script for Linux and UNIX system monitoring with
# ping command
# -------------------------------------------------------------------------
# Copyright (c) 2006 nixCraft project <http://www.cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
# Setup email ID below
# See URL for more info:
# http://www.cyberciti.biz/tips/simple-linux-and-unix-system-monitoring-with-ping-command-and-scripts.html
# -------------------------------------------------------------------------

# add ip / hostname separated by white space
HOSTS="cyberciti.biz theos.in router"

# no ping request
COUNT=1

# email report when
SUBJECT="Ping failed"
EMAILID="me@mydomain.com"
for myHost in $HOSTS
do
  count=$(ping -c $COUNT $myHost | grep 'received' | awk -F',' '{ print $2 }' | awk '{ print $1 }')
  if [ $count -eq 0 ]; then
    # 100% failed
    echo "Host : $myHost is down (ping failed) at $(date)" | mail -s "$SUBJECT" $EMAILID
  fi
done

Monitoring listening ports

Which ports are listening for TCP connections from the network:

nmap -sT -O localhost

Check if the port is associated with the official list of known services:

cat /etc/services | grep 834

Check for port 834 using netstat:

netstat -anp | grep 834

 Check for port 834 using lsof:

lsof -i | grep 834

Find ORG ID

Query to find ORG_ID in R12

SELECT fnd_profile.VALUE('ORG_ID') FROM dual;

Component Versions

Component Versions in Apps 11i/R12 ->

-> Apps Version (11i/R12/12i).

SQL> select release_name from apps.fnd_product_groups;

-> Web Server/Apache or Application Server in Apps 11i/R12

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version

-> Forms & Report version (aka developer 6i) in 11i

$ORACLE_HOME/bin/f60run | grep Version | grep Forms

-> Forms & Report version in R12/12i

$ORACLE_HOME/bin/rwrun | grep Release

-> Oracle Jinitiator in 11i/R12/12i

grep jinit_ver_comma $CONTEXT_FILE

-> Oracle Java Plug-in in 11i/R12/12i

grep plugin $CONTEXT_FILE

-> File Version on file system

adident Header
strings | grep Header

-> Version of pld file

adident Header $AU_TOP/resource/.pll
strings $AU_TOP/resource/.pll | grep -i header

-> OA Framework Version

11i - http:// hostname.domainName:port/OA_HTML/OAInfo.jsp

adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp

-> Discoverer Version for 11i (3i or 4i)

$ORACLE_HOME/bin/disc4ws | grep -i Version

-> Workflow Version with Apps

SQL> select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;

-> Identity Management component Version/Release Number

SQL>select version from orasso.wwc_version$;

-> Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
>>> To find software/binary version

$ORACLE_HOME/bin/oidldapd -version

- To find Schema Version/ database use

ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \
-s base “objectclass=*” orcldirectoryversion

SQL> select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;

-> Application Server
a. Oracle Application Server 10g Rel 3 (10.1.3.X)

cat $ORACLE_HOME/config/ias.properties | grep Version

b. For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database

SQL> select * from ias_versions;
or
SQL>select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;

c. AOC4J (Oracle Container for J2EE)

cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version

d. Oracle Portal

SQL> select version from portal.wwc_version$;

-> Database Component
a. Oracle Database

SQL> select * from v$version;

b. All component version in database

$ORACLE_HOME/OPatch/opatch lsinventory -detail

-> Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release

-> Forms Version in 11i from front end?
Login to forms from frontend , -> Help -> ”About Oracle Applications -> Forms Server

-> How to find if your database is 32 bit or 64 bit

file $ORACLE_HOME/bin/oracle

-> How to find OUI version ?

./runInstaller -help ( From OUI location)

-> How to find Oracle Workflow Cartridge Release Version ?

owf_mgr> select wf_core.translate(\’WF_VERSION\’) from dual;

-> opatch version ?

$ORACLE_HOME/OPatch/opatch version

-> How to Discoverer Version installed with Apps ?

cd $ORACLE_HOME/discwb4/bin
strings dis4ws | grep -i 'discoverer version\'

-> How to find version of JDK Installed on Apps ?

cat $APPL_TOP/admin/$SID_$HOSTNAME.xml  | grep s_jdktop

R12 patching tables

Tables
ad_applied_patches
ad_bugs

select bug_number from ad_bugs where bug_number in ('&bug_number');

If this query returns at least one row, that means that the particular bug has been fixed or a patch of the same number was applied.

---

Details of specific patch that was applied:

col PATCH_NAME format a10
col PATCH_TYPE format a10
col DRIVER_FILE_NAME format a15
col PLATFORM format a10
select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE,  AD.PLATFORM,AL.LANGUAGE
from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID
and AP.PATCH_NAME = '4502962';

select applied_patch_id, last_update_date
from ad_applied_patches
order by last_update_date; 

---

Run script $AD_TOP/sql/adutconf.sql. adutconf.lst will be generated where you can see actual patchlevels of each product.

---

Run patchset.sh utility. Explained in detail in note R11i / R12.0 / R12.1 / R12.2: Oracle Applications Current Patchset Comparison Utility - patchsets.sh (Doc ID 139684.1)

select * from AD_TRACKABLE_ENTITIES

Oracle Notes:
How To List Patches Applied In Pre-Install Mode? (Doc ID 1495138.1)
How To Verify Application Of Pre-install Patches As They Are Not Recorded In Tables AD_APPLIED_PATCHES Or AD_BUGS (Doc ID 1541054.1)

SELECT aat.applications_system_name DATABASE, aat.NAME Server, apps.patch_name, apps.patch_type,
DECODE (apps.rapid_installed_flag, NULL, ‘No’, ‘Yes’) Rapid_install,
apps.source_code, apd.driver_file_name, apr.patch_top,
apr.patch_action_options, apr.start_date, apr.end_date,
apd.platform, apr.server_type_node_flag NODE, apr.server_type_admin_flag ADMIN,
apr.server_type_forms_flag FORMS, apr.server_type_web_flag WEB
FROM applsys.ad_applied_patches apps,
applsys.ad_appl_tops aat,
applsys.ad_patch_drivers apd,
applsys.ad_patch_runs apr
WHERE apps.applied_patch_id = apd.applied_patch_id
AND apd.patch_driver_id = apr.patch_driver_id
AND apr.appl_top_id = aat.appl_top_id
AND TRUNC(apps.CREATION_DATE) > TRUNC(TO_DATE(’06/04/2006′, ‘DD/MM/YYYY’))
— AND patch_name LIKE ‘%3442800%’
ORDER BY apr.start_date ASC;

You might be interesting and about if specific bugs have been solved from which patches

SELECT apps.patch_name,q.patch_top
FROM applsys.ad_patch_run_bugs a,applsys.ad_bugs z,
applsys.ad_patch_runs q,applsys.ad_patch_drivers apd, applsys.ad_applied_patches apps
WHERE a.bug_id= z.bug_id
AND z.bug_number = ‘3442800’
AND q.patch_run_id = a.patch_run_id
AND apps.applied_patch_id = apd.applied_patch_id
AND apd.patch_driver_id = q.patch_driver_id;

Find the patch level

select a.application_name, decode(b.status,’I’,’Installed’,’S’,’Shared’,’N/A’) STATUS, PATCH_LEVEL
from APPS.fnd_application_vl a, APPS.fnd_product_installations b
where a.application_id = b.application_id
–and application_name like ‘Receivables%’

Create keypair - no password access Linux

7. Use Public/Private Keys for Authentication

Using encrypted keys for authentication offers two main benefits. Firstly, it is convenient as you no longer need to enter a password (unless you encrypt your keys with password protection) if you use public/private keys. Secondly, once public/private key pair authentication has been set up on the server, you can disable password authentication completely meaning that without an authorized key you can't gain access - so no more password cracking attempts.

It's a relatively simple process to create a public/private key pair and install them for use on your ssh server.

First, create a public/private key pair on the client that you will use to connect to the server (you will need to do this from each client machine from which you connect):

$ ssh-keygen -t rsa

This will create two files in your (hidden) ~/.ssh directory called: id_rsa and id_rsa.pub The first: id_rsa is your private key and the other: id_rsa.pub is your public key.

If you don't want to still be asked for a passphrase (which is basically a password to unlock a given public key) each time you connect, just press enter when asked for a passphrase when creating the key pair. It is up to you to decide whether or not you should add the passphrase protective encryption to your key when you create it. If you don't passphrase protect your key, then anyone gaining access to your local machine will automatically have ssh access to the remote server. Also, root on the local machine has access to your keys although one assumes that if you can't trust root (or root is compromised) then you're in real trouble. Encrypting the key adds additional security at the expense of eliminating the need for entering a password for the ssh server only to be replaced with entering a passphrase for the use of the key. This may be further simplified by the use of the ssh_agent program

Now set permissions on your private key:

$ chmod 700 ~/.ssh
$ chmod 600 ~/.ssh/id_rsa 

Copy the public key (id_rsa.pub) to the server and install it to the authorized_keys list:

$ cat id_rsa.pub >> ~/.ssh/authorized_keys

Note: once you've imported the public key, you can delete it from the server.

and finally set file permissions on the server:

$ chmod 700 ~/.ssh
$ chmod 600 ~/.ssh/authorized_keys 

The above permissions are required if StrictModes is set to yes in /etc/ssh/sshd_config (the default).

Ensure the correct SELinux contexts are set:

$ restorecon -Rv ~/.ssh 

Now when you login to the server you won't be prompted for a password (unless you entered a passphrase when you created your key pair). By default, ssh will first try to authenticate using keys. If no keys are found or authentication fails, then ssh will fall back to conventional password authentication.

Once you've checked you can successfully login to the server using your public/private key pair, you can disable password authentication completely by adding the following setting to your /etc/ssh/sshd_config file:

# Disable password authentication forcing use of keys

PasswordAuthentication no

Recompile invalids EBS

3 ways...

1. Adadmin -> Compile/Reload Applications Database Entities menu -> Compile APPS schema

2. Manually -> alter [object] [object's name] compile

3. Database -> sqlplus / as sysdba @utlrp.sql

Show Hidden parameters 11g

Show DB 11g hidden parameters:

SELECT a.ksppinm "Parameter",
       b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value"
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' escape '/'
/

 select
   *
from
   v$parameter
where
   substr(name, 0,1) ='_';

Show Long OPS

Sho long OPS session

set lines 200
col OPNAME for a25
Select
a.sid,
a.serial#,
b.status,
a.opname,
to_char(a.START_TIME,' dd-Mon-YYYY HH24:mi:ss') START_TIME,
to_char(a.LAST_UPDATE_TIME,' dd-Mon-YYYY HH24:mi:ss') LAST_UPDATE_TIME,
a.time_remaining as "Time Remaining Sec" ,
a.time_remaining/60 as "Time Remaining Min",
a.time_remaining/60/60 as "Time Remaining HR"
From v$session_longops a, v$session b
where a.sid = b.sid
and a.sid =&sid
And time_remaining > 0;

Move tables, indexes, lobs to another tablespace

Move tables to another TS:

SELECT 'ALTER TABLE <USER>.' || OBJECT_NAME ||' MOVE TABLESPACE '||' <TABLESPACE>; '
FROM ALL_OBJECTS
WHERE OWNER = '<USER>'
AND OBJECT_TYPE = 'TABLE';

Move indexes to another TS:

SELECT 'ALTER INDEX <USER>.'||INDEX_NAME||' REBUILD TABLESPACE <TABLESPACE>;'
FROM ALL_INDEXES
WHERE OWNER = '<USER>';

Move LOBs to another TS:

SELECT 'ALTER TABLE <USER>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <TABLESPACE>);'
FROM DBA_TAB_COLS
WHERE OWNER = '<USER>' AND DATA_TYPE like '%LOB%';

Show CPU info in Linux

Show CPU info on Linux system:

more /proc/cpuinfo
cat /proc/cpuinfo
less /proc/cpuinfo
grep processor /proc/cpuinfo
lscpu

DB init parameters script

Db init parameters

SELECT "Parameter", "Value", "Comment", "Type", "Description", "Modified", "Dynamic", "Basic" FROM(
select name "Parameter",
              value "Value",
              update_comment "Comment",
              decode(type,1,'Boolean',2,' String',3,'Integer',4,'Parameter file',5,'Reserved',6,'Big integer') "Type" ,
              description "Description",
              decode(ISDEFAULT,'TRUE','No','FALSE','Yes') "Modified",
              decode(isinstance_modifiable,'TRUE','Yes','No') "Dynamic",
              decode(ISBASIC,'TRUE','Yes','No') "Basic"
              from GV$SYSTEM_PARAMETER Paramter order by 1
)

Instal vmware tools in Oracle Linux 6.*

Shortly:

yum install perl gcc make -y
yum -y install gcc
yum -y install kernel-uek-devel-`uname -r`
yum -y install kernel-uek-headers-`uname -r`
yum install kernel-headers
./vmware-install.pl

Mount CDROM in Linux

Try this:

mount -t iso9660 -o ro /dev/cdrom /cdrom

or this:

mkdir /mnt/cdrom
mount -t iso9660 -o ro /dev/cdrom /mnt/cdrom

Mount ISO image

 mount -o loop disk1.iso /mnt/disk

DB Pga Usage

Pga usage queries

select name, sum(value/1024) "Value - KB"
 from v$statname n,
 v$session s,
 v$sesstat t
 where s.sid=t.sid
 and n.statistic# = t.statistic#
 and s.type = 'USER'
 and s.username is not NULL
 and n.name in ('session pga memory', 'session pga memory max',
 'session uga memory', 'session uga memory max')
 group by name
 /

select *
from v$pgastat
order by lower(name)
/
select
    sum(pga_used_mem) sum_pga_used_mem
    , sum(pga_alloc_mem) sum_pga_alloc_mem
    , sum(pga_max_mem) sum_pga_max_mem
from v$process
/
select
    max(pga_used_mem) max_pga_used_mem
    , max(pga_alloc_mem) max_pga_alloc_mem
    , max(pga_max_mem) max_pga_max_mem
from v$process
/
select *
from v$pga_target_advice
order by pga_target_for_estimate
/
SELECT
    to_number(decode(sid, 65535, null, sid)) sid
    , operation_type operation_type
    , trunc(expected_size/1024) expected_size_k
    , trunc(actual_mem_used/1024) actual_mem_used_k
    , trunc(max_mem_used/1024) max_mem_used_k
    , number_passes
    , trunc(tempseg_size/1024) tempseg_size
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2
/
select *
from v$pgastat
order by lower(name)
/

Find and delete files in linux

Remove files and directories

find . -name "FILE-TO-FIND" -exec rm -rf {} \;

Remove only files

find . -type f -name "FILE-TO-FIND" -exec rm -f {} \;

Find files older then 10 days

find /var/log -mtime +10

Find files older then 30 min

find /tmp -mmin +30

Remove files older then X days

find /path/* -mtime +5 -exec rm {} \;

Find and move files

find ~/projects/* -mtime +14 -exec mv {} ~/old_projects/ \;

Recursively Search All Files For A String:

cd /path/to/dir
find . -type f -exec grep -l "word" {} +
find . -type f -exec grep -l "seting" {} +
find . -type f -exec grep -l "foo" {} +

Database and Tablespace scripts(Growth, Size...etc)

Tablespace growth script

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

Database Growth script

  SELECT b.tsname tablespace_name,
         MAX (b.used_size_mb) cur_used_size_mb,
         ROUND (AVG (inc_used_size_mb), 2) avg_increas_mb
    FROM (SELECT a.days,
                 a.tsname,
                 used_size_mb,
                   used_size_mb
                 - LAG (used_size_mb, 1)
                      OVER (PARTITION BY a.tsname ORDER BY a.tsname, a.days)
                    inc_used_size_mb
            FROM (  SELECT TO_CHAR (sp.begin_interval_time, 'MM-DD-YYYY') days,
                           ts.tsname,
                           MAX (
                              ROUND (
                                   (tsu.tablespace_usedsize * dt.block_size)
                                 / (1024 * 1024),
                                 2))
                              used_size_mb
                      FROM dba_hist_tbspc_space_usage tsu,
                           dba_hist_tablespace_stat ts,
                           dba_hist_snapshot sp,
                           dba_tablespaces dt
                     WHERE     tsu.tablespace_id = ts.ts#
                           AND tsu.snap_id = sp.snap_id
                           AND ts.tsname = dt.tablespace_name
                           AND sp.begin_interval_time > SYSDATE - 7
                  GROUP BY TO_CHAR (sp.begin_interval_time, 'MM-DD-YYYY'),
                           ts.tsname
                  ORDER BY ts.tsname, days) a) b
GROUP BY b.tsname
ORDER BY b.tsname;

To check Tablespace free space:

SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)"  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

To check Tablespace by datafile:

SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE
group by tablespace_name, file_id;

To Check Tablespace used and free space %:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
 FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

--or--

Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

Tablespace (per file) used and Free space

SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;

List all Tablespaces with free space < 10% or full space> 90%

Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;

Script to find all object Occupied space for a Tablespace

Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments
where TABLESPACE_NAME = 'APPS_TS_TX_DATA'
group by OWNER, SEGMENT_NAME;

Which schema are taking how much space

Select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
 (select owner, ceil(sum(bytes)/1024/1024) seg_size  from dba_segments group by owner) seg
  where obj.owner  = seg.owner(+)
  order    by 3 desc ,2 desc, 1;

To Check Default Temporary Tablespace Name:

Select * from database_properties where PROPERTY_NAME like '%DEFAULT%';

To know default and Temporary Tablespace for particualr User:

Select username,temporary_tablespace,default_tablespace from dba_users where username='APEX_PUBLIC_USER';

To know Default Tablespace for All Users:

Select default_tablespace,temporary_tablespace,username from dba_users;

To check Used free space in Temporary Tablespace:

SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM   V$temp_space_header GROUP  BY tablespace_name;
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         ( SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Sort (Temp) space used by Session

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace, COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;
Sort (Temp) Space Usage by Statement
SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Who is using which UNDO or TEMP segment?

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter   x
WHERE s.taddr = t.addr AND r.usn   = t.xidusn(+) AND x.name  = 'db_block_size';

Who is using the Temp Segment?

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL, a.username, a.program
FROM sys.v_$session a,
sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name  = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Total Size and Free Size of Database:

Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;

To find used space of datafiles:

SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;
IO status of all of the datafiles in database:
WITH total_io AS
     (SELECT SUM (phyrds + phywrts) sum_io
        FROM v$filestat)
SELECT   NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,
         phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio
    FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c
   WHERE a.file# = b.file#
ORDER BY a.file#;

Displays Smallest size the datafiles can shrink to without a re-organize.

SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes - b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes
FROM   dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to
        FROM   dba_extents
        GROUP by file_id) b
        WHERE  a.file_id = b.file_id
        ORDER BY a.tablespace_name, a.file_name;

Show number of files per folder - linux script

Create file count_em.sh:

#!/bin/bash
# count_em - count files in all subdirectories under current directory.
echo 'echo $(ls -a "$1" | wc -l) $1' >/tmp/count_em_$$
chmod 700 /tmp/count_em_$$
find . -mount -type d -print0 | xargs -0 -n1 /tmp/count_em_$$ | sort -n
rm -f /tmp/count_em_$$

copy the file in /bin and then

chmod +x ~/bin/count_em