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