搜索
首页数据库mysql教程同时丢失参数文件、控制文件及redologfile的不完全恢复（有数据

同时丢失参数文件、控制文件及redologfile的不完全恢复（有数据

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 03:57 PM
丢失参数控制文件

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/30839817 ##########恢复前的准备工作 1、做个热备 select alter tablespace ||tablespace_name|| begin backup; ||chr(10)|| host cp ||file_name|| /backup ||chr(10)|| alter tablespace |

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/30839817

 ##########恢复前的准备工作

1、做个热备 
   select
    'alter tablespace '||tablespace_name|| ' begin backup;' ||chr(10)||
    'host cp '||file_name||' /backup' ||chr(10)||
    'alter tablespace '||tablespace_name|| ' end backup;'
   from dba_data_files order by tablespace_name;

sys@PROD> alter tablespace SYSAUX begin backup;

2、日志做切换

sys@PROD> alter system switch logfile;

System altered.

sys@PROD> alter system switch logfile;

System altered.

sys@PROD> alter system switch logfile;

System altered.

sys@PROD> conn gyj/gyj
Connected.
gyj@PROD> select * from gyj_test1;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 guoyJ
         2 BBBBB

gyj@PROD> insert into gyj_test1 values(3,'CCCCC');

1 row created.

gyj@PROD> commit;

Commit complete.

gyj@PROD> alter system switch logfile;

System altered.

gyj@PROD> alter system switch logfile;

System altered.

3、丢失参数文件，控制文件，redo日志文件

[oracle@jfdb dbs]$ rm  -rf spfile.ora
[oracle@jfdb dbs]$ rm -rf spfilePROD.ora
[oracle@jfdb dbs]$ rm -rf initPROD.ora 
[oracle@jfdb PROD]$ rm -rf control0*
[oracle@jfdb PROD]$ rm -rf redo0*

###########开始恢复
[oracle@jfdb trace]$ cat alert_PROD.log vi /tmp/pfile.ora processes = 150 sga_max_size = 900M sga_target = 900M control_files = "/u01/app/oracle/oradata/PROD/control01.ctl" control_files = "/u01/app/oracle/oradata/PROD/control02.ctl" _controlfile_update_check= "OFF" db_block_size = 8192 log_archive_dest_1 = "location=/arch" undo_tablespace = "UNDOTBS" _in_memory_undo = FALSE service_names = "PROD,crm,oa" local_listener = "(DESCRIPTION= (ADDRESS_LIST= (ADDRESS = (PROTOCOL=TCP)(HOST=jfdb)(PORT=1521)) (ADDRESS = (PROTOCOL=TCP)(HOST=jfdb)(PORT=1522))))" db_name = "PROD" pga_aggregate_target = 200M

2、启动实例
sys@PROD> startup pfile='/tmp/pfile.ora' nomount; ORACLE instance started. Total System Global Area 939495424 bytes Fixed Size 2233960 bytes Variable Size 251660696 bytes Database Buffers 679477248 bytes Redo Buffers 6123520 bytes sys@PROD> create spfile from pfile='/tmp/pfile.ora'; File created.
3、开始还原数据文件 
oracle@jfdb arch]$ cd /backup
[oracle@jfdb backup]$ ll
total 1664052
-rw-r-----. 1 oracle oinstall 340795392 Jun  8 06:01 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Jun  8 06:01 system01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Jun  8 06:01 tp01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Jun  8 06:01 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Jun  8 06:02 users01.dbf
[oracle@jfdb backup]$ cp * -rf /u01/app/oracle/oradata/PROD

4、查数据库字符集 

select distinct dbms_rowid.rowid_block_number(rowid) from props$;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 801
[oracle@jfdb PROD]$ dd if=system01.dbf of=guoyJoe bs=8192 skip=801 count=1

1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000159113 s, 51.5 MB/s
[oracle@jfdb PROD]$ strings guoyJoe
NO_USERID_VERIFIER_SALT 0438054C4F979EC5A5F74990346F5327,
WORKLOAD_REPLAY_MODE
bPREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress,
WORKLOAD_CAPTURE_MODE
/CAPTURE implies workload capture is in progress,
EXPORT_VIEWS_VERSION
Export views revision #,
DEFAULT_PERMANENT_TABLESPACE
USERS$Name of default permanent tablespace,
GLOBAL_DB_NAME
PROD
Global database name,
NLS_RDBMS_VERSION
11.2.0.3.0 RDBMS version for NLS parameters,
NLS_NCHAR_CHARACTERSET  AL16UTF16
NCHAR Character set,
NLS_NCHAR_CONV_EXCP
FALSE
NLS conversion exception,
NLS_LENGTH_SEMANTICS
BYTE
NLS length semantics,
NLS_COMP
BINARY
NLS comparison,
NLS_DUAL_CURRENCY
Dual currency symbol,
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
Timestamp with timezone format,
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
Time with timezone format,
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
Time stamp format,
NLS_TIME_FORMAT
HH.MI.SSXFF AM
Time format,
NLS_SORT
BINARY
Linguistic definition,
NLS_DATE_LANGUAGE
AMERICAN
Date language,
NLS_DATE_FORMAT DD-MON-RR
Date format,
NLS_CALENDAR    GREGORIAN
Calendar system,
NLS_CHARACTERSET
ZHS16GBK
Character set,
NLS_NUMERIC_CHARACTERS
Numeric characters,
NLS_ISO_CURRENCY
AMERICA
ISO currency,
NLS_CURRENCY
Local currency,
NLS_TERRITORY
AMERICA Territory,
NLS_LANGUAGE
AMERICAN
Language,
DEFAULT_TBS_TYPE        SMALLFILE
Default tablespace type,
DST_SECONDARY_TT_VERSION
0'Version of secondary timezone data file,
DST_PRIMARY_TT_VERSION
14%Version of primary timezone data file,
DST_UPGRADE_STATE
NONE&State of Day Light Saving Time Upgrade,
DBTIMEZONE
+08:00
DB time zone,
TDE_MASTER_KEY_ID,
Flashback Timestamp TimeZone
GMT"Flashback timestamp created in GMT,
DEFAULT_EDITION
ORA$BASE$Name of the database default edition,
DEFAULT_PERMANENT_TABLESPACE
SYSTEM$Name of default permanent tablespace,
DEFAULT_TEMP_TABLESPACE
TEMPTS$Name of default temporary tablespace,
        DICT.BASE
2 dictionary base tables version #

5、创建控制文件

sys@PROD> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG
  2      MAXLOGFILES 32
  3      MAXLOGMEMBERS 2
  4      MAXDATAFILES 32
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 449
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M,
  9    GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M,
 10    GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/u01/app/oracle/oradata/PROD/system01.dbf',
 13    '/u01/app/oracle/oradata/PROD/sysaux01.dbf',
 14    '/u01/app/oracle/oradata/PROD/undotbs01.dbf',
 15    '/u01/app/oracle/oradata/PROD/users01.dbf',
 16    '/u01/app/oracle/oradata/PROD/tp01.dbf'
 17  CHARACTER SET ZHS16GBK;

Control file created.

sys@PROD> select count(*) from v$archived_log;

  COUNT(*)
----------
         0
sys@PROD> alter database register physical logfile '/arch/1_134_842976958.dbf';

Database altered.

sys@PROD> alter database register physical logfile '/arch/1_135_842976958.dbf';

Database altered.

sys@PROD> alter database register physical logfile '/arch/1_136_842976958.dbf';

Database altered.

 sys@PROD> alter database register physical logfile '/arch/1_137_842976958.dbf';

Database altered.

sys@PROD> alter database register physical logfile '/arch/1_138_842976958.dbf';

Database altered.

sys@PROD> select count(*) from v$archived_log;

  COUNT(*)
----------
         5
sys@PROD> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1658759
         2            1658739
         3            1658788
         4            1658802
         5            1658774

sys@PROD> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1658759
         2            1658739
         3            1658788
         4            1658802
         5            1658774

8、开始不完全恢复

sys@PROD> recover database using backup controlfile until cancel;

ORA-00279: change 1658739 generated at 06/08/2014 06:01:29 needed for thread 1
ORA-00289: suggestion : /arch/1_134_842976958.dbf
ORA-00280: change 1658739 for thread 1 is in sequence #134


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1658838 generated at 06/08/2014 06:02:46 needed for thread 1
ORA-00289: suggestion : /arch/1_135_842976958.dbf
ORA-00280: change 1658838 for thread 1 is in sequence #135
ORA-00278: log file &#39;/arch/1_134_842976958.dbf&#39; no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1658841 generated at 06/08/2014 06:02:47 needed for thread 1
ORA-00289: suggestion : /arch/1_136_842976958.dbf
ORA-00280: change 1658841 for thread 1 is in sequence #136
ORA-00278: log file &#39;/arch/1_135_842976958.dbf&#39; no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1658844 generated at 06/08/2014 06:02:50 needed for thread 1
ORA-00289: suggestion : /arch/1_137_842976958.dbf
ORA-00280: change 1658844 for thread 1 is in sequence #137
ORA-00278: log file &#39;/arch/1_136_842976958.dbf&#39; no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1658856 generated at 06/08/2014 06:03:17 needed for thread 1
ORA-00289: suggestion : /arch/1_138_842976958.dbf
ORA-00280: change 1658856 for thread 1 is in sequence #138
ORA-00278: log file &#39;/arch/1_137_842976958.dbf&#39; no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1658859 generated at 06/08/2014 06:03:21 needed for thread 1
ORA-00289: suggestion : /arch/1_139_842976958.dbf
ORA-00280: change 1658859 for thread 1 is in sequence #139
ORA-00278: log file &#39;/arch/1_138_842976958.dbf&#39; no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

9、再次查看数据文件头的检查点与控制文件的检查点是否一致

sys@PROD> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1658859
         2            1658859
         3            1658859
         4            1658859
         5            1658859

sys@PROD> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1658859
         2            1658859
         3            1658859
         4            1658859
         5            1658859

10、用resetlogs打开数据库

sys@PROD> alter database open resetlogs;

Database altered.

#########恢复完成
声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
相关文章
在MySQL中使用视图的局限性是什么？在MySQL中使用视图的局限性是什么？May 14, 2025 am 12:10 AM

mysqlviewshavelimitations：1）他们不使用Supportallsqloperations，限制DatamanipulationThroughViewSwithJoinSorsubqueries.2）他们canimpactperformance，尤其是withcomplexcomplexclexeriesorlargedatasets.3）

确保您的MySQL数据库：添加用户并授予特权确保您的MySQL数据库：添加用户并授予特权May 14, 2025 am 12:09 AM

porthusermanagementInmysqliscialforenhancingsEcurityAndsingsmenting效率databaseoperation.1）usecReateusertoAddusers，指定connectionsourcewith@'localhost'or@'％'。

哪些因素会影响我可以在MySQL中使用的触发器数量？哪些因素会影响我可以在MySQL中使用的触发器数量？May 14, 2025 am 12:08 AM

mysqldoes notimposeahardlimitontriggers，butacticalfactorsdeterminetheireffactective：1）serverConfiguration impactactStriggerGermanagement; 2）复杂的TriggerSincreaseSySystemsystem load; 3）largertablesslowtriggerperfermance; 4）highConconcConcrencerCancancancancanceTigrignecentign; 5）; 5）

mysql：存储斑点安全吗？mysql：存储斑点安全吗？May 14, 2025 am 12:07 AM

Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

mySQL：通过PHP Web界面添加用户mySQL：通过PHP Web界面添加用户May 14, 2025 am 12:04 AM

通过PHP网页界面添加MySQL用户可以使用MySQLi扩展。步骤如下：1.连接MySQL数据库，使用MySQLi扩展。2.创建用户，使用CREATEUSER语句，并使用PASSWORD()函数加密密码。3.防止SQL注入，使用mysqli_real_escape_string()函数处理用户输入。4.为新用户分配权限，使用GRANT语句。

mysql：blob和其他无-SQL存储，有什么区别？mysql：blob和其他无-SQL存储，有什么区别？May 13, 2025 am 12:14 AM

mysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而alenosqloptionslikemongodb，redis和calablesolutionsoluntionsoluntionsoluntionsolundortionsolunsolunsstructureddata.blobobobsimplobissimplobisslowderperformandperformanceperformancewithlararengelitiate;

mySQL添加用户：语法，选项和安全性最佳实践mySQL添加用户：语法，选项和安全性最佳实践May 13, 2025 am 12:12 AM

toaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith

MySQL：如何避免字符串数据类型常见错误？MySQL：如何避免字符串数据类型常见错误？May 13, 2025 am 12:09 AM

toAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollat​​ionsEttingsefectery.1）usecharforfixed lengengters lengengtings，varchar forbariaible lengength，varchariable length，andtext/blobforlabforlargerdata.2 seterters seterters seterters seterters

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序，用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！

显示更多

热门文章

如何修复KB5055612无法在Windows 10中安装？
4 周前ByDDD
<🎜>：泡泡胶模拟器无穷大 - 如何获取和使用皇家钥匙
4 周前By尊渡假赌尊渡假赌尊渡假赌
<🎜>：种植花园 - 完整的突变指南
3 周前ByDDD
北端：融合系统，解释
4 周前By尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆树的耳语 - 如何解锁抓钩
3 周前By尊渡假赌尊渡假赌尊渡假赌
显示更多

热工具

安全考试浏览器

安全考试浏览器

Safe Exam Browser是一个安全的浏览器环境，用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问，并防止学生使用未经授权的资源。

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

功能强大的PHP集成开发环境

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

SublimeText3汉化版

SublimeText3汉化版

中文版，非常好用

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序，非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具，帮助Web开发人员更好地理解保护Web应用程序的过程，并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞，难度各不相同。请注意，该软件中

显示更多

热门话题

Java教程
1672
14
CakePHP 教程
1428
52
Laravel 教程
1332
25
PHP教程
1276
29
C# 教程
1256
24
显示更多