ANTYNET.PL – Administracja Sieci

check:
SELECT * FROM dba_jobs;
SELECT * FROM dba_jobs_running;
SELECT schema_user,job,what,last_date,this_date,next_date,total_time/3600 hours,failures,broken FROM dba_jobs;
column TOTAL_TIME determine the time which system spend run this job from first initialization, it is not time of present us
column THIS_DATE is filled when job is still executing and show time present execution time

new job:
declare
jobnr number;
begin
dbms_job.submit(job=>jobnr,
what=>’dbms_output.put_line(”ble”);’,
next_date=>to_date(’10:00 09/09/2009′,’HH24:MI DD/MM/YYYY’),
interval=>’SYSDATE+1′);
end;
/

hand launch (only by owner, in the other case ORA-23421, even for sys user), beware on change next_date which will by calculated from now
example. interval: sysdate+1:
exec DBMS_JOB.RUN(JOB => [nr]);

set state BROKEN (only by owner, in the other case ORA-23421, even for sys user):
exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => TRUE);

unset state BROKEN (only by owner, in the other case ORA-23421, even for sys user):
exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => FALSE, NEXT_DATE => TO_DATE(’10:00 01/01/09′, ‘HH24:MI MM/DD/YY’));

change job (must be set all arguments, if any is unchange we set NULL):
exec DBMS_JOB.CHANGE(JOB => [nr], NEXT_DATE=>null, WHAT=>null, INTERVAL => ‘SYSDATE + 3′);

remove job (only by owner, in the other case ORA-23421, even for sys user):
exec DBMS_JOB.REMOVE(JOB => [nr]);

how to set time:
sysdate+1 #from present time + 1 day
trunc(sysdate)+1 #from 12.00 AM +1day
trunc(sysdate)+17/24 #17.00 PM today

check time:
SELECT to_char(trunc(sysdate+1) + 90/1440, ‘MM/DD/YYYY HH:MI AM’) FROM dual;

TIME_STATISTICS = true

v$event_name #describe events with parameters P and share to 12 wait_class

v$system_event #based on events which was collected when instance was started for all sessions (please check TIME_WAITED regard to  STARTUP_TIME.v$instance)

• TOTAL_WAITS #how many times session was waiting on this event
• TOTAL_TIMEOUTS #how often default time was reached, default time is set for each event
• TIME_WAITED # time used by event (1/100 sek)
• TIME_WAITED_MICRO #as above (/1000000 sek)
• AVERAGE_WAIT #average time TIME_WAITED/TOTAL_WAITS (1/100 sek)

v$session_event #the same as v$system_event but for session, added SID column

v$session_wait #present events which was expected by session

• SEQ #internal seqence nr for any event, it is increased any time when session wait at event
• STATE #value WAITING and WAITED UNKNOWN TIME show that TIMES_STATISTICS is FALSE
• SECONDS_IN_WAIT #time spending by session

from 10g
v$session_wait_history #the same as v$session_wait but there is last 10 wait events for any session but col SEQ# means something diferent
v$active_session_history #last 30 min probes which was collected every 1 sec., more probes than at v$session_wait_history

v$system_wait_class #waits grouped by class category
v$session_wait_class #waits grouped by class and session category

v$event_histogram #share between events which spend less than 1,2,4,8,16 ms

query order:

running mode:

shutdown mode:

run order:
spfile[db_sid].ora
spfile.ora
pfile[sb_sid].ora
pfile.ora

download opatch

1. download patch
2. unpack
3. shutdown instance
4. read README file
5. change directory to unpacked archive
6. opatch apply
7. run Post Installation Instructions

opatch lsinventoryopatch lsinventory -detail

opatch query -all /[path_to_patch_directory]

opatch -help [option]

opach rollback -id [nr_patcha]

SELECT object_name FROM dba_objects WHERE status = 'INVALID';

INSERT INTO [table] (SELECT * FROM [table] AS OF TIMESTAMP TO_TIMESTAMP(’2008-01-01 10:00:00′,’YYYY-MM-DD HH24:MI:SS’) MINUS SELECT * FROM [table];

SELECT oldest_flashback_scn, oldest_flashback_time FROM v$flashback_database_log; #oldest point which recovery is possible (if you want to chec, delete oldest flashback log and oldest point will change)
SELECT * FROM v$flash_recovery_area_usage;
SELECT * FROM v$recovery_file_dest;

SELECT SCN_TO_TIMESTAMP(10213123) FROM DUAL;

exec DBMS_STATS.GATHER_SCHEMA_STATS(’[username]‘, cascade=>TRUE);
exec #dla tabeli wraz z indeksami DBMS_STATS.GATHER_TABLE_STATS(’[username','[table_name]‘,cascade=>TRUE); #dla całego schematu wraz z indeksami

TIMED_STATISTICS = TRUE

włączenie:
ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 8′

• level 0 #wyłączone
• level 1 #default
• level 4 #default + bind variable values
• level 8 #default + wait event information
• level 12 #level 4 + level 8

wyłączenie:
ALTER SESSION SET EVENTS ‘10046 trace name context off’

włączenie dla innego usera;
ORADEBUG SETOSPID [nr z v$process];
ORADEBUG UNLIMIT;
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL8;
wyłączenie dla innego usera:
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

in 10g is available package DBMS_MONITOR:
- SESSION LEVEL
- turn on (level
exec DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id => [sid],
serial_num => [serial],
waits => TRUE,
binds => FALSE);
- turn off:
exec DBMS_MONITOR.SESSION_TRACE_DISABLE(
session_id=> [sid],
serial_num=> [serial]);
- check:
SELECT sql_trace,sql_trace_waits,sql_trace_binds FROM v$session;
- DATABASE LEVEL
- turn on:
exec DBMS_MONITOR.DATABASE_TRACE_ENABLE(
waits => TRUE,
binds => TRUE,
instance_name > NULL);
- turn off:
exec DBMS_MONITOR.DATABASE_TRACE_ENABLE(
instance_name > NULL);
- check:
SELECT * FROM dba_enabled_traces;
- CLIENT LEVEL
- turn on:
exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
client_id => ‘client_name’,
waits => TRUE,
binds => FALSE);
- turn off:
exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => ‘client_name’);
- check:
SELECT * FROM dba_enabled_traces;
- COMPONENT LEVEL (11g)
- turn on :
exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => ’serv_name’,
module_name => ‘module’,
action_name => ‘action’,
waits => TRUE,
binds => FALSE,
instance_name => NULL);
- turn off:
exec DBMS_MONITOR.SERV_MOD_TRACE_DISABLE(service_name => ’serv_name’,
module_name => ‘module’,
action_name => ‘action’,
instance_name => NULL)
- check:
SELECT * FROM dba_enabled_traces;

TKPROF
! tkprof czyta pliki do 2GB
jeżeli plik trace jest większy niż 2GB należy utworzyć plik fifo:
mkfifo [plik_fifo]
wywołać komendę tkprof na pliku fifo:
TKPROF [plik_fifo] [output_file] …
przejść do innej konsoli i wywołać:
cat [plik_trace] > [plik_fifo]

TKPROF [trace_file] [output_file] SORT=([option] if option sort is not set, order is similar as tracefile, many options means that TKPROF sums the value of the options) PRINT=[only with sort opition - limit number of SQL in output file] SYS=[no - dont list SQL executed by SYS user]

options:

example:
tkprof ../db_ora_10196.trc db_ora_10196-1.txt sys=no sort=prsela,exeela,fchela

dobre wyjaśnienie na blogu
podane za Oracle® Database Performance Tuning Guide
10g Release 2 (10.2) Part Number B14211-03:

Install repository:

1. get full version Grid Control 10.2.0.1.0 (10.2.0.2.1 for Windows) and install, newer versions are installed against 10.2.0.1.0
2. unpack files straight to directory with file runInstaller, in the other case error: OUI-10133 invalid staging area appear
3. when on machine was instaled database do not forget remove /etc/oraInst.loc which describe path to previous inventory
4. install missing libraries:
   • openmotif2.1
   • ysstat
   • control-center
   • compat-db
5. run runInstaller
6. at the end files was generated:
• /usr/bin/dbhome, /usr/local/bin/dbhome
• /usr/bin/oraenv, /usr/local/bin/oraenv
• /usr/bin/coraenv, /usr/local/bin/

Install agent on every monitoring hosts
CHECK STATUS, START AND STOP:
EMCTL (Enterprise Manager tool starts,stops and manages security for Oracle Enteripise Manager)
[agent_path]/bin/emctl status | start | stop agent #check status, start, stop AGENT – perl application [oms_path]/bin/emctl
status iasconsole
[oms_path]/opmn/bin/opmnctl status -l | startall | stopall #check status, start, stop AS – java application

LOCAL EM – not GRID:
Must remember that EM is installed in directory ORACLE_HOME/hostname_sid so when ex hostname was changed it must be change in files:
/sysman/config/emca.properties #repo config
/sysman/config/emd.properties #AGENT config
/sysman/config/emoms.properties #repo config
/sysman/emd/targets.xml #AGENT config

DBCONSOLE (for LOCAL EM) – not GRID:
set ORACLE_HOSTNAME (beware it is casesensitive, sometimes password for DBSNMP inside quotation mark)
emca -deconfig dbcontrol db -repos drop #drop all directory and repository from db
emca -config dbcontrol db -create #create directory and repository in db
emca -config dbcontrol db -repos recreate -SID [sid] -PORT [port] -ORACLE_HOME [oracle_home] -DBCONTROL_HTTP_PORT [port]
emca -config all db -repos recreate;
emctl getemhome

during changing hostname:
from
oracle_home/oc4j/j2ee/OC4J_DBConsole_[oldhostname]_sid
oracle_home/[oldhostname]_sid
to newhostname_sid
and changes in files:
/sysman/config/emca.properties
/sysman/config/emd.properties
/sysman/config/emoms.properties
/sysman/emd/targets.xml
/hostname_sid/config/emca.properties
/hostname_sid/config/emd.properties
/hostname_sid/config/emoms.properties
/hostname_sid/emd/targets.xml

set ports:
emca -reconfig ports [-DBCONTROL_HTTP_PORT [port]] [-RMI_PORT [port]] [-JMS_PORT [port]] [-AGENT_PORT [port]]
ISQLPLUS:
isqlplusctl start | stop http://hostname:5560/isqlplu

check reposidory database:
SELECT * FROM sysman.mgmt_targets;
SELECT * FROM sysman.mgmt_versions;

GRID:
when Management Service (MS) was changed so it must reconfigure Agent but it is not necessary to make changes in Management Service, but restart Agent only

1. stop AGENT
2. $AGENT_HOME/sysman/config/emd.properties
REPOSITORY_URL
emdWalletSrcUrl
emdWalletDest
3. remove all files from:
$AGENT_HOME/sysman/emd/upload/*
$AGENT_HOME/sysman/emd/state/*
4. restart AGENT

when AGENT port (to get commands from MS) was changed:
1. stop AGENT
2. $AGENT_HOME/sysman/config/emd.properties
EMD_URL
3. start AGENT

set password for db which is monitored by AGENT:
1. stop AGENT
2. edit $AGENT_HOME/sysman/emd/targets.xml

3. change password for DMSNMP user in line: 4. start AGENT

limit disk size for AGENT to buffer data before sending to repository:
$AGENT_HOME/sysman/emd/upload
default is up to 50M or to 98% disk size
$AGENT_HOME/sysman/config/emd.properties
UploadMaxBytesXML
UplloadMaxDiskUsedPct

watchdog process – controling AGENT if it is on, and start it when not
$AGENT_HOME/bin/emwd.pl
ps aux|grep emwd

———————————
${ORACLE_HOME}/bin/mkwallet -i welcome
${ORACLE_HOME}/sysman/config/monwallet
${ORACLE_HOME}/sysman/config/b64SiteCertificate.txt NZDST_CLEAR_PTP
————————————

changing password in repository:
MANAGEMENT SERVICE
1. set repo
[$ORACLE_HOME/sysman/config/emoms.properties]
emdRepUser
emdRepPwd
emdRepConnectDescriptor
emdRepSID
emdRepServer
emdRepPort

2. stop MS
$ORACLE_HOME/bin/emctl stop oms
3. set passwd
$ORACLE_HOME/bim/emctl setpasswd oms
4. start MS
$ORACLE_HOME/bim/emctl start oms

change IAS password:
The ias_admin user is the administrative user for Oracle Application Server instances
This password is same as the SYSMAN password that you specified when you first installed the Grid Control in your environment.
After installing the Grid Control in your environment, if you change the SYSMAN password, ias_admin password will not be changed.
$ORACLE_HOME/bin/emctl set password

reset IAP password:
OMS_path/bin/emctl stop iasconsole
edit:
OMS_path/sysman/j2ee/config/jazn-data.xml
change lines:
ias_admin
{903}fdsafsafsdafsadfsdafsd
!new_password
OMS_path/bin/emctl start iasconsole
——————-
spr.
agntstmp.txt
lastupld.xml
——————-
change MS port:
1. stop MS
2. edit $ORACLE_HOME/sysman/config/httpd_em.conf
change all references to port 4889
3. send info to DCM tier
$ORACLE_HOME/dcm/bin/dcmctl updateconfig -ct ohs
4. edit $ORACLE_HOME/sysman/config/emoms.properties
oracle.sysman.emSDK.svlt.ConsoleServerPort=4889
5. start MS
6. reconfigure all AGENTS

AGENT logs:
edit:
$AGENT_HOME/sysman/config/emd.properties
lines:
LogFilewithPID
LogFIleMaxSize
LogFIleMaxRolls
TrcFileMaxSize
TrcFileMaxRolls

filelogs for AGENT
$AGENT_HOME/sysman/log/emagent.log
$AGENT_HOME/sysman/log/emagent.trc
$AGENT_HOME/sysman/log/emagent.nohup

settings in $Agent_HOME/sysman/config/emagantlogging.properies
log4j.appender etc.
for collecting stats:
emagentfetchlet.log
emagentfetchlet.trc

MS logs:
emoms.log
emoms.trc
$AS_HOME/sysman/log
settings in $AS_home/sysman/config/emomslogging.properies

AS logs
HTTP Server ORACLE_HOME/Apache/Apache/logs/error_log.timeORACLE_HOME/Apache/Apache/logs/access_log.time
OC4J ORACLE_HOM
E/j2ee/instance_name/logORACLE_HOME/j2ee/instance_name/application-deployments/application_name/application.log
OPMN ORACLE_HOME/opmn/logs
Web Cache ORACLE_HOME/webcache/logs

Errors:
ERROR host.HostOverviewDataObject getLogonInfo.2447 – Connection refused
oracle.sysman.emSDK.emd.comm.CommException: Connection refused
ERROR host.HostSummaryDataObject getHostSummary.815 – Connection refused
oracle.sysman.emSDK.emd.comm.CommException: Connection refused

uprawnienia:
SELECT * FROM user_role_privs; — role aktualnego usera
SELECT * FROM user_sys_privs; — uprawnienia systemowe aktualnego usera
SELECT * FROM role_sys_privs; — uprawnienia systemowe powiązane z rolami
SELECT * FROM session_privs;
SELECT * FROM session_roles;
SELECT * FROM dba_roles; — wypisanie ról
SELECT * FROM dba_role_privs; — powiązanie ról z userami
SELECT * FROM dba_sys_privs; — powiązanie uprawnienień systemowych z userami
SELECT * FROM dba_tab_privs; — wszystkie prawa do obiektów w bazie także do procedur,funkcji,pakietów
SELECT * FROM role_tab_privs WHERE role=”; –prawa do obiektów dla roli
katalogi:
SELECT * FROM dba_tab_privs WHERE table_name = ‘[katalog]‘;