http://www.mercubuana-it.org/roninmorgue/index.php/site/index/ en petambakudang@yahoo.co.id Copyright 2010 2010-09-28T14:19:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/create_oracle_tablespace_on_sap_with_brtools/ http://www.mercubuana-it.org/roninmorgue/index.php/site/create_oracle_tablespace_on_sap_with_brtools/#When:14:19:00Z Pada kesempatan kali ini saya akan berbagi ilmu tentang Oracle, dimana kita akan membuat tablespace oracle di SAP dengan menggunakan BRTools, sebagai catatan SAP sangat merekomendasikan untuk menggunakan BRTools saat kita akan melakukan maintence pada Database Oracle. sapdrp:orat01 1> brtools BR0651I BRTOOLS 7.00 (24) BR0280I BRTOOLS time stamp: 2010-08-23 09.16.25 BR0656I Choice menu 1 - please make a selection ------------------------------------------------------------------------------- BR*Tools main menu 1 = Instance management 2 - Space management 3 - Segment management 4 - Backup and database copy 5 - Restore and recovery 6 - Check and verification 7 - Database statistics 8 - Additional functions 9 - Exit program Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 2 BR0280I BRTOOLS time stamp: 2010-08-23 09.16.32 BR0663I Your choice: ‘2’ BR0280I BRTOOLS time stamp: 2010-08-23 09.16.32 BR0656I Choice menu 5 - please make a selection ------------------------------------------------------------------------------- Database space management 1 = Extend tablespace 2 - Create tablespace 3 - Drop tablespace 4 - Alter tablespace 5 - Alter data file 6 - Move data file 7 - Additional space functions 8 - Reset program status Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 2 BR0280I BRTOOLS time stamp: 2010-08-23 09.16.34 BR0663I Your choice: ‘2’ BR0280I BRTOOLS time stamp: 2010-08-23 09.16.34 BR0657I Input menu 82 - please check/enter input values ------------------------------------------------------------------------------- BRSPACE options for create tablespace 1 - BRSPACE profile (profile) ...... [initT01.sap] 2 - Database user/password (user) .. [/] 3 ~ Tablespace name (tablespace) ... [] 4 - Confirmation mode (confirm) .... [yes] 5 - Scrolling line count (scroll) .. [20] 6 - Message language (language) .... [E] 7 - BRSPACE command line (command) . [-p initT01.sap -s 20 -l E -f tscreate] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 3 BR0280I BRTOOLS time stamp: 2010-08-23 09.16.39 BR0663I Your choice: ‘3’ BR0280I BRTOOLS time stamp: 2010-08-23 09.16.39 BR0681I Enter string value for “tablespace” []: PSAPGG1 BR0280I BRTOOLS time stamp: 2010-08-23 09.17.05 BR0683I New value for “tablespace”: ‘PSAPGG1’ BR0280I BRTOOLS time stamp: 2010-08-23 09.17.05 BR0657I Input menu 82 - please check/enter input values ------------------------------------------------------------------------------- BRSPACE options for create tablespace 1 - BRSPACE profile (profile) ...... [initT01.sap] 2 - Database user/password (user) .. [/] 3 ~ Tablespace name (tablespace) ... [PSAPGG1] 4 - Confirmation mode (confirm) .... [yes] 5 - Scrolling line count (scroll) .. [20] 6 - Message language (language) .... [E] 7 - BRSPACE command line (command) . [-p initT01.sap -s 20 -l E -f tscreate -t PSAPGG1] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRTOOLS time stamp: 2010-08-23 09.17.29 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR0291I BRSPACE will be started with options ‘-p initT01.sap -s 20 -l E -f tscreate -t PSAPGG1’ BR0280I BRTOOLS time stamp: 2010-08-23 09.17.29 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: c BR0280I BRTOOLS time stamp: 2010-08-23 09.17.33 BR0257I Your reply: ‘c’ BR0259I Program execution will be continued… ############################################################################### BR1001I BRSPACE 7.00 (24) BR1002I Start of BRSPACE processing: sedyonnh.tsc 2010-08-23 09.17.33 BR0484I BRSPACE log file: /oracle/T01/sapreorg/sedyonnh.tsc BR0280I BRSPACE time stamp: 2010-08-23 09.17.34 BR1009I Name of database instance: T01 BR1010I BRSPACE action ID: sedyonnh BR1011I BRSPACE function ID: tsc BR1012I BRSPACE function: tscreate BR0280I BRSPACE time stamp: 2010-08-23 09.17.35 BR0657I Input menu 305 - please check/enter input values ------------------------------------------------------------------------------- Main options for creation of tablespace in database T01 1 - Tablespace name (tablespace) ......... [PSAPGG1] 2 - Tablespace contents (contents) ....... [data] 3 - Segment space management (space) ..... [auto] 4 ? Database owner of tablespace (owner) . [] 5 ~ Table data class / tabart (class) .... [] 6 - Data type in tablespace (data) ....... [both] 7 # Joined index/table tablespace (join) . [] 8 ~ Uniform size in MB (uniform) ......... [] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 4 BR0280I BRSPACE time stamp: 2010-08-23 09.23.06 BR0663I Your choice: ‘4’ BR0280I BRSPACE time stamp: 2010-08-23 09.23.06 BR0681I Enter string value for “owner” []: SAPSR3 BR0280I BRSPACE time stamp: 2010-08-23 09.23.12 BR0683I New value for “owner”: ‘SAPSR3’ BR0280I BRSPACE time stamp: 2010-08-23 09.23.12 BR0657I Input menu 305 - please check/enter input values ------------------------------------------------------------------------------- Main options for creation of tablespace in database T01 1 - Tablespace name (tablespace) ......... [PSAPGG1] 2 - Tablespace contents (contents) ....... [data] 3 - Segment space management (space) ..... [auto] 4 - Database owner of tablespace (owner) . [SAPSR3] 5 ~ Table data class / tabart (class) .... [] 6 - Data type in tablespace (data) ....... [both] 7 # Joined index/table tablespace (join) . [] 8 ~ Uniform size in MB (uniform) ......... [] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 09.23.20 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR1061W Tablespace name ‘PSAPGG1’ should have at least 7 characters and begin with ‘PSAPSR3’ BR0691W Warning by checking input value for ‘tablespace’ BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRSPACE time stamp: 2010-08-23 09.23.20 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: s BR0280I BRSPACE time stamp: 2010-08-23 09.25.54 BR0257I Your reply: ‘s’ BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]: y BR0280I BRSPACE time stamp: 2010-08-23 09.25.55 BR0257I Your reply: ‘y’ BR0260E BRSPACE cancelled by user BR1008I End of BRSPACE processing: sedyonnh.tsc 2010-08-23 09.25.55 BR0280I BRSPACE time stamp: 2010-08-23 09.25.55 BR1007I BRSPACE terminated with errors ############################################################################### BR0292I Execution of BRSPACE finished with return code 2 BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRTOOLS time stamp: 2010-08-23 09.25.56 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: s BR0280I BRTOOLS time stamp: 2010-08-23 09.26.08 BR0257I Your reply: ‘s’ BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]: y BR0280I BRTOOLS time stamp: 2010-08-23 09.26.10 BR0257I Your reply: ‘y’ BR0260E BRTOOLS cancelled by user BR0280I BRTOOLS time stamp: 2010-08-23 09.26.10 BR0654I BRTOOLS terminated with errors sapdrp:orat01 2> ------------------------- SAPGG1 BR0280I BRSPACE time stamp: 2010-08-23 10.27.27 BR0683I New value for “owner”: ‘SAPGG1’ BR0280I BRSPACE time stamp: 2010-08-23 10.27.27 BR0657I Input menu 305 - please check/enter input values ------------------------------------------------------------------------------- Main options for creation of tablespace in database T01 1 - Tablespace name (tablespace) ......... [PSAPGG1] 2 - Tablespace contents (contents) ....... [data] 3 - Segment space management (space) ..... [auto] 4 - Database owner of tablespace (owner) . [SAPGG1] 5 ~ Table data class / tabart (class) .... [] 6 - Data type in tablespace (data) ....... [both] 7 # Joined index/table tablespace (join) . [] 8 ~ Uniform size in MB (uniform) ......... [] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 10.27.33 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2010-08-23 10.27.33 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [1000] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [10000] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 1000M autoextend on next 20M maxsize 10000M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 10.29.34 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 420575.048 MB BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3700 located on this disk volume, missing at least 292575.048 MB BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRSPACE time stamp: 2010-08-23 10.29.34 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: b BR0280I BRSPACE time stamp: 2010-08-23 10.30.46 BR0257I Your reply: ‘b’ BR0673I Going back to the previous menu… BR0280I BRSPACE time stamp: 2010-08-23 10.30.46 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [1000] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [10000] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 1000M autoextend on next 20M maxsize 10000M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 3 BR0280I BRSPACE time stamp: 2010-08-23 10.31.13 BR0663I Your choice: ‘3’ BR0280I BRSPACE time stamp: 2010-08-23 10.31.13 BR0682I Enter integer value for “size” (1-32767) [1000]: 100 BR0280I BRSPACE time stamp: 2010-08-23 10.31.19 BR0683I New value for “size”: ‘100’ BR0280I BRSPACE time stamp: 2010-08-23 10.31.19 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [100] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [10000] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 100M autoextend on next 20M maxsize 10000M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 10.31.26 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 419675.051 MB BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3700 located on this disk volume, missing at least 291675.051 MB BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRSPACE time stamp: 2010-08-23 10.31.26 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: b BR0280I BRSPACE time stamp: 2010-08-23 10.32.45 BR0257I Your reply: ‘b’ BR0673I Going back to the previous menu… BR0280I BRSPACE time stamp: 2010-08-23 10.32.45 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [100] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [10000] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 100M autoextend on next 20M maxsize 10000M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 3 BR0280I BRSPACE time stamp: 2010-08-23 10.32.49 BR0663I Your choice: ‘3’ BR0280I BRSPACE time stamp: 2010-08-23 10.32.49 BR0682I Enter integer value for “size” (1-32767) [100]: 10 BR0280I BRSPACE time stamp: 2010-08-23 10.32.52 BR0683I New value for “size”: ‘10’ BR0280I BRSPACE time stamp: 2010-08-23 10.32.52 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [10] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [10000] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 10M autoextend on next 20M maxsize 10000M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 10.32.54 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 419585.028 MB BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3700 located on this disk volume, missing at least 291585.028 MB BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRSPACE time stamp: 2010-08-23 10.32.54 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: b BR0280I BRSPACE time stamp: 2010-08-23 10.33.22 BR0257I Your reply: ‘b’ BR0673I Going back to the previous menu… BR0280I BRSPACE time stamp: 2010-08-23 10.33.22 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [10] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [10000] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 10M autoextend on next 20M maxsize 10000M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: =================================================== BR1011I BRSPACE function ID: tsc BR1012I BRSPACE function: tscreate BR0280I BRSPACE time stamp: 2010-08-23 14.03.31 BR0657I Input menu 305 - please check/enter input values ------------------------------------------------------------------------------- Main options for creation of tablespace in database T01 1 - Tablespace name (tablespace) ......... [PSAPGG1] 2 - Tablespace contents (contents) ....... [data] 3 - Segment space management (space) ..... [auto] 4 ? Database owner of tablespace (owner) . [] 5 ~ Table data class / tabart (class) .... [] 6 - Data type in tablespace (data) ....... [both] 7 # Joined index/table tablespace (join) . [] 8 ~ Uniform size in MB (uniform) ......... [] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 4 BR0280I BRSPACE time stamp: 2010-08-23 14.03.38 BR0663I Your choice: ‘4’ BR0280I BRSPACE time stamp: 2010-08-23 14.03.38 BR0681I Enter string value for “owner” []: SAPGG1 BR0280I BRSPACE time stamp: 2010-08-23 14.03.46 BR0683I New value for “owner”: ‘SAPGG1’ BR0280I BRSPACE time stamp: 2010-08-23 14.03.46 BR0657I Input menu 305 - please check/enter input values ------------------------------------------------------------------------------- Main options for creation of tablespace in database T01 1 - Tablespace name (tablespace) ......... [PSAPGG1] 2 - Tablespace contents (contents) ....... [data] 3 - Segment space management (space) ..... [auto] 4 - Database owner of tablespace (owner) . [SAPGG1] 5 ~ Table data class / tabart (class) .... [] 6 - Data type in tablespace (data) ....... [both] 7 # Joined index/table tablespace (join) . [] 8 ~ Uniform size in MB (uniform) ......... [] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 14.03.51 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2010-08-23 14.03.51 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [1000] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [10000] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 1000M autoextend on next 20M maxsize 10000M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: 5 BR0280I BRSPACE time stamp: 2010-08-23 14.04.24 BR0663I Your choice: ‘5’ BR0280I BRSPACE time stamp: 2010-08-23 14.04.24 BR0682I Enter integer value for “maxsize” (0-32767) [10000]: 1500 BR0280I BRSPACE time stamp: 2010-08-23 14.04.33 BR0683I New value for “maxsize”: ‘1500’ BR0280I BRSPACE time stamp: 2010-08-23 14.04.33 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (1. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_1/gg1.data1] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [1000] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [1500] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 1000M autoextend on next 20M maxsize 1500M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 14.04.41 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 420619.157 MB BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_1 for total maximum size of all database files of tablespace PSAPSR3700 located on this disk volume, missing at least 292619.157 MB BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRSPACE time stamp: 2010-08-23 14.04.41 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: c BR0280I BRSPACE time stamp: 2010-08-23 14.19.22 BR0257I Your reply: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2010-08-23 14.19.22 BR1091I Next data file can be specified now BR0675I Do you want to perform this action? BR0676I Enter ‘y[es]’ to perform the action, ‘n[o]/c[ont]’ to skip it, ‘s[top]’ to abort: y BR0280I BRSPACE time stamp: 2010-08-23 14.21.40 BR0257I Your reply: ‘y’ BR0677I The action will be performed… BR0280I BRSPACE time stamp: 2010-08-23 14.21.40 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (2. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_2/gg1.data2] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [1000] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [1500] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 1000M autoextend on next 20M maxsize 1500M, ‘/oracle/T01/sapdata4/gg1_2/gg1.data2’ size 1000M autoextend on next 20M maxsize 1500M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 14.22.49 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_2 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 421619.250 MB BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_2 for total maximum size of all database files of tablespace PSAPSR3700 located on this disk volume, missing at least 293619.250 MB BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRSPACE time stamp: 2010-08-23 14.22.49 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: c BR0280I BRSPACE time stamp: 2010-08-23 14.23.17 BR0257I Your reply: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2010-08-23 14.23.17 BR1091I Next data file can be specified now BR0675I Do you want to perform this action? BR0676I Enter ‘y[es]’ to perform the action, ‘n[o]/c[ont]’ to skip it, ‘s[top]’ to abort: y BR0280I BRSPACE time stamp: 2010-08-23 14.23.50 BR0257I Your reply: ‘y’ BR0677I The action will be performed… BR0280I BRSPACE time stamp: 2010-08-23 14.23.50 BR0657I Input menu 306 - please check/enter input values ------------------------------------------------------------------------------- Space options for creation of tablespace PSAPGG1 (3. file) 1 - Tablespace file name (file) .......... [/oracle/T01/sapdata4/gg1_3/gg1.data3] 2 ~ Raw disk / link target (rawlink) ..... [] 3 - File size in MB (size) ............... [1000] 4 - File autoextend mode (autoextend) .... [yes] 5 - Maximum file size in MB (maxsize) .... [1500] 6 - File increment size in MB (incrsize) . [20] 7 - SQL command (command) ................ [create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 1000M autoextend on next 20M maxsize 1500M, ‘/oracle/T01/sapdata4/gg1_2/gg1.data2’ size 1000M autoextend on next 20M maxsize 1500M, ‘/oracle/T01/sapdata4/gg1_3/gg1.data3’ size 1000M autoextend on next 20M maxsize 1500M] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2010-08-23 14.24.57 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_3 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 422619.239 MB BR1049W Not enough free space in /oracle/T01/sapdata4/gg1_3 for total maximum size of all database files of tablespace PSAPSR3700 located on this disk volume, missing at least 294619.239 MB BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action BR0280I BRSPACE time stamp: 2010-08-23 14.24.57 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: c BR0280I BRSPACE time stamp: 2010-08-23 14.25.08 BR0257I Your reply: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2010-08-23 14.25.08 BR1091I Next data file can be specified now BR0675I Do you want to perform this action? BR0676I Enter ‘y[es]’ to perform the action, ‘n[o]/c[ont]’ to skip it, ‘s[top]’ to abort: c BR0280I BRSPACE time stamp: 2010-08-23 14.25.12 BR0257I Your reply: ‘c’ BR0678I The action will be skipped… BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2010-08-23 14.25.12 BR0370I Directory /oracle/T01/sapreorg/sedypmxe created BR0280I BRSPACE time stamp: 2010-08-23 14.25.12 BR0319I Control file copy created: /oracle/T01/sapreorg/sedypmxe/cntrlT01.old 10567680 BR0280I BRSPACE time stamp: 2010-08-23 14.25.12 BR0370I Directory /oracle/T01/sapdata4/gg1_2 created BR0370I Directory /oracle/T01/sapdata4/gg1_3 created BR0280I BRSPACE time stamp: 2010-08-23 14.25.12 BR1089I Creating tablespace PSAPGG1… BR0280I BRSPACE time stamp: 2010-08-23 14.25.39 BR1016I SQL statement ‘create tablespace PSAPGG1 extent management local autoallocate segment space management auto datafile ‘/oracle/T01/sapdata4/gg1_1/gg1.data1’ size 1000M autoextend on next 20M maxsize 1500M, ‘/oracle/T01/sapdata4/gg1_2/gg1.data2’ size 1000M autoextend on next 20M maxsize 1500M, ‘/oracle/T01/sapdata4/gg1_3/gg1.data3’ size 1000M autoextend on next 20M maxsize 1500M’ executed successfully BR1060I Tablespace PSAPGG1 created successfully with files: /oracle/T01/sapdata4/gg1_1/gg1.data1 1000M, /oracle/T01/sapdata4/gg1_2/gg1.data2 1000M, /oracle/T01/sapdata4/gg1_3/gg1.data3 1000M BR0280I BRSPACE time stamp: 2010-08-23 14.25.39 BR0340I Switching to next online redo log file for database instance T01 ... BR0321I Switch to next online redo log file for database instance T01 successful BR0280I BRSPACE time stamp: 2010-08-23 14.25.43 BR0319I Control file copy created: /oracle/T01/sapreorg/sedypmxe/cntrlT01.new 10567680 BR0280I BRSPACE time stamp: 2010-08-23 14.25.43 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: Computer DataBase 2010-09-28T14:19:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/audit_on_oracle_database/ http://www.mercubuana-it.org/roninmorgue/index.php/site/audit_on_oracle_database/#When:09:05:01Z Kali ini saya membuat artikel untuk melakukan audit pada oracle, audit ini menggunakan feature yang sudah ada pada oracle dengan mengaktifkan feature audit. Oracle menyediakan aplikasi untuk management audit ini yaitu Audit Vault Server, tapi lisensi nya mahal ... sekitar 52 ribu US dollar (tentu saja dengan banyak kelebihan2nya dan cocok untuk enterprise berskala besar). Disini saya hanya memanfaatkan feature2 yang sudah ada pada oracle dan menampilkan hasil auditnya dengan sangat sederhana..tapi cukup lumayan untuk mengetahui aktifitas yang terjadi pada oracle.. -- Audit Trail not yet activated SQL> select * from sys.aud$; no rows selected -- Activated audit trail SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE; System altered. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 209715200 bytes Fixed Size 1218556 bytes Variable Size 62916612 bytes Database Buffers 142606336 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. -- Create user to audit SQL> CREATE USER audit_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; User created. -- Make grant to user SQL> GRANT connect TO audit_user; Grant succeeded. SQL> GRANT create table, create procedure TO audit_user; Grant succeeded. -- Make policy for audit to user audit SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_user BY ACCESS; Audit succeeded. -- Testing audit SQL> CONN audit_user/password Connected. SQL> CREATE TABLE user_tab (id NUMBER); Table created. SQL> INSERT INTO user_tab (id) VALUES (1); 1 row created. SQL> UPDATE user_tab SET id = id; 1 row created. SQL> SELECT * FROM user_tab; ID ---------- 1 SQL> DELETE FROM user_tab; 1 row deleted. SQL> SELECT view_name FROM dba_views WHERE view_name LIKE ‘DBA&#xAU;DIT%’ ORDER BY view_name; VIEW_NAME ------------------------------ DBA_AUDIT_EXISTS DBA_AUDIT_OBJECT DBA_AUDIT_POLICIES DBA_AUDIT_POLICY_COLUMNS DBA_AUDIT_SESSION DBA_AUDIT_STATEMENT DBA_AUDIT_TRAIL DBA_COMMON_AUDIT_TRAIL DBA_FGA_AUDIT_TRAIL DBA_OBJ_AUDIT_OPTS DBA_PRIV_AUDIT_OPTS DBA_REPAUDIT_ATTRIBUTE DBA_REPAUDIT_COLUMN DBA_STMT_AUDIT_OPTS 14 rows selected. SQL> COLUMN username FORMAT A10 COLUMN owner FORMAT A10 COLUMN obj_name FORMAT A10 COLUMN extended_timestamp FORMAT A35 -- View audit result SQL> SELECT username, extended_timestamp, owner, obj_name, action_name FROM dba_audit_trail WHERE owner = ‘audit_user’ ORDER BY timestamp; USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME ---------- ----------------------------------- ---------- -------- ----------- audit_user 29-SEP-09 04.10.29.873811 AM +07:00 audit_user user_tab INSERT audit_user 29-SEP-09 04.10.29.905141 AM +07:00 audit_user user_tab DELETE audit_user 29-SEP-09 04.10.29.881962 AM +07:00 audit_user user_tab SELECT audit_user 29-SEP-09 04.10.29.878485 AM +07:00 audit_user user_tab UPDATE -- For Specific audit use FGA ---- FGA ----- SQL> CONN audit_user/password Connected. SQL> CREATE TABLE emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); Table created. SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Tim’, 1); 1 row created. SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Larry’, 50001); 1 row created. SQL> COMMIT; Commit complete. SQL> BEGIN DBMS_FGA.add_policy( object_schema => ‘audit_user’, object_name => ‘EMP’, policy_name => ‘SALARY_CHK_AUDIT’, audit_condition => ‘SAL > 50000’, audit_column => ‘SAL’); END; / PL/SQL procedure successfully completed. SQL> CONN audit_user/password Connected. SQL> SELECT sal FROM emp WHERE ename = ‘Tim’; SAL ---------- 1 SQL> SELECT sal FROM emp WHERE ename = ‘Larry’; SAL ---------- 50001 SQL> SELECT sql_text FROM dba_fga_audit_trail; SQL_TEXT -------------------------------------------------------------------------------- SELECT sal FROM emp WHERE ename = ‘Larry’ SQL> select db_user,sql_text from dba_fga_audit_trail; DB_USER SQL_TEXT ------- -------- audit_user SELECT sal FROM emp WHERE ename = ‘Larry’ -- Clear down the audit trail SQL> TRUNCATE TABLE fga_log$; Table truncated. SQL> SELECT sql_text FROM dba_fga_audit_trail; no rows selected -- Apply the policy to the SAL column of the EMP table. SQL> BEGIN DBMS_FGA.add_policy( object_schema => ‘audit_user’, object_name => ‘EMP’, policy_name => ‘SAL_AUDIT’, audit_condition => NULL,—Equivalent to TRUE audit_column => ‘SAL’, statement_types => ‘SELECT,INSERT,UPDATE,DELETE’); END; / PL/SQL procedure successfully completed. -- Test the auditing. SQL> CONN audit_user/password Connected. SQL> SELECT * FROM emp WHERE empno = 9998; no rows selected SQL> INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1); 1 row created. SQL> UPDATE emp SET sal = 10 WHERE empno = 9998; 1 row updated. SQL> DELETE emp WHERE empno = 9998; 1 row deleted. SQL> ROLLBACK; Rollback complete. -- Check the audit trail as sysdba. SQL> SELECT db_user,sql_text FROM dba_fga_audit_trail; DB_USER SQL_TEXT ---------- ------------------------------------------------------------------------ audit_user SELECT * FROM emp WHERE empno = 9998 audit_user INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1) audit_user UPDATE emp SET sal = 10 WHERE empno = 9998 audit_user DELETE emp WHERE empno = 9998 -- Drop the policy. SQL> BEGIN DBMS_FGA.drop_policy( object_schema => ‘audit_user’, object_name => ‘EMP’, policy_name => ‘SAL_AUDIT’); END; / PL/SQL procedure successfully completed. Computer DataBase 2009-10-05T09:05:01+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/membuat_oracle_data_guard/ http://www.mercubuana-it.org/roninmorgue/index.php/site/membuat_oracle_data_guard/#When:01:47:00Z Seperti yang saya janjikan, bahwa setelah menulis artikel pembuatan Data Guard pada Oracle Standart Edition.. saya akan menulis artikel cara membuat Oracle Data Guard di Enterprise Edition, kali ini saya akan akan membuat Physical Standby Database. (Data Guard memiliki 2 buah metode : Physical Standby Database dan Logical Standby Database). Skenario : Ada 2 buah server Database, dimana 1 sebagai primary database dan 1 sebagai standby database 1. Versi database: Oracle Database 10g Enterprise Edition Release 2 2. OS: Oracle Linux Enterprise 5 Primary Database ---------------- 1. IP -> 192.168.58.10 2. DB_UNIQUE_NAME -> TRAINING 3. SERVICE_NAME -> TRAINING Standby Database ---------------- 1. IP -> 192.168.58.20 2. DB_UNIQUE_NAME -> TRAINING 3. SERVICE_NAME -> TRAINSBY Dan standby database berfungsi sebagai server database cadangan Persiapan - persiapan yang perlu dilakukan : === PRIMARY DATABASE === 1. Apply force logging SQL> ALTER DATABASE FORCE LOGGING; Database altered. 2. Mengaktifkan mode archived log SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Current log sequence 7 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 444596224 bytes Fixed Size 1219880 bytes Variable Size 163578584 bytes Database Buffers 272629760 bytes Redo Buffers 7168000 bytes Database mounted. SQL> ALTER DATABASE ARCHIVELOG; Database altered. SQL> ALTER DATABASE OPEN; Database altered. 3. Membuat password file [oracle@gunawan ~]$ cd $ORACLE_HOME/dbs [oracle@gunawan dbs]$ orapwd file=orapwTRAINING password=oracle entries=10 force=y [oracle@gunawan dbs]$ ls -al total 112 drwxr-x--- 2 oracle dba 4096 Dec 11 20:38 . drwxr-x--- 55 oracle dba 4096 Dec 9 20:23 .. -rw-r--r-- 1 oracle dba 5975 Dec 9 21:13 control01.txt -rw-rw---- 1 oracle dba 1544 Dec 4 01:00 hc_TRAINING.dat -rw-r----- 1 oracle dba 12920 May 3 2001 initdw.ora -rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora -rw-rw---- 1 oracle dba 24 Dec 4 01:01 lkTRAINING -rw-r----- 1 oracle dba 1536 Dec 4 01:03 orapwTRAINING -rw-r----- 1 oracle dba 3584 Dec 11 20:38 spfileTRAINING.ora -rw-r--r-- 1 oracle dba 1056 Dec 7 21:17 spfileTRAINSBY.txt -rw-r--r-- 1 oracle dba 719 Dec 7 21:34 sqlnet.log 4. Mengecek semua parameter yang akan dibutuhkan dalam pembuatan standby database SQL> sho parameter log_file_name_convert; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_file_name_convert string SQL> sho parameter remote_login_passwordfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE SQL> sho parameter log_archive_config; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string SQL> sho parameter log_archive_dest_1; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string log_archive_dest_10 string SQL> sho parameter log_archive_dest_state_1; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable SQL> sho parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string enable SQL> sho parameter log_archive_format; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf SQL> sho parameter fal_client; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string SQL> sho parameter fal_server; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_server string SQL> sho parameter log_archive_dest_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string 5. Buat backup spfile (usahakan untuk selalu membuat backup sebelum melakukan perubahan pada parameter-parameter primary database SQL> create pfile=’/oracle/product/10.2.0/training/dbs/init_TRAINING.txt’ from spfile; File created. 6. Lakukan perubahan pada parameter database SQL> alter system set log_file_name_convert=’/home/oracle/training/redo’,’home/oracle/training/redo’ scope=spfile; System altered. SQL> alter system set db_file_name_convert=’/data/training/files’,’/data/training/files’ scope=spfile; System altered. SQL> alter system set log_archive_config=’DG_CONFIG=(training,trainsby)’ scope=spfile; System altered. SQL> alter system set log_archive_dest_1=’LOCATION=/data/training/archive’ scope=spfile; System altered. SQL> alter system set log_archive_dest_state_2=’defer’ scope=spfile; System altered. SQL> alter system set log_archive_format=’%t_%s_%r.arc’ scope=spfile; System altered. SQL> alter system set fal_client=’training’ scope=spfile; System altered. SQL> alter system set fal_server=’trainsby’ scope=spfile; System altered. SQL> alter system set log_archive_dest_2=’service=trainsby optional LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=training’ scope=spfile; System altered. <<< Restart database >>> SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 444596224 bytes Fixed Size 1219880 bytes Variable Size 167772888 bytes Database Buffers 268435456 bytes Redo Buffers 7168000 bytes Database mounted. Database opened. 7. Buat pfile dari spfile terbaru yang akan digunakan untuk standby database (setelah restart database maka spfile sudah berubah) SQL> create pfile=’/home/oracle/pfile_training.txt’ from spfile; File created. 8. Edit/buat file tnsnames.ora [oracle@gunawan ~]$ cd $ORACLE_HOME/network/admin/ [oracle@gunawan admin]$ vi tnsnames.ora TRAINING = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.10)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = TRAINING) ) ) TRAINSBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.20)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = TRAINING) ) ) 9. Buat standby control file dari primary database SQL> alter database create standby controlfile as ‘/home/oracle/controlstby.ctl’; Database altered. 10. Buat backup database secara online SQL> select TABLESPACE_NAME from dba_tablespaces where CONTENTS <>’TEMPORARY’; TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS SQL> alter tablespace SYSTEM begin backup; Tablespace altered. SQL> alter tablespace UNDOTBS1 begin backup; Tablespace altered. SQL> alter tablespace SYSAUX begin backup; Tablespace altered. SQL> alter tablespace USERS begin backup; Tablespace altered. SQL> select name as file_name from (select name from v$tempfile union select name from v$datafile union select member as name from v$logfile) order by file_name; FILE_NAME -------------------------------------------------------------------------------- /data/training/files/sysaux01.dbf /data/training/files/system01.dbf /data/training/files/temp01.dbf /data/training/files/undotbs01.dbf /data/training/files/users01.dbf /home/oracle/training/redo/redo01a.log /home/oracle/training/redo/redo01b.log /home/oracle/training/redo/redo02a.log /home/oracle/training/redo/redo02b.log /home/oracle/training/redo/redo03a.log /home/oracle/training/redo/redo03b.log 11 rows selected. ====== STANDBY ======== 11. Copy semua datafile dan redolog yang telah dibackup (saya melakukan proses peng-copy-an dari standby database [oracle@gunawan ~]$ scp -r oracle@192.168.58.10:/data/training/files/ /data/training/ oracle@192.168.58.10’s password: users01.dbf 100% 5128KB 5.0MB/s 00:01 undotbs01.dbf 100% 25MB 2.5MB/s 00:10 system01.dbf 100% 480MB 3.5MB/s 02:17 temp01.dbf 100% 20MB 4.0MB/s 00:05 sysaux01.dbf 100% 240MB 2.4MB/s 01:39 [oracle@gunawan ~]$ [oracle@gunawan ~]$ scp -r oracle@192.168.58.10:/home/oracle/training/redo/ /home/oracle/training/ oracle@192.168.58.10’s password: redo02a.log 100% 50MB 7.1MB/s 00:07 redo01a.log 100% 50MB 5.6MB/s 00:09 redo01b.log 100% 50MB 7.1MB/s 00:07 redo03a.log 100% 50MB 6.3MB/s 00:08 redo03b.log 100% 50MB 6.3MB/s 00:08 redo02b.log 100% 50MB 6.3MB/s 00:08 [oracle@gunawan ~]$ [oracle@gunawan ~]$ scp oracle@192.168.58.10:/home/oracle/controlstby.ctl /home/oracle/ oracle@192.168.58.10’s password: controlstby.ctl 100% 6896KB 6.7MB/s 00:01 [oracle@gunawan ~]$ scp oracle@192.168.58.10:/home/oracle/pfile_training.txt /home/oracle/ oracle@192.168.58.10’s password: pfile_training.txt 100% 1567 1.5KB/s 00:00 [oracle@gunawan ~]$ [oracle@gunawan ~]$ scp oracle@192.168.58.10:/oracle/product/10.2.0/training/dbs/orapwTRAINING /oracle/product/10.2.0/training/dbs/ oracle@192.168.58.10’s password: orapwTRAINING 100% 2560 2.5KB/s 00:00 [oracle@gunawan ~]$ scp oracle@192.168.58.10:/oracle/product/10.2.0/training/network/admin/tnsnames.ora /oracle/product/10.2.0/training/network/admin oracle@192.168.58.10’s password: ===== PRIMARY ====== setelah backup selesai maka akhiri mode backup pada tablespace di primary database SQL> alter tablespace SYSTEM end backup; Tablespace altered. SQL> alter tablespace UNDOTBS1 end backup; Tablespace altered. SQL> alter tablespace SYSAUX end backup; Tablespace altered. SQL> alter tablespace USERS end backup; Tablespace altered. ====== STANDBY ====== 12. Copy standby controlfile menjadi 3 buah [oracle@gunawan ~]$ pwd /home/oracle [oracle@gunawan ~]$ cp -rp controlstby.ctl control01.ctl [oracle@gunawan ~]$ cp -rp controlstby.ctl control02.ctl [oracle@gunawan ~]$ cp -rp controlstby.ctl control03.ctl [oracle@gunawan ~]$ mv control0* /home/oracle/training/control/ 13. Edit file pfile_training.txt log_archive_dest_2=’service=training optional LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=training’ 14. Buat direktory untuk file-file dump $ mkdir /oracle/product/admin/training/adump $ mkdir /oracle/product/admin/training/bdump $ mkdir /oracle/product/admin/training/cdump $ mkdir /oracle/product/admin/training/udump 15. Naikkan standby database menggunakan pfile SQL> startup mount pfile=’/home/oracle/pfile_training.txt’; ORACLE instance started. Total System Global Area 444596224 bytes Fixed Size 1219880 bytes Variable Size 167772888 bytes Database Buffers 268435456 bytes Redo Buffers 7168000 bytes Database mounted. 16. Rubah parameter service_names SQL> sho parameter SERVICE_NAMES; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string TRAINING <<< Untuk merubah parameter database harus dinaikkan menggunakan spfile >>> SQL> create spfile from pfile=’/home/oracle/pfile_training.txt’; File created. <<< Restart Database >>> SQL> shutdown ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 444596224 bytes Fixed Size 1219880 bytes Variable Size 167772888 bytes Database Buffers 268435456 bytes Redo Buffers 7168000 bytes Database mounted. SQL> alter system set SERVICE_NAMES=’TRAINSBY’ scope=spfile; System altered. <<< Restart Database >>> SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 444596224 bytes Fixed Size 1219880 bytes Variable Size 167772888 bytes Database Buffers 268435456 bytes Redo Buffers 7168000 bytes Database mounted. SQL> sho parameter SERVICE_NAMES; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string TRAINSBY 17. Jalankan recovery di standby database untuk meng-apply arhived log SQL> alter database recover managed standby database disconnect; Database altered. ========= PRIMARY ====== 18. Rubah parameter log_archive_dest_state_2 dari defer menjadi enable di primary database SQL> alter system set log_archive_dest_state_2=’enable’ scope=both; System altered. SQL> sho parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string enable Akhirnya standby database selesai di-create. Untuk melihat archived log yang telah di-apply di standby database gunakan command ini. SQL> set pages 100 SQL> col name for a45 SQL> select name,to_char(FIRST_TIME,’dd-mon-yy hh24:mi:ss’) TIME ,SEQUENCE#,APPLIED from v$archived_log; Pastikan colomn APPLIED bernilai YES. NAME TIME SEQUENCE# APP --------------------------------------------- ------------------ ---------- --- /data/training/archive/1_7_672541313.arc 11-dec-08 20:36:03 7 YES /data/training/archive/1_8_672541313.arc 12-dec-08 17:29:07 8 YES /data/training/archive/1_9_672541313.arc 12-dec-08 18:02:31 9 YES /data/training/archive/1_10_672541313.arc 12-dec-08 18:06:36 10 YES /data/training/archive/1_11_672541313.arc 12-dec-08 18:11:41 11 YES /data/training/archive/1_12_672541313.arc 12-dec-08 18:15:23 12 YES /data/training/archive/1_13_672541313.arc 12-dec-08 18:18:07 13 YES /data/training/archive/1_14_672541313.arc 12-dec-08 18:27:07 14 YES /data/training/archive/1_15_672541313.arc 12-dec-08 18:29:38 15 YES /data/training/archive/1_16_672541313.arc 12-dec-08 18:32:03 16 YES /data/training/archive/1_17_672541313.arc 12-dec-08 18:35:39 17 YES /data/training/archive/1_18_672541313.arc 12-dec-08 18:36:25 18 YES /data/training/archive/1_19_672541313.arc 12-dec-08 18:56:36 19 YES 13 rows selected. Computer DataBase 2008-12-18T01:47:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/cerita_tentang_kamu/ http://www.mercubuana-it.org/roninmorgue/index.php/site/cerita_tentang_kamu/#When:11:37:00Z Saat jiwa-jiwa kembali menyusuri sepinya malam ini.. saat mata ini menyusuri jendela segiempat yang terdiam kaku dan membisu.. Bayangan temaram dahan bergerak-gerak harmonis menciptakan tarian malam yang membuat jiwaku kembali teringat akan indahnya engkau kekasih.. Saat dinginnya malam ini membawa hembusan angin, membuat setiap jiwa yang merasakannya ingin selalu berada dalam kehangatan cinta.. Kekasih, saat malam kembali hadir.. aku ingin engkau menatap langit dimalam ini. Dan ku ingin engkau dapat merasakan dan mengerti akan kata-kata yang akan membentuk kalimat.. saat aku berbicara dari hatiku yang selalu haus akan kerinduan bersamamu.. Aku ingin cinta merasukimu, lewat pengertian yang begitu sederhana namun menciptakan keagungan dalam mendewasakan cinta dan kita berbahagia saat menyadari bahwa cinta telah mendewasakan pikiran dan jiwa kita.. Dan aku telah belajar banyak dari penempuhan ini.. saat cinta mengajarkan aku untuk selalu ikhlas mendoakanmu, diantara waktu-waktu disaat aku bersujud kepada sang Pencipta cinta.. Saat aku mencarimu diantara lembaran puisi-puisi ini, akupun luruh saat menemukanmu.. dan aku tak pernah mengerti mengapa aku demikian gemetar saat tatapan matamu menghujam diantara kedua bola mata ini.. langsung.. dan menusuk tepat hatiku.. selalu ada perasaan tak menentu menyadari akan kehadiranmu.. dan kembali mata ini hanya mampu menatapmu.. diam.. bisu dan terseret kedalam pusaran asa yang akan selalu ada dalam pikiranku.. dan aku seringkali menemukan dirimu yang utuh saat ku terdiam membisu dan menemukanmu begitu utuh dalam pikiranku saat jiwaku sendiri disudut sepi jiwa.. seperti saat hujan sore tadi yang kembali mengingatkanku pada kenangan-kenangan akan puisi-puisi yang kutuliskan untukmu.. Kekasih, saat malam kembali menaungi mimpi-mimpimu.. aku ingin engkau pun dapat melihat bahwa aku ingin selalu menjadi cerita dalam duniamu, ketika jiwamu meletih dan meredup menyerupai rembulan saat gerhana menciptakan bayangan indah.. laksana lukisan malam sang maestro.. aku pun ingin memberikan seteguk kedamaian dan ketenangan.. dan aku akan tersenyum saat menatap engkau kembali merekah laksana sekuntum bunga lotus di senja hari.. kekasih.. saat aku memberikanmu sebuah kekhilafan dan ketidakmampuanku untuk dapat menjadi sandaranmu.. tapi senyumanmu tak pernah sekalipun engkau sembunyikan dariku, seperti pelangi yang bersinar setelah redanya hujan.. dan aku pun kembali terpekur menatap rintik-rintik hujan yang tersisa.. jatuh ketanah.. meresap.. dan hilang, hanya menciptakan genangan-genangan yang memantulkan indahnya senyuman sang kekasih.. Sayang, engkau selalu dapat membuatku tersenyum..walaupun terkadang dapat memberikan galau..resah..dan sesak saat hati kembali hadir saat untaian hujan yang turun ketanah membentuk jalinan dan ikatan tali-temali, dan hujan kembali dapat menyatukan langit dan bumi yang tak akan pernah bisa menyatu.. Dan cintaku.. terima kasih untuk semua yang telah engkau berikan.. dan tetaplah kita saling bergenggaman tangan.. dan janganlah kita membiarkan apapun yang meyelusup diantara jari-jemari genggaman tangan ini, agar takkan pernah terlepas.. Computer Diary 2008-12-05T11:37:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/membuat_ulang_re_create_controlfile_di_oracle/ http://www.mercubuana-it.org/roninmorgue/index.php/site/membuat_ulang_re_create_controlfile_di_oracle/#When:15:13:00Z recreate_controlfile.txt Pada artikel ini, akan dijelaskan bagaimana cara membuat ulang controlfile pada oracle. Kita perlu membuat ulang controlfile pada saat akan melakukan perubahan-perubahan paramater pada controlfile, disini saya akan melakukan perubahan pada nilai: MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 300 MAXINSTANCES 8 MAXLOGHISTORY 292 menjadi MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 1024 MAXINSTANCES 15 MAXLOGHISTORY 500 Langkah-langkah yang dilakukan adalah : 1. pastikan nama database yang akan kita buat ulang controlfilenya SQL> select name from v$database; NAME --------- TRAINING 2. lihat path dimana controlfile berada. SQL> select name from v$controlfile; NAME ------------------------------------------------------------------------------ /ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/CONTROL01.CTL /ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/CONTROL02.CTL /ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/CONTROL03.CTL 3. backup controlfile kedalam bentuk file text untuk membuat ulang controlfilenya SQL> alter database backup controlfile to trace as ‘/coba2oracle/controlfile.txt’; Database altered. 4. edit file controlfile.txt sesuai dengan kebutuhan $ vi /coba2oracle/controlfile.txt =============== potong ==================== -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE “TRAINING” NORESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 300 MAXINSTANCES 8 MAXLOGHISTORY 292 ============== potong ===================== kemudian edit file tersebut menjadi seperti dibawah ini: STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE “TRAINING” NORESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 50 MAXLOGMEMBERS 10 MAXDATAFILES 1024 MAXINSTANCES 15 MAXLOGHISTORY 500 LOGFILE GROUP 1 ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/REDO01.LOG’ SIZE 50M, GROUP 2 ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/REDO02.LOG’ SIZE 50M, GROUP 3 ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/REDO03.LOG’ SIZE 50M DATAFILE ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/SYSTEM01.DBF’, ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/UNDOTBS01.DBF’, ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/SYSAUX01.DBF’, ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/USERS01.DBF’, ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/USERS02.DBF’, ‘/ORACLEDATA/HAPUS.DBF’, ‘/ORACLEDATA/HAPUS02.DBF’, ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/LAT01.DBF’ CHARACTER SET WE8MSWIN1252; RECOVER DATABASE ALTER SYSTEM ARCHIVE LOG ALL; ALTER DATABASE OPEN; ALTER TABLESPACE TEMP ADD TEMPFILE ‘D:ORACLEPRODUCT10.2.0ORADATATRAININGTEMP01.DBF’ SIZE 20971520 REUSE AUTOEXTEND OFF; NOTE : pada file controlfile.txt terdapat 2 metode dalam pembuatan controlfile, case 1 (noresetlogs) dan case 2 (resetlogs), untuk kasus ini hapus step2 pada case 2(resetlogs).. Setelah itu simpan hasil perubahan dan rubah controlfile.txt menjadi controlfile.sql $ mv controlfile.txt controlfile.sql 5. matikan database SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 6. jalankan script pada file controlfile.sql SQL> @ /coba2oracle/controlfile.sql; ORACLE instance started. Total System Global Area 1048576000 bytes Fixed Size 1252976 bytes Variable Size 583008656 bytes Database Buffers 461373440 bytes Redo Buffers 2940928 bytes Control file created. ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required System altered. Database altered. Tablespace altered. SQL> NOTE : Pesan error tersebut tidak masalah, karena menunjukkan bahwa database tidak perlu di recover. Computer DataBase 2008-11-23T15:13:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/membuat_rman_dengan_crontab/ http://www.mercubuana-it.org/roninmorgue/index.php/site/membuat_rman_dengan_crontab/#When:14:57:01Z create_rman_crontab.txt Untuk artikel kali ini, saya akan membuat script RMAN yang akan dijalankan secara terjadwal dengan menggunakan crontab… Langkah-langkah yang dilakukan adalah: 1. membuat file fullbackup_rman.sh * * * * * /app1/oracle/scripts/fullbackup_rman.sh > /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log ==================================== #vi fullbackup_rman.sh ORACLE_SID=training; export ORACLE_SID ORACLE_HOME=/ora10g/app/oracle/OraHome_1 export ORACLE_HOME PATH=$PATH:$ORACLE_HOME/bin:/bin:/usr/bin:/usr/ucb:/etc:.;export PATH rman target=/ @/app1/oracle/scripts/fullbackup_script.conf -> path tempat menaruh script RMAN ================================ 2. buat file fullbackup_script.conf contoh script RMAN yang akan dijalankan. #vi /app1/oracle/scripts/fullbackup_script.conf run { allocate channel ch1 type disk; backup incremental level 0 filesperset 1 format ‘/app1/oracle_backup/data_%T_d%d_p%p_U%U.bak’ (database); backup incremental level 0 format ‘/app1/oracle_backup/cf_%T_d%d_p%p_U%U.bak’ (current controlfile); delete noprompt obsolete; } exit; ============================ Cara membuat Crontab file ___________ Crontab syntax :- A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval. * * * * * command to be executed - - - - - | | | | | | | | | +----- day of week (0 - 6) (Sunday=0) | | | +------- month (1 - 12) | | +--------- day of month (1 - 31) | +----------- hour (0 - 23) +------------- min (0 - 59) * in the value field above means all legal values as in braces for that column. The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range). Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed . 3. membuat Crontab untuk menjalankan RMAN _______ contoh crontab ini akan menjalankan script /app1/oracle/scripts/fullbackup_rman.sh dan membuat log di /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log setiap hari pada jam 6:30 PM. 30 18 * * * /app1/oracle/scripts/fullbackup_rman.sh > /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log ================================= $ chmod 775 fullbackup_rman.sh $ crontab -e => 30 18 * * * /app1/oracle/scripts/fullbackup_rman.sh > /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log $ crontab -l ================================= Computer DataBase 2008-11-23T14:57:01+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/saat_kau_hadir_kembali/ http://www.mercubuana-it.org/roninmorgue/index.php/site/saat_kau_hadir_kembali/#When:18:51:00Z Dy.. tahukah kamu yang aku rasakan malam ini.? sadarkah engkau dengan isi hatiku saat ini? dan masih ingatkah engkau tentang memori-memori yang senantiasa kugoreskan kedalam lembaran hatimu? Saat malam-malam lalu.. entah sudah berapa ribu malam yang selalu kita lewati bersama.. dan selalu saja tentang hadirnya sepi.. galau.. resah dan kesunyian.. Saat aku menyapu dinding-dinding kamar kotak segi empat ini dengan pandangan mata yang penuh harap.. saat jiwa ini terpekur pada sebuah sudut sepi, dan kamu selalu setia untuk menemani aku dy.. saat aku menghamburkan buncahan kekecewaan kepadamu dy, saat itu kamu hanya terdiam dan selalu saja terdiam.. tapi aku tahu bahwa diammu adalah sebuah pelukan hangat untuk aku, dan kamu akan selalu dapat mengobati setiap kecewaku.. Dan entah sudah berapa ribu malam juga aku tidak memperdulikanmu dy.. saat sunyi ini telah dapat kulewati, kembali aku melemparmu pergi dari hari-hariku.. seakan kamu hanyalah sebuah masa lalu yang tak ingin kuingat kembali.. saat terpisah jarak antara aku dan kamu.. Harus kuakui bahwa aku memang ingin melupakanmu.. Namum malam ini aku kembali menemui kamu Dy.. ingin kembali ku isi hatimu dengan rasa yang kini kembali hadir, ingin ku tatap engaku dengan pandangan bahagia dan ingin aku memelukmu dengan perasaan rindu yang kini telah aku miliki.. setelah sekian lama aku terbiasa hidup tanpa asmara.. aku ingin kau mencoba untuk dengarkan apa yang ingin aku katakan..yang selama ini sungguh telah lama terpendam.. dan sungguh aku pun tak percaya dengan rasa ini sebelumnya, bahwa aku telah jatuh cinta kepadanya.. saat aku menatap teduhnya tatapan mata itu, saat aku terpesona dengan indah wajahnya dan saat aku terseret kedalam halus hatinya.. aku benar-benar dapat merasakan itu semua Dy, saat dia datang di hatiku.. ada sesuatu yang berbeda.. aku bertanya… mengapa begini dan apa yang sedang terjadi??.. tak pernah sebelumnya, dan tak pernah ku duga.. dan harus kuakui aku tak bisa tuk memungkiri,, bersamanya kurasakan.. yang tak pernah kurasakan sebelumnya, sebuah rasa yang dapat membuat aku tenang namun juga dapat membuatku gundah, dan rasa itu dapat membuat aku selalu ingin bersamanya.. aku ingin dia selalu menemani saat aku terluka.. saat aku tengah gundah dan saat aku sedang bahagia.. Dy aku ingin selalu menjaganya, saat dia terluka dan saat dia resah, dan ku ingin selalu menemaninya saat bahagia.. dan tak ingin aku meninggalkannya saat dirinya rapuh.. Dy.. aku ingin selalu menjaganya,.. melindunginya,..dan mendampinginya di setiap langkah-langkahnya. Tahukah kamu Dy.. dia telah membawaku kedalam hidupnya dan dia telah membasuh hatiku dengan rasa sayang, dan dia telah menyentuh cintaku dengan lembut.. senyumnya dan juga sedihnya adalah hidupku Dy.. dan naluriku telah berkata bahwa dia adalah milikku.. masih kuingat saat aku masih bertanya pantaskah aku untuk mendapatkan hatinya dan berharap dia mencintaiku setulus hatinya.. masih kuingat saat aku berharap sebuah kesempatan darinya.. saat itulah aku ingin selalu memberikan yang terbaik untuknya.. Dy.. aku telah memilih yang terbaik.. yang terindah untuk aku dan kuingin diapun memberikan yang terbaik untukku.. Dy.. dimataku dia begitu indah.. dan dia telah menghapus semua sesalku.. Dy.. kuingin kamu dapat menemani kembali malam-malamku saat aku ingin bercerita tentang dia, saat ingin ku ungkapkan rindu ini.. saat aku menatap teduh sinar matanya dan saat suaranya memenuhi hatiku.. ingin ku ceritakan itu semua ke kamu Dy.. dan bahkan aku ingin menceritakan saat aku sedih karena dia.. karena aku tahu dia pun memiliki kekurangan dan aku ingin mencintainya dengan segala kekurangannya.. Dan sebagai penutup malam ini aku ingin ucapkan kepadanya.. Selamat datang cinta .. selamat mengisi lembar baruku.. dan selamat datang dihidupku… Computer Diary 2008-10-12T18:51:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/memindahkan_controlfile_dan_redologfile_pada_oracle/ http://www.mercubuana-it.org/roninmorgue/index.php/site/memindahkan_controlfile_dan_redologfile_pada_oracle/#When:11:52:00Z tutorial_moving.txt Dear all.. Pada suatu hari.. halah, ko pembukaannya ga enak yah… langsung aja deh, pada suatu ketika… hmm kayanya sama noraknya deh… ya udah.. suatu saat ada kondisi dimana database klien gw perlu dilakukan proses pemindahan dan perubahan pada file-file kritis pada database oracle-nya.. ingat ini file-file kritis yah… file-file tersebut adalah : - controlfile - redolog file serta menghapus dan membuat kembali tablespace UNDO dan merubah parameter undo retentionnya… dan ini lah rencana aksi kita.. kerennya sih Action Planning : A. Moving Undo Tablespace :  1 Backup file-file yang akan dipindah : control file, redo log file 2 Create new Undo Tablespace 3 Mengganti parameter Undo Tablespace ke tablespace baru 4 Delete tablespace lama beserta content dan datafile-nya B. Rubah parameter Undo Retention 1 Mengganti nilai Undo Retention pada system C. Moving online Redo Log File 1 Drop redo log file dengan status INACTIVE 2 Create redo 3 Switch logfile D. Moving Control File 1 Create pfile baru dari spfile 2 Edit pfile baru (mengganti parameter Control File) 3 Shutdown Instance 4 Moving Control file ke path yang baru 5 Create spfile dari pfile yang sudah di edit 6 Startup Mount 7 Open database Dan inilah aksinya.. hehehe SQL> !df -k Filesystem kbytes used avail capacity Mounted on /dev/dsk/c1t0d0s0 4129290 954465 3133533 24% / /proc 0 0 0 0% /proc fd 0 0 0 0% /dev/fd mnttab 0 0 0 0% /etc/mnttab swap 3486720 16 3486704 1% /var/run swap 3487328 624 3486704 1% /tmp /dev/dsk/c1t0d0s3 4129290 2694240 1393758 66% /oracle /dev/dsk/c1t1d0s0 8257241 7639452 535217 94% /oradata /dev/dsk/c1t1d0s1 8257241 4144738 4029931 51% /oradata2 SQL> sho parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1 SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name like ‘&#xUN;DO%’; FILE_NAME TABLESPACE_NAME BYTES -------------------------------------------- ------------------------------ --------- /oradata/PRODUCTION/PRODUCTION/undotbs01.dbf UNDOTBS1 209715200 SQL> col “Tablespace” for a20 col “Allocated” for 999999999999 select ddf.TABLESPACE_NAME “Tablespace”, SQL> SQL> SQL> 2 ddf.BYTES “Allocated”, 3 round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) “Used %”, 4 round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) “Free %” 5 from (select TABLESPACE_NAME, 6 sum(BYTES) bytes 7 from dba_data_files 8 group by TABLESPACE_NAME) ddf, 9 (select TABLESPACE_NAME, 10 sum(BYTES) bytes 11 from dba_free_space 12 group by TABLESPACE_NAME) dfs 13 where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME 14 order by ddf.tablespace_name; Tablespace Allocated Used % Free % -------------------- ------------- ---------- ---------- DRSYS 39059456 98.99 1.01 EXAMPLE 152698880 99.74 .26 IDATA 209715200 .81 99.19 IINDEX 62914560 .1 99.9 INDX 26214400 .25 99.75 ODM 20971520 45.94 54.06 TBS_DDAA 4194304000 48.18 51.83 TOOLS 10485760 90.63 9.38 UNDOTBS1 209715200 1.19 98.81 USERS 26214400 .25 99.75 XDB 39976960 99.34 .66 11 rows selected. $ pwd /oradata2 $ mkdir PRODUCTION $ cd PRODUCTION $ pwd /oradata2/PRODUCTION A. Moving Undo Tablespace : SQL> create undo tablespace UNDOTBS2 datafile ‘/oradata2/PRODUCTION/undotbs02.dbf’ size 1000M reuse autoextend ON; Tablespace created. SQL> alter system set undo_tablespace=UNDOTBS2; System altered. SQL> sho parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS2 SQL> drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. B. Rubah parameter Undo Retention SQL> alter system set undo_retention=1800; System altered. SQL> sho parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 1800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS2 SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name like ‘&#xUN;DO%’; FILE_NAME TABLESPACE_NAME BYTES ------------------------------------------- ------------------------------ ---------- /oradata2/PRODUCTION/undotbs02.dbf UNDOTBS2 1048576000 Tablespace Allocated Used % Free % -------------------- ------------- ---------- ---------- DRSYS 39059456 98.99 1.01 EXAMPLE 152698880 99.74 .26 IDATA 209715200 .81 99.19 IINDEX 62914560 .1 99.9 INDX 26214400 .25 99.75 ODM 20971520 45.94 54.06 TBS_DDAA 4194304000 48.18 51.83 TOOLS 10485760 90.63 9.38 UNDOTBS2 1048576000 .13 99.87 USERS 26214400 .25 99.75 XDB 39976960 99.34 .66 11 rows selected. C. Moving online Redo Log File SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 CURRENT /oradata/PRODUCTION/PRODUCTION/redo01.log 887 2 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo02.log 885 3 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo03.log 886 SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 (’/oradata2/PRODUCTION/redo03.log’) size 100M Database altered. SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 CURRENT /oradata/PRODUCTION/PRODUCTION/redo01.log 887 2 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo02.log 885 3 UNUSED /oradata2/PRODUCTION/redo03.log 0 SQL> alter system switch logfile; System altered. SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b whe e a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 ACTIVE /oradata/PRODUCTION/PRODUCTION/redo01.log 887 2 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo02.log 885 3 CURRENT /oradata2/PRODUCTION/redo03.log 888 SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 ACTIVE /oradata/PRODUCTION/PRODUCTION/redo01.log 899 2 INACTIVE /oradata2/PRODUCTION/redo02.log 898 3 CURRENT /oradata2/PRODUCTION/redo03.log 900 D. Moving Control File SQL> !vi /oradata2/PRODUCTION/pfilenew.ora *.control_files=’/oradata2/PRODUCTION/control01.ctl’,’/oradata2/PRODUCTION/control02.ctl’,’/oradata/PRODUCTION/PRODUCTION/control03.ctl’ SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. $ mv /oradata/PRODUCTION/PRODUCTION/control01.ctl /oradata2/PRODUCTION/control01.ctl $ mv /oradata/PRODUCTION/PRODUCTION/control02.ctl /oradata2/PRODUCTION/control02.ctl SQL> create spfile from pfile=’/oradata2/PRODUCTION/pfilenew.ora’; File created. SQL> startup mount; ORACLE instance started. Total System Global Area 320308312 bytes Fixed Size 730200 bytes Variable Size 285212672 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes Database mounted. SQL> alter database open; Database altered. SQL> select status from v$instance; STATUS ---------- OPEN $ df -k Filesystem kbytes used avail capacity Mounted on /dev/dsk/c1t0d0s0 4129290 954473 3133525 24% / /proc 0 0 0 0% /proc fd 0 0 0 0% /dev/fd mnttab 0 0 0 0% /etc/mnttab swap 3500736 16 3500720 1% /var/run swap 3501344 624 3500720 1% /tmp /dev/dsk/c1t0d0s3 4129290 2899208 1188790 71% /oracle /dev/dsk/c1t1d0s0 8257241 7225908 948761 89% /oradata /dev/dsk/c1t1d0s1 8257241 5377883 2796786 66% /oradata2 Setelah proses ini selesai.. sangat disarankan untuk melakukan full backup database.... Computer DataBase 2008-10-03T11:52:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/oracle_dataguard_on_standart_edition/ http://www.mercubuana-it.org/roninmorgue/index.php/site/oracle_dataguard_on_standart_edition/#When:13:46:00Z dataguard_on_standart_edition.txt ================= || Primary || ================= 1. Disable db_recovery_file_dest = ‘’ and Check parameter db_recovery_file_dest SQL> ALTER SYSTEM SET db_recovery_file_dest=’’ SCOPE=BOTH; SQL> sho parameter db_recovery_file_dest; 2. Alter log_archive_dest and Check parameter log_archive_dest SQL> ALTER SYSTEM SET log_archive_dest=’/oradata/archive’ SCOPE=BOTH; SQL> sho parameter log_archive_dest 3. Check Archived log SQL> ARCHIVE LOG LIST; 4. Switch log file SQL> ALTER SYSTEM SWITCH LOGFILE; 5. Check Archived log and file SQL> ARCHIVE LOG LIST; $ ls /oradata/archive 6. Backup Database and Archived.log (RMAN): cd $ORACLE_HOME/bin $ ./rman target RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/oradata/backup/%d_%T_%u_s%s_p%p’ RMAN> run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; backup incremental level 0 tag full_backup format ‘/oradata/backup/%d_%T_%u_s%s_p%p’ database; backup incremental level 0 tag full_backup format ‘/oradata/backup/%d_%T_%u_s%s_p%p’ (current controlfile); backup archivelog all format ‘/oradata/backup/%d_%T_%u_s%s_p%p’ delete input; delete obsolete; } 8. Switch log file SQL> ALTER SYSTEM SWITCH LOGFILE; 9. Check Archived log and file SQL> ARCHIVE LOG LIST; $ ls /oradata/archive 10. Create some transaction to create archive SQL> grant connect, resource to test identified by test; SQL> create table test.testing as select * from dba_objects; SQL> select count(1) from test.testing; 11. Switch log file SQL> ALTER SYSTEM SWITCH LOGFILE; 12. Check Archived log and file SQL> ARCHIVE LOG LIST; $ ls /oradata/archive 13. RUN { ALLOCATE CHANNEL d1 DEVICE TYPE DISK; BACKUP DATAFILE 1 FORMAT ‘/u01/backups/%U’, ‘/u02/backups/%U’; } 13. Create Standby controlfile from primary database: SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/home/oracle/ctlstdby.ctl’; 14. Create pfile from spfile; ------------------------------------------------------------------------- 13. Shutdown database 14. Copy initprotecal.ora, orapwprotecal, control standby(ctlstdby.ctl), onlinelog, datafile, tempfile ================= || Standby || ================= 1. Edit pfile, change parameter *.control_files to *.control_files=’/u02/oradata/ctlfile/ctlstdby.ctl’ 2. Check validation of pfile parameter 3. Startup nomount pfile=’$ORACLE_HOME/dbs/initprotecal.ora’ 4. Startup mount SQL> alter database mount standby database; ================= || crontab || ================= on primary : ---------------------------------------------------------------------------------- $ cd /home/oracle $ vi move_standby.sh => rsync -e ssh -Pazv /oradata/flash_recovery_area/protecal/archivelog/ oracle@stby-db:/oradata/flash_recovery_area/protecal/archivelog/ rsync -e ssh -Pazv /oradata/archive/ oracle@stby-db:/oradata/archive/ $ chmod 775 move_standby.sh $ crontab -e => */3 * * * * /home/oracle/move_standby.sh $ crontab -l on standby : ---------------------------------------------------------------------------------- $ cd /home/oracle $ vi manual_standby.sh => ORACLE_SID=protecal ORACLE_BASE=/oracle/app ORACLE_HOME=/oracle/app/product/ora10.2 export LD_LIBRARY_PATH=$ORACLE_HOME/lib export TNS_ADMIN=$ORACLE_HOME/network/admin export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/sbin:. sqlplus -s “/nolog” <<-EOF conn /as sysdba recover standby database; AUTO $ chmod 775 manual_standby.sh $ crontab -e => */5 * * * * /home/oracle/manual_standby.sh $ crontab -l purging mail (oracle and root) => as root on primary and standby ---------------------------------------------------------------------------------- # cd /var/spool/mail # touch oracle.new # vi mail_oracle.sh => cp --reply=yes /var/spool/mail/oracle.new /var/spool/mail/oracle # chmod 775 mail_oracle.sh # crontab -e => */10 * * * * /var/spool/mail/mail_oracle.sh # crontab -l */2 * * * * rsync -e ssh -Pazv /home/oracle/test/ oracle@rumah:/home/oracle/hapus/ A. Setting ssh and scp on primary and standby B. Configure Primary and standby note: nama mesin dan domain dah gw rubah..tolong disesuaikan dengan mesin anda node 2 :ssh-keygen -t rsa ssh-keygen -t dsa node 1 :ssh-node_1 cat /oracle/.ssh/id_rsa.pub Node 2 --------- [oracle@rumah ~]$ ssh-keygen -t rsa [oracle@rumah ~]$ ssh-keygen -t dsa Node 1 --------- [oracle@vmwareas ~]$ ssh-keygen -t rsa [oracle@vmwareas ~]$ ssh-keygen -t dsa Node 1 ---------- [oracle@vmwareas ~]$ ssh node_1 cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys [oracle@vmwareas ~]$ ssh node_1 cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys Node 2 --------- [oracle@vmwareas ~]$ ssh node_2 cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys [oracle@vmwareas ~]$ ssh node_2 cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys Node 1 ---------- [oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys [oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys [oracle@vmwareas ~]$ ssh rumah cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys [oracle@vmwareas ~]$ ssh rumah cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys Node 2 ---------- [oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_rsa.pub >> /home/oracle/.ssh/authorized_keys [oracle@vmwareas ~]$ ssh vmwareas cat /home/oracle/.ssh/id_dsa.pub >> /home/oracle/.ssh/authorized_keys Computer DataBase, Computer Unix 2008-09-30T13:46:00+07:00 http://www.mercubuana-it.org/roninmorgue/index.php/site/adakah_aku_dihatimu/ http://www.mercubuana-it.org/roninmorgue/index.php/site/adakah_aku_dihatimu/#When:18:00:00Z Malam ini lampu padam. Aku melihat runcing api lilin bergerak-gerak dalam hitam. Seperti daun, seperti rambut kekasih dipermainkan angin. Ia bergerak-gerak pelan, lalu cepat bersilangan. Semakin cepat, semakin kecil, dan akhirnya padam, karena angin semakin lancang menyelinap jendela yang sedikit terbuka. Malam padam, semakin padam dalam pejam. Tapi kenapa ingatan semakin buncah menyebut arah? Aku bangkit, membuka jendela lebar-lebar. Terasa angin menusuk-nusuk, dingin merasuk. Ah, langit. Langit yang menyimpan bintang-bintang yang terang dan tenang. Inikah hidup? Nyala lilin redup, masih ada cahaya bintang. Kelak jika bintang-bintang hilang, masih ada cahaya siang. Jika ternyata sunyi, masih ada puisi. Juga dalam luka, masih ada cinta. Harapan tak pernah mati, seperti juga cahaya hati. Ia tak pernah berhenti, hanya berganti. Hidup memang tak perlu letih, tak perlu perih. Hidup memang tak perlu bersedih. Hidup hanya punya alasan tersembunyi untuk semua ini. Tak perlu marah dan resah. Berbahagialah yang tak pernah lelah. Setiap harapan yang baik membuat hidup ini terasa indah… Tuhan ciptakan Cinta dari Cahaya-Nya. Seperti yang Ia anugerahkan pada nafas malaikat-malaikat-Nya yang bertasbih dan bersujud dalam kepatuhan yang abadi. Cinta tentang Kesucian dan Kesejatian. Lalu ia ciptakan manusia berpasang-pasangan. Tuhan dapat dengan mudah mempertemukan mereka menjadi sepasang kekasih, seperti juga ia dapat dengan mudah memisahkan mereka… Kemudian hadirlah takdir tentang cahaya dan dosa, cinta dan air mata. Disanalah sepasang kekasih memahami dunia sebagai sebuah pencarian. Yang satu mencari dan merindukan yang lainnya… Aku berkelahi dengan bayanganku sendiri, di bawah lampu-lampu yang hampir mati, tak henti menduga dahaga dan menukar rasa lapar dengan sebungkus puisi yang dikerubungi lalat. Malam melengkapi muram, seperti tampak bayangan kekasih melintas menyalakan bara api di sepanjang helaan napasku. Sepasang matanya yang jernih, tak henti kuringkas dalam doa-doa, tak akan pernah kulepas… Mencintaimu membuatku mau belajar pada setiap orang di tepi jalan, diruas-ruas buku dan kaki langit yang paling sepi—aku mau belajar pada semesta, pada hidup. Tapi bukankah kita juga dapat “mati” oleh cinta jika kita tak menerima cinta sebagai sebuah pelajaran untuk mencintai yang lainnya dalam keadaan paling buruk sekalipun. Tiba-tiba jam berhenti setajam belati… Kekasih, terus terang, malam ini aku sebenarnya tak mengerti kenapa setiap ingatan tentang masa lalu selalu berharga untuk diungkapkan kembali, bahkan acapkali berharga untuk tetap dipertahankan. Mungkin ini semacam kerinduan. Aku memang selalu bahagia ketika memahami cinta dan kerinduan padamu sebagai sebuah gagasan dan semangat. Hingga aku cukup bahagia mempertahankannya. Karena aku merasa, setiapkali menerima kehadiran seseorang yang dicintai adalah menerima hidup yang maha tenang, nyaman dan giat berkarya serta merenung. Tetapi bisa juga berarti bermain dalam kecemasan. Bukankah kecemasan juga dapat melahirkan keindahan dan sebuah gairah berkarya? Akupun memang harus belajar untuk hidup tegap dalam setiap keputusanku, meski setiap keputusan itu benar-benar tak nyaman—termasuk ketika aku membuat keputusan untuk tetap bertahan mencintaimu. Aku pernah menulis catatan kecil di halaman pertama buku kawanku: “…sudah saatnya kita mempercayai ada banyak hal yang dapat membuat kita merdeka dalam hidup ini, meski dicari dengan cara yang paling tak nyaman sekalipun.” Aku hanya ingin membuktikan diriku dalam doa-doa yang dapat mengubah setiap kesepian menjdi kenangan; mengubah cinta menjadi karya. Aku ingin belajar mengisi seluruh ruang dengan segenapmu. Aku percaya, ada sesuatu yang tetap kita pertahankan dalam hidup sesingkat ini. Tapi ia sesuatu yang tak bisa disebut. Hanya jika cinta utuh dimengerti, dengan bathin yang bersih, dengan doa… Hingga aku mencintaimu dalam diam. Seperti diam ketika dihadapkan pada kenyataan yang sangat rumit untuk diuraikan dipermukaan keinginan. Tetapi kadang cinta membuat kita untuk tetap siap berpikir. Dan aku telah menuliskan setiap cercah pikiran itu. Seperti ketika kita mencintai seseorang, dan ia yang kita cintai tak pernah sedikitpun mencintai kita. Maka kita hanyut dalam arus kegelisahan. Dalam itu, akupun berusaha melupakanmu dengan berbagai cara, tetapi selalu saja malam yang hening dan puisi yang sunyi berulangkali menegaskanmu. Tetapi aku percaya cinta menjadi agung jika kita mampu dan ikhlas mencintai seseorang yang ternyata tak mencintai kita. Nurani adalah sabda paling lengkap dan ia kadang menunjukkan arah lain. Demikian penempuhan ini, sabda membangun titik dikeseluruhanmu. Aku berharap ada iman yang dahaga dan demikian tetap menyala, menerangi jalan menujumu. Aku pikir, setiap orang punya alasan yang kokoh kenapa ia mesti mempertahankan keyakinannya atas cinta, meski terjal. Mereka berkata: “kenapa harus cinta dipaksakan?” aku menunduk, diam. Tapi aku harus belajar bahagia dapat mencintai seseorang, sekalipun ia tak mencintaiku dan terasa jauh, menjauh menjelma bayangan… Hingga aku masih berdiri, disini, menatap batas langit—mungkin juga batasmu. Ketika berjalan sendiri, hidupku demikian cukup. aku ingin percaya bahwa langit telah teduh dan bumi bukan lagi riuh. Aku sendiri ketika manusia terdengar seperti gelas pecah dan kunang-kunang yang saling menjauh keluar dari dadanya. Aku telah sendiri ketika manusia pecah dan menjauh, ketika yang satu tak lagi siap menghargai yang lainnya, ketika yang satu tak lagi siap mencintai yang lainnya. Aku berjalan sendiri dan perlahan menghimpun keheningan dan kenangan disudut kecil yang—aku yakin—kebanyakan orang tak suka melewatinya. Menyendiri. Kekasih, kamu berhak sepenuhnya tak bicara sedikitpun untuk menjawab tentang ini. Sebab keseluruhan dirimu telah mengajarkan aku untuk percaya bahwa cinta bisa diajukan bukan sebagai suatu pertanyaan, tetapi sesuatu yang dapat menjadi pelajaran untuk menjadi seseorang yang terbaik bagi yang lainnya, mampu bersikap memberi tanpa menuntut untuk selalu ingin menerima. Mungkin suatu saat tiba-tiba aku akan berjalan menikung dan meletih dalam hidup, maka aku dapat belajar banyak hal dari kenangan tentang arti melangkah. Belajar tentang arti kehilangan, kecemasan, keindahan, ketabahan, kerinduan dan arti cinta, doa, arti berharap… Hingga tiba sebuah hidup yang demikian sederhana ketika aku terluka karena kau luka. Aku bahagia karena kau bahagia… Computer Diary 2008-07-06T18:00:00+07:00