Skip to content

influence of selinux on postgres

influence of selinux on postgres published on Комментариев к записи influence of selinux on postgres нет

Test was done on VM with 4X CPU E5-2660 2.20GHz + 4GB mem with tools written by pgbench-tools on postgresql 9.4.5

settings: run test 3 times by 10 min, db size 100(1.5gb) 400(6gb) 700 (10gb) 1000 (15gb), clients 1,2,4,8,16,32

Results: with the exception of an anomalous drawdown in the last test, without selinux performance gain can reaches 20%

raw data:
pg_selinux_res

select * from (select set,dbsize,clients,avg(tps) avg_tps, round (100*(  1-lag ( avg(tps)) over ( order by dbsize,clients,set)/avg(tps)),1)  diff_pct,scale from v1 group by set,dbsize,scale,clients order by  dbsize,clients,set) t where t.set =4 order by scale,clients;
 set | dbsize  | clients |        avg_tps         | diff_pct | scale
-----+---------+---------+------------------------+----------+-------
   4 | 1502 MB |       1 |  7002.7727490000000000 |      2.1 |   100
   4 | 1502 MB |       2 |     13726.746474333333 |      0.8 |   100
   4 | 1502 MB |       4 |     24392.296147000000 |      2.6 |   100
   4 | 1502 MB |       8 |     24276.725237333333 |      2.3 |   100
   4 | 1502 MB |      16 |     23974.144015000000 |      0.2 |   100
   4 | 1502 MB |      32 |     25629.092840000000 |      0.5 |   100
   4 | 5988 MB |       1 |  2633.2450016666666667 |      4.2 |   400
   4 | 5988 MB |       2 |  4668.1277356666666667 |     15.8 |   400
   4 | 5988 MB |       4 |  7622.5932590000000000 |     14.1 |   400
   4 | 5988 MB |       8 |  9906.9333880000000000 |      8.7 |   400
   4 | 5988 MB |      16 | 12596.0858780000000000 |     20.4 |   400
   4 | 5988 MB |      32 | 13229.7484046666666667 |     18.1 |   400
   4 | 10 GB   |       1 |  2073.9020166666666667 |      1.8 |   700
   4 | 10 GB   |       2 |  3829.0723596666666667 |      7.8 |   700
   4 | 10 GB   |       4 |  6281.3118036666666667 |      7.2 |   700
   4 | 10 GB   |       8 |  8431.4767026666666667 |      8.0 |   700
   4 | 10 GB   |      16 | 11576.7615346666666667 |     19.2 |   700
   4 | 10 GB   |      32 | 13192.6005800000000000 |     20.4 |   700
   4 | 15 GB   |       1 |  1586.9348243333333333 |     -9.0 |  1000
   4 | 15 GB   |       2 |  3034.7334050000000000 |     -2.6 |  1000
   4 | 15 GB   |       4 |  5172.9388216666666667 |     -7.6 |  1000
   4 | 15 GB   |       8 |  7585.5694350000000000 |     -0.7 |  1000
   4 | 15 GB   |      16 | 10037.6583543333333333 |      5.3 |  1000
   4 | 15 GB   |      32 | 11943.1506076666666667 |     16.1 |  1000

Screen Shot 2015-11-29 at 21.11.13
Screen Shot 2015-11-29 at 21.11.07

So, for better results selinux should be disabled

create standby using backup

create standby using backup published on 1 комментарий к записи create standby using backup

quick way to make standby of small database is to make backup, copy it to destination and create standby
backup on source:

run {ALLOCATE CHANNEL DISK_1 DEVICE TYPE DISK  MAXPIECESIZE 80G; BACKUP AS COMPRESSED BACKUPSET DATABASE  FORMAT '/oracle/oradata/backup/full_%d_%T_%s_%p_db.bak' ;  }
run {ALLOCATE CHANNEL DISK_1 DEVICE TYPE DISK  MAXPIECESIZE 80G; backup ARCHIVELOG FROM TIME 'SYSDATE -1/24' FORMAT '/oracle/oradata/backup/full_%d_%T_%s_%p_db.bak'  ;}
run {ALLOCATE CHANNEL DISK_1 DEVICE TYPE DISK  MAXPIECESIZE 80G; backup as copy current controlfile for standby format  '/oracle/oradata/backup/sby_%d_%T_%s_%p_control01.ctl';}

param file:

SYS@xxxxx> create pfile='/tmp/pfile.ora' from spfile;

copy to standby,modify audit dest and create spfile:

 
[oracle@xxxxx ~]$scp /tmp/pfile.ora 192.168.0.10:/tmp/
SQL> create spfile from pfile='//tmp/pfile.ora';

restore database:

SQL> shutdown abort;
SQL> startup nomount pfile='/tmp/pfile.ora';
SQL> create spfile from pfile='/tmp/pfile.ora';

ASMCMD> mkdir +DATA/s00xxxx/
RMAN> restore controlfile from '/oracle/distr/backup/sby_P00TSMEV_20151025_32133_1_control01.ctl';

RMAN> alter database mount;
RMAN> catalog start with '/oracle/distr/backup/‘;
RMAN> run { allocate channel ch1 device type disk; crosscheck backup; }

RMAN> run { allocate channel ch1 device type disk;set newname for database to '+DATA'; restore database;switch datafile all; recover database; }

configure standby:

SQL> alter system set dg_broker_start=true;

DGMGRL> create configuration tsmevp_cfg as primary database is p00xxxxx connect identifier is p00xxxxx;
DGMGRL> add database s00xxxxx as connect identifier is s00xxxxx maintained as physical;
Database "s00tsmevr1" added


DGMGRL> enable configuration;

DGMGRL> show configuration;

script to monitor status of full and inc backup

script to monitor status of full and inc backup published on Комментариев к записи script to monitor status of full and inc backup нет

script to monitor status of full and inc backup

         select * from (
   select OBJECT_TYPE,case when  OBJECT_TYPE='DB FULL' and  max (START_TIME) < TRUNC (SYSDATE - 7) then max (START_TIME) || ' DB FULL BACKUP is too old'
                       when OBJECT_TYPE='DB INCR' and  max (START_TIME) < TRUNC (SYSDATE - 1) then max (START_TIME) || ' DB INC BACKUP is too old'
                       else null
                       end as backup_status
      from  V$RMAN_STATUS where OBJECT_TYPE ='DB FULL' or OBJECT_TYPE='DB INCR' group by OBJECT_TYPE )
      pivot (max (backup_status) for OBJECT_TYPE in ('DB INCR','DB FULL') )
         union all
        SELECT rs.STATUS||':'||
         rs.operation ||' '|| rs.OBJECT_TYPE ||':'||
          rs.END_TIME  , ro.output
    FROM V$RMAN_STATUS  rs
      join v$rman_output ro on rs.RECID=ro.RMAN_STATUS_RECID
   WHERE START_TIME > TRUNC (SYSDATE - 1)
   and status not in ( 'COMPLETED','RUNNING')
    group by rs.STATUS||':'||
         rs.operation ||' '|| rs.OBJECT_TYPE ||':'||
          rs.END_TIME  , ro.output;

ORA-15557: workload replay client cannot access the replay directory or the database version do not match the preprocessing one

ORA-15557: workload replay client cannot access the replay directory or the database version do not match the preprocessing one published on Комментариев к записи ORA-15557: workload replay client cannot access the replay directory or the database version do not match the preprocessing one нет
$wrc mode=CALIBRATE REPLAYDIR=/oracle/RAT_PEAK_LOAD/

Workload Replay Client: Release 11.2.0.4.0 - Production on Wed Nov 18 10:04:18 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Errors in file :
ORA-15557: workload replay client cannot access the replay directory or the database version do not match the preprocessing one

fixed by merge of 2 folders from rac nodes into single directory:

wrc mode=CALIBRATE REPLAYDIR=/oracle/RAT_PEAK_LOAD/

Workload Replay Client: Release 11.2.0.4.0 - Production on Wed Nov 18 10:10:23 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Report for Workload in: /oracle/RAT_PEAK_LOAD/
-----------------------

Recommendation:
Consider using at least 15 clients divided among 4 CPU(s)
You will need at least 157 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.

Workload Characteristics:
- max concurrency: 624 sessions
- total number of sessions: 4149

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

how to get cpu load from awr

how to get cpu load from awr published on 1 комментарий к записи how to get cpu load from awr

hi, there is a script to find out cpu load from awr by one instance ( in example number two ) :

select start_time,round (100*"'LOAD'"/"'NUM_CPU_CORES'") AS LOAD from
(
select os.INSTANCE_NUMBER,stat_name,sum(os.value) as load,
min(to_date(to_char(s.begin_interval_time,'DD.MM.YYYY hh24.mi.ss'))) as START_TIME,max(to_date(to_char(s.end_interval_time,'DD.MM.YYYY hh24.mi.ss'))) end_time
from DBA_HIST_OSSTAT os join
DBA_HIST_SNAPSHOT s on s.snap_id= os.SNAP_ID
where os.stat_name in ('LOAD','NUM_CPU_CORES','INSTANCE_NUMBER')
group by os.stat_name, (trunc(to_date(to_char(s.begin_interval_time,'DD.MM.YYYY hh24.mi.ss')),'HH24')),os.INSTANCE_NUMBER   
)
  pivot( max(LOAD) for stat_name in ('LOAD','NUM_CPU_CORES') )
  where instance_number=2
order by instance_number,start_time asc ;

ps: usefull to make graphs linke this:
Screen Shot 2015-11-16 at 17.39.36

Screen Shot 2015-11-16 at 17.39.40

how to backup invalid object source and drop it

how to backup invalid object source and drop it published on Комментариев к записи how to backup invalid object source and drop it нет

in addition to
how to backup invalid object source :

create table system.invalid_object_backup as 
select  i.owner,i.object_type,i.object_name,dbms_metadata.get_ddl(replace (i.object_type,'PACKAGE BODY','PACKAGE_BODY'),i.object_name,i.owner) as source from 
( SELECT owner,       object_type,       object_name,       status FROM   dba_objects WHERE  status = 'INVALID' ORDER BY owner, object_type, object_name)i ;

and drop them:

select 'drop '||object_type||' '||owner||'.'||object_name||';' from invalid_object_backup  order by owner, object_name; 

Metalink note 175982.1

Metalink note 175982.1 published on Комментариев к записи Metalink note 175982.1 нет

Unfortunately, the old Metalink note 175982.1 which used to describe the internal mnemonic’s has disappeared from My Oracle Support.

 

 
  Modified: 16-AUG-2011 Type: BULLETIN Status: PUBLISHED  

In this Document
Purpose
Scope and Application
ORA-600 Lookup Error Categories
Internal Errors Categorised by number range
Internal Errors Categorised by mnemonic


Applies to:

Oracle Server – Enterprise Edition – Version:
Oracle Server – Personal Edition – Version:
Oracle Server – Standard Edition – Version:
Information in this document applies to any platform.
Checked for relevance 04-Jun-2009

Purpose

This note aims to provide a high level overview of the internal
errors which may be encountered on the Oracle Server (sometimes referred
to as the Oracle kernel). It is written to provide a guide to where a
particular error may live and give some indication as to what the impact
of the problem may be. Where a problem is reproducible and connected
with a specific feature, you might obviously try not using the feature.
If there is a consistent nature to the problem, it is good practice to
ensure that the latest patchsets are in place and that you have taken
reasonable measures to avoid known issues.

For repeatable
issues which the ora-600 tool has not listed a likely cause , it is
worth constructing a test case. Where this is possible, it greatly
assists in the resolution time of any issue. It is important to remember
that, in a many instances , the Server is very flexible and a
workaround can very often be achieved.

Scope and Application

This bulletin provides Oracle DBAs with an overview of internal database errors.

Disclaimer:Every effort has been made to provide a
reasonable degree of accuracy in what has been stated. Please consider
that the details provided only serve to provide an indication of
functionality and, in some cases, may not be wholly correct.

ORA-600 Lookup Error Categories

In the Oracle Server source, there are two types of ora-600 error :

  • the first parameter is a number which reflects the source component or layer the error is connected with; or
  • the first parameter is a mnemonic which indicates the source module
    where the error originated. This type of internal error is now used in
    preference to an internal error number.

Both types of error may be possible in the Oracle server.

Internal Errors Categorised by number range

The following table provides an indication of internal error codes
used in the Oracle server. Thus, if ora-600[X] is encountered, it is
possible to glean some high level background information : the error in
generated in the Y layer which indicates that there may be a problem
with Z.

Ora-600 Base Functionality Description
1 Service Layer The service layer has
within it a variety of service related components which are associated
with in memory related activities in the SGA such as, for example : the
management of Enqueues, System Parameters, System state objects (these
objects track the use of structures in the SGA by Oracle server
processes), etc.. In the main, this layer provides support to allow
process communication and provides support for locking and the
management of structures to support multiple user processes connecting
and interacting within the SGA.

Note : vos – Virtual Operating System provides features to
support the functionality above. As the name suggests it provides base
functionality in much the same way as is provided by an Operating
System.

Ora-600 Base Functionality Description
1 vos Component notifier
100 vos Debug
300 vos Error
500 vos Lock
700 vos Memory
900 vos System Parameters
1100 vos System State object
1110 vos Generic Linked List management
1140 vos Enqueue
1180 vos Instance Locks
1200 vos User State object
1400 vos Async Msgs
1700 vos license Key
1800 vos Instance Registration
1850 vos I/O Services components
2000 Cache Layer Where errors are
generated in this area, it is advisable to check whether the error is
repeatable and whether the error is perhaps associated with recovery or
undo type operations; where this is the case and the error is
repeatable, this may suggest some kind of hardware or physical issue
with a data file, control file or log file. The Cache layer is
responsible for making the changes to the underlying files and well as
managing the related memory structures in the SGA.

Note :rcvindicates recovery. It is important
to remember that the Oracle cache layer is effectively going through the
same code paths as used by the recovery mechanism.

Ora-600 Base Functionality Description
2000 server/rcv Cache Op
2100 server/rcv Control File mgmt
2200 server/rcv Misc (SCN etc.)
2400 server/rcv Buffer Instance Hash Table
2600 server/rcv Redo file component
2800 server/rcv Db file
3000 server/rcv Redo Application
3200 server/cache Buffer manager
3400 server/rcv Archival & media recovery component
3600 server/rcv recovery component
3700 server/rcv Thread component
3800 server/rcv Compatibility segment

It is important to consider when the error occurred and the
context in which the error was generated. If the error does not
reproduce, it may be an in memory issue.

4000 Transaction Layer Primarily the
transaction layer is involved with maintaining structures associated
with the management of transactions. As with the cache layer , problems
encountered in this layer may indicate some kind of issue at a physical
level. Thus it is important to try and repeat the same steps to see if
the problem recurs.

Ora-600 Base Functionality Description
4000 server/txn Transaction Undo
4100 server/txn Transaction Undo
4210 server/txn Transaction Parallel
4250 server/txn Transaction List
4300 space/spcmgmt Transaction Segment
4400 txn/lcltx Transaction Control
4450 txn/lcltx distributed transaction control
4500 txn/lcltx Transaction Block
4600 space/spcmgmt Transaction Table
4800 dict/rowcache Query Row Cache
4900 space/spcmgmt Transaction Monitor
5000 space/spcmgmt Transaction Extent

It is important to try and determine what the object involved
in any reproducible problem is. Then use the analyze command. For more
information, please refer to the analyze command as detailed in the
context of Note:28814.1; in addition, it may be worth using the dbverify as discussed in Note:35512.1.

6000 Data Layer The data layer is
responsible for maintaining and managing the data in the database tables
and indexes. Issues in this area may indicate some kind of physical
issue at the object level and therefore, it is important to try and
isolate the object and then perform. an anlayze on the object to
validate its structure.

Ora-600 Base Functionality Description
6000 ram/data
ram/analyze
ram/index
data, analyze command and index related activity
7000 ram/object lob related errors
8000 ram/data general data access
8110 ram/index index related
8150 ram/object general data access

Again, it is important to try and determine what the object
involved in any reproducible problem is. Then use the analyze command.
For more information, please refer to the analyze command as detailed in
the context of Note:28814.1; in addition, it may be worth using the dbverify as discussed in Note:35512.1.

12000 User/Oracle Interface & SQL Layer Components This layer governs the
user interface with the Oracle server. Problems generated by this layer
usually indicate : some kind of presentation or format error in the data
received by the server, i.e. the client may have sent incomplete
information; or there is some kind of issue which indicates that the
data is received out of sequence

Ora-600 Base Functionality Description
12200 progint/kpo
progint/opi
lob related
errors at interface level on server side, xa , etc.
12300 progint/if OCI interface to coordinating global transactions
12400 sqlexec/rowsrc table row source access
12600 space/spcmgmt operations associated with tablespace : alter / create / drop operations ; operations associated with create table / cluster
12700 sqlexec/rowsrc bad rowid
13000 dict/if dictionary access routines associated with kernel compilation
13080 ram/index kernel Index creation
13080 sqllang/integ constraint mechanism
13100 progint/opi archival and Media Recovery component
13200 dict/sqlddl alter table mechanism
13250 security/audit audit statement processing
13300 objsupp/objdata support for handling of object generation and object access
14000 dict/sqlddl sequence generation
15000 progint/kpo logon to Oracle
16000 tools/sqlldr sql loader related

You should try and repeat the issue and with the use of sql
trace , try and isolate where exactly the issue may be occurring within
the application.

14000 System Dependent Component internal error values This layer manages
interaction with the OS. Effectively it acts as the glue which allows
the Oracle server to interact with the OS. The types of operation which
this layer manages are indicated as follows.

Ora-600 Base Functionality Description
14000 osds File access
14100 osds Concurrency management;
14200 osds Process management;
14300 osds Exception-handler or signal handler management
14500 osds Memory allocation
15000 security/dac,
security/logon
security/ldap
local user access validation;
challenge / response activity for remote access validation; auditing
operation; any activities associated with granting and revoking of
privileges; validation of password with external password file
15100 dict/sqlddl this component manages operations
associated with creating, compiling (altering), renaming, invalidating,
and dropping procedures, functions, and packages.
15160 optim/cbo cost based optimizer layer is used
to determine optimal path to the data based on statistical information
available on the relevant tables and indexes.
15190 optim/cbo cost based optimizer layer. Used in
the generation of a new index to determine how the index should be
created. Should it be constructed from the table data or from another
index.
15200 dict/shrdcurs used to in creating sharable context area associated with shared cursors
15230 dict/sqlddl manages the compilation of triggers
15260 dict/dictlkup
dict/libcache
dictionary lookup and library cache access
15400 server/drv manages alter system and alter session operations
15410 progint/if manages compilation of pl/sql packages and procedures
15500 dict/dictlkup performs dictionary lookup to ensure semantics are correct
15550 sqlexec/execsvc
sqlexec/rowsrc
hash join execution management;
parallel row source management
15600 sqlexec/pq component provides support for Parallel Query operation
15620 repl/snapshots manages the creation of snapshot or materialized views as well as related snapshot / MV operations
15640 repl/defrdrpc layer containing various functions for examining the deferred transaction queue and retrieving information
15660 jobqs/jobq manages the operation of the Job queue background processes
15670 sqlexec/pq component provides support for Parallel Query operation
15700 sqlexec/pq component provides support for Parallel Query operation; specifically mechanism for starting up and shutting down query slaves
15800 sqlexec/pq component provides support for Parallel Query operation
15810 sqlexec/pq component provides support for
Parallel Query operation; specifically functions for creating mechanisms
through which Query co-ordinator can communicate with PQ slaves;
15820 sqlexec/pq component provides support for Parallel Query operation
15850 sqlexec/execsvc component provides support for the execution of SQL statements
15860 sqlexec/pq component provides support for Parallel Query operation
16000 loader sql Loader direct load operation;
16150 loader this layer is used for ‘C’ level call outs to direct loader operation;
16200 dict/libcache this is part of library Cache
operation. Amongst other things it manages the dependency of SQL objects
and tracks who is permitted to access these objects;
16230 dict/libcache this component is responsible for managing access to remote objects as part of library Cache operation;
16300 mts/mts this component relates to MTS (Multi Threaded Server) operation
16400 dict/sqlddl this layer contains functionality
which allows tables to be loaded / truncated and their definitions to be
modified. This is part of dictionary operation;
16450 dict/libcache this layer layer provides support
for multi-instance access to the library cache; this functionality is
applicable therefore to OPS environments;
16500 dict/rowcache this layer provides support to load / cache Oracle’s dictionary in memory in the library cache;
16550 sqlexec/fixedtab this component maps data structures
maintained in the Oracle code to fixed tables such that they can be
queried using the SQL layer;
16600 dict/libcache this layer performs management of data structures within the library cache;
16651 dict/libcache this layer performs management of dictionary related information within library Cache;
16701 dict/libcache this layer provides library Cache support to support database creation and forms part of the bootstrap process;
17000 dict/libcache this is the main library Cache
manager. This Layer maintains the in memory representation of cached sql
statements together will all the necessary support that this demands;
17090 generic/vos this layer implementations error
management operations: signalling errors, catching errors, recovering
from errors, setting error frames, etc.;
17100 generic/vos Heap manager. The Heap manager
manages the storage of internal data in an orderly and consistent
manner. There can be many heaps serving various purposes; and heaps
within heaps. Common examples are the SGA heap, UGA heap and the PGA
heap. Within a Heap there are consistency markers which aim to ensure
that the Heap is always in a consistent state. Heaps are use extensively
and are in memory structures – not on disk.
17200 dict/libcache this component deals with loading remote library objects into the local library cache with information from the remote database.
17250 dict/libcache more library cache errors ; functionality for handling pipe operation associated with dbms_pipe
17270 dict/instmgmt this component manages
instantiations of procedures, functions, packages, and cursors in a
session. This provides a means to keep track of what has been loaded in
the event of process death;
17300 generic/vos manages certain types of memory allocation structure. This functionality is an extension of the Heap manager.
17500 generic/vos relates to various I/O operations.
These relate to async i/o operation, direct i/o operation and the
management of writing buffers from the buffer cache by potentially a
number of database writer processes;
17625 dict/libcache additional library Cache supporting functions
17990 plsql plsql ‘standard’ package related issues
18000 txn/lcltx transaction and savepoint management operations
19000 optim/cbo cost based optimizer related operations
20000 ram/index bitmap index and index related errors.
20400 ram/partnmap operations on partition related objects
20500 server/rcv server recovery related operation
21000 repl/defrdrpc,
repl/snapshot,
repl/trigger
replication related features
23000 oltp/qs AQ related errors.
24000 dict/libcache operations associated with managing stored outlines
25000 server/rcv tablespace management operations

Internal Errors Categorised by mnemonic

The following table details mnemonics error stems which are possible.
If you have encountered : ora-600[kkjsrj:1] for example, you should
look down the Error Mnemonic column (errors in alphabetical order) until
you find the matching stem. In this case, kkj indicates that something
unexpected has occurred in job queue operation.

Error Mnemonic(s) Functionality Description
ain ainp ram/index ain – alter index; ainp – alter index partition management operation
apacb optim/rbo used by optimizer in connect by processing
atb atbi atbo ctc ctci cvw dict/sqlddl alter table , create table (IOT) or
cluster operations as well as create view related operations (with
constraint handling functionality)
dbsdrv sqllang/parse alter / create database operation
ddfnet progint/distrib various distributed operations on remote dictionary
delexe sqlexec/dmldrv manages the delete statement operation
dix ram/index manages drop index or validate index operation
dtb dict/sqlddl manages drop table operation
evaa2g evah2p evaa2g dbproc/sqlfunc various functions involves in
evaluating operand outcomes such as : addition , average, OR operator,
bites AND , bites OR, concatenation, as well as Oracle related functions
: count(), dump() , etc. The list is extensive.
expcmo expgon dbproc/expreval handles expression evaluation with respect to two operands being equivalent
gra security/dac manages the granting and revoking of privilege rights to a user
gslcsq plsldap support for operations with an LDAP server
insexe sqlexec/dmldrv handles the insert statement operation
jox progint/opi functionality associated with the Java compiler and with the Java runtime environment within the Server
k2c k2d progint/distrib support for database to database
operation in distributed environements as well as providing, with
respect to the 2-phase commit protocol, a globally unique Database id
k2g k2l txn/disttx support for the 2 phase commit
protocol protocol and the coordination of the various states in managing
the distributed transaction
k2r k2s k2sp progint/distrib k2r – user interface for managing
distributed transactions and combining distributed results ; k2s –
handles logging on, starting a transaction, ending a transaction and
recovering a transaction; k2sp – management of savepoints in a
distributed environment.
k2v txn/disttx handles distributed recovery operation
kad cartserv/picklercs handles OCIAnyData implementation
kau ram/data manages the modification of indexes
for inserts, updates and delete operations for IOTs as well as
modification of indexes for IOTs
kcb kcbb kcbk kcbl kcbs kcbt kcbw kcbz cache manages Oracle’s buffer cache operation as well as operations used by capabilities such as direct load, has clusters , etc.
kcc kcf rcv manages and coordinates operations on the control file(s)
kcit context/trigger internal trigger functionality
kck rcv compatibility related checks associated with the compatible parameter
kcl cache background lck process which manages locking in a RAC or parallel server multiple instance environment
kco kcq kcra kcrf kcrfr kcrfw kcrp kcrr kcs kct kcv rcv various buffer cache operation such as quiesce operation , managing fast start IO target, parallel recovery operation , etc.
kd ram/data support for row level dependency checking and some log miner operations
kda ram/analyze manages the analyze command and collection of statistics
kdbl kdc kdd ram/data support for direct load operation, cluster space management and deleting rows
kdg ram/analyze gathers information about the underlying data and is used by the analyze command
kdi kdibc3 kdibco kdibh kdibl kdibo kdibq kdibr kdic kdici kdii kdil kdir kdis kdiss kdit kdk ram/index support of the creation of indexes on tables an IOTs and index look up
kdl kdlt ram/object lob and temporary lob management
kdo ram/data operations on data such as inserting a row piece or deleting a row piece
kdrp ram/analyze underlying support for operations provided by the dbms_repair package
kds kdt kdu ram/data operations on data such as retrieving a row and updating existing row data
kdv kdx ram/index functionality for dumping index and managing index blocks
kfc kfd kfg asm support for ASM file and disk operations
kfh kfp kft rcv support for writing to file header and transportable tablespace operations
kgaj kgam kgan kgas kgat kgav kgaz argusdbg/argusdbg support for Java Debug Wire Protocol (JDWP) and debugging facilites
kgbt kgg kgh kghs kghx kgkp vos kgbt – support for BTree operations;
kgg – generic lists processing; kgh – Heap Manager : managing the
internal structures withing the SGA / UGA / PGA and ensures their
integrity; kghs – Heap manager with Stream support; kghx – fixed sized
shared memory manager; kgkp – generic services scheduling policies
kgl kgl2 kgl3 kgla kglp kglr kgls dict/libcache generic library cache operation
kgm kgmt ilms support for inter language method services – or calling one language from another
kgrq kgsk kgski kgsn kgss vos support for priority queue and scheduling; capabilities for Numa support; Service State object manager
kgupa kgupb kgupd0 kgupf kgupg kgupi kgupl kgupm kgupp kgupt kgupx kguq2 kguu vos Service related activities
activities associated with for Process monitor (PMON); spawning or
creating of background processes; debugging; managing process address
space; managing the background processes; etc.
kgxp vos inter process communication related functions
kjak kjat kjb kjbl kjbm kjbr kjcc kjcs
kjctc kjcts kjcv kjdd kjdm kjdr kjdx kjfc kjfm kjfs kjfz kjg kji kjl kjm
kjp kjr kjs kjt kju kjx
ccl/dlm dlm related functionality ; associated with RAC or parallel server operation
kjxgf kjxgg kjxgm kjxgn kjxgna kjxgr ccl/cgs provides communication &
synchronisation associated with GMS or OPS related functionality as well
as name service and OPS Instance Membership Recovery Facility
kjxt ccl/dlm DLM request message management
kjzc kjzd kjzf kjzg kjzm ccl/diag support for diagnosibility amongst OPS related services
kkb dict/sqlddl support for operatoins which load/change table definitions
kkbl kkbn kkbo objsupp/objddl support for tables with lobs , nested tables and varrays as well as columns with objects
kkdc kkdl kkdo dict/dictlkup support for constraints, dictionary lookup and dictionary support for objects
kke optim/cbo query engine cost engine; provides
support functions that provide cost estimates for queries under a number
of different circumstances
kkfd sqlexec/pq support for performing parallel query operation
kkfi optim/cbo optimizer support for matching of expressions against functional ndexes
kkfr kkfs sqlexec/pq support for rowid range handling as well as for building parallel query query operations
kkj jobqs/jobq job queue operation
kkkd kkki dict/dbsched resource manager related support.
Additionally, provides underlying functions provided by
dbms_resource_manager and dbms_resource_manager_privs packages
kklr dict/sqlddl provides functions used to
manipulate LOGGING and/or RECOVERABLE attributes of an object
(non-partitioned table or index or partitions of a partitioned table or
index)
kkm kkmi dict/dictlkup provides various semantic checking functions
kkn ram/analyze support for the analyze command
kko kkocri optim/cbo Cost based Optimizer operation :
generates alternative execution plans in order to find the optimal /
quickest access to the data. Also , support to determine cost and
applicability of scanning a given index in trying to create or rebuild
an index or a partition thereof
kkpam kkpap ram/partnmap support for mapping predicate keys expressions to equivalent partitions
kkpo kkpoc kkpod dict/partn support for creation and modification of partitioned objects
kkqg kkqs kkqs1 kkqs2 kkqs3 kkqu kkqv kkqw optim/vwsubq query rewrite operation
kks kksa kksh kksl kksm dict/shrdcurs support for managing shared cursors/ shared sql
kkt dict/sqlddl support for creating, altering and dropping trigger definitions as well as handling the trigger operation
kkxa repl/defrdrpc underlying support for dbms_defer_query package operations
kkxb dict/sqlddl library cache interface for external tables
kkxl dict/plsicds underlying support for the dbms_lob package
kkxm progint/opi support for inter language method services
kkxs dict/plsicds underlying support for the dbms_sys_sql package
kkxt repl/trigger support for replication internal trigger operation
kkxwtp progint/opi entry point into the plsql compiler
kky drv support for alter system/session commands
kkz kkzd kkzf kkzg kkzi kkzj kkzl kkzo kkzp kkzq kkzr kkzu kkzv repl/snapshot support for snapshots or Materialized View validation and operation
kla klc klcli klx tools/sqlldr support for direct path sql loader operation
kmc kmcp kmd kmm kmr mts/mts support for Multi Threaded server
operation (MTS) : manange and operate the virtual circuit mechanism,
handle the dispatching of massages, administer shared servers and for
collecting and maintaining statistics associated with MTS
knac knafh knaha knahc knahf knahs repl/apply replication apply operation associated with Oracle streams
kncc repl/repcache support for replication related information stored and maintained in library cache
kncd knce repl/defrdrpc replication related enqueue and dequeue of transction data as well as other queue related operations
kncog repl/repcache support for loading replicaiton object group information into library cache
kni repl/trigger support for replication internal trigger operation
knip knip2 knipi knipl knipr knipu knipu2 knipx repl/intpkg support for replication internal package operation.
kno repl/repobj support for replication objects
knp knpc knpcb knpcd knpqc knps repl/defrdrpc operations assocaied with propagating transactions to a remote node and coordination of this activity.
knst repl/stats replication statistics collection
knt kntg kntx repl/trigger support for replication internal trigger operation
koc objmgmt/objcache support for managing ADTs objects in the OOCI heap
kod objmgmt/datamgr support for persistent storage for
objects : for read/write objects, to manage object IDs, and to manage
object concurrency and recovery.
koh objmgmt/objcache object heap manager provides memory allocation services for objects
koi objmgmt/objmgr support for object types
koka objsupp/objdata support for reading images, inserting images, updating images, and deleting images based on object references (REFs).
kokb kokb2 objsupp/objsql support for nested table objects
kokc objmgmt/objcache support for pinning , unpinning and freeing objects
kokd objsupp/datadrv driver on the server side for managing objects
koke koke2 koki objsupp/objsql support for managing objects
kokl objsupp/objdata lob access
kokl2 objsupp/objsql lob DML and programmatic interface support
kokl3 objsupp/objdata object temporary LOB support
kokle kokm objsupp/objsql object SQL evaluation functions
kokn objsupp/objname naming support for objects
koko objsupp/objsup support functions to allow oci/rpi to communicate with Object Management Subsystem (OMS).
kokq koks koks2 koks3 koksr objsupp/objsql query optimisation for objects , semantic checking and semantic rewrite operations
kokt kokt2 kokt3 objsupp/objddl object compilation type manager
koku kokv objsupp/objsql support for unparse object operators and object view support
kol kolb kole kolf kolo objmgmt/objmgr support for object Lob buffering , object lob evaluation and object Language/runtime functions for Opaque types
kope2 kopi2 kopo kopp2 kopu koputil kopz objmgmt/pickler 8.1 engine implementation,
implementation of image ops for 8.1+ image format together with various
pickler related support functions
kos objsupp/objsup object Stream interfaces for images/objects
kot kot2 kotg objmgmt/typemgr support for dynamic type operations to create, delete, and update types.
koxs koxx objmgmt/objmgt object generic image Stream routines and miscellaneous generic object functions
kpcp kpcxlt progint/kpc Kernel programmatic connection pooling and kernel programmatic common type XLT translation routines
kpki progint/kpki kernel programatic interface support
kpls cartserv/corecs support for string formatting operations
kpn progint/kpn support for server to server communication
kpoal8 kpoaq kpob kpodny kpodp kpods kpokgt kpolob kpolon kpon progint/kpo support for programmatic operations
kpor progint/opi support for streaming protocol used by replication
kposc progint/kpo support for scrollable cursors
kpotc progint/opi oracle side support functions for setting up trusted external procedure callbacks
kpotx kpov progint/kpo support for managing local and distributed transaction coordination.
kpp2 kpp3 sqllang/parse kpp2 – parse routines for dimensions;
kpp3 – parse support for create/alter/drop summary statements
kprb kprc progint/rpi support for executing sql efficiently on the Oracle server side as well as for copying data types during rpi operations
kptsc progint/twotask callback functions provided to all streaming operation as part of replication functionality
kpu kpuc kpucp progint/kpu Oracle kernel side programmatic user interface, cursor management functions and client side connection pooling support
kqan kqap kqas argusdbg/argusdbg server-side notifiers and callbacks for debug operations.
kql kqld kqlp dict/libcache SQL Library Cache manager – manages the sharing of sql statements in the shared pool
kqr dict/rowcache row cache management. The row cache
consists of a set of facilities to provide fast access to table
definitions and locking capabilities.
krbi krbx krby krcr krd krpi rcv Backup and recovery related operations :
krbi
– dbms_backup_restore package underlying support.; krbx – proxy copy
controller; krby – image copy; krcr – Recovery Controlfile Redo; krd –
Recover Datafiles (Media & Standby Recovery); krpi – support for the
package : dbms_pitr
krvg krvt rcv/vwr krvg – support for generation of redo associated with DDL; krvt – support for redo log miner viewer (also known as log miner)
ksa ksdp ksdx kse ksfd ksfh ksfq ksfv ksi ksim ksk ksl ksm ksmd ksmg ksn ksp kspt ksq ksr kss ksst ksu ksut vos support for various kernel associated capabilities
ksx sqlexec/execsvc support for query execution associated with temporary tables
ksxa ksxp ksxr vos support for various kernel associated capabilities in relation to OPS or RAC operation
kta space/spcmgmt support for DML locks and temporary tables associated with table access
ktb ktbt ktc txn/lcltx transaction control operations at the block level : locking block, allocating space within the block , freeing up space, etc.
ktec ktef ktehw ktein ktel kteop kteu space/spcmgmt support for extent management operations :
ktec
– extent concurrency operations; ktef – extent format; ktehw – extent
high water mark operations; ktein – extent information operations; ktel –
extent support for sql loader; kteop – extent operations : add extent
to segment, delete extent, resize extent, etc. kteu – redo support for
operations changing segment header / extent map
ktf txn/lcltx flashback support
ktfb ktfd ktft ktm space/spcmgmt ktfb – support for bitmapped space
manipulation of files/tablespaces; ktfd – dictionary-based extent
management; ktft – support for temporary file manipulation; ktm – SMON
operation
ktp ktpr ktr ktri txn/lcltx ktp – support for parallel
transaction operation; ktpr – support for parallel transaction recovery;
ktr – kernel transaction read consistency;

ktri – support for dbms_resumable package
ktsa ktsap ktsau ktsb ktscbr ktsf ktsfx ktsi ktsm ktsp ktss ktst ktsx ktt kttm space/spcmgmt support for checking and verifying space usage
ktu ktuc ktur ktusm txn/lcltx internal management of undo and rollback segments
kwqa kwqi kwqic kwqid kwqie kwqit kwqj kwqm kwqn kwqo kwqp kwqs kwqu kwqx oltp/qs support for advanced queuing :
kwqa
– advanced queue administration; kwqi – support for AQ PL/SQL trusted
callouts; kwqic – common AQ support functions; kwqid – AQ dequeue
support; kwqie – AQ enqueu support ; kwqit – time management operation ;
kwqj – job queue scheduler for propagation; kwqm – Multiconsumer queue
IOT support; kwqn – queue notifier; kwqo – AQ support for checking
instType checking options; kwqp – queueing propagation; kwqs –
statistics handling; kwqu – handles lob data. ; kwqx – support for
handling transformations
kwrc kwre oltp/re rules engine evaluation
kxcc kxcd kxcs sqllang/integ constraint processing
kxdr sqlexec/dmldrv DML driver entrypoint
kxfp kxfpb kxfq kxfr kxfx sqlexec/pq parallel query support
kxhf kxib sqlexec/execsvc khhf- support for hash join file and memory management; kxib – index buffering operations
kxs dict/instmgmt support for executing shared cursors
kxti kxto kxtr dbproc/trigger support for trigger operation
kxtt ram/partnmap support for temporary table operations
kxwph ram/data support for managing attributes of the segment of a table / cluster / table-partition
kza security/audit support for auditing operations
kzar security/dac support for application auditing
kzck security/crypto encryption support
kzd security/dac support for dictionary access by security related functions
kzec security/dbencryption support inserting and retrieving encrypted objects into and out of the database
kzfa kzft security/audit support for fine grained auditing
kzia security/logon identification and authentication operations
kzp kzra kzrt kzs kzu kzup security/dac security related operations associated with privileges
msqima msqimb sqlexec/sqlgen support for generating sql statments
ncodef npi npil npixfr progint/npi support for managing remote network connection from within the server itself
oba sqllang/outbufal operator buffer allocate for various types of operators : concatenate, decode, NVL, etc. the list is extensive.
ocik progint/oci OCI oracle server functions
opiaba opidrv opidsa opidsc opidsi opiexe opifch opiino opilng opipar opipls opirip opitsk opix progint/opi OPI Oracle server functions – these
are at the top of the server stack and are called indirectly by ythe
client in order to server the client request.
orlr objmgmt/objmgr support for C langauge interfaces to user-defined types (UDTs)
orp objmgmt/pickler oracle’s external pickler / opaque type interfaces
pesblt pfri pfrsqc plsql/cox pesblt – pl/sql built in interpreter; pfri – pl/sql runtime; pfrsqc – pl/sql callbacks for array sql and dml with returning
piht plsql/gen/utl support for pl/sql implementation of utl_http package
pirg plsql/cli/utl_raw support for pl/sql implementation of utl_raw package
pism plsql/cli/utl_smtp support for pl/sql implementation of utl_smtp package
pitcb plsql/cli/utl_tcp support for pl/sql implementation of utl_tcp package
piur plsql/gen/utl_url support for pl/sql implementation of utl_url package
plio plsql/pkg pl/sql object instantiation
plslm plsql/cox support for NCOMP processing
plsm pmuc pmuo pmux objmgmt/pol support for pl/sql handling of collections
prifold priold plsql/cox support to allow rpc forwarding to an older release
prm sqllang/param parameter handling associated with sql layer
prsa prsc prssz sqllang/parse prsa – parser for alter cluster
command; prsc – parser for create database command; prssz – support for
parse context to be saved
psdbnd psdevn progint/dbpsd psdbnd – support for managing bind variables; psdevn – support for pl/sql debugger
psdicd progint/plsicds small number of ICD to allow pl/sql to call into ‘C’ source
psdmsc psdpgi progint/dbpsd psdmsc – pl/sql system dependent miscellaneous functions ; psdpgi – support for opening and closing cursors in pl/sql
psf plsql/pls pl/sql service related functions for instantiating called pl/sql unit in library cache
qbadrv qbaopn sqllang/qrybufal provides allocation of buffer and control structures in query execution
qcdl qcdo dict/dictlkup qcdl – query compile semantic analysis; qcdo – query compile dictionary support for objects
qci dict/shrdcurs support for SQL language parser and semantic analyser
qcop qcpi qcpi3 qcpi4 qcpi5 sqllang/parse support for query compilation parse phase
qcs qcs2 qcs3 qcsji qcso dict/dictlkup support for semantic analysis by SQL compiler
qct qcto sqllang/typeconv qct – query compile type check operations; qcto – query compile type check operators
qcu sqllang/parse various utilities provided for sql compilation
qecdrv sqllang/qryedchk driver performing high level checks on sql language query capabilities
qerae qerba qerbc qerbi qerbm qerbo qerbt
qerbu qerbx qercb qercbi qerco qerdl qerep qerff qerfi qerfl qerfu
qerfx qergi qergr qergs qerhc qerhj qeril qerim qerix qerjm qerjo qerle
qerli qerlt qerns qeroc qeroi qerpa qerpf qerpx qerrm qerse qerso qersq
qerst qertb qertq qerua qerup qerus qervw qerwn qerxt
sqlexec/rowsrc row source operators :
qerae
– row source (And-Equal) implementation; qerba – Bitmap Index AND row
source; qerbc – bitmap index compaction row source; qerbi – bitmap index
creation row source; qerbm – QERB Minus row source; qerbo – Bitmap
Index OR row source; qerbt – bitmap convert row source; qerbu – Bitmap
Index Unlimited-OR row source; qerbx – bitmap index access row source;
qercb – row source: connect by; qercbi – support for connect by; qerco –
count row source; qerdl – row source delete; qerep – explosion row
source; qerff – row source fifo buffer; qerfi – first row row source;
qerfl – filter row source definition; qerfu – row source: for update;
qerfx – fixed table row source; qergi – granule iterator row source;
qergr – group by rollup row source; qergs – group by sort row source;
qerhc – row sources hash clusters; qerhj – row source Hash Join; qeril –
In-list row source; qerim – Index Maintenance row source; qerix – Index
row source; qerjo – row source: join; qerle – linear execution row
source implementation; qerli – parallel create index; qerlt – row source
populate Table; qerns – group by No Sort row source; qeroc – object
collection iterator row source; qeroi – extensible indexing query
component; qerpa – partition row sources; qerpf – query execution row
source: prefetch; qerpx – row source: parallelizer; qerrm – remote row
source; qerse – row source: set implementation; qerso – sort row source;
qersq – row source for sequence number; qerst – query execution row
sources: statistics; qertb – table row source; qertq – table queue row
source; qerua – row source : union-All;

qerup – update
row source; qerus – upsert row source ; qervw – view row source; qerwn –
WINDOW row source; qerxt – external table fetch row source
qes3t qesa qesji qesl qesmm qesmmc sqlexec/execsvc run time support for sql execution
qkacon qkadrv qkajoi qkatab qke qkk qkn qkna qkne sqlexec/rwsalloc SQL query dynamic structure allocation routines
qks3t sqlexec/execsvc query execution service associated with temp table transformation
qksmm qksmms qksop sqllang/compsvc qksmm – memory management services
for the SQL compiler; qksmms – memory management simulation services for
the SQL compiler; qksop – query compilation service for operand
processing
qkswc sqlexec/execsvc support for temp table transformation associated for with clause.
qmf xmlsupp/util support for ftp server; implements processing of ftp commands
qmr qmrb qmrs xmlsupp/resolver support hierarchical resolver
qms xmlsupp/data support for storage and retrieval of XOBs
qmurs xmlsupp/uri support for handling URIs
qmx qmxsax xmlsupp/data qmx – xml support; qmxsax – support for handling sax processing
qmxtc xmlsupp/sqlsupp support for ddl and other operators related to the sql XML support
qmxtgx xmlsupp support for transformation : ADT -> XML
qmxtsk xmlsupp/sqlsupp XMLType support functions
qsme summgmt/dict summary management expression processing
qsmka qsmkz dict/dictlkup qsmka – support to analyze request
in order to determine whether a summary could be created that would be
useful; qsmkz – support for create/alter summary semantic analysis
qsmp qsmq qsmqcsm qsmqutl summgmt/dict qsmp – summary management partition
processing; qsmq – summary management dictionary access; qsmqcsm –
support for create / drop / alter summary and related dimension
operations; qsmqutl – support for summaries
qsms summgmt/advsvr summary management advisor
qxdid objsupp/objddl support for domain index ddl operations
qxidm objsupp/objsql support for extensible index dml operations
qxidp objsupp/objddl support for domain index ddl partition operations
qxim objsupp/objsql extensible indexing support for objects
qxitex qxopc qxope objsupp/objddl qxitex – support for create / drop
indextype; qxope – execution time support for operator callbacks; qxope –
execution time support for operator DDL
qxopq qxuag qxxm objsupp/objsql qxopq – support for queries with
user-defined operators; qxuag – support for user defined aggregate
processing; qxxm – queries involving external tables
rfmon rfra rfrdb rfrla rfrm rfrxpt drs implements 9i data guard broker monitor
rnm dict/sqlddl manages rename statement operation
rpi progint/rpi recursive procedure interface which
handles the the environment setup where multiple recursize statements
are executed from one top level statement
rwoima sqlexec/rwoprnds row operand operations
rwsima sqlexec/rowsrc row source implementation/retrieval according to the defining query
sdbima sqlexec/sort manages and performs sort operation
selexe sqlexec/dmldrv handles the operation of select statement execution
skgm osds platform. specific memory management rountines interfacing with O.S. allocation functions
smbima sor sqlexec/sort manages and performs sort operation
sqn dict/sqlddl support for parsing references to sequences
srdima srsima stsima sqlexec/sort manages and performs sort operation
tbsdrv space/spcmgmt operations for executing create / alter / drop tablespace and related supporting functions
ttcclr ttcdrv ttcdty ttcrxh ttcx2y progint/twotask two task common layer which provides
high level interaction and negotiation functions for Oracle client when
communicating with the server. It also provides important function of
converting client side data / data types into equivalent on the server
and vice versa
uixexe ujiexe updexe upsexe sqlexec/dmldrv support for : index maintenance
operations, the execution of the update statement and associated actions
connected with update as well as the upsert command which combines the
operations of update and insert
vop optim/vwsubq view optimisation related functionality
xct txn/lcltx support for the management of transactions and savepoint operations
xpl sqlexec/expplan support for the explain plan command
xty sqllang/typeconv type checking functions
zlke security/ols/intext label security error handling component

References

NOTE:153788.1– ORA-600/ORA-7445/ORA-700 Error Look-up Tool

oracle healthcheck scripts ( add to monitoring )

oracle healthcheck scripts ( add to monitoring ) published on 1 комментарий к записи oracle healthcheck scripts ( add to monitoring )

script to monitor tablespace usage, in this example warn me if tablespace usage is more than 85% and free space in in lower than 30GB

with add_datafile_view as
(select rec.tbs_cnt,round(sum(df.bytes)/1024/1024/1024,2) as free_space,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
  'alter tablespace '||rec.tablespace_name|| ' add datafile '''||
  substr(d.file_name,1,length (d.file_name) -4) ||'_'||
  to_char (rec.tbs_cnt +1) ||'.ora'' size 1G AUTOEXTEND ON NEXT 100M MAXSIZE unlimited'                           AS ADD_DATAFILE
  from dba_data_files d,
  (
SELECT COUNT(*) AS tbs_cnt,min(dd.file_id) as min_file_id,
  dd.tablespace_name,
  ROUND (SUM(dd.bytes   /1024/1024/1024))                                                                             AS tot_size,
  ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024) )                                                 AS max_size,
   ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024)  -ROUND (SUM(dd.bytes )/1024/1024/1024))        AS free_gb,
  100                * ROUND (SUM(dd.bytes/1024/1024/1024)/ SUM( NVL(NULLIF( dd.maxbytes , 0),dd.bytes) /1024/1024/1024),3) AS usage_pct
FROM  DBA_DATA_FILES dd, dba_tablespaces dt
               WHERE   DD.TABLESPACE_NAME = Dt.TABLESPACE_NAME
                       AND DD.STATUS = 'AVAILABLE'
                       and dt.status <>'READ ONLY'
                       and dt.contents='PERMANENT'
GROUP BY dd.tablespace_name
)rec,    dba_Free_space df
  where d.file_id = rec.min_file_id
    and df.tablespace_name=rec.tablespace_name
    group by rec.tbs_cnt,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
      'alter tablespace '||rec.tablespace_name|| ' add datafile '''||
  substr(d.file_name,1,length (d.file_name) -4) ||'_'||
  to_char (rec.tbs_cnt +1) ||'.ora'' size 1G AUTOEXTEND ON NEXT 100M MAXSIZE unlimited'
  order by rec.usage_pct desc)
 
select * from sys.add_datafile_view where free_space < 30  and USAGE_PCT > 85;

same thing but for asm :

with add_datafile_view as (
select rec.tbs_cnt,round(sum(df.bytes)/1024/1024/1024,2) as free_space,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
  'alter tablespace '||rec.tablespace_name|| ' add datafile ' as add_datafile
  from   dba_Free_space df,
  (
SELECT COUNT(*) AS tbs_cnt,min(dd.file_id) as min_file_id,
  dd.tablespace_name,
  ROUND (SUM(dd.bytes   /1024/1024/1024))                                                                             AS tot_size,
  ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024) )                                                 AS max_size,
   ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024)  -ROUND (SUM(dd.bytes )/1024/1024/1024))        AS free_gb,
  100                * ROUND (SUM(dd.bytes/1024/1024/1024)/ SUM( NVL(NULLIF( dd.maxbytes , 0),dd.bytes) /1024/1024/1024),3) AS usage_pct
FROM  DBA_DATA_FILES dd, dba_tablespaces dt
               WHERE   DD.TABLESPACE_NAME = Dt.TABLESPACE_NAME
                       AND DD.STATUS = 'AVAILABLE'
                       and dt.status <>'READ ONLY'
                       and dt.contents='PERMANENT'
GROUP BY dd.tablespace_name
--HAVING 100* ROUND (SUM(dd.bytes/1024/1024/1024)/ SUM( NVL(NULLIF( dd.maxbytes , 0),dd.bytes) /1024/1024/1024),3) >75
--and ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024) ) -ROUND (SUM(dd.bytes/1024/1024/1024)) < 100
) rec
  where df.tablespace_name=rec.tablespace_name
    group by rec.tbs_cnt,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
      'alter tablespace '||rec.tablespace_name|| ' add datafile '''
  order by rec.usage_pct desc )
  select * from sys.add_datafile_view where free_space < 30  and USAGE_PCT > 85;

check scheduler state ( last running job status )

col log_date format a35
col STATUS format a10
col JOB_NAME format a40
col JOB_ERR_LOG format a100
select log_date,status,job_name,job_err_log from (
SELECT
  log_date
  ,STATUS
  ,OWNER ||'.'||JOB_NAME as job_name
  ,dbms_lob.substr(ADDITIONAL_INFO,400,1) as job_err_log
  ,rank() over (partition by OWNER ||'.'||JOB_NAME order by log_date desc) rnk
FROM
  dba_scheduler_job_run_details
WHERE 1=1
  and owner not like 'SYS%'
GROUP BY
  STATUS
  ,OWNER ||'.'||JOB_NAME,log_date
   , dbms_lob.substr(ADDITIONAL_INFO,400,1) ) t where rnk=1 and status !='SUCCEEDED';

check invalid objects



col object_type format a15
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

check unusable index

select ' alter index '||OWNER||'.'|| index_name||' rebuild online ;' as rebuild_script from DBA_INDexes where (status  not in ( 'USABLE','VALID','N/A') or (DOMIDX_OPSTATUS<>'VALID' or DOMIDX_STATUS<>'VALID' or FUNCIDX_STATUS ='DISABLED')) and owner not like '%SYS%'
union all
select ' alter index '||INDEX_owner||'.'|| index_name||' rebuild PARTITION '|| PARTITION_NAME ||' online ;' from  DBA_IND_PARTITIONS     where status  not in ( 'USABLE','VALID','N/A')
union all
select  'alter index '||INDEX_owner||'.'|| index_name||' rebuild SUBPARTITION '|| SUBPARTITION_NAME ||' online ;' from  DBA_IND_SUBPARTITIONS     where status  not in ( 'USABLE','VALID','N/A');

postgres monitoring metrics

postgres monitoring metrics published on 2 комментария к записи postgres monitoring metrics
postgres=#
SELECT COUNT(1) AS session_count,
  SUM(  CASE    WHEN state = 'active'    THEN 1    ELSE 0  END) AS active,
  SUM(  CASE    WHEN state LIKE 'idle in%'    THEN 1    ELSE 0  END) AS idle_in_transaction,
  SUM(  CASE    WHEN now()-query_start > INTERVAL '1s'    AND state  = 'active'    THEN 1    ELSE 0  END) AS slow_query_1s,
  SUM(  CASE    WHEN now()-query_start > INTERVAL '600s'    AND state = 'active'    THEN 1    ELSE 0 END)              AS slow_query_600s,
  SUM(waiting::INT) AS waiting_query,
  CASE pg_is_in_recovery()    WHEN true    THEN 1    ELSE 0  END AS is_in_recovery,
  CASE pg_is_in_recovery()    WHEN true    THEN extract(epoch FROM now() - COALESCE(pg_last_xact_replay_timestamp(),now()))    ELSE 0   END AS replication_delay
FROM pg_stat_activity ;
 session_count | active | idle_in_transaction | slow_query_1s | slow_query_600s | waiting_query | is_in_recovery | replication_delay
---------------+--------+---------------------+---------------+-----------------+---------------+----------------+-------------------
           177 |      3 |                   0 |             2 |               1 |             0 |              0 |                 0

enable Oracle Label Security

enable Oracle Label Security published on Комментариев к записи enable Oracle Label Security нет

Oracle Database 12c: Security 13 – 3

Enabling and Managing OLS

•Register and enable OLS:
– Using DBCA or
– Executing the LBACSYS.CONFIGURE_OLS and LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS procedures
•The The LBACSYS schema owns all OLS objects.
•Use Enterprise Manager Cloud Control (EMCC) SQL*Plus to manage the Label Security policies.
•In PDBs, which canhttp://orabase.org/wp-admin/edit-tags.php?taxonomy=post_tag be plugged in and out of CDBs:
– Manage OLS components on a subset of PDBs in a CDB:
— Policies (no policies in the root)
— Data labels
— User authorizations
– The LBACSYS schema is a common user schema.
– LBACSYS objects are automatically available to any PDB.

OLS: Features

OLS provides:
• Row-level security based on Virtual Private Database (VPD) technology
– All required packages for access mediation
– Complete data dictionary for managing OLS components
•A complete infrastructure for managing label security policies, sensitivity labels, and user security clearances
• Enterprise Manager pages containing a graphical user interface for managing OLS
• Integration with Oracle Identity Management starting Oracle Database 10g Release 1

OLS is built on the fine-grained access control technology of VPD. The major advantage of using OLS is that OLS is a complete system. It is a ready-to-use VPD. OLS provides sophisticated functions and procedures for evaluating and comparing sensitivity labels. It provides a sophisticated infrastructure for storing and managing sensitivity labels and user security clearances.

SELECT status FROM DBA_OLS_STATUS WHERE name = 'OLS_CONFIGURE_STATUS';

22:34:47 (1)[PDB1]c##bushmelev_aa@p00db1> SELECT status FROM DBA_OLS_STATUS WHERE name = 'OLS_CONFIGURE_STATUS';

STATUS
------
FALSE

Register OLS.

22:39:02 (1)[PDB1]sys@p00db1> EXEC LBACSYS.CONFIGURE_OLS

PL/SQL procedure successfully completed.

22:39:06 (1)[PDB1]sys@p00db1> SELECT status FROM DBA_OLS_STATUS WHERE name = 'OLS_CONFIGURE_STATUS';

STATU
-----
TRUE

Check whether OLS is enabled.

22:40:33 (1)[PDB1]sys@p00db1> SELECT value FROM V$OPTION WHERE parameter = 'Oracle Label Security';

VALUE
----------------------------------------------------------------
FALSE

Enable OLS

22:48:46 (1)[PDB1]sys@p00db1>  EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53
22:49:45 (1)[PDB1]sys@p00db1> SELECT value FROM V$OPTION WHERE parameter = 'Oracle Label Security';

VALUE
----------------------------------------------------------------
TRUE

ps: Registration and enabling execute at the container level.

pps:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options

OLS2

Implementing an OLS Solution

OLS3

OLS4

check OLS:
http://www.oracle.com/technetwork/articles/idm/ls-093349.html

create user myco_emp identified by 1;
create user myco_mgr identified by 1;
create user myco_planning identified by 1;

 GRANT CREATE SESSION to MYCO_EMP ;
 GRANT CREATE SESSION to MYCO_MGR;
 GRANT CREATE SESSION to MYCO_PLANNING ;


grant select on hr.job_history to MYCO_PLANNING;
grant select on hr.job_history to MYCO_emp;
grant select on hr.job_history to MYCO_mgr;
 GRANT SELECT ON hr.LOCATIONS TO MYCO_EMP;
 GRANT SELECT ON hr.LOCATIONS TO MYCO_mgr;
 GRANT SELECT ON hr.LOCATIONS TO MYCO_PLANNING;
 GRANT SELECT, INSERT, UPDATE, DELETE ON HR.LOCATIONS TO  MYCO_PLANNING;

connect as sysdba or unlock LBACSYS and connect by it

 -- ****************************************************
 -- Creating FACILITY Policy
 -- ****************************************************
 BEGIN
 SA_SYSDBA.CREATE_POLICY('FACILITY','FACLAB',
 'READ_CONTROL,CHECK_CONTROL,LABEL_DEFAULT,HIDE');
 END;
 /

 -- ****************************************************
 -- Adding sensitivity levels to FACILITY policy:
 -- ****************************************************
BEGIN
SA_COMPONENTS.CREATE_LEVEL('FACILITY', 1000,'P','PUBLIC');
SA_COMPONENTS.CREATE_LEVEL('FACILITY',2000,'S','SENSITIVE');
SA_COMPONENTS.CREATE_LEVEL('FACILITY',3000,'HS','HIGHLY_SENSITIVE');
END;
/



 -- ****************************************************
 -- Adding groups to FACILITY policy:
 -- ****************************************************
 BEGIN
 SA_COMPONENTS.CREATE_GROUP('FACILITY', 1000,'Global','Global');
 SA_COMPONENTS.CREATE_GROUP('FACILITY',101,'US','United States','GLOBAL');
 SA_COMPONENTS.CREATE_GROUP('FACILITY',102,'EU','Europe','GLOBAL');
 SA_COMPONENTS.CREATE_GROUP('FACILITY',103,'Asia','Asia','GLOBAL');
 END;
 /


 -- ****************************************************
 -- Creating Labels for FACILITY policy
 -- ****************************************************
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY', 1000,'P');
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',2101,'S::US');
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',3101,'HS::US');
 EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY', 2103,'S::ASIA');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',3103,'HS::ASIA');


 -- **************************************************
 -- Setting MYCO_EMP user label authorizations
 -- Setting MYCO_MGR user label authorizations
 -- Setting MYCO_PLANNING user label authorizations
 -- **************************************************
 exec SA_USER_ADMIN.SET_USER_LABELS ('PRIVACY', 'MYCO_MGR','C');
 exec SA_USER_ADMIN.SET_USER_LABELS ('FACILITY','MYCO_EMP','P');
 exec SA_USER_ADMIN.SET_USER_LABELS ('FACILITY','MYCO_MGR','S::US,EU,ASIA');
 exec SA_USER_ADMIN.SET_USER_LABELS ('FACILITY','MYCO_PLANNING','HS::GLOBAL');

-- Applying FACILITY policy to hr.locations table.
-- *************************************************
 Begin
 sa_policy_admin.apply_table_policy (
 POLICY_NAME => 'FACILITY',
 SCHEMA_NAME => 'HR',
 TABLE_NAME => 'LOCATIONS',
 TABLE_OPTIONS => NULL,
 LABEL_FUNCTION => NULL);
 END;
 /


 -- ****************************************************
 -- Update Labels for Sites In ASIA
 -- ****************************************************

 update hr.locations set faclab = char_to_label('FACILITY','S::ASIA') where upper(city) in ('BEIJING','TOKYO','SINGAPORE');

 -- ****************************************************
 -- Update Labels for Sites In US
 -- ****************************************************

 update hr.locations set faclab = char_to_label('FACILITY','HS::US') where upper(city) in ('SOUTH SAN FRANCISCO');


 -- ****************************************************
 -- Update Labels for all remaining locations
 -- ****************************************************

 update hr.locations set faclab = char_to_label('FACILITY','P') where faclab is NULL;


commit;


                                


where upper(city) in ('BEIJING','TOKYO','SINGAPORE');
-- ****************************************************
-- SETTING LABELS FOR PRIVACY POLICY
-- ****************************************************


connect hr/oracle_4U@localhost:1521/pdb1

last piece of code failed with error :

 update hr.job_history set privlab = char_to_label('PRIVACY','S') where ((to_char(sysdate,'YYYY') - to_char(end_date,'YYYY')) > 10);
 update hr.job_history set privlab = char_to_label('PRIVACY','C') where ((to_char(sysdate,'YYYY')- to_char(end_date,'YYYY')) <= 10);

now lets check emp:

01:13:40 not connected> select l.*,label_to_char(faclab) label from hr.locations l;

LOCATION_ID | STREET_ADDRESS                           | POSTAL_CODE  | CITY                           | STATE_PROVINCE            | CO | LABEL
----------- | ---------------------------------------- | ------------ | ------------------------------ | ------------------------- | -- | ----------
       1000 | 1297 Via Cola di Rie                     | 00989        | Roma                           | <NULL>                    | IT | P
       1100 | 93091 Calle della Testa                  | 10934        | Venice                         | <NULL>                    | IT | P
       1300 | 9450 Kamiya-cho                          | 6823         | Hiroshima                      | <NULL>                    | JP | P
       1400 | 2014 Jabberwocky Rd                      | 26192        | Southlake                      | Texas                     | US | P
       1600 | 2007 Zagora St                           | 50090        | South Brunswick                | New Jersey                | US | P
       1700 | 2004 Charade Rd                          | 98199        | Seattle                        | Washington                | US | P
       1800 | 147 Spadina Ave                          | M5V 2L7      | Toronto                        | Ontario                   | CA | P
       1900 | 6092 Boxwood St                          | YSW 9T2      | Whitehorse                     | Yukon                     | CA | P
       2100 | 1298 Vileparle (E)                       | 490231       | Bombay                         | Maharashtra               | IN | P
       2200 | 12-98 Victoria Street                    | 2901         | Sydney                         | New South Wales           | AU | P
       2400 | 8204 Arthur St                           | <NULL>       | London                         | <NULL>                    | UK | P
       2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB      | Oxford                         | Oxford                    | UK | P
       2600 | 9702 Chester Road                        | 09629850293  | Stretford                      | Manchester                | UK | P
       2700 | Schwanthalerstr. 7031                    | 80925        | Munich                         | Bavaria                   | DE | P
       2800 | Rua Frei Caneca 1360                     | 01307-002    | Sao Paulo                      | Sao Paulo                 | BR | P
       2900 | 20 Rue des Corps-Saints                  | 1730         | Geneva                         | Geneve                    | CH | P
       3000 | Murtenstrasse 921                        | 3095         | Bern                           | BE                        | CH | P
       3100 | Pieter Breughelstraat 837                | 3029SK       | Utrecht                        | Utrecht                   | NL | P
       3200 | Mariano Escobedo 9991                    | 11932        | Mexico City                    | Distrito Federal,         | MX | P

19 rows selected.

01:13:42 not connected> sho user
USER is "MYCO_EMP"

check mgr

01:16:10 not connected> sho user
USER is "MYCO_MGR"
01:16:12 not connected> select l.*,label_to_char(faclab) label from hr.locations l;

LOCATION_ID | STREET_ADDRESS                           | POSTAL_CODE  | CITY                           | STATE_PROVINCE            | CO | LABEL
----------- | ---------------------------------------- | ------------ | ------------------------------ | ------------------------- | -- | ----------
       1000 | 1297 Via Cola di Rie                     | 00989        | Roma                           | <NULL>                    | IT | P
       1100 | 93091 Calle della Testa                  | 10934        | Venice                         | <NULL>                    | IT | P
       1200 | 2017 Shinjuku-ku                         | 1689         | Tokyo                          | Tokyo Prefecture          | JP | S::ASIA
       1300 | 9450 Kamiya-cho                          | 6823         | Hiroshima                      | <NULL>                    | JP | P
       1400 | 2014 Jabberwocky Rd                      | 26192        | Southlake                      | Texas                     | US | P
       1600 | 2007 Zagora St                           | 50090        | South Brunswick                | New Jersey                | US | P
       1700 | 2004 Charade Rd                          | 98199        | Seattle                        | Washington                | US | P
       1800 | 147 Spadina Ave                          | M5V 2L7      | Toronto                        | Ontario                   | CA | P
       1900 | 6092 Boxwood St                          | YSW 9T2      | Whitehorse                     | Yukon                     | CA | P
       2000 | 40-5-12 Laogianggen                      | 190518       | Beijing                        | <NULL>                    | CN | S::ASIA
       2100 | 1298 Vileparle (E)                       | 490231       | Bombay                         | Maharashtra               | IN | P
       2200 | 12-98 Victoria Street                    | 2901         | Sydney                         | New South Wales           | AU | P
       2300 | 198 Clementi North                       | 540198       | Singapore                      | <NULL>                    | SG | S::ASIA
       2400 | 8204 Arthur St                           | <NULL>       | London                         | <NULL>                    | UK | P
       2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB      | Oxford                         | Oxford                    | UK | P
       2600 | 9702 Chester Road                        | 09629850293  | Stretford                      | Manchester                | UK | P
       2700 | Schwanthalerstr. 7031                    | 80925        | Munich                         | Bavaria                   | DE | P
       2800 | Rua Frei Caneca 1360                     | 01307-002    | Sao Paulo                      | Sao Paulo                 | BR | P
       2900 | 20 Rue des Corps-Saints                  | 1730         | Geneva                         | Geneve                    | CH | P
       3000 | Murtenstrasse 921                        | 3095         | Bern                           | BE                        | CH | P
       3100 | Pieter Breughelstraat 837                | 3029SK       | Utrecht                        | Utrecht                   | NL | P
       3200 | Mariano Escobedo 9991                    | 11932        | Mexico City                    | Distrito Federal,         | MX | P

22 rows selected.

now check myco_planning:

USER is "MYCO_PLANNING"
01:17:57 not connected> select l.*,label_to_char(faclab) label from hr.locations l;

LOCATION_ID | STREET_ADDRESS                           | POSTAL_CODE  | CITY                           | STATE_PROVINCE            | CO | LABEL
----------- | ---------------------------------------- | ------------ | ------------------------------ | ------------------------- | -- | ----------
       1000 | 1297 Via Cola di Rie                     | 00989        | Roma                           | <NULL>                    | IT | P
       1100 | 93091 Calle della Testa                  | 10934        | Venice                         | <NULL>                    | IT | P
       1200 | 2017 Shinjuku-ku                         | 1689         | Tokyo                          | Tokyo Prefecture          | JP | S::ASIA
       1300 | 9450 Kamiya-cho                          | 6823         | Hiroshima                      | <NULL>                    | JP | P
       1400 | 2014 Jabberwocky Rd                      | 26192        | Southlake                      | Texas                     | US | P
       1500 | 2011 Interiors Blvd                      | 99236        | South San Francisco            | California                | US | HS::US
       1600 | 2007 Zagora St                           | 50090        | South Brunswick                | New Jersey                | US | P
       1700 | 2004 Charade Rd                          | 98199        | Seattle                        | Washington                | US | P
       1800 | 147 Spadina Ave                          | M5V 2L7      | Toronto                        | Ontario                   | CA | P
       1900 | 6092 Boxwood St                          | YSW 9T2      | Whitehorse                     | Yukon                     | CA | P
       2000 | 40-5-12 Laogianggen                      | 190518       | Beijing                        | <NULL>                    | CN | S::ASIA
       2100 | 1298 Vileparle (E)                       | 490231       | Bombay                         | Maharashtra               | IN | P
       2200 | 12-98 Victoria Street                    | 2901         | Sydney                         | New South Wales           | AU | P
       2300 | 198 Clementi North                       | 540198       | Singapore                      | <NULL>                    | SG | S::ASIA
       2400 | 8204 Arthur St                           | <NULL>       | London                         | <NULL>                    | UK | P
       2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB      | Oxford                         | Oxford                    | UK | P
       2600 | 9702 Chester Road                        | 09629850293  | Stretford                      | Manchester                | UK | P
       2700 | Schwanthalerstr. 7031                    | 80925        | Munich                         | Bavaria                   | DE | P
       2800 | Rua Frei Caneca 1360                     | 01307-002    | Sao Paulo                      | Sao Paulo                 | BR | P
       2900 | 20 Rue des Corps-Saints                  | 1730         | Geneva                         | Geneve                    | CH | P
       3000 | Murtenstrasse 921                        | 3095         | Bern                           | BE                        | CH | P
       3100 | Pieter Breughelstraat 837                | 3029SK       | Utrecht                        | Utrecht                   | NL | P
       3200 | Mariano Escobedo 9991                    | 11932        | Mexico City                    | Distrito Federal,         | MX | P

23 rows selected.

Primary Sidebar

Яндекс.Метрика