Arquivo mensais:março 2008

Criar uma rotina bkp oracle

Publicado em por
Responder

SQL> conn / as sysdba
Conectado.
SQL> shutdown immediate;
Banco de dados fechado.
Banco de dados desmontado.
InstΓncia ORACLE desativada.
SQL> startup mount;
InstΓncia ORACLE iniciada.

Total System Global Area 494927872 bytes
Fixed Size 1288240 bytes
Variable Size 180357072 bytes
Database Buffers 310378496 bytes
Redo Buffers 2904064 bytes
Banco de dados montado.
SQL> alter database archivelog;

Banco de dados alterado.

SQL> alter database open;

Banco de dados alterado.

SQL> archive log list;
Modo log de banco de dados Modo de Arquivamento
Arquivamento automßtico Ativado
Destino de arquivamento USE_DB_RECOVERY_FILE_DEST
A seqⁿΩncia de log on-line mais antiga 2179
Pr≤xima seqⁿΩncia de log a arquivar 2180
SeqⁿΩncia de log atual 2180
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string C:oraclexeapporacleflash_r
ecovery_area
db_recovery_file_dest_size big integer 10G
SQL>

Conteudo do arquivo bkdiariorman.rc
run {
backup database plus archivelog delete all input;
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt expired backup;
}

Conteudo do arquivo BAT
rman target / @C:bkpORACLEbkdiariorman.rc

Configurar o RMAN

C:Documents and SettingsAdministrator>rman target /

Gerenciador de Recuperaτπo: Release 10.2.0.1.0 – Production on Sßb Mar 15 16:07:
45 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

conectado ao banco de dados de destino: XE (DBID=2477763744)

RMAN> quit

C:Documents and SettingsAdministrator>rman target /

Gerenciador de Recuperaτπo: Release 10.2.0.1.0 – Production on Sßb Mar 15 16:11:
43 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

conectado ao banco de dados de destino: XE (DBID=2477763744)

RMAN> show all;

usar o arquivo de controle do banco de dados de destino em vez do catßlogo de re
cuperaτπo
os parΓmetros de configuraτπo RMAN sπo:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘C:ORACLEXEAPPORACLEPRODUCT10.2.0SE
RVERDATABASESNCFXE.ORA’; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

novos parΓmetros de configuraτπo RMAN:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
os novos parΓmetros de configuraτπo RMAN foram armazenados com sucesso

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

novos parΓmetros de configuraτπo RMAN:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
os novos parΓmetros de configuraτπo RMAN foram armazenados com sucesso

RMAN>

Recompilar todos objetos invalidos de todos esquemas

Publicado em por
Responder

Use sempre a $ORACLE_HOME/rdbms/admin/ultrp.sql

sqlplus / as sysdba

sql>@$ORACLE_HOME/rdbms/admin/ultrp.sql;

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> @C:oraclexeapporacleproduct10.2.0serverRDBMSADMINutlrp.sql

TIMESTAMP
—————————————————————————–

COMP_TIMESTAMP UTLRP_BGN 2008-03-15 16:01:59

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>#

Procedimento PL/SQL concluφdo com sucesso.

TIMESTAMP
—————————————————————————–

COMP_TIMESTAMP UTLRP_END 2008-03-15 16:02:05

Procedimento PL/SQL concluφdo com sucesso.

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
——————-
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
—————————
0

Procedimento PL/SQL concluφdo com sucesso.

AdvertΩncia: VocΩ nπo estß mais conectado ao ORACLE.
SQL>
Conectado.
SQL> select count(*) from user_objects;

COUNT(*)
———-
195

SQL> select count(*) from user_objects where status = ‘INVALID’;

COUNT(*)
———-
0

SQL> select count(*) from user_objects where status = ‘INVALID’;

COUNT(*)
———-
0

SQL>

retençao no AWR

Publicado em por
Responder

Para reduzir a retenção do AWR

EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>1107, high_snap_id=>1108);
— Reduzir a retencao para dois dias.

exec dbms_workload_repository.modify_snapshot_settings(retention => 2880, interval => 60, topnsql => ‘DEFAULT’);

— Verificar o status da retencao atual do AWR

select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) “Snapshot Interval”,
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) “Retention Interval”
from dba_hist_wr_control;

retention = snapshot interval (1 hr ) X 24 X X 7 days
For each week it’s 10080

– Retain data for 21 days ( 3 weeks )
exec dbms_workload_repository.modify_snapshot_settings(retention => 30240, interval => 60, topnsql => ‘DEFAULT’);

– Retain data for 28 days (4 weeks

SQL> desc dba_hist_snapshot;
Nome Nulo? Tipo
—————————————– ——– —————————-

SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)

SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER

SQL>

Drop AWR snapshots in range

declare
v_max number;
v_min number;
begin

select max(snaP_id) ,min(snap_id)
into v_max, v_min
from dba_hist_snapshot;

dbms_workload_repository.drop_snapshot_range (low_snap_id=>v_min, high_snap_id=> v_max);
end;

MAX(SNAP_ID) MIN(SNAP_ID)
———— ————
14360 14176

Drop AWR snapshots in range

EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>14176, high_snap_id=>14360);

Criar coleta de estatisticas no 10G

Publicado em por
Responder

create procedure ESTATISTICA
is
begin
— Usuarios que serao contemplados.
DBMS_STATS.gather_schema_stats(‘ADMUSER’);
DBMS_STATS.gather_schema_stats(‘PRIVUSER’);
DBMS_STATS.gather_schema_stats(‘TSMSYS’);
end;
/
— Criar o JOB

DECLARE
job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(job,’begin ESTATISTICA; END;’,sysdate,’sysdate+24′);
END;
/

FLUSH na SGA

Publicado em por
Responder

Quando a area de buffer cache, log buffer e shared pool esta cheia ou,
precisamos dar uma “limpada” nestas areas para que o analise do performance seja feito, precisamos então executar o comando alter system flush.
Com a consulta abaixo você consegue enxergar quais sao os objetos atualmente no buffer cache.

SELECT o.owner,
o.object_type,
substr(o.object_name,1,10)
objname,
b.objd,
b.status,
count(b.objd)
FROM v$bh b, dba_objects o
WHERE b.objd = o.data_object_id
AND o.owner not in (‘SYS’,’SYSTEM’,’SYSMAN’)
GROUP BY o.owner,
o.object_type,
o.object_name,
b.objd,
b.status;

Se caso, alguns objetos persistem em ficar na memória, iremos entao fazer a limpeza dessa area:

SQL> alter system flush buffer_pool;

Com o bloco abaixo conseguimos verificar se o pool atingiu mais de 70% e então se caso necessario podemos acionar a limpeza..

CREATE OR REPLACE VIEW sys.sql_summary AS SELECT
username,
sharable_mem,
persistent_mem,
runtime_mem
FROM sys.v_$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id;

BEGIN

CURSOR get_share IS
SELECT SUM(sharable_mem)
FROM sys.sql_summary;

CURSOR get_var IS
SELECT value
FROM v$sga
WHERE name like ‘Var%’;

CURSOR get_time is
SELECT SYSDATE
FROM dual;

todays_date DATE;
mem_ratio NUMBER;
share_mem NUMBER;
variable_mem NUMBER;
cur INTEGER;
sql_com VARCHAR2(60);
row_proc NUMBER;

BEGIN

OPEN get_share;
OPEN get_var;

FETCH get_share INTO share_mem;
DBMS_OUTPUT.PUT_LINE(‘share_mem: ‘||to_char(share_mem));

FETCH get_var INTO variable_mem;
DBMS_OUTPUT.PUT_LINE(‘variable_mem: ‘||to_char(variable_mem));

mem_ratio:=share_mem/variable_mem;
DBMS_OUTPUT.PUT_LINE(‘mem_ratio: ‘||to_char(mem_ratio));

IF (mem_ratio>0.3) THEN
DBMS_OUTPUT.PUT_LINE (‘Flushing Shared Pool … Limpeza a vista’);
cur:=DBMS_SQL.open_cursor;
sql_com:=’ALTER SYSTEM FLUSH SHARED_POOL’;
DBMS_SQL.PARSE(cur,sql_com,dbms_sql.v7);
row_proc:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END IF;
END;
/

Inserir linha nula para verificar o comportamento das funções de agrupamento no Oracle

Publicado em por
Responder

Como todos sabem, as funções de agrupamento não levam em consideração os atributos que estão com valor NULL nas linhas. Assim fizemos um teste para comprovar a atuação do count(*). O resultado era esperado que quando utilizassemos a função de agrupamento count(*) ele entao iria verificar que a linha estava nula e nao contabilizasse essa linha. Mas o Oracle armazena duas pseudo colunas que são: ROWID e ROWNUM, e quando executo count(*) estas colunas sao contempladas para efeito de calculo e o retorno de total de linhas inseridas com valor nulo é retornado.

SQL> create table TOTO (CODIGO NUMBER);

Tabela criada.

SQL> insert into TOTO VALUES(NULL);

1 linha criada.

SQL> /

1 linha criada.

SQL> /

1 linha criada.

SQL> /

1 linha criada.

SQL> /

1 linha criada.

SQL> select * from toto;

CODIGO
———-

SQL> select count(*) from toto;

COUNT(*)
———-
5

SQL>
SQL> select count(codigo) from toto;

COUNT(CODIGO)
————-
0

SQL> select rowid, rownum, codigo from toto;

ROWID ROWNUM CODIGO
—————— ———- ———-
AAAMrXAABAAAOpaAAA 1
AAAMrXAABAAAOpaAAB 2
AAAMrXAABAAAOpaAAC 3
AAAMrXAABAAAOpaAAD 4
AAAMrXAABAAAOpaAAE 5

SQL>

Time out no ISQLPLUS

Publicado em por
Responder

Para aumentar o timeout do isqplus faça a seguinte alteracao:

Va ate o arquivo:

C:oracleproduct10.2.0db_1oc4jj2eeoc4j_applicationsapplicationsisqlplusisqlplusWEB-INF

abra o arquivo: web.xml

encontre a entrada abaixo:

15

aumente o valor de 15 minutos para um tempo maior.
O tempo pode ser aumentado de 1 to 1440 minutos.

Reinicie o isqplus com o comando no prompt do dos ou linux:

isqplusctl stop
isqplusctl start

Instalacao do ASM

Publicado em por
Responder

Tivemos um problema recente no red hat 4 ES, mas que foi resolvido com o script

Documentacao
– Sobre o ASM – Download
http://www.oracle.com/technology/tech/linux/asmlib/index.html
– Instalacao
http://www.oracle.com/technology/tech/linux/asmlib/install.html

Com o script abaixo conseguimos prosseguir com a instalacao do ASM, lembrando que esta somente criando arquivos fisicos com o proposito de laboratorio do curso WS2.

# http://oss.oracle.com/projects/oracleasm/dist/documentation/asm-install.txt# /etc/init.d/oracleasm configure
# Copie o script para /home/oracle/script.sh
# Para rodar o script: sh /home/oracle/script.sh

service rawdevices stop

rm -rf /dev/raw

cd /dev

MAKEDEV raw

rm -rf /u01/asmdisks/*
cd /u01/asmdisks

dd if=/dev/zero of=dd if=/dev/zero of=asm_disk2 bs=1024k count=400
dd if=/dev/zero of=asm_disk3 bs=1024k count=400
dd if=/dev/zero of=asm_disk4 bs=1024k count=400
dd if=/dev/zero of=asm_disk5 bs=1024k count=400

chmod 777 asm_disk*

losetup -d /dev/loop1
losetup -d /dev/loop2
losetup -d /dev/loop3
losetup -d /dev/loop4
losetup -d /dev/loop5

losetup /dev/loop1 asm_disk1
losetup /dev/loop2 asm_disk2
losetup /dev/loop3 asm_disk3
losetup /dev/loop4 asm_disk4
losetup /dev/loop5 asm_disk5

echo “/dev/raw/raw1 /dev/loop1” >> /etc/sysconfig/rawdevices
echo “/dev/raw/raw2 /dev/loop2” >> /etc/sysconfig/rawdevices
echo “/dev/raw/raw3 /dev/loop3” >> /etc/sysconfig/rawdevices
echo “/dev/raw/raw4 /dev/loop4” >> /etc/sysconfig/rawdevices
echo “/dev/raw/raw5 /dev/loop5” >> /etc/sysconfig/rawdevices

chown oracle:oinstall /dev/raw/raw[1-5]
# chown root:oinstall /dev/raw/raw[1-5]
chmod 660 /dev/raw/raw[1-5]
chown root:oinstall /dev/loop[1-5]
chmod 660 /dev/loop[1-5]

service rawdevices restart
./localconfig reset
./localconfig delete
./localconfig add
/etc/init.d/oracleasm restart

asmtool -C /u01/asmdisks/asm_disk1 -n disco1 -s /dev/loop1 -l /dev/oracleasm/asmtool -C /u01/asmdisks/asm_disk2 -n disco2 -s /dev/loop2 -l /dev/oracleasm/asmtool -C /u01/asmdisks/asm_disk3 -n disco3 -s /dev/loop3 -l /dev/oracleasm/asmtool -C /u01/asmdisks/asm_disk4 -n disco4 -s /dev/loop4 -l /dev/oracleasm/asmtool -C /u01/asmdisks/asm_disk5 -n disco5 -s /dev/loop5 -l /dev/oracleasm/ls /dev/oracleasm/disks

### alterar na mao o arquivo
# /etc/udev/permissions.d/50-udev.permissions
# raw devices
# raw/*:oracle:oinstall:0660

raw -qa

chmod 777 /dev/raw/*
chown oracle.dba /dev/raw/*
chown oracle.dba /u01/asmdisks/*

cd /home/oracle/oracle/product/10.2.0/db_1/bin

###########################################################################################

Ou RODAR O SCRIPT ABAIXO:
Crie um arquivo e deixe-o no arquivo:

/etc/init.d/S99asdisk

Conteudo do arquivo:

#!/bin/bash
#set -xv

export DESTASMDISKS=”/u01/asmdisks”
export ASMDISKNAME=”asm_disk”
export LO0P=”/dev/loop”
export RAW=”/dev/raw/raw”
export ORACLE_HOME=”/home/oracle”
chowns(){
chown oracle:oinstall -R $1
}
mkdir -p $DESTASMDISKS
for i in 1 2 3 4 5
do
dd if=/dev/zero of=${DESTASMDISKS}/$ASMDISKNAME${i} bs=1024k count=400
sleep 2
chmod 777 ${DESTASMDISKS}/$ASMDISKNAME${i}
sleep 2
losetup /dev/loop${i} ${DESTASMDISKS}/$ASMDISKNAME${i}
sleep 2
raw $RAW${i} /dev/loop${i}
sleep 2
chowns $RAW${i}
sleep 2
done

[ ! -d /stage/OSB ] && mkdir -p /stage/OSB
chowns /stage/OSB
chowns $DESTASMDISKS
chmod 777 /dev/raw/raw*
chmod 777 /u01/asmdisks/*

mv labs solutions $ORACLE_HOME
mv bash_profile-oracle $ORACLE_HOME/.bash_profile

chowns $ORACLE_HOME/labs
chowns $ORACLE_HOME/solutions
chowns $ORACLE_HOME/.bash_profile

set +xv

Recriar os indices fragmentados

Publicado em por
Responder

Recriacao dos indices é uma tarefa constante do DBA, se os indices estao fragmentados, pode ser penoso ao banco de dados, percorrer os indices para que eles possam informar a localizacao da informacao da qual buscamos.
Entao, outro dia precisei de recriar os indices que estavam fragmentados. Entao porque nao, criar um script para que o proprio banco, no domingo a noite, uma vez por semana fizesse isso?

Assim, o script abaixo foi transformado em uma procedure da qual, criei um job para rodar no domingo.

########################## INICIO PROCEDURE

CREATE OR REPLACE PROCEDURE “SYS”.”PROC_CRIAINDICEFRAG” is
begin
/* Sao dois blocos identicos, para garantir que os indices foram recriados */
FOR cur_rec IN
(SELECT ‘alter index ‘||table_owner||’.’||index_name||’ rebuild tablespace ‘|| tablespace_name||’ ‘ myddl
FROM DBA_indexes
WHERE blevel >=4
— and ROWNUM <=1
) loop
execute immediate cur_rec.myddl;
/*
if l_status != ‘OK’ then
dbms_output.put_line(‘error’);
end if;
*/
END LOOP;

FOR cur_rec IN
(SELECT ‘alter index ‘||table_owner||’.’||index_name||’ rebuild tablespace ‘|| tablespace_name||’ ‘ myddl
FROM DBA_indexes
WHERE blevel >=4
— and ROWNUM <=1
) loop
execute immediate cur_rec.myddl;
/*
if l_status != ‘OK’ then
dbms_output.put_line(‘error’);
end if;
*/
END LOOP;

end;

################################################# FIM PROCEDURE
— Bloco de chamada dentro do JOB

begin
SYS.PROC_CRIAINDICEFRAG;
end;