What do you do if some rows from a table in production get deleted. How to get them back?
Having sound knowledge on restore/recovery fundamentals is imperative for a Database Administrator (DBA) as they come across so many different scenarios when the possible work around seems quite unprecedented. In this blog, I will review and explain some rare restore/recovery scenarios that can be used as best practices.
Case 1: How to recover data in a table even after rows are deleted and committed.
What do you do if/when some rows from a table in production get deleted. How to get them back?
The 10g version of Oracle has come up with the functionality called the FLASHBACK that enables a DBA to get any table, or database or any particular query to resume in case of deletion. Let’s discuss the FLASHBACK TABLE a little more.
How far can you flashback a table in a past point in time? Is it infinite? The answer is no. One needs to understand the limitation of flashback table in order to do that you need to understand the database parameter undo_retention.
undo_retention is a database initialization parameter that defines the time in seconds until which ORACLE will keep undo data on undo segments before purging them. Whenever any DML statement is executed on a data block, in database, ORACLE always keeps the previous state of the data block inside its undo segments, and marks that undo segment as ‘ACTIVE’. Once the DML statement is committed or rolled back, ORACLE marks that undo segment as ‘UNEXPIRED’. The previous state of the data block will still remain inside ‘UNEXPIRED’ undo segments until it reaches the time as defined by undo_retention parameter (provided that the undo retention is guaranteed).
Now consider this scenario, developers have deleted all rows from table hz_cust_accounts_all approximately one hour ago, and now they want all of those rows back.
The first thing to do is to check how much the undo_retention value is set to.
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 14400
In this case, the undo retention is four hours. Now see if undo data is guaranteed i.e ORACLE will still preserve undo data until undo_retention period even if the undo segment is INACTIVE
SQL> select RETENTION from dba_tablespaces where TABLESPACE_NAME='APPS_UNDOTS1';
So this means that for four hours, the previous state of the table hz_cust_accounts_all will be preserved.
Let’s see currently how many rows are there inside the table.
SQL> select count(*) from hz_cust_accounts_all;
Now let’s see how many rows were there in the table before an hour, i.e. just before the rows got deleted.
SQL> select count(*) from HZ_CUST_ACCOUNTS_ALL as of timestamp TO_TIMESTAMP('2014-05-24 09:00:00', 'YYYY-MM-DD HH24:MI:SS');
As per the example, we had 288188 rows in the table that needed to be restored back.
The next statement will flashback the table until time '2014-05-24 09:00:00’ and that will eventually restore the rows back to the table.
SQL> flashback table HZ_CUST_ACCOUNTS_ALL to timestamp TO_TIMESTAMP('2013-09-24 09:00:00', 'YYYY-MM-DD Hh24:MI:SS');
Now select the existing row count in the table.
SQL> select count(*) from HZ_CUST_ACCOUNTS_ALL
The above steps helped to easily restore the deleted rows of a table.
Case 2: How to recover a corrupt datafile even if backups are not available?
In a production database, you may come across a situation when one or more of your datafiles have become corrupted. You are saved when you have a valid recent backup to restore the datafile from. But imagine a scenario when you don’t have a backup at all.
You can still recover the datafile or rather re-create the datafile if you have:
a. All archive logs generated after the creation of the datafile
b. A current controlfile that has the name of the damaged file in it
First thing is to create a new, empty datafile to replace the damaged datafile.
Suppose the datafile which is damaged and has no backup available is /u01/app/PROD/apps_st/data/A.dbf
Now create an empty file A1.dbf as below.
ALTER DATABASE CREATE DATAFILE ‘/u01/app/PROD/apps_st/data/A1.dbf’ as ‘/u01/app/PROD/apps_st/data/A.dbf’;
The above statement will look for the damaged datafile information in controlfile and in data dictionary to obtain the files original size, and then replace the file A.dbf with A1.dbf with a similar size. At this point the new datafile A1.dbf has no data in it.
Now recover the new datafile with all archivelogs available on the disk.
RECOVER DATAFILE ‘/u01/app/PROD/apps_st/data/A1.dbf’;
Once recovered, the database is good enough for operations.
Case 3: How to recover when all of your controlfiles are lost?
Yes, you can get your database operational, even if you lose all of your current controlfiles.
Let us understand how.
a. When the database is up & running, remove the controlfiles.
[oracle@localhost orcl]$ rm /u01/app/oracle/orcl/apps_st/data/control02.ctl /u01/app/oracle/orcl/apps_st/data/control02.ctl/control01.ctl
b. Now try to create a tablespace and this will throw an error and abort the database
SQL> create tablespace t1 datafile
'/u01/app/oracle/orcl/apps_st/data/t101.dbf' size 1M;
create tablespace t1 datafile '/u01/app/oracle/orcl/apps_st/data/t101.dbf'
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/orcl/apps_st/data/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
At this point the database is down (crashed). To recover the database you need to restore the backup controlfiles and open the database using RESETLOGS.
SQL> startup nomount;
ORACLE instance started.
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
If we try to open the database with the above command, it will throw an error as we lost the current controlfiles. We will have to open the database using RESETLOGS.
RMAN> alter database open;
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-03002: failure of alter db command at 06/11/2014 08:42:50
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> recover database;
RMAN> alter database open resetlogs;
More cases will be discussed in the next blog.