define S_DB=xxx
define T_DB=yyy
set pagesize 10000
set linesize 680 trimspool on
set long 300000
set longchunk 300000
set linesize 680 trimspool on feedback off verify off head off newpage 0 pagesize 0
select 'WHENEVER SQLERROR EXIT SQL.SQLCODE' from dual
union all
select '-- alter database backup controlfile to trace resetlogs;' from dual
union all
select 'STARTUP NOMOUNT' from dual
union all
select 'CREATE CONTROLFILE REUSE SET DATABASE "'|| upper('&&T_DB')
|| '" RESETLOGS FORCE LOGGING NOARCHIVELOG' from dual
union all
select '-- MAXLOGFILES 24' from dual
union all
select '-- MAXLOGMEMBERS 2' from dual
union all
select '-- MAXDATAFILES 1024' from dual
union all
select '-- MAXINSTANCES 1' from dual
union all
select '-- MAXLOGHISTORY 11393' from dual
union all
select 'MAXLOGFILES ' || records_total from v$controlfile_record_section where type = 'REDO LOG'
union all
select 'MAXLOGMEMBERS ' || dimlm from x$kccdi
union all
select 'MAXDATAFILES ' || records_total from v$controlfile_record_section where type = 'DATAFILE'
union all
select 'MAXINSTANCES ' || records_total from v$controlfile_record_section where type = 'DATABASE'
union all
select 'MAXLOGHISTORY ' || records_total from v$controlfile_record_section where type = 'LOG HISTORY'
union all
select 'LOGFILE' from dual
union all
select ' GROUP 1 (' from dual
union all
select ' '''|| replace(member,'&&S_DB','&&T_DB') || '''' || decode(rownum,1, ',', '') member
from v$logfile where group#=1
union all
select ' ) ' from dual
union all
select '-- SIZE 200M BLOCKSIZE 512,' from dual
union all
select 'SIZE ' || to_char(bytes/(1024*1024)) || 'M BLOCKSIZE ' || blocksize ||',' from v$log where rownum< 2
union all
select ' GROUP 2 (' from dual
union all
select ' '''|| replace(member,'&&S_DB','&&T_DB') || '''' || decode(rownum,1, ',', '') member
from v$logfile where group#=2
union all
select ' ) ' from dual
union all
select '-- SIZE 200M BLOCKSIZE 512,' from dual
union all
select 'SIZE ' || to_char(bytes/(1024*1024)) || 'M BLOCKSIZE ' || blocksize ||',' from v$log where rownum< 2
union all
select ' GROUP 3 (' from dual
union all
select ' '''|| replace(member,'&&S_DB','&&T_DB') || '''' || decode(rownum,1, ',', '') member
from v$logfile where group#=3
union all
select ' ) ' from dual
union all
select '-- SIZE 200M BLOCKSIZE 512,' from dual
union all
select 'SIZE ' || to_char(bytes/(1024*1024)) || 'M BLOCKSIZE ' || blocksize ||',' from v$log where rownum< 2
union all
select ' GROUP 4 (' from dual
union all
select ' '''|| replace(member,'&&S_DB','&&T_DB') || '''' || decode(rownum,1, ',', '') member
from v$logfile where group#=4
union all
select ' ) ' from dual
union all
select '-- SIZE 200M BLOCKSIZE 512' from dual
union all
select 'SIZE ' || to_char(bytes/(1024*1024)) || 'M BLOCKSIZE ' || blocksize ||' ' from v$log where rownum< 2
union all
select '-- STANDBY LOGFILE' from dual
union all
select 'DATAFILE' cmd from dual
union all
select substr(cmd,1,length(cmd)-1) from (
select cmd from (select ' '''||replace(name,'&&S_DB','&&T_DB')||'''' ||
decode(file#, maxf.max_data_file_num,'',',')|| chr(10) cmd
from v$datafile, (
select max(file#) max_data_file_num from v$datafile ) maxf
where name is not null order by file#)
)
union all
select 'CHARACTER SET ' || value db_nls_charset
from V$NLS_PARAMETERS where parameter = 'NLS_CHARACTERSET'
union all
select ';' from dual
union all
select 'ALTER DATABASE OPEN RESETLOGS;' from dual
union all
select * from (
select 'ALTER TABLESPACE ' || t.name || ' ADD TEMPFILE ''' ||
replace(f.name,'&&S_DB','&&T_DB')|| '''' ||
' SIZE '|| round(bytes/(1024*1024),0) || 'M ' ||
' REUSE AUTOEXTEND ON NEXT 20971520' ||
' MAXSIZE '|| round(bytes/(1024*1024),0) || 'M ' || ';' cmd
from v$tempfile f, v$tablespace t where f.ts#=t.ts# order by f.ts#, f.file# desc
)
;
|