A-A+

数据库控制文件的5种备份与6种恢复场景的模拟

2013年01月14日 Backup&Recovery, Database 暂无评论 阅读 1,110 次

控制文件的备份与恢复示例
数据库控制文件的重要性毋庸置疑,也是数据库中重要文件中的一种,下边将会从控制文件的5种备份和6种场景的恢复进行讨论,基本涵盖了控制文件的各种备份恢复场景。

1.备份控制文件
1.1 在rman下设置自动控制文件自动备份策略
[oracle@ora10gr2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 14 01:34:35 2013

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

connected to target database: ORA10GR2 (DBID=4093928674)

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
说明:这样设置之后,在数据库结构发生变化的时候,当前控制文件会自动进行备份,当然也会包含参数文件的备份。再者就是在数据库在使用rman进行备份的时候也会自动备份当前控制文化。
如果数据库结构没有发生变化,且没有进行rman备份,怎可以用下边的方法人为进行控制文件的备份。

1.2 在rman人为进行控制文件的备份
RMAN> backup current controlfile;

Starting backup at 14-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 14-JAN-13
channel ORA_DISK_1: finished piece 1 at 14-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/backupset/2013_01_14/o1_mf_ncnnf_TAG20130114T013830_8h7njt47_.bkp tag=TAG20130114T013830 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:11
Finished backup at 14-JAN-13

Starting Control File and SPFILE Autobackup at 14-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_14/o1_mf_s_804649121_8h7nk249_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-JAN-13

此处可以看到控制文件备份到的目的地已经也可以看出参数文件也一并备份了。

1.3 可以将控制文件备份到一个二进制文件
RMAN> sql "alter database backup controlfile to ''/tmp/controlfile.bak''";

sql statement: alter database backup controlfile to ''/tmp/controlfile.bak''

1.4 可以将控制文件备份到一个可读的trace文件
RMAN> sql "alter database backup controlfile to trace as ''/tmp/controlfile.trc''";

sql statement: alter database backup controlfile to trace as ''/tmp/controlfile.trc''

1.5 可以在进行rman备份的同时备份当前控制文件
RMAN> backup tablespace test1 include current controlfile;

Starting backup at 14-JAN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/tmp/test1.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-13
channel ORA_DISK_1: finished piece 1 at 14-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T014443_8h7nwd7p_.bkp tag=TAG20130114T014443 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 14-JAN-13
channel ORA_DISK_1: finished piece 1 at 14-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/backupset/2013_01_14/o1_mf_ncnnf_TAG20130114T014443_8h7nwj3d_.bkp tag=TAG20130114T014443 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 14-JAN-13

Starting Control File and SPFILE Autobackup at 14-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_14/o1_mf_s_804649491_8h7nwn9j_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-JAN-13

2.模拟控制文件出错
查看当前控制文件位置
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ora10g
r2/control01.ctl, /u01/app/ora
cle/oradata/ora10gr2/control02
.ctl, /u01/app/oracle/oradata/
ora10gr2/control03.ctl
人为删除控制文件
[oracle@ora10gr2 11.1]$ mv /u01/app/oracle/oradata/ora10gr2/control01.ctl /u01/app/oracle/oradata/ora10gr2/control01.ctl_bak
[oracle@ora10gr2 11.1]$ mv /u01/app/oracle/oradata/ora10gr2/control02.ctl /u01/app/oracle/oradata/ora10gr2/control02.ctl_bak
[oracle@ora10gr2 11.1]$ mv /u01/app/oracle/oradata/ora10gr2/control03.ctl /u01/app/oracle/oradata/ora10gr2/control03.ctl_bak
[oracle@ora10gr2 11.1]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 14 01:55:01 2013

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora10gr2/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 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 230690580 bytes
Database Buffers 373293056 bytes
Redo Buffers 2969600 bytes
ORA-00205: error in identifying control file, check alert log for more info
此处会发现,当控制文件删除后,在数据库正常关闭的时候需要写信息到控制文件故报错,当数据库正常打开时,在从nomount到mount要去确认控制文件的时候发现不存在报错。

3.恢复控制文件
3.1 使用1.2或者1.5中关于控制文件的备份恢复控制文件
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 230690580 bytes
Database Buffers 373293056 bytes
Redo Buffers 2969600 bytes

[oracle@ora10gr2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 14 02:01:23 2013

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

connected to target database: ora10gr2 (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_14/o1_mf_s_804649121_8h7nk249_.bkp'
2> ;

Starting restore at 14-JAN-13
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:10
output filename=/u01/app/oracle/oradata/ora10gr2/control01.ctl
output filename=/u01/app/oracle/oradata/ora10gr2/control02.ctl
output filename=/u01/app/oracle/oradata/ora10gr2/control03.ctl
Finished restore at 14-JAN-13
RMAN> recover database;

Starting recover at 14-JAN-13
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 14-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 14-JAN-13

Starting implicit crosscheck copy at 14-JAN-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-JAN-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_14/o1_mf_s_804649121_8h7nk249_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_14/o1_mf_s_804649491_8h7nwn9j_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T014443_8h7nwd7p_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/backupset/2013_01_14/o1_mf_ncnnf_TAG20130114T014443_8h7nwj3d_.bkp

using channel ORA_DISK_1
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 1

starting media recovery

archive log thread 1 sequence 35 is already on disk as file /u01/app/oracle/oradata/ora10gr2/redo01.log
archive log filename=/u01/app/oracle/oradata/ora10gr2/redo01.log thread=1 sequence=35
media recovery complete, elapsed time: 00:00:05
Finished recover at 14-JAN-13

SQL> alter database mount;

Database altered.
SQL> alter database open RESETLOGS;

Database altered.

SQL> select * from test.test;

ID NAME INSERTTIM
---------- -------------------- ---------
1 yallonking 13-JAN-13

3.2 使用人为备份的控制文件恢复控制文件
首先需要修改参数文件中指定的控制文件
[oracle@ora10gr2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 14 02:11:30 2013

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

Connected to an idle instance.

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.
[oracle@ora10gr2 ~]$ cat /tmp/pfile.txt | grep *.control_files
*.control_files='/tmp/controlfile.bak'
[oracle@ora10gr2 ~]$
此处是将参数文件中的控制文路径改为1.3中备份的二进制文件的路径。
使用pfile启动数据库到mount
再在rman下恢复同步数据库
[oracle@ora10gr2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 14 02:17:32 2013

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

connected to target database: ORA10GR2 (DBID=4093928674, not open)

RMAN> recover database;

Starting recover at 14-JAN-13
Starting implicit crosscheck backup at 14-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 14-JAN-13

Starting implicit crosscheck copy at 14-JAN-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-JAN-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/archivelog/2013_01_14/o1_mf_1_34_8h7p4gz7_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/archivelog/2013_01_14/o1_mf_1_35_8h7p3zrf_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/archivelog/2013_01_14/o1_mf_1_33_8h7p41jk_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_14/o1_mf_s_804649491_8h7nwn9j_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_14/o1_mf_s_804650799_8h7p5oln_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T014443_8h7nwd7p_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORA10GR2/backupset/2013_01_14/o1_mf_ncnnf_TAG20130114T014443_8h7nwj3d_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 35 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA10GR2/archivelog/2013_01_14/o1_mf_1_35_8h7p3zrf_.arc
archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/oradata/ora10gr2/redo01.log
archive log filename=/u01/app/oracle/flash_recovery_area/ORA10GR2/archivelog/2013_01_14/o1_mf_1_35_8h7p3zrf_.arc thread=1 sequence=35
archive log filename=/u01/app/oracle/oradata/ora10gr2/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:05
Finished recover at 14-JAN-13

SQL> alter database open RESETLOGS;

Database altered.

SQL> select * from test.test;

ID NAME INSERTTIM
---------- -------------------- ---------
1 yallonking 13-JAN-13
正常关闭库,将控制文件复用到以前的形式,需要拷贝文件,修改pfile,创建spfile,具体如下:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@ora10gr2 ~]$ vi /tmp/pfile.txt
[oracle@ora10gr2 ~]$ cat /tmp/pfile.txt | grep *.control_files
*.control_files='/u01/app/oracle/oradata/ora10gr2/control01.ctl','/u01/app/oracle/oradata/ora10gr2/control02.ctl','/u01/app/oracle/oradata/ora10gr2/control03.ctl'
[oracle@ora10gr2 ~]$ cp /tmp/controlfile.bak /u01/app/oracle/oradata/ora10gr2/control01.ctl
[oracle@ora10gr2 ~]$ cp /tmp/controlfile.bak /u01/app/oracle/oradata/ora10gr2/control02.ctl
[oracle@ora10gr2 ~]$ cp /tmp/controlfile.bak /u01/app/oracle/oradata/ora10gr2/control03.ctl
[oracle@ora10gr2 ~]$ exit
exit

SQL> startup pfile='/tmp/pfile.txt';
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 234884884 bytes
Database Buffers 369098752 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='/tmp/pfile.txt';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 234884884 bytes
Database Buffers 369098752 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ora10g
r2/control01.ctl, /u01/app/ora
cle/oradata/ora10gr2/control02
.ctl, /u01/app/oracle/oradata/
ora10gr2/control03.ctl

3.3 利用可读控制文件的trace文件重建控制文件
3.3.1 在数据库关闭状态下控制文件的丢失
备份控制文件可读trace文件
正常关闭库后,破坏当前所有控制文件
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control01.ctl /u01/app/oracle/oradata/ora10gr2/control01.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control02.ctl /u01/app/oracle/oradata/ora10gr2/control02.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control03.ctl /u01/app/oracle/oradata/ora10gr2/control03.ctl_bak
[oracle@ora10gr2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 14 02:30:31 2013

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown abort
ORACLE instance shut down.
将库启动至nomount阶段
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 197136148 bytes
Database Buffers 406847488 bytes
Redo Buffers 2969600 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10GR2" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ora10gr2/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/ora10gr2/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/ora10gr2/redo03.log' SIZE 50M
11 DATAFILE
12 '/u01/app/oracle/oradata/ora10gr2/system01.dbf',
13 '/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf',
14 '/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ora10gr2/users01.dbf',
16 '/u01/app/oracle/oradata/ora10gr2/test01.dbf',
17 '/u01/app/oracle/oradata/ora10gr2/ogg01.dbf'
18 CHARACTER SET WE8ISO8859P1
19 ;

Control file created.

SQL> alter database open;

Database altered.
添加临时数据文件
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora10gr2/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

3.3.2 在数据库运行状态下控制文件的丢失,且直接以abort方式关掉库的恢复
SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> !
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control01.ctl /u01/app/oracle/oradata/ora10gr2/control01.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control02.ctl /u01/app/oracle/oradata/ora10gr2/control02.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control03.ctl /u01/app/oracle/oradata/ora10gr2/control03.ctl_bak
[oracle@ora10gr2 ~]$ exit
exit

SQL> shutdown abort --注意:此处是直接以abort方式关掉库的
ORACLE instance shut down.
SQL> startup nomount; --此处还是以nomount模式启动
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 197136148 bytes
Database Buffers 406847488 bytes
Redo Buffers 2969600 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10GR2" NORESETLOGS FORCE LOGGING ARCHIVELOG --重建控制文件
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ora10gr2/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/ora10gr2/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/ora10gr2/redo03.log' SIZE 50M
11 DATAFILE
12 '/u01/app/oracle/oradata/ora10gr2/system01.dbf',
13 '/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf',
14 '/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ora10gr2/users01.dbf',
16 '/u01/app/oracle/oradata/ora10gr2/test01.dbf',
17 '/u01/app/oracle/oradata/ora10gr2/ogg01.dbf'
18 CHARACTER SET WE8ISO8859P1
19 ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora10gr2/system01.dbf'

SQL> recover database; --此处是将控制文件和各个文件头同步一次
Media recovery complete.
SQL> alter database open; --正常打开库

Database altered.
添加临时数据文件
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora10gr2/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

3.3.3 在数据库运行状态下控制文件的丢失,且直接以immediate方式正常关掉库的恢复

[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control01.ctl /u01/app/oracle/oradata/ora10gr2/control01.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control02.ctl /u01/app/oracle/oradata/ora10gr2/control02.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control03.ctl /u01/app/oracle/oradata/ora10gr2/control03.ctl_bak
[oracle@ora10gr2 ~]$ exit
exit

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down. --此刻能够正常关闭库,是因为OS还没有释放文件句柄,所以还没报错,但是在下次启动时会报错
SQL> startup
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 197136148 bytes
Database Buffers 406847488 bytes
Redo Buffers 2969600 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10GR2" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ora10gr2/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/ora10gr2/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/ora10gr2/redo03.log' SIZE 50M
11 DATAFILE
12 '/u01/app/oracle/oradata/ora10gr2/system01.dbf',
13 '/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf',
14 '/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ora10gr2/users01.dbf',
16 '/u01/app/oracle/oradata/ora10gr2/test01.dbf',
17 '/u01/app/oracle/oradata/ora10gr2/ogg01.dbf'
18 CHARACTER SET WE8ISO8859P1
19 ;

Control file created.

SQL> alter database open; --此处不需要恢复即可打开库(因为之前的文件句柄问题,已经将同步信息写到控制文件了)

Database altered.
添加临时文件
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora10gr2/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

3.3.4 在数据库运行状态下控制文件的丢失,并用旧的控制文件备份打开新库
备份当前控制文件trace后,添加表空间,删除所有控制文件,进行恢复
SQL> alter database backup controlfile to trace as '/tmp/controlfile.trc';

Database altered.

SQL> create tablespace test1 datafile '/tmp/test01.bdf' size 1m;

Tablespace created.

SQL> !
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control01.ctl /u01/app/oracle/oradata/ora10gr2/control01.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control02.ctl /u01/app/oracle/oradata/ora10gr2/control02.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control03.ctl /u01/app/oracle/oradata/ora10gr2/control03.ctl_bak
[oracle@ora10gr2 ~]$ exit
exit

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 201330452 bytes
Database Buffers 402653184 bytes
Redo Buffers 2969600 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10GR2" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ora10gr2/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/ora10gr2/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/ora10gr2/redo03.log' SIZE 50M
11 DATAFILE
12 '/u01/app/oracle/oradata/ora10gr2/system01.dbf',
13 '/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf',
14 '/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ora10gr2/users01.dbf',
16 '/u01/app/oracle/oradata/ora10gr2/test01.dbf',
'/u01/app/oracle/oradata/ora10gr2/ogg01.dbf'
17 18 CHARACTER SET WE8ISO8859P1
19 ;

Control file created. --此处控制文件用旧的trace备份文件可以重建成功,也是因为文件句柄的原因。

SQL> alter database open; --能够正常打开库,同上

Database altered.
SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'TEST%';

TABLESPACE_NAME
------------------------------
TEST
TEST1
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora10gr2/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.
--此处能够查到新建的表空间,也证明了之前能够正常关闭库,并且相关信息通过还未释放的文件句柄写到了控制文件。

下面排除文件句柄的影响,重新测试以上2个例子
3.3.5 在数据库运行状态下控制文件的丢失,且直接重启主机的恢复,这个和3.3.2以abort关库的情形是一致的。
[oracle@ora10gr2 ~]$ rm -rf /tmp/*.trc
[oracle@ora10gr2 ~]$ exit
exit

SQL> alter database backup controlfile to trace as '/tmp/controlfile.trc';

Database altered.

SQL> !
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control01.ctl /u01/app/oracle/oradata/ora10gr2/control01.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control02.ctl /u01/app/oracle/oradata/ora10gr2/control02.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control03.ctl /u01/app/oracle/oradata/ora10gr2/control03.ctl_bak
[oracle@ora10gr2 ~]$ su - root
Password:
[root@ora10gr2 ~]# reboot --直接重启主机,排除文件句柄的影响

Broadcast message from root (pts/0) (Mon Jan 14 06:15:02 2013):

The system is going down for reboot NOW!
正常启动数据库
[oracle@ora10gr2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 14 06:18:39 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 201330452 bytes
Database Buffers 402653184 bytes
Redo Buffers 2969600 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10GR2" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ora10gr2/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/ora10gr2/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/ora10gr2/redo03.log' SIZE 50M
11 DATAFILE
12 '/u01/app/oracle/oradata/ora10gr2/system01.dbf',
13 '/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf',
14 '/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ora10gr2/users01.dbf',
16 '/u01/app/oracle/oradata/ora10gr2/test01.dbf',
17 '/u01/app/oracle/oradata/ora10gr2/ogg01.dbf'
18 CHARACTER SET WE8ISO8859P1
19 ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora10gr2/system01.dbf'

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora10gr2/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

3.3.6 在数据库运行状态下控制文件的丢失,且直接重启主机,用旧的控制文件trace进行恢复
备份当前控制文件trace后,添加表空间,删除所有控制文件,进行恢复
SQL> alter database backup controlfile to trace as '/tmp/controlfile.trc';

Database altered.

SQL> create tablespace test1 datafile '/tmp/test01.bdf' size 1m;

Tablespace created.

SQL> !
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control01.ctl /u01/app/oracle/oradata/ora10gr2/control01.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control02.ctl /u01/app/oracle/oradata/ora10gr2/control02.ctl_bak
[oracle@ora10gr2 ~]$ mv /u01/app/oracle/oradata/ora10gr2/control03.ctl /u01/app/oracle/oradata/ora10gr2/control03.ctl_bak
[oracle@ora10gr2 ~]$ su - root
Password:
[root@ora10gr2 ~]# reboot --此处重启主机,排除未释放的文件句柄的影响

Broadcast message from root (pts/0) (Mon Jan 14 06:40:39 2013):

The system is going down for reboot NOW!
SQL> startup
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 201330452 bytes
Database Buffers 402653184 bytes
Redo Buffers 2969600 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10GR2" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/ora10gr2/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/ora10gr2/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/ora10gr2/redo03.log' SIZE 50M
11 DATAFILE
12 '/u01/app/oracle/oradata/ora10gr2/system01.dbf',
13 '/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf',
14 '/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf',
15 '/u01/app/oracle/oradata/ora10gr2/users01.dbf',
16 '/u01/app/oracle/oradata/ora10gr2/test01.dbf',
17 '/u01/app/oracle/oradata/ora10gr2/ogg01.dbf',
18 '/tmp/test01.bdf' --此处只需将缺失的数据文件添加上即可,而这个文件名可以从alert.log中获取到。
19 CHARACTER SET WE8ISO8859P1
20 ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora10gr2/system01.dbf'

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora10gr2/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'TEST%';

TABLESPACE_NAME
------------------------------
TEST
TEST1

至此,控制文件的5种备份和6种场景的恢复已经进行了讨论,基本涵盖了控制文件的各种备份恢复场景,希望大家积极提出各种场景,我来模拟进行恢复。

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: