ITPub博客

首页 > 数据库 > Oracle > RMAN脚本

RMAN脚本

原创 Oracle 作者:wenjunheyixiao 时间:2016-03-16 20:03:33 0 删除 编辑

--实验1.认识SCN

select current_scn from v$database;----查看数据库当前的SCN

select dbms_flashback.get_system_change_number from dual;-----查看数据库当前的SCN,和上面的是一样的;

 

--实验2.实例恢复

2.1.判断是否需要实例恢复

select last_time from v$datafile;

mount状态后查看。

 

--实验3.介质恢复

1.v$datafile.checkpoint_change#,v$datafile.checkpoint_time(来自于控制文件)

2.v$datafile_header.checkpoint_change#(来自于数据文件)

 

 

select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

select GROUP#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;

 

------------关键数据文件

--备份数据文件

backup database

--切换日志,改变SCN

alter system switch logfie;

--恢复

restore tablespace system;

restore tablespace UNDOTBS1;

recover database;

--打开

alter database open;

 

------------普通数据文件

backup tablespace EXAMPLE;

alter system switch logfile;

alter database datafile 5 offline;

restore datafile 5;

recover datafile 5;

alter database datafile 5 online;

 

 

-------------在线日志文件(INACTIVE)

--------------同组------------

alter database drop logfile member '/u01/app/oracle/oradata/PROD1/redo01.log';

alter database add logfile member '/u01/app/oracle/oradata/PROD1/redo01.log';

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

rm redo02.log(INACTIVE或者其他状态但是不shutdown abort)

alter system archive log current;

shutdown immediate;

startup mount;

alter database clear unarchived logfile group 2;

alter database open;

 

-------------在线日志文件(CURRENT,ACTIVE)

backup database;

rm redo03.log(active并且shutdown abort)

startup mount

select group#,sequence#,status from v$log;

resotre database;

recover database until sequence 93;

alter database open resetlogs;

 

------------不完全恢复

create tablespace TBS_RMAN datafile '/u01/app/oracle/oradata/PROD1/tbs_rman01.dbf' size 100m autoextend off;

create user rman identified by user default tablespace TBS_RMAN account unlock;

grant resource,connect to rman;

 

conn rman/user

create table T9(ID number,NAME varchar(1000));

insert into T9(ID,NAME) values(1,'A');

commit;

alter system switch logfile;

 

insert into T9(ID,NAME) values(2,'B');

commit;

alter system switch logfile;

 

insert into T9(ID,NAME) values(3,'C');

commit;

alter system switch logfile;

 

shutdown immediate;

 

--查询restore后文件的ckpt change#

select file#,change# from v$recover_file;

--查询对应的sequence#

select sequence# from v$log_history where 1951119 between first_change# and next_change#-1;

 

list incarnation;

reset database to incarnation 7;

restore database;

recover database until sequence 16;

 

 

--控制文件

--1.DBID

alter system dump logfile '/u01/app/oracle/oradata/PROD1/redo01.log';

select value from v$diag_info where name='Default Trace File';

 

 

----控制文件

--1.自动备份

show control file autobackup;

backup database;

backup tablespace system;

 

--2.手动备份

backup as backupset current controlfile;

list backup of controlfile;

backup as copy current controlfile;

list copy of controlfile;

 

alter database backup controlfile to trace;

select value from v$diag_info where name='Default Trace File';

sed -n "/NORESETLOGS/,/REUSE AUTOEXTEND ON/p" PROD1_ora_15186.trc | grep -v '^--'

 

================================================================================================================

目标:所有控制文件。原料:保存在文件系统上的归档日志和在线日志。前提:控制文件无损。

SQL>recover database;

--目标:所有数据文件及控制文件。原料:保存在文件系统上的归档日志及在线日志。

SQL>recover database using backup control file;

--目标:所有数据文件及控制文件。原料:增量备份、备份中的和文件系统上的归档日志,以及文件系统上的在线日志。

RMAN>recover database;

================================================================================================================

 

--利用自动备份恢复

restore controlfile from autobackup;

recover database;

alter database open resetlogs;

 

------不一致控制文件的恢复

--1.新增表空间

backup current controlfile format '/u01/bk/ctrl.bk';

create tablespace TBS_RMANTEST datafile '/u01/app/oracle/oradata/PROD1/tbs_rmantest01.dbf' size 10m autoextend off;restore controlfile from '/u01/bk/ctrl.bk';

alter database mount;

restore controlfile from '/u01/bk/ctrl.bk';

recover database;

alter database open  resetlogs;

 

--2.删除表空间

backup current controlfile format '/u01/bk/ctrl3.ctl';

drop tablespace TBS_RMAN;

restore  controlfile from '/u01/bk/ctrl3.ctl';

mount database;

recover database;(出错)

offline(数据文件下线)

recover database using backup controlfile;

AUTO(应答)

recover database using backup controlfile;(再次)

输入日志文件

alter database open resetlogs;

 

--3.缺失归档

backup current controlfile format '/u01/bk/ctrl4.ctl';

rm arch1_11_906220404.dbf (之后删掉某个归档)

select max(sequence#) from v$archived_log where status='A';(查找最大的归档)

select * from v$log;(查找日志序号)

删除控制文件

shutdown immediate;

 

restore controlfile from '/u01/bk/ctr4.ctl';

mount database;

recover database;

报错

 

select max(sequence#) from v$archived_log where status='A';(查找最大的归档)

select * from v$log;(查找日志序号)

 

alter database backup controlfile to trace;

select value from v$diag_info where name='Default Trace File';

 

startup nomount;

 

sed -n "/NORESETLOGS/,/REUSE AUTOEXTEND ON/p" /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_24558.trc PROD1_ora_15186.trc | grep -v '^--'

recover database;(失败)

recover database using backup controlfile;

输入日志

alter database open resetlogs;

 

 

参数文件

--备份

backup spfile format '/u01/bk/spfile.bk';

--查看

show parameter spfile;

--恢复

startup nomount;

restore spfile from '/u01/bk/spfile.bk';(手动指定了备份集)

shutdown abort

startup

 

--实例运行时参数文件丢失

restore spfile to ‘/’ from;

restore spfile to '/home/oracle/spflePROD1.ora' from '/u01/bk/spfile.bk';

mv spflePROD1.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD1.ora

 

 

--自动备份+快速恢复区

restore spfile db_name=PROD1 db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

--自动备份

set dbid 2082231315;

startup nomount;

restore spfile from autobackup;

shutdown abort;

startup;

 

--无备份恢复

--1.告警日志

--2.DB_NAME,CONTROL_FILE,DB_BLOCK_SIZE,COMPATIBLE

— 3.create pfile=‘$ORACLE_HOME/dbs/initPROD1.ora.memory’from memory;

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

下一篇: RMAN脚本
请登录后发表评论 登录
全部评论

注册时间:2015-12-02

  • 博文量
    117
  • 访问量
    159238