RAC增加控制文件

查询现有控制文件

SQL>  SELECT NAME FROM V$CONTROLFILE ;

NAME
--------------------------------------------------------------------------------
+DATA/REPORT/CONTROLFILE/current.262.1150058049
+DATA/REPORT/CONTROLFILE/current.261.1150058049

创建pfile，防止后续调整spfile有问题无法启动库

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

File created.

备份下控制文件

SQL> alter database backup controlfile to '/home/oracle/controlfile_report.ctl';

Database altered.
 

关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
启动到nomount状态

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2466249664 bytes
Fixed Size                  8899520 bytes
Variable Size            1426063360 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7876608 bytes

rman创建个控制文件

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.2.0.0
[oracle@tab-prd-db04 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 13 18:48:07 2023
Version 19.13.2.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: REPORT (not mounted)
 

RMAN> restore controlfile to '+DATA' from '+DATA/REPORT/CONTROLFILE/current.262.1150058049';

RMAN> exit

Recovery Manager complete.
切换grid用户并查看新控制文件路径

[root@tab-prd-db04 ~]# pwd&& su - grid
/root
Last login: Mon Nov 13 18:37:09 CST 2023
[grid@tab-prd-db04 ~]$ asmcmd
ASMCMD> cd '+DATA/REPORT/CONTROLFILE/
ASMCMD-8007: unclosed single quotation mark
ASMCMD> cd +DATA/REPORT/CONTROLFILE/
ASMCMD> ls
Current.261.1150058049
Current.262.1150058049
current.293.1152816497
 

ASMCMD> exit
[grid@tab-prd-db04 ~]$ exit
logout
[root@tab-prd-db04 ~]# pwd&& su - oracle
/root
Last login: Mon Nov 13 18:37:10 CST 2023
[oracle@tab-prd-db04 ~]$  sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 13 18:49:43 2023
Version 19.13.2.0.0

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

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

确认数据库状态

SQL> select status from v$instance;

STATUS
------------
STARTED

增加新建数据库控制文件

SQL> alter system set control_files='+DATA/REPORT/CONTROLFILE/Current.261.1150058049','+DATA/REPORT/CONTROLFILE/Current.262.1150058049','+DATA/REPORT/CONTROLFILE/Current.293.1152816497' scope=spfile sid='*';

System altered.

关库

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

启动（使用spfile）
SQL> startup
ORACLE instance started.

Total System Global Area 2466249664 bytes
Fixed Size                  8899520 bytes
Variable Size            1426063360 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
 

万一修改过程中出现问题，使用原来的pfile启动，创建spfile（注意创建spfile，需要指定路径，否则启动依旧失败）

SQL> startup pfile='/home/oracle/pfile1103.ora'

SQL> create spfile='+DATA' from pfile='/home/oracle/pfile1103.ora';

File created.

其他关闭库的方式，但是不太会用（新新DBA）

[oracle@tab-prd-db04 ~]$  srvctl stop instance -d report -i report1,report2 -o immediate
 

[oracle@tab-prd-db04 ~]$  srvctl start database -d report -o nomount