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/orcl11g/control01.ctl, /u01/app/oracle/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
```sql
### 5 删除控制文件，模仿控制文件全部丢失，关闭数据库
```shell
[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方式打开数据库

6.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

6.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
--已在原来的位置创建了控制文件

####6.3 打开数据库
直接以open模式打开数据库报错，提示必须使用RESETLOGS或者NORESETLOGS方式打开数据库，使用resetlogs打开数据库时，数据文件1（system01.dbf需要恢复）

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'
--read only 方式也不能打开数据库
SQL> alter database open read only;
alter database open read only

6.4恢复数据库

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: {=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_gmqbjrmd_.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: {=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;
Tablespace altered.
--临时文件需要重新创建