12 Replies Latest reply: Mar 2, 2013 3:24 AM by TSharma-Oracle RSS

    totally lost controlfile

    986295
      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
          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
            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
              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
                Provided, of course, you kept or can gather, the required information.
                • 5. Re: totally lost controlfile
                  986295
                  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
                    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
                      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
                        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....
                          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
                            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
                              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
                                You should really start with above mentioned documentation links. I think you have no clue what you are trying to do.