Hello Friends,
In this post we will discuss how to change database name by using nid utility
We will modify name from XP1 to XA1 : Senario : Database restore completed lets start with name change of database
Step1 :
% sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 17 03:40:38 2020
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS'nstance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------
XP1 finda10 PRIMARY MOUNTED ARCHIVELOG 12.2.0.1.0 ALLOWED 16-JUN-2020 14:11:20
SQL>
SQL>
SQL> select * from v$log
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- ----------
1 ONLINE /oracle/XP1/origlogA/log_g11m1.dbf NO 0
1 ONLINE /oracle/XP1/mirrlogA/log_g11m2.dbf NO 0
2 ONLINE /oracle/XP1/origlogB/log_g12m1.dbf NO 0
2 ONLINE /oracle/XP1/mirrlogB/log_g12m2.dbf NO 0
3 ONLINE /oracle/XP1/origlogA/log_g13m1.dbf NO 0
3 ONLINE /oracle/XP1/mirrlogA/log_g13m2.dbf NO 0
4 ONLINE /oracle/XP1/origlogB/log_g14m1.dbf NO 0
4 ONLINE /oracle/XP1/mirrlogB/log_g14m2.dbf NO 0
8 rows selected.
SQL> alter database open resetlogs;
Database altered.
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS'nstance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------
XP1 finda10 PRIMARY READ WRITE ARCHIVELOG 12.2.0.1.0 ALLOWED 16-JUN-2020 14:11:20
Step 2 : Stop database and make it in mount state and then start with NID as follows
% nid TARGET=sys DBNAME=XA1
DBNEWID: Release 12.2.0.1.0 - Production on Wed Jun 17 03:56:42 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database XP1 (DBID=1561882277)
Connected to server version 12.2.0
Control Files in database:
/oracle/XA1/origlogA/cntrl/cntrlXA1.dbf
/oracle/XA1/origlogB/cntrl/cntrlXA1.dbf
/oracle/XA1/sapdata1/cntrl/cntrlXA1.dbf
Change database ID and database name XP1 to XA1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1561882277 to 276617539
Changing database name from XP1 to XA1
Control File /oracle/XA1/origlogA/cntrl/cntrlXA1.dbf - modified
Control File /oracle/XA1/origlogB/cntrl/cntrlXA1.dbf - modified
Control File /oracle/XA1/sapdata1/cntrl/cntrlXA1.dbf - modified
Datafile /oracle/XP1/sapdata1/system_1/system.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/sysaux_1/sysaux.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/undo_1/undo.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_1/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_2/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_3/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_4/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_5/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_6/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_7/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_8/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_9/rd1.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_10/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_11/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_12/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_13/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_14/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_15/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_16/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_17/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_18/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_19/rd1.data1 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_20/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_21/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_22/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_23/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_24/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_25/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_26/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_27/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_28/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_29/rd1.data2 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_30/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_31/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_32/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_33/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_34/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_35/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_36/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_37/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_38/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_39/rd1.data3 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_40/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_41/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_42/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_43/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_44/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_45/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_46/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_47/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_48/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_49/rd1.data4 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_50/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_51/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_52/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_53/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_54/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_55/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_56/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_57/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_58/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_59/rd1.data5 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1_60/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_61/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_62/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_63/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_64/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_65/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_66/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_67/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_68/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_69/rd1.data6 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_70/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_71/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_72/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1_73/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_74/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_75/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_76/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1_77/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1731i_1/rd1731i.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1731x_1/rd1731x.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1731x_2/rd1731x.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1731x_3/rd1731x.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/rd1731x_4/rd1731x.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/rd1731x_5/rd1731x.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1usr_1/rd1usr.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/undo_2/undo.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata3/XP1bmcd_1/XP1bmcd.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata4/XP1bmci_1/XP1bmci.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1bmcd_1/rd1bmcd.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata1/rd1bmci_1/rd1bmci.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_78/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/rd1_79/rd1.data7 - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/temp_1/temp.data - dbid changed, wrote new name
Datafile /oracle/XP1/sapdata2/temp_2/temp.data - dbid changed, wrote new name
Control File /oracle/XA1/origlogA/cntrl/cntrlXA1.dbf - dbid changed, wrote new name
Control File /oracle/XA1/origlogB/cntrl/cntrlXA1.dbf - dbid changed, wrote new name
Control File /oracle/XA1/sapdata1/cntrl/cntrlXA1.dbf - dbid changed, wrote new name
Instance shut down
Database name changed to XA1.
Modify parameter file and generate a new password file before restarting.
Database ID for database XA1 changed to 276617539.
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.
Now start the database and open it in resetlogs mode
% setenv ORACLE_SID XA1
% sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 17 04:00:21 2020
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup mount pfile='initXA1.ora'
ORACLE instance started.
Total System Global Area 1.2885E+10 bytes
Fixed Size 19345928 bytes
Variable Size 6744444408 bytes
Database Buffers 6039797760 bytes
Redo Buffers 81313792 bytes
Database mounted.
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS'nstance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------
XA1 finda10 PRIMARY READ WRITE ARCHIVELOG 12.2.0.1.0 ALLOWED 17-JUN-2020 04:01:00
ConversionConversion EmoticonEmoticon