Step by step oracle database upgrade from 10.2.0.2 to 10.2.0.4 (also for SAP patch as well )

Note: One of our visitors  Lokesh Suryawanshi asked this question by posting a comment. Thanks Lokesh for your contribution. As promised i did patch upgrade on yesterday and here is the details of it. Keep visiting/commenting!

Required Software and Installable are available in “ */*/Patch” directory

$HOME = /oracle/SAM

$ORACLE_HOME = /oracle/SAM/102_64

1. 10.2.0.4 Patchset (patchset file is already extracted)

2. OPatch version 10.2.0.5.0

 3. MOPatch version2.1.7

Note:- All Required Software are kept in $HOME/Patch Directory and extract files in following order

1) Mopatch-2-1-7.zip in $ORACLE_HOME directory Note:- Unzip After 10.2.0.4 patchset installation.

2) OPatch10205_Generic_v0.zip file in $ORACLE_HOME directory Note:- Unzip After 10.2.0.4 patchset installation.

3) Patchset_10204.zip file in current directory i.e. in Patch Path

Step 1.-> Check upgradation prerequisites:

1.1 Stop SAP. (No need while applying on BCV)

 1.2:- check oratab and orainventory location at “/var/opt/oracle” and entry of “SAM:/oracle/SAM/102_64: N” in oratab file, If not make an entry in oratab file.

1.3 :- To check invalid objects and Tablepsace prerequisites Sql> @?/rdbms/admin/utlu102i.sql (? Sign indicates ORACLE_HOME path)

Sql> @?/rdbms/admin/utlu102i.sql (? Sign indicates ORACLE_HOME path)

Important Note:- After running prerequisites script you seen some public objects are invalid in SYS schema and other schemas(SAPSR3). Here we are only concern about SYS objects. Which  is known and need to be compiled. Next step to run utlrp script to get compiled invalid objects.

1.2:- Run utlrp.sql to validate invalid objects.

Purpose:- To ensure there is no object invalid found in SYS schema otherwise probabilities patch up gradation failed.  Run below scripts

 Sql>@?/rdbms/admin/utlrp.sql

Result:- In most cases result should report 0 in SYS schemas.( invalid objects). IF NOT ABORT OR STOP INSTALLATION PROCESS

Again Run Prerequistes script

Sql>@?/rdbms/admin/utlu102i.sql (? Sign indicates ORACLE_HOME path)

Note:- No Objects are invalid in SYS and SAPSR3 Schemas and DB is Ready for Upgradation. Next step to Shutdown Database and related Components.

Step 2:- Shutdown database listener,OEM and all component related to Oracle

 2.1 Gracefully stop Database instance and Listener process (Don’t use abort or kill Oracle process)

2.2 OEM is not valid when you test in BCV server as this is not configured for same but in Production you have to gracefully stop.

 Sql>shutdown immediate;

Log off from sql prompt

Orap22>lsnrctl stop

 Check the oracle processes by executing command ps –ef |grep -i ora_

Check the listener process by executing command ps –ef |grep -i LIS

Step: 3 Run OPatch

 3.1 Run OPatch lsinventory to ensure ora inventory location

 Orap22>/oracle/SAM/102_64/OPatch/opatch lsinventory

Step:-4  start 10.2.0.4 Patch Binary upgradation process.

Go to Patch directory in Disk1 directory

1.1   Start XBrowser

1.2   Set Display Env type command “export DISPLAY=your Desktop/Laptop IP:0.0”

1.3   ./oracle/SAM/Patch/Disk1/runInstaller

4.4 Verified ORACLE_HOME

Purpose:- To Validate correct ORACLE_HOME where 10.2.0.4 binary is going to update.

Workaround:- If not seen correct ORACLE_HOME abort and verify oraInventory location

4.5 Installaler summary

4.6 sh /oracle/SAM/102_64/root.sh from another session as root user

a)      Go to ORACLE_HOME run command from $ Prompt sh root.sh

Step-5:-  Run MOPatch for CPU patches(SBP)

5.1) Unzip Mopatch-2-1-7.zip in $ORACLE_HOME directory from Patch location

Orap22>unzip /oracle/SAM/Patch/Mopatch-2-1-7.zip /oracle/SAM/102_64/.

5.2) Unzip OPatch 10205_Generic_v0.zip file in $ORACLE_HOME directory Patch location

Orap22>unzip /oracle/SAM/Patch/OPatch10205_Generic_v0.zip  /oracle/SAM/102_64/.

Note:-  Run OPatch version to confirm OPatch version 10.2.0.5.0 which is required to run SAP Supplied Bundle Patch

CAUTION:- Before running mopatch ensure OPatch version:10.2.0.5.0

Otherwise MOpatch run but some patch failed which required OUI version 10.2.0.5.0

Verified OPatch lsinventory

Purpose:-  To Ensure Inventory of Patches which we applied recently. Next Step to Upgrade Database.

Orap22>Sh /oracle/SAM/102_64/MOPatch/mopatch.sh –v –s SAP_102048_201105_SOL64.zip

Step6:- Upgrade Database

6.1 startup database in upgrade mode

Sql>startup upgrade

 check oracle version 10.2.0.4 after loggin into sql prompt.

6.2 Run catupgrd.sql

Execute SQL from sql command prompt

Sql>spool catupgrd.log

Sql> @?/rdbms/admin/catupgrd.sql

Step 7:- Shutdown Database and restart in normal mode.

Sql>shutdown immediate;

Sql>startup

Step 8:- Run utlrp to validate any invalid object while upgrading

Sql> @?/rdbms/admin/utlrp.sql

Step:-9 Shutdown and restart database to take new parameter in effects.

Step:-10 Start listener and OEM Test and release to user.

Coming post will be on Oracle most popular wait events and there solutions, till that enjoy the Oracle jou5rney….. 🙂

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles….!!!!

Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!

Read Full Post »

SAP- Brtools – Drop Tablespace step by step

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: Creating a new tablespace

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 here I am coming with new post to add tablespace in DB using the BRtools.

bash-3.00$ brtools
BR0651I BRTOOLS 7.20 (1)

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

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

BR0280I BRTOOLS time stamp: 2011-08-01 00.28.33
BR0657I Input menu 82 – please enter/check input values
——————————————————————————-
BRSPACE options for create 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 tscreate]

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.28.39
BR0663I Your choice: ‘3’
BR0681I Enter string value for “tablespace” []:
PSAPSR3SAM
BR0280I BRTOOLS time stamp: 2011-08-01 00.29.06
BR0683I New value for “tablespace”: ‘PSAPSR3SAM’

BR0280I BRTOOLS time stamp: 2011-08-01 00.29.06
BR0657I Input menu 82 – please enter/check input values
——————————————————————————-
BRSPACE options for create 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 tscreate -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.29.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 tscreate -t PSAPSR3SAM’

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

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

BR1001I BRSPACE 7.20 (1)
BR1002I Start of BRSPACE processing: segljdri.tsc 2011-08-01 00.29.18
BR0484I BRSPACE log file: /oracle/SAM/sapreorg/segljdri.tsc

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

BR1010I BRSPACE action ID: segljdri
BR1011I BRSPACE function ID: tsc
BR1012I BRSPACE function: tscreate

BR0280I BRSPACE time stamp: 2011-08-01 00.29.20
BR0657I Input menu 305 – please enter/check input values
——————————————————————————-
Main options for creation of tablespace in database SAM

 1 – Tablespace name (tablespace) ……… [PSAPSR3SAM]
 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) ……… []
 9 # Tablespace compression (compress) …. [no]
10 # Tablespace encryption (encrypt) …… [no]
11 # Encryption algorithm (algorithm) ….. []

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
5
BR0280I BRSPACE time stamp: 2011-08-01 00.29.26
BR0663I Your choice: ‘5’
BR0681I Enter string value for “class” (all|<tab_class>|<tab_class_list>|<old_tsp>|<old_tsp_list>|none) []:
PSAPSR3SAM01
BR0280I BRSPACE time stamp: 2011-08-01 00.29.47
BR0683I New value for “class”: ‘PSAPSR3SAM01’

BR0280I BRSPACE time stamp: 2011-08-01 00.29.47
BR0657I Input menu 305 – please enter/check input values
——————————————————————————-
Main options for creation of tablespace in database SAM

 1 – Tablespace name (tablespace) ……… [PSAPSR3SAM]
 2 – Tablespace contents (contents) ……. [data]
 3 – Segment space management (space) ….. [auto]
 4 – Database owner of tablespace (owner) . [SAPSR3]
 5 ~ Table data class / tabart (class) …. [PSAPSR3SAM01]
 6 – Data type in tablespace (data) ……. [both]
 7 # Joined index/table tablespace (join) . []
 8 ~ Uniform size in MB (uniform) ……… []
 9 # Tablespace compression (compress) …. [no]
10 # Tablespace encryption (encrypt) …… [no]
11 # Encryption algorithm (algorithm) ….. []

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.30.23
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-08-01 00.30.23
BR0657I Input menu 306 – please enter/check input values
——————————————————————————-
Space options for creation of tablespace PSAPSR3SAM (1. file)

 1 – Tablespace file name (file) ………. [/oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1]
 2 ~ Raw disk / link target (rawlink) ….. []
 3 – File size in MB (size) …………… [30]
 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 PSAPSR3SAM extent management local autoallocate segment space management auto datafile ‘/oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1’ size 30M 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: 2011-08-01 00.30.49
BR0663I Your choice: ‘3’
BR0682I Enter integer value for “size” (1-32767) [30]:
500
BR0280I BRSPACE time stamp: 2011-08-01 00.31.13
BR0683I New value for “size”: ‘500’

BR0280I BRSPACE time stamp: 2011-08-01 00.31.13
BR0657I Input menu 306 – please enter/check input values
——————————————————————————-
Space options for creation of tablespace PSAPSR3SAM (1. file)

 1 – Tablespace file name (file) ………. [/oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1]
 2 ~ Raw disk / link target (rawlink) ….. []
 3 – File size in MB (size) …………… [500]
 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 PSAPSR3SAM extent management local autoallocate segment space management auto datafile ‘/oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1’ size 500M 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: 2011-08-01 00.31.21
BR0663I Your choice: ‘5’
BR0682I Enter integer value for “maxsize” (0-32767) [10000]:
1000
BR0280I BRSPACE time stamp: 2011-08-01 00.31.28
BR0683I New value for “maxsize”: ‘1000’

BR0280I BRSPACE time stamp: 2011-08-01 00.31.28
BR0657I Input menu 306 – please enter/check input values
——————————————————————————-
Space options for creation of tablespace PSAPSR3SAM (1. file)

 1 – Tablespace file name (file) ………. [/oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1]
 2 ~ Raw disk / link target (rawlink) ….. []
 3 – File size in MB (size) …………… [500]
 4 – File autoextend mode (autoextend) …. [yes]
 5 – Maximum file size in MB (maxsize) …. [1000]
 6 – File increment size in MB (incrsize) . [20]
 7 – SQL command (command) ……………. [create tablespace PSAPSR3SAM extent management local autoallocate segment space management auto datafile ‘/oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1’ size 500M autoextend on next 20M maxsize 1000M]

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.31.55
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR1049W Not enough free space in /oracle/SAM/sapdata4 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 76824.785 MB
BR1049W Not enough free space in /oracle/SAM/sapdata4 for total maximum size of all database files of tablespace PSAPSR3702 located on this disk volume, missing at least 47264.785 MB

BR0280I BRSPACE time stamp: 2011-08-01 00.31.55
BR0668I Warnings or errors occurred – you can continue to ignore them or go back to repeat the last action
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRSPACE time stamp: 2011-08-01 00.32.05
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR1091I Next data file can be specified now

BR0280I BRSPACE time stamp: 2011-08-01 00.32.05
BR0675I This is a optional action – do you want to execute it now?
BR0676I Enter ‘y[es]’ to execute the action, ‘n[o]/c[ont]’ to skip it, ‘s[top]’ to abort:
c
BR0280I BRSPACE time stamp: 2011-08-01 00.32.19
BR0257I Your reply: ‘c’
BR0678I The action will be skipped…
BR0259I Program execution will be continued…

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

BR0280I BRSPACE time stamp: 2011-08-01 00.32.20
BR0319I Control file copy created: /oracle/SAM/sapreorg/segljdri/cntrlSAM.old 13844480

BR0280I BRSPACE time stamp: 2011-08-01 00.32.20
BR0370I Directory /oracle/SAM/sapdata4/sr3sam_1 created

BR0280I BRSPACE time stamp: 2011-08-01 00.32.20
BR1089I Creating tablespace PSAPSR3SAM…

BR0280I BRSPACE time stamp: 2011-08-01 00.32.45
BR1016I SQL statement ‘create tablespace PSAPSR3SAM extent management local autoallocate segment space management auto datafile ‘/oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1′ size 500M autoextend on next 20M maxsize 1000M’ executed successfully
BR1060I Tablespace PSAPSR3SAM created successfully with file: /oracle/SAM/sapdata4/sr3sam_1/sr3sam.data1 500M

BR0280I BRSPACE time stamp: 2011-08-01 00.32.45
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.32.48
BR0319I Control file copy created: /oracle/SAM/sapreorg/segljdri/cntrlSAM.new 13844480

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

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

BR1008I End of BRSPACE processing: segljdri.tsc 2011-08-01 00.33.51
BR0280I BRSPACE time stamp: 2011-08-01 00.33.51
BR1006I BRSPACE completed successfully with warnings

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

BR0292I Execution of BRSPACE finished with return code 1

BR0280I BRTOOLS time stamp: 2011-08-01 00.33.51
BR0668I Warnings or errors occurred – you can continue to ignore them or go back to repeat the last action
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRTOOLS time stamp: 2011-08-01 00.33.58
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0280I BRTOOLS time stamp: 2011-08-01 00.33.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:
BR0256I Enter ‘c[ont]’ to continue, ‘s[top]’ to cancel BRTOOLS:
s
BR0280I BRTOOLS time stamp: 2011-08-01 00.34.51
BR0257I Your reply: ‘s’
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
y
BR0280I BRTOOLS time stamp: 2011-08-01 00.34.54
BR0257I Your reply: ‘y’
BR0260E BRTOOLS cancelled by user

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

Hope you guy’s like this post. ….!!!!

 Experts are always welcome for their valuable comment or suggestion for the above post….!!!!

Read Full Post »

SAP BRTOOLS for table reorganization

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

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

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

 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 tsextend]

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

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

 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:
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 »