oracle数据库控制文件的备份和恢复之一手动备份和恢复

1 环境准备，创建测试表，准备表中数据

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
sql>CREATE TABLE "SYS"."TEST_CONTROL"("CURR_TIME" DATE) 
#####更改会话显示时间格式，查看表数据时可以看到区别。
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select * from test_control;
CURR_TIME
-------------------
2019-07-21 16:25:33

2 查看控制文件配置

SQL> show parameter control
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/orcl11
                                                 g/control01.ctl, /u01/app/orac
                                                 le/fast_recovery_area/orcl11g/
                                                 control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

3 手动备份控制文件

SQL> alter database backup controlfile to trace;
Database altered.

 备份的控制文件创建脚本位于以下文件中

NAME               VALUE
------------------ ------------------------------------------------------------------------
Default Trace File /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_2175.trc

4 插入几行数据，切换几次日志，多产生几个归档日志

SQL> insert into test_control select sysdate from dual;
SQL> commit;
SQL> alter system switch logfile;
System altered.
SQL> insert into test_control select sysdate from dual;
SQL> commit;
SQL> alter system switch logfile;
System altered.
SQL> select * from test_control;
CURR_TIME
-------------------
2019-07-21 16:25:33
2019-07-27 09:32:21
2019-07-27 09:33:39

5 删除控制文件，模仿控制文件全部丢失，关闭数据库

[oracle@orclserv1 orcl11g]$ rm /u01/app/oracle/oradata/orcl11g/control01.ctl
[oracle@orclserv1 orcl11g]$ rm /u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl
       ##查询v$database视图，系统提示打不开控制文件，操作系统错误为没有文件和目录，
   以immediate方式不能关闭数据库，报同样错误，这时，可以以abort方式关闭数据库。
SQL> select  CHECKPOINT_CHANGE#, CURRENT_SCN from v$database;
select  CHECKPOINT_CHANGE#, CURRENT_SCN from v$database
                                             *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl11g/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl11g/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>

6 以nomount方式启动数据库，用备份的脚本重新创建控制文件，恢复数据库，以resetlogs方式打开数据库

1） 以nomount方式启动数据库

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes

2） 运行备份的脚本重新创建控制文件

 CREATE CONTROLFILE REUSE DATABASE "ORCL11G" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl11g/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl11g/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl11g/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl11g/system01.dbf',
  '/u01/app/oracle/oradata/orcl11g/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl11g/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl11g/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl11g          MOUNTED
     ##数据库已进入mount状态
SQL> !ls /u01/app/oracle/oradata/orcl11g/control01.ctl
/u01/app/oracle/oradata/orcl11g/control01.ctl

SQL> !ls /u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl
/u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl
    ### 已在原来的位置创建了控制文件

3） 打开数据库

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 resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl11g/system01.dbf'


SQL> alter database open readonly;
alter database open readonly
                    *
ERROR at line 1:
ORA-02288: invalid OPEN mode


SQL> alter database open read only;
alter database open read only
#####各种方式均不能打开数据库，以restlog模式打开需要恢复

4）恢复数据库

 recover database using backup controlfile until cancel;
ORA-00279: change 971431 generated at 07/27/2019 09:34:16 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2019_07_27/o1_mf_1_10_%u_.
arc
ORA-00280: change 971431 for thread 1 is in sequence #10


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2019_07_27/o1_mf_1_9_gmqbjrmd_.arc
ORA-00310: archived log contains sequence 9; sequence 10 required
ORA-00334: archived log:
'/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2019_07_27/o1_mf_1_9_gmqb
jrmd_.arc'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent

###change 971431 for thread 1 is in sequence #10不在归档日志中，应该位于在线日志中，这种方式的数据库恢复不检查在线日志，手动指定在线日志即可恢复。
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 971431 generated at 07/27/2019 09:34:16 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2019_07_27/o1_mf_1_10_%u_.
arc
ORA-00280: change 971431 for thread 1 is in sequence #10


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl11g/redo01.log ##手动指定在线日志位置
Log applied.
Media recovery complete.
SQL> alter database open resetlogs; ## 以resetlog方式打开数据库

Database altered.

SQL>  alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from test_control;

CURR_TIME
-------------------
2019-07-21 16:25:33
2019-07-27 09:32:21
2019-07-27 09:33:39
  ####检查表test_control中的数据，同控制文件删除前相同
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl11g/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
  2
Tablespace altered.
### 临时文件需要重新创建