How to generate a cold backup script for a database

Probably many ways to to this, but here is how I generated a simple file that after being made executable will copy all files to a specific folder. The database must be shut down before running the script, so in other words, this will be a good, old-fashioned cold backup!

set lines 200
set pages 0
set trimspool on
set heading off
set echo off
set feedback off
set verify off

spool cp_files.sh


SELECT    'cp '
       || a.file_name
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.file_name,
                                (  INSTR (b.file_name,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM cdb_data_files b
            WHERE a.file_name = b.file_name)
  FROM cdb_data_files a
UNION
SELECT    'cp '
       || a.MEMBER
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.MEMBER,
                                (  INSTR (b.MEMBER,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM v$logfile b
            WHERE a.MEMBER = b.MEMBER)
FROM v$logfile A
UNION
SELECT    'cp '
       || a.name
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.name,
                                (  INSTR (b.name,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM v$tempfile b
            WHERE a.name = b.name)
  FROM v$tempfile a
UNION
SELECT    'cp '
       || a.name
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.name,
                                (  INSTR (b.name,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM v$controlfile b
            WHERE a.name = b.name)
  FROM v$controlfile a;

Some common errors seen when configuring TSM

ANU2534E Option file error.

The above error can occur when you run the command

tdpoconf showenv

as a non-root user (typically the user owning the oracle database software):

tdpoconf showenv

IBM Tivoli Storage Manager for Databases:
Data Protection for Oracle

Version 6, Release 3, Level 0.0
(C) Copyright IBM Corporation 1997, 2011. All rights reserved.

ANU2534E Option file error.

Solution: Change permissions on two of the configuration files:

chmod 644 /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.sys

ANS1217E (RC409) Server name not found in System Options File.

The following error typically occurs during a redirected restore, which is often needed during duplication of Oracle databases:

ANS1217E (RC409)  Server name not found in System Options File

The above indicates that the attribute "TCPServeraddress" found in the file /usr/tivoli/tsm/client/api/bin64/dsm.sys must be changed so that it identical to the source server.

ANS1087E (RC106) Access to the specified file or directory is denied.

This error could occur when you run the command tdpoconf shownev as a non-root user:

ANS1087E (RC106)  Access to the specified file or directory is denied

The above error is resolved by setting correct permissions on the /var/adm/log folder:

su -
cd /var/adm/
chmod 755 log
cd log
chmod 777 tdpoerror.log dsmerror.log

ANS1035S (RC406) Options file '*' could not be found, or it cannot be read.

This error is also triggered when you access tdpo as a non-root user, for example, by executing

tdpoconf showenv

as a non-root user:

ANS1035S (RC406)  Options file '*' could not be found, or it cannot be read.

To solve the problem, grant the right permissions on the files:

su -
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.opt
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.sys

ANS1025E (RC137) Session rejected Authentication failure.

The above error may occur when you are attempting to set a new password as a part of reconfiguring the server for a redirected restore:

ANS1025E (RC137) Session rejected: Authentication failure

When you run

tdpoconf passwd

you are prompted for password three times:

current password
new password
confirm new password

At this point during the configuration, the tsm server pointed to in the attribute "TCPServeraddress" in the configuration file /usr/Tivoli/tsm/client/api/bin64/dsm.sys, believes that it is communicating to the original backupclient. Therefore, it is important that you supply the original backupclient's password at all three prompts.

ANS0282E (RC168) Password file is not available.

When the above error message occurs, you simply need to execute

tdpoconf passwd

to reset the password. Supply the original backup client's password at all three prompts.

How to relocate the block change tracking file

To relocate the block change tracking file you have two options:

1) shutdown database, mount database, update control file, open database

sqlplus / as sysdba
shutdown immediate
exit
 -- Move the block change tracking file to the new location using the appropriate os utility. --
sqlplus / as sysdba
startup mount
ALTER DATABASE RENAME FILE 'ora_home/dbs/change_trk.f' TO '/new_disk/change_trk.f'; 
ALTER DATABASE OPEN;

OR

2) disable and re-enable block change tracking, and point to the new location when re-enabling.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

See Section "4.4.4.3 Moving the Change Tracking File" in the Oracle Documentation regarding this feature.

Why are there multiple rows in V$SESSION_LONGOPS when running an RMAN backup?

Answer:
When performing an RMAN backup, V$SESSION_LONGOPS will be populated with two types of rows:

Detailed rows: Captures the progress of individual job steps.

Aggregated rows: Captures and summarizes the progress for the individual step of the backup job, as they complete. They are updated only after each detailed step has finished.

Example:
Below I am querying the V$SESSION_LONGOPS after an incremental backup level 0 has started, using 6 channels. Notice that there are both individual rows and an aggregated row present:

SELECT SID,SERIAL#,OPNAME,TARGET_DESC,TOTALWORK,SOFAR,UNITS,START_TIME,TIME_REMAINING,ELAPSED_SECONDS,MESSAGE
FROM V$SESSION_LONGOPS 
WHERE  TOTALWORK <> 0
AND SOFAR <> TOTALWORK
ORDER BY SERIAL# ASC;

SID	SERIAL#	OPNAME	TARGET_DESC	TOTALWORK	SOFAR	UNITS	START_TIME	TIME_REMAINING	ELAPSED_SECONDS	MESSAGE
203	7409	RMAN: incremental datafile backup	Set Count	4096000	1004926	Blocks	01.08.2014 12:28:28	557	181	RMAN: incremental datafile backup: Set Count 137085: 1004926 out of 4096000 Blocks done
398	15079	RMAN: incremental datafile backup	Set Count	3018368	1122302	Blocks	01.08.2014 12:28:28	306	181	RMAN: incremental datafile backup: Set Count 137088: 1122302 out of 3018368 Blocks done
439	17451	RMAN: incremental datafile backup	Set Count	2560000	1127038	Blocks	01.08.2014 12:28:29	229	180	RMAN: incremental datafile backup: Set Count 137089: 1127038 out of 2560000 Blocks done
357	31627	RMAN: incremental datafile backup	Set Count	3044608	1115518	Blocks	01.08.2014 12:28:28	313	181	RMAN: incremental datafile backup: Set Count 137087: 1115518 out of 3044608 Blocks done
476	55467	RMAN: incremental datafile backup	Set Count	2557440	940542	Blocks	01.08.2014 12:28:29	309	180	RMAN: incremental datafile backup: Set Count 137090: 940542 out of 2557440 Blocks done
512	55527	RMAN: aggregate input	backup	76514816	4360436	Blocks	01.08.2014 12:28:28	2085	126	RMAN: aggregate input: backup 33: 4360436 out of 76514816 Blocks done
281	56085	RMAN: incremental datafile backup	Set Count	3251200	969214	Blocks	01.08.2014 12:28:28	426	181	RMAN: incremental datafile backup: Set Count 137086: 969214 out of 3251200 Blocks done

Let's join V$SESSION_LONGOPS with V$SESSION and (if desirable) V$PROCESS, to view only the detailed rows, and estimate the progress in percent. This query is very useful to quickly get an overview of your backup, whether or not it is progressing according to expected speed etc:

SELECT S.CLIENT_INFO "Client Info", SL.OPNAME "Operation" ,SL.MESSAGE, SL.SID, SL.SERIAL#, P.SPID "OS Process ID", SL.SOFAR "So Far", SL.TOTALWORK "Totalwork", ROUND(SL.SOFAR/SL.TOTALWORK*100,2) "% complete"
FROM V$SESSION_LONGOPS SL INNER JOIN V$SESSION S ON SL.SID = S.SID 
                          INNER JOIN V$PROCESS P ON P.ADDR = S.PADDR
AND OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/

Client Info	Operation	MESSAGE	SID	SERIAL#	OS Process ID	So Far	Totalwork	% complete
rman channel=tsm_channel_0	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137096: 1173118 out of 2252800 Blocks done	203	7409	42926864	1173118	2252800	52,07
rman channel=tsm_channel_1	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137095: 2029054 out of 2393600 Blocks done	281	56085	52101750	2029054	2393600	84,77
rman channel=tsm_channel_2	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137097: 293246 out of 2096640 Blocks done	357	31627	56361104	293246	2096640	13,99
rman channel=tsm_channel_3	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137099: 174206 out of 2048000 Blocks done	398	15079	52756692	174206	2048000	8,51
rman channel=tsm_channel_4	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137098: 460286 out of 2048000 Blocks done	439	17451	29032454	460286	2048000	22,47
rman channel=tsm_channel_5	RMAN: incremental datafile backup	RMAN: incremental datafile backup: Set Count 137100: 196094 out of 2048000 Blocks done	476	55467	9700724	196094	2048000	9,57

Notice how the V$RMAN_STATUS only hold information on aggregated level (2 rows, one for each operation although the first spawns the other):

SELECT SID,OPERATION,STATUS,MBYTES_PROCESSED, START_TIME, END_TIME, OBJECT_TYPE, OUTPUT_DEVICE_TYPE 
FROM V$RMAN_STATUS WHERE STATUS = 'RUNNING';

SID	OPERATION	STATUS	MBYTES_PROCESSED	START_TIME	OBJECT_TYPE	OUTPUT_DEVICE_TYPE
512	BACKUP	RUNNING	226641,953125	01.08.2014 12:28:28	DB INCR	SBT_TAPE
512	RMAN	RUNNING	0	01.08.2014 12:28:28

How to backup and delete a specific range of archivelogs

Use the "BACKKUP ARCHIVELOG FROM LOGSEQ .. UNTIL LOGSEQ" syntax:

RMAN> backup archivelog from logseq 3 until logseq 4 delete input;

Output will be similar to:

Starting backup at 27-MAY-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=3 STAMP=848499170
input archived log thread=1 sequence=4 RECID=4 STAMP=848499311
channel ORA_DISK_1: starting piece 1 at 27-MAY-14
channel ORA_DISK_1: finished piece 1 at 27-MAY-14
piece handle=/u04/archive/PRODDB01/backupset/2014_05_27/o1_mf_annnn_TAG20140527T115156_9r8r1wt7_.bkp tag=TAG20140527T115156 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_3_9r3qksmy_.arc RECID=3 STAMP=848499170
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_4_9r3qp6mt_.arc RECID=4 STAMP=848499311

A check in the V$ARCHIVED_LOG reveals what Oracle has done:

SQL>  select name,sequence#,archived,applied,deleted,status,IS_RECOVERY_DEST_FILE,compressed from v$archived_log;

NAME                                                                      SEQUENCE# ARC APPLIED   DEL S IS_ COM
------------------------------------------------------------------------ ---------- ---------- --- --------- --- - --- ---
                                                                                   1 YES NO        YES D YES NO
                                                                                   2 YES NO        YES D YES NO
                                                                                   3 YES NO        YES D YES NO
                                                                                   4 YES NO        YES D YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_5_9r3r0fo6_.arc                5 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_6_9r3rc6pf_.arc                6 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_7_9r3rmkpr_.arc                7 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_8_9r3s0vs3_.arc                8 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_9_9r3sbst4_.arc                9 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_10_9r3so0v9_.arc              10 YES NO        NO  A YES NO

How to enable block change tracking for faster incremental backups:

From Oracle 10g and onwards, you can enable block change tracking to speed up incremental backups. In short,

"if change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile"

Syntax:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/path/PRODDB_rman_change_track.dbf' REUSE;

Database altered.

More info here

Important files and directories in a Netbackup setup

Important netbackup files and directories on a typcial unix server:

/usr/openv/netbackup/version --> displays the netbackup version
/usr/openv/netbackup/bp.conf --> displays important settings such as SERVER, MEDIA_SERVER, CLIENT_NAME
/usr/openv/netbackup/logs/user_ops/dbext/logs --> log files for rman backups

Example of bp.conf

SERVER = backupserver1.mydomain.com
MEDIA_SERVER = server1-bkp.mydomain.com
CLIENT_NAME = server1-bkp.mydomain.com
REQUIRED_INTERFACE = server1-bkp.mydomain.com

Note that the CLIENT_NAME used above translates into the NB_ORA_CLIENT typically used in when sending backups to tape using Netbackup:

NB_ORA_CLIENT=server1-bkp.mydomain.com