Thinking Out Loud

May 28, 2018

Using Postman For REST API on Oracle Cloud Infrastructure

Filed under: Uncategorized — mdinh @ 3:21 pm
Tags:

Just completed quick training for Oracle Cloud Infrastructure (OCI)

REST API can be used on OCI for automation and presentation.

I find presentation for JSON results from CLI on server is UGLY.

Finally, I found POSTMAN which does to very nice job of presentation.

POSTMAN has results history, command history, search function.

https://www.getpostman.com/apps

 

Advertisements

May 18, 2018

Using GoldenGate LogDump To Find Bad Data

Filed under: GoldenGate — mdinh @ 4:38 am

GoldenGate Primary Extract from source database captured data without any issues;

however, target was not able to consume the data since GoldenGate process would ABEND.

Unfortunately, I cannot provide all the details but high level.

Logdump 2433 >pos 0
+++ Starting with GoldenGate 12.2 TDR – Table Definition Record is in trail
+++ This will provide metadata for the table

Reading forward from RBA 0 
Logdump 2434 >SCANFORMETADATA
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :    86  (x0056)   IO Time    : 2018/03/25 18:24:23.307.797   
IOType     :   170  (xaa)     OrigNode   :     1  (x01) 
TransInd   :     .  (x01)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
DDR/TDR Idx:   (001, 000)     AuditPos   : 3277290592 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/03/25 18:24:23.307.797 Metadata             Len 86 RBA 1689 
Name:  
*
DDR Version: 1
Database type: ORACLE
Character set ID: CESU-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: GMT-05:00
Global name: DBNAME
* 
Logdump 2435 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   561  (x0231)   IO Time    : 2018/03/25 18:28:16.317.879   
IOType     :   170  (xaa)     OrigNode   :     2  (x02) 
TransInd   :     .  (x01)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
DDR/TDR Idx:   (001, 001)     AuditPos   : 3352410292 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/03/25 18:28:16.317.879 Metadata             Len 561 RBA 1826 
Name: SCHEMA.TABLE 
*
 1)Name          2)Data Type        3)External Length  4)Fetch Offset      5)Scale         6)Level
 7)Null          8)Bump if Odd      9)Internal Length 10)Binary Length    11)Table Length 12)Most Sig DT
13)Least Sig DT 14)High Precision  15)Low Precision   16)Elementary Item  17)Occurs       18)Key Column
19)Sub DataType 20)Native DataType 21)Character Set   22)Character Length 23)LOB Type     24)Partial Type
*
TDR version: 1
Definition for table SCHEMA.TABLE
Record Length: 4298
Columns: 7
ID           64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0
A            B      C         D  E  F G H     I      J      K  L M N O P    Q R

Position 18)Key Column identify column is Primary Key; hence, ID a primary key column.
I labeled each column using the alphabet for reference and R is the 18th letter of the alphabet.

Even though ID is the first column of the table, GoldenGate offset starts with 0

SQL> desc SCHEMA.TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
0 ID                                        NOT NULL NUMBER
5 TEXT                                               VARCHAR2(4000)

SQL> r
  1  select b.column_name,a.constraint_type
  2  from dba_constraints a, dba_cons_columns b
  3  where a.table_name = b.table_name
  4  and a.constraint_name=b.constraint_name
  5  and a.constraint_type = 'P'
  6  and a.table_name='TABLE'
  7  and a.owner='SCHEMA'
  8*

COLUMN_NAME                    C
------------------------------ -
ID                             P

Logdump 2500 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   885  (x0375)   IO Time    : 2018/05/10 14:16:43.000.514   
IOType     :    15  (x0f)     OrigNode   :   255  (xff) 
TransInd   :     .  (x02)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :      36261       AuditPos   : 2145515112 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/05/10 14:16:43.000.514 FieldComp            Len   885 RBA 88598006 
Name: SCHEMA.TABLE  (TDR Index: 1) 
After  Image:                                        ....................
 0000 000c 0000 0008 3239 3533 3432 3936 0001 0009 | ........29534296....  
 0000 0005 3937 3932 3600 0200 0500 0000 0131 0003 | ....................
 0005 0000 0001 3600 0400 0b00 0000 0731 3738 3337 | ....................
 3939 0005 0325 0000 0321 456e 6a6f 7920 796f 7572 | ....................  
 2073 7461 7920 696e 206f 7572 2068 6f74 656c 7320 | ....................
 7769 7468 2074 6865 206d 6f73 7420 6469 7363 6f75 | ....................  
 6e74 6564 2072 6174                               | ....................
Column     0 (x0000), Len    12 (x000c)  
 0000 0008 3239 3533 3432 3936                     | ....29534296 --- PRIMARY KEY VALUE
Column     1 (x0001), Len     9 (x0009)  
 0000 0005 3937 3932 36                            | ....97926  
Column     2 (x0002), Len     5 (x0005)  
 0000 0001 31                                      | ....1  
Column     3 (x0003), Len     5 (x0005)  
 0000 0001 36                                      | ....6  

SQL> select id, substr(TEXT,1,30) txt, vsize(TEXT), length(TEXT) from SCHEMA.TABLE where ID in (29534296);

        ID LTXT                           VSIZE(LTEXT) LENGTH(LTEXT)
---------- ------------------------------ ------------ -------------
  29534296 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx          801           800

SQL>

For database, NLS_LENGTH_SEMANTICS=BYTE; hence, 800 characters should equal 800 bytes. 
However, one character is most likely a multi-byte character which target cannot consume 
as it is not able to handle multi-byte.

May 15, 2018

Goldengate REPORTING P2

Filed under: GoldenGate — mdinh @ 2:35 am

Previous post for Goldengate REPORTING

Goldengate reporting has to be the least implemented functionality until it is needed and often in hindsight.

Here is an example of what I would normally implement.

STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 15 MINUTES, RATE
DISCARDROLLOVER AT 00:01 ON SUNDAY

If business only cares about monthly data, then would not make sense to collect daily nor would it make sense for business to request such.

May 11, 2018

DataGuard Convention

Filed under: Dataguard,dgmgrl — mdinh @ 11:58 am

Good convention and implementation make life and automation so much simpler and more time for golfing.

I have seen some really poor and really good implementation and here’s a good one.

Wish I can take credit for it and unfortunately I cannot.

The scripts were created by whoa.

Scripts an be run from primary or standby for any instances provided profile to source database environment exists on host.

Use ORACLE_UNQNAME for DataGuard Environment

====================================================================================================
+++ PRIMARY RACONENODE
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test_1"

$ env|grep ORACLE

ORACLE_SID=test_1 (db_name)
ORACLE_UNQNAME=test (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: test
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile: +FLASH/test/spfiletest.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test
Database instances:
Disk Groups: FLASH,DATA
Mount point paths:
Services: testsvc
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: test
Candidate servers: host01,host02
Database is administrator managed

====================================================================================================
+++ STANDBY NON-RAC
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      testdr
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test"

$ env|grep ORACLE
ORACLE_SID=test (db_name)
ORACLE_UNQNAME=testdr (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: testdr
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: test
Disk Groups: DATA,FLASH
Services:

====================================================================================================
DATAGUARD BROKER CONFIGURATION
====================================================================================================
DGMGRL> show configuration

Configuration - dg_test (db_name)

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database (db_unique_name)
    testdr - Physical standby database (db_unique_name)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database test

Database - test

  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    test_1
    test_2

Database Status:
SUCCESS

DGMGRL> show database testdr

Database - testdr

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       7 seconds (computed 0 seconds ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    test

Database Status:
SUCCESS

DGMGRL> exit

====================================================================================================
ls -l dg*.sh
====================================================================================================
-rwxr-xr-x    1 oracle   dba             377 May 08 21:50 dg_lag.sh
-rwxr-x---    1 oracle   dba             445 May 08 20:12 dg_start.sh
-rwxr-xr-x    1 oracle   dba             337 May 08 20:05 dg_status.sh
-rwxr-x---    1 oracle   dba             447 May 08 20:12 dg_stop.sh

====================================================================================================
dg_lag.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show database ${ORACLE_SID} SendQEntries
show database ${ORACLE_UNQNAME} RecvQEntries
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
cat dg_start.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-ON';
edit database ${ORACLE_UNQNAME} set state='APPLY-ON';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_status.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_stop.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-OFF';
edit database ${ORACLE_UNQNAME} set state='APPLY-OFF';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
check_dg
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit


May 5, 2018

DBFS Nightmare

Filed under: 12c,GoldenGate — mdinh @ 4:19 pm
====================================================================================================
How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1)	
====================================================================================================
High level overview of the DBMS_DBFS_SFS.REORGANIZEFS procedure:
----------------------------------------------------------------------------------------------------
1) Create a NEW tablespace
2) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the new tablespace.
3) Run dbms_dbfs_sfs.reorganizeFS -->>
   EXEC DBMS_DBFS_SFS.REORGANIZEFS(SRCSTORE=>'FS_FS1', DSTSTORE=>'FS_TMP_FS');
4) The dbfs data is now in the smaller NEW tablespace.
5) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem
6) The OLD original tablespace is empty now.
----------------------------------------------------------------------------------------------------
To reuse the same tablespace again please follow the below steps
----------------------------------------------------------------------------------------------------
7) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the OLD ORIGINAL tablespace.
8) Run dbms_dbfs_sfs.reorganizeFS
9) The dbfs data is now in the smaller the ORIGINAL tablespace.
10) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem
11) The NEW small tablespace is empty now and can be dropped.

====================================================================================================
DBFS tablespace keep growing not using expired blocks (Doc ID 2327299.1)	
====================================================================================================
----------------------------------------------------------------------------------------------------
First perform the DBFS re-org to cleanup the tablespace after removing the files at file system level by following below document
----------------------------------------------------------------------------------------------------
How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1)

----------------------------------------------------------------------------------------------------
After the re-org, set the  below parameter to reuse the expired blocks
----------------------------------------------------------------------------------------------------
ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0;

====================================================================================================
ORA-1654 - DBFS FREE SPACE NOT RECLAIMED AFTER CLEARING FILES (Doc ID 1948305.1)	
====================================================================================================
----------------------------------------------------------------------------------------------------
a) Check Lob retention setting:
----------------------------------------------------------------------------------------------------
  ex. SQL> select 
  table_name,retention_type,retention_value,retention,securefile from dba_lobs 
  where table_name = 'T_FS1';

----------------------------------------------------------------------------------------------------  
b) Change LOB retention to none.
----------------------------------------------------------------------------------------------------
  ex. SQL> alter table DBFS_USER.T_FS1 modify lob (FILEDATA) (retention none);

After countless discussions, team found GOLD.

12.1 Shrinking and Reorganizing DBFS Filesystems

STEPS:

sqlplus /as sysdba
create bigfile tablespace DBFS_NEW datafile size 8G autoextend on next 1G maxsize 70G;
grant dba to dbfs_user;
alter user dbfs_user default role all;
exit

+++ REORGANIZE DBFS FILESYSTEM FS1 IN TABLESPACE DBFS_TS INTO A NEW TABLESPACE DBFS_NEW, 
+++ USING A TEMPORARY FILESYSTEM NAMED TMP_FS, WHERE ALL FILESYSTEMS BELONG TO DATABASE USER DBFS_USER
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user
@dbfs_create_filesystem DBFS_NEW TMP_FS
exec dbms_dbfs_sfs.reorganizefs('FS1','TMP_FS');
@dbfs_drop_filesystem TMP_FS
purge user_recyclebin;
exit

sqlplus / aa sysdba 
revoke dba from dbfs_user;
select count(*) from dba_extents where tablespace_name='DBFS_TS'; 
select count(*) from dba_extents where tablespace_name='DBFS_NEW'; 
-- DROP TABLESPACE HAVING ZERO EXTENTS
-- BE CAREFUL IN CASE USER AND DBFS ARE USING SAME TABLESPACE
Example:
create user dbfs_user identified by **** default tablespace dbfs_ts quota unlimited on dbfs_ts;
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts FS1

TRUE CONFESSION:

I created an imperfect plan at first that might have saved me from a major catastrophe.

Create DBFS_TMP in a separate Disk Group since I was concern existing may not have enough storage.
Reorg from DBFS_TS to DBFS_TMP (bad idea to name tablespace as DBFS_TMP as LOB Segments migrated here).
Reorg from DBFS_TMP back to DBFS_TS.
Drop tablespace DBFS_TMP.

There’s 29G difference between old and new. How much if this space will be reclaimable due to HWM in data file?

Performing reorg twice is really not an option in prod.

RESULTS:

SEGMENT_NAME                   TABLESPACE_NAME                        MB    EXTENTS
------------------------------ ------------------------------ ---------- ----------
LOB_SFS$_FST_1                 DBFS_TS                        30647.1875       1851
LOB_SFS$_FST_6225924           DBFS_NEW                         1025.125        130

WARNINGS:

This has only been tested in a vacuum (non-prod env w very low activities) – YMMV.

May 3, 2018

Multiplex Redo Log

Filed under: 12c,oracle — mdinh @ 4:14 am

When db_create_online_log_dest_1 is defined, REDO log is not multiplexed which is good for creating STANDBY REDO.

REDO log is created at db_create_online_log_dest_1 ONLY.

However, when creating ONLINE REDO, db_create_online_log_dest_1 should NOT be defined to be multiplexed.

REDO logs are created at db_create_file_dest and db_recovery_file_dest.

[oracle@db-asm-1 sql]$ sqlplus / as sysdba @ logfile.sql

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 3 05:56:30 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

05:56:30 SYS @ owl:>show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1                      +FRA
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
05:56:30 SYS @ owl:>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 7116M

05:56:30 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc;

    GROUP# MEMBER                                                                           TYPE    STATUS
---------- -------------------------------------------------------------------------------- ------- -------
         1 +DATA/OWL/ONLINELOG/group_1.261.962643743                                        ONLINE
         1 +FRA/OWL/ONLINELOG/group_1.257.962643743
         2 +DATA/OWL/ONLINELOG/group_2.262.962643743
         2 +FRA/OWL/ONLINELOG/group_2.258.962643743
         3 +DATA/OWL/ONLINELOG/group_3.263.962643745
         3 +FRA/OWL/ONLINELOG/group_3.259.962643745

6 rows selected.

05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2;

    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------------
         1          1         79  104857600 CURRENT
         2          1         77  104857600 INACTIVE
         3          1         78  104857600 INACTIVE

05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2;

no rows selected

05:56:30 SYS @ owl:>alter database add standby logfile thread 1 group 11 size 104857600;

Database altered.

05:57:03 SYS @ owl:>alter system set db_create_online_log_dest_1='';

System altered.

05:57:39 SYS @ owl:>alter database add logfile thread 1 group 4 size 104857600;

Database altered.

05:58:01 SYS @ owl:>@logfile.sql
05:58:06 SYS @ owl:>set lines 200 tab off trimsp on pages 1000
05:58:06 SYS @ owl:>col member for a80
05:58:06 SYS @ owl:>break on TYPE
05:58:06 SYS @ owl:>set echo on
05:58:06 SYS @ owl:>show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
05:58:06 SYS @ owl:>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 7116M
05:58:06 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc;

    GROUP# MEMBER                                                                           TYPE    STATUS
---------- -------------------------------------------------------------------------------- ------- -------
         1 +DATA/OWL/ONLINELOG/group_1.261.962643743                                        ONLINE
         1 +FRA/OWL/ONLINELOG/group_1.257.962643743
         2 +DATA/OWL/ONLINELOG/group_2.262.962643743
         2 +FRA/OWL/ONLINELOG/group_2.258.962643743
         3 +DATA/OWL/ONLINELOG/group_3.263.962643745
         3 +FRA/OWL/ONLINELOG/group_3.259.962643745
         4 +DATA/OWL/ONLINELOG/group_4.267.975131881
         4 +FRA/OWL/ONLINELOG/group_4.295.975131881
        11 +FRA/OWL/ONLINELOG/group_11.296.975131823                                        STANDBY

9 rows selected.

05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2;

    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------------
         1          1         79  104857600 CURRENT
         2          1         77  104857600 INACTIVE
         3          1         78  104857600 INACTIVE
         4          1          0  104857600 UNUSED

05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2;

    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------
        11          1          0  104857600 UNASSIGNED

05:58:07 SYS @ owl:>

Create a free website or blog at WordPress.com.