<span style=" font-size:10pt;font-family:Courier New">Hi Dejan Muhamedagic</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">We are using
pacemaker with your pcs resource oracle and oralsnr.</span>
<br><span style=" font-size:10pt;font-family:Courier New">When we sometimes
execute DELETE (thousands rows and transaction time</span>
<br><span style=" font-size:10pt;font-family:Courier New">mostly > 10s
Oracle will usually stop and it is very bad.</span>
<br><span style=" font-size:10pt;font-family:Courier New">These servers
are mission critical.</span>
<br><span style=" font-size:10pt;font-family:Courier New">Indexes is rebuilded
and statistics is analyzed every day.</span>
<br><span style=" font-size:10pt;font-family:Courier New">Structure of
table is</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">CREATE TABLE
TDC.VD_ARC10</span>
<br><span style=" font-size:10pt;font-family:Courier New">(</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDST INTEGER,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDTP INTEGER,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDLN INTEGER,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
DTIME DATE,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
FLAG CHAR (1 BYTE) NOT NULL,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
VALID CHAR (1 BYTE) NOT NULL,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
VALUE NUMBER NOT NULL</span>
<br><span style=" font-size:10pt;font-family:Courier New">)</span>
<br><span style=" font-size:10pt;font-family:Courier New">TABLESPACE USERS</span>
<br><span style=" font-size:10pt;font-family:Courier New">PCTFREE 10</span>
<br><span style=" font-size:10pt;font-family:Courier New">INITRANS 1</span>
<br><span style=" font-size:10pt;font-family:Courier New">MAXTRANS 255</span>
<br><span style=" font-size:10pt;font-family:Courier New">STORAGE (INITIAL
64 K</span>
<br><span style=" font-size:10pt;font-family:Courier New">
NEXT 1 M</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MINEXTENTS 1</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MAXEXTENTS UNLIMITED</span>
<br><span style=" font-size:10pt;font-family:Courier New">
PCTINCREASE 0</span>
<br><span style=" font-size:10pt;font-family:Courier New">
BUFFER_POOL DEFAULT</span>
<br><span style=" font-size:10pt;font-family:Courier New">
FLASH_CACHE DEFAULT</span>
<br><span style=" font-size:10pt;font-family:Courier New">
CELL_FLASH_CACHE DEFAULT)</span>
<br><span style=" font-size:10pt;font-family:Courier New">NOLOGGING</span>
<br><span style=" font-size:10pt;font-family:Courier New">NOCOMPRESS</span>
<br><span style=" font-size:10pt;font-family:Courier New">NOCACHE</span>
<br><span style=" font-size:10pt;font-family:Courier New">RESULT_CACHE
(MODE DEFAULT)</span>
<br><span style=" font-size:10pt;font-family:Courier New">NOPARALLEL;</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">indexes are</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">--</span>
<br><span style=" font-size:10pt;font-family:Courier New">-- VD_ARC10_I1
(Index) </span>
<br><span style=" font-size:10pt;font-family:Courier New">--</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">CREATE UNIQUE
INDEX TDC.VD_ARC10_I1</span>
<br><span style=" font-size:10pt;font-family:Courier New">
ON TDC.VD_ARC10 (DTIME,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDST,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDTP,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDLN)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
NOLOGGING</span>
<br><span style=" font-size:10pt;font-family:Courier New">
TABLESPACE USERS</span>
<br><span style=" font-size:10pt;font-family:Courier New">
PCTFREE 10</span>
<br><span style=" font-size:10pt;font-family:Courier New">
INITRANS 2</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MAXTRANS 255</span>
<br><span style=" font-size:10pt;font-family:Courier New">
STORAGE (INITIAL 50 M</span>
<br><span style=" font-size:10pt;font-family:Courier New">
NEXT 100 M</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MINEXTENTS 1</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MAXEXTENTS UNLIMITED</span>
<br><span style=" font-size:10pt;font-family:Courier New">
PCTINCREASE 0</span>
<br><span style=" font-size:10pt;font-family:Courier New">
BUFFER_POOL DEFAULT</span>
<br><span style=" font-size:10pt;font-family:Courier New">
FLASH_CACHE DEFAULT</span>
<br><span style=" font-size:10pt;font-family:Courier New">
CELL_FLASH_CACHE DEFAULT)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
NOPARALLEL;</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">--</span>
<br><span style=" font-size:10pt;font-family:Courier New">-- VD_ARC10_PK
(Index) </span>
<br><span style=" font-size:10pt;font-family:Courier New">--</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">CREATE UNIQUE
INDEX TDC.VD_ARC10_PK</span>
<br><span style=" font-size:10pt;font-family:Courier New">
ON TDC.VD_ARC10 (IDST,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDTP,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDLN,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
DTIME)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
NOLOGGING</span>
<br><span style=" font-size:10pt;font-family:Courier New">
TABLESPACE USERS</span>
<br><span style=" font-size:10pt;font-family:Courier New">
PCTFREE 10</span>
<br><span style=" font-size:10pt;font-family:Courier New">
INITRANS 2</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MAXTRANS 255</span>
<br><span style=" font-size:10pt;font-family:Courier New">
STORAGE (INITIAL 50 M</span>
<br><span style=" font-size:10pt;font-family:Courier New">
NEXT 100 M</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MINEXTENTS 1</span>
<br><span style=" font-size:10pt;font-family:Courier New">
MAXEXTENTS UNLIMITED</span>
<br><span style=" font-size:10pt;font-family:Courier New">
PCTINCREASE 0</span>
<br><span style=" font-size:10pt;font-family:Courier New">
BUFFER_POOL DEFAULT</span>
<br><span style=" font-size:10pt;font-family:Courier New">
FLASH_CACHE DEFAULT</span>
<br><span style=" font-size:10pt;font-family:Courier New">
CELL_FLASH_CACHE DEFAULT)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
NOPARALLEL;</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">ALTER TABLE TDC.VD_ARC10</span>
<br><span style=" font-size:10pt;font-family:Courier New">
ADD (CONSTRAINT VD_ARC10_PK PRIMARY KEY (IDST,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDTP,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
IDLN,</span>
<br><span style=" font-size:10pt;font-family:Courier New">
DTIME)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
USING INDEX TDC.VD_ARC10_I1 ENABLE VALIDATE);</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Number of records
are over 18 million rows. Your</span>
<br><span style=" font-size:10pt;font-family:Courier New">script tries
to connect to database and checks if</span>
<br><span style=" font-size:10pt;font-family:Courier New">it is succesfull
or not.</span>
<br><span style=" font-size:10pt;font-family:Courier New">But Oracle instance
could be living if it is in NOMOUNT</span>
<br><span style=" font-size:10pt;font-family:Courier New">or MOUNT state
and not in the state OPEN. Time between</span>
<br><span style=" font-size:10pt;font-family:Courier New">NOMOUNT and OPEN
state could sometimes spend minutes if</span>
<br><span style=" font-size:10pt;font-family:Courier New">Oracle do consistency
from redo files. Cluster resource</span>
<br><span style=" font-size:10pt;font-family:Courier New">still shutdown
database or disconnects cluster and I must</span>
<br><span style=" font-size:10pt;font-family:Courier New">reboot all servers
more than once.</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">If is instance
living, we could get an information by</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">sqlplus / as
sysdba</span>
<br><span style=" font-size:10pt;font-family:Courier New">select status
from v$instance</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">STATUS return
STARTED, MOUNT or OPEN but if it is</span>
<br><span style=" font-size:10pt;font-family:Courier New">down and it is
not possible to connect as sysdba Oracle</span>
<br><span style=" font-size:10pt;font-family:Courier New">shows error message:</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">ORA-01034: ORACLE
not available</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Static queries
like view v$instance work also in not</span>
<br><span style=" font-size:10pt;font-family:Courier New">opened database.</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">We need not to
shutdown database or not to disconect cluster</span>
<br><span style=" font-size:10pt;font-family:Courier New">when long time
queries is running. What to do? I am beginner</span>
<br><span style=" font-size:10pt;font-family:Courier New">in administration
of pcs resources. Timouts of resources?</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">[root@vdpr2 ~]#
pcs resource show</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Master/Slave
Set: cluster_drbd_clone [cluster_drbd]</span>
<br><span style=" font-size:10pt;font-family:Courier New">
Masters: [ vdpr2a ]</span>
<br><span style=" font-size:10pt;font-family:Courier New">
Slaves: [ vdpr2b ]</span>
<br><span style=" font-size:10pt;font-family:Courier New"> cluster_www
(ocf::heartbeat:apache): Started
vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New"> cluster_cron
(systemd:crond): Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Resource
Group: telemat_group</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_ip (ocf::heartbeat:IPaddr2): Started
vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_ip_gprs (ocf::heartbeat:IPaddr2):
Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_fs (ocf::heartbeat:Filesystem): Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_hostname (lsb:hostname): Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_oracle_listener (ocf::heartbeat:oralsnr):
Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_oracle (ocf::heartbeat:oracle):
Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_delay_telemat (ocf::heartbeat:Delay):
Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_scada (systemd:monit-node): Started
vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_delay_telemat2 (ocf::heartbeat:Delay): Started
vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">
cluster_centrala (systemd:monit-node2): Started vdpr2a</span>
<br><span style=" font-size:10pt;font-family:Courier New">[root@vdpr2 ~]#</span>
<br>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">[root@vdpr2 ~]#
pcs resource show cluster_oracle</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Resource:
cluster_oracle (class=ocf provider=heartbeat type=oracle)</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Attributes:
sid=ORAC user=oracle</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Operations:
methods interval=0s timeout=5s (cluster_oracle-methods-interval-0s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
monitor interval=120s timeout=30s (cluster_oracle-monitor-interval-120s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
start interval=0s timeout=300s (cluster_oracle-start-interval-0s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
stop interval=0s timeout=120s (cluster_oracle-stop-interval-0s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">[root@vdpr2 ~]#</span>
<br>
<br>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">[root@vdpr2 ~]#
pcs resource show cluster_oracle_listener</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Resource:
cluster_oracle_listener (class=ocf provider=heartbeat type=oralsnr)</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Attributes:
sid=ORAC user=oracle</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Meta Attrs:
migration-threshold=3</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Operations:
methods interval=0s timeout=5s (cluster_oracle_listener-methods-interval-0s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
monitor interval=10s timeout=30s (cluster_oracle_listener-monitor-interval-10s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
start interval=0s timeout=120s (cluster_oracle_listener-start-interval-0s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">
stop interval=0s timeout=120s (cluster_oracle_listener-stop-interval-0s)</span>
<br><span style=" font-size:10pt;font-family:Courier New">[root@vdpr2 ~]#</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">How to test status
of Oracle during startup or shutdown (do nothing yet):</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">[oracle@vdpr2
~]$ sqlplus / as sysdba << EOF</span>
<br><span style=" font-size:10pt;font-family:Courier New">> SELECT status
FROM v\$instance;</span>
<br><span style=" font-size:10pt;font-family:Courier New">> exit;</span>
<br><span style=" font-size:10pt;font-family:Courier New">> EOF</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Output:</span>
<br><span style=" font-size:10pt;font-family:Courier New">SQL*Plus: Release
12.2.0.1.0 Production on Tue Apr 4 10:04:54 2023</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Copyright (c)
1982, 2016, Oracle. All rights reserved.</span>
<br>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Connected to:</span>
<br><span style=" font-size:10pt;font-family:Courier New">Oracle Database
12c Standard Edition Release 12.2.0.1.0 - 64bit Production</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">SQL></span>
<br><span style=" font-size:10pt;font-family:Courier New">STATUS</span>
<br><span style=" font-size:10pt;font-family:Courier New">------------</span>
<br><span style=" font-size:10pt;font-family:Courier New">OPEN</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">SQL> Disconnected
from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit
Production</span>
<br><span style=" font-size:10pt;font-family:Courier New">[oracle@vdpr2
~]$</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">If STATUS is
STARTED then it is in NOMOUNT state but instance is started</span>
<br><span style=" font-size:10pt;font-family:Courier New">If STATUS is
MOUNT then Oracle opens the control files and find names of</span>
<br><span style=" font-size:10pt;font-family:Courier New">datafiles and
online redo files.</span>
<br><span style=" font-size:10pt;font-family:Courier New">If STATUS is
OPEN then Oracle is ready to connect by another user then</span>
<br><span style=" font-size:10pt;font-family:Courier New">/ as sysdba</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">The best is test
status if it is in STATUS STARTED or MOUNT and wait</span>
<br><span style=" font-size:10pt;font-family:Courier New">until Oracle
will open. It can spend minutes.</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">If listener is
running and working properly we could test it by</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">[oracle@vdpr2
~]$ lsnrctl status | grep "READY"</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Instance
"ORAC", status READY, has 1 handler(s) for this service...</span>
<br><span style=" font-size:10pt;font-family:Courier New"> Instance
"ORAC", status READY, has 1 handler(s) for this service...</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Do you have any
idea why DELETE with transaction time more then 10</span>
<br><span style=" font-size:10pt;font-family:Courier New">seconds shutdowns
database? Thanks for advice in advance.</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">S pozdravem </span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Robert Reich,
informatik</span>
<br><span style=" font-size:10pt;font-family:Courier New">_______________________________</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Povodí Ohře,
státní podnik</span>
<br><span style=" font-size:10pt;font-family:Courier New">Bezručova 4219,
430 03 Chomutov</span>
<br><span style=" font-size:10pt;font-family:Courier New">tel. :
+420 474 636 352</span>
<br><span style=" font-size:10pt;font-family:Courier New">mobil : +420
606 757 459</span>
<br><span style=" font-size:10pt;font-family:Courier New">e-mail: reich@poh.cz</span>
<br><a href=http://www.poh.cz/><span style=" font-size:10pt;color:blue;font-family:Courier New">http://www.poh.cz/</span></a>
<br><span style=" font-size:10pt;font-family:Courier New">_______________________________</span>
<br>
<br><span style=" font-size:10pt;font-family:Courier New">Kontrolováno
antivirovým systémem SOPHOS</span>
<br>