change_dbid.sql

Dateiinhalt

/***********************************************
*
* SQLPlus Script enthaelt 1 Parameter, die entweder
* durch Scriptparameter gesetzt werden koennen,
* oder bei einer manuellen Ausfuehrung ohne
* Angabe der Parameter mittels Eingabeprompt
* abgefragt werden.
*
************************************************/
var old_name varchar2(20)
var old_dbid number
var new_name varchar2(20)
var new_dbid number
var status varchar2(20)

exec select name,dbid,name,dbid into :old_name,:old_dbid,:new_name,:new_dbid from v$database
print old_dbid

SET SERVEROUTPUT ON
SET VERIFY OFF
PROMPT Please give the new DBID and press ENTER:
SET TERMOUT OFF
DEFINE new_dbid = &1 
SET TERMOUT ON

PROMPT
PROMPT Your selection commit
PROMPT *********************
PROMPT
PROMPT Parameter new_dbid: &new_dbid
PROMPT

exec :new_dbid:='&&new_dbid'

set serveroutput on
exec dbms_output.put_line('Convert '||:old_name||  -
	'('||to_char(:old_dbid)||') to '||:new_name|| -
	'('||to_char(:new_dbid)||')')

declare
	v_chgdbid   binary_integer;
	v_chgdbname binary_integer;
	v_skipped   binary_integer;
	v_status    VARCHAR2(50);
	e_status     exception;
	e_dbid     exception;

begin
	select status into v_status from v$instance;
	IF  :new_dbid  =  :old_dbid THEN
		raise e_dbid;
	END IF;

	IF  v_status  !=  'MOUNTED' THEN
		raise e_status;
	END IF;

	dbms_backup_restore.nidbegin(:new_name,:old_name,:new_dbid,:old_dbid,0,0,10);
	dbms_backup_restore.nidprocesscf(v_chgdbid,v_chgdbname);
	dbms_output.put_line('ControlFile: ');
	dbms_output.put_line('  => Change Name:' || to_char(v_chgdbname));
	dbms_output.put_line('  => Change DBID:' || to_char(v_chgdbid));
	for i in (select file#,name from v$datafile)
	loop
		dbms_backup_restore.nidprocessdf(i.file#,0, v_skipped,v_chgdbid,v_chgdbname);
		dbms_output.put_line('DataFile: '|| i.name);
		dbms_output.put_line('  => Skipped:' ||to_char(v_skipped));
		dbms_output.put_line('  => Change Name:' || to_char(v_chgdbname));
		dbms_output.put_line('  => Change DBID:' || to_char(v_chgdbid));
	end loop;
	
	for i in (select file#,name from v$tempfile)
	loop
		dbms_backup_restore.nidprocessdf(i.file#,1,
		v_skipped,v_chgdbid,v_chgdbname);
		dbms_output.put_line('DataFile: '||i.name);
		dbms_output.put_line('  => Skipped:' || to_char(v_skipped));
		dbms_output.put_line('  => Change Name:' || to_char(v_chgdbname));
		dbms_output.put_line('  => Change DBID:' || to_char(v_chgdbid));
	end loop;
	dbms_backup_restore.nidend;
EXCEPTION
	WHEN e_dbid THEN dbms_output.put_line('info: dbid old and new ist equal');
	WHEN e_status THEN dbms_output.put_line('error: database status is: '|| v_status|| ':  start Database in mount status');
	WHEN OTHERS THEN dbms_output.put_line('can not rename DBID: error');
end;
/
exit