Oracle Database 12c Backup and Recovery Survival Guide

Oracle 12c Released in July 2013, and with every release for new oracle database version we expect huge amount of book to be released also, Books Talking about new Features, Administration, RAC, Data Guard,and finally Backup and Recovery.

When I am looking  for Oracle book it should have three Main things at least :

  • Description ( Table Of Content )
  • Examples for the new features.
  • Cover The specific topic and cover it so well.
Recently i received an email from Packt Publishing WebSite Here to review A book for Oracle 12c Related to Backup and Recovery, the Book Title

            ” Oracle Database 12c Backup and Recovery Survival Guide”

The above book written  by two oracle experts

  • Francisco Munoz ( Oracle ACE Director )
  • Aman Sharma ( Oracle ACE )
And Trust me when i am saying i need another post to talk about these two author. the books contain an amazing information for every DBA, it’s not necessary to be an expert to buy this book because they already covered everything from beginning with Examples or should i say with Lot of examples, what i would say here i recommended this book because it’s contain best practice for Backup and Recovery Covered by two Oracle Experts.
Description for Table Of Content :

Chapter 1: Understanding the Basics of Backup and Recovery 
First Chapter describe the Backup basic, why the backup is very important and what is the most common issue 

Chapter 2: NOLOGGING Operations
I have to say this chapter give me new idea about Nologging, and describe Best Solution for Nologging and Logging Operating in Database. 
Chapter 3: What is New in 12
Discuss 12c New Feature Related to Backup and Recovery ( RMAN,Datapump .. ) and simple description about multitenant Features and Architecture. 
Chapter 4: User-managed Backup and Recovery
Describe All kind of backup,Offline,cold and hot with Examples and Description for these example.

Chapter 5: Understanding RMAN and Simple Backups
This Chapter Describe RMAN Architecture & RMAN Concept Backup and how to use RMAN with oracle 12c,how to backup Controlfile, Spfile and Archive log,how to use Fast Incremental Backup. 
Chapter 6: Configuring and Recovering with RMAN
This Chapter Covered how to configure database for backup and recovery and how to configure RMAN for Backup, All the configuration covered by Examples and Lot Of scenarios.Configuration for Compression backup,snapshot and authentication for RMAN.

Chapter 7: RMAN Reporting and Catalog Management
From the Name of this chapter it’s covered RMAN Catalog and Report command and List command to get query and check backup.

Chapter 8: RMAN Troubleshooting and Tuning

This is Excellent Chapter for All DBA it’s Contains information how to troubleshoot RMAN,Tune RMAN, and how to monitor RMAN Session , Useful for Every DBA.
Chapter 9: Understanding Data Pump
This Chapter talking about Data Pump Architecture and Examples how to move data use data pump.
Chapter 10: Advanced Data Pump
If you want to move to next level and Increase your knowledge about Data pump , this chapter contains topics every DBA needs such as create small copies of production, create database in different file Structure, Moving Object from one table space to another and other excellent topics.

Chapter 11: OEM12c and SQL Developer
Enterprise manager 12c and Sql Developer friends for DBA, and Have been used started to used by DBA’s  All the above chapter using the command Line, but this one describe how to use these Two Tools to Configure backup and monitor backup, using export/import with EM12c.

About the Author :

has over two decades of experience in consulting, analysis, support, implementation, and migration of Oracle products. He is also an expert in most phases of a database life cycle, for example, development, stabilization, security, backup and recovery, tuning, installations, and data warehouse (ETL) with excellent implementation and support methodologies. He is a popular speaker at many Oracle conferences around the world.

is an Oracle Database consultant and instructor. He holds a Master’s degree in Computer Applications and has been working with Oracle Database for over a decade. His main focus is to understand how Oracle Database works internally. Besides the core database, he has a very strong knowledge of Linux, Solaris, Oracle RAC, Data Guard, RMAN, Oracle Exadata and Oracle Enterprise Manager.

You can Order the Book Now :

Finally i would thank Francisco & Aman for this amazing Book.

Thank you 
Osama Mustafa

Scripts For Backup

This Script Will Let you choose One Of three Type Of backup , All you have to do right the backup scripts to make the below script read it and run it :

echo “Please Specify the kind of backup you want to take”
echo “1) COLD BACKUP”
echo “2) HOT BACKUP”
echo “Enter your option”
read option
 while [ $option -gt 3 ]||[ $option -le 0 ]
echo “Please Enter the correct option”
read option
case $option in
1|2) echo “You are taking rman backup of DB”
   rman target sys/sys @/oracle/product/11g/rman_backup_$option.txt;exit;;
3) echo “You are taking export backup of DB”
   exp system/sys file=/oracle/exp_dat.dmp log=/oracle/exp_dat.log full=y;

 The above script can call anyone of the following rman script depending upon the user who wants take cold or hot backup

The content of rman_backup_1.txt

run {
   shutdown immediate;
   startup mount;
   allocate channel dup1 device type disk;
   allocate channel dup2 device type disk;
   backup format ‘/oracle/%U’ database;
   release channel dup1;
   release channel dup2;
   alter database open;

The content of rman_backup_2.txt

run {
allocate channel dup1 device type disk;
allocate channel dup2 device type disk;
backup format ‘/oracle/%U’ database;
backup format ‘/oracle/arch_%U’ archivelog all;
backup format ‘/oracle/ctl_%U’ current controlfile;
release channel dup1;
release channel dup2;

Thank you
Osama mustafa 

Check RMAN Backup Status

The Below Scripts To Check Backup Status Via Sqlplus , Maybe you will find Same Or Another Scripts In the Same way : 

Sqlplus / as sysdba

This script will report on all backups – full, incremental and archivelog backups –

col STATUS format a9
col hrs format 999.99
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
order by session_key;

This script will report all on full and incremental backups, not archivelog backups –

col STATUS format a9
col hrs format 999.99
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
where input_type='DB INCR'
order by session_key;

I would Love to Thank gavin soorma – OCM For His Amazing Effort . Share it For Knowledge .

Thank you
Osama Mustafa

RMAN-10003 ORA-12154

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/15/2011 12:54:22
RMAN-12001: could not open channel ORA_DISK_1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12154: TNS:could not resolve the connect identifier specified

Solution :

RMAN> run {
2> allocate channel ch00 type disk;
3> restore database;

Thank You
osama Mustafa

Some Basic To backup Voting Disk And OCR For Oracle RAC

We Need To Backup Voting Disk and OCR For Oracle RAC For Disaster Situation :

Add Voting Disk :

# crsctl add css votedisk

To remove a voting disk:

# crsctl delete css votedisk

If Oracle Clusterware is down on all nodes, use the –force option:

# crsctl add css votedisk -force
# crsctl delete css votedisk -force

 Backup Voting Disk :

$ crsctl query css votedisk
$ dd if= of= bs=4k


Oracle RAC Taking Backup For OCR By Default , You will Find It On :

 cd $ORACLE_BASE/Crs/cdata/jfv_clus

Change the default automatic backup location:

# ocrconfig –backuploc /shared/bak

Back Up OCR Manually :

 # ocrconfig –export file name

 Recover OCR Using Physical Backups:

1.Locate a physical backup:
 $ ocrconfigshowbackup
2.Review its contents:

# ocrdumpbackupfile file_name
 3.Stop Oracle Clusterwareon all nodes:

 # crsctl stop crs
4.Restore the physical OCR backup:

# ocrconfig –restore <CRS HOME>/cdata/jfv_clus/day.ocr

5.Restart Oracle Clusterware on all nodes:

 # crsctl start crs
6.Check OCR integrity:

$ cluvfy comp ocr -n all

Thank You
Osama Mustafa 



Take RMAN backup of the production RAC database

RMAN> run{
allocate channel c1 type disk format ‘/tmp/%U’;
backup database;
backup archivelog all;

backup controlfile ;

2-Create Pfile from RAC for single Node Using SQLPLUS :
 SQL>create pfile ” from spfile ;

3-Open pfile that you create it and modify the following parameters manually:
%dest, control_files

4-Use the pfile created above to STARTUP NOMOUNT the database on the new host
$ sqlplus / as sysdba
SQL> startup nomount;

5-rman target /
A-restore controlfile from ‘/tmp/’;
  restore controlfile from ‘/media/moh_1509/MEPSLIVE_20110919_762231895_3885.ctl’

C.catalog backuppiece  ‘.bkp’ repeat it for all of backupset .
D.catalog backuppiece  ‘.arch’ repeat it for all of ARCHIVELOG.

set newname for datafile 1 to ”;
set newname for datafile 2 to  ”;
set newname for datafile 3 to ”;
set newname for datafile 4 to  ”;
set newname for datafile 5 to  ”;
set newname for datafile 6 to  ”;
     restore database;
     switch datafile all;

NOTE : datafile number must be taken from the production system .

sqlplus / as sysdba

alter database rename file ” to ”;
alter database rename file ” to ”;
alter database rename file ” to ”;

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

Then Delete unnecessary Redolog .


7.SQL> alter database open resetlogs;

If open database fail with error ORA-38856
then, Set the following parameter in the init.ora file:


SQL> select THREAD#, STATUS, ENABLED 2 from v$thread;

SQL> select group# from v$log where THREAD#=2;
SQL> alter database disable thread 2;

 SQL> alter database clear unarchived logfile group ;  
SQL>alter database drop logfile group ;

After doing this you can remove undo tablespace for other instance 

10.To Remove it :
 SQL> show parameter undo;
SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’;
SQL> drop tablespace UNDOTBS2 including contents and datafiles; 

Now you have single node instance 🙂

Thank you
Osama Mustafa

Threats to Database Security Part 1

Today, all company needs to save data and information, these kinds of data different from company to other such as (pictures, employee and Customer data); it’s so rarely to find companies used papers to save important data.We don’t disagree on database type, Sure Oracle is the best but there’s another product in the market today such as Oracle, Microsoft and k2 but all these products have common thing Called Threats .

Before this, we need to know what we mean with database Security since it’s my article subject
Database Security: can be defined as a system or process by which the “Confidentiality, Integrity, and Availability (CIA) of the database can be protectedUnauthorized entry or access to a database server signifies a loss of confidentiality; unauthorized alteration to the available data signifies loss of integrity; and lack of access to database services signifies loss of availability. Loss of one or more of these basic facets will have a significant impact on the security of the database.
This is the common defined for database security.For an illustration of this concept, imagine that the website of a company contains information like who they are, what they do, and what prospective customers have to do to contact them for their queries. In this case, the availability of the database services is more important when compared with other factors like the confidentiality or integrity of the database security.
Threats and risks to databases have increased and therefore, the need for securing databases has also increased. When it comes to securing a database, lots of things have to take care of , if you was focused On pervious talking you will know that I am talking about:
1- Confidentiality
2- Integrity
3- Availability


What I mean in this word is so simple by encrypting the data stored in the database,two type of encryption in database :
This refers to data that is moving within the network. Sensitive data, for example, that is sent through network layers or through the Internet. A hacker can gain access to this sensitive data by eavesdropping. When this happens, the confidentiality of the data is compromised. Encrypting datain-transit avoids such compromises.

2- Data-at-rest:
It is possible for a hacker to hack the data that is stored in the database. Encrypting data-at-rest prevents such data leakages.

I am not going to say it’s too simple to, because I don’t want from Listeners to kick me; but guys for me It’s simple sorry, Integrity talking about which users have to be given what permissions in the database For example, data related to employee information is stored in a database. An employee may have permission for viewing the records and altering only part of information like his contact details, whereas a person in the human resources department will have more privileges.

To make sure everything is going to be ok just following these steps:

  • Change the password, once the database is installed.
  • Policies to set strong passwords have to be enforced. A good idea is to have a policy of changing the passwords once per a month.
  • Does your company have multiple database administrators? If yes, segregate the duties
    among these database administrators. 

Simple steps (again simple) save you and your company. 

Now last but I am not finished yet


Databases must not have unplanned downtime, to ensure this, following steps have to be taken:

  • To ensure high availability, usage of database clusters is recommended. 
  • Databases should be secured against security vulnerabilities.
  • Backup the data at periodic intervals to ensure data recovery in case of application issues.

As we all know there’s nothing perfect so when we talk about Security, that mean we talk about Threats and attackers. With the increase in usage of databases, the frequency of attacks against those databases has also increased; Database attacks are an increasing trend these days. What is the reason behind database attacks? One reason is the increase in access to data stored in databases. When the data is been accessed by many people, the chances of data theft increases. In the past, database attacks were prevalent, but were less in number as hackers hacked the network more to show it was possible to hack and not to sell proprietary information. Another reason for database attacks is to gain money selling sensitive information, which includes credit card numbers, Social Security Numbers, etc. We previously defined database security and talked about common database security concepts. Now let’s look at the various types of threats that affect database security.

Thank You 
Written By : Osama Mustafa 
Consider this as part one since i will post another one related to this .

Backup an entire hard disk using dd command

The ‘ dd ‘ command is one of the original Unix utilities and should be in everyone’s tool box. It can strip headers, extract parts of binary files and write into the middle of floppy disks; it is used by the Linux kernel Makefiles to make boot images. It can be used to copy and convert magnetic tape formats, convert between ASCII and EBCDIC, swap bytes, and force to upper and lowercase. 

# dd –help

full hard disk copy

dd if=/dev/hdx of=/dev/hdy
dd if=/dev/hdx of=/path/to/image
dd if=/dev/hdx | gzip > /path/to/image.gz

Hdx could be hda, hdb etc. In the second example gzip is used to compress the image if it is really just a backup.  

Restore Backup of hard disk copy

dd if=/path/to/image of=/dev/hdx

gzip -dc /path/to/image.gz | dd of=/dev/hdx  

MBR backup

In order to backup only the first few bytes containing the MBR and the partition table you can use dd as well.

dd if=/dev/hdx of=/path/to/image count=1 bs=512 

MBR restore

dd if=/path/to/image of=/dev/hdx

Add “count=1 bs=446” to exclude the partition table from being written to disk. You can manually restore the table.

 “All This Information was taken from the other site , just for information to take hard-disk backup , it will be useful to use it with Oracle ”

thank you 
Osama mustafa  


hi All ,

All of us using backup techniques maybe it will be different but the aim is one , saving your data
some error could be appear while you doing backup but everything can be solved one of this errors
deadlock error

but what is this mean ?

deadlock : situation where in two or more competing actions are each waiting for the other to finish

so when you have two backup not necessary backup btw it could be session , backup-1 will wait backup-2 and vice verse …

In rman backup its Bug 6830296 accroding to meta-link ID 779095.1

Solution is :

In Rman catalog database create the following indexes :-

CREATE INDEX tfatt_i_sck on tfatt(start_ckp_key)
CREATE INDEX tfatt_i_eck on tfatt(end_ckp_key)


Check for availability of one off patch using the following link Patch 6830296

thank you all