RMAN backup script.

#!/bin/bash
SHELL=/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export ORACLE_SID=xxx

#wd=`date +%Y_%m_%d`
#mkdir -p /orabackup/test_backup/$wd

rman target backup/backup "two less than simbole" EOF
run {
set controlfile autobackup format for device type disk to '/backup/%F';
allocate channel c1 device type disk format='/backup/%d_%T_%U';
backup database;
sql 'alter system archive log current';
backup archivelog all;
delete noprompt archivelog all completed before 'SYSDATE-7';
release channel c1;
}
EOF

Linux automate backup copy to windows script

#!/bin/bash

SHELL=/bin/bash


# get sysdate to variable
wd=`date +%Y_%m_%d`


# get previous day to variable if required
#wd=`date -d "-1 day" +"%Y_%m_%d"`


mount -t smbfs -o username=administrator,password=password //[IP]/Exp /share/winShare

tar -czPf /share/winShare/MiddleDB_$wd.tgz /orabackup/backup/$wd/*


umount /share/winShare

Find Lock on database.

select username,
v$lock.sid,
id1, id2,
lmode,
request, block, v$lock.type , v$session.osuser , v$session.machine
from v$lock, v$session
where v$lock.sid = v$session.sid
and v$lock.type = 'TX'
--and v$session.username = USER
order by username

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

select lock.

SQL> select * from v$lock ;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0


select lock object

SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA

get the record.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1 a

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

Lock Type

'RT', 'Redo Thread'
'UN', 'User Name'
'TX', 'Transaction'
'TM', 'DML'
'UL', 'PL/SQL User Lock'
'DX', 'Distributed Xaction'
'CF', 'Control File'
'IS', 'Instance State'
'FS', 'File Set'
'IR', 'Instance Recovery'
'ST', 'Disk Space Transaction'
'TS', 'Temp Segment'
'IV', 'Library Cache Invalidation'
'LS', 'Log Start or Switch'
'RW', 'Row Wait'
'SQ', 'Sequence Number'
'TE', 'Extend Table'
'TT', 'Temp Table'

Oracle password profile.

credit to Scott Stephens

CREATE PROFILE myprofile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;Users assigned to this profile will be locked out of theiraccounts after five login attempts with an incorrect password. The account willbe inaccessible for one day or until a DBA issues this command:

ALTER USER ACCOUNT UNLOCK.Even after several years, I've found that my old passwordstill works on previous projects. This makes a good case for placing a limit ona password's lifetime so it will expire after a certain period (e.g., at theend of a contract). There's also an option to allow a specific grace period,which is useful for projects that aren't used very often. If the user doesn'tlog in until after the password expires, the user can still connect, but awarning will display until the grace period expires. Use the PASSWORD_LIFE_TIMEand PASSWORD_GRACE_TIME tags on a profile to enable these features.

ALTER PROFILE myprofile LIMIT PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 3;Users assigned to that profile will be locked out of theiraccounts 30 days after the last time the password is changed. After 30 days,attempting to log in will result in warning messages for three more days beforethe account is locked.

Many users will see these limits and simply try to resettheir passwords to what they were previously using rather than using a newpassword each time. You can prevent users from reusing a password with thePASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX tags.

ALTER PROFILE myprofile LIMIT PASSWORD_REUSE_TIME 30 PASSWORD_REUSE_MAX 100;Users with this profile will not be able to reuse a passwordfor 30 days, or until after they change the password 100 times.

Finally, some users will use passwords that are easy toguess. It's possible to restrict a password's format (such as checking for aminimum width, letters, numbers, or mixed case, or verifying that the passwordisn't a variation of the username) by creating a PL/SQL procedure thatvalidates passwords. You must format the procedure like this:

CREATE OR REPLACE FUNCTION verify_password( useridvarchar(30), password varchar(30), old_passwordvarchar(30)) RETURN BOOLEAN…You can assign this function (which can be any name, but itmust be owned by the SYS account) with the following:

ALTER PROFILE myprofile LIMIT PASSWORD_VERIFY_FUNCTION verify_password;

Delete Files Older Than x Days on Linux

The find utility on linux allows you to pass in a bunch of interesting arguments, including one to execute another command on each file. We’ll use this in order to figure out what files are older than a certain number of days, and then use the rm command to delete them.

Command Syntax

find /path/to/files* -mtime +5 -exec rm {} \;

Note that there are spaces between rm, {}, and \;

Explanation

• The first argument is the path to the files. This can be a path, a directory, or a wild card as in the example above. I would recommend using the full path, and make sure that you run the command without the exec rm to make sure you are getting the right results.
• The second argument, -mtime, is used to specify the number of days old that the file is. If you enter +5, it will find files older than 5 days.
• The third argument, -exec, allows you to pass in a command such as rm. The {} \; at the end is required to end the command.

This should work on Ubuntu, Suse, Redhat, or pretty much any version of linux.

Top SQL session monitoring

Get the TOP session by following command

$ top

top - 14:30:26 up 36 days, 20:35, 3 users, load average: 0.59, 1.09, 1.37
Tasks: 532 total, 4 running, 528 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.5%us, 1.2%sy, 0.0%ni, 87.5%id, 7.4%wa, 0.1%hi, 0.3%si, 0.0%st
Mem: 32960328k total, 32489652k used, 470676k free, 259284k buffers
Swap: 16876140k total, 77236k used, 16798904k free, 26004652k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8167 oracle 15 0 6151m 131m 122m S 10 0.4 0:00.46 oracle
14723 oracle 15 0 6150m 2.1g 2.1g R 4 6.8 1:20.46 oracle
24356 oracle -2 0 6160m 4.5g 4.5g S 3 14.4 148:25.96 oracle
24360 oracle -2 0 6160m 4.5g 4.5g S 3 14.4 145:54.39 oracle
15450 oracle 15 0 6152m 1.8g 1.7g S 3 5.6 0:34.34 oracle
2517 oracle 15 0 6150m 536m 528m S 2 1.7 0:16.67 oracle
24354 oracle 15 0 6160m 153m 140m S 2 0.5 21:09.91 oracle

get the top CPU utilize PID.

apply PID to below select

select x.inst_id,x.sid, x.serial#,x.USERNAME,x.osuser,x.MACHINE,x.TERMINAL,x.program,x.TYPE,x.sql_hash_value,x.module,y.sql_text
from gv$session x,gv$sqlarea y
where paddr in (select addr from gv$process where spid =8167 )
and x.SQL_ADDRESS = y.address

Select the HASH value and apply below select.
select SQL_TEXT from v$sqlTEXT where HASH_VALUE in (2024423863) ORDER BY PIECE;