重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
同时丢失参数文件、控制文件及redologfile的不完全恢复（有数据文件的热备和归档）
转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/30839817
创新互联建站是一家专业提供阳朔企业网站建设,专注与网站制作、成都做网站、HTML5、小程序制作等业务。10年已为阳朔众多企业、政府机构等服务。创新互联专业网站建设公司优惠进行中。
##########恢复前的准备工作
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 = 200M2、启动实例
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
当前名称：同时丢失参数文件、控制文件及redologfile的不完全恢复（有数据文件的热备和归档）
文章源于：http://cqcxhl.com/article/psdcci.html