How to restore Clarity Oracle databases on Linux, Solaris, or AIX?

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

This guide will show you how to restore Clarity Oracle databases on Linux, Solaris, or AIX step-by-step.

Solution:

1. Download and install Putty or any other tool you can use to connect using SSH (example below):
   
   

   <Please see attached file for image>

   
   
   
2. Type in your Login information
   
   
3. Go into the directory where you want to create your directory and place the database dump file. To create a directory use the command "mkdir name_of_directory" and then move into that directory
   
   
4. If you are using FTP to get your datafile, here's are the steps that you can use (example has values that can be changed to your own):
   
   
   1. Type "ftp name_of_ftp_server", use your credentials to connect
      
      
   2. Type get name_of_file.dmp.gz (most of times those files will be compressed hence the extension .gz)
      
      
   3. You should see "Transfer complete"
      
      
   4. Exit FTP by typing quit
      
      
5. Decompress the file. E.g: gunzip name_of_file.dmp.gz
   
   
6. Create an Oracle user schema:
   
   
   1. Connect to SQL Plus or any other query tool (e.g: Toad, Aqua Data)
      
      
   2. Run the following script to create the Oracle user schema and commit the change (This can be done inside SQL Plus or any other query tool like Toad or Aqua Data, you will have to connect with a user that has the privilege of creating another user and granting the accesses described below, usually the user "system"):
      
      CREATE USER name_of_user
      IDENTIFIED BY password_for_the_user
      DEFAULT TABLESPACE USERS_LARGE
      TEMPORARY TABLESPACE TEMP;
      GRANT CONNECT,
      RESOURCE,
      CREATE SESSION,
      UNLIMITED TABLESPACE,
      QUERY REWRITE,
      CREATE ANY TABLE,
      CREATE ANY TRIGGER,
      CREATE ANY INDEX,
      CREATE ANY VIEW,
      ALTER ANY TABLE
      TO name_of_user;
      COMMIT;
      
      
   3. Restore the restore datafile using the "imp" command (considering you used the tool called "exp" to export your database, if it was data pump then the commands will be different):
      
      
   4. imp buffer=256000 ignore=y grants=n statistics=none log=/directory_to_place_log/import.log touser=name_of_user_created_on_step_above fromuser=name_of_user_used_to_export_database file=/directory_where_datafile_is/name_of_file.dmp
      
      
7. Once complete check import.log for errors. A typical Oracle restore generates a lot of errors. However all of those errors are after the last xdm table is imported. Errors before that need to be addressed. If you have a table that failed to import because the tablespace didn't exist you will want to create that tablespace and execute the following command:
   
   
   1. imp buffer=256000 ignore=y grants=n statistics=none log=/directory_to_place_log/import.log touser=name_of_user_created_on_step_above fromuser=name_of_user_used_to_export_database file=/directory_where_datafile_is/name_of_file.dmp tables=tables_that_were_missed, table2_that_was_missed. If there are multiple tables that were missed, it is best to drop the user and start over

*****NOTE: If your command are not working sometimes it's because you are not using C-shell. Please type "csh" and try again*****

Environment

Release: ESPCLA99000-12.1-Clarity-Extended Support Plus
Component: