简单电商网站模板,网站推广营销策划,网站模板安卓,个人网站可以做资讯小说类今天负责报表的同事反馈在DG库查询时出现如下报错
ORA-01578:ORACLE数据块损坏(文件号6,块号 2494856)ORA-01110:数据文件6: /oradata/PMSDG/o1 mf users_molczgmn_.dbfORA-26040:数据块是使用 NOLOGGING 选项加载的
可以看到报错是数据文件损坏#xff0c;提示了file id和b…今天负责报表的同事反馈在DG库查询时出现如下报错
ORA-01578:ORACLE数据块损坏(文件号6,块号 2494856)ORA-01110:数据文件6: /oradata/PMSDG/o1 mf users_molczgmn_.dbfORA-26040:数据块是使用 NOLOGGING 选项加载的
可以看到报错是数据文件损坏提示了file id和block id 在 Oracle Data Guard 环境中如果 备库数据文件 出现坏块而主库正常如何来修复呢这里假设备库是 Physical Standby 模式。
如果是逻辑备库则如下的方法1/2不适用。 一. 确认问题 确认坏块信息
在备库执行以下命令查看坏块信息 SELECT * FROM V$DATABASE_BLOCK_CORRUPTION; 检查具体受影响的数据文件
查看坏块对应的数据文件编号和名称:(主备均查询一下确认主库是否也有损坏) SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE# FROM V$DATABASE_BLOCK_CORRUPTION); 二. 修复方法
方法 1通过主库恢复坏块
在 Data Guard 环境中 如果确认主库没有损坏 只有备库出现文件损坏可以直接通过rman来修复
1.1 停止备库的日志应用
在备库停止日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
1.2 恢复坏块
通过 RMAN 恢复受影响的数据文件
登录到备库的 RMAN rman target / RMAN RECOVER DATAFILE 6 BLOCK 2494856; Starting recover at 02-JAN-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID200 device typeDISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 02-JAN-25 RMAN RMAN 会自动从主库提取所需的日志恢复受影响的块。
1.3 启动日志应用
修复完成后重新启动日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 方法 2从主库复制数据文件
如果坏块数量较多或 RECOVER 无法成功可以直接从主库复制整个数据文件。
2.1 确认数据文件路径
在主库和备库执行以下查询确认数据文件路径一致 SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;
2.2 在主库创建数据文件备份
登录到主库的 RMAN备份受影响的数据文件 [oraclePMS~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:46:31 2025 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PMS (DBID4262015389) RMAN BACKUP AS COPY DATAFILE 6 FORMAT /home/oracle/o1 mf users_molczgmn_.dbf; Starting backup at 02-JAN-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID764 instancepms2 device typeDISK channel ORA_DISK_1: starting datafile copy input datafile file number00006 nameDATA/pms/datafile/o1 mf users_molczgmn_.dbf output file name/home/oracle/o1 mf users_molczgmn_.dbf tagTAG20250102T224636 RECID4 STAMP1189378160 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:45 Finished backup at 02-JAN-25 Starting Control File and SPFILE Autobackup at 02-JAN-25 piece handleARCH/pms/autobackup/2025_01_02/s_1189378162.29728.1189378163 commentNONE Finished Control File and SPFILE Autobackup at 02-JAN-25 2.3 传输备份到备库
将备份文件传输到备库相同路径 scp -P 11122 o1 mf users_molczgmn_.dbf oracle10.xx.xx.xx:/home/oracle
2.4 在备库切换数据文件
在备库停止日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
登录到备库的 RMAN切换到新数据文件 启动数据库到mount注册数据文件副本CATALOG DATAFILECOPY /home/oracle/o1 mf users_molczgmn_.dbf;RESTORE DATAFILE 6 ;RECOVER DATAFILE 6;开启数据库alter database open[oraclepmsdg ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:14:57 2025 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PMS(DBID4262015389) RMAN catalog start with /home/oracle/o1 mf users_molczgmn_.dbf; searching for all files that match the pattern /home/oracle/o1 mf users_molczgmn_.dbf List of Files Unknown to the Database File Name: /home/oracle/o1 mf users_molczgmn_.dbf Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files File Name: /home/oracle/o1 mf users_molczgmn_.dbf --将备库启动到mount阶段. [oraclepmsdg]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 22:27:50 2025 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYSorcl150 startup mount; ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2253624 bytes Variable Size 989859016 bytes Database Buffers 587202560 bytes Redo Buffers 7393280 bytes Database mounted. [oraclepmsdg]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 22:28:24 2025 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PMSDG (DBID4262015389, not open) RMAN restore datafile 6; Starting restore at 02-JAN-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID63 device typeDISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to /oradata/PMSDG/o1 mf users_molczgmn_.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/o1 mf users_molczgmn_.dbf channel ORA_DISK_1: piece handle/home/oracle/o1 mf users_molczgmn_.dbf tagTAG20230203T204533 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:06:01 Finished restore at 02-JAN-25 RMAN recover datafile 6; Starting recover at 02-JAN-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID63 device typeDISK starting media recovery media recovery complete, elapsed time: 00:05:01 Finished recover at 02-JAN-25 RMAN alter database open; database opened
2.5 启动日志应用
修复完成后重新启动日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 方法 3重建备库简单粗暴
如果坏块影响严重且以上方法无法修复可以考虑重新同步备库如果是数据量比较小的推荐这样操作
如果是数据量很大的话 推荐前面的两种方法
3.1 RMAN duplicate方式重建备库
创建主库的全量备份包括数据文件、控制文件、归档日志 rman target sys/xxxxpms auxiliary sys/xxxxPMSDGrun { allocate channel d1 type disk; allocate channel d2 type disk; allocate channel d3 type disk; allocate channel d4 type disk; allocate auxiliary channel c1 type disk; allocate auxiliary channel c2 type disk; allocate auxiliary channel c3 type disk; allocate auxiliary channel c4 type disk; duplicate target database for standby from active database nofilenamecheck; }
3.2 重新开始归档应用
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
在备库使用 RMAN 恢复备份并重新配置 Data Guard 环境。 三. 验证修复 检查坏块是否修复
在备库执行以下命令确认坏块是否已修复 SELECT * FROM V$DATABASE_BLOCK_CORRUPTION; 确认日志应用正常
查看备库是否同步正常 --日志传输状态 standby select Last Applied : Logs, to_char(next_time, DD-MON-YY HH24:MI:SS) Time from v$archived_log where sequence# (select max(sequence#) from v$archived_log where applied YES) union select Last Received : Logs, to_char(next_time, DD-MON-YY HH24:MI:SS) Time from v$archived_log where sequence# (select max(sequence#) from v$archived_log); -- 最后应用的sequence序列 Standby select al.thrd Thread, almax Last Seq Received, lhmax Last Seq Applied from (select thread# thrd, max(sequence#) almax from v$archived_log where resetlogs_change# (select resetlogs_change# from v$database) group by thread#) al, (select thread# thrd, max(sequence#) lhmax from v$log_history where first_time (select max(first_time) from v$log_history) group by thread#) lh where al.thrd lh.thrd; 四. 注意事项优先通过主库恢复 如果坏块较少推荐使用方法 1 恢复。如果坏块较多直接使用方法 2 替换数据文件。如果数据库比较小 也推荐重建方式 简单直接如果数据库太大 重建耗时太久 推荐前两种方式另外如果是主库有文件损坏备库正常方法2也适用。
