ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库归档以及flashbak操作

数据库归档以及flashbak操作

原创 Linux操作系统 作者:freshairpeng 时间:2009-02-25 15:42:03 0 删除 编辑

1:列出归档信息日志

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence         6

 

2:查看归档日志位置

SQL> select name,value from v$parameter where name =’log_archive_dest1’;

SQL>select name,value from v$parameter where name=’log_archive_format’;

SQL>select name,value from v$parameter where name=’db_recovery_file_dest’;

SQL> select value from v$parameter where name='db_recovery_file_dest';

SQL>show parameter db_recovery_file_dest;

Db_recovery_file_dest   string  /ora10gBase/flashback_recovery_area

Db_recovery_file_dest_size integer 2G

 

VALUE

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

/ora10gBase/flash_recovery_area

 

3: 将归档日志根据日期存放在不同子目录下

/oradata/[SID]/archivelog/[yyyy-mm-dd]/.arc

SQL>alter system set archive_log_format=’/oradata/[SID]/archivelog/[yyyy-mm-dd]/.arc

 

4:flashback 操作

启动flashback

加大UNDO_RETENTION的值86400

使用Flashback恢复一个被删除的表

设置数据库参数,

启动归档日志和闪回

» 设置需要闪回的表属性options

Enable Row Movement -> yes

闪回表

删除一个表中的记录delete from scott.dept,再闪回

执行Flashback Versions Query

修改一条记录,再闪回

SQL>shutdown immediate;

SQL> startup mount exclusive;

ORACLE instance started.

 

Total System Global Area  197132288 bytes

Fixed Size                  1218484 bytes

Variable Size              67110988 bytes

Database Buffers          125829120 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter system set recyclebin=on scope=both;--开启recyclebin

 

System altered.

 

SQL> alter system set db_flashback_retention_target=14400 scope=both;

 

System altered.

SQL> alter database archivelog;

 

Database altered.

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence           6

--修改undo_retention 20分钟

SQL> select 20*60*60 from dual;

 

  20*60*60

----------

     72000

 

SQL> alter system set undo_retention=72000 scope=both;

 

System altered.

--解锁用户scott 进行相关的增加,删除,更新操作

SQL> alter user scott identified by tiger account unlock;

 

User altered.

 

SQL> grant resource to scott;

 

Grant succeeded.

 

SQL> conn scott/tiger;

Connected.

 

SQL> alter user scott identified by tiger account unlock;

 

User altered.

 

SQL> grant resource to scott;

 

Grant succeeded.

 

SQL> conn scott/tiger;

Connected.

 

--进行flashback操作之前,需要先把row movement 打开

SQL>alter table dept enable row movement;

 

 

---进行更新删除操c

 

 

SQL> create table test(id number(4),name varchar2(30));

 

Table created.

 

SQL> insert into test values(1,'TEST');

 

1 row created.

 

SQL> insert into test values(2,'Fuck');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> drop table test;

 

Table dropped.

 

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST             BIN$Xkza01ABRArgQAB/AQARTw==$0 TABLE        2008-12-18:15:08:08

 

---recyclebin中查询被删除的数据信息

SQL> select * from "BIN$Xkza01ABRArgQAB/AQARTw==$0";

 

        ID NAME

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

         1 TEST

         2 Fuck

--recyvlebin中恢复被删除的表

SQL> flashback table test to before drop;

 

Flashback complete.

 

SQL> select table_name from user_tables;

 

TABLE_NAME

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

DEPT

EMP

BONUS

SALGRADE

TEST

--恢复后recyclebin中的信息也相应的消失啦

SQL> show recyclebin;

 

 

4:purge 表操作

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14766526/viewspace-557766/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Sqlldr操作
请登录后发表评论 登录
全部评论

注册时间:2009-02-25

  • 博文量
    69
  • 访问量
    246958