oracle导入导出学习(4)

and seg_owner='SCOTT'; SCN

---------- SQL_REDO

---------------------------------------------------------------------------------------------------- 2274300

drop table scott.t01 purge;

在备用节点还原所有数据文件和控制文件:

select 'alter database rename file '||chr(39)||name||chr(39)||' to '||chr(39)||'/test'||substr(name,instr(name,'/',-1))||chr(39)||';' from

(select name from v$datafile union all

select name from v$tempfile union all

select member from v$logfile);

恢复数据库到灾难产生时间点:

recover database using backup controlfile until change 2274300;

select 'scp '||name||' 192.168.2.21:/test/' from v$archived_log where sequence# between 29 and 39;

select 'cp '||name||' /test/' from v$archived_log where sequence# between 29 and 39;

使用resetlogs打开数据库:

alter database open resetlogs; exp imp

--------------------------------------------------------------------- 不完全恢复，不需要还原所有的数据文件，用哪些数据文件就还原哪些数据文件! scp control01.ctl system01.dbf undotbs01.dbf tbs02.dbf oracle@192.168.2.21:/test/

将没有还原的文件offline for drop:

alter database datafile 3 offline for drop;

恢复数据库到灾难产生时间点:

recover database using backup controlfile until change 2274300;

使用resetlogs打开数据库:

alter database open resetlogs; exp imp

--------------------------------------------------------------------- 被动的不完全恢复: 基于cancel的不完全恢复 1.归档日志丢失(断档，断在中间)

2.联机日志的current组丢失(也属于断档，断在最后)

ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3

SQL> alter database open resetlogs; alter database open resetlogs *

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/alvin/system01.dbf'

recover database using backup controlfile until cancel;

alter database open resetlogs;

alter system checkpoint; alter system switch logfile;

--------------------------------------------------------------------- 使用resetlogs前的备份，还原恢复resetlogs后的数据 \

--------------------------------------------------------------------- 重做控制文件的备份还原和恢复(有所有的日志), 恢复到最后使用noresetlogs方式重建控制文件！

alter database backup controlfile to '/home/alvin/hotbk/ctl2.bak'; ---------------------------------------------------------------------

闪回数据库

将数据库所有的数据恢复到某一个时间点，这个数据库版本的倒退 需要开启闪回恢复（增加数据库的压力） select flashback_on from v$database;

FLASHBACK_ON

------------------ NO

shut immediate

alter database flashback on； alter database open;

show parameter flashback；

show parameter db_recovery_file_dest flashback database to scn xxxxx；

******************************************************************************************************************

rman --> recover manager

查看数据库的物理结构: report schema;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

镜像备份控制文件:

copy current controlfile to '/home/alvin/rmanbk/control01.ctl'; 查看控制文件镜像备份: list copy of controlfile; 还原控制文件:

RMAN> restore controlfile from '/home/alvin/rmanbk/control01.ctl'; 装载数据库:

RMAN> alter database mount; 恢复控制文件:

RMAN> recover database; 打开数据库:

RMAN> alter database open resetlogs;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

镜像备份数据文件:

copy datafile 4 to '/home/alvin/rmanbk/users01.dbf'; 查看数据文件镜像备份: list copy of datafile 4; 还原数据文件:

RMAN> restore datafile 4; 恢复数据文件:

RMAN> recover datafile 4;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~

使用备份集管理rman备份 备份spfile:backup piece

RMAN> backup spfile format '/home/alvin/rmanbk/spfilealvin.ora'; 查看备份spfile:

RMAN> list backup of spfile; 使用rman启动伪实例(DUMMY): RMAN> startup nomount 还原spfile:

RMAN> restore spfile from '/home/alvin/rmanbk/spfilealvin.ora'; 停止伪实例(DUMMY): RMAN> shutdown 启动真的实例: RMAN> startup

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

备份控制文件:

RMAN> backup current controlfile format '/home/alvin/rmanbk/ctl.bkp'; 查看备份控制文件:

RMAN> list backup of controlfile; 还原控制文件:

RMAN> restore controlfile from '/home/alvin/rmanbk/ctl.bkp'; 装载数据库:

RMAN> alter database mount; 恢复控制文件:

RMAN> recover database; 打开数据库:

RMAN> alter database open resetlogs;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

备份数据文件:

RMAN> backup datafile 6 format '/home/alvin/rmanbk/demo01.bkp'; 查看数据文件的备份:

RMAN> list backup of datafile 6; 还原数据文件：

RMAN> restore datafile 6; 恢复数据文件:

RMAN> recover datafile 6;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

还原和恢复数据文件：rman语句块的使用 run{

restore datafile 6; recover datafile 6;

}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

向新的位置还原和恢复数据文件： run{

sql 'alter tablespace demo offline';

set newname for datafile 6 to '/opt/oracle/oradata/alvin/demo01.dbf'; restore datafile 6; switch datafile 6; recover datafile 6;

sql 'alter tablespace demo online'; }

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

校验备份片的有效性:

RMAN> crosscheck backup; 校验镜像备份的有效性: RMAN> crosscheck copy; 删除过期的备份:

RMAN> delete n …… 此处隐藏：3567字，全部文档内容请下载后查看。喜欢就下载吧 ……