11g New Features:

11g New Features:

1. Adaptive Cursor sharing.

2.Adaptive Direct Path Reads.

3.adrci utility for the purge the trace files.

$ adrci
adrci> help

4. SCAN Ip  for simplified Cilent Access

5.Automatic restart of single instance.

6.SRVCTL support for single instance.

7. Real Application testing - Database replay

8.Active Datagaurd.

Why is my index not being used?

Why is my index not being used?

1. where surname != 'FRASER'
2. where upper(surname) = 'FRASER'
3. where surname is null
4. where surname like '%FRASER'
5. small tables
6. histograms:
   
   90% rows 'Yes'
   1% rows 'No'
   1% 'Maybe'
   1% 'Not in'
   1% 'Later'
   where answer = 'Yes'

----> IN is faster than EXISTS for subqueries that return few rows.
----> EXISTS is faster than IN for subqueries that return many rows.

Oracle Interview Questions....

1. DIFFERENCE  between migration and upgaration mode ?

2. How to add the voting disk ?

3. How to find out the master node ?

4. How to take the OCR and Votingdisk backup ?

5. Which process will write  in to the alert log file ?

6. What you will see in the AWR report  ?

7. Why VIP ?

8. Types of Failovers ?

9.What is the use of change track file ?

10.What will happens when the rman lose the change track file ?

11 . Use of fal_client and fal_server ?

12 . Diff between max performacne and max avilability mode ?

13. Types of Oracle rac services ?

14. What will happens when arch2 process kills ?

15. Location of the CRS backup files ? how it will maintain the backups ?

16. Default optimizer mode ?

17. Diff. between analyzed table casecade and with out ?

18. What will happens when the index rebuild online and with out ?

19. Where you will find the node evictions ?

20. What is the save point in the flash back recovery ?

21. What is bindpeeking ?

22. Use of Histograms and types ?

23. what will happens in the adptive curser sharing ?

24. How "Real Time Appy" works ?

25. 11g Features ?

26. Where you will find out the datagaurd status ?

ans:
archived _gap
managed_staby
archive_log_files.
$dataguard_status
V$RECOVERY_PROGRESS

27. Active datagaurd feature in the 11g ?

28. What you will do if the standby database slow ?

29. After rebooting the server ...cluster shuld not reboot what parameter you shuld set ?

30. In the ASM what will happens when you will added the sum amount of disks?

31. Which command used to recover the block using rman ?

32. What are the base version for the 10 g and 11 g  oracle software ?

33. What is the option in the opatch to remove the 1 patch from the oracle software ?

34. How to diagnos the cluster node evictions ?

35. What is the "DB Sequencial Read " and  what action you will take if the awr top wait event always ?


36. How you find out the DG working properly or not ?

37 . If you received the archive destination reached 99% in your production database , what action you will take ?

38 . IF you define the delay parameter in the archive _dest what will happens ?

39. How to check the CRS backup ?

40 . Who will writes data into the databuffer cache ?

42. What Parameter you will use in the init.ora parameter to setup the DG high avilability mode ?

43. Why SCAN ?

44. what will happens when you will run the root.sh script ?

45. what type of errors you will get in the crs installation while running root.sh script ?

46. Use of diagnal.pl script ?

47. How the TableSpace Pointing Time recvery will works ?

48.In the RAC instance how you will configure the channels for rman backup ?

49.Diff types of protection modes in the DG ?

50. In the max protection mode the database how much time it will wait for the standby database if any network issues ?

51. Which method is good in DG  ? fail over or swith over ? why ?

52. Back ground process of cluster and rac use of them ?

53. What are the ASM backgroud process and Instant background process which is using the asm files ?

54. Types of redendancy in the rman and explain ?

55. Explain about the update process in the Database ?

56. In which mode of database operation rollback and rollfarward will happens ?

57. Newly created datafile missed there is no backup..how to recver ?

58. Sunday full backup and daily incremental backup in the evening  .Monday evening  incremental backup successfull , tuesday morning changetrack file deleted , what will happens to the tuesday evening backup ?

59. What are the RAC features in the 11g R2 ?

60. Two tables are similar size One session truncating the table and other session deleting the table , In the both tables zero rows.after that 2 sessions are selecting on that table ,which query will run fast and why ?

61.How rman will manages the consistancy in the backup  with out redo generation.

62. session  wait event showing like  idle event but blocking the other sessions ,what might be the cause ?

63. What is your approch  when the sunday  night jobs taking the long time  and monday morning p1 call ,what are the details get from the client and what are the diff ways you will check to find out the root cause. 

64. Which process will write  into the alert log file.

65. what is Adaptive cursor sharing .

66.  Use of SCAN ip and how it will work.

 

Role /datafile created Time and Column sizes............

Role Created Time..........

SELECT name AS role, ctime AS created FROM sys.user$

Check The column Size:

select num_rows from dba_tables where table_name='TSS_ORDER_LINES_F';

select AVG_COL_LEN from dba_tab_columns where COLUMN_NAME='WAYBILL_NUMBERS' and table_name='TSS_ORDER_LINES_F';

DataFile Addition date :


col file_name format a65
col tablespace_name format a25
set lin 120
select df.file_id, df.file_name, vdf.creation_time, (df.BYTES/1024)/1024 FILE_SIZE_MB
from dba_data_files df, v$datafile vdf
where df.tablespace_name like 'APPS_TS_SUMMARY'
and df.file_id=vdf.file#
ORDER BY df.tablespace_name, VDF.CREATION_TIME;

Build the Inventory NoteID's...................

MOS Doc 556834.1 - Steps To Recreate Central Inventory(oraInventory) In RDBMS Homes
MOS Doc 472854.1 - How to Recreate the OraInventory on UNIX Systems

Import with out Export dump using network Link..............

In the Target Host....


1 . Create a directory OS level and database level.

mkdir /oracle/export/ASSTEST

CREATE DIRECTORY ASSTEST AS '/oracle/export/ASSTEST';

2. Add the tnsentry of source Db.

create public database link ASSTEST CONNECT TO system IDENTIFIED BY asts4sprd using 'ASSTEST_BACKUP';

3.import your schema using below syntax.

 impdp system/manager99 parfile=schema_ASSTEST.par



vi schema_ASSTEST.par
=============================
DIRECTORY=ASSTEST
NETWORK_LINK=ASSTEST
PARALLEL=20
SCHEMAS=ANC_APPL,IR2_ADMIN,
EXCLUDE=STATISTICS
LOGFILE=ASSTEST.log trace=480300
JOB_NAME=ASSTEST1

To check High redo generation sessions .....

set lines 132
set pages 100
set time on
set timing on
col username format a20
col program format a30
spool redo_vol
--1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
--   how much blocks have been changed by the session. High values indicate a
--   session generating lots of redo.
--   The query you can use is:
       SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
         FROM v$session s, v$sess_io i
        WHERE s.sid = i.sid
          AND i.block_changes > 200
        ORDER BY 5 desc, 1, 2, 3, 4;
--   Run the query multiple times and examine the delta between each occurrence
--   of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.
--2) Query V$TRANSACTION. This view contains information about the amount of
--   undo blocks and undo records accessed by the transaction (as found in the
--   USED_UBLK and USED_UREC columns).

--  The query you can use is:
      SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec
        FROM v$session s, v$transaction t
       WHERE s.taddr = t.addr
         AND t.used_ublk > 200
       ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
--   Run the query multiple times and examine the delta between each occurrence
--   of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
--   the session.
spool off

Locks Holders and Waiters..............

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,         
       id1, id2, lmode, request, type   
FROM V$LOCK  
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)   ORDER BY id1, request  ;

How to find Master Node in Oracle RAC

 

I have seen many users asking how to find “Master node” in Oracle RAC, let me clear some of their doubts.

There are two types of Masters in Oracle RAC, one is Mater node at Oracle Clusterware level and other is Master node for specific resource or block or object.

The node which gets the active state during startup is authorized to be a master node by Cluster Synchronization Service.

Run the below command to find which node is master at Clusterware level
$cat $ORA_CRS_HOME/log/`hostname`/cssd/ocssd* |grep master
or
$ for x in `ls -tr $ORA_CRS_HOME/log/`hostname`/cssd/ocssd* `; do grep -i "master node" $x ; done | tail -1

The OCR Automatic backups are taken only by master node. If the Master fails, the OCR backups will be created on the new Master. The Master node which has OCR backups goes down due to failure then we cannot be recover the OCR that’s why Oracle recommends taking backups using “ocrconfig” and also integrating OCR backups with backup strategy.

Run the below command to find which node is OCR Master and taking automatic backups.
$ocrconfig –showbackup
testrac02 2010/08/30 16:29:52 /oracle/crs/cdata/crs
testrac02 2010/08/30 16:29:52 /oracle/crs/cdata/crs
testrac02 2010/08/30 12:29:49 /oracle/crs/cdata/crs
testrac02 2010/08/30 08:29:46 /oracle/crs/cdata/crs
testrac02 2010/08/29 00:29:23 /oracle/crs/cdata/crs

The block level masters are used by Cache fusion while transferring the block. Any node can become the master node of a particular block and you can also see which node acting as master in V$GES_RESOURCE table (MASTER_NODE column)

You can manually remaster an object with oradebug command:
SQL> oradebug lkdebug -m pkey "object_id"

Why is excessive redo generated during an Online/Hot Backup ......

There is not excessive redo generated, there is additional information logged into
the online redo log during a hot backup the first time a block is modified in a
tablespace that is in hot backup mode. 

in hot backup mode only 2 things are different:

o the first time a block is changed in a datafile that is in hot backup mode, the ENTIRE
BLOCK is written to the redo log files, not just the changed bytes.  Normally only the
changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged
the FIRST TIME.  This is because you can get into a situation where the process copying
the datafile and DBWR are working on the same block simultaneously.  Lets say they are
and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time).
The backup program goes to read an 8k Oracle block.  The OS gives it 4k.  Meanwhile --
DBWR has asked to rewrite this block.  the OS schedules the DBWR write to occur right
now.  The entire 8k block is rewritten.  The backup program starts running again
(multi-tasking OS here) and reads the last 4k of the block.  The backup program has now
gotten an impossible block -- the head and tail are from two points in time.  We cannot
deal with that during recovery.  Hence, we log the entire block image so that during
recovery, this block is totally rewritten from redo and is consistent with itself at
least.  We can recover it from there.

o the datafile headers which contain the SCN of the last completed checkpoint are NOT
updated while a file is in hot backup mode.  This lets the recovery process understand
what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one
tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon
as you have backed it up.  This will reduce the number of blocks that may have to be
logged to the minimum possible.
---by tom

How much Recovery is enough Recovery

Long ago in one of the non-technical group discussions we had to decide ‘How much money is enough money?’ Obviously no answer is enough answer. Fortunately in Oracle, we know how much recovery is just sufficient to OPEN the DB.

During the recovery of the DB, when the archives are being applied one after other and another, is it possible to definitively confirm that the most recently applied archive was the last one required to be applied and it is safe thereafter to open the DB in RESETLOGS?
There should be several ways of identifying where to stop, and here are three that I can think of:

1. Alert log – If the backup was hot, finding out the last occurrence of END BACKUP and its corresponding SEQ# should tell us where to stop. Usually we stop after applying the above identified SEQ# and the DB should then be good to OPEN RESETLOGS.
2. RMAN log – If the backup was taken using RMAN, its log also shows the SEQ# where the backup ends. We can stop applying archives once we reach the max SEQ#
3. Query the Data Dictionary – Query the FHSTA column in X$KCVFH table during recovery to know if the recovery was sufficient or does it need more archives.

As I said, there must be other ways too.
I prefer option (3) because the information is right there in the DB to be queried for, anytime.
Consider a typical scenario where a user, say Lucy, is recovering the DB from the hot backup of the source database. She has already applied 30 archives and swears that she hasn’t seen another hungrier database. She has no access to the alert log of the source database to check how many more archives are to be applied. After each archive log has been applied, she CANCELs the recovery and gets the below error:
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u11/data/PRD/PRD_SYSTEM_01.DBF’
The better way to confirm if the recovery was sufficient or not is to query the data dictionary. The SQL is:
SELECT hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE FROM x$kcvfh;
SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh


The output will look like above.
The STATUS (or fhsta) column has the value of 1 which means the datafile with FILENUMBER needs more recovery. This is the time to apply more archives.
The STATUS is 0 when the recovery is sufficient and it is safe to OPEN the database.
The fhsta can have one of the below values:
0     – DB is consistent. No more recovery required.
1     – DB needs more recovery. It’s time to apply more archives.
4     – DB is in a FUZZY state. Was the backup good?
8192  -
8196  -
I have come across 8192 and 8196 statuses too, but I don’t know their significance yet.
When the archives have been sufficiently applied, the fhsta column will be zero and looks like below:

At this stage, it is possible to cancel the recovery and bring up the DB in RESETLOGS.
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL> alter database open resetlogs;
Database altered.
The table X$KCVFH is a fixed table in Oracle. It stores the file headers along with their statuses. The name is derived from:
K     – Kernel layer
C     – Cache layer
V     – RecoVery component
FH    - File Header
Hope this post was useful.

Check the rman backup details from catalog

set pagesize 5000;
set linesize 400;
col OBJECT_TYPE for a10;
col STATUS for a15;
col operation for a15;
select DB_KEY,DB_NAME,STATUS,START_TIME,END_TIME,INPUT_BYTES/1024/1024/1024 INPUT_BYTES,OUTPUT_BYTES/1024/1024/1024 OUTPUT_BYTES,OBJECT_TYPE,OPERATION from rc_rman_status where  START_TIME >= (sysdate-1) and OBJECT_TYPE not like '%ARCH%' and OPERATION not in ('DELETE','LIST') order by db_name,START_TIME;

 

RMAN clonning with Duplicate database option

rman target sys/abc123@ibpausa1 catalog rman/rman_oncdbmp@rmanmna2 auxiliary /

run
{
set until time "to_date('30-APR-2010 05:30','DD-MON-YYYY hh24:MI')";
allocate auxiliary channel C1 device type disk;
SET NEWNAME FOR DATAFILE 1 to '/oracle/data/dbpa/usa1/vol30/dbpausa1.system.a001.dbf';
SET NEWNAME FOR DATAFILE 2 to '/oracle/data/dbpa/usa1/vol40/dbpausa1.undo.a001.dbf';
SET NEWNAME FOR DATAFILE 3 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.sbpa0001.a001.dbf';
SET NEWNAME FOR DATAFILE 4 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.users.a001.dbf';
SET NEWNAME FOR DATAFILE 5 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.tools.a001.dbf';
SET NEWNAME FOR DATAFILE 6 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a001.dbf';
SET NEWNAME FOR DATAFILE 7 to '/oracle/data/dbpa/usa1/vol20/dbpausa1.xbpa0001.a001.dbf';
SET NEWNAME FOR DATAFILE 9 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a002.dbf';
SET NEWNAME FOR DATAFILE 10 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a003.dbf';
SET NEWNAME FOR DATAFILE 11 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.sysaux.a001.dbf';
SET NEWNAME FOR TEMPFILE 1 to '/oracle/data/dbpa/usa1/vol50/dbpausa1.tps00001.a001.dbf';
duplicate target database to dbpausa1;
}

log_file_name_convert =("/oracle/data/ibpa/usa1/", "/oracle/data/dbpa/usa1/")
db_file_name_convert =("/oracle/data/ibpa/usa1/", "/oracle/data/dbpa/usa1/")


ORACLE noteid's:


Note 228257.1 - RMAN 'Duplicate Database' Feature in Oracle9i / Oracle 10G
Note 259694.1 - Oracle10G RMAN Database Duplication
Bug 5327865 - CLONING THE DB USING 10G OEM "SEARCH FOR HOST" RESULTS IN (NO ITEMS FOUND)

Clonning Pre steps..........................

PRE -STEPS
set lines 200
set pages 9999
col owner for a20
col db_link for a30
col username for a15
col host for a40
col created for a12
spool db_details.lst
select name from v$database;
select * from global_name;
select * from dba_db_links;
select name from v$controlfile;
select member from v$logfile;
select file_name from dba_data_files;
select file_name from dba_temp_files;
spool off
spool create_db_links.sql
select 'create DATABASE LINK '||NAME|| ' connect to '|| userid || ' identified by '|| password || ' using '||''''|| host ||''''||'; ' FROM
sys.link$ where name='EXTPROC_CONNECTION.CISCO.COM';
spool off
spool alter_user.sql
select ' alter user '||username||' identified by values xxxxx'||password|| 'xxxxx;' from dba_users;
spool off
Set verify off
Set space 0
set feedback off;
set echo off;
set pages 1000;
set lines 150;
spool create_synonym.sql
Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR mailto:'%7C%7Ctable_owner%7C%7C'.'%7C%7Ctable_name%7C%7C'@'%7C%7Cdb_link%7C%7C';' from dba_synonyms where db_link
is not null and table_owner is not null;
Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR mailto:'%7C%7Ctable_name%7C%7C'@'%7C%7Cdb_link%7C%7C';' from dba_synonyms where db_link is not null and
table_owner is null;
spool off;

select count(1) from dba_synonyms where db_link is not null and table_owner is null;

restore database , catalog start with option

catalog start with

CATALOG START WITH '/fs1/datafiles/';

connect target /;
startup force nomount;
restore spfile from '${CTL_PIECE}';
shutdown immediate;
startup nomount;
restore controlfile from '${CTL_PIECE}';
shutdown immediate;
startup mount;
catalog start with '${BACKUP_LOC}' noprompt;
restore database;
recover database;
sql 'alter database open resetlogs';
EOF

RMAN restore commands

restore archivelog from logseq=100 until logseq=110;
restore archivelog from time='15-JUN-05' until time='20-JUN-05';

run {
set archivelog destination to '/oracle/data/dsed/mna1/vol90/';
allocate channel ch1 type disk;
restore archivelog
from logseq 51066
until logseq 51076;
}

run {
set archivelog destination to '';
allocate channel ch1 type disk;
restore archivelog
from logseq 49751
until logseq 49770;
}

RMAN list commands

IST BACKUP;
LIST BACKUP OF DATABASE;
LIST BACKUP SUMMARY;
LIST INCARNATION;
LIST BACKUP BY FILE;
LIST COPY OF DATABASE ARCHIVELOG ALL;
LIST COPY OF DATAFILE 1, 2, 3;
LIST BACKUP OF DATAFILE 11 SUMMARY;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
LIST BACKUPSET OF DATAFILE 1;
LIST BACKUP OF ARCHIVELOG all;
LIST EXPIRED BACKUP;

 list backup of database completed between '01-MAY-07' and '07-MAY-07' ;

 list backup of database;

 list backup of database summary completed between '01-MAY-07' and '07-MAY-07';

 list backup of archivelog all;

list backup of archivelog from time  '05-MAY-07';

list backup of archivelog high sequence=10796;

list backup of archivelog low sequence=10796;
      LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 10966;
list backup of archivelog from time '26-JUN-07';
backup archivelog until time 'SYSDATE-6' delete input;
backup archivelog sequence between 6 and 29 delete input;
list backup of archivelog time between '17-JUN-07' and '18-JUN-07';
list backup of database summary TAG 'clone_db_bk_full';
list backupset completed between '25-OCT-07' and '30-OCT-07' TAG 'clone_db_bk_full';

To get the report in HTML format

conn as sysdba SQL> set markup html on;
spool 1212703_preprod.xls

Get DDL ................

set verify off
set long 100000
set lines 132
set pages 0
prompt 'Pl Enter the Input in Capital Letters............'
accept owner char prompt 'Enter Owner : '
accept object_type char prompt 'Enter Object Type [TABLE/INDEX/MATERIALIZED VIEW/PACKAGE/PROCEDURE/FUNCTION] : '
accept object_name char prompt 'Enter Object Name : '
spool get_ddl
select dbms_metadata.get_ddl('&&object_type','&&object_name','&&owner') from dual;
spool off

No . of Insertions in the Database

 select substr(sql_text,instr(sql_text,'INTO "PRO'),50) Inserts,
         rows_processed,
         round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
         trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
  from   sys.v_$sqlarea
  where  upper(sql_text) like 'INSERT %' and sql_text like '%SL%';

UNIX Commands For ORACLE

list commands:

ls –ltr

cat alert_LIN1.log | grep -i ORA-

cat /proc/cpuinfo

process commands:

ps –ef |grep “ora_”

ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID

ps -ef |awk '{print $1}'|sort -u

change dir :

cd ..

view command:

vi

view

more

Find the CPU count :

grep -c processor /proc/cpuinfo

uname –X

/usr/sbin/psrinfo |wc –l

/usr/sbin/psrinfo -p

Memory chechecking

dmesg | grep Memory

cat /proc/meminfo

AIX $ /usr/sbin/lsattr -E -l sys0 -a realmem
Linux $ /usr/sbin/dmesg | grep "Physical:"
HP $ grep MemTotal /proc/meminfo
Solaris $ /usr/sbin/prtconf | grep "Memory size"
Tru64 $ /bin/vmstat -P | grep "Total Physical Memory"

Display RAM Memory size on Solaris
prtconf |grep -i mem

:

Display RAM memory size on AIX
First determine name of memory device
lsdev -C |grep mem
then assuming the name of the memory device is ‘mem0’
lsattr -El mem0


Display the number of CPU’s in Solaris
psrinfo -v | grep “Status of processor”|wc –l

Display the number of CPU’s in AIX
lsdev –C | grep Process|wc –l

cpu performance:

TOP

TOPAS

SAR

$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):

Time	%usr	%sys	%wio	%idle
11:57:31	72	28	0	0
11:57:41	70	30	0	0
11:57:51	70	30	0	0
11:58:01	68	32	0	0
11:58:11	67	33	0	0
11:58:21	65	28	0	7
11:58:31	73	27	0	0
11:58:41	69	31	0	0
Average	69	30	0	1

%usr: Percent of CPU in user mode

%sys: Percent of CPU in system mode

%wio: Percent of CPU running idle with a process waiting for block I/O

%idle: Percent of CPU that is idle

SAR  -u  <CPU report >

Sar –d <disk usage >

to create the file :

touch

cat

disk free:

df -k

disk usage:

du -sg

remove the file

rm -rf

remove dir:

rmdir

rm -rf

copy or move

cp <source to dist>

move <sorce to dest>

cp -r <inclusing sub dir>

last and first lines:

tail -n filename

head -n filename

echo <variable>

chmode -R 777 <filename>

chown oracle:dba <dir name>

chgrp filename

FIND and REMOVE :

find /oracle/sftw -name <filename>

find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;

FTP commands:

ftp servername

ftp>binary

get

put

cd

!pwd

pwd

bye

ls

Mail commands:

mail -s "Test mail"  "sss@gmail.com" < msg.log

who

kill -9 <process id>

hostname

uname -a

telnet <hostname>

ifconfig

jobs

sysreserve

User management :

useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user

• The "-G" flag specifies the primary group.
• The "-g" flag specifies the secondary group.
• The "-d" flag specifies the default directory.
• The "-m" flag creates the default directory.
• The "-s" flag specifies the default shell.

usermod -s /bin/csh my_user

userdel -r my_user

passwd my_user

wc –l ===è gives the count                         

COMPRESS the files :

Gzip <filename >

Zip <filename>

Compress <filename>

Gunzip < filename >

Unzip <filename>

Uncompress < filename >

SYSTEM statistics :

VMSTAT

DISPLAY < hostname > :<port no >

Export DISPLAY

Tar –cvf <filename>.tar <file/dir loc>

Tar –xvf <filrname>.tar

History

Date

Time

Whoiam

Diff

At

Crontab

Uptime --- server uptime

Export Exit Codes

INTRODUCTION
============
This is a short reference note describing the EXIT codes (errorlevel)
used by the Export and Import utilities.

EXIT CODES
==========
A. Successful export/import.
   -------------------------
   Result    : Export terminated successfully without warnings
               Import terminated successfully without warnings
   Exit Code : EX_SUCC
   Exit Level: 0
B. Successful export/import with warnings.
   ---------------------------------------
   Result    : Export terminated successfully with warnings
               Import terminated successfully with warnings
   Exit Code : EX_OKWARN
   Exit Level: 0 (for Unix platforms)
               1 (for Windows platform with Oracle9i, Oracle8i, and below)
               3 (for Windows platform with Oracle10g and higher)
C. Unsuccessful export/import.
   ---------------------------
   Result    : Export terminated unsuccessfully
               Import terminated unsuccessfully
   Exit Code : EX_FAIL
   Exit Level: 1 (for Unix platforms, and for Windows platforms with
                  Oracle10g and higher)
               3 (for Windows platform with Oracle9i, Oracle8i, and below)

CHECK
=====
To determine the errorlevel at the commandline after a command ends:
- Unix Bourne/Korn shell:
 echo $?
- Unix C Shell:
 echo $status
- Windows:
 echo %ERRORLEVEL%

echo %ORACLE_SID%

SET COMMANDS

ECHO {OFF|ON}
   Display commands as they are executed

HEA[DING] {OFF|ON}
   print column headings

LIN[ESIZE] {150|n}
   Width of a line (before wrapping to the next line)
   Earlier versions default to 80, Oracle 9 is 150

LONG {80|n}
   Set the maximum width (in chars) for displaying and copying LONG values.

PAGES[IZE] {14|n}
   The height of the page - number of lines.
   0 will suppress all headings, page breaks, titles

set timing on;
set time off;

DBMS_JOBS examples

--
-- Schedule a snapshot to be run on this instance every hour
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour
-- ------------------------------------------------------------
dbms_job.submit(
:jobno, 'statspack.snap;',
trunc(sysdate)+6/24,
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0900 and run 12 hours later
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+9/24,
'trunc(SYSDATE+12/24,''HH'')',
TRUE,
:instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every 10 minutes
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1/144,'MI'),
'trunc(sysdate+1/144,''MI'')',
TRUE,
:instno);
-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE,''MONDAY''),
next_day(SYSDATE,''TUESDAY''),
next_day(SYSDATE,''WEDNESDAY''),
next_day(SYSDATE,''THURSDAY''),
next_day(SYSDATE,''FRIDAY'')
)
+1/24,''HH'')',
TRUE,
:instno);
commit;
end;
/

Piped EXPORT/IMPORT

mknod is used to create special files,

*  Create a block-type special file
*  Create a character-type special file
*  Create a FIFO (named pipe)


$mknod name b major minor
Creates a block-type special file
major, minor are device numbers

$mknod name c major minor
Creates a character-type special file

$mknod name p
Creates a FIFO named pipe.

Examples:
If datafiles are larger than 2GB, pipes can be used to read or extract information
from compressed datafiles.

In Oracle a table can contain more than 2GB of data, but lower versions of Solaris 2.x
doesn't support larger files. We can use unix pipes to export data and compress. Similar
way we can import data without uncomressing an export dump file.

Below is an example of how to use UNIX's named pipes for an exp/imp:

Creating a compressed export file

   $ mknod /tmp/exp_pipe p                     # Create pipe
   $ compress < /tmp/exp_pipe > export.dmp.Z & # Compress
   $ exp file=/tmp/exp_pipe &ltother options>   # Export to the pipe
 
Reading a compressed export file

   $ mknod /tmp/imp_pipe p                       # Create pipe
   $ uncompress < export.dmp.Z > /tmp/imp_pipe & # Uncompress
   $ imp file=/tmp/imp_pipe < other options >  # Import from the pipe
 
Exporting to tape via unix named pipes
  
   $ mknod /tmp/exp_pipe p                     # Create pipe
   $ dd if=/tmp/exp_pipe of=< tape device> &    # Write from pipe to tape
   $ exp file=/tmp/exp_pipe < other options >  # Export to the pipe 

Importing from tape via unix named pipes

   $ mknod /tmp/imp_pipe p                     # Create pipe
   $ dd of=/tmp/imp_pipe if=< tape device> &    # Write from tape to pipe
   $ imp file=/tmp/imp_pipe < other options >  # Import from the pipe 

SQL*Loading from tape via unix named pipes

   $ mknod /tmp/ldr_pipe p                         # Create pipe
   $ dd of=/tmp/ldr_pipe if=< tape device> &        # Write from tape to pipe
   $ sqlload data=/tmp/ldr_pipe < other options >  # Load from the pipe 

To Check Last analyzed table date ...........

select count(1),owner,trunc(last_analyzed) from dba_tables where owner not in ('SYS','SYSTEM','OUTLN','PERSTAT') group by owner,trunc(last_analyzed) order by owner,trunc(last_analyzed);

Session Info .....................

col sid format 9999
col username format a10
col osuser format a10
col program format a25
col process format 9999999
col spid format 999999
col logon_time format a13
set lines 150
set heading off
set verify off
set feedback off
undefine sid_number
undefine spid_number
rem accept sid_number number prompt "pl_enter_sid:"
col sid NEW_VALUE sid_number noprint
col spid NEW_VALUE spid_number noprint

         select  s.sid   sid,
                p.spid  spid
--              ,decode(count(*), 1,'null','No Session Found with this info') " "
         FROM v$session s,
              v$process p
         WHERE s.sid LIKE NVL('&sid', '%')
         AND p.spid LIKE NVL ('&OS_ProcessID', '%')
         AND s.process LIKE NVL('&Client_Process', '%')
         AND s.paddr = p.addr
--       group by s.sid, p.spid;
PROMPT Session and Process Information
PROMPT -------------------------------
col event for a30
select '    SID                         : '||v.sid      || chr(10)||
       '    Serial Number               : '||v.serial#  || chr(10) ||
       '    Oracle User Name            : '||v.username         || chr(10) ||
       '    Client OS user name         : '||v.osuser   || chr(10) ||
       '    Client Process ID           : '||v.process  || chr(10) ||
       '    Client machine Name         : '||v.machine  || chr(10) ||
       '    Oracle PID                  : '||p.pid      || chr(10) ||
       '    OS Process ID(spid)         : '||p.spid     || chr(10) ||
       '    Session''s Status           : '||v.status   || chr(10) ||
       '    Logon Time                  : '||to_char(v.logon_time, 'MM/DD HH24:MIpm')   || chr(10) ||
       '    Program Name                : '||v.program  || chr(10)
from v$session v, v$process p
where v.paddr = p.addr
and v.serial# > 1
and p.background is null
and p.username is not null
and sid = &sid_number
order by logon_time, v.status, 1
/

PROMPT Sql Statement
PROMPT --------------
select sql_text
from v$sqltext , v$session
where v$sqltext.address = v$session.sql_address
and sid = &sid_number
order by piece
/
PROMPT
PROMPT Event Wait Information
PROMPT ----------------------
select '   SID '|| &sid_number ||' is waiting on event  : ' || x.event || chr(10) ||
       '   P1 Text                      : ' || x.p1text || chr(10) ||
       '   P1 Value                     : ' || x.p1 || chr(10) ||
       '   P2 Text                      : ' || x.p2text || chr(10) ||
       '   P2 Value                     : ' || x.p2 || chr(10) ||
       '   P3 Text                      : ' || x.p3text || chr(10) ||
       '   P3 Value                     : ' || x.p3
from v$session_wait x
where x.sid= &sid_number
/
PROMPT
PROMPT Session Statistics
PROMPT ------------------
select        '     '|| b.name  ||'             : '||decode(b.name, 'redo size', round(a.value/1024/1024,2)||' M', a.value)
from v$session s, v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and name in ('redo size', 'parse count (total)', 'parse count (hard)', 'user commits')
and s.sid = &sid_number
and a.sid = &sid_number
--order by b.name
order by decode(b.name, 'redo size', 1, 2), b.name
/
COLUMN USERNAME FORMAT a10
COLUMN status FORMAT a8
column RBS_NAME format a10
PROMPT
PROMPT Transaction and Rollback Information
PROMPT ------------------------------------
select        '    Rollback Used                : '||t.used_ublk*8192/1024/1024 ||' M'          || chr(10) ||
              '    Rollback Records             : '||t.used_urec        || chr(10)||
              '    Rollback Segment Number      : '||t.xidusn           || chr(10)||
              '    Rollback Segment Name        : '||r.name             || chr(10)||
              '    Logical IOs                  : '||t.log_io           || chr(10)||
              '    Physical IOs                 : '||t.phy_io           || chr(10)||
              '    RBS Startng Extent ID        : '||t.start_uext       || chr(10)||
              '    Transaction Start Time       : '||t.start_time       || chr(10)||
              '    Transaction_Status           : '||t.status           
FROM v$transaction t, v$session s, v$rollname r
WHERE t.addr = s.taddr
and r.usn = t.xidusn
and s.sid = &sid_number
/
PROMPT
PROMPT Sort Information
PROMPT ----------------
column username format a20
column user format a20
column tablespace format a20
SELECT        '    Sort Space Used(8k block size is asssumed    : '||u.blocks/1024*8 ||' M'             || chr(10) ||
              '    Sorting Tablespace                           : '||u.tablespace       || chr(10)||
              '    Sort Tablespace Type                 : '||u.contents || chr(10)||
              '    Total Extents Used for Sorting               : '||u.extents 
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr
AND s.sid = &sid_number
/

set heading on
set verify on
clear column

DB Upgrade Note ID's

Note.420146.1 - What is the Best Way to Upgrade?
Note 316889.1 - Complete checklist for manual upgrades to 10gR2
Note:316889.1 for 10g upgrades.
ID 429825.1----Complete checklist for manual upgrades to 11gR1
419550.1

If you are upgrading to any 10.2.0.x version on AIX5L, review the following note before upgrading:
Note 557242.1 "Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed"

TO take Procedure Backup

REM procedure, package or package body.
REM This SCRIPT is provided as a guide and is not supported by
REM ORACLE Corporation.
set verify off
set feedback off
set lines 132
set pages 0
set heading off
set space 0
column text format a79
column line noprint
select DECODE(line,1,'create or replace ','')||text, line
from dba_source
where
owner = upper('TRUTK') and
type = upper('PROCEDURE')
and name = upper('SYS')
and line >= 1;
set verify on
set feedback on

To check the Partition Info

 SELECT A.TABLE_NAME,A.PARTITIONING_TYPE,B.PARTITION_NAME,B.TABLESPACE_NAME,D.FILE_NAME,D.BYTES/1024/1024/1024 "BYTES" FROM
 DBA_TAB_PARTITIONS B,DBA_PART_TABLES A,DBA_TABLESPACES C,DBA_DATA_FILES D WHERE A.TABLE_NAME=B.TABLE_NAME  AND
 D.TABLESPACE_NAME=C.TABLESPACE_NAME AND C.TABLESPACE_NAME=B.TABLESPACE_NAME  AND A.TABLE_NAME IN(B_FORECAST_MSPN_DEMAND_TREND)

To Take the User Syntax..............

Create user systax
==========================================
select 'create user ' || '&usr' ||
       ' identified by ' || '&password' ||
       ' default tablespace ' || du.default_tablespace ||
       ' temporary tablespace ' || du.temporary_tablespace ||
       ' account unlock profile ' || du.profile
from dba_users du
where du.username = upper('&&usr') ;

quota for a user
==========================
select 'quota ' || decode(dtq.max_bytes,-1,'UNLIMITED',dtq.max_bytes) ||
       ' on ' || dtq.tablespace_name
from   dba_ts_quotas dtq
where  dtq.username = upper('&&usr') ;

/* GRANT NON-DEFAULT ROLES */
select 'grant ' || drp.granted_role || ' to ' || drp.grantee ||
       decode(drp.admin_option,'YES', ' with admin option ;', 'NO', ' ;')
from  dba_role_privs drp
where drp.grantee = upper('&&usr')
and   drp.default_role = 'NO' ;
/* GRANT DEFAULT ROLES */
select 'alter user ' || drp.grantee || ' default role ' || granted_role || ' ;'
from dba_role_privs drp
where drp.grantee = upper('&&usr')
and   drp.default_role = 'YES' ;
/* GRANT SYSTEM PRIVS */
SELECT 'grant ' || dsp.privilege || ' to ' || dsp.grantee ||
       decode(dsp.admin_option,'YES',' with admin option ;','NO',' ;')
from dba_sys_privs dsp
where dsp.grantee = upper('&&usr') ;
/* GRANT OBJECT PRIVS (NEED TO ADD CONNECT STATEMENTS) */
SELECT 'grant ' || dtp.privilege || ' on ' ||
       dtp.owner || '.' || dtp.table_name ||
       ' to ' || dtp.grantee ||
       decode(dtp.grantable, 'YES',' with grant option ;','NO',' ;')
from dba_tab_privs dtp
where dtp.grantee = upper('&&usr') ;

TO Connect to DB with out tns entry .............

Using the following command one can pass hostname, port, servicename and SID and connect to a database without having an entry in tnsnames.ora
$ sqlplus username/password@hostname:port/SERVICENAME
OR
$ sqlplus username
Enter password: password@//hostname:port/SERVICENAME
OR
$ sqlplus /nolog
SQL> connect username/password@hostname:port/SERVICENAME

Get Temporary Tablespace Usage

SELECT A.tablespace_name tablespace,D.mb_total,SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total-SUM (A.used_blocks * D.block_size)/1024/ 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D  WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;


TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP2 27000 80 26920

Get Current TEMP space usage by session id (9i/10g):
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;