Skip to content

ORA-25152: TEMPFILE cannot be dropped at this time

ORA-25152: TEMPFILE cannot be dropped at this time published on Комментариев к записи ORA-25152: TEMPFILE cannot be dropped at this time нет

hello, here how I dropped tempfile :

start with:

ALTER TABLESPACE temp  DROP TEMPFILE '+DATADG/p00pgp/tempfile/temp.340.882540285';
ALTER TABLESPACE temp  DROP TEMPFILE '+DATADG/p00pgp/tempfile/temp.339.882540283';

but when I try to drop datafiles I’ve got this error:
ORA-25152: TEMPFILE cannot be dropped at this time

helped this sript

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     and b.segfile# in ( 205,206)
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

which helped me to find sessions using tempfile
sessions was idle, so I kill them, after that this command have no errors:

ALTER DATABASE TEMPFILE '+DATADG/p00pgp/tempfile/temp.339.882540283' DROP      INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '+DATADG/p00pgp/tempfile/temp.340.882540285' DROP      INCLUDING DATAFILES;

postgresql pgpool-II autofailover and failback

postgresql pgpool-II autofailover and failback published on Комментариев к записи postgresql pgpool-II autofailover and failback нет

Hello, try to find a good material about how-to configure pgpool and found nothing, so I’ve decide to write my own article =))

I’m recommend to read previous articles:
How to install postgres
How to configure repmgr to manage standby databases
because I use those config in this scenario

So, lets start =)

First of all lest configure OS to match our needs ( I had to use RH 6 distributive )
1. configure passwordless ssh to all nodes from root and from postgres user
2. configure passwordless sudo to allow this commands:
add to /etc/sudoers

postgres ALL=(root) NOPASSWD: /sbin/ip
postgres ALL=(root) NOPASSWD: /sbin/arping
postgres ALL=(root) NOPASSWD: /sbin/ifconfig
postgres ALL=(root) NOPASSWD: /etc/init.d/postgresql-9.5

To allow passwordless pcp commands create this file in postgres and root users:

cat $HOME/.pcppass
*:*:pgpooladmin:q1
chmod 600 .pcppass

now lets configure pgpool

start with easiest part, configure pcp.conf
to do this run pg_md5 command with password as first argument:

[postgres@pg1 ~]$ pg_md5 q1
ff33f1b12213e021c2c4a888141953ba

result add to pcp.conf with format of username:pg_md5_pass

echo "pgpooladmin:ff33f1b12213e021c2c4a888141953ba" >> pcp.conf

I’m allow connect to pgpool from whole network, to archive this I’ve add this line to pool_hba.conf

host	all		all	10.10.10.0/24	md5

next session is to configure pgpool.conf
I’ve made it from pgpool.conf.sample-stream

diff pgpool.conf.sample-stream pgpool.conf

27c27,28
< listen_addresses = 'localhost' --- > listen_addresses = ‘*’
>
65c66
< backend_hostname0 = 'host1' --- > backend_hostname0 = ‘pg1’
71c72
< backend_data_directory0 = '/data' --- > backend_data_directory0 = ‘/u01/postgres/9.5/main/pgdata’
76,80c77,89
< #backend_hostname1 = 'host2' < #backend_port1 = 5433 < #backend_weight1 = 1 < #backend_data_directory1 = '/data1' < #backend_flag1 = 'ALLOW_TO_FAILOVER' --- > backend_hostname1 = ‘pg2’
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = ‘/u01/postgres/9.5/main/pgdata’
> backend_flag1 = ‘ALLOW_TO_FAILOVER’
>
> backend_hostname2 = ‘pg3’
> backend_port2 = 5432
> backend_weight2 = 1
> backend_data_directory2 = ‘/u01/postgres/9.5/main/pgdata’
> backend_flag2 = ‘ALLOW_TO_FAILOVER’
>
>
84c93
< enable_pool_hba = off --- > enable_pool_hba = on
120c129
< num_init_children = 32 --- > num_init_children = 300
123c132
< max_pool = 4 --- > max_pool = 1
218c227
< pid_file_name = '/var/run/pgpool/pgpool.pid' --- > pid_file_name = ‘/var/run/pgpool-II-95/pgpool.pid’
333c342
< sr_check_user = 'nobody' --- > sr_check_user = ‘repmgr’
337c346
< sr_check_password = '' --- > sr_check_password = ‘q1’
348c357
< follow_master_command = '' --- > follow_master_command = ‘/u01/postgres/9.5/main/conf/follow_master.sh %d %H %m %P %h’
367c376
< health_check_period = 0 --- > health_check_period = 5
373c382
< health_check_user = 'nobody' --- > health_check_user = ‘repmgr’
375c384
< health_check_password = '' --- > health_check_password = ‘q1’
377c386
< health_check_database = '' --- > health_check_database = ‘postgres’
379c388
< health_check_max_retries = 0 --- > health_check_max_retries = 5
381c390
< health_check_retry_delay = 1 --- > health_check_retry_delay = 5
383c392
< connect_timeout = 10000 --- > connect_timeout = 30000
394c403
< failover_command = '' --- > failover_command = ‘/u01/postgres/9.5/main/conf/failover.sh %d %H %m %P %h’
408c417,418
< failback_command = '' --- > failback_command = ‘/u01/postgres/9.5/main/conf/stb_recreate.sh %d %H %m %P %h’
> #failback_command = »
439c449
< recovery_user = 'nobody' --- > recovery_user = ‘repmgr’
441c451
< recovery_password = '' --- > recovery_password = ‘q1’
465c475
< use_watchdog = off --- > use_watchdog = on
471c481
< trusted_servers = '' --- > trusted_servers = ‘app01,app02’
482c492
< wd_hostname = '' --- > wd_hostname = ‘pg1’
505c515
< delegate_IP = '' --- > delegate_IP = ‘10.10.10.20’
509c519
< if_cmd_path = '/sbin' --- > if_cmd_path = ‘/usr/bin’
512c522
< if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' --- > if_up_cmd = ‘sudo ip addr add $_IP_$/24 dev eth1 label eth1:1’
515c525
< if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' --- > if_down_cmd = ‘sudo ip addr del $_IP_$/24 dev eth1:1’
518c528
< arping_path = '/usr/sbin' --- > arping_path = ‘/usr/bin’
521c531
< arping_cmd = 'arping -U $_IP_$ -w 1' --- > arping_cmd = ‘sudo arping -U $_IP_$ -w 1’
569c579
< heartbeat_destination0 = 'host0_ip1' --- > heartbeat_destination0 = ‘pg2’
586,588c596,598
< #heartbeat_destination1 = 'host0_ip2' < #heartbeat_destination_port1 = 9694 < #heartbeat_device1 = '' --- > heartbeat_destination1 = ‘pg3’
> heartbeat_destination_port1 = 9694
> heartbeat_device1 = »
601c611
< wd_lifecheck_user = 'nobody' --- > wd_lifecheck_user = ‘pgpool’
604c614
< wd_lifecheck_password = '' --- > wd_lifecheck_password = ‘w7DeMth2oid3oYyE’
610c620
< #other_pgpool_hostname0 = 'host0' --- > other_pgpool_hostname0 = ‘pg2’
613c623
< #other_pgpool_port0 = 5432 --- > other_pgpool_port0 = 9999
616c626
< #other_wd_port0 = 9000 --- > other_wd_port0 = 9000
619,621c629,631
< #other_pgpool_hostname1 = 'host1' < #other_pgpool_port1 = 5432 < #other_wd_port1 = 9000 --- > other_pgpool_hostname1 = ‘p01db03’
> other_pgpool_port1 = 9999
> other_wd_port1 = 9000

[свернуть]

in my configuration I use trusted servers ( app servers ) you may leave it blank, also there was 3 node config ( for quorum) but in my test stand I use only two stands for one datacenter.

Key thig here is to keep first part of this file the same on all host ( where backend_hostname defenition is described )

copy config to second node:

scp  /etc/pgpool-II-95/* pg2:/etc/pgpool-II-95/

modify:
wd_hostname to second node
heartbeat_destination0 to first node
other_pgpool_hostname0 to first node

if you have any problems start pgpool in foreground mode ( also can be usefull to enable debug )
pgpool -f /etc/pgpool-II-95/pgpool.conf -n -d

usefull commands:
check node status
I use connect to VIP ip and port 9999

psql -U repmgr -d postgres -p 9999 -h 10.10.10.20 -c "show pool_nodes"
 node_id | hostname | port | status | lb_weight |  role   | select_cnt
---------+----------+------+--------+-----------+---------+------------
 0       | pg1      | 5432 | 2      | 0.500000  | primary | 0
 1       | pg2      | 5432 | 2      | 0.500000  | standby | 0 

scripts ( check that they are executable or add chmod +x /u01/postgres/9.5/main/conf/*.sh )

follow_master.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf -D $PGDATA -h $new_master -U repmgr -d postgres standby follow"
 	else  /usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf -D $PGDATA -h $new_master -U repmgr -d postgres standby follow
fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

[свернуть]

failover.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "/usr/bin/ssh -T -l postgres $new_master \"/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-\" "
 	else /usr/bin/ssh $new_master "/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
fi
sleep 5
if [ $UID -eq 0 ]
 	then su postgres -c  "/usr/bin/ssh -T -l postgres $new_master \"pcp_attach_node -p 9898 -U pgpooladmin -w -n $new_master_id\""
	else /usr/bin/ssh -T -l postgres $new_master "/usr/bin/ssh -T -l postgres $new_master \"pcp_attach_node -p 9898 -U pgpooladmin -w -n $new_master_id\""
fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

[свернуть]
stb_recreate.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
repmgr_conf=/u01/postgres/9.5/main/conf/repmgr.conf
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "
/usr/pgsql-9.5/bin/repmgr -c -h $master_node -d postgres -U repmgr -D $PGDATA -f $repmgr_conf standby clone
sudo /etc/init.d/postgresql-9.5 start
/usr/pgsql-9.5/bin/repmgr -f $repmgr_conf standby register -F
sleep 15
pcp_attach_node -h localhost -U pgpooladmin -w -n 1
"
 	else
	/usr/pgsql-9.5/bin/pg_ctl -m fast stop
	mv $PGDATA "$(echo $PGDATA)_$(echo `date +%H:%M_%d%m%Y`)"
	/usr/pgsql-9.5/bin/repmgr -c -h $new_master -d postgres -U repmgr -D $PGDATA -f $repmgr_conf standby clone
	sudo /etc/init.d/postgresql-9.5 start
	/usr/pgsql-9.5/bin/repmgr -f $repmgr_conf standby register -F
	sleep 15
	pcp_attach_node -h localhost -U pgpooladmin -w -n 1

fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_recreate.log

[свернуть]

postgresql repmgr how-to

postgresql repmgr how-to published on 1 комментарий к записи postgresql repmgr how-to

For better result you should install database by this document
how-to install postgres database

i have 4 node config, 2 nodes in one datacenter and 2 in other

config will look like this:

DR1     DR2
DB01--->DB11
|       |
 \_DB02  \_DB12

create config file:

node1 /u01/postgres/9.5/main/conf/repmgr.conf

node=1
cluster=db_cluster
node_name=pg1
conninfo='host=pg01 user=repmgr dbname=postgres password=q1'
pg_bindir=/usr/pgsql-9.5/bin/
master_response_timeout=5
reconnect_attempts=2
reconnect_interval=2
failover=manual
promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf'
follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf'
use_replication_slots=1

node2 /u01/postgres/9.5/main/conf/repmgr.conf

node=2
cluster=db_cluster
node_name=pg2
conninfo='host=pg02 user=repmgr dbname=postgres password=q1'
pg_bindir=/usr/pgsql-9.5/bin/
master_response_timeout=5
reconnect_attempts=2
reconnect_interval=2
failover=manual
promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf'
follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf'
use_replication_slots=1

node3 /u01/postgres/9.5/main/conf/repmgr.conf

node=3
cluster=db_cluster
node_name=pg3
conninfo='host=pg03 user=repmgr dbname=postgres password=q1'
pg_bindir=/usr/pgsql-9.5/bin/
master_response_timeout=5
reconnect_attempts=2
reconnect_interval=2
failover=manual
promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf'
follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf'
use_replication_slots=1

node4 /u01/postgres/9.5/main/conf/repmgr.conf

node=4
cluster=db_cluster
node_name=pg4
conninfo='host=pg04 user=repmgr dbname=postgres password=q1'
pg_bindir=/usr/pgsql-9.5/bin/
master_response_timeout=5
reconnect_attempts=2
reconnect_interval=2
failover=manual
promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /u01/postgres/9.5/main/conf/repmgr.conf'
follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /u01/postgres/9.5/main/conf/repmgr.conf'
use_replication_slots=1
upstream_node=3

also we need to setup passwordless auth of repmgr user
create .pgpass file in postgres user home directory with 0600 premissions:

All nodes:

cat ~/.pgpass
*:*:*:repmgr:q1
chmod 600 ~/.pgpass

create repmgr user

createuser -s repmgr

also we need to allow repmgr to connect to database with replication role
add this lines to your pg_hba.conf file on all nodes:

cat /u01/postgres/9.5/main/conf/pg_hba.conf | tail -3

local   replication,postgres        repmgr                             trust
host    replicatoin,postgres        repmgr      127.0.0.1/32           trust
host    replication,postgres        repmgr      10.10.10.0/24        md5

Here I’m allowed to connect from all network, but if you want, you can limit this by certain machines.

Next step is configure repmgr

Create Master:

[postgres@pg1 ~]$  /usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf master register
[2016-12-05 19:06:36] [NOTICE] master node correctly registered for cluster db_cluster with id 1 (conninfo: host=pg1 user=repmgr dbname=postgres password=q1)

create standbys
on host pg2 and pg3:

/usr/pgsql-9.5/bin/repmgr  -c -h pg1 -d postgres -U repmgr -D $PGDATA -f /u01/postgres/9.5/main/conf/repmgr.conf standby clone

start standby:

 pg_ctl start -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"

and register standby:

/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby register

create last standby ( which is going to be cascade standby ) and we are create this standby from PG3 database

/usr/pgsql-9.5/bin/repmgr  -c -h pg3 -d postgres -U repmgr -D $PGDATA -f /u01/postgres/9.5/main/conf/repmgr.conf standby clone

start and register database:

pg_ctl start -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"
/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby register

final result is

postgres=# select * from repmgr_db_cluster.repl_nodes ;
 id |  type   | upstream_node_id |  cluster   | name |                            conninfo                            |   slot_name   | priority | active
----+---------+------------------+------------+------+----------------------------------------------------------------+---------------+----------+--------
  1 | master  |                  | db_cluster | pg1  | host=pg1 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_1 |      100 | t
  2 | standby |                1 | db_cluster | pg2  | host=pg2 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_2 |      100 | t
  3 | standby |                1 | db_cluster | pg3  | host=pg3 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_3 |      100 | t
  4 | standby |                3 | db_cluster | pg4  | host=pg4 user=repmgr dbname=postgres password=xxxxxxxxxxxxxxxx | repmgr_slot_4 |      100 | t

postgresql install how-to

postgresql install how-to published on 2 комментария к записи postgresql install how-to

I will split material of how-to install pgpool on several logical parts
here is the first one:

start install postgres with packages:

yum install  postgresql95-devel postgresql95 postgresql95-test postgresql95-libs postgresql95-server postgresql95-contrib pgbouncer.x86_64 repmgr95* pgpool-II-95 pgpool-II-95-extensions

create directories and change home directory of postgres user:

mkdir /var/log/postgres
chown postgres: /var/log/postgres


usermod -m -d /u01 postgres
chown postgres: /u01 -R
su - postgres
cp /etc/skel/.bash* $HOME
mkdir -p /u01/postgres/9.5/main/pgdata/
mkdir -p /u01/postgres/9.5/main/pgdatats/
mkdir -p /u01/postgres/9.5/main/recovery
mkdir -p /u01/postgres/9.5/main/conf
mkdir /u01/postgres/9.5/main/recovery/backup
mkdir /u01/postgres/9.5/main/recovery/walarchive
mkdir /u01/postgres/9.5/main/recovery/dmp

configure bash_profile:

echo "export PGDATA=/u01/postgres/9.5/main/pgdata ">> ~/.bash_profile
echo "export PGTBS=/u01/postgres/9.5/main/pgdatats ">> ~/.bash_profile
echo "export PGCFG=/u01/postgres/9.5/main/conf ">> ~/.bash_profile

echo "export HISTTIMEFORMAT='%Y-%m-%d %H:%M:%S ' ">> ~/.bash_profile
echo "export HISTFILESIZE=9000 ">> ~/.bash_profile
echo "export PROMPT_COMMAND='history -a' ">> ~/.bash_profile
echo "export PS1='\[\e]0;\u@\h: \w\a\][\u@\h \W]$ ' ">> ~/.bash_profile

create dummy database:

/usr/pgsql-9.5/bin/initdb  --locale=ru_RU.utf8

configure init scripts ( or systemd daemon ) to work with our external config directory:
RH6:

/etc/init.d/postgresql-9.5
PGDATA=/u01/postgres/9.5/main/pgdata
PGOPTS="-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"

RH7:

vim /usr/lib/systemd/system/postgresql-9.5.service
Environment=PGDATA=/u01/postgres/9.5/main/pgdata
ExecStart=/usr/pgsql-9.5/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"

Create starter database:

/usr/pgsql-9.5/bin/initdb --lc-collate=ru_RU.UTF8 --locale=en_US.UTF8 --encoding=UTF8
/usr/pgsql-9.5/bin/pg_ctl stop

Move config file /u01/postgres/9.5/main/conf/postgresql.conf to $PGCFG directory and modify it :

data_directory='/u01/postgres/9.5/main/pgdata'
hba_file='/u01/postgres/9.5/main/conf/pg_hba.conf'
ident_file='/u01/postgres/9.5/main/conf/pg_ident.conf'

enable database startup:

chkconfig postgresql-9.5 on
cat postgresql.conf | egrep -v (^#|^s*$|^s* *#)

 
data_directory='/u01/postgres/9.5/main/pgdata'
hba_file='/u01/postgres/9.5/main/conf/pg_hba.conf'
ident_file='/u01/postgres/9.5/main/conf/pg_ident.conf'
listen_addresses = '*'		# what IP address(es) to listen on;
max_connections = 500			# (change requires restart)
shared_buffers = 8GB			# min 128kB
work_mem = 64MB				# min 64kB
maintenance_work_mem = 2GB		# min 1MB
dynamic_shared_memory_type = posix	# the default is the first option
wal_level = hot_standby			# minimal, archive, hot_standby, or logical
max_wal_size = 3GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9	# checkpoint target duration, 0.0 - 1.0
archive_mode = on		# enables archiving; off, on, or always
archive_command = 'cp -i %p /u01/postgres/9.5/main/recovery/walarchive/%f < /dev/null'		# command to use to archive a logfile segment
max_wal_senders = 4		# max number of walsender processes
max_replication_slots = 4	# max number of replication slots
hot_standby = on			# "on" allows queries during recovery
hot_standby_feedback = on		# send info from standby to prevent
effective_cache_size = 24GB
log_destination = 'stderr'		# Valid values are combinations of
logging_collector = on			# Enable capturing of stderr and csvlog
log_directory = '/var/log/postgres'		# directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d.log'	# log file name pattern,
log_truncate_on_rotation = on		# If on, an existing log file with the
log_rotation_age = 1d			# Automatic rotation of logfiles will
log_rotation_size = 0			# Automatic rotation of logfiles will
log_min_duration_statement = 300	# -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] db=%d,appname=%q,user=%u,client=%h'			# special values:
log_statement = 'ddl'			# none, ddl, mod, all
log_temp_files = 0			# log temporary files equal or larger
log_timezone = 'Europe/Moscow'
cluster_name = 'p00nrd'			# added to process titles if nonempty
track_io_timing = on
autovacuum_vacuum_scale_factor = 0.02	# fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.01	# fraction of table size before analyze
autovacuum_vacuum_cost_delay = 30ms	# default vacuum cost delay for
datestyle = 'iso, mdy'
timezone = 'Europe/Moscow'
lc_messages = 'en_US.UTF8'			# locale for system error message
lc_monetary = 'en_US.UTF8'			# locale for monetary formatting
lc_numeric = 'en_US.UTF8'			# locale for number formatting
lc_time = 'en_US.UTF8'				# locale for time formatting
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements'		# (change requires restart)
pg_stat_statements.max=10000
pg_stat_statements.track=top
pg_stat_statements.track_utility=off

[свернуть]

How to add em graph to ZABBIX

How to add em graph to ZABBIX published on Комментариев к записи How to add em graph to ZABBIX нет

add to crontab:

* * * * * /etc/zabbix/set_wait_classes.sh oracle_sid /oracle/app/base/db/12.1.0.2 /etc/zabbix/res_sqldb.ini WRITE_WAITS

script:

cat /etc/zabbix/set_wait_classes.sh
#!/bin/bash

ORACLE_SID="$1"
ORACLE_HOME="$2"
INI_FILE="$3"
PARAM="$4"
WAIT_CLASS="$5"

export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
export PATH
LD_LIBRARY_PATH_64=$ORACLE_HOME/lib64:/usr/local/lib64:/lib64:/usr/lib64
export LD_LIBRARY_PATH_64
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/local/lib:/lib:/usr/lib
export LD_LIBRARY_PATH

NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_SID

USERNAME="zabbix"
USERPASSWORD="herewasthepassword"


#Проверка кода завершения
function detect_error  () {
if [ $? -ne 0 ]
then
    echo "ERROR for ${1}. "
    exit 1
fi
}

#Выполнить SQL
function sqlexecute {
result=`sqlplus -s ${USERNAME}/${USERPASSWORD} <<EOF
set echo off serverout on HEADS off FEED off HEA off TRIM on TRIMS on
 ${SQL}
 ;
exit

EOF`

detect_error "sqlplus ${SQL}"

if echo "${result}" | egrep "^(SP-|ORA-|PLS-)" >/dev/null
then
 echo "ERROR for SQL. ${SQL}"
 echo "${result}"
 exit 1
fi

#Удалить пустые строки, пробелы и табуляции из результата
echo "${result}" | grep -v ^Elapsed | grep -v ^$ | sed 's/ [\t]*//g'
detect_error "sed 's/[ \t]*//g'"
}


#BEGIN

SQL=""
case $PARAM in
"WRITE_WAITS")
             SQL="select
		     count (*) ||
		     '~'|| nvl(wait_class,'CPU')||
		     '~'||to_char(sysdate,'yyyy-dd-mm-hh24:mi')
		from v\$active_session_history
		where
		   SAMPLE_TIME > sysdate-1/1440
		group by wait_class"
             sqlexecute > ${INI_FILE}
          ;;
"SET_PARAM")
           if grep "${WAIT_CLASS}" ${INI_FILE} >/dev/null
             then grep "${WAIT_CLASS}" ${INI_FILE} | awk -F"~" '{print $1}'
             else echo "0"
           fi
          detect_error "Error print WAIT_CLASS"
          ;;

*)
          help
          ;;
esac

modify /etc/zabbix/zabbix_agentd.conf add

UserParameter=oracle.monitor.wait[*],/etc/zabbix/set_wait_classes.sh oracle_sid /oracle/app/base/db/12.1.0.2 /etc/zabbix/res_sqldb.ini SET_PARAM "$1"

and here is zabbix template with correct colors :

zbx_export_templates.xml

<zabbix_export>
    <version>2.0</version>
    <date>2016-11-23T14:00:15Z</date>
    <groups>
        <group>
            <name>db_hosts_INC000002161969</name>
        </group>
    </groups>
    <templates>
        <template>
            <template>db_wait_template</template>
            <name>db_wait_template</name>
            <groups>
                <group>
                    <name>db_hosts_INC000002161969</name>
                </group>
            </groups>
            <applications>
                <application>
                    <name>Oracle wait event</name>
                </application>
            </applications>
            <items>
                <item>
                    <name>wait Administrative</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Administrative]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Application</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Application]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Cluster</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Cluster]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Commit</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Commit]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Concurrency</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Concurrency]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Configuration</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Configuration]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait CPU</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[CPU]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait IO</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[UserI/O]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Network</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Network]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Other</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Other]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Queueing</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Queueing]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait Scheduler</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[Scheduler]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
                <item>
                    <name>wait SystemIO</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>oracle.monitor.wait[SystemI/O]</key>
                    <delay>60</delay>
                    <history>7</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units>ses</units>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications>
                        <application>
                            <name>Oracle wait event</name>
                        </application>
                    </applications>
                    <valuemap/>
                    <logtimefmt/>
                </item>
            </items>
            <discovery_rules/>
            <macros/>
            <templates/>
            <screens/>
        </template>
    </templates>
    <graphs>
        <graph>
            <name>EM</name>
            <width>900</width>
            <height>200</height>
            <yaxismin>0.0000</yaxismin>
            <yaxismax>100.0000</yaxismax>
            <show_work_period>1</show_work_period>
            <show_triggers>1</show_triggers>
            <type>1</type>
            <show_legend>1</show_legend>
            <show_3d>0</show_3d>
            <percent_left>0.0000</percent_left>
            <percent_right>0.0000</percent_right>
            <ymin_type_1>0</ymin_type_1>
            <ymax_type_1>0</ymax_type_1>
            <ymin_item_1>0</ymin_item_1>
            <ymax_item_1>0</ymax_item_1>
            <graph_items>
                <graph_item>
                    <sortorder>8</sortorder>
                    <drawtype>0</drawtype>
                    <color>444444</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Administrative]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>5</sortorder>
                    <drawtype>0</drawtype>
                    <color>FF3333</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Application]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>11</sortorder>
                    <drawtype>0</drawtype>
                    <color>CCCCCC</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Cluster]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>6</sortorder>
                    <drawtype>0</drawtype>
                    <color>EEEE00</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Commit]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>4</sortorder>
                    <drawtype>0</drawtype>
                    <color>AA0000</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Concurrency]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>7</sortorder>
                    <drawtype>0</drawtype>
                    <color>888800</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Configuration]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>0</sortorder>
                    <drawtype>0</drawtype>
                    <color>00C800</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[CPU]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>2</sortorder>
                    <drawtype>0</drawtype>
                    <color>0000CC</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[UserI/O]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>9</sortorder>
                    <drawtype>0</drawtype>
                    <color>777777</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Network]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>12</sortorder>
                    <drawtype>0</drawtype>
                    <color>FF9999</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Other]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>10</sortorder>
                    <drawtype>0</drawtype>
                    <color>555555</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Queueing]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>1</sortorder>
                    <drawtype>0</drawtype>
                    <color>99FF99</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[Scheduler]</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>3</sortorder>
                    <drawtype>0</drawtype>
                    <color>9999FF</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>db_wait_template</host>
                        <key>oracle.monitor.wait[SystemI/O]</key>
                    </item>
                </graph_item>
            </graph_items>
        </graph>
    </graphs>
</zabbix_export>

[свернуть]

The result is:
screen-shot-2016-11-23-at-17-09-42

Thx to Vladimir Mukin for material

SQLT ORA-06598: insufficient INHERIT PRIVILEGES privilege

SQLT ORA-06598: insufficient INHERIT PRIVILEGES privilege published on Комментариев к записи SQLT ORA-06598: insufficient INHERIT PRIVILEGES privilege нет

Hi, faced with this error while generate report to Oracle support:

BEGIN SQLTXADMIN.sqlt$i.xtract(p_statement_id => :v_statement_id, p_sql_id_or_hash_value => '1v4z8fymw07t8', p_password => 'Y'); END;

*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SQLTXADMIN.SQLT$I", line 1
ORA-06512: at line 1

solution is:

grant INHERIT ANY PRIVILEGES to sqltxadmin;

ORA-03297 how-to find how data distributed

ORA-03297 how-to find how data distributed published on Комментариев к записи ORA-03297 how-to find how data distributed нет

original

column segment heading 'Segment Name' format a14
column file_name heading 'File Name' format a50
column segment_type heading 'Segment Type' format a10
select
            tablespace_name,
            file_id,
            file_name,
            dfsizeMB,
            hwmMB,
            dffreeMB,
            trunc((dffreeMB/dfsizeMB)*100,1) "% Free",
            trunc(dfsizeMB-hwmMB,2) "Resizeble"
   from
   (
      select
           df.tablespace_name tablespace_name,
           df.file_id file_id,
           df.file_name file_name,
           round (df.bytes/1024/1024) dfsizeMB,
           round((ex.hwm*(ts.block_size))/1024/1024) hwmMB,
           dffreeMB
      from
           dba_data_files df,
           dba_tablespaces ts,
      (
           select file_id, round(sum(bytes/1024/1024)) dffreeMB
           from dba_free_space
           group by file_id
      ) free,
      (
           select file_id, max(block_id+blocks) hwm
           from dba_extents
           group by file_id
      ) ex
      where df.file_id = ex.file_id
      and df.tablespace_name = ts.tablespace_name
      and df.file_id = free.file_id (+)
      order by df.tablespace_name, df.file_id
    ) 
/


TABLESPACE_NAME      |    FILE_ID | File Name                                          |   DFSIZEMB |      HWMMB |   DFFREEMB |     % Free |  Resizeble
-------------------- | ---------- | -------------------------------------------------- | ---------- | ---------- | ---------- | ---------- | ----------
ADMSF_TS             |         14 | +DATA/S00TESIA2/DATAFILE/admsf_ts.292.924278433    |        100 |         27 |         87 |         87 |         73
ESIA                 |          5 | +DATA/s00tesia2/datafile/esia.262.911677179        |      32767 |      32767 |      25380 |       77.4 |          0
ESIA                 |         12 | +DATA/s00tesia2/datafile/esia.261.911673773        |      32768 |      32768 |      20200 |       61.6 |          0
ESIA                 |         13 | +DATA/s00tesia2/datafile/esia.265.911681735        |       3100 |       3100 | <NULL>     | <NULL>     |          0
ESIA_PDS             |          8 | +DATA/s00tesia2/datafile/esia_pds.271.911682045    |        100 |          6 |         99 |         99 |         94
ESIA_TECH            |          6 | +DATA/s00tesia2/datafile/esia_tech.269.911682035   |        100 |          9 |         93 |         93 |         91
SIA_DB               |         10 | +DATA/s00tesia2/datafile/sia_db.266.911681879      |       1260 |       1199 |         61 |        4.8 |         61
SYSAUX               |          2 | +DATA/s00tesia2/datafile/sysaux.264.911681479      |       4096 |       1591 |       2519 |       61.4 |       2505
SYSTEM               |          1 | +DATA/s00tesia2/datafile/system.267.911681965      |        890 |        886 |         57 |        6.4 |          4
UNDOTBS1             |          3 | +DATA/s00tesia2/datafile/undotbs1.268.911681999    |        910 |        620 |        884 |       97.1 |        290
USERS                |          4 | +DATA/s00tesia2/datafile/users.263.911680523       |       9069 |       8587 |       9030 |       99.5 |        482

column segment heading 'Segment Name' format a40
column file_name heading 'File Name' format a50
column segment_type heading 'Segment Type' format a20
select
                file_name,
                segment_type,
                owner||'.'||segment_name segment,
                block_id,
                blockIdMB
        from
        (
         select
              ex.owner owner,
              ex.segment_name segment_name,
              ex.segment_type segment_type,
              ex.block_id block_id,
              df.file_name file_name,
              trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB
      from
              dba_extents ex, dba_data_files df, dba_tablespaces ts
              where df.file_id = &file_id
              and df.file_id = ex.file_id
              and df.tablespace_name = ts.tablespace_name
              order by ex.block_id desc
      )
      where rownum <= 100
/


File Name                                          | Segment Type         | Segment Name                             |   BLOCK_ID |  BLOCKIDMB
-------------------------------------------------- | -------------------- | ---------------------------------------- | ---------- | ----------
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194168 |   32766.93
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194160 |   32766.87
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194152 |   32766.81
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194144 |   32766.75
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194136 |   32766.68
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194128 |   32766.62
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194120 |   32766.56
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194112 |    32766.5
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194104 |   32766.43
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194096 |   32766.37
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194088 |   32766.31
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194080 |   32766.25
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194072 |   32766.18
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194064 |   32766.12
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194056 |   32766.06
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194048 |      32766

how to get ash report from sqlplus

how to get ash report from sqlplus published on Комментариев к записи how to get ash report from sqlplus нет
Select *
    from table(dbms_workload_repository.ASH_report_html(
             l_dbid => 2316837477,
             l_inst_num => 1,
             l_btime =>  TIMESTAMP'2016-11-14 14:36:00' ,
             l_etime =>  TIMESTAMP'2016-11-14 14:41:00' ,
             l_options => 0,
             l_slot_width => 0,
             l_sid => null,
            l_sql_id => null,
            l_wait_class => null,
            l_service_hash => null,
            l_module => null,
            l_action => null,
            l_client_id => null,
            l_plsql_entry => null))
   ;

Upload csv files to postgresql database

Upload csv files to postgresql database published on Комментариев к записи Upload csv files to postgresql database нет

here is my drafts of how-to automate upload a lot of csv files to database ( use text fieds because there is no need to mach all types of columns in this case )

 for i in $(ls -1 *.csv); do echo "create table $i (" ; echo $(head -1 $i | sed 's/,/ text,\n/g' ); echo "text );" ;echo copy $i | awk -F "." '{print $1}'; echo "from '/u01/import/$i' DELIMITER ',' CSV HEADER;" ; done

postgresql script to find excess indexes

postgresql script to find excess indexes published on Комментариев к записи postgresql script to find excess indexes нет

here how excess indexes can be found, excess mean that there is a single column index which is covered by first value of complex index

select * from (
select count(*),pg_size_pretty(pg_relation_size(t.table_name)),t.table_name,t.indkey_names[1],min(array_length(t.indkey_names,1)) min_index_elements_cnt from (
SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass table_name,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
where idx.indrelid::text not like 'pg_toast%' 
and pg_relation_size(indexrelid::regclass)>100*1024*1024 ) t
group by t.indkey_names[1],t.table_name
having count (*) >1
) tt where min_index_elements_cnt =1;

Primary Sidebar

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