12c Migracija baze podataka sa datotečnog sustava na ASM premještanjem datoteka na živo

Wednesday, 18.02.2015 – msutic

Postoji mnogo članaka gdje je objašnjeno na koji način migrirati bazu podataka sa datotečnog sustava na ASM. Moglo bi se RMAN-om kopirati datoteke baze podataka na ASM pa početi aktivno koristiti kopirane datoteke, povući datoteke iz sigurnosnih kopija i vratiti na ASM ili kreirati duplikat baze podataka na ASM-u.

Sve je ovo moguće odraditi na Oracle verzijama starijim od 12c.

U ovom postu bi se htio osvrnuti na malo drugačiji pristup – odraditi premještanje datoteka na živo u ASM.

Promjene imena i premještanje živih datoteka je moguće tek od verzije 12c što nam omogućava promjenu ili premještanje datoteka dok je baza otvorena i korisnici pristupaju podacima na datotekama. Time nam je pojednostavljeno upravljanje datotekama i izbjegavanje zastoja u radu baze čak i kad premještamo SYSTEM i UNDO tablespace.

 

Ovo je 12.1 utična baza podataka sa jednom instancom koju želim migrirati sa datotečnog sustava na ASM:

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u01/app/oracle12/oradata/cdb12c/system01.dbf
2    260      PDB$SEED:SYSTEM      NO      /u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf
3    740      SYSAUX               NO      /u01/app/oracle12/oradata/cdb12c/sysaux01.dbf
4    585      PDB$SEED:SYSAUX      NO      /u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf
5    740      UNDOTBS1             YES     /u01/app/oracle12/oradata/cdb12c/undotbs01.dbf
6    5        USERS                NO      /u01/app/oracle12/oradata/cdb12c/users01.dbf
7    270      PDB:SYSTEM           NO      /u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf
8    605      PDB:SYSAUX           NO      /u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf
9    5        PDB:USERS            NO      /u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf
10   50       PDB:MARKO            NO      /u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 100         /u01/app/oracle12/oradata/cdb12c/temp01.dbf
2    62       PDB$SEED:TEMP        32767       /u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf
3    100      PDB:TEMP             100         /u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf

 

Kreirati skriptu za premještanje datoteka:

$ sqlplus -s / as sysdba
set lines 200
set pages 50
set feed off
set head off
spool /tmp/move_dbfiles.sql
select 'ALTER DATABASE MOVE DATAFILE '''||name||''' TO ''+DATA'';' from v$datafile
order by con_id;

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/undotbs01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/sysaux01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf' TO '+DATA';
spool end;
exit

Kada bi sada pokrenuli ovu skriptu dobili bi nekoliko grešaka jer nije moguće prebaciti PDB datoteke ako niste u PDB kontejneru. Također PDB baza podataka ne smije biti zatvorena.

 

Nakon što sam malo izmijenio skriptu ovo je finalna verzija:

$ cat /tmp/move_dbfiles.sql

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/undotbs01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/sysaux01.dbf' TO '+DATA';

ALTER SESSION SET CONTAINER=pdb$seed;

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf' TO '+DATA';

ALTER SESSION SET CONTAINER=pdb;
ALTER DATABASE OPEN;

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf' TO '+DATA';

 

Izvršimo sada skriptu:

$ sqlplus / as sysdba

SQL> @/tmp/move_dbfiles.sql

Database altered.
Database altered.
Database altered.
Database altered.
Session altered.
Database altered.
Database altered.
Session altered.
Database altered.
Database altered.
Database altered.
Database altered.
Database altered.

 

Datoteke su premještene u ASM bez ikakvog prekida. Vrlo jednostavno.

Privremeni tablespace-ovi baza se i dalje nalaze na datotečnom sustavu.

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 100         /u01/app/oracle12/oradata/cdb12c/temp01.dbf
2    62       PDB$SEED:TEMP        32767       /u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf
3    100      PDB:TEMP             100         /u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf

 

Njih ne mogu prebaciti metodom koja je gore opisana pa ću ovdje upotrijebiti malo drugačiji postupak.

SQL> alter tablespace TEMP add tempfile '+DATA';
Tablespace altered.

SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle12/oradata/cdb12c/temp01.dbf';
Tablespace altered.


SQL> alter session set container=pdb;
Session altered.

SQL> alter tablespace TEMP add tempfile '+DATA';
Tablespace altered.

SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf';
Tablespace altered.

 

Zeznut dio je premještanje privremenih datoteka TEMP tablespace-a iz PDB$SEED-a koji se koristi za kreiranje utičnih baza podataka. Postupak sam pronašao na blog postu Jože Senegačnika.

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

SQL> alter pluggable database pdb$seed close;
Pluggable database altered.

SQL> alter pluggable database pdb$seed open read write;
Pluggable database altered.

SQL> alter session set container=pdb$seed;
Session altered.

SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.

SQL>  alter tablespace temp drop tempfile '/u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf';
Tablespace altered.

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> alter pluggable database pdb$seed close;
Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;
Pluggable database altered.

 

Podatkovne i privremene datoteke su sada migrirane na ASM.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     +DATA/CDB12C/DATAFILE/system.259.871936371
2    260      PDB$SEED:SYSTEM      NO      +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/system.265.871936527
3    740      SYSAUX               NO      +DATA/CDB12C/DATAFILE/sysaux.263.871936455
4    585      PDB$SEED:SYSAUX      NO      +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/sysaux.256.871936497
5    740      UNDOTBS1             YES     +DATA/CDB12C/DATAFILE/undotbs1.257.871936413
6    5        USERS                NO      +DATA/CDB12C/DATAFILE/users.258.871936451
7    270      PDB:SYSTEM           NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/system.269.871936549
8    605      PDB:SYSAUX           NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/sysaux.266.871936565
9    5        PDB:USERS            NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/users.270.871936547
10   50       PDB:MARKO            NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/marko.271.871936543

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    100      PDB$SEED:TEMP        32767       +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.264.871937389
4    100      TEMP                 32767       +DATA/CDB12C/TEMPFILE/temp.268.871936871
5    100      PDB:TEMP             32767       +DATA/CDB12C/0C38EB3894542836E055000000000001/TEMPFILE/temp.267.871936909
7    100      PDB$SEED:TEMP        32767       +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.262.871937843

 

Premještanje redo log datoteka na ASM:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12/oradata/cdb12c/redo01.log
/u01/app/oracle12/oradata/cdb12c/redo02.log
/u01/app/oracle12/oradata/cdb12c/redo03.log


SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '+DATA';

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '+DATA';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '+DATA';

Database altered.


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/CDB12C/ONLINELOG/group_1.261.871938327
+DATA/CDB12C/ONLINELOG/group_2.260.871938351
+DATA/CDB12C/ONLINELOG/group_3.272.871938381

 

Sve ove radnje su načinjene dok je baza podataka bila podignuta i aktivna.

Sada slijedi dio kada će biti potreban kratak prekid u radu baze – dio kada premještamo kontrolne datoteke na ASM.

 

$ srvctl stop database -d cdb12c


RMAN> startup nomount;

Oracle instance started

Total System Global Area     838860800 bytes

Fixed Size                     2929936 bytes
Variable Size                570428144 bytes
Database Buffers             260046848 bytes
Redo Buffers                   5455872 bytes

RMAN> restore controlfile to '+DATA' from '/u01/app/oracle12/oradata/cdb12c/control01.ctl';

Starting restore at 17-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 17-FEB-15

RMAN> restore controlfile to '+FRA' from '/u01/app/oracle12/oradata/cdb12c/control01.ctl';

Starting restore at 17-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 17-FEB-15




ASMCMD [+] > find --type CONTROLFILE +DATA *
+DATA/CDB12C/CONTROLFILE/current.273.871940441
ASMCMD [+] > find --type CONTROLFILE +FRA *
+FRA/CDB12C/CONTROLFILE/current.256.871940457
SQL> alter system set control_files='+DATA/CDB12C/CONTROLFILE/current.273.871940441','+FRA/CDB12C/CONTROLFILE/current.256.871940457' scope=spfile;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.

 

Stavljanje parametar datoteke na ASM i startanje baze podatka:

$ srvctl start database -d cdb12c -o mount


RMAN> run
2> {
3> BACKUP AS BACKUPSET SPFILE;
4> RESTORE SPFILE TO "+DATA/CDB12C/spfilecdb12c.ora";
5> }

Starting backup at 17-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-FEB-15
channel ORA_DISK_1: finished piece 1 at 17-FEB-15
piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/backupset/2015_02_17/o1_mf_nnsnf_TAG20150217T214744_bg7b7jkg_.bkp tag=TAG20150217T214744 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-FEB-15

Starting Control File and SPFILE Autobackup at 17-FEB-15
piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-FEB-15

Starting restore at 17-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/CDB12C/spfilecdb12c.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp tag=TAG20150217T214745
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-15



$ srvctl modify database -db cdb12c -spfile '+DATA/CDB12C/spfilecdb12c.ora';



$ srvctl stop database -db cdb12c
$ srvctl start database -db cdb12c

 

 

Posljednje provjere:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/CDB12C/spfilecdb12c.ora

SQL> set lines 200
SQL> set pages 999
SQL> select name from v$datafile union all
  2  select name from v$tempfile union all
  3  select member from v$logfile union all
  4  select name from v$controlfile;

NAME
-----------------------------------------------------------------------------
+DATA/CDB12C/DATAFILE/system.259.871936371
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/system.265.871936527
+DATA/CDB12C/DATAFILE/sysaux.263.871936455
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/sysaux.256.871936497
+DATA/CDB12C/DATAFILE/undotbs1.257.871936413
+DATA/CDB12C/DATAFILE/users.258.871936451
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/system.269.871936549
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/sysaux.266.871936565
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/users.270.871936547
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/marko.271.871936543
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.264.871937389
+DATA/CDB12C/TEMPFILE/temp.268.871936871
+DATA/CDB12C/0C38EB3894542836E055000000000001/TEMPFILE/temp.267.871936909
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.262.871937843
+DATA/CDB12C/ONLINELOG/group_1.261.871938327
+DATA/CDB12C/ONLINELOG/group_2.260.871938351
+DATA/CDB12C/ONLINELOG/group_3.272.871938381
+DATA/CDB12C/CONTROLFILE/current.273.871940441
+FRA/CDB12C/CONTROLFILE/current.256.871940457

19 rows selected.



REFERENCE

http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN012

http://joze-senegacnik.blogspot.com/2015/02/maintaining-tempfile-in-temp-tablespace.html

 

 

 


oracle.ninja

Wednesday, 25.06.2014 – Dejan

Slucajno naletih na neku .ninja web adresu, pa sam kupio sebi oracle.ninja :)

Mozda djeluje neozbiljno, ali meni je simpa & cool – dejan @ oracle.ninja


Konačno!! Oracle Certified Master 11g

Tuesday, 24.06.2014 – Dejan

Nakon dugogodišnjeg odugovlačenja, početkom juna sam u Minhenu bio na ispitu za Oracle Certified Master 11g. Već nekoliko dana prije samog ispita me hvatala nervoza, uzbuđenje, strah – zbog toga nisam mogao da spavam normalno, pa sam se uspaničio zbog te hronične nesanice – psihički i fizički umor su me zamalo koštali ispita!

Pomogle su mi tablete za spavanje, pa sam koliko-toliko bio u stanju da skupim koncentraciju i razvalim ispit. Nakon 20 dana (ne)strpljivog čekanja, danas je konačno došao rezultat:

Dear Dejan,

Congratulations! on the successful completion of the Oracle DBA 11g Certified Master practicum.

You are now a member of an elite group of Oracle professionals.  You will receive your Oracle DBA 11g Certified Master fulfillment kit that includes a congratulations letter, OCM certificate, OCM ID card, and denim OCM shirt to the mailing address mentioned in your Oracle DBA 11g OCM Hands-on course requirement Form.

Radost, sreća, olakšanje!

Ukratko – da sam znao unaprijed koliko će me koštati vremena, zdravlja, novaca i odricanja drugih stvari – ne bih išao na ovaj ispit… Da – moglo bi se reći da sam mazohista. :)

Usput moram uputiti izvinjenje, jer sam zadnjih godinu-dvije skroz zapostavio blog. :( Učio sam, radio sam, pa opet učio i još više radio. A kraj se ne nazire – paralelno radim za 3 klijenta, pičim Oracle Exadata kurs (Training on Demand), pokušavam uhvatiti korak sa “12c New Features” i pored svega toga voditi normalan privatni život…

Djeco – manite se IT-a! Bavite se nečim kreativnijim i opuštenijim…

Odjava – u potrazi za “reset” dugmetom.


Toad for Oracle – Zaboravljeni pasvordi…

Sunday, 13.10.2013 – mradovan

Lijepo je imati uključenu opciju “Save password” u našim alatima, pa ne moramo zamarati svoje male sive ćelije pamćenjem istih. Ponekad zaboravimo što smo si upisali za password, a iako postoje i raznorazni password manager programi, ljenost je ta koja je ponekad jača od alata :)

Postoji više solucija za pronalaženje istih, dat ću samo linkove na dva rješenja, jedno je ugrađeno u sam Toad (slučajno ili namjerno), dok je drugo online web servis:

1. http://damir-vadas.blogspot.com/2013/06/retrive-oracle-password-from-toad-for.html?showComment=1381689078487#c7070601693746741997

2. http://www.talkingbyte.com/index.php?page=connectionpwds

Ovim putem zahvaljujem autorima na trudu i nesebičnosti!!!

 


Zasto je super-kljuc super stvar?

Monday, 09.09.2013 – Zidar

Svi koji se bavimo bazam podataka znamo sta je kljuc – minimalan skup atributa koji jedinstveno odredjuje entorke (tuples) koji cine telo posmatrane relacije. Minimalan znaci da ako izbacimo neki element iz kljuca, preostali skup vise nije kljuc, jer se gubi jedinstvenost. Kljuc genralno ima vise atributa, a cesto tacno jedan. Sta je onda super-kljuc? Super kljuc je – prosireni kljuc, to jest kljuc kome smo dodali jedan ili vise atributa.

U teoriji, super kljucevi su veoma vazni. A sta nam u praksi znace superkljucevi? Zar nije dovoljno strasno  sto ponekad moramo da koristimo kljuceve od sastavljene od vise atributa? Zasto bi dodavali kljucu jos atributa? Ispostavlja se das u superkljucevi veoma korisna stvar i u praksi.

Dodavanjem atributa kljucevima ne narusava se jedinstvenost. Ako nekakav ID jedinstveno odredjuje redove u nakoj tabeli, onda i kombinacija (ID, Ime) isto tako jedinstveno odredjuje redove u tabeli. Ovo pak znaci da super kljuc mozemo da upotrebimo umesto kljuca, ako nam je tako zgodno.

Pokazacemo dva primera gde superkljucevi znacajno olaksavaju odrzanje integriteta i kvaliteta podataka koje cuvamo u bazi.

Primer 1 – generalizacija.

Neka imamo relaciju koja sdrzi podatke o knjigama u biblioteci. Predikat P koji opisuje relaciju glasi:

P = “Knjiga sa identifikacioniom brojem [ISBN] ima naslov [Title] cuva se u formatu [Format]” . Kljuc relacije je [ISBN]. Format moze imati vrednosti iz skupa {‘mp3′,’printed’}

Iz ovog predikata se mogu izvesti sledece propozicije (iskazi) koji cine telo relacije:

Knjiga [ISBN=9000] ima naslov [Title=’An Introduction To Database Systems’] i cuva se u formatu [Format=’mp3′]

Knjiga [ISBN=75284] ima naslov [Title=’Database Design for Mere Mortals’] i cuva se u formatu [Format=’printed’]

Knjiga [ISBN=53495] ima naslov [Title=’SQL Antipatterns’] i cuva se u formatu [Format=’printed’]

Umesto da pisemo iskaze koji odgovaraju datom predikatu, naravno da je lakse prikazati sve u tabelarnoj formi:

RELATION Books:

[ISBN] [Title] [Format]
9000 An Introduction To Database Systems mp3
75284 Database Design for Mere Mortals printed
53495 SQL Antipatterns printed

Primary Key PK = {ISBN}

Naslovni red u tabeli predstavlja zaglavlje (heading) relacije, a redovi sa podacima predstavljaju telo relacije. Tablea koju smo nacrtali je slika relacije R koju smo definisali predikatom P.

Posto knjige dolaze u razlicitim formatima (tacno dva u nasem slucaju), mi zelimo da za razlicte formate cuvamo razlicte informacije. Za format ‘printed’ hocemo da cuvamo broj stranica i vrstu poveza. Za format ‘mp3′ hocemo da cuvamo velicnu u kilobajtima. Da to postignemo, mozemo da napravimo jos dve relacije, sa ovakvim predikatima:

Predikat PS: “Knjiga [ISBN] ima [Pages] stranica i ima povez [Binding]

Predikat PMP3: “Knjiga [ISBN] zahteva [KB] kilobajta na elektronskim medijima

Slike relacija koje definisanih predikatima PS i PS3 izgledale bi ovako:

RELATION PrintedBooks:

[ISBN] [Pages] [Binding]
75284 612 paper-back
53495 334 hard cover

Primary key PK = {ISBN}

Foreign Key FK = {ISBN} REFERENCES Books {ISBN}

RELATION BooksMP3:

[SBN] [KB]
9000 12350

Primary key PK = {ISBN}

Foreign Key FK = ISBN REFERENCES Books {ISBN}

Relacije su perfektno normalizovane i sve izgleda OK. Ipak, imamo jedan ozbiljan problem. Ja sam pazljivo uneo stampane knjige u relaciju PrintedBooks i knjigu ISBN = 9000 u relaciju BooksMP3. Da nisam bio pazljiv, mogao sam da unesem knjige u pogresne relacije. Kako da garantujem da ce u PrintedBooks biti unesene samo one knjige koje su u format ‘printed’, I das vi MP3 idu u relaciju BooksMP3? Odgovor je – super kljuc, uz jedan CHECK constraint.

Na relaciji Books, definisacemo super kljuc SK1 = {ISBN,Format}. Relacija sada izgleda ovako:

RELATION Books:

[ISBN] [Title] [Format]
9000 An Introduction To Database Systems mp3
75284 Database Design for Mere Mortals printed
53495 SQL Antipatterns printed

Primary Key PK = {ISBN}

Super key SK1: UNIQUE {ISBN,Format}

Relacije PrintedBooks I BooksMP3 prosiricemo zap o jedan novi atribut – [Format], ovako:

RELATION PrintedBooks:

[ISBN] [Pages] [Binding] [Format]
75284 612 paper-back printed
53495 334 hard cover printed

Primary key PK = {ISBN}

Foreign Key FK = {ISBN} REFERENCES Books {ISBN,Format}

CHECK [Format] = ‘printed’

RELATION BooksMP3:

[SBN] [KB] Format
9000 12350 mp3

Primary key PK = {ISBN}

Foreign Key FK = {ISBN} REFERENCES Books {ISBN,Format}

CHECK [Format] = ‘mp3′

Superkljuc SK1 na relaciji Books treba nam da bi mogli da uspostavimo Foreign Key u relacijama PrintedBooks i BooksMP3. CHECK constraints garantuju da samo knjige sa korektnim formatom mogu da idu relacije PrintedBooks i BooksMP3.

Da budemo inzenjerski precizni, trebalo bi da azuriramo i predikate koji idu uz tabele PrintedBooks i BooksMP3, ovako:

Predikat PS: “Knjiga [ISBN] je formata [Format=’printed’] , [Pages] stranica i ima povez [Binding]

Predikat PMP3: “Knjiga [ISBN] cuva se u formatu [Format=’mp3′] i zahteva [KB] kilobajta na elektronskim medijima

Slucaj koji smo opisali zove se generalizacija, preciznije “Relacija Books je generalizacija relacija PrintedBooks i BooksMP3 po atributu [Format]”

Primer 2: Dozvoljeni iznos za povlacenje na bankovnoj masini

Svaki vlasnik tekuceg racuna moze da na bankovnoj masini povuce odredjenu sumu novca. Iznos koji se moze povuci u jednoj transakciji odredjuje se za svaki racun posebno. Kako garantovati da niko ne moze u jednoj transakciji da povuce vise nego sto mu je dozvoljeno? Narvno – uz pomoc super kljcuceva.

Predikat R1: “Racun broj [AcctNo] ima ogranicenje za podizanje novca u jednoj transakciji of [MaxAmt] dinara”

RELATION Accounts:

[AcctNo] MaxAmt
A1 100
A2 150
A3 50

Primary Key PK = {AcctNo}

Predikat R2: “U transakciji [TrasnsID] u datum i vreme [TransDate] sa racuna [AcctNo] podignuto je [Amt] dinara”

RELATION: Transactions

[TransId] [TransDate] [AcctNo] [Amt]
T1 D1 A1 70
T2 D2 A2 130
T3 D1 A3 45

Primary key PK =:{ TransID}

Alternate Key AK1 = {[AcctNo],[Transdate]}

Foreign Key FK1 =  {[AcctNo]} REFERENCES Accounts {[AcctNo]}

Ponovo je sve perfektno normalizovano. I ponovo imamo problem – kako garantovati da relacija nece prihvatiti vrednosti vece od dozvoljenih u atributu [Amt]?

Uradicemo istu stvar kao u slucaju sa generalizacijom:

  • Uvodimo super kluc u relaciji Accounts, {AcctNo, MaxAmt}
  • Dodajemo atribut [MaxAmt] u relaciju Transactions
  • Azuriramo Foreign Key u relaciji Transaction
  • Dodajemo CHECK constraint u relaciju Accounts koji poredi [Amt] se [MaxAmt]

Konacne relacije izgledace ovako:

RELATION Accounts:

[AcctNo] [MaxAmt]
A1 100
A2 150
A3 50

Primary Key PK = {AcctNo}

Super Key SK1 = {AcctNo, MaxAmt}

RELATION: Transactions

[TransId] [TransDate] [AcctNo] [Amt] [MaxAmt]
T1 D1 A1 70 100
T2 D2 A2 130 150
T3 D1 A3 45 50

Primary key PK =:{ TransID}

Alternate Key AK1 = {[AcctNo],[Transdate]}

Foreign Key FK1 = { [AcctNo],[MaxAmt]} REFERENCES Accounts ([AcctNo],[MaxAmt]}

CHECK [Amt]<=[MaxAmt]

Azurirani predikati izgledaju ovako:

Predikat R2: “U transakciji [TrasnsID] u datum i vreme [TransDate] sa racuna [AcctNo] podignuto je [Amt] dinara, sto je manje od dozvoljenih [MaxAmt] dinara”

 U oba slucaja koja smo pokazali, konacne relacije su donekle denormalizovane.  Uveli smo redundansu – [MaxAmt] se nalazi u relaciji [Transactions], gde mu nije mesto, ako cemo da doslovno ispostujemo Boyce-Codd normalnu formu. To je cena koju smo morali da platimo da bismo garantovali veoma vazna poslovna pravila. Ovo se moze nazvati i ‘kontrolisana denormalizacija’. Konacan rezultat je skup tabela koje bolje predstavljaju realnost nego formalno perfektno normalizovana shema.

Super kljucevi su ponekad zaista super stvar.


Oracle Database 12c je i službeno objavljena!

Wednesday, 26.06.2013 – Dejan

Ako već niste saznali iz drugih izvora, onda će vas ova vijest obradovati – Oracle database 12c je konačno i službeno objavljena, tj. dostupna za download!

12c_download1


Logiranje izmjene strukture baze – DDL LOGGER

Thursday, 11.04.2013 – mradovan

Modeliranje baze podataka često nije jednokratan posao, uvijek se nađe potreba da se naprave izmjene na dijelovima koda ili strukturi tablica, doda ili oduzme neka kolona u nekoj tablici, izmijeni tip podatka neke kolone, doda ili obriše neki ključ ili index…

Kada se takve izmjene rade nepredviđeno, a ne dokumentiraju se, ili u okolini gdje takve stvari radi više ljudi (namjerno ili slučajno), i kad se to radi ručno kroz komandnu liniju a bez modeliranja u nekom alatu za modeliranje (čiji bi se modeli spremali pod sistemom verzioniranja), mogu dovesti do vrlo nezgodnih situacija u kojima ne znamo tok događaja – tko je, kada i što napravio (i zašto ?).

Čak sam i sam bio u situacijama gdje neke promjene radim na razvoju, pa nakon nekog vremena ne znam šta sam napravio i kojim redoslijedom, ili zaključim da sam otišao pogrešnim putem i da se trebam vratiti nekoliko koraka unatrag, a ne znam točno kako…

Rješenje je vrlo jednostavno – database trigger koji logira sistemske događaje u tablicu DDLOG sa sljedećim kolonama:

  • OWNER (vlasnik objekta)
  • OPERATION (CREATE, ALTER ili DROP)
  • OBJECT_TYPE (procedure, table, trigger,package…)
  • OBJECT_NAME (ime objekta)
  • SQL_TEXT (prvih 1000 znakova sql izraza)
  • SQL_FULLTEXT (kompletni kod izraza)
  • CREATED (vrijeme upisa)
  • CREATED_BY (user koji je pokrenuo operaciju)
  • TERMINAL_NAME (ime računala s kojeg je operacija pokrenuta)
  • TERMINAL_IP (IP adresa računala s kojeg je operacija pokrenuta)
  • SESSIONID (ID sesije u kojoj je operacija izvršena).

Iz drugih korisničkih shema se može raditi select na ovu tablicu uz posjedovanje SELECT ANY TABLE privilegije.

Druga tablica ERRLOG služi za logiranje eventualnih grešaka u samom triggeru.

Priložena je skripta koju je potrebno pokrenuti pod SYS userom, čime se kreira user DDLOGGER, sa tablicom DDLOG:

/********************************************************************************************************************************************
Desc: User for logging changes on database objects: CREATE, ALTER and DROP operations.
Author: Mihael Radovan
Last change: 28.03.2013
********************************************************************************************************************************************/
/*  user with privileges */
CREATE user ddlogger IDENTIFIED BY DDL;
GRANT UNLIMITED TABLESPACE TO ddlogger;

/* error log table */
CREATE TABLE ddlogger.errlog
(tstamp TIMESTAMP DEFAULT systimestamp,
errcode number,
errtext varchar2(500))
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;

/* err log procedure */

CREATE OR REPLACE PROCEDURE ddlogger.plog(errcode number, errtext varchar2)
IS
  PRAGMA autonomous_transaction;
BEGIN
  INSERT /*+ append */ INTO ddlogger.errlog(errcode, errtext) 
  VALUES (errcode, errtext);
  
  COMMIT;

END plog;
/
/* ddl log table */

CREATE TABLE ddlogger.ddlog (
owner   VARCHAR2(30),
operation   VARCHAR2(30),
object_type VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(1000),
sql_fulltext clob,
created TIMESTAMP,
created_by  VARCHAR2(30),
terminal_name VARCHAR2(255),
terminal_ip VARCHAR2(30),
sessionid number)
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;

/* database trigger */

CREATE OR REPLACE TRIGGER ddlogger.DDL_TRIGGER BEFORE DDL ON DATABASE
DECLARE
  oper varchar2(30);
  l_sql_text ora_name_list_t;
  l_count NUMBER;
  l_sql varchar2(1000);
  errcode number;
  errtext varchar2(500);
BEGIN
  SELECT ora_sysevent
    INTO oper
   FROM DUAL;

   l_count := ora_sql_txt(l_sql_text);

  FOR i IN 1..l_count

  LOOP
      l_sql := l_sql||l_sql_text(i);
  END LOOP;

  IF (oper IN ('CREATE', 'ALTER', 'DROP')) AND (ora_dict_obj_name != ' ddlog') THEN NULL;

   INSERT /*+ append */ INTO ddlogger.ddlog (owner, operation, object_type, object_name, sql_text, sql_fulltext, created, created_by, terminal_name, terminal_ip, sessionid)
   VALUES (ora_dict_obj_owner, oper, ora_dict_obj_type, ora_dict_obj_name, substr(l_sql, 1, 1000), l_sql, systimestamp, user, SYS_CONTEXT ('userenv', 'host'),  SYS_CONTEXT ('userenv', 'ip_address'), SYS_CONTEXT ('userenv', 'sessionid'));
  END IF;

EXCEPTION
  WHEN others THEN
     errcode:= SQLCODE;
     errtext:= SQLERRM;
     plog(errcode, errtext);
END;
/

REGEXP_SUBSTR/REGEXP_INSTR: ORA-01428: argument ‘-1′ is out of range

Monday, 11.02.2013 – Dejan

Programiram ja danas nesto i hocu pri tome koristiti regular expression (REGEXP_SUBSTR, REGEXP_INSTR i REGEXP_REPLACE), ali sam na jednom “problemu” izgubio 3-4 sata i ne mogu da nadjem uzrok te greske… Ni Google mi nije pomogao, MyOracleSupport takodje nista…

Htio sam koristiti opciju “backward search”, tj. da se pattern u stringu trazi unatrag, a ne od pocetka. Dakle, ovaj SQL:

select regexp_instr('Austria01#Vienna01#1130', '#', -1, 1)
from dual;

izbacuje gresku:

ORA-01428: argument '-1' is out of range

Sluzbena dokumentacija prvo kaze:
“position: A nonzero integer indicating the character of source_char where the function begins the search. When position is negative, then the function counts and searches backward from the end of string.

A na drugom mjestu samo:
“position is a positive integer indicating the character of source_char where Oracle should begin the search.”

Dakle, sta je od ovih tvrdnji u sluzbenoj dokumentaciji tacno!?

Kao sto rekoh, pretragom interneta nisam nasao nikakve korisne informacije, tako da sam kreirao Service Request kod Oracle Support, pa cemo vidjeti sta ce oni reci…

Stay tuned.

Edit 21.02.2013.:
nakon prepiske sa Oracle Supportom, ustanovljeno je da je dokumentacija pogresna. :)
Ovo su mi javili:

“Hello,

the new doc bug 16374293 has been created and it was already screened for DBE, then it will be processed in accord with its priority and the next Olap guides will be corrected.

Thanks for your hint.”

Znaci, trenutno nije moguce koristiti backward search sa REGEXP komandama…


Druženje u Beogradu?

Saturday, 26.01.2013 – Dejan

Nakon nekoliko seminara u Zagrebu, ovaj put idem na jedan kurs u Beograd: “Oracle Database 11g: Data Guard Administration Rel 2″, jer mi je potreban kao uslov za polaganje OCM (Oracle Certified Master) ispita.
Dakle, od 31.03.2013. do 04.04.2013. sam u Beogradu, pa ako je neko za druženje uz pivo/nespresso/ćevape/Sushi i ostale delikatese, nek se javi!


Full Table Scan vs. BITMAP Index vs. VIRTUAL Column

Friday, 02.11.2012 – Dejan

Ovaj put donosim još jedan primjer iz prakse, na osnovu kojeg ću pokazati interesantne metode optimizacije problematičnog SQL upita.

U stvarnoj bazi postoji jedna tabela, u koju se podaci unose i obrađuju po slijedećem principu:
– podaci se unose u nejednakim razmacima, pri čemu se jedna status kolona označi sa ‘N’, što znači da podaci nisu obrađeni; ukoliko dođe do neke greške prilikom unosa ili su podaci nepotpuni, onda status kolona dobija vrijednost NULL
– jedan SQL upit se izvršava svakih 5 sekundi i provjerava, da li postoje neobrađeni ili nepotpuni podaci
– ukoliko postoje neobrađeni podaci, onda bivaju obrađeni, nakon čega se vrijednost u status koloni mijenja iz ‘N’ u ‘Y’

E sad, ostavimo na stranu što je dizajn ove tabele, odnosno njena fizička struktura, u praksi veoma loša…

Zadatak je da optimiram onaj SQL, koji svakih 5 sekundi provjerava, da li postoje neobrađeni podaci. Možeš se žaliti da je struktura tabele loša i da ju treba izmijeniti – odgovor je uvijek isti:”Nemamo sad vremena za komplikovane izmjene, moramo do kraja mjeseca završiti druge bitnije stvari, a to ćemo naknadno…bla bla“…

Dakle, na posao… Trebalo mi je skoro 2 sata vremena, ali rezultat je bio itekako dojmljiv… Zanima vas kako?
Pročitaj kompletan tekst »