Manually Create a Controlfile via SQL

From NazimWIKI
Jump to navigation Jump to search

Something I no doubt cobbled together many years ago for reasons I cannot recall. Could be useful though!

declare
   
   
   cursor log_files is
   select v$log.group#
        , member
        , BYTES
     from v$log
        , v$logfile
    where v$log.group# = v$logfile.group#
   order   by v$log.group#;
   
   
  cursor datafiles is
  select name
    from v$datafile;

  controlparam_str  varchar2(40);
  prev_group number := 0;
  prev_filesize number;
  not_first_datafile boolean := FALSE;


begin
   dbms_output.put_line ('CREATE CONTROLFILE REUSE set DATABASE "${TARGET_SID}" RESETLOGS FORCE LOGGING NOARCHIVELOG');

   select 'MAXLOGFILES '||RECORDS_TOTAL into controlparam_str
     from v$controlfile_record_section
    where type = 'REDO LOG';

    dbms_output.put_line (controlparam_str);
    dbms_output.put_line ('MAXLOGMEMBERS 3');

   select 'MAXDATAFILES '||RECORDS_TOTAL into controlparam_str
     from v$controlfile_record_section
    where type = 'DATAFILE';

   dbms_output.put_line (controlparam_str);

   select 'MAXINSTANCES '||RECORDS_TOTAL into controlparam_str
     from v$controlfile_record_section
    where type = 'REDO THREAD';
 
   dbms_output.put_line (controlparam_str);
 
   select 'MAXLOGHISTORY '||RECORDS_TOTAL into controlparam_str
     from v$controlfile_record_section
    where type = 'LOG HISTORY';
 
   dbms_output.put_line (controlparam_str);
   dbms_output.put_line ('LOGFILE');
 
 
   FOR log_file in log_files
   LOOP
     IF prev_group <> log_file.group# THEN
   IF prev_group <> 0 THEN
           dbms_output.put_line (') SIZE '||log_file.BYTES/1024/1024||'M,');
           dbms_output.put_line ('GROUP '||log_file.group#||' (');
   ELSE
           dbms_output.put_line ('GROUP '||log_file.group#||' (');
   END IF;
     END IF;
     IF prev_group = log_file.group# THEN
   IF prev_group <> 0 THEN
           dbms_output.put_line (',');
   END IF;
     END IF;
     dbms_output.put_line (''''||replace(log_file.member,'${SOURCE_SID}','${TARGET_SID}')||'''');
     prev_group := log_file.group#;
     prev_filesize := log_file.bytes;
   END LOOP;
   dbms_output.put_line (') SIZE '||prev_filesize/1024/1024||'M');
   dbms_output.put_line ('DATAFILE');
   FOR datafile in datafiles
   LOOP
     IF not_first_datafile THEN
   dbms_output.put_line (',');
     END IF;
     dbms_output.put_line (''''||replace(datafile.name,'${SOURCE_SID}','${TARGET_SID}')||'''');
     not_first_datafile := TRUE;
   END LOOP;
   select value  into controlparam_str
   from v$NLS_PARAMETERS
   where PARAMETER = 'NLS_CHARACTERSET';
   dbms_output.put_line ('CHARACTER SET '||controlparam_str);
   dbms_output.put_line (';');
   dbms_output.put_line ('alter database open resetlogs;');
   dbms_output.put_line ('alter database rename global_name to ${TARGET_SID}.`hostname`;');
   dbms_output.put_line ('');
   dbms_output.put_line ('');
end;
/