DBA Blogs

Oracle Advanced Queues dequeue throughput

Tom Kyte - Tue, 2017-09-12 09:46
I am a developer, not a DBA, so know just enough about this to be dangerous. I have a setup a queue using the fairly standard scripts that are on multiple sites. I have used this one: http://www.oracle-developer.net/display.php?id=411 The trouble s...
Categories: DBA Blogs

Update a rows in a table with data from other table with only table name and primary key.

Tom Kyte - Tue, 2017-09-12 09:46
I'm trying to restore a data from a backup data with some special condition for rows. Example: Today database in DB1: <code>City ID Name location type 1 A [SDO.geom] 1 2 B [SDO.geom] 2 District ID Name At...
Categories: DBA Blogs

ORA-24263: Certificate of the remote server does not match the target address.

Tom Kyte - Tue, 2017-09-12 09:46
I encountered ORA-24263 when migrating from Oracle Database 11g to 12.2.0.1. This error is thrown when using UTL_HTTP to access a site via HTTPS, and that site is a multi-server domain (as is common in today's cloud based world). I no longer have...
Categories: DBA Blogs

PL/SQL - Array collection of Records

Tom Kyte - Tue, 2017-09-12 09:46
Hi Tom, I am a regular visitor of your site and I love it. Every time I visit this site, I learn new things. Here is my problem. I tried using a collection(PL/SQL Tables) of Records in Oracle 8i but later came to know that ...
Categories: DBA Blogs

Pro*C procedure call taking longer time

Tom Kyte - Tue, 2017-09-12 09:46
I have below code in pro*c EXEC SQL CALL SP_INTL_BATCH(:l_wrapper, :l_func, :p_env_data, :l_paramstr, :l_user,:l_error); This simple code taking 12 to 13 second to execute and move to next line , while if i normally execute store procedure from...
Categories: DBA Blogs

DB Link Intermittent Failures

Tom Kyte - Tue, 2017-09-12 09:46
Hi Tom We have a DB Link to a database run by an external institution. Some days we can run our import/export processes just fine through the DB Link to the external database. On other days it will go through periods where it just seems to cut...
Categories: DBA Blogs

#GoldenGate Microservices (1 of 5) … ServiceManager

DBASolved - Mon, 2017-09-11 14:30

This is post 1 of a 5 part post related to Oracle GoldenGate 12.3 Microservices. This series of posts will provide some details over the new graphical user interface (GUI) that has been built into Oracle GoldenGate 12.3.

The first of the Oracle GoldenGate Microservices that you need to understand is that ServiceManager. This service is main interface into the Oracle GoldenGate environment. From the HTML5 page that is provided with ServiceManager, you can quickly get an overview of what is running on the server that Oracle GoldenGate is running on. Additonally the ServiceManager acts as the watch dog process for the environment. This means that if a service was to go down, it can be restarted via the ServiceManager.

The ServiceManager can be configured in three different modes. These modes range from manual to Real Application Cluster (RAC) aware. These modes are:

  1. Manual
  2. Deamon
  3. XAG

These configuration options are selectable during the inital run of Oracel GoldenGate Configuration Assistant (oggca.sh) (Figure 1). If you wanted to use it manually, you woudn’t select anything on this screen. If you want to run it on a single server, than the deamon option is a good choice. For the XAG option, you need to make sure you have the current XAG agent (here).

Figure 1:

After ServiceManage is installed, then you will be able to access the interface via a URL (Figure 2). In the example, you will see that I’m usign port 16000. This is not the default port, but a port that I had configured during the installation using Oracle GoldenGate Configuration Assistant.

Figure 2:

Once the ServerManager page is up, then you just need to login with the administrator account you created during installation of ServiceManager. Keep in mind, if you do not know the userid or password, the consult the people who initally configured the ServiceManager.

After logging in, you are taken directly to the overview page. This page has a few items of importance for the Oracle GoldenGate environment. First you will notice on the left hand side of the page, is a context menu that provides a link to Overview and Diagnostics. This is basically a navigation menu that will be in every microservice (different per service). Then notice at the top of the page, a summary of processes that are running, stopped, or other Lastly, at the middle and bottom of the page, you see the services and deployments that are associated with the installation (Figure 3).

Figure 3:

If you want to know details of the ServiceManager and/or deployments, you can click on the associated deployment under Deployments. This will take you to a summary page, where you can identify items releated to the deployment and change the $OGG_HOME (more on this later).

As you can tell, we have made some great improvments to the Oracle GoldenGate product. Hopefully, this post has you excited to see what else is hiding in the product and my next couple of posts.

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

Events to round out 2017 … where I’ll be!

DBASolved - Mon, 2017-09-11 08:23

The first half of 2017 started off with me joining Oracle and getting the first looks at the new Microservices Architecture for Oracle GoldenGate. So where does this leave me for the second half of 2017?

As some of you may know, I’m currently working on a Oracle GoldenGate 12.3 roadshow; where I’m explaining the new features in 12.3 and giving attendees hands-on to the new product. After two stops on the roadshow, so far, we have seen so much excitement for this new architecture. It is going to change the way we, as a community, replicate data both on-premise, in the cloud, and in hybrid architecture.

Check out the new Microservice Architecture by downloading it from OTN (here).

As we continue the roadshow, we will be hitting a few more cities in the US before moving on to EMEA in November. Once we get to EMEA, there will be some cool places we plan on holding events and/or meeting customers. The excitement for Oracle GoldenGate 12.3 continues to grow as we attend more cities.

If you want to know the cities in the US, please see this blog post (here).

On top of the US and EMEA legs of the Oracle GoldenGate 12.3 roadshow, I will be attending two conferences in EMEA. I’m sure you can guess at which two … DOAG and UKOUG. This will my first time at both events and I hope I get to see a lot of great friends and make many new ones.

At DOAG, I will be speaking, on November 22, 2017 @ 08:00, about Oracle GoldenGate Peformance Tuning. Additionally, I will have another product manager in tow (hopefully) so attendees will have direct access to the PMs who are responsible for the core Oracle GoldenGate product.

At UKOUG, I will be speaking, need to confirm date, about the Conflict, Detection and Resolution (CDR) in Oracle GoldenGate 12.3 Both the manual process and the new Automatic CDR. Then if needed, by the UKOUG board, I may have another Oracle GoldenGate 12.3 presentation.

All in all, 2017 is rounding out to be a great first year at Oracle along with many first on top of that. Looking forward to seeing everyone on the roadshow!

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

fetch the latest total value

Tom Kyte - Sun, 2017-09-10 21:06
Hi, We want to fetch the latest total value for emp id = 2 but its summing total_value for all the rows for emp_id = 2. we want the result(total_value) only for the latest row of emp_id = 2. the statements and query are shown as below, cre...
Categories: DBA Blogs

Creation of Composite sub-partitions on multiple columns using template

Tom Kyte - Sun, 2017-09-10 21:06
Hi, Can we create Composite sub-partitions on multiple columns using template Example CREATE TABLE sgs.part_test ( order_date_time SYSTIMESTAMP, modulo_store_id NUMBER, recurring_flag CHAR (1) ) PARTITION BY RANGE (orde...
Categories: DBA Blogs

Why is there a limit on DBMS_OUTPUT?

Tom Kyte - Sun, 2017-09-10 21:06
I always run into an error that the dbms output exceeds 2000 and I can increase to max of 1000000. My question is, why is there a limit on this? I have gigs of space, why does oracle have to be so stingy with the output log?
Categories: DBA Blogs

Partner Webcast - Oracle Identity Management: Journey to the Cloud

From large organizations to small organizations, a journey to using more cloud-based applications is under way. Oracle extends its leadership in IAM by introducing the next generation...

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

Bind capturing

Tom Kyte - Sat, 2017-09-09 08:26
Dear Tom How can I find out if a SQL used bind variable or not? I know you can check v$sql and get a list of distinct SQLs after translating the constants in the predicates. But if it's using bind variable, shouldn't V$SQL_BIND_CAPTURE have that S...
Categories: DBA Blogs

Oracle Database Server Utility UTL_FILE

Tom Kyte - Sat, 2017-09-09 08:26
Dear Mr. Tom, Greetings!. In my NT Server, I want to generate files onto a Mapped network drive. You have already mailed me telling it's impossible on NTOS. But how to accomplish that task?.. Is there any oracle tool to generate files an...
Categories: DBA Blogs

Partner Webcast – Moving Oracle Workloads to the Cloud: Oracle Applications

When customers using E-Business Suite, JD Edwards, PeopleSoft or Siebel move their applications to the Oracle Cloud, they get not only greater enterprise scalability and agility, and cost and risk...

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

Python for the Oracle DBA – Outline/Intro

Bobby Durrett's DBA Blog - Fri, 2017-09-08 18:48

I want to put together a talk about how useful the Python programming language is for an Oracle database administrator or DBA. I thought that I would start by putting my thoughts down in a blog post as a possible starting point for a speech. I think the best way to organize this post is around a series of questions.

First off, why does an Oracle DBA need a programming language? We are not developers. DBAs do backup and recovery, performance tuning, installations, upgrades and patching, etc. We may write the occasional small script or program to help with our work but we are not full-time heads down 40 hours a week plus programmers. A lot of what I do uses simple SQL scripts that I run through Oracle’s SQL*Plus command line utility. A handful of these scripts include Oracle’s PL/SQL programming language so I have done some programming but it is not my full-time job. Our database servers have Unix and Linux shell scripts that do things like exports and trace file cleanups. In addition I have graphical tools like Toad or Oracle Enterprise Manager that I use in my job. I can do most of my job with SQL, PL/SQL, shell scripts, and GUI database tools. Why do a need a general purpose programming language like Python in my role as an Oracle DBA?

A language like Python helps me in my DBA job because it is easy to use and connects to a lot of things.

Why would a smart Oracle database administrator need a programming language that is easy to use? We are very technical people, are we not? I think DBAs need an easy to use programming language because they are not full-time developers. I don’t know about you, but I can only hold so many details in the front of my mind. I have a lot of Oracle database syntax and details rolling around in my head. I know how to join V$SESSION and V$PROCESS without looking it up. That’s not surprising after using Oracle for 20 plus years. But, I can only remember so much. Since programming is not my primary focus I do not think I can remember a language’s details very well. So, I think it makes sense for a DBA to have an easy to use programming language like Python. I can quickly look up details that I forget if it has been a while since I wrote a Python program, so I don’t need all the details in the front of my brain.

What do I mean when I say that Python connects to a lot of things? There are all kinds of libraries or modules that you can use with Python. One of Python’s claims to fame is that you can use it as the glue to tie varies components together. I use a graphics module that helps me make plots of Oracle performance metrics. I get the data using an Oracle database module. My SQL*Plus and Unix shell scripts did not let me connect to a client side graphics library. Plus, I use Python to connect to SQL*Plus and to ssh into Unix systems to run shell scripts. So, Python can connect to pretty much any type of system or resource that I could need in my job as a database administrator. On top of all that, cloud providers such as Amazon Web Services use Python. I have used Python to connect to AWS. Also, I have tested Python with the cloud based Snowflake database. I have also connected Python to a MySQL database. It connects to a lot of stuff! Contrast Python to PL/SQL. PL/SQL is great for Oracle database programming. But it doesn’t connect to other stuff very easily. You aren’t going to connect to Amazon Web Services or to a client side graphics library through PL/SQL. It is easy to connect to these things with Python.

So, to summarize my two points for why Python is a good programming language for Oracle DBAs :

  1. Python is easy to use and DBAs are not full-time programmers.
  2. Python connects to everything a DBA uses.

In my talk I want to go into more depth on each point. What makes Python easy to use? What are some things it connects to? It would be helpful for an audience to see evidence to support each point. I could include code examples or quotes from web sites.

Maybe to keep this post from being excessively long I can make this one an intro or outline and delve into the supporting material in follow-up posts. If anyone reading this has questions or criticisms of this material I would be happy to hear it. I’m presenting my own thoughts about Python’s usefulness in my job based on my experience. If other people have good reasons why Python is not so useful to an Oracle DBA or see problems with my reasoning I would be happy to hear your opinion.

Bobby

 

Categories: DBA Blogs

Need to generate the output in incremental loops

Tom Kyte - Fri, 2017-09-08 14:06
Hi Tom, I have a query which returns the below output. ONE TWO THREE I need to get the out as ONE ONE TWO ONE TWO THREE ONE TWO THREE FOUR i.e like incremental sets (by step 1). ONE ONE TWO ONE TWO THREE ONE ...
Categories: DBA Blogs

When will a datafile increment

Tom Kyte - Fri, 2017-09-08 14:06
Hi Team, I have checked the tablespace is autoextend on and i wanted to checked - Q.1 When it will grow ? Q.2 How much it will grow ? Q.3 Can we do anytime auotextend ON or Off in live production ? Will it impact on DB ? Q.4 If the size of au...
Categories: DBA Blogs

Dataguard setup

Tom Kyte - Fri, 2017-09-08 14:06
I have One PC and I also install VMWare on my pc. So I have oracle database 12c on my pc. I want to setup dataguard in my vmware workspace. Is it possible to setup dataguard environment from my pc to vmware pc also same in my pc.
Categories: DBA Blogs

CLOB field options in SQL Loader

Tom Kyte - Fri, 2017-09-08 14:06
Hi team, We are migrating DB2 Z/OS (Mainframe) to Oracle 12c R1. We are using UNLOAD utility in DB2 and SQL LOADER to load in Oracle end. We face issues in CLOB filed. A table has one CLOB field which are unloaded with dedicated text files for ea...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs