Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Script tp parse control file backup ??

RE: Script tp parse control file backup ??

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Thu, 1 Jul 2004 10:54:42 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5C95@exchsen0a1ma> 





Not bad Mitchell!  A few minor things missing when I ran it for my 9i
instance, and compared the output to the actual trace file (like Rman
configuration stuff).  But a workable piece of code.



Thanks for sharing!



Tom Mercadante


Oracle Certified Professional




-----Original Message-----



From: Mitchell Baldwin [mailto:Mitchell.Baldwin_at_cogent-dsn.com] 
Sent: Thursday, July 01, 2004 10:31 AM


To: oracle-l_at_freelists.org


Subject: RE: Script tp parse control file backup ??




I thought I ought to post the 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;








-----Original Message-----



From: DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM]
Sent: 01 July 2004 15:12


To: 'oracle-l_at_freelists.org'


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_at_lifetouch.com=20


I said it "looked" clear - Riddick




-----Original Message-----



From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On
Behalf Of Prem Khanna J


Sent: Thursday, July 01, 2004 12:12 AM


To: oracle-l_at_freelists.org


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_at_freelists.org put
'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------



Received on Thu Jul 01 2004 - 09:52:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US