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
Hello friends, I am trying <b>SOAP->XI->RFC</b> scenario. would like to explain in brief about requirement then <b>let me know if this is correct approach</b> <i>Our client wants a pull approach from R/3 , for example l [More]
Hi All, I have a vendor for which there are 20 open items, out of which there are credit memo's as well as vendor invoices. Vendor is a Newzealand vendor. Invoices details are given below: 1) some of the credit memo's have Amount in NZD and the corre [More]
i am creating 2 files with file adaptors.. lest say a1.txt and a2.txt.. in the file adaptor i am specifying the path of a batch file which is located at the same location where these 2 files are created......this batch file should run after message p [More]
Hi Folks, Can anyone please explain why this happens? In FW when I click the bold text button, the text appears bold but italic aswell. Thanks in advanceThat's unusual. Which version of Fireworks and what operating system are you using? If you click [More]
Hi, Is there a Flex/Air Mail Client sample demo application (with source code) like the the GWT one  -- http://gwt.google.com/samples/Mail/Mail.html -- Thank you for your helpNo.Read other 2 answers [More]
hi all,     help me regarding the differece between 4.7 and ecc 6( only FI/CO) and if possible any documentation on this. pls send me thanks sandeep.chHi Sandeep, I'm listing out a few differences. 1. Audit Information Support-Offers new roles that s [More]
So I ordered an Iphone 6 on back-order with a ship date October 17. My question is while there is a guarantee ship date, is there a real possibility that it could ship before that date?    Congrats on your new phone order Dedicated121. Your confirmat [More]
hello, .I need FI- process flow charts. like exp: ar,ap,bank.assetaccounting.  like pls help anyone. rajeshi think u r in blueprint preparation,... this list may usefull to u .... if you need any details please communicate me... 1     General Ledger [More]
It works when logging in using citrix viewer, but once you are login to your office and wants to quit, it is almost impossible to click "Disconnect"..Please fix this bugIt works when logging in using citrix viewer, but once you are login to your [More]
Hello Experts, I have an issue regarding process order. If i add an operation while process order creation then will it get updated in recipe master table PLPO. Or suggest me an table in which i can find this updated values of operations. Thank you. [More]