ITPub博客

系统表空间恢复[@more@]Ø 系统表空间损坏或丢失 SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: '/u01/oradata/retc/rwsw_system' SQL> recover database using backup controlfile;ORA-00283: recovery session canceled due to errorsORA-01110: data file 1: '/u01/oradata/retc/rwsw_system'ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1:

数据文件恢复[@more@]Ø 数据文件rwsw_quest1损坏或丢失SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01157: cannot identify/lock data file 11 - see DBWR trace fileORA-01110: data file 11: '/u01/oradata/retc/rwsw_quest1'SQL> recover database;ORA-00283: recovery session canceled due to errorsORA-01610: recovery using the BACKUP CONTROLFILE option must be doneSQL> recover database using backup controlfile until cancel;ORA-00283: recovery session

联机重做日志文件恢复[@more@]Ø 损坏状态为INACTIVE的联机日志文件SQL> select group#,thread#,sequence#,members,archived,status,first_change# from v$log order by group#;GROUP# THREAD# SEQUENCE# MEMBERS ARCHIV STATUS FIRST_CHANGE#---------- ---------- ---------- ---------- ------ -------------------------------- ------------- 1 1 9 2 YES INACTIVE 30536577211 2 1 8 2 YES INACTIVE 3053657

全库恢复到另外一台测试库，属于异机恢复[@more@]Ø 测试库创建PFILE参数文件INITSHESTATE.ORA*.aq_tm_processes=0*.compatible='9.2.0.0.0'*.db_block_size=8192*.db_cache_size=268435456*.db_name=shestate*.db_file_multiblock_read_count=16*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=254803968*.processes=150*.session_max_open_files=20#*.sga_target=767557632*.sga_max_size=536870912*.large_pool_size=33554432*.shared_pool_size=262144000*.sort_area_size=524288*.undo_management='AUTO'*.

控制文件恢复[@more@]$ export ORACLE_SID=shestate$ rman target / nocatalogRecovery Manager: Release 9.2.0.5.0 - 64bit ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database (not started)RMAN> set dbid=3563853082executing command: SET DBIDRMAN> startup nomount;Oracle instance startedTotal System Global Area 1411877936 bytesFixed Size 744496 bytesVariable Size 1358954496 bytesDatabase Buffers 50331648 bytesRedo Buffers 1847296 bytesRMAN> RU

搞懂介质恢复-使用备份的控制文恢复数据库[@more@]数据文件user01.dbf丢失，控制文件丢失--restore controlfile;--restore datafile 4;--目前数据库为mount状态：查看相关SCN:--备份控制文件记录的CNSQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------5277885--备份控制文件记录的SCNSQL> select file#,checkpoint_change# from v$datafile;FILE# CHECKPOINT_CHANGE#---------- ------------------1 52779302 52779303 52779304 52779305 52779306 52779307 52779308 5277930--当前数据库数据文

搞懂介质恢复-使用当前的控制文件恢复数据库[@more@]数据文件users01丢失1.首先是restore数据文件restore前：比较SCN控制文件记录的SCNSQL> col checkpoint_change# for 9999999999SQL> select file#,checkpoint_change# from v$datafile;FILE# CHECKPOINT_CHANGE#---------- ------------------1 52736332 52736333 52736334 52736335 52736336 52736337 52736338 5273633数据文件记录的SCNSQL> select file#,checkpoint_change# from v$datafile_header;FILE# CHECKPOINT_CHANGE#---------- ------------------1 52736332 5273

有这样的一种需要恢复数据库场景：当前数据库有一份全备，归档，联机重做日志都没有损坏或丢失，但是控制文件意外的损坏了，而且更糟糕的是，居然没有备份的控制文件（当然， 这种情形很少见），这种情形下，只有重建控制文件，但是重建控制文件也是一件很棘手的事情，如果数据库有上百个数据文件，要写在控制文件里，不是件容易的是，这就要求数据库管理员对数据库结构相当熟悉。这也是说明了控制文件的重要性了。我这里的场景是使用alter database backup controlfile to trace得到重建控制文件的脚本，纯粹是为了今天要说的主题来的。在

在备份集无法restore的情况下，使用dbms_backup_restore包在nomount状态下restore数据文件，然后恢复数据库。[@more@][oracle@primary dbs]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on Sun Dec 2 15:18:58 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: dbwdn (not mounted)RMAN> run2> {3> allocate channel t1 device type disk;4> restore database from '0vnrrptr_1_1.bak';5> release channel t1;6> }using target database control file

copy datafile 与copy datafilecopy的区别：[@more@]RMAN> copy2> datafile '/u01/app/oracle/oradata/dbwdn/users01.dbf' to '/tmp/users01.dbf';Starting backup at 02-DEC-2012 13:57:54using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile fno=00004 name=/u01/app/oracle/oradata/dbwdn/users01.dbfoutput filename=/tmp/users01.dbf tag=TAG20121202T135754 recid=12 stamp=800978275channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 02-DEC-2012 13:5

在linux redhat 5.4版本，rm误删除数据库文件的恢复过程如下：[@more@]1.首先测试rm 误删除数据库文件[oracle@primary dbwdn]$ lltotal 2153164-rw-r----- 1 oracle dba 8536064 Nov 27 21:50 control01.ctl-rw-r----- 1 oracle dba 8536064 Nov 27 21:50 control02.ctl-rw-r----- 1 oracle dba 8536064 Nov 27 21:50 control03.ctl-rw-r----- 1 oracle dba 104865792 Nov 27 21:32 ggs01.dbf-rw-r----- 1 oracle dba 525344768 Nov 27 21:32 perfstat.dbf-rw-r----- 1 oracle dba 52429312 Nov 27 21:10 redo01.log-rw-r----- 1 oracle d

使用rman命令全备份数据库:[oracle@primary u01]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on Tue Nov 27 21:25:33 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: DBWDN (DBID=3486622777)RMAN> backup incremental level = 0 database format '/u01/bak/full_0_bak.bak';[@more@]Starting backup at 27-NOV-2012 21:26:35using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=142 devtype=DI

续《使用oracle duplicate技术复制需要恢复的表空间到测试环境(异机)（一）》[@more@]datafile 61 not processed because file is offlinedatafile 62 not processed because file is offlinedatafile 63 not processed because file is offlinedatafile 64 not processed because file is offlinedatafile 65 not processed because file is offlinestarting media recoveryOracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01

一、测试项目使用oracle duplicate技术复制需要恢复的表空间到测试环境二、测试目的假定数据库有数据损坏，在flashback、灾备库、快照库无法进行恢复的情况下，使用数据库高级技术TSPITR（Tablespace Point-In-Time Recovery）进行数据恢复.三、测试环境目标数据库数据库版本：oracle 9.2.0.7系统环境 ：AIX 5.2IP地址 ：172.16.34.33db_name ：retc辅助数据库数据库版本：oracle 9.2.0.7系统环境 ：AIX 5.2IP地址 ：172.16.34.35db_name ：auxCatalog数据库 数据库版本：oracle 9.2.0.1 系统环境 ：windows 2003