11g OCM备考8——catalog和flashback

最新推荐文章于 2025-05-03 09:07:35 发布

cijiayuan5672

最新推荐文章于 2025-05-03 09:07:35 发布

阅读量146

点赞数

文章标签：数据库

1. catalog 配置

Create a rman recovery catalog in woods database, the recovery catalog store in tablespace RC_DATA with a file size 200m,

make sure the recovery catalog owner is RC_ADMIN password is same as username,

backup woods3 database in ‘/u01/app/oracle/backup’ and keep forver

创建表空间和用户

SQL> create tablespace RC_DATA datafile '/u01/app/oracle/oradata/woods/rc_data.dbf' size 200m ;

SQL> create user RC_ADMIN identified by RC_ADMIN temporary tablespace temp default tablespace RC_DATA quota unlimited on RC_DATA;

SQL> grant recovery_catalog_owner to RC_ADMIN;

创建catalog和注册

[oracle@ocm ~]$ rman catalog RC_ADMIN/RC_ADMIN;

RMAN> create catalog;

recovery catalog created

[oracle@ocm2 ~]$ rman target / catalog RC_ADMIN/RC_ADMIN@woods

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

备份：

woods3要是归档的

/u01/app/oracle/backup路径要建

RMAN> backup database format '/u01/app/oracle/backup/%U' keep forever;

2. flashback

（1）开闪回和设置参数

Turn on Flashback Database for woods database

Configure a flash recovery area of 4GB;

Put your flash recovery area in /home/oracle/flash；

Configure the flashback retention 48 hours

（2）flashback table to before drop

user scott create table a from table scott.emp

drop table a

user scott create table a from table scott.dept

drop table a

flashback table earlier a

flashback table later a rename to b

（3）flashback database to restore point

create a guaranteed restore point before_truncate;

truncate table hr.job_history

use flashback database to restore the hr.job_history table rows;

（4）基于时间点的闪回

(1)

[oracle@ocm ~]$ mkdir -p /home/oracle/flash

SQL> alter system set db_recovery_file_dest_size=4g;

SQL> alter system set db_recovery_file_dest='/home/oracle/flash';

SQL> alter system set db_flashback_retention_target=2880;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

SQL> alter database open ;

(2)

SQL> create table a as select * from emp;

SQL> drop table a;

SQL> create table a as select * from emp;

SQL> drop table a;

SQL> select OBJECT_NAME,DROPTIME from recyclebin;

OBJECT_NAME DROPTIME

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

BIN$MPC6Jqj5WOLgU6ABqMDTKw==$0 2016-04-20:13:49:16

BIN$MPC6Jqj6WOLgU6ABqMDTKw==$0 2016-04-20:13:49:22

SQL> flashback table "BIN$MPC6Jqj5WOLgU6ABqMDTKw==$0" to before drop;

SQL> flashback table " BIN$MPC6Jqj6WOLgU6ABqMDTKw==$0" to before drop rename to b;

（3）

SQL> create restore point before_truncate guarantee flashback database;

Restore point created.

SQL> select count(*) from hr.job_history;

COUNT(*)

----------

SQL> truncate table hr.job_history;

Table truncated.

SQL> select count(*) from hr.job_history;

COUNT(*)

----------

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point before_truncate;

Flashback complete.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;

Database altered.

SQL> select count(*) from hr.job_history;

COUNT(*)

----------

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2016-04-20 14:12:09

SQL> select count(*) from hr.job_history;

COUNT(*)

----------

SQL> truncate table hr.job_history;

Table truncated.

SQL> select count(*) from hr.job_history;

COUNT(*)

----------

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1121554432 bytes

Fixed Size 1344428 bytes

Variable Size 771755092 bytes

Database Buffers 335544320 bytes

Redo Buffers 12910592 bytes

Database mounted.

SQL> flashback database to timestamp to_timestamp('2016-04-20 14:12:09','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;

Database altered.

SQL> select count(*) from hr.job_history;

COUNT(*)

----------

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

转载于:http://blog.itpub.net/30935525/viewspace-2084705/