I wrote this to put forward some points when I found a test database on a live database instance(9i) and was asked to say why I wanted to move it. Not a question I answer all the time wondered what you all think.
Reduce Unexpected Issues with an Oracle Database
The best way to reduce unexpected issues with an Oracle database is to know exactly what effect changes to existing application code or database objects will have on a production environment. The only way to achieve this is to mimic the set of the production environment. However, the cost of setting up an environment, maintenance and version control can become out of scope.
Moreover, a solution is required to at least limit unexpected errors or to perform disaster recover/backup tests. Three databases are needed, development, test, and production. All these databases should live in their own separate database instances.
It is well known that development environments are unsafe testing areas due to the quality of the data and code used, moreover, the system is not version controlled. A correctly version controlled database is needed to test code against, with quality testing data. It is this database often referred to as a Quality Assurance Database which should be separate from the Production Database.
The following points support a separate testing instance.
• With similar objects being shared by one instance it is possible for code to unknowingly utilize an object in a live schema if the test object was missing. Although preventable using roles and privileges, oracle will not guarantee the correct object will be referenced even if the schema name is used as a prefix.
• As code has only been tested in a development environment there is no way to measure the impact the code would have in a test environment. In many test systems instances are regularly restarted due to the instance hanging caused by bugs in code, moreover with the advent of web services, XML etc, which will be used in the future, testing such application can cause server performance problems to test and live systems.
• Often Oracle will require updates to be applied to a production environment or there may be the need to install third-party or new oracle packages such as the XML package. These installs effect the live environment often affecting the sys and system schemas which are the core of the database. Disaster recovery is not testable against the current configuration; therefore, there is no way to test the impact of patches. Moreover if failures occur when installing patches in addition to backups not being tested, the only outcome will eventually be down time for the production environment although loss of data is very unlikely. A separate test system is commonsense in this situation.
• Oracle recovery functionality and architecture are based on an instance environmental structure, a database is an oracle instance, changes made to an object, file etc effect the oracle instance therefore, oracle manages the instance as a whole when considering backup recover. Take the following example: while testing a batch job it is discovered that the jobs has contaminated the core underlining test data being used in a critical test run for another process. The test database data is now useless, unless an export is available for the corrupted tables a full rebuild of the data is necessary to complete a pointing time recovery in a separate instance given the current configuration. If the test database was in a separate instance the batch job could have been rolled back instantly without risking production. Moreover, the batch job could have well updated the live database objects.
With the current configuration the test database is the production database and this is fundamentally wrong. No code should be moved to the production environment until it's been tested against the QA database and server. This environment should essentially represent Production+1: in other words, it should reflect exactly how the database will be configured when the next release of the application rolls out, or when the next Oracle database patch is applied. This allows for isolation of the root causes of problems with software or hardware; moreover it is best practice.