通常，我们都是用RMAN去还原数据文件，再利用归档做恢复，如果没有有效的备份集，但是有热备份生成的文件，那么一样可以进行恢复，这里演示的是非系统表空间对应的数据文件损坏后的恢复。 --首先获取热备份的语句 SQL select 'alter tablespace '||tablespace
通常，我们都是用RMAN去还原数据文件，再利用归档做恢复，如果没有有效的备份集，但是有热备份生成的文件，那么一样可以进行恢复，这里演示的是非系统表空间对应的数据文件损坏后的恢复。--首先获取热备份的语句 SQL> select 'alter tablespace '||tablespace_name|| ' begin backup;'
2 ||chr(10) 3 ||'cp '||file_name||' /u01/' 4 ||chr(10) 5 ||'alter tablespace '||tablespace_name|| ' end backup;' as "script" 6 from dba_data_files where tablespace_name='ZLM';
script -------------------------------------------------------------------------------- alter tablespace ZLM begin backup; cp /u01/app/oracle/oradata/ora10g/zlm01.dbf /u01/ alter tablespace ZLM end backup;
SQL> alter tablespace ZLM begin backup;
SQL> ! [oracle@ora10g ~]$ cp /u01/app/oracle/oradata/ora10g/zlm01.dbf /u01/ [oracle@ora10g ~]$ exit exit
SQL> alter tablespace ZLM end backup;
--验证热备已经产生 SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- ---------- 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 4 NOT ACTIVE 0 5 NOT ACTIVE 0 6 NOT ACTIVE 1340174 2014-11-28
6 rows selected.
--连接到测试用户开始执行事务 SQL> conn zlm/zlm Connected. SQL> create table emp as select * from scott.emp;
Table created.
SQL> set lin 130 SQL> set pages 130 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10
14 rows selected.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
一共执行了4次update操作，并切换了4次日志
--查看当前的数据 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 1200 20 7499 ALLEN SALESMAN 7698 1981-02-20 2000 300 30 7521 WARD SALESMAN 7698 1981-02-22 1650 500 30 7566 JONES MANAGER 7839 1981-04-02 3375 20 7654 MARTIN SALESMAN 7698 1981-09-28 1650 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 3250 30 7782 CLARK MANAGER 7839 1981-06-09 2850 10 7788 SCOTT ANALYST 7566 1987-04-19 3400 20 7839 KING PRESIDENT 1981-11-17 5400 10 7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30 7876 ADAMS CLERK 7788 1987-05-23 1500 20 7900 JAMES CLERK 7698 1981-12-03 1350 30 7902 FORD ANALYST 7566 1981-12-03 3400 20 7934 MILLER CLERK 7782 1982-01-23 1700 10
14 rows selected.
--破坏数据文件 SQL> ! [oracle@ora10g ~]$ cat >> abc.txt abc > efg > hij > EOF [oracle@ora10g ~]$ cat abc.txt abc efg hij [oracle@ora10g ~]$ cp abc.txt /u01/app/oracle/oradata/ora10g/zlm01.dbf [oracle@ora10g ~]$ cat /u01/app/oracle/oradata/ora10g/zlm01.dbf abc efg hij [oracle@ora10g ~]$
--切换日志3次后继续执行查看 SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
SQL> ! [oracle@ora10g ~]$ cp /u01/zlm01.dbf /u01/app/oracle/oradata/ora10g/zlm01.dbf [oracle@ora10g ~]$ exit exit
SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
把原来热备生成的文件替换掉损坏的6号文件，依然提示无法读取
--把故障文件offline后再online SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
此时，会提示6号文件需要做介质恢复
SQL> col error for a10 SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ---------- ---------- ---------- 6 OFFLINE OFFLINE 1385889 2014-11-29
可以看到6号文件现在是offline状态，需要做恢复
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME ---------- ---------- ---------- ARCHIVE_NAME ---------------------------------------------------------------------------------------------------------------------------------- 1 58 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_b7mbppk8_.arc
1 59 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96_.arc
1 60 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j_.arc
1 61 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8_.arc
1 62 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_b7mbw693_.arc
1 63 2014-11-29 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_63_b7mc54hm_.arc
6 rows selected.
v$recovery_log这个视图中查询到的，都是恢复需要用到的归档日志文件
SQL> recover datafile 6; ORA-00279: change 1385889 generated at 11/29/2014 18:46:26 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_%u_.arc ORA-00280: change 1385889 for thread 1 is in sequence #58
Specify log: {
ORA-00279: change 1387536 generated at 11/29/2014 19:16:58 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_%u_.arc ORA-00280: change 1387536 for thread 1 is in sequence #60 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96_.arc' no longer needed for this recovery
ORA-00279: change 1387553 generated at 11/29/2014 19:17:21 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_%u_.arc ORA-00280: change 1387553 for thread 1 is in sequence #61 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j_.arc' no longer needed for this recovery
ORA-00279: change 1387562 generated at 11/29/2014 19:17:41 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_%u_.arc ORA-00280: change 1387562 for thread 1 is in sequence #62 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8_.arc' no longer needed for this recovery
ORA-00279: change 1387587 generated at 11/29/2014 19:18:30 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_63_%u_.arc ORA-00280: change 1387587 for thread 1 is in sequence #63 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_b7mbw693_.arc' no longer needed for this recovery
Log applied. Media recovery complete.
当把v$recovery_log中列出的5个归档日志全部应用后，介质恢复完成
--再次把6号文件online SQL> alter database datafile 6 online;
Database altered.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 1200 20 7499 ALLEN SALESMAN 7698 1981-02-20 2000 300 30 7521 WARD SALESMAN 7698 1981-02-22 1650 500 30 7566 JONES MANAGER 7839 1981-04-02 3375 20 7654 MARTIN SALESMAN 7698 1981-09-28 1650 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 3250 30 7782 CLARK MANAGER 7839 1981-06-09 2850 10 7788 SCOTT ANALYST 7566 1987-04-19 3400 20 7839 KING PRESIDENT 1981-11-17 5400 10 7844 TURNER SALESMAN 7698 1981-09-08 1900 0 30 7876 ADAMS CLERK 7788 1987-05-23 1500 20 7900 JAMES CLERK 7698 1981-12-03 1350 30 7902 FORD ANALYST 7566 1981-12-03 3400 20 7934 MILLER CLERK 7782 1982-01-23 1700 10
14 rows selected.
此时，表空间ZLM及对应的数据文件zlm01.dbf都已经顺利地恢复了
声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
如何向新的MySQL用户授予权限May 09, 2025 am 12:16 AMTograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)
如何在MySQL中添加用户：逐步指南May 09, 2025 am 12:14 AMtoadduserInmysqleffectection andsecrely，theTheSepsps：1）USEtheCreateuserStattoDaneWuser，指定thehostandastrongpassword.2）GrantNectalRevileSaryPrivilegesSustate，usiveleanttatement，AdheringTotheTeprinciplelastPrevilegege.3）
mysql：添加具有复杂权限的新用户May 09, 2025 am 12:09 AMtoaddanewuserwithcomplexpermissionsinmysql，loldtheSesteps：1）创建eTheEserWithCreateuser'newuser'newuser'@''localhost'Indedify'pa ssword';。2）GrantreadAccesstoalltablesin'mydatabase'withGrantSelectOnMyDatabase.to'newuser'@'localhost';。3）GrantWriteAccessto'
mysql：字符串数据类型和coltrationsMay 09, 2025 am 12:08 AMMySQL中的字符串数据类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT，排序规则（Collations）决定了字符串的比较和排序方式。1.CHAR适合固定长度字符串，VARCHAR适合可变长度字符串。2.BINARY和VARBINARY用于二进制数据，BLOB和TEXT用于大对象数据。3.排序规则如utf8mb4_unicode_ci忽略大小写，适合用户名；utf8mb4_bin区分大小写，适合需要精确比较的字段。
MySQL：我应该在Varchars上使用什么长度？May 09, 2025 am 12:06 AM最佳的MySQLVARCHAR列长度选择应基于数据分析、考虑未来增长、评估性能影响及字符集需求。1)分析数据以确定典型长度；2)预留未来扩展空间；3)注意大长度对性能的影响；4)考虑字符集对存储的影响。通过这些步骤，可以优化数据库的效率和扩展性。
mysql blob：有什么限制吗？May 08, 2025 am 12:22 AMmysqlblobshavelimits：tinyblob（255bytes），blob（65,535 bytes），中间布洛布（16,777,215个比例），andlongblob（4,294,967,967,295 bytes）.tousebl观察性：1）考虑performance impactsandSandStorelargeblobsextern; 2）管理backbackupsandreplication carecration; 3）usepathsinst
MySQL：自动化用户创建的最佳工具是什么？May 08, 2025 am 12:22 AM自动化在MySQL中创建用户的最佳工具和技术包括：1.MySQLWorkbench，适用于小型到中型环境，易于使用但资源消耗大；2.Ansible，适用于多服务器环境，简单但学习曲线陡峭；3.自定义Python脚本，灵活但需确保脚本安全性；4.Puppet和Chef，适用于大规模环境，复杂但可扩展。选择时需考虑规模、学习曲线和集成需求。
mysql：我可以在斑点内搜索吗？May 08, 2025 am 12:20 AM是的，YouCansearchInIdeAblobInMysqlusingsPecificteChniques.1）转换theblobtoautf-8StringWithConvertFunctionWithConvertFunctionandSearchusiseLike.2）forCompresseBlyblobs，useuncompresseblobs，useuncompressbeforeconversion.3）acpperformance impperformance imperformance imptactsanddataEccoding.4）
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
如何修复KB5055523无法在Windows 11中安装？
4 周前ByDDD
如何修复KB5055518无法在Windows 10中安装？
4 周前ByDDD
<🎜>：种植花园 - 完整的突变指南
3 周前ByDDD
<🎜>：泡泡胶模拟器无穷大 - 如何获取和使用皇家钥匙
3 周前By尊渡假赌尊渡假赌尊渡假赌
如何修复KB5055612无法在Windows 10中安装？
3 周前ByDDD
热工具
螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具，用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。
Atom编辑器mac版下载
最流行的的开源编辑器
MinGW - 适用于 Windows 的极简 GNU
这个项目正在迁移到osdn.net/projects/mingw的过程中，你可以继续在那里关注我们。MinGW：GNU编译器集合（GCC）的本地Windows移植版本，可自由分发的导入库和用于构建本地Windows应用程序的头文件；包括对MSVC运行时的扩展，以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。
Dreamweaver Mac版
视觉化网页开发工具
禅工作室 13.0.1
功能强大的PHP集成开发环境
