Sunday, May 4, 2014

การแสดงผลแบบ Transpose ข้อมูลจาก Column มาเป็น Row

โพสต์ครั้งแรก: 28 พฤศจิกายน 2010


ในบางกรณีเราอาจจะต้องการ แสดงผลข้อมูลที่เรียงตัวอยู่เป็นคอลัมน์ให้กลับมาเป็นแถว อย่างเช่นในกรณีที่ เราสร้างตารางที่เป็นเหมือนเทมเพลตเอาไว้เก็บค่าซึ่งจะใช้เป็นอัตราคูณกับค่าในตารางที่แสดงรายการธุรกรรม อย่างเช่นถ้าเรามีตารางเทมเพลต TAX_RATE

SQL> create table tax_rate (country varchar2(30), col1 number, col2 number, col3 number);

Table created

SQL> insert into tax_rate values ('THAILAND',7,3,15);

1 row inserted
SQL> select * from tax_rate;

COUNTRY                    COL1       COL2       COL3
-------------------- ---------- ---------- ----------
THAILAND                      7          3         15


โดยที่เราไม่ได้ระบุชื่อคอลัมน์ไว้ เนื่องจากข้อมูลในแต่ละแถวอาจจะมีความหมายของแต่ละคอลัมน์ต่าง ๆ กัน หรือด้วยเหตุุผลอื่น ๆ
เรามีตารางธุรกรรมที่อ้างถึงอัตราในตารางเทมเพลต TAX_RATE ของเรา โดยการอ้างชื่อคอลัมน์แทนที่จะเป็นชื่อแถว (โดยใช้ Primary Key หรือ Unique Key) เช่น

SQL> create table product_tax (country varchar2(30),product_type varchar2(30),tax_column varchar2(5),price number);

Table created

SQL> insert into product_tax values
('THAILAND','VEHICAL','COL3',100000);

1 row inserted

SQL> insert into product_tax values
('THAILAND','SPIRITS','COL1',300);

1 row inserted

SQL> insert into product_tax values
('THAILAND','MEDICAL','COL2',100);

1 row inserted
SQL> commit;

Commit complete
SQL> select * from product_tax;

COUNTRY             PRODUCT_TYPE            TAX_COLUMN      PRICE
------------------- ----------------------- ---------- ----------
THAILAND            VEHICAL                 COL3           100000
THAILAND            SPIRITS                 COL1              300
THAILAND            MEDICAL                 COL2              100

ถ้าเราต้องการข้อมูลแบบนี้เราจะเขียนคิวรียังไง?
COUNTRY                        PRODUCT_TYPE                   TAX_RATE        PRICE
------------------------------ ------------------------------ ---------- ----------
THAILAND                       VEHICAL                                15     100000
THAILAND                       SPIRITS                                 7        300
THAILAND                       MEDICAL                                 3        100
ประเด็นคือเรารู้ว่าเราสามารถเอา TAX_RATE มา Join กับ PRODUCT_TAX ได้ ถ้า TAX_RATE อยู่ในแนวตั้งในรูปของ TAX_RATE{COUNTRY,COL,RATE}
COUNTRY                        COL              RATE
------------------------------ ---------- ----------
THAILAND                       COL1                7
THAILAND                       COL2                3
THAILAND                       COL3               15
ถ้าการออกแบบตารางมีข้อจำกัดด้วยประการใด ๆ เราอาจจะต้องหาทางกลับเอาข้อมูลที่ยึดกับคอลัมน์มาเป็นแบบแถวแทน (Transpose Column to Row)

ผลคูณคาร์ทีเชียนของ ROWNUM กับการ DECODE
โดยตัวของตารางเองแล้วไม่มีคิวรีใด ๆ ที่สามารถจะจับเอาข้อมูลมากลับด้านเช่นนั้นได้ เราจำเป็นต้องใช้ตารางที่เป็นแนวตั้งมา Join กับมันเพื่อดึงค่าของมันลงมาเป็นแนวแถว เราจะใช้ตารางวิธีการ Select Rownum จากตารางใด ๆ ที่มีจำนวนแถวมากพอกับจำนวนคอลัมน์ที่เราต้องการดึงลงมา เราจะใช้ตาราง ALL_OBJECTS ซึ่งเป็น Data Dictionary View ซึ่งมีอยู่ในทุก ๆ Schema มีจำนวนแถวประมาณสองหมื่นแถวหรือมากกว่า เราเริ่มต้นด้วยคิวรี
SQL> select rownum r from all_objects where rownum <=5;

R
----------
1
2
3
4
5

ROWNUM เป็น Pseudo Column ไม่มีอยู่จริงในตาราง เราสามารถใช้ ROWNUM เพื่อแสดง Running Number ของแต่ละแถวข้อมูลได้

จากนั้นเราจะทำคาร์ทีเชียนระหว่างวิว Select Rownum กับตาราง TAX_RATE โดยใช้ DECODE ในการกำหนดว่าถ้าเป็นแถวที่ 1 ให้แสดงผลจาก COL1, แถวที่ 2 ให้แสดงผลจาก COL2 ฯลฯ
SQL> select t.country,r,decode(r,1,t.col1,2,t.col2,3,t.col3) as rate from
2 tax_rate t,
3 (select rownum r from all_objects where rownum <=5)
4 ;

COUNTRY                                 R       RATE
------------------------------ ---------- ----------
THAILAND                                1          7
THAILAND                                2          3
THAILAND                                3         15
THAILAND                                4
THAILAND                                5

ถ้าเราปรับแต่งนิดนึงโดยการ Concatenate คอลัมน์ 'R' ด้วย String 'COL' เราก็จะสามารถนำคิวรีที่เราสร้างขึ้นมาไป Join กับตาราง PRODUCT_TAX ได้แล้วดังนี้
SQL> select pt.country,pt.product_type,tr.rate,pt.price
2 from
3 product_tax pt,
4 (
5 select t.country,'COL'||r as col,decode(r,1,t.col1,2,t.col2,3,t.col3) as rate from
6 tax_rate t,
7 (select rownum r from all_objects where rownum <=5)
8 ) tr
9 where pt.tax_rate = tr.col
10 /

COUNTRY         PRODUCT_TYPE               RATE      PRICE
--------------- -------------------- ---------- ----------
THAILAND        SPIRITS                       7        300
THAILAND        MEDICAL                       3        100
THAILAND        VEHICAL                      15       1000

ผลคูณคาร์ทีเชียน (Cartesian) ของสองตาราง คือการ Join ตารางสองตารางแบบไม่ใส่ Join Condition (ไม่มี Where Table1.Colx=Table2.Colx) จะให้ผลแบบ 'พบกันหมด' กล่าวคือจำนวนของแถวที่ได้จากการ Join แบบคาร์ทีเชียนจะเท่ากับผลคูณของจำนวนของแถวในแต่ละตาราง เช่น

Table1

COL1
----
1
2
3

TableACOLA
 
------
A
B


ถ้าเรา Join ทั้งสองตารางแบบคาร์ทีเชียนจะได้

SQL> select * from table1, tablea;
COL1 COLA
----- -----
1 A
1 B
2 A
2 B
3 A
3 B


โดยปกติผลที่ได้จากการ Join แบบคาร์ทีเชียนไ่ม่ค่อยจะมีความหมายนักในการทำงาน ข้อมูลที่ได้มักจะเป็นขยะ แต่ในบางกรณีก็สามารถนำมาใช้ประโยชน์ได้ เช่นกรณีนี้เป็นต้น



การใส่เงื่อนไขในการแสดงผลด้วย Decode

Decode เป็นฟังก์ชันตัวหนึ่งที่ทำหน้าที่กำหนดเงื่อนไขให้กับการแสดงค่าของคอลัมน์ เช่น DECODE(COL1,1,'ONE',2,'TWO','OTHERS') หมายถึงถ้าค่าในคอลัมน์ COL1 เท่ากับ 1 ก็ให้แสดงผลเป็น 'ONE' ถ้าเป็น 2 แสดงผลเป็น 'TWO' หรือถ้าเป็นตัวอื่น ๆ ก็ให้แสดงผลเป็น 'OTHERS' เป็นต้น

เปลี่ยนชื่อฐานข้อมูล

โพสต์ครั้งแรก: 28 มกราคม 2012 


เราจะเปลี่ยนชื่อฐานข้อมูลได้หรือเปล่า พรหล้า พรหล้า!พรหล้า SE หญิงคนเดียวในทีม ที่ทำหน้าที่ดูแลฐานข้อมูล Oracle อยู่ ต้องสะดุ้งจากภวังค์ "อะไรคะ ขออีกที จะเปลี่ยนชื่อผู้ดูแลฐานข้อมูลหรือคะ" เธออยากจะให้การฟังผิดของเธอเป็นเรื่องจริงเสียเหลือเกิน เพราะเท่าที่เธอเคยรู้การจะเปลี่ยนชื่อฐานข้อมูล Oracle มันค่อนข้างจะยุ่งยาก แล้วก็เสี่ยงกับการที่เบสจะเจ๊งอีกด้วย

ก่อนที่ Oracle จะเปิดตัวยูทิลิตี้ชื่อ DBNEWID การแก้ไข DBID เป็นเรื่องที่เป็นไปไม่ได้ และการเปลี่ยน DBNAME ก็หลีกเลี่ยงไม่ได้ที่จะต้องสร้าง Controlfile ขึ้นใหม่  แต่ในปัจจุบันเราสามารถใช้ DBNEWID ในการเปลี่ยน DBID และการแก้ไข DB_NAME ก็ง่ายขึ้น การเปลี่ยน DBID เป็นสิ่งที่จำเป็นเมื่อคุณต้องการใช้ Catalog ของ RMAN เพื่อแบ็คอัพ Database ที่ Clone มาเนื่องจาก RMAN จะรู้จักและระบุถึง Databaseแต่ละตัว โดยใช้ DBID, ฐานข้อมูลที่ Clone มาจะมี DBID เดียวกันกับตัวต้นฉบับ ทำให้ไม่สามารถใช้ Catalog ชุดเดียวกันในการจัดการฐานข้อมูลทั้งสองได้ ซึ่งการสามารถเปลี่ยน DBID ได้ ทำให้ข้อจำกัดนี้หายไป

ขั้นตอนการเปลี่ยน DBID และ DBNAME พร้อม ๆ กันด้วย DBNEWID
  • แบ็คอัพฐานข้อมูล
  • Shutdown แบบสะอาด ๆ
sys@ORCL92> startup mount
sys@ORCL92> shutdown immediate
  • รันยูทิลิตี้ DBNEWID (nid) บน OS Prompt โดยระบุ DBNAME ตัวใหม่ ในคำสั่งนี้เราจะต้องใช้ Username และ Password ที่มีสิทธิ์ SYSDBA ด้วย
C:\> nid TARGET=sys/password@ORCL92 DBNAME=PROD
  • ถ้าไม่มีปัญหาอะไร จะมี Prompt เพื่อให้เราตอบยืนยันตอบ "Y" เพื่อยืนยัน จะได้ Output คล้าย ๆ ข้างล่าง
C:\> nid TARGET=sys@ORCL92 DBNAME=PROD
DBNEWID: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

Password:
Connected to database ORCL92 (DBID=2529228049)

Control Files in database:
C:\ORACLE\ORADATA\ORCL92\CONTROL01.CTL
C:\ORACLE\ORADATA\ORCL92\CONTROL02.CTL
C:\ORACLE\ORADATA\ORCL92\CONTROL03.CTL

Change database ID and database name ORCL92 to PROD? (Y/[N]) =>Y

Proceeding with operation
Changing database ID from 2529228049 to 185608941
Changing database name from ORCL92 to PROD
Control File C:\ORACLE\ORADATA\ORCL92\CONTROL01.CTL - modified
Control File C:\ORACLE\ORADATA\ORCL92\CONTROL02.CTL - modified
Control File C:\ORACLE\ORADATA\ORCL92\CONTROL03.CTL - modified
Datafile C:\ORACLE\ORADATA\ORCL92\SYSTEM01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\TESTBLOCK.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\CWMLITE01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\DRSYS01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\EXAMPLE01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\INDX01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\ODM01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\TOOLS01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\USERS01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\XDB01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\LOGMNR_TS01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\PERFSTAT01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\TEST_IMP01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile C:\TEMP1.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\ORCL92\TEMP02.DBF - dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\ORCL92\CONTROL01.CTL - dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\ORCL92\CONTROL02.CTL - dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\ORCL92\CONTROL03.CTL - dbid changed, wrote new name

Database name changed to PROD.
Modify parameter file and generate a new password file before restarting.
Database ID for database PROD changed to 185608941.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
  • ล็อกอินเข้าฐานข้อมูลอีกครั้ง แล้ว Shutdown ฐานข้อมูล
sys@ORCL92> shutdown immediate
  • แก้ไขพารามิเตอร์ DB_NAME ในไฟล์พารามิเตอร์ (Initialization Parameter File) ให้ตรงกับชื่อฐานข้อมูลที่เราต้องการ แล้ว Startup Mount ตอน Startup อาจจะมี Error แต่ไม่ต้องสนใจ
sys@ORCL92> startup mount
ORACLE instance started.

Total System Global Area 839984040 bytes
Fixed Size 455592 bytes
Variable Size 419430400 bytes
Database Buffers 419430400 bytes
Redo Buffers 667648 bytes
ORA-01103: database name 'PROD' in controlfile is not 'ORCL92'
  • แก้ไข DB_NAME ใน SPFile โดยใช้คำสั่ง alter และ Shutdown ฐานข้อมูลอีกครั้ง
sys@ORCL92> alter system set db_name=PROD scope=spfile;
sys@ORCL92> shutdown immediate
  • สร้าง Password ไฟล์ใหม่ (ไฟล์ Password ใช้ในการเก็บพาสเวิร์ดของ SYSDBA กรณีที่อนุญาตให้เข้าระบบด้วย SYSDBA จากเครื่อง Client)
c:\> orapwd file=c:\oracle\ora92\database\PWDprod.ora password=manager entries=5
  • ให้มั่นใจว่าพาธที่กำหนดไปยังไฟล์ Password เป็นพาธที่เป็น Oracle Home "\database" (Windows) หรือ "/dbs" (Unix) ส่วนชื่อต้องเป็นฟอร์แมตที่ถูกต้องด้วยคือ "PWDdb_name.ora" เราอาจจะดูจากไฟล์เดิมที่มีอยู่ซึ่งจะเป็นพาสเวิร์ดไฟล์เก่าของ DBNAME เดิมก็ได้
  • จากนั้นเปลี่ยนชื่อ (Rename) SPFile เพื่อให้ตรงกับ DB_NAME ใหม่, ใช่แล้ว Rename แบบดื้อ ๆ เลย
c:\> ren SPFILEORCL92.ORA SPFILEPROD.ORA
  • ถ้าคุณใช้ Windows คุณต้องสร้าง Service ใหม่ด้วยชื่อที่ถูกต้องเพื่อให้ใช้ Parameter File ตัวใหม่ ก่อนจะสร้าง Service ตัวใหม่ ให้ลบตัวเก่าออกก่อน
c:\> oradim -delete -sid prod
c:\> oradim -new -sid PROD -intpwd manager -startmode a -pfile c:\oracle\ora92\database\SPFILEPROD.ORA
  • กรณีที่เป็น Unix หรือ Linux เราแค่ Reset ORACLE_SID (Environment Variable)
$ ORACLE_SID=PROD; export ORACLE_SID
  • จากนั้นให้แก้ Configuration ที่เกี่ยวข้องกับชื่อฐานข้อมูลในไฟล์ที่ listener.ora และไฟล์ tnsnames.ora แล้ว Restart Listener, ไฟล์ทั้งสองปกติจะอยู่ใน %ORACLE_HOME%\network\admin รันคำสั่งข้างล่างบน OS Prompt หลัง Restart Listener ลองเช็คสถานะดู
c:\> lsnrctl reload
c:\> lsnrctl status
  • จากนั้นให้เปิดใช้ระบบฐานข้อมูล โดยจะต้องใช้ Option RESETLOGS การ Reset Logs จะทำให้ Redo Log ถูก Reset ตั้งค่า Sequence เป็น 1 ใหม่ เหมือนเริ่มต้นชีวิตกันใหม่
sys@ORCL92> startup mount
sys@ORCL92> alter database open resetlogs;
  • แบ็คอัพฐานข้อมูลทั้งหมด (แบ็คอัพตัวเก่าของคุณจะใช้ไม่ได้แล้วนะคร๊าบ)
เรียบเรียงจาก DBNEWID Utility, http://www.oracle-base.com/articles/9i/DBNEWID.phpอ่านเพิ่มเติมที่ DBNEWID Utility, Oracle9i Database Utilities Release 2 (9.2)

Drop Database

โพสต์ครั้งแรก: 15 มกราคม 2012


เราสามารถลบฐานข้อมูล (เฉพาะฐานข้อมูล ไม่รวมโปรแกรม Oracle หรือ Oracle Home)ได้ง่าย ๆ โดยใช้คำสั่ง DROP DATABASE ซึ่งเหมาะกับการลบฐานข้อมูลทดสอบ หรือลบฐานข้อมูลเก่าซึ่งได้ Migrate ไปตัวใหม่เรียบร้อยแล้วเป็นต้น
คุณไม่สามารถจะ Rollback ได้ เมื่อ Drop Database แล้ว


หากต้องการลบฐานข้อมูลรวมทั้งโปรแกรม Oracle ออกไปทั้งหมด ดูที่ Clone Oracle - ก๊อปปี้ฐานข้อมูลแบบยกชุด
คุณจะต้องมีสิทธิ์ SYSDBA ฐานข้อมูลจะต้องอยู่ในโหมด Mount และ Restrict
เมื่อคุณใช้คำสั่งนี้ Oracle จะ Drop ฐานข้อมูล และลบ Control Files และ Datafiles ที่มีชื่ออยุ่ใน Control Files ถ้าฐานข้อมูลใช้ SPFile, SPFile ก็จะถูก Drop ด้วย Archive Log และไฟล์แบ็คอัพ จะไม่ถูกลบ คุณจะต้องลบไฟล์เหล่านี้เอง
ลองเช็คตำแหน่งของไฟล์ต่าง ๆ ก่อน ว่าอยู่ที่ใดบ้าง โดยเริ่มจากตำแหน่งของไฟล์ Dump ต่าง ๆ
sys@ora10gR2> show parameter dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
background_dump_dest                 string      /data2/ora10gR2/bdump
core_dump_dest                       string      /data2/ora10gR2/cdump
user_dump_dest                       string      /data2/ora10gR2/udump

sys@ora10gR2> !ls /data2/ora10gR2/bdump
alert_ora10gR2.log       ora10gR2_lgwr_1778.trc   ...
...

sys@ora10gR2> !ls /data2/ora10gR2/udump
ora10gR2_ora_1259.trc  ora10gR2_ora_1364.trc  ...
...

sys@ora10gR2> !ls -la /data2/ora10gR2/cdump
total 9
drwxr-xr-x   3 oracle   dba            3 Dec  8 11:38 .
drwxr-xr-x   7 oracle   dba           19 Dec  8 11:32 ..
drwxr-x---   2 oracle   dba            3 Dec  8 11:38 core_1541


Output บางบรรทัดถูกละไว้เพื่อไม่ให้เยิ่นเย้อ ต่อไปเช็คตำแหน่งของ Archive Logs ผมเก็บ Archive Logs ไว้ใน Flash Recovery Area ซึ่งเป็นค่าดีฟอลต์อยู่แล้ว ในไดเรคทอรี archivelog ของผมมีไดเรคทอรี 2012_01_15 ซึ่งข้างในเก็บไฟล์ Archive Log

sys@ora10gR2> select name,value from v$parameter where name ='db_recovery_file_dest';

NAME                           VALUE
------------------------------ ----------------------------------------
db_recovery_file_dest          /data2/ora10gR2/flash_recovery_area

sys@ora10gR2> !ls /data2/ora10gR2/flash_recovery_area/ora10gR2/archivelog
2012_01_15

เช็คตำแหน่ง Datafiles และ Controlfiles ตำแหน่งของไฟล์ต่าง ๆ อาจจะต่างกันไปในแต่ละเครื่อง

sys@ora10gR2> ! ls -l
total 1504167
drwxr-xr-x 2 oracle dba 61 Dec 10 12:29 adump
drwxr-xr-x 2 oracle dba 161 Dec 27 00:00 bdump
drwxr-xr-x 3 oracle dba 3 Dec 8 11:38 cdump
-rw-r----- 1 oracle dba 14401536 Dec 17 00:24 control01.ctl
-rw-r----- 1 oracle dba 14401536 Dec 17 00:24 control02.ctl
-rw-r----- 1 oracle dba 14401536 Dec 17 00:24 control03.ctl
-rw-r--r-- 1 oracle dba 760 Dec 2 20:12 creatdb.sql
drwxr-xr-x 2 oracle dba 2 Dec 2 20:07 flash_recovery_area
-rw-r--r-- 1 oracle dba 2760 Dec 8 14:21 initthaidb.ora
-rw-r----- 1 oracle dba 52429312 Dec 17 00:22 thaidb_redo111.rdo
-rw-r----- 1 oracle dba 52429312 Dec 18 06:00 thaidb_redo112.rdo
-rw-r----- 1 oracle dba 52429312 Dec 25 00:21 thaidb_redo113.rdo
-rw-r----- 1 oracle dba 84025344 Dec 27 00:41 thaidb_sysaux0000.dbf
-rw-r----- 1 oracle dba 254156800 Dec 17 00:22 thaidb_system0000.dbf
-rw-r----- 1 oracle dba 10493952 Dec 24 11:34 thaidb_temp0000.dbf
-rw-r----- 1 oracle dba 220536832 Dec 17 00:22 thaidb_undo1000.dbf
drwxr-xr-x 2 oracle dba 41 Dec 10 04:52 udump

เช็คไฟล์ SPFile, SPFile จะถูกลบด้วยเมื่อเรา Drop Database
$ ls $ORACLE_HOME/dbs | grep -i ora10gR2.ora
spfileora10gR2.ora


เริ่มต้น Drop ฐานข้อมูล ล็อกอินเข้าฐานข้อมูลด้วย User ที่มีสิทธิ์ SYSDBA แล้ว Shutdown Immediate

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Jan 15 11:15:11 2012

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Shutdown ฐานข้อมูล
sys@ora10gR2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup ฐานข้อมูลในโหมด Mount Restrict
sys@ora10gR2> startup restrict mount
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1279336 bytes
Variable Size             104860312 bytes
Database Buffers           96468992 bytes
Redo Buffers                7106560 bytes
Database mounted.

คราวนี้ก็มาถึงตัวสำคัญ รันคำสั่ง DROP DATABASE บน SQL Prompt เพื่อ Drop ฐานข้อมูล ระวัง! ขั้นตอนนี้ย้อนกลับไม่ได้นะครับ
sys@ora10gR2> drop database;
ERROR:
ORA-01034: ORACLE not available

Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10gR2> exit
ไปดูไฟล์ใน Dump Directory กัน
$ ls /data2/ora10gR2/bdump | wc -l
169
$ ls /data2/ora10gR2/udump | wc -l
49
$ ls /data2/ora10gR2/cdump/core_1541 | wc -l
1
ไฟล์ที่เป็น Archive Log
$ ls /data2/ora10gR2/flash_recovery_area/ora10gR2/archivelog/2012_01_15
o1_mf_1_23_7k4mv22o_.arc
ทั้งไฟล์ Dump และไฟล์ Archive Log ต่างก็ยังอยู่ คุณจะต้องเข้าไปลบเอง ในทางกลับกันคราวนี้ไปดู Datafiles และ Controlfiles จะเห็นได้ว่าถูกลบไปแล้ว
$ ls /data2/ora10gR2
adump                cdump                flash_recovery_area  udump
bdump                creatdb.sql          initora10gR2.ora
$ ls $ORACLE_HOME/dbs | grep -i ora10gR2.ora
เดิมไดเรคทอรีนี้มี Datafiles และ Controlfiles อยุ่ ตอนนี้ไม่มีแล้ว เช่นเดียวกับ SPFile ซึ่งตอนนี้ถูกลบไปแล้ว

นอกจากนี้ยังมีไฟล์ listener.ora และ tnsnames.ora ซึ่งจะต้องลบเอาส่วนของฐานข้อมูลตัวที่ถูกลบนี้ออกไปด้วย

การเปลี่ยน Hostname และ IP Address บนเครื่อง Solaris10

โพสต์ครั้งแรก: 30 ธันวาคม 2011

ดูเพิ่มเติม bit.ly/uTdpl1


Originally Posted by shiv729
Changing the IP on Solaris 10 requires editing two files:

1. /etc/hosts
2. /etc/inet/ipnodes

If you are changing network address, you will need to change the router address in the file:
1. /etc/defaultrouter

Changing the hostname now only requires editing these files:

1. /etc/hosts
2. /etc/nodename
3. /etc/hostname.

is the driver name followed by the instance number of the interface. ie. hme0, bge0, ce0, qfe0

You can find all your network interfaces by drivername and instance# by running the following command: prtconf -D | grep network

Note: the following hosts files are no longer used in solaris 10.
/etc/net/ticlts/hosts
/etc/net/ticots/hosts
/etc/net/ticotsord/hosts

Rebooting the computer should bring up the new ip and hostname.

Clone Oracle - Copy ระบบฐานข้อมูลแบบยกชุด

โพสต์ครั้งแรก: 24 ธันวาคม 2511


บางครั้งบางคราว อาจไม่บ่อยนักที่เราจะต้องทำสำเนาทั้งฐานข้อมูล,ทั้งตัวโปรแกรมของ Oracle รวมทั้ง Configuration ทั้งหมดออกมา และนำไปติดตั้งไว้บนเครื่องอื่น (หรือเครื่องเดียวกัน) เพื่อวัตถุประสงค์บางอย่างเช่น การทำฐานข้อมูลทดสอบจากฐานข้อมูลตัวจริง (Production) โดยที่เราต้องการให้ฐานข้อมูลตัวใหม่ที่ทำสำเนาขึ้น มีคุณสมบัติทุกอย่างเหมือนกับฐานข้อมูลตัวต้นฉบับ ยกเว้นไว้แต่เพียงฮาร์ดแวร์เท่านั้นที่อาจจะแตกต่างกัน แม้ว่าการ Clone จะทำให้กระบวนการทำสำเนาทำได้อย่างรวดเร็ว และสะดวกมากขึ้น แต่ก็มีข้อควรระวังคือ
1) ไม่ควรใช้กับการ Install Production เนื่องจากการ Clone มีขั้นตอนค่อนข้างมาก และเป็นการทำแบบ Manual โอกาสผิดพลาดจึงมีสูง จึงไม่เหมาะที่จะใช้วิธีนี้ในการสร้างฐานข้อมูล Production
2) วิธีนี้ไม่ได้ครอบคลุมถึงระบบที่เป็น Cluster หรือเกี่ยวเนื่อง เช่น ASM หรือ RAC
3) เราตั้งสมมติฐานว่าเครื่องต้นทางกับปลายทางมีการเตรียมการติดตั้งไว้บน OS เหมือนกัน หมายถึงข้อกำหนดที่ต้องมีก่อนติดตั้ง Oracle ทั้งหลาย ได้จัดทำไว้แล้วบนเครื่องปลายทาง สิ่งที่ต่างกันจะมีเพียง Hostname และ IP Address เท่านั้น
4) มีวิธีอื่น ๆ ที่สามารถใช้ได้ในการ Clone เช่น Master Note For Cloning Oracle Database Server ORACLE_HOME's Using the Oracle Universal Installer (OUI) ซึ่งเป็นวิธีที่ได้รับการรับรองจาก Oracle

ขั้นตอนในการ Clone Oracle Database มีดังนี้
หยุดการทำงานของ Oracle
$ emctl stop dbconsole
$ dbshut $ORACLE_HOME

เช็คว่า Process ของ Instance กับ Listener ปิดไปหมดแล้วหรือยัง
$ ps -ef | grep pmon
oracle 1682 1507 0 13:16:21 pts/3 0:00 grep pmon <= ไม่มี Process PMON ของฐานข้อมูลแล้ว
$ ps -ef | grep lsnr
oracle 1684 1507 0 13:16:27 pts/3 0:00 grep lsnr <= ไม่มี Process ของ Listener แล้ว

สร้างไฟล์ TAR
ล็อกอินเป็น root แล้วทำการ TAR และ Zip ไดเรคทอรีของ Oracle ในที่นี้ทั้งโปรแกรม Oracle และ Datafile ทั้งหมดอยู่ใน /opt/oracle
# tar -cvf /tmp/clone.tar /opt/oracle
# gzip /data/clone.tar

ย้ายไฟล์ TAR ไปไว้ที่เครื่องปลายทาง
คุณสามารถใช้เครื่องมืออะไรในการย้ายไฟล์ก็ได้ ในที่นี้เราจะใช้ scp
# scp /data/clone.tar.gz root@192.168.25.101 :/data/clone.tar.gz

Unzip และ Extract ไฟล์ TAR
Unzip และดึงเอา Content ในไฟล์ TAR ออกมาไว้ที่พาธที่ต้องการในเครื่องปลายทาง ล็อกอินด้วย root แล้วรัน
# gunzip /data/clone.tar.gz
# cd /
# tar -xvf /data/clone.tar

ตรวจสอบความความเป็นเจ้าของ (Ownership)
ตรวจสอบความเป็นเจ้าของไดเรคทอรีที่เราเพิ่ง Extract ออกมา (/opt/oracle) ถ้าไม่ตรงกับ Ownership ของเครื่องต้นทางให้เปลี่ยน Ownership ให้ตรง
# chown -R oracle:dba /opt/oracle

รัน Root Configuration Scripts
ให้รัน Root Configuration Scripts ชื่อ orainstRoot.sh และ root.sh บนเครื่องปลายทาง ซึ่งเป็นการกำหนด Permission และสร้างไฟล์ Configuration ต่าง ๆ บน Host
รันคำสั่งข้างล่าง โดยยังคงล็อกอินเป็น root
# /opt/oracle/oraInventory/orainstRoot.sh
Changing permissions of /opt/oracle/oraInventory to 770.
Changing groupname of /opt/oracle/oraInventory to dba.
The execution of the script is complete
พาธที่เก็บไฟล์ orainstRoot.sh อาจจะต่างกันไปจากนี้บ้างนะครับ ขึ้นอยู่กับตอน Install เครื่องต้นทาง จากนั้นให้รันอีกไฟล์ครับชื่อ root.sh
bash-3.00# /opt/oracle/102/root.sh
Running Oracle 10g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /opt/oracle/102

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y Copying coraenv to /usr/local/bin ...

Creating /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
ถ้าบนเครื่องมีไดเรคทอรี dbhome, oraenv และ coraenv อยู่แล้ว (ดังตัวอย่างข้างบน เพราะอาจจะเคยมี Oracle อยู่ในระบบมาก่อน) ก็อนุญาตให้ Replace ไป

แก้ไข Environment Variable ในไฟล์ Config
ถ้าคุณยังไม่ได้เตรียมไฟล์ .bash_profile ไว้ ให้ Copy ไฟล์จากเครื่องต้นทางไปไว้ยังปลายทาง ที่ Home Directory ของ User ที่เป็นเจ้าของ Oracle
# scp /export/home/oracle/.bash_profile oracle@192.168.25.101:/export/home/oracle/./bash_profile
แก้ไขไฟล์ .bash_profile บนเครื่องปลายทาง ใส่ค่าใน Environment Variable ต่าง ๆ ให้ถูกต้อง
แก้ไขชื่อ Host และ IP ในไฟล์ listener.ora และ tnsnames.ora ในพาธ $ORACLE_HOME/network/admin ให้ถูกต้อง
แก้ไขไฟล์ /var/opt/oracle/oratab (พาธอาจจะต่างออกไปจากนี้บ้าง ขึ้นอยู่กับ OS ที่ใช้) เพื่อระบุว่าจะให้เปิดฐานข้อมูลตอนบูธเครื่องหรือไม่ (Y|N) เช่น
PAO1:/opt/oracle/102:Y

Start Oracle
เปิด Listener และ Database ล็อกอินโดยใช้ user ที่เป็นเจ้าของ Oracle (ปกติจะเป็น User ชื่อ oracle หรือเป็นผู้ที่ Install Oracle)
$ lsnrctl start
$ dbstart $ORACLE_HOME

การแก้ไข Configuration เพิ่มเติม
ถ้าคุณต้องการให้ฐานข้อมูลของคุณ Start โดยอัตโนมัติหลังเปิดเครื่องให้ใช้วิธีใน เปิด Oracle อัตโนมัติตอนบูธเครื่อง Solaris

อ้างอิง: Manually Cloning an Existing Oracle Database Installation on Linux, ORACLE-BASE, Tim Hall

เปิด Oracle อัตโนมัติตอนบูธเครื่อง Solaris

โพสต์ครั้งแรก: 18 ธันวาคม 2011

ปกติเมื่อเราติดตั้ง Oracle บนเครื่องตระกูล Unix และใช้งานไปได้สบาย ๆ สักพัก พอวันหนึ่งเกิดเราต้อง Restart เครื่อง Server แต่ปรากฎว่าเราพบว่า Database ที่เราติดตั้งไว้ไม่ได้ Start ขึ้นมาด้วยทำให้เราต้อง Restart ฐานข้อมูลขึ้นมาเอง บทความวันนี้จะขอพูดถึงวิธีการทำให้ Oracle Start โดยอัตโนมัติเมื่อเราเปิดเครื่อง Server ขึ้นมา ซึ่งรวมถึงการ Start Listener โดยอัตโนมัติด้วย

Oracle ได้เตรียมสคริปต์ไว้สองตัวที่ใช้ในการ Start และ Stop Oracle บน OS โดยไม่ต้องล็อกอินเข้าไปในฐานข้อมูลได้แก่

$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut


คราวนี้พอเรา Restart เครื่อง Server ตอนที่เครื่องกำลัง Restart ขึ้นมา มันจะไปอ่านสคริปต์ในพาธ /etc/rc3.d ชื่อสคริปต์นี้คือ Snnname (S ย่อมาจาก Start) โดย "nn" หมายถึงลำดับของการรันเช่น S01script1 จะรันก่อน S03script3

ขณะเดียวกันตอนปิดเครื่อง Server มันจะไปอ่านสคริปต์ในพาธ /etc/rc0.d โดยอ่านสคริปต์ชื่อ Knnname (K ย่อมาจาก Kill)

เช่นถ้าเราต้องการให้รันสคริปต์ชื่อ oracle ขึ้นเป็นตัวสุดท้ายตอน Start Server และให้รันเป็นตัวแรกตอน Shutdown Server ไฟล์สคริปต์บน OS จะเป็นแบบนี้ /etc/rc3.d/S99oracle และ /etc/rc0.d/K01oracle ตามลำดับ ปกติในพาธ /etc/rc3.d/ จะมีไฟล์สคริปต์อื่น ๆ อีก มารวม ๆ กันเพื่อให้ระบบอ่านตอน Startup เรามักจะกำหนดตัวขึ้นต้นชื่อว่า S99 เพื่อให้ Oracle ถูกเปิดเป็นตัวสุดท้าย (ไฟล์ S99... เป็น Shortcut ที่จะอ้างไปถึงไฟล์สคริปต์ dbora ซึ่งจะไปเรียกสคริปต์ dbstart ของ Oracle อีกที

อีกประการหนึ่งสคริปต์ dbstart และ dbshut จะไปอ่านว่าจะ Startup และ Shutdown ฐานข้อมูลตัวไหนจากไฟล์ชื่อ /var/opt/oracle/oratab

เรามาเริ่มกันเลยดีกว่าครับ ในตัวอย่างข้างล่างผมทำบนเครื่อง VMWare ที่มี OS เป็น Solaris10 ใช้ Oracle10g Release 10.2.0.2

1) ตรวจสอบไฟล์ oratab
เข้าไปที่ Terminal เปิดไฟล์ oratab เพือกำหนดว่าจะให้ฐานข้อมูลตัวใดเปิดบ้างตอน Start เครื่อง
$ vi /var/opt/oracle/oratab


หาบรรทัดที่มีลักษณะคล้าย ๆ ข้างล่างนี้
$ORACLE_SID:$ORACLE_HOME:[Y|N]
ให้ตรวจสอบว่าตรง [Y|N] นั้นมีค่าเป็น Y สำหรับฐานข้อมูลตัวที่เราต้องการให้ขึ้นโดยอัตโนมัติตอน Start เครื่อง เช่น PAO1:/opt/oracle/102:Y หมายความว่าถ้า Start เครื่องให้เอาฐานข้อมูลชื่อ PAO1 ที่มี Oracle Home อยู่ที่ "/opt/oracle/102" ขึ้นมาด้วย (Y) ถ้าเรามีมากกว่า 1 ฐานข้อมูล เราก็เพิ่มบรรทัดเข้าไปได้เลย เช่น


PAO1:/opt/oracle/102:Y
ORCL:/opt/oracle/102:Y

2) สร้างสคริปต์ไฟล์ dbora เพื่อเปิดปิดฐานข้อมูลอัตโนมัติ
ไฟล์นี้จะไปเรียกสคริปต์ของ Oracle ชื่อ dbstart (เพื่อเปิด) และ dbshut (เพื่อปิด) เราจะสร้างไฟล์นี้ไว้ที่พาธ "etc/init.d"

a) ล็อกอินเป็น root
b)เปลี่ยนไดเรคทอรีไปที่ /etc/init.d
c) สร้างไฟล์ dbora และ chmod เป็น 750
# touch dbora
# chmod 750 dbora

d) Copy และ Paste ข้อมูลข้างล่างลงในไฟล์ dbora และแก้ส่วนที่กำหนดพารามิเตอร์ ORACLE_HOME และ ORA_OWNER ให้ถูกต้อง

# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/opt/oracle/102
ORA_OWNER=oracle

if [! -f $ORA_HOME/bin/dbstart]
then
echo "Oracle startup: cannot start"
exit
fi

case "" in
'start')

# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart &
;;

'stop')

# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut $ORACLE_HOME &
;;

esac

3) สร้าง Symbolic Links
การสร้าง Symbolic Links เป็นคล้าย ๆ การสร้าง Shortcut เพื่อให้ระบบไปอ่านไฟล์สคริปต์ตัวที่เราต้องการตอนที่ Startup ขึ้นมามีขั้นตอนดังนี้
ขณะที่ยังเป็น User root อยู่ให้สร้าง Symbolic Links ดังข้างล่าง การสร้าง Symbolic Links ดังข้างล่างนี้เป็นการ Register Service ให้กับ OS ตอนบูธเครื่อง โดยตอนเปิดจะไปอ่าน /etc/rc3.d/S99oracle ซึ่งเป็น Shortcut ที่ชี้ไปที่ dbora ที่เราสร้างไว้ในขั้นตอนก่อนหน้า ส่วนตอนปิดก็จะไปอ่าน /etc/rc0.d/K01oracle ซึ่งเป็น Shortcut ไปยัง dbora เช่นกัน
# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle
 

4) ทดสอบสคริปต์ที่สร้างขึ้น 
ล็อกอินเป็น root แล้วรัน

# /etc/init.d/dbora start (เพื่อ Startup)
# /etc/init.d/dbora stop (เพื่อ Shutdown)

ถ้าการเปิดปิดฐานข้อมูลทำได้ปกติ แสดงว่าน่าจะ OK แล้ว
เราเช็คว่าฐานข้อมูลเปิดอยู่ได้จากคำสั่ง ps บน Unix เช่น
# ps -ef | grep pmon <== Check Process ของ Database ชื่อ PMON
oracle 1148 1 0 11:07:10 ? 0:01 ora_pmon_PAO1 <== แสดงว่า Start แล้ว
root 1533 1529 0 11:13:40 pts/3 0:00 grep pmon
# ps -ef | grep lsnr <== Check Listener Process
oracle 918 1 0 11:06:48 ? 0:00 /opt/oracle/102/bin/tnslsnr L ISTENER -inherit <== Start แล้ว
root 1535 1529 0 11:13:49 pts/3 0:00 grep lsnr

อ้างอิง: Automatic startup and shutdown oracle on linux, Oracle in World, Arju, October 2008