CHANGING A DATABASE NAME USING NEWID

CHANGING A DATABASE NAME USING NEWID

DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.

The DBNEWID utility solves this problem by allowing you to change any of the following:

• Only the DBID of a database
• Only the DBNAME of a database
• Both the DBNAME and DBID of a database

CHECK THE DATABASE NAME AND DBID BEFORE CHANGING.

select dbid,name from v$database;

SQL> select dbid,name from v$database;

DBID NAME
———- —————————
3659638953 PROD

Shutdown the database and mount the database .

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size 12178656 bytes
Variable Size 8287947552 bytes
Database Buffers 7784628224 bytes
Redo Buffers 21372928 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 – 64bit Production

Execute NID command :

[oracle@doyen PROD] nid target=SYS dbname=NEWPROD

DBNEWID: Release 12.2.0.1.0 – Production on Fri Feb 21 07:22:44 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Password:*******
Connected to database PROD (DBID=3655454153)

Connected to server version 12.2.0

Control Files in database:
/u02/oradata/PROD/control01.ctl
/u02/oradata/PROD/control02.ctl

Change database ID and database name PROD to NEWPROD? (Y/[N]) => y

Proceeding with operation
Changing database ID from 3655454153 to 4021552588
Changing database name from PROD to NEWPROD
Control File /u02/oradata/PROD/control01.ctl – modified
Control File /u02/oradata/PROD/control02.ctl – modified
Datafile /u02/oradata/PROD/system.257.98751791 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.280.98751945 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sysaux.258.98751795 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.259.98751797 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs2.270.98751809 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/users.260.98751797 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.281.98751945 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.282.98751945 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.283.98751946 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.284.98751946 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.285.98751946 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.286.98751946 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.287.98751946 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/audit_data.289.98751954 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sysaux.279.98936086 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sysaux.610.98979205 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs2.290.99153865 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.608.99153868 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.607.99153877 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.606.99358348 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.603.100033148 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.604.100033148 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.605.100033148 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_perm.599.100033184 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_perm.601.100033184 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_perm.598.100033184 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_perm.596.100033184 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.594.100243039 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.595.100484813 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.592.100751229 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.591.100994492 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.593.101037933 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.256.101067858 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.257.101067864 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.258.101067865 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.259.101067866 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.260.101067867 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.261.101067868 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.262.101067869 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.263.101067870 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.264.101067871 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.265.101067872 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.266.101067873 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.267.101067874 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.268.101067875 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.269.101067876 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.270.101067877 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.271.101067878 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_perm.272.101067879 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_perm.273.101067886 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_perm.274.101067888 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_perm.275.101067888 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_arch.276.101067958 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_arch.277.101068013 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dmcor_perm.278.101142689 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_arch.288.101332831 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.290.101334555 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.291.101334703 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.292.101334878 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.293.101335044 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.294.101335207 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.295.101335397 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs1.296.101335587 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/undotbs2.297.101344456 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.298.101398924 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.299.101589830 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_perm.300.101589854 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.301.101620481 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.609.101810776 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.302.101962597 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_perm.303.101997742 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.304.101999592 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_dmd_sht_arch.305.102003796 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_perm.306.102006339 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_perm.307.102006363 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.308.102178965 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.309.102234364 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.310.102320641 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.311.102409373 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.312.102455563 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.313.102489940 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_gop_perm.314.102599862 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.315.102628412 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.316.102676176 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.317.102740708 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.318.102770027 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/system.319.102835524 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dw_perm.320.102947057 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/temp.269.98751804 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_temp2.281.101276450 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_temp2.282.101276457 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_temp2.279.101249514 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_dwstg_temp2.280.101249531 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/sathish_prod_temp2.283.101276465 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/temp.284.101331136 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/temp.285.101331141 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/temp.286.101332188 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/temp.287.101332189 – dbid changed, wrote new name
Datafile /u02/oradata/PROD/temp.289.101332882 – dbid changed, wrote new name
Control File /u02/oradata/PROD/control01.ctl – dbid changed, wrote new name
Control File /u02/oradata/PROD/control02.ctl – dbid changed, wrote new name
Instance shut down

Database name changed to NEWPROD.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEWPROD changed to 4021552588.
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.

STARTUP THE INSTANCE WITH NOMOUNT OR CHANGE THE DATABASE NAME IN PFILE TO AFFECT THE CHANGE.

HERE WE CHANGED THE DB_NAME IN PFILE .

[oracle@doyen NEWPROD] export ORACLE_SID=NEWPROD
[oracle@doyen NEWPROD] export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
[oracle@doyen NEWPROD] sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 21 07:36:33 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size 12178656 bytes
Variable Size 8287947552 bytes
Database Buffers 7784628224 bytes
Redo Buffers 21372928 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME
—————————
OPEN_MODE
————————————————————
NEWPROD
READ WRITE

CHECK THE DBNAME AND DBID :

SQL> select dbid,name from v$database;

DBID NAME
———- —————————
4029532688 NEWPROD