Oracle 改变数据库的名字的工具NID
博客首页 » Oracle 改变数据库的名字的工具NID
发布于 27 Aug 2013 00:09标签 blog
在复制数据库,测试数据库的时候,可以通过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
这篇文章对你有帮助吗,投个票吧?
留下你的评论