Oracle 改变数据库的名字的工具NID

博客首页 » Oracle 改变数据库的名字的工具NID

发布于 27 Aug 2013 00:09
标签 blog
%E6%AD%A3%E5%BC%8F%E6%9B%B4%E5%90%8D.jpg
在复制数据库,测试数据库的时候,可以通过nid转换数据库的名字和ID。

(图片稍微调侃一下著名的猫狗改名事件,也算是帮他们做宣传了吧,呵呵。)

准备工作

把数据库启动到mount状态。

shutdown immediate;
startup mount;

执行nid

[~ oracle(test01)@test03] nid target=sys/pass dbname=test02t
 
DBNEWID: Release 10.2.0.4.0 - Production on Tue Aug 27 08:59:20 2013
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Connected to database test01 (DBID=3792989435)
 
Connected to server version 10.2.0
 
Control Files in database:
    /u00/oradata/test01/control01.ctl
    /u02/oradata/test01/control02.ctl
    /u03/oradata/test01/control03.ctl
    /u04/oradata/test01/control04.ctl
 
Change database ID and database name test01 to test02t? (Y/[N]) => y
 
Proceeding with operation
Changing database ID from 3792989435 to 1066969834
Changing database name from test01 to test02t
    Control File /u00/oradata/test01/control01.ctl - modified
    Control File /u02/oradata/test01/control02.ctl - modified
    Control File /u03/oradata/test01/control03.ctl - modified
    Control File /u04/oradata/test01/control04.ctl - modified
    Datafile /u02/oradata/test01/system01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/undotbs_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/undotbs_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/undotbs_03.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/undotbs_04.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/undotbs_05.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/sysaux01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_users_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_mast_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_mast_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_mast_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_mast_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_tran_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_tran_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_tran_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_tran_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_summ_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_summ_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_summ_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_summ_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_work_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_work_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_work_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_work_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_m001_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_m001_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_m001_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_m001_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t001_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t001_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t001_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t001_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t003_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t003_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t003_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t003_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t002_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t002_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t002_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t002_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t035_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_t035_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t035_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t035_idx_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_m001_text_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_m001_text_idx_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t001_text_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_c/test_c_t001_text_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_users_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_mast_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_mast_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_mast_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_mast_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_tran_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_tran_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_tran_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_tran_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_summ_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_summ_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_summ_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_summ_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_work_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_work_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_work_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_work_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_m001_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_m001_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_m001_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_m001_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t001_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t001_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t001_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t001_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t003_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t003_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t003_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t003_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t002_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t002_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t002_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t002_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t035_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_b/test_b_t035_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t035_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t035_idx_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_m001_text_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_m001_text_idx_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t001_text_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_b/test_b_t001_text_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_text_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/statspack_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/statspack_dat_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_users_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_mast_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_mast_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_mast_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_mast_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_tran_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_tran_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_tran_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_tran_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_summ_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_summ_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_summ_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_summ_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_work_dat_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_work_idx_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_m001_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_m001_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_m001_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_m001_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t001_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t001_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t001_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t001_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t003_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t003_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t003_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t003_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t002_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t002_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t002_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t002_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t035_dat_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_a/test_a_t035_dat_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t035_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t035_idx_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_m001_text_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_m001_text_idx_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t001_text_idx_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/test_a/test_a_t001_text_idx_02.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/test01/test_c/test_c_users_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_02.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_03.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_04.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_05.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_06.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_07.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_08.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_09.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/test01/temp_10.dbf - dbid changed, wrote new name
    Control File /u00/oradata/test01/control01.ctl - dbid changed, wrote new name
    Control File /u02/oradata/test01/control02.ctl - dbid changed, wrote new name
    Control File /u03/oradata/test01/control03.ctl - dbid changed, wrote new name
    Control File /u04/oradata/test01/control04.ctl - dbid changed, wrote new name
    Instance shut down
 
Database name changed to test02t.
Modify parameter file and generate a new password file before restarting.
Database ID for database test02t changed to 1066969834.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

使用新的dbname关闭数据库。

export ORACLE_SID=test02t
 
sqlplus "/ as sysdba"
shutdown immediate;
quit;

修改init/spfile中的dbname。

sqlplus "/ as sysdba"
startup nomount;
alter system set db_name = 'test02t' scope=spfile;
shutdown immediate;

如果需要修改的内容太多,可以转储成init文件修改

shutdown immediate;
create pfile from spfile;

注意之后要用

create spfile from pfile;

转储回spfile。

启动新数据库到mount,用resetlogs打开,检查。

[~ oracle(test02t)@test03] export ORACLE_SID=test02t
[~ oracle(test02t)@test03] sqlplus "/ as sysdba"
 
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 27 09:04:06 2013
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
test02t(SYS)> shutdown immediate;
ORA-01507: database not mounted
 
ORACLE instance shut down.
test02t(SYS)> startup nomount
ORACLE instance started.
 
Total System Global Area 2.1475E+10 bytes
Fixed Size                  2122368 bytes
Variable Size            1.2482E+10 bytes
Database Buffers         8975810560 bytes
Redo Buffers               14651392 bytes
test02t(SYS)> alter database mount;
 
Database altered.
 
Elapsed: 00:00:04.09
test02t(SYS)> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 
Elapsed: 00:00:00.40
test02t(SYS)> alter database open resetlogs;
 
Database altered.
 
Elapsed: 00:00:31.86
test02t(SYS)>
test02t(SYS)> /
 
      DBID NAME      CREATED  RESETLOGS_CHANGE# RESETLOG PRIOR_RESETLOGS_CHANGE# PRIOR_RE LOG_MODE     CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROL
---------- --------- -------- ----------------- -------- ----------------------- -------- ------------ ------------------ --------------- -------
CONTROLF CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLF OPEN_RESETL VERSION_ OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A
-------- --------------------- ------------------- -------- ----------- -------- ---------- -------------------- -------------------- --------
ACTIVATION# SWITCHOVER# DATABASE_ROLE    ARCHIVELOG_CHANGE# ARCHIVEL SWITCHOVER_STATUS    DATAGUAR GUARD_S SUPPLEME SUP SUP FOR PLATFORM_ID
----------- ----------- ---------------- ------------------ -------- -------------------- -------- ------- -------- --- --- --- -----------
PLATFORM_NAME                                                                                         RECOVERY_TARGET_INCARNATION#
----------------------------------------------------------------------------------------------------- ----------------------------
LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON       SUP SUP DB_UNIQUE_NAME                 STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS
---------------------- ----------- ------------------ --- --- ------------------------------ -------------------------- ---------------------
FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL
------------------------------ --------------------- -------
FS_FAILOVER_OBSERVER_HOST
------------------------------------------------------------------------------------------------------------------------------------------------------
1066969834 test02t   12-06-04        2.3470E+12 13-08-27                       1 12-06-04 ARCHIVELOG           2.3470E+12               0 CURRENT
12-06-04                860814          2.3470E+12 13-08-27 NOT ALLOWED 12-06-04 READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED
 1066919280  1066919280 PRIMARY                  2.3470E+12 DISABLED NOT ALLOWED          DISABLED NONE    NO       NO  NO  YES          13
Linux x86 64-bit                                                                                                                 2
                     2  2.3470E+12 NO                 NO  NO  test02t                                                 0 DISABLED
                                                   0
 
Elapsed: 00:00:00.01
test02t(SYS)> select * from v$instance;
 
INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_ STATUS       PAR
--------------- ---------------- ---------------------------------------------------------------- ----------------- -------- ------------ ---
   THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
              1 test02t          test03                                                    10.2.0.4.0        13-08-27 OPEN         NO
         1 STARTED                 ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
 
Elapsed: 00:00:00.00

备份

rman target / nocatalog
show all;
backup database plus archivelog delete input;
quit;

改变DB的数据文件路径(OFA)

DB的数据文件一般在路径里都有SID。所以改变SID以后也需要改变数据文件名。全库文件改名需要在mount模式下。

shutdown immediate;
startup mount;
 
set line 1000;
set pages 0;
set feedback off;
set echo off;
set trimspool on;
 
spool rename_files.sql
-- 数据文件和临时文件
select 'alter database rename file ''' || name || ''' to ''' || replace(name, '/旧SID/', '/新SID/') || ''';'
from (
select name from v$datafile
union all
select name from v$tempfile
)
order by 1;
 
--log文件
select 'alter database rename file ''' || member || ''' to ''' || replace(member, '/旧SID/', '/新SID/') || ''';'
from (
select member from v$logfile
)
order by 1;
spool off;
 
@rename_files.sql

Reference:
http://www.dedecms.com/knowledge/data-base/oracle/2012/0706/2758.html


本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • Oracle 改变数据库的名字的工具NID

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment