Menu de navegação

[bbarrosoParticipante]

Prezados,

Aqui na empresa tenho uma rotina de backup do export e hot diaários, só que quando vou realizar o backup hot ele faz de uma instância normalmente e a outra ele só copia o listener.ora, spfilepmedprod.ora, tnsnames.ora, verifiquei no alert para ver se tinha algum erro e encontrei o erro ORA-03136: inbound connection timed out.

Esse erro pode ser a causa de o backup hot não está sendo feito corretamente e como poderia resolver?

Bruno Soares

[vieriParticipante]

Este erro nada tem haver com o seu backup hot não está sendo feito.

Esse erro é um warning que tem relação ao listener e processos clientes.

Poste seu script de bkp para entedermos porque ele não está copiando os datafiles.

[ramasineParticipante]

Bruno,

Da uma olhada nas características deste erro!

ORA-03136: inbound connection timed out

Cause: Inbound connection was timed out by the server because user authentication was not completed within the given time specified by SQLNET.INBOUND_CONNECT_TIMEOUT or its default value

Action: 1) Check SQLNET and RDBMS log for trace of suspicious connections. 2) Configure SQLNET with a proper inbound connect timeout value if necessary.

Qual a versão do teu banco?
Tem Dataguard no ambiente? Se temm, algumas mensagens dessa são até normais, quando a conexão entre a primary BD e o Standby BD (Dataguard) se perdem, essa mensagem é exibida no alert!
Agora se for permanente a mensagem de erro, ai precisa olhar o ambiente com mais calma!

Observação:

What the error is telling you is that a connection attempt was made, but the session authentication was not provided before SQLNET.INBOUND_CONNECT_TIMEOUT seconds.

As far as adverse effects in the long run, you have a user or process that is unable to connect to the database. So someone is unhappy about the database/application.

Before setting SQLNET.INBOUND_CONNECT_TIMEOUT, verify that there is not a firewall or Network Address Translation (NAT) between the client and server. Those are common cause for ORA-3136.

Realmente não deve ter nada a ver com o teu backup!

[bbarrosoParticipante]

Segue em anexo o script de backup!!!

#! /bin/sh
#File: hot_backup.sh
#

Description: This is a script file that performs hot backups. It takes one

parameter: the database name (DBNAME). It needs to run locally on the

database server as it uses local OS calls to copy all of the required files

to a disk.

#

Environment Variables:

This script assumes it resides in the oracle user’s UNIX home directory

in a sub-directory called BACKUP

DBLIST_DIR – a temporary directory in which text files can be created

#

Author: Tim Moss

#

Date: 8th December 1995

#

Change History

#

Date Changed By Description

12/04/96 Tim Moss Implement hard ware compression

16/04/96 Tim Moss Change SQLDBA to SVRMGR due to forthcomming

1997/06 B. Beaton Adapt to QC Data, Calgary

1997/12 D. Morgan Adapt to london

1998/1 Hari.K Adapt to Calgary

1999/08 Hari.K Adapt to DLT and some clean up

2002/06 D. Morgan Adapt for Anadarko

2002/11 D. Morgan Upgraded, single file now

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

Tests to check the number of arguments

#
alias compress=’gzip’
echo a
if [ X$1 != X ]; then
DBNAME=$1
export DBNAME
else
echo “The database name must be passed as a parameter”
exit
fi

The database must be up for this to work

#
ERRCHK=ps -fu oracle | grep $DBNAME |grep -c ora_
if [ “$ERRCHK” = “0” ]; then
echo “Timestamp : date :The database is not running, unable to continue”
exit
fi

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

Environment variables to change for specific sites

#

Change following line if location is different

Configure settings in $DBNAME.env file to match system

#

Set environment

echo b
. /opt/oracle/bin/${DBNAME}.env
echo c

The $DBNAME.env file contains the following variables

and exports

#

CONNECT_STRING=sys/the_password@EXAMPLE

ORACLE_HOME=/usr/oracle/product/8.1.7 # Oracle home for the instance

BACKUP_HOME=/usr/oracle/BACKUP

DBLIST_DIR=${BACKUP_HOME}/DB_DIR # directory to handle temporary files

HOTBACKUP_DIR=/data/orabk/LANDPRD/hot # directory for backup files

COMPRESS=YES # Use compress instead of cp

#export CONNECT_STRING
#export BACKUP_HOME
#export DBLIST_DIR
#export HOLD_DIR
#export ORACLE_HOME
#export HOTBACKUP_DIR
#export COMPRESS

#End contents $DBNAME.env
#

End of customized variables

##############################################################################
#Define some commands and files

SQLPLUS=”${ORACLE_HOME}/bin/sqlplus -s ${CONNECT_STRING}”
echo $SQLPLUS
TIME_STAMP=date +"%d-%b-%Y"

Test for duplicate filenames

#
$SQLPLUS <<-EOF
set feedback off
var numfiles number;
BEGIN
SELECT count(*) into :numfiles
FROM dba_data_files a, dba_data_files b
WHERE a.file_name != b.file_name
AND SUBSTR(a.file_NAME, INSTR(a.file_name, '/', -1) + 1)
= SUBSTR(b.file_NAME, INSTR(b.file_name, '/', -1) + 1);
END;
/
exit :numfiles
EOF

FILE_ERROR=$?
echo "Number of duplicate files $FILE_ERROR"

if [ $FILE_ERROR -gt 0 ]; then
echo "Aborting, … $FILE_ERROR duplicate filenames"
exit
fi

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

echo ""
echo "Timestamp : date :Starting Backup.."

echo "Timestamp : date : Switching logfile at start of backup.."
$SQLPLUS < ${DBLIST_DIR}/${DBNAME}.lis <$DBLIST_DIR/${TABLESPACE}.lis <<-EOF
set pages 0
set lines 80
set head off
set echo off
set verify off
set feedback off
define TSPACE=$TABLESPACE
column fname format a70

select ddf.file_name fname
from sys.dba_data_files ddf
where ddf.tablespace_name = upper('&&TSPACE');

exit
EOF

done

reading of ${DBNAME}.lis

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

line_count=cat $DBLIST_DIR/${DBNAME}.lis |wc -l
total_ts_count=expr $line_count

cur_ts_count=0

echo

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

Read the file, taking each tablespace off-line, backing up the file(s),

and bringing the tablespace back on line

cat $DBLIST_DIR/${DBNAME}.lis | while read TABLESPACE
do
cur_ts_count=expr $cur_ts_count + 1
echo "Timestamp : date :Backing up tablespace $TABLESPACE – [${cur_ts_count}/${total_ts_count}]"

$SQLPLUS < ${HOTBACKUP_DIR}/${BKUP_FILENAME}.gz
else
cp $DATAFILE $HOTBACKUP_DIR
fi
done # Reading of $TABLESPACE.lis

Delete the datafile

rm -f $DBLIST_DIR/${TABLESPACE}.lis

Bring the tablespace back on-line

$SQLPLUS <<-EOF
alter tablespace $TABLESPACE end backup;
exit
EOF

echo "Timestamp : date :Completed back up of tablespace $TABLESPACEn"

done
###################################################

End of tablespace backup

aka Reading of $DBNAME.lis

echo "Timestamp : date :Backup Miscellaneous files..n"

cp -p ${ORACLE_HOME}/dbs/${DBNAME}ora ${HOTBACKUP_DIR}
cp -p ${ORACLE_HOME}/network/admin/*ora ${HOTBACKUP_DIR}

$SQLPLUS < ${DBLIST_DIR}/misc_file_list.lst
set verify OFF;
set feedback OFF;
set echo OFF;
set heading OFF;
set pause OFF

alter database backup controlfile to ‘$HOTBACKUP_DIR/${DBNAME}_ctl_file.bckup’ reuse;
alter database backup controlfile to trace noresetlogs;
select name from v$controlfile;
exit;
EOF

cp cat ${DBLIST_DIR}/misc_file_list.lst $HOTBACKUP_DIR

cp grep -l "CREATE CONTROLFILE" ${ADMIN_DIR}/${DBNAME}/udump/*.* ${HOTBACKUP_DIR}

echo “Timestamp : date : Switching logfile after backup..”
$SQLPLUS <<-EOF
alter system SWITCH LOGFILE;
exit;
EOF

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

Check the contents

echo
echo "Timestamp : date :Listing the contents of the Backup Directory"
echo
ls -l $HOTBACKUP_DIR/*

echo "nTimestamp : date :Hot backup process for $DBNAME completed at date"

[ramasineParticipante]

Bruno, não tem a ver com o teu processo de backup, dá uma olhada no meu comentário anterior!

[vieriParticipante]

Como vai ser erro de TNS – inbound se ele não está usando?
Ele está usando o sqlplus local realizando conexão por loop-back
aonde vai ter timeout?

Verifique se todos os diretórios usados no bkp estão OK.

[bbarrosoParticipante]

Aê Galera,

Valeu pela ajuda, mais já consegui resolver o problema aqui.

Bruno Soares

[ramasineParticipante]

Mas e aí, qual era de fato o problema? 8)

valeu!

[bbarrosoParticipante]

Foi o diretório temp que tinha removido, com isso ele não estava conseguindo gerar os arquivos temporários, com isso recriei e voltou a funcionar normalmente.

Bruno Soares

[Autor]

Posts