Tablespace | samadhandba

Posts Tagged ‘Tablespace’

SAP- Brtools – Drop Tablespace step by step

Posted in SAP DBA, tagged BRTOOL, drop tablespace, SAP, Tablespace on July 31, 2011| 2 Comments »

Dear Friens back to my Oracle journey after long time. In our organisation I got the opportunity to work on SAP as well from Database point of issue.I want to make my blog as complete solution for BRtools.So again here I am coming with new post to drop tablespace in DB using the BRtools.

bash-3.00$ brtools
BR0651I BRTOOLS 7.20 (1)

BR0280I BRTOOLS time stamp: 2011-08-01 00.55.52
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: 2011-08-01 00.55.58
BR0663I Your choice: ‘2’

BR0280I BRTOOLS time stamp: 2011-08-01 00.55.58
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:
3
BR0280I BRTOOLS time stamp: 2011-08-01 00.56.01
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2011-08-01 00.56.01
BR0657I Input menu 83 – please enter/check input values
——————————————————————————-
BRSPACE options for drop tablespace

1 – BRSPACE profile (profile) …… [initSAM.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 initSAM.sap -s 20 -l E -f tsdrop]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-08-01 00.56.09
BR0663I Your choice: ‘3’
BR0681I Enter string value for “tablespace” []:
PSAPSR3SAM
BR0280I BRTOOLS time stamp: 2011-08-01 00.56.34
BR0683I New value for “tablespace”: ‘PSAPSR3SAM’

BR0280I BRTOOLS time stamp: 2011-08-01 00.56.34
BR0657I Input menu 83 – please enter/check input values
——————————————————————————-
BRSPACE options for drop tablespace

1 – BRSPACE profile (profile) …… [initSAM.sap]
2 – Database user/password (user) .. [/]
3 ~ Tablespace name (tablespace) … [PSAPSR3SAM]
4 – Confirmation mode (confirm) …. [yes]
5 – Scrolling line count (scroll) .. [20]
6 – Message language (language) …. [E]
7 – BRSPACE command line (command) . [-p initSAM.sap -s 20 -l E -f tsdrop -t PSAPSR3SAM]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2011-08-01 00.56.42
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0291I BRSPACE will be started with options ‘-p initSAM.sap -s 20 -l E -f tsdrop -t PSAPSR3SAM’

BR0280I BRTOOLS time stamp: 2011-08-01 00.56.42
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRTOOLS time stamp: 2011-08-01 00.56.56
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

###############################################################################

BR1001I BRSPACE 7.20 (1)
BR1002I Start of BRSPACE processing: segljgdc.tsd 2011-08-01 00.56.56
BR0484I BRSPACE log file: /oracle/SAM/sapreorg/segljgdc.tsd

BR0280I BRSPACE time stamp: 2011-08-01 00.56.56
BR1009I Name of database instance: SAM

BR1010I BRSPACE action ID: segljgdc
BR1011I BRSPACE function ID: tsd
BR1012I BRSPACE function: tsdrop

BR0280I BRSPACE time stamp: 2011-08-01 00.56.57
BR0657I Input menu 310 – please enter/check input values
——————————————————————————-
Options for dropping of tablespace PSAPSR3SAM

1 * Number of files in tablespace (files) . [1]
2 * Total tablespace size in MB (size) …. [500]
3 – Force tablespace drop (force) ……… [no]
4 – SQL command (command) …………….. [drop tablespace PSAPSR3SAM]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2011-08-01 00.57.08
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-08-01 00.57.08
BR1070I Searching for segments in tablespace PSAPSR3SAM…
BR0285I This function can take several seconds/minutes – be patient…

BR0280I BRSPACE time stamp: 2011-08-01 00.57.08
BR1071I Tablespace PSAPSR3SAM is empty

BR0280I BRSPACE time stamp: 2011-08-01 00.57.08
BR0370I Directory /oracle/SAM/sapreorg/segljgdc created

BR0280I BRSPACE time stamp: 2011-08-01 00.57.09
BR0319I Control file copy created: /oracle/SAM/sapreorg/segljgdc/cntrlSAMold 13844480

BR0280I BRSPACE time stamp: 2011-08-01 00.57.10
BR0768I Tablespace PSAPSR3SAM set OFFLINE

BR0280I BRSPACE time stamp: 2011-08-01 00.57.10
BR1090I Dropping tablespace PSAPSR3SAM…

BR0280I BRSPACE time stamp: 2011-08-01 00.57.11
BR1016I SQL statement ‘drop tablespace PSAPSR3SAM’ executed successfully
BR1069I Tablespace PSAPSR3SAM dropped successfully

BR0280I BRSPACE time stamp: 2011-08-01 00.57.11
BR0794I Database file /oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1 deleted
BR0480I Directory /oracle/SAM/sapdata4/sr3sam_1 deleted

BR0280I BRSPACE time stamp: 2011-08-01 00.57.11
BR0340I Switching to next online redolog file for database instance SAM…
BR0321I Switch to next online redolog file for database instance SAM successful

BR0280I BRSPACE time stamp: 2011-08-01 00.57.13
BR0319I Control file copy created: /oracle/SAM/sapreorg/segljgdc/cntrlSAM.new 13844480

BR0280I BRSPACE time stamp: 2011-08-01 00.57.13
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRSPACE time stamp: 2011-08-01 00.57.19
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-08-01 00.57.19
BR1020I Number of tablespaces processed: 1
BR1003I BRSPACE function ‘tsdrop’ completed

BR1008I End of BRSPACE processing: segljgdc.tsd 2011-08-01 00.57.19
BR0280I BRSPACE time stamp: 2011-08-01 00.57.19
BR1005I BRSPACE completed successfully

###############################################################################

BR0292I Execution of BRSPACE finished with return code 0

BR0280I BRTOOLS time stamp: 2011-08-01 00.57.19
BR0256I Enter ‘c[ont]’ to continue, ‘s[top]’ to cancel BRTOOLS:
c
BR0280I BRTOOLS time stamp: 2011-08-01 00.57.26
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRTOOLS time stamp: 2011-08-01 00.57.26
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:
s
BR0280I BRTOOLS time stamp: 2011-08-01 00.57.29
BR0663I Your choice: ‘s’
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
y
BR0280I BRTOOLS time stamp: 2011-08-01 00.57.31
BR0257I Your reply: ‘y’
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2011-08-01 00.57.31
BR0654I BRTOOLS terminated with errors
bash-3.00$

Read Full Post »

SAP BRTOOLS for table reorganization

Posted in SAP DBA, tagged BRTOOL, Oracle Database, SAP, Tablespace on April 18, 2011| 6 Comments »

** Tested with UNIX / Solarise operating system.

Sorry guy’s for delay on new post.As we were on trip to GOA for one week.So now we will continue with BR Tool.

So here is another one tested example for table reorganization using the BRTOOL. Hope you guys enjoy with this one as well…..!!!!

bash-3.00$ /oracle/brspace -u / -f tbreorg -t BALDAT -degree 8
BR1001I BRSPACE 7.00 (51)
BR1002I Start of BRSPACE processing: sefphmbn.tbr 2011-04-06 07.40.07
BR0484I BRSPACE log file: /oracle/P2/sapreorg/sefphmbn.tbr
BR1301W Error message from likeywlib:
===…could not load SSF library /usr/sap/SAM/SYS/exe/run/libsapsecu.so .

BR1301W Error message from likeywlib: likey_init: Tried to load SAPSECU lib (“/usr/sap/SAM/SYS/exe/run/libsapsecu.so”), rc = 10.
BR1301W Error message from likeywlib:
===…could not load SSF library libsapsecu.so .

BR1301W Error message from likeywlib: likey_init: Couldn’t load SAPSECULIB (“libsapsecu.so”) using function SsfSupInit (), rc = 10.
BR1302W Initialization of license key library likeywlib failed, return code 1
BR1304W Checking SAP license failed at location BrLicCheck-108
BR0602W No valid SAP license found – please contact SAP

BR0280I BRSPACE time stamp: 2011-04-06 07.40.14
BR1009I Name of database instance: SAM

BR1010I BRSPACE action ID: sefphmbn
BR1011I BRSPACE function ID: tbr
BR1012I BRSPACE function: tbreorg

BR0280I BRSPACE time stamp: 2011-04-06 07.40.22
BR0657I Input menu 353 – please enter/check input values
——————————————————————————-
Options for reorganization of tables: SAPSR3.BALDAT (degree 1)

1 ~ New destination tablespace (newts) …….. []
2 ~ Separate index tablespace (indts) ……… []
3 – Parallel threads (parallel) …………… [1]
4 ~ Table/index parallel degree (degree) …… [8]
5 – Create DDL statements (ddl) …………… [yes]
6 ~ Category of initial extent size (initial) . []
7 ~ Sort by fields of index (sortind) ……… []
8 – Table reorganization mode (mode) ………. [online]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2011-04-06 07.40.39
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-04-06 07.40.39
BR1108I Checking tables for reorganization…

BR0280I BRSPACE time stamp: 2011-04-06 07.40.40
BR1112I Number of tables selected/skipped for reorganization: 1/0

BR0280I BRSPACE time stamp: 2011-04-06 07.40.41
BR0370I Directory /oracle/SAM/sapreorg/sefphmbn created

BR0280I BRSPACE time stamp: 2011-04-06 07.40.41
BR1101I Starting online table reorganization…
BR0280I BRSPACE time stamp: 2011-04-06 07.40.42
BR1124I Starting online reorganization of table SAPSR3.BALDAT …
BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR1105I Table SAPSR3.BALDAT reorganized successfully

BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR1141I 1 of 1 table processed – 14230500 of 14230500 rows done
BR0204I Percentage done: 100.00%, estimated end time: 7:54
BR0001I **************************************************

BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR1102I Number of tables reorganized successfully: 1

BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
s
BR0280I BRSPACE time stamp: 2011-04-06 07.57.27
BR0257I Your reply: ‘s’
BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]:
y
BR0280I BRSPACE time stamp: 2011-04-06 07.57.36
BR0257I Your reply: ‘y’
BR0260E BRSPACE cancelled by user

BR1008I End of BRSPACE processing: sefphmbn.tbr 2011-04-06 07.57.36
BR0280I BRSPACE time stamp: 2011-04-06 07.57.37
BR1007I BRSPACE terminated with errors

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

SAP BRTOOLS for tablespace extend

Posted in SAP DBA, tagged BRTOOL, Oracle Database, SAP, Tablespace on April 7, 2011| 2 Comments »

** Tested with UNIX / Solarise operating system.

In our organisation SAP is front end application. So as DBA we do have exposure on BRTOOL,BRSPACE etc.So just sharing the BR tool handling issue from todays onwards.

So here is one tested example for adding datafile in tablespace using the BRTOOL. Hopw you guys enjoy with this one…..!!!!

bash-3.00$ brtools
BR0651I BRTOOLS 7.00 (11)

BR0280I BRTOOLS time stamp: 2011-04-06 07.10.35
BR0656I Choice menu 1 – please make a selection
——————————————————————————-
BR*Tools main menu

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2011-04-06 07.10.39
BR0663I Your choice: ‘2’

BR0280I BRTOOLS time stamp: 2011-04-06 07.10.39
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:
1
BR0280I BRTOOLS time stamp: 2011-04-06 07.10.57
BR0663I Your choice: ‘1’

BR0280I BRTOOLS time stamp: 2011-04-06 07.10.57
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.11.09
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.11.09
BR0681I Enter string value for “tablespace” []:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.11.32
BR0683I New value for “tablespace”: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.11.32
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

1 – BRSPACE profile (profile) …… [initSAM.sap]
2 – Database user/password (user) .. [/]
3 ~ Tablespace name (tablespace) … [3]
4 – Confirmation mode (confirm) …. [yes]
5 – Scrolling line count (scroll) .. [20]
6 – Message language (language) …. [E]
7 – BRSPACE command line (command) . [-p initSAM.sap -s 20 -l E -f tsextend -t 3]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.12.55
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.12.55
BR0681I Enter string value for “tablespace” [3]:

BR0280I BRTOOLS time stamp: 2011-04-06 07.13.17
BR0686I The value of “tablespace” was not changed

BR0280I BRTOOLS time stamp: 2011-04-06 07.13.17
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.13.59
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.13.59
BR0681I Enter string value for “tablespace” [3]:
PSAPSR3
BR0280I BRTOOLS time stamp: 2011-04-06 07.14.51
BR0683I New value for “tablespace”: ‘PSAPSR3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.14.51
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

1 – BRSPACE profile (profile) …… [initSAM.sap]
2 – Database user/password (user) .. [/]
3 ~ Tablespace name (tablespace) … [PSAPSR3]
4 – Confirmation mode (confirm) …. [yes]
5 – Scrolling line count (scroll) .. [20]
6 – Message language (language) …. [E]
7 – BRSPACE command line (command) . [-p initSAM.sap -s 20 -l E -f tsextend -t PSAPSR3]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2011-04-06 07.15.13
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0291I BRSPACE will be started with options ‘-p initSAM.sap -s 20 -l E -f tsextend -t PSAPSR3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.15.13
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRTOOLS time stamp: 2011-04-06 07.15.25
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

###############################################################################

BR1001I BRSPACE 7.00 (11)
BR1002I Start of BRSPACE processing: sefphjwn.tse 2011-04-06 07.15.25

BR0280I BRSPACE time stamp: 2011-04-06 07.15.26
BR1009I Name of database instance: SAM
BR1010I BRSPACE action ID: sefphjwn
BR1011I BRSPACE function ID: tse
BR1012I BRSPACE function: tsextend

BR0280I BRSPACE time stamp: 2011-04-06 07.15.26
BR0657I Input menu 303 – please check/enter input values
——————————————————————————-
Options for extension of tablespace PSAPSR3 (1. file)

1 * Last added file name (lastfile) ……. [/oracle/SAM/sapdata2/sr3_30/sr3.data30]
2 * Last added file size in MB (lastsize) . [10240]
3 – New file to be added (file) ……….. [/oracle/SAM/sapdata2/sr3_31/sr3.data31]
4 ~ Raw disk / link target (rawlink) …… []
5 – Size of the new file in MB (size) ….. [10240]
6 – File autoextend mode (autoextend) ….. [no]
7 # Maximum file size in MB (maxsize) ….. []
8 # File increment size in MB (incrsize) .. []
9 – SQL command (command) …………….. [alter tablespace PSAPSR3 add datafile ‘/oracle/SAM/sapdata2/sr3_31/sr3.data31’ size 10240M autoextend off]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRSPACE time stamp: 2011-04-06 07.16.07
BR0663I Your choice: ‘3’

BR0280I BRSPACE time stamp: 2011-04-06 07.16.07
BR0681I Enter string value for “file” (<file>|<sapdata>|<N>) [/oracle/SAM/sapdata2/sr3_31/sr3.data31]:
/oracle/SAM/sapdata2/sr3_31/sr3.data31
BR0280I BRSPACE time stamp: 2011-04-06 07.17.24
BR0683I New value for “file”: ‘/oracle/SAM/sapdata2/sr3_31/sr3.data31’

BR0280I BRSPACE time stamp: 2011-04-06 07.17.24
BR0657I Input menu 303 – please check/enter input values
——————————————————————————-
Options for extension of tablespace PSAPSR3 (1. file)

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2011-04-06 07.17.49
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-04-06 07.17.49
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: 2011-04-06 07.18.17
BR0257I Your reply: ‘c’
BR0678I The action will be skipped…
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-04-06 07.18.17
BR0370I Directory /oracle/SAM/sapreorg/sefphjwn created

BR0280I BRSPACE time stamp: 2011-04-06 07.18.18
BR0319I Control file copy created: /oracle/SAM/sapreorg/sefphjwn/cntrlSAM.old 14237696

BR0280I BRSPACE time stamp: 2011-04-06 07.18.18
BR0370I Directory /oracle/SAM/sapdata2/sr3_31 created

BR0280I BRSPACE time stamp: 2011-04-06 07.18.18
BR1088I Extending tablespace PSAPSR3…

BR0280I BRSPACE time stamp: 2011-04-06 07.19.33
BR1016I SQL statement ‘alter tablespace PSAPSR3 add datafile ‘/oracle/SAM/sapdata2/sr3_31/sr3.data31′ size 10240M autoextend off’ executed successfully
BR1051I Tablespace PSAPSR3 extended successfully with file: /oracle/SAM/sapdata2/sr3_31/sr3.data31 10240M

BR0280I BRSPACE time stamp: 2011-04-06 07.19.33
BR0340I Switching to next online redo log file for database instance SAM…
BR0321I Switch to next online redo log file for database instance SAM successful

BR0280I BRSPACE time stamp: 2011-04-06 07.19.35
BR0319I Control file copy created: /oracle/SAM/sapreorg/sefphjwn/cntrlSAM.new 14237696

BR0280I BRSPACE time stamp: 2011-04-06 07.19.35
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
s
BR0280I BRSPACE time stamp: 2011-04-06 07.21.00
BR0257I Your reply: ‘s’
BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]:
yes
BR0280I BRSPACE time stamp: 2011-04-06 07.21.03
BR0257I Your reply: ‘yes’
BR0260E BRSPACE cancelled by user

BR1008I End of BRSPACE processing: sefphjwn.tse 2011-04-06 07.21.03
BR0280I BRSPACE time stamp: 2011-04-06 07.21.03
BR1007I BRSPACE terminated with errors

###############################################################################

BR0292I Execution of BRSPACE finished with return code 4

BR0668I Warnings or errors occurred – you can continue to ignore them or go back to repeat the last action
BR0280I BRTOOLS time stamp: 2011-04-06 07.21.03
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
s
BR0280I BRTOOLS time stamp: 2011-04-06 07.21.08
BR0257I Your reply: ‘s’
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
yes
BR0280I BRTOOLS time stamp: 2011-04-06 07.21.11
BR0257I Your reply: ‘yes’
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2011-04-06 07.21.11
BR0654I BRTOOLS terminated with errors
bash-3.00$

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Table Compression in Oracle

Posted in Administration, tagged Oracle Database, space managment, table compress, Tablespace on March 7, 2011| Leave a Comment »

Recently i had to move a schema of size 70GB to another database[Oracle 10.2.0.1]. That was a readonly schema and never had any DML on any of the tables. In the new database i did compress the tables and the size was brought down from 70Gb to 22Gb.

1]Divided the tables based on the size.
2]Created the tablesapces.
3]Pre-created the tables on respective tablespaces.
4]Imported the tables.
5]Compressed and moved big tables from APP_BIG_TABLES_TEMP to APP_BIG_TABLES.
6]Rebuild the primary key indexes of the moved tables.
7]Droped APP_BIG_TABLES_TEMP.
8]Created the indexes.

The tables where create with PCTFREE 0 to take maximum advantage of the space in each extents as i was sure that never an UPDATE would happen on any of these tables.

Compress statement with “move”
======================
ALTER TABLE <table_name> MOVE TABLESPACE <to new tablespace> COMPRESS NOLOGGING PARALLEL (degree N);
eg: ALTER TABLE SALES_INFORMATION MOVE TABLESPACE APP_BIG_TABLES COMPRESS NOLOGGING PARALLEL (degree 8);

COMPRESS alone will only compress the tables, to recalim the space use MOVE in the statement.
My Server had 32 CPUs, but i just used 8. I should leave the rest for other applications.

To move tables with LOB segments:
ALTER TABLE <TABLE_NAME> MOVE TABLESPACE <new_tablespace> LOB(<lob_column_1) STORE AS (TABLESPACE <new_tablespace) LOB(<lob_column_2) STORE AS (TABLESPACE <new_tablespace);

Why did not i compress and move the table in the same tablespace?
COMPRESS and MOVE of tables in the same tablespace will not bring down the datafiles high water mark and i will not be able to resize the datafiles to a smaller size. So i found it easy to drop the very big APP_BIG_TABLES_TEMP once the tables were compressed and moved to APP_BIG_TABLES.

BEFORE COMPRESS
================
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 7.73681641
TABLE 55 62.6555786
———-
sum 70.392395
AFTER COMPRESS
===============
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 2.375
TABLE 55 19.8398438
———-
sum 22.2148438

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

When you would make Index and when not

Posted in Performance Tunning, tagged Column, Data Definition Language, Index (database), Null (SQL), sql, Tables, Tablespace, Unique key on February 14, 2011| Leave a Comment »

* Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
* To improve performance on joins of multiple tables, index columns used for joins.

* Small tables do not require indexes.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

* Values are relatively unique in the column.
* There is a wide range of values (good for regular indexes).
* There is a small range of values (good for bitmap indexes).
* The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:

WHERE COL_X > -9.99 * power(10,125)

Using the preceding phrase is preferable to:

WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

Columns with the following characteristics are less suitable for indexing:

* There are many nulls in the column and you do not search on the not null values.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Other Considerations:

1. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

2. There is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

3. Drop Index that are no longer required.

4. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced.

Expert are always welcome for their valuable comment or suggestion for the above post.

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

Improve INSERT Performance in oracle

Posted in Performance Tunning, tagged Database, Insert (SQL), Oracle, Oracle Database, Performa, sql, Tables, Tablespace on February 14, 2011| 2 Comments »

One day we had an issue to increase the speed of insert statements. This activity was carried out at our one of the bank client. We had to insert about 500K record to database as quickly as possible. We were inserting at a rate of 10 records/sec. Well, I was thinking the following approaches to gain speed-

1. Use a large blocksize – By defining large (i.e. 16k or 32k) blocksizes for the target tables, we can reduce I/O because more rows fit onto a block before a “block full” condition (as set by PCTFREE) unlinks the block from the freelist.

>DROP TABLESPACE sam_tbs INCLUDING CONTENTS AND DATAFILES;
>CREATE TABLESPACE sam_tbs DATAFILE ‘/mnt/extra/test1.dbf’ SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED blocksize 16K;

2. Increase the size of UNDO tablespace –

>CREATE UNDO TABLESPACE UNDOTBS DATAFILE ‘/mnt/extra/test_undo1.dbf’ SIZE 200M BLOCKSIZE 16K;
>alter system set undo_tablespace=’UNDOTBS‘ scope=both;
>DROP TABLESPACE UNDOTBSP1 INCLUDING CONTENTS AND DATAFILES;

3. APPEND into tables – By using the APPEND hint, it ensures that Oracle always grabs fresh data blocks by raising the high-water-mark for the table.

>insert /*+ append */ into customer values (’hello’,’;there’);

4. Table into NOLOGGING mode – Putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging.

>SELECT logging FROM user_tables WHERE table_name = ‘LOGIN’;
>ALTER TABLE login NOLOGGING;

Again, to enable logging –
>ALTER TABLE login LOGGING;

5. Disable/drop indexes – It’s far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.

6. Parallelize the load – We can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL “APPEND” option.

Read Full Post »

Troubleshoot unusable Index in Oracle

Posted in Index, Performance Tunning, tagged Database, Oracle Database, sql, Tablespace, Unique key, unusable on February 13, 2011| 1 Comment »

If a procedural object, such as a stored PL/SQL function or a view, becomes invalid, the DBA does not necessarily have to do anything: the first time it is accessed, Oracle will attempt to recompile it, and this may well succeed.

But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used.

This is because an index consists of the index key values, sorted into order, each with the relevant
rowid. The rowid is the physical pointer to the location of the row to which the index key refers.

If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE…MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index.

Identifying Unusable Indexes
———————————
In earlier releases of the Oracle database, when executing SQL statements, if the session attempted to use an unusable index it would immediately return an error, and the statement would fail. Release 10g of the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index.

Thus, statements will always succeed—but perhaps at the cost of greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML.

To detect indexes which have become unusable, query the DBA_INDEXES view:
SQL> select owner, index_name from dba_indexes where status=’UNUSABLE’;

Repairing Unusable Indexes
—————————————-

To repair the index, it must be re-created with the ALTER INDEX…REBUILD command.

This will make a pass through the table, generating a new index with correct rowid pointers
for each index key. When the new index is completed, the original unusable index is dropped.

The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a table space with the TABLESPACE keyword, it can be moved to a different one.

Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword.

1)Create Table and insert row in it:
—————————————-
SQL> create table test ( a number primary key);
Table created.

SQL> insert into test values(1);
1 row created.

SQL> commit;
Commit complete.

2)Check the Index Status
————————–
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 VALID

3)Move the Table and Check Status:
————————————
SQL> alter table test move;
Table altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 UNUSABLE

4)Rebuild The Index:
———————–
SQL> alter index SYS_C0044514 rebuild online;
Index altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044514 VALID

Expert are always welcome for their valuable comment or suggestion for the above post.

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

The possible causes for Excessive Undo generation

Posted in Performance Tunning, Undo, tagged Database, Tablespace, Undo on February 13, 2011| Leave a Comment »

1)Transactions with huge undo.
——————————-
It is obvious to see high undo usage when there are huge transactions. If that is going to be the case this growth should be expected behavior.

2)The Higher value setting of UNDO_RETENTION.
————————————————
The higher value of UNDO_RETENTION will increase much undo. As it can’t be marked undo extents as EXPIRED till the duration of the UNDO_RETENTION.

3)Autoextensible mode of Undo data files.
————————————————–
Disabling the auto extensible mode of the datafiles of active undo tablespace will reuse the UNEXPIRED extents when undo tablespace suffer in space pressure.If they are set to auto extensible, it will not reuse the space for the new transaction. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.

4)Undo tablespace is using Autoallocate option:
———————————————-
UNDO is using auto allocate option of LMT. As the number of the extents goes up, the extent size will be increased too.When the number is extents reaches hundred the extent size will be bigger. If it reaches thousands it will be even more bigger.If you know the size of most of your transactions you can use UNIFORM rather than AUTO.

Expert are always welcome for their valuable comment or suggestion for the above post.

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »