Chinaunix首页 | 论坛 | 博客

郭一军的ChinaUnix博客

首页　| 　博文目录　| 　关于我

guoyJoe

  • 博客访问： 884974
  • 博文数量： 72
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 435
  • 用 户 组： 普通用户
  • 注册时间： 2013-04-27 20:07
个人简介

郭一军，网名guoyJoe，尖峰在线培训创始人、Oracle OCM、ITPUB论坛Oracle认证版版主、2013最佳精华贴、Senior DBA、Oracle OCT、 ACOUG MEMBER、CSDN 认证专家、RHCE、在线技术分享的倡导者和实践者。 http://www.jianfengedu.com http://jianfengedu.taobao.com

文章分类

全部博文（72）

文章存档

2014年（72）

我的朋友
最近访客
推荐博文
相关博文
同时丢失参数文件丶控制文件及redo log file的不完全恢复（有数据文件的热备和归档）

分类： Oracle

2014-06-19 10:39:02

转载请注明出处：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;
host cp /u01/app/oracle/oradata/PROD/sysaux01.dbf /backup
alter tablespace SYSAUX end backup;

Tablespace altered.

sys@PROD> 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丶日志做切换

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*




###########开始恢复
  1丶建参数文件
[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.

6丶注册规档日志

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


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

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: {=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 \'/arch/1_134_842976958.dbf\' no longer needed for this recovery


Specify log: {=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 \'/arch/1_135_842976958.dbf\' no longer needed for this recovery


Specify log: {=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 \'/arch/1_136_842976958.dbf\' no longer needed for this recovery


Specify log: {=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 \'/arch/1_137_842976958.dbf\' no longer needed for this recovery


Specify log: {=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 \'/arch/1_138_842976958.dbf\' no longer needed for this recovery


Specify log: {=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.



#########恢复完成
gyj@PROD> select * from gyj_test1;


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


阅读(4633) | 评论(0) | 转发(0) |
0

上一篇：尖峰在线Oracle OCM实战 --开创国内Dtrace先河！

下一篇：基于rman的坏块恢复

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6