Kreiranje Oracle database 12c sa komandne linije na MS Windows platformi

Pošto je moguće da na jednoj mašini bude instalirano više tipova i verzija Oracle softvera prepručljivo je postaviti vrednost ORACLE_HOME. Kako je moguće da na jednoj mašini bude instaliranoviše Oracle instance Oracle softver koristi ORACLE_SID (System Identifier). U prilogu je kreiranje container baze glob koja sadrzi samo pluggable bazu PDB$SEED.

set ORACLE_SID=GLOB

echo %ORACLE_SID%

glob

set ORACLE_HOME=C:\app\OracleHomeUser1\product\12.1.0\dbhome_1

echo %ORACLE_HOME%

C:\app\OracleHomeUser1\product\12.1.0\dbhome_1

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Kreiranje Oracle Servisa

Na MS Windows platformi svaka instanca mora da ima po jedan servis.

Podignut servis je uslov za podizanje i kreiranje baze.

Program za kreiranje servisa je oradim i nalazi u %ORACLE_HOME%/bin direktorijumu.

Objašnjenja svih parametara dobija se komandom oradim:

D:\oracle\product\10.2.0\db_1>oradim

ORADIM: <command> [options]. Refer to manual.

Enter one of the following command:

Create an instance by specifying the following options:

-NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]

[-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]

[-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]

Edit an instance by specifying the following options:

-EDIT -SID sid | -ASMSID sid [-SYSPWD pass]

[-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]

[-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]

Delete instances by specifying the following options:

-DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc

Startup services and instance by specifying the following options:

-STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]

[-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]

Shutdown service and instance by specifying the following options:

-SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]

[-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]

Query for help by specifying the following parameters: -? | -h | -help

Kreiranje inicijalizacione parameter datoteke

Pre nego što startujemo instance treba pripremiti inicijalizacione parametre.

U nastavku teksta sledi minimalni set parametara koji je naveden u testnom primeru:

Kreirati pfile C:\app\OracleHomeUser1\product\12.1.0\dbhome_1\database\pfileglob.ora

*.audit_file_dest='C:\app\OracleHomeUser1\admin\glob\adump'

*.audit_trail='db'

*.control_files='C:\app\OracleHomeUser1\oradata\glob\control01.ctl','C:\app\OracleHomeUser1\oradata\glob\control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='glob'

*.diagnostic_dest='C:\app\OracleHomeUser1'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=globXDB)'

*.enable_pluggable_database=true

*.local_listener='LISTENER_ORCL'

*.memory_target=1225m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.core_dump_dest='C:\app\OracleHomeUser1\diag\rdbms\glob\glob\cdump'

*.DB_CREATE_FILE_DEST='C:\app\OracleHomeUser1\oradata\glob'

Priprema direktorijuma za bazu

Dakle pre početka rada treba pripremiti direktorijume

Proveriti postojanje svih direktorijuma.

Kreranje database servisa

Servis instance se kreira komandom:

C:\Windows\system32>oradim -new -sid %ORACLE_SID% -intpwd elcaro -startmode M

Enter password for Oracle service user:Welcome1

Instance created.

(Welcome1 je password Windows user-a)

Verifikacija servisa vrši se komandom

start->run->services.msc

Kreirani su servisi OracleServiceGLOB i OracleJobSchedulerGLOB.

Kreirana je i pasvord datoteka:

Directory of C:\app\OracleHomeUser1\product\12.1.0\dbhome_1\database

17/01/2016 16:26 7,680 PWDGLOB.ORA

1 File(s) 7,680 bytes

Konačno proba startovanje instance

Kada je inicijalizaciona datoteka pripremljena i postavljena na odgovarajuće mesto treba probati start instance.

C:\app\OracleHomeUser1\product\12.1.0\dbhome_1\BIN>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 17 17:00:20 2016

SQL> connect sys/dataglob as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='C:\app\OracleHomeUser1\product\12.1.0\dbhome_1\database\PFILEGLOB.ORA';

ORACLE instance started.

Total System Global Area 1291845632 bytes

Fixed Size 3045264 bytes

Variable Size 855640176 bytes

Database Buffers 419430400 bytes

Redo Buffers 13729792 bytes

Kreiranje baze podataka

Vrši se komandom koja sledi ili sličnom:

SQL> CREATE DATABASE glob

USER SYS IDENTIFIED BY elcaro

USER SYSTEM IDENTIFIED BY elcaro

EXTENT MANAGEMENT LOCAL

DEFAULT TABLESPACE users

DEFAULT TEMPORARY TABLESPACE temp

UNDO TABLESPACE undotbs1

ENABLE PLUGGABLE DATABASE

SEED

SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYSAUX DATAFILES SIZE 100M;

Ukoliko komanda dojavi grešku bice u datoteci alert.log

(tj. Alert_%ORACLE_SID%.log) u direktorijumu C:\app\OracleHomeUser1\product\12.1.0\dbhome_1\RDBMS\trace datoteka alert_glob.log.

U istom direktorijumu se nalaze i trace datoteke.

Kreiranje sistemskih objekata

Startovati skripta kao korisnik sys:

%ORACLE_HOME%/rdbms/admin/catalog.sql %ORACLE_HOME%/rdbms/admin/catproc.sql

SQL*Plus prosleđuje parameter ORACLE_HOME koristeći alias (?). Dakle skripta se mogu startovati i na sledeći način:

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

catalog.sql kreira rečnik podataka (data dictionary).

catproc.sql kreira proceduralne strukture potrebne za PL/SQL.

Kao korisnik system startovati pupbld.sql skript.

SQL> connect system/elcaro

SQL> @?/sqlplus/admin/pupbld

Podešavanje sistemske parameter datoteke

SQL> create spfile from pfile='C:\app\OracleHomeUser1\product\12.1.0\dbhome_1\database\PFILEGLOB.ORA';

Probati restart baze.

Podešavanje SQL*Net datoteke tnsnames.ora

U direktorijumu %ORACLE_HOME%\network\admin pronaći datoteku tnsnames.ora

i dodati novu konekciju npr.

glob =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = glob)

)

Promena sistemskih pasvorda baze

Po potrebi promeniti pasvorde i uraditi backup.

Napomena* Imati u vidu da je inicijalna konekcija

sqlplus sys/elcaro as sysdba

bila ostvarena korišćenjem pasvord datoteke.

Konekcije:

sqlplus sys/elcaro@glob as sysdba

sqlplus system/elcaro@glob

su TCP/IP konekcije sa korisničkim pasvordima koje treba promeniti npr.

SQL> alter user sys identified by dataglob;

SQL> alter user system identified by dataglob;

Isto uraditi u pasvord datoteci.