RMAN backup skip tablespace using EXCLUDE feature

RMAN backup skip tablespace using EXCLUDE feature

To Skip the tablespace during backup database

export ORACLE_SID=PROD
sqlplus / as sysdba

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
———- —————————— — — — —
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
3 TEMP NO NO YES
4 USERS YES NO YES
5 PROD_DATA YES NO YES
6 PROD_INDX YES NO YES

10 rows selected.

sql exit

rman target /

rman>show exclude;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD are:
RMAN configuration has no stored or default parameters

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_DATA;

Tablespace PROD_DATA will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_INDX;
Tablespace PROD_INDX will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_DATA’;
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_INDX’;

RMAN> BACKUP DATABASE;

Starting backup at 08-JUNE-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
file 5 is excluded from whole database backup
file 6 is excluded from whole database backup
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oradata/SYSTEM01.DBF
…….

We can override the exclude feature explicitly during database backup.

RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_DATA’;
CONFIGURE EXCLUDE FOR TABLESPACE ‘PROD_INDX’;

RMAN> BACKUP DATABASE NOEXCLUDE;

To Clear the EXCLUDE feature:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_DATA CLEAR;
Tablespace PROD_DATA will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE PROD_INDX CLEAR;
Tablespace PROD_INDX will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> show EXCLUDE;
RMAN configuration parameters for database with db_unique_name PROD are:
RMAN configuration has no stored or default parameters
Note:- This will only work while taking backups with RMAN. While cloning or while duplicating we can use :skip tablespace tba_name" to skip particular database.

thanks for blogging  learnappsdbaskills.blogspot.com

Data Recovery Advisor in RMAN

Data Recovery Advisor in RMAN

Connect to sqlplus and create any table.

Sql> create table myobjects  tablespace users as select * from all_objects;

Using DBMS_ROWID, we determine the blocks which this table occupies (if you like, just restrict the query to the first 5 blocks in case the table contains many blocks)

SQL> select * from
2 (select distinct dbms_rowid.rowid_block_number(rowid)
3 from myobjects)
4 where rownum < 6;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
12
13
14
15
16

We can then corrupt any one of these blocks (in our case block 13) to simulate a block corruption - don't do this in production!

dd of=/u01/app/oracle9/oradata/SATISH/datafile / o1_mf_users_9gdfxf2t_.dbf  bs=8192 seek=13 conv=notrunc count=1 if=/dev/zero

Now try to take backup of tablespace users. RMAN will validate corrupted blocks and it will not take backup.

[oracle9@fed01 datafile]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 18 12:09:23 2014

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

connected to target database: SATISH (DBID=3984685639)

RMAN> backup tablespace users;

Starting backup at 18-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=190 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf

channel ORA_DISK_1: starting piece 1 at 18-FEB-14

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

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

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

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/18/2014 12:09:46

ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf

RMAN is unable to take backup of tablespace users as a block was corrupted .

Using Data Recovery Advisor we will recover corrupted block.

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

562        HIGH     OPEN      18-FEB-14     Datafile 4: '/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf' contains one or more corrupt blocks

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

562        HIGH     OPEN      18-FEB-14     Datafile 4: '/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

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

1      Perform block media recovery of block 221 in file 4

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle9/diag/rdbms/satish/SATISH/hm/reco_1937462406.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle9/diag/rdbms/satish/SATISH/hm/reco_1937462406.hm

contents of repair script:

   # block media recovery

   recover datafile 4 block 221;

Do you really want to execute the above repair (enter YES or NO)? Yes

executing repair script

Starting recover at 18-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00004

channel ORA_DISK_1: reading from backup piece /u01/app/oracle9/product/11.2.0/dbhome_1/dbs/07ovuei2_1_1

channel ORA_DISK_1: piece handle=/u01/app/oracle9/product/11.2.0/dbhome_1/dbs/07ovuei2_1_1 tag=TAG20140206T104938

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:07

Finished recover at 18-FEB-14

repair failure complete

RMAN> backup tablespace users;

Starting backup at 18-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf

channel ORA_DISK_1: starting piece 1 at 18-FEB-14

channel ORA_DISK_1: finished piece 1 at 18-FEB-14

piece handle=/u01/app/oracle9/product/11.2.0/dbhome_1/dbs/0ep0u7sd_1_1 tag=TAG20140218T121157 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-FEB-14

Now RMAN successfully taken backup of tablespace users.

Summary of RMAN Commands

Following  are RMAN commands that you can execute at the RMAN prompt, within a RUN command, or both. All commands from previous RMAN releases work with the current release.

Command	Purpose
"@"	Run a command file.
"@@"	Run a command file in the same directory as another command file that is currently running. The @@ command differs from the @ command only when run from within a command file.
"ALLOCATE CHANNEL"	Establish a channel, which is a connection between RMAN and a database instance.
"ALLOCATE CHANNEL FOR MAINTENANCE"	Allocate a channel in preparation for issuing maintenance commands such as DELETE.
"allocOperandList"	A subclause that specifies channel control options such as PARMS, FORMAT, and MAXOPENFILES.
"ALTER DATABASE"	Mount or open a database.
"archivelogRecordSpecifier"	Specify a range of archived redo logs files.
"BACKUP"	Back up a database, tablespace, datafile, archived log, or backup set.
"BLOCKRECOVER"	Recover an individual data block or set of data blocks within one or more datafiles.
"CATALOG"	Add information about a datafile copy, archived redo log, or control file copy to the repository.
"CHANGE"	Mark a backup piece, image copy, or archived redo log as having the status UNAVAILABLE or AVAILABLE; remove the repository record for a backup or copy; override the retention policy for a backup or copy.
"completedTimeSpec"	Specify a time range during which the backup or copy completed.
"CONFIGURE"	Configure persistent RMAN settings. These settings apply to all RMAN sessions until explicitly changed or disabled.
"CONNECT"	Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
"connectStringSpec"	Specify the username, password, and net service name for connecting to a target, recovery catalog, or auxiliary database. The connection is necessary to authenticate the user and identify the database.
"COPY"	Create an image copy of a datafile, control file, or archived redo log.
"CREATE CATALOG"	Create the schema for the recovery catalog.
"CREATE SCRIPT"	Create a stored script and store it in the recovery catalog.
"CROSSCHECK"	Determine whether files managed by RMAN, such as archived logs, datafile copies, and backup pieces, still exist on disk or tape.
"datafileSpec"	Specify a datafile by filename or absolute file number.
"DELETE"	Delete backups and copies, remove references to them from the recovery catalog, and update their control file records to status DELETED.
"DELETE SCRIPT"	Delete a stored script from the recovery catalog.
"deviceSpecifier"	Specify the type of storage device for a backup or copy.
"DROP CATALOG"	Remove the schema from the recovery catalog.
"DUPLICATE"	Use backups of the target database to create a duplicate database that you can use for testing purposes or to create a standby database.
"EXECUTE SCRIPT"	Run an RMAN stored script.
"EXIT"	Quit the RMAN executable.
"HOST"	Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.
"keepOption"	Specify that a backup or copy should or should not be exempt from the current retention policy.
"LIST"	Produce a detailed listing of backup sets or copies.
"listObjList"	A subclause used to specify which items will be displayed by the LIST command.
"maintQualifier"	A subclause used to specify additional options for maintenance commands such as DELETE and CHANGE.
"obsOperandList"	A subclause used to determine which backups and copies are obsolete.
"PRINT SCRIPT"	Display a stored script.
"QUIT"	Exit the RMAN executable.
"recordSpec"	A subclause used to specify which objects the maintenance commands should operate on.
"RECOVER"	Apply redo logs or incremental backups to a restored backup set or copy in order to update it to a specified time.
"REGISTER"	Register the target database in the recovery catalog.
"RELEASE CHANNEL"	Release a channel that was allocated with an ALLOCATE CHANNEL command.
"releaseForMaint"	Release a channel allocated with an ALLOCATE CHANNEL FOR MAINTENANCE command.
"REPLACE SCRIPT"	Replace an existing script stored in the recovery catalog. If the script does not exist, then REPLACE SCRIPT creates it.
"REPORT"	Perform detailed analyses of the content of the recovery catalog.
"RESET DATABASE"	Inform RMAN that the SQL statement ALTER DATABASE OPEN RESETLOGS has been executed and that a new incarnation of the target database has been created, or reset the target database to a prior incarnation.
"RESTORE"	Restore files from backup sets or from disk copies to the default or a new location.
"RESYNC"	Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.
"RUN"	Execute a sequence of one or more RMAN commands, which are one or more statements executed within the braces of RUN.
"SEND"	Send a vendor-specific quoted string to one or more specific channels.
"SET"	Make the following session-level settings: Control whether RMAN commands are displayed in the message log Set the DBID when restoring a control file or server parameter file Specify new filenames for restored datafiles Specify a limit for the number of permissible block corruptions Override default archived redo log destinations Specify the number of copies of each backup piece Determine which server session corresponds to which channel Control where RMAN searches for backups when using an Oracle Real Application Clusters configuration Override the default format of the control file autobackup
"SHOW"	Displays the current CONFIGURE settings.
"SHUTDOWN"	Shut down the target database. This command is equivalent to the SQL*Plus SHUTDOWN command.
"SPOOL"	Write RMAN output to a log file.
"SQL"	Execute a SQL statement from within Recovery Manager.
"STARTUP"	Start up the target database. This command is equivalent to the SQL*Plus STARTUP command.
"SWITCH"	Specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file. This command is equivalent to the SQL statement ALTERDATABASE RENAME FILE as it applies to datafiles.
"untilClause"	A subclause specifying an upper limit by time, SCN, or log sequence number. This clause is usually used to specify the desired point in time for an incomplete recovery.
"UPGRADE CATALOG"	Upgrade the recovery catalog schema from an older version to the version required by the RMAN executable.
"VALIDATE"	Examine a backup set and report whether its data is intact. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksums to verify that the contents can be successfully restored.

Block Change Tracking file

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change trackingfile to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

Change tracking is disabled by default, because it introduces some minimal performance overhead on database during normal operations. However, the benefits of avoiding full datafile scans during backup are considerable, especially if only a small percentage of data blocks are changed between backups. If backup strategy involves incremental backups, then we should enable change tracking.

One change tracking file is created for the whole database. By default, the change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. We can also specify the name of the block change tracking file, placing it in any desired location.

Using change tracking in no way changes the commands used to perform incremental backups, and the change trackingfiles themselves generally require little maintenance after initial configuration.

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

In a Real Applications Clusters (RAC) environment, the change tracking file must be located on shared storage accessible from all nodes in the cluster.

Oracle saves enough change-tracking information to enable incremental backups to be taken using any of the 8 most recent incremental backups as its parent.

Although RMAN does not support backup and recovery of the change-tracking file itself, if the whole database or a subset needs to be restored and recovered, then recovery has no user-visible effect on change tracking. After the restore and recovery, the change tracking file is cleared, and starts recording block changes again. The next incremental backup after any recovery is able to use change-tracking data.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the changetracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

Enabling and Disabling Change Tracking

We can enable or disable change tracking when the database is either open or mounted. To alter the change tracking setting, we must use SQL*Plus to connect to the target database with administrator privileges.

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;  

We can also create the change tracking file in a desired location, using the following SQL statement:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f';

The REUSE option tells Oracle to overwrite any existing file with the specified name.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f' REUSE;

To disable change tracking, use this SQL statement:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;  

If the change tracking file was stored in the database area, then it will be deleted when we disable change tracking.

Checking Whether Change Tracking is enabled

From SQL*Plus, we can query V$BLOCK_CHANGE_TRACKING to determine whether change tracking is enabled or not. 

SQL> select status from V$BLOCK_CHANGE_TRACKING;

        ENABLED   => block change tracking is enabled.

        DISABLED  => block change tracking is disabled.

Query V$BLOCK_CHANGE_TRACKING to display the filename.

SQL> select filename from V$BLOCK_CHANGE_TRACKING;

Moving the Change Tracking File

If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file to refer to the new location.

1. If necessary, determine the current name of the change tracking file:

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u02/rman/rman_change_track.f

2. Shutdown the database.
SQL> SHUTDOWN IMMEDIATE

3. Using host operating system commands, move the change tracking file to its new location.
$ mv /u02/rman/rman_change_track.f /u02/rman_new/rman_change_track.f

4. Mount the database and move the change tracking file to a location that has more space. For example:

SQL> ALTER DATABASE RENAME FILE '/u02/rman/rman_change_track.f' TO '/u02/rman_new/rman_change_track.f';

5. Open the database.
SQL> ALTER DATABASE OPEN;

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u02/rman_new/rman_change_track.f

If you cannot shutdown the database, then you must disable change tracking and re-enable it, at the new location:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman_new/rman_change_track.f';

If you choose this method, you will lose the contents of the change tracking file. Until the next time you complete a level 0 incremental backup, RMAN will have to scan the entire file.

Estimating Size of the , on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database.

Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. The following two factors that may cause the file to be larger than this estimate suggests:

·                     To avoid overhead of allocating space as database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB increments. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

·                     For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size ofthe file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.