This discussion is archived
12 Replies Latest reply: Mar 2, 2013 1:24 AM by TSharma-Oracle RSS

totally lost controlfile

986295 Newbie
Currently Being Moderated
here is the scenario

i wanted to create same DB as other place

1.used ORADIM created instance
2.start pfile ="";
3.start spfile from pfile; & Pfile from spfile;
4.startup mount;
5.oracle instance started ; error in identifying controlfile , check alertlog

In above case i dont have controlfile copy (accidentally deleted)
how can i create controlfile new?

10.2.0
win 7 ultimate
  • 1. Re: totally lost controlfile
    986295 Newbie
    Currently Being Moderated
    i want to ADD that in my Pfile (init.ora which i started database) i didnt define of controlfile ,because i didnt have it
  • 2. Re: totally lost controlfile
    damorgan Oracle ACE Director
    Currently Being Moderated
    You have no copies of the control file from the original database and no backups? Please confirm that statement.

    ... and everyone else here knows exactly what I'm thinking so I won't say it. You don't have a problem creating a copy ... you have a problem with the original database and you'd best address that first.
  • 3. Re: totally lost controlfile
    sb92075 Guru
    Currently Being Moderated
    983292 wrote:
    i want to ADD that in my Pfile (init.ora which i started database) i didnt define of controlfile ,because i didnt have it
    just make a new one by doing as below
    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
        MAXLOGFILES 16
        MAXLOGMEMBERS 3
        MAXDATAFILES 100
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
      GROUP 1 '/home/oracle/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
      GROUP 2 '/home/oracle/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
      GROUP 3 '/home/oracle/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
    -- STANDBY LOGFILE
    DATAFILE
      '/home/oracle/app/oracle/oradata/orcl/system01.dbf',
      '/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf',
      '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf',
      '/home/oracle/app/oracle/oradata/orcl/users01.dbf',
      '/home/oracle/app/oracle/oradata/orcl/example01.dbf',
      '/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf'
    CHARACTER SET AL32UTF8
    ;
    -- Commands to re-create incarnation table
    -- Below log names MUST be changed to existing filenames on
    -- disk. Any one log file from each branch can be used to
    -- re-create incarnation records.
    -- ALTER DATABASE REGISTER LOGFILE '/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_01/o1_mf_1_1_%u_.arc';
    -- ALTER DATABASE REGISTER LOGFILE '/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_01/o1_mf_1_1_%u_.arc';
    -- Recovery is required if any of the datafiles are restored backups,
    -- or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    -- 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 TEMP ADD TEMPFILE '/home/oracle/app/oracle/oradata/orcl/temp01.dbf'
         SIZE 165675008  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    -- End of tempfile additions.
  • 4. Re: totally lost controlfile
    damorgan Oracle ACE Director
    Currently Being Moderated
    Provided, of course, you kept or can gather, the required information.
  • 5. Re: totally lost controlfile
    986295 Newbie
    Currently Being Moderated
    YEs i dnt have , ( i can have it if i want, BUT i am confused that will that controlfile will work for this newly created database? because older controlfiles can have older destinations of folders&files)
    but i am wondering if this kind of failure comes in future ...

    I can describe more in Detail :-

    1.I have a database running IN system A which contains folders (oracle\product\10.2.0\db_1,flash_area,oradata,admin),
    2.Now, i just copied Entire Oracle Folder as it is TO system B ( for make a copy of Database like system A) except (controlfiles , pfile,spfile - because i thot these 3 files is unique for database)
    3.used Oradim , instance created .
    4.modified that Pfile from system A and and startup with pfile , den created SPfile , and then again created Pfile from sp(so i can have my own sp and Pfiles) ---[in my Pfile i didnt define controlfiles]
    5.now its giving error of controlfile.
    6. i can have that control file of system"A" but will it work .
    7.what in case lost of control file and u can have a copy of controlfile
    8.i DONT have Backup in FRA


    10.2.0
    win 7 ultimate

    Thanks
  • 6. Re: totally lost controlfile
    986295 Newbie
    Currently Being Moderated
    One more thing i want to ask is ,,

    Which are the necessary parameters in Pfile if im Making Pfile manually ( that atleast we need these parameters to start the Datbase )

    Thanks

    Edited by: 983292 on Mar 2, 2013 11:01 AM
  • 7. Re: totally lost controlfile
    asahide Expert
    Currently Being Moderated
    Hi,

    On system A, you can create CONTROLFILE CREATION SCRIPT via follows.
    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
    Regards,
  • 8. Re: totally lost controlfile
    TSharma-Oracle Guru
    Currently Being Moderated
    If you are worried about the control file, you can restore it from the original system's A backup. If you are worried about the information inside the control file....do not worry
    You can still restore the control file and then mount your database ...after mount you can change the location of the datafiles and logfiles before start actual datafile rstore or recovery.

    1) Give the right(valid path) path of control file in your pile.
    2) Startup nomount
    3) restore the control file from system's A backup
    4) Mount the database
    5) Change location of datafiles and logfiles if you want to restore these files in different location.
    6) Restore database
    7) Recover database

    Moreover you should read

    http://docs.oracle.com/cd/B14117_01/server.101/b10734/rcmconc2.htm
  • 9. Re: totally lost controlfile
    Aman.... Oracle ACE
    Currently Being Moderated
    Did you see this doc section?
    http://docs.oracle.com/cd/E11882_01/backup.112/e10642/osadvsce.htm#BRADV90042

    Aman....
  • 10. Re: totally lost controlfile
    TSharma-Oracle Guru
    Currently Being Moderated
    Which are the necessary parameters in Pfile if im Making Pfile manually ( that atleast we need these parameters to start the Datbase )
    1) Nomrally just to start the instance you need db_name and control file parameter.

    2) You can go to your system A database and run the following query and save it. It will give you all the parameters

    SELECT name, value
    FROM gv$parameter
    WHERE isdefault = 'FALSE'
    ORDER BY 1;

    3) This is the list of sample parameters in example file

    db_name='ORCL'
    memory_target=1G
    processes = 150
    audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
    audit_trail ='db'
    db_block_size=8192
    db_domain=''
    db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
    db_recovery_file_dest_size=2G
    diagnostic_dest='<ORACLE_BASE>'
    dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
    open_cursors=300
    remote_login_passwordfile='EXCLUSIVE'
    undo_tablespace='UNDOTBS1'
    # You may want to ensure that control files are created on separate physical
    # devices
    control_files = (ora_control1, ora_control2)
    compatible ='11.2.0'
  • 11. Re: totally lost controlfile
    986295 Newbie
    Currently Being Moderated
    hey , thanks
    I have done "alter database backup controlfile to trace"
    that will go in Udump directory as .trc file
    then i can open with notepad .
    Now, shud i change only DB name in that file?

    and how can i tell system B that this is new controlfile?

    Thanks

    Edited by: 983292 on Mar 2, 2013 2:53 PM
  • 12. Re: totally lost controlfile
    TSharma-Oracle Guru
    Currently Being Moderated
    You should really start with above mentioned documentation links. I think you have no clue what you are trying to do.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points