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..
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.
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]
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.
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
------------------------------- -------- ----
SQL> select distinct(instance) from system.network where server='NameServer_oraserv92.world' ;
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.
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.