Catatan kerja

Database beberapa permasalahan yang terjadi di tempat kerja

When and How to Recreate the Controlfile October 28, 2008

Filed under: Oracle — aderahman @ 4:24 am

When to Create Controlfile
No one should not create control file until he is not suppose to do it. You should only need to recreate your control file under very special circumstances:

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

2)You need to change a “hard” database parameter such as MAXDATAFILES, MAXLOGFILES, MAXLOGHISTORY, etc. Though after 10.2g it is handled by database.

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

4)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.

How I will create a new control file:

In this case I may have to face two scenarios.

Scenario 1:CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE:
————–

1.Create a control file trace
SQL> conn / as sysdba
SQL>startup mount;
SQL>alter database backup controlfile to trace as ‘file.txt’;

2.Modify the trace file ‘file.txt’ and change the required parameter in it.

3.Shutdown the database. shutdown immediate;

4.Take a full database backup.

5.Rename/move the existing database controlfiles to a backup.

6.Create the new controlfile.

7.Take backup of full database.

CREATING A NEW CONTROL FILE WITHOUT AN EXISTING CONTROL FILE:
—————————————————————–
1. Take a full backup of the database, including all datafiles and redo log files.

2.Do a startup nomount.

3.Issue the create controlfile statement.

4. Perform media recovery on the database. recover database.

5.Open the database. Alter database open.

6. At the first opportunity, shut the database down and take a full cold backup.

Here is one script of how you can create a new controlfile.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “database_name_here” NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 449
LOGFILE
GROUP 1 ‘path of redo log member here’ SIZE 500K,
GROUP 2 ‘path of redo log member here’ SIZE 500K
DATAFILE
‘list of datafile name here’,
‘/path/oracle/dbs/data.dbf’,
‘/path/oracle/dbs/data02.f’,
‘/path/oracle/dbs/arju02.dbf’,
‘/path/oracle/dbs/arju.dbf
CHARACTER SET WE8DEC

 

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