修改dbname以及dbid通常在使用RMAN还原到异机之后需要更改dbname以及dbid的情形。对于这个修改我们可以借助于命令行下的 nid工具来完成。同时也可以直接调用API来实现。本文即是通过dbms_backup_restore方式来修改dbname及dbid,供大家参考。
有关使用nid方式修改dbname及dbid,请参考:使用nid命令修改 db name 及 dbid
1、修改dbid及dbname的步骤
2、实战演习
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
robin@SZDB:~/dba_scripts/custom/sql> export ORACLE_SID=ES0481
robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 29 20:18:28 2014
Copyright (c) 1982, 2006, Oracle.All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
sys@ES0481> shutdown immediate;
sys@ES0481> startup open read only;
sys@ES0481> select name,dbid from v$database;
NAMEDBID
--------- ----------
ES0481123456
sys@ES0481> @chg_dbname_dbid
PL/SQL procedure successfully completed.
OLD_NAME
------------------------------------------------------
ES0481
Enter the new Database Name:ES0480
Enter the new Database ID:654321
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Convert ES0481(123456) to ES0480(654321)
PL/SQL procedure successfully completed.
ControlFile:
=> Change Name:1
=> Change DBID:1
DataFile: /u02/database/ES0481/oradata/sysES0481.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/database/ES0481/undo/undotbsES0481.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
.................
DataFile: /u02/database/ES0481/temp/ES0481_tempES0481.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
PL/SQL procedure successfully completed.
sys@ES0481> create pfile from spfile;
File created.
sys@ES0481> ho cat $ORACLE_HOME/dbs/initES0481.ora |sed "s/db_name='ES0481'/db_name='ES0480'/">$ORACLE_HOME/dbs/initES0480.ora
sys@ES0481> shutdown immediate;
sys@ES0481> exit
Disconnected from Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
robin@SZDB:~/dba_scripts/custom/sql> export ORACLE_SID=ES0480
robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdba
idle> startup pfile=/users/oracle/OraHome10g/dbs/initES0480.ora mount;
ORACLE instance started.
Total System Global Area599785472 bytes
Fixed Size2074568 bytes
Variable Size167774264 bytes
Database Buffers423624704 bytes
Redo Buffers6311936 bytes
Database mounted.
idle> alter database open resetlogs;
Database altered.
-- Author : Leshami
-- Blog: http://blog.csdn.net/leshami
idle> create spfile from pfile='/users/oracle/OraHome10g/dbs/initES0480.ora';
File created.
idle> startup force;
idle> select name,dbid from v$database;
NAMEDBID
--------- ----------
ES0480654321
3、脚本chg_dbname_dbid.sql
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
--该脚本从网上整理而来
--该脚本可以修改dbname,以及dbid,或者两者同时修改
--该脚本在10g下测试ok,11g下有待测试
robin@SZDB:~/dba_scripts/custom/sql> more chg_dbname_dbid.sql
var old_name varchar2(20)
var old_dbid number
var new_name varchar2(20)
var new_dbid number
exec select name, dbid -
into :old_name,:old_dbid -
from v$database
print old_name
accept new_name prompt "Enter the new Database Name:"
accept new_dbid prompt "Enter the new Database ID:"
exec :new_name:='&&new_name'
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_chgdbidbinary_integer;
v_chgdbname binary_integer;
v_skippedbinary_integer;
begin
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;
end;
/
更多参考
有关Oracle RAC请参考
有关Oracle 网络配置相关基础以及概念性的问题请参考:
有关基于用户管理的备份和备份恢复的概念请参考
有关RMAN的备份恢复与管理请参考
有关ORACLE体系结构请参考