oracle maxdatafiles,关于db_files和maxdatafiles的问题

SOLVING ORA-1118 and ORA-1165 :

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

The following are possible options to get around these errors :

1. You should verify that you haven't reached a hard limit for MAXDATAFILES

imposed by the Operating System. This limit can be found in the O/S specific

documentation.

If MAXDATAFILES is already equal to the maximum value, then you must

restructure the database by reducing the number of datafiles associated with

each individual tablespace:

a) export the objects from the tablespace

b) drop the tablespace

c) recreate the tablespace with less datafiles

d) import the objects back into the tablespace

In some cases, if the datafile size is too small, it might be usefull to

resize the datafiles. Make sure that you keep the datafile size lower than

the Operating System limitation on file sizes :

> ALTER DATABASE DATAFILE 'D:\Oradata\Orcl\datafile\usr01.dbf' RESIZE 500M;

Query DBA_SEGMENTS to find out all objects belonging to a particular

tablespace.

2. Increase the MAXDATAFILES parameter. It is possible for you to increase

the MAXDATAFILES limit without recreating the entire database. This is done

by recreating the control file only.

3. Especially for Oracle8+ you should make sure that you do not encounter an

error against the maximum number of open database files (DB_FILES). It is

more likely that the value for DB_FILES is too low since the controlfile in

Oracle8 expands automatically as long as the number of the added datafile is

lower then the value for DB_FILES. Normally the error message should

indicate this:

ORA-00059 : maximum number of DB_FILES exceeded

4. Recreate the database. Since the MAXDATAFILES parameter is specified

upon database creation, recreating the database allows you to increase

this parameter.

In addition to your operating system documentation, you may want to

reference the following bulletins:

O7 - Creating an Oracle 7.X Database under Unix - Note:10280.1 RECREATING THE CONTROL FILE:

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

In Oracle7 or higher, you can create the control file. In addition, you can get

Oracle to create the script for you. To do this, perform the following steps:

1. With the database mounted or open, issue the following commands:

SQL> alter database backup controlfile to trace;

SQL> exit

2. A trace file will have been generated in your 'user_dump_dest'.

User_dump_dest is an init.ora parameter, and can be found by

issuing:

SQL> show parameter user_dump_dest

The easiest way to locate the correct trace is to look at its date. A

file will exist with the current date and time. The naming convention

for these files is operating system specific.

3. Once the file is located, search through the file for the word "CONTROL"

You should find:

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

# to open the database.

# No data other than log history will be lost. Additional logs may

ETC.ETC.

4. Copy this trace file to some location and rename it to end it ".sql",

for this example, it is called "recr_con.sql".

5. Edit the "recr_con.sql" deleting the trace header information. Then

increase the value that you find next to the word "MAXDATAFILES".

6. Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).

7. Take a full database backup at this time.

8. Remove the current control files. It is essential to remove all control

files, otherwise, you will receive an error. In addition, you CANNOT

REUSE the control file, since the size of the control file will increase

when you increase MAXDATAFILES.

9. Create the controlfile within SQLDBA:

SQL> connect internal [if using sqldba or svrmgrl]

or

connect / as sysdba [if using sqlplus]

SQL> @recr_con.sql

SQL> alter database open noresetlogs;

If you receive a "Statement processed" message, then your database is

now back up and running with a higher datafile limit. It is recommended

to shutdown at this time and take a full backup.

CREATE CONTROLFILE SYNTAX:

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

The following is information on the create control file syntax, this

information is fully documented in the Oracle SQL Reference Manual.

The syntax of this command is similar to CREATE DATABASE.

The defaults for any missing clauses are the same as the

DATABASE defaults for CREATE DATABASE.

CREATE CONTROLFILE [REUSE]

SET DATABASE name

[LOGFILE filespec [, filespec] ...]

RESETLOGS | NORESETLOGS

[MAXLOGFILES integer]

[DATAFILE filespec [, filespec] ...]

[MAXDATAFILES integer]

[MAXINSTANCES integer]

[ARCHIVELOG | NOARCHIVELOG]

[SHARED | EXCLUSIVE]