alter system set control_files

来源：互联网发布：程序员游戏编辑：程序博客网时间：2024/06/06 03:44

在设置多个control_files时，需要将每个控制文件逗号分隔，带上scope=spfile参数！

alter system set control_files='D:\ORADATA\TEST\CONTROL01.CTL','D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL' scope=spfile;

control_files参数为静态参数，修改完需要重启实例；

SQL> select t.NAME,t.VALUE,t.ISSYS_MODIFIABLE from v$system_parameter t where t.name='control_files';

NAME
--------------------------------------------------------------------------------

VALUE
--------------------------------------------------------------------------------

ISSYS_MODIFIABLE
---------
control_files
D:\ORADATA\TEST\CONTROL01.CTL, D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL
FALSE

视图中ISSYS_MODIFIABLE即显示为FALSE的都需要重启实例，也就是说都是静态参数，显示为IMMEDIATE都是动态参数，alter后立即生效；

千万不能写成 alter system set control_files='D:\ORADATA\TEST\CONTROL01.CTL,D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL' scope=spfile;

否则，数据库会报错

SQL> alter database mount;alter database mount*ERROR at line 1:ORA-00205: error in identifying control file, check alert log for more info

查看alter.log

alter database mountSun Jun 08 11:49:16 2014ORA-00210: cannot open the specified control fileORA-00202: control file: 'D:\ORADATA\TEST\CONTROL01.CTL,D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL'ORA-27041: unable to open fileOSD-04002: unable to open fileO/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。Sun Jun 08 11:49:16 2014Errors in file d:\oracle\diag\rdbms\test\test\trace\test_m000_6756.trc  (incident=49364):ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [3], [D:\ORADATA\TEST\CONTROL01.CTL,D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL], [0], [1], [0], [], [], [], [], [], []ORA-27041: unable to open fileOSD-04002: unable to open fileO/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。ORA-00210: cannot open the specified control fileORA-00202: control file: 'D:\ORADATA\TEST\CONTROL01.CTL,D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL'ORA-27041: unable to open fileOSD-04002: unable to open fileO/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。Incident details in: d:\oracle\diag\rdbms\test\test\incident\incdir_49364\test_m000_6756_i49364.trcErrors in file d:\oracle\diag\rdbms\test\test\trace\test_m000_6756.trc:ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [3], [D:\ORADATA\TEST\CONTROL01.CTL,D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL], [0], [1], [0], [], [], [], [], [], []ORA-27041: unable to open fileOSD-04002: unable to open fileO/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。ORA-00210: cannot open the specified control fileORA-00202: control file: 'D:\ORADATA\TEST\CONTROL01.CTL,D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL'ORA-27041: unable to open fileOSD-04002: unable to open fileO/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。Sun Jun 08 11:49:18 2014Trace dumping is performing id=[cdmp_20140608114918]ORA-205 signalled during: alter database mount...

正确设置control_files，重启实例解决！

0 0