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
OK, not at all happy with the purchase of my 8110. I originaly purchased approx. 6 months ago. Phone consistantly shut off and would not turn back on until the battery was pulled and replaced. I "wiped" as suggested and reloaded the OS. Same iss [More]
I changed the format on an external hard drive and lost everything that was on it. Is there a way to recover what was on it?1) Partition your hard drive or add an external drive and install Snow Leopard (with the Optional Rosetta) into it and then yo [More]
Hi Gurus, I am opening this thread in continuation to my previous thread:- Date range in Xcelsius I could resolve this issue by referencing the cells in the next column in descending order for eg. I had data in ascending order  in M3 to M38 so  what [More]
Hello colleagues, I know that SAP provides special Translation Environment for translation of ABAP-objects (TX SE63 etc.). But what about translation of non-ABAP objects? Could be SE63 suitable for this task or it can be performed only via translatio [More]
How do I get my music from iTunes it's not showing in purchased like it usually doesYou can sync the songs to the iPhone using iTunes on your computer. This video will give you a better idea of the syncing process. If you want to download it directly [More]
Hello, I'm going to be purchasing an educational copy of Adobe CS5.5 Design Premium in a week or two, but I have a question about the licensing I would like clearing up first. It is my understanding that i'd be able to use the software on two compute [More]
I have a crosstab discoverer report with the following structure: ==================================================== .............|Y | Y | Y | X1 X2 X3 |Z | Z | Z | X1 X2 X3 |Z | Z | Z | X1 X2 X3 |Z | Z | Z | X1 X2 X3 |Z | Z | Z | Y = verticle axis [More]
After importing clips from my GoPro to iMovie 10.0.2, the clips along with others in the same events are not available. The preview screen and thumbnail both say, "Missing File." Many of the clips that are now "missing" were uploaded a [More]
I've attempted to install itunes many times without success. Each time I get a message "cabinet file Quicktime.cab is corrupt and cannot be used. Error code 2350". The installation process then stops and reverses all changes. I've tried troubles [More]
Hello, We are Unable to find the batch input session in the SM35 which is executed successfully. User executed T.code S_ALR_87012357 Which automatically triger for Batch input session in SM35, User successfully executed the Batch input session in SM3 [More]