Home » Database Administration » Re-create my Oracle control file

Re-create my Oracle control file

RSS Link

  • An error has occurred; the feed is probably down. Try again later.

Blog Stats

  • 714,715 hits


Categories


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.


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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

My twitter post @devratt

May 2010
M T W T F S S
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Popularity


PageRank

Bookmark and Share
%d bloggers like this: