Oracle DB and beyond: practical tips, Q&A: August 2016

Wednesday 31 August 2016

How to check for which tables is the supplemental logging enabled in the database?

Displaying Supplemental Log Groups at a Source Database

To check whether one or more log groups are specified for the table at the source database, run the following query:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20

SELECT 
    LOG_GROUP_NAME, 
    TABLE_NAME, 
    DECODE(ALWAYS,
             'ALWAYS', 'Unconditional',
             'CONDITIONAL', 'Conditional') ALWAYS,
    LOG_GROUP_TYPE
  FROM DBA_LOG_GROUPS;

Your output looks similar to the following:

                                     Conditional or
Log Group            Table           Unconditional  Type of Log Group
-------------------- --------------- -------------- --------------------
LOG_GROUP_DEP_PK     DEPARTMENTS     Unconditional  USER LOG GROUP
SYS_C002105          REGIONS         Unconditional  PRIMARY KEY LOGGING
SYS_C002106          REGIONS         Conditional    FOREIGN KEY LOGGING
SYS_C002110          LOCATIONS       Unonditional   ALL COLUMN LOGGING
SYS_C002111          COUNTRIES       Conditional    ALL COLUMN LOGGING
LOG_GROUP_JOBS_CR    JOBS            Conditional    USER LOG GROUP

Friday 26 August 2016

Linux: how to transfer files and bypass the "jump" server

If we need to transfer files from the server A to the server C, but we need to jump through server B, since we don't have connectivity straight to C, we can use the setup below:

On server A, put the lines below in the file : .ssh/config:

# MYPROJ

Host C*

ProxyCommand ssh serverB nc -w 43200 %h 22 2> /dev/null

StrictHostKeyChecking=no

How to determine the ascii value of a specific character within a row?

How to determine the ascii value of a specific character within a row?
Let's say we are searching for the character § inside the DB.

SQL> select dump('§') from dual;

DUMP('§')
---------------------
Typ=96 Len=2: 194,167

SQL> select * from k where col1 like '%'||chr(194)||'%'||chr(167)||'%';

COL1
--------------------------------------------------------------------------------
§

Thanks to the site below:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2143700800346224648

Tuesday 2 August 2016

How often are the archived logs generated?

SQL> set line 200 pages 80
SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

Session altered.

Hourly report:

select trunc(COMPLETION_TIME,'HH') Hour,thread# , count(*) Archives from v$archived_log
where COMPLETION_TIME > sysdate-1
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1;

HOUR THREAD# ARCHIVES
------------------ ---------- ----------
07-mar-17 15:00:00 1 3
07-mar-17 16:00:00 1 6
07-mar-17 17:00:00 1 6
07-mar-17 18:00:00 1 9
07-mar-17 19:00:00 1 19
07-mar-17 20:00:00 1 5
07-mar-17 21:00:00 1 20
07-mar-17 22:00:00 1 8
07-mar-17 23:00:00 1 4
08-mar-17 00:00:00 1 4
08-mar-17 01:00:00 1 19
08-mar-17 02:00:00 1 15
08-mar-17 03:00:00 1 25
08-mar-17 04:00:00 1 10
08-mar-17 05:00:00 1 5
08-mar-17 06:00:00 1 12
08-mar-17 07:00:00 1 4
08-mar-17 08:00:00 1 16
08-mar-17 09:00:00 1 6
08-mar-17 10:00:00 1 20
08-mar-17 11:00:00 1 6
08-mar-17 12:00:00 1 6
08-mar-17 13:00:00 1 19
08-mar-17 14:00:00 1 7
08-mar-17 15:00:00 1 3

25 rows selected.

Daily report:

select trunc(COMPLETION_TIME,'DD') Day,thread# , count(*) Archives from v$archived_log
where COMPLETION_TIME > sysdate-10
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1
/

Another question would be, how many archived logs per day are generated, on average?

On average we generate 77 logs/day.

SQL> l

select avg(count(*)) Archives from v$archived_log

where COMPLETION_TIME > sysdate-100

group by trunc(COMPLETION_TIME,'DD')

SQL> /