Searching for a user (enhanced)

Now the same as before but using a Windows batch FOR loop.
The script iterates through the file "oracle.dat", that contains "username password SID" in every line. Then connects with sqlplus using this information and executes "busca.sql" that do and formats the search.


BUSCA.BAT

@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
@echo off
@echo >%1.out
FOR /F "tokens=1,2,3" %%G IN (oracle.dat) DO (sqlplus -S %%G/%%H@%%I @c:\busca.sql %1>>%1.out)

type %1.out|find "->"

REM ^ List only nontrivial results
type %1.out|find /c /i "%1"
REM ^ Count the results

ORACLE.DAT
(Information separated by ONE space. Can be configured with "option delims=xxx")

system password SID
....


BUSCA.SQL
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1');
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado||')');
end if;
exception
when no_data_found then null;
end;
/
exit

EXAMPLE

C:\>busca rman
Se genera el fichero "rman.out"
RESUMEN:
========
AE02 -> aecobde9 ---> RMAN (OPEN)
AE03 -> arepbde1 ---> RMAN (OPEN)
AE05 -> aremsae4 ---> RMAN (OPEN)
PROD -> sun06.sscc.intranet.local ---> RMAN (OPEN)
RMAN -> rman ---> RMAN (OPEN)
RMAN9 -> rman ---> RMAN (OPEN)
XE -> bcnaorarman ---> RMAN (OPEN)
AOLT -> sun03 ---> RMAN (OPEN)
8

Searching for a user

It's usual to have to locate where an user resides in. Here it's a way to do it from my Windows, that have all access permisions. Another way is to install a Linux or emulator and do it with a shell script (some day...).

Result:

C:\>busca perfstat
Se genera el fichero "perfstat.out"
RESUMEN:
========
AE02 -> aecobde9 ---> PERFSTAT (OPEN)
AE03 -> arepbde1 ---> PERFSTAT (OPEN)
AE05 -> aremsae4 ---> PERFSTAT (OPEN)
AE07 -> aecobde9 ---> PERFSTAT (OPEN)
AE08 -> arepbde1 ---> PERFSTAT (OPEN)
AE09 -> arepbde1 ---> PERFSTAT (OPEN)
FINP -> sunfin ---> PERFSTAT (OPEN)
PROD -> sun06.sscc.intranet.local ---> PERFSTAT (OPEN)
RMAN -> rman ---> PERFSTAT (OPEN)
SCENTER1 -> bcnascec1p01 ---> PERFSTAT (OPEN)
AOLT -> sun03 ---> PERFSTAT (OPEN)
APP02 -> aremsar1 ---> PERFSTAT (OPEN)
APP05 -> agenbdr1 ---> PERFSTAT (OPEN)
13

And now the implementation:

C:\>type busca.bat
@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
echo ***************************>%1.out
echo *** P R O D U C C I O N ***>>%1.out
echo ***************************>>%1.out

sqlplus -S system/password@AE01 @c:\busca.sql %1>>%1.out
sqlplus -S system/password@AE01-10 @c:\busca.sql %1>>%1.out
....
type %1.out|find "->"
type %1.out|find /c /i "%1"


C:\>type busca.sql
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1'
);
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado
||')');
end if;
exception
when no_data_found then null;
end;
/
exit

Event trigger after logon

This trigger was necessary to execute 1-2 remote selects after login, because all the selects trough db links were working only the third time it was issued, a very strange behaviour. I had to create two similar triggers and they were very effective until we found the root cause.

CREATE OR REPLACE TRIGGER toni_bdn AFTER LOGON ON database
DECLARE
v_user VARCHAR2(30); x number;
a integer; b integer; c integer; a1 integer; a2 integer; a3 integer;
b1 integer; b2 integer; b3 integer;
BEGIN
if sys_context('userenv','session_user') in ('SYSTEM','SYS','BDNOWN01','BDNUSR01') then
a:=dbms_sql.open_cursor;
dbms_sql.parse(a, 'select count(1) from midual@BDN_DL_ROMEGA', dbms_sql.native);
execute immediate 'select count(1) into x from midual@BDN_DL_ROMEGA';
b:=dbms_sql.execute(a);
b1:=dbms_sql.execute(c);
b2:=dbms_sql.execute(a);
b3:=dbms_sql.execute(a);
b:=dbms_sql.execute(a);
dbms_sql.close_cursor(a);
end if;
END;
/

Creating objects without STORAGE clauses

In some teams it's usual not to indicate neither storage clauses nor tablespace clauses, so I've gotten some interest in studying the consecuences. Here are the results with a single table in Oracle 10GR2:

I create a single table with no clauses:

create table sense_clausules (col varchar(2000)) tablespace PL_DATOS;

col segment_name for a30
col INITIAL_EXTENT for 999,999
col next_extent, bytes for 999,999
select segment_name, INITIAL_EXTENT, next_extent, bytes
from dba_segments
where segment_name=upper('sense_clausules')
order by 1;


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 65536

Next I load a lot of rows:


set serveroutput on
declare i number;
j number;
begin
for j in 1..100000 loop
for i in 1..1000 loop
insert into sense_clausules values ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') nologging;
end loop;
commit;
dbms_output.put_line(j*1000);
end loop;
end;
/


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 75.497.472

select count(1) from sense_clausules;

COUNT(1)
------------
1.998.010

select sum(bytes)
from dba_extents
where segment_name=upper('sense_clausules')
order by 1;

SUM(BYTES)
------------
148.897.792

And now the quantity and size of the extensions after the load:

col segment_name format a30
select segment_name,bytes, count(*)
from dba_extents
where segment_name=upper('sense_clausules')
group by segment_name,bytes
order by 2,3;

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 6291456 1
SENSE_CLAUSULES 8388608 9

4 files seleccionades.

Now a more heavy load and observe:


COUNT(1)
------------
100.000.000

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 8388608 120
SENSE_CLAUSULES 35651584 1
SENSE_CLAUSULES 67108864 94

5 files seleccionades.

294 extensions created for 100 milions rows.

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 148.897.792


drop table sense_clausules cascade constraints;

I, personally, prefer to indicate the INITIAL and NEXT clauses in the way the capacity plan indicates.

Checking the status of an instance

We need to verify if, as a consequence of a hot backup, the instance remains in a "suspend" status, then put it in open (resuming it), put all the tablespaces in "end backup", and sending a mail only when it do all this. The script follows up.

###############################################
##### resucita.sh ######
##### Authors: Toni Blanco ######
##### Fecha de creacion: 200800906 ######
##### Ultima modificacion: 20080906 ######
##### Revision: v1.0 ######
###############################################
# Si la instancia es troba en estat SUSPEND li fa un RESUME i envia un correu a explo.sistemas
# Si es troba en estat OPEN no fa res, només m'envia un correu a mi.
. /export/home/oracle/.profile
date
resultado=0
# I catch the result of the invocation to sqlplus to determine the status
# of the instance, using the dbms_output API.

resultado=`sqlplus -S "/as sysdba"<< EOF
set serveroutput on;
declare
estat varchar2(20):='nada';
a integer;
b integer;
c varchar2(100);
x integer:=0;
cursor c1 is select tablespace_name
from dba_tablespaces
where status<>'ONLINE'
order by 1;
fc1 c1%rowtype;

begin

select status into estat from "v\$instance" ; -- Status of the instance

if estat='SUSPEND' then
dbms_output.put_line(1); -- resultado:=1 --> send a mail
execute immediate 'alter system RESUME'; --> resume the instance
/* Loop to take to "end backup" all the tablespaces with another status */
open c1;
loop
exit when c1%notfound;
fetch c1 into fc1;
c:='alter tablespace '||fc1.tablespace_name||' online';
a:=dbms_sql.open_cursor;
dbms_sql.parse(a,c,dbms_sql.native);
b:=dbms_sql.execute(a);
dbms_sql.close_cursor(a);
end loop;
close c1;
else dbms_output.put_line(0);
end if;
end;
/
exit
EOF`

echo $resultado
if [ $resultado -gt 0 ]; then
# If the result i other than 0, send a mail to inform and suggest to check the actual status
echo "Instance in SUSPEND state. Check its actual status, please. "|mailx -s "Oracle AE05 recovered from a SUSPEND state" explo.sistemas@empresa.com
else
echo "Instance in OPEN state. "|mailx -s "Oracle AE05 OK" toni.blanco@empresa.com
fi

exit

Checking backups

Generation of a listing with all the filesystems that forms an instance. It's useful to send to the backup administrator, for instance.

We configure BACKUPS.BAT to connect to all the instances we administer. It uses backups.sql that groups and order the directories, and generates an ordered listing of instances with the directories that contains some datafiles or configurations files.

-- BACKUPS.BAT
@echo off
sqlplus -S system/****@APP05 @c:\backups.sql >>c:\backups.out
sqlplus -S system/****@APP01-10 @c:\backups.sql >>c:\backups.out
....

-- backups.sql

set pagesize 0

col instancia format a10

set serveroutput on feed off
declare
ins varchar2(20);
ver varchar2(120);
begin
select UPPER(instance) into ins from v$thread where rownum=1;
select banner into ver from gv$version where banner like '%Edition%' and rownum=1; dbms_output.put_line(rpad(ins,8,' ')' - 'ver);
end;
/

select HOST_NAME HOST from v$instance;
select distinct substr(file_name, 1, instr(file_name,'/',-1)) from dba_data_files
union
select distinct substr(name, 1, instr(name,'/',-1)) from v$controlfile
union
select distinct substr(member, 1, instr(member,'/',-1)) from v$logfile
union
select distinct substr(file_name, 1, instr(file_name,'/',-1)) from dba_temp_files union
select distinct substr(value, 1, instr(value,'/',-1)) from v$parameterwhere name like 'log_archive_dest%' and value is not null and value <>'enable'order by 1;
select rpad('-',80,'-') from dual;
exit



-- Backups.out

APP05 - Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
hostname1
/global/aecobde5/u01/oradata/ae06/
/global/aecobde5/u02/oradata/ae06/
/global/aecobde5/u03/oradata/ae06/
/global/aecobde5/u04/oradata/ae06/
/global/aecobde5/u05/oradata/ae06/arch/
/global/aecobde5/u06/oradata/ae06/
/global/aecobde5/u07/oradata/ae06/
/global/aecobde5/u08/oradata/ae06/
/global/aecobde5/u09/oradata/ae06/
/global/aecobde5/u11/oradata/ae06/
--------------------------------------------------------------------------------
APP011 - Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
hostname2
+DATOS/app01/controlfile/
+DATOS/app01/datafile/
+DATOS/app01/onlinelog/
+DATOS/app01/tempfile/
+FLASHDB/app01/controlfile/
+FLASHDB/app01/onlinelog/
--------------------------------------------------------------------------------

Comparing several schemas with dbms_space.object_space_usage

This is useful, for example, if we import or refresh a schema to another instance.
In this example we'll compare schemas DWH03 and PRUEBA03.

set serveroutput on

DECLARE
CURSOR C IS select OWNER, SEGMENT_NAME, SEGMENT_TYPE,100, PARTITION_NAME
from dba_segments
where (OWNER LIKE 'PRUEBA03' OR OWNER LIKE 'DWH03')
and segment_name not like 'BIN%'
AND PARTITION_NAME IS NULL
and SEGMENT_TYPE in ('TABLE', 'INDEX', 'CLUSTER')
group by owner, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME
order by owner, SEGMENT_TYPE, SEGMENT_NAME;

F C%rowtype;
SPACE_USED NUMBER;
SPACE_ALLOCATED NUMBER;
TOTAL1 NUMBER;TOTAL2 NUMBER;

BEGIN
DBMS_OUTPUT.ENABLE(999999999);
open c;
fetch c into f;
TOTAL1:=0; TOTAL2:=0;
LOOP
EXIT WHEN C%NOTFOUND;
dbms_space.object_space_usage(F.OWNER, F.SEGMENT_NAME,
F.SEGMENT_TYPE,100, F.PARTITION_NAME,SPACE_USED,
SPACE_ALLOCATED);
if F.OWNER='DWH03' THEN TOTAL1:=TOTAL1+SPACE_USED;
elsif F.OWNER='PRUEBA03' THEN TOTAL2:=TOTAL2+SPACE_USED;
end if;
dbms_output.put_line
(F.OWNER','F.SEGMENT_TYPE','F.SEGMENT_NAME','F.PARTITION_NAME','SPACE_USED','SPACE_ALLOCATED);
fetch c into f;
END LOOP;
close c;
dbms_output.put_line('SUMA DE DWH03 = 'TOTAL1);
dbms_output.put_line('SUMA DE PRUEBA03 = 'TOTAL2);
END;
/