Manually Create a Controlfile via SQL
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; /