在Oracle12.1中使用NID 修改数据库的DBID和DBName【实战】

在异机恢复完成后，使用NID 修改数据库的DBID和DBName，重新启动数据库。

主要步骤如下：

1、 开启数据库：

 SQL> startupORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 4.2950E+10 bytesFixed Size                  4508576 bytesVariable Size            2.6172E+10 bytesDatabase Buffers         1.5569E+10 bytesRedo Buffers             1203449856 bytesDatabase mounted.Database opened.

2、创建pfile,用于对DBName进行参数修改

SQL> create pfile from spfile;

3、查询当前的DBID和DBNAME

select name,dbid from v$database;

4、关闭数据库

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> exit

5、备份数据文件，尤其是控制文件

cp proddata  data  (简单进行冷备份)

6、将数据库启动到mount状态

[orapbf@erptest dbs]$ export ORACLE_SID=PROD[orapbf@erptest dbs]$ echo $ORACLE_SIDPROD

[orapbf@erptest dbs]$sqlplus / as sysdbaSQL>startup mount;

7、查看nid命令简单帮助文档

[orapbf@erptest dbs]$ nid

DBNEWID: Release 12.1.0.2.0 - Production on Wed Mar 14 22:40:19 2018Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Keyword     Description                    (Default)----------------------------------------------------TARGET      Username/Password              (NONE)DBNAME      New database name              (NONE)LOGFILE     Output Log                     (NONE)REVERT      Revert failed change           NOSETNAME     Set a new database name only   NOAPPEND      Append to output log           NOHELP        Displays these messages        NO

8、使用nid修改dbid和DBName

[orapbf@erptest dbs]$ nid target=sys/oracle dbname=PBFDBNEWID: Release 12.1.0.2.0 - Production on Wed Mar 14 22:41:41 2018Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Connected to database PROD (DBID=357998902)Connected to server version 12.1.0Control Files in database:    /mnt/data/PBF/db/proddata/cntrl01.dbf    /mnt/data/PBF/db/proddata/cntrl02.dbf    /mnt/data/PBF/db/proddata/cntrl03.dbfChange database ID and database name PROD to PBF? (Y/[N]) => yProceeding with operationChanging database ID from 357998902 to 2995320870Changing database name from PROD to PBF    Control File /mnt/data/PBF/db/proddata/cntrl01.dbf - modified    Control File /mnt/data/PBF/db/proddata/cntrl02.dbf - modified    Control File /mnt/data/PBF/db/proddata/cntrl03.dbf - modified    Datafile /mnt/data/PBF/db/proddata/system01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system05.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/ctxd01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/owad01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_queue02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/odm.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/olap.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/sysaux01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/apps_ts_tools01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system12.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_data04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind06.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_ref03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_int02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/sysaux02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system13.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system14.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system15.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system16.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system17.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system18.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system19.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system20.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system21.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system22.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system23.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system24.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system25.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_ref04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_ref05.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_ref06.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/undo02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/cux_data01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/cux_ind01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/undo03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/undo04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/undo05.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/sysaux03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media05.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind07.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind08.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_data05.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_data06.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_queue03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/ctxd0 - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media06.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media07.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/sysaux04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media08.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media09.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/sysaux05.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind09.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_data07.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media10.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media11.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media12.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media13.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media14.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media15.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media16.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media17.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media18.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_queue04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media19.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media20.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media21.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system10.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system06.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/portal01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system07.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system09.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system08.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/system11.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/undo01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_data01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_ref01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_int01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_summ01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_nolog01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_archive01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_queue01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_media01.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_data02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_data03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind03.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind04.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_txn_ind05.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/a_ref02.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp11.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp21.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp12.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp22.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp32.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp42.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp13.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp33.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp43.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp31.db - dbid changed, wrote new name    Datafile /mnt/data/PBF/db/proddata/temp41.db - dbid changed, wrote new name    Control File /mnt/data/PBF/db/proddata/cntrl01.dbf - dbid changed, wrote new name    Control File /mnt/data/PBF/db/proddata/cntrl02.dbf - dbid changed, wrote new name    Control File /mnt/data/PBF/db/proddata/cntrl03.dbf - dbid changed, wrote new name    Instance shut downDatabase name changed to PBF.Modify parameter file and generate a new password file before restarting.Database ID for database PBF changed to 2995320870.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.

[orapbf@erptest dbs]$ exit登出

9、修改参数文件

[orapbf@erptest ~]$ cd $ORACLE_HOME/dbs[orapbf@erptest dbs]$ lshc_PROD.dat  initPROD.ora  lkPROD  snapcf_PROD.f  spfilePROD.ora[orapbf@erptest dbs]$ vi initPROD.ora *.compatible='12.1.0'...*.db_name='PBF'...*._sort_elimination_cost_ratio=5*._system_trig_enabled=TRUE*._TRACE_FILES_PUBLIC=FALSE*.aq_tm_processes=1*.AUDIT_SYS_OPERATIONS=TRUE*.compatible='12.1.0'*.control_files='/mnt/data/PBF/db/proddata/cntrl01.dbf','/mnt/data/PBF/db/proddata/cntrl02.dbf','/mnt/data/PBF/db/proddata/cntrl03.dbf'#Restore Controlfile*.cursor_sharing='EXACT'# Required 11i settting*.db_block_checking='FALSE'*.db_block_checksum='TRUE'*.db_block_size=8192*.db_files=512# Max. no. of database files*.db_name='PBF'*.db_recovery_file_dest_size=214748364800*.db_recovery_file_dest='/mnt/data/PBF/db/db_recovery_dest'*.diagnostic_dest='/mnt/data/PBF/db/12.1.0/admin/PBF_erptest'*.dml_locks=10000*.event='10995 trace name context forever, level 16'*.job_queue_processes=10*.local_listener='PBF_LOCAL'*.log_archive_dest_1='LOCATION=/mnt/data/PBF/db/archive'"initPROD.ora" 76L, 2920C 已写入

10、创建新的密码文件

orapwd file=/mnt/data/PBF/db/12.1.0/dbs/initPBF password=oracle format=12

11、使用resetlogs 选项打开数据库

[orapbf@erptest dbs]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 14 22:46:59 2018Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup nomount;ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 4.2950E+10 bytesFixed Size                  4508576 bytesVariable Size            1.1274E+10 bytesDatabase Buffers         3.0467E+10 bytesRedo Buffers             1203449856 bytesSQL> alter database mount;Database altered.SQL> alter database open resetlogs;SQL> select name,dbid from v$database;NAME            DBID--------- ----------PBF       2995320870Database altered.sqlplus / as sysdbastartup

12、创建新的spfile文件

create spfile from pfile;