I am in the process of putting my notes into web pages, please keep checking in later for more contents

Rename DB and Create Controlfile

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
)
;
rem add temp file with auto extend on or off

Rename Datafiles



RMAN CLONE



Home   Oracle