Can multiple Oracle databases share a single parameter file?

Posted on

Question :

I’m a developer working on an application team that supports a system with one production database and 4 test databases (2 Dev, a SIT and UAT environment).

The prod database exists on it’s own Unix server and the 4 test databases all live on a different Unix server. For various reasons, over the years these 6 databases have become quite out sync with regards to their Initialization Parameters. A quick comparison of the V$Parameter table reveals a number of key setting differences that have been causing our application team problems and need to be corrected.

I’m working with our DBA team now to try and correct them but it is a large Enterprise and the process is painful. I’d love to find a way to keep these parameters better in line for those databases that share the same server.

Is there a way to have multiple databases share a parameter file? I reviewed some documentation and saw some references to doing this for a RAC setup but these databases aren’t clustered but rather just copies of one another. I know certain parameters like SID and db_name would need to be unique but I was wondering if common settings like global_names and resource_limit could be centralized somehow?

Note I’m currently using 11G but we plan to upgrade to 12 within a month.

Answer :

No, you would have to use separate parameter files/spfiles for each database. As you’ve pointed out, you’ll need some settings to differ (locations of files, database name, etc.). In many environments, you’d want to adjust memory and other performance settings differently because the development environments often don’t have nearly as much RAM as the production environment.

Even if you could share a parameter file, though, you wouldn’t want to. Changing a parameter should involve some amount of development and testing just like changing code. So you would want to make that change in dev, validate it, and promote it up just like a code change. If you were sharing a parameter file, you’d have to make the change in all the environments simultaneously which somewhat defeats the purpose of having multiple environments. If you make a change in dev that doesn’t work out, you need to back that out just like you would back out a code change.

You could certainly take a backup of the production parameter file and copy that down with whatever changes are necessary to the lower environments. You could use whatever mechanism you use today to manage code deploys (I’m assuming some sort of source control system and build process) to version those parameter files and to deploy them so that you know what the differences are and why (i.e. dev uses a lower memory_target in general, dev has global_names set to true because you are testing that out for project X but that hasn’t been promoted to production yet).

Generally we use a different spfile for every database. Even though the spfile is binary, you can use the strings command to extract out the settings in the spfile. and you can also compare spfiles from one database to another to look for differences. It seems to me that the underlying issue is a change management issue. You should have a configuration that works and make every database use the same configuration.

Leave a Reply

Your email address will not be published. Required fields are marked *