Re-create my Oracle control file

From my previous posting I had mention that I need to recreate my Oracle database control file since I need to increase my MAXDATAFILES parameter.

Yesterday (sunday) I had a chance to manage my control file since I have time to shutdown my server. I log on my AIX server and logon to Oracle SQL Plus.

ora[sid]>sqlplus /nolog

Connect to my database.

SQL>conn /  as sysdba;

Then dump my current control file to a text file.

SQL>alter database backup controlfile to trace as ‘/oracle/[SID]/controlfile.txt’;

Then I copied that file (controlfile.txt) to my local notebook and edit it using my favourite text editor.

Here are list of my old control file :

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “SID” NORESETLOGS  ARCHIVELOG

—  SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 768  —–> need to be changed

MAXINSTANCES 50

MAXLOGHISTORY 22462

LOGFILE

GROUP 1 (

‘/oracle/SID/origlogA/log_g11m1.dbf’,

‘/oracle/SID/mirrlogA/log_g11m2.dbf’

) SIZE 50M,

GROUP 2 (

‘/oracle/SID/origlogB/log_g12m1.dbf’,

‘/oracle/SID/mirrlogB/log_g12m2.dbf’

) SIZE 50M,

GROUP 3 (

‘/oracle/SID/origlogA/log_g13m1.dbf’,

‘/oracle/SID/mirrlogA/log_g13m2.dbf’

) SIZE 50M,

GROUP 4 (

‘/oracle/SID/origlogB/log_g14m1.dbf’,

‘/oracle/SID/mirrlogB/log_g14m2.dbf’

) SIZE 50M

— STANDBY LOGFILE

DATAFILE

‘/oracle/SID/sapdata1/system_1/system.data1’,

—— a lot of lines for datafile definition —

‘/oracle/SID/sapdata10/wpr_722/wpr.data722’

CHARACTER SET WE8DEC

;

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

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

RECOVER DATABASE

# All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

# 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 PSAPTEMP ADD TEMPFILE ‘/oracle/SID/sapdata3/temp_1/temp.data1’

SIZE 8000M REUSE AUTOEXTEND ON NEXT 20971520  MAXSIZE 10000M;

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE ‘/oracle/SID/sapdata3/temp_2/temp.data2’

SIZE 5120M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE ‘/oracle/SID/sapdata3/temp_3/temp.data3’

SIZE 5120M REUSE AUTOEXTEND OFF;

# End of tempfile additions.

And I changed my MAXDATAFILES from 784 to 2048.

Here are the complete list of my new control file look like.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “SID” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 2048

MAXINSTANCES 50

MAXLOGHISTORY 22462

LOGFILE

GROUP 1 (

‘/oracle/SID/origlogA/log_g11m1.dbf’,

‘/oracle/SID/mirrlogA/log_g11m2.dbf’

) SIZE 50M,

GROUP 2 (

‘/oracle/SID/origlogB/log_g12m1.dbf’,

‘/oracle/SID/mirrlogB/log_g12m2.dbf’

) SIZE 50M,

GROUP 3 (

‘/oracle/SID/origlogA/log_g13m1.dbf’,

‘/oracle/SID/mirrlogA/log_g13m2.dbf’

) SIZE 50M,

GROUP 4 (

‘/oracle/SID/origlogB/log_g14m1.dbf’,

‘/oracle/SID/mirrlogB/log_g14m2.dbf’

) SIZE 50M

DATAFILE

‘/oracle/SID/sapdata1/system_1/system.data1’,

—- a lot of datafile definition —

‘/oracle/SID/sapdata10/wpr_722/wpr.data722’

CHARACTER SET WE8DEC

;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE ‘/oracle/SID/sapdata3/temp_3/temp.data3’ SIZE 5120M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE ‘/oracle/SID/sapdata3/temp_2/temp.data2’ SIZE 5120M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE ‘/oracle/SID/sapdata3/temp_1/temp.data1’ SIZE 8000M REUSE AUTOEXTEND ON NEXT 20971520  MAXSIZE 10000M;

I saved this edited file to controlfile.sql then I copied again to my database server.

Before applying this file, I need to backup my old control file (WARNING : always backup your old control file before doing this).

Shutdown database before recreate control file.

SQL>shutdown immediate;

Then run your new controlfile.sql on this SQL command.

SQL>@/oracle/[SID]/controlfile.sql;

I have no error messages from this command and I can check this new control file from SQL command.

SQL>select * from v$controlfile;

Now, I have my MAXDATAFILES parameter set to 2048.