Category Archives: Oracle备份恢复

ORA-01207/ORA-00338恢复

Posted on by

1、模拟环境
拷贝出来控制文件，然后对数据库进行一些操作，然后关闭数据库，使用拷贝出来的控制文件替换原控制文件，启动数据库
出现类此错误：
ORA-00338: 日志 2 (用于线程 1) 比控制文件更新
ORA-00312: 联机日志 2 线程 1: ‘E:\ORACLE\ORADATA\XFF\REDO02’
或者
ORA-01122: 数据库文件 1 验证失败
ORA-01110: 数据文件 1: ‘E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF’
ORA-01207: 文件比控制文件更新 – 旧的控制文件

2、处理思路
1）创建控制文件
1.1）alter database backup controlfile to trace;
1.2）生成控制文件脚本

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 'E:\ORACLE\ORADATA\XFF\REDO01'  SIZE 10M BLOCKSIZE 512,
  GROUP 2 'E:\ORACLE\ORADATA\XFF\REDO02'  SIZE 10M BLOCKSIZE 512,
  GROUP 3 'E:\ORACLE\ORADATA\XFF\REDO03'  SIZE 10M BLOCKSIZE 512
DATAFILE
  'E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'E:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'E:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'E:\ORACLE\ORADATA\XFF\USERS01.DBF',
  'E:\ORACLE\ORADATA\XFF\EXAMPLE01.DBF',
  'E:\ORACLE\ORADATA\XFF\O_ORACLE.DBF',
  'E:\ORACLE\ORADATA\XFF\XIFENFEI01.DBF',
  'E:\ORACLE\ORADATA\XFF\P_TEST01.DBF',
  'E:\ORACLE\ORADATA\XFF\SYS_MG01.DBF',
  'E:\ORACLE\ORADATA\XFF\P101.DBF',
  'E:\ORACLE\ORADATA\XFF\P201.DBF',
  'E:\ORACLE\ORADATA\XFF\P301.DBF',
  'E:\ORACLE\ORADATA\XFF\OGG01.DBF',
  'E:\ORACLE\ORADATA\XFF\SPOT01.DBF'
CHARACTER SET ZHS16GBK
;

1.3）关闭数据库
shutdown abort;

1.4）删除现在的控制文件
1.5）执行生成控制文件脚本

2）恢复数据库
recover database;

3）打开数据库
alter database open;

4）添加临时文件
alter tablespace temp add tempfile
‘E:\ORACLE\ORADATA\XFF\TEMP01.DBF’ size 20m reuse;

说明：可以使用备份的控制文件进行恢复
1：startup mount;
2：recover database using backup controlfile until cancel;
然后根据情况，指定archive log和redo file。
3：alter database open resetlogs;

undo损坏恢复–有事务

Posted on by

startup nomount;
create pfile=’/tmp/pfile’ from spfile;

修改pfile
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,
_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

startup pfile=’/tmp/pfile’ mount;
–undo数据文件不用离线也可以打开的时候（2），不行采用下面的方法
alter database datafile 2 offline drop;
alter database open;

CREATE UNDO TABLESPACE UNDOTBS01
DATAFILE ‘/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf’ SIZE 50M;
select segment_name,status from dba_rollback_segs;–查看是否有回滚段被创建，处于offline状态
shutdown immediate;

修改pfile：
*.undo_tablespace=’UNDOTBS01′

startup pfile=’/tmp/pfile’
–alter tablespace undotbs1 offline normal;（2）–使数据文件先离线，如果开始已经离线，这里不需要处理
select segment_name,status from dba_rollback_segs;
drop rollback segment “_SYSSMUx$”;
drop tablespace UNDOTBS02 including contents and datafiles;
select * from v$recover_file;–验证是否还有文件需要恢复
shutdown immediate

修改参数文件
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,
_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

startup pfile=’/tmp/pfile’;
create spfile from pfile=’/tmp/pfile’;

undo损坏恢复–无事务

Posted on by

startup nomount;
create pfile=’/tmp/pfile’ from spfile;

修改pfile
*.undo_management=’MANUAL’
*.undo_tablespace=’SYSTEM’
#*.undo_tablespace=’UNDOTBS02′
#*.undo_management=’AUTO’

startup pfile=’/tmp/pfile’ mount;
alter database datafile 2 offline drop;
alter database open;
drop tablespace undotbs1 including contents;
select segment_name,status from dba_rollback_segs;
drop rollback segment “_SYSSMUx”$;
drop tablespace undotbs1 including contents;

CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE ‘/opt/oracle/oradata/xifenfei/UNDOTBS02.dbf’ SIZE 50M
shutdown immediate;

修改pfile
#*.undo_management=’MANUAL’
#*.undo_tablespace=’SYSTEM’
*.undo_tablespace=’UNDOTBS02′
*.undo_management=’AUTO’

startup pfile=’/tmp/pfile’;
create spfile from pfile=’/tmp/pfile’;

Rman异机恢复

Posted on by

一、查看数据库的dbid
select dbid from v$database;

二、修改参数文件
create pfile=’/tmp/pfile’ from spfile;
直接恢复spfile

三、rman操作
1.rman target /
2.startup nomount pfile=’xxxxxx’
3.set DBID=XXXXXXX

4.恢复控制文件

run{
allocate channel c1 type disk;
restore controlfile from '控制文件rman备份路径';
release channel c1;
}

5.加载控制文件
alter database mount;

6.恢复

run{
allocate channel c1 type disk;
[Set newname for datafile 1 to '新数据文件路径';]
restore database;
[switch datafile all;]
--rman中修改控制文件中datafile path
--sql "alter database rename file ''/opt/oracle/oradata/test/xff_02.dbf'' to ''/opt/oracle/oradata/test/xifenfei02.dbf''"
recover database;
release channel c1;
}

使用rman备份做Data Guard

Posted on by

前两天公司的一套oracle dg因为维护人员的疏忽到值出现gap，无法恢复，只能重做，因为该库每天都有rman的增量备份，所以选择最近的一次全部进行重做dg

1、主库
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/control01.ctl’;
alter system archive log current;

2、修改pfile文件（因为我是重做，所以不需要做）
见11g data guard（PHYSICAL STANDBY）配置

3、拷贝控制文件/rman文件到备库的相同位置
备份全备的内容（/opt/ORBS/data /tmp/control01.ctl)

4、复制数据库做dg
启动到nomount状态
rman target sys/srtsysdb@zjch_Action auxiliary /
duplicate target database for standby dorecover nofilenamecheck;

出现以下错误
RMAN-06025: no backup of log thread 1 seq 675 lowscn 1096519 found to restore
RMAN-06025: no backup of log thread 1 seq 674 lowscn 1095666 found to restore
RMAN-06025: no backup of log thread 1 seq 673 lowscn 1095663 found to restore
RMAN-06025: no backup of log thread 1 seq 672 lowscn 1095656 found to restore
RMAN-06025: no backup of log thread 1 seq 671 lowscn 1095081 found to restore
RMAN-06025: no backup of log thread 1 seq 670 lowscn 1092545 found to restore
RMAN-06025: no backup of log thread 1 seq 669 lowscn 1090351 found to restore
RMAN-06025: no backup of log thread 1 seq 668 lowscn 1088837 found to restore
RMAN-06025: no backup of log thread 1 seq 667 lowscn 1088834 found to restore
RMAN-06025: no backup of log thread 1 seq 666 lowscn 1088721 found to restore
原因：因为控制文件和备份文件中有时间间隔，所以rman要恢复，但是归档日志没有传输过来，所以不能恢复。

–到主库的归档目录查看这些日志是否存在
ls -l（如果存在，下面的操作恢复日志的操作可以忽略）
–查看这些日志是否存在
list backup of archivelog from sequence 666 until sequence 675;
–如果存在rman备份中，使用rman进行恢复
restore archivelog from sequence 666 until sequence 675;

如果存在相关联机日志就不需要添加
–添加redo log
alter database add logfile group 1 (‘/opt/oracle/oradata/zjch/redo01.log’) size 50m;
alter database add logfile group 2 (‘/opt/oracle/oradata/zjch/redo02.log’) size 50m;
alter database add logfile group 3 (‘/opt/oracle/oradata/zjch/redo03.log’) size 50m;
–添加sandby log
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo04.log’) SIZE 50M reuse;
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo05.log’) SIZE 50M reuse;
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo06.log’) SIZE 50M reuse;
ALTER DATABASE ADD STANDBY LOGFILE (‘/opt/oracle/oradata/zjch/std_redo07.log’) SIZE 50M reuse;

5、开启日志应用
–开启实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
–开启应用（根据配置和需求）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
到这一步会如果主机端有所需的归档日志，会自动传输过来，并进行恢复，不用人工干预。

–查看alter文件，查询日志传输是否正常
–查看日志是否传输完成
select thread#, low_sequence#, high_sequence# from v$archive_gap;

–主库切换日志
alter system switch logfile;
–查询最大sequence
select max(sequence#) from v$archived_log;

–备库查看alert文件
tail -100 alert.log
Media Recovery Log /opt/oracle/oradata/zjch/archive/stdarch/1_46544_681145105.dbf
Media Recovery Log /opt/oracle/oradata/zjch/archive/stdarch/1_46545_681145105.dbf 有类似内容
–查询最大sequence
select max(sequence#) from v$archived_log;

–主库建立一个测试表
create table SCOTT.t_test(id number,name varchar2(10));
insert into SCOTT.t_test values(1,’aaaa’);
commit;
alter system switch logfile;
–查询最大sequence
select max(sequence#) from v$archived_log;

–备库检查
–查询最大sequence
select max(sequence#) from v$archived_log;
–取消日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
–只读模式打开数据库
alter database open read only;
–查询刚刚建的表和插入数据是否通报不成功
select * from SCOTT.t_test;

–重启备库，设置为日志应用模式
shutdown immediate;
startup mount;
–开启实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
–开启应用（根据配置和需求）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

–主库删除测试表
drop table SCOTT.t_test;