How to use TAR

- when creating a directory or file in TAR format

tar -cvf database.tar ./database


- how to untar the file (Note: there no such command called untar)

tar xvf database.tar .

How to use SCP (copy file from one server to another server)

copy directory
scp -r oracle@hostname:/ora01/database.tar.gz .           (Note: getting directory from source server)

copy a file
scp oracle@hostname:/ora01/app/oracle/dbupgdiag.sql .    (Note: getting file from source server)

Check if datapump session is running or not

On OS level type following:

ps -eaf | grep impdp
ps -eaf | grep expdp

ON database level:

set linesize 300
col JOB_NAME format A20
col DATAPUMP_SESSIONS format 999
col JOB_MODE format A10
col OPERATION format A10
select * from dba_datapump_jobs;

or

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
    s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
    from v$session s, v$process p, dba_datapump_sessions d
   where p.addr=s.paddr and s.saddr=d.saddr;

or

select sid, serial#, sofar, totalwork from v$session_longops;
select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

Gather statistics on schema level

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

View POOL sizes in SGA

select pool, name , bytes from v$sgastat order by pool;

or

select pool, name, bytes from v$sgastat  select sum (value)/(1024*1024)
from v$parameter
where name in
  (
       'large_pool_size',
       'java_pool_size',
       'shared_pool_size'
   )
order by pool, name;

/*
select sum(bytes) from v$sgastat where pool = 'shared pool';

/* show parameter shared_pool
/*
select name,value
from v$parameter
where  upper(name) in ('DB_NAME','SGA_TARGET','DB_CACHE_SIZE','DB_BLOCK_SIZE','SGA_MAX_SIZE',
'SHARED_POOL_SIZE', 'LARGE_POO_SIZE');

How apply PSU patch on database level?

export ORACLE_HOME= [provive right path]
export ORACLE_SID=test
@$ORACLE_HOME/rdms/admin/catbundle.sql psu apply

then verify with following sql script:

Select
BUNDLE_SERIES,ACTION ,NAMESPACE,VERSION,ID,COMMENTS
from registry$history;

How to use opatch utility? or how to apply oracle patch

set OPatch patch first:

$> export PATH=$PATH:/ora02/app/oracle/product/11.2.0.3/OPatch

to check opatch version:

$> opatch version (Note: always read the readme file for patch, see which version of opatch you should use)

Then find out whether this patch already has been applied or not?

$> opatch lsinventory

Note: will give list off all the applied patch

then goto patch directory then type

$> cd <patch number>
$> opatch apply