ODA Useful Commands to Manage VMs

1. To restart the vm:

oakcli stop vm [vm-name]
oakcli stop vm [vm-name] -force
oakcli start vm [vm-name]

2. To show All vm status

oakcli show vm

Example:

root@server oak]# oakcli show vm

NAME NODENUM MEMORY VCPU STATE REPOSITORY
vm1 1 8192 4 ONLINE fileshare

3. Listing Configured Options for a VM Template

oakcli show vmtemplate ol6linux_64

4. Adding a Network to the VM Template

oakcli modify vmtemplate ol6linux_64 -addnetwork net1

5. Configure CPU, Memory on the Template

oakcli configure vmtemplate ol6linux_64 -vcpu 4 -maxvcpu 8 -cpucap 10 -memory 3000M -maxmemory 6G -os OTHER_LINUX

6. Create a VM by Cloning from Template

oakcli clone vm ol6test -vmtemplate ol6linux_64 -repo repo1 -node oda2

7. Override VM Template Values

oakcli configure vm ol6test -vcpu 6 -memory 4G

8. Configure High Availability and Failover Values

oakcli configure vm ol6test -prefnode oda2 -failover oda1

9. Start a VM

oakcli start vm ol6test

10. Access VM Console for a VM:

oakcli show vmconsole [vm-name]

Hope it helps you!

General ILOM Faults Management

Hi all,
So just a quick reference: Some useful general commands for ILOM:

1. Check for Faults:

/home/boesing> ssh root@[ilom ip]    

Oracle(R) Integrated Lights Out Manager

Version 3.1.2.10 r74387

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

-> cd /SP/faultmgmt
/SP/faultmgmt

-> start shell
Are you sure you want to start /SP/faultmgmt/shell (y/n)? y

faultmgmtsp> 
faultmgmtsp> fmadm 
Usage: fmadm 
  where  is one of the following:
    faulty [-asv] [-u ]   : display list of faulty resources
    faulty -f                   : display faulty on FRUs
    acquit                 : acquit faults on a FRU
    acquit                : acquit faults associated with UUID
    acquit           : acquit faults specified by
                                  (FRU, UUID) combination
    replaced               : replaced faults on a FRU
    repaired               : repaired faults on a FRU
    repair                 : repair faults on a FRU
    rotate errlog               : rotate error log
    rotate fltlog               : rotate fault log

faultmgmtsp> 
faultmgmtsp> fmadm faulty
No faults found

– Another way to see current issues:

show /SP/logs/event/list show faulty

2. Clearing Faults:
In case there is a failure that can be ignored (for example, lost of AC power), it may be cleared:

set /SYS/PSU1 clear_fault_action=true

3. Checking Additional Logs:

start /SYS 
->y 
ls /SYS 
start /SP/console 
-> y
show /SP/logs/event/list 

Some MOS notes for reference:

– Diagnostic information for ILOM, ILO , LO100 issues (Doc ID 1062544.1)
– How to run an ILOM Snapshot on a Sun/Oracle X86 System (Doc ID 1448069.1)
– PSH Procedural Article for ILOM-Based Diagnosis (Doc ID 1155200.1)

Diagsnap Causing Node Eviction on 12c

Hi all,

So, you know how it is when we are having node evictions over a RAC. Lot’s of sessions getting killed, some effects for the clients and applications, also boss bossing in our shoulders to have a quick resolution over this. Plus it’s never an easy thing to drill down and understand.

Troubleshooting node reboots/evictions within Grid Infrastructure (GI) often is difficult due to the lack of Network and OS level resource information. To help circumvent this situation the diagsnap feature has been developed and integrated with Grid Infrastructure. Diagsnap is triggered to collect Network and OS level resource information when a given node is about to get evicted or when Grid Infrastructure is about to crash.

The diagsnap feature is enabled automatically starting from 12.1.0.2 Oct2017 PSU and 12.2.0.1 Oct2017 RU.
For more information about the diagsnap feature, refer to the Document 2345654.1 “What is diagsnap resource in 12c GI and above?”

However, after a lot of research and SR logs sending and interaction with Oracle to investigate a node eviction, we ended up finding a match to Bug 25785073 – OCSSD hangs while DIAGSNAP takes pstack causing a node reboot(Doc ID 25785073.8).

So Diagsnap is not helping, it is the cause of the issues.

After some research, seems this is not the only bug related to it. See some more:

  • Bug 27182006 – Auxiliary commands generated by DIAGSNAP spin CPU(Doc ID 27182006.8)
  • Bug 24692439 – Auxiliary commands generated by DIAGSNAP consumes high CPU(Doc ID 24692439.8)
  • Bug 23101338 – Disable diagsnap after 12.1.0.2.160419 GI PSU patch was installed(Doc ID 23101338.8)
  • Bug 28462215 – The Process diagsnap.pl is Restarted Every 2 Minutes(Doc ID 28462215.8)

Well,

At least for the 12Cs, I’m disabling it in all my environments:

– Check if diagsnap is disabled or not (DIAGSNAP=Disable if disabled, nothing if enabled)

$ egrep '^DIAGSNAP|^PSTACK' /u01/app/12.2.0.1/grid/crf/admin/crf$(hostname -s).ora

– Disable diagsnap (this will disable diagsnap on all nodes)

$ /u01/app/12.2.0.1/grid/bin/oclumon manage -disable diagsnap

– Check that diagsnap is disabled (DIAGSNAP=Disable if disabled, nothing if enabled) — to be done on each node

$ egrep '^DIAGSNAP|^PSTACK' /u01/app/12.2.0.1/grid/crf/admin/crf$(hostname -s).ora

Hope it helps!
Cheers!

Oracle Patching with OPlan

Everyone that I’ve worked with knows that I don’t like patching (and sometimes I try to imagine who does), but they are necessary to corrects bugs and improve the Oracle software stability.

When you have a single node server with one database, the patch planing is no brainer but when you have a RAC with multiple nodes, different Oracle homes and so on, the planning and preparations start to get more complex and it is easy to miss or overlook a step in the planning which can lead to issues during your patching.

So to help me with all that I use oplan. Oplan is a tool which comes with along OPatch and you can get its latest version in patch 6880880

More informations on oplan can be found here: Oracle Software Patching with OPLAN (Doc ID 1306814.1)

OK, so what do I used it most for?

Generating the apply patching steps, which are very in handy:

$ORACLE_HOME/OPatch/oplan/oplan generateApplySteps <bundle patch location>

And my favorite, rollback steps, which I have done more times that I would like to admit:

$ORACLE_HOME/OPatch/oplan/oplan generateRollbackSteps <bundle patch location>

Also as rollback, I do tar of the oracle binaries being patched prior as there times even the rollback did not work :-/

Both files will be created under the directory below and you will see an html and text files.

$ORACLE_HOME/cfgtoollogs/oplan/<TimeStamp>/

This process is to help you organise your steps, read it through prior executing to make sure it makes sense in your environment

Oplan has its limitations, from the Oracle note which I mentioned above:

Data Guard configurations are not supported.
OPlan can be used to create patch plans for Oracle home's running Oracle Data Guard configurations, but OPlan does not consider such an environment usable as 'Data Guard Standby-First Patch Apply' alternative. See the following for additional information on 'Data Guard Standby-First Patch Apply'

<Document 1265700.1> Oracle Patch Assurance - Data Guard Standby-First Patch Apply

Shared Oracle Home Configurations are not supported.

Single Instance Databases running in the same configuration are not supported

Even so I would still use it as it generates a plan based on your target environment adding more information that you would need to do manually if you were only to read the README files from the patching

Hope it helps.

Thanks and until next time

Elisson Almeida

Automatic SQL Tuning Advisor Raising ORA-00600: internal error code, arguments: [qksvcReplaceVC0]

Hi all,

So I got to receive frequently this error, always on same hour, from a database:

ORA-00600: internal error code, arguments: [qksvcReplaceVC0], [], [], [], [], [], [], [], [], [], [], []

Not much was required for matching it to the Automatic SQL Tuning Advisor.

This only seems to happen during execution of Automatic SQL Tuning Advisor. Several bugs have been logged for the issue but have not been resolved as the error is not reproducible at will. For example:

Bug 17401718: ORA-600 [QKSVCREPLACEVC0] USING SQL TUNING ADVISOR
Bug 16491690: ORA-600 [QKSVCREPLACEVC0] WHEN AUTOMATIC SQL TUNING ADVISOR EXECUTED
Bug 13959984: ORA-00600 [QKSVCREPLACEVC0]

How to fix it? Apply the patches!

To workaround it?

A few options:

1. Setting “_replace_virtual_columns” to false.

You can set this parameter at both session (where automatic SQL Tuning Advisor starts)
and system level with the following commands-

SQL> alter session set "_replace_virtual_columns"=false;

SQL> alter system set "_replace_virtual_columns"=false

2. Since it is only failing in the SQL Tuning Advisor auto task and has no effect on the database the error can be ignored.
You can disable that auto task and just run it manually when required:

–check auto job status

SQL> select client_name,status from dba_autotask_task;

SQL> select client_name,status from dba_autotask_client;

SQL> select client_name, operation_name, status from dba_autotask_operation;

–disable SQL Tuning Advisor job

SQL> exec dbms_auto_task_admin.disable ('sql tuning advisor', null, null);

-OR-

SQL> exec dbms_auto_task_admin.disable (client_name => 'sql tuning advisor', operation => null, window_name => null);

–enable SQL Tuning Advisor job

SQL> exec dbms_auto_task_admin.enable ('sql tuning advisor', null, null);

-OR-

SQL> exec dbms_auto_task_admin.enable (client_name => 'sql tuning advisor', operation => null, window_name => null);

 

Hope it helps!

Orphan ASM File Cleanup Script

Hi all,

So I got asked by a client to perform a checking on ASM for orphan files, as they have some frequent create/drop database on this environment, as being a development env.

Also, lots of databases shared the same data diskgroup, so I had to work this out for all databases and also for possible inexistent databases.

Some basic approaches I raised:

1) Locating uncatalloged files in ASM per database.
– Source: https://oraganism.wordpress.com/2012/09/09/orphaned-files-in-asm/
– This approach assumes the files on ASM uncatalogged to any database are the Orphaned ones. Which is a fair assumption.
– But I understand that files can be catalloged and unmonted, which would brake this approach.

2) Listing files in ASM but not in database (v$datafile, v$datafile_copy, v$controlfile, v$tempfile, v$logfile) by database.
– Source: https://oracledba.blogspot.com/2018/11/orphaned-files-in-asm.html
– This seems a fair assumption. Would need to be ran from each database.
– There is not guarantee if this is working properly or not.
– Not clear also if PDB files are included.
– There is another similar one: https://dbaliveblog.wordpress.com/asm-orphaned-file-identification-script/
– Also this one: https://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

3) MOS: Query That Can Be Used to Find Orphaned Datafiles on a 12c ASM Instance (Doc ID 2228573.1)
– From MOS, seems the most recommended approach.
– Attention point: PDB$SEED may not be shown as per: PDB$SEED Datafiles Not Appear In CDB_DATA_FILES (Doc ID 1940806.1)
— On 12.1.0.2, recommended to use “EXCLUDE_SEED_CDB_VIEW”. To check if it can be done on session level.

I downloaded and ran referred script on MOS Script to report the list of files stored in ASM and CURRENTLY NOT OPENED (Doc ID 552082.1) on the environment.
But the results didn’t sound correct.

After a while, I ended up building my own script based on all mentioned approaches and it worked very fine.

After approved I dropped all the listed files, freed a several TBs of space and no database affected. So I’d assume it as correct and would really recommend it for you.

So what did I used:

SQL to Check ASM Space per Database:

set pages 350 timing on
col gname form a10
col dbname form a10
col file_type form a16
break on gname skip 2 on dbname skip 1
compute sum label total_db of gb on dbname
compute sum label total_diskg of gb on gname  
SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                    ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY
    gname,
    dbname,
    file_type
/

Expected Output:

SQL> @asm_sizebydb

GNAME	   DBNAME     FILE_TYPE 	       MB	  GB	 #FILES
---------- ---------- ---------------- ---------- ---------- ----------
DATAC1	   DATABSE1   CONTROLFILE	     2316	   2	      1
		      DATAFILE		  7620756	7442	     49
		      DATAGUARDCONFIG	       16	   0	      2
		      ONLINELOG 	    82536	  81	     14
		      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					7525

	   DATABSE2   CONTROLFILE	      492	   0	      1
		      DATAFILE		  3081604	3009	     47
		      ONLINELOG 	      416	   0	      4
		      PARAMETERFILE	       16	   0	      2
		      PASSWORD			0	   0	      2
		      TEMPFILE		    83372	  81	      3
	   **********				  ----------
	   total_db					3090

	   DATABSE3   CONTROLFILE	      588	   1	      1
		      DATAFILE		  1430712	1397	      8
		      DATAGUARDCONFIG	       16	   0	      2
		      ONLINELOG 	   147816	 144	     18
		      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					1542
[...]

**********					  ----------
total_disk					       76868

SQL To list Orphan files per Database:

SET VERIFY OFF
SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000

DECLARE
   cmd   CLOB;
BEGIN
   FOR c IN (SELECT name Diskgroup
               FROM V$ASM_DISKGROUP)
   LOOP
      FOR l
         IN (SELECT 'rm ' || files files
               FROM
                    (SELECT '+' || c.Diskgroup || files files, TYPE
                       FROM (    SELECT UPPER
                                        (
                                           SYS_CONNECT_BY_PATH (aa.name, '/')
                                        )
                                           files
                                      , aa.reference_index
                                      , b.TYPE
                                   FROM (SELECT file_number
                                              , alias_directory
                                              , name
                                              , reference_index
                                              , parent_index
                                           FROM v$asm_alias) aa
                                      , (SELECT parent_index
                                           FROM (SELECT parent_index
                                                   FROM v$asm_alias
                                                  WHERE     group_number =
                                                               (SELECT group_number
                                                                  FROM v$asm_diskgroup
                                                                 WHERE name =
                                                                          c.Diskgroup)
                                                        AND alias_index = 0)) a
                                      , (SELECT file_number, TYPE
                                           FROM (SELECT file_number, TYPE
                                                   FROM v$asm_file
                                                  WHERE group_number =
                                                           (SELECT group_number
                                                              FROM v$asm_diskgroup
                                                             WHERE name =
                                                                      c.Diskgroup)))
                                        b
                                  WHERE     aa.file_number = b.file_number(+)
                                        AND aa.alias_directory = 'N'
                                        AND b.TYPE IN
                                               ('DATAFILE'
                                              , 'ONLINELOG'
                                              , 'CONTROLFILE'
                                              , 'TEMPFILE')
                             START WITH aa.PARENT_INDEX = a.parent_index
                             CONNECT BY PRIOR aa.reference_index =
                                           aa.parent_index)
                      WHERE SUBSTR
                            (
                               files
                             , INSTR (files, '/', 1, 1)
                             ,   INSTR (files, '/', 1, 2)
                               - INSTR (files, '/', 1, 1)
                               + 1
                            ) =
                               (SELECT dbname
                                  FROM (SELECT    '/'
                                               || UPPER (db_unique_name)
                                               || '/'
                                                  dbname
                                          FROM v$database))
                     MINUS
                     (SELECT UPPER (name) files, 'DATAFILE' TYPE
                        FROM v$datafile
                      UNION ALL
                      SELECT UPPER (name) files, 'TEMPFILE' TYPE
                        FROM v$tempfile
                      UNION ALL
                      SELECT UPPER (name) files, 'CONTROLFILE' TYPE
                        FROM v$controlfile
                       WHERE name LIKE '+' || c.Diskgroup || '%'
                      UNION ALL
                      SELECT UPPER (name), 'CONTROLFILE' TYPE
                        FROM v$datafile_copy
                       WHERE deleted = 'NO'
                      UNION ALL
                      SELECT UPPER (MEMBER) files, 'ONLINELOG' TYPE
                        FROM v$logfile
                       WHERE MEMBER LIKE '+' || c.Diskgroup || '%')))
      LOOP
         DBMS_OUTPUT.put_line (l.files);
      END LOOP;
   END LOOP;
END;
/

Expected Output:

rm +DATA/XPTODB/CONTROLFILE/CURRENT.4928.955985765
rm +DATA/XPTODB/CONTROLFILE/CURRENT.4934.955986589
rm +DATA/XPTODB/CONTROLFILE/CURRENT.4962.955998825
rm +DATA/XPTODB/CONTROLFILE/CURRENT.5063.956480113
rm +DATA/XPTODB/CONTROLFILE/CURRENT.6374.955984145
rm +DATA/XPTODB/CONTROLFILE/CURRENT.7547.955968953
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4936.955985803
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4966.955998847
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7540.955968995
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7574.955984177
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4937.955985803
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4967.955998847
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7542.955968995
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7558.955984177
rm +DATA/XPTODB/DATAFILE/SYSAUX.4935.955986599
rm +DATA/XPTODB/DATAFILE/SYSAUX.4963.955998847
rm +DATA/XPTODB/DATAFILE/SYSAUX.6286.955984161
rm +DATA/XPTODB/DATAFILE/SYSAUX.7544.955968963
rm +DATA/XPTODB/DATAFILE/SYSTEM.4930.955986599
rm +DATA/XPTODB/DATAFILE/SYSTEM.4964.955998847
rm +DATA/XPTODB/DATAFILE/SYSTEM.7536.955968965

To run this for all databases on server (RAC Databases):

export ORAENV_ASK=NO
for DBSID in `ps -ef | grep ora_pmon | grep -v grep | awk -F_ '{ print $3}'` 
do
echo "######" ${DBSID}
export ORACLE_SID=${DBSID}
. oraenv
sqlplus / as sysdba
@script.sql
exit
done

Hope it helps you!

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!

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

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