Performing User-Managed Database-18.5、Restoring Control Files
18.5、Restoring Control Files
18.5.1、Restore Lost Copy of a Multiplexed Control File
18.5.1.1、Copying a Multiplexed Control File to a Nondefault Location
如果丢失控制文件的磁盘和文件系统不能再关联，那么必须改动CONTROL_FILES初始化參数来表明丢失的控制文件的新位置。
（1）数据库读或写控制文件直接shutdown
（2）拷贝正确的控制文件到指定的位置
（3）改动CONTROL_FILES初始化參数
（4）启动，mount，open数据库
模拟1、某块磁盘介质失败，丢失了控制文件，把控制文件放到一个新的位置
（1）
SQL> select status from v$instance;
STATUS
------------
OPEN
$ rm -rf control01.ctl
SQL> alter system checkpoint;
SQL> select dbid,name,log_mode,checkpoint_change# from v$database;
DBID NAME LOG_MODE CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------------ ------------------
1375601832 BOSS ARCHIVELOG 577511
SQL>
alter tablespace testtbs01
add datafile '/oracle/oradata/boss/testtbs01_02.dbf' size 10m;
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oracle/oradata/boss/control01
.ctl, /oracle/oradata/boss/con
trol02.ctl, /oracle/oradata/bo
ss/control03.ctl
SQL> shutdown immediate;
SQL> startup open;
ORA-00205: error in identifying control file, check alert log for more info
$ cd $ORACLE_BASE/admin/boss/bdump
$ vi alert_boss.log
Wed Jun 4 16:11:38 2014
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=17321
Wed Jun 4 16:11:38 2014
starting up 1 shared server(s) ...
Wed Jun 4 16:11:39 2014
ALTER DATABASE MOUNT
Wed Jun 4 16:11:39 2014
ORA-00202: control file: '/oracle/oradata/boss/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Wed Jun 4 16:11:42 2014
ORA-205 signalled during: ALTER DATABASE MOUNT...
（2）
$ cp -rf /oracle/oradata/boss/control02.ctl /oradata/boss/control01.ctl
（3）
SQL> create pfile from spfile;
$ cd $ORACLE_HOME/dbs
$ vi initboss.ora
*.control_files='/oradata/boss/control01.ctl','/oracle/oradata/boss/control02.ctl','/oracle/oradata/boss/control03.ctl'
（4）
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup open;
SQL> select status from v$instance;
STATUS
------------
OPEN
18.5.2、Restore Control File from Backup After Loss of All Current Control Files--即二进制控制文件
如果一个永久的介质失败损坏了全部控制文件。有控制文件的备份。能够完毕还原控制文件。
控制文件不能訪问。能够启动实例，可是不能mount数据库。
直到控制文件能够再次訪问
数据库才干mount和open。还原一个备份的控制文件，必须alter database open resetlogs。
还原控制文件的过程依赖于重做日志是否可用（是否存在）：
模拟1、创建控制文件的trace文件，表空间testtbs01创建表test01，然后多次切换日志，备份归档日志，删除全部控制文件
（1）
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TESTTBS01 ONLINE
TESTTBS02 OFFLINE
TESTTBS03 READ ONLY
（2）
SQL> alter database backup controlfile to '/oradata/bossbak/20140605/control.bak' reuse;
$ cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_05
$ ll
总用量 4188
-rw-r----- 1 oracle oinstall 1119744 6月 5 09:49 o1_mf_1_22_9rzm5zy9_.arc
-rw-r----- 1 oracle oinstall 1551360 6月 5 10:08 o1_mf_1_23_9rzn9jnv_.arc
-rw-r----- 1 oracle oinstall 485376 6月 5 10:53 o1_mf_1_24_9rzpxbt8_.arc
-rw-r----- 1 oracle oinstall 1125888 6月 5 11:19 o1_mf_1_25_9rzrg1n0_.arc
（3）
SQL> truncate table test01;
SQL> drop table test01;
SQL> purge table test01;
SQL> create table test01 (id number, name varchar2(30)) tablespace testtbs01;
SQL> insert into test01 values(1, 'aaaaa');
SQL> insert into test01 values(2, 'bbbbb');
SQL> insert into test01 values(3, 'ccccc');
SQL> commit;
（4）##恢复时须要26
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 26 NO CURRENT 646383
2 1 24 YES INACTIVE 604101
3 1 25 YES INACTIVE 625062
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 29 NO CURRENT 654804
2 1 27 YES INACTIVE 654799
3 1 28 YES INACTIVE 654801
（5）备份新的归档日志
$ pwd
/oracle/flash_recovery_area/BOSS/archivelog/2014_06_05
$ cp -rf * /oradata/bossbak/20140605/
（6）删除全部控制文件。删除今天的全部归档
$ cd $ORACLE_BASE/oradata/boss
$ rm -rf *.ctl
$ cd /oradata/boss
$ rm -rf control01.ctl
$ cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_05
$ rm -rf *
（7）
##居然能创建表空间
SQL> create tablespace testtbs04
2 datafile '/oracle/oradata/boss/testtbs04_01.dbf' size 10m
3 autoextend on next 1m maxsize unlimited
4 logging
5 extent management local autoallocate
6 blocksize 8k
7 segment space management auto
8 flashback on;
Tablespace created.
SQL> drop tablespace testtbs04 including contents and datafiles;
Tablespace dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open;
ORA-00205: error in identifying control file, check alert log for more info
（8）把备份的二进制控制文件复制到control_files指定的全部位置
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oradata/boss/control01.ctl, /oracle/oradata/boss/control02.ctl, /oracle/oradata/boss/control03.ctl
$ cp -rf control.bak /oradata/boss/control01.ctl
$ cp -rf control.bak /oracle/oradata/boss/control02.ctl
$ cp -rf control.bak /oracle/oradata/boss/control03.ctl
SQL> select dbid,name,log_mode,checkpoint_change# from v$database;
select dbid,name,log_mode,checkpoint_change# from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount;
SQL> select dbid,name,log_mode,checkpoint_change# from v$database;
DBID NAME LOG_MODE CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------------ ------------------
1375601832 BOSS ARCHIVELOG 651283
SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header;
FILE# NAME STATUS CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
1 /oracle/oradata/boss/system01.dbf ONLINE 656030
2 /oracle/oradata/boss/undotbs01.dbf ONLINE 656030
3 /oracle/oradata/boss/sysaux01.dbf ONLINE 656030
4 /oracle/oradata/boss/users01.dbf ONLINE 656030
5 /oracle/oradata/boss/example01.dbf ONLINE 656030
6 /oracle/oradata/boss/testtbs01_01.dbf ONLINE 656030
7 /oracle/oradata/boss/testtbs01_02.dbf ONLINE 656030
8 /oracle/oradata/boss/testtbs02_01.dbf OFFLINE 652783
9 /oracle/oradata/boss/testtbs03_01.dbf ONLINE 652799
SQL> select
2 ts.name "表空间名"
3 , df.file# "文件号"
4 , df.checkpoint_change# "检查点"
5 , df.name "文件名称"
6 from v$tablespace ts,v$datafile df
7 where ts.ts#=df.ts#
8 order by df.file#;
???? ?
?? ??
?
------------------------------ ---------- ----------
???
------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM 1 651283
/oracle/oradata/boss/system01.dbf
UNDOTBS1 2 651283
/oracle/oradata/boss/undotbs01.dbf
SYSAUX 3 651283
/oracle/oradata/boss/sysaux01.dbf
?
??
?
??
? ???
------------------------------ ---------- ----------
??
?
------------------------------------------------------------------------------------------------------------------------------------------------------
USERS 4 651283
/oracle/oradata/boss/users01.dbf
EXAMPLE 5 651283
/oracle/oradata/boss/example01.dbf
TESTTBS01 6 651283
/oracle/oradata/boss/testtbs01_01.dbf
???? ???
??
?
------------------------------ ---------- ----------
???
------------------------------------------------------------------------------------------------------------------------------------------------------
TESTTBS01 7 651283
/oracle/oradata/boss/testtbs01_02.dbf
TESTTBS02 8 652783
/oracle/oradata/boss/testtbs02_01.dbf
TESTTBS03 9 652799
/oracle/oradata/boss/testtbs03_01.dbf
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 26 NO CURRENT 646383
3 1 25 YES INACTIVE 625062
2 1 24 YES INACTIVE 604101
（9）
SQL> recover database using backup controlfile；
ORA-00279: change 652808 generated at 06/05/2014 11:19:29 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_05/o1_mf_1_26_%u_.arc
ORA-00280: change 652808 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_05/o1_mf_1_26_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_05/o1_mf_1_26_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 652808 generated at 06/05/2014 11:19:29 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_05/o1_mf_1_26_%u_.arc
ORA-00280: change 652808 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
$ cp -rf *.arc /oracle/flash_recovery_area/BOSS/archivelog/2014_06_05
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 652808 generated at 06/05/2014 11:19:29 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_05/o1_mf_1_26_%u_.arc
ORA-00280: change 652808 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_05/o1_mf_1_26_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_05/o1_mf_1_26_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
##终极ORA-01152解决方法
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL> startup force;
SQL> alter database open resetlogs;
二次測试：应用归档日志和重做日志（依据大小）
（1）
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TESTTBS01 ONLINE
TESTTBS02 OFFLINE
TESTTBS03 READ ONLY
（2）
SQL> alter database backup controlfile to '/oradata/bossbak/20140606/control.bak' reuse;
##全备后产生的全部归档
$ cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_06
$ ll
总用量 1084
-rw-r----- 1 oracle oinstall 1109504 6月 6 09:27 o1_mf_1_5_9s268l7n_.arc
$ cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_05
$ ll
总用量 2468
-rw-r----- 1 oracle oinstall 2513408 6月 5 18:03 o1_mf_1_2_9s0j2p1s_.arc
-rw-r----- 1 oracle oinstall 4608 6月 5 18:03 o1_mf_1_3_9s0j2yhy_.arc
-rw-r----- 1 oracle oinstall 2048 6月 5 18:03 o1_mf_1_4_9s0j338f_.arc
（3）
SQL> truncate table test03;
truncate table test03
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table test03 (id number, name varchar2(30)) tablespace testtbs01;
SQL> insert into test03 values(101, 'aaaaa');
SQL> insert into test03 values(101, 'bbbbb');
SQL> commit;
（4）
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 4 YES INACTIVE 675809
2 1 5 YES INACTIVE 675812
3 1 6 NO CURRENT 696775
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 7 YES ACTIVE 697245
2 1 8 YES ACTIVE 697253
3 1 9 NO CURRENT 697256
（5）备份全备后全部的归档日志
$ cd /oracle/flash_recovery_area/BOSS/archivelog/
$ ll
总用量 20
drwxr-x--- 2 oracle oinstall 4096 5月 27 13:50 2014_05_27
drwxr-x--- 2 oracle oinstall 4096 6月 3 15:01 2014_06_03
drwxr-x--- 2 oracle oinstall 4096 6月 4 17:59 2014_06_04
drwxr-x--- 2 oracle oinstall 4096 6月 5 18:09 2014_06_05
drwxr-x--- 2 oracle oinstall 4096 6月 6 09:34 2014_06_06
$ cp -rf 2014_06_05 /oradata/bossbak/20140606/
$ cp -rf 2014_06_06 /oradata/bossbak/20140606/
（6）删除全部控制文件，删除今天的全部归档
$ cd $ORACLE_BASE/oradata/boss
$ rm -rf *.ctl
$ cd /oradata/boss
$ rm -rf control01.ctl
$ cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_06
$ rm -rf *
（7）
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open;
ORA-00205: error in identifying control file, check alert log for more info
（8）把备份的二进制控制文件复制到control_files指定的全部位置
SQL> show parameter control_files;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_files string
/oradata/boss/control01.ctl, /
oracle/oradata/boss/control02.
ctl, /oracle/oradata/boss/cont
rol03.ctl
$ cp -rf control.bak /oradata/boss/control01.ctl
$ cp -rf control.bak /oracle/oradata/boss/control02.ctl
$ cp -rf control.bak /oracle/oradata/boss/control03.ctl
SQL> alter database mount;
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
696776
SQL> select
2 ts.name "表空间名"
3 , df.file# "文件号"
4 , df.checkpoint_change# "检查点"
5 , df.name "文件名称"
6 from v$tablespace ts,v$datafile df
7 where ts.ts#=df.ts#
8 order by df.file#;
???? ?
?? ???
------------------------------ ---------- ----------
?
??
--------------------------------------------------------------------------------
SYSTEM 1 696776
/oracle/oradata/boss/system01.dbf
UNDOTBS1 2 696776
/oracle/oradata/boss/undotbs01.dbf
SYSAUX 3 696776
/oracle/oradata/boss/sysaux01.dbf
?
??? ???
???
------------------------------ ---------- ----------
??
?
--------------------------------------------------------------------------------
USERS 4 696776
/oracle/oradata/boss/users01.dbf
EXAMPLE 5 696776
/oracle/oradata/boss/example01.dbf
TESTTBS01 6 696776
/oracle/oradata/boss/testtbs01_01.dbf
??
?? ??? ?
??
------------------------------ ---------- ----------
???
--------------------------------------------------------------------------------
TESTTBS01 7 696776
/oracle/oradata/boss/testtbs01_02.dbf
TESTTBS02 8 652783
/oracle/oradata/boss/testtbs02_01.dbf
TESTTBS03 9 652799
/oracle/oradata/boss/testtbs03_01.dbf
SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header;
FILE# NAME STATUS CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
1 /oracle/oradata/boss/system01.dbf ONLINE 697985
2 /oracle/oradata/boss/undotbs01.dbf ONLINE 697985
3 /oracle/oradata/boss/sysaux01.dbf ONLINE 697985
4 /oracle/oradata/boss/users01.dbf ONLINE 697985
5 /oracle/oradata/boss/example01.dbf ONLINE 697985
6 /oracle/oradata/boss/testtbs01_01.dbf ONLINE 697985
7 /oracle/oradata/boss/testtbs01_02.dbf ONLINE 697985
8 /oracle/oradata/boss/testtbs02_01.dbf OFFLINE 652783
9 /oracle/oradata/boss/testtbs03_01.dbf ONLINE 652799
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 4 YES INACTIVE 675809
3 1 6 NO CURRENT 696775
2 1 5 YES INACTIVE 675812
（9）
SQL> recover database using backup controlfile;
ORA-00279: change 696968 generated at 06/06/2014 09:27:45 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_6_%u_.arc
ORA-00280: change 696968 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto;
ORA-00308: cannot open archived log 'auto;'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 697245 generated at 06/06/2014 09:34:44 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_7_%u_.arc
ORA-00280: change 697245 for thread 1 is in sequence #7
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_6_9s26onjy_.arc' no longer needed
for this recovery
ORA-00279: change 697253 generated at 06/06/2014 09:34:46 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_8_%u_.arc
ORA-00280: change 697253 for thread 1 is in sequence #8
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_7_9s26opmx_.arc' no longer needed
for this recovery
ORA-00279: change 697256 generated at 06/06/2014 09:34:51 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_9_%u_.arc
ORA-00280: change 697256 for thread 1 is in sequence #9
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_8_9s26ov73_.arc' no longer needed
for this recovery
ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_9_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile;
ORA-00279: change 697256 generated at 06/06/2014 09:34:51 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_06/o1_mf_1_9_%u_.arc
ORA-00280: change 697256 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ##一个一个的查找重做日志文件，应用重做日志文件
/oracle/oradata/boss/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
SQL> alter database open resetlogs;
SQL> select status from v$instance;
STATUS
------------
OPEN
posted on 2019-05-01 11:35 xfgnongmin 阅读(161) 评论(0) 编辑 收藏 举报
