Fleet Patching and Provisioning commands

Formerly “Rapid Home Provisioning (RHP)”, thats the reason for the command rhpctl.

This artefact consists of a server connected to an ACFS where clusterware and RDBMS binaries are stored for distribution and installation on client nodes.

The following is a summary of the main commands that will help us install and update the software

srvctl start rhpserver

srvctl add rhpserver -storage /// -diskgroup xxx

rhpctl import image… -imagetype -path

rhpctl add image -image abc -workingcopy abcwc

rhpctl add workingcopy -workingcopy WC1 -image iii -storagetype LOCAL -path /ora19

rhpctl upgrade gihome -sourcewc aa -destwc bb

rhpctl upgrade database

rhpctl zdtupgrade database…

rhpctl addpdb database…

rhpctl deletepdb database…

Bynaries:rhpctl add workingcopy -image db19 -path /u01…. -client xxx -oraclebase /u01.. - workingcopy w1

RDBMS:rhpctl add database -workingcopy w1 -dbname db - node a,b -dbtype RAC- datafiledestination xx_DG

rhpctl move database -sourcewc a -patchedwc a_psu

CHANGING THE COLUMNS WIDTH USING CSSCAN

After executing CSSCAN some files are generated (.err, .out) informing about the columns that will not fit in the target charset(usually ALxUTFz)and, furthermore, in the host schema CSMIG (@csminst.sql) the same information is stored, so we can use it to generate the modifications needed:

[CAT ]Després de passar el CSSCAN, com a subproducte, i a part dels fitxerets que es generen, queda la mateixa informació a l'esquema CSMIG (s'ha de crear abans de passar el CSSCAN [@csminst.sql]). Doncs aquesta info es pot utilitzar per generar les ordres d'ampliació de les columnes que no hi cabran al nou codi de caràcters.


cat prova.err|grep "Number of Exceptions"|grep -v ": 0"|wc –l

This select produces the instructions for the tables that can't fit in the actual column size:

set linesize 222
SELECT 'ALTER TABLE '||PROP||'.'||TAULA||' MODIFY '
||dba_tab_columns.COLUMN_NAME||' '||
dba_tab_columns.DATA_TYPE||'('||TAMANY||');' DDL
FROM
(select DISTINCT U.USERNAME PROP, O.OBJECT_NAME TAULA,
T.COLUMN_ID COL_ID, max(MAXSIZ) TAMANY
from CSM$COLUMNS C, dba_users U,
dba_objects O, dba_tab_columns T
where C.ERRCNT > 0 and
C.USR#=U.USER_ID and
C.OBJ#=O.OBJECT_ID and
C.COL#=T.COLUMN_ID
group by U.USERNAME, O.OBJECT_NAME, T.COLUMN_ID),
dba_tab_columns
WHERE PROP = dba_tab_columns.OWNER
AND TAULA = dba_tab_columns.TABLE_NAME
AND COL_ID = dba_tab_columns.COLUMN_ID
ORDER BY 1;

EXAMPLE OUTPUT:
ALTER TABLE DMBATCH.TAULA MODIFY NOM_PERSONA_TIT VARCHAR2(54);
....

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