Pro Oracle Database 11g Administration (75 page)

3. Export the table.

4. Import the table into the production database.

This process can be time-consuming and resource-intensive. It requires extra server resources as well as time and effort from a DBA.

Oracle introduced the Flashback feature in recent versions; it lets you quickly restore from an accidentally dropped table. Oracle applies the term
flashback
to several different database features, which can be confusing. In regard to flashing back tables, Oracle provides two distinctly different types of flashback operations:

• FLASHBACK TABLE TO BEFORE DROP quickly undrops a previously dropped table.

This feature uses the recycle bin.

• FLASHBACK TABLE flashes back to a recent point in time to revert the effects of undesired Data Manipulation Language (DML) statements. You can flash back to an SCN, timestamp, or restore point.

Oracle introduced FLASHBACK TABLE TO BEFORE DROP to allow you to quickly recover a dropped table.

As of Oracle Database 10
g
, when you drop a table, if you don’t specify the PURGE clause, Oracle doesn’t drop the table—instead, the table is renamed. Any tables you drop (that Oracle actually renames) are placed into a logical container named the
recycle bin
. The recycle bin provides you with an efficient way to view and manage dropped objects.

■
Note
To use the Flashback Table feature, you don’t need to implement an FRA. Nor do you need Flashback Database enabled.

The FLASHBACK TABLE TO BEFORE DROP operation only works if your database has the recycle bin feature enabled (which it is by default). You can check the status of the recycle bin as follows: SQL> show parameter recyclebin

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string on

451

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

FLASHBACK TABLE TO BEFORE DROP

When you drop a table, if you don’t specify the PURGE clause, Oracle renames the table with a system-generated name. Because the table isn’t really dropped, you can use FLASHBACK TABLE TO BEFORE DROP to instruct Oracle to rename the table with its original name. Here’s an example. Suppose the INV table is accidentally dropped:

SQL> drop table inv;

Verify that the table has been renamed by viewing the contents of the recycle bin: SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

INV BIN$jmx7BIkFlYrgQAB/AQBIgg==$0 TABLE 2010-08-22:10:36:11

The SHOW RECYCLEBIN statement only shows tables that have been dropped. To get a more complete picture of renamed objects, query the RECYCLEBIN view:

select

object_name

,original_name

,type

from recyclebin;

Here’s the output:

OBJECT_NAME ORIGINAL_NAME TYPE

------------------------------ -------------------- -------------------------

BIN$jmx7BIkFlYrgQAB/AQBIgg==$0 INV TABLE

BIN$jmx7BIkElYrgQAB/AQBIgg==$0 INV_TRIG TRIGGER

BIN$jmx7BIkDlYrgQAB/AQBIgg==$0 INV_PK INDEX

In this output, the table also has a primary key that was renamed when the object was dropped. To undrop the table, do the following:

SQL> flashback table inv to before drop;

This restores the table to its original name. This statement doesn’t restore the index to its original name, though:

SQL> select index_name from user_indexes where table_name='INV';

INDEX_NAME

------------------------------

BIN$jmx7BIkDlYrgQAB/AQBIgg==$0

In this scenario, you have to rename the index:

SQL> alter index "BIN$jmx7BIkDlYrgQAB/AQBIgg==$0" rename to inv_pk; You have to rename any trigger objects in the same manner. If referential constraints were in place before the table was dropped, you must manually re-create them.

If for some reason you need to flash back a table to a different name than the original name, you can do so as follows:

SQL> flashback table inv to before drop rename to inv_bef;

452

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Flashing Back a Table to a Previous Point in Time

The Flashback Table feature that flashes back to a previous point in time is completely different from FLASHBACK TABLE TO BEFORE DROP. Flashing back a table to a previous point in time uses information in the undo tablespace. The point in time in the past depends on your undo tablespace retention period, which specifies the minimum time that undo information is kept.

If the required flashback information isn’t in the undo tablespace, you receive an error such as this: ORA-01555: snapshot too old

In other words, to be able to flash back to a point in time in the past, the required information in the undo tablespace must not have been overwritten.

FLASHBACK TABLE TO SCN

Suppose you’re testing an application feature, and you want to quickly restore a table back to a specific SCN. As part of the application testing, you record the SCN before testing begins: SQL> select current_scn from v$database;

CURRENT_SCN

-----------

17879789

You perform some testing and then want to flash back the table to the SCN previously recorded.

First, ensure that row movement is enabled for the table:

SQL> alter table inv enable row movement;

SQL> flashback table inv to scn 17879789;

The table should now reflect transactions that were committed as of the historical SCN value specified in the FLASHBACK statement.

FLASHBACK TABLE TO TIMESTAMP

You can also flash back a table to a prior point in time. For example, to flash back a table to 15 minutes in the past, first enable row movement, and then use FLASHBACK TABLE:

SQL> alter table inv enable row movement;

SQL> flashback table inv to timestamp(sysdate-1/96) ;

The timestamp you provide must evaluate to a valid format for an Oracle timestamp. You can also explicitly specify a time as follows:

SQL> flashback table inv to timestamp

to_timestamp('22-aug-10 12:07:33','dd-mon-yy hh:mi:ss');

FLASHBACK TABLE TO RESTORE POINT

A
restore point
is a name associated with a timestamp or SCN in the database. You can create a restore point that contains the current SCN of the database as follows:

SQL> create restore point point_a;

453

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Later, if you decide to flash back a table to that restore point, first enable row movement: SQL> alter table inv enable row movement;

SQL> flashback table inv to restore point point_a;

The table should now contain transactions as they were at the SCN associated with the specified restore point.

Flashing Back a Database

The Flashback Database feature allows you to perform an incomplete recovery to a point in time in the past. Flashback Database uses information stored in flashback logs; it doesn’t rely on restoring database files (like a cold backup, hot backup, or RMAN). In some situations, Flashback Database can restore your database to a point in time in the past much more quickly than performing a user-managed incomplete recovery (which requires you to copy datafiles from the backup).

■
Tip
Flashback Database isn’t a substitute for a backup of your database. If you experience a media failure with a datafile, you can’t use Flashback Database to flash back to before the failure. If a datafile is damaged, you have to restore and recover using a physical backup (hot, cold, or RMAN).

The Flashback Database feature may be desirable in situations where you want to consistently reset your database to a point in time in the past. For example, you may want to periodically set a test or training database back to a known baseline. Or you may be upgrading an application and, before making large-scale changes to the application database objects, mark the starting point. After the upgrade, if things don’t go well, you want the ability to quickly reset the database back to the point in time before the upgrade took place.

There are several prerequisites for Flashback Database:

• The database must be in archivelog mode.

• You must be using an FRA.

• The Flashback Database feature must be enabled.

See the earlier sections in this chapter about enabling archivelog mode and using the FRA. You can verify the status of these features using the following SQL*Plus statements: SQL> archive log list;

SQL> show parameter db_recovery_file_dest;

To enable the Flashback Database feature, alter your database into flashback mode as shown: SQL> alter database flashback on;

■
Note
In Oracle Database 10
g
, the database must be in mount mode to enable Flashback Database.

You can verify the flashback status as follows:

454

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

After you enable Flashback Database, you can view the flashback logs in your FRA with this query: select

name

,log#

,thread#

,sequence#

,bytes

from v$flashback_database_logfile;

The range of time you can flash back is determined by the DB_FLASHBACK_RETENTION_TARGET

parameter. This specifies the upper limit in minutes of how far your database can be flashed back.

You can view the oldest SCN and time you can flash back your database by running the following SQL:

select

oldest_flashback_scn

,to_char(oldest_flashback_time,'dd-mon-yy hh24:mi:ss')

from v$flashback_database_log;

If you need to disable Flashback Database for any reason, you can turn it off as follows: SQL> alter database flashback off;

You can use either RMAN or SQL*Plus to flash back a database. You can specify a point in time in the past using one of the following:

• SCN

• Timestamp

• Restore point

• Last RESETLOGS operation (works from RMAN only)

This example creates a restore point:

SQL> create restore point flash_1;

Next, the application performs some testing, after which the database is flashed back to the restore point so that a new round of testing can begin:

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point flash_1;

SQL> alter database open resetlogs;

At this point, your database should be transactionally consistent as it was at the SCN associated with the restore point.

455

CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY

Summary

This chapter covers B&R techniques that aren’t taught or used much anymore. Most DBAs are (and should be) using RMAN for their Oracle B&R requirements. However, it’s critical for you to understand how cold backups and hot backups work. You may find yourself consulting in a shop where they’ve implemented old technology and now need you to restore and recover their database or assist them in migrating to RMAN. In these scenarios, you must fully understand the older backup technologies.

This chapter also discussed restore and recovery techniques using cold and hot backups.

Understanding what happens at each step and why the step is required is vital for complete knowledge of the Oracle B&R architecture. This awareness translates into key troubleshooting skills when you’re using Oracle tools such as RMAN (B&R) and Data Guard (disaster recovery, high availability, and replication).

Now that you have an in-depth understanding of Oracle B&R mechanics, you’re ready to investigate RMAN. The next several chapters in this book examine how to configure and use RMAN for production-strength B&R.

456

CHAPTER 17 ■ CONFIGURING RMAN

PL/SQL packages:
RMAN uses two internal PL/SQL packages (owned by SYS) to perform B&R tasks: DBMS_RCVMAN and DBMS_BACKUP_RESTORE. DBMS_RCVMAN