Oracle 11g – Transport Tablespace with an Until Time Clause

Some findings/information about … a TRANSPORT TABLESPACE job with an UNTIL TIME clause.

Purpose:
Use the TRANSPORT TABLESPACE command to create transportable tablespace sets from RMAN backups instead of the live datafiles of the source database.

1st task on source server:

[oracle@newrac11 dumps]$ rman target / catalog rmangrid/******** @emrepository

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jan 19 15:37:30 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: NRAC1 (DBID=12345678)
connected to recovery catalog database

run {
TRANSPORT TABLESPACE ppu1_sd
until time "TO_DATE('2011-01-07:05:00:00','yyyy-mm-dd:hh24:mi:ss')"
TABLESPACE DESTINATION '/scratch/dumps/transportdest'
AUXILIARY DESTINATION '/scratch/dumps/auxdest'
;
}

.
.
.
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 01:16:16
Finished restore at 19-JAN-11

Removing automatic instance
shutting down automatic instance
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_sysaux_6mg0p41y_.dbf deleted
auxiliary instance file /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_undotbs2_6mfytn9k_.dbf deleted
auxiliary instance file /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_undotbs1_6mfy14c6_.dbf deleted
auxiliary instance file /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_system_6mfzjn8x_.dbf deleted
auxiliary instance file /scratch/dumps/auxdest/NRAC1/controlfile/o1_mf_6mfxy6r9_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of transport tablespace command at 01/19/2011 16:57:01
RMAN-03015: error occurred in stored script Memory Script
RMAN-06004: ORACLE error from recovery catalog database: ORA-03113: end-of-file on communication channel
Process ID: 12566
Session ID: 32 Serial number: 17643
ORACLE error from recovery catalog database: ORA-03114: not connected to ORACLE

1st fix on source server:

Changed some timeouts at the recovery catalog database (gridcontrol) in "sqlnet.ora" in "RMAN catalog server"
To avoid this errors: RMAN-06004: ORACLE error from recovery catalog database: ORA-03113: end-of-file on communication channel ...
To avoid this errors: ORACLE error from recovery catalog database: ORA-03114: not connected to ORACLE ...

## Timeout Parameters
##
## Specially for tsm connection aka long running but idle backups via rman/tivoli
##
SQLNET.INBOUND_CONNECT_TIMEOUT = 15 # Timeout in seconds
SQLNET.SEND_TIMEOUT = 7200 # Timeout in seconds
SQLNET.RECV_TIMEOUT = 7200 # Timeout in seconds
SQLNET.EXPIRE_TIME = 5 # Idle time/keep alive check in minutes

1st task on source server after the fix:

RMAN> run {
TRANSPORT TABLESPACE ppu1_sd
until time "TO_DATE('2011-01-07:05:00:00','yyyy-mm-dd:hh24:mi:ss')"
TABLESPACE DESTINATION '/scratch/dumps/transportdest'
AUXILIARY DESTINATION '/scratch/dumps/auxdest'
;
}

Creating automatic instance, with SID='Cpsc'

initialization parameters used for automatic instance:
db_name=NRAC1
db_unique_name=Cpsc_tspitr_NRAC1
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/scratch/dumps/auxdest
log_archive_dest_1='location=/scratch/dumps/auxdest'
#No auxiliary parameter file used

starting up automatic instance NRAC1

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2225872 bytes
Variable Size 125831472 bytes
Database Buffers 159383552 bytes
Redo Buffers 4837376 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2011-01-07:05:00:00','yyyy-mm-dd:hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 19-JAN-11
.
.
.
channel ORA_AUX_SBT_TAPE_2: Data Protection for Oracle: version 5.4.1.0

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-638987237-20110107-01
channel ORA_AUX_SBT_TAPE_1: piece handle=c-638987237-20110107-01 tag=TAG20110107T012036
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:56
output file name=/scratch/dumps/auxdest/NRAC1/controlfile/o1_mf_6mg4j9m5_.ctl
Finished restore at 19-JAN-11

sql statement: alter database mount clone database

sql statement: alter system archive log current

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2011-01-07:05:00:00','yyyy-mm-dd:hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 15 to
"/scratch/dumps/transportdest/ppu1_sd";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 4, 2, 15;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME
.
.
.
renamed tempfile 1 to /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 19-JAN-11
.
.
.
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00002 to /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00015 to /scratch/dumps/transportdest/ppu1_sd
channel ORA_AUX_SBT_TAPE_1: reading from backup piece NRAC1_20110107_17m1fril_1_1_0T
channel ORA_AUX_SBT_TAPE_1: piece handle=NRAC1_20110107_17m1fril_1_1_0T tag=NRAC11_0T
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 01:14:06
Finished restore at 19-JAN-11

datafile 1 switched to datafile copy
.
.
.
input datafile copy RECID=10 STAMP=740861203 file name=/scratch/dumps/transportdest/ppu1_sd

contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2011-01-07:05:00:00','yyyy-mm-dd:hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 15 online";
# recover and open resetlogs
recover clone database tablespace "PPU1_SD", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online
.
.
.
sql statement: alter database datafile 15 online

Starting recover at 19-JAN-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

starting media recovery

channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=8424
.
.
.
channel ORA_AUX_SBT_TAPE_1: reading from backup piece NRAC1_20110108_2om1ifj5_1_1_0Tarch
channel ORA_AUX_SBT_TAPE_1: piece handle=NRAC1_20110108_2om1ifj5_1_1_0Tarch tag=NRAC11_0T
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:02:35
archived log file name=/scratch/dumps/auxdest/1_8424_734280101.dbf thread=1 sequence=8424
archived log file name=/scratch/dumps/auxdest/2_8481_734280101.dbf thread=2 sequence=8481
channel clone_default: deleting archived log(s)
.
.
.
archived log file name=/scratch/dumps/auxdest/1_8425_734280101.dbf RECID=16825 STAMP=740861358
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=8426
.
.
.
channel ORA_AUX_SBT_TAPE_1: reading from backup piece NRAC1_20110108_2pm1ifl7_1_1_0Tarch
channel ORA_AUX_SBT_TAPE_1: piece handle=NRAC1_20110108_2pm1ifl7_1_1_0Tarch tag=NRAC11_0T
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=/scratch/dumps/auxdest/1_8426_734280101.dbf thread=1 sequence=8426
channel clone_default: deleting archived log(s)
.
.
.
archived log file name=/scratch/dumps/auxdest/2_8486_734280101.dbf RECID=16827 STAMP=740861377
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-JAN-11

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace PPU1_SD read only';
# create directory for datapump export
sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''
/scratch/dumps/transportdest''";
}
executing Memory Script

sql statement: alter tablespace PPU1_SD read only

sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/scratch/dumps/transportdest''

Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_Cpsc":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_Cpsc" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Cpsc is:
EXPDP> /scratch/dumps/transportdest/dmpfile.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace PPU1_SD:
EXPDP> /scratch/dumps/transportdest/ppu1_sd
EXPDP> Job "SYS"."TSPITR_EXP_Cpsc" successfully completed at 18:50:52
Export completed

/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /scratch/dumps/transportdest/ppu1_sd
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/scratch/dumps/transportdest/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/scratch/dumps/transportdest';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'dmpfile.dmp';
dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'ppu1_sd';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------

Removing automatic instance
shutting down automatic instance
database closed
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /scratch/dumps/auxdest/NRAC1/datafile/o1_mf_temp_6mg92r8r_.tmp deleted
.
.
.
auxiliary instance file /scratch/dumps/auxdest/NRAC1/controlfile/o1_mf_6mg4j9m5_.ctl deleted

It looks fine, so let's go to the destination server for this transport tablespace task (it's an 11g std edition)

1st task on destination server:

[oracle@orac11 transportdest]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 19 19:06:06 2011

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

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

create role ppu_role not identified;

grant
create session
,create procedure
,create sequence
,create synonym
,create table
,create trigger
,create view
,create type
to ppu_role
;

set echo off verify off

DECLARE
v_user varchar2(1000) ;

cursor c_get_user is
select user
from dual
;
BEGIN
dbms_output.enable(1000000) ;
open c_get_user ;
fetch c_get_user into v_user ;
close c_get_user ;
execute immediate 'revoke ppu_role from '||v_user ;
END ;
/

create user ppu_user1 identified by ppu_user1
default tablespace users
temporary tablespace temp
;

grant
ppu_role
,unlimited tablespace
to ppu_user1
;

2nd task on destination server:

[oracle@orac11 transportdest]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 19 19:06:06 2011

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

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> CREATE OR REPLACE DIRECTORY mydest_dir AS '/scratch/dumps/transportdest/';

3rd task on destination server:

[oracle@orac11 transportdest]$ impdp system/******** directory='mydest_dir' dumpfile='dmpfile.dmp' transport_datafiles='/scratch/dumps/transportdest/ppu1_sd'

Import: Release 11.2.0.1.0 - Production on Wed Jan 19 19:07:53 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=mydest_dir dumpfile=dmpfile.dmp transport_datafiles=/scratch/dumps/transportdest/ppu1_sd
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 19:08:21

4th task on destination server:

[oracle@orac11 transportdest]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 19 19:06:06 2011

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

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter user ppu_user1 default tablespace ppu1_sd;

It looks fine, checked the tables for the timestamp, everthing was right




REFERENCES:
http://oracleadvisor.com/documentation/oracle/database/11.2/backup.112/e10643/rcmsynta2021.htm
http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmttbsb.htm#CACJAEFH
http://forums.oracle.com/forums/thread.jspa?threadID=1555086&tstart=0
http://forums.oracle.com/forums/thread.jspa?threadID=911607

RMAN delete information about old backups from the catalog without a tape device

Delete the old entries in the backup catalog with touch the tapes containing the old backups, so we need a ‘dummy channel’ for the tape:

— check how many backups we have
list backup summary;

— create a dummy channel for tape
allocate channel for maintenance device type sbt
parms ‘SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)’;

— delete entries in catalog from tape backups
report obsolete;
delete obsolete;

RMAN Configuration Examples

RMAN Setup

create tablespace cat_ts
datafile ‘/opt/apps/database/oradata/emrep/cat_ts.dbf’
size 100M
autoexetend on next 100M
maxsize unlimited
segment space management auto
extent management local autoallocate; create user rman identified by blabla
temporary tablespace temp
default tablespace cat_ts
quota unlimited on cat_ts
;

RMAN Configure

(sqlplus / as sysdba) GRANT RECOVERY_CATALOG_OWNER TO rman;
rman TARGET /
(rman) CONNECT CATALOG rman/blabla
(rman) CREATE CATALOG;
rman TARGET / CATALOG rman/blabla
(rman) REGISTER DATABASE;
(rman) REPORT SCHEMA;

RMAN Setup on the Nodes

(edit tnsnames.ora for emrep resolving)
rman target / catalog rman/blabla@emrep
(rman) REGISTER DATABASE;
(rman) REPORT SCHEMA;

DB Setup on Nodes

GENERAL:
Steps when the database state is down:
1. Login to sqlplus
-sqlplus /nolog
-connect / as sysdba
2. The db must be mounted EXCLUSIVE and not open for operation
-startup mount;
3. Check the log mode status of the database
-select log_mode from v$database;
4. Setting it to noarchivelog mode
-alter database noarchivelog;
5. Setting db open for user operation
-alter database open;
6. Reverting back to archivelog mode
-alter database archivelog; In our case:
1. srvctl stop database -d rac1
2. srvctl start instance -d rac1 -i rac11 -o mount
3. connect / as sysdba
4. alter database archivelog;
5. alter database flashback on;
6. select log_mode from v$database;
7. srvctl stop database -d rac1
8. srvctl start database -d rac1
Alternative to point 8 (on each node):1. /opt/apps/grid/product/11.2.0/grid/bin/crsctl stop crs
Alternative to point 8 (on each node):2. /opt/apps/grid/product/11.2.0/grid/bin/crsctl start crs

TEST the backup

rman target / catalog rman/blabla@emrep

show all;
RMAN configuration parameters for database with db_unique_name RAC1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/apps/database/product/11.2.0/db1/dbs/snapcf_rac11.f’; # default

backup database;

USE FULL backup scripts
rman_archive_backup.sh
+++++++++++++++++++

#! /bin/ksh
#
####################################################################
#
# THE INPUT PARAMETERS ARE :
#
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
#
# Script to backup archived redo log files
#
####################################################################
#
# Check arguments
#
if [[ $# -ne 4 ]]
then
echo “$0 Error on $HOST: \nUsage: $0 TARGET_DB RMAN_OWNER RMAN_PWD RMAN_DB”
exit 1
fi

#
# Check if rman is still running; If not create the lockfile
#
if [ -f /home/oracle/scripts/var/lock/rman.lock ]
then
echo ” rman is still running, because the lockfile exists in /home/oracle/scripts/var/lock/ ”
exit 2
else
touch /home/oracle/scripts/var/lock/rman.lock
fi

###################
# RMAN variables #
###################

export SID=$1
export RMAN_OWNER=$2
export RMAN_PWD=$3
export RMAN_DB=$4
export LOGIN_PATH=$PATH
export LOGIN_LD_LIBRARY_PATH=$LD_LIBRARY_PATH

. /home/oracle/scripts/etc/ora11$SID.sh

export HBlog=/home/oracle/$SID/rmanlogs/archivebackup${SID}.`date +%d%m%y%H%M%S`.log
export HBdellog=/home/oracle/$SID/rmanlogs/archivebackup${SID}.`date +%d%m%y%H%M%S`.del.log
export emailsubfail=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.ArchBackup.Failure
export emailsubok=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.ArchBackup.OK.[info]
export mailreciever=”hosting@foo.com”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBlog} \
<
show all;
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
backup tag “${ORACLE_SID}_AR” ## filesperset 8
archivelog all
format ‘%d_%T_%U_arch’
not backed up 2 times;
resync catalog;
release channel ‘t_1’;
}
Exit

SQL

oraerror=`cat $HBlog | grep “ORA-“`
rmanerror=`cat $HBlog | grep “RMAN-“`

echo $oraerror
echo $rmanerror
if [ -z “$oraerror” -a -z “$rmanerror” ]
then
echo “No Errors”
rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBdellog} \
<
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
delete noprompt archivelog all backed up 2 times to device type ‘sbt_tape’ completed before ‘sysdate – 2’;
resync catalog;
release channel ‘t_1’;
}
## report obsolete;
## delete noprompt obsolete;
## list backup;
exit

DELSQL

oradelerror=`cat $HBdellog | grep “ORA-“` | grep -v -e “ORA-19921:”
rmandelerror=`cat $HBdellog | grep “RMAN-“` | grep -v -e “RMAN-06900:” -e “RMAN-06901:”

echo $oradelerror
echo $rmandelerror
if [ -z “$oradelerror” -a -z “$rmandelerror” ]
then
echo “No Errors”
mailx -s $emailsubok $mailreciever
## cat $HBlog $HBdellog |mailx -s $emailsubok $mailreciever
else
echo “Errors Found !!!!”
cat $HBlog $HBdellog |mailx -s $emailsubfail $mailreciever
fi
else
echo “Errors Found !!!!”
cat $HBlog |mailx -s $emailsubfail $mailreciever
fi

#
# After successfull backup job, remove the lockfile
#
if [ -f /home/oracle/scripts/var/lock/rman.lock ]
then
rm -f /home/oracle/scripts/var/lock/rman.lock
fi

rman_full_backup.sh
++++++++++++++++

#! /bin/ksh
#
####################################################################
#
# THE INPUT PARAMETERS ARE :
#
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
#
# Script to full backup the database – incremental level 0
#
####################################################################
#
# Check arguments
#

echo on

if [[ $# -ne 4 ]]
then
echo “$0 Error on $HOST: \nUsage: $0 TARGET_DB RMAN_OWNER RMAN_PWD RMAN_DB”
exit 1
fi

###################
# RMAN variables #
###################

export SID=$1
export RMAN_OWNER=$2
export RMAN_PWD=$3
export RMAN_DB=$4
export LOGIN_PATH=$PATH
export LOGIN_LD_LIBRARY_PATH=$LD_LIBRARY_PATH

. /home/oracle/scripts/etc/ora11$SID.sh

export HBlog=/home/oracle/$SID/rmanlogs/fullbackup${SID}.`date +%d%m%y%H%M%S`.log
export HBdellog=/home/oracle/$SID/rmanlogs/fullbackup${SID}.`date +%d%m%y%H%M%S`.del.log
export emailsubfail=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.FullBackup.Failure
export emailsubok=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.FullBackup.OK.[info]
export mailreciever=”hosting@foo.com”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBlog} \
<
show all;
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
backup force tag “${ORACLE_SID}_0T” incremental level 0 check logical ## filesperset 8
database force format ‘%d_%T_%U_0T’
plus archivelog format ‘%d_%T_%U_0Tarch’;
sql “alter database backup controlfile to trace”;
sql “alter database backup controlfile to trace as ”/home/oracle/$SID/cf.bak” reuse”;
sql “create pfile=”/home/oracle/$SID/pfile-ora10g-$SID.ora” from spfile”;
resync catalog;
release channel ‘t_1’;
}
Exit

SQL

oraerror=`cat $HBlog | grep “ORA-“`
rmanerror=`cat $HBlog | grep “RMAN-“`

echo $oraerror
echo $rmanerror
if [ -z “$oraerror” -a -z “$rmanerror” ]
then
echo “No Errors”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBdellog} \
<
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
delete noprompt archivelog all backed up 2 times to device type ‘sbt_tape’ completed before ‘sysdate – 2’;
resync catalog;
release channel ‘t_1’;
}
report obsolete;
delete noprompt obsolete;
## list backup;
exit

DELSQL

oradelerror=`cat $HBdellog | grep “ORA-“` | grep -v -e “ORA-19921:”
rmandelerror=`cat $HBdellog | grep “RMAN-“` | grep -v -e “RMAN-06900:” -e “RMAN-06901:”

echo $oradelerror
echo $rmandelerror
if [ -z “$oradelerror” -a -z “$rmandelerror” ]
then
echo “No Errors”
mailx -s $emailsubok $mailreciever
## cat $HBlog $HBdellog |mailx -s $emailsubok $mailreciever
else
echo “Errors Found !!!!”
cat $HBlog $HBdellog |mailx -s $emailsubfail $mailreciever
fi
else
echo “Errors Found !!!!”
cat $HBlog |mailx -s $emailsubfail $mailreciever
fi

cat rman_incr_backup.sh
+++++++++++++++++++

#! /bin/ksh
#
####################################################################
#
# THE INPUT PARAMETERS ARE :
#
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
#
# Script to backup cumulative the database and also the archive files
# blocks changed since level 0 – level 1 cumulative
#
######################################################################
#
# Check arguments
#

echo on

if [[ $# -ne 4 ]]
then
echo “$0 Error on $HOST: \nUsage: $0 TARGET_DB RMAN_OWNER RMAN_PWD RMAN_DB”
exit 1
fi

###################
# RMAN variables #
###################

export SID=$1
export RMAN_OWNER=$2
export RMAN_PWD=$3
export RMAN_DB=$4
export LOGIN_PATH=$PATH
export LOGIN_LD_LIBRARY_PATH=$LD_LIBRARY_PATH

. /home/oracle/scripts/etc/ora11$SID.sh

export HBlog=/home/oracle/$SID/rmanlogs/incrbackup${SID}.`date +%d%m%y%H%M%S`.log
export HBdellog=/home/oracle/$SID/rmanlogs/incrbackup${SID}.`date +%d%m%y%H%M%S`.del.log
export emailsubfail=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.IncrBackup.Failure
export emailsubok=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.IncrBackup.OK.[info]
export mailreciever=”hosting@foo.com”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBlog} \
<
show all;
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
backup force tag “${ORACLE_SID}_1D” incremental level 1 cumulative check logical ## filesperset 8
database force format ‘%d_%T_%U_1D’
plus archivelog format ‘%d_%T_%U_1Darch’;
sql “alter database backup controlfile to trace”;
sql “alter database backup controlfile to trace as ”/home/oracle/$SID/cf.bak” reuse”;
sql “create pfile=”/home/oracle/$SID/pfile-ora10g-$SID.ora” from spfile”;
resync catalog;
release channel ‘t_1’;
}
Exit

SQL

oraerror=`cat $HBlog | grep “ORA-“`
rmanerror=`cat $HBlog | grep “RMAN-“`

echo $oraerror
echo $rmanerror
if [ -z “$oraerror” -a -z “$rmanerror” ]
then
echo “No Errors”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBdellog} \
<
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
delete noprompt archivelog all backed up 2 times to device type ‘sbt_tape’ completed before ‘sysdate – 2’;
resync catalog;
release channel ‘t_1’;
}
## report obsolete;
## delete noprompt obsolete;
## list backup;
exit

DELSQL

oradelerror=`cat $HBdellog | grep “ORA-“` | grep -v -e “ORA-19921:”
rmandelerror=`cat $HBdellog | grep “RMAN-“` | grep -v -e “RMAN-06900:” -e “RMAN-06901:”

echo $oradelerror
echo $rmandelerror
if [ -z “$oradelerror” -a -z “$rmandelerror” ]
then
echo “No Errors”
mailx -s $emailsubok $mailreciever
## cat $HBlog $HBdellog |mailx -s $emailsubok $mailreciever
else
echo “Errors Found !!!!”
cat $HBlog $HBdellog |mailx -s $emailsubfail $mailreciever
fi
else
echo “Errors Found !!!!”
cat $HBlog |mailx -s $emailsubfail $mailreciever
fi

crontab -l -u root
++++++++++++

##
#
# M H DoM MoY DoW Command
#
####
# THE INPUT PARAMETERS ARE :
##
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
##

00 6-23 * * * su – oracle -c “/home/oracle/scripts/rman_archive_backup.sh rac11 rman blabla emrep >/dev/null 2>&1”
15 00 * * * su – oracle -c “/home/oracle/scripts/rman_full_backup.sh rac11 rman blabla emrep >/dev/null 2>&1”

##
# set the variables for the rman log dir
##

RMAN_LOGDIR_1=/home/oracle/rac11/rmanlogs

##
# delete the rman logs older than 31 days
##

30 20 * * * find ${RMAN_LOGDIR_1} -name “*.log” -mtime +31 -exec rm -f \{\} \; > /dev/null 2>&1

ora11rac11.sh
++++++++++

##
## settings for using shellscripts over crontab
##

SHELL=/bin/sh
ORACLE_SID=rac11
ORACLE_HOME=/opt/apps/database/product/11.2.0/db1
PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin

export SHELL PATH ORACLE_SID ORACLE_HOME

DB settings for TSM
******************

sqlnet.ora
++++++++++

## Timeout Parameters
##
## Specially for tsm connection aka long running but idle backups via rman/tivoli
## and the catalog database located at an other server (eg gridcontrol)
##
SQLNET.INBOUND_CONNECT_TIMEOUT = 10 # Timeout in seconds (60 = default)
SQLNET.SEND_TIMEOUT = 5 # Timeout in seconds (none = default)
SQLNET.RECV_TIMEOUT = 5 # Timeout in seconds (none = default)
SQLNET.EXPIRE_TIME = 90 # Idle time in minutes (none = default)