Oracle数据恢复

1、丢失redo的情况下怎么恢复

redo丢失恢复的四种情况：http://blog.itpub.net/23135684/viewspace-626935/

--查询当前数据库redo的状态以及sequence
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
     1        1          73   52428800       512        1 YES INACTIVE              890012 20-JUN-19              891270 20-JUN-19
     2        1          74   52428800       512        1 NO  CURRENT              891270 20-JUN-19          2.8147E+14
     3        1          72   52428800       512        1 YES INACTIVE              889191 20-JUN-19              890012 20-JUN-19

--删除处于active状态的redo

--关闭数据库之后再次打开数据库
SQL> alter database recover until cancel;

Database altered.

SQL> alter database open resetlogs;

Database altered.

--再次查询redo的状态以及sequence
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
     1        1           1   52428800       512        1 NO  CURRENT              914755 20-JUN-19          2.8147E+14
     2        1           0   52428800       512        1 YES UNUSED               0                   0
     3        1           0   52428800       512        1 YES UNUSED               0                   0

 

2、丢失spfile的情况下该如何恢复

  • 备份参数文件
RMAN> backup spfile;

Starting backup at 24-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-19
channel ORA_DISK_1: finished piece 1 at 24-JUN-19
piece handle=/oracle/app/oracle/flash_recovery_area/OCM/backupset/2019_06_24/o1_mf_nnsnf_TAG20190624T174403_gk16v3sj_.bkp tag=TAG20190624T174403 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-19
  • 模拟文件损坏
[ocm:oracle]:/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs>mv spfileocm.ora spfileocm.ora.bak
[ocm:oracle]:/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 24 17:36:17 2019

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/initocm.ora'
SQL>
  • 恢复参数文件
[ocm:oracle]:/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 24 17:45:52 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/initocm.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2211448 bytes
Variable Size                 88080776 bytes
Database Buffers              62914560 bytes
Redo Buffers                   5455872 bytes

RMAN> 
RMAN> restore spfile from '/oracle/app/oracle/flash_recovery_area/OCM/backupset/2019_06_24/o1_mf_nnsnf_TAG20190624T174403_gk16v3sj_.bkp';

Starting restore at 24-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/app/oracle/flash_recovery_area/OCM/backupset/2019_06_24/o1_mf_nnsnf_TAG20190624T174403_gk16v3sj_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 24-JUN-19

RMAN> shutdown abort;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     868511744 bytes

Fixed Size                     2218272 bytes
Variable Size                457180896 bytes
Database Buffers             406847488 bytes
Redo Buffers                   2265088 bytes

RMAN> alter database  mount;

database mounted

RMAN> alter database open;

database opened

RMAN>
  •  需要注意的点【在没有指定fast revovery area的时候，backup database之后文件的位置如下，恢复方式如下】
RMAN> backup database;

Starting backup at 24-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/oracle/app/oracle/oradata/ocm/undotbs01.dbf
input datafile file number=00001 name=/oracle/app/oracle/oradata/ocm/system01.dbf
input datafile file number=00002 name=/oracle/app/oracle/oradata/ocm/sysaux01.dbf
input datafile file number=00005 name=/oracle/app/oracle/oradata/ocm/fda.dbf
input datafile file number=00004 name=/rman/user01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JUN-19
channel ORA_DISK_1: finished piece 1 at 24-JUN-19
piece handle=/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/0du4tv2o_1_1 tag=TAG20190624T180928 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-19
channel ORA_DISK_1: finished piece 1 at 24-JUN-19
piece handle=/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/0eu4tv3r_1_1 tag=TAG20190624T180928 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-19

RMAN> exit


Recovery Manager complete.


RMAN> restore spfile from '/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/0eu4tv3r_1_1';



Starting restore at 24-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK




channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/0eu4tv3r_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 24-JUN-19




RMAN>


 




 


3、模拟控制文件丢失


    

  • 先来说下什么是控制文件的自动备份【自动备份是指在备份数据文件、归档、参数文件等时，会自动备份参数文件和控制文件】
    • 





RMAN> show controlfile autobackup;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name OCM are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default                      --若想打开，需要将autobackup 设置为on




    

  • 再来说下什么是控制文件手动备份?
    • 





RMAN> backup as backupset current controlfile;   --备份集备份

Starting backup at 25-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 25-JUN-19
channel ORA_DISK_1: finished piece 1 at 25-JUN-19
piece handle=/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/0fu4vpv1_1_1 tag=TAG20190625T105425 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JUN-19


RMAN> backup as backupset current controlfile format '/tmp/control1.ctl';

Starting backup at 25-JUN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 25-JUN-19
channel ORA_DISK_1: finished piece 1 at 25-JUN-19
piece handle=/tmp/control1.ctl tag=TAG20190625T105541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JUN-19



RMAN> backup as copy current controlfile; --镜像复制备份




Starting backup at 25-JUN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/cf_D-OCM_id-2393802673_0iu4vq1n tag=TAG20190625T105551 RECID=5 STAMP=1011869751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUN-19


 




    

  •  控制文件损坏之后进行修复【采用从autobackup中restore的方式时，虽然参数AUTOBACKUP时处于on的状态，但是在采用backup database备份的文件进行restore的时候一直不成功，一直到采用backup tablespace的时候自动备份才成功】
    • 





RMAN> set dbid=2393802673;


executing command: SET DBID



RMAN> restore controlfile from autobackup;

Starting restore at 25-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190625
channel ORA_DISK_1: AUTOBACKUP found: c-2393802673-20190625-03
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-2393802673-20190625-03
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/oracle/app/oracle/oradata/ocm/control01.ctl
output file name=/oracle/app/oracle/flash_recovery_area/ocm/control02.ctl
Finished restore at 25-JUN-19





RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1


 


 


 


SQL> select current_scn from v$database;     --查看此时的scn为0


CURRENT_SCN
-----------
	  0




SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile;       --数据文件的checkpoint_change#不一致




 FILE# CHECKPOINT_CHANGE#
---------- ------------------
	 1	      1421665
	 2	      1421665
	 3	      1421665
	 4	      1421841
	 5	      1421665




 


SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header;   --数据文件的datafile_header也不一致




 FILE# CHECKPOINT_CHANGE#
---------- ------------------
	 1	      1421665
	 2	      1421665
	 3	      1421665
	 4	      1421841
	 5	      1421665




 




RMAN> alter database open resetlogs;       --尝试打开数据库时报错如下，提示数据文件需要恢复




RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/25/2019 14:03:03
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/app/oracle/oradata/ocm/system01.dbf'




RMAN> recover database;                     --恢复数据库




Starting recover at 25-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK




starting media recovery




archived log for thread 1 with sequence 19 is already on disk as file /oracle/app/oracle/oradata/ocm/redo01.log
archived log file name=/oracle/app/oracle/oradata/ocm/redo01.log thread=1 sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-JUN-19




RMAN> alter database open;             --再次打开数据库时提示报错如下




RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/25/2019 14:03:21
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open




RMAN> alter database open resetlogs;         --以open resetlogs方式打开数据库




database opened


 




SQL> select current_scn from v$database;       --open之后的新scn




CURRENT_SCN
-----------
    1422006




SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header;




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




SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile;




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





 


4、模拟数据文件丢失【此处的数据文件包括system、undo以及普通数据文件】




RMAN> restore datafile 1;

Starting restore at 25-JUN-19
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/ocm/system01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/0nu502ph_1_1
channel ORA_DISK_1: piece handle=/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/0nu502ph_1_1 tag=TAG20190625T132505
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 25-JUN-19

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/25/2019 14:40:18
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/app/oracle/oradata/ocm/system01.dbf'

RMAN> recover datafile 1;

Starting recover at 25-JUN-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 18 is already on disk as file /rman/1_18_1011541931.dbf
archived log for thread 1 with sequence 19 is already on disk as file /rman/1_19_1011541931.dbf
archived log file name=/rman/1_18_1011541931.dbf thread=1 sequence=18
archived log file name=/rman/1_19_1011541931.dbf thread=1 sequence=19
media recovery complete, elapsed time: 00:00:06
Finished recover at 25-JUN-19

RMAN> alter database open;

database opened




 






		

		
posted @ 
2019-06-20 15:08 
dayu.liu 
阅读(680) 
评论(0) 
 
收藏 
举报