DBA Blogs

Does Oracle 9i Data Guard have real time sync?

Tom Kyte - Fri, 2018-07-06 02:26
Hi We have a Oracle 9i database ,When I set up the data guard I want to use real time sync I got a error .So I want to confirm whether Oracle 9i have this future
Categories: DBA Blogs

update cascade

Tom Kyte - Fri, 2018-07-06 02:26
How do I cascade update on the key fields of linked tables thereby ensuring referencial integrity.
Categories: DBA Blogs

I want to understand this decode function which applied in this query

Tom Kyte - Fri, 2018-07-06 02:26
<code> SELECT a.syrmn, a.orgcode, SUBSTR (a.accode, 1, 3) || '0000000' caccode, SUBSTR (a.accode, 1, 6) || '0000' saccode, accode, DECODE (SIGN (SUM (DECODE (a.sntb, 'D', a.samnt, -a.samnt))), 1, SUM (...
Categories: DBA Blogs

#Exasol Fail-Safety explained

The Oracle Instructor - Thu, 2018-07-05 10:26

The building blocks of an Exasol cluster are commodity Intel servers like e.g. Dell PowerEdge R740 with 96 GB RAM,12 x 1.2 TB SAS Hot-plug hard-drives and 2 x 10Gb Ethernet Cards for the private network. That’s sufficient to deliver outstanding performance combined with high availability. The picture below shows a 4+1 cluster, one of our most popular configurations:

Exasol 4+1 Cluster

Exasol 4+1 Cluster: Shared Nothing Architecture

Each active node hosts one database instance that works on its part of the database (A,B,C,D) in an MPP way. The instances communicate over the private network. Optionally, the private network can be separated into one database network and one storage network. In this case, the instances communicate over the database network. Notice that the instances access their part of the database directly on their local hard drives, they do not need the private network respectively the storage network for that. The reserve node becomes relevant only if one of the active nodes fails. The local hard drives are being setup in RAID 1 pairs, so single disk failures can be tolerated without losing database availability. Not listed is the license node that is required to boot the cluster initially. After that, the license node is no longer required to keep the cluster running.

If data volumes with redundancy 2 are in use – which is the most common case – then each node holds a copy of the data operated on by a neighbor node:

 Redundancy 2

Exasol 4+1 Cluster: Redundancy 2

If a Master-Segment like A is modified, the Slave-Segment (A’) is synchronized accordingly over the private network respectively the storage network.

Availability comes with a price: The raw disk capacity is reduced by half because of the RAID 1 mirroring and again by half because of the redundancy 2, so you remain with approximately (Linux OS and database software also require a small amount of disk space) 1/4 of your raw disk capacity. But since we are running on commodity hardware – no storage servers, no SAN, no SSDs required etc. – this is actually a very competitive price.

Now what if one node fails?

 Node failure

Exasol 4+1 Cluster: Node failure

ExaClusterOS – Exasols Clusterware – will detect the node failure within seconds and shutdown all remaining database instances in order to preserve a consistent state of the database. Then it restarts them again on the still available 3 nodes and also on the Reserve node that now becomes an Active node too. The database itself becomes available again with the node n15 now immediately working with segment B’.

The downtime of the system caused by the node failure is below 30 seconds typically. The restart of the database triggers a threshold called Restore Delay which defaults to 10 Minutes. If within that time the failed node becomes available again, we will just re-synchronize the segments (A’ and B in the example) which can be done fast. The instance on n15 will then work with the segment B as a Master-Segment until the cluster is manually restarted. Then n15 becomes a reserve node again and n12 is active with an instance running there.

If the failed node doesn’t come back within Restore Delay:

 Restore Delay is over

Exasol 4+1 Cluster: Restore Delay is over

We will then create new segments on node n15: A’ is copied from n11 and B is copied from n13. This activity is time-consuming and puts a significant load on the private network, which is why configuring a dedicated storage network may be beneficial to avoid a drop in performance during that period. A new reserve node should now be added to the cluster, replacing the crashed n12.

Categories: DBA Blogs

ORA-01652 unable to extend temp

Tom Kyte - Thu, 2018-07-05 08:06
Hi Team, I went through some weird situation where I am not able to figure out the reason behind error ORA-01652 unable to extend temp. While executing a PL/SQL block at one point I am facing this issue. ORA-01652 unable to extend temp. Whi...
Categories: DBA Blogs

CentOS as the Operating System for Oracle 12.2 database server

Tom Kyte - Thu, 2018-07-05 08:06
Hello Experts, We are planning to install Oracle <b>12.2</b> database server on a Linux <b>CentOS 7.4</b>. <b>My first question</b> May I know whether this setting is supported by Oracle? I've already noted that this OS is not listed under t...
Categories: DBA Blogs

Exadata and direct path read

Tom Kyte - Thu, 2018-07-05 08:06
Hi Tom, In order for smart scan to be achieved , mainly two thing should happen : 1 - The optimizer should choose to full scan the table. 2 - Scanning the data should be done using direct path read. The first requirement is quit simple , bu...
Categories: DBA Blogs

Index Column Order – Impact On Index Branch Blocks Part II (The Weeping Song)

Richard Foote - Wed, 2018-07-04 21:25
In Part I, I discussed how the order of columns in an index makes no real difference to the effectiveness of the index if all columns are referenced via equality predicates. If the leading column has a high number of distinct columns, it might result in less necessary data within index branches as less data […]
Categories: DBA Blogs

regexp question

Tom Kyte - Wed, 2018-07-04 13:46
Hi, I am trying to highlight a search term in a result set feeding a web page. So far I can highlight the entire result, but not just the term match. The problem is in white space mismatches between the column data and the search term that is ente...
Categories: DBA Blogs

IMPLICIT CURSOR ATTRIBUTE SQL%NOTFOUND NOT WORKING

Tom Kyte - Wed, 2018-07-04 13:46
Hi Tom, I am using implicit cursor attributes SQL%NOTFOUND for the below PL/SQL block. But it raises NO_DATA_FOUND exception. It works fine for update and delete statement as they are not going to raise the NO_DATA_FOUND exception. ...
Categories: DBA Blogs

Partner Webcast – Oracle Modern Application Development for Enterprises

Today’s global marketplace dictates that every organization - from large to small and across all industries - can only remain relevant through successful digital initiatives. These initiatives...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast - Maximizing the value of Oracle Cloud Infrastructure (OCI)

Oracle Cloud Infrastructure is a set of cloud services that enable you to build and run a wide range of applications in a highly-available environment. With Oracle Cloud Infrastructure we’ve...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Announcement: New Europe Seminar in Brussels, Belgium 27-28 September 2018

Richard Foote - Tue, 2018-07-03 01:49
Due to popular demand, I’ll be running another of my acclaimed seminars in Europe later in the year, this time in Brussels, Belgium on 27-28 September 2018. This is a must attend seminar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high […]
Categories: DBA Blogs

Email Domain Extraction using sql query

Tom Kyte - Tue, 2018-07-03 01:06
if the part of email before domain matches for eg in : xyz@gmail.com xyzef@gmail.com if xyz and xyzef does not match ,then do not consider this records and if the scenario is like , if non domain part are equal like xyz@gmail.com xyz@g...
Categories: DBA Blogs

Make Index Invisible for a session

Tom Kyte - Tue, 2018-07-03 01:06
Hi With the advent of In-Memory capabilities in 12C is there a way to make the optimizer ignore indexes for particular table for a session. The reason being if we want to use Oracle 12C as HTAP (Hybrid Transactional Analytical Processing) we wou...
Categories: DBA Blogs

ORA-31011: XML parsing failed issue

Tom Kyte - Tue, 2018-07-03 01:06
While extracting data from xml parsing issue is coming. Issue is coming while extracting data for field_name 401K_LOAN_1 and 401K_LOAN_2. Please advise. <code>PROCEDURE SP1( SXML IN CLOB, p_status OUT VARCHAR2, p_message OUT VARCHAR...
Categories: DBA Blogs

XMLForest for more than one tables

Tom Kyte - Tue, 2018-07-03 01:06
hi all i'm trying to make an enquiry from 2 tables with XML function, but i have some problems: this is sql i'm using for this sample select from the 2 different tables i'm using <code>select deptno,dname from dept; select empno,ename,de...
Categories: DBA Blogs

Transpose Rows Into Column

Tom Kyte - Mon, 2018-07-02 06:46
Hi, My question is regarding transpose of rows into columns: <code>BANNER_CODE/DIV_CODE/LEG_MATNR/SAP_MATNR/MAKTX/LEG_MATKL/SAP_MATKL/LEG_WHERL/SAP_WHERL/ CS/1/10137/58351/BAKE KING CHOCOLATE RICE 160G/384/10203004/34/SG/1 GH/1/36762/212615/M...
Categories: DBA Blogs

acfs not supported in oracle linux 7 though oracle documents says its supported.

Tom Kyte - Sun, 2018-07-01 12:26
Hi tom, In a rac environment with grid infrastructure i am trying to configure acfs. But i am getting following error. ACFS-9459: ADVM/ACFS is not supported on this OS version: 'unknown' ACFS-9201: Not Supported Blogs says i need to appl...
Categories: DBA Blogs

Creating linguistic indexes for CANADIAN FRENCH

Tom Kyte - Sun, 2018-07-01 12:26
When creating a linguistic index, I am not able to specify CANADIAN FRENCH. Oracle reports that the NLS parameter string is invalid. I suspect that it's because there is a space in it, but the answer eludes me. Here is a short example of a script ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs