I am not a fan of having multiple Oracle databases on the same server and will try to avoid it. As Paul B stated, it requires additional resources for each database added onto the server. Additionally, our task was consolidate into 1 database to minimize licensing costs, free up hardware for other uses, and minimize database maintenance.

To give an update, the 6 databases were successfully consolidated into a single database with 6 schemas/tablespaces back in November 2011. The users are still testing and have reported no technical/performance issues yet (related to the consolidated infrastracture). Actually, one issue we notified users prior to testing was related to merging database user accounts.

We support 2 types of database users, "regular" oracle users and "OS" authenticated users. The "regular" Oracle users are sometimes shared between multiple employees and are slowly being phased out, whereas the OS authenticated user is associated to a single employee.

The issue was with the "regular" Oracle users. For example, suppose user = User1 existed in Plant1 and Plant2 and the user had different permissions and were also associated to different employees (Employee1 used this acccount to login at Plant1, whereas Employee2 also used same account to login at Plant2). When consolidating into single database, I could not simply create User1 with same permission from Plant1 and Plant2 because an Employee would then be able to view data in another schema which they should not access to. Luckily, there were only a few of these types of accounts (around 20 or so). What we decided was to keep User1 from one of the plants and create a new user account for the other and notify the effected employee of their new user account.

Other issues in consolidation were data related changes. We had to update/insert some data changes into various tables to reflect the new server/database (i.e. dba_directories and other table data reference changes).

The downtime should be minimal because I created scripts to do the entire process. Once users give the OK, the script will be run on new production server which will import all the plants data, create necessary schemas/tablespaces, consolidate and merge current Oracle users/roles from the multiple production server to be replaced, etc.