Performing User-Managed Database-18.5、Restoring Control Files-CSDN博客

本文链接：https://blog.csdn.net/offbeatmine/article/details/28429339

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

EXAMPLE 5 651283
/oracle/oradata/boss/example01.dbf

TESTTBS01 6 651283
/oracle/oradata/boss/testtbs01_01.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

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;

（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

???? ??? ???
------------------------------ ---------- ----------
???
--------------------------------------------------------------------------------
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