Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for the ‘Uncategorized’ Category

« Previous Entries
Next Entries »

Installing and configuring Oracle 21c using RPM method.

Posted by FatDBA on September 6, 2021

Hi Folks,

I know there are already few posts there explaining how to install Oracle 21c database using RPMs, but this one is to explicate both installing the software and creating a test PDB database after RPM installation using ‘configure’ command.

Alright, so let me first install the oracle-database-preinstall-21c package which will do all pre-work for you.

[root@localhost ~]#
[root@localhost ~]# yum install oracle-database-preinstall-21c.x86_64
BDB2053 Freeing read locks for locker 0x829: 3296/140273180206912
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-21c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: ksh for package: oracle-database-preinstall-21c-1.0-1.el7.x86_64
--> Running transaction check
---> Package ksh.x86_64 0:20120801-142.0.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                                            Arch                       Version                                  Repository                      Size
=============================================================================================================================================================
Installing:
 oracle-database-preinstall-21c                     x86_64                     1.0-1.el7                                ol7_latest                      26 k
Installing for dependencies:
 ksh                                                x86_64                     20120801-142.0.1.el7                     ol7_latest                     882 k

Transaction Summary
=============================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 908 k
Installed size: 3.2 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/ol7_latest/packages/oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Public key for oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm is not installed
(1/2): oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm                                                                            |  26 kB  00:00:01
(2/2): ksh-20120801-142.0.1.el7.x86_64.rpm                                                                                            | 882 kB  00:00:02
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        350 kB/s | 908 kB  00:00:02
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
Importing GPG key 0xEC551F03:
 Userid     : "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"
 Fingerprint: 4214 4123 fecf c55b 9086 313d 72f9 7b74 ec55 1f03
 Package    : 7:oraclelinux-release-7.7-1.0.5.el7.x86_64 (@anaconda/7.7)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : ksh-20120801-142.0.1.el7.x86_64                                                                                                           1/2
  Installing : oracle-database-preinstall-21c-1.0-1.el7.x86_64                                                                                           2/2
  Verifying  : oracle-database-preinstall-21c-1.0-1.el7.x86_64                                                                                           1/2
  Verifying  : ksh-20120801-142.0.1.el7.x86_64                                                                                                           2/2

Installed:
  oracle-database-preinstall-21c.x86_64 0:1.0-1.el7

Dependency Installed:
  ksh.x86_64 0:20120801-142.0.1.el7

Complete!
[root@localhost ~]#

Now when all pre-work is done, time to install the software using RPM package which I’ve downloaded from Oracle’s website.

[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# yum -y localinstall  oracle-database-ee-21c-1.0-1.ol7.x86_64.rpm
Loaded plugins: langpacks, ulninfo
Examining oracle-database-ee-21c-1.0-1.ol7.x86_64.rpm: oracle-database-ee-21c-1.0-1.x86_64
Marking oracle-database-ee-21c-1.0-1.ol7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-21c.x86_64 0:1.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                                  Arch                     Version                  Repository                                                  Size
=============================================================================================================================================================
Installing:
 oracle-database-ee-21c                   x86_64                   1.0-1                    /oracle-database-ee-21c-1.0-1.ol7.x86_64                   7.1 G

Transaction Summary
=============================================================================================================================================================
Install  1 Package

Total size: 7.1 G
Installed size: 7.1 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-ee-21c-1.0-1.x86_64                                                                                                       1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-21c configure
  Verifying  : oracle-database-ee-21c-1.0-1.x86_64                                                                                                       1/1

Installed:
  oracle-database-ee-21c.x86_64 0:1.0-1

Complete!
[root@localhost ~]#

Installation of software is finished! Next we will create a test database with name ORCLCDB and a pluggable database with name ORCLPDB1.

[root@localhost ~]# /etc/init.d/oracledb_ORCLCDB-21c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.
[root@localhost ~]#

Okay, its all set. Lets connect with the CDB and the pluggable database that we’ve created above.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Sep 2 12:03:22 2021
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> alter session set container = ORCLPDB1;

Session altered.


SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

My top 5 Oracle 21c features …

Posted by FatDBA on September 3, 2021

Hi Guys,

Recently I started the ‘Top 5’ series where I share my top 5 features in any particular tool or product. Last time I did for SQL Developer command line (SQLcl) & TOP utility, this time it will be about top 5 features in Oracle 21c database.

So, without any particular order, below are my top 5 Oracle 21c features …

1. Immutable Tables :

Native Blockchain Tables provide in-database immutable, insert-only tables. This type of tamper resistance helps protect against hacks and illegal changes and is a great feature added into Oracle 21c database. Even an account with DBA role cannot modify there tables. Immutable tables intended for use in an environment where it is required that an audit trail could potentially be tampered with my insertion but that once a record was inserted it would not be possible to alter or delete it except within the date constraints imposed as part of the NO DROP and NO DELETE clauses.

Let me do a demo to explain!

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Sep 2 12:03:22 2021
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL>
--- You will get an error if you try to create it in the roo container.
SQL> create immutable table testimmu (
  id            number,
  testname         varchar2(20),
  class           number,
  created_date  date,
  constraint testimmu_pk primary key (id)
)
no drop until 1 days idle
no delete until 20 days after insert; 
create immutable table testimmu (
*
ERROR at line 1:
ORA-05729: blockchain or immutable table cannot be created in root container


SQL> alter session set container = ORCLPDB1;

Session altered.


SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>


SQL> show user
USER is "SYS"
SQL>
SQL> create immutable table testimmu (
  id            number,
  testname         varchar2(20),
  class           number,
  created_date  date,
  constraint testimmu_pk primary key (id))
no drop until 1 days idle
no delete until 20 days after insert;  

Table created.

SQL>
SQL>
SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';

Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
                  20 NO                       1

SQL>
-- lets try to alter the NO DELETE clause.
SQL> alter table testimmu no delete until 60 days after insert;

Table altered.

SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';

Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
                  60 NO                       1

-- What happens when anyone tries to lower down that ?
SQL>  alter table testimmu no delete until 59 days after insert;
 alter table testimmu no delete until 59 days after insert
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered


-- Lets insert some data.
SQL> insert into testimmu (id, testname, class, created_date) values (10,'Elisa',50,sysdate-1);

1 row created.

SQL>
SQL> select * from testimmu;

        ID TESTNAME                  CLASS CREATED_D
---------- -------------------- ---------- ---------
        10 Elisa                        50 01-SEP-21

-- Now try to UPDATE the table record.
SQL> update testimmu set CLASS=40 where TESTNAME='Elisa';
update testimmu set CLASS=40 where TESTNAME='Elisa'
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table



SQL> alter table testimmu no drop;

Table altered.

SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';

Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
                  60 NO                  365000

SQL>

-- Now will try to drop it and will see what will happen.
SQL> drop table testimmu;
drop table testimmu
           *
ERROR at line 1:
ORA-05723: drop blockchain or immutable table TESTIMMU not allowed


SQL> alter table testimmu no drop until 10 days idle;
alter table testimmu no drop until 10 days idle
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered

2. Compare EXECUTION PLANS:

Starting from 21c, now you can compare your execution plans. This is a great in-build feature which helps you to identify the differences between any two plans. Maybe a demo can help explaining how …

SQL> explain plan
  2  set statement_id = 'd1'
  3  for select /*+ full(bigtab) */ * from bigtab where id=840;

Explained.

SQL>
SQL> explain plan
  2  set statement_id = 'd2'
  3  for select /*+ index(bigtab) */ * from bigtab where id=840;

Explained.

SQL>


SQL> VARIABLE d varchar2(5000)
SQL> exec :d := dbms_xplan.compare_explain('d1','d2')

PL/SQL procedure successfully completed.

SQL>


SQL> print d

D
----------------------------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SYS
  Total number of plans  : 2
  Number of findings     : 1
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : SYS
 Plan Table Name        : PLAN_TABLE
 Statement ID           : d1
 Plan ID                : 1
 Plan Database Version  : 21.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : No SQL Text

Plan
-----------------------------

 Plan Hash Value  : 441133017

-----------------------------------------------------------------------
| Id  | Operation           | Name   | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |   74 |  2590 |   71 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | BIGTAB |   74 |  2590 |   71 | 00:00:01 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=840)


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : SYS
 Plan Table Name        : PLAN_TABLE
 Statement ID           : d2
 Plan ID                : 2
 Plan Database Version  : 21.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : No SQL Text

Plan
-----------------------------

 Plan Hash Value  : 3941851520

--------------------------------------------------------------------------------------------
| Id  | Operation                             | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |           |   74 |  2590 |   87 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BIGTAB    |   74 |  2590 |   87 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | IDX_TESTA |   74 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=840)


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


Comparison Results (1):
-----------------------------
 1. Query block SEL$1, Alias "BIGTAB"@"SEL$1": Access path is different -
    reference plan: FULL (line: 1), current plan: INDEX_RS_ASC (lines: 1, 2).


---------------------------------------------------------------------------------------------

3. CHECKSUM with export dumps

This is again a great feature and this is to avoid any data tampering or modifications in export dumps. So, you generate the checksum at the time of export backup and if someone modifies any data into the dump (via any editing tool), it will be highlighted during import time.

-- To create a checksum at the time of exporting the data in to dump.
[oracle@localhost admin]$ expdp system/*****@*** DIRECTORY=testdirectory1 DUMPFILE=bigtabtestbkp.dmp tables=bigtab CHECKSUM=YES

-- To verify checksum during import
[oracle@localhost admin]$ impdp system/*****@*** DUMPFILE=bigtabtestbkp.dmp verify_checksum=yes

4. Zero Down Time Timezone Upgrade :

One problem with this is that DST patch required “startup upgrade”, I mean it is not a RAC-rolling patch and standby databases have to be in MOUNT mode. With this feature now these patches are RAC-rolling patches and standby databases can be OPEN-ed. This is really cool! Who knows they will even be part of RU & RUR’s 🙂

5. MULTIVALUE INDEX on JSON data.

I’ve recently starting using JSON data for one of our data and I know how difficult it was when you are doing searches using JSON_EXISTS or JSON_QUERY operators in your query.
Now in 21c, a new create index syntax CREATE MULTIVALUE INDEX allows you to create a functional index on arrays of strings or numbers within a JSON type column. Each unique value within the array will become a searchable index entry. This avoids the need for full JSON scans to find values within arrays in JSON columns, when searched using the JSON_EXISTS or JSON_QUERY operators.

CREATE MULTIVALUE INDEX idx_jsndatest ON mytable tempjsdata (temp.jcol.item_grade.numberOnly());

There are few other good features like automatic materialized views, SQL Macros, Expressions in Initialization Parameters (i.e. alter system set pga_aggregate_target=’sga_target/2‘ now possible) etc. available with 21c.

Hope It Helped
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

With Oracle 21c, now you have Clusterware REST API …

Posted by FatDBA on September 1, 2021

Hi Guys,

While reading official database 21c guides, I came across something really cool – Starting from 21c you have Clusterware REST API 🙂 … REST API is available in previous versions too, but not for Clusterware command line utilities, but with release of 21c this is finally here ..

The REST application programming interfaces (APIs) for Oracle Clusterware makes you capable to remotely execute commands on your database cluster, whether in the Oracle Cloud, at remote physical locations, or locally purveyed. With the remote running of REST interface commands, you are able to get back information about that execution, including output, error codes, and execution lengths. The REST interface allows secure support for Oracle Clusterware command line utilities like CRSCTL, CLUVFY and SRVCTL.

REST APIs for Oracle Clusterware expect that the CDP Cross Cluster Domain Protocol (CDP) daemon is running on all of the SCAN VIPs of the cluster. To support the ability to make requests from outside the cluster, you can run the srvctl modify cdp command to provide a list of IPs or networks in CIDR format.

Below are few of the cluster commands you can run using REST APIs …

To enable connections from outside the cluster, run the following commands.

$ srvctl start cdp
$ srvctl modify cdp -allow "ip/networkid1,ip/networkid2,.."

You can view the configuration information with the following command

$ srvctl config cdp

Get the list of all homes

curl -k -X GET https://scan-name:port/grid/cmd/v1/cmd/ --user admin:DixitTestPassword

Create a job (crsctl) and monitor the status

curl -k -X POST \
    https://scan-name:port/grid/cmd/v1/cmd/exec \
    '-H "accept: text/plain,text/javascript,application/json"' \
    '-H "content-type: application/vnd.oracle.resource+json;type=singular"' \
     --user admin:DixitTestPassword \
    '-d  {"command" : ["crsctl", "stat", "res", "-t"], "runAsUser":"osUser", "userPassword":"osPasswd"}'

curl -k -X GET https://scan-name:port/grid/cmd/v1/cmd/jobs/myJobId --user admin:DixitTestPassword

Monitor the status of all jobs

curl -k -X GET \
    https://scan-name:port/grid/cmd/v1/cmd/jobs/ --user admin:DixitTestPassword

Delete a job

curl -k -X DELETE \ 
         https://scan-name:port/grid/cmd/v1/cmd/jobs/myJobId --user admin:DixitTestPassword

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

Oracle zero downtime migration 21.2

Posted by FatDBA on August 31, 2021

I am happy, excited and I guess this time no much writing and explanation is needed, as this sole image is enough 🙂
Pic courtesy: Oracle Corp

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

When and why the optimizer switched to RBO from CBO mode … Why I cannot see that in 10053 trace ??

Posted by FatDBA on August 28, 2021

Hi Guys,

Recently I was working on one performance issue where one critical SQL statement started consuming more time. After a quick check I saw a switch was happened to RBO mode from CBO, but wasn’t sure on when and why the optimizer mode was switched. And the expected answer to my quest is to generate the debug 10053 trace file to get some insight about the cost-based optimizer internal calculations and to check the cardinality, selectivity and draw a good parallel with the way cost of table, index or sort etc. may be calculated.

Usually the best way to work out what’s going on in this situation is to look at the optimizer debug trace event, 10053 trace file. I always prefer to generate optimizer traces in such situations when the mighty optimizer messed up things. Being a performance consultant, it had saved me so many times in the past, always a best bet for me.

But this time it was looking little different, I couldn’t see details about why optimizer switched the mode in the ‘Query‘ section of the trace. I was totally perplexed, I mean this was not the first time I was looking for that information in the trace file. Why it’s not there, what happened .. 😦

This was Oracle 19.3.0.0.0 database running on RHEL, I tried metalink and found one document specific to this issue and luckily this was happening all due to a known bug 31130156. The problem was later on solved after we applied the bug-fix patch and interpreted the reason of the mode switch (I will write another post about the core problem) …

Note: It can be very difficult to interpret the 10053 optimizer trace if you don’t have any prior experience on it. I recommend readers to check one great document written by Wolfgang Breitling with title ‘A Look Under The Hood Of CBO’.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: | Leave a Comment »

OGG-01201 Error reported by MGR Access denied

Posted by FatDBA on August 22, 2021

Hi Guys,

Last week encountered a problem with one old GG setup running on v12.2 where the extract was failing with errors OGG-01201/OGG-01668 when doing Initial load.

ERROR   OGG-01201  Oracle GoldenGate Capture for Oracle, exld1.prm:  Error reported by MGR : Access denied
ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, exld1.prm:  PROCESS ABENDING

This ‘access denied’ error was there even when the login information was correct for both source and target systems. I was overly confused and wasn’t sure what was causing the issue!

What I come to know after reading a particular piece of documentation, in version 12.2 of GG, the default behavior is the MANAGER and related EXTRACT/REPLICAT cannot be started or stopped remotely as by default there is only deny rule. And while I was trying to do the initial load on the source server and attempts to starts the replicat on target server, I hit the error. This is a security feature and is to prevent unauthorized access to Oracle GoldenGate manager processes and the processes under its control.

Solution to the problem is add “ACCESSRULE, PROG *, IPADDR *, ALLOW” to your manager parameter file on the target system, something like below. The ACCESSRULE parameter restricts the remote system access.

-- GoldenGate Manager Parameter File (mgr.prm) on Target system
--
userid xxxxxxx, password xxxxxxx
PORT 7810
ACCESSRULE, PROG REPLICAT, IPADDR 10.11.01.15, ALLOW
PURGEOLDEXTRACTS ./dirdat/rp*, USECHECKPOINTS, MINKEEPHOURS 4

Here you can also set priority using PRI (0-99) which specifies the priority. The PROG parameter could be anything like GGSCI, GUI, MGR/MANAGER, REPLICAT, COLLECTOR|SERVER and * for all options (default). IPADDR specifies from which IP can access the specified program. Login_ID specifies with RMTHOST configuration and ALLOW | DENY specifies allow or deny the access.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

New JSON features – The old lovely database is new again with 21c

Posted by FatDBA on August 21, 2021

Oracle Database supports relational, graph, spatial, text, OLAP, XML, and JSON data – yes, all at once in one database.
The Oracle Database 21c provides a native JSON data type in binary format. This data type can be used in tables, uses less space and is faster. It’s uniquely designed Oracle Binary JSON format (OSON) is able to speed up both OLAP and OLTP workloads over JSON documents.

I’d recently did some tests and found JSON datatype is now fully integrated into all components of the 21c database and have few new things added to improve its performance. This post is all about JSON datatype in the Oracle 21c Database eco-system, new features, improvements etc.

So, before I move ahead, would like to first build the foundation for the readers, lets create a table with with JSON Data and do some examples.

-- Create a table with JSON datatype.
CREATE TABLE testOrder
 (did NUMBER PRIMARY KEY, jdoc JSON)


-- Let's insert some data to the table
INSERT INTO testOrder
VALUES (1, ' {"testOrder": {
"podate": "2015-06-03",
"shippingAddress": {"street": "3467 35th Ave",
 "city" : "Clara", “state”: “CA”, "zip":
94612},
"comments" : "Discounted sales Foundation Day",
"sparse_id" :"PFHA35",
"items": [
 {"name" : "TV", "price": 341.55, "quantity": 2,
 "parts": [
 {"partName": "remoteCon", "partQuantity": 1},
 {"partName": "antenna”, "partQuantity": 2}]},
 {"name": “PC”, “price”: 441.78, "quantity": 10,
 "parts": [
 {"partName": "mousepad", "partQuantity": 2},
 {"partName": "keyboard", "partQuantity": 1}]}
]}}');



-- Do some SELECT ops
SELECT did,
 po.jdoc.testOrder.podate.date(),
 po.jdoc.testOrder.shippingAddress,
 po.jdoc.testOrder.items[*].count(),
 po.jdoc.testOrder.item[1]
FROM testOrder po
WHERE po.jdoc.testOrder.podate.date() =
TO_DATE(‘2015-06-03’,'YYYY-MM-DD') AND
po.jdoc.testOrder.shippingAddress.zip.number()
BETWEEN 84610 AND 84620;


SELECT JSON {
 ‘name’ : li.itemName,
 ‘sales’ : li.price * li.quantity
}
FROM lineItems_rel li 




-- That's how to UPDATE 
UPDATE testOrder po
SET jdoc = JSON_TRANSFORM(jdoc,
 REPLACE
‘$.testOrder.shippingAddress.city’
 = ‘Oakland’,
 REPLACE ‘$.testOrder.shippingAddress.zip’
 = 94607,
 SET '$.testOrder.contactPhone' =
 JSON('["(415)-867-8560","(500)312-8198"]'),
 REMOVE ‘$.testOrder.sparse_id’,
 APPEND ‘$.testOrder.items’ =
 JSON(‘{“items” :[{“name”:”iphone”,
 “price” : 635.54, “quantity” :2}]}’))
WHERE po.jdoc,testOrder.podate.date() =
 TO_DATE(‘2019-07-01’);

So, that’s how you can create, query, update your JSON data in any table, pretty cool right 🙂

Okay, coming back to the purpose of the post – What is new in Oracle 21c in terms of JSON support ?

  • Though JSON data type was added in Oracle 20c to provide native support, but is generally available in version 21c.
  • Earlier to 21c, users can only use a single-value functional index to accelerate JSON_VALUE() predicate evaluation. Antecedently, a functional index was bounded to index at most one value per row for JSON that meant a field value having at most one occurrence. In 21c, a user can create a multi-value functional index on a JSON datatype column to index elements within a JSON array. This speeds up the rating of JSON_EXISTS() – an operator allowing the use array of equivalence predicates of the SQL/JSON path language.
  • Oracle 21c includes several other enhancements to the JSON functionality in the database JSON_SCALAR function, that creates an instance of a JSON type from a SQL scalar value.
  • JSON_TRANSFORM function was introduced in Oracle Database 21c to make JSON data alterations simpler or easier in complexity.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: | Leave a Comment »

What’s new in Golden Gate version 21c ?

Posted by FatDBA on August 20, 2021

Hi Guys,

Oracle has recently released Golden Gate version 21.1, this happened immediately after they released database version 21c (21.3) for on-prem. Today’s post is all about new features and changes happened with this new GG version.

  • Oracle GoldenGate is available with Microservices Architecture : This release of Oracle GoldenGate is available with Microservices Architecture only.
  • This release of Oracle GoldenGate is available with Microservices Architecture only.
  • Automatic Extract of tables with supplemental logging is supported : Oracle GoldenGate provides a new auto_capture mode to capture changes for all the tables that are enabled for logical replication. You can list the tables enabled for auto-capture using the LIST TABLES AUTO_CAPTURE command option. Use the TRANLOGOPTIONS INTEGRATEDPARAMS auto_capture option to set up automatic capture.
  • Oracle native JSON datatype is supported : Oracle GoldenGate capture and apply processes now support the new native JSON datatype, which is supported by Oracle Database 21c and higher.
  • Enhanced Automatic Conflict Detection and Resolution for Oracle Database 21c
  • Autonomous Database Extract is supported : Oracle GoldenGate can now capture from the Autonomous Databases in OCI.
  • Large DDL (greater than 4 MB) replication is supported : DDLs that are greater than 4 MB in size will be provided replication support.
  • DB_UNIQUE_NAME with heartbeat table : DB_UNIQUE_NAME is available with the heartbeat table to allow users to uniquely identify the source of the heartbeat.
  • Oracle GoldenGate binaries are no longer installed on a shared drive : Oracle always recommended installing the Oracle GoldenGate binaries (OGG_HOME) on a local file system as a best practice. From this release onward, it is a requirement. The binaries must be installed on local drives.
  • Partition Filtering
  • A new Extract needs to be created when the DB timezone is changed : You need to create new Extract if DB timezone is changed, especially in case of Oracle Cloud deployment.
  • DB_UNIQUE_NAME with trail file header : DB_UNIQUE_NAME is added in the trail file header along with DB_NAME, which helps in troubleshooting replication in active-active environments, where mostly all replicas have the same DB_NAME but identify each replica site uniquely using the DB_UNIQUE_NAME.
  • Per PDB Capture
  • Parallel Replicat Core Infrastructure Support for Heterogeneous Databases : Parallel Replicat is supported with SQL Server, DB2 z/OS, and MySQL.

Release announcement link : https://blogs.oracle.com/dataintegration/oracle-goldengate-21c-release-announcement

Hope It Helped
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Kafka Producer: Error while fetching metadata with correlation id INVALID_REPLICATION_FACTOR

Posted by FatDBA on August 14, 2021

Hi Guys,

Recently I was working on a replication project where we used Kafka to move data from source to target. I tried to create a test topic using Kafka producer console and immediately kicked out with error which says “INVALID_REPLICATION_FACTOR”. This we were doing on a test VM with single CPU and with limited system resources.

[root@cantowintert bin]#
[root@cantowintert bin]# kafka-console-producer.sh --broker-list 127.0.0.1:9092 --topic first_topic
OpenJDK 64-Bit Server VM warning: If the number of processors is expected to increase from one, then you should configure the number of parallel GC threads appropriately using -XX:ParallelGCThreads=N
>hello prashant
[2021-07-26 08:18:30,051] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 40 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,154] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 41 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,260] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 42 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,367] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 43 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,471] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 44 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,576] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 45 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,681] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 46 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,788] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 47 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:30,896] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 48 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:31,000] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 49 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:31,103] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 50 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:18:31,221] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 51 : {first_topic=INVALID_REPLICATION_FACTOR} (org.apache.kafka.clients.NetworkClient)
^Corg.apache.kafka.common.KafkaException: Producer closed while send in progress
        at org.apache.kafka.clients.producer.KafkaProducer.doSend(KafkaProducer.java:909)
        at org.apache.kafka.clients.producer.KafkaProducer.send(KafkaProducer.java:885)
        at kafka.tools.ConsoleProducer$.send(ConsoleProducer.scala:71)
        at kafka.tools.ConsoleProducer$.main(ConsoleProducer.scala:53)
        at kafka.tools.ConsoleProducer.main(ConsoleProducer.scala)
Caused by: org.apache.kafka.common.KafkaException: Requested metadata update after close
        at org.apache.kafka.clients.producer.internals.ProducerMetadata.awaitUpdate(ProducerMetadata.java:126)
        at org.apache.kafka.clients.producer.KafkaProducer.waitOnMetadata(KafkaProducer.java:1047)
        at org.apache.kafka.clients.producer.KafkaProducer.doSend(KafkaProducer.java:906)
        ... 4 more
[root@cantowintert bin]#

Lets check what is captured in Kafka server startup logs, and we found the hint that the RF is > than the available brokers.

org.apache.kafka.common.errors.InvalidReplicationFactorException: Replication factor: 1 larger than available brokers: 0.
[2021-07-26 08:24:45,723] WARN [Controller id=0, targetBrokerId=0] Connection to node 0 (cantowintert.bcdomain/192.168.20.129:9092) could not be established. Broker may not be available. (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:25:06,830] WARN [Controller id=0, targetBrokerId=0] Connection to node 0 (cantowintert.bcdomain/192.168.20.129:9092) could not be established. Broker may not be available. (org.apache.kafka.clients.NetworkClient)
[2021-07-26 08:25:27,950] WARN [Controller id=0, targetBrokerId=0] Connection to node 0 (cantowintert.bcdomain/192.168.20.129:9092) could not be established. Broker may not be available.

Solution to the problem is to uncomment this line and restart and try to edit the topic all over again.

listeners=PLAINTEXT://:9092

changed this to

listeners=PLAINTEXT://127.0.0.1:9092

Hope It helped
Prashant Dixit

Posted in Uncategorized | Tagged: , | 1 Comment »

What is that strange looking TRANLOGOPTIONS EXCLUDETAG in parameter file ?

Posted by FatDBA on August 11, 2021

Hi Guys,

Recently someone asked why we have this strange looking entry in Golden Gate extract parameter file which reads ‘TRANLOGOPTIONS EXCLUDETAG 00‘. Why is that, what are those numbers ? I was able to explain him the purpose, would like to write a short post about it.

OGG v12.1.2 has a new EXTRACT parameter TRANLOGOPTIONS EXCLUDETAG. This is typically used to exclude the REPLICAT user in bi-directional configurations. When Extract is in classic or integrated capture mode, use the TRANLOGOPTIONS parameter with the EXCLUDETAG tag option. This parameter directs the Extract process to ignore transactions that are tagged with the specified redo tag. For example:

extract exttestpd
useridalias clouduser
EXTTRAIL ./dirdat/rp, format release 12.1
ddl include all
ddloptions addtrandata, report
tranlogoption excludetag 00 
TABLE dixituser.*;

Changes made by Integrated REP are tagged by default in redo as 00. So adding the EXTRACT parameter TRANLOGOPTIONS EXCLUDETAG 00 Would exclude those operations. The tag can also be explicitly set in REPLICAT using:

DBOPTIONS SETTAG 0885

Then in EXTRACT param:

TRANLOGOPTIONS EXCLUDETAG 0885

The TRANLOGOPTION EXCLUDETAG 00 prevents Golden Gate extract from capturing transactions from the replication which are by default tagged with “00”. The excludetag will ensure that the we don’t run into problems with ping-pong updates.

Some other possible examples of using this parameter are …

TRANLOGOPTIONS EXCLUDETAG 00
TRANLOGOPTIONS EXCLUDETAG +
TRANLOGOPTIONS EXCLUDETAG 0991
TRANLOGOPTIONS EXCLUDETAG 2222 4444

Hope It Helped
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

« Previous Entries
Next Entries »