MSSQL locks

Use following to find blocking process

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

use master
GO
select * from sysprocesses (nolock) where blocked = 0 and spid in (
  select blocked from sysprocesses (nolock) where blocked <> 0
)
GO
#####################

To kill process which is blocking use following.

KILL 81;
GO

Use Following to find sessions or processes in the server.

sp_who2

GO

Resource Busy Ora-00054

Use following query to find blockers

select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

Use following query to get info of session holding lock.

set lines 100 pages 999
col username  format a20
col sess_id  format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
 , 0, 'Not Blocking'
 , 1, 'Blocking'
 , 2, 'Global') status
, decode(v.locked_mode
 , 0, 'None'
 , 1, 'Null'
 , 2, 'Row-S (SS)'
 , 3, 'Row-X (SX)'
 , 4, 'Share'
 , 5, 'S/Row-X (SSX)'
 , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where  v.object_id = d.object_id
and  v.object_id = l.id1
and  v.session_id = s.sid
order by oracle_username
, session_id
/

Show which row is locked

select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, 
    ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and  s.ROW_WAIT_OBJ# = do.OBJECT_ID
/

Then select the row with that rowid...

select * from <table> where rowid=<rowid>;

List locks

column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id
, lock_type
, mode_held
, mode_requested
, blocking_others
, lock_id1
FROM dba_lock l
WHERE  lock_type NOT IN ('Media Recovery', 'Redo Thread')
/

RMAN Duplicate database

Using RMAN to copy database from one machine to another.

Node1-----SR or source database
Node2-----DT or destination database.

Backups are on SBT tapes and using EMC Networker Module for Oracle.

Initial requirements.

Oracle installation version must be same on both nodes and both nodes are having same OS version.

I am taking an example of refresh from PROD to QA environment in which most OS and DB versions are same.

Networker client is installed on both nodes and configured for backup

Networker settings for source database on networker server must have set remote access list so that the database backups can be accessed by destination node.(Very important)

RMAN catalog database is already configured and backups are done using catalog.

on Destination

Shutdown the destination database.

sql> shutdown immediate


Go to directory where datafiles ,redo , control files are located and delete them(take backup to a different location before deleting).

$ cd /oracle/oradata/DT/
$cp /oracle/oradata/DT/ *    /backup/DT/
$ rm -f   *

Do not delete initDT.ora file if its deleted then create one with appropriate parameters.

Edit initDT.ora file and update following parameters for conversion of paths when data files are copied.

DB_FILE_NAME_CONVERT=('/oracle/oradata/SR','/oracle/oradata/DT')
LOG_FILE_NAME_CONVERT=('/oracle/oradata/aSR','/oracle/oradata/DT')

Backup source database to tapes (Not required if backup already exists on tapes and want to restore old backups)

on SOURCE database

Login as oracle or db owner UID.

$export NSR_SERVER=NWSERVER
$export NSR_CLIENT=NODE1
$export NSR_KEEPALIVE_WAIT=10
$export NSR_DATA_VOLUME_POOL=oracle     (If  tape pool is used)

rman>
rman>connect target sys/oracle@SR
rman>connect rcvcat rmanprod/rmanprod@rmanprod
run {
allocate  channel t1 type 'SBT_TAPE';
allocate  channel t2 type 'SBT_TAPE';
send 'NSR_ENV=(NSR_SERVER=NWSERVER,NSR_CLIENT=NODE1, NSR_DATA_VOLUME_POOL=oracle)';
backup database plus archivelog;
release channel t1;
release channel t2;
}

 Once database backup is complete on production.

Now you can start restore on Destination as follows.

connect to NODE2 as oracle or oracle installation owner UID

set environment for destination database DT

$ sqlplus / as sysdba

SQL> startup nomount pfile=/oracle/dbs/initDT.ora                     (path to init param file created above)
SQL> exit

Here
target= source database (SR)
catalog = same database as used during source backup
auxillary = destination database (DT)


set environment for DT database and following parameters

$export NSR_SERVER=NWSERVER
$export NSR_CLIENT=NODE1------------------> (Very important this must be NODE1 only since backups are taken there)
$export NSR_KEEPALIVE_WAIT=10
$export NSR_DATA_VOLUME_POOL=oracle

$rman
rman> connect target sys/oracle@SR
rman>connect rcvcat rmanprod/rmanprod@rmanprod
rman>connect auxiliary /
run {
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
configure channel device type 'SBT_TAPE' parms  'ENV=(NSR_DEBUG_FILE=/tmp/nsr_channel1.log,NSR_DEBUG_LEVEL=2)';
allocate auxiliary channel t1 type 'SBT_TAPE' debug=5 trace=2 parms 'ENV=(NSR_DEBUG_FILE=/tmp/nsr_channel1.log,NSR_DEBUG_LEVEL=2,NSR_DATA_VOLUME_POOL=oracle)';
debug on;
send 'NSR_ENV=(NSR_SERVER=NWSERVER,NSR_CLIENT=NODE1)';
set until time "to_date('2011-06-07:17:00:00','YYYY-MM-DD:hh24:mi:ss')";
duplicate target database to DT;
debug off;
release channel t1;
}

you can remove debug and also set untill if you dont need them.

Once duplicate is complete you can comment out the file conversion parameters added in init param file of DT.

Additional commands


Restart networker client service as ROOT user

#/sbin/service networker stop

#/sbin/service networker status

#ps -ef|grep nsr

#/sbin/service networker start




 Errors encountered.

Could not locate the LNM save file 'him8opgk_1_1' on server 'kap-us-297nw2'. (2:9:0)

Solution. Make sure you have the right tape volumes accessible for networker server.

Virtual IP in Oracle CRS

To check the value of IP/interface on RAC node.

$ srvctl config nodeapps -n {hostname} -a

Ex :
$ srvctl config nodeapps -n hostname -a
VIP exists.: /vipalias/10.0.125.12/255.255.255.224/lan900:lan901
IP=10.0.125.12
Subnet=255.255.255.224
Interface name=lan900:lan901


To change Network interface of VIP in CRS

Shutdown RAC DB and CRS nodeapps before executing following.

$ srvctl modify nodeapps -n hostname -A 10.0.125.12/255.255.255.224/lan900

Stop/Start Oracle RAC databases.

To Shutdown oracle RAC databases.
Execute following on Primary or node1

$ srvctl stop database -d {database name}

To Shutdown ASM

Execute following on each node.

$  srvctl stop asm -n {Hostname}

Execute following on node1/Primary to stop node apps.

$ srvctl stop nodeapps -n host1

Finally Stop CRS on each RAC node.

switch user to root and execute following
set environment of CRS_HOME and cd to $CRS_HOME/bin

# ./crsctl stop crs


To check crs

# ./crsctl check crs


__________________________________________________________________


Start CRS on each RAC node. (CRS auto starts as part of init script if it does not start then use following command)

Make sure CRS is up on all nodes before starting node apps,ASM,Database.

To check crs

# ./crsctl check crs


switch user to root and execute following
set environment of CRS_HOME and cd to $CRS_HOME/bin

# ./crsctl start crs


Execute following on node1/Primary to start node apps.

$ srvctl start nodeapps -n host1



To start ASM

Execute following on each node.

srvctl start asm -n {Hostname}


To start oracle RAC databases.
Execute following on Primary or node1

srvctl start database -d {database name}

Oracle CRS commands

To get the status of all nodeapps.

$ ./crs_stat -v -t

To see public and private interfaces.

$  oifcfg getif

HP-Unix network commands

To get IP and interface names.

#netstat -inw

# lanscan -i |awk '{print $1}'

ASM Disk Space

To Check free space diskgroup wise.

set lines 255
col path for a35
col Diskgroup for a15
col DiskName for a20
col disk# for 999
col total_mb for 999,999,999
col free_mb for 999,999,999
compute sum of total_mb on DiskGroup
compute sum of free_mb on DiskGroup
break on DiskGroup skip 1 on report -  
set pages 255
select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb, b.path, b.header_status from v$asm_disk b, v$asm_diskgroup a where a.group_number (+) =b.group_number order by b.group_number, b.disk_number, b.name
/

To Check disk group status.

SELECT STATE, NAME FROM V$ASM_DISKGROUP;

To mount/dismount diskgroup

ALTER DISKGROUP TEST123 MOUNT;

ALTER DISKGROUP TEST123 DISMOUNT;
Where TEST is diskgroup name

to Drop disk group

DROP DISKGROUP TEST123; --(from the ASM instance, on which diskgroup is still mounted make sure its unmounted on all other nodes).

To add disk to already exixting disk group.

Once disk is presented to nodes check access privileges to oracle or the user who is owner of ASM installation on all nodes this is very important.

If ASM lib is installed(Typically on Linux OS)

$ /etc/init.d/oracleasm  scandisks
$ /etc/init.d/oracleasm  listdisks
$ /etc/init.d/oracleasm  querydisk <each disk from previous output>

Check diskgroup to which you want to add disk

SELECT STATE, NAME FROM V$ASM_DISKGROUP;

Add disk to existing diskgroup as follows

ALTER DISKGROUP <my diskgroup> ADD DISK '<your candidate disk>';

Check if rebalance is complete.

select * from v$asm_operation;

if no rows returned then rebalance complete.