云贝教育 |【技术文章】Oracle 19c 之控制文件管理

原文链接：【OracleOCP】oracle19c之控制文件管理-课程体系-云贝教育

每个Oracle数据库都有一个控制文件，它是一个小的二进制文件，记录了数据库的物理结构。

Thedatabasename--数据库名称Namesandlocationsofassociateddatafilesandredologfiles--数据文件和日志文件的路径Thetimestampofthedatabasecreation--数据库创建的时间Thecurrentlogsequencenumber--当前日志的序列号Checkpointinformation--数据库检查点信息

Oracle数据库的控制文件是与数据库同时创建的。默认情况下，在创建数据库期间至少创建控制文件的一个副本。在某些操作系统上，默认是创建多个副本。在创建数据库期间，应该创建控制文件的两个或多个副本。如果丢失了控制文件或希望更改控制文件中的特定设置，也可以稍后创建控制文件。

2.1查看当前数据库的控制文件(pdb和cdb共享)

SYS@ORCLCDBshowparametercontrol_filesNAMETYPEVALUE-----------------------------------------------------------------------------control_filesstring/opt/oracle/oradata/ORCLCDB/,/opt/oracle/oradata/ORCLCDB/@ORCLCDBselectnamefromv$controlfile;NAME--------------------------------------------------------------------------------/opt/oracle/oradata/ORCLCDB//opt/oracle/oradata/ORCLCDB/

2.2修改控制文件多路复用

1、修改参数SYS@ORCLCDBaltersystemsetcontrol_files='/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/'scope=spfile;2、关闭数据库SYS@ORCLCDBshutdownimmediate;、操作系统上CP控制文件[oracle@databaseORCLCDB]$、启动数据库SYS@ORCLCDBstartup;

提问：如果失误将控制文件参数改成如下格式，如何通过修改参数文件将数据库OPEN

1、修改控制文件参数altersystemsetcontrol_files='/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/control03ctl'scope=spfile;2、而实际3号控制文件是这样的[oracle@databaseORCLCDB]$lltotal2715912-rw-r-----1oracleoinstall18726912Jul602:32:32:34参数文件里是control03ctl

3.1通过TRACE备份

意义上来说，不是针对控制文件的备份，而是根据现在系统情况生成了一份trace文件，这个trace文件中记录了创建控制文件的基本信息。

1、CDB下执行，PDB无法执行SQLalterdatabasebackupcontrolfiletotrace;

上面这句话生成的trace文件在dump里面，可以通过告警日志查看。

2023-07-06T07:23:56.772531-07:00alterdatabasebackupcontrolfiletotrace2023-07-06T07:23:56.856692-07:00Backupcontrolfilewrittentotracefile/opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_16999.trcCompleted:alterdatabasebackupcontrolfiletotrace

具体内容见附件

CREATECONTROLFILEREUSEDATABASE"ORCLCDB"NORESETLOGSNOARCHIVELOGMAXLOGFILES16MAXLOGMEMBERS3MAXDATAFILES1024MAXINSTANCES8MAXLOGHISTORY292LOGFILEGROUP1'/opt/oracle/oradata/ORCLCDB/'SIZE200MBLOCKSIZE512,GROUP2'/opt/oracle/oradata/ORCLCDB/'SIZE200MBLOCKSIZE512,GROUP3'/opt/oracle/oradata/ORCLCDB/'SIZE200MBLOCKSIZE512--STANDBYLOGFILEDATAFILE'/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_d_l9qy2qvq_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_data_l9qz094v_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_idx_l9qz0jbq_.dbf','/opt/oracle/product/19c/dbhome_1/dbs/shadow_lwp1_df','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_16k_l9thkbf9_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs1_l9y26m6z_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs2_l9y3f1bt_.dbf'CHARACTERSETAL32UTF8;

3.2通过BINARY二进制备份

这种方式是针对控制文件的热备份。

--CDB下执行SYS@ORCLCDBalterdatabasebackupcontrolfileto'/home/oracle/';Databasealtered.

4.1丢失/损坏多个控制文件中的一个

模拟单个控制文件损坏，删除其中一个控制文件。数据库无法启动。

方案有两种：1、直接复制多路复用中的其他控制文件2、通过pfile修改控制文件的参数，将损坏的控制文件删掉

1）、通过OS层复制参数文件

1、关闭数据库SYS@ORCLCDBshutdownimmediate;、删除其中一个控制文件[oracle@databaseORCLCDB]$、启动数据库失败SYS@eSize583008256bytesDatabaseBuffers58720256bytesRedoBuffers3440640bytesIn-MemoryArea104857600bytesORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreinfo4、具体报错如下，提示3号文件未找到ALTERDATABASEMOUNT2023-07-06T08:05:16.072594-07:00ORA-00210:cannotopenthespecifiedcontrolfileORA-00202:controlfile:'/opt/oracle/oradata/ORCLCDB/control03ctl'ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:7ORA-205signalledduring:ALTERDATABASEMOUNT2023-07-06T08:05:16.144178-07:00Errorsinfile/opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_mz00_17712.trc:ORA-00202:controlfile:'/opt/oracle/oradata/ORCLCDB/control03ctl'ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:75、复制一个控制文件[oracle@databaseORCLCDB]$、再次启动数据库成功

2）、通过pfile修改控制文件

1、关闭数据库SYS@ORCLCDBshutdownimmediate;、删除其中一个控制文件[oracle@databaseORCLCDB]$、启动数据库失败SYS@eSize583008256bytesDatabaseBuffers58720256bytesRedoBuffers3440640bytesIn-MemoryArea104857600bytesORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreinfo4、创建pfile，修改参数文件SYS@ORCLCDBcreatepfilefromspfile;Filecreated[oracle@databasedbs]$cd/opt/oracle/product/19c/dbhome_1/dbs--修改参数文件将损坏的参数文件删除*.control_files='/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/'5、以pfile启动数据库startuppfile='/opt/oracle/product/19c/dbhome_1/dbs/'

参照控制文件，我们需要找到以下信息

1、日志文件名称，体积和块大小2、所有的dbf文件3、数据库的名称和字符集CREATECONTROLFILEREUSEDATABASE"ORCLCDB"NORESETLOGSNOARCHIVELOGMAXLOGFILES16MAXLOGMEMBERS3MAXDATAFILES1024MAXINSTANCES8MAXLOGHISTORY292LOGFILEGROUP1'/opt/oracle/oradata/ORCLCDB/'SIZE200MBLOCKSIZE512,GROUP2'/opt/oracle/oradata/ORCLCDB/'SIZE200MBLOCKSIZE512,GROUP3'/opt/oracle/oradata/ORCLCDB/'SIZE200MBLOCKSIZE512--STANDBYLOGFILEDATAFILE'/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_d_l9qy2qvq_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_data_l9qz094v_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_idx_l9qz0jbq_.dbf','/opt/oracle/product/19c/dbhome_1/dbs/shadow_lwp1_df','/opt/oracle/oradata/ORCLCDB/','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_16k_l9thkbf9_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs1_l9y26m6z_.dbf','/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs2_l9y3f1bt_.dbf'CHARACTERSETAL32UTF8;