Oracle 12c 新特性

新特性：CDB/PDB的维护命令：备份与恢复（4）删除控制文件后恢复

2020年10月29日2020年12月25日 handsup

本次实验是模拟所有的控制文件都被删除的情况下，且没有做RMAN的备份的情况下的恢复。一般生产环境至少会有2个以上的控制文件，一般不会出现这个问题。建议生产环境至少配置3个控制文件，分布于不同的asm磁盘。
操作前请先根据 http://www.821121.com/?p=310 中的操作进行备份

查看控制文件路径

[oracle@cat1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 2 22:56:56 2005
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA1/WHS1/CONTROLFILE/current.261.1057318079

数据库开启状态下，ASM无法删除

ASMCMD> rm +DATA1/WHS1/CONTROLFILE/current.261.1057318079
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA1/WHS1/CONTROLFILE/current.261.1057318079' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

关库后删除

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

ASMCMD> rm +DATA1/WHS1/CONTROLFILE/current.261.1057318079
ASMCMD>

重建控制文件

查看 http://www.821121.com/?p=310 中的备份文件：/u01/app/oracle/diag/rdbms/whs1/whs11/trace/whs11_ora_47143.trc
根据trace文件中的内容恢复控制文件。

SQL> startup nomount 
ORACLE instance started.

Total System Global Area 2818568848 bytes
Fixed Size                  9138832 bytes
Variable Size             838860800 bytes
Database Buffers         1962934272 bytes
Redo Buffers                7634944 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "WHS1" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA1/WHS1/ONLINELOG/group_1.262.1057318081'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA1/WHS1/ONLINELOG/group_2.263.1057318081'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '+DATA1/WHS1/ONLINELOG/group_3.270.1057318741'  SIZE 200M BLOCKSIZE 512,
 11    GROUP 4 '+DATA1/WHS1/ONLINELOG/group_4.271.1057318743'  SIZE 200M BLOCKSIZE 512
 12  -- STANDBY LOGFILE
 13  DATAFILE
 14    '+DATA1/WHS1/DATAFILE/system.257.1057317971',
 15    '+DATA1/WHS1/DATAFILE/sysaux.258.1057318005',
 16    '+DATA1/WHS1/DATAFILE/undotbs1.259.1057318021',
 17    '+DATA1/WHS1/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1057318247',
 18    '+DATA1/WHS1/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1057318247',
 19    '+DATA1/WHS1/DATAFILE/users.260.1057318021',
 20    '+DATA1/WHS1/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1057318247',
 21    '+DATA1/WHS1/DATAFILE/undotbs2.269.1057318497',
 22    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/system.274.1057318913',
 23    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/sysaux.275.1057318911',
 24    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/undotbs1.273.1057318911',
 25    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/undo_2.277.1057318927',
 26    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/users.278.1057318927',
 27    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/system.282.1057335893',
 28    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/sysaux.283.1057335893',
 29    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/undotbs1.281.1057335893',
 30    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/undo_2.279.1057335893',
 31    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/users.280.1057335893',
 32    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/system.289.557110713',
 33    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/sysaux.288.557110713',
 34    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/undotbs1.287.557110713',
 35    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/users.285.557110713',
 36    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/undotbs2.286.557110713'
 37  CHARACTER SET AL32UTF8
 38  ;
CREATE CONTROLFILE REUSE DATABASE "WHS1" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

由于是在RAC环境下，所以会报上面的错误，修改相关参数后重启

SQL> alter system set cluster_database=false scope=spfile sid='*'; 

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2818568848 bytes
Fixed Size                  9138832 bytes
Variable Size             838860800 bytes
Database Buffers         1962934272 bytes
Redo Buffers                7634944 bytes
SQL>

再次重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "WHS1" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA1/WHS1/ONLINELOG/group_1.262.1057318081'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA1/WHS1/ONLINELOG/group_2.263.1057318081'  SIZE 200M BLOCKSIZE 512
 10  -- STANDBY LOGFILE
 11  DATAFILE
 12    '+DATA1/WHS1/DATAFILE/system.257.1057317971',
 13    '+DATA1/WHS1/DATAFILE/sysaux.258.1057318005',
 14    '+DATA1/WHS1/DATAFILE/undotbs1.259.1057318021',
 15    '+DATA1/WHS1/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1057318247',
 16    '+DATA1/WHS1/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1057318247',
 17    '+DATA1/WHS1/DATAFILE/users.260.1057318021',
 18    '+DATA1/WHS1/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1057318247',
 19    '+DATA1/WHS1/DATAFILE/undotbs2.269.1057318497',
 20    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/system.274.1057318913',
 21    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/sysaux.275.1057318911',
 22    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/undotbs1.273.1057318911',
 23    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/undo_2.277.1057318927',
 24    '+DATA1/WHS1/B4D31742116320ECE0532900A8C025DC/DATAFILE/users.278.1057318927',
 25    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/system.282.1057335893',
 26    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/sysaux.283.1057335893',
 27    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/undotbs1.281.1057335893',
 28    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/undo_2.279.1057335893',
 29    '+DATA1/WHS1/B4D6EBAED583686DE0532900A8C0FDBB/DATAFILE/users.280.1057335893',
 30    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/system.289.557110713',
 31    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/sysaux.288.557110713',
 32    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/undotbs1.287.557110713',
 33    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/users.285.557110713',
 34    '+DATA1/WHS1/F5FB22B2D89BCA4AE0332900A8C0FF59/DATAFILE/undotbs2.286.557110713'
 35  CHARACTER SET AL32UTF8
 36  ;

Control file created.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 49796942 generated at 05/02/2005 23:00:00 needed for thread 1
ORA-00289: suggestion : +DATA1
ORA-00280: change 49796942 for thread 1 is in sequence #18

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL   <--CANCEL可能会导致坏块，我这里是正常关库后才删除的控制文件，选择cancel不会有坏块，生产环境慎用
Media recovery cancelled.

SQL> alter database open resetlogs;

修改cluster_database会原状态后，重启数据库

SQL> alter system set cluster_database=true scope=spfile sid='*'; 

System altered.

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

Total System Global Area 2818568848 bytes
Fixed Size                  9138832 bytes
Variable Size             838860800 bytes
Database Buffers         1962934272 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.
SQL>