SYS,SYSTEM pwds change in enterprise

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

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.

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...

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?

dunno what do you mean change dbname but to generate this script is not very hard, just use dynamic sql

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

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:

---

Originally posted by pwoneill
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?

---

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.

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]

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.