Oracle Application DBA

Block changing tracking improves the performance of incremental backups by recording changed blocks in the block change tracking file. During an incremental backup, instead of scanning all data blocks to identify which blocks have changed, RMAN uses this file to identify the changed blocks that need to be backed up.
You can enable block change tracking when the database is either open or mounted. This section assumes that you intend to create the block change tracking file as an Oracle Managed File in the database area, which is where the database maintains active database files such as data files, control files, and online redo log files.
To determine if block change tracking is enabled, check the STATUS and FILENAME columns in the V$BLOCK_CHANGE_TRACKING view, using the following statement from the SQL or RMAN prompt:
SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
To enable block change tracking:
Connect RMAN to the target database as described in "Connecting to the Target Database Using RMAN."
Determine the current location of the database data files by submitting the following query:
RMAN> SELECT NAME FROM V$DATAFILE;
NAME
-----------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
In this example, the query results show that data files are stored in the file system in the directory /u01/app/oracle/oradata/orcl. Data files might also be stored in an Oracle Automatic Storage Management disk group.
Set the DB_CREATE_FILE_DEST initialization parameter to specify the location where new database files, including the block change tracking file, must be stored. You can specify the same directory shown in query results from the previous step, with the final portion of the path—the database SID—stripped, as shown in the following example, or designate a new directory. Any directory that you specify must have the write permission for the Oracle software owner.
The following command specifies that new database files must be stored in the directory /u01/app/oracle/oradata/:
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata';
Enable block change tracking for the database using the following command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

RMAN> drop database including backups;

ORA-19693: backup piece already included
Encounter the following errors when trying to duplicate the database to another server using the RMAN backup:
ORA-19693: backup piece E:\RMAN_BACKUP\xxxxxxxxxxx already included
10168047_748681825176303_456468755789080273_n
SOLUTION:
The location of the RMAN backup pieces at the target and auxiliary/duplicate databases are
different. So, RMAN cannot see the RMAN backup pieces, because they are stored in a different directory at the auxiliary/duplicate database server.
Try with below command which may resolve issue
RMAN> catalog start with ‘E:\RMAN_BACKUP’;
Delete expired archivelogs using RMAN
Below commands will helpful for deleting expired archivelog files through RMAN :
RMAN>list expired archivelog all;
RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all;
Now check the output with below command it should not return any list
RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>
RMAN COLD BACKUP AND RESTORE
For taking RMAN cold database backup, Database should be in mount status, Which can be noticed from below scipt.
mkdir -p /BACKUP/PROD
mkdir -p /BACKUP/PROD/log
$cold_backup.sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0
export ORACLE_SID=PROD
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
rman target / log=/BACKUP/PROD/log/PROD`date +%d%m%Y`.log <<EOF
sql ‘alter system checkpoint’;
shutdown immediate;
startup mount;
sql “create pfile=”/BACKUP/PROD/pfile`date +%d%m%Y`.ora” from spfile”;
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/BACKUP/PROD/%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/BACKUP/PROD/%U’;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT ‘/BACKUP/PROD/%U’;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT ‘/BACKUP/PROD/cntrl_%s_%p_%t’;
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}
script execution:
./cold_backup.sh
RMAN script to RESTORE DATABASE:
Make sure that rman backup is mounted on target system and necessary directories configured same as source system
$restore.sh
run
{
startup pfile=’/BACKUP/PROD/pfilexxxxxxx.ora’ nomount;
restore controlfile from ‘/BACKUP/PROD/cntrl_xxxxxxx’;
alter database mount;
restore database;
alter database open resetlogs;
}
script execution:
$rman target /
rman>@restore.sh
Finally change the database name using nid (DBNEWID) utility.
RMAN : Perform Block Recovery
We can perform BLOCK RECOVERY with or without RMAN BACKUP, Here is the demonstration of block media recovery with HOT BACKUP and RMAN BACKUP as well.
1)  Create tablespace, user and table in same schema.
2)  Take the backup of datafile.
a) HOT BACKUP.
b) RMAN BACKUP.
3)  Corrupt the data in datafile.
4)  Connect with RMAN and try to use BLOCKRECOVER command.
a) Perform Block Recovery with HOTBACKUP.
b) Perform Block Recovery with RMAN BACKUP.
Create tablespace, user and table in same schema:
$sql> create tablespace TS1 datafile ‘/d01/oradata/ts1.dbf’ size 100m;
$Create user U1 identified by U1 default tablespace TS1;
$sql> grant connect,resource to U1;
$sql> conn U1/U1
$sql> create table TEST_CORRUPT (no number);
$sql> insert into TEST_CORRUPT values(1);
$sql> coomit;
$sql> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b WHERE a.header_file=b.file# AND a.segment_name=’TEST_CORRUPT’;
SEGMENT_NAME     TABLESPACE_NAME    NAME
TEST_CORRUPT     TS1        /d01/oradata/ts1.dbf
HOTBACKUP of datafile:
$sql> ALTER TABLESPACE TS1 BEGIN BACKUP;
[oracle@localhost]$cp /d01/oradata/ts1.dbf /d01/oradata/ts1_bkp.dbf
$sql> ALTER TABLESPACE TS1 END BACKUP;
RMAN BACKUP of datafile 4:
[oracle@localhost]$rman target /
RMAN> backup datafile 4;
Corrupt the data in datafile with dd command:
Make sure that dd command given below is just for learning purposes and should only be used on testing systems
$sql>select segment_name , header_file , header_block  from dba_segments where segment_name = ‘TEST_CORRUPT’
and owner = ‘U1’;
SEGMENT_NAME  HEADER_FILE   HEADER_BLOCK
TEST_CORRUPT     4             16
[oracle@localhost]$cd /d01/oradata
[oracle@localhost]$dd of=ts1.dbf bs=8192 conv=notrunc seek=17 << EOF
> Bingo! Corrupted.
> EOF
0+1 records in
0+1 records out
18 bytes (18 B) copied, 0.000684 seconds, 27.0 kB/s
This command successfully executed block 17 in the data file “/d01/oradata/ts1.dbf” is corrupt
Check the data block curroption:
$sql> sqlplus / as sysdba
$sql> alter system flush buffer_cache;
$sql> conn u1/u1
$sql> select count(*) from TEST_CORRUPT;
select count(*) from test_corrupt
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 17)
ORA-01110: data file 4: ‘/d01/oradata/ts1.dbf’
Please perform below any one of the method, Here I am showing two methods with or without RMAN Backup.
Performing Block Recovery without having RMAN BACKUP:
As we have HOTBACKUP for datafile ts1.dbf, Catalog the “hot backup” to the RMAN repository
[oracle@localhost]$rman target /
RMAN> CATALOG DATAFILECOPY ‘/d01/oradata/ts1_bkp.dbf’;
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 17;
Performing Block Recovery with RMAN BACKUP:
As we have datafile 4 RMAN BACKUP, Perform recovey…
[oracle@localhost]$rman target /
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 17;
BLOCK MEDIA RECOVERY Complete we should now get the block 17 recovered back:
[oracle@localhost]$ sqlplus U1/U1
$sql> select count(*) from TEST_CORRUPT;
COUNT(*)
———-
1
$SQL> exit