Oracle RMAN备份与恢复学习笔记

rman target nocatlog 不使用恢复目录
shutdown immediate
startup mount
sql 'alter database open';
run {host "dir f:\oracle";}
report schema;目标数据库表空间的信息
list baskup of database;备份集列表
list backup of datafile 3;
list backup of archivelog 3;
crosscheck backup;检查备份情况
list backup 1;检查第一个备份集

show all; rman配置参数

configure defualt device type to disk;

shutdown immediate;
exit;

rman target nocatlog;

startup mount;

backup database format='f:\oracle\bak\%d_%s.dbf';备份整个数据库

sql 'alter database open';打开数据库

list backup of database;查看备份集

list backup of 3;查看备份集的3号文件

数据丢失情况下的恢复:
startup
startup mount
restore datafile 4;
recover
sql'alter database open';

不完全恢复(做完后必须进行一次全备份);
create tableaa (no number)    tablespace uses;
insert into aa values(1);
alter system switch logfile;归档
select * from aa;
select current_scn from v$database;
select to_char(sysdata,'yyyy-mm-dd hh24:mi:ss') from dual;
drop table aa;误操作删除
select * from aa;
shutdown immediate
startup mount;
set nls_data_format=yyyy-mm-dd hh24:mi:ss
rman target nocatlog;

run {
set until time='2007-06-14 16:23:12';
restore database;
recover database;
}
从最近的备份文件里去找进行恢复

sql 'alter database open resetlogs';
shutdown immediate
做全备份:
startup mount
run{
backup database format='数据备份文件的路径';
}
list backup of database
report obsolete redun
delete obsolete删除过期的备份文件

恢复目录必须有两个数据库:
create tablespace thrman datafile'f:\thrman.dbf' size 50m;
create user bkman identified by bkman default tablespace thrman;
grant connect,recovery_catalog_ower to bkman;
alter user bkman unlimited on thrman;
conn
select object_name, object_type from user_objects;
create catalog tablespace thrman;
select object_name, object_type from user_objects order by object_type;
注册数据库:target
exit;
rman catalog target
register database;

show parameter undo
select ora_rowscn,no from aa;

alter table aa enable row movement;
flashback table aa to timestamp to_timestamp('2007-06-14 17:10:10')

create table aa as select * from dept;
select * from aa;
select ora_rousen,depton from aa;
insert into
delete aa where deptno=80;

alter table aa enable row movement;
flalshback table aa to scn 606677;
select * from aa;
select scn_to_timestamp(ora_rowsen).deptno from aa;

select * from tab;
select * from user_recyclebin;
drop table aa;
select * from tab;
show recyclebin;
flashback table aa to before drop;
show recyclebin
select * from recyclebin;
select * from aa;

清空回收站
drop table aa;
show recyclebin;
purge table aa;
purge recyclebin;

逻辑备份
exp imp

数据泵
expdp
create directory db_dmp as 'c:\oracle\archiver';创建目录
grant read,write on directory db_dmp to scott;赋权限
expdp directory=db_dmp dumpfile=tab.dmp tables=dept.emp

select * from tab;
drop table emp;
select * from emp;

impdp directory=db_dmp dumpfile=tab.dmp tables=emp;

select * from emp;

SQL优化
set time on
show user
select * from emp a where a.deptno not in
( select deptno from dept where deptno=10);

select * from emp a where not exits( select 'x' from emp h where b.deptno=a.deptno    and b.deptno=10);

执行计划C:\oracle\db_1\RDBMS\ADMIN\utlxplan.sql执行脚本文件
explain plan for select * from emp;
desc id, plan_id,cost,cpu_cost,io_cost from plan_table;