Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Kako kreirati Oracle Designer Repository?

Oracle Designer je CASE razvojni alat i dolazi u sklopu Oracle Developer Suite.

Nakon što ste uspješno obavili dva prethodna koraka (instalacija Oracle XE baze i Developer Suite), možete nastaviti sa kreiranjem Repositorya za Oracle Designer.

Oracle Designer Repository: Instalacija pomoću SQL skripta

Kompletan rezultat možete pogledati u ovoj datoteci: install_oracle_designer_repository.txt .

1. Otvorite prozor sa komandnom linijom: Start -> Run… -> cmd -> OK

2. Podesite Oracle Home za Developer Suite:

C:\>set ORACLE_HOME=C:\DevSuiteHome
C:\>cd C:\DevSuiteHome
C:\DevSuiteHome>cd repadm61\ADMIN

3. Sa SQL*Plus spojite se na bazu kao SYS korisnik:

C:\DevSuiteHome\repadm61\ADMIN>sqlplus sys@xe as sysdba

SQL*Plus: Release 10.1.0.4.2 - Production on So Mrz 9 11:53:43 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Kennwort eingeben:

Verbunden mit:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

4. Potrebno je pokrenuti SQL skript ckqa i unijeti tražene vrijednosti:

Enter TNS connect string for server

Enter password for SYS

Enter the repository size you wish
to create...

Dropping temporary table CKRAU_TEMP

Enter datafile location and name for
tablespace

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?

Hit enter to Continue with validation
process...

Please verify the above values. Edit
'ckparams.txt' to make any changes.
Then run ckparams.txt.

SQL> @ckqa
Enter TNS connect String for server
Enter TNS : XE
Enter password for SYS ....'
Kennwort eingeben:
Connect durchgef³hrt.

Enter the Repository size you wish to create. Small(S),Medium(M),Large(L) ...
Enter S/M/L : S
Dropping temporary table CKRAU_TEMP

drop table CKRAU_TEMP
           *
FEHLER in Zeile 1:
ORA-00942: Tabelle oder View nicht vorhanden

Creating temporary table CKRAU_TEMP

Tabelle wurde angelegt.

Enter datafile location and name for CONSTANT_GROW_INDEXES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\constant_grow_indexes.dbf

Enter datafile location and name for CONSTANT_GROW_TABLES...
Enter datafile : C:ORACLEXE\ORADATA\XE\constant_grow_tables.dbf

Enter datafile location and name for DEPENDENCY_INDEXES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\dependency_indexes.dbf

Enter datafile location and name for DEPENDENCY_TABLES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\dependency_tables.dbf

Enter datafile location and name for DIAGRAM_INDEXES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\diagram_indexes.dbf

Enter datafile location and name for DIAGRAM_TABLES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\diagram_tables.dbf

Enter datafile location and name for LOB_DATA...
Enter datafile : C:\ORACLEXE\ORADATA\XE\lob_data.dbf

Enter datafile location and name for RAPID_GROW_INDEXES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\rapid_grow_indexes.dbf

Enter datafile location and name for RAPID_GROW_TABLES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\rapid_grow_tables.dbf

Enter datafile location and name for SYSTEM_META_INDEXES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\system_meta_indexes.dbf

Enter datafile location and name for SYSTEM_META_TABLES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\system_meta_tables.dbf

Enter datafile location and name for TEMPORARY_INDEXES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\temporary_indexes.dbf

Enter datafile location and name for TEMPORARY_TABLES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\temporary_tables.dbf

Enter datafile location and name for VERSION_INDEXES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\version_indexes.dbf

Enter datafile location and name for VERSION_TABLES...
Enter datafile : C:\ORACLEXE\ORADATA\XE\version_tables.dbf

Enter datafile location and name for REPOS_RBS tablespace...
Enter datafile : C:\ORACLEXE\ORADATA\XE\repos_rbs.dbf

Enter datafile location and name for REPOS_TEMP tablespace...
Enter datafile : C:\ORACLEXE\ORADATA\XE\repos_temp.dbf

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
...
"  **********************************************************
* "
"  *           Please verify the above values.
* "
"  *        Edit 'ckparams.txt' to make any changes. Then run ckparams.txt
* "
"  **********************************************************
* "

5. Nakon toga treba pokrenuti novokreirani skript ckparams.txt:

SQL>@ckparams.txt

6. Pošto nama treba podrška za naše znakove, a koristimo i Oracle bazu sa Unicode podrškom, moramo umjesto ckcreate pokrenuti skript ckcreate_mb:

SQL>@ckcreate_mb

Dropping role CK_ORACLE_REPOS_OWNER if it already exists
DROP ROLE CK_ORACLE_REPOS_OWNER
          *
FEHLER in Zeile 1:
ORA-01919: Rolle 'CK_ORACLE_REPOS_OWNER' nicht vorhanden

Creating role CK_ORACLE_REPOS_OWNER

Rolle wurde angelegt.

Granting privileges to CK_ORACLE_REPOS_OWNER

Benutzerzugriff (Grant) wurde erteilt.

Granting EXECUTE privilege on STANDARD to CK_ORACLE_REPOS_OWNER

Benutzerzugriff (Grant) wurde erteilt.

************************************************
*         Creating database Objects            *
************************************************

Creating tablespaces ..............
...
End of Grant privilege on database objects to REPOS_MANAGER

End of 'Create database Objects'  process
************************************************

7. Na kraju možemo pomoću skripta ckreport generisati izvještaj o kreiranim objektima u bazi tokom instalacije (izvještaj je snimljen u datoteku CKREPORT.LST u istom direktoriju gdje i SQL skript):

SQL> @ckreport
Connect durchgef³hrt.
****************************************************
Report on the missing database objects
****************************************************
...
The following Roles were granted to REPOS_MANAGER
------------------------------------------------

CK_ORACLE_REPOS_OWNER
CONNECT
RESOURCE

8. Promijenite šifru/lozinku za REPOS_MANAGER korisnika, da ne bude standardna MANAGER:

SQL> alter user repos_manager identified by r3p0man;
Benutzer wurde geõndert.

9. Dodijelite EXECUTE privilegiju nad paketom UTL_FILE korisniku REPOS_MANAGER, kako ne bi došlo do problema prilikom instalacije (više o dotičnom problemu kasnije):

SQL> grant execute on utl_file to repos_manager;
Benutzerzugriff (Grant) wurde erteilt.

Repository Administration Utility

10. Pokrenite Repository Administration Utility: Start -> All Programs -> Oracle Developer Suite – DevSuiteHome -> Designer -> Repository Administration Utility

11. Unesite šifru/lozinku za korisnika REPOS_MANAGER i odgovarajući konektor za bazu. “OK”.

12. Kada se otvori panel za administraciju, kliknite na “Check requirements” i provjerite, da li su sva podešavanja i privilegije ispravni.

13. Ako jesu, kliknite na “Install” kako biste započeli sa instalacijom

14. Odaberite opcije za instalaciju

15. Omogućite kreiranje PUBLIC sinonima

16. Nastavite instalaciju bez podrške za poboljšavanje performansi

17. Dodijelite odgovarajuće Tablespaces pripadajućim objektima

18. Nastavite sa instalacijom

19. Instalacija je u toku

20. Ukoliko se ne pojavi nijedna greška, instalacija bi trebala biti gotova nakon 20-30 minuta

To bi bilo to. Oracle Designer Repository je spreman za korištenje.

Troubleshooting

Da se osvrnem na mogući problem spomenut u koraku 9. Ukoliko korisniku REPOS_MANAGER ne dodijelimo privilegiju EXECUTE nad paketom UTL_FILE, doći će do greške prilikom instalacije:
ORA-04603: package body “REPOS_MANAGER.JR_REG_IO” has errors
ORA-06508: PL/SQL: could not find program unit being called: “REPOS_MANAGER.JR_REG_IO”

Ukoliko zaboravite dodijeliti tu privilegiju prije početka instalacije, pokušajte taj problem ovako riješiti (napomena: NE zatvarajte instalacijski prozor!!!):

1. Otvorite prozor sa komandnom linijom (Start -> Run … -> cmd) i pokrenite sqlplus:

C:\DevSuiteHome\repadm61\ADMIN>sqlplus sys@xe as sysdba

SQL*Plus: Release 10.1.0.4.2 - Production on So Mrz 9 11:53:43 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Kennwort eingeben:

Verbunden mit:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

2. Otkucajte slijedeće naredbe:

SQL> grant execute on utl_file to repos_manager;

Benutzerzugriff (Grant) wurde erteilt.

SQL> conn repos_manager/r3p0man@xe
Connect durchgef³hrt.

SQL> alter package jr_reg_io compile body;

Paketrumpf wurde geõndert.

3. Nakon toga u instalacijskom prozoru kliknite na zeleno “Retry” dugme i sačekajte da se instalacija završi.

Ugodan rad sa Oracle Designerom!