0

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.

Improve this question
2
  • 1
    short answer, no
    – Raj
    Commented May 25, 2016 at 17:45
  • No. Sort your business processes and change control out
    – Philᵀᴹ
    Commented May 26, 2016 at 9:42

2 Answers 2

Reset to default
2

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).

Improve this answer
1
  • Thanks Justin... my understanding of what parameters can be maintained in these files and what are able to be extracted is limited. I feared as much. Good point too about change management and the propagation of lower environment files to production. I hadn't considered what we might lose with centralization.. but the inability to test new parameters would be very limiting.
    – user87970
    Commented May 25, 2016 at 18:24
0

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.

Improve this answer
2
  • Yeah, I think you hit the nail on the head... these files aren't being properly maintained and config changes are being made on occasion without proper oversight. Unfortunately my team doesn't own or have visibility to these files. We only see the outcome when we run into development issues.
    – user87970
    Commented May 25, 2016 at 18:21
  • I backup the spfiles for each database as a part of my rman backup. I could use the backup to compare the database parameters from one point in time to another. Maybe you can use the audit function to track when the spfile changes.
    – Gandolf989
    Commented May 25, 2016 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.