Dec 6, 2009

Unix DBA commands

Display total RAM (Memory) on the server

Tru64 Unix uerf –r 300 grep –i mem
Solaris prtconf grep –i mem
Linux free
HP-UX swapinfo –tm
AIX lsdev –C grep mem

lsattr –El

$ lsdev -C grep mem

Top ten process -Memory usage on the server:

# svmon -Pau 10

Displaying the No. of CPU processors in UNIX:

AIX lsdev –C grep Process wc –l
HP-UX ioscan –C processor grep processor wc –l
Solaris psrinfo –v grep “Status of Processor” wc –l
Linux cat /proc/cpuinfo grep processor wc –l

Display top CPU Consumers with ps:

$ ps –ef sort +7 tail

$ps auxgw sort +2 tail

$ps augxww grep “RSS “ head
The third column of this listing (%CPU) shows the percentage of CPU used.

To display processes consuming CPU% & elapsed time

ps -aef -o ruser,pid,pcpu,args,etime,

To list the tapes on your system

$ lsdev -C -c tape

To list the disks on your system

$ lsdev -C -c disk

Viewing UNIX volume groups

$ lsvg –o

-o Lists only the active volume groups (those that are varied on). An active volume group is one that is available for use.

Output on AIX

appvg1
rootvg

lsvg –l

For details on a specific volumne group, you can use lsvg –l, passing the volume group name as a parameter

$ lsvg -l rootvg

To see the previous logons and logoffs

last

Searching for a particular Text in the directory and sub-directories under that

find . -name "*" -exec grep -il {} \; tee

Find all files except owned by a specific user

Will list the file names containing the search string.

find . ! -user oracle9i -print

! ‘ negates the expression. The other example will list all the files not owned by user oracle9i.

Find files having some specific permission

find . –name *.* -perm 664 -print

Searching for links in all the folders under the directory and output to a text file to create a script to change the links.

find . -type l -print -exec ls -trl {} \; grep -i uat awk '{ print $11, $9 }' tee change_link.sh

Sending Mails

Sending a file as the body of the mail

For HP-UX
mailx –s “SUBJECT” yourmailid@id.com

For AIX
mail –s “” yourmailid@id.com <>

Sending file as attachment

uuencode file1.test file1.test mail yourmailid@id.com

You can’t specify the subject with mail.

Using mailx instead of mail will send the file as body of the mail and not as an attachment.

vi editor commands

Movement Commands

l or SPACEBAR or -> Moves right one character
h or CTRL-H or BACKSPACE or <- moves left one character j or CTRL-J or CTRL-N or down arrow move down one line k or CTRL-P or up arrow move up one line 0 beginning of line $ End of Line + or Enter Beginning of next line - Beg of Previous line w Move to next word or punctuation mark W Move to next word e Move to end of current word or punctuation E Move to end of current word b move back to beginning of word or punct B move back begning of word ) start of next sentence ( start of current sentence } start of next paragraph { start of current paragraph ]] start of next section [[ start of current section CTRL-F Move forward one full screen CTRL-D Move forward one half screen CTRL-B Move back one full screen CTRL-U Move back one half screen G Move to end of file CTRL-F move ahead full screens
CTRL-B move back full screens

Modifying Text

rn replace current char with ‘n’
rn replace characters with ‘n’
R
string ESC replaces current characters with ‘string until ESC is pressed

cwstringESC change the current word by replacing. The change continues until ESC is pressed. Vi puts $ over the last character of the work do be changed.

c$stringESC will change from current cursor position to end of line.

cw will change next words
3c$ will change next three lines

Deleting Text

x deletes current character
x deletes characgters
dw delete from cursor to end of the word
dw delete words
d$ delete to the end of line
D deletes to end of line (Same as d$)
d$ deletes till end of n lines ahead
d) deletes to beg of next line
d} deletes to beg of next paragraph
d]] deletes to be of next section
dd deletes current line
dd deletes n lines
dENTER deletes 2 lines
dG Deletes from Cursor to End of File

Undoing changes and deletions

u undo most recent change or deletion
U undo all changes made ina line since you last moved to that line
p put the contents to of the buffer to the right of the cursor (immediately below the current line)
P put the contents of the buffer to the left of the cursor (immediately above the current line)
:e! undo all the changes made since last time you save the file

Searching for text

/ seach forward for the specified string
/ENTER continue searching
? search backup for the specified string
n continue searching in the same direction

Copying and moving text

yw yanks (copies) a word
yw yanks words
y$ yanks to the end of line
y) yanks to the end of sentence
y} yanks to the end of paragraph
y]] yanks to the end of section
yy or Y yanks the current line
Y yanks lines
p paste to the right of the cursor
P paste to the left of the cursor

Nov 28, 2009

Interview question - RMAN

why RMAN is keeping archive log backups older than retention.
List backup shows archives older than datafile backups not expiring.
Reason: RMAN will keep the archive files, not expire them, because these older archives are needed to recover a datafile which is older than the rest of the database.
This file may either be offline or a 'begin backup' was issued on the tablespace without the 'end backup'.


Cause for Excessive growth in Archive Logs:
Reason: 1 )could be generated by the Automatic Workload Repository (AWR) and Active session History (ASH).
Solution: These advisories collect statistic data even when there's no activity in the database. They perfom inserts in to some of these v$ tables and then use the data collected to automatically tune the system.You can try to set the statistics_level = basic, this will reduce the amount of redo that is generated.
2) Check the Auditing has been enabled.
=========================================================================
RMAN Interview questions:

Scenario 1 : Backup is configured using RMAN recovery catalog database and TSM.
rcv_cat = repdb
target_database=prodcrm
control_file_record_keep_time=15
Daily the backup kicked off at 18:00 hours,
During the backup, recovery catalog database(repdb) went down due to server crash.
a) Will my backup run completed successfully or not?
b) if yes,what is the reason?
c) and if No, what is the reason?

Answer : Backup will continue. It will update all the information in the target database controlfile.
======================================================================

Session lvl
------------



Scenario : 1 what is the difference between "disconnect session" and "kill session" ?

Disconnect Session : primarily for TAF (failing over)

Killing Session : the session is dead, killed, resources release.

Scenario 2 : Yesterday, In a database, archive generation is 2 archive for 20mins and after creating the Materialized view using ONCOMMIT, my archive generation has increased to 3 per minute. What is the reason behind this?

ONCOMMIT -it refreshes automatically on the next COMMIT performed on the MASTER TABLE.

So the redo generation is generating more and more.

RMAN - keeping a backup valid for long term

rdbms : 10.2.0.4
o/s : RHEL4
Step 1
: From the sql session
SQL > SHUTDOWN IMMEDIATE
SQL > startup mount;


Step 2: From RMAN session
RMAN> BACKUP DATABASE KEEP FOREVER NOLOGS TAG 'ARCHIVE_1'
#make long-term consistent backup
# mark backup as unavailable in the repository
# so that RMAN does not attempt to restore it unless explicitly specified on the restore.
RMAN> CHANGE BACKUP TAG 'ARCHIVE_1' unavailable
RMAN>sql 'alter database open';

Note : Nocatalog mode is not supported for keep forever.
RMAN 06522 : KEEP FOREVER option is not supported without the recovery catalog

Nov 24, 2009

LibraryCacheNotEmptyOnClose

o/s : IBM-AIX 5.3
rdbms version :10.2.0.4

ORA-00600 [LibraryCacheNotEmptyOnClose]

Cause :
can occur during shutdown.

Workaround:
Ignore the error as it just indicates that there are
some items in the library cache when closing down
the instance. The error itself occurs AFTER the database
close and dismount stages so only affects the instance
shutdown itself. Datafiles have been closed cleanly.

As per the metalink doc :466425.1

Known Issues:
  Bug# 4483084   See Note:4483084.8
OERI[LibraryCacheNotEmptyOnClose] on shutdown
Fixed: 11.1.0.6

Nov 23, 2009

Database got crashed - Needs recovery of undotbs

OS : IBM-AIX 5.3
RDBMS : 10.2.0.4
Scenario: Server abruptly crashed
Cause: corruption of block change tracking file and undotbs of our recovery catalog database.

Workaround:

Step 1 : After starting the server the database did not came up. We found the following error in the alert log file.

ORA-00600: internal error code, arguments: [krctcr_4], [137290073], [0],
[137309719], [0], [], [], []

Step 2 : We found the metalink doc related this issue: 553009.1

Step 3 : while performing the steps given in above metalink doc .

we faced
SQL> select name,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE
--------- ---------- ------------
XXXXXXX MOUNTED ARCHIVELOG


SQL> alter database disable block change tracking;

Database altered.



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01172: recovery of thread 1 stuck at block 1033 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed


Step 4 : Since we have a cold backup.
We restored the files from the coldbackup and applied all the archive logs and opened
the database.


a) Shutdown the instance (which is running in mount state).

b) restored the file from the backup dir.(
XXXXXX_full_27042009_20:30:00.tar.Z)

$ uncompress XXXXXX_full_27042009_20:30:00.tar.Z
$ tar -xvf /opt/oracle/backup/XXXXXX_full_21112009_17:30:00.tar.Z /opt/oradata/dbarep/XXXXX

c) This zip files contains all controlfile, datafiles etc...

Note : check all the archive logs are in place.
SQL>startup nomount
SQL>alter database mount
SQL>set autorecovery on
SQL>recover database using backup controlfile untill time '2009-11-22:02:09:00';
SQL>ALTER DATABASE OPEN RESETLOGS;



'2009-11-22:02:09:00';