重新建立控制文件，修改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了.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1057174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32980/viewspace-1057174/