STANDBY LOGFILE control_files string /u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl. If you want to discuss contents of this page - this is the easiest way to do it. See pages that link to and include this page. Method 1: Copythe good control file that we have available to the missing control file location, $cp /u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl /u01/oracle/DB11G/oradata/brij/control01.ctl, SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 19 12:05:17 2014. If possible, restore the control file to its original location.

You should now have control files re-created in every location identified by your control_files initialization parameter in your target database and you can go ahead with the database recovery steps.. awesome dude ,keep posting like this,keep it up.. a nice document . The following SQL statement backs up a database's control file to /backup/control01.dbf: "Copying a Multiplexed Control File to a Nondefault Location", "Create New Control File After Losing All Current and Backup Control Files", "Restoring a Backup Control File to the Default Location", "Backing Up the Control File to a Trace File", "Re-Creating Datafiles When Backups Are Unavailable: Scenario". Also, In the above case it was assumed that the autobackup of control file was enabled. channel ORA_DISK_1: piece handle=/backups/brij/controlfile_brij_c-1279650270-20140219-02 tag=TAG20140219T131851 Restore the backup control file to all locations specified in the CONTROL_FILES parameter. If the online logs are inaccessible, then you must re-create the control file, using the procedure described in "Create New Control File After Losing All Current and Backup Control Files". Edit the output of ALTER DATABASE BACKUP CONTROLFILE TO TRACE to reflect the change. set dbid=4097466002; Database Buffers 390070272 bytes allocated channel: ORA_DISK_1 channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_19/o1_mf_ncsnf_TAG20140219T133923_9jb97f0z_.bkp tag=TAG20140219T133923 output file name=/u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl If all datafiles are current, and if redo in the online logs is required for recovery, then you cannot open the database without applying the online logs. channel ORA_DISK_1: SID=134 device type=DISK, channel ORA_DISK_1: restoring control file SQL> alter system set control_files=/u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl scope=spfile; SYS@brij > alter system set control_files=/u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl scope=spfile; ORA-01507: database not mounted However, if an RMAN catalog is not used, then any recovery of the database will be reliant on the backup records stored within the control file. GROUP 4 ( Finished recover at 19-FEB-2014 14:28:25.

Now all the archivelogs are applied, fingers crossed for the last operation that is supposed to bring back the database to life: Yes everything is OK!!! You should create two or more copies of the control file during database creation. By default, at least one copy of the control file is created during database creation. /u01/oracle/DB11G/oradata/brij/sysaux01.dbf, If method one is not possible because there is no RMAN Catalog or it has been destroyed in addition to the control file not being available, then method two is the last resort, but not a guaranteed resolution. Starting restore at 19-FEB-2014 13:26:46 Logon to the Database server as Oracle. Copyright (c) 1982, 2011, Oracle and/or its affiliates. allocated channel: ORA_DISK_1 The following guide shows how the control file can be recovered from Netbackup using RMAN when no local copy of the control file is available. channel ORA_DISK_1: restoring control file Apart from generic name for your datafiles, a single controlfile, no spfile, default-configured redologs and probably no temporary tablespace. This ensures that whenever any backups are taken an automatic and independent backup of the controlfile is also taken to a predefined location, normally a file system. Now either you copy the good control file that we have available to the missing control file location ormodify the control_files parameter in the initialization file or spfile to remove the lost control file information from spfile/pfile. channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 The %F tag combines DBID, day, month, year and sequence into a unique and repeatable generated name. ORA-00202: control file: /u01/oracle/DB11G/oradata/brij/control01.ctl The current log sequence number 2. And through this instance you can have access to many things without actually having a real database. Create a temporary database instance, mount the backup control file, and then run ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Youll have to edit the trace file so that it contains only the appropriate SQL statement that you require. 1. Linux-x86_64 Error: 2: No such file or directory Search results are not available at this time. If you cannot repair the hardware problem quickly, then proceed with database recovery by restoring damaged control files to an alternative storage device, as described in "Copying a Multiplexed Control File to a Nondefault Location". Normal restore operations with RMAN are quite easy and secure as far as you have backupsets for database, archivelogs, and spfile/controlfile: Step 1 restore the spfile and start the instance Remember that Anytime you restore a control file from a backup, you are required to perform media recovery on your entire database and then open it with the open resetlogs command. Total System Global Area 1068937216 bytes /u01/oracle/DB11G/oradata/brij/users01.dbf, If autobackup of control file wasnotenabled then we will have to use below command instead to provide complete path of control file backup. Variable Size 671089544 bytes Execute the CREATE CONTROLFILE statement manually (refer to Oracle Database SQL Reference). Below are some of the recovery scenarios related to control files. Use the CREATE CONTROLFILE statement from the trace output as-is. Very useful for us now. For example, here is the partial output of RMAN messages during database backup: Furthermore, the control file backup is simply a copy of the controlfile and as such, can be manipulated using OS commands. Without an existing control file (and no RMAN catalog), there is no means to identify which backuppiece contains the control file. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Your database does not have to be mounted for this to work. /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_6_9hl972tr_.log To restore the Oracle database there must be a control file, but if the control file does not exist is there a way to restore the control file from a Data Protection backup so that the Oracle database can be recovered. Click here to toggle editing of individual sections of the page (if possible). using target database control file instead of recovery catalog If the disk and file system containing the lost control file are intact, then you can simply copy one of the intact control files to the location of the missing control file. RMAN will by default back up your control file anytime you back up datafile 1 (which is SYSTEM data file), regardless of whether you have the autobackup of your control file feature enabled. CASE 1:LOST ALL CONTROL FILES AND USING FLASH RECOVER AREA (FRA) BUT NOT RECOVERY CATALOG, CASE 1 SCENARIO 1:AUTOBACKUP OF CONTROL FILE IS ENABLED . if you have not done any such RMAN manual configuration then for your database the control file autobackup will go to default location: $ORACLE_HOME/dbs and you dont need to use above command then. Linux-x86_64 Error: 2: No such file or directory Watch headings for an "edit" link when available. The easiest means to restore the control file is when the RMAN Catalog exists and is viable for the restore. A few lines of PL/SQL code can help you to restore all the datafiles from all the available backuppieces. output file name=/u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl using target database control file instead of recovery catalog This allows DBAs to get access to the control file outside of the standard RMAN backups. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ, where: 1. RMAN-03002: failure of alter db command at 02/19/2014 13:11:39 We lost both of the control files, Control file 1: /u01/oracle/DB11G/oradata/brij/control01.ctl, Using the RMAN Catalog, Oracle support should be able to assist in restoring the control file. No results were found for your search query. At many sites, the control file backup is backed up to tape using backup software. Use the control file copy to obtain SQL output. Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB, Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB, Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture, RMAN: RECOVERY SCENARIOS RELATED TO DATABASE, Multiplex control file in 11gR2 RAC ASM using RMAN, RMAN: GRANTING RESTRICTED ACCESS USING VIRTUAL PRIVATE CATALOG, KEY ORACLE BACKGROUND PROCESSES RELATED TO BACKUP AND RECOVERY, INSTALLING DATA GUARD 11g SETUP ON LINUX FOR TESTING, REP-54002: file : Cache item is no longer valid. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== media recovery complete, elapsed time: 00:00:01 Table 18-1 Scenarios When Control Files Are Lost. For example, if you recently added a datafile to the database, then add this datafile to the DATAFILE clause of the CREATE CONTROLFILE statement. Open the database with the RESETLOGS option after finishing recovery: If you cannot restore the control file to its original place because the media damage is too severe, then you must specify new control file locations in the server parameter file. Step 3 restore the database (meaning the datafiles) Replace controlfile with spfile in the above RMAN blcok should you need to restore the spfile, as both the controlfile and spfile will be in the same backuppiece, How to Restore a Control File from Netbackup without an RMAN Catalog, Unless otherwise stated, the content of this page is licensed under. You can also create control files later, if you lose control files or want to change particular settings in the control files. Additional information: 3. Step 2 restore the controlfile and mount the database COntrol file 2: /u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl. Since these scenarios can be quite complex, it is suggested that Oracle Support be contacted for assistance as a first step in the process. Variable Size 671089544 bytes

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== Redo Buffers 5517312 bytes If you then receive another message saying that the required archived log is missing, it probably means that a necessary redo record is located in the online redo logs. > change REUSE to SET channel ORA_DISK_1: SID=134 device type=DISK, .. ORA-01194: file 1 needs more recovery to be consistent Nous sommes des experts des infrastructures et plateformes de donnes innovantes et efficientes. For example, if the control file locations listed in the server parameter file are as follows, and both locations are inaccessible: Then, you can edit the initialization parameter file as follows: If all control files have been lost in a permanent media failure, but all online redo log members remain intact, then you can recover the database after creating a new control file. METHOD 1:Getting the DBID from an Autobackup File, If you check any of the RMAN backup log or check the RMAN generated autobackup files, you can easily find DBID, Starting Control File and SPFILE Autobackup at 19-FEB-2014 14:17:52 db-crawler, a database search utility for Documentum, Power BI Active Directory data integration. One such backup tool is Netbackup which can be used in conjunction with RMAN to redirect or duplex auto control file backups to tape in addition to or instead of disk. Method 2: modify the control_files parameter in the initialization file or spfile, Just starting database in nomount mode is enough tomodify the control_files parameter in the initialization file or spfile. Two of the easy methods are described below. output file name=/u01/oracle/DB11G/fast_recovery_area/brij/control02.ctl Work is not yet finished because the datafiles are probably inconsistent. 17 June 2018, [{"Product":{"code":"SSTFZR","label":"Tivoli Storage Manager for Databases"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Protection for Oracle","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]. The timestamp of the database creation Note that changes to the database are recorded in the alert_SID.log, so check this log when deciding which option to choose. Restore a backup control file, perform incomplete recovery, and then open RESETLOGS. This means we can not use the same binary control file and we need to recreate it. channel ORA_DISK_1: SID=134 device type=DISK, channel ORA_DISK_1: starting datafile backup set restore Finished backup at 19-FEB-2014 13:03:01. ; After editing the control file trace file as per requirement, move it to target server and execute it as sysdba. NOTE Controlfile autobackups can be identified using the standard name generated by the RMAN %F tag. MAXINSTANCES 8 piece handle=/backups/brij/controlfile_brij_c-1279650270-20140219-05comment=NONE However, what happens if the SAN has been lost in its entirety and there is no backup of the control file on disk? Recovery Manager: Release 11.2.0.4.0 Production on Wed Feb 19 13:26:11 2014. The advantage of this tactic is that you are not required to open the database with the RESETLOGS option. -rw-rw---- oracle dba 262144 Jul 0108:34 /rman_MYSID_010712_223410_arch_j2odkrdj_1_1_819621299 You are cloning your database to another database and you will be using a new database name for it. media recovery complete, elapsed time: 00:00:01 Redo Buffers 5517312 bytes For example, enter: Apply the prompted archived logs. For example, enter: Assuming that the disk and file system containing the lost control file are not intact, then you cannot copy one of the good control files to the location of the missing control file. Lets iterate this anonymous PL/SQL block for each backuppiece in your backup folder: Well done! dbi services est une entreprise spcialise dans le consulting et les services IT. Nous proposons nos clients des solutions adaptes et sur mesure grce nos consultant.e.s dont les comptences et connaissances voluent constamment grce la formation continue. In which schema do temporary tables go in PostgreSQL. Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one control file has not been damaged by the media failure. "RMAN Restore and Recover of a Database When the Repository and spfile/init.ora Files are Lost". 2. Total System Global Area 1068937216 bytes ) SIZE 100M BLOCKSIZE 512, 20140219 >> refers to DATE when backup was created. If the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. Check out how this page has evolved in the past. NAME TYPE VALUE Now you can not start the database since the oracle process is unable to locate the control files. MAXDATAFILES 100 including current control file in backup set, including current SPFILE in backup set Or you just manage to keep your job , Encore un bilan la hausse pour dbi services, Eine weitere Umsatzsteigerung fr dbi services. 9 datafiles were restored. 2. GROUP 5 ( RESTORE CONTROLFILE FROM AUTOBACKUP channel ORA_DISK_1: restored backup piece 1 Identifing the Controlfile Backups in Netbackup, Creative Commons Attribution-ShareAlike 3.0 License, YYYYMMDD is a time stamp in the Gregorian calendar of the day the backup is generated, QQ is the sequence in hexadecimal number that starts with 00 and has a maximum of 'FF' (256). Performed your most recent execution of ALTER DATABASE BACKUP CONTROLFILE TO TRACE before you made a structural change to the database. If a multiplexed control file becomes damaged, you can either modify the control_files initialization parameter to match the locations of the remaining good control files or copy a good control file to the location of the damaged or missing control file. ORA-00202: control file: /u01/oracle/DB11G/oradata/brij/control01.ctl Oracle technote ID 403883.1 Start the database in nomount mode.

) SIZE 100M BLOCKSIZE 512, General Wikidot.com documentation and help section. and below will be the error that you will see in the alert log at this stage>, ALTER DATABASE MOUNT media recovery complete, elapsed time: 00:00:01 I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. Starting restore at 19-FEB-2014 13:59:42 RMAN> restore controlfile from autobackup; Starting restore at 19-FEB-2014 12:50:00 STARTUP NOMOUNT The control files are identical copies of each other. So we now have only one control file in our database, which is not recommended. Start the database in NOMOUNT mode. So you need to first find the DBID for your target database, so that RMAN should be aware of the database for which restore is required. When you restore your control file and when the autobackup feature has not been enabled, then RMAN is unable to determine by itself the default location. ORACLE instance shut down. MAXLOGHISTORY 292 Append content without editing the whole page source. RMAN-03002: failure of restore command at 02/19/2014 14:20:24

Remember that control files autobackup OFF doesnt mean you dont have control files backup. Modified date: Say our database was down and when we tried to start it we got the below error: SYS@brij > startup RMAN> restore controlfile from /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_19/o1_mf_ncsnf_TAG20140219T130101_9jb702jq_.bkp; Starting restore at 19-FEB-2014 13:10:28 This is true although we didnt restore any datafiles (because the control files SCN is no longer synchronized with the SCNs in the datafiles and online redo log files). /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_4_9hl96l67_.log I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. In this case, you must alter the CONTROL_FILES initialization parameter to indicate a new location for the missing control file. Using this technote, with the assistance of Oracle support, it should be possible to recreate the control file. -rw-rw---- oracle dba 60555264 Jul 0108:33 /c-4097466002-20120701-0b. MAXLOGMEMBERS 3 If youre neither using a flash recovery area nor using a recovery catalog, then you might have to know your DBID before you restore the control file. /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_5_9hl934xp_.log NOTE:Now since you enabled autobackups of your control file and also used a flash recovery area, you dont have to explicitly provide RMAN with the name and location ofbackup files or your target database identifier (DBID). Do not have a control file backup in either TO TRACE format or TO filename format. Specify UNTIL CANCEL if you are performing incomplete recovery. This is the simplest method that RMAN provides for restoring a control file. restore controlfile from 'c-4097466002-20120701-0c'; Here we are not using RMAN for control file restore. piece handle=/u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_19/o1_mf_ncsnf_TAG20140219T130101_9jb702jq_.bkptag=TAG20140219T130101 comment=NONE Something does not work as expected? Thankfully my work allows me time for researching new technologies (and to write about them).

CASE 1 SCENARIO 2:AUTOBACKUP OF CONTROL FILE IS NOT ENABLED, CASE 2:LOST ALL CONTROL FILES AND USING RECOVERY CATALOG BUT NOT FLASH RECOVER AREA (FRA), CASE 3:LOST ALL CONTROL FILES AND NOT USING EITHER RECOVERY CATALOG OR FLASH RECOVER AREA (FRA), CASE 5:RECREATING THE CONTROL FILES (FOR DATABASE CLONING). It should never happen but sometimes it happens. Now if the entire SAN gets corrupted or the ASM disks are lost, the control file backup can be recovered from tape using RMAN and Netbackup. To restore a backup control file to its default location: If the instance is still running, shut it down: Correct the hardware problem that caused the media failure. Copyright2003, 2005,Oracle. using target database control file instead of recovery catalog Please try again later or use one of the other support options on this page. RMAN backup records are stored in the RMAN catalog and the control file. Finished Control File and SPFILE Autobackup at 19-FEB-2014 14:17:55, The auto generated control file is : /backups/brij/controlfile_brij_c-1279650270-20140219-05, c >> refers to control file backup }, 3. RMAN-00571: =========================================================== Database opened. CREATE CONTROLFILE REUSE DATABASE BRIJ RESETLOGS ARCHIVELOG If the online logs contain redo necessary for recovery, then re-create the control file. using target database control file instead of recovery catalog -rw-rw---- oracle dba 4980736 Jul 0108:34 /rman_MYSID_010712_223410_arch_j0odkrcq_1_1_819621274 > change database name BRIJ to the target database name SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE to '%F'; The Data Protection for Oracle is unable to restore the database because there is no control file available to complete the task. Options to restore Oracle DB when the control file is not available. Say If AUTOBACKUP OF CONTROL FILE WAS NOT ENABLED, then also RMAN restore controlfile; comannd would have located the right backup piece to get control file. You can use the output of the following command from source database to re-create your control file: SQL> alter database backup controlfile to trace; The previous command generates a trace file that is placed in your user dump directory. /u01/oracle/DB11G/oradata/BRIJ/onlinelog/o1_mf_5_9hl934pt_.log, Copyright (c) 1982, 2013, Oracle. RMAN uses the DBID to uniquely identify databases. As indicated in the following table, the procedure for restoring the control file depends on whether the online redo logs are available. channel ORA_DISK_1: restoring control file This is true even if you dont restore any datafiles (because the control files SCN is no longer synchronized with the SCNs in the datafiles and online redo log files). ORA-27041: unable to open file /u01/oracle/DB11G/oradata/brij/example01.dbf full resync complete. > change location of logfiles and datafiles. channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/BRIJ/backupset/2014_02_19/o1_mf_ncsnf_TAG20140219T133923_9jb97f0z_.bkp channel ORA_DISK_1: SID=133 device type=DISK, channel ORA_DISK_1: starting datafile backup set restore Let us look at all the above cases with some example: SCENARIO 1: AUTOBACKUP OF CONTROL FILE IS ENABLED, It is highly recommended to enable autobackup of the control file, We had two control files, one at FRA location and another in Non-FRA location.