joi, 25 aprilie 2013

How to exactly match the name of a database listener service

The services of a listener are not given by default, the same with the database name. And, more so, they can be a lot, created subsequently of the database and the first listener creation. So, sometimes, we should address that service we want to use in order to connect to a certain schema and we can't figure out the precise name of the service. Useless to say that in this situation you cannot connect to that given schema by any chance. So, what do you have to do is:


host1 >lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-APR-2013 11:13:22

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:9367 refused:0 state:ready
         LOCAL SERVER
Service "mydb.infra" has 1 instance(s).
  Instance "mydb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:25443 refused:0 state:ready
         LOCAL SERVER
Service "mydbXDB.infra" has 1 instance(s).
  Instance "mydb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host1, pid: 21623>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host1.infra)(PORT=50186))
Service "db.infra" has 1 instance(s).
  Instance "db1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2021 refused:0 state:ready
         LOCAL SERVER
Service "dbXDB.infra" has 1 instance(s).
  Instance "db", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host1, pid: 25637>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host1.infra)(PORT=24653))
Service "db_DGB.infra" has 1 instance(s).
  Instance "db1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2021 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
<oracle@host1:~


Notice that the listener above is by some complexity. It services two databases, db and mydb, the two databases are clustered, the two instances, db_name and db_name1 are registered, the XDB service, meant to ensure the connection to the APEX features (htmldb part of the database) it appears as well in the ecuation.
So if we want to use, let's say the mydb database, we will conect like this:

vi tnsnames.ora


# tnsnames.ora Network Configuration File: \oracle\32\11.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

mydb=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=host-cluster.infra)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=mydb.infra)
    )
  )

Notice that the host is not found from the lsnrctl command from above, is a scan name, 11g database specific, but it doesn't matter for the purpose here.

miercuri, 24 aprilie 2013

How to find the Oracle AS ports

Sometimes, we can find in a situation when are supposed to manage some servers that are not belonging to our aria of administration. You're familiar with this type of situation: anxious and searching addresses and users and schemas and ports and so on. Shortly, this post is about not knowing the Oracle AS ports.
Solution:

cd $ORACLE_HOME/install
vi portlist.ini

Now you'll see a value or a range of values for the Oracle HTTP Server Listen Port parameter. This is all you should know.
Be aware, you can be easely seduced by another parameter: Oracle HTTP Server Port, the more so as is anterior of the above one. If you will use this one IN ORDER TO RUN ORACLE DEPLOYED APPLICATION you'll get the error:

FRM-92050 failed to connect to Server /forms/servlet -1

That's because this port is meant only to server Apache purposes, not OC4J.

vineri, 19 aprilie 2013

Lock held by in-doubt distributed transaction n.n.nnnn


The title is self explanatory and I'll get quick to the solution:

select LOCAL_TRAN_ID,GLOBAL_ORACLE_ID,state,RECO_TIME ,top_db_user from PENDING_TRANS$;

Normally, you'll have to see the n.n.nnnn number under the local_tran column

rollback force 'n.n.nnnn';    (replace n.n.nnnn)

Copy/paste from/to MyMachine/VMWareESXGuest

I can't imagine what the VMWare developer who "forgot" to beatify us  with copy/paste superhuman power between local machine and esx infrastructure can have in mind. Especially this issue is maintained over a lot of versions past.
I know you can find the solution maybe in about 10000 places over the internet, but, from my point of view, this issue is so annoying, that i wish to have the solution on every single web site on the net. Of course, not quite on every one, if you understand, but almost like.
Solution:


Select the virtual machine and click the Summary tab.
Click Edit Settings.
Navigate to Options > Advanced > General and click Configuration Parameters.
Click Add Row and type the following values in the Name and Value columns:
isolation.tools.copy.disable – false
isolation.tools.paste.disable – false


I know, the above text is not about Oracle or Linux, but, you're agree with me, there is no dba nowadays not using virtualisation.





Prerequisites: Forms 9.0. Matter: where's dissapeared the StringComparator.class from f90all.jar!


I don't know exactly all the java versions having this problem related to Forms 9.0, but i am pretty certain that java 6 is. 
The thing is that nasty class is in another archive, share.jar, the one who can be founded under $ORACLE_HOME/jlib
I recommend you to work clean, something like this:

mkdir ~/test
cd ~/test
mkdir share
mkdir f90all

copy the two archives under its everyone folder

cd share
cp $ORACLE_HOME/forms90/java/share.jar
jar -xvf share.jar
cd ../f90all
cp $ORACLE_HOME/forms90/java/f90all.jar
jar -xvf f90all.jar

(i assume that your java bin executables are traceable by  PATH)

find the /oracle/bali/share/sort/StringComparator.class  file under share director and copy to the same named place under f90all folder.

cp ../share//oracle/bali/share/sort/StringComparator.class ../f90all///oracle/bali/share/sort/

Now u have to repackage again your upgraded class. First, be aware, mind the f90all.jar file, not to be arround, because the archiver might include it either in the archive. Also, delete the folder META-INF prior to repackage.

cd ~/test/f90all
jar -cvf  ~/test/f90all.jar *

And now all you have to do is signing the whole thing:

$ORACLE_HOME/forms90/webutil/sign_webutil.sh ~/test/f90all.jar
cp ~/test/f90all.jar $ORACLE_HOME/forms90/java

Et voila! The error vanished!

ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []


Of course, you know the 00600 fateful error has  tons of posible  faces. And of course, you know, 9 dba colleagues out of 10 will urge you to the famous Metalink. I won't debate here about Metalink, i've never made a single SR. Just downloaded some updates or patches. One of the 000600 erros, the one having 19004 as argument can be easely overcome like this:

begin 
dbms_stats.DELETE_SCHEMA_STATS('username');
end;

Too easy? Sometimes, time is more than money.

joi, 18 aprilie 2013

Keep it simple! Schedule a job in no time!

Very often, the Oracle DBAs are in the middle of tons of tasks, running out of time, craving for speedy answers for their problems. One of the big time eaters issues related to db admining is the confusing syntax of scheduling a job. Shortly, use the following plsql code and leave the theory for those who are entitled to do it.
First, let's create a table just for test purposes, keep in  mind you don't  necessarily  have to do it.


create table table_of_moments (moment date);

BEGIN
--DBMS_SCHEDULER.drop_job('del_benef_10_job');
--DBMS_SCHEDULER.drop_program('del_benef_10_prg');
--DBMS_SCHEDULER.drop_schedule('del_benef_10');

/* note that the above code (the 3 commented lines) has to be uncommented when you will, eventually, run for the second or more than second time*/

  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'test_schedule',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',

/*well, here let's discuss just a bit about the posibilities, i am sure the needs of scheduling various times and intervals are really infinite:
START_DATE could be, of course, SYSDATE or SYSDATE+1 for a day past, or +1/24 for an hour past, or +1/24/120 for 30 seconds past, choose what it fits your needs.
repeat_interval could be:
FREQ: DAILY, HOURLY, MINUTELY, SECONDELY, i don't know if can be anything else
INTERVAL: this parameter is pretty straight forward, you can imagine by yourselves how can you use it
The syntax for these two fields is the key of scheduling and is enormously rich but, remember, the actual scope here is to schedule a job quick and move on, to another tasks
*/

    end_date        => NULL,
    comments        => 'This schedule test will trigger every 30 seconds from NOW .');

DBMS_SCHEDULER.create_program (
    program_name   => 'test_program',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN 
                                   insert into table_of_moments values (sysdate)
                           commit; END;',
    enabled        => TRUE,
    comments       => 'This program will fill the test table with the exact value of moment time the program was triggered .');

Dbms_scheduler.create_job
(Job_name => 'test_job',
Program_name => 'test_program',
Schedule_name => 'test_schedule',
Enabled => TRUE,
Comments => 'This job will make the things working .');

END;
/

Now, let's check the things are working:

select * from table_of_moments; --repeat this command how many times you                                          
                                --like

These being said, i remind you that the ways of scheduling a job are so various than you can study days in order to encompass them entirely. You can use dbms_scheduler or dbms_post and you can call many methods to submit or schedule a job. The present tip from this post is just about speed of doing right things.

miercuri, 17 aprilie 2013

I hate annoying "U'v got n more days before your password will expire"

I am sure 75% of you are not so interested in keeping the passwords fresh and sneaking-from-behind-careless, thus it's a very good idea to change all the parameters defining the lifespan of a user password. I am sure u agree with me that the most annoying expiration of any oracle password in this world, the never disputed champion of unlikeability is expiration of the DBSNMP user password.
As far as I could understand from the entirely oracle web community, the general way of overcoming this problem is to make a new profile and to assign any user you want to that profile. From my point of view this is not a perfect approach. First of all, maybe I don't know which the users are, until I won't query the dba tables, and maybe i won't eighter, after. Second, maybe two users can't share the same profile, in some scenarios. My proposal here is to treat equally all the users, and run a script careless of any information from dba_users  and dba_profiles views, a script that will modify all the profiles that are in your database. If the fact having the same passwords related profiles parameters for all the profiles is not a weakness for you, my aproach will be what you were searching for. So, the major advantage is provided by the tiny amount of time involved in the action, also the zero amount of implication in knowing any information about any user or profile in database. Check out the script:



declare
nume_profil  dba_profiles.profile%type;

nume_user dba_users.username%type;
begin
for nume_profil in (select distinct profile from dba_users)
loop
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit failed_login_attempts UNLIMITED';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_life_time  UNLIMITED';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_grace_time unlimited';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_lock_time unlimited';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_reuse_max unlimited';
    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_reuse_time unlimited'; 
end loop;

/*for nume_user in (select username from DBA_USERS where default_tablespace not in ('SYSTEM','SYSAUX') and instr(username,'$')=0 and instr(account_status,'EXPIRED')<>0) -- avoid system users and the ones whose password is not expired
loop
    execute immediate 'ALTER user '||nume_user.username||' identified by '||nume_user.username;
end loop;*/ 

for nume_user in (select username from DBA_USERS where default_tablespace not in ('SYSTEM','SYSAUX') and instr(username,'$')=0) -- avoid system users
loop
    execute immediate 'ALTER user '||nume_user.username||' account unlock';

end loop;
end;
/  

and BINGO, problem solved effortless and careless. Notice the third loop, which is useful by unlocking any user that is IN THIS MOMENT locked. Unfortunately, it's not a very good ideea to change, also, the EXPIRED state of the account, because you can alter accounts of other people, or critical accounts, used in some applications, etc. Every user of the database should change the password of their managed accounts, following the situation that  after the script will be executed, some accounts will be unlocked for ever but expired. Anyway, if you want to rip the account from the expired state and give it a password identically with the username, you can uncomment the loop above, the only one commented. But, i repeat, 90% it will prove to be a silly thing.
Check out the DISTINCT keyword, it's not dramatically important, but is there in honor of performance.
Don't forget to check once again, and make this a generale rule of your dba way of thinking: Check allways twice!!

select * from DBA_PROFILES order by profile,resource_name;

and carefully observe the results (ALL the profiles should have the appropriate parameters)!

Just for the record, let's enumerate the parameters:

  • failed_login_attempts - the number of failed login attempts before locking the account. It defaults to 10 times.

  • password_grace_time - the grace period after the password_life_time limit is exceeded. After that, your account will be EXPIRED & LOCKED. It defaults to unlimited.
     
  • password_life_time - how long an existing password is valid. 180 days is the default value.
     
  • password_lock_time – how long to lock the account after the failed login attempts is met. Don't mention its value and you will end having 1 day of relax, looking for the next login attempt to pe considered. Of course, this was a joke, the problem is not YOU, the dba, but the regular users, and they may not be happy waiting for the database to take them into consideration.
     
  • password_reuse_max – the number of times that you can reuse a password. Default = unlimited.
     
  • password_reuse_time – a time limit before a previous password can be reused. Default = unlimited.


Well, let's just remember that you've got one more parameter in this category, PASSWORD_VERIFY_FUNCTION, but, don't forget, we're talking here about some kind of security relaxation, so having an enforced function in order to treat the login attempts is not a goal for us right now.


miercuri, 3 aprilie 2013

I want a user copy of a user

Well, now let's practice something little bit more tricky and not very well documented.
As DBA, you wonder, pretty often, how to create a user different as name and schema from some existent user, but the same as all of the other characteristics and parameters of a user. This is it, your new user will connect using the same password, will use the same tablespace and will share the very same grants and rights as his original ancestor.

The next command will do the trick:



select dbms_metadata.get_ddl( 'USER', 'SCOTT' ) from dual
   UNION ALL
   select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SCOTT' ) from dual
   UNION ALL
   select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SCOTT' ) from dual
   UNION ALL
  select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SCOTT' ) from dual;

Of course, you have to specify your own desired username as a function parameter up there.

I warn you about something that can lead to an error: if your user hasn't got a granted resource, included in any of above categories (it doesn't have granted, let's say,  an object), an error of bellow form will be thrown:


ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
31608. 00000 -  "specified object of type %s not found"
*Cause:    The specified object was not found in the database.
*Action:   Correct the object specification and try the call again.

And let's see the correct answer (actually, in one of my databases, the user scott really wasn't granted with any right to any object, just has got some system grants and roles):


   CREATE USER "SCOTT" IDENTIFIED BY VALUES tigger
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP""
"
  GRANT UNLIMITED TABLESPACE TO "SCOTT""
"
   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT""


Of course, the result must be a little bit edited (some leading and trailing quotation marks should be deleted and semicolons added at any end of different command. So, the final code should be look like this:

CREATE USER "newuser" IDENTIFIED BY VALUES password
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
GRANT UNLIMITED TABLESPACE TO "newuser";
GRANT "CONNECT" TO "newuser";
GRANT "RESOURCE" TO "newuser";



Where are those dblinks?


This is the first post here, so i won't reveal any dark secret. Most of you are, sometimes, asking yourselves about what db links the database already has, created and available. If you cannot use any GUI client (toad, oracle sql developer, etc) and have sys rights you can:

select * from dba_db_links;

and the result will be pretty straight forward.

Here it is the structure of this dba view:


Name     Null     Type           
-------- -------- -------------- 
OWNER    NOT NULL VARCHAR2(30)   
DB_LINK  NOT NULL VARCHAR2(128)  
USERNAME          VARCHAR2(30)   
HOST              VARCHAR2(2000) 
CREATED  NOT NULL DATE 

Legend:

owner=the user who owns the dblink (if it is public, it goes for everyone)
db_link=the actual name of it
username=the user you under will you connect in the specified database
host=hostname (ip), port and SID (service name) of the linked database
created