LaGiPusHinGWitH ----- IT

About This Site

Teknologi Informasi sangatlah penting untuk menunjang kemajuan ilmu pengetahuan di Indonesia pada khususnya. Dan ini semua haruslah ditunjang dengan kemampuan yang sepadan dengan kemajuan teknologi itu sendiri, terlebih untuk para praktisi IT itu sendiri. Akhir kata "Selamat berkarya" untuk kita semua. koy^k^guy^ - Owner

Navigation

Home |

Calendar

<<	February 2012					>>
S	M	T	W	T	F	S
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29

Monthly Archives

Most recent entries

Syndicate

RSS 2.0
Atom

Referrers

Site Credits

Design by:
BlogMoxie

Computer DataBase

Tuesday, September 28, 2010

Create Oracle Tablespace on SAP with BRTools

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

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)

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)

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)

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

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)

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:

Posted by roninmorgue on 09/28 at 09:19 PM
Computer DataBase • (1) Comments • (0) Trackbacks • Permalink

Monday, October 05, 2009

Audit on Oracle Database

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.

Posted by roninmorgue on 10/05 at 04:05 PM
Computer DataBase • (0) Comments • (528) Trackbacks • Permalink

Thursday, December 18, 2008

Membuat Oracle Data Guard

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.

Posted by roninmorgue on 12/18 at 08:47 AM
Computer DataBase • (0) Comments • (735) Trackbacks • Permalink

Sunday, November 23, 2008

Membuat ulang/re-create controlfile di oracle

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:\ORACLE\PRODUCT\10.2.0\ORADATA\TRAINING\TEMP01.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.

Posted by roninmorgue on 11/23 at 10:13 PM
Computer DataBase • (0) Comments • (7477) Trackbacks • Permalink

Membuat RMAN dengan crontab

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

=================================

Posted by roninmorgue on 11/23 at 09:57 PM
Computer DataBase • (0) Comments • (563) Trackbacks • Permalink

Friday, October 03, 2008

Memindahkan controlfile dan redologfile pada Oracle

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;

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#;

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....

Posted by roninmorgue on 10/03 at 06:52 PM
Computer DataBase • (0) Comments • (701) Trackbacks • Permalink

Tuesday, September 30, 2008

Oracle Dataguard on Standart Edition

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

Posted by roninmorgue on 09/30 at 08:46 PM
Computer DataBase • Computer Unix • (0) Comments • (3206) Trackbacks • Permalink

Wednesday, May 28, 2008

Instalasi 10g di HP-UX Itanium

install-10g-hpux.txt

pemeriksaan memory dan swap

# /usr/contrib/bin/machinfo | grep -i Memory
# /usr/sbin/swapinfo -a

pemeriksaan kapasitas direktori /tmp

# bdf /tmp

untuk melihat sisa/penggunaan hardisk

# bdf

Memeriksa konfigurasi jaringan

- Konfigurasi name resolution
1.memeriksa file /etc/hosts untuk di samakan dengan file nsswitch.conf
# cat /etc/nsswitch.conf | grep hosts

2.verifikasi hostname
# hostname

3.verifikasi nama domain
# domainname

4.verifikasi file host memiliki nama host yang qualified
# cat /etc/hosts | grep `eval hostname`

example output:
192.168.100.16 myhost.us.mycompany.com myhost
127.0.0.1 localhost localhost.localdomain

jika file tidak berisi nama host yang benar, silahkan untuk dirubah

memperbaharui parameter kernel
-menggunakan kcweb application
# /usr/sbin/kcweb -F

membuat group, user, dan direktori
- grup dan user yang akan dibuat :
grup oracle inventory(oinstall)
grup OSDBA(dba)
owner software oracle(oracle)
user tanpa privileged(nobody)

1.cek keberadaan grup pada sistem
# more /var/opt/oracle/oraInst.loc

jika file tersebut ada dan menghasilkan output :

inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall

maka oracle inventori sudah memiliki grup oinstall

2.jika diperlukan, buat grup oinstall
# /usr/sbin/groupadd oinstall

3.buat grup OSDBA jika belum ada
# /usr/sbin/groupadd dba

4.periksa apakah user oracle sudah terbentuk dan memiliki grup yang benar
# id oracle

jika user oracle sudah ada dan menampilkan informasi seperti dibawah ini, maka oinstall adalah grup utama dan dba adalah
grup kedua dari user oracle

uid=440(oracle) gid=200(oinstall) groups=201(dba),202(oper)

5. - jika user oracle sudah ada tapi grup utama bukan oinstal atau bukan member dari grup dba.
# /usr/sbin/usermod -g oinstall -G dba oracle
- jika user oracle belum ada
# /usr/sbin/useradd -g oinstall -G dba oracle

6.buat password untuk user oracle
#passwd oracle

7.melihat keberadaan user nobody
# id nobody

jika tidak ada user nobody, maka buat user nobody
# /usr/sbin/useradd nobody

- direktori yang akan dibuat :
Oracle base directory
Oracle inventory directory
Oracle home directory

membuat oracle base directory dan menspesifikasikan owner, grup dan permision
# mkdir -p /mount_point/app/oracle_sw_owner
# chown -R oracle:oinstall /mount_point/app/oracle_sw_owner
# chmod -R 775 /mount_point/app/oracle_sw_owner

jika mount point /u01 dan user untuk oracle software owner adalah oracle, maka oracle base directory adalah

/u01/app/oracle

tambahan jika ingin menempatkan flash recovery area dan file data pada mount point yang berbeda

Database file directory:

# mkdir /mount_point/oradata
# chown oracle:oinstall /mount_point/oradata
# chmod 775 /mount_point/oradata

Recovery file directory (flash recovery area):

# mkdir /mount_point/flash_recovery_area
# chown oracle:oinstall /mount_point/flash_recovery_area
# chmod 775 /mount_point/flash_recovery_area

Mengkonfigurasi/set environment user oracle

1. Buka X terminal baru
2. setting agar aplikasi X window dapat berjalan dalam sistem

$ xhost fully_qualified_remote_host_name atau xhost +

sebagai contoh:

$ xhost somehost.us.acme.com

3. jika belum login kedalam sistem dimana akan diinstall oracle maka loginlah dengan user oracle
4. jika tidak login dengan user oracle, maka rubahlah menjadi user oracle
$ su - oracle

5. untuk melihat shell yang aktif
$ echo $SHELL

6. buka file startup shell user oracle

- C shell (csh or tcsh):
% vi .login

- Bash shell di redhat
$ vi .bash_profile

- Bourne shell atau korn shell
$ vi .profile

7. edit file untuk mendefinisikan mode pembuatan file default dan setting ORACLE_BASE dan ORACLE_SID environment variable
umask 022
ORACLE_SID=KPPDB
ORACLE_BASE=/oracle10gr2/app
ORACLE_HOME=$ORACLE_BASE/product/10.2
export ORACLE_SID ORACLE_BASE ORACLE_HOME

8. simpan file dan keluar dari editor

9.untuk menjalankan script startup shell
- C shell (csh or tcsh):
% source ./.login

- Bash shell di redhat
$ . ./.bash_profile

- Bourne shell atau korn shell
$ . ./.profile

10.jika tidak melakukan instalasi pada sistem lokal, dan untuk membuat aplikasi X berjalan di sistem lokal
- Bourne, bash atau korn shell:

$ DISPLAY=local_host:0.0 ; export DISPLAY

- C shell:

% setenv DISPLAY local_host:0.0

11.jika direktori /tmp kurang dari 400 MB
- periksa dengan bdf untuk melihat mount point yang ada.
- buat direktori temporary dan set permissionnya
$ su - root
# mkdir /mount_point/tmp
# chmod a+wr /mount_point/tmp
# exit
- setting tmp evironment variables

Bourne, Bash, or Korn shell:

$ TMP=/mount_point/tmp
$ TMPDIR=/mount_point/tmp
$ export TMP TMPDIR

C shell:

% setenv TMP /mount_point/tmp
% setenv TMPDIR /mount_point/tmp

12.untuk memastikan semua environment diset dengan benar, jalankan perintah

$ umask
$ env | more

pastikan tampilan yang dihasilkan adalah nilai 22,022,atau 0022 dan semua environment variables memiliki nilai yang
benar.

Mounting CD/DVD

1. jalankan dari user root
$ su - root

2. unmount DVD
# /usr/sbin/umount /SD_DVD
/SD_DVD adalah contoh mount untuk DVD

3. masukkan CD pada disc drive, jalankan perintah
# /usr/sbin/mount -F cdfs -o rr /dev/dsk/cxtydz /SD_DVD

contoh, /SD_DVD adalah mount point untuk CD dan /dev/dsk/cxtydz adalah nama device dari disc drive,
sebagai contoh /dev/dsk/c0t2d0

4. Jika Oracle universal installer menampilkan kotak dialog lokasi disk, masukkan path mount point disk
contoh /SD_DVD

Instalasi

1. jika dari DVD
$ /mount_point/db/runInstaller

2. jika dari hard disk, masuk ke path software dan jalankan
$ ./runInstaller

Posted by roninmorgue on 05/28 at 09:34 AM
Computer DataBase • Computer Unix • (4) Comments • (3192) Trackbacks • Permalink

Tuesday, March 11, 2008

Syncronize archive log

Sinkronisasi-archivedLog.txt

Artikel ini berhubungan dengan artikel gw yang mengenai merubah ukuran archived log pada database, apabila kita telah membackup archived log maka kita perlu menghapusnya secara fisik pada server kita, dan setelah kita menghapus file archive secara fisik dari server/OS maka pada database harus dilakukan sinkronisasi?.

Langkah-langkah teknis :

[root@alt2007 archivelog]# pwd
/app/oracle/flash_recovery_area/MCC2007/archivelog

[root@alt2007 archivelog]# ls -al
total 44
drwxr-x--- 11 oracle oinstall 4096 Feb 15 00:53 .
drwxr----- 5 oracle oinstall 4096 Apr 2 2007 ..
drwxr-x--- 2 oracle oinstall 4096 Feb 5 23:40 2008_02_05
drwxr-x--- 2 oracle oinstall 4096 Feb 6 23:00 2008_02_06
drwxr-x--- 2 oracle oinstall 4096 Feb 7 23:00 2008_02_07
drwxr-x--- 2 oracle oinstall 4096 Feb 8 17:34 2008_02_08
drwxr-x--- 2 oracle oinstall 4096 Feb 11 23:00 2008_02_11
drwxr-x--- 2 oracle oinstall 4096 Feb 12 23:10 2008_02_12
drwxr-x--- 2 oracle oinstall 4096 Feb 13 23:00 2008_02_13
drwxr-x--- 2 oracle oinstall 4096 Feb 14 23:56 2008_02_14
drwxr-x--- 2 oracle oinstall 4096 Feb 15 15:13 2008_02_15

[root@alt2007 archivelog]# rm -rf /app/oracle/flash_recovery_area/MCC2007/
archivelog/2008_02_0*
[root@alt2007 bin]# ./rman target gunawan/mccdba@mcc2007
Recovery Manager: Release 10.1.0.3.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: MCC2007 (DBID=3021959133)
RMAN> crosscheck copy;
====================== potong =========================================
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39940_614016670.dbf recid=39806 stamp=647924450
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39941_614016670.dbf recid=39807 stamp=647928023
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39942_614016670.dbf recid=39808 stamp=647931658
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39943_614016670.dbf recid=39809 stamp=647935254
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39944_614016670.dbf recid=39810 stamp=647938818
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39945_614016670.dbf recid=39811 stamp=647942443
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39946_614016670.dbf recid=39812 stamp=647946018
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39947_614016670.dbf recid=39813 stamp=647949632
validation succeeded for archived log
archive log filename=/app/oracle/oradata/mcc2007/archive/1_39948_614016670.dbf recid=39814 stamp=647953230
Crosschecked 598 objects

RMAN> delete noprompt expired copy;

=================potong===============

deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39521_3v5nhc3n_.arc recid=39387 stamp=646597083
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39522_3v5qv763_.arc recid=39388 stamp=646600535
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39523_3v5v29vv_.arc recid=39389 stamp=646603834
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39524_3v5v9zsj_.arc recid=39390 stamp=646604080
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39525_3v5y0n6k_.arc recid=39391 stamp=646606852
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39526_3v61jfn6_.arc recid=39392 stamp=646610430
deleted archive log
archive log filename=/app/oracle/flash_recovery_area/MCC2007/archivelog/2008_02_13/
o1_mf_1_39527_3v6518sb_.arc recid=39393 stamp=646614041
Deleted 176 EXPIRED objects
RMAN>

Selesai....

Posted by roninmorgue on 03/11 at 12:48 AM
Computer DataBase • (0) Comments • (770) Trackbacks • Permalink

Merubah ukuran archived Log

Alter-archived-Sized.txt

Disaat kita menentukan ukuran dari archived log, ternyata kita terlalu kecil dalam menentukan ukurannya
yang dapat menyebabkan database ‘down’, apabila itu terjadi maka kita perlu untuk memperbesar ukuran dari
archived log tersebut.

Langkah-langkah teknis :

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=60G SCOPE=BOTH;

note : lakukan proses backup archived log secara rutin dan hapus dari database

Posted by roninmorgue on 03/11 at 12:46 AM
Computer DataBase • (0) Comments • (3353) Trackbacks • Permalink

Alter Block Size for tablespace

alter-block-size.txt

Disaat Ukuran ‘default’ dari Block tablespace tidak mencukupi kebutuhan dari database kita, maka diperlukan ukuran block yang lebih besar, biasanya ini diperlukan untuk database yang menjalankan proses yang besar seperti warehouse dan mining.

langkah-langkah teknis :

SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size big integer 164M
db_cache_size big integer 0

SQL> alter system set db_16k_cache_size = 80M;

System altered.

Selesai.....

Posted by roninmorgue on 03/11 at 12:45 AM
Computer DataBase • (0) Comments • (566) Trackbacks • Permalink

Cleaning Temporary Tablespaces

Cleansing-temp-Tablespace.txt

Apabila ukuran temporary tabelspace mulai membengkak, ini tentu mempengaruhi kinerja dari database Oracle.
Oleh karena itu kita harus mengecilkan ukurannya, lebih tepatnya sih menggantinya dengan temporary tablespace yang baru. Ok langsung saja, ini adalah langkah-langkah teknisnya :

1. ALTER DATABASE TEMPFILE ‘/app/oracle/oradata/db2006/temp01.dbf’ resize 500M;
2. CREATE TEMPORARY TABLESPACE temp2
TEMPFILE ‘/app/oracle/oradata/db2006/temp2_01.dbf’ SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
3. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
4. DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
5. CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/app/oracle/oradata/db2006/temp01.dbf’ SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
6. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
7. DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Ok, sekarang ukuran temporary tablespace kita sudah sesuai dengan keinginan kita.

Posted by roninmorgue on 03/11 at 12:43 AM
Computer DataBase • (0) Comments • (980) Trackbacks • Permalink

Create Job Scheduller

Create-job-schedule.txt

Langsung aja deh, gw lagi males pake kalimat pembukaan, kali ini gw mau kasih contoh untuk membuat ‘job scheduller’ pada database oracle.

Sample Code

BEGIN
sys.dbms_scheduler.create_job(
job_name => ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE"’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into customer_name_card
select * from customer_name_card@mccconect
where orig_input_date >= ‘’1-oct-2000’’’,
start_date => to_timestamp_tz(’2006-12-20 17:17:00 +7:00’, ‘YYYY-MM-DD HH24:MI:SS TZH:TZM’),
job_class => ‘DEFAULT_JOB_CLASS’,
comments => ‘Mengupdate customer_name_card’,
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE"’, attribute => ‘logging_level’, value => DBMS_SCHEDULER.LOGGING_FULL);
sys.dbms_scheduler.enable( ‘“GUNAWAN”."CUSTOMERNAMECARDUPDATE”’ );
END;

BEGIN
sys.dbms_scheduler.create_job(
job_name => ‘“GUNAWAN”."IMPORTAIG"’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into master_trx
select custnum, ‘’4. jan-mar06’’ as periode, ‘’CREDIT CARD’’ as tndrtype
from mcc_tm_tdrtypebycpc
where (periode between ‘’2006-01’’ and ‘’2006-03’’) and
(tndrtype in (’’2’’,’’3’’,’’4’’,’’5’’,’’6’’,’’7’’,’’8’’,’’9’’,’’10’’,’’11’’)) and
(custnum not in (select distinct custnum from mcc_tm_tdrtypebycpc_unik))
group by custnum’,
start_date => to_timestamp_tz(’2007-02-17 20:00:00 +7:00’, ‘YYYY-MM-DD HH24:MI:SS TZH:TZM’),
job_class => ‘DEFAULT_JOB_CLASS’,
comments => ‘import for aig’,
auto_drop => FALSE,
enabled => TRUE);
END;

selesai..................

Posted by roninmorgue on 03/11 at 12:40 AM
Computer DataBase • (0) Comments • (2670) Trackbacks • Permalink

Restore SPFile from backup

Restore-SPFile.txt

lansung juga deh.....tanpa basa-basi, soalnya gw lagi males basa-basi yang nantinya malah jadi basi ....

Apabila kita ingin mengembalikan SPFile yang terakhir kita backup

Langkah-langkah teknis :

SQL> shutdown immediate;
SQL> restore spfile from ‘/app/oracle/flash_recovery_area/DB2006/autobackup/2007_01_29/
o1_mf_s_613129956_2vtrcoxr_.bkp’;
SQL> startup;

Selesai......

Posted by roninmorgue on 03/11 at 12:36 AM
Computer DataBase • (0) Comments • (930) Trackbacks • Permalink

Sunday, December 09, 2007

Checking User Lock

userLock.txt

Seringkali dalam menangani sebuah server database, seorang DBA mengalami/mendapatkan server-nya
crash/hang. Ini pula yang terkadang saya alami, ada laporan dari department lain bahwa database tidak
dapat diakses untuk input data maupun melihat data (database hanya diam tidak menunjukkan reaksi apapun juga).

Dan ini merupakan tanggung jawab seorang DBA untuk melakukan analisa serta mencari tahu apa permasalahannya.
Singkat cerita, saya langsung melakukan pemeriksaan dan berdasarkan pengalaman biasanya yang menyebabkan
hal ini adalah masalah di size, SGA memori, Object Lock, User Lock, dll.

Dari semua kemungkinan tersebut didapatkan bahwa permasalahan disebabkan oleh User Lock, maksudnya disini
ada user-user yang menggunakan resource pada database tetapi proses dari user tersebut terkunci oleh database
dan hal ini akan membuat database menjadi crash.

Technical Review :

1. Seperti biasa jalankan SQlplus dan login ke database dengan role DBA
2. Lihat field-field yang ada pada tabel sistem dba_locks

SQL> desc dba_locks;
Name Null? Type
----------------------------------------- -------- ----------------------------
SESSION_ID NUMBER
LOCK_TYPE VARCHAR2(26)
MODE_HELD VARCHAR2(40)
MODE_REQUESTED VARCHAR2(40)
LOCK_ID1 VARCHAR2(40)
LOCK_ID2 VARCHAR2(40)
LAST_CONVERT NUMBER
BLOCKING_OTHERS VARCHAR2(40)

3. Lihat session-session pada database yang memiliki kemungkinan terkunci

SQL> select * from dba_locks where mode_held = ‘Exclusive’;

SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS

3 Redo Thread Exclusive None 1 0 38934 Not Blocking
9 Transaction Exclusive None 196645 3730106 6714 Not Blocking
13 Transaction Exclusive None 1703965 3655 0 Not Blocking
13 Distributed Xaction Exclusive None 66 0 0 Not Blocking
274 Transaction Exclusive None 458799 2187103 7527 Blocking
282 Transaction Exclusive None 131115 3026301 5534 Not Blocking
329 Transaction Exclusive None 589833 2876171 4621 Not Blocking
377 Transaction Exclusive None 393240 2944259 4041 Not Blocking
467 Transaction Exclusive None 983041 12005 724 Not Blocking

9 rows selected.

Terlihat ada 9 session yang memiliki kemunginan dan kita lihat pada field BLOCKING_OTHERS, session yang sudah pasti terkunci
dan kemungkinan besar penyebab permasalahan ini. Dari data yang ada terlihat bahwa session dengan ID 274 memilki type transaksi
dan status ‘BLOCKING’

4. Dari sini kita lanjutkan dengan mencari siapa pemilik session ID tersebut, Lihat field-field pada
tabel sistem v$session

SQL> desc v$session;

SQL> select * from v$session where sid = ‘274’;

SADDR SID SERIAL# AUDSID PADDR USER# USERNAME STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE TERMINAL
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ---------------- -------- -----
00000003C7E8B458 274 70 4700007 00000003C7CC8E88 795 XXXXX INACTIVE DEDICATED 795 XXXXX Gugun 5916:5248 DATABASE\GUNAWAN GUNAWAN

Ok...tidak semua data pada field di perlihatkan karena terlalu banyak dan juga user saya edit, tetapi ini sudah cukup untuk mendapatkan data yang diperlukan:

Dari data ini bisa terlihat bahwa status user ini Tidak Aktif/INACTIVE.

5. Langkah terakhir adalah kita ‘membunuh’ session yang tidak diperlukan ini,
dan parameter yang diperlukan adalah SID dan SADDR.

SQL> alter system kill session ‘274,00000003C7E8B458’; --> alter system kill session ‘SID,SADDR’;

Selesai....akhirnya database dapat berjalan lagi dengan lancar.

PS : 1. Sebelum kill session kita harus yakin bahwa session tersebut sudah tidak aktif
2. Setelah proses ini berjalan lakukan analisa mengapa session ini bermasalah agar tidak terulang kembali.</pre>

Posted by roninmorgue on 12/09 at 10:56 PM
Computer DataBase • (0) Comments • (409) Trackbacks • Permalink

Page 1 of 2 pages 1 2 >

LaGiPusHinGWitH ----- IT

About This Site

Navigation

Search

Calendar

Categories

Monthly Archives

Most recent entries

Syndicate

Site Statistics

Site Credits

Computer DataBase