Data base user change

[podichin]

Hi All,

Presently we have a 2 node cluster , And we are running oracle data base package on the nodes,

All the instance are using same user (operating system) "oracle"and group "dba" , Am planning to use saperate user and and saperate group for each instance,

Please help me how can i do this, What are all the precautions and changes required in changing the user?,

Thanks in advance,

Siju

[Peter Godron]

Siju,
please read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#28

As long as your new user has the correct priviledges on the packages, you should be ok.

[Ben Dehner]

I'm not sure I would recommend this. While I can see that it gives some security and resource isoloation -- it's easy to see who is allocating IPC resources -- most gains are going to be nullified troubleshooting permissions problems.

First, you need to be very careful with file permissions for this to work. All of the directories for the datafiles, logfiles, archives, etc. have to be writable by the new userid. The same for all of the log/dump directory entries in the init.ora. The ones that I can think of are

background_dump_dest
user_dump_dest
core_dump_dest
audit_file_dest

Some files are by default created under ORACLE_HOME and need to live there. If you use a password file, it lives under ORACLE_HOME/dbs/orapwSID, and the instance userid needs write permissions to that file. Instance lock files (lkSID), RMAN controlfile snapshots (snapcf_SID.f) and pfiles and spfiles also go by default under here. Other types of dump and log files may be dumped under ORACLE_HOME as well. If you've missed something, it can be a pain to troubleshoot because the broken process doesn't have permissions to write its log file out.

When Oracle is installed, there is an OS group (typically "dba") that is used for SYSOPER administration, like starting up and shutting down. If you're per-instance userid are not members of this dba group, you'll need to explicitly provide the SYS password in all of the database startup/shutdown scripts. This also may include backup scripts, depending on the backup method. For a system restart, you'll either have to start the instances manually or write a script that can identify the particular instance with the userid for proper startup.

It probably can be done, but, again, I wouldn't recommend it. I'm interested to find out how it turns out.

[TwoProc]

Podi, as Peter has tried to genly point out, go back to your previous questions to the forum.

To answer your question:

The only tricky part to your question is changing the group (believe it or not), not changing the owner of the binaries and the data files. To change the owner - just change the owner of the binaries, and all of the data files (all types of data files, and all of the directories that they live in). HOWEVER, that new owner (let's say "neworacle" is the new oracle owner) MUST be a member of the *"dba"* group that was defined by answering a question during the install. If you are not using the same group as before, the fix follows below.

For your "oracle" rdbms binary to claimed shared memory, it must be (from its own code enforcement) a member of the group assigned to the "dba" function on the installation of the Oracle home. The answer to that question and the question of the operator group are created in the file $ORACLE_HOME/rdbms/lib/config.c. You can make the change and relink.

In the ORACLE_HOME/rdbms/lib/config.c file there is a #define SS_DBA_GRP and a #define SS_OPER_GRP which must be changed.

If you wanted the new dba group to be "dbanew" you would make your changes like:
#define SS_DBA_GRP "dbanew"

The process change for a new operator group "SS_OPWER_GRP" would be similar to above, and is pretty straightforward.


Then relink the whole $ORACLE_HOME to get new executables which can use the new group.

cd $ORACLE_HOME/bin
./relink all

Now, cd $ORACLE_HOME

chown -R oraclenew:dbanew .

Do the same for all of the database files,
control files, log files, etc.

You can now create a new database/or use the existing one under new user and group ownership as you would normally.