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]
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.
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
------------------------------- -------- ----
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.
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.
Reply With Quote
Bookmarks