Oracle DB备份恢复篇之丢失控制文件
一、实验目的
本篇主要模拟控制文件丢失后，如何根据实际情况恢复数据库，才能使数据库尽可能不丢失数据。
二、实验环境
1)Linux系统环境
[oracle@DG1 ~]$ lsb_release -a
LSB
Version:
:core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: RedHatEnterpriseServer
Description:
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
Release:
5.4
Codename:
Tikanga
2)Oracle数据库版本信息
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE
10.2.0.1.0
Production
TNS
for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
3)查看数据库是否归档
[oracle@DG1 ~]$sqlplus / as sysdba
SQL> archive logfile list;
SP2-0734: unknown command beginning "archive lo..." - rest
of line ignored.
SQL> archive list;
SP2-0734: unknown command beginning "archive li..." - rest
of line ignored.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival
Enabled
Archive destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence
1
Next
log sequence to archive
1
Current log sequence
1
三、实验模拟种类及解决方案
1)丢失部分控制文件，其余控制文件还在
解决方案：一致性关库后，通过copy剩下的控制文件恢复
2)在无备份的情况下丢失了所有的控制文件，但对控制文做了追踪备份
解决方案：通过相应的trace文件，生成脚本，重新创建controlfile
3)在归档模式下，对数据库有完备，丢失全部控制文件
解决方案：通过备份集中的控制文件进行恢复
四、实验过程
1)丢失部分控制文件，其余控制文件还在
查看数据库中控制文件
SQL> select '!rm '||name from
v$controlfile;
'!RM
'||NAME
-------------------------------------------------------------------------------
!rm
/u01/app/oracle/oradata/lzcdb/control01.ctl
!rm
/u01/app/oracle/oradata/lzcdb/control02.ctl
!rm
/u01/app/oracle/oradata/lzcdb/control03.ctl
SQL> !rm
/u01/app/oracle/oradata/lzcdb/control02.ctl
!rm
/u01/app/oracle/oradata/lzcdb/control03.ctl
查看控制文件
SQL> !ls
/u01/app/oracle/oradata/DG1/
control01.ctlredo01.log
redo02.log
redo03.log system01.dbf
undotbs01.dbf
redo01_a.log redo02_a.log
redo03_a.log sysaux01.dbf temp01.dbf
users01.dbf
关闭数据库再重新启动数据库(关闭时要执行一致性关闭)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
重新启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 285212672
bytes
Fixed Size
1218992 bytes
Variable Size
88082000 bytes
Database Buffers
192937984 bytes
Redo
Buffers
2973696 bytes
ORA-00205: error in identifying control file, check alert
log for more info
警告日志信息如下
Wed
Jun 13 23:38:18 2012
ALTER DATABASE
MOUNT
Wed
Jun 13 23:38:18 2012
ORA-00202: control file:
'/u01/app/oracle/oradata/DG1/control02.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Wed
Jun 13 23:38:18 2012
ORA-205 signalled during: ALTER DATABASE
MOUNT在由nomount启动到mount时错误
解决方案：将数据库一致性关闭之后把control01.ctl复制两份，然后改名成删除的控制文件
SQL> shutdownimmediate;
ORA-01507: databasenot mounted
ORACLE instance shutdown.
SQL>
!cp/u01/app/oracle/oradata/lzcdb/control01.ctl/u01/app/oracle/oradata/lzcdb/control02.ctl
SQL>
!cp/u01/app/oracle/oradata/lzcdb/control01.ctl/u01/app/oracle/oradata/lzcdb/control03.ctl
再次重新启动数据库
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 285212672
bytes
Fixed Size
1218992 bytes
Variable Size
83887696 bytes
DatabaseBuffers
197132288 bytes
Redo
Buffers
2973696 bytes
Database mounted.
Database opened.
成功启动！这种恢复控制文件，一定要在控制文件丢失后一致性关闭数据库，这样才能保证恢复出的控制文件课数据文件的SCN一致，打开数据库是不会出错(但如果是在归档模式子，即便不一致性关库，通过此方式也应该能恢复数据库吧！但我还没做实验验证！)。
2)在没有备份的情况下丢失了所有的控制文件，但是对控制文件做了追踪备份
在这里罗嗦一点，注意一定要先将控制文件做追踪备份到trace文件中，才能删除全部控制，否则你删除全部控制文件，数据库必然挂掉了，你怎么可能再去将控制文件做追踪备份到trace文件中呢？千万不要犯这样的低级错误。
将控制文件备份到跟踪文件
SQL> alter database backup controlfile to
trace;
Database altered.
查看跟踪文件的位置
SQL> SQL> show parameter
user;
NAME
TYPE
VALUE
------------------------------------ -----------
------------------------------
license_max_users
integer
0
parallel_adaptive_multi_user
boolean
TRUE
user_dump_dest
string
/u01/app/oracle/admin/DG1/udump
找到刚刚生成的trace文件，将trace文件中创建控制文件的sql语句读取出来
[oracle@DG1 ~]$ cd /u01/app/oracle/admin/
dataguard1/ DG1/
[oracle@DG1 ~]$ cd
/u01/app/oracle/admin/DG1/udump/
[oracle@DG1 udump]$ ls -lrt|tail -1
-rw-r----- 1 oracle oinstall
8397 Jun 13 23:04 dg1_ora_30712.trc
查看生成的读取的trace内容
[oracle@DG1 udump]$ cat dg1_ora_30712.trc
/u01/app/oracle/admin/DG1/udump/dg1_ora_30712.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With
the Partitioning, OLAP and Data Mining options
ORACLE_HOME =
/u01/app/oracle/product/10.2.0.1/db_1
System name:
Linux
Node
name:
DG1
Release:
2.6.18-164.el5
Version:
#1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:
i686
Instance name: DG1
Redo
thread mounted by this instance: 1
Oracle process number: 15
Unix
process pid: 30712, image: oracle@DG1 (TNS V1-V3)
***
SERVICE NAME:(SYS$USERS) 2012-06-13 23:04:23.363
***
SESSION ID:(159.3) 2012-06-13 23:04:23.363
***
2012-06-13 23:04:23.363
--
The following are current System-scope REDO Log Archival
related
--
parameters and can be included in the database initialization
file.
--
--
LOG_ARCHIVE_DEST=''
--
LOG_ARCHIVE_DUPLEX_DEST=''
--
--
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
--
DB_UNIQUE_NAME="DG1"
--
--
LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
--
LOG_ARCHIVE_MAX_PROCESSES=2
--
STANDBY_FILE_MANAGEMENT=MANUAL
--
STANDBY_ARCHIVE_DEST=?/dbs/arch
--
FAL_CLIENT=''
--
FAL_SERVER=''
--
--
LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
--
LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
--
LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY
SYNC'
--
LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
--
LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED
NODB_UNIQUE_NAME'
--
LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
--
LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
--
Below are two sets of SQL statements, each of which creates a
new
--
control file and uses it to open the database. The first set
opens
--
the database with the NORESETLOGS option and should be used only
if
--
the current versions of all online logs are available. The
second
--
set opens the database with the RESETLOGS option and should be
used
--
if online logs are unavailable.
--
The appropriate set of statements can be copied from the trace
into
-- a
script file, edited as necessary, and executed when there is
a
--
need to re-create the control file.
--
--
Set #1. NORESETLOGS case
--
-- The following commands will create a new control file
and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of
offline
-- Use this only if the current versions of all online logs
are
-- available.
-- After mounting the created controlfile, the following
SQL
-- statement will place the database in the
appropriate
-- protection mode:
--
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DG1"
NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/DG1/redo01.log',
'/u01/app/oracle/oradata/DG1/redo01_a.log'
)
SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/DG1/redo02.log',
'/u01/app/oracle/oradata/DG1/redo02_a.log'
)
SIZE 50M,
GROUP 3 (
'/u01/app/oracle/oradata/DG1/redo03.log',
'/u01/app/oracle/oradata/DG1/redo03_a.log'
)
SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/DG1/system01.dbf',
'/u01/app/oracle/oradata/DG1/undotbs01.dbf',
'/u01/app/oracle/oradata/DG1/sysaux01.dbf',
'/u01/app/oracle/oradata/DG1/users01.dbf',
'/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf'
CHARACTER SET US7ASCII
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK
FORMAT
''/home/oracle/DiskBackupLocation/%U''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE
AUTOBACKUP','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE','DISK TO
''/home/oracle/DiskBackupLocation/%F''');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO
NONE');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames
on
-- disk. Any one log file from each branch can be used
to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE
'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE
'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE
'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are
restored backups,
-- or if the last shutdown was not normal or
immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is
needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary
tablespaces.
-- Online tempfiles have complete space
information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'/u01/app/oracle/oradata/DG1/temp01.dbf'
SIZE 419430400 REUSE AUTOEXTEND ON
NEXT 655360 MAXSIZE
32767M;
-- End of tempfile additions.
--
--
Set #2. RESETLOGS case
--
-- The following commands will create a new control file
and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups
will
-- be invalidated. Use this only if online logs are
damaged.
-- After mounting the created controlfile, the following
SQL
-- statement will place the database in the
appropriate
-- protection mode:
--
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DG1"
RESETLOGS
ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/DG1/redo01.log',
'/u01/app/oracle/oradata/DG1/redo01_a.log'
)
SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/DG1/redo02.log',
'/u01/app/oracle/oradata/DG1/redo02_a.log'
)
SIZE 50M,
GROUP 3 (
'/u01/app/oracle/oradata/DG1/redo03.log',
'/u01/app/oracle/oradata/DG1/redo03_a.log'
)
SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/DG1/system01.dbf',
'/u01/app/oracle/oradata/DG1/undotbs01.dbf',
'/u01/app/oracle/oradata/DG1/sysaux01.dbf',
'/u01/app/oracle/oradata/DG1/users01.dbf',
'/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf'
CHARACTER SET US7ASCII
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK
FORMAT
''/home/oracle/DiskBackupLocation/%U''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE
AUTOBACKUP','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE','DISK TO
''/home/oracle/DiskBackupLocation/%F''');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO
NONE');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames
on
-- disk. Any one log file from each branch can be used
to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE
'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE
'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE
'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are
restored backups,
-- or if the last shutdown was not normal or
immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online
logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary
tablespaces.
-- Online tempfiles have complete space
information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'/u01/app/oracle/oradata/DG1/temp01.dbf'
SIZE 419430400 REUSE AUTOEXTEND ON
NEXT 655360 MAXSIZE
32767M;
-- End of tempfile additions.
--
创建用于恢复控制文件的sql脚本
[oracle@DG1 udump]$ vi dg1_ora_30712.trc
：set nu
53,125 w! /home/oracle/controlfile_trace1_1.sql
查看用于恢复控制文件的sql脚本
[oracle@DG1 ~]$ ls
10201_database_linux32.zip database
DiskBackupLocation
controlfile_trace1_1.sqlDesktop
FlashRecovery
删除所有控制文件
SQL> select '!rm '||name from
v$controlfile;
'!RM'||NAME
-------------------------------------------------------------------------------
!rm/u01/app/oracle/oradata/lzcdb/control01.ctl
!rm/u01/app/oracle/oradata/lzcdb/control02.ctl
!rm/u01/app/oracle/oradata/lzcdb/control03.ctl
SQL> !rm
/u01/app/oracle/oradata/DG1/control01.ctl
!rm
/u01/app/oracle/oradata/DG1/control02.ctl
!rm
/u01/app/oracle/oradata/DG1/control03.ctl
查看控制文件是否被删除
SQL> !ls
/u01/app/oracle/oradata/DG1/
redo01_a.log redo02_a.log redo03_a.log
sysaux01.dbf
temp01.dbf
users01.dbf
redo01.log redo02.log
redo03.log
system01.dbf
undotbs01.dbf
关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
重新启动
SQL> startup
ORACLE instance started.
Total System Global Area 285212672
bytes
Fixed Size
1218992 bytes
Variable Size
83887696 bytes
Database Buffers
197132288 bytes
Redo
Buffers
2973696 bytes
ORA-00205: error in identifying control file, check alert
log for more info
警告日志出现如下错误
Wed
Jun 13 23:20:37 2012
ALTER DATABASE
MOUNT
Wed
Jun 13 23:20:37 2012
ORA-00202: control file:
'/u01/app/oracle/oradata/DG1/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Wed
Jun 13 23:20:40 2012
ORA-205 signalled during: ALTER DATABASE
MOUNT...
现在解决问题
将实例关闭，执行上面创建的恢复控制文件的脚本controlfile_trace1_1.sql
SQL> @controlfile_trace1_1.sql
ORACLE instance started.
Total System Global Area 285212672
bytes
Fixed Size
1218992 bytes
Variable Size
83887696 bytes
Database Buffers
197132288 bytes
Redo
Buffers
2973696 bytes
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
ORA-00283: recovery session canceled due to
errors
ORA-00264: no recovery required
ALTER SYSTEM ARCHIVE LOG ALL
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving
Database altered.
Tablespace altered.恢复成功
查看数据库当前状态
SQL> selectstatus from v$instance
STATUS
------------
OPEN
恢复成功！为了确保数据不丢失，我们应该定期将控制文件做追踪备份到trace文件，生成恢复控制文件的sql脚本，以防控制文件全部丢失之后，在没有归档模式下的全备份，我们可以通过这种方法恢复数据库。
3)在归档模式下，对数据库有完备，丢失全部控制文件
注意这里一定要清楚自己备份集中控制文件备份存放的位置，否则当你干掉全部控制文件后，进入RMAN模式是在NOMOUNT状态，此时你无法查用RMAN来看备份集中控制文件的备份位置，更何谈用备份集中的控制文件来恢复干掉的所有控制文件然后再恢复数据库。
先进入RMAN模式查看备份集中控制文件存放的位置
[oracle@DG1 ~]$ rman target sys/oracle@DG1
Recovery Manager: Release 10.2.0.1.0 - Production on Wed
Jun 13 22:09:16 2012
Copyright (c) 1982, 2005, Oracle. All rights
reserved.
connected to target database: DG1
(DBID=1762320829)
RMAN> list backup;
List
of Backup Sets
===================
BS
Key Type
LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
2
Incr 0
3.39G
DISK
00:08:22
13-JUN-12
BP Key: 2
Status: AVAILABLE Compressed:
NO Tag:
BACKUP_DG1_000001_061312022053
Piece Name: /home/oracle/DiskBackupLocation/02ndeh2i_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN
Ckp Time
Name
---- -- ---- ---------- --------- ----
1
0 Incr
675126
13-JUN-12 /u01/app/oracle/oradata/DG1/system01.dbf
2
0 Incr
675126
13-JUN-12 /u01/app/oracle/oradata/DG1/undotbs01.dbf
3
0 Incr
675126
13-JUN-12 /u01/app/oracle/oradata/DG1/sysaux01.dbf
4
0 Incr
675126
13-JUN-12 /u01/app/oracle/oradata/DG1/users01.dbf
5
0 Incr
675126
13-JUN-12
/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf
BS
Key Type
LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
3
Full
7.08M
DISK
00:00:05
13-JUN-12
BP Key: 3
Status: AVAILABLE Compressed:
NO Tag:
TAG20120613T142932
Piece Name:
/home/oracle/DiskBackupLocation/c-1762320829-20120613-00
Control File Included: Ckp SCN: 677193
Ckp time: 13-JUN-12
SPFILE Included: Modification time: 13-JUN-12
BS
Key
Size
Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------
---------------
4
1.14G
DISK
00:03:47
13-JUN-12
BP Key: 4
Status: AVAILABLE Compressed:
NO Tag:
BACKUP_DG1_000001_061312022053
Piece Name: /home/oracle/DiskBackupLocation/04ndehir_1_1
List of Archived Logs in backup set 4
Thrd Seq
Low
SCN
Low Time
Next SCN
Next Time
---- ------- ---------- --------- ---------- ---------
1
6
524597
20-MAY-12 528875
13-JUN-12
1
7
528875
13-JUN-12 528877
13-JUN-12
1
8
528877
13-JUN-12 528880
13-JUN-12
1
9
528880
13-JUN-12 529115
13-JUN-12
1
10
529115
13-JUN-12 529117
13-JUN-12
1
11
529117
13-JUN-12 529120
13-JUN-12
1
12
529120
13-JUN-12 529129
13-JUN-12
1
13
529129
13-JUN-12 529131
13-JUN-12
1
14
529131
13-JUN-12 529134
13-JUN-12
1
15
529134
13-JUN-12 529136
13-JUN-12
1
16
529136
13-JUN-12 529138
13-JUN-12
1
17
529138
13-JUN-12 529140
13-JUN-12
1
18
529140
13-JUN-12 529142
13-JUN-12
1
19
529142
13-JUN-12 529144
13-JUN-12
1
20
529144
13-JUN-12 529154
13-JUN-12
1
21
529154
13-JUN-12 529156
13-JUN-12
1
22
529156
13-JUN-12 529158
13-JUN-12
1
23
529158
13-JUN-12 529160
13-JUN-12
1
24
529160
13-JUN-12 529162
13-JUN-12
1
25
529162
13-JUN-12 529164
13-JUN-12
1
26
529164
13-JUN-12 529431
13-JUN-12
1
27
529431
13-JUN-12 529433
13-JUN-12
1
28
529433
13-JUN-12 529436
13-JUN-12
1
29
529436
13-JUN-12 529438
13-JUN-12
1
30
529438
13-JUN-12 529440
13-JUN-12
1
31
529440
13-JUN-12 529442
13-JUN-12
1
32
529442
13-JUN-12 529444
13-JUN-12
1
33
529444
13-JUN-12 529446
13-JUN-12
1
34
529446
13-JUN-12 529448
13-JUN-12
1
35
529448
13-JUN-12 529450
13-JUN-12
1
36
529450
13-JUN-12 529452
13-JUN-12
1
37
529452
13-JUN-12 529454
13-JUN-12
1
38
529454
13-JUN-12 529456
13-JUN-12
1
39
529456
13-JUN-12 529458
13-JUN-12
1
40
529458
13-JUN-12 529460
13-JUN-12
1
41
529460
13-JUN-12 529462
13-JUN-12
1
42
529462
13-JUN-12 529464
13-JUN-12
1
43
529464
13-JUN-12 529466
13-JUN-12
1
44
529466
13-JUN-12 529468
13-JUN-12
1
45
529468
13-JUN-12 529470
13-JUN-12
1
46
529470
13-JUN-12 534645
13-JUN-12
1
47
534645
13-JUN-12 539056
13-JUN-12
1
48
539056
13-JUN-12 543505
13-JUN-12
1
49
543505
13-JUN-12 547897
13-JUN-12
1
50
547897
13-JUN-12 552310
13-JUN-12
1
51
552310
13-JUN-12 556688
13-JUN-12
1
52
556688
13-JUN-12 561084
13-JUN-12
1
53
561084
13-JUN-12 565473
13-JUN-12
1
54
565473
13-JUN-12 569854
13-JUN-12
1
55
569854
13-JUN-12 574302
13-JUN-12
1
56
574302
13-JUN-12 579860
13-JUN-12
1
57
579860
13-JUN-12 586089
13-JUN-12
1
58
586089
13-JUN-12 592233
13-JUN-12
1
59
592233
13-JUN-12 598391
13-JUN-12
1
60
598391
13-JUN-12 604553
13-JUN-12
1
61
604553
13-JUN-12 610704
13-JUN-12
1
62
610704
13-JUN-12 616840
13-JUN-12
1
63
616840
13-JUN-12 623696
13-JUN-12
1
64
623696
13-JUN-12 631159
13-JUN-12
1
65
631159
13-JUN-12 637993
13-JUN-12
1
66
637993
13-JUN-12 644418
13-JUN-12
1
67
644418
13-JUN-12 650775
13-JUN-12
1
68
650775
13-JUN-12 659810
13-JUN-12
1
69
659810
13-JUN-12 677285
13-JUN-12
BS Key Type LV
Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
5
Full
7.08M
DISK
00:00:03
13-JUN-12
BP Key: 5
Status: AVAILABLE Compressed:
NO Tag:
TAG20120613T143358
Piece Name:
/home/oracle/DiskBackupLocation/c-1762320829-20120613-01
Control File Included: Ckp SCN: 679052
Ckp time: 13-JUN-12
SPFILE Included: Modification time: 13-JUN-12
妹的，全备份这么多次(由于使用了压力测试工具)，我的磁盘伤不起啊，做完这个实验立即干掉他们！可以看出上面红色部分BP Key
5是备份集中最新控制文件的存放位置，我们记住这个目录，稍后恢复控制文件时要使用。
查看控制文件
SQL> select '!rm '||name from
v$controlfile;
'!RM
'||NAME
-------------------------------------------------------------------------------
!rm
/u01/app/oracle/oradata/DG1/control01.ctl
!rm
/u01/app/oracle/oradata/DG1/control02.ctl
!rm
/u01/app/oracle/oradata/DG1/control03.ctl
将控制文件全部删除
SQL> !rm
/u01/app/oracle/oradata/DG1/control01.ctl
!rm
/u01/app/oracle/oradata/DG1/control02.ctl
!rm
/u01/app/oracle/oradata/DG1/control03.ctl
查看控制文件是否还存在
SQL> !ls
/u01/app/oracle/oradata/DG1
redo01_a.log
redo02.log
sysaux01.dbf
undotbs01.dbf
redo01.log
redo03_a.log
system01.dbf
users01.dbf
redo02_a.log
redo03.log
temp01.dbf
将数据库关闭，然后重新启动
SQL> shutdown immediate;
ORA-00210: cannot open the specified control
file
ORA-00202: control file:
'/u01/app/oracle/oradata/DG1/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
妹的，竟然正常关闭不了(之前做测试都能正常关闭)，直接强制关闭
SQL> shutdown abort;
ORACLE instance shut down.
重新启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 285212672
bytes
Fixed Size
1218992 bytes
Variable Size
88082000 bytes
Database Buffers
192937984 bytes
Redo
Buffers
2973696
bytes
ORA-00205: error in identifying control file, check alert
log for more info
出现报错信息，不能识别控制文件，这是肯定的，由于缺少控制文件，数据库在由nomount状态启动到mount状态时要读取控制文件中的内容，控制文件都木有了，怎么能启动到mount，还别说open了，所以在数据库只能启动到nomount状态。由于我们有归档模式下的RMAN全备，所以我们可以借助RMAN备份集来恢复参数文件。
SQL> select status from
v$instance;
STATUS
------------
STARTED
进入RMAN模式
[oracle@DG1 ~]$ rman target sys/oracle@DG1
Recovery Manager: Release 10.2.0.1.0 - Production on Wed
Jun 13 22:01:19 2012
Copyright (c) 1982, 2005, Oracle. All rights
reserved.
connected to target database: DG1 (not mounted)
现在从RMAN完备数据库的备份集中进行控制文件的恢复
RMAN> restore controlfile from
'/home/oracle/DiskBackupLocation/c-1762320829-20120613-01';
Starting restore at 13-JUN-12
using target database control file instead of
recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155
devtype=DISK
channel ORA_DISK_1: restoring control
file
channel ORA_DISK_1: restore complete, elapsed
time: 00:00:06
output
filename=/u01/app/oracle/oradata/DG1/control01.ctl
output
filename=/u01/app/oracle/oradata/DG1/control02.ctl
output
filename=/u01/app/oracle/oradata/DG1/control03.ctl
Finished restore at 13-JUN-12
可以看到控制文件已经由全备份集中的控制文件成功恢复
恢复控制完毕，查看控制文件
[oracle@DG1 ~]$ ls /u01/app/oracle/oradata/DG1/
control01.ctl
redo01_a.log
redo02.log
sysaux01.dbf
undotbs01.dbf
control02.ctl
redo01.log
redo03_a.log
system01.dbf
users01.dbf
control03.ctl
redo02_a.log
redo03.log
temp01.dbf
将数据库启动到mount状态恢复数据库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1成功启动到mount状态
执行RMAN恢复数据库的操作
RMAN> recover database;
Starting recover at 13-JUN-12
Starting implicit crosscheck backup at 13-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 13-JUN-12
Starting implicit crosscheck copy at 13-JUN-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-JUN-12
searching for all files in the recovery area
cataloging files...
no
files cataloged
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 70 is already on disk as file
/u01/app/oracle/oradata/DG1/redo03.log
archive log thread 1 sequence 71 is already on disk as file
/u01/app/oracle/oradata/DG1/redo01.log
archive log filename=/u01/app/oracle/oradata/DG1/redo03.log
thread=1 sequence=70
archive log filename=/u01/app/oracle/oradata/DG1/redo01.log
thread=1 sequence=71
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-JUN-12
数据库恢复成功
打开数据库
RMAN> alter database open
resetlogs;
database opened RMAN>成功恢复
由于数据库一直处于归档模式，所以从归档下的完备份集恢复数据库，可以保证数据不丢失，当由备份集恢复控制文件之后，接着会从数据库的归档日志和undo日志文件中读取SCN信息，将恢复的控制文件更新到最新状态。
五、总结
没有不丢失数据的数据库，所以经常备份数据，是很有必要的，也许某一天数据库就挂掉了，如果有备份，恢复当然容易，如果没有，也许结果就不一样了………无论你的数据库是以何种方式恢复的，一定切记恢复后，做一次归档下的全备，这样就可以尽可能降低恢复数据库后数据库再次挂掉后丢失数据的可能性。
-----Jialin.Lee
2012-06-13
806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
