LORDBASE: maja 2015

piątek, 29 maja 2015

Instalacja Oracle XE na Ubuntu

Ściągniętą paczkę rozpakowujemy

unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

konwertujemy pakiet RPM na DEB
alien --scripts Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

instalacja
dpkg --install oracle-xe_11.2.0-2_amd64.deb
/etc/init.d/shm_load start

konfiguracja
/etc/init.d/oracle-xe configure responseFile=oracle.rsp >> oracle-install.log

Logujemy się do SQLPlus jako sys
i sprawdzamy dostępność
SQL> select * from v$version;

otrzymujemy komunikat:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE        11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>
zakładamy użytkownika Oracle w systemie Ubuntu
ustawiamy ścieżkę i zmienną ORACLE_HOME


 export  ORACLE_HOME="/usr/lib/oracle/xe/app/oracle/product/11.2.0/server"




    export PATH="$PATH:$ORACLE_HOME/bin"

    export ORACLE_SID="XE"

Zmieniamy numer portu (domyślnie 8080)


 

  
  
prompt>sqlplus system@xe

enter the password you created for the user "system"

 

  
  
SQL>begin   (notice that there is no ; on this line)

2 dbms_xdb.sethttpport('8383');

3 end;

4 /

 

Restartujemy serwer a następnie sprawdzamy numer portu

select dbms_xdb.gethttpport as "HTTP-Port", dbms_xdb.getftpport as
"FTP-Port" from dual;

Zarządzanie przez www

dla wersji 10 http://127.0.0.1:8080/apex
dla wersji 11 http://127.0.0.1:8080/apex/f?p=4950

Płyty szkoleniowe Oracle Developer Days

Oracle udostępnia płytę szkoleniową z Oracle Developer Days (wymaga logowania sso)
Pod adresem
Oracle Technology Network Developer Day
znajdziemy:

obraz VBox'a Oracle_DB_Developer_VM_new.ova

a w nim

Oracle Linux 7

Oracle Database 12c Release 1 Enterprise Edition (12.1.0.2 with In-Memory Option)

Oracle XML DB

Oracle SQL Developer

Oracle SQL Developer Data Modeler

Oracle Application Express

W wersjach wcześniejszych był to system CentOS

Po ściągnięciu importujemy plik OVA do VBox'a

szczegółowy opis znajdziemy pod adresem

http://praitheesh.blogspot.nl/2013/10/oracle-developer-day-vm-how-to-use-pre.html

Podstawowe dane dostępowe

APEX port 8888

user obe

pass obe

schema obe

Po wszystkich wymaganych zabiegach powinniśmy otrzymać komunikat podczas startu

All passwords are oracle or noted if otherwise.

The SQL Developer and Data Modeler scripts are in the directory:

/home/oracle/Desktop/SQLDev_Tutorials/sqldev

To Reset the labs:

SQL Developer: /home/oracle/reset_sqldev

Application Express: /home/oracle/reset_apex

In-Memory Database Cache: /home/oracle/reset_imdbcache

XML Database: /home/oracle/reset_xmldb

Oracle Enterprise Manager

Start: emctl start dbconsole

Stop : emctl stop dbconsole

URL : https://127.0.0.1:1158/em

*** Please note that this appliance is for testing purposes only,

as such it is unsupported and should not be used as a production environment.

inet addr:10.0.3.17 Bcast:10.0.3.255 Mask:255.255.255.0 #uwaga adres IP w zależności od tego jaki ustawiłeś na sztywno na VBox

inet addr:127.0.0.1 Mask:255.0.0.0

czwartek, 28 maja 2015

Sesje

Podany skrypt SQL zapisujemy pod nazwą sesja

select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,a.sql_text from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address
and b.status = 'ACTIVE'
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
order by c.spid,a.hash_value,a.piece

wywołujemy go w SQLPlus
@sesja

ubicie sesji

Po numerze SID zabijamy sesje

SQL> alter system kill session '268,1268';

lub

SQL> alter system disconnect session '315,1223' immediate;

Sprawdzamy

select sid, serial#, machine from v$session
where username = 'moj_uzytkownik_sesji';

Odczytywanie konfiguracji bazy

Skrypt ten odczytuje z bazy danych info o plikach bazy i ich konfiguracji,
stopniu zapełnienia, możliwości autorozszerzenia itd.

podzielony na sekcje zawierające pliki:

temp
kontrolne
logów
wszystkie bazy
stałe
autorozszerzalne

set pagesize 66;

spool "tblsp.out.txt";

set linesize 160;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;

select * from dba_data_files;

set linesize 72;
select FILE_NAME,
FILE_ID,
TABLESPACE_NAME,
(BYTES/1024/1024) AS mBYTES,
AUTOEXTENSIBLE,
BLOCKS
from dba_data_files
where autoextensible= 'NO';

set linesize 88;
select FILE_NAME,
FILE_ID,
TABLESPACE_NAME,
(BYTES/1024/1024) AS mBYTES,
AUTOEXTENSIBLE,
(MAXBYTES/1024/1024) AS MAX_MBYTES,
(INCREMENT_BY*MAXBYTES/MAXBLOCKS/1024/1024) as INCREMENT_MBYTE
from dba_data_files
where autoextensible= 'YES';

spool off;
exit;

Rozszerzanie rozmiaru przestrzeni plikowej

Skrypt do rozszerzania rozmiaru przestrzeni plikowej dla przykładu przestrzeń tymczasowa UNDO

CEL:

Zwiększenie rozmiaru pliku UNDO.DBF do 1GB
Wyłączenie autorozszerzania w UNDO.DBF
Dodanie do UNDO dodatkowych 3 plików o początkowym rozmiarze 128MB i maksymalnym rozmiarze 2GB każdy.

dla Windows

ALTER DATABASE DATAFILE 'C:\oraclexe\oradata\XE\UNDO.DBF' resize 1024M;
ALTER database datafile 'C:\oraclexe\oradata\XE\UNDO.DBF' autoextend off;
ALTER TABLESPACE UNDO ADD DATAFILE 'C:\oraclexe\oradata\XE\UNDO_02.DBF' SIZE 64M autoextend on next 64M maxsize 2048M;
ALTER TABLESPACE UNDO ADD DATAFILE 'C:\oraclexe\oradata\XE\UNDO_03.DBF' SIZE 64M autoextend on next 64M maxsize 2048M;
ALTER TABLESPACE UNDO ADD DATAFILE 'C:\oraclexe\oradata\XE\UNDO_04.DBF' SIZE 64M autoextend on next 64M maxsize 2048M;

Replikowanie Bazy do Testów

Skrypt - Replikowanie Bazy do Testów

prompt
prompt Usuwanie użytkownika
prompt Proszę czekać...
drop user test cascade;
prompt
prompt Zakładanie użytkownika
prompt Proszę czekać...

create user test identified by hello
default tablespace adm_data
temporary tablespace adm_tmp
/
grant connect,resource,dba to test
/
grant select on sys.v_$session to test
/
prompt Za chwilę nastąpi Eksport i Import danych
prompt Proszę nie zamykać okna...
exit;

EXP Eksport danych IMP import danych

EXP Export danych do pliku - Nie archiwizuje on struktury całej bazy danych
lecz dane zawarte w danej instancji.

Komendy narzędzia EXP

użytkownik/hasło@nazwa_instancji - dane logowania do bazy (deskryptor połączenia)
np. hr/hello@xe

file=C:\sciezka_do_pliku\plik.dmp - ścieżka wskazująca plik eksportu (Windows)
file=/u03/Backup/kopia.dmp (Linux)

log =C:\sciezka_do_pliku_logu\plik.log - ścieżka wskazująca plik logu (informacje o eksporcie)

full=y - pełen eksport bazy (wszystkie schematy)

owner=nazwa_użytkownika - eksport konkretnego schematu z bazy (np. owner=xe,hr)

indexes=y grants=y constraints=y rows=y - Parametry dodatkowe eksportu (99%)
consistent=y (1%)
-- 100%

exp system/sys@xe file=/u03/test/kopia.dmp log=/u03/test/kopia.log full=y indexes=y grants=y constraints=y rows=y consistent=y

użytkownik sys as sysdba

Eksportuje się do /home/dump/xe/dump/30042014
/home/oracle/dump/xe/dump/30052014

exp file='sciezka' log='sciezka' FULL=Y

exp file='/home/oracle/dump/xe/dump/11062014' log='/home/oracle/dump/xe/dump/11062014' FULL=Y

imp file='/home/oracle/dump/xe/dump/11062014/dump.exp' log='/home/oracle/dump/xe/dump/11062014/imp.log' FULL=Y

IMP - Import Danych

użytkownik/hasło@nazwa_instancji - dane logowania do bazy (deskryptor połączenia)
np. hr/hello@xe

file=C:\sciezka_do_pliku\plik.dmp - ścieżka wskazująca plik eksportu (Windows)
file=/u03/Backup/kopia.dmp - (Linux)

log =C:\sciezka_do_pliku_logu\plik.log - ścieżka wskazująca plik logu (informacje o eksporcie)

full=y - pełen eksport bazy (wszystkie schematy)

fromuser=nazwa_użytkownika (z użytkownika)
touser=nazwa_użytkownika (do użytkownika)

fromuser=hr touser=test - Jedna kopia na test
fromuser=(maat,maat,maat) touser=(test1,test2,test3) - Trzy kopie na test

indexes=y grants=y constraints=y rows=y - Parametry dodatkowe eksportu

imp system/sys@xe file=/u03/Backup/backup.dmp log=/u03/Backup/backup.log fromuser=hr touser=test indexes=y grants=y constraints=y rows=y

Przebudowa indesków

Prosty skrypt do wyświetlenia indeksów dla schematu HR.
Zostanie wygenerowany plik temp.sql z listą poleceń przebudowy wszystkich indeksów.

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER INDEX ' || index_name ||' REBUILD;'
from dba_indexes where owner ='HR'
/

SPOOL OFF

lub
dla bieżącego schematu

SELECT 'ALTER INDEX '|| TABLE_OWNER ||'.'|| INDEX_NAME || ' REBUILD;' FROM USER_INDEXES;

Podczas indeksowania nie można pracować na bazie

wtorek, 26 maja 2015

Tuning Oracle - Statystyki

poprawiamy wydajność itd.

Statystyki

Musimy wyłączyć naliczanie statystyk i wykasować statystyki istniejące.
Sprawdzamy czy istnieją jakieś statystyki zatem np logujemy się na użytkownika HR

SELECT TABLE_NAME FROM USER_TABLES WHERE last_analyzed IS NOT NULL

Kasujemy statystyki jako użytkownik np HR

BEGIN

FOR C IN (SELECT table_name FROM user_tables WHERE last_analyzed IS NOT NULL) LOOP

EXECUTE IMMEDIATE 'ANALYZE TABLE '||C.table_name||' DELETE STATISTICS';

END LOOP;

END;

Zatrzymanie naliczania statystyk logujemy się jako SYS

BEGIN

DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

END;

BEGIN

DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');

END;

Przeliczenie statystyk szybkie logujemy się jako SYSTEM

begin

dbms_utility.analyze_schema('FK','ESTIMATE');

end;

Przeliczenie statystyk dokładne logujemy się jako SYSTEM

begin

dbms_utility.analyze_schema('GABINET','COMPUTE');

end;

sobota, 23 maja 2015

Przestrzeń dyskowa

Tworzenie nowych przestrzeni dyskowych

CREATE TABLESPACE USER1
DATAFILE 'd:\db\usr1.dat' SIZE 100M
AUTOEXTEND ON
/

CREATE TABLESPACE USER2
DATAFILE 'd:\db\usr2.dat' SIZE 100M
AUTOEXTEND ON
/

CREATE USER "ADM_USER" IDENTIFIED BY "HASLO" DEFAULT TABLESPACE "USER1" PROFILE DEFAULT ACCOUNT UNLOCK
/

GRANT "CONNECT" TO "ADM_USER"
/

GRANT "RESOURCE" TO "ADM_USER"
/

ALTER USER "ADM_USER" DEFAULT ROLE ALL
/

Możemy nadać prawa do nieograniczonego miejsca na dysku

grant unlimited tablespace to HR;

Dodanie użytkownika do bazy, zmiana nazwy, odblokowanie użytkownika

Przechodzimy do SQLPlus wpisujemy

CREATE USER DEMO IDENTIFIED BY haslo

DEFAULT TABLESPACE TESTDATA

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT ACCOUNT UNLOCK;

lub

PROFILE MONITORING ACCOUNT UNLOCK;

Przyznajemy uprawnienia

GRANT CONNECT TO DEMO;

GRANT RESOURCE TO DEMO;

ALTER USER DEMO DEFAULT ROLE ALL;

Przyznajemy przestrzeń dyskową bez limitów dla użytkownika DEMO

ALTER USER DEMO QUOTA UNLIMITED ON TESTDATA;

GRANT UNLIMITED TABLESPACE TO DEMO;

ALTER USER DEMO QUOTA UNLIMITED ON TEMP;

należy potwierdzić transakcję

COMMIT;

Szczegóły

SQL*Plus: Release 11.2.0.2.0 Production on So Maj 23 17:48:35 2015

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> CREATE USER DEMO IDENTIFIED BY haslo
2 DEFAULT TABLESPACE TESTDATA
3 TEMPORARY TABLESPACE TEMP
4 PROFILE DEFAULT ACCOUNT UNLOCK;

User created.

SQL> GRANT CONNECT TO DEMO;

Grant succeeded.

SQL>
SQL> GRANT RESOURCE TO DEMO;

Grant succeeded.

SQL>
SQL> ALTER USER DEMO DEFAULT ROLE ALL;

User altered.

SQL>
SQL> ALTER USER DEMO QUOTA UNLIMITED ON TESTDATA;

User altered.

SQL>
SQL> GRANT UNLIMITED TABLESPACE TO DEMO;

Grant succeeded.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> ALTER USER DEMO QUOTA UNLIMITED ON TEMP;

User altered.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL> EXIT;
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Zmieniamy hasło użytkownika

Logujemy się do SQLPlus jako sys/hasło as sysdba wykonujemy

ALTER USER nazwa_schematu IDENTIFIED BY nowe_hasło;

Wykonujemy poniższe 3 polecenia:

ALTER USER nazwa_schematu ACCOUNT UNLOCK;
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Następnie musimy odblokować konto po wykonaniu wcześniejszych czynności. W tym celu wykonujemy poniższe 2 polecenia:

ALTER USER nazwa_schematu IDENTIFIED BY nowe_hasło;
ALTER USER nazwa_schematu IDENTIFIED BY HASŁO account unlock

dla użytkownika HR
np

ALTER USER HR ACCOUNT UNLOCK

Pamiętaj!!!

Oracle 10g domyślnie ogranicza liczbę błędnych logowań do 3, można to wyłączyć :

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED

Wpisy do rejestru Oracle XE

Dla użytkowników Windows

Gdy mamy problemy z HTTP lub z uruchomieniem usługi to możemy się brakuje kilku wpisów w systemie Windows

Poniższy tekst należy skopiować do notatnika i zapisać plik o rozszerzeniu reg

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup]
"NumberOfInstalls"=dword:%NOI%

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\All Versions]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\All Versions\%NOI%]
"Version"="11.2.0.2.0"
"Home"="%ORACLE_HOME%"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\Current Version]
"Version"="10.2.0.1.0"
"Home"="%ORACLE_HOME%"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Protid_0]
"Name"="HTTP"
"Host"="%HOSTNAME%"
"Port"="%MTSPORTNUM%"

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleMTSRecoveryService]
"OMTSRECO_TRACELEVEL"="NOTRACE"
"OMTSRECO_TRACEFILE"="%ORACLE_HOME%\\oramts\\trace\\OracleMTSRecoveryService.trc"

Kodowanie znaków Oracle XE

Jeśli mamy problemy z importem danych, schematu w Oracle XE to można się spodziewać że problem jest po stronie kodowania znaków.

Aby rozwiązać problem

Uruchamiamy SQLPlus wpisujemy

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE EE8MSWIN1250;
SHUTDOWN IMMEDIATE;
STARTUP OPEN;

EXIT

Cały przebieg operacji powinien tak wyglądać

SQL*Plus: Release 11.2.0.2.0 Production on So Mar 21 23:49:02 2015

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> SET SERVEROUTPUT ON

SQL>
SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.
SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 552402944 bytes
Fixed Size 1384872 bytes
Variable Size 167775832 bytes
Database Buffers 377487360 bytes
Redo Buffers 5754880 bytes

Database mounted.

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE EE8MSWIN1250;

Database altered.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.
SQL> STARTUP OPEN;

ORACLE instance started.

Total System Global Area 552402944 bytes
Fixed Size 1384872 bytes
Variable Size 167775832 bytes
Database Buffers 377487360 bytes
Redo Buffers 5754880 bytes

Database mounted.

Database opened.
SQL>
SQL> EXIT
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

środa, 20 maja 2015

Tryb Archivelog

Włączenie trybu archivelog
wpisujemy do konsoli

rman target /
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SQL "ALTER DATABASE ARCHIVELOG";
ALTER DATABASE OPEN;

Wyłączenie trybu archivelog

rman target /
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SQL "ALTER DATABASE NOARCHIVELOG";
ALTER DATABASE OPEN;

Sprawdzenie stanu czy archivelog jest wyłączony

logujemy się do SQLPlus
wpisujemy

select log_mode from v$database;

Wykonanie backupu przed usunięciem dzienników

rman target /
crosscheck archivelog all;
backup archivelog all delete input;

Usunięcie starych dzienników

rman target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate-1';

Ustawienie zapisu archivelog
np:
SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';

Wyświetlenie zawartości dzienników

ls -lh /u01/flash_recovery_area/INSTANCJA/archivelog/2014_01_02/

total 1012M
-rw-r----- 1 oracle oinstall 42M Jan 2 10:22 o1_mf_1_10_9dbcz0kd_.arc
-rw-r----- 1 oracle oinstall 44M Jan 2 10:22 o1_mf_1_11_9dbcz9kt_.arc
-rw-r----- 1 oracle oinstall 48M Jan 2 10:22 o1_mf_1_12_9dbczlf6_.arc
-rw-r----- 1 oracle oinstall 42M Jan 2 10:23 o1_mf_1_13_9dbczvld_.arc
-rw-r----- 1 oracle oinstall 39M Jan 2 10:34 o1_mf_1_14_9dbdo2hk_.arc
........

Obcinanie SHRINK

W przypadku gdy przekroczymy rozmiar bazy

Zapytanie informujące o obiektach takich jak tabele i loby zajmujące najwięcej miejsca w bazie
Logujemy się na użytkownika SYS i wykonujemy

select
segment_namena u,
a.bytes/1024/1024 Size_MB,
a.tablespace_name,
b.table_name,
column_name,
owner
from dba_segments a
left join dba_lobs b using (owner, segment_name)
where a.tablespace_name like 'IMED%' order by a.bytes desc

wybieramy obiekt

Dla object_test np.

alter table object_test enable row movement;
ALTER TABLE audit_document modify LOB (CONTENT) (SHRINK SPACE);
Alter table audit_document shrink space compact;

Po odzyskaniu miejsca możemy spróbować zmniejszyć pliki bazy danych odpowiednią wartość próbujemy dobrać na podstawie tego zajętości plików

Na użytkowniku SYS np.

ALTER DATABASE DATAFILE '../oraclexe/oradata/xe/IMEDDT_DAT’
RESIZE 1000M;
ALTER DATABASE DATAFILE '../oraclexe/oradata/xe/IMEDDT_LOB’
RESIZE 200M;

restartujemy bazę