Block Dump

Sometimes it is necessary to take block dump of a particular segment(table or index) to identify few things such as number of ITL slots allocated to support concurrency,etc.

A) Select the datafile where the segments are located.

SQL> select FILE_ID from dba_data_files where TABLESPACE_NAME=(select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

FILE_ID
----------
7
14
16

B) Get range of blocks corresponding to the segment.

select 'alter system dump datafile 7 block min '||c1||' block max '||c2||';' from (select block_id c1,(block_id + blocks -1) c2 from dba_extents where segment_name='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

select 'alter system dump datafile 14 block min '||c1||' block max '||c2||';' from (select block_id c1,(block_id + blocks -1) c2 from dba_extents where segment_name='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

select 'alter system dump datafile 16 block min '||c1||' block max '||c2||';' from (select block_id c1,(block_id + blocks -1) c2 from dba_extents where
segment_name='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

C) Do the block dump

The output of the previous step would be something similar to below. Just execute these statements and the output will be generated under user_dump_dest.

alter system dump datafile 7 block min 661 block max 664;
alter system dump datafile 7 block min 3633 block max 3632;
alter system dump datafile 7 block min 4181 block max 4180;

RMAN Archivelog Backup and Recovery Script

Backup

To Tape

$rman catalog rman_user/rman_password@rcat target /
RMAN> run {
# Archivelog backup
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
sql 'alter system archive log current';
backup
filesperset 20
skip inaccessible
format 'al_%s_%p_%t'
(archivelog all
delete input);
}

To Disk

run {
crosscheck archivelog all;
crosscheck backup of archivelog all;
delete expired archivelog all;
backup
format '/u12/backup/archive/%d_arch_%T_%U'
FILESPERSET 20
ARCHIVELOG ALL;
}

To Tape using stored script

We can store the above script in RMAN catalog as shown below.

RMAN> create script hot_archive_backup_level0 {
# Archivelog backup
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
sql 'alter system archive log current';
backup
filesperset 20
skip inaccessible
format 'al_%s_%p_%t'
(archivelog all
delete input);
}

This can be executed as
RMAN > run { hot_archive_backup_level0; }

Restore

From Tape

$rman catalog rman_user/rman_password@rcat target /
RMAN>run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
SET ARCHIVELOG DESTINATION TO '/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 1041 UNTIL SEQUENCE 1042;
}

From Disk

$rman catalog rman_user/rman_password@rcat target /
RMAN>run {
allocate channel d1 type disk format '/u12/backup/archive/%d_arch_%T_%U';
SET ARCHIVELOG DESTINATION TO '/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 71222UNTIL SEQUENCE 71223;
}

output:

$ rman catalog rmanuser/rmanpass@catdb target /

Recovery Manager: Release 9.2.0.6.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: MYDB (DBID=177653166)
connected to recovery catalog database

RMAN> run {
allocate channel d1 type disk format '/u12/backup/archive/%d_arch_%T_%U';
SET ARCHIVELOG DESTINATION TO '/tmp';
RESTORE ARCHIVELOG FROM SEQUENCE 71222UNTIL SEQUENCE 71223;
}2> 3> 4> 5>

allocated channel: d1
channel d1: sid=35 devtype=DISK

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 15-JUN-06

channel d1: starting archive log restore to user-specified destination
archive log destination=/tmp
channel d1: restoring archive log
archive log thread=1 sequence=71222
channel d1: restoring archive log
archive log thread=1 sequence=71223
channel d1: restored backup piece 1
piece handle=/u12/backup/archive/MYDB_arch_20060615_i9hllscs_1_1 tag=TAG20060615T072352 params=NULL
channel d1: restore complete
Finished restore at 15-JUN-06
released channel: d1

RMAN> exit

Recovery Manager complete.


$ ls -ltr *.ARC
-rw-r----- 1 oracle dba 604672 Jun 15 12:42 T0001S0000071223.ARC
-rw-r----- 1 oracle dba 597504 Jun 15 12:42 T0001S0000071222.ARC

RMAN Database Backup and Recovery Script

Backup

Hot Backup

To Tape

$rman catalog rman_user/rman_password@rcat target /
RMAN> run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
backup
incremental level 0
skip inaccessible
tag hot_db_bk_level0
filesperset 5
format 'db_%s_%p_%t'
(database);
}

To Tape using stored script

We can store the above script in RMAN catalog as shown below.

RMAN> create script hot_database_backup_level0 {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
backup
incremental level 0
skip inaccessible
tag hot_db_bk_level0
filesperset 5
format 'db_%s_%p_%t'
(database);
}

This can be executed as
RMAN > run { hot_database_backup_level0; }

Cold Backup

$ cat rman_cold_backup.sh
{
rman nocatalog <connect target /
shutdown immediate;
startup mount;
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
backup
format '/u01/oradata/ColdBack/%d_%s:%t:%p'
database
include current controlfile
;
}

exit
!
} >rman_backup.log
mailx -s "Backup ended for Q01" 1234567890@vtext.com <check logfile
!




!>

Tuesday, May 30, 2006

Unix: HOW-TO remove largest(in size) five files

find . -name "*.*" -ls | sort +6 | tail -5

rm `find . -name "*.*" -ls | sort +6 | tail -5|cut -c68-`

alias large5="find ./ -name \"*.*\" -ls | sort +6 | tail -5"

Unix: HOW-TO send an email with attachments

/usr/bin/uuencode ./testing.html testing.html > ./test123.htm
mailx -s "OLPR Reports" Sami_id@yahoo.com < ./test123.htm

Renaming group of files

Renaming Groups of Files
Suppose you have a group of files whose filenames end in .c and you want to rename them so that the filenames end in .cc. Your first try might be
mv *.c *.cc

but this won't work! Why? Because the shell expands the filenames before executing the mv command. It is the same as typing
mv file1.c file2.c file3.c *.cc

which isn't what you want to do. The solution is to use a loop. The loop's format is shell dependent.
C-Shell
If you are using the c-shell or tc-shell the following command will work.
foreach old ( *.c )
set new=`echo $old | sed 's/.c/.cc/'`
mv $old $new
end

Bourne, Korn, Bash and Z-Shell
If you are using the Bourne shell or a derivative (including the Korn, bash or Z-shell) the following command will work.

for old in *.c; do
new=`echo $old | sed 's/.c/.cc/'`
mv $old $new
done

HOW-TO remove space between lines

$cat space_between_lines.txt
first line

second

third



$sed /^$/d space_between_lines.txt
first line
second
third
$

Unix : Vi Editor - Convert Upper to Lower Case & Vice Versa

Convert from Uppercase to Lowercase
===================================
1)Open vi
2)Shift Colon
:%s/.*$/\L&

Convert from Uppercase to Uppercase
===================================
1)Open vi
2)Shift Colon
:%s/.*$/\U&

HOW-TO use PL/SQL Table

Oracle Technology Blog: HOW-TO use PL/SQL Table

Return a result from SQL*Plus to Unix variable

Oracle Technology Blog: Return a result from SQL*Plus to Unix variable

HOW-TO use DBMS_PROFILE

Oracle Technology Blog: HOW-TO use DBMS_PROFILE

Pagination Query

select col1,col2,(select count(col3) from table1 ) cnt
from (select a.*, rownum rnum
from (select *
from table1
order by col1
) a
where rownum <= 550)
where rnum >= 525;

Explain Plan in table format

SQL> select * from table(dbms_xplan.display);

| Id | Operation | Name | Rows | Bytes | Cost | Pstart|Pstop |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 | ||
|* 1 | TABLE ACCESS FULL | SALES | 1 | 21 | 2 | 2 |2 |
----------------------------------------------------------------------------

Setup extended trace

alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='my_trace_file_name';

alter session set events '10046 trace name context forever, level 12';

execute sys.dbms_system.set_ev(46, 13217, 10046, 12, '');


dbms_system.set_bool_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'timed_statistics',
bval => true)
dbms_system.set_int_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'max_dump_file_size',
intval => 2147483647)

Unix(Solaris) command to check CPU,Memory and IO statistics

/usr/bin/isainfo -kv
psrinfo -v
mpstat 1 3
iostat 1 3
iostat -xtc 5 2
vmstat 1 3
/etc/swap -s
/etc/swap -l
ipcs -b|grep -i oracle
cat /etc/system
/usr/sbin/prtconf | grep Memory
sysdef | grep -i SHMMAX
sysdef | grep -i SEMMNS

How much redo generated for a particular DB operation

column value new_val V
set verify off

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';

NAME VALUE
------------------------------ ----------
redo size 153976776

exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );

select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';

NAME VALUE DIFF
------------------------------ ---------- ----------------
redo size 330108116 176,131,340

HOW-TO return CLOB datatype in PL/SQL

CREATE OR REPLACE PROCEDURE Clobtestproc(o_clob OUT CLOB)
AS
tmpclob CLOB;
tmpxml VARCHAR2(100);
BEGIN
tmpxml :='ABC';
DBMS_LOB.createtemporary(tmpclob, FALSE);
DBMS_LOB.WRITE (tmpclob,LENGTH (tmpxml), 1, tmpxml);
o_CLOB := tmpclob;
DBMS_LOB.freetemporary(tmpclob);
END;
/

Invoke function from SQL*Loader

It is possible to call any built-in or user-defined function during load process. Usually it's done for date columns, when non-default date format must be used, however user-defined function(s) may be called to perform some application logic.

The syntax for such calls is quite simple:

LOAD DATA
INFILE *
APPEND
INTO TABLE dept (
deptno POSITION(01:02) INTEGER EXTERNAL,
dname POSITION(03:16) CHAR "LDR_PCK.NAME(:dname, :deptno)",
loc POSITION(17:29) CHAR
)
BEGINDATA
21Dep Loc
22Dep Loc
Here LDR_PCK.NAME is name of package and function, :dnname and :deptno are parameters.

When calling user-defined functions we must remember that only conventional path may be used. An attempt to use direct path will cause an error:

SQL*Loader-00417 SQL String (on column column_name) not allowed in direct path.

Return a result from SQL*Plus to Unix variable

$ cat a.sh
#!/bin/sh
var="set pagesize 0
select * from v\$instance;"
nad=$(print "$var" | sqlplus -s "/ as sysdba")
echo $nad
$ a.sh
1 mydb_name myhostname 9.2.0.6.0 16-MAY-06 OPEN YES 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL

Setup nohup in Unix

$ cat a.sh
sqlplus << EEE
username/password
@a.sql
exit
EEE

$ nohup a.sh &

Customized SQL Prompt

open $ORACLE_HOME/sqlplus/admin/glogin.sql

column global_name new_value gname
set termout off
select lower(user) || '@' || substr(global_name,1,instr(global_name,'.',1)-1) global_name
from global_name;
set termout on
set sqlprompt '&gname SQL> '

HOW-TO use PL/SQL Table

set serveroutput on

begin
-- Declare the PL/SQL table
declare
type deptarr is table of dept%rowtype index by binary_integer;
d_arr deptarr;
type deptarr1 is table of dept.deptno%type;
type deptarr2 is table of dept.dname%type;
type deptarr3 is table of dept.loc%type;
v_deptno deptarr1;
v_dname deptarr2;
v_loc deptarr3;
type deptarr10 is table of dept.deptno%type;
type deptarr20 is table of dept.dname%type;
type deptarr30 is table of dept.loc%type;
v_deptno10 deptarr10;
v_dname20 deptarr20;
v_loc30 deptarr30;

-- Declare cursor
type d_cur is ref cursor return dept%rowtype;
c1 d_cur;
i number := 1;
begin
select deptno,dname,loc bulk collect
into v_deptno,v_dname,v_loc
from dept;

-- Populate the PL/SQL table from the cursor
open c1 for select * from dept;
fetch c1 bulk collect into v_deptno10,v_dname20,v_loc30;
-- loop
-- exit when c1%NOTFOUND;
-- fetch c1 bulk collect into d_arr(i);
-- i := i+1;
-- end loop;
close c1;


-- Display the entire PL/SQL table on screen
--for i in 1..d_arr.last loop
for i in 1..v_deptno.last loop
dbms_output.put_line('DEPTNO1 : '||v_deptno(i));
dbms_output.put_line('DNAME1 : '||v_dname(i));
dbms_output.put_line('LOC1 : '||v_loc(i));

dbms_output.put_line('DEPTNO10 : '||v_deptno10(i));
dbms_output.put_line('DNAME20 : '||v_dname20(i));
dbms_output.put_line('LOC30 : '||v_loc30(i));

---dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
---dbms_output.put_line('DNAME : '||d_arr(i).dname );
---dbms_output.put_line('LOC : '||d_arr(i).loc );
dbms_output.put_line('---------------------------');
end loop;
end;
end;
/

HOW-TO use DBMS_PROFILE

If you are not already configured DBMS_PROFILE package look the following script in Oracle Home->rdbms->admin

SCRIPT : PROFLOAD.SQL and PROFTAB.SQL
--------------------------------------
SETUP :
-------

U:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 14 13:26:03 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/***** as sysdba
Connected.

SQL> @D:\oracle\rdbms\admin\profload.sql
Package created.
Grant succeeded.
Synonym created.
Library created.
Package body created.
Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

Create the profiler tables
--------------------------

SQL> connect hr/hr
Connected.
SQL> @D:\oracle\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints *
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints *
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints *
ERROR at line 1:
ORA-00942: table or view does not exist

drop sequence plsql_profiler_runnumber *
ERROR at line 1:
ORA-02289: sequence does not exist

Table created.
Comment created.
Table created.
Comment created.
Table created.
Comment created.
Sequence created.


The above script will create the following table
PLSQL_PROFILER_DATA TABLE
PLSQL_PROFILER_RUNS TABLE
PLSQL_PROFILER_UNITS TABLE

Let us create a sample table and a procedure for demonstration

SQL> create table testemp(emp number);
Table created.


CREATE OR REPLACE PROCEDURE hr.testproc
IS
i NUMBER := 0;
vempno NUMBER;
BEGIN
WHILE i <> Set serveroutput on;
SQL>
SQLDECLARE
vrun NUMBER;
BEGIN
vrun := SYS.DBMS_PROFILER.start_profiler ('TESTRUN1');
DBMS_OUTPUT.put_line ('START PROFILER STATUS ' || vrun);
hr.testproc; -- calling procedure
vrun := SYS.DBMS_PROFILER.stop_profiler;
DBMS_OUTPUT.put_line ('STOP PROFILER STATUS ' || vrun);
DBMS_OUTPUT.put_line ('0 successful');
DBMS_OUTPUT.put_line ('1 incorrect parameter');
DBMS_OUTPUT.put_line ('2 data flush operation failed');
DBMS_OUTPUT.put_line ('-1 version mismatch between package and tables');
END;
/

Expected Output
================

START PROFILER STATUS 0
No of record100000
STOP PROFILER STATUS 0
0 successful
1 incorrect parameter
2 data flush operation failed
-1 version mismatch between package and tables

PL/SQL procedure successfully completed.

Now run the sql to find out the most extensive part of the plsql

SET linesize 132
COL unit format a20
COL tc format 9999999 heading "Exection"
COL stext format a40
SELECT u.unit_owner || '.' || u.unit_name unit, line#,
ROUND (d.total_time / 1000000000) total_time_s, d.total_occur tc,
SUBSTR (s.text, 1, 40) stext
FROM plsql_profiler_runs r,
plsql_profiler_units u,
plsql_profiler_data d,
all_source s
WHERE r.run_comment = 'TESTRUN1'
AND r.runid = u.runid
AND d.runid = u.runid
AND u.unit_number = d.unit_number
AND s.owner = u.unit_owner
AND s.TYPE = u.unit_type
AND s.NAME = u.unit_name
AND s.line = d.line#
ORDER BY line#
/

Expected Output

UNIT LINE# TIME[s] Exection STEXT
--------------- ---------- ------------ -------- ----------------------------------------
HR.TEST 3 0.000 1 i number := 0;
HR.TESTPROC 6 58.000 100001 while i < 100000
HR.TESTPROC 8 4,951.000 100000 insert into hr.testemp(emp ) values (i);
HR.TESTPROC 9 2,901.000 100000 commit;
HR.TESTPROC 10 131.000 100000 i := i + 1;
HR.TESTPROC 12 6.000 1 select count(*) into vempno from hr.test
HR.TESTPROC 13 0.000 2 dbms_output.put_line('No of record'||vem
HR.TESTPROC 14 1,461.000 1 delete from hr.testemp;
HR.TESTPROC 15 0.000 1 commit;
HR.TESTPROC 16 70.000 100001 for i in 1..100000
HR.TESTPROC 18 5,371.000 100000 insert into hr.testemp(emp ) values (i);
HR.TESTPROC 19 2,955.000 100000 commit;

Use Time [s] value to determine only those areas where performance is slow. It may not be equal to the elapsed time.