|
| | Oracle import via SQL-laag? | |
Minder belasting en minder risico’s met transportable tablespaces
door René Kundersma, Oracle Database Administrator en Oracle Certified Master
Met de komst van het Terabyte databasetijdperk wordt het databasebeheer voor 24/7 omgevingen behalve omvangrijker, soms ook omslachtiger en risicovoller. Vaak voorkomende acties voor DBA’ers als de bekende Oracle import (imp), zijn daarom de moeite waard om eens met een frisse blik te bekijken. Wat is er dan zo risicovol aan een import, kun je je afvragen? Stel je het volgende scenario eens voor:
Scenario
Een projectleider vraagt aan de DBA’er of hij een hoeveelheid transacties voor archiefdoeleinden wil aanbrengen op de database. Het gaat om een periode van twaalf maanden met een slordige dertig miljoen transacties per maand. De opdracht is de data (140GB!) van het eerste jaar initieel in één keer te laden. Wat je in dit geval vaak ziet, is dat export/import-opdrachten of andere zelf geschreven tools de data dan van de bron naar het doelsysteem brengen. Maar het inladen van deze hoeveelheid data door de SQL-laag van Oracle (bijvoorbeeld via een import) is niet altijd zo’n verstandig idee. Acties op dit SQL-niveau genereren namelijk redo-log data waaruit archived redo-logs voortkomen, die weer gebruikt worden bij (point-in-time) recoveries.
Op het moment dat na het importeren de database zou crashen, dan moet – als er nog geen nieuwe backup is gemaakt – de complete hoeveelheid data opnieuw door de database, maar nu om te recoveren, wat de ‘time-to-recover’ onacceptabel lang kan maken. Hoewel het risico natuurlijk klein is, is het misschien beter dit niet te nemen. Een tweede argument om geen import door de 'SQL-laag' te gebruiken, heeft te maken met performance. Het inladen van 140GB is een behoorlijke performance-penalty voor de response-time van OnLine Transaction Processing (OLTP).
Alternatief
Een methode om de beschreven twee nadelen te omzeilen, is het gebruik van transportable tablespaces. Hoewel deze optie al sinds 9i beschikbaar is, wordt hij nog weinig gebruikt en dat is zonde. De kracht van transportable tablespaces is namelijk dat er geen tonnen aan archived redo-logs gegenereerd worden, waardoor ze het nadeel van de te lange recovery time en de performance-penalty effectief elimineren. Een opgeruimde manier van werken die mogelijk is doordat transportable tablespaces als het ware de datafiles van bepaalde tablespaces uit de bron database beschikbaar maken, om een kopie ervan in de doel database dienst te laten doen.
In het volgende scenario laat ik zien hoe een brontabel van A naar B gaat via transportable tablespaces. Om de data nog gemakkelijker te kunnen beheren en omdat het type data het goed toelaat, zet ik direct partitionering in, in dit geval per maand.
Opbouw tablespaces
Ik dit voorbeeld ga ik uit van een niet-productieomgeving. Allereerst maken we een tablespace voor de tabel zelf en vervolgens voor iedere partitie een aparte tablespace. Ook krijgt iedere index een eigen tablespace.
-- Base tablespace
CREATE TABLESPACE P_TS_BASE
DATAFILE '/data/oracle/CASHP/p_ts_base_01.dbf'
SIZE 10M
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;-- De tablespace voor de maand jan. 2006
CREATE TABLESPACE P_TS_JAN_2006
datafile '/data/oracle/CASHP/part/p_ts_jan_2006_01.dbf'
SIZE 10000M
LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
-- Een vergelijkbare actie vindt plaats voor de resterende 11 maanden/tablespaces
-- De tablespace voor de indexen
CREATE TABLESPACE P_TS_IND
DATAFILE '/data/oracle/CASHP/p_ts_ind_01.dbf'
SIZE 5000M
LOGGING EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Opbouw source table
Vervolgens is de gepartitioneerde tabel aan te maken. Ik wil benadrukken dat de tabel een base tablespace heeft en per maand een aparte tablespace. Er is bewust gekozen voor een op zichzelf staande base tablespace, met het oog op gemakkelijk onderhoud.
CREATE TABLE TRANSACTIONS (
ACCOUNTS NUMBER NOT NULL,
LOG_TIME DATE DEFAULT trunc(sysdate) NOT NULL,
SEQ_NO NUMBER NOT NULL,
ENTRY_TIME DATE DEFAULT trunc(sysdate) NOT NULL,
VALUE_TIME DATE DEFAULT trunc(sysdate) NOT NULL,
DOWNLOAD_TIME DATE DEFAULT sysdate,
SYNC VARCHAR2 (16),
CURRENCY VARCHAR2 (3) DEFAULT 'EUR' NOT NULL,
BALANCE NUMBER (19,6) DEFAULT 0.0,
CASH_AMOUNT NUMBER (19,6) DEFAULT 0.0 NOT NULL)
partition by range (log_time)(
partition Pjan2005 values less than
(to_date ('01-feb-2005','dd-mon-yyyy')) tablespace P_TS_JAN_2005,
partition Pfeb2005 values less than
(to_date('01-mar-2005','dd-mon-yyyy')) tablespace P_TS_FEB_2005,
partition Pmrt2005 values less than
(to_date('01-apr-2005','dd-mon-yyyy')) tablespace P_TS_MRT_2005,
partition Papr2005 values less than
(to_date('01-may-2005','dd-mon-yyyy')) tablespace P_TS_APR_2005,
partition Pmei2005 values less than
(to_date('01-jun-2005','dd-mon-yyyy')) tablespace P_TS_MEI_2005,
partition Pjun2005 values less than
(to_date('01-jul-2005','dd-mon-yyyy')) tablespace P_TS_JUN_2005,
partition Pjul2005 values less than
(to_date('01-aug-2005','dd-mon-yyyy')) tablespace P_TS_JUL_2005,
partition Paug2005 values less than
(to_date('01-sep-2005','dd-mon-yyyy')) tablespace P_TS_AUG_2005,
partition Psep2005 values less than
(to_date('01-oct-2005','dd-mon-yyyy')) tablespace P_TS_SEP_2005,
partition Pokt2005 values less than
(to_date('01-nov-2005','dd-mon-yyyy')) tablespace P_TS_OKT_2005,
partition Pnov2005 values less than
(to_date('01-dec-2005','dd-mon-yyyy')) tablespace P_TS_NOV_2005,
partition Pdec2005 values less than
(to_date('01-jan-2006','dd-mon-yyyy')) tablespace P_TS_DEC_2005,
partition Pjan2006 values less than
(to_date('01-feb-2006','dd-mon-yyyy')) tablespace P_TS_JAN_2006)
TABLESPACE P_TS_BASE LOGGING
ENABLE ROW MOVEMENT;
Opbouw source index
De tabel krijgt een ‘local index’. In tests bleek een ‘global index’ wel net iets sneller te zijn met selects, maar met de updates/inserts weer velen malen trager.
CREATE UNIQUE INDEX PK_TRANSACTIONS
ON TRANSACTIONS(ACCOUNTS, LOG_TIME, SEQ_NO)
LOCAL TABLESPACE P_TS_IND;
Vullen source table
Omdat we de sourcetabel hebben aangemaakt op een niet kritiek productiesysteem, is binnen deze omgeving zonder risico data te laden via import. Na het laden kijken we via een select hoeveel rijen elke partitie bevat:
select NUM_ROWS
, PARTITION_NAME
FROM dba_tab_partitions
where TABLE_NAME = 'TRANSACTIONS'; NUM_ROWS PARTITION_NAME
---------- ------------------------------
35295760 PJAN2005
32818900 PFEB2005
35763120 PMRT2005
36156610 PAPR2005
35431980 PMEI2005
35480660 PJUN2005
35922850 PJUL2005
38833530 PAUG2005
30281140 PSEP2005
36319140 POKT2005
33395900 PNOV2005
33231860 PDEC2005
30689404 PJAN2006
30944738 PFEB2006
Start to move…
De data is nu geïmporteerd in verschillende partities, klaar om te voorzien van data. Zet de zojuist gevulde tablespaces op read-only en let erop dat dit op alle tablespaces, die de partitioned table kent, tegelijkertijd gebeurt!
SQL> ALTER TABLESPACE P_TS_JAN_2005 READ ONLY;Tablespace altered.
Een vergelijkbare actie vindt vervolgens ook voor de base en de resterende tablespaces plaats (en in dit voorbeeld ook voor de index tablespace).
Export meta…
Nu de tablespaces read-only staan, kan de transportable tablespace actie plaatsvinden: een ‘metadatadump’ van de tablespaces en datafiles van de gepartitioneerde tabel. Ik noem het specifiek metadump omdat de dump alleen data over data bevat. In het onderstaande script is te zien welke opties aan het exportcommando zijn meegeven om dit te bewerkstelligen. De gegenereerde meta-dump file is slechts enkele tientallen KB’s groot, de actie duurt dan ook maar enkele seconden.
Noot: Om er zeker van te zijn dat de data transportable is, kun je gebruik maken van het package dbms_tts.transport_set_check. Het gaat er namelijk om dat de data in de transportable tablespace ‘op zichzelf’ moet kunnen staan (self contained; er mogen geen referenties zijn naar andere tablespaces die niet meegaan met de actie).
#!/usr/bin/ksh
#
# Filename: exp_tts.sh
# Author : R. Kundersma, Qualogy Consultancy B.V.
# Date : Jan 4th 2006
# Purpose : Export metadata for TTS
# Version : 0.1
# Parameters:
# Note:
# /usr/bin/ksh # Sun Solaris
# /usr/bin/ksh # AIX 5.2
# /bin/ksh # RedHat Linux 3.0
#######################################
exp \'/ as sysdba\' \
TRANSPORT_TABLESPACE=y \
TABLESPACES=P_TS_JAN_2005,P_TS_OKT_2005,P_TS_
FEB_2005,P_TS_JAN_20 06,P_TS_SEP_2005,P_TS_
APR_2005,P_TS_MRT_2005,
P_TS_JUN_2005,P_TS_BASE,P_ TS_DEC_2005,
P_TS_IND,P_TS_AUG_2005,P_TS_
NOV_2005,P_TS_JUL_2005,P_TS_MEI _2005,P_
TS_FEB_2006 \
TRIGGERS=n \
GRANTS=Y \
CONSTRAINTS=Y \
FILE=t_transactions_meta.dmp \
LOG=t_transactions_meta.log
-- Wanneer het script eenmaal wordt gestart, dan ziet de output er als volgt uit:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace P_TS_JAN_2005 ...
. exporting cluster definitions
. exporting table definitions
. exporting table TRANSACTIONS
For tablespace P_TS_OKT_2005 ...
. exporting cluster definitions
--- Ingekort ---
For tablespace P_TS_MEI_2005 ...
. exporting cluster definitions
. exporting table definitions
For tablespace P_TS_FEB_2006 ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. end transportable tablespace metadata export
Export terminated successfully without warnings.
Scp, ftp, restore
Na het maken van de meta-dump mag de data naar de doelomgeving voor het uiteindelijke gebruik. De datafiles – onderdeel van de tablespaces van de gepartitioneerde tabel, evenals de metadump – zijn middels tools als scp, ftp of zelfs ook door een taperestore van bron naar doel te brengen.
#!/usr/bin/ksh
#
# Filename: imp_tts.sh
# Author : R. Kundersma, Qualogy Consultancy B.V.
# Date : Jan 4th 2006
# Purpose : Import metadata for TTS
# Version : 0.1
# Parameters:
# Note:
# /usr/bin/ksh # Sun Solaris
# /usr/bin/ksh # AIX 5.2
# /bin/ksh # RedHat Linux 3.0
#######################################
imp \'/ as sysdba\' \
file=t_transactions_meta.dmp \
transport_tablespace=y \
datafiles='/data/oracle/CASHP/ts/part/p_ts_apr_2005_01.dbf','/dat a/oracle/CASHP/ts/part/p_ts_aug_2005_01.dbf','/data/oracle/EB
prod/ts/part/p_ts_base_01.dbf','/data/oracle/CASHP/ts/part/p_ts_
dec_2005_01.dbf','/data/oracle
/CASHP/ts/part/p_ts_feb_2005_01.dbf' ,'/data/oracle/CASHP/ts/
part/p_ts_feb_2006_01.dbf','/data/oracle/ CASHP/ts/part/p_ts_ind
_01.dbf','/data/oracle/CASHP/ts/part/p_ts_i nd_02.dbf','/data/oracle/
CASHP/ts/part/p_ts_ind_03.dbf','/data/or acle/CASHP/ts/part/p_ts_
jan_2005_01.dbf','/data/oracle/CASHP/ts/p art/p_ts_jan_2006_01.dbf'
,'/data/oracle/CASHP/ts/part/p_ts_jul_2005_01.dbf','/data/oracle/
CASHP/ts/part/p_ts_jun_2005_01.dbf','/data/oracle/CASHP/ts/part/
p_ts_mei_2005_01.dbf','/data/oracle/CASHP /ts/part/p_ts_mrt_2005
_01.dbf','/data/oracle/CASHP/ts/part/p_ts_nov_2005_01.dbf','/data/
oracle/CASHP/ts/part/p_ts_okt_2005_01.dbf','/data/oracle/CASHP
/ts/part/p_ts_sep_2005_01.dbf' TABLESPACES=P_TS_JAN_2005,
P_TS_OKT_2005,P_TS_FEB_2005,P_TS_JAN_20 06,P_TS_
SEP_2005,P_TS_APR_2005,P_TS_MRT_2005,
P_TS_JUN_2005,P_TS_BASE,P_TS_DEC_2005,P_TS_IND,
P_TS_AUG_2005,P_TS_NOV_2005,P_TS_JUL_
2005,P_TS_MEI_2005,P_TS_FEB_2006 \
tts_owners=cashp \
fromuser=cashp \
touser=cashp \
log=t_transactions_meta-imp.log
-- Onderstaand de logging na het draaien van het ‘import’ script.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing CASHP's objects into CASHP
Import terminated successfully without warnings.
In tegenstelling tot de import die letterlijk tientallen uren een zware performance-impact heeft gehad op bronomgeving, is de ‘import’ nu binnen 10 seconden klaar. Binnen 10 seconden is de database dus voorzien van 140GB extra data!
Niet vergeten...
Nu de tablespaces eenmaal zijn aangebracht op de doeldatabase, is hun status nog wel read-only – dit was namelijk een verplichte stap bij het loskoppelen van de source-omgeving. Om vervolgens ook inserts, deletes en updates op deze data toe te kunnen passen, is het zaak de tablespaces op ‘read write’ te zetten:
SQL> alter tablespace P_TS_JAN_2005 read write;
Tablespace altered.
Wat ook niet vergeten moet worden is de gebruikers quota te geven op de tablespaces. De quotagegevens komen namelijk niet automatisch mee:SQL> alter user cashp quota unlimited on P_TS_JAN_2005 ;
User altered.
Check
Volledigheidshalve doen we nog een check op de nieuwe tabel:select NUM_ROWS
, PARTITION_NAME
FROM dba_tab_partitions
where TABLE_NAME = 'TRANSACTIONS';
NUM_ROWS PARTITION_NAME
---------- ------------------------------
35295760 PJAN2005
32818900 PFEB2005
35763120 PMRT2005
36156610 PAPR2005
35431980 PMEI2005
35480660 PJUN2005
35922850 PJUL2005
38833530 PAUG2005
30281140 PSEP2005
36319140 POKT2005
33395900 PNOV2005
33231860 PDEC2005
30689404 PJAN2006
30944738 PFEB2006
Bovenstaand scenario laat zien hoe krachtig de transportable tablespace optie is en hoe gemakkelijk je met deze tool risico’s binnen productie-omgevingen kan minimaliseren om performance veilig te stellen. Blijf wel letten op de restricties van de transportable tablespace optie: een tablespace moet namelijk uniek zijn in een database – als bijvoorbeeld de naam van de tablespace in de doelomgeving al in gebruik is, moet je eerst zorgen dat de bron tablespaces een andere naam krijgen. En zo zijn er nog wat details waar je gaandeweg snel genoeg handigheid in krijgt. Gelukkig heb je daar meer dan genoeg tijd voor, aangezien je voortaan dankzij transportable tablespaces geen tijd meer kwijt bent aan onnodig lange recoveryprocessen.
