Install Oracle Repository

Oracle Designer

You have just installed Oracle Designer and when you open it up, you receive an error about the repository - 'This user does not have an installed Repository. Invoke the Repository Administrator Utility'. It all seems a bit vague, and doesn't give you a clue about what you need to do to rectify the issue. What it boils down to is - you need to install a repository for Oracle before you can use the designer. I discovered this using trial and error, and also installed the repository with a bit of trial and error. Although this may not be the 'perfect' way to achieve this, it worked for me, and I am now able to use the Oracle Designer.

I am using Oracle 9i, but I'm sure the steps would be roughly the same in whichever version you are using. If a file is not where I say it is - do a bit of hunting on your system to see if it's in a different location for your version.

Pre-reqs

This tutorial assumes you have already installed Oracle, with the Oracle Developer Suite. You should be able to open up Oracle Designer, as well as open and log into SQL Plus. You should also be able to log in to the Repository Administration Utility (below).

What we need to do is to actually set up the repository for Oracle, and you can then set up a user's repository.

1. Check that you have the following files:

  1. ckclean.sql
  2. ckcreate.sql
  3. ckqa.sql
  4. ckreport.sql
  5. ckvalqa.sql

On my system these scripts are in: C:\oracle\oradev\REPADM61\ADMIN\ - have a look for a similar location on your system, or run a search for one of the files.

2. Open up and log in to SQL Plus.

From within SQL Plus, you will create install parameters by running the above SQL scripts, then install the repository.

Get the path to the ckqa.sql file, then paste it into SQL Plus, starting with the @ because you're running a file - and hit enter:

    @C:\oracle\oradev\REPADM61\ADMIN\ckqa.sql
    Paste this whole line (replace with the actual path to the file on your system) into SQL Plus
You will then get the following prompts:
    Enter TNS connect String for server:
    Enter TNS : Enter the name of the connect (host) string which you enter when you log into SQL Plus.
    Enter password for SYS ....
    Enter password: (this is the password for the Oracle sys user which was set up when Oracle was installed)
    Enter the Repository size you wish to create. Small(S),Medium(M),Large(L) ...
    Enter S/M/L : (I chose small. If you are using this for a very large setup, choose a larger size)


The following just need a name - I called them CGINDEXES etc, you could call them LateForDinner I guess:

    Enter datafile location and name for CONSTANT_GROW_INDEXES...
    Enter datafile : CGINDEXES
    Enter datafile location and name for CONSTANT_GROW_TABLES...
    Enter datafile : CGTABLES
    Enter datafile location and name for DEPENDENCY_INDEXES...
    Enter datafile : DINDEXES
    Enter datafile location and name for DEPENDENCY_TABLES...
    Enter datafile : DTABLES
    Enter datafile location and name for DIAGRAM_INDEXES...
    Enter datafile : DIAGINDEXES
    Enter datafile location and name for DIAGRAM_TABLES...
    Enter datafile : DIAGTABLES
    Enter datafile location and name for LOB_DATA...
    Enter datafile : LDATA
    Enter datafile location and name for RAPID_GROW_INDEXES...
    Enter datafile : RGINDEXES
    Enter datafile location and name for RAPID_GROW_TABLES...
    Enter datafile : RGTABLES
    Enter datafile location and name for SYSTEM_META_INDEXES...
    Enter datafile : SMINDEXES
    Enter datafile location and name for SYSTEM_META_TABLES...
    Enter datafile : SMTABLES
    Enter datafile location and name for TEMPORARY_INDEXES...
    Enter datafile : TINDEXES
    Enter datafile location and name for TEMPORARY_TABLES...
    Enter datafile : TTABLES
    Enter datafile location and name for VERSION_INDEXES...
    Enter datafile : VINDEXES
    Enter datafile location and name for VERSION_TABLES...
    Enter datafile : VTABLES
    Enter datafile location and name for REPOS_RBS tablespace...
    Enter datafile : REPRBS
    Enter datafile location and name for REPOS_TEMP tablespace...
    Enter datafile : REPTEMP
    For ten or more subordinate users, we recommend giving access using public synonyms.
    Do you wish to grant create/drop public synonym privilege to Repository Owner ?
    Enter Choice (Y/N) : Y

A bunch of sql will execute, and I received the following message (with error) at the end of it:

Hit enter to Continue with validation process ....

 

SP2-0310: unable to open file "ckvalqa.sql"

You can run this script by hand, which will run a bit of validation on your settings so far:

    @C:\oracle\oradev\REPADM61\ADMIN\ckvalqa.sql

You should receive no errors running this file, unless you made changes to ckparams.txt, in which case - fix what you messed up in the file, or run ckqa.sql again.

The create script, when run, will generate errors. It falls over on the creation of the REPOS_MANAGER user. So - before we run ckcreate.sql, create the user:
    create user REPOS_MANAGER identified by MANAGER;

Once the user is created, you can now run the ckcreate.sql script:

    @C:\oracle\oradev\REPADM61\ADMIN\ckcreate.sql

Open up the Repository Administration Utility

Go to: Start/All Programs/Oracle9i Developer Suite - OracleDevHome/Oracle9i Software Configuration Manager/Repository Administration Utility

Log into the Repository Administration Utility with the username, password and connection string of your Oracle user. You will notice down the bottom next to 'Repository Status' it says 'No Repository Present'.

If you click the 'Install' box, you will get a popup which leads you to believe you're on the right track. Click OK to the first screen, then you get a message about having the privilege to 'CREATE PUBLIC SYNONYM'. Click Yes to this, and a screen comes up with a bunch of dropdowns. I left all mine at 'SYSTEM', then clicked 'Start'. This is where you go to bed, because this takes a long, long time.



Once this is completed, click the 'Maintain Users' box in the Repository Administration Utility (under 'Repository Maintenance' section).Select 'Users', then click the box with the green '+' on the left. Select your normal Oracle user from the dropdown. Click 'OK'.

If you receive an error about 'admin option not granted for role...' (Oracle error ora-01932), run the following SQL command in SQL Plus:


    grant CKR_REPOS_MANAGER to REPOS_MANAGER with admin option;

Almost there!!

After you have done this, you should be able to add the user. Once you have added the user (you're almost there!!!) highlight the user, then click 'Reconcile' down near the bottom of the window. This will take a while, so let it sit for a bit doing its stuff once again. NOW you should be able to log into Oracle Designer with your user!

 

Creating Oracle Designer workareas

If you wish to be able to create workareas within the Oracle Designer but find the option is grayed out - log back into the Repository Administration Utility and go to 'Options' along the very top menu, and choose 'Enable version support'. Then click the 'Maintain Users' button. Expand the menu, and find your user. Right click on the user, and choose 'Properties'. Check the 'Workareas' box, under 'Allow Management Of'. This will allow your user to create their own workareas.