How to Change Maxdatafiles, Maxlogfiles after Creating Database

Posted: October 29, 2009 in HowTo, Oracle, Oracle Database, Tips
Tags: , , , ,

1. Dump a trace of the controlfile.
Use SQL*Plus (or Server Manager) and enter:
SQL> alter database backup controlfile to trace ;
2. Modify the created trace file (located in the user_dump_dest directory):
a) remove from the first line to the line before “STARTUP NOMOUNT”
b) remove line “ALTER DATABASE OPEN” to the end of the file
c) change value of maxdatafiles, maxlogfiles and other parameters as desired
d) rename the file to something .sql (such as cntl.sql)
3. Shutdown the database.
4. Backup the database.
5. Run Server Manager/SQLPLUS and do the following:
SVRMGR> connect internal or connect /as sysdba
SVRMGR> @cntl.sql (The SQL script that you created in step 2) or SQLPLUS>@cntl.sql
SVRMGR> alter database open noresetlogs ; or SQLPLUS> alter database open noresetlogs

SQLPLUS if you are using 9i SVRMGRL if you are using 8i or below.

Also Please read the following article from metalink…

Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 14-JAN-2005

PURPOSE
This article describes how you can recreate your controlfile.

SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.

WARNING:
——–

You should only need to recreate your control file under very special
circumstances:

– All current copies of the control file have been lost or are corrupted.

– You need to change a “hard” database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
MAXLOGHISTORY, etc.

– You are restoring a backup in which the control file is corrupted or
missing.

– Oracle Customer Support advises you to do so.

– If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s