Answer

The Situation
Two systems have been identified, the first of which is designated System A (the system with current install of SA 4.x using Oracle SYSTEM tablespace. The second system will be designated System B and does not currently have SA installed (but does have Oracle and WAS installed). On System B, we will setup Site Analyzer to use a user-defined tablespace instead of SYSTEM. The goal is to migrate the existing data from System A to System B with as little manual intervention as possible. All pre-reqs (WAS and Oracle) for SA are assumed to be already installed on System B. Three databases are assumed to have been used on System A, and we’ll use the same on System B (one each for admin, DNS and projects).

Note: Performing this process requires working knowledge and skill of Oracle at the level of a DBA.

Steps

System A:

1. Identify all of your SA users defined for Oracle. Remember that in Oracle, each schema is associated with an Oracle user.
a. SA – your administration schema user. This one must be SA.
b. SA – your DNS schema user. This one must be SA.
c. SA1001 etc. – your project schema users. There will be one for each project defined, and the user name will usually match the schema name (you can find these schema names by using the SA GUI (user interface) and selecting edit for each project). By default, these users are named SA1001, SA10xx, and so on, but if you have already created this by hand, the user names could be anything – just check the GUI to be sure.

2. Identify all of your databases in use by SA. There can be from one to three actual databases in use. For example, the DNS and Admin schemas can be in a single database, or the project schema(s) and DNS can be in a single database. For the purposes of this document, we’ll assume that there are three separate databases, one for Admin, one for DNS and one for the project schemas.

3. EXPORT each database. This is done three times (assuming you have three separate databases) using the Oracle EXP command. For example, to get command line help on EXP, just might type exp help=y.

/u01/app/oracle/product/x.x>exp help=y

Now, to use EXP, you need an Oracle user with a rich set of privileges so that it can export data from multiple schemas. This user must have the CREATE SESSION privilege on the Oracle databases, and the EXP_FULL_DATABASE role enabled if this user is not also the owner of the tables. This will be the user running the export sessions.

The command to export will be something like:

exp userid=username/password
buffer=20975120
owner=userlist
file=exportfilename
indexes=y
grants=n
rows=y
log=logfilename

userid: This is the user running the export.
buffer: memory (in bytes) for work area for export session. Example above is 20 million bytes. This should be at least 1 million bytes.
owner: This is the SA user (for admin or DNS), or a list of project users. For example, this might be owner=(SA1001, SA1024, SA1040). Warning: using owner with parentheses in unix might not work if you are using ksh or bash.
file: This is the export file name – the file that will be moved to the new system for later import.
indexes: specify y to export the SQL statements necessary to recreate the indexes upon import.
grants: specify n to not export the SQL grant statements.
rows: specify y to export the data in the schema (not just the data definitions).
log: This is the name of a file to receive any export messages. This will be very useful should any errors occur.

Once you have run the EXP command for each of the three Site Analyzer databases, the work on the original system is done.

4. Copy the three export files to the target system, System B. To improve performance of your import of System B, you may want to place these export files on a different physical disk from the databases on System B.
5. On System B, you may need to edit these export files to change any reference to the SYSTEM tablespace to your user defined tablespace name. In some instances, we’ve found that the import may fail when importing LOBs.

System B:

1. You may want to increase your DB_CACHE_SIZE (DB_BLOCK_BUFFERS in Oracle 8.1.7) in the init$SID.ora file. You may also want to increase your LOG_BUFFER. Changing these settings will require a restart of Oracle.
2. Create the three Site Analyzer databases.
3. Create three (3) user defined tablespaces, one for each database.
4. Create all of your Oracle SA users on System B. Remember that in Oracle, each schema is associated with an Oracle user. To make sure that your user defined tablespace is used for these new users, revoke the “UNLIMITED TABLESPACE” privilege for each user, and revoke the user’s quota from the tablespace from where the data was exported (this would have been SYSTEM tablespace). This will force import to create the tables in the user defined tablespace. Also make sure that the temporary tablespace setting for all SA users is also set to the user defined tablespace.
a. SA – your administration schema user. This one must be SA. Minimal roles and privileges are: RESOURCE, CONNECT and CREATE TABLE. Make sure that the default tablespace and temporary tablespace are both set to your user defined tables space.
b. SA – your DNS schea user. This one must be SA. Minimal roles and privileges are: RESOURCE, CONNECT and CREATE TABLE. Make sure that the default tablespace and temporary tablespace are both set to your user defined tables space.
c. SA1001 etc. – your project schemas users. There will be one for each project defined, and the user name will match the schema. Minimal roles and privileges are: RESOURCE, CONNECT, CREATE TABLE and CREATE VIEW. Make sure that the default tablespace and temporary tablespace are both set to your user defined tables space.

5. IMPORT each database. This is done three times (assuming you have three separate databases) using the Oracle IMP command. For example, to get command line help on IMP, just might type imp help=y.

/u01/app/oracle/product/x.x>imp help=y

Now, to use IMP, you need an Oracle user with a rich set of privileges so that it can import data to multiple schemas. This use must have the CREATE SESSION privilege on the Oracle databases, and the IMP_FULL_DATABASE role enabled if this user is not also the owner of the tables. This will be the user running the import sessions.

The command to import will be something like:

imp userid=username/password
buffer=20975120
touser=owner
fromuser=owner
file=importfilename
indexes=y
grants=n
rows=y
commit=y
log=logfilename

userid: This is the user running the import.
buffer: memory (in bytes) for work area for import session. Example above is 20 million bytes. This should be at least 1 million bytes.
touser/fromuserr: This is the SA user (for admin or DNS), or a list of project users. For example, this might be touser=(SA1001, SA1024, SA1040). Warning: using owner with parentheses in unix might not work if you are using ksh or bash.
file: This is the import file name – the file that was exported from the old system.
indexes: specify y to import the SQL statements necessary to recreate the indexes.
grants: specify n to NOT import the SQL grant statements. We don’t want the grants from System A copied over to this new system.
rows: specify y to import the data in the schema (not just the data definitions).
commit: specify y to help insure that the import session runs successfully to completion. This is especially true if the database to import is very large (for example, greater than 100MB).
log: This is the name of a file to receive any import messages. This will be very useful should any errors occur.

NOTE: You may need to edit the export files (prior to import) to change any reference to the SYSTEM tablespace to your user defined tablespace name. In some instances, we’ve found that the import may fail when importing LOBs.

Once you have run the IMP command for each of the three Site Analyzer databases, the work on the new system is done, and Site Analyzer should be for install.

6. If using oci8 driver, catalog all three databases.
7. Install Site Analyzer on System B. If you are using the thin driver, you will need to edit each project in the Site Analyzer GUI and change the hostname for the database URL to match System B (ignore the warning Site Analyzer gives you here about changing the hostname).