Category Archives: RMAN

Clone/Refresh Database using RMAN

For our understanding

Source Database: PRIM (It would be TARGET in case of RMAN Terminology)

Auxiliary/Clone Database: TEST

1) Take FULL backup on source database using RMAN:

Verify Source database is configured in archivelog mode.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

RMAN> backup database plus archivelog;

2) Perform following preparation tasks in Auxiliary Database:

Create a password file for the duplicate/Target instance, create password file in source and copy to auxiliary would also work here.

orapwd file=/u01/oracle/product/12c/dbs/orapwTEST password=password entries=10

Configure listener and tnsnames files for Auxiliary/TEST database server. Configure listener and tnsnames files

Create pfile for auxiliary environment and start the database to nomount status:

Add following entries to pfile, in case directory path for Auxiliary server is different for DB and log files when compare to source database.

Db_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path’
Log_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path

SQL> conn /as sysdba
SQL> startup nomount pfile=/u01/oracle/product/12c/dbs/initTEST.ora

 Add tnsentries of source database in Auxiliary, please do vice versa

3) duplicate command to create TEST database:

Veryfy the RMAN connection connecting to TARGET Database(PRIM):

rman TARGET sys/password@PRIM

Veryfy the RMAN connection connecting to TARGET Database(PRIM) and AUXILIARY(TEST):

rman TARGET sys/password@PRIM auxiliary /

Now We can use duplicate database command to create TEST database.

rman > duplicate target database to ‘TEST’;

This process takes a while depending on the size of database and server configuration.

Thank you !!

 

 

ORA-19693: backup piece already included

PROBLEM:

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’;

 

Thank You !!

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>

 

Thank You !!

RMAN COLD BACKUP AND RESTORE

Simple RMAN script to take cold database backup:

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

Simple 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.

Here you go…

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

HAPPY LEARNING !!