change_dbid.sql
change_dbid.sql
—
SQL code,
2 KB (2732 bytes)
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
Artikelaktionen