Rendszerparaméterek

# apt-get update
# apt-get -f -y install unzip gcc make binutils lesstif2 libc6 libc6-dev rpm libmotif3 libaio libstdc++5 gawk alien libg++2.8.1.3-glibc2.2 ksh gcc-3.3 g++-3.3 libstdc++5
# addgroup oinstall
# addgroup dba
# addgroup nobody
# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
# usermod -g nobody nobody
# mkdir /home/oracle
# chown oracle.dba /home/oracle
# passwd oracle
# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /usr/bin/basename /bin/basename
# mkdir /etc/rc.d
# ln -s /etc/rc0.d /etc/rc.d/rc0.d
# ln -s /etc/rc2.d /etc/rc.d/rc2.d
# ln -s /etc/rc3.d /etc/rc.d/rc3.d
# ln -s /etc/rc4.d /etc/rc.d/rc4.d
# ln -s /etc/rc5.d /etc/rc.d/rc5.d
# ln -s /etc/rc6.d /etc/rc.d/rc6.d
# ln -s /etc/init.d /etc/rc.d/init.d
# echo "Red Hat Linux release 4" > /etc/redhat-release
# echo -e "fs.file-max = 65536\nkernel.shmall = 2097152\nkernel.shmmax = 2147483648\nkernel.shmmni = 4096\nkernel.sem = 250 32000 100 128\nnet.ipv4.ip_local_port_range = 1024 65000\nnet.core.rmem_default = 4194308\nnet.core.rmem_max = 4194308\nnet.core.wmem_default = 262144\nnet.core.wmem_max = 262144\n" >> /etc/sysctl.conf
# sysctl -p
# echo -e "oracle soft nproc 2047\noracle hard nproc 16383\noracle soft nofile 1023\noracle hard nofile 65535\n" >> /etc/security/limits.conf

Letöltjük a binárist

Egy alkalmas könyvtárba (ahol van elég hely - nálam /oracle) letöltjük az alábbi zipet: linux11gR1database.zip (1 844 533 232 byte)

# unzip linux_11gR1_database.zip
# chown -R oracle:oinstall database

Az Oracle feltelepítése

Grafikus felületen:

# xhost +
# su - oracle
$ export DISPLAY=:1
$ cd /oracle/databases
$ ./runInstaller

System parameters

# apt-get update
# apt-get -f -y install unzip gcc make binutils lesstif2 libc6 libc6-dev rpm libmotif3 libaio libstdc++5 gawk alien libg++2.8.1.3-glibc2.2 ksh gcc-3.3 g++-3.3 libstdc++5
# addgroup oinstall
# addgroup dba
# addgroup nobody
# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
# usermod -g nobody nobody
# mkdir /home/oracle
# chown oracle.dba /home/oracle
# passwd oracle
# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /usr/bin/basename /bin/basename
# mkdir /etc/rc.d
# ln -s /etc/rc0.d /etc/rc.d/rc0.d
# ln -s /etc/rc2.d /etc/rc.d/rc2.d
# ln -s /etc/rc3.d /etc/rc.d/rc3.d
# ln -s /etc/rc4.d /etc/rc.d/rc4.d
# ln -s /etc/rc5.d /etc/rc.d/rc5.d
# ln -s /etc/rc6.d /etc/rc.d/rc6.d
# ln -s /etc/init.d /etc/rc.d/init.d
# echo "Red Hat Linux release 4" > /etc/redhat-release
# echo -e "fs.file-max = 65536\nkernel.shmall = 2097152\nkernel.shmmax = 2147483648\nkernel.shmmni = 4096\nkernel.sem = 250 32000 100 128\nnet.ipv4.ip_local_port_range = 1024 65000\nnet.core.rmem_default = 4194308\nnet.core.rmem_max = 4194308\nnet.core.wmem_default = 262144\nnet.core.wmem_max = 262144\n" >> /etc/sysctl.conf
# sysctl -p
# echo -e "oracle soft nproc 2047\noracle hard nproc 16383\noracle soft nofile 1023\noracle hard nofile 65535\n" >> /etc/security/limits.conf

Download the installer

Download the installer in a directory which has enough space (my place is /oracle): linux11gR1database.zip (1 844 533 232 byte)

# unzip linux_11gR1_database.zip
# chown -R oracle:oinstall database

Installing Oracle 11g

On the X11:

# xhost +
# su - oracle
$ export DISPLAY=:1
$ cd /oracle/databases
$ ./runInstaller

select count(*) "Futásszám",
       lpad(a.concurrent_program_id,8) "ProgID",
       lpad(a.program_application_id,6) "ApplID",
       rpad(b.user_concurrent_program_name,75) "Program név",
       rpad(c.concurrent_program_name,25) "Rövid név"
  from applsys.fnd_concurrent_requests a, 
       applsys.fnd_concurrent_programs_tl b, 
       apps.fnd_concurrent_programs_vl c
 where a.program_application_id=b.application_id
   and a.program_application_id=c.application_id
   and a.concurrent_program_id = b.concurrent_program_id
   and a.concurrent_program_id = c.concurrent_program_id
   and b.language='HU'
 group by a.concurrent_program_id,
          a.program_application_id,
          b.user_concurrent_program_name,
          c.concurrent_program_name
 order by count(*) desc;

A felrakott patchek kódjai, és felrakásukhoz szükséges idő

select ap.patch_name
      ,substr(pr.patch_top, instr(pr.patch_top, ap.patch_name), 20) "patch"
      ,to_char(pr.start_date, 'HH24:MI:SS') "start"
      ,to_char(pr.end_date, 'HH24:MI:SS') "end"
      ,to_char(trunc(pr.end_date) + (pr.end_date - pr.start_date), 'HH24:MI:SS') "elapsed"
  from applsys.ad_applied_patches ap
 inner join applsys.ad_patch_drivers pd on ap.applied_patch_id=pd.applied_patch_id
 inner join applsys.ad_patch_runs pr on pd.patch_driver_id=pr.patch_driver_id
 order by 2

Hosszan futó konkurrens programok, idejük, indítóik

select rpad(u.description,16) "Indító",
       to_char(fr.request_id,'99999999') "Feld. ID",
       rpad(fp.user_concurrent_program_name,50) "Program név",
       sysdate "Lekérdezés ideje",
       fr.actual_start_date "Feld. indulás",
       to_char(trunc(sysdate) + (sysdate - fr.actual_start_date), 'HH24:MI:SS') "Fut. idő"
  from apps.fnd_concurrent_requests fr, apps.fnd_concurrent_programs_tl fp, apps.fnd_user u
 where ((sysdate - fr.actual_start_date) * 24) > 1
   and phase_code = 'R'
   and status_code = 'R'
   and fr.program_application_id = fp.application_id
   and fr.concurrent_program_id = fp.concurrent_program_id
   and u.user_id = fr.requested_by
   and language = 'HU';

Melyik modul milyen product verzióval van fent

select application_short_name, patch_level 
  from fnd_product_installations i, fnd_application a 
 where a.application_id = i.application_id

Kinek milyen jogosultságai vannak

select u.user_id, u.user_name, u.description, r.responsibility_name, g.start_date, g.end_date
  from apps.fnd_responsibility_vl r
      ,apps.fnd_user_resp_groups g
      ,apps.fnd_user u
 where u.user_id = g.user_id
   and r.responsibility_id = g.responsibility_id
   and u.user_id >= 1110
 order by u.user_id;

select f.tablespace_name,
       to_char(sum(f.bytes)/1024/1024, '999G999G999D99')||'M' "FREE",
       to_char(t.bytes/1024/1024, '999G999G999D99')||'M' "TOTAL",
       to_char(sum(f.bytes)/t.bytes*100, '990.99')||'%' "FREE %"
  from dba_free_space f, dba_data_files t
 where f.tablespace_name=t.tablespace_name
 group by f.tablespace_name, t.bytes
 order by 3 desc

Objektumok méreteinek megállapítása

select segment_name
      ,to_char(sum(bytes)/1024/1024, '999G999G999G999') as MB
  from dba_segments
 where owner='SYS'
 group by segment_name
 order by 2 desc;

Cache találati arányok

select to_char((1-(phy.value - lob.value - dir.value)/ses.value)*100, '900') "buffer cache hit ratio"
  from v$sysstat ses, 
       v$sysstat lob, 
       v$sysstat dir, 
       v$sysstat phy
 where ses.name ='session logical reads'
   and dir.name ='physical reads direct'
   and lob.name ='physical reads direct (lob)'
   and phy.name ='physical reads'

select to_char(((sum(gets-getmisses))/sum(gets))*100, '900') "dictionary cache hit ratio" from v$rowcache

select to_char(sum(pins-reloads)/sum(pins)*100, '900') "library cache hit ratio" from v$librarycache

Végrehajtási terv kiírása

explain plan for
select * from dual;
select * from table(dbms_xplan.display());

sqlplusrc az sqlplus-hoz

set lines 156
set pages 50
set colsep |
set long 2000000
set timing on
set time on
set serveroutput on
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

col db_name format a14
col host format a14
col terminal format a14
col sessionid format a14
col db_domain format a14
col ip_address format a14

select
sys_context('userenv', 'db_name') db_name,
sys_context('userenv', 'host') host,
sysdate,
sys_context('userenv', 'terminal') terminal,
sys_context('userenv', 'sessionid') sessionid,
sys_context('userenv', 'db_domain') db_domain,
sys_context('userenv', 'ip_address') ip_address
from dual;

Ezek után az sqlplus-t a következőképpen érdemes indítani:

$ sqlplus "/ as sysdba" @sqlplusrc

Hogyan derítsük ki, hogy milyen lekérdezések futnak FULL TABLE SCAN-nel?

select substr(s.sql_text, 1, instr(upper(s.sql_text), upper('from'))+4) as sel, o.name, u.name
-- select 'select '''||o.name||''', count(*) from "'||u.name||'"."'||o.name||'" union all'
  from v$sql s, x$kqlfxpl p, obj$ o, user$ u
 where p.kqlfxpl_oopt = 'FULL' 
   and p.kqlfxpl_oper != 'FIXED TABLE'
   and p.kqlfxpl_phad = s.address
   and o.obj# = p.kqlfxpl_objn
   and u.user# = o.owner#
   and (o.name not like '%$%' 
    and o.name != 'DUAL' 
    and o.name != 'PLAN_TABLE'
    and o.name not like '%SQLPLUS%'
    and o.name not like '%LOGMNR%'
   )
 group by o.name, u.name, substr(s.sql_text, 1, instr(upper(s.sql_text),upper('from'))+4);

Melyik OS processzhez milyen SQL tartozik?

select p.pid,
       p.spid,
       p.username,
       p.program,
       s.sid,
       s.user#,
       s.status,
       s.schemaname,
       s.osuser,
       s.machine,
       s.terminal,
       s.module,
       s.action,
       s.logon_time,
       s.event,
       q.sql_text,
       q.sql_id,
       q.parsing_schema_name,
       q.object_status,
       q.program_id,
       q.last_active_time
  from v$process p
 right join v$session s on p.addr = s.paddr
 right join v$sql q on q.address = s.sql_address
 where p.spid = <OSPID>