DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: SYS,SYSTEM pwds change in enterprise

  1. #1
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    Question

    Hi,

    you guys can help me. I want to automate the passwords change implementation for Oracle super user on Unix, SYS and SYSTEM with a kinda parameter driven recycling procedure which can be run periodically...

    As we have around 114 databases... Its not easy to change manually/ by some specific standalone script... on every database...

    Is there any way I can ping to each and every database on the network and change the passwords of those users... Any guide lines or spec script.. to implement these process..

    Your help will be greatly appreciated...Thx

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    I suppose if you have one machine that can connect to all the databases, you can write a small script that does something like this.

    Have a small control file (assuming you want all the passwords to be the same), which just contains

    alter user sys identified by xxxx;
    alter user system identified by xxxx;

    then assuming you have a list of all the 114 sid's or connect strings, you can create a script which generates a list of commands

    sqlplus sys/password@db1 @control.sql
    sqlplus sys/password@db2 @control.sql

    Then just run that script.

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Yeah !

    Thats what I mean, I don't want my script to be static, and I should be changing frquently the parms like dbname etc.,as its little pain to hardcode the dbname, and dbnames will never stay constant as some of 'em like test,devt will always on fly. I wanted to generate list of dbnames from my network/names23 database. I don't have any object in names23 that gives me list of database names. I have a view named 'network' owned by system which gives me information about all service names in the network. Its not going to help me much as it include all aliases along with database names.

    Is thre anywork around to get only database names list from anywhere either from networking product/OEM product in the enterprise. We use Enterprse Manager for management of our databasea and names23 fro networking.

    Thx anyway...

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    Well what's in your network view? (since you said owned by system I assume it's a database view) You said that has all the aliases and service names.

    Why can't you spool that view to a file so each service name is on a line.

    Fetch each line of that file, parse it for whatever information you need, sid, service name, connect string, etc.

    Then spit out the 'sqlplus sys/password @sid' statements, then run that?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    dunno what do you mean change dbname but to generate this script is not very hard, just use dynamic sql

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    If you know the nodes where these databases live, then you could tap down the instance names from the "oratab" files on those specific nodes.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    The view network in names23 database has entries for sever name serving for the names and instances attached to it. View says instance it has also aliases not just sid`s. Its hard to spit out aliases... Creation/Delition of service names(aliases) is so common in this shop for somany reasons... Don't have better control. Its 6 members DBA group. Can't keep modifying my script every time I want to run, asking evrybody what aliases they have created from last time I ran that script and hard coding the aliases to be seperated from network view entries is real pain.

    Hope you understand here what I am saying...The bottom line here is I don't want to modify script with anything else except the password string to be changed. Nothing else, the sid names to connect database should be generated dynamically that moment, when I run the script.





    [QUOTE][i]Originally posted by pwoneill [/i]
    [B]Well what's in your network view? (since you said owned by system I assume it's a database view) You said that has all the aliases and service names.

    Why can't you spool that view to a file so each service name is on a line.

    Fetch each line of that file, parse it for whatever information you need, sid, service name, connect string, etc.

    Then spit out the 'sqlplus sys/password @sid' statements, then run that? [/B][/QUOTE]

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    What I mean is you won't have to edit the script if you get everything out of the view.

    An extreamly simple example is as follows.

    In your view you have 1 column, the sid. You're sitting at a machine that is able to connect to every database.

    you have a small control file that has

    alter user sys identified by xxxx;
    alter user system identified by xxxx;

    you can either edit this file every time you change the passwords, or just make the passwords accepted as arguments so you don't have to touch it again.

    then you have a sql script that does this, after setting vars to ignore normal sql output. this could accept the new old and new passwords as arguments so you don't have to hard code it.

    spool change_all_pass.sh
    select '#!/bin/sh' from dual;
    select 'sqlplus sys/oldpass@' || sid || ' @control.sql' from network_view;
    spool off

    you can make the oldpass an argument instead of hard coding it. this will create a small file with the following. you could add arguments if you didn't code the passwords in the control file.

    sqlplus sys/password@sid1 @control.sql
    sqlplus sys/password@sid2 @control.sql
    ...

    for every sid in the database. run this and if the machine can resolve each one just by the sid it will work fine. you can run it automatically from the sql script that generates it by using some "host" commands. if you need more than the sid use whatever information that's in your view to connect to every database and do it that way.

    unless you're leaving out some detail i don't see what the problem is.

  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Thanks advisor,

    I understand what you are saying ! I know the part you are talking about... See the network view contents.

    SQL> desc system.network
    Name Null? Type
    ------------------------------- -------- ----
    SERVER VARCHAR2(255)
    INSTANCE VARCHAR2(255)

    SQL> select distinct(instance) from system.network where server='NameServer_oraserv92.world' ;

    INSTANCE
    ----------------
    prd1
    dev2
    test
    --
    --
    stge

    189 rows selected.


    As I said before I have only 114 instances on our network, its reporting 189. That means rest of 'em are aliases(service names). MY Q is thre any way to get only sid names(114) from anywhere else with no aliases included. I have the idea of spitting out all the sid names to a flat file and grep 'em seperate 'em, to seperate 'em I need to hardcode the service names... to be excluded. Not a solution for the automated script thats in my mind to implement. This z what I was talking about in my prv. posting. So, I was asking is there any other oracle network products location from where I can get the sid names only. Thx for your inputs anyway.



    [Edited by sreddy on 12-20-2000 at 02:18 PM]

  10. #10
    Join Date
    Jun 2000
    Posts
    417
    ah ok, now i see what your problem is.

    you could try sam's oratab suggestion or, you could add a shell script/perl wrapper that checks each of the ID's and parses your net config files to see if they are a sid or alias.

    or you could just spit out all 189 into the sqlplus script. the worst that I can think of happening is that it either changes the passwords twice on some instances which does no harm, or the commands fail for the aliases because it can't connect by just the alias, but as long as the sid's for those aliases are also in the view the passwords will be changed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width