Thursday, July 17, 2014

Creating Control File in oracle database.

We can create control file using backup scrips of current control file.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:\APP\CONTROL_FILE.SQL';

Now open your CONTROL_FILE.SQL and copy the following line and run from isqlplus.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ORADATA\ORCL\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\APP\ORADATA\ORCL\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\APP\ORADATA\ORCL\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\APP\ORADATA\ORCL\SYSTEM01.DBF',
  'D:\APP\ORADATA\ORCL\SYSAUX01.DBF',
  'D:\APP\ORADATA\ORCL\UNDOTBS01.DBF',
  'D:\APP\ORADATA\ORCL\USERS01.DBF',
  'D:\APP\ORADATA\ORCL\EXAMPLE01.DBF',
  'D:\APP\ORADATA\SPFTL\TBACNTS.DBF',
  'D:\APP\ORADATA\SPFTL\TRBFES.DBF',
  'D:\APP\ORADATA\SPFTL\TBFES.DBF',
  'D:\APP\ORADATA\SPFTL\TBAML.DBF',
  'D:\APP\ORADATA\SPFTL\DATA.DBF',
  'D:\APP\ORADATA\SPFTL\TBSTRAN .DBF',
  'D:\APP\ORADATA\SPFTL\MIG_BCBL01.DBF',
  'D:\APP\ORADATA\SPFTL\STLBAS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBITS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBMAS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBSYS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBDT101.DBF',
  'D:\APP\ORADATA\STLBAS\ORBDT201.DBF',
  'D:\APP\ORADATA\STLBAS\ORBDT301.DBF',
  'D:\APP\ORADATA\STLBAS\ORBLOG01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBMAV01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBIND01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBIMG01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBBBR01.DBF',
  'D:\APP\ORADATA\ORCL\APEX_DATA01.DBF',
  'D:\APP\ORADATA\STLBAS\MYBANK01.DBF',
  'D:\APP\ORADATA\SPFTL\SHRINK_TEST.DBF',
  'D:\APP\ORADATA\ORCL\RECLAIM01.DBF',
  'D:\APP\ORADATA\ORCL\RND_TBS.DBF'
CHARACTER SET WE8MSWIN1252
;

Another way to create control file you need to collect control file information form data dictionary view.

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE; 
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

After collect information create control file from nomount mode.

CREATE CONTROLFILE
   SET DATABASE prod
   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', 
                    '/u01/oracle/prod/redo01_02.log'),
           GROUP 2 ('/u01/oracle/prod/redo02_01.log', 
                    '/u01/oracle/prod/redo02_02.log'),
           GROUP 3 ('/u01/oracle/prod/redo03_01.log', 
                    '/u01/oracle/prod/redo03_02.log') 
   NORESETLOGS
   DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
            '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
            '/u01/oracle/prod/users01.dbs' SIZE 5M,
            '/u01/oracle/prod/temp01.dbs' SIZE 5M
   MAXLOGFILES 50
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 400
   MAXDATAFILES 200
   MAXINSTANCES 6
   ARCHIVELOG; 



No comments:

Post a Comment