OraFAQ Forum: Server Administration » user defined database paramters (eg environment variables)

Home » RDBMS Server » Server Administration » user defined database paramters (eg environment variables) (9i,10g,11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

user defined database paramters (eg environment variables) [message #461527]

Fri, 18 June 2010 09:45

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

HI all

I have a set of databases which are doing LDAP calls via DBMS_LDAP. Dev database, TI / QA / PROD as well, so four databases. Each one of these databases has a corresponding LDAP setup. Thus the DEV database calls a different LDAP from the QA database etc.

This causes a bit of difficulty because it means we have to modify code for each environment before it can run. The name of the LDAP server changes between environments which makes porting code more work. So the question has been raised, how can we make our databases know which LDAP server they are supposed to use. Here is what has been suggested:

1) use a database environment table and put an entry in it for LDAP_HOST, LDAP_PORT

2) use an external file and put and entry in it for LDAP_HOST, LDAP_PROT

3) use environment variables and have oracle read the environment variables. This begs the question how do these environment variables get set.

4) it was even suggested to look into defining user parameters in the init.ora file but I was not aware that this was possible. I would be an excellent solution from my perspective if so.

Here is what I think:

1) I would prefer #1 because it keeps everything in the database. However, I see problems when it comes time to do things like RE-INITIALIZE DEV by taking a copy from production. This table would be overlaid by the corrsponding production table which defeats the purpose of having the table. That means we have to know to put a DEV copy back in DEV and so on.

2) This sounds OK. As long as I can read the file at system startup to set a context variable, or as part of a job do same. But I am not who the file will be managed.

3) Not a fan of because I cannot figure out how I would make the same environment varaibles work from both my WINDOWS machine and from the database host machine (Unix).

4) don't think this is allowed.

So as you can see I am worried not about the solution itself, but about how the solution complicates matters like BACKUP/RECOVERY, and WORKING FROM WINDOWS VS. HOST OS, and maybe more stuff I have not considered yet.

What do you guys do?

Kevin

Report message to a moderator

Re: user defined database paramters (eg environment variables) [message #461529 is a reply to message #461527]

Fri, 18 June 2010 10:11

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I think I will recommend the following:

create table database_environment
(
    dbname varchar2(30) not null
  , variable_name varchar2(30) not null
  , value varchar2(30)
)
/

create or replace view vw_database_environment
as
select *
from database_environment
where dbname =  sys_context('USERENV', 'DB_NAME')
/

Now an application cannot get the wrong set of values unless it actually tries too and that will be hard to do if I never expose the actual table. Additionally the table can be populated the same way in all environments so restores are not a problem. Lastly, no going outside of oracle to get what we need.

Who has comments.

Kevin

Report message to a moderator

Re: user defined database paramters (eg environment variables) [message #461533 is a reply to message #461529]

Fri, 18 June 2010 11:13

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

One additional problem with 3) and 4) would probably be that the settings are only read at instance startup, so you would have to bounce the DB. So not good.

Option 1 is the best in my opinion. We also use a variable/value table for this kind of configuration, although we change the values with a script after we copy the DB to test or dev.

We also have some definitions that are system specific but not likely to change "on the fly" as constants in package variables.

Report message to a moderator

Previous Topic:	Big Datafile vs Multiple Small Datafiles
Next Topic:	oracle questionaire

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 19:25:56 CDT 2024