数据库归档以及flashbak

数据库归档以及flashbak

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 表操作

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值