Re-create Control File

當需要變更 Control File 的參數時，必須按以下步驟完成。

以 MAXLOGFILES 為例：

備份 controlfile & 查詢 MAXLOGFILES 目前設定

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /opt/oracle/admin/oddfdc/udump

SQL> Select 'MAXLOGFILES: ' || records_total from v$controlfile_record_section where type = 'REDO LOG';

'MAXLOGFILES:'||RECORDS_TOTAL
-----------------------------------------------------
MAXLOGFILES: 5 

修改 controlfile 內容

從 udump 目錄找尋最新的 *.trc，內容有建立 crontrolfile 的內容

例如 oddfdc_ora_18405.trc：
複製以下這一段內容，用來建立 create_controlfile.sql

....
CREATE CONTROLFILE REUSE DATABASE "ODDFDC" RESETLOGS  NOARCHIVELOG
...
...
CHARACTER SET AL32UTF8
;

新增 create_controlfile.sql：

• 修改 MAXLOGFILES 5 為 10

CREATE CONTROLFILE REUSE DATABASE "ODDFDC" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 10
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/opt/oracle/oradata/oddfdc/redo01.log'  SIZE 2048M,
  GROUP 2 '/opt/oracle/oradata/oddfdc/redo02.log'  SIZE 2048M,
  GROUP 3 '/opt/oracle/oradata/oddfdc/redo03.log'  SIZE 2048M,
  GROUP 4 '/opt/oracle/oradata/oddfdc/redo04.log'  SIZE 2048M
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/oddfdc/system01.dbf',
  '/opt/oracle/oradata/oddfdc/undotbs01.dbf',
  '/opt/oracle/oradata/oddfdc/sysaux01.dbf',
  '/opt/oracle/oradata/oddfdc/STDB-01.dbf',
  '/opt/oracle/oradata/oddfdc/CDB-01.dbf',
  '/opt/oracle/oradata/oddfdc/STDB-02.dbf'
CHARACTER SET AL32UTF8
;

重建 controlfile

SQL> shutdown immediate
SQL> startup nomount;
SQL> @create_controlfile.sql
SQL> alter database open; 

TIPs：

如果 script 內含 RESETLOGS，執行完 script 開啟 database 時要加上 resetlogs，否則會遭遇到錯誤
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
開啟 db 指令改成
SQL> alter database open resetlogs;

一旦啟用 RESETLOGS，之前的 archive log 紀錄都會被重置，如果要保留以前的 archive log 紀錄，可以改成 NORESETLOGS，開啟 DB 的指令不用加任何參數。

重設所有 Temporary Tablespace

alter tablespace TEMP  add tempfile '/opt/oracle/oradata/oddfdc/temp01.dbf' size 500M reuse autoextend on maxsize 2048M;

最後檢查

SQL> Select 'MAXLOGFILES: ' || records_total from v$controlfile_record_section where type = 'REDO LOG';

'MAXLOGFILES:'||RECORDS_TOTAL
-----------------------------------------------------
MAXLOGFILES: 10