Para restaurar alguns arquivos de log arquivados – archives de um backup:
RUN
{
SET ARCHIVELOG DESTINATION TO ‘/u03/archive/’;
RESTORE ARCHIVELOG FROM SEQUENCE 129419 UNTIL SEQUENCE 129618;
}
EXIT;
Para restaurar alguns arquivos de log arquivados – archives de um backup:
RUN
{
SET ARCHIVELOG DESTINATION TO ‘/u03/archive/’;
RESTORE ARCHIVELOG FROM SEQUENCE 129419 UNTIL SEQUENCE 129618;
}
EXIT;
Para criar o link primeiro configure o seu tnsnames.ora (do servidor do banco onde vc quer criar o vinculo)
abra o tnsnames.ora e inclua o apelido da conexao:
BANCOREMOTO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.12)(PORT = 1521))
)
(CONNECT_DATA =
(SID = bdo)
)
)
sqlplus / as sysdba
sql> CREATE public DATABASE LINK PRODUC CONNECT TO scott IDENTIFIED BY tiger using ‘BANCOREMOTO’;
sql> SELECT * FROM v$instance@produc;
RMAN> RUN {
2> SET NEWNAME FOR DATAFILE ‘/u02/oracle/banco/logix/dados07.dbf’ TO ‘/u03/oracle/banco/logix/dados07.dbf’;
SET NEWNAME FOR DATAFILE ‘/u02/oracle/banco/logix/tbsindxl01.dbf’ TO ‘/u03/oracle/banco/logix/tbsindxl01.dbf’;
restore DATAFILE 32;
restore DATAFILE 35;
SWITCH DATAFILE ALL;
}
3> 4> 5> 6> 7>
executing command: SET NEWNAME
using target database controlfile instead of recovery catalog
executing command: SET NEWNAME
Starting restore at 04-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00032 to /u03/oracle/banco/logix/dados07.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/backup/bkp_LOGIX_15973_1_20100203.bkp tag=FULLBACKUP params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 04-FEB-10
Starting restore at 04-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00035 to /u03/oracle/banco/logix/tbsindxl01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/backup/bkp_LOGIX_15976_1_20100203.bkp tag=FULLBACKUP params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 04-FEB-10
datafile 32 switched to datafile copy
input datafilecopy recid=4668 stamp=710093659 filename=/u03/oracle/banco/logix/dados07.dbf
datafile 35 switched to datafile copy
input datafilecopy recid=4669 stamp=710093659 filename=/u03/oracle/banco/logix/tbsindxl01.dbf
RMAN>
Tabelas muito grandes sao candidatas a ficarem em tablespaces separadas, isso se sao fruto de full table scan, principalmente. Seus segmentos serao criados todos em uma unica tablespace. Melhora signficativa em performance.
SELECT a.owner, a.tablespace_name, a.segment_name, b.num_rows,
round(sum(a.bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments a, dba_tables b
WHERE — a.owner = ‘LOGIX’ AND
a.segment_type = ‘TABLE’
AND a.segment_name = b.table_name
AND a.owner = b.owner
AND a.tablespace_name = b.tablespace_name
–AND segment_name like ‘%LOTE%’
GROUP BY a.owner, a.tablespace_name, a.segment_name, b.num_rows
ORDER BY round(sum(a.bytes/1024/1024),2) DESC
O script acima podemos gerar a saida para criar os comandos para alterar os objetos de tablespace e alterarmos a tablespace da qual se encontram. Melhor estarem em tablespaces separadas.
— Maiores tabelas do banco – tamanho e registros
– Tamanho de Cada Tabela
SELECT owner, tablespace_name, segment_name,
round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
WHERE owner = ‘SYSTEM’
AND segment_type = ‘TABLE’
– AND segment_name like ‘CLIENTE%’
GROUP BY owner, tablespace_name, segment_name
– Tamanho das Tabelas Por Usuário
SELECT owner, round(sum(bytes/1024/1024),2) as Tamanho_MB
FROM dba_segments
GROUP BY owner
– Tamanho Total das Tabelas
SELECT round(sum(bytes/1024/1024),2) as Tamanho_MB
FROM dba_segments
– % de Uso das TableSpaces
SELECT a.TABLESPACE_NAME “TableSpace Name”,
round(a.BYTES/1024/1024) “MB Allocated”,
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) “MB Used”,
nvl(round(b.BYTES / 1024 / 1024), 0) “MB Free”,
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) “Pct Used”,
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) “Pct Free”
FROM (SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME) a,
(SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM sys.dba_free_space
GROUP BY TABLESPACE_NAME) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES);
Continuando con nuestro Programa Mejores Instructores LAD, queremos comunicarles los resultados obtenidos en Q2-FY10. Recuerden que este programa hace reconocimiento a aquellos instructores que han obtenido un alto desempeño en todos los cursos brindados durante el trimestre y que cumplen las siguientes condiciones:
Requisitos
Pais
|
Instructor
|
Partner
|
Pais
|
Instructor
|
Partner
|
|
MX
|
PERERA CARLOS-ABRAHAM
|
Compueducación México
|
MX
|
MARTINEZ OMAR ORLANDO
|
Compueducacion Monterrey
|
|
Vzla
|
ASCANIO EDUARDO JOSE
|
EDA Venezuela
|
MX
|
Morales Javier
|
Compueducación México
|
|
CH
|
BURAGLIA CAMILO ANTONIO
|
In Motion
|
MX
|
RAMIREZ GALDI CARLOS
|
Independiente
|
|
CO
|
CABRERA GUILLERMO
|
BusinessMind
|
MX
|
Rivera Madrid Hector
|
Independiente
|
|
CO
|
CUBIDES GUILLERMO
|
EDA Colombia
|
CO
|
Robayo Rodriguez Jasmin
|
GTS
|
|
BR
|
Rocha Andre-Luiz-O
|
Techmax
|
BR
|
Rocha Andre-Luiz-O
|
Techmax
|
|
Vzla
|
Gonzalez Claudia
|
EDA Venezuela
|
CO
|
RODRIGUEZ WILMAR
|
externo
|
|
BR
|
Gonzalez Macedo Daniela
|
Education Tecnologia da
Informacao LTDA
|
MX
|
RUIZ MA.DEL CARMEN
|
EDA México
|
|
MX
|
Hernandez Hugo
|
SCAP
|
MX
|
SANCHEZ MAURICIO
|
Independiente
|
|
MX
|
Lara-Morales Claudia
|
Independiente
|
MX
|
Sarabia Ortiz, Victor
|
Independiente
|
|
MX
|
LAZARINI ULISES
|
Independiente
|
CO
|
Troya-Toscano Jose
|
BusinessMind
|
|
CH
|
Leyton, Sebastian
|
In Motion
|
ARG
|
VALDEZ GONZALO
|
Vault Consulting
|
|
BR
|
Lovato Wallace
|
Quasar Consultoria e Sistemas
|
MX
|
Villanueva Jose-Alfredo
|
Compueducación México
|
|
Vzla
|
Yanes Carlos-Eduardo
|
EDA Venezuela
|
MX
|
Cesar Hiraldo
|
Integrasoft
|
|
MX
|
Rodolfo Rosales
|
EDA México
|
|
Pa
is |
Instructor
|
Partner
|
#Cursos
|
#Eval
|
Promedio
Delivery |
Promedio
Total Eval |
MX
|
Sarabia- Ortiz, Victor
|
Compueducación Mexico
|
11
|
11
|
98,95
|
99,39
|
MX
|
RAMIREZ GALDI, CARLOS
|
Independiente
|
6
|
6
|
98.16
|
98,86
|
MX
|
Mauricio Sanchez
|
Independiente
|
8
|
8
|
98,07
|
98,36
|
VE
|
Gonzalez, Claudia
|
EDA Venezuela
|
10
|
10
|
97,66
|
97,84
|
MX
|
LAZARINI-CASTANEDA,ULISES
|
Independiente
|
5
|
5
|
96,83
|
97,70
|
MX
|
Villanueva, Jose-Alfredo
|
Compueducación Mexico
|
10
|
10
|
97,53
|
97,02
|
CO
|
Troya-Toscano, Jose
|
BusinessMind
|
5
|
5
|
95,20
|
97.18
|
VE
|
Yanes-Veracierta, Carlos-Eduardo
|
EDA Venezuela
|
10
|
10
|
96,62
|
96,88
|
BR
|
Rocha, Andre-Luiz-O
|
Casa de Software S.A
|
9
|
9
|
95,94
|
96,86
|
MX
|
Lara-Morales, Claudia
|
Independiente
|
10
|
10
|
94,02
|
96,46
|
AR
|
VALDEZ, GONZALO
|
Vault Consulting
|
5
|
5
|
90,76
|
95,22
|
MX
|
Cesar Hiraldo
|
Integrasoft
|
15
|
15
|
97.06
|
97.35
|
MX
|
Rodolfo Rosales
|
EDA México
|
6
|
6
|
96.08
|
97.16
|
Invitamos a todos los instructores a ser parte de esta distinción, mejorando cada día los servicios y velando por completar las evaluaciones de los cursos.
Com os scripts abaixo conseguimos reduzir a marca dagua movendo as tabelas que estao
no final dos datafiles. O que fazemos é sempre repetir o script de move table, e reindexando…
Os scripts abaixo geram novos scripts que devem ser rodados..
########################################
Verificar o tamanho do bloco do banco
########################################
set pagesize 1000
set head off
column value new_val blksize
select value
from v$parameter
where name = ‘db_block_size’
/
##############################################################
Usei o tamanho do bloco de 4096 conforme na consulta abaixo.
Essa consulta ela simplesmente lista quais sao os datafiles
que podem sofrer encolhimento, e gera o comando para ja
reduzir o tamanho conforme a marca dagua.
Essa consulta deve ser repetida toda vez que voce rodar a
consulta que gera o alter table, ou seja movendo a tabela
que esta na marca dagua podemos entao encolher ainda mais
os datafiles. Lembrando que se mover a tabela de lugar
devemos reindexar os indices com status UNUSABLE.
##############################################################
select ‘alter database datafile ”’ ||
file_name || ”’ resize ‘ ||
ceil( (nvl(hwm,1)*4096)/1024/1024 )
|| ‘m;’ cmd
from dba_data_files a,
( select file_id,
max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and
ceil(blocks*4096/1024/1024)-
ceil((nvl(hwm,1)*
4096)/1024/1024 ) > 0
/
##############################################################
Cria o script para fazer o alter table das tabelas que estao
na marca dagua permitindo o encolhimento do datafile,
lembrando que temos que sempre trocar os datafiles
para tentar promover o encolhimento o primeiro que estou
investigando é o ‘/u01/banco/dados01.dbf’
##############################################################
select * from
( select’alter table ‘||owner||’.’||segment_name||’ move tablespace ‘||TABLESPACE_NAME||’;’ dados
from dba_extents
where segment_type = ‘TABLE’
and file_id = ( select file_id from dba_data_files
where file_name = ‘/u01/logix10/dados02.dbf’ )
order by block_id desc )
where rownum <=25;
##############################################################
Cria o script para fazer o rebuild dos indices UNUSABLE
##############################################################
select ‘alter index ‘||owner ||’.’||index_name||’ rebuild tablespace ‘||TABLESPACE_NAME||’;’
from dba_indexes
where status = ‘UNUSABLE’;
Acho interessante criar uma tabela para acessar o arquivo alert, assim nao e necessario abrir o arquivo de alerta para se verificar o conteudo das linhas.
Outro detalhe e que se pode criar rotinas para percorrer o arquivo de alerta
para verificar seu conteudo. Primeiro passo e criar um diretorio:
Create directory BDUMP as ‘c:oracle9iadminprodbdump’;
Create directory BDUMP as ‘c:oracle9iadminprodbdump’;
Create table alert_log (text varchar2(200))
Organization EXTERNAL (
Type oracle_loader
Default directory BDUMP
Access parameters
(
Records delimited by newline
Badfile ‘rejeitado.bad’
Logfile ‘logs.log’
Fields terminated by ‘
)
Location (‘diralert.log’)
)
Reject limit unlimited;
#.bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
umask 022
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
export PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID
Quem nao se lembra do doskey… hoje é so voltar a setinha do teclado para cima e o ultimo comando se repete.. No linux isso tambem é possivel.
Instale o rlwrap. E facil. Descompacte,
gunzip NOMEARQUIVO.gz e tar -xvf NOMEARQUIVO.gz ou tar -xvfg NOMEARQUIVO.gz
Em seguida como root:
cd rlwrap-0.33
./configure
make
make install
Depois va ate a pasta:
cd /etc/profile.d/
crie o arquivo sqlplus.sh com o conteudo:
alias sqlplus=’rlwrap sqlplus’
Sobre o RLWRAP pode ser baixado do link abaixo:
rlwrap is a ‘readline wrapper’ that uses the GNU readline library to allow the editing of keyboard input for any other command. Input history is remembered across invocations, separately for each command; history completion and search work as in bash and completion word lists can be specified on the command line.
http://utopia.knoware.nl/%7Ehlub/uck/rlwrap/
Pode ser baixado pelo link:
http://utopia.knoware.nl/%7Ehlub/uck/rlwrap/rlwrap-0.33.tar.gz
Para se ter um menu interativo crie um arquivo com o conteudo abaixo.
gedit menu.sh e depois que estiver criado e com o conteudo, execute o comando chmod +x menu.sh e execute o menu com ./menu.sh
#!/bin/bash
# Menu Shell
menu ()
{
a=”ok”
while true $a !=”ok”
do
clear
echo “#################################################################”
echo “################### MENU PRINCIPAL ###################”
echo “#################################################################”
echo “”
echo “”
echo ” 1 – Iniciar banco de dados – treina”
echo ” 2 – Interromper banco de dados – treina”
echo ” 3 – Entrar no sqlplus como DBA – modo interativo “
echo ” 4 – Entrar no rman – modo interativo”
echo ” R – Reboot “
echo “”
echo “”
echo “”
echo “#################################################################”
echo ” OBS. pressione ‘ctrl+c’ p/ sair sem reiniciar o Linux”
echo “#################################################################”
echo “”
echo ” Digite a opção desejada: “
echo “”
echo “#################################################################”
read a
case $a in
R) clear; echo “FINALIZADO O SCRIPT REINICIANDO O SERVIDOR”;
sleep 2; reboot;
sleep 2; clear; break;;
1) sh inicia.sh;;
2) sh para.sh;;
3) sqlplus / as sysdba;;
4) rman target / ;;
R)reboot;;
esac
done
}
menu