Change oracle parameter for process and sessions

Change oracle parameter for process and sessions

Above parameter can’t be changed directly through ALTER command.

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where NAME in ('sessions','processes');

NAME                 VALUE                ISSES ISSYS_MOD

-------------------- -------------------- ----- ---------

processes            190                  FALSE FALSE

sessions             384                  FALSE FALSE

SQL>

SQL> ALTER SYSTEM SET processes=600 SCOPE=BOTH;

ALTER SYSTEM SET processes=600 SCOPE=BOTH

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sessions=1200 scope=both;

alter system set sessions=1200 scope=both

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

Follow below step to change.

1.    Create Pfile from SPFILE.

SQL> create pfile from spfile;

File created.

SQL>

Will create file init<SAPSID>.ora (initQLS.ora) inside /oracle/QLS/112_64/dbs directory.

2.    Open Pfile with ora<sapsid> user

vi /oracle/QLS/112_64/dbs/initQLS.ora

Change above parameter as per your requirement.

3.    In SQL plus shutdown database.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

4.    Start database using Pfile.

            SQL> startup pfile='/oracle/QLS/112_64/dbs/initQLS.ora';

ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated

ORACLE instance started.

Total System Global Area 4676788224 bytes

Fixed Size                  2188952 bytes

Variable Size            2348812648 bytes

Database Buffers         2315255808 bytes

Redo Buffers               10530816 bytes

Database mounted.

Database opened.

5.    Create SPfile from Pfile.

SQL> create spfile from pfile;

File created.

SQL>

6.    Shutdown database.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

7.    Start database with SPFILE.

SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 4676788224 bytes

Fixed Size                  2188952 bytes

Variable Size            2348812648 bytes

Database Buffers         2315255808 bytes

Redo Buffers               10530816 bytes

Database mounted.

Database opened.

SQL>

How to import transport request of other system into SAP system

Having multiple transport requested exported from other system in compress file.

1. Login with <SAPSID>adm user create folder /tmp/transportrequest
2. Copy all extracted file into /tmp/transportrequest folder
3. move data files into data directory of transport 
    cd /tmp/transportrequest
    mv R* /usr/sap/trans/data
4. move data files into data directory of transport
    cd /tmp/transportrequest
    mv K* /usr/sap/trans/cofiles
5. Login into SAP system
6. call transaction stms_import
7. follow below steps for all request
    choose extras -> Other Requests -> Add
    Enter transport request number i.e. D11K901027
    choose right arrow
8. To import request choose request and choose import request and your client on which would like to import.

Drop datafile of temp tablespace

ALTER TABLESPACE PSAPTEMP DROP TEMPFILE '/oracle/PIP/sapdata3/temp_5/temp.data5';
ALTER TABLESPACE PSAPTEMP DROP TEMPFILE '/oracle/PIP/sapdata6/temp_6/temp.data6';
ALTER TABLESPACE PSAPTEMP DROP TEMPFILE '/oracle/PIP/sapdata6/temp_7/temp.data7';

cd /oracle/PIP/sapdata3/
rmdir temp_5


cd /oracle/PIP/sapdata6/
rmdir temp_6

cd /oracle/PIP/sapdata6/
rmdir temp_7

Switching oracle database archive to non archive mode and vice versa.

switching off archive mode.

    shutdown immediate;
    startup mount;
    alter database noarchivelog;
    alter database open;

switching on archive mode.

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;

Mount windows share into HP-UX

Create mount point
mkdir winshare

Mount H drive of windows server 10.32.0.164 where nikunj is local user of that server and having rights on shared drive H

# cifsmount //10.32.0.164/h /winshare -U nikunj -P testing
The CIFS Client is not running.
#

Start CIFS Client

# cifsclient
CIFS Client started; process id: 14102

Mount H drive of windows server 10.32.0.164
# cifsmount //10.32.0.164/h /winshare -U nikunj -P testing
LOC: Netbios session refused
LOC: Netbios session refused


To resolve LOC: Netbios session refused error do the entry into /etc/hosts
10.32.0.164     BACKUPSRV.domain.com


Mount H drive of windows server 10.32.0.164
# cifsmount //BACKUPSRV.domain.com/h /winshare -U nikunj -P testing


Check file system mounted
# bdf
Filesystem          kbytes    used   avail %used Mounted on
backupsrv.domain.com:/h
                   2711840 2711840       0  100% /winshare
#


Unmount mounted windows shared
cifsumount /winshare


Stop CIFS Client
# cifsclient stop
No CIFS filesystems to unmount.
The CIFS Client has been shut down.
#

R3trans -d failed when oracle archiver get stuck

archiver location is full.
# bdf
Filesystem             kbytes    used            avail %used Mounted on
/dev/vg00/lvol12   20971520 20947873   22276  100% /oracle/PIP/oraarch

test R3trans -d which failed with return code 0012.
tstmig:pipadm 42> R3trans -d
This is R3trans version 6.14 (release 700 - 19.01.11 - 11:44:00).
unicode enabled version
2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_o                                                                                        ra_tnsname = 'PIP'"
R3trans finished (0012).
 
archiver location free
tstmig:pipadm 43> bdf
Filesystem          kbytes    used   avail %used Mounted on
/dev/vg00/lvol12   20971520 1151649 18581263    6% /oracle/PIP/oraarch




test R3trans -d which success with return code 0000.
tstmig:pipadm 44> R3trans -d
This is R3trans version 6.14 (release 700 - 19.01.11 - 11:44:00).
unicode enabled version
R3trans finished (0000).
tstmig:pipadm 45>

How to make table editable in sap &SAP_EDIT

How to make table editable in sap

Earlier you can use &SAP_EDIT functionality in se16n so users can update table.

SAP has removed this function through note 1420281 which was delivered in Release 600 with Support Package 17 and Support Package 18.

Now other alternet as below.

1. call transaction se16n
2. Type '/h' to enter into the debug mode.
3. type two below variable name
    gd-sapedit
    gd-edit
4. change there value to X
5. execute

congratulation table open in editing mode.

Beginning Performance Tuning By Arup Nanda - Artical from ORACLE MAGAZINE

Extract of Beginning Performance Tuning By Arup Nanda - Artical from ORACLE MAGAZINE
http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42dba-1566567.html

Query for displaying sessions, session state, and events

select sid, state, event from v$session where username = 'ARUP';

SID   STATE              EVENT
————— —————————————————  ————————————————————————————
2832  WAITED KNOWN TIME  SQL*Net message from client
3346  WAITING            enq: TX - row lock contention


Query for displaying sessions, session state, and wait details


col "Description" format a50
select sid,decode(state, 'WAITING','Waiting','Working') state,
        decode(state,'WAITING','So far '||seconds_in_wait,'Last waited '||wait_time/100)||' secs for '||event "Description"
from v$session where username = 'ARUP';

Output:

SID   STATE       Description
————— ——————————  ———————————————————————————————————————————————————————
2832  Working     Last waited 2029 secs for SQL*Net message from client
3346  Waiting     So far 743 secs for enq: TX - row lock contention
4208  Waiting     So far 5498 secs for SQL*Net message from client



You can find out the blocking session and instance by issuing the following SQL statement:

select blocking_session B_SID,blocking_instance B_Inst from v$session
where sid = 3346;

B_SID   B_INST
——————  ———————
 2832      1


Getting row lock information

select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#
from v$session where sid = 3346;

ROW_WAIT_OBJ#  ROW_WAIT_FILE#  ROW_WAIT_BLOCK#  ROW_WAIT_ROW#
—————————————  ——————————————  ———————————————— ——————————————
241876         1024            2307623          0



To get the object information:

select owner, object_type, object_name, data_object_id from dba_objects
where object_id = 241876;

OWNER  OBJECT_TYPE  OBJECT_NAME   DATA_OBJECT_ID
—————  ———————————— ————————————  ——————————————
ARUP   TABLE        T1                    241877



Finding the row information

REM Filename: rowinfo.sql
REM This shows the row from the table when the
REM components of ROWID are passed. Pass the
REM following in this exact order
REM  1. owner
REM  2. table name
REM  3. data_object_id
REM  4. relative file ID
REM  5. block ID
REM  6. row Number
REM
select *
from &1..&2
where rowid =
        dbms_rowid.rowid_create (
                rowid_type      =>  1,
                object_number   => &3,
                relative_fno    => &4,
                block_number    => &5,
                row_number      => &6
        )
/

SQL> @rowinfo ARUP T1 241877 1024 2307623 0

COL1  C
————— —
  1   x


Sessions from a specific user

select SID, osuser, machine, terminal, service_name,
       logon_time, last_call_et
from v$session
where username = 'ARUP';

SID   OSUSER  MACHINE   TERMINAL  SERVICE_NAME  LOGON_TIME LAST_CALL_ET
————— ——————  ———————   ————————  ————————————  —————————— ————————————
3346  oradb   prodb1    pts/5     SYS$USERS     05-FEB-12          6848
2832  oradb   prodb1    pts/6     SERV1         05-FEB-12          7616
4408  ANANDA  ANLAP     ANLAP     ADHOC         05-FEB-12             0



Session waits for a specific machine

col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
        decode(state, 'WAITING', 'Waiting',
                'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where machine = 'appsvr1'
/



Getting the SQL
Another key piece of performance tuning information is the SQL statement a session is executing, which will provide more insights into the workings of the session. The same V$SESSION view also shows the SQL statement information. The SQL_ID column in the V$SESSION view shows the ID of the last SQL statement executed. You can get the text of that SQL statement from the V$SQL view, using the SQL_ID value. Here is an example of how I have identified the SQL statement executed by the session that appears slow to the user.



select sql_id from v$session
where sid = 3089;

SQL_ID
—————————————————
g0uubmuvk4uax

set long 99999
select sql_fulltext
from v$sql
where sql_id = 'g0uubmuvk4uax';
SQL_FULLTEXT
————————————————————————————————————————
update t1 set col2 = 'y' where col1 = 1



Data Access Issues
I have used row-level locking as the cause of a slowdown in this article. Although locking-related contention is a very common cause, it is not the only cause of performance problems. Another major cause of contention is disk I/O. When a session retrieves data from the database data files on disk to the buffer cache, it has to wait until the disk sends the data. This wait shows up for that session as “db file sequential read” (for index scans) or “db file scattered read” (for full-table scans) in the EVENT column, as shown below:



select event from v$session where sid = 3011;

EVENT
—————————————————————————
db file sequential read

Unix Script for renaming archivelog file with have different SID

Below useful unix script while performing Test Server Refresh.

cd /oracle/<TSID>/saparch
for i in 'ls|grep <SSID>';
do
file_name='echo $i |cut -dD -f2';
mv $i <TSID>${file_name};
done

Monitoring how much time physical standby database behind primary.

SQL> select * from V$DATAGUARD_STATS;

NAME                      VALUE              UNIT                           TIME_COMPUTED                  DATUM_TIME
------------------------- ------------------ ------------------------------ ------------------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval   09/03/2012 18:32:37            09/03/2012 18:32:35
apply lag                 +00 12:05:02       day(2) to second(0) interval   09/03/2012 18:32:37            09/03/2012 18:32:35
apply finish time         +00 00:00:04.972   day(2) to second(3) interval   09/03/2012 18:32:37
estimated startup time    23                 second                         09/03/2012 18:32:37

SQL>

if you see above apply lag showing 12 hrs 05 min and 2 sec behind primary.

but when check archived log list @ primary.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1145
Next log sequence to archive   1148
Current log sequence           1148
SQL>

as per above current redo log sequence 1148

Now when i checked @ standby server last applied log sequence number.

SQL> select sequence# last_sequence#,archived,applied,status from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

LAST_SEQUENCE# ARC APPLIED   S
-------------- --- --------- -
          1147 YES YES       A

SQL>

its showing 1147

last archived log already applied @ standby, So how apply tag showing value of 12 hrs and 05 min and 2 sec behind primary ?

Check last archived log time at primary.
[root@testsap1 ~]# ls -la /oracle/EHP/oraarch/ | grep 1147
-rw-r-----  1 oraehp dba 43153408 Sep  3 06:22 EHParch1_1147_790785542.dbf
[root@testsap1 ~]#

Its difference of last archived time and current time. ( current time 18:27:03 last archive log (1147) @ 06:22 ).