重新建立控制檔案，修改MAXDATAFILES引數

一. 概述

本文的環境為:

Linux 5.4 X86

Oracle 10.2.0.1

本文主要介紹如何透過重新建立控制檔案,來完成修改MAXDATAFILES引數的目的.

主要的步驟為:

1.備份控制檔案到trace檔案,找出noresetlog case的內容

2.immediate方式關閉資料庫.

3.資料庫啟動到nomount 狀態,執行重新建立控制的指令碼,控制檔案建立成功後,資料庫自動啟動到mount狀態.

4.開啟資料庫,建立新的temp檔案.

5.重新備份控制檔案到trace,檢查MAXDATAFILES引數是否生效.

[@more@]

二. 具體實施步驟

2.1 備份控制檔案到trace檔案,找出noresetlog case的內容

SQL>alter database backup controlfile to trace ;

在使用者dump檔案目錄中找到剛剛生成的trace檔案,並檢索該檔案,找到noresetlogs 方式的相關語句,如下:

$more aidu_ora_10162.trc

-- Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "AIDU" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oracle/oradata/aidu/redo01.log' SIZE 16M,

GROUP 2 '/oracle/oradata/aidu/redo02.log' SIZE 16M,

GROUP 3 '/oracle/oradata/aidu/redo03.log' SIZE 16M

-- STANDBY LOGFILE

DATAFILE

'/oradata/aidu/system01.dbf',

'/oradata/aidu/undotbs01.dbf',

'/oradata/aidu/sysaux01.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/oracle/product/10.2.0/db_1/dbs/arch1_1_772954438.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMPTS1 ADD TEMPFILE '/oradata/aidu/temp01.dbf'

SIZE 134217728 REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

注意：

因為本次只是修改控制檔案裡面的MAXDATAFILES 引數的值，資料庫的所有資料檔案和redo檔案都儲存完好，重建控制檔案也是在正常關閉資料庫的前提下進行的，所以不需要進行任何形式的資料庫恢復工作；只是需要最後重新建立一下temp表空間檔案（這個在trace檔案中有相關的語句）。

在alter database backup controlfile to trace 產生的trace檔案中，有集中建立控制檔案的方式選擇，我們應該選擇noresetlogs方式，即-- Set #1. NORESETLOGS case

2.2 immediate方式關閉資料庫.

SQL>shutdown immediate

2.3 資料庫啟動到nomount 狀態,執行重新建立控制的指令碼,控制檔案建立成功後,資料庫自動啟動到mount狀態

Sql>STARTUP nomount;

SQL> CREATE CONTROLFILE REUSE DATABASE "AIDU" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 1200

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oracle/oradata/aidu/redo01.log' SIZE 16M,

GROUP 2 '/oracle/oradata/aidu/redo02.log' SIZE 16M,

GROUP 3 '/oracle/oradata/aidu/redo03.log' SIZE 16M

-- STANDBY LOGFILE

DATAFILE

'/oradata/aidu/system01.dbf',

'/oradata/aidu/undotbs01.dbf',

'/oradata/aidu/sysaux01.dbf'

CHARACTER SET ZHS16GBK

;

Control file created.

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database open;

Database altered.

SQL> select name from v$tempfile;

no rows selected

SQL> ALTER TABLESPACE TEMPTS1 ADD TEMPFILE '/oradata/aidu/temp01.dbf'

2 ;

Tablespace altered.

SQL> select name from v$tempfile;

NAME

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

/oradata/aidu/temp01.dbf

SQL> alter database backup controlfile to trace;

Database altered.

SQL> exit

$ls -lt

[oracle@ocmdb udump]$ ls -lt

total 644

-rw-r----- 1 oracle oinstall 6840 Jan 19 18:24 aidu_ora_10183.trc

-rw-r----- 1 oracle oinstall 587 Jan 19 18:20 aidu_ora_10162.trc

…...

[oracle@ocmdb udump]$ more aidu_ora_10183.trc

…...

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "AIDU" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 1200

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

可以看到,引數MAXDATAFILES 已經修改為1200了.