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

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:

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 UDIT%’
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.

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.

Membuat ulang/re-create controlfile di oracle

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.

Membuat RMAN dengan crontab

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