Uso de HS para conectar ORACLE con DB2

En esta ubicación se debe generar un initXXX.ora con el nombre que declaremos en el listener, para no complicar las cosas es conveniente poner el mismo nomre tanto en el listener como en el tnsnames.


/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/hs/admin/initDB2DATABASE.ora

#This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
#HS_FDS_CONNECT_INFO = ODBC_DSN
HS_FDS_CONNECT_INFO = prueba
HS_FDS_TRACE_FILE_NAME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/hs/admin/DB2DATABASE.log
HS_FDS_CONNECT_INFO = DB2DATABASE
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

#
# Environment variables required for the non-Oracle system
#
set DB2INSTANCE=is400

listener.ora

Para la configuración del listener se debe usar una entrada como la que se usa para el "extproc" pero en PROGRAM se debe poner hsodbc.

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM = extproc)
)

(SID_DESC =
(SID_NAME = DB2DATABASE)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM =hsodbc)
(ENVS = LD_LIBRARY_PATH = /opt/ibm/iSeriesAccess/lib:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)


DEFAULT_SERVICE_LISTENER = (XE)


tnsnames.ora

En el tnsnames agregamos la entrada que en nuestro caso al igual que en el listener llamamos DB2DATABASE.

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srvpdf)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

DB2DATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =localhost )(PORT=1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB2DATABASE)
)
(HS=OK)
)

odbc.ini

[prueba]
Description = iSeries Access ODBC Driver
Driver = iSeries Access ODBC Driver
System = xxx.xx.3.2
UserID = xxxxxx
Password = xxxxxx
Naming = 0
DefaultLibraries = NOMBRE
Database = NOMBRE
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 0
MaxFieldLength = 32
BlockFetch = 1
BlockSizeKB = 128
ExtendedColInfo = 0
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

odbcinst.ini

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1

[iSeries Access ODBC Driver]
Description = iSeries Access for Linux ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
UsageCount = 1
CPTimeout =
CPReuse =
System = 192.181.13.2
User = username
Password = password
NOTE1 = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2 = the following Driver64/Setup64 keywords will provide that support.
Threading = 2
DontDLClose = 1

ODBC Driver


-bash-3.2$ cd /opt/ibm/iSeriesAccess/lib
-bash-3.2$ ls -ltr
total 2260
-r-xr-xr-x 1 root root 443939 Apr 5 2008 libcwbxda.so
-r-xr-xr-x 1 root root 94504 Apr 5 2008 libcwbrc.so
-r-xr-xr-x 1 root root 16636 Apr 5 2008 libcwbodbcs.so
-r-xr-xr-x 1 root root 729572 Apr 5 2008 libcwbodbc.so
-r-xr-xr-x 1 root root 998060 Apr 5 2008 libcwbcore.so


luego de hacer todo esto me encontre con un error porque esta apuntando al driver en vez de apuntar al ODBC en el archivo de confuguración en el ORACLE_HOME/hs/admin


SQL> /
*select * from display@db2database*
***
ERROR en línea 1:
ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este
mensaje:
[Generic Connectivity Using ODBC][C077] Could not find symbol 'SQLAllocConnect'
in dynamic library
[C079] Failed to load dynamic library
'/opt/ibm/iSeriesAccess/lib/libcwbodbc.so'
ORA-02063: 3 lines precediendo a DB2DATABASE


en el parámetro

lo apunte a

HS_FDS_SHAREABLE_NAME = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so

que es el driver del ODBC

luego lo reapunte a

HS_FDS_SHAREABLE_NAME = /usr/local/etc/libodbc.so

Métodos de RECOVERY con RMAN

RMAN Recovery Methods

When performing a restore operation, it is best to open two telnet sessions,

one for SQL commands, and one for RMAN commands. For the rest of this document,

RMAN commands will be prefaced with the RMAN> prompt, and SQL commands will

be prefaced with the SQL> prompt. A $ indicates that the command is executed from the Unix prompt.

Restoring and Recovering All Datafiles

In this scenario, it is assumed that your control files are still accessible.

You have a backup, done for example with backup database plus archivelog;

Your first step is to make sure that the target database is shut down:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
ORACLE instance shut down.

Next, you need to start up your target database in mount mode. RMAN cannot restore datafiles

unless the database is at least in mount mode, because RMAN needs to be able to access the

control file to determine which backup sets are necessary to recover the database. If the control

file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in

the following example to mount the database:

SQL> startup mount;
Oracle instance started.

Database mounted.

Since backup set files are created in an RMAN-specific format, you must use RMAN to restore

the datafiles. To use RMAN, connect to the target database:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

The remainder of this example shows how to restore all of the datafiles of the target database.

When the restore command is executed, RMAN will automatically go to its last good backup

set and restore the datafiles to the state they were in when that backup set was created.

When restoring database files, RMAN reads the datafile header and makes the determination as to

whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then i

ssuing the RMAN restore database command.

With Oracle9i and above, you don't need to allocate a channel explicitly. Instead, you can use the default channel mode:

RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

For Oracle8i, the ALLOCATE, RESTORE, and RECOVER commands need to be enclosed by the run{} command:

RMAN> run {
allocate channel d1 type disk;
restore database;
recover database;
}
alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Specific Tablespaces

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example

with backup database plus archivelog;

Take the tablespace that needs recovery offline, restore the tablespace, recover the tablespace, and bring the

tablespace online. If you cannot take the tablespace offline, then shutdown abort the database and restore in mount mode.

First try to take the tablespace offline;

$ sqlplus "/ as sysdba"
SQL> alter tablespace tab offline;

If this works, continue with the RMAN recovery:

$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore tablespace tab;
RMAN> recover tablespace tab;
SQL> alter tablespace tab online;

If taking the tablespace offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore tablespace tab;
RMAN> recover tablespace tab;
SQL> alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Specific Datafiles

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with

backup database plus archivelog;

Take the datafile that needs recovery offline, restore the datafile, recover the datafile, and bring the datafile online.

If you cannot take the datafile offline, then shutdown abort the database and restore in mount mode.

First try to take the datafile offline:

SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' offline;

If this works, continue with the RMAN recovery:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'
RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'

SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' online;

If taking the datafile offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';
RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';
SQL> alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Control Files

In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;

In an ideal world you'll never use RMAN to restore a control file. But if something catastrophic happens, and you lose all control files, here are the steps for getting them back:

The following examples assume that you are using a catalog. First, here's the simplest Oracle9i syntax for restoring a control file:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod

Set the database id (DBID) with the following command. This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log. The number can be obtained from any previous RMAN backup log file.

RMAN> set dbid = xxxxxxxxxx;

RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;

If this fails with ...

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

... then you must perform a recover database:

SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> recover database;
SQL> alter database open resetlogs;

RMAN> reset database;

Note, that all offline archivelogs are now useless, perform a full back as soon as possible.

Restoring Online Redologs

In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod

Set the database id (DBID) with the following command. This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log. The number can be obtained from any previous RMAN backup log file.

RMAN> set dbid = xxxxxxxxxx;

RMAN> restore controlfile;
SQL> alter database mount;
RMAN> restore database;
RMAM> recover database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/28/2004 11:03:23
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 8448414

Since the online logs were lost, complete recovery is not possible. Open the database with resetlogs to continue.

RMAN> alter database open resetlogs;

-------------------------------------IMPORTANT-------------------------------------------

During this type of recovery, if you receive error messages like this:

RMAN> restore database;

Starting restore at 11-JUL-05

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/11/2005 14:25:22

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 10 found to restore

RMAN-06023: no backup or copy of datafile 9 found to restore

RMAN-06023: no backup or copy of datafile 8 found to restore

RMAN-06023: no backup or copy of datafile 7 found to restore

RMAN-06023: no backup or copy of datafile 6 found to restore

RMAN-06023: no backup or copy of datafile 5 found to restore

RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN-06023: no backup or copy of datafile 2 found to restore

RMAN-06023: no backup or copy of datafile 1 found to restore

…use the following directions to recover (recreate the controlfile):

• With the database mounted, execute ‘alter database backup controlfile to trace resetlogs;’
• Perform a shutdown abort on the database, but remain at the SQL> prompt.
• In another telnet session, go to the udump directory to retrieve the resulting trace file and copy it to another location to edit it.
• Edit the file and add the phrase “until cancel” to the recover database command at the end. The phrase should read “recover database until cancel using backup controlfile”. Remove the “alter database open” command after the recover command. Save the file with a .sql extension.
• Back at the SQL> prompt, execute the modified trace file. When prompted for an archived log, type in “cancel” and the reply should be “media recovery cancelled”.
• Issue “alter database open resetlogs”. The database should open after a few moments.
• Connect to the RMAN recovery catalog and issue the “reset database” command.
• Perform a full RMAN backup as soon as possible.

----------------------------------------------------------------------------------------

Time-Based, Change-Based, or SCN-based Incomplete Recovery

Incomplete recovery uses a backup to produce a non-current version of the database. In other words, you
do not apply all of the redo records generated after the most recent backup.

You usually perform incomplete recovery of the whole database in the following situations:

Media failure destroys some or all of the online redo logs.

A user error causes data loss, for example, a user inadvertently drops a table.

You cannot perform complete recovery because an archived redo log is missing.

You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database; in other words, a database with a new stream of log sequence numbers starting with log sequence 1.

NOTE – Start every RMAN incomplete recovery with the following commands:

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod

--For time-based recovery, use these commands:

RMAN> restore database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";
media recovery complete.
SQL> alter database open resetlogs;

--For SCN-based recovery, user these commands:

RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
media recovery complete.
SQL> alter database open resetlogs;

--For change-based recovery, user these commands:

RMAN> restore database until sequence 9923; --Archived log sequence number
RMAN> recover database until sequence 9923; --Archived log sequence number
media recovery complete.
SQL> alter database open resetlogs;

Once the recovery has been completed, execute the following steps:

• Delete prior backups with this command (from the RMAN prompt):

RMAN> delete force backup;

This command removes all prior backups from the RMAN catalog as they can no longer be used once the database has been restarted with the resletlogs option. After completing this command, create a new RMAN backup to establish a new baseline.

Recovering Archived Logs only

In the event that you want to recover the database archived redo logs until a desired time, you can use the following commands:

$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';

or

RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')

UNTIL TIME 'SYSDATE';

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.