PW Oracle Backup and Restore (Part 1)

I've never found there to be a simple way of backing up and restoring ProjectWise databases in Oracle. Usually it can be achieved using a combination of the IMP/EXP tools and/or dropping schemas or tablespaces. One of the main obstacles is that importing a database dump into an existing schema does not overwrite the old version of the schema with the new version. A further problem is that these methods invariably require DBA rights to the Oracle database.

A recent request by one client required that I achieve this management of the ProjectWise schema with the minimum DBA privileges. Also, I needed to be able to regularly transfer the ProjectWise database from a development to a Production database instance. This seems like a simple task; however it isn't quite so simplistic. It would be nice to have the easy-to-use SQL Server backup and restore functionality or the even uncomplicated detach/attach database tools.

This all may seem like simple fair to you Oracle gurus out there, but to some of us mere ProjectWise consultants this is all a veritable black art.

So I have finally cracked a simple way of carrying out these tasks without requiring a System login. All of the following examples are run from SQLPLUS in the Windows Command line.

So I will cover the following:
1. Create the ProjectWise Tablespace (Needs SYSTEM or SYS account)
2. Create the ProjectWise User (Needs an account with Grant rights to SYS packages - A lot easier if you do this as the SYS account)

That's all standard stuff. Then I'll show you how to do the interesting bits!
3. Export (Dump) the ProjectWise schema.
4. Empty the ProjectWise schema, without dropping the user or tablespace.
5. Import the ProjectWise schema from the dump file.

And all without a DBA account and from the command line!

Let's assume that we have a ProjectWise Integration Server all properly installed and configured, and an Oracle 10g database correctly installed with the UTF8 language settings; and that the Oracle Net Service and ODBC connections are configured, tested and working.

I'll be using the following details:
Oracle Database Instance & Service = PROJECTWISE
Install Path = C:\Oracle\product\10.2.0\ProjectWise\
Script & Backup Files Path = C:\OraBackup
System User [Password] = SYS [password]
ProjectWise DB User = PWISEV8i [password]

Create the creation scripts
First, we'll create a tablespace called PWISEV8I. To do this, start up your favourite text editor (Mine's NotePAD++), and create a script file with the following:

This will create a tablespace called PWISEV8i, with an initial size of 250MB, and an auto-increment size of 10MB. Save the file to the script directory as follows:

Now we'll create a script file for the ProjectWise user, also called PWISEV8I:

This user is set to use the tablespace we created as its default tablespace. The TRIGGER and TYPE grants are in addition to the standard grants required for ProjectWise V8i. This is because in my example these are needed to create and manage a number of customised modules.

Save the file to the following location:

C:\OraBackup\Create_PWISEV8I_User.sql

Note: I have created and attached these and further scripts for your use. Please feel free to rename with the correct extension (.sql or .cmd), edit and use as applicable.

Create the ProjectWise Tablespace and User in Oracle

The initial user must be created by a DBA user that has grant privileges on SYS objects. So to create the Tablespace and User I log in as the SYS user. To do this, on the database server, start a COMMAND window, change the directory to the Script path, and log in as the SYS user using the following:

sqlplus sys/password@projectwise as sysdba

where projectwise defines the Oracle service. The SYS user must log in a SYSDBA.

Then run the saved script files, ensuring you create the tablespace before you create the user.
To do this type;

START Create_PWISEV8I_Tablespace.sql;

and when that's finished type:

START Create_PWISEV8I_User.sql;

and that's it.

You could of course simply cut and paste the scripts directly into the command window without saving and running script files, but I like to keep a record of what I've done.

ProjectWise Schema Backup (EXPORT)

Having created the tablespace and user in Oracle, let's assume we've now created the ProjectWise datasource and it's all working beautifully (of course!).

To backup the database, or to migrate it from one server to another, we use the Oracle EXP tool. This is also run from a Command line prompt, but again I find it simpler to save the command in a CMD file and run this. Create a script file with the following; making sure it's all on one line (NO Carriage Returns):

Save the file as follows:

C:\OraBackup\Export_PWISEV8I_Schema.cmd

To run this file, double-click it. A command window will open up and show the export. This information will also be written the log file defined in the script.

In the following part, I will show you how to clear out and restore a ProjectWise schema without needing to drop the user or tablespace, and without needing DBA rights, over and above those already assigned to the ProjectWise user.

JP