首页 > 数据库 > Oracle > 备份恢复及闪回


原创 Oracle 作者:zhaojienju 时间:2007-11-02 11:18:57 0 删除 编辑

Backup and Recovery



Backup and Recovery

Perform backup
1. Configure the flashback recovery table
enable archive mode, enable flashback on, edit the flashback retention time

SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database flashback on
SQL>alter database open;

2. Enable block change tracking
3. Test disck backup
4. Backup the database
5. Review backup reports
6. Examine the flash recovery area usage
7. Scheduling RMAN scripts executions

Perform recovery of a data file
1. Delete a data file (need to offline the tablespace)
2. Perform a recovery
3. Place tablespace online

Flashback of the Database - the way to flashback the changes to a database, not only a table
1. Create a restore point, you can view it in v$restore_point
2. modify a table, maybe truncate a table or insert new columns
3. Perform recovery. When the database is shutdown and started in mount stat,
SQL>connect / as sysdba
SQL>flashback database to restore point "point1";
SQL>alter database open resetlogs;
Then you can check the database is restore to the point.

SQL> flashback database to restore point test_point;
flashback database to restore point test_point
ERROR at line 1:
ORA-38780: Restore point 'TEST_POINT' does not exist.

SQL> select SCN, NAME from v$restore_point;


803336 before_trunc

813382 test_point

SQL> flashback database to scn 813382
2 ;

Flashback complete.

A: use "test_point" instead of test_point


Q: 错把example表空间的datafile offline,执行online操作时遇到类似错误:

ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: 'C:X1.DBF'




SQL>startup mount
SQL>alter database datafile 'file' offline drop;
SQL>alter database open;



All files that are needed to completely recover a database from a media failure are part of the flash recovery area. The recovery related files that can be created in the flash recovery area include: archived redo log files, control files, backups created by Recovery Manager (RMAN), and flashback logs.

In previous releases of the Oracle server, the DROP command permanently removed objects from the database. Now when you drop a table and its dependent objects, it is placed in the recycle bin. It is not removed from the database until you empty the recycle bin. The extents allocated to the segment are not deallocated until you purge the object. You can restore these dropped objects from the recycle bin at any time.

1. Setup
enable the archive mode, enable the flashback on,
2. fill the recycle bin
create a new table then drop it so recycle bin will contain a table
3. Query tables in recycle bin
SQL>show recyclebin;
4. Do "flashback drop"
SQL>flashback table tableName to before drop;
5. Empty the Recycle bin
SQL>purge recyclebin;
SQL>purge table tableName; //purge table
SQL>purge user_recyclebin; //purge user's table
SQL>drop table tableName purge; //drop and purge table

Using Flashback Versions Query to Recover a Table to a Point in Time
The rows returned by the Flashback Versions Query are a history of changes of the rows across transactions.The Flashback Versions Query retrieves all committed occurrences of the rows.
1. update the jobs table
2. enable row movement on the jobs table
3. Recove the jobs table to a particular point in time
for jobs table, use "flashback version query"

·需要有flashback any table的系统权限或者是该表的flashback对象权限;
·必须保证该表row movement。

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

下一篇: 要看的文章


  • 博文量
  • 访问量