RMAN脚本

--实验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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30606702/viewspace-2058429/