CONTROL FILE MANAGEMENT

Note:

Before proceeding take Full Backup of Control and Datafiles in All the cases.

Multiplexing(P file):

[oracle@reddy ~]$ export ORACLE_SID=rub

[oracle@reddy ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 10:34:57 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

 SQL> startup;

ORACLE instance started.

 Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

SQL> select name from v$controlfile;

 NAME

——————————————————————————

/home/oracle/rub/control/c1.ctl

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@reddy ~]$ cd rub/control/

[oracle@reddy control]$ ls

c1.ctl

[oracle@reddy control]$ cp c1.ctl c2.ctl

[oracle@reddy control]$ ls

c1.ctl c2.ctl

[oracle@reddy control]$ cd $ORACLE_HOME/dbs

[oracle@reddy dbs]$ vi initrub.ora

*.background_dump_dest=’/home/oracle/rub/bdump’

*.compatible=’10.2.0.1.0′

*.control_files=’/home/oracle/rub/control/c1.ctl’,'/home/oracle/rub/control/c2.ctl’

*.db_block_size=4096

*.db_name=’rub7′

*.log_archive_dest=’/home/oracle/rub/archive’

*.log_archive_format=’arch_%t_%r_%s.arc’

*.log_archive_start=true

*.remote_login_passwordfile=’exclusive’

*.shared_pool_size=250m

*.undo_management=’auto’

*.user_dump_dest=’/home/oracle/rub/udump’

timed_statistics=true

 :wq!

[oracle@reddy dbs]$ cd

[oracle@reddy ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 10:50:35 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

 SQL> create spfile from pfile;

 File created.

SQL> startup;

ORACLE instance started.

 

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

SQL> select name from v$controlfile;

 NAME

——————————————————————————-

/home/oracle/rub/control/c1.ctl

/home/oracle/rub/control/c2.ctl

  

Multiplexing(SP file):

[oracle@reddy ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 11:00:03 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> select name from v$controlfile;

 NAME

——————————————————————————–

/home/oracle/rub/control/c1.ctl

 SQL> alter system set control_files=’/home/oracle/rub/control/c1.ctl’,'/home/oracle/rub/control/c2.ctl’ scope=spfile;

 System altered.

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

File created.

SQL> !

[oracle@reddy ~]$ cd rub/control/

[oracle@reddy control]$ ls

c1.ctl

[oracle@reddy control]$ cp c1.ctl c2.ctl

[oracle@reddy control]$ ls

c1.ctl c2.ctl

[oracle@reddy control]$ cd

[oracle@reddy ~]$ sqlplus sys as sysdba

S QL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 11:07:48 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

 SQL> startup;

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

SQL> select name from v$controlfile;

 NAME

——————————————————————————–

/home/oracle/rub/control/c1.ctl

/home/oracle/rub/control/c2.ctl

 

Recreating Control file

 

When existing Control file is lost or corrupted:

[oracle@reddy ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 11:07:48 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

 SQL> startup;

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

 

SQL> alter database backup controlfile to trace;(Creates trace of controlfile in user_dump_dest)

 Database altered.

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@reddy ~]$ cd rub/udump/

[oracle@reddy udump]$ ls -ltr (look for the last trace of controlfile in user_dump_dest)

-rw-r—– 1 oracle dba 781 Nov 18 11:05 rub_ora_5139.trc

-rw-r—– 1 oracle dba 1222 Nov 18 11:07 rub_ora_5174.trc

-rw-r—– 1 oracle dba 1306 Nov 18 11:07 rub_ora_5195.trc

-rw-r—– 1 oracle dba 8416 Nov 18 11:35 rub_ora_5196.trc

[oracle@reddy udump]$ cp rub_ora_5196.trc reddy.sql

Remove the unwanted comments

[oracle@reddy udump]$ vi reddy.sql

To remove the unwanted lines use the commands (esc :set nu, (n.no) dd)

1 STARTUP NOMOUNT

2 CREATE CONTROLFILE REUSE DATABASE “RUB7″ NORESETLOGS

ARCHIVELOG

3 MAXLOGFILES 4

4 MAXLOGMEMBERS 4

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 292

8 LOGFILE

9 GROUP 1 ‘/home/oracle/rub/datafiles/redo01.log’ SIZE 10M,

10 GROUP 2 ‘/home/oracle/rub/datafiles/redo02.log’ SIZE 10M

11 — STANDBY LOGFILE

12 DATAFILE

13 ‘/home/oracle/rub/datafiles/system01.dbf’,

14 ‘/home/oracle/rub/datafiles/undotbs1.dbf’,

15 ‘/home/oracle/rub/datafiles/sysaux01.dbf’,

16 ‘/home/oracle/rub/datafiles/users01.dbf’

17 CHARACTER SET US7ASCII

18 ;

 

[oracle@reddy udump]$ cd /home/oracle/rub/control/

[oracle@reddy control]$ rm -rf *.ctl (Remove the earlier controlfile)

[oracle@reddy control]$ ls

[oracle@reddy control]$

[oracle@reddy control]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 12:04:21 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

SQL> @/home/oracle/rub/udump/reddy.sql

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Control file created.

SQL> alter database open;

 Database altered.

When you want to change MAX Parameter of database:

[oracle@reddy ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 12:26:54 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

 SQL> alter database backup controlfile to trace;

 Database altered.

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 SQL> !

[oracle@reddy ~]$ cd rub/udump/

[oracle@reddy udump]$ ls -ltr

-rw-r—– 1 oracle dba 1306 Nov 18 11:07 rub_ora_5195.trc

-rw-r—– 1 oracle dba 8416 Nov 18 11:35 rub_ora_5196.trc

-rw-r—– 1 oracle dba 541 Nov 18 11:55 reddy.sql

-rw-r—– 1 oracle dba 625 Nov 18 12:05 rub_ora_5822.trc

-rw-r—– 1 oracle dba 1706 Nov 18 12:06 rub_ora_5845.trc

-rw-r—– 1 oracle dba 5330 Nov 18 12:27 rub_ora_5902.trc

[oracle@reddy udump]$ cp rub_ora_5902.trc reddy1.sql

[oracle@reddy udump]$ vi reddy1.sql

1 STARTUP NOMOUNT

2 CREATE CONTROLFILE REUSE DATABASE “RUB7″ NORESETLOGS

ARCHIVELOG

3 MAXLOGFILES 8

4 MAXLOGMEMBERS 4

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 292

8 LOGFILE

9 GROUP 1 ‘/home/oracle/rub/datafiles/redo01.log’ SIZE 10M,

10 GROUP 2 ‘/home/oracle/rub/datafiles/redo02.log’ SIZE 10M

11 — STANDBY LOGFILE

12 DATAFILE

13 ‘/home/oracle/rub/datafiles/system01.dbf’,

14 ‘/home/oracle/rub/datafiles/undotbs1.dbf’,

15 ‘/home/oracle/rub/datafiles/sysaux01.dbf’,

16 ‘/home/oracle/rub/datafiles/users01.dbf’

17 CHARACTER SET US7ASCII

18 ;

 

 [oracle@reddy udump]$ cd

[oracle@reddy ~]$ cd rub/control/

[oracle@reddy control]$ ls

c1.ctl c2.ctl

[oracle@reddy control]$ rm -rf *.ctl

[oracle@reddy control]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 12:51:48 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

  SQL> @/home/oracle/rub/udump/reddy1.sql

ORACLE instance started.

 Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

 Control file created.

 SQL> alter database open;

 Database altered.

When you want to change database name:

[oracle@reddy ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 13:17:04 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

 SQL> startup;

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

SQL> select name from v$database;

 NAME

———

RUB7

 SQL> alter database backup controlfile to trace;

 Database altered.

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@reddy ~]$ cd rub/udump/

[oracle@reddy udump]$ ls -ltr

-rw-r—– 1 oracle dba 541 Nov 18 11:55 reddy.sql

-rw-r—– 1 oracle dba 625 Nov 18 12:05 rub_ora_5822.trc

-rw-r—– 1 oracle dba 1706 Nov 18 12:06 rub_ora_5845.trc

-rw-r—– 1 oracle dba 5330 Nov 18 12:27 rub_ora_5902.trc

-rw-r—– 1 oracle dba 541 Nov 18 12:45 reddy1.sql

-rw-r—– 1 oracle dba 2680 Nov 18 12:48 rub_ora_5946.trc

-rw-r—– 1 oracle dba 625 Nov 18 12:53 rub_ora_5978.trc

-rw-r—– 1 oracle dba 1706 Nov 18 12:53 rub_ora_5999.trc

-rw-r—– 1 oracle dba 3056 Nov 18 13:16 rub_ora_6202.trc

-rw-r—– 1 oracle dba 625 Nov 18 13:21 rub_ora_6224.trc

-rw-r—– 1 oracle dba 653 Nov 18 13:21 rub_ora_6271.trc

-rw-r—– 1 oracle dba 5468 Nov 18 13:27 rub_ora_6272.trc

[oracle@reddy udump]$ cp rub_ora_6272.trc reddy2.sql

 [oracle@reddy udump]$ vi reddy2.sql

1 STARTUP NOMOUNT

2 CREATE CONTROLFILE set DATABASE “RUB” RESETLOGS

ARCHIVELOG

3 MAXLOGFILES 8

4 MAXLOGMEMBERS 4

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 292

8 LOGFILE

9 GROUP 1 ‘/home/oracle/rub/datafiles/redo01.log’ SIZE 10M,

10 GROUP 2 ‘/home/oracle/rub/datafiles/redo02.log’ SIZE 10M

11 — STANDBY LOGFILE

12 DATAFILE

13 ‘/home/oracle/rub/datafiles/system01.dbf’,

14 ‘/home/oracle/rub/datafiles/undotbs1.dbf’,

15 ‘/home/oracle/rub/datafiles/sysaux01.dbf’,

16 ‘/home/oracle/rub/datafiles/users01.dbf’

17 CHARACTER SET US7ASCII

1 8 ;

 : x

 [oracle@reddy udump]$ cd $ORACLE_HOME/dbs

[oracle@reddy dbs]$ vi initrub.ora

*.background_dump_dest=’/home/oracle/rub/bdump’

*.compatible=’10.2.0.1.0′

*.control_files=’/home/oracle/rub/control/c1.ctl’

*.db_block_size=4096

*.db_name=’rub’

*.log_archive_dest=’/home/oracle/rub/archive’

*.log_archive_format=’arch_%t_%r_%s.arc’

*.log_archive_start=true

*.remote_login_passwordfile=’exclusive’

*.shared_pool_size=250m

*.undo_management=’auto’

*.user_dump_dest=’/home/oracle/rub/udump’

timed_statistics=true

[oracle@reddy dbs]$ cd /home/oracle/rub/control/

[oracle@reddy control]$ ls

c1.ctl c2.ctl

[oracle@reddy control]$ rm -rf *.ctl

[oracle@reddy control]$ ls

[oracle@reddy control]$ sqlplus sys as sysdba

 SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 18 13:51:42 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

 SQL> create spfile from pfile;

File created.

SQL> @/home/oracle/rub/udump/reddy2.sql

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

 Control file created.

 SQL> alter database open resetlogs;

 Database altered.

 SQL> select name from v$database;

NAME

———

RUB

Advertisement