oracle12c修改控制文件,Oracle 12c增加控制文件 以及ORA-01078的处理思路

01.启动数据库

[BEGIN] 2020/6/4 13:43:31

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 4 13:44:07 2020

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1660944384 bytes

Fixed Size 8793448 bytes

Variable Size 1056965272 bytes

Database Buffers 587202560 bytes

Redo Buffers 7983104 bytes

Database mounted.

Database opened.

02.查看数据库和控制文件状态

SQL> select name,cdb,open_mode from v$database;

NAME CDB OPEN_MODE

--------- --- --------------------

orcl YES READ WRITE

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/itp

uxdb/control01.ctl, /oracle/ap

p/oracle/oradata/orcl/contr

ol02.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

03.增加控制文件

a.由于手误复制了空格，导致直接修改成功到spfile文件了

alter system set control_files='/u01/app/oracle/oradata/itp','','' scope=spfile;

System altered.

b.尝试修改回原来的，发现报错

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00117: syntax error at ',' at the start of input

SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' scope=spfile;

alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' scope=spfile

*

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00117: syntax error at ',' at the start of input

c.继续尝试，修改成1个测试还不行

SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl' scope=spfile;

alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl' scope=spfile

*

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00117: syntax error at ',' at the start of input

d.将spfile生成pfile，查看pfile，发现问题了吧

SQL> create pfile='/oracle/pfile.ora' from spfile;

File created.

SQL> shutdown abort

ORACLE instance shut down.

e.修改pfile文件中的参数

*.control_files='/u01/app/oracle/oradata/itpuxdb/control01.ctl', '/oracle/app/oracle/oradata/orcl/control02.ctl', '/u01/app/oracle/oradata/orcl/control03.ctl'

f.从pfile创建spfile参数文件

SQL> create spfile from pfile='/oracle/pfile.ora';

File created.

g.拷贝新的控制文件

SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl

04.open数据库、查看控制文件状态

SQL> startup

ORACLE instance started.

Total System Global Area 1660944384 bytes

Fixed Size 8793448 bytes

Variable Size 1056965272 bytes

Database Buffers 587202560 bytes

Redo Buffers 7983104 bytes

Database mounted.

Database opened.

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/itp

uxdb/control01.ctl, /oracle/ap

p/oracle/oradata/orcl/contr

ol02.ctl, /u01/app/oracle/o

radata/orcl/control03.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

[END] 2020/6/4 14:01:30