Recover One

Recover one table from a database backup with Oracle Recovery Manager in Oracle Database 12c.

By Arup Nanda

March/April 2015

Everyone understands the occasional need to recover a database from backup. Although it may sound complicated, it’s actually a relatively simple task with the Oracle Recovery Manager (Oracle RMAN) feature of Oracle Database. A much bigger challenge is recovering a specific table (or a partition of a table) from backup while keeping the rest of the database intact. You often need this more granular recovery when a table is dropped mistakenly or when a copy of the table as of a previous point in time needs to be reinstated after a lot of changes. In some cases, you may be able to recover the table with flashback technologies, but if you can’t, you can follow this general series of manual steps to recover a specific dropped table without affecting the operation of the database:

1. Create a temporary database instance.
2. Restore all the required tablespaces from the backup to this temporary database.
3. Perform a point-in-time recovery of these tablespaces to just before the table drop.
4. Export the specific table (or partition).
5. Import the table to the original database.
6. Drop the temporary database.

This whole process is complicated and error-prone. And as if that were not enough, often you have no choice but to perform the recovery on the same server where the source database is running, therefore increasing the possibility that your activity will overwrite the original database files and corrupt the original database—a significant risk. So the task of performing a single table recovery demands thorough planning, likely by a senior DBA experienced in this specific activity.

Not anymore. With Oracle RMAN in Oracle Database 12c, you can recover a single table or a partition of a table with just one command. Behind the scenes, that Oracle RMAN command performs all the necessary complicated and risky tasks—sparing you the detailed planning and execution. The process is simple enough to be carried out by just about anyone familiar with Oracle RMAN. In this article, I will describe various scenarios for recovering a specific table or partition of a backup while keeping the rest of the database unaffected and explain how the recovery works under the covers. I will also reference the six general manual recovery steps listed above in the explanations of the Oracle RMAN actions. Note that the steps in this article assume that you have a backup of the database and that backup is available.

Your task is to recover a table—T1, accidentally dropped earlier —in the ACME schema. To complete the recovery, you need to know three things besides the table name:

1. Because this is a point-in-time recovery, you need to know the ending time of the recovery. This time should be just before the drop. In this case, the time is 20:36:00 on December 11.
2. You need to know the tablespace (or tablespaces, for a partitioned table) where the table was originally located.
3. You need to know where to create the temporary database. Because the recover command needs to create a temporary—also known as an auxiliary—database, you will need to designate a space where this database can be created. It will be dropped later, so it doesn’t have to be a permanent space, but it has to be large enough to store all the necessary tablespaces required for recovery. Note that the recovery process will restore the original tablespace(s) of the table, including the SYSTEM, SYSAUX, and undo tablespaces. The process will not overwrite the existing files in the original database; it will create a new auxiliary database and restore the tablespace(s) from the original database backup to the temporary space. For this recovery, the temporary space is the DATA2 disk group. It can be any location—a directory or a disk group—as long as it is available to the database server.

With the time, original tablespace location, and temporary space location information, invoke Oracle RMAN and run the following at the prompt to recover the T1 table:

recover table acme.t1
until time "to_date('2014-12-11:20:36:00',
'yyyy-mm-dd:hh24:mi:ss')"
auxiliary destination '+DATA2';

Note that instead of recovering by specifying a time, you can recover by specifying a system change number (SCN) in the UNTIL clause:

until scn 123456

If you use the Oracle Multitenant option with multiple pluggable databases (PDBs), it’s possible that the schema (ACME in this example) exists on many PDBs and the ACME.T1 table is not unique. If that’s the case, provide another clause to specify the PDB name where this table was located:

recover table acme.t1
of pluggable database pdb1
until time "to_date(‘2014-12-11:20:36:00',
'yyyy-mm-dd:hh24:mi:ss')"
auxiliary destination '+DATA2';

The recover command produces a very long output. Rather than showing the entire output as one massive code listing, I have broken it into smaller chunks in different listings. I have also truncated most of the output, leaving only those parts necessary for understanding the table recovery process. Listing 1 shows the first part of the recover command output, which demonstrates typical Oracle RMAN activities such as channel creation. Note the line in the output in boldface; it shows that Oracle RMAN creates an automatic database instance with the system identifier (SID) ‘pDFk’. (This process is the equivalent of manual step 1 at the beginning of this article). This SID name is a random four-character string unlikely to coincide with a real database SID on that server. After several lines, the output reports that an instance was created automatically with the initialization parameters shown.

Code Listing 1: Part 1 of recover output

Starting recover at 11-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 instance=cdborcl1 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to 
specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
… output truncated …
Creating automatic instance, with SID='pDFk'
initialization parameters used for automatic instance:
db_name=CDBORCL
… output truncated …
starting up automatic instance CDBORCL
Oracle instance started
… output truncated …
Automatic instance created

After it creates the instance with the SID ‘pDFk’, the recover command creates a script in memory, as shown in Listing 2. This script restores the control file from the backup for this newly created instance and mounts it. Because the instance is new but the backups are from an existing database, the automatic instance is mounted as a clone database of the original database (shown in the boldface “sql statement” line in the output). Then the Oracle RMAN recover command restores the datafiles necessary for this recovery (shown in boldface in the output). Oracle RMAN automatically determines the datafiles needed—you don’t have to provide them. (This process is the equivalent of manual step 2 at the beginning of this article.)

Code Listing 2: Part 2 of recover output

contents of Memory Script: 
{
set until time "to_date('2014-12-11:20:36:00','yyyy-mm-dd:hh24:mi:ss')";
restore clone controlfile;
sql clone 'alter database mount clone database';
sql 'alter system archive log current';
}
executing Memory Script
... output truncated ...
Finished restore at 11-DEC-14
sql statement: alter database mount clone database
... output truncated ...
contents of Memory Script:
{
set until  time "to_date('2014-12-11:20:36:00','yyyy-mm-dd:hh24:mi:ss')";
set newname for clone datafile  1 to new;
... output truncated ...
switch clone tempfile all;
restore clone datafile  1, 4, 8, 9, 10, 3, 11, 12;
switch clone datafile all;
}
executing Memory Script
... output truncated …

After the database restore, the next recovery step is to bring the files up to a point in time. Part 3 of the Oracle RMAN recover command output (shown in Listing 3) shows the recovery continuing for the clone database until the boldface line that confirms that the recovery is finished. At this point, the database is opened, and because this recovery is for a table in a specific PDB, that PDB—named PDB1—is recovered, as shown in the last line of the output in Listing 3. (This process is the equivalent of manual step 3 at the beginning of this article.)

Code Listing 3: Part 3 of recover output

contents of Memory Script:
{
set until  time "to_date('2014-12-11:20:36:00','yyyy-mm-dd:hh24:mi:ss')";
sql clone "alter database datafile  1 online";
... output truncated ...
sql clone 'PDB1' "alter database datafile  12 online";
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "UNDOTBS2", 
"UNDOTBS3", "UNDOTBS4", "SYSAUX", "PDB1":"SYSTEM", "PDB1":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
... output truncated ...
sql statement: alter database datafile  12 online
Starting recover at 11-DEC-14
... output truncated ...
starting media recovery
... output truncated ...
archived log file name=+DATA2/CDBORCL/ARCHIVELOG/2014_12_11/thread_1_
seq_312.308.866061509 thread=1 sequence=312
... output truncated ...
Finished recover at 11-DEC-14
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database  PDB1 open read only';
}
executing Memory Script
... output truncated ...
contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files = 
  ''+DATA2/CDBORCL/CONTROLFILE/current.315.866068089'' comment=''RMAN set'' 
scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
sql clone 'alter database mount clone database';
}
executing Memory Script
... output truncated ...
sql statement: alter pluggable database  PDB1 open

At this point, the temporary database is ready. The next task is to export the T1 table in that temporary database, which is exactly what Oracle RMAN does. As shown in the continued output in Listing 4, the recover command creates a directory object—called TSPITR_DIROBJ_DPDIR—in the auxiliary space designated earlier. Then the recover command exports the T1 table, as shown in the first boldface line in Listing 4, to a dump file named TSPITR_EXP_pDFk_igrf. (This process is the equivalent of manual step 4 at the beginning of this article.)

Code Listing 4: Part 4 of recover output

contents of Memory Script:
{
sql clone 'PDB1' "create or replace directory TSPITR_DIROBJ_DPDIR as 
''+DATA2''";
}
executing Memory Script
... output truncated ... 
Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_pDFk_igrf":  
... output truncated ...   
EXPDP> . . exported "ACME"."T1"      8.518 MB   73604 rows
... output truncated ...   
Oracle instance shut down
Performing import of tables...
... output truncated ...   
   IMPDP> . . imported "ACME"."T1"    8.518 MB   73604 rows
... output truncated ...   
Import completed
... output truncated ...   
Automatic instance removed
... output truncated ...   
Finished recover at 11-DEC-14
Recovery Manager complete.

After the export, the recover command imports the dump file into the primary source database, using IMPDP, as shown in the output in Listing 4. (The “Import completed” line confirms the action.) At this stage, the T1 table—from backup—is now part of the primary source database. (This process is the equivalent of manual step 5 at the beginning of this article.) The temporary clone database and instance (with the ‘pDFk’ SID) created earlier have served their purpose and are no longer needed, so as a final act, the Oracle RMAN recover command drops them as shown in the last lines of the output in Listing 4. (This process is the equivalent of manual step 6 at the beginning of this article.)

Mission accomplished.

Looking at the complete Oracle RMAN recover process, the activities conducted by Oracle RMAN are no different from what you would have done to recover a single table from backup, but all the tasks you would have done manually (shown as manual steps at the beginning of the article) were done automatically by Oracle RMAN. The Oracle RMAN recover process not only makes the recovery process simple to the point of being trivial, but it also makes it significantly less risky, less error-prone, and less time-consuming—meaning that it can be done by just about anyone incredibly quickly, with no lead time.

If you attempt to recover a table that already exists in the primary source database, you will get the following types of errors:

RMAN-05063: Cannot recover 
specified tables
RMAN-05112: table “ACME”.”T1” 
already exists

You can, of course, drop the T1 table in the primary source database before you start the Oracle RMAN recover command, but you can also keep the table. When you recover a table that still exists in the primary source database, you can rename it, using the REMAP TABLE clause. The following gives the recovered T1 table the new name T2:

recover table acme.t1
of pluggable database pdb1
until SCN 1234567
auxiliary destination ‘+DATA2’
remap table ACME.T1:T2

If you want to recover a specific partition of a table—not the entire table—you can use REMAP. Consider a case where a partition exists in the table. Instead of dropping the partition, you can easily create a new table to hold the contents of that partition. The following recovers partition P1 of the table PT1 but creates it as a new table named PT1_P1:

recover table acme.pt1:p1
of pluggable database pdb1
until scn 123456
auxiliary destination '+DATA2'
remap table ACME.PT1:P1:PT1_P1

Sometimes you may want to send the export dump file to someone or import it to another database—not the database the backup was from. For example, you may want to get a copy of a table from production as of some point in time for development debugging purposes. In such a case, you can use the NOTABLEIMPORT clause, which instructs the Oracle RMAN recover activities to skip the import portion:

recover table acme.t1
of pluggable database pdb1
until SCN 1234567
auxiliary destination '+DATA2'
notableimport;

The recover command output in this case is the same as the original example, except that the import portion is absent. The following line appears after the export:

Not performing table import after 
point-in-time recovery

The output does, however, show the name of the export dump file that was created by the recover command. For this example, the dump file is +DATA2/tspitr_Bmsq_94367.dmp. To import it into a database on a file system, you can copy the file to the file system with the ASMCMD utility:

$ asmcmd -p
ASMCMD [+] > cd DATA2
ASMCMD [+DATA2] > cp tspitr_Bmsq_94367.dmp 
/tmp/tspitr_Bmsq_94367.dmp
copying +DATA2/tspitr_Bmsq_94367.dmp -> 
/tmp/tspitr_Bmsq_94367.dmp

The tspitr_Bmsq_94367.dmp file is copied to /tmp. To import it, execute the following:

$ impdp acme/acme@pdb11 
cluster=no 
directory=tmp_dir 
dumpfile=tspitr_Bmsq_94367.dmp 

To confirm that the table exists, connect to the database as ACME, and execute the following:

SQL> select tname from tab; 
TNAME
————————
T1

In this article, you learned how to recover a single table or partition from backup by using just one Oracle RMAN command—recover. You can also use this recovery process to create a copy of the table (or partition) from a backup as of a previous point in time while the database is unaffected. This is useful for debugging scenarios in which you may want to compare the contents of a table across different time periods.