Database Instances | Guerrilla Oracle: The Succinct Windows Perspective

All shops have more than one database, unless all of the development and testing are done off-site. Normally you will have perhaps three or four "instances" of your database. You may have a simple, generic database that is a baseline for your system. This may be the original database product you purchased or created, and usually this copy is not modified. Then you will have an instance for your technical staff to work with; in fact you will probably have several such instances. Third, your users will have their own copy for acceptance testing, and finally, you will have a production instance.

Now remember that you will also be responsible for maintaining all upgrades from all your vendors Oracle included. Sometimes you will need additional database instances to test these upgrades. So it is not uncommon for a shop to have more than four instances of a database at any one time. As DBA you will be the one who has to keep track of themwhat state they're in, who has which rights to each one, and so forth. Make sure you keep good notes.

Fortunately, creating an instance is straightforward. We'll use import/export as an example.

Create the database, giving it your instance name , such as TEST or PREPROD.
Shut down the database you want to copy and bring it up in restricted mode. (This is why so many DBAs work weekends!)
Export the database.
Bring the original database back online.
Import the backup file to the new database.
There will be some errors as the system tries to create files that already exist. These are system files, so ignore the errors.
Check the logs for meaningful errors.
Test the new instance and release it for use.
Make sure you have a backup plan, especially if this is going to be a developmental database, or if it is going to require hours and hours of upgrades.

Warning

There is something called a refresh of a test instance. A refresh is useful when the test data is so out-of-date that it is no longer relevant. You can import just certain files, but most of the time refresh is an all-or-nothing proposition. Just remember to warn your customers that when you run the IMPORT utility just to bring the test database current, they will lose any data that they have entered because it will be overlaid with the database that you are importing. So if someone has favorite test cases out there, they will lose them and have to start over.