Export/Import จาก version สูงกว่า ไป version ต่ำกว่า

หากต้องการ เอาข้อมูลจาก oracle ที่ version สูงกว่า ไปลง version ต่ำกว่า

ใน version 10g ขึ้นไป ทุกคนจะรู้จัก DATA PUMP(expdp/impdp) 

ในบทความนี้ จะแสดงตัวอย่าง การนำ table จาก 10.2. to 10.1

option ที่สำคัญ คือ version, ซึ่งคือ version (compatible) ของ database ที่เราจะ import ลง

เตรียม parfile สำหรับ export

parfile:

DIRECTORY=DIR

dumpfile=01.dmp

logfile=01.log

tables=t01

VERSION=10.1.0

$ expdp parfile=parfile

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "USERNAME"."SYS_EXPORT_TABLE_01":  USERNAME/******** parfile=parfile 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 15 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "USERNAME"."T01"                       4.921 KB       1 rows

Master table "USERNAME"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for USERNAME.SYS_EXPORT_TABLE_01 is:

  /tmp/01.dmp

Job "USERNAME"."SYS_EXPORT_TABLE_01" successfully completed at 16:45:05

เมื่อได้ dump file จากนั้นก็ เตรียม parfile สำหรับ import

parfile:

DIRECTORY=DIR

dumpfile=01.dmp

tables=t01

logfile=01.log

$ impdp parfile=parfile

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "USERNAME"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "USERNAME"."SYS_IMPORT_TABLE_01":  USERNAME/******** parfile=parfile 

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "USERNAME"."T01"                       4.921 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "USERNAME"."SYS_IMPORT_TABLE_01" successfully completed at 16:53

ถ้าไม่ได้ทำ compatible (version) ตั้งแต่ export จะพบ error ตอน import

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39142: incompatible version number x.y in dump file ...

ใช้ "UPDATE GLOBAL INDEXES" "UPDATE INDEXES"

เมื่อเรามี การ add/move/split ... อะไรก็ตามที่ กระทำให้ data มีการเปลี่ยน partitions indexes:
เราควรสนใจ option: "UPDATE GLOBAL INDEXES" with global "UPDATE INDEXES" with local

นี่เป็นตัวอย่าง คร่าวๆ กับ hash table + gloabal index

CREATE TABLE TEMP_PAR01

     (no NUMBER,

      data VARCHAR2 (50))

   PARTITION BY HASH (no)

   PARTITIONS 5;

CREATE INDEX temp01_global_par_idx ON temp_par01(no)

GLOBAL PARTITION BY hash(no)

PARTITIONS 5;

alter table temp_par01 add partition p6;

analyze table temp_par01 compute statistics;

ไม่พบ error ใดๆ ทั้งสิ้นเพราะยังไม่มีข้อมูล

ใส่ข้อมูลเพื่อทดสอบ:

begin

 for x in 1 .. 1000 

  loop 

   insert into TEMP_PAR01 (no) values (x);

  end loop;

end;

/

commit;

ลอง เพิ่ม partition อีกครั้ง

alter table temp_par01 add partition p7;

analyze table temp_par01 compute statistics;

analyze table temp_par01 compute statistics

*

ERROR at line 1:

ORA-01502: index 'SCOTT.TEMP01_GLOBAL_PAR_IDX' or partition of such index is in unusable state

พบว่า partitions indexes กลายเป็น unusable state

select partition_name, status  from user_ind_partitions 

where index_name='TEMP01_GLOBAL_PAR_IDX';

PARTITION_NAME                 STATUS

------------------------------ --------

SYS_P62                        UNUSABLE

SYS_P63                        UNUSABLE

SYS_P64                        UNUSABLE

SYS_P65                        UNUSABLE

SYS_P61                        UNUSABLE

ดังนั้นลองใช้ "UPDATE GLOBAL INDEXES"

Use "UPDATE GLOBAL INDEXES" with global "UPDATE INDEXES" with local 

alter table temp_par01 add partition p8 UPDATE GLOBAL INDEXES;

analyze table temp_par01 compute statistics;

ไม่เจอ error:

select partition_name, status  from user_ind_partitions where index_name='TEMP01_GLOBAL_PAR_IDX';

PARTITION_NAME                 STATUS

------------------------------ --------

SYS_P62                        USABLE

SYS_P63                        USABLE

SYS_P64                        USABLE

SYS_P65                        USABLE

SYS_P61                        USABLE

สรุปได้ว่า ขณะที่มีการ เพิ่ม partition tables ก็ได้มีการ update ที่ indexes ด้วย
ข้อดีตรงนี้ ช่วยให้เรามั่นใจ ได้ว่า indexes ที่้ใช้กลายเป็น unusable state

อย่างไร เมื่อมีการกระทำกับ partitions tables อย่าลืม check status ของ partitions indexes ด้วย 

 ถ้าพบ unusable state ก็ทำการ rebuild partitions indexes นั้นๆไป 

ALTER INDEX  &lt index_name &gt REBUILD PARTITION &lt partition_name&gt 

ตัวอย่าง variable on SQL Statement

หลายคนทราบกันดีว่า library cache นั้นเก็บข้อมูลเกี่ยวกับ sql statements + pl/sql
ดังนั้น ทุกๆ sql ที่เราเรียกใช้งานจะเข้าไปในกระบวนการ optimization แล้วจะเก็บไว้ใน library cache
แล้วจะเอาออกจาก memory ด้วย lru algorithm.

แต่ที่พูดมาทั้งนั้น และทั้งนี้ ถ้าเรา ลดภาระ การ optimization + การเก็บ sql ใน Library cache นั้นคงดี

ผมจึงนำเสนอ การใช้ variable ก็ตัวแปรนี่เอง

ตัวอย่าง 1

log user:

SQL> select * from test01 where object_id=100;

no rows selected

SQL> select * from test01 where object_id=101;

no rows selected

จากนั้น Login ด้วย sys เพื่อต้องการ query V$SQL

SQL> select sql_text,sql_id from v$sql where lower(sql_text) like 'select% from test01%';

SQL_TEXT SQL_ID
-------------------------------------------------- -------------
select * from test01 where object_id=100 42pxm51pbj3gq
select * from test01 where object_id=101 fw9x60q5qbr7z


เราจะพบว่าจาก sql ตอนแรกนั้นได้มีการเก็บเข้าไปใน memory ถึงสองครั้ง
และแน่นอน ว่าต้องมีการ Optimization 2 ครั้งเช่นกัน ลักษณะแบบนี้เขาเรียกกันว่า hard parse

ตัวอย่าง 2

ก่อนอื่นใช้ sys user -> flush shared_pool

SQL> alter system flush shared_pool;

System altered.

login user:


SQL> var A number
SQL> exec :A := 100

PL/SQL procedure successfully completed.

SQL> select * from test01 where object_id=:A;

no rows selected

SQL> exec :A := 101

PL/SQL procedure successfully completed.

SQL> select * from test01 where object_id=:A;

no rows selected

จากนั้นใช้ sys user เพื่อเข้าไป เช็คอีกครั้ง

SQL> select sql_text,sql_id from v$sql where lower(sql_text) like 'select% from test01%'

SQL_TEXT SQL_ID
-------------------------------------------------- -------------
select * from test01 where object_id=:A dtruvsaj9k5xp

จากผลลัพท์ที่เห็น ทำให้รู้ว่า ทั้งสอง statement นั้น จะมีการ optimization ครั้งแรกครั้งเดียว
พอครั้งที่ สอง oracle พบใน memory และนำมาใช้ได้เลย แบบนี้ statement ตัวที่สอง ทำการ soft parse

ซึ่งจะประหยัด CPU และ process ในการคิดของ database

ตัวอย่าง +ASM + Virtual Disk on linux

ใช้ "root" user (สร้าง virtual disk) ->

# mkdir /u01/asmtest
# chown oracle:dba /u01/asmtest/
# su - oracle
$ dd if=/dev/zero of=/u01/asmtest/file_disk1 bs=2k count=100000
100000+0 records in
100000+0 records out

ใช้ root user (associate a loop device with a file) ->

# losetup /dev/loop1 /u01/asmtest/file_disk1


ใช้ root user (bind raw device) ->

# raw /dev/raw/raw10 /dev/loop1
/dev/raw/raw10: bound to major 7, minor 1


Check permission :
# ls -ltr /dev/raw/raw10
crw-rw---- 1 oracle dba 162, 10 Nov 4 12:04 /dev/raw/raw10


use oracle user ->

$ export ORACLE_BASE=/u01/oracle/product
$ export ORACLE_HOME=/u01/oracle/product/10.2.0/asm
$ export ORACLE_SID=+ASM
$ export PATH=$ORACLE_HOME/bin:$PATH
$ sqlplus / as sysdba

SQL> select group_number, disk_number, mount_status, header_status, state, path from v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STA STATE PATH
------------ ----------- ------- ---------- -------- --------------------
0 1 CLOSED CANDIDATE NORMAL /dev/raw/raw10



SQL> CREATE DISKGROUP tmp_test EXTERNAL REDUNDANCY DISK '/dev/raw/raw10';

Diskgroup created.

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH
------------ ----------- ------- ------------ -------- ---------------
3 0 CACHED MEMBER NORMAL /dev/raw/raw10



SQL> select group_number, name, total_mb, free_mb, state, type from v$asm_diskgroup;

GROUP_NUMBER NAME TOTAL_MB FREE_MB STATE TYPE
------------ ------------------------------ ---------- ---------- ----------- ------
3 TMP_TEST 195 145 MOUNTED EXTERN

ตอนนี้เราก็ ได้ diskgroup จาก virtual disk แล้ว

การใช้ oradebug เพื่อ enable trace file

ก่อนอื่นต้องอธิบาย event# 10046
เพราะจริงแล้ว event# 10046 มีความหมายเท่ากับ SQL_TRACE=TRUE
Event# 10046 นั้น มีหลาย level ด้วยกัน

1 - Enable standard SQL_TRACE functionality (Default)
4 - Level 1 + trace bind values
8 - Level 1 + trace waits
This is especially useful for spotting latch wait etc.
but can also be used to spot full table scans and index scans.
12 – Level both trace bind values and waits


ตัวอย่าง:

$ sqlplus / as sysdba

ต้อง การ os process ใน Username ที่ต้องการ

SQL> select p.spid,s.username from v$process p, v$session s where p.ADDR = s.PADDR and s.username = 'SCOTT';

SPID USERNAME
------------------------ ------------------------------
12925 SCOTT

$ ps -ef | grep 12925
oracle 12925 12924 0 15:07 ? 00:00:00 oracleSID (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

หลังจากได้ os process ก็เริ่ม ใช้ Oradebug (อย่างไรก็ตาม เราจะหาtrace file PATH ที่เกิดขึ้น -> show parameter user_dump_dest )


SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID Set OS pid of process to debug
SETORAPID ['force'] Set Oracle pid of process to debug
SETORAPNAME Set Oracle process name to debug
SHORT_STACK Get abridged OS stack
CURRENT_SQL Get current SQL
DUMP [addr] Invoke named dump
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT Set trace event in process
SESSION_EVENT Set trace event in session
DUMPVAR

[level] Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE

Print/dump an address with type info
SETVAR

Modify a fixed PGA/SGA/UGA variable
PEEK [level] Print/Dump memory
POKE Modify memory
WAKEUP Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G Parallel oradebug command prefix
-R Parallel oradebug prefix (return output
SETINST Set instance list in double quotes
SGATOFILE Dump SGA to file; dirname in double quotes
DMPCOWSGA Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS Helps translate PCs to names
WATCH

Watch a region of memory
DELETE watchpoint Delete a watchpoint
SHOW watchpoints Show watchpoints
DIRECT_ACCESS Fixed table access
CORE Dump core without crashing process
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL [-t count] [arg1]...[argn] Invoke function with arguments


เริ่มจับ process ที่ต้องการ

SQL> oradebug setospid 12925
Oracle pid: 29, Unix process pid: 12925, image: oracle@testhost (TNS V1-V3)

เริ่ม enable trace file

SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.


เราจะพบไฟล์ SID_ora_12925.trc, หลัง จากนั้นเราสามารถใช้ tkprof command ในการ analyze SQL statement ใน session นี้ได้

หากต้องการยกเลิก trace ใน event# 10046 นี้

SQL> oradebug setospid 12925
Oracle pid: 29, Unix process pid: 12925, image: oracle@testhost (TNS V1-V3)

ยกเลิก enable trace file

SQL> oradebug event 10046 trace name context off
Statement processed.

ORA-00312: online log 1 thread 1 ... and resolve for Today

Oracle TEST
V. 11.1.0.6.0

ไม่สามารถ start database


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 100 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 1 thread 1: '/oradata/testdb2/redo01.log'

ดังนั้น recover database:

SQL> recover database until cancel;
Media recovery complete.

SQL> ALTER DATABASE OPEN RESETLOGS;

ตัวอย่าง ORA-00313 เมื่อบาง member ใน group ถูกลบ

SQL> alter database add logfile member '/tmp/redo033.log' to group 3;

Database altered.

SQL> ho
$ ls -la /tmp/redo033.log
-rw-r----- 1 oracle oinstall 52429312 May 19 17:13 /tmp/redo033.log


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 436 52428800 2 YES ACTIVE 32611337 19-MAY-08
2 1 437 52428800 2 NO CURRENT 32611361 19-MAY-08
3 1 435 52428800 3 YES INACTIVE 32611335 19-MAY-08


$ rm /tmp/redo033.log

SQL> alter system switch logfile;
-----------
NO ERROR:
-----------

Check alert log:

Errors in file testdb_arc0_25296.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/tmp/redo033.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory


SQL> alter database drop logfile member '/tmp/redo033.log';

Database altered.

SQL> alter database add logfile member '/tmp/redo033.log' to group 3;

Database altered.


------------------------------


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 463 52428800 2 YES ACTIVE 32612535 19-MAY-08
2 1 462 52428800 2 YES ACTIVE 32612521 19-MAY-08
3 1 464 52428800 3 NO CURRENT 32612545 19-MAY-08

SQL> ho

$ rm /tmp/redo033.log


SQL> alter system switch logfile;
-----------
NO ERROR:
-----------

Check alert log:
Errors in file testdb_m000_14312.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/tmp/redo033.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thread 1 cannot allocate new log, sequence 466


SQL> alter database drop logfile member '/tmp/redo033.log';

Database altered.

SQL> alter database add logfile member '/tmp/redo033.log' to group 3;

Database altered.

--------------------------------

อยากรู้ว่า Oracle Database ใช้spfile หรือไม่

ตอบง่ายๆ ว่านี่ไง
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/testdb/spfiletestdb.ora

จากนั้นก็เกิดคำถามว่ามีวิธีอื่นอีกไหมละนี่
มีครับ เราสามารถเช็คได้ที่ V$SPPARAMETER View ตัวนี้เลยครับ

Column	Datatype	Description
SID	VARCHAR2(80)	SID for which the parameter is defined
NAME	VARCHAR2(80)	Name of the parameter
VALUE	VARCHAR2(255)	Parameter value (null if a server parameter file was not used to start the instance)
DISPLAY_VALUE	VARCHAR2(255)	Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.
ISSPECIFIED	VARCHAR2(6)	Indicates whether the parameter was specified in the server parameter file (TRUE) or not (FALSE)
ORDINAL	NUMBER	Position (ordinal number) of the parameter value (0 if a server parameter file was not used to start the instance). Useful only for parameters whose values are lists of strings.
UPDATE_COMMENT	VARCHAR2(255)	Comments associated with the most recent update (null if a server parameter file was not used to start the instance)

V$SPPARAMETER จะบอกข้อมูลเกี่ยวกับ spfile, แล้วจะใช้ไม่ใช้ spfile ตอน startup database นี่ ดูที่ ISSPECIFIED .

ตัวอย่าง query

SQL> select isspecified, count(*) from v$spparameter group by
isspecified;

ISSPEC COUNT(*)
------ ----------
TRUE 40
FALSE 254

ผลลัพธ์คือใช้ spfile ครับ
ทำไมละครับ ก็เพราะว่ามี บางค่าใน view มี ISSPECIFIED = TRUE
แล้วถ้าไม่ใช้ละ

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=/tmp/pfile;
ORACLE instance started.

Total System Global Area 1703624704 bytes
Fixed Size 2145064 bytes
Variable Size 1291846872 bytes
Database Buffers 402653184 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.

SQL> select isspecified, count(*) from v$spparameter group by
isspecified;

ISSPEC COUNT(*)
------ ----------
FALSE 289
ไม่มี TRUE ตรงนี้เลยไม่ใช้ spfile ครับ

---> กลับไปใช้ spfile:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1703624704 bytes
Fixed Size 2145064 bytes
Variable Size 1291846872 bytes
Database Buffers 402653184 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.

SQL> select isspecified, count(*) from v$spparameter group by
isspecified; 2

ISSPEC COUNT(*)
------ ----------
TRUE 40
FALSE 254

หรือนี่เลยครับ

SQL> select decode(count(*), 1, 'spfile', 'pfile' )
from v$spparameter
where rownum=1
and isspecified='TRUE';

DECODE
------
spfile



Enjoy!

โครงสร้าง Oracle Memory

Oracle memory จะเก็บข้อมูลเกี่ยวกับ:
- โค้ดโปรแกรม
- ข้อมูลเกี่ยวกับ session ที่ได้มีการติดต่อเข้าไป
- ข้อมูลระหว่างมีการใช้โปรแกรม เช่น state
- ข้อมูลที่ใช้แชร์ และการติดต่อ ที่เกิดกับ Oracle process นั้นๆ
- Cache ข้อมูล เช่น data blocks

โครงสร้าง Memory:
- System Global Area (SGA) แชร์ server ทั้งหมด และ background processes.
- Program Global Area (PGA) แชร์แต่ละ server และ background process; 1 PGA สำหรับแต่ละ process.

ต้องการ query เอาข้อมูล max อันดับที่ 3 เท่านั้น

สร้าง table เพื่อต้องการ test table:

SQL> create table test as select * from all_objects;
Table created.

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)

ข้อมูลที่อยากได้ คือ 80971 บน object_id (max อันดัยที่ 3):

SQL> select object_name ,object_id from test;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
DBA_TABLESPACES 4641
DBA_TEMP_FILES 4642
.
.
.
WRH$_EVENT_HISTOGRAM_PK 80971
WRH$_EVENT_HISTOGRAM_PK 80972
TEST 80990

จากนั้นเป็น step ในการหา (ใช้ rownum เข้ามาช่วย):

SQL> select * from (select object_name , object_id from test order by object_id desc) where rownum <=3 ;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
TEST 80990
WRH$_EVENT_HISTOGRAM_PK 80972
WRH$_EVENT_HISTOGRAM_PK 80971

SQL> select * from (select object_name , object_id from test order by object_id desc) where rownum <=3 order by object_id;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
WRH$_EVENT_HISTOGRAM_PK 80971
WRH$_EVENT_HISTOGRAM_PK 80972
TEST 80990

SQL> select * from (select * from (select object_name , object_id from test order by object_id desc) where rownum <=3 order by object_id) where rownum <=1;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
WRH$_EVENT_HISTOGRAM_PK 80971

ในที่สุดก็ได้ข้อมูลที่ต้องการ