Oracle rac使用nid和dbms_backup_restore包修改dbid和dbname
之前的连接： http://blog.itpub.net/26736162/viewspace-2140640/
----nid 启动到mount状态
nid target=sys/lhr #只修改dbid
nid target=sys/lhr dbname=lhrdb setname=yes #只修改dbname，修改pfile文件中的db_name参数
nid target=sys/lhr dbname=lhrxxt #同时修改dbname和dbid
select dbid,name,open_mode from gv$database;
DBID NAME OPEN_MODE
---------- --------- --------------------
2136828548 LHRRAC1 MOUNTED
RAC环境：
1、alter system set cluster_database=false scope=spfile sid='*';
2、启动单实例到mount
否则：NID-00120: Database should be mounted exclusively
srvctl remove db -d lhrrax1
srvctl add database -d lhrxxt -o $ORACLE_HOME -c RAC
srvctl add instance -d lhrxxt -i lhrrac11 -n raclhr-11gR2-N1
srvctl add instance -d lhrxxt -i lhrrac12 -n raclhr-11gR2-N2
--启动到READ ONLY模式，然后直接使用如下脚本进行修改，对于RAC环境不用修改参数文件内容
----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_chgdbid binary_integer;
v_chgdbname binary_integer;
v_skipped binary_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;
/
=======================修改数据库dbid======================
1、查看目前数据库dbid以及dbname
[root@rac01 ~]# su - oracle
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:23:24 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
SQL> select dbid from v$database;
DBID
----------
2、将群集参数修改为false，否则修改不成功
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
3、停止群集数据库，并在一个节点启动到mount
[oracle@rac01 ~]$ srvctl stop database -d orcl
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:34:34 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
4、修改数据库dbid
[oracle@rac01 ~]$ nid target=sys/oracle@orcl
DBNEWID: Release 11.2.0.4.0 - Production on Thu May 4 17:37:21 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1466421350)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.261.938640615
+DATA/orcl/controlfile/current.260.938640615
Change database ID of database ORCL? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1466421350 to 1470887377
Control File +DATA/orcl/controlfile/current.261.938640615 - modified
Control File +DATA/orcl/controlfile/current.260.938640615 - modified
Datafile +DATA/orcl/datafile/system.256.93864038 - dbid changed
Datafile +DATA/orcl/datafile/sysaux.257.93864038 - dbid changed
Datafile +DATA/orcl/datafile/undotbs1.258.93864038 - dbid changed
Datafile +DATA/orcl/datafile/users.259.93864038 - dbid changed
Datafile +DATA/orcl/datafile/example.267.93864064 - dbid changed
Datafile +DATA/orcl/datafile/undotbs2.268.93864124 - dbid changed
Datafile +DATA/orcl/tempfile/temp.266.93864063 - dbid changed
Control File +DATA/orcl/controlfile/current.261.938640615 - dbid changed
Control File +DATA/orcl/controlfile/current.260.938640615 - dbid changed
Instance shut down
Database ID for database ORCL changed to 1470887377.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
5、重建密码文件
[oracle@rac01 dbs]$ orapwd file='$ORACLE_HOME/dbs/orapworcl1' password=oracle entries=10
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:41:10 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
6、以resetlogs模式打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
ORCL READ WRITE 1470884038 14-MAR-17
7、将数据库集群参数修改为true，重启数据库
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac01 dbs]$ srvctl stop database -d orcl
[oracle@rac01 dbs]$ srvctl start database -d orcl
[oracle@rac01 dbs]$ su - grid
[grid@rac01 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.LISTENER.lsnr
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.VOTE.dg
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.asm
ONLINE ONLINE rac01 Started
ONLINE ONLINE rac02 Started
ora.gsd
OFFLINE OFFLINE rac01
OFFLINE OFFLINE rac02
ora.net1.network
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.ons
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.registry.acfs
ONLINE ONLINE rac01
ONLINE ONLINE rac02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
ONLINE ONLINE rac01
ora.cvu
ONLINE ONLINE rac02
ora.oc4j
ONLINE ONLINE rac02
ora.orcl.db
ONLINE ONLINE rac01 Open
ONLINE ONLINE rac02 Open
ora.rac01.vip
ONLINE ONLINE rac01
ora.rac02.vip
ONLINE ONLINE rac02
ora.scan1.vip
ONLINE ONLINE rac01
==========================修改数据库dbname=======================
1、查看数据库dbid及数据库dbname
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:49:36 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
2、修改数据库集群参数为false，并创建pfile文件
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> create pfile='/tmp/pfile.ora' from spfile;
File created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
3、关闭数据库并在一个节点启动到mount
[oracle@rac01 ~]$ srvctl stop database -d orcl
[oracle@rac01 ~]$ cd /tmp/
[oracle@rac01 tmp]$ ls -l pfil*
-rw-r--r-- 1 oracle oinstall 1374 May 4 17:51 pfile.ora
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:59:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
SQL> quit
4、修改数据库dbname为db11g
[oracle@rac01 dbs]$ nid TARGET=SYS/oracle@orcl DBNAME=db11g SETNAME=Y
DBNEWID: Release 11.2.0.4.0 - Production on Thu May 4 17:57:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1470887377)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.261.938640615
+DATA/orcl/controlfile/current.260.938640615
Change database name of database ORCL to DB11G? (Y/[N]) => y
Proceeding with operation
Changing database name from ORCL to DB11G
Control File +DATA/orcl/controlfile/current.261.938640615 - modified
Control File +DATA/orcl/controlfile/current.260.938640615 - modified
Datafile +DATA/orcl/datafile/system.256.93864038 - wrote new name
Datafile +DATA/orcl/datafile/sysaux.257.93864038 - wrote new name
Datafile +DATA/orcl/datafile/undotbs1.258.93864038 - wrote new name
Datafile +DATA/orcl/datafile/users.259.93864038 - wrote new name
Datafile +DATA/orcl/datafile/example.267.93864064 - wrote new name
Datafile +DATA/orcl/datafile/undotbs2.268.93864124 - wrote new name
Datafile +DATA/orcl/tempfile/temp.266.93864063 - wrote new name
Control File +DATA/orcl/controlfile/current.261.938640615 - wrote new name
Control File +DATA/orcl/controlfile/current.260.938640615 - wrote new name
Instance shut down
Database name changed to DB11G.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
5、尝试startup无法启动，因为在参数文件中记录的dbname仍然是orcl
[oracle@rac01 dbs]$ cat initorcl1.ora
SPFILE='+DATA/orcl/spfileorcl.ora'
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:59:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
ORA-01103: database name 'DB11G' in control file is not 'ORCL'
6、修改pfile中的dbname参数，修改为新的dbname
[oracle@rac01 dbs]$ vi /tmp/pfile.ora
change *.db_name='orcl'
to *.db_name='db11g'
复制代码
复制代码
7、使用pfile启动
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 18:03:20 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter database open;
Database altered.
8、创建spfile，需指定路径，因为spfile在ASM磁盘中，否则会在本地生成一个spfile
SQL> create SPFILE='+DATA/orcl/spfileorcl.ora' from pfile='/tmp/pfile.ora';
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
9、重启数据库
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string db11g
db_unique_name string db11g
global_names boolean FALSE
instance_name string orcl1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string db11g
10、修改群集参数为true
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
1470887377 DB11G READ WRITE 1470884038 14-MAR-17
11、重建密码文件
[oracle@rac01 dbs]$ orapwd file='$ORACLE_HOME/dbs/orapworcl1' password=oracle entries=10 force=y
[oracle@rac01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-MAY-2017 18:24:57
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 04-MAY-2017 17:17:18
Uptime 0 days 1 hr. 7 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "db11g" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
12、修改tnsname.ora文件的service_name为db11g
[oracle@rac01 dbs]$ cd ../network/admin/[oracle@rac01 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g)
)
)
13、尝试连接
[oracle@rac01 admin]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 18:25:53 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
连接成功。
14、将新的数据库名称加入群集进行管理，删除旧的数据库名称
[oracle@rac01 ~]$ srvctl add database -d db11g -o $ORACLE_HOME
[oracle@rac01 ~]$ exit
logout
[root@rac01 ~]# su - grid
[grid@rac01 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac01
ora....ER.lsnr ora....er.type ONLINE ONLINE rac01
ora....N1.lsnr ora....er.type ONLINE ONLINE rac01
ora.VOTE.dg ora....up.type ONLINE ONLINE rac01
ora.asm ora.asm.type ONLINE ONLINE rac01
ora.cvu ora.cvu.type ONLINE ONLINE rac02
ora.db11g.db ora....se.type OFFLINE OFFLINE
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac01
ora.oc4j ora.oc4j.type ONLINE ONLINE rac02
ora.ons ora.ons.type ONLINE ONLINE rac01
ora.orcl.db ora....se.type ONLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE rac01
ora....01.lsnr application ONLINE ONLINE rac01
ora.rac01.gsd application OFFLINE OFFLINE
ora.rac01.ons application ONLINE ONLINE rac01
ora.rac01.vip ora....t1.type ONLINE ONLINE rac01
ora....SM2.asm application ONLINE ONLINE rac02
ora....02.lsnr application ONLINE ONLINE rac02
ora.rac02.gsd application OFFLINE OFFLINE
ora.rac02.ons application ONLINE ONLINE rac02
ora.rac02.vip ora....t1.type ONLINE ONLINE rac02
ora....ry.acfs ora....fs.type ONLINE ONLINE rac01
ora.scan1.vip ora....ip.type ONLINE ONLINE rac01
[oracle@rac01 ~]$ srvctl add instance -d db11g -i orcl1 -n rac01
[oracle@rac01 ~]$ srvctl add instance -d db11g -i orcl2 -n rac02
[oracle@rac01 ~]$ srvctl remove database -d orcl
Remove the database orcl? (y/[n]) y
[oracle@rac01 ~]$ srvctl start database -d db11g
[grid@rac01 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac01
ora....ER.lsnr ora....er.type ONLINE ONLINE rac01
ora....N1.lsnr ora....er.type ONLINE ONLINE rac01
ora.VOTE.dg ora....up.type ONLINE ONLINE rac01
ora.asm ora.asm.type ONLINE ONLINE rac01
ora.cvu ora.cvu.type ONLINE ONLINE rac02
ora.db11g.db ora....se.type ONLINE ONLINE rac01
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac01
ora.oc4j ora.oc4j.type ONLINE ONLINE rac02
ora.ons ora.ons.type ONLINE ONLINE rac01
ora....SM1.asm application ONLINE ONLINE rac01
ora....01.lsnr application ONLINE ONLINE rac01
ora.rac01.gsd application OFFLINE OFFLINE
ora.rac01.ons application ONLINE ONLINE rac01
ora.rac01.vip ora....t1.type ONLINE ONLINE rac01
ora....SM2.asm application ONLINE ONLINE rac02
ora....02.lsnr application ONLINE ONLINE rac02
ora.rac02.gsd application OFFLINE OFFLINE
ora.rac02.ons application ONLINE ONLINE rac02
ora.rac02.vip ora....t1.type ONLINE ONLINE rac02
ora....ry.acfs ora....fs.type ONLINE ONLINE rac01
ora.scan1.vip ora....ip.type ONLINE ONLINE rac01
工作中不可避免地碰到需要修改 dbname 以及 dbid 的情形，如将数据库恢复到同一台机器的情形是其中之一。但 dbname 以及 dbid 是用于标识数据库的重要标志之一，尤其是 dbid ，具有唯一性，因此对其修改应慎重处理。
修改 dbname 以及 dbid 通常在使用 RMAN 还原到异机之后需要更改 dbname 以及 dbid 的情形 。对于这个修改我们可以借助于命令行下的 nid 工具来完成。同时也可以直接调用 API 来实现。
本文描述了修改 dbname 以及 dbid 的步骤并给出示例。
nid 工具执行的命令实际也是调用 DBMS_BACKUP_RESTORE 包相应的存储过程实现的。
1、 修改 dbid 及 dbname 的影响
a、 修改 dbid
等同于创建一个新的数据库，不同的是数据已经存在于数据文件。这是由 dbid 的唯一性决定的。
修改之后所有之前的备份与归档日志将不可用，因为在恢复时会检测 dbid ，由于不匹配，则所有备份无效。
修改之后需要使用 open resetlogs 打开数据库，一个新的 incarnation 会被创建，且 sequence 被置为 1 。
修改之后且成功 open 的情形下，建议一致性关闭数据库，重启并做一个完整的备份。
b、 修改 dbname
修改之后，无需使用 open resetlogs 打开数据库。 ( 注 dbname 不具有唯一性， global name 则具有唯一性 ) 。
修改之后，所有的备份即归档日志依旧可用。
修改之后，需要修改 pfile/spfile 中对应的 db_name 参数以及重建相应的 Oracle 密码文件 。
如果需要使用旧的控制文件恢复数据库，应当使用修改之前的 pfile/spfile 以及密码文件启动数据库再进行恢复。
c、 同时修改 dbid 及 dbname
如果同时修改，则是上述两种情形的综合，修改完毕后需要 open resetlogs 以及修改 pfile/spifle ，密码文件，全备数据库。
2、 nid 命令
robin@SZDB :~> nid
DBNEWID: Release 10.2.0.3.0 - Production on Thu Apr 24 16:34:28 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)
DBNAME New database name (NONE)
LOGFILE Output Log (NONE)
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays these messages NO
在执行 nid 命令时，该程序会校验当前数据库所有数据文件以及控制文件的头部信息，校验成功后提示是否要修改。
如果使用了输出到日志文件 logfile 则不会出现修改提示。
接下来 nid 将使用新的 dbid( 或者 dbname) 逐个修改控制文件，数据文件 ( 包括只读文件，正常脱机文件 ) 的头部。
修改成功后自动关闭数据库并退出。
注 : 对于只读文件，正常脱机文件本文未作测试。其次应确保数据库处于归档状态，可正常归档以及不存在需要 recover 的数据文件。
3、 修改步骤
a 、全备数据库，如果是热备 (rman or os) 应确保所有的 归档日志 以及 联机日志 可用
b 、删除 dbconsole([ID 863800.1] 有此要求，如用到 dbconsole ，应考虑按此操作 )
c 、启动需要修改的数据库到 mount 状态 (startup mount)
d 、如果使用 spfile 文件启动数据库，备份 spfile 文件到 pfile 用于后续修改 db_name
e 、发布 nid 命令
nid target=sys/password # 此方式是仅仅修改 dbid
nid target=/ dbname=new_dbname [setname=yes]# / 表明连接到当前环境的 sid ，且使用操作系统认证
nid target=sys/password dbname=new_dbname [setname=yes] # setname=yes 仅仅修改数据库名字，如果省略，则两者同时修改
nid target=sys/pwdd@conn_string dbname=new_dbname [setname=yes] # 使用连接串连接到远程主机并修改
f 、修改 Oracle 参数文件 pfile( 使用先前从 spfile 备份的 ) 中的参数 db_name ，如果仅仅修改 dbid ，跳过此步骤
g 、使用新的参数文件 pfile 启动到 mount 状态 ( 如果修改了 dbname ，如果仅仅是 dbid ，则直接使用原来的 pfile 或 spfile 启动 )
h 、使用 open resetlogs 方式打开数据库 ( 修改非 dbid ，直接打开即可 )
i 、重建当前数据库的 Oralce 密码文件 及将 pfile 文件转换成 spfile 文件
j 、修改相应的监听器的配值，包括 listener.ora 以及 tnsnames.ora
k 、修改全局 dbname ，如果有用到的话。 ALTER DATABASE RENAME GLOBAL_NAME TO <newname>.<domain>;
l 、重建 dbconsole $ emca -config dbcontrol db -repos recreate
m 、全备数据库
1.1.1 修改 dbid
mount 状态下：
[oracle@orcltest dbs]$ nid target=sys/lhr
DBNEWID: Release 11.2.0.3.0 - Production on Sat May 2 12:12:14 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORA11G (DBID=4274875142)
Connected to server version 11.2.0
Control Files in database:
/u02/app/oracle/oradata/orcltest/control01.ctl
/u02/app/oracle/flash_recovery_area/orcltest/control02.ctl
Change database ID of database ORA11G? (Y/[N]) => y
Proceeding with operation
Changing database ID from 4274875142 to 4274849246
Control File /u02/app/oracle/oradata/orcltest/control01.ctl - modified
Control File /u02/app/oracle/flash_recovery_area/orcltest/control02.ctl - modified
Datafile /u02/app/oracle/oradata/orcltest/system01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/sysaux01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/undotbs01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/users01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/example01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/temp01.db - dbid changed
Control File /u02/app/oracle/oradata/orcltest/control01.ctl - dbid changed
Control File /u02/app/oracle/flash_recovery_area/orcltest/control02.ctl - dbid changed
Instance shut down
Database ID for database ORA11G changed to 4274849246.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
1.1.2 修改 dbname
$ export ORACLE_SID=CLBO
$ sqlplus / as sysdba
--> 当前数据库的 dbid,name, 以及状态 . 下面的演示中使用了 pfile 文件来启动数据库 .
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2229744 bytes
Variable Size 339741200 bytes
Database Buffers 150994944 bytes
Redo Buffers 8093696 bytes
Database mounted.
SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- ----------
1924111546 CLBO MOUNTED
$ nid target=sys/oracle dbname=MMBO setname=yes
DBNEWID: Release 10.2.0.3.0 - Production on Wed Apr 24 18:16:54 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database CLBO (DBID=1924111546)
Connected to server version 10.2.0
Control Files in database:
/u02/database/SYBO/controlf/cntl1SYBO.ctl
/u02/database/SYBO/controlf/cntl2SYBO.ctl
/u02/database/SYBO/controlf/cntl3SYBO.ctl
Change database name of database CLBO to MMBO? (Y/[N]) => Y
Proceeding with operation
Changing database name from CLBO to MMBO
Control File /u02/database/SYBO/controlf/cntl1SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl2SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl3SYBO.ctl - modified
................. 省略 .................
Database name changed to MMBO.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
-- 修改 pfile 文件中的 db_name 参数，并重新启动数据库 ( 略 )
-- 校验修伽后的状态， dbid 未发生变化，但 dbname 已经 被修改
$ export ORACLE_SID=MMBO
$ sqlplus / as sysdba
SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- ----------
1924111546 MMBO READ WRITE
一.1.1.3 同时修改 dbname以及dbid
[oracle@testdb dbs]$ nid target=sys/lhr dbname=love
DBNEWID: Release 11.2.0.3.0 - Production on Sat Jan 24 14:51:39 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database lihu (DBID=888888)
NID-00121: Database should not be open
Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.
$ export ORACLE_SID=MMBO
SQL> startup mount pfile=/u02/database/SYBO/initMMBO.ora
$ nid target=/ dbname=SYBO
DBNEWID: Release 10.2.0.3.0 - Production on Wed Apr 24 19:12:57 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database MMBO (DBID=1924111546)
Connected to server version 10.2.0
Control Files in database:
/u02/database/SYBO/controlf/cntl1SYBO.ctl
/u02/database/SYBO/controlf/cntl2SYBO.ctl
/u02/database/SYBO/controlf/cntl3SYBO.ctl
Change database ID and database name MMBO to SYBO? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1924111546 to 4235963258
Changing database name from MMBO to SYBO
Control File /u02/database/SYBO/controlf/cntl1SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl2SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl3SYBO.ctl - modified
Datafile /u02/database/SYBO/oradata/sysSYBO.dbf - dbid changed, wrote new name
................
Datafile /u02/database/SYBO/temp/tmp.dbf - dbid changed, wrote new name
Control File /u02/database/SYBO/controlf/cntl1SYBO.ctl - dbid changed, wrote new name
Control File /u02/database/SYBO/controlf/cntl2SYBO.ctl - dbid changed, wrote new name
Control File /u02/database/SYBO/controlf/cntl3SYBO.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to SYBO.
Modify parameter file and generate a new password file before restarting.
Database ID for database SYBO changed to 4235963258.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
-- 修改 pfile 文件的 db_name ，并重命名了 pfile 文件为 initSYBO.ora
SQL> startup pfile=/u02/database/SYBO/initSYBO.ora
ORACLE instance started.
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- ----------
2315014338 SYBO READ WRITE
-- 接下来重建 spfile 文件以及密码文件 , 配值监听 ,tnsnames.ora, 修改 global name, 重建 dbconsole, 全备等 , 此处省略 .
-- 对于仅仅修改 dbid 的情形不再演示 . mount 状态下发布命令 nid target=sys/pwd 即可 .
1.1 使用 dbms_backup_restore 包修改 dbname 及 dbid
本文通过 dbms_backup_restore 方式来修改 dbname 及 dbid ，供大家参考。
创建 dbms_backup_restore 包：
SQL> @?/rdbms/admin/dbmsbkrs.sql
1、 修改 dbid 及 dbname 的步骤
a 、一致性关闭数据库并启动数据库到 read only 状态 ( 需要调用 dbms_backup_restore ，因此要 open 数据库 )
b 、调用脚本修改 dbname 或者 dbid( 根据提示输入 )
c 、修改 spfile 或 pfile 中的 db_name 的值，如果仅改变 dbid ，此步骤可忽略
d 、以 open resetlogs 方式打开数据库
2 、实战演习
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;
-- startup open pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initLIHUARONG.ora' read only;
sys@ES0481> select name,dbid,open_mode from v$database;
NAME DBID open_mode
--------- ---------- -----------
ES0481 123456 READ ONLY
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 Area 599785472 bytes
Fixed Size 2074568 bytes
Variable Size 167774264 bytes
Database Buffers 423624704 bytes
Redo Buffers 6311936 bytes
Database mounted.
idle> alter database open resetlogs;
Database altered.
idle> create spfile from pfile='/users/oracle/OraHome10g/dbs/initES0480.ora';
File created.
idle> startup force;
idle> select name,dbid from v$database;
NAME DBID
--------- ----------
ES0480 654321
3 、脚本 chg_dbname_dbid.sql
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_chgdbid binary_integer;
v_chgdbname binary_integer;
v_skipped binary_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;
/
About Me
| ........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、博客园（ http://www.cnblogs.com/lhrbest ）和个人weixin公众号（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 （满） 、618766405 ● weixin群：可加我weixin，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ） ，注明添加缘由 ● 于 2018-08-01 06:00 ~ 2018-08-31 24:00 在魔都完成 ● 最新修改时间：2018-08-01 06:00 ~ 2018-08-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用weixin 客户端 扫描下面的二维码来关注小麦苗的weixin公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）、添加小麦苗weixin， 学习最实用的数据库技术。 ........................................................................................................................ |
 | |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2212464/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2212464/
940
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
