Friday, 29 January 2016

Useful Linux Commands for Apps DBA's

Check Used Space on  a Disk/ Mount Point
The du command summarizes your disk usage. The sort command sorts the files by size. The command shown below sorts from smallest to largest all files in the current directory and all directories contained below the starting directory. Because the sort starts with the smallest file, the end of the list shows the largest files.

Deleting a large file is usually more efficient than deleting many small files. Often the largest files are core files or cache files, both of which you can delete.

du -h --max-depth=1
du -a | sort -k 1n,1|tail -30
du -sk * | sort -n | tail

Files more than 5mb

find . -mount -size +50000000c -print | ls -lh
find . -size +100000000c -print
find . -size +50000000c -print | xargs ls -l
find . -size +10000000c -print
find . -size +10000 -print

Files more than 30 day old.

find . -name "*.aud" -mtime +7

find . -name *.req -mtime +30|xargs gzip

find . -name "TEST01_*.out" -mtime +2 |xargs ls -l

find . -name "*.log" -mtime -1|xargs ls -l

Files modified in the last 6hrs ago
find . -name "*.log" -mmin 360|xargs ls -l
find . -mmin 360|xargs ls -l

find . -mtime +1|xargs ls -l

find . -size +30000000c -xdev -exec ls -l {} \;


Database Links in Oracle

A database link is a schema object in one database that enables you to access objects on another database.

SQL> desc dba_db_links
 Nombre                                                                                     ¨Nulo?   Tipo
 ------------------------------------------------------------------------------------------ -------- -------------------------------------------------------------
 OWNER                                                                                      NOT NULL VARCHAR2(30)
 DB_LINK                                                                                    NOT NULL VARCHAR2(128)
 USERNAME                                                                                            VARCHAR2(30)
 HOST                                                                                                VARCHAR2(2000)
 CREATED                                                                                    NOT NULL DATE

Use the below query to find the db links available for a database:

set lines 152
col owner for a15
col db_link for a30
col username for a15
col host for a30

select owner,db_link,username,host,CREATED
from dba_db_links;

spool db_link_test.sql

select 'select count(*) from tab@'||db_link||';'
from dba_db_links;

spool off

spool db_link_output.log


spool off


How to Test if a DB link is working fine?

select ' connect '||owner||'/'||owner||'1'||chr(10)||
'select count(*) from tab@'||db_link||';'
from dba_db_links;

select distinct owner
from dba_db_links;

Monday, 18 January 2016

R12.2 Filesystem Layout

  • File System 1 (FS1)--Stores a complete copy of all  Applications and Middle Tier code
  • File System 2 (FS2)--Stores a complete copy of all Applications and Middle Tier code 
  • File System Non-Editioned (FS_ NE)--Stores data that is written or read from the file system,Report Outputs,Log Files etc.
FS1 and FS2 Rotate Run and Patch Designation