DBA Blogs

How can we skip existing values while inserting with sequence to id column(unique)

Tom Kyte - Wed, 2016-07-13 11:46
Hi Tom, Can you please let me know best possible way for below scenario to skip existing value while loading through sequence Table Structure: Tem_seq_check(id number(3) unique,name varchar2(5)); Tem_name(name varchar2(5)). I will insert...
Categories: DBA Blogs

Message queue

Tom Kyte - Wed, 2016-07-13 11:46
Hi, I have a situation where I was asked to use message queue technique, can you please suggest me whether it is possible/feasible or not. There are two data bases D1 & D2, upon changing the status of a particular field in D1 some data need to ...
Categories: DBA Blogs

get ORA-01031: insufficient privileges when execute procedure

Tom Kyte - Wed, 2016-07-13 11:46
Hello there: I met the "ORA-01031: insufficient privileges" error, when rebuild index with online option in a procedure. I know the role cannot be used in procedure, so grant some privileges to system(its DBA' user:system privilege: -- 2...
Categories: DBA Blogs

Analytics question

Tom Kyte - Wed, 2016-07-13 11:46
<code> I have a table from a 3rd party application that is used to track an order through the various manufacturing operations. A subset of the information looks like this: ORDER OPN STATION CLOSE_DATE ----- --- ------- ----------...
Categories: DBA Blogs

not able to re-create materialized view on prebuilt table

Tom Kyte - Wed, 2016-07-13 11:46
Steps I am trying to execute : <code> CREATE TABLE sample.MV(application_mode varchar2(25)); CREATE MATERIALIZED VIEW sample.MV ON PREBUILT TABLE REFRESH FORCE ON DEMAND AS SELECT application_mode FROM sample.tbl_name WHERE cnt > 0 ...
Categories: DBA Blogs

oracle lsitener config

Tom Kyte - Wed, 2016-07-13 11:46
I am trying to understand how the oracle listener gets its config info. I see this when I run lsnrctl status: Listener Parameter File /home/oracle/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /home/oracle/u0...
Categories: DBA Blogs

Archiving log buffer directly to archive logs

Tom Kyte - Wed, 2016-07-13 11:46
Is it possible to archive contents of log buffer directly to archive logs,instead of writing into redo logs. Will this decrease the load in system I/O. Please explain the reason if it is not possible.
Categories: DBA Blogs

Consuming MCS Custom API using SDK for Android

Recently experimenting with the latest Oracle Mobile Cloud Service SDK for Android I was pleasantly surprised how easy you can call MCS Custom API using SDK with just a few lines of code. In common...

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

List all caches in Oracle

Tom Kyte - Tue, 2016-07-12 17:26
Hi Tom, I would like to list all caches in Oracle. Are there any usefull SQL queries which can help me to see some usesull performance data from Oracle's caches? BR, Peter
Categories: DBA Blogs

CASE statement

Tom Kyte - Tue, 2016-07-12 17:26
Hello Gurus, Please correct me if am framing wrong CASE statement using multiple columns (CASE WHEN (ENA_PRE1 IS NULL AND ENA_RT1 IS NULL) THEN RT_UNE IS NULL ELSE RT_UNE END) Thank you in Advance.
Categories: DBA Blogs

How to allow 100% CPU?

Tom Kyte - Tue, 2016-07-12 17:26
Hi I am running Oracle Database 11g Release 11.2.0.1.0 (Standard Edition) - 64bit Production on Windows 2008 R2, on a physical machine with a 4 core CPU. Whatever I do, the maximum CPU utilization of oracle.exe process is 25%. This is annoying b...
Categories: DBA Blogs

Column view definition in data dictionary

Tom Kyte - Tue, 2016-07-12 17:26
Hello there, Is it possible to get view column definition from data dictionary? I mean something similar as for tables - user_tab_columns. Thanks, Dusan
Categories: DBA Blogs

SELECT column from TABLE3, UPDATE column in TABLE2, INSERT all column in TABLE1

Tom Kyte - Tue, 2016-07-12 17:26
Hi I need to solve some difficult logic process. create table aa ( id int, name_child varchar2(25) ); create table bb ( id int, name_master varchar2(25) ); insert into bb values('-1', 'DUMMY'); bb is the master table, aa is the child...
Categories: DBA Blogs

difference b/w row database and column database, how the data will compressed in database

Tom Kyte - Tue, 2016-07-12 17:26
Hi Tom, I have to questions i.e; 1.what is difference b/w row database and column database, how can we create indexes on column database? 2.how the data will be compressed in database(on what basis data will be compressed in database)?
Categories: DBA Blogs

Using BULK COLLECT

Tom Kyte - Tue, 2016-07-12 17:26
Hi Tom, I am running into an issue while using BULK COLLECT INTO in a stored procedure. I use a cursor to get data (42 million rows, 70 columns) from a remote database (a table in Teradata), then insert those records into a table in Oracle. After ...
Categories: DBA Blogs

TNS-00583: Valid node checking: unable to parse configuration parameters

Learn DB Concepts with me... - Tue, 2016-07-12 11:19
 TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking: unable to parse configuration parametersI  Received following errors when trying to startup my listener. I verified the listener.ora and sqlnet.ora files and everything seemed to look normal.
 
[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:05:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters



Listener failed to start. See the error message(s) above...

[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:07:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters



Listener failed to start. See the error message(s) above...


I had below line in my sqlnet.ora file.

[oracle@Linux03 admin]$ cat sqlnet.ora_bak
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)  
  

Solution (In my case):

1. Tried to remove the ENCRYPTION_WALLET_LOCATION info and it worked for me. I knew there was somthing wrong with the syntax.
2. Copied the content from ENCRYPTION_WALLET* into notepad ++ . It helped me realize that I was missing two closing parenthesis )) at end.
3. Added them at end. Started the listener and worked.


[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:31:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                12-JUL-2016 10:31:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


Also try these if above trick doesn't help.

1.  Oracle listener.ora or sqlnet.ora file contains any special characters.
2.  Oracle Listener.ora or sqlnet.ora file are in wrong format or syntax.
3.  Oracle listener.ora or sqlnet.ora file have some left justified parenthesis which are not accepted by oracle parser.
Categories: DBA Blogs

Eight Ways To Ensure Your Applications Are Enterprise-Ready

Pythian Group - Tue, 2016-07-12 10:00

When it comes to building database applications and solutions, developers, DBAs, engineers and architects have a lot of new and exciting tools and technologies to play with, especially with the Hadoop and NoSQL environments growing so rapidly.

While it’s easy to geek out about these cool and revolutionary new technologies, at some point in the development cycle you’ll need to stop to consider the real-world business implications of the application you’re proposing. After all, you’re bound to face some tough questions, like:

Why did you choose that particular database for our mission-critical application? Can your team provide 24/7 support for the app? Do you have a plan to train people on this new technology? Do we have the right hardware infrastructure to support the app’s deployment? How are you going to ensure there won’t be any bugs or security vulnerabilities?

If you don’t have a plan for navigating and anticipating these kinds of questions in advance, you’re likely to face difficulty getting approval for and implementing your application.

Any database applications or solutions you build or adopt for your organization must be “enterprise-ready”: secure, stable and scalable with a proven, tested capacity for deployment. They must be easy to administer and easy to support. But how do you make sure that happens?

Here are eight things to consider before declaring your proposed solution enterprise-ready:

  1. Open communications: A close working relationship between the development and operations teams goes a long way toward seamless integration of your database applications. By working together (from start to finish, as early on as possible), you can better anticipate the issues to be solved so your app or solution gets up and running faster.
  2. Platform reliability: Open source databases are great for obvious reasons: they’re free and easily customizable. But if your app is revenue-generating or mission-critical, it’s better to use a tested and proven distribution platform like Datastax Enterprise for Cassandra, Cloudera or HortonWorks for Hadoop, and Oracle or Percona for MySQL.
  3. Continuous quality: No matter which technology you use to build your app, make sure it passes rigorous quality assurance, scale and performance testing — both initially and with every new release of the software. Your vendor also needs to be proactive when it comes to releasing patches and fixing bugs.
  4. Suitable infrastructure: Consider whether the infrastructure you’re running is appropriate for the app you’re developing. If the database is set to run on multiple nodes of commodity hardware — to cover your bases in case one fails — but your operations team likes to store everything on an expensive SAN device, you might want to look into other alternatives.
  5. Experienced hosting: You’ll want to find a hosting company that is reliable, cost-effective and meets your company’s security policies. It should also have experience hosting the database technology you plan on using; that way, it knows how to respond when issues or challenges arise.
  6. Expert talent: Bring in a team of experts that can support your entire environment. While your operations team may want to take care of some elements themselves (everything up to the OS level, for instance), you’ll still want to show them that you have 24/7 support coverage available if needed. This team should be committed to continuous training and have enough people skilled with your technology to provide uninterrupted coverage.
  7. Comprehensive skills: Your team should be able to check your configurations against best practices for security, performance and availability — but don’t forget to ensure that they’re also set up for the more mundane things like systems monitoring, responding to alerts and fault finding.
  8. Ongoing costs: When tallying the running cost of your application, keep in mind that you need to incorporate the cost of the distributed version, its hosting, and 24/7 support and optimization.

With all the elements that go into getting an application enterprise-ready, it might be easier to work with a reputable partner who has the experience and expertise to help you deploy the right solution for your organization and ensure its long-term success.

Find out how Pythian’s solutions can help you succeed.

Categories: DBA Blogs

Links for 2016-07-11 [del.icio.us]

Categories: DBA Blogs

Decide Query performance

Tom Kyte - Mon, 2016-07-11 23:06
How we define which query is run slower? Means how we can say that query takes more time and require performance tuning?
Categories: DBA Blogs

find an error at online document

Tom Kyte - Mon, 2016-07-11 23:06
Hi I found a error at online document when I study the oracle. https://docs.oracle.com/database/121/CWADD/crsref.htm#CWADD91788 I do not where to submit this .So I write here, May it can help . crsctl config has Use the <b><i><code>crsctl ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs