Sitemap

Amicuk Programming Answers

Best script for recreating control file

-0001-11-30   Views:0

Advertisement

Hi Yesterday I had a lot of fun trying to refresh a Dev db with an inconsistent backup (just learned that the offline backup from that night finished late and not before the db was started up again, thus making datafile inconsistent). Today I will tr

Hi
Yesterday I had a lot of fun trying to refresh a Dev db with an inconsistent backup (just learned that the offline backup from that night finished late and not before the db was started up again, thus making datafile inconsistent).
Today I will try again. I am waiting for the tape backup to finish restoring to the Dev datafile directory.
I would love to know if there is a template out there, or if we can agree here, for the best standard for recreating a control file through a @script.
I will include the one I intend to use here, minus the datafiles.
I am using the RESETLOGS, since we are using :"SET" database.
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 19285
LOGFILE
GROUP 1 (
'/oradbi1/oracle/proddata/log01a.dbf',
'/oradbi1/oracle/proddata/log01b.dbf'
) SIZE 10M,
GROUP 2 (
'/oradbi1/oracle/proddata/log02a.dbf',
'/oradbi1/oracle/proddata/log02b.dbf'
) SIZE 10M,
GROUP 3 (
'/oradbi1/oracle/proddata/log03a.dbf',
'/oradbi1/oracle/proddata/log03b.dbf'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/oradbi1/oracle/proddata/system01.dbf',
'/oradbi1/oracle/proddata/applsysd09.dbf',
'/oradbi1/oracle/proddata/bend06.dbf'
CHARACTER SET US7ASCII
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi1/oracle/proddata/tmp04.dbf'
SIZE 2040M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi1/oracle/proddata/tmp03.dbf'
SIZE 2040M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi1/oracle/proddata/tmp02.dbf'
SIZE 2040M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi1/oracle/proddata/tmp01.dbf'
SIZE 2000M REUSE AUTOEXTEND OFF;
If anyone can see room for improvement on this, please let us know.
Thanks.
AIX 5.2
Oracle 9.2

The replay answer
Advertisement
I normally break it up into seperate scripts (even though the "trace" file generates a single script file).
1. The first is the CREATE CONTROLFILE itself. If successful , it also mounts the database.
The most important portion is to validate that we do have ALL the datafiles included.
(eg if you are generating from an old controlfile backup and some new datafile(s) have been added since that backup you had better ensure that you add them to the script , else the RECOVER DATABASE will ignore them and they will be unrecoverable after the OPEN RESETLOGS !)
2. The next portion is the RECOVER DATABASE USING BACKUP CONTROLFILE which I never run non-interactively.
Always ensure that it is NOT an interactive run. If you really have more than a dozen archivelogs to apply, then you could just enter AUTO after supplying the first 3 or 4 archivelogs and wait for the RECOVER DATABASE to "error" out after it applies the last archivelog.
3. The next portion is a manual RECOVER to apply any Archivelogs that couldn't be applied by the AUTO -- eg Archives that weren't on disk but which I've restored OR the actual last Active/Current Online Redo Logs that hadn't been applied.
between step 3 and step 4,
to be safe : I could SHUTDOWN (it is MOUNTED but not OPEN) and take a Cold Backup of the database files and controlfiles. -- This can help me retry a RESETLOGS or restore a datafile that I might have mistakenly excluded from the CREATE CCF script
and re-"recover" database -- meaning that Oracle will recover that datafile alone.
4. Finally I review the alert.log, satisfy myself that I have applied all archivelogs and then manually issue the ALTER DATABASE OPEN RESETLOGS.
5. Now I add TEMPFILEs (if 10g hasn't done it "automagically")
6. If I have time, take a Cold Backup OR initiate a Hot Backup asap.
I know that 10g now provides supported methods of recovering through a RESETLOGS, based on Incarnation ID, ... but still , old habits and old commands die hard.
Have I answered your question about how to format the CREATE CCF ? No. But I've just added some inputs on how I prefer the next few steps be done.
Hemant K Chitale
http://hemantoracledba.blogspot.com
Edited by: Hemant K Chitale on Oct 22, 2008 10:05 PM
Added the addition of TEMPFILEs
Edited by: Hemant K Chitale on Oct 22, 2008 10:20 PM
Corrected step 2 to be "Always ensure that it is NOT an interactive run"

Go to See the other 11 answers

Best script for recreating control file

Category:DefaultRelease time:-0001-11-30Views:130

Hi Yesterday I had a lot of fun trying to refresh a Dev db with an inconsistent backup (just learned that the offline backup from that night finished late and not before the db was started up again, thus making datafile inconsistent). Today I will tr[More]

Unable to recreate control file.

Category:DefaultRelease time:-0001-11-30Views:130

Hello experts. I'm trying to recreate controlfile in oracle database 10.2 using trace file. by during running script it shows   ERROR at line 8: ORA-01967: invalid option for CREATE CONTROLFILE my script create control file script below. please help[More]

Problem in recreating control file

Category:DefaultRelease time:2015-10-11Views:130

Hi, When i recreate controlfile ... It creates a set of datafile... FILE# NAME STATUS 84 /u01/app/oracle/product/10.2.0/dbs/MISSING00084 RECOVER 85 /u01/app/oracle/product/10.2.0/dbs/MISSING00085 RECOVER 86 /u01/app/oracle/product/10.2.0/dbs/MISSING0[More]

Recreating control file in RAC with noresetlogs

Category:DefaultRelease time:-0001-11-30Views:130

Hi all, I need to recreate the controlfile in a 2-node RAC environment (9i) for altering the MAXLOGHISTORY value. I will be using noresetlogs as I have all the online logs available with me. I referred to metalink note (Note:118931.1) for the same, a[More]

Recreate control file

Category:DefaultRelease time:-0001-11-30Views:130

Hi gurus! Need help I've lost all my control files + i want to rename my database from testrman to ntstrman. But I am getting an error ORA 01503 create control file failed ORA 01160 file is not a daatafile dataf file is '/oradata/ntstrman/temp01.dbf'[More]

ERROR IN RECREATING CONTROL FILES

Category:DefaultRelease time:-0001-11-30Views:130

hello, I am trying to learn the concepts of database administration. I am facing a problem while creating a new control file: O.S=Windows 7 ORacle 10g What i want to do: 1. The database has been created, now i have deleted the control file of my data[More]

Recreating Control File

Category:DefaultRelease time:-0001-11-30Views:130

Hi Can anybody explain when to use "RESETLOGS" and "NORESETLOGS" option while making backup of Control File to TRACE and whats the purpose of using it? ALTER DATABASE BACKUP CONTROL FILE TO TRACE RESETLOGS/NORESETLOGS; TIA Aqueel.read[More]

Problem while recreating control file : Oracle 10g

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am using oracle 10g version of database. i have multiple databases. I was not able to start one database which led to control file error. Then I found that the file got corrupted. I used the following syntax to restore my control file. STARTUP[More]

SQL Loader Control File Recoverable Option in Oracle 9i

Category:DefaultRelease time:-0001-11-30Views:130

We are migrating from Oracle 8 to Oracle 9i. We are running some of the SQL*Loader scripts. The control file uses "OPTIONS (UNRECOVERABLE)" option, which is working fine with Orace 8 whereas it is not working in Oracle 9i. Are these options &quo[More]

DBCA -control file locations in template

Category:DefaultRelease time:-0001-11-30Views:130

Windows Server 2003 Enterprise. Oracle 9.2.0.6 With Oct 2005 patch. I'm trying to place my control files in an alternate shared location as required for using Oracle Failsafe. The editor allows me to make changes but when I view the template and the[More]

RECREATE DATABASE Using CONTROL File After SUSPEND Database

Category:DefaultRelease time:-0001-11-30Views:130

Hello All, Does someone can tell me if he does achieve recreating a database using "Backup Control File" after putting database in "suspend" mode? The procedure looks like this : 1 - Alter DATABASE Suspend 2 - A Snapshot of the filesys[More]

Hot
Dear ADOBE, Please HELP. I have paid for a CC Complete and I am encountering several problems. What's worse is it's a struggle to reach you for help or "Customer Care". I struggled finding a way to Chat for help. I left messages on Twitter for h [More]
My MacBook Pro has 10.5.8 but came with 10.5.7 install disc and won't boot up and can't reinstall. the disc is spinning and I see the apple on the screen with the spinning gear and after a while it just turns off OR the few times it does get past tha [More]
Hello Friends, I am new to this community,please tell me if I make any mistake while posting. Friends I had written this code. ALV is displayed at first time but when I am double clicking on the row then error is coming related to mismatch of actual [More]
Hi All, in vendor master data, we maintain email id for sending remittance advice to vendor. We have requirement to send remittance advice on more than one email id for a single vendor. Could you please advice how we can do this. Regards DeepakHi , W [More]
Hi all, I need a SAP doc for Case Management-Interaction center. Please send me on [email protected] Will surealy get points . Nice weekendHi Babu, Please check the following SAP Help link: http://help.sap.com/saphelp_crm50/helpdata/en/89/c5be40d7e4c [More]
Hi. I just bought a Mac Mini Server and want to use the VPN service to access files on the host. I am not sure how this works and so far have been unable to find a tutorial that walks through the steps to do so. If anyone here can help me out I would [More]
Hi, In the list data type, I stored multiple values. I am not able to access the individual values. How to access the particular value of List datatype in Link editor? with the expression editor functions, I can able to get the first and last element [More]
Hi there, I would like to get buy-in from business for upgrading from 11i to R12. I know there are lot of benefits but what are the risks for not upgrading besides losing Oracle support? We have the following modules on 11i for use - GL, AP, PO, INV, [More]
This morning when I got to the computer, the screen was weird. Weird in the way it looks like some early 90's home computer and photos don't look like photos at all. Have shut the imac down for a couple of times and the problem persists. Help. Please [More]
i upgrade to new version but bck up file cant find..how to restore to my ipad?Restore from iTune Backup 1. Settings>General>Reset>Erase all content and settings 2. You'll be asked twice to confirm 3. You'll see Apple logo and progress bar 4. You' [More]