Oracle coding techi and exhancement guide, optimization

Dear all,

right now  looking for URL/Links/book on tuning Oracle query and architecture the way to optimized Oracle application, any idea in terms of data modelling and code optimization ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
For PL/SQL

I recommend Steven Feuerstein's writings - PL/SQL Programming and numerous web articles and videos.
Also,  "Oracle PL/SQL Performance Tuning TIps & Techniques" by Rosenblum and Dorsey

For Oracle architecture and general application construction everything by Tom Kyte
0
marrowyungSenior Technical architecture (Data)Author Commented:
alex,

I mean the way to architect a good oracle application from the Oracle DBA point of view. not only index.


first link doesn't works.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
yeah I heard about asK tom and the link you shown me is about how to architecture the Oracle application as fast as possible ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
BTW, do you know how to make sure that the oracle has turn on the basic audit feature to log failure login attempt ?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
The base line would be:
audit create session whenever not successful;

Open in new window


This should be well documented within the official sites:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams017.htm#REFRN10006

Other (re)sources:
http://www.oracle-scripts.net/tracking-failed-logon-attempts/
https://hoopercharles.wordpress.com/2012/04/04/failed-logon-attempts/
http://www.dba-oracle.com/t_tracking_counting_failed_logon_signon_attempts.htm
http://lindaoracledb.blogspot.de/2013/02/how-to-audit-failed-logon-attempts.html
http://roqet.org/audit_oracle_logins.html
0
sdstuberCommented:
for Tom Kyte  books  - any of them.

The most recent is, I think, Expert Oracle Database Architecture
0
marrowyungSenior Technical architecture (Data)Author Commented:
Alexander Eßer,

when I setting this up and I  login using sqlplus, I see this error: TNS:protocol adapter error.

any reason for that?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
How do you login -> OS user, as sysdba etc...?!
0
marrowyungSenior Technical architecture (Data)Author Commented:
sqlplus SYS/<pw> as sysdba
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Please try "sqlplus / as sysdba" from OS level...
0
marrowyungSenior Technical architecture (Data)Author Commented:
it gives the same error message.

basically I forget what will the system assume is we only type / ?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
basically I forget what will the system assume is we only type / ?
http://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1007520
http://oracle-base.com/articles/misc/os-authentication.php

What about the listener?!
lsnrctl status

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
LSNRCTL for 64-bit Windows: Version 10.2.0.3.0 - Production on 05-JUN-2015 10:44
:24

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_MAIN_DB
Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.3.0 - Produ
ction
Start Date                17-FEB-2015 17:25:22
Uptime                    107 days 16 hr. 19 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\oracle\product\10.2.0\mainDB\network\admin\listener
.ora
Listener Log File         E:\oracle\product\10.2.0\mainDB\network\log\listener_m
ain_db.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=1523)))

Open in new window


it seems ok, right?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Once authenticated by the operating system, users can connect to Oracle more conveniently, without specifying a user name or password. For example, an operating-system-authenticated user can invoke SQL*Plus and skip the user name and password prompts by entering the following:"

tks.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
it seems ok, right?
looks "weird" somehow...

Can you "tnsping <your_db>"?!
What about your env settings, like ORACLE_HOME?!

You're running the DB on Windows, right?! Check all Oracle related services!

Btw: Are you sure, the database is up and running at all?!

other resources:
http://dba.stackexchange.com/questions/68939/error-ora-12560-when-trying-to-connect-windows-sqlplus-client-to-oracle-11g-lin
https://community.oracle.com/thread/2425280
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:431775600346873836
0
marrowyungSenior Technical architecture (Data)Author Commented:
"You're running the DB on Windows, right?! Check all Oracle related services! "

ok.

"Btw: Are you sure, the database is up and running at all?!"

Yeah, this make me come here and ask ! it runs everyday and SQL developer can connect to it. SQL developer can even fetch performance data from it and post it to the performance dashboard!

tnsping :

C:\Documents and Settings\bggg>tnsping xxx

TNS Ping Utility for 64-bit Windows: Version 10.2.0.3.0 - Production on 05-JUN-2
015 11:51:26

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:
E:\oracle\product\10.2.0\mainDB\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.yyy.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE
_NAME = aaa.yyy.com)))
OK (110 msec)

Open in new window



it means it can, right?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
And the error you mentioned above is raised on your client or do you get this even on the Windows machine where the db resides?!
0
slightwv (䄆 Netminder) Commented:
This question is getting WAY off topic.

That said:
sqlplus SYS/<pw> as sysdba

Needs to be executed from the database server since it doesn't have a database alias.
To execute this the OS user needs to be a member of the ORA_DBA group.

If you want to try from a remote client:
sqlplus SYS/<pw>@SOMETNSALIAS as sysdba

But the database needs to allow you do connect as SYS remotely.  I would stick to trying it from the database server itself.

The tnsping only talks to the listener.  It really doesn't help much here.  The database can be down and tnsping can still be successful.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
quite nice explanation, slightwv, but what about the "TNS:protocol adapter error"??!! haven't said anything about that ;-)
Thus, it really doesn't help much here either...
0
slightwv (䄆 Netminder) Commented:
>>but what about the "TNS:protocol adapter error"

99.9 % of the time this is because the OracleService<SID> isn't running.  

I think I saw something the other day that this might also be caused by the OS User not being in the ORA_DBA group.
0
marrowyungSenior Technical architecture (Data)Author Commented:
alex:

"And the error you mentioned above is raised on your client or do you get this even on the Windows machine where the db resides?!"

on the machine where the DB resides! the problem is, it works before when I try to turn on the archive log mode. weeks later, it doesn't anymore !

slightwv,

"To execute this the OS user needs to be a member of the ORA_DBA group."

this is good too ! but I am using SYS to login, the OS user right should doesn't matter, right?

"99.9 % of the time this is because the OracleService<SID> isn't running.  
"

I can tell you that the instance is really still running everyday,

"I think I saw something the other day that this might also be caused by the OS User not being in the ORA_DBA group."

tks anyway.

I thank you both and I agree that I have to stop that right now.
0
marrowyungSenior Technical architecture (Data)Author Commented:
sdstuber,

"for Tom Kyte  books"

is it the one who own the  Ask Tom site ?  and he is Thomas Kyte and you are talking about that book:  http://www.amazon.com/gp/product/1430262982/ref=ox_sc_sfl_title_1?ie=UTF8&psc=1&smid=ATVPDKIKX0DER ?
0
sdstuberCommented:
yes  asktom.oracle.com
 and yes  that's the author of that book I was suggesting
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks very much ! do you know MS SQL server buy the way ? I want to about MS SQL expert architecture too as I am a MS SQL administrator, any formal book from experts on this !

I have a lot of data modelling, data warehousing and tuning but no one focus on architecture !
0
marrowyungSenior Technical architecture (Data)Author Commented:
sorry missed this question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.