Chequeo de Filsystem de ARCHIVE LOGS

#!/bin/ksh
################################################################################
# AUTHOR : MOGE ALEJANDRO #
# CREATED: 13/04/2009 #
# PROGRAM: checkarchives.sh #
# PURPOSE: Ejecuta un bkp de archive logs ante #
# determionado procentaje del filesystem #
# Database should be online. #
# USAGE : checkarchive.sh SID UMBRAL #
# PARAMETROS: #
# SID: Nombre de la instancia de base de datos #
# UMBRAL: Porcentaje máximo al cual puede llegar un filsystem. #
################################################################################

UMBRAL=${2}
FILESYSTEM=/oradata/${1}/archive
FECHA=`date +%H%M_%d%m%Y`
FECHA2=`date +%H%M %d%m%Y`
LOGFILE=/home/oracle/log/checkarchives_${1}_${FECHA}.log

SIZEFS=`bdf ${FILESYSTEM} | grep [0-9]% | awk '{print $(NF-1)}' | awk -F% '{print $1}'`
if [ ${SIZEFS} -gt ${UMBRAL} ]
then

/home/oracle/scripts/rman_backup.sh EXTTROP EMERGENCY ARCH

fi

Script completo de RMAN

Este script fue creado por Juan Vivas y proporciona un abanico de posibilidades para hacer Backup según la situación en donde nos encontremos con respecto a nuestra base de datos.


#!/sbin/sh
################################################################################
# AUTHOR : JUAN MANUEL VIVAS #
# CREATED: 23/12/2008 #
# PROGRAM: rman_weekly.sh #
# PURPOSE: Performs a LEVEL 0 + ARCHIVELOG backup. #
# Database should be online. #
# USAGE : rman_backup SID [WEEKLY|DAILY|EMERGENCY] [ARCH|NOARCH] #
################################################################################

#------------------------------------------------------------------------------#
# fn_backup_E_arch(): ARCHIVELOG BACKUP #
#------------------------------------------------------------------------------#
fn_backup_E_arch() {

rman target=/ log=${LOGFILE} append <<>
RUN {

CROSSCHECK ARCHIVELOG ALL;
BACKUP ARCHIVELOG ALL
FILESPERSET 10
TAG = 'ARC_E_$mes$dia'
FORMAT '${BKPDIR}/archivelog/arc_%d_%T_s%sp%p.%t'
DELETE ALL INPUT;
SQL 'ALTER SYSTEM SWITCH LOGFILE';

#*** Maintenance steps
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

}
EXIT;
EOF

}

#------------------------------------------------------------------------------#
# fn_backup_l0_arch(): Perform LEVEL 0 + ARCHIVELOG BACKUP #
#------------------------------------------------------------------------------#
fn_backup_l0_arch() {

rman target=/ log=${LOGFILE} append <<>
RUN {

CROSSCHECK ARCHIVELOG ALL;
#*** Weekly full database plus archivelog backup
BACKUP CHECK LOGICAL
AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL = 0
TAG = 'DBF_W_$mes$dia'
FORMAT '${BKPDIR}/backupset/dbf_%d_%T_s%sp%p.%t'
DATABASE;

#*** Weekly full archivelog backup
change archivelog all validate;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';

BACKUP CHECK LOGICAL
AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL = 0
TAG = 'ARC_W_$mes$dia'
FORMAT '${BKPDIR}/archivelog/arc_%d_%T_s%sp%p.%t'
ARCHIVELOG FROM TIME 'SYSDATE-7';

DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-10' DEVICE TYPE DISK;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7';

#*** Maintenance steps
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

}
EXIT;
EOF

}
#------------------------------------------------------------------------------#
# fn_backup_l0_noarch(): Perform LEVEL 0 + NOARCHIVELOG MODE #
#------------------------------------------------------------------------------#
fn_backup_l0_noarch() {

rman target=/ log=${LOGFILE} append <<>
RUN {
#*** shutdown database
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

#*** Weekly full database
BACKUP CHECK LOGICAL
AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL = 0
TAG = 'DBF_W_$mes$dia'
FORMAT '${BKPDIR}/backupset/dbf_%d_%T_s%sp%p.%t'
DATABASE;

#*** Maintenance steps
DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-10' DEVICE TYPE DISK;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED BACKUP;

#*** Open database
ALTER DATABASE OPEN;

}
EXIT;
EOF

}

#------------------------------------------------------------------------------#
# fn_backup_l1_arch(): Perform LEVEL 1 + ARCHIVELOG BACKUP #
#------------------------------------------------------------------------------#
fn_backup_l1_arch() {

rman target=/ log=${LOGFILE} append <<>
RUN {
#*** Daily full database backup

BACKUP CHECK LOGICAL
AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL = 1
DATABASE
FORMAT '${BKPDIR}/backupset/dbf_%d_%T_s%sp%p.%t'
TAG='DBF_D_$mes$dia';

#*** Daily full archivelog backup

change archivelog all validate;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP CHECK LOGICAL
AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL = 1
FORMAT '${BKPDIR}/archivelog/arc_%d_%T_s%sp%p.%t'
TAG='ARC_D_$mes$dia'
ARCHIVELOG FROM TIME 'SYSDATE-3';

#*** Maintenance steps

CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7';
}
EXIT;
EOF

}



#------------------------------------------------------------------------------#
# fn_backup_l1_noarch(): Perform LEVEL 1 + NOARCHIVELOG BACKUP #
#------------------------------------------------------------------------------#
fn_backup_l1_noarch() {

rman target=/ log=${LOGFILE} append <<>
RUN {
#*** shutdown database

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

#*** Daily full database backup

BACKUP CHECK LOGICAL
AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL = 1
DATABASE
FORMAT '${BKPDIR}/backupset/dbf_%d_%T_s%sp%p.%t'
TAG='DBF_D_$mes$dia';

#*** Maintenance steps

CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;

#*** Open database

ALTER DATABASE OPEN;
}
EXIT;
EOF

}
#------------------------------------------------------------------------------#
# fn_chk_parm(): Check for input parameters
#------------------------------------------------------------------------------#
fn_chk_parm() {
if [ ${NARG} -ne 3 ]; then
echo "BACKUP: ${ORACLE_SID}, Not enough arguments passed"
exit 1
fi
}


#------------------------------------------------------------------------------#
# fn_log(): log messages
#------------------------------------------------------------------------------#
fn_log() {
dia=`date "+%d"`
mes=`date "+%m"`
anio=`date "+%C%y"`
hora=`date "+%H%M"`
echo ${anio}/${mes}/${dia} - ${hora}: $1 | tee -a ${LOGFILE}
}


#------------------------------------------------------------------------------#
# fn_start_blackout(): start blackout for backup
#------------------------------------------------------------------------------#
fn_start_blackout() {

/u01/oracle/product/agent10g/bin/emctl start blackout ${ORACLE_SID}_BKP ${ORACLE_SID}.garba.com.ar

}

#------------------------------------------------------------------------------#
# fn_stop_blackout(): stop blackout status
#------------------------------------------------------------------------------#
fn_stop_blackout() {

/u01/oracle/product/agent10g/bin/emctl stop blackout ${ORACLE_SID}_BKP

}



################################################################################
# MAIN
################################################################################

export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/db10gr2
export PATH=/opt/unzip/bin:/usr/sbin:$ORACLE_HOME/bin:/opt:$PATH;
export NLS_LANG=american;
export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';

NARG=$#
dia=`date "+%d"`
mes=`date "+%m"`
anio=`date "+%C%y"`
hora=`date "+%H%M"`
export ORACLE_SID=$1
ORACLE_HOME=/u01/oracle/product/db10gr2
WDIR=/restore/backup/`hostname`/${ORACLE_SID}
BKPDIR=/${WDIR}
LOGFILE=${WDIR}/rman_${ORACLE_SID}.$anio$mes$dia$hora.log

fn_log "Starting rman backup of ${ORACLE_SID}... "

fn_chk_parm
if [ $2 = "WEEKLY" ] && [ $3 = "NOARCH" ]; then
echo "WEEKLY / NOARCH"
fn_start_blackout
fn_backup_l0_noarch
fn_stop_blackout
fn_log "Backup finished successfully."
exit
fi
if [ $2 = "WEEKLY" ] && [ $3 = "ARCH" ]; then
echo "WEEKLY / ARCH"
fn_start_blackout
fn_backup_l0_arch
fn_stop_blackout
fn_log "Backup finished successfully."
exit
fi
if [ $2 = "DAILY" ] && [ $3 = "NOARCH" ]; then
echo "DAILY / NOARCH"
fn_start_blackout
fn_backup_l1_noarch
fn_stop_blackout
fn_log "Backup finished successfully."
exit
fi
if [ $2 = "DAILY" ] && [ $3 = "ARCH" ]; then
echo "DAILY / ARCH"
fn_start_blackout
fn_backup_l1_arch
fn_stop_blackout
fn_log "Backup finished successfully."
exit
fi
if [ $2 = "EMERGENCY" ] && [ $3 = "ARCH" ]; then
echo "DAILY / ARCH"
fn_backup_E_arch
fn_log "Backup finished successfully."
exit
fi




fn_log "An unknown type of backup was submited, please review the input parameters: "
fn_log "rman_backup SID [WEEKLY|DAILY|EMERGENCY] [ARCH|NOARCH]"
fn_log "BACKUP FAILED!. Please review log files"

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

CREAR BASE STANDBY en ORACLE 7

Como primer paso debemos la base de datos primaria debe esta en modo ARCHIVELOG.

SVRMGR> connect internal
Connected.SVRMGR> select * from v$database;
NAME CREATED LOG_MODE CHECKPOINT ARCHIVE_CH
--------- -------------------- ------------ ---------- ----------
DATA 06/16/06 17:24:55 ARCHIVELOG 4728248833 4728248830
1 row selected.SVRMGR>
Si no se encuentra en modo ARCHIVELOG los pasos son.seteo de los parámetros del initlog_archive_dest = /opt1/oracle/arch/
log_archive_start = true
shutdown de la base.

STARTUP MOUNT EXCLUSIVE;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

listo ahora esta en archivelog mode
una vez comprobado esto hay que hacer un backup.

yo utilize este para generar el script de bkp debido a que la base tenía datafiles sobre raw devices y sobre archivos planos.


set serveroutput on size 100000
spool /opt1/oracle/dbs/copia_hot.sql
BEGIN
DECLARE
v_sentencia VARCHAR2(500);
secuencia number;
v_termina varchar2(50);
v_archivo varchar2(50);
BEGIN
secuencia := 0;
dbms_output.put_line('set serveroutput on;');
dbms_output.put_line('spool /opt1/oracle/dbs/copia_hot.log;');
FOR i IN (SELECT tablespace_name,SUM(bytes)
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY 2 DESC)
LOOP
BEGIN
dbms_output.put_line ('alter tablespace 'i.tablespace_name' begin backup;');
FOR j IN ( SELECT file_name
FROM dba_data_files
WHERE tablespace_name = i.tablespace_name)
LOOP
secuencia := secuencia + 1;
select upper(substr(j.file_name,(length(j.file_name))-3,length(j.file_name))),
substr(file_name,(instr(file_name,'/',1,((length(file_name) - nvl(length(replace(file_name, '/', '')),0)) /length('/'))))+1)
into v_termina, v_archivo
from dba_data_files
where file_name = j.file_name
and tablespace_name = i.tablespace_name;

if v_termina = '.DBF' then

dbms_output.put_line ('!rcp ' j.file_name ' oracle1@destino:'j.file_name';');

dbms_output.put_line ('!rsh destino -l oracle1 chmod 660 'j.file_name';');

else

dbms_output.put_line ('!dd if='j.file_name' bs=2048k rsh bbatrece -l oracle1 "dd of=' j.file_name' bs=2048k";');

end if;

END LOOP;

dbms_output.put_line ('alter tablespace 'i.tablespace_name' end backup;');
dbms_output.put_line ('alter system archive log current;');

END;

END LOOP;

END;

dbms_output.put_line ('ALTER DATABASE CREATE STANDBY CONTROLFILE AS''''/opt1/oracle/dbs/controlsb.ctl'''';');

begin

for cur1 in ( select upper(substr(name,(length(name))-3,length(name))) termina, name archivo
from v$controlfile)
loop

if cur1.termina = '.CTL' then

dbms_output.put_line ('!rcp /opt1/oracle/dbs/controlsb.ctl destino:'cur1.archivo';'); else
dbms_output.put_line ('!dd if=/opt1/oracle/dbs/controlsb.ctl bs=2048k rsh destino-l oracle1 "dd of='cur1.archivo' bs=2048k";');

end if;

end loop;

dbms_output.put_line('spool off;');
dbms_output.put_line('exit;');

END;/

spool OFF


Con este script se genera otro como este.



set serveroutput on;
spool /opt1/oracle/dbs/copia_hot.log;
alter tablespace COMERCIAL begin backup;
!rcp /oracle/oradata03/comercial1.dbf oracle1@destino:/oracle/oradata03/comercial1.dbf;
!rsh destino -l oracle1 chmod 660 /oracle/oradata03/comercial1.dbf;
!dd if=/dev/rdsk/c120t0d9s2 bs=2048k rsh destino -l oracle1 "dd of=/dev/rdsk/c120t0d9s2 bs=2048k";
!dd if=/dev/rdsk/c120t0d11s4 bs=2048k rsh destino -l oracle1 "dd of=/dev/rdsk/c120t0d11s4 bs=2048k";
!dd if=/dev/rdsk/c120t0d2s1 bs=2048k rsh destino -l oracle1 "dd of=/dev/rdsk/c120t0d2s1 bs=2048k";!rcp /oradata/SPERIF/base/comercial2.dbf oracle1@destino:/oradata/SPERIF/base/comercial2.dbf;
!rsh destino -l oracle1 chmod 660 /oradata/SPERIF/base/comercial2.dbf;
;alter tablespace COMERCIAL end backup;

etc...etc...etc

Luego de estar copiado todo junto con el home de ORACLE
se genera el controlfile para la Standby.


ALTER DATABASE CREATE STANDBY CONTROLFILE AS'/opt1/oracle/dbs/controlsb.ctl';

se copia el control file generado pisando el o los control files en el destino.

rcp /opt1/oracle/dbs/controlsb.ctl destino:/opt1/oracle/dbs/ctlSPERIF02.ctl;

una vez hecho esto se intenta subir la base para ver que funcione.


svrmgrl <
startup nomount;
alter database mount standby database;
recover standby database;
auto
quit
EOF

Ahora va a aplicar todos los archive logs que necesite aplicar.

PARTITIONING

Ejemplo de una tabla Particionada por rangos de fechas y su respectivo índice.


CREATE TABLE OWNER.TABLE_NAME (
campo_1 number(10),
campo_2 varchar2(100),
campo_3 date)
PARTITION BY RANGE (campo_3) (
PARTITION P200808 VALUES LESS THAN (TO_DATE('01-09-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200809 VALUES LESS THAN (TO_DATE('01-10-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200810 VALUES LESS THAN (TO_DATE('01-11-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200811 VALUES LESS THAN (TO_DATE('01-12-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200812 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200901 VALUES LESS THAN (TO_DATE('01-02-2009','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200902 VALUES LESS THAN (TO_DATE('01-03-2009','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200903 VALUES LESS THAN (TO_DATE('01-04-2009','DD-MM-YYYY'))
TABLESPACE DATA_P);


El índice para la tabla con el mísmo rango de particiones.


CREATE INDEX OWNER.INDEX_NAME ON OWNER:TABLE_NAME
(campo_2)
TABLESPACE EXCH_INDX_P
LOGGING
LOCAL (
PARTITION P200808
TABLESPACE INDX_P,
PARTITION P200809
TABLESPACE INDX_P,
PARTITION P200810
TABLESPACE INDX_P,
PARTITION P200811
TABLESPACE INDX_P,
PARTITION P200812
TABLESPACE INDX_P,
PARTITION P200901
TABLESPACE INDX_P,
PARTITION P200902
TABLESPACE INDX_P,
PARTITION P200903
TABLESPACE INDX_P,
)
NOPARALLEL;

BKP FULL RMAN CON ARCHIVE LOG

$RMAN target / rcvcat $RCVCAT_CONNECT_STR@$RCVCAT_TNS msglog $RMAN_LOG_FILE append <<>

RUN
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup database include current controlfile plus archivelog delete all input;
DELETE COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
release channel ch1;
}
crosscheck backup of database;
delete noprompt expired backup of database;
crosscheck backup of archivelog all;
delete noprompt expired backup of archivelog all;
crosscheck Archivelog all;
crosscheck backup of controlfile;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
"


Este script borra los archivelogs de LOG_ARCHIVE_DEST_1 dejando los últimos 3 días on-line.

RMAN

Crear una nueva base de datos para el repositorio, en la misma crear:
Creación del repositorio
SQL> Create tablespace rman Datafile '/u01/app/oracle/oradata/Rman/rman.dbf' Size 10M;
Tablespace created.

Creación del usuario rman
SQL> Create user rman identified by rman01 Default tablespace rman;
User created.

Otorgamos los privilegios necesarios para el usuario rman
SQL> Grant resource, connect, recovery_catalog_owner to rman;
Grant succeeded.

Ahora de esta forma podremos conectarnos al catalogo e inicializar el repositorio

[oracle@server14 oracle]$ rman catalog rman/rman01@Rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Mar 10 10:49:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> Create catalog;
recovery catalog created

A partir de este punto, la utilizacion de un repositorio es tan sencilla como especificar el nombre de usuario y la
contraseña para el repositorio en la linea de comandos.

Registrar una base de datos en el catalogo
Desde la base de datos productiva
Registramos la base de datos recuperacion en el catalogo de RMAN
[oracle@server14 oracle]$ rman target / catalog rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Mar 10 11:03:16 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DESAFA (DBID=3904418899)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>

Estadísticas usando DBMS_STATS

alter session set sort_area_size = 67108864;

BEGINSYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'OWNER',
Granularity => 'ALL',
Options => 'GATHER',
Gather_Temp => TRUE,
Estimate_Percent => 53,
Block_sample => TRUE,
Degree => 6,
Cascade => TRUE,
No_Invalidate => TRUE);
END;
/