ORA-02056: 2PC: k2lcom: bad two-phase command number rdonly from coord

Hi all,

So, you found this error?

Well, I received this in a client environment. After checking MOS ORA-02056: 2PC: K2gCheckGlobalCommit: Bad Two-phase Command Number 1 From Coord (Doc ID 2498134.1), which points to a known issue when dealing with committing transactions as per Bug 22016049 I noticed my problem could be different…

My real root cause is my transaction being done on a remote object and when checking on remote database I found pending distributed transactions.

For more informations about distributed transactions, please check: https://docs.oracle.com/database/121/ADMIN/ds_txns.htm#ADMIN12211

GUID-D521B4E9-E916-4B02-8B0E-4FAF4DC61423-default

So, if you getting this situation, before planning and applying the patch I suggest you:

  • Check if your process is not working on remote databases.
  • Check for any possible distributed remote transactions.

How? Easy check (on remote db):

select * from dba_2pc_pending;

I hope it helps!

PS NOTE: I just found this very good and complete post by Rodrigo Mufalani on the same topic: ORA-01591: lock held by in-doubt distributed transaction

Have a look for an Action Plan!
Cheers!

OEM 12c Agent: java.lang.OutOfMemoryError: Java heap space

Hi all,

So I got this error from OEM in a client. When connecting noticed OEM agent was down.

From logs, it failed to restart because of error “java.lang.OutOfMemoryError: Java heap space“.

I tried to run clearstate but got same error.

Searched on MOS – found MOS Duplicate 1952593.1: EM12c: emctl start agent Fails With Target Interaction Manager failed at Startup java.lang.OutOfMemoryError: Java heap space reported in gcagent_errors.log (Doc ID 1902124.1)

Solution seems to be moving /agent_inst/sysman/emd/state/ content to another location before running clearstate.

After doing so, all worked fine! So if you are facing the same, try this out!

oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin$ cd ..
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst$ cd sysman
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd log/
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ ls -lrt | tail
-rw-r----- 1 oracle dba 4096 Jan 9 09:47 emagent.nohup.lr
-rw-r----- 1 oracle dba 11165 Jan 9 09:48 startup.info
-rw-r----- 1 oracle dba 153432 Jan 9 09:48 gcagent_errors.log
-rw------- 1 oracle dba 540010788 Jan 9 09:48 heapDump_7.hprof
-rw-r----- 1 oracle dba 4687452 Jan 9 09:48 gcagent.log
-rw-r----- 1 oracle dba 1746 Jan 9 09:48 agabend.log
-rw-r----- 1 oracle dba 2863 Jan 9 09:48 gcagent_pfu.log
-rw-r----- 1 oracle dba 84308 Jan 9 09:48 emagent.nohup
-rw-r----- 1 oracle dba 762142 Jan 9 10:06 emdctlj.log
-rw-r----- 1 oracle dba 121782 Jan 9 10:06 emctl.log
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ date
Monday, January 9, 2020 10:06:24 AM PST
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ tail gcagent.log
2020-01-09 09:48:50,875 [32:F9C26A76] INFO - *jetty*: Graceful shutdown SslSelectChannelConnector@0.0.0.0:3872
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@63b5a40a@63b5a40a/emd/lifecycle/main,null
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPLifecycleHandler@7284aa02
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@5dac13d7@5dac13d7/emd/main,null
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPRequestHandler@52a34783
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ServletContextHandler@201d592a@201d592a/emd/browser,null
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@19a9bea3@19a9bea3/emd/persistence/main,null
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPAgentPersistenceHandler@3d89acb5
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@5722cc7e@5722cc7e/,null
2020-01-09 09:48:53,932 [32:F9C26A76] INFO - *jetty*: Shutdown hook complete
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ tail gcagent_errors.log
at oracle.sysman.gcagent.target.interaction.execution.Threshold.initThreshold(Threshold.java:4132)
at oracle.sysman.gcagent.target.interaction.execution.TargetInteractionMgr.init(TargetInteractionMgr.java:225)
at oracle.sysman.gcagent.target.interaction.execution.TargetInteractionMgr.tmNotifier(TargetInteractionMgr.java:171)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeNotifier(TMComponentSvc.java:998)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeInitializationStep(TMComponentSvc.java:1083)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.doInitializationStep(TMComponentSvc.java:916)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.notifierDriver(TMComponentSvc.java:812)
at oracle.sysman.gcagent.tmmain.TMMain.startup(TMMain.java:256)
at oracle.sysman.gcagent.tmmain.TMMain.agentMain(TMMain.java:557)
at oracle.sysman.gcagent.tmmain.TMMain.main(TMMain.java:546)
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ less gcagent_errors.log
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ cd ..
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd config/
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ ls -lrt | tail
-rw-r----- 1 oracle dba 8001 May 13 2016 b64LocalCertificate.txt
-rw-r----- 1 oracle dba 17893 May 13 2016 b64InternetCertificate.txt
-rw-r----- 1 oracle dba 7833 May 13 2016 emd.properties.2016_05_13_10_05_31
-rw------- 1 oracle dba 499 May 20 2016 s_jvm_options.opt.save
-rw-r----- 1 oracle dba 8202 Sep 26 01:20 emd.properties.bkp
-rw-r----- 1 oracle dba 8204 Jan 9 09:46 emd.properties.bak
-rw-r----- 1 oracle dba 156 Jan 9 09:46 private.properties.bak
-rw-r----- 1 oracle dba 8204 Jan 9 09:48 emd.properties
-rw-r----- 1 oracle dba 266 Jan 9 09:48 autotune.properties
-rw-r----- 1 oracle dba 156 Jan 9 09:48 private.properties
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ date
Monday, January 9, 2020 10:07:56 AM PST
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ n/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ which emctl
/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin/emctl
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ e agent; emctl start agent

Oracle Enterprise Manager Cloud Control 12c Release 4 Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved. EMD clearstate failed: Offline clearstate failed : java.lang.OutOfMemoryError: Java heap space Oracle Enterprise Manager Cloud Control 12c Release 4 Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved. Starting agent ...........................................................................................................................failed. Consult emctl.log and emagent.nohup in: /db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ cd .. oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd emd oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ mkdir state-20200109 oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ mv state/* state\-20200109/ oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ du -hs state* 1K state 166M state-20200109 oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ emctl clearstate agent; emctl sta
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting agent ......................... started.
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.4.0
OMS Version : 12.1.0.4.0
Protocol Version : 12.1.0.1.0
Agent Home : /db/oracle/product/agent12c/12.1.0.4/agent_inst
Agent Log Directory : /db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log
Agent Binaries : /db/oracle/product/agent12c/12.1.0.4/core/12.1.0.4.0
Agent Process ID : 27885
Parent Process ID : 27848
Agent URL : https://x0319vp114.nordstrom.net:3872/emd/main/
Local Agent URL in NAT : https://x0319vp114.nordstrom.net:3872/emd/main/
Repository URL : https://oemcloud.nordstrom.net:4900/empbs/upload
Started at : 2020-01-09 10:15:39
Started by user : oracle
Operating System : SunOS version 5.11 (sparcv9)
Last Reload : (none)
Last successful upload : 2020-01-09 10:17:35
Last attempted upload : 2020-01-09 10:17:35
Total Megabytes of XML files uploaded so far : 0.59
Number of XML files pending upload : 1
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 50.38%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-01-09 10:16:54
Last successful heartbeat to OMS : 2020-01-09 10:16:54
Next scheduled heartbeat to OMS : 2020-01-09 10:17:54

---------------------------------------------------------------
Agent is Running and Ready
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$

 

ORA-04030: out of process memory when trying to allocate % bytes (kkoutlCreatePh,ub1 : kkoabr)

Hi all,

So, I started seen this on a client environment. Researching on the  case after no crear reference on MOS, I noticed some high PGA allocation as per below.

SYS@proddb AS SYSDBA PROD> select pid, serial#,category, allocated/1024/1024 MB, used/1024/1024 MB_used, max_allocated/1024/1024 MB_MAX_ALLOCATED_ON_PGA
2 from v$process_memory where pid=852;

PID SERIAL# CATEGORY MB MB_USED MB_MAX_ALLOCATED_ON_PGA
---------- ---------- --------------- ---------- ---------- -----------------------
852 91 SQL .086807251 .00806427 .672416687
852 91 PL/SQL .087730408 .078926086 .126182556
852 91 Freeable .5625 0
852 91 Other 2.25187302 2.25187302

Seems a match MOS ORA-04030 Error With High “kkoutlCreatePh” (Doc ID 1618444.1).

The solution? Simply disabled the following parameter:

"_b_tree_bitmap_plans"=false

 

Trace for additional info:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1
System name: Linux
Node name: proddb.local
Release: 4.1.12-94.8.3.el7uek.x86_64
Version: #2 SMP Wed Apr 25 19:57:32 PDT 2018
Machine: x86_64
Instance name: proddb
Redo thread mounted by this instance: 1
Oracle process number: 854
Unix process pid: 28899, image: oracle@proddb.local

*** 2019-01-09 11:20:09.130
*** SESSION ID:(5429.55092) 2019-01-09 11:20:09.130
*** CLIENT ID:() 2019-01-09 11:20:09.130
*** SERVICE NAME:(XXXXX) 2019-01-09 11:20:09.130
*** MODULE NAME:(JDBC Thin Client) 2019-01-09 11:20:09.130
*** CLIENT DRIVER:(jdbcthin) 2019-01-09 11:20:09.130
*** ACTION NAME:() 2019-01-09 11:20:09.130

[TOC00000]
Jump to table of contents
Dump continued from file: /u01/app/oracle/diag/rdbms/proddb/proddb/trace/proddb_ora_28899.trc
[TOC00001]
ORA-04030: out of process memory when trying to allocate 34392040 bytes (kkoutlCreatePh,ub1 : kkoabr)

[TOC00001-END]
[TOC00002]
========= Dump for incident 2710965 (ORA 4030) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
72% 18 GB, 1356 chunks: "free memory " SQL
kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0
27% 6809 MB, 3366 chunks: "permanent memory " SQL
kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0
0% 100 MB, 894 chunks: "permanent memory " SQL
kkoutlCreatePh ds=0x7fa8cce16708 dsprt=0x7fa8df330220
0% 23 MB, 589515 chunks: "chedef : qcuatc "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 18 MB, 150124 chunks: "opndef: qcopCreateOpnViaM "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 16 MB, 214829 chunks: "logdef: qcopCreateLog "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 11 MB, 241 chunks: "free memory "
top call heap ds=0x7fa8df49dbe0 dsprt=(nil)
0% 9643 KB, 4623 chunks: "qkkele " SQL
kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0
0% 6534 KB, 58578 chunks: "optdef: qcopCreateOptInte "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 4134 KB, 15399 chunks: "kccdef : qcsvwsci "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
25 GB total:
25 GB commented, 794 KB permanent
12 MB free (0 KB in empty extents),
24 GB, 1 heap: "kxs-heap-c " 67 KB free held
------------------------------------------------------
Summary of subheaps at depth 1
25 GB total:
203 MB commented, 6809 MB permanent
18 GB free (0 KB in empty extents),
6398 MB, 9243 chunks: "allocator state " 6398 MB free held
3758 MB, 4623 chunks: "qkkele " 3749 MB free held
3650 MB, 4623 chunks: "qkkkey " 3650 MB free held

=========================================
REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS
-----------------------------------------

Dump of Real-Free Memory Allocator Heap [0x7fa8df317000]
mag=0xfefe0001 flg=0x5000003 fds=0x0 blksz=65536
blkdstbl=0x7fa8df317010, iniblk=524288 maxblk=524288 numsegs=318
In-use num=2965 siz=641597440, Freeable num=0 siz=0, Free num=254 siz=3586195456

==========================================
INSTANCE-WIDE PRIVATE MEMORY USAGE SUMMARY
------------------------------------------

Dumping Work Area Table (level=1)
=====================================

Global SGA Info
---------------

global target: 102400 MB
auto target: 62376 MB
max pga: 2048 MB
pga limit: 4096 MB
pga limit known: 0
pga limit errors: 0

pga inuse: 33104 MB
pga alloc: 35238 MB
pga freeable: 1225 MB
pga freed: 433664026 MB
pga to free: 0 %
broker request: 0

pga auto: 12 MB
pga manual: 0 MB

pga alloc (max): 35238 MB
pga auto (max): 12284 MB
pga manual (max): 2 MB

# workareas : 0
# workareas(max): 551

Merry Christmas!

We wish you a Merry Christmas!

Please, copy and paste on any sqlplus. This is your gift for today:

set heading off;
set pages 5000;
SELECT DECODE(SIGN(FLOOR(maxwidth / 2) - ROWNUM),
              1,
              LPAD(' ', FLOOR(maxwidth / 2) - (ROWNUM - 1)) ||
              RPAD('*', 2 * (ROWNUM - 1) + 1, ' *'),
              LPAD('* * *', FLOOR(maxwidth / 2) + 3))
  FROM all_objects, (SELECT 40 AS maxwidth FROM DUAL)
 WHERE ROWNUM < FLOOR(maxwidth / 2) + 5
union all select '|GrepOra Team Wishes You a Merry Christmas!' from dual;

 

 

Surprise!

newscreenshot-2016-12-24-as-16-24-08

Enjoy!

Amazon RDS: How to perform RMAN operations?

Hi all,

Need to performa any RMAN Operation from Amazon RDS? Maybe something like validating backups?

Well, we have some options under by using RDSADMIN_RMAN_UTIL package. See below an example for validate backup:

BEGIN
 rdsadmin.rdsadmin_rman_util.validate_database(
 p_validation_type => 'PHYSICAL+LOGICAL',
 p_parallel => 4,
 p_section_size_mb => 10,
 p_rman_to_dbms_output => FALSE);
END;
/

More info about it: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.RMAN.html

Hope it helps, cheers!

AWS RDS: Read Oracle Traces from SQL*Plus

Hi all,

One more for AWS services. Let’s say you need to read tracefiles from RDS, hot to do it?

Here is an example on how to list and read those files:

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ------------------------- ------------------------------------------------------------ -------------
SYS BDUMP /rdsdbdata/log/trace 0
SYS ADUMP /rdsdbdata/log/audit 0
SYS OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch 0
SYS OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch 0
SYS DATA_PUMP_DIR /rdsdbdata/datapump 0
SYS OPATCH_INST_DIR /rdsdbbin/oracle/OPatch 0
SYS TMP /rdsdbdata/userdirs/01 0

7 rows selected.


SQL> select * from table (rdsadmin.rds_file_util.read_text_file( p_directory => 'BDUMP', p_filename => 'trace/CPROD1_s003_81573.trc'));


From the trace file:
=========================
..................................................................................
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x000e289e.51da.47
Dump kdilk : itl=98, kdxlkflg=0x1 sdc=0 indexid=0x1c53db block=0x0019bdc0
(kdxlpu): purge leaf row
key :(24):
07 78 76 0c 17 17 09 32 08 c7 07 1a 02 28 15 01 18 06 00 1a 3c 99 00 1e

File 3 is not mirrored.

End dump previous blocks for kdsgrp
* kdsgrp1-2: ***********************************************
kdsDumpState: RID context dump

45511581 rows selected.

SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) order by mtime;

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_ora_48800.trm file 73 24-DEC-18
CPROD1_ora_48800.trc file 998 24-DEC-18
CPROD1_ora_86597.trc file 998 24-DEC-18
CPROD1_ora_86597.trm file 73 24-DEC-18
CPROD1_ora_7999.trc file 881 24-DEC-18
CPROD1_ora_7999.trm file 71 24-DEC-18
CPROD1_ora_7997.trm file 71 24-DEC-18
CPROD1_ora_7997.trc file 881 24-DEC-18
CPROD1_ora_8240.trm file 71 24-DEC-18
CPROD1_ora_8240.trc file 881 24-DEC-18
CPROD1_ora_8381.trm file 72 24-DEC-18
CPROD1_ora_8381.trc file 995 24-DEC-18
CPROD1_ora_8540.trc file 881 24-DEC-18
CPROD1_ora_8540.trm file 71 24-DEC-18
CPROD1_ora_9876.trc file 881 24-DEC-18
CPROD1_ora_9876.trm file 71 24-DEC-18
CPROD1_ora_11142.trm file 72 24-DEC-18
CPROD1_ora_11142.trc file 883 24-DEC-18
CPROD1_ora_11182.trc file 883 24-DEC-18
CPROD1_ora_11182.trm file 72 24-DEC-18
CPROD1_ora_55077.trm file 73 24-DEC-18
CPROD1_ora_55077.trc file 997 24-DEC-18
CPROD1_ora_92260.trm file 73 24-DEC-18
CPROD1_ora_92260.trc file 997 24-DEC-18
CPROD1_ora_123869.trc file 1000 24-DEC-18
CPROD1_ora_123869.trm file 74 24-DEC-18
CPROD1_ora_41305.trc file 998 24-DEC-18
CPROD1_ora_41305.trm file 73 24-DEC-18
CPROD1_j002_3293.trc file 114049 24-DEC-18
CPROD1_j002_3293.trm file 370 24-DEC-18
CPROD1_mmon_71739.trc file 7511332 24-DEC-18
CPROD1_mmon_71739.trm file 738330 24-DEC-18
CPROD1_ora_92888.trc file 997 24-DEC-18
CPROD1_ora_92888.trm file 73 24-DEC-18
trace/ directory 323584 24-DEC-18
alert_CPROD1.log file 204808 24-DEC-18
CPROD1_ora_70145.trc file 1470 24-DEC-18
CPROD1_ora_70145.trm file 109 24-DEC-18

3845 rows selected.

SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) where filename like '%CPROD1_s003_81573.trc%';

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_s003_81573.trc file 1948134047 23-DEC-18

			

Oracle Script: hcheck.sql

Hi all,
Just documenting here, in case you don’t know this script:

Script to Check for Known Problems in oracle8i, oracle9i, oracle10g, Oracle 11g and Oracle 12c( Doc ID 136697.1 )

SQL> @hcheck.sql
HCheck Version 07MAY18 on 12-AUG-2019 21:51:32
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name: R360PD
Is CDB?: NO

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj                 ... 1202000100 <=  *All Rel* 08/12 21:51:32 PASS
.- MissingOIDOnObjCol          ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- SourceNotInObj              ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- OversizedFiles              ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- PoorDefaultStorage          ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- PoorStorage                 ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- TabPartCountMismatch        ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- OrphanedTabComPart          ... 1202000100 <=  *All Rel* 08/12 21:51:34 PASS
.- MissingSum$                 ... 1202000100 <=  *All Rel* 08/12 21:51:38 PASS
.- MissingDir$                 ... 1202000100 <=  *All Rel* 08/12 21:51:38 PASS
.- DuplicateDataobj            ... 1202000100 <=  *All Rel* 08/12 21:51:39 PASS
.- ObjSynMissing               ... 1202000100 <=  *All Rel* 08/12 21:55:56 PASS
.- ObjSeqMissing               ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedUndo                ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedIndex               ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedIndexPartition      ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedIndexSubPartition   ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedTable               ... 1202000100 <=  *All Rel* 08/12 21:56:00 PASS
.- OrphanedTablePartition      ... 1202000100 <=  *All Rel* 08/12 21:56:00 PASS
.- OrphanedTableSubPartition   ... 1202000100 <=  *All Rel* 08/12 21:56:00 PASS
.- MissingPartCol              ... 1202000100 <=  *All Rel* 08/12 21:56:04 PASS
.- OrphanedSeg$                ... 1202000100 <=  *All Rel* 08/12 21:56:04 PASS
.- OrphanedIndPartObj#         ... 1202000100 <=  *All Rel* 08/12 21:56:15 PASS
.- DuplicateBlockUse           ... 1202000100 <=  *All Rel* 08/12 21:56:15 PASS
.- FetUet                      ... 1202000100 <=  *All Rel* 08/12 21:56:17 PASS
.- Uet0Check                   ... 1202000100 <=  *All Rel* 08/12 21:56:17 PASS
.- SeglessUET                  ... 1202000100 <=  *All Rel* 08/12 21:56:18 PASS
.- BadInd$                     ... 1202000100 <=  *All Rel* 08/12 21:56:19 PASS
.- BadTab$                     ... 1202000100 <=  *All Rel* 08/12 21:56:21 PASS
.- BadIcolDepCnt               ... 1202000100 <=  *All Rel* 08/12 21:56:21 PASS
.- ObjIndDobj                  ... 1202000100 <=  *All Rel* 08/12 21:56:21 PASS
.- TrgAfterUpgrade             ... 1202000100 <=  *All Rel* 08/12 21:56:22 PASS
.- ObjType0                    ... 1202000100 <=  *All Rel* 08/12 21:56:22 FAIL

HCKE-0036: Bad OBJ$ entry with TYPE#=0 (Doc ID 1361015.1)
OBJ$ OBJ#=212606505 TYPE#=0 NAME=COMMENTS NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606501 TYPE#=0 NAME=CONTACT NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606496 TYPE#=0 NAME=HT360_CURRENCY_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=74768696 TYPE#=0 NAME=HT360_USER_ACCESS_MV NAMESPACE=66 Dblink=MDMPROD
OBJ$ OBJ#=183148682 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=186229204 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=115573869 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=212606499 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=212606631 TYPE#=0 NAME=TC_HT_SERVICE_DEL_ATTRIBUTES_V NAMESPACE=66
Dblink=EBSPROD
OBJ$ OBJ#=186229477 TYPE#=0 NAME=GDS360_PROPERTY_REMOTE NAMESPACE=66
Dblink=MDMPROD
OBJ$ OBJ#=148134057 TYPE#=0 NAME=MEDIA_ROI_INS NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606497 TYPE#=0 NAME=MEDIA_ROI_INS NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=75622441 TYPE#=0 NAME=CONTACT_REMOTE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=197171249 TYPE#=0 NAME=CONTACT_REMOTE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134085 TYPE#=0 NAME=CAMPAIGN_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606500 TYPE#=0 NAME=CAMPAIGN_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134088 TYPE#=0 NAME=INSERTION_CODE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606503 TYPE#=0 NAME=INSERTION_CODE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134080 TYPE#=0 NAME=INSERTION_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606504 TYPE#=0 NAME=INSERTION_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134083 TYPE#=0 NAME=INSERTION_ELEMENT NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606502 TYPE#=0 NAME=INSERTION_ELEMENT NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=172793128 TYPE#=0 NAME=SUPPLIER_CONTACT_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=197171212 TYPE#=0 NAME=SUPPLIER_CONTACT_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=183148695 TYPE#=0 NAME=PROPERTY_MAPPING NAMESPACE=66 Dblink=MDMPROD
OBJ$ OBJ#=154666744 TYPE#=0 NAME=PROPERTY_MAPPING NAMESPACE=66 Dblink=MDMPROD

.- BadOwner                    ... 1202000100 <=  *All Rel* 08/12 21:56:22 PASS
.- StmtAuditOnCommit           ... 1202000100 <=  *All Rel* 08/12 21:56:23 PASS
.- BadPublicObjects            ... 1202000100 <=  *All Rel* 08/12 21:56:23 PASS
.- BadSegFreelist              ... 1202000100 <=  *All Rel* 08/12 21:56:23 PASS
.- BadDepends                  ... 1202000100 <=  *All Rel* 08/12 21:56:25 PASS
.- CheckDual                   ... 1202000100 <=  *All Rel* 08/12 21:56:26 PASS
.- ObjectNames                 ... 1202000100 <=  *All Rel* 08/12 21:56:26 WARN

HCKW-0018: OBJECT name clashes with SCHEMA name (Doc ID 2363142.1)
Schema=TC_MONITOR JOB=TC_MONITOR.TC_MONITOR
Schema=TCDWSTAGE DATABASE LINK=PUBLIC.TCDWSTAGE

.- BadCboHiLo                  ... 1202000100 <=  *All Rel* 08/12 21:56:27 PASS
.- ChkIotTs                    ... 1202000100 <=  *All Rel* 08/12 21:56:32 PASS
.- NoSegmentIndex              ... 1202000100 <=  *All Rel* 08/12 21:56:32 PASS
.- BadNextObject               ... 1202000100 <=  *All Rel* 08/12 21:56:32 PASS
.- DroppedROTS                 ... 1202000100 <=  *All Rel* 08/12 21:56:33 PASS
.- FilBlkZero                  ... 1202000100 <=  *All Rel* 08/12 21:56:33 PASS
.- DbmsSchemaCopy              ... 1202000100 <=  *All Rel* 08/12 21:56:33 PASS .- OrphanedIdnseqObj           ... 1202000100 >  1201000000 08/12 21:56:33 PASS
.- OrphanedIdnseqSeq           ... 1202000100 >  1201000000 08/12 21:56:33 PASS
.- OrphanedObjError            ... 1202000100 >  1102000000 08/12 21:56:33 PASS
.- ObjNotLob                   ... 1202000100 <=  *All Rel* 08/12 21:56:34 PASS
.- MaxControlfSeq              ... 1202000100 <=  *All Rel* 08/12 21:56:34 PASS .- SegNotInDeferredStg         ... 1202000100 >  1102000000 08/12 21:56:34 PASS
.- SystemNotRfile1             ... 1202000100 >   902000000 08/12 21:56:35 PASS
.- DictOwnNonDefaultSYSTEM     ... 1202000100 <=  *All Rel* 08/12 21:56:35 PASS
.- OrphanTrigger               ... 1202000100 <=  *All Rel* 08/12 21:56:35 PASS
.- ObjNotTrigger               ... 1202000100 <=  *All Rel* 08/12 21:56:35 PASS 

---------------------------------------
12-AUG-2019 21:56:36  Elapsed: 304 secs
---------------------------------------
Found 26 potential problem(s) and 2 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/u01/app/oracle/diag/rdbms/r360pdlo/r360pd3/trace/r360pd3_ora_310651_HCHECK.trc

 

useful, right?

Checking Basic Licensing Info on a DB Server

Hi all!
So, I got a new client and started checking on his licensing and hardware. Then I realized how “non-standard” this is and Oracle should probably provide a better way to do it. So I decided to share a few things:

# Checking Oracle Version Installed:

[oracle@greporaSRV inventory]$ cd /opt/oraInventory/logs
[oracle@greporaSRV logs]$  grep "\- Database edition" installActions*.log
INFO: - Database edition : Standard Edition One (Create and configure a database)

# Checking number of Sockets

[root@greporaSRV ~]# cat /proc/cpuinfo | grep "physical id" | sort -u | wc -l
1

# Checking number of CPU Cores per Socket

[root@greporaSRV ~]# lscpu | grep 'socket'
Core(s) per socket:    4

Parallel file transfer on Linux

Hi all,

I had a request to copy a ton of files from one file system to another,  I know that there are tools that can help with that like rsync but due to some requirements and me wanted to do some scripting I put something together  to help with this request. This is not the 1st time I do something like this but it is the 1st time I share 🙂

What I’m sharing is now what I did for the request I mentioned but you will get an idea

The script will copy a list of files from one server to another. This list I usually create by using find like this

find /Directory_which_I_want_to_copy -type f > file_list.txt

The script will receive some parameters as listed below

parallel_xfer.ksh    

Also a requirement for this to work is that you can ssh to the target server without a password.

It will keep X parallel sessions running at all times until there are new files to start copying it, After all copies are started, it will monitor them until completion. Also the script assumes that the source and target directory destination is the same but this is easily changed if needed.

The logging needs to be improved but it will show the file it started as well their processes count

Hope it helps

Elisson

#!/bin/ksh
DBLIST=${1}
DEST_DIR=${2}
SERVER=${3}
NUM_SESS=${4}
STARTED_COUNT=0
RUN_COUNT=0

trim() {
    local var=$@
    var="${var#"${var%%[![:space:]]*}"}"   # remove leading whitespace characters
    var="${var%"${var##*[![:space:]]}"}"   # remove trailing whitespace characters
    echo "$var"
}


FILE_COUNT="$(cat ${DEST_DIR}/$DBLIST | wc -l)"
cd ${DEST_DIR}
for FILE in $(cat $DBLIST)
do
 STARTED_COUNT=$((${STARTED_COUNT}+1))
 if [ ${RUN_COUNT} -le ${NUM_SESS} ]
 then
   sftp -Cq USER@${SERVER}:${FILE} ${DEST_DIR}/. >/dev/null 2>/dev/null &
   echo "`date` - Transferring file ${FILE} to ${DEST_DIR} - ${STARTED_COUNT}/$(trim ${FILE_COUNT})"
   sleep 5
 fi
 echo "\n"

 RUN_COUNT=$(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l)

 while [ ${RUN_COUNT} -ge ${NUM_SESS} ]
 do
  RUN_COUNT=$(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l)
  echo "`date` - $(trim ${RUN_COUNT}) transfer processes running"
  echo "`date` - Amount of GB transferred `du -sg ${DEST_DIR}`\n"
  sleep 60
 done
done

while [ $(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l) -gt 0  ]
do
 RUN_COUNT=$(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l)
 echo "`date` - $(trim ${RUN_COUNT}) transfer processes running"
 echo "`date` - Amount of GB transferred - `du -sg ${DEST_DIR}`\n"
 sleep 60
done
echo "`date` - Transfered completed"

Automatic Graceful Shutdown and Consistent Startup Method for Oracle DB on Linux

Hi all,

This is quite a common question whenever I arrive on any new company. The things is, there are more then one way to implement this depending on your environment, licensing and version.

So I decided to compile here some sort of summary for this:

1. Prefer to use Oracle Restart
This is the automated and validated method provided by Oracle, however it can be a bit confusing in some items which can lead us to think it’s not working. Here is a summary of the the configuration I recommend:

a) Configure database management to AUTOMATIC on SRVCTL

srvctl modify database -y AUTOMATIC
  • If AUTOMATIC (the default), the database is automatically restored to its previous running condition (started or stopped) upon restart of the database host computer.
  • If MANUAL, the database is never automatically restarted upon restart of the database host computer.

Refhttps://docs.oracle.com/cd/E11882_01/server.112/e25494/restart.htm#BABHHAHI

b) Set AUTO_START=always on CRSCTL

crsctl modify resource ora.grepora.db -attr AUTO_START=always
  • ALWAYS: Restarts the resource when the server restarts regardless of the state of the resource when the server stopped.
  • RESTORE: Restores the resource to the same state that it was in when the server stopped. Oracle Clusterware attempts to restart the resource if the value of TARGET was ONLINE before the server stopped.
  • NEVER: Oracle Clusterware never restarts the resource regardless of the state of the resource when the server stopped.

NOTE: On Oracle 11.2, the database auto start policy in the clusterware is restore, which means that clusterware will remember the last state of the database. As well as database, Oracle 11.2 comes by default with several important resources with attribute AUTO_START=restore in the profile.

NOTE2: 12c on you might need to use the flag “-unsupported” on command above (crsctl modify resource ora.grepora.db -attr AUTO_START=always – unsupported).

Refhttps://docs.oracle.com/cd/E11882_01/rac.112/e41959/resatt.htm#CHDFFEHJ

Observation: This is recommended for all the required components managed by those tools, like databases, asm, listener, diskgroups, etc.
I wrote an article about it with an script that I made by my own and can help you: https://grepora.com/2018/08/22/services-not-starting-automatically-with-crs-after-reboot/

A common problem: “I set the SRVCTL to Automatic, but databases still not starting automatically’.”
Explanation: When database Management policy is configured as AUTOMATIC and the resource of the database parameter AUTO_START is configured as restore, the cluster will restore its last state, because the cluster level is the first in the chain of commands, it overrides the database.

c) Save desired state of Pluggable Databases in case of Multitenant:
With the PDB in desired state, save it with command below:

ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

When the CDB start, it will bring the pdbs to it saved states.

2. As second Option, Oracle Provided Scripts

Oracle has some scripts to automate it in a standard and supported way. This is documented for 12.1 in Stopping and Starting Oracle Software.

NOTES:

  • Oracle 11gR2 documentation states the use of the dbstart and dbshut scripts are deprecated. It’s supposed to be replaced by Oracle Restart.
  • The Oracle 12c documentation has no mention of the deprecation of dbstart and dbshut and has reinstated the documentation about them (as I linked above). So, feel free to use dbstart and dbshut in a supported manner for all versions of the database.

I also wrote an article about those, with some info and scripts: https://grepora.com/2017/11/22/how-to-setup-automatic-startup-and-shutdown-of-an-oracle-database-on-linux-not-using-oracle-restart/

Observation: Item 1.c is still recommended here.

3. Community proven scripts

As a third option, we would have some community scripts, which are usually proven and doesn’t require us to remember or to code everything. I’d use some additional time reviewing and testing them though, as they are not Oracle provided/supported.

In general, I’d recommend the material produced by Tim Hall (Oracle Base): https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

He has additional articles that may help for other versions:

  • Automating Shutdown and Startup (12.2)
  • Automating Shutdown and Startup (12.1)
  • Automating Shutdown and Startup (11.2)
  • Automating Shutdown and Startup (10.2)
  • Automating Startup and Shutdown (10.1)
  • Automating Database Startup and Shutdown (9.2)
  • Linux Services (systemd, systemctl)

 

Some Additional Twists:

  • The Oracle Restart configuration assume the CRS is left “enabled”. Disabling it means we don’t want it to start automatically. So, if you want the CRS to start with your server, it need to be enabled. After this, to start targets, depend on configurations as per mentioned on my previous post.
  •  Oracle will no execute any rpm change or relink automatically, as this is not part of any “restart” process. It may be required due any configuration change or corruption, and it cannot be automated.
  • Regarding gracefulness, it depends on the configuration you have on your SRVCTL too. It’s configured using stop and start option, as per example below:
srvctl modify database -d [db_unique_name] -s [start_options] -t [stop_options]
  • So for your case, it seems to me a complete command containing what was recommended on my previous post PLUS gracefulness, it would be:
srvctl modify database -d [db_unique_name] -s OPEN -t IMMEDIATE -y automatic

(Note the SRVCTL syntax can very on the versions. This one is valid for 11.2).

I hope this helps you on understanding the process.

See you next time!