* jijo.sql
|| mauro rossolato, 2006
||
|| >>> FOR DB DUPLICATION <<<
|| you can use RMAN (copy) or operating system copy command (dd or ocopy)
|| In addition creates a controlfile creation script.
||
||
|| Major Modifications (when, who what)
|| ----------------------------------------------------------------
|| 29.11.2006 mauro creates
*/
/*
PREREQUISITES
create a directory c:/logs
Oracle 8/9
/*
The source datafiles are renamed using the tablespace name and sequence (smartname).
The source datafile may be raws.
scenario 1
copy ALFA to BRAVO using RMAN
1) log as sysdba to ALFA
2) modify jijo.sql for s_modeout as 'RMANCOPY'
3) run jijo.sql
4) modify jijo.sql for s_modeout as 'CTLFILE'
5) run jijo.sql
6) exit from sqlplus
7) run RMAN script (created from step #3). This task 'll copy datafiles to new destination
8) logs to BRAVO node
9) sqlplus
10) startup nomount
11) run script created from (created from step #5)
12) recover database using backup controlfile until cancel/time/change;
13) alter database open RESETLOGS;
scenario 2
copy ALFA to BRAVO using 'dd' (or 'OCOPY' if Windosws)
1) log as sysdba to ALFA
2) modify jijo.sql for s_modeout as 'COPYNET'
3) run jijo.sql
4) modify jijo.sql for s_modeout as 'CTLFILE'
5) run jijo.sql
6) exit from sqlplus
7) run COPYNET script (created from step #3). This task 'll copy datafiles to new destination
8) logs to BRAVO node
9) sqlplus
11) startup nomount
12) run script created from (created from step #5)
13) recover database using backup controlfile until cancel/time/change;
14) alter database open RESETLOGS;;
BEFORE TO START
Check and modify 3 PL/SQL variables:
1) s_modeout
2) s_platform <------------------------------------------+
3) s_dskgrpdst |
|
where |
(1) maybe : |
'CTLFILE' means controlfile dump text-mode |
'COPYNET' use 'dd' or 'ocopy' (depending on platform, s_platform variable)
'RMANCOPY' use rman and controlfile to copy a datafile
-- ///
(2) maybe:
'WINDOWS' convert pathname to backslash
'UNIX' convert pathname to backslash
-- ///
(3) is your path:
e.g. '/mynfs/MIODB2/' or 'C:/ORADATA/MIODB/'
*/
SET LINES 200
SET SERVEROUTPUT ON SIZE 1000000
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SET TIMING OFF
col mysysdate new_value ladata
col myinstance new_value laistanza
SELECT INSTANCE_NAME myinstance FROM v$instance;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD__HH24_MI_SS') mysysdate FROM dual;
SPOOL c:/LOGS/jijo-&&laistanza-&&ladata..sql
-- test only:
-- SPOOL c:/LOGS/jijo.txt
DECLARE
s_tblspc_corrente VARCHAR2(255);
s_charset VARCHAR2(255);
i_blocco NUMBER;
s_when VARCHAR2(22);
s_dskgrpdst VARCHAR2(40);
s_modeout VARCHAR2(10);
s_dbname VARCHAR2(10);
s_platform VARCHAR2(16);
s_remark VARCHAR2(10);
s_extscript VARCHAR2(10);
s_slash1 VARCHAR2(5);
s_slash2 VARCHAR2(5);
s_datafile VARCHAR2(500);
i_numtmp NUMBER;
CURSOR c_redolog
IS
SELECT F.MEMBER, F.GROUP#, L.BYTES FROM GV$LOGFILE F, GV$LOG L WHERE F.GROUP# = L.GROUP#;
c_redolog_rec c_redolog%ROWTYPE;
CURSOR c_tblspc
IS
SELECT tablespace_name FROM DBA_TABLESPACES ORDER BY 1;
c_tblspc_rec c_tblspc%ROWTYPE;
CURSOR c_datafile (s_tbsname IN VARCHAR2 )
IS
SELECT F.file_name, F.TABLESPACE_NAME || '_' || LPAD(ROW_NUMBER()
OVER (PARTITION BY F.TABLESPACE_NAME ORDER BY F.TABLESPACE_NAME), 3, '0') || '.dbf' SMARTNAME,
F.BLOCKS, D.BLOCK1_OFFSET, D.BYTES
FROM DBA_DATA_FILES f,
v$datafile d
WHERE TABLESPACE_NAME = s_tbsname
AND F.FILE_ID = D.FILE#;
c_datafile_rec c_datafile%ROWTYPE;
CURSOR c_temptbs
IS
SELECT
T.NAME
FROM
v$tablespace t
WHERE
T.TS# IN (SELECT TS# FROM V$TEMPFILE);
c_temptbs_rec c_temptbs%ROWTYPE;
CURSOR c_tempfile (s_tbsname IN VARCHAR2 )
IS
SELECT F.name, F.BYTES, T.NAME || '_' || LPAD(ROW_NUMBER()
OVER (PARTITION BY T.NAME ORDER BY T.NAME), 3, '0') || '.dbf' SMARTNAME
-- ,
-- F.BLOCKS,D.BLOCK1_OFFSET,D.BYTES
FROM v$tempfile f, v$tablespace t
WHERE
t.name = s_tbsname;
-- F.TS#=T.TS#
c_tempfile_rec c_tempfile%ROWTYPE;
BEGIN
-- ///
-- /// CONFIGURATION SECTION (START HERE)/
-- ///
-- s_modeout := 'CTLFILE';
-- s_modeout := 'COPYNET';
s_modeout := 'RMANCOPY';
-- ///
-- s_platform := 'WINDOWS';
S_platform := 'UNIX';
-- ///
s_dskgrpdst := '/u03/miodb/refresh/';
-- ///
-- /// CONFIGURATION SECTION (ENDS HERE)//
-- ///
IF s_modeout = 'RMANCOPY'
THEN
s_extscript := 'rmn';
s_remark :='# ';
ELSE
s_extscript := 'sql';
s_remark :='-- ';
END IF;
IF s_platform = 'WINDOWS'
THEN
s_slash1 := '/';
s_slash2 := '/';
ELSE
s_slash1 := '/';
s_slash2 := '/';
END IF;
DBMS_OUTPUT.PUT_LINE(s_remark ||' jijo-&&laistanza-&&ladata..'|| s_extscript);
DBMS_OUTPUT.PUT_LINE(s_remark ||' mauro rossolato, 2005');
DBMS_OUTPUT.PUT_LINE(s_remark ||' generated automatically');
DBMS_OUTPUT.PUT_LINE(s_remark);
-- ****************************************************************************
SELECT VALUE INTO i_blocco FROM v$parameter WHERE name = 'db_block_size';
-- i_blocco := (( i_blocco / 1024) * 64) * 1024;
-- ****************************************************************************
-- s_tblspc_corrente 'zzzz' used for fence start
s_tblspc_corrente := 'zzzz';
-- ****************************************************************************
SELECT NAME INTO s_dbname FROM V$DATABASE;
IF NOT c_tblspc%ISOPEN
THEN
OPEN c_tblspc;
IF s_modeout = 'COPYNET'
THEN
DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SWITCH LOGFILE;');
DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SWITCH LOGFILE;');
DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM SWITCH LOGFILE;');
END IF;
IF s_modeout = 'CTLFILE'
THEN
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('CREATE CONTROLFILE SET DATABASE '|| s_dbname || ' RESETLOGS NOARCHIVELOG');
DBMS_OUTPUT.PUT_LINE('MAXLOGFILES 32');
DBMS_OUTPUT.PUT_LINE('MAXLOGMEMBERS 4');
DBMS_OUTPUT.PUT_LINE('MAXDATAFILES 1000');
DBMS_OUTPUT.PUT_LINE('MAXINSTANCES 1');
DBMS_OUTPUT.PUT_LINE('MAXLOGHISTORY 453');
DBMS_OUTPUT.PUT_LINE('LOGFILE ');
IF NOT c_redolog%ISOPEN
THEN
DBMS_OUTPUT.PUT_LINE(s_remark ||' change redolog path and name!!!');
OPEN c_redolog;
LOOP
FETCH c_redolog INTO c_redolog_rec;
EXIT WHEN c_redolog%NOTFOUND;
IF LENGTH(s_datafile) > 0
THEN
DBMS_OUTPUT.PUT_LINE(s_datafile || ',');
END IF;
s_datafile := 'GROUP '|| TO_CHAR(c_redolog_rec.group#) || ' '||
'''' || c_redolog_rec.MEMBER || '''' ||' SIZE '||
TO_CHAR(TRUNC(c_redolog_rec.BYTES / 1024)) || 'K';
END LOOP;
CLOSE c_redolog;
DBMS_OUTPUT.PUT_LINE(s_datafile);
END IF;
END IF;
IF s_modeout = 'RMANCOPY'
THEN
DBMS_OUTPUT.PUT_LINE(s_remark || 'usage (sample):');
DBMS_OUTPUT.PUT_LINE(s_remark || ' nohup rman TARGET system/manager @rmanmau.rmn > rmanmau.out 2>'
|| CHR(38) ||'1 '|| CHR(38));
DBMS_OUTPUT.PUT_LINE(s_remark);
DBMS_OUTPUT.PUT_LINE('RUN {');
DBMS_OUTPUT.PUT_LINE('ALLOCATE CHANNEL D1 DEVICE TYPE DISK; ');
END IF;
IF s_modeout = 'CTLFILE'
THEN
DBMS_OUTPUT.PUT_LINE('DATAFILE');
END IF;
s_datafile := '';
LOOP
FETCH c_tblspc INTO c_tblspc_rec;
EXIT WHEN c_tblspc%NOTFOUND;
IF s_tblspc_corrente <> c_tblspc_rec.tablespace_name
THEN
IF s_tblspc_corrente = 'zzzz'
THEN
IF s_modeout = 'COPYNET'
THEN
DBMS_OUTPUT.PUT_LINE('alter tablespace '||
c_tblspc_rec.tablespace_name || ' BEGIN backup;');
END IF;
ELSE
IF s_modeout = 'COPYNET'
THEN
DBMS_OUTPUT.PUT_LINE('alter tablespace '||
s_tblspc_corrente || ' END backup;');
DBMS_OUTPUT.PUT_LINE('exec DBMS_LOCK.SLEEP (7);');
DBMS_OUTPUT.PUT_LINE('alter tablespace '||
c_tblspc_rec.tablespace_name || ' begin backup;');
END IF;
END IF;
s_tblspc_corrente := c_tblspc_rec.tablespace_name;
END IF;
OPEN c_datafile (c_tblspc_rec.tablespace_name );
LOOP
FETCH c_datafile INTO c_datafile_rec;
EXIT WHEN c_datafile%NOTFOUND;
-------------------------------------------------------------------------------------------------------
IF s_modeout = 'COPYNET'
THEN
IF s_platform = 'UNIX'
THEN
DBMS_OUTPUT.PUT_LINE('!dd if='|| c_datafile_rec.file_name || ' of=' ||
REPLACE(s_dskgrpdst, s_slash1, s_slash2) || c_datafile_rec.smartname || ' bs=' ||
TO_CHAR(i_blocco ) || ' count='||
TO_CHAR(c_datafile_rec.BYTES + c_datafile_rec.BLOCK1_OFFSET) / I_BLOCCO);
-- dbms_output.put_line('cp ' || c_datafile_rec.file_namefile_name || ' s_dskgrpdst||c_datafile_rec.smartname);
-------------------------------------------------------------------------------------------------------
ELSE
DBMS_OUTPUT.PUT_LINE('!OCOPY '|| c_datafile_rec.file_name || ' ' ||
REPLACE(s_dskgrpdst, s_slash1, s_slash2) || c_datafile_rec.smartname);
END IF;
END IF;
IF s_modeout = 'RMANCOPY'
THEN
IF LENGTH(s_datafile) > 0
THEN
DBMS_OUTPUT.PUT_LINE(s_datafile || ',');
ELSE
DBMS_OUTPUT.PUT_LINE('-- ');
END IF;
s_datafile :='COPY DATAFILE '|| '''' || c_datafile_rec.file_name || '''' || ' TO ' || '''' || REPLACE(s_dskgrpdst, s_slash1, s_slash2) ;
s_datafile :=s_datafile||c_datafile_rec.smartname ||'''';
END IF;
IF s_modeout = 'CTLFILE'
THEN
IF LENGTH(s_datafile) > 0
THEN
DBMS_OUTPUT.PUT_LINE(s_datafile || ',');
END IF;
s_datafile := '''' || REPLACE(s_dskgrpdst, s_slash1, s_slash2) ||
c_datafile_rec.smartname || '''';
END IF;
END LOOP;
CLOSE c_datafile;
END LOOP;
DBMS_OUTPUT.PUT_LINE(s_datafile);
CLOSE c_tblspc;
END IF;
IF s_modeout = 'RMANCOPY'
THEN
DBMS_OUTPUT.PUT_LINE(' }');
END IF;
IF s_modeout = 'COPYNET'
THEN
DBMS_OUTPUT.PUT_LINE('alter tablespace '||
s_tblspc_corrente || ' END backup;');
END IF;
IF s_modeout = 'CTLFILE'
THEN
SELECT VALUE INTO S_CHARSET FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
DBMS_OUTPUT.PUT_LINE('CHARACTER SET '|| s_charset || ';');
END IF;
IF s_modeout = 'CTLFILE' OR s_modeout = 'COPYNET'
THEN
IF NOT c_temptbs%ISOPEN
THEN
OPEN c_temptbs;
LOOP
i_numtmp := 0;
FETCH c_temptbs INTO c_temptbs_rec;
EXIT WHEN c_temptbs%NOTFOUND;
IF NOT c_tempfile%ISOPEN
THEN
OPEN c_tempfile (c_temptbs_rec.name);
LOOP
i_numtmp := i_numtmp + 1;
FETCH c_tempfile INTO c_tempfile_rec;
EXIT WHEN c_tempfile%NOTFOUND;
IF i_numtmp = 1
THEN
DBMS_OUTPUT.PUT_LINE(
'-- CREATE TEMPORARY TABLESPACE '||
c_temptbs_rec.name || ' TEMPFILE '||
'''' || s_dskgrpdst || c_tempfile_rec.SMARTNAME || '''' ||
' SIZE ' || TO_CHAR(c_tempfile_rec.BYTES) || ' REUSE AUTOEXTEND OFF;');
ELSIF i_numtmp > 1
THEN
DBMS_OUTPUT.PUT_LINE(
'-- ALTER TABLESPACE '||
c_temptbs_rec.name || ' ADD TEMPFILE '||
'''' || s_dskgrpdst || c_tempfile_rec.SMARTNAME || '''' ||
' SIZE ' || TO_CHAR(c_tempfile_rec.BYTES) || ' REUSE AUTOEXTEND OFF;');
END IF;
END LOOP;
CLOSE c_tempfile;
END IF;
END LOOP;
CLOSE c_temptbs;
END IF;
IF s_modeout = 'COPYNET'
THEN
DBMS_OUTPUT.PUT_LINE('-- ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');
END IF;
DBMS_OUTPUT.PUT_LINE('SPOOL OFF');
END IF;
END;
/
SPOOL OFF
-- eof
