I thought I ought to post the code !.... ------- Start of Code spool controlfile_to_trace.txt set termout on set serveroutput on set feedback off execute sys.dbms_output.enable(30000); declare V_MAXLOGFILES v$controlfile_record_section.records_total%TYPE; V_MAXLOGMEMBERS v$controlfile_record_section.records_total%TYPE; V_MAXDATAFILES v$controlfile_record_section.records_total%TYPE; V_MAXINSTANCES v$controlfile_record_section.records_total%TYPE; V_MAXLOGHISTORY v$controlfile_record_section.records_total%TYPE; V_CHARSET v$nls_parameters.value%TYPE;=20 V_DATABASE v$database.name%TYPE; V_COUNTER number; V_COUNTER1 number; V_ISCOM varchar2(1000); V_ISCOM1 varchar2(1000); V_STRING varchar2(1000); cursor C1 is=20 select group#, bytes/1024/1024 MBYTES=20 from v$log order by 1 asc; cursor C2 (V_GROUP v$log.group#%TYPE) is select member from v$logfile where group# =3D V_GROUP; cursor C3 is select file_id, file_name from dba_data_files order by 1 asc; cursor C4 is select file_id, file_name, tablespace_name, decode(AUTOEXTENSIBLE,'NO','OFF','YES','ON',AUTOEXTENSIBLE) AE, bytes/1024/1024 MBYTES from dba_temp_files; BEGIN select name into V_DATABASE from v$database; select value into V_CHARSET from v$nls_parameters where parameter =3D'NLS_CHARACTERSET'; select records_total into V_MAXLOGFILES from v$controlfile_record_section where TYPE =3D'REDO LOG'; SELECT DIMLM into V_MAXLOGMEMBERS from X$KCCDI; select records_total into V_MAXDATAFILES from v$controlfile_record_section where TYPE =3D'DATAFILE'; select records_total into V_MAXINSTANCES from v$controlfile_record_section where TYPE =3D'REDO THREAD'; select records_total into V_MAXLOGHISTORY from v$controlfile_record_section where TYPE =3D'LOG HISTORY'; dbms_output.put_line('STARTUP NOMOUNT'); dbms_output.put_line('CREATE CONTROLFILE REUSE'); dbms_output.put_line('SET DATABASE "'||V_DATABASE||'" RESETLOGS = NOARCHIVELOG'); dbms_output.put_line('MAXLOGFILES '||V_MAXLOGFILES); dbms_output.put_line('MAXLOGMEMBERS '||V_MAXLOGMEMBERS); dbms_output.put_line('MAXDATAFILES '||V_MAXDATAFILES); dbms_output.put_line('MAXINSTANCES '||V_MAXINSTANCES); dbms_output.put_line('MAXLOGHISTORY '||V_MAXLOGHISTORY); dbms_output.put_line('LOGFILE'); select count(*) into V_COUNTER from v$log; for LG in C1 loop dbms_output.put_line('GROUP '||LG.GROUP#||' ('); if V_COUNTER =3D 1 then=20 V_ISCOM :=3D null; else V_ISCOM :=3D ','; end if; select count(*) into V_COUNTER1 from v$logfile where group# =3D LG.GROUP#; for LF in C2 (LG.GROUP#) loop if V_COUNTER1 =3D 1 then V_ISCOM1 :=3D ''''; else V_ISCOM1 :=3D ''','; end if; V_STRING :=3D ''''||LF.MEMBER||V_ISCOM1; dbms_output.put_line(V_STRING); V_COUNTER1 :=3D V_COUNTER1 - 1; end loop; V_STRING :=3D ') SIZE '||LG.MBYTES||'M'||V_ISCOM; dbms_output.put_line(V_STRING); V_COUNTER :=3D V_COUNTER - 1; end loop; dbms_output.put_line('DATAFILE'); select count(*) into V_COUNTER from dba_data_files; for F in C3 loop if V_COUNTER =3D 1 then V_ISCOM :=3D ''''; else V_ISCOM :=3D ''','; end if; V_STRING :=3D ''''||F.FILE_NAME||V_ISCOM; dbms_output.put_line(V_STRING); V_COUNTER :=3D V_COUNTER - 1; end loop; dbms_output.put_line(' CHARACTER SET '||V_CHARSET||';'); dbms_output.put_line(' ALTER DATABASE OPEN RESETLOGS;'); for TF in C4 loop dbms_output.put_line(' ALTER TABLESPACE '||TF.TABLESPACE_NAME||' ADD = TEMPFILE'); dbms_output.put_line(''''||TF.FILE_NAME||''''); dbms_output.put_line(' SIZE '||TF.MBYTES||'M REUSE AUTOEXTEND = '||TF.AE||';'); end loop; END; / spool off; --- END of Code -----Original Message----- From: DENNIS WILLIAMS [mailto:DWILLIAMS@xxxxxxxxxxxxx] Sent: 01 July 2004 15:12 To: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: Script tp parse control file backup ?? Prem I don't understand your request. To convert the trace version of the controlfile to a usable script is a simple matter of removing lines, = easily accomplished with an editor like vi in Unix or Wordpad in Winders. If = you need an automated method, that will depend on your platform and tools available. Also note that in 9i you can specify the name of the created trace file. Dennis Williams DBA Lifetouch, Inc. dwilliams@xxxxxxxxxxxxx=20 I said it "looked" clear - Riddick -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Prem Khanna J Sent: Thursday, July 01, 2004 12:12 AM To: oracle-l@xxxxxxxxxxxxx Subject: Script tp parse control file backup ?? Hi All, SQL > alter system backup controlfile to trace; Does anybody have a script to parse this control file backup ? that is, i need to create a neat script that contains just STARTUP NOMOUNT CREATE CONTROLFILE... ... ... CHARACTER SET JA16SJIS; Can someone share it with me ?? Thanks and Regards, Prem. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------