[ 登录注册 ]

语言

同时丢失参数文件、控制文件及redo log file的不完全恢复(有数据文件的热备和归档)

2017-07-14 10:39:58 admin 返回上一页

标签:des   style   class   blog   code   http   

转载请注明出处: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;


 [email protected]> alter tablespace SYSAUX begin backup;
host cp /u01/app/oracle/oradata/PROD/sysaux01.dbf /backup
alter tablespace SYSAUX end backup;

Tablespace altered.

[email protected]> alter tablespace SYSTEM begin backup;
host cp /u01/app/oracle/oradata/PROD/system01.dbf /backup
alter tablespace SYSTEM end backup;


alter tablespace TP1 begin backup;
host cp /u01/app/oracle/oradata/PROD/tp01.dbf /backup
alter tablespace TP1 end backup;


alter tablespace UNDOTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/undotbs01.dbf /backup
alter tablespace UNDOTBS end backup;


alter tablespace USERS begin backup;
host cp /u01/app/oracle/oradata/PROD/users01.dbf /backup
alter tablespace USERS end backup;


2、日志做切换

[email protected]> alter system switch logfile;System altered.[email protected]> alter system switch logfile;System altered.[email protected]> alter system switch logfile;System altered.[email protected]> conn gyj/gyjConnected.[email protected]> select * from gyj_test1;ID NAME---------- ----------------------------------------------------------------------------------------------------1 guoyJ2 BBBBB[email protected]> insert into gyj_test1 values(3‘CCCCC‘);1 row created.[email protected]> commit;Commit complete.[email protected]> alter system switch logfile;System altered.[email protected]> alter system switch logfile;System altered.


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

[[email protected] dbs]$ rm  -rf spfile.ora[[email protected] dbs]$ rm -rf spfilePROD.ora[[email protected] dbs]$ rm -rf initPROD.ora[[email protected] PROD]$ rm -rf control0*[[email protected] PROD]$ rm -rf redo0*




###########开始恢复
  1、建参数文件
[[email protected] trace]$ cat alert_PROD.logvi /tmp/pfile.oraprocesses                = 150sga_max_size             = 900Msga_target               = 900Mcontrol_files            = "/u01/app/oracle/oradata/PROD/control01.ctl"control_files            = "/u01/app/oracle/oradata/PROD/control02.ctl"_controlfile_update_check= "OFF"db_block_size            = 8192log_archive_dest_1       = "location=/arch"undo_tablespace          = "UNDOTBS"_in_memory_undo          = FALSEservice_names            = "PRODcrmoa"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、启动实例
[email protected]> startup pfile=‘/tmp/pfile.ora‘ nomount;ORACLE instance started.Total System Global Area  939495424 bytesFixed Size                  2233960 bytesVariable Size             251660696 bytesDatabase Buffers          679477248 bytesRedo Buffers                6123520 bytes[email protected]> create spfile from pfile=‘/tmp/pfile.ora‘;File created.

3、开始还原数据文件

[email protected] arch]$ cd /backup[[email protected] backup]$ lltotal 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[[email protected] 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[[email protected] PROD]$ dd if=system01.dbf of=guoyJoe bs=8192 skip=801 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied 0.000159113 s 51.5 MB/s[[email protected] PROD]$ strings guoyJoeNO_USERID_VERIFIER_SALT 0438054C4F979EC5A5F74990346F5327WORKLOAD_REPLAY_MODEbPREPARE implies external replay clients can connect; REPLAY implies workload replay is in progressWORKLOAD_CAPTURE_MODE/CAPTURE implies workload capture is in progressEXPORT_VIEWS_VERSIONExport views revision #DEFAULT_PERMANENT_TABLESPACEUSERS$Name of default permanent tablespaceGLOBAL_DB_NAMEPRODGlobal database nameNLS_RDBMS_VERSION11.2.0.3.0 RDBMS version for NLS parametersNLS_NCHAR_CHARACTERSET  AL16UTF16NCHAR Character setNLS_NCHAR_CONV_EXCPFALSENLS conversion exceptionNLS_LENGTH_SEMANTICSBYTENLS length semanticsNLS_COMPBINARYNLS comparisonNLS_DUAL_CURRENCYDual currency symbolNLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZRTimestamp with timezone formatNLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZRTime with timezone formatNLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AMTime stamp formatNLS_TIME_FORMATHH.MI.SSXFF AMTime formatNLS_SORTBINARYLinguistic definitionNLS_DATE_LANGUAGEAMERICANDate languageNLS_DATE_FORMAT DD-MON-RRDate formatNLS_CALENDAR    GREGORIANCalendar systemNLS_CHARACTERSETZHS16GBKCharacter setNLS_NUMERIC_CHARACTERSNumeric charactersNLS_ISO_CURRENCYAMERICAISO currencyNLS_CURRENCYLocal currencyNLS_TERRITORYAMERICA TerritoryNLS_LANGUAGEAMERICANLanguageDEFAULT_TBS_TYPE        SMALLFILEDefault tablespace typeDST_SECONDARY_TT_VERSION0‘Version of secondary timezone data fileDST_PRIMARY_TT_VERSION14%Version of primary timezone data fileDST_UPGRADE_STATENONE&State of Day Light Saving Time UpgradeDBTIMEZONE+08:00DB time zoneTDE_MASTER_KEY_IDFlashback Timestamp TimeZoneGMT"Flashback timestamp created in GMTDEFAULT_EDITIONORA$BASE$Name of the database default editionDEFAULT_PERMANENT_TABLESPACESYSTEM$Name of default permanent tablespaceDEFAULT_TEMP_TABLESPACETEMPTS$Name of default temporary tablespaceDICT.BASE2 dictionary base tables version #


5、创建控制文件

[email protected]> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG2      MAXLOGFILES 323      MAXLOGMEMBERS 24      MAXDATAFILES 325      MAXINSTANCES 16      MAXLOGHISTORY 4497  LOGFILE8    GROUP 1 ‘/u01/app/oracle/oradata/PROD/redo01.log‘  SIZE 50M9    GROUP 2 ‘/u01/app/oracle/oradata/PROD/redo02.log‘  SIZE 50M10    GROUP 3 ‘/u01/app/oracle/oradata/PROD/redo03.log‘  SIZE 50M11  DATAFILE12    ‘/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.

6、注册规档日志

[email protected]> select count(*) from v$archived_log;COUNT(*)----------0[email protected]> alter database register physical logfile ‘/arch/1_134_842976958.dbf‘;Database altered.[email protected]> alter database register physical logfile ‘/arch/1_135_842976958.dbf‘;Database altered.[email protected]> alter database register physical logfile ‘/arch/1_136_842976958.dbf‘;Database altered.[email protected]> alter database register physical logfile ‘/arch/1_137_842976958.dbf‘;Database altered.[email protected]> alter database register physical logfile ‘/arch/1_138_842976958.dbf‘;Database altered.[email protected]> select count(*) from v$archived_log;COUNT(*)----------5


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

[email protected]> select file#checkpoint_change# from v$datafile;FILE# CHECKPOINT_CHANGE#---------- ------------------1            16587592            16587393            16587884            16588025            1658774[email protected]> select file#checkpoint_change# from v$datafile_header;FILE# CHECKPOINT_CHANGE#---------- ------------------1            16587592            16587393            16587884            16588025            1658774


8、开始不完全恢复

[email protected]> recover database using backup controlfile until cancel;ORA-00279: change 1658739 generated at 06/08/2014 06:01:29 needed for thread 1ORA-00289: suggestion : /arch/1_134_842976958.dbfORA-00280: change 1658739 for thread 1 is in sequence #134Specify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658838 generated at 06/08/2014 06:02:46 needed for thread 1ORA-00289: suggestion : /arch/1_135_842976958.dbfORA-00280: change 1658838 for thread 1 is in sequence #135ORA-00278: log file ‘/arch/1_134_842976958.dbf‘ no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658841 generated at 06/08/2014 06:02:47 needed for thread 1ORA-00289: suggestion : /arch/1_136_842976958.dbfORA-00280: change 1658841 for thread 1 is in sequence #136ORA-00278: log file ‘/arch/1_135_842976958.dbf‘ no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658844 generated at 06/08/2014 06:02:50 needed for thread 1ORA-00289: suggestion : /arch/1_137_842976958.dbfORA-00280: change 1658844 for thread 1 is in sequence #137ORA-00278: log file ‘/arch/1_136_842976958.dbf‘ no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658856 generated at 06/08/2014 06:03:17 needed for thread 1ORA-00289: suggestion : /arch/1_138_842976958.dbfORA-00280: change 1658856 for thread 1 is in sequence #138ORA-00278: log file ‘/arch/1_137_842976958.dbf‘ no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00279: change 1658859 generated at 06/08/2014 06:03:21 needed for thread 1ORA-00289: suggestion : /arch/1_139_842976958.dbfORA-00280: change 1658859 for thread 1 is in sequence #139ORA-00278: log file ‘/arch/1_138_842976958.dbf‘ no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelMedia recovery cancelled.


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

[email protected]> select file#checkpoint_change# from v$datafile;FILE# CHECKPOINT_CHANGE#---------- ------------------1            16588592            16588593            16588594            16588595            1658859[email protected]> select file#checkpoint_change# from v$datafile_header;FILE# CHECKPOINT_CHANGE#---------- ------------------1            16588592            16588593            16588594            16588595            1658859


10、用resetlogs打开数据库

[email protected]> alter database open resetlogs;Database altered.



#########恢复完成
[email protected]> select * from gyj_test1;


        ID NAME
---------- -----------------------------------------
         1 guoyJ
         2 BBBBB
         3 CCCCC


同时丢失参数文件、控制文件及redo log file的不完全恢复(有数据文件的热备和归档)布布扣bubuko.com

同时丢失参数文件、控制文件及redo log file的不完全恢复(有数据文件的热备和归档)

标签:des   style   class   blog   code   http   


文章来源:http://www.bozhiyue.com/yuyan/2017/0714/1484298.html
返回上一页    返回分类 上一篇:   下一篇:
相关