DBA Blogs

SYS_CONTEXT('userenv','module') behaviour in Database Vault

Tom Kyte - 1 hour 18 min ago
Hello Tom, I have implemented DB Vault on a Oracle database. I created a Vault policy to block adhoc access to application schema using DB tools like Toad etc. The policy should allow only application connection to DB from application s...
Categories: DBA Blogs

Triggers on materialized views

Tom Kyte - Wed, 2018-07-18 07:26
Are triggers on materialized views supported by oracle? If so, is a good practice to use them?
Categories: DBA Blogs


Tom Kyte - Wed, 2018-07-18 07:26
Tom: can you give me some example at which situation IN is better than exist, and vice versa.
Categories: DBA Blogs

Initializing Contexts after getting a connection from a ConnectionPool

Tom Kyte - Wed, 2018-07-18 07:26
I understand best practice is to initialize a connection from a connection pool by clearing the Application Context. However,there are multiple namespaces. Should every namespace be cleared? How does one find the name of every namespace?
Categories: DBA Blogs

DDL for objects

Tom Kyte - Wed, 2018-07-18 07:26
Hi, I wanted to get the DDL for all objects in a database schema. I'm aware of DBMS_METADATA.GET_DDL to get the DDL from a PL/SQL block but was facing the bellow issue: The return type is HUGECLOB and I need it in a directly viewable form simil...
Categories: DBA Blogs

Oracle Database Performance tuning using Application Developer ( Application user)

Tom Kyte - Wed, 2018-07-18 07:26
This question is from the context when App-server and Oracle Database is hosted on a vendor cloud. They are in a separate container and application will be in a separate container. Example, App-server and DB on Oracle cloud and GUI on customer's ...
Categories: DBA Blogs

Announcement: Venue Confirmed For Upcoming Brussels “Oracle Indexing Internals and Best Practices” Seminar

Richard Foote - Wed, 2018-07-18 02:55
I can finally confirm the venue for my upcoming “Oracle Indexing Internals and Best Practices” seminar in beautiful Brussels, Belgium running on 27-28 September 2018. The venue will be the Regus Brussels City Centre Training Rooms Facility, Avenue Louise / Louizalaan 65, Stephanie Square, 1050, Brussels. Note: This will be the last public seminar I’ll run […]
Categories: DBA Blogs

Force logging

Tom Kyte - Tue, 2018-07-17 13:06
Hi Tom, As I have my dc and dr database are working fine and both are in sync. Today I got the force logging mode is no logging So we have planned to make it force logging enable. For that 1.how can we do on primary 2. It is required any downt...
Categories: DBA Blogs

Rebuilding Indexes: Danger With Clustering Factor Calculation (Chilly Down)

Richard Foote - Tue, 2018-07-17 01:33
Let me start by saying if you don’t already following Jonathan Lewis’s excellent Oracle blog, do yourself a favour. In a recent article, Jonathan highlighted a danger with rebuilding indexes (or indeed creating an index) when used in relation to collecting index statistics with the TABLE_CACHED_BLOCKS preference. I’ve discussed the importance of the TABLE_CACHED_BLOCKS statistics […]
Categories: DBA Blogs

#Exasol Cluster Architecture

The Oracle Instructor - Mon, 2018-07-16 11:57

This article gives a more detailed view on the Exasol Cluster Architecture. A high level view is provided here.

Exasol Cluster Nodes: Hardware

An Exasol Cluster is built with commodity Intel servers without any particular expensive components. SAS hard drives and Ethernet Cards are sufficient. Especially there is no need for an additional storage layer like a SAN.

See here for a list of Exasol Certified Servers.

Disk layout

As a best practice the hard drives of Exasol Cluster nodes are configured as RAID 1 pairs. Each cluster node holds four different areas on disk:

1.OS with 50 GB size containing CentOS Linux, EXAClusterOS and the Exasol database executables

2.Swap with 4 GB size

3.Data with 50 GB size containing Logfiles, Coredumps and BucketFS

4.Storage consuming the remaining capacity for the hard drives for the Data Volumes and Archive Volumes

The first three areas can be stored on dedicated disks in which case these disks are also configured in RAID 1 pairs, usually with a smaller size than those that contain the volumes. More common than having dedicated disks is having servers with only one type of disk. These are configured as hardware RAID 1 pairs. On top of that software RAID 0 partitions are being striped across all disks to contain OS, Swap and Data partition.

Exasol 4+1 Cluster: Software Layers

This popular multi-node cluster serves as example to illustrate the concepts explained. It is called 4+1 cluster because it has 4 Active nodes and 1 Reserve node. Active and Reserve nodes have the same layers of software available. The purpose of the Reserve node is explained here. Upon cluster installation, the License Server copies these layers as tar-balls across the private network to the other nodes. The License Server is the only node in the cluster that boots from disk. Upon cluster startup, it provides the required SW layers to the other cluster nodes.

Exasol License Essentials

There are three types of licenses available:

Database RAM License: This most commonly used model specifies the total amount of RAM that can be assigned to databases in the cluster.

Raw Data License: Specifies the maximum size of the raw data you can store across databases in the cluster.

Memory Data License: Specifies the maximum size of the compressed data you can store across all databases.

For licenses based on RAM, Exasol checks the RAM assignment at the start of the database. If the RAM in use exceeds the maximum RAM specified by the license, the database will not start.

For licenses based on data size (raw data license and memory data license), a periodic check is done by Exasol on the size of the data. If the size limit exceeds the value specified in the license, the database does not permit any further data insertion until the usage drops below the specified value.

Customers receive their license as a separate file. To activate the license, these license files are uploaded to the License Server using EXAoperation.

EXAStorage volumes

Storage Volumes are created with EXAoperation on specified nodes.

EXAStorage provides two kinds of volumes:

Data volumes:

Each database needs one volume for persistent data and one temporary volume for temporary data.

While the temporary volume is automatically created by a database process, the persistent data volume has to be created by an Exasol Administrator upon database creation.

Archive volumes:

Archive volumes are used to store backup files of an Exasol database.

Exasol 4+1 Cluster: Data & Archive Volume distribution

Data Volumes and Archive Volumes are hosted on  the hard drives of the active nodes of a cluster.

They consume the major capacity of these drives. The license server usually hosts EXAoperation.

EXAoperation Essentials

EXAoperation is the major management GUI for Exasol Clusters, consisting of an Application Server and a small Configuration Database, both located on the License Server under normal circumstances. EXAoperation can be accessed from all Cluster Nodes via HTTPS. Should the License Server go down, EXAoperation will failover to another node while the availability of the Exasol database is not affected at all.

Shared-nothing architecture (MPP processing)

Exasol was developed as a parallel system and is constructed according to the shared-nothing principle. Data is distributed across all nodes in a cluster. When responding to queries, all nodes co-operate and special parallel algorithms ensure that most data is processed locally in each individual node’s main memory.

When a query is sent to the system, it is first accepted by the node the client is connected to. The query is then distributed to all nodes. Intelligent algorithms optimize the query, determine the best plan of action and generate needed indexes on the fly. The system then processes the partial results based the local datasets. This processing paradigm is also known as SPMD (single program multiple data). All cluster nodes operate on an equal basis, there is no Master Node. The global query result is delivered back to the user through the original connection.

Above picture shows a Cluster with 4 data nodes and one reserve node. The license server is the only server that boots from disk. It provides the OS used by the other nodes over the network.

Exasol uses a shared nothing architecture. The data stored in this database is symbolized with A,B,C,D to indicate that each node contains a different part of the database data. The active nodes n11-n14 each host database instances that operate on their part of the database locally in an MPP way. These instances communicate and coordinate over the private network.

Exasol Network Essentials

Each Cluster node needs at least two network connections: One for the Public Network and one for the Private Network. The Public Network is used for client connections. 1 Gb Ethernet is sufficient usually. The Private Network is used for the Cluster Interconnect of the nodes. 10 GB Ethernet or higher is recommended for the Private Network. Optionally, the Private Network can be separated into one Database Network (Database Instances communicate over it) and one Storage Network (Mirrored Segments are synchronized over this network).

Exasol Redundancy Essentials

Redundancy is an attribute that can be set upon EXAStorage Volume creation. It specifies the number of copies of the data that is hosted on Active Cluster nodes. In practice this is either Redundancy 1 or Redundancy 2. Redundancy 1 means there is no redundancy, so if one node fails, the volume with that redundancy is no longer available. Typically that is only seen with one-node Clusters. Redundancy 2 means that each node holds a copy of data that is operated on by a neighbor node, so the volume remains available if one node fails.

Exasol 4+1 Cluster: Redundancy 2

If volumes are configured with redundancy 2 – which is a best practice – then each node holds a mirror of data that is operated on by a neighbor node. If e.g. n11 modifies A the mirror A‘ on n12 is synchronized over the private network. Should an active node fail, the reserve node will step in starting an instance.


Categories: DBA Blogs

After motherboard change on BDA server eth0 network interface is missing

Alejandro Vargas - Sun, 2018-07-15 03:55

Recently I had a BDA server that was refusing to come up. Every trial to run a start /SYS failed.

We were able to identify this as a motherboard failure, then the motherboard was replaced and we were able to bring up the server using the ILOM.

Back on track I've tried to setup the management network, but this failed because the eth0 NIC was missing.

[root@bdanode01 ~]# ethtool -i eth0 Cannot get driver information: No such device

But I've noticed we had eth1 available, nevertheless not functioning.

[root@bdanode01 ~]# ethtool -i eth1 driver: igb version: 5.3.0-k firmware-version: 3.25, 0x80000681 bus-info: 0000:02:00.0 supports-statistics: yes supports-test: yes supports-eeprom-access: yes supports-register-dump: yes supports-priv-flags: no

First step was to check that the mac address of eth0 was correct, for that I've checked it on the ILOM cli and on file /etc/sysconfig/network-scripts/ifcfg-eth0

-> show /SYS/MB/NET0 /SYS/MB/NET0 Targets: Properties: type = Network Interface ipmi_name = MB/NET0 fru_description = 1G Ethernet Controller fru_manufacturer = INTEL fru_part_number = i210 fru_macaddress = 00:10:e0:de:5d:84 fault_state = OK clear_fault_action = (none) On  /etc/sysconfig/network-scripts/ifcfg-eth0 we have a missing mac address, so I've added it.

Then I've checked /etc/udev/rules.d/70-persistent-net.rules

On this file we had the mismatch. It seems the mac address from the previous motherboard remained in place for eth0 while the new one was assigned to eth1

Then I've edited the file leaving only one line for eth0 and with the correct mac address

[root@bdanode01 ~]# cat /etc/udev/rules.d/70-persistent-net.rules

# This file was automatically generated by the /lib/udev/write_net_rules # program, run by the persistent-net-generator.rules rules file. ## You can modify it, as long as you keep each rule on a single # line, and change only the value of the NAME= key. # PCI device 0x8086:0x1533 (igb) (custom name provided by external tool) SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:10:e0:de:5d:84", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"

After reboot eth0 was back to work and I was able to setup the network.

Categories: DBA Blogs

SQL*Plus copy command errorring on CLOB

Tom Kyte - Fri, 2018-07-13 17:26
I am trying to move select records from one database to another using the copy command and I am getting this error below. Is there a way around this using the copy command? Thank you, A.J. SQL> copy from saturn/******@test insert SFRAREG ...
Categories: DBA Blogs

Global synonym not used

Tom Kyte - Thu, 2018-07-12 23:06
Hello, I need some help understanding the synonym behavior. We have a table in production database with a global synonym, which is being referenced by other objects in the database. As part of a weekly process that runs every weekend, we drop this...
Categories: DBA Blogs

MTS Configuration

Tom Kyte - Thu, 2018-07-12 23:06
Hi Tom ! I have configured MTS. My INIT.ORA and tnsnames.ora file configurations are given below. I have given PORT=1528 in the INIT.ORA file and when I query the V$DISPATCHER, I am surprised to see that the PORT numbers are chaning always i.e ...
Categories: DBA Blogs

Which Indexes are being used by our Application?

Tom Kyte - Thu, 2018-07-12 04:46
We have just gone live and I have noticed alarming growth in the indexes. Closer investigation shows several large, heavy transactional tables with a large number of indexes. I believe we are duplicating indexes and that some of the could be remove...
Categories: DBA Blogs

Is it Possible to Audit Manual Partition Creation Specifically?

Tom Kyte - Tue, 2018-07-10 16:06
Are you aware of any way to specifically audit the addition of new partitions? So far my searching has come up fruitless. Auditing is enabled within the DB which records each <code>alter table</code> command. However, that is too large a net. The spe...
Categories: DBA Blogs

Passing partition name dynamically to get records of a specific partitions from a partitioned table

Tom Kyte - Tue, 2018-07-10 16:06
Categories: DBA Blogs

Partition query - limiting results

Tom Kyte - Mon, 2018-07-09 21:46
I have a situation where I am trying to determine the taxability of an invoiced line. If the invoiced line quantity is 6, for example, the detail lines should not exceed 6. The problem is that if one of the detailed lines causes the cumulative q...
Categories: DBA Blogs


Tom Kyte - Mon, 2018-07-09 21:46
Hi Team, Could you please have a look at below use case and help to form SQL/PLSQL using which I can get the below report.. Table: order_country : holds order id and country its belong. There can be 100 and more countries in that but for sampl...
Categories: DBA Blogs

Using identity columns in Oracle 12c

Tom Kyte - Mon, 2018-07-09 03:26
What is the difference between using sequence.netxval as DEFAULT value in a column or check the column as identity? Please, check the following scenarios: <b>SCENARIO 1:</b> CREATE TABLE USER1.TEST_TABLE ( ID NUMBER GENERATED BY DEFAULT AS I...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs