nid (DBNEWID) - Changing DBID and Changing DBNAME in Oracle
We can change the DBID and Database name using nid command.
We will do below 3 demonstration
1. Changing Your Database Identifier (DBID) Only:
2. Changing Your Database Name (DBNAME) Only:
3. Changing Both Your Database Name and Database Identifier:
1. Changing Your Database Identifier (DBID) Only:
SELECT dbid, name FROM v$database;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
nid TARGET=SYS/secure@<service_name>
nid TARGET=SYS/Mallik123@DEVDB
SHUTDOWN IMMEDIATE
orapwd file=orapw<SID> password=<your password> entries=
orapwd file=orapwDEVDB password=Mallik123 entries=5
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;
SELECT dbid, name FROM v$database;
2. Changing Your Database Name (DBNAME) Only:
SELECT name FROM v$database;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
nid TARGET=<username>/<password>@<servicename> DBNAME=<newname> SETNAME=YES
nid TARGET=SYS/Mallik123@DEVDB DBNAME=TESTDB SETNAME=YES
SHUTDOWN IMMEDIATE
Change the DB_NAME initialization parameter to your new database name.
mv init<DB_NAME>.ora init<NEW_NAME>.ora
orapwd file=orapw<SID> password=<your password> entries=
orapwd file=orapwTESTDB password=Mallik123 entries=5
STARTUP
SELECT name FROM v$database;
3. Changing Both Your Database Name and Database Identifier:
SELECT dbid, name FROM v$database;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
nid TARGET=<username>/<password>@<service name> DBNAME=<new name>
nid TARGET=SYS/Mallik123@TESTDB DBNAME=UATDB
SHUTDOWN IMMEDIATE
Change the DB_NAME initialization parameter to your new database name.
mv init<DB_NAME>.ora init<NEW_NAME>.ora
orapwd file=orapw<SID> password=<your password> entries=
orapwd file=orapwUATDB password=Mallik123 entries=5
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;
SELECT name, dbid FROM v$database;
Output Logs:
1. Changing Your Database Identifier (DBID) Only:
[oracle@oraclelab3 ~]$ ps -ef|grep smon
oracle 17624 1 0 Jul15 ? 00:00:01 asm_smon_+ASM
oracle 20985 1 0 09:47 ? 00:00:00 ora_smon_DEVDB
oracle 24985 19618 0 11:48 pts/2 00:00:00 grep --color=auto smon
[oracle@oraclelab3 ~]$
[oracle@oraclelab3 ~]$ . oraenv
ORACLE_SID = [DEVDB] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 11:53:42 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT dbid, name FROM v$database;
DBID NAME
---------- ---------
982691568 DEVDB
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL>
[oracle@oraclelab3 ~]$ nid TARGET=SYS/Mallik123@DEVDB
DBNEWID: Release 19.0.0.0.0 - Production on Fri Jul 16 11:55:53 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to database DEVDB (DBID=982691568)
Connected to server version 19.3.0
Control Files in database:
+DATA/DEVDB/CONTROLFILE/current.261.1077961585
+RECO/DEVDB/CONTROLFILE/current.256.1077961585
Change database ID of database DEVDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 982691568 to 982765001
Control File +DATA/DEVDB/CONTROLFILE/current.261.1077961585 - modified
Control File +RECO/DEVDB/CONTROLFILE/current.256.1077961585 - modified
Datafile +DATA/DEVDB/DATAFILE/system.257.107796146 - dbid changed
Datafile +DATA/DEVDB/DATAFILE/sysaux.258.107796151 - dbid changed
Datafile +DATA/DEVDB/DATAFILE/undotbs1.259.107796154 - dbid changed
Datafile +DATA/DEVDB/DATAFILE/users.260.107796154 - dbid changed
Datafile +DATA/DEVDB/TEMPFILE/temp.265.107796159 - dbid changed
Control File +DATA/DEVDB/CONTROLFILE/current.261.1077961585 - dbid changed
Control File +RECO/DEVDB/CONTROLFILE/current.256.1077961585 - dbid changed
Instance shut down
Database ID for database DEVDB changed to 982765001.
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 ID.
DBNEWID - Completed succesfully.
[oracle@oraclelab3 ~]$
[oracle@oraclelab3 ~]$ ps -ef|grep smon
oracle 17624 1 0 Jul15 ? 00:00:01 asm_smon_+ASM
oracle 27579 19618 0 11:57 pts/2 00:00:00 grep --color=auto smon
[oracle@oraclelab3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 11:57:13 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab3 ~]$ cd $ORACLE_HOME
[oracle@oraclelab3 dbhome_1]$ cd dbs
[oracle@oraclelab3 dbs]$ ls -ltrh
total 20K
-rw-r--r--. 1 oracle oinstall 3.1K May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 24 Jul 15 10:54 lkDEVDB
-rw-r-----. 1 oracle oinstall 3.5K Jul 15 11:19 orapwDEVDB
-rw-r-----. 1 oracle oinstall 608 Jul 15 11:19 initDEVDB.ora
-rw-rw----. 1 oracle oinstall 1.6K Jul 16 11:58 hc_DEVDB.dat
[oracle@oraclelab3 dbs]$
Creation of password file is optional since the databas name is same, We have only changed the DBID:
[oracle@oraclelab3 dbs]$ orapwd file=orapwDEVDB password=Mallik123 entries=5 force=y
OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
[oracle@oraclelab3 dbs]$ orapwd file=orapwDEVDB password=Mallik123! entries=5 force=y
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:00:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT dbid, name FROM v$database;
DBID NAME
---------- ---------
982765001 DEVDB
SQL> SELECT name FROM v$database;
NAME
---------
DEVDB
SQL>
2. Changing Your Database Name (DBNAME) Only:
[oracle@oraclelab3 ~]$ ps -ef|grep smon
oracle 17624 1 0 Jul15 ? 00:00:01 asm_smon_+ASM
oracle 20985 1 0 09:47 ? 00:00:00 ora_smon_DEVDB
oracle 24985 19618 0 11:48 pts/2 00:00:00 grep --color=auto smon
[oracle@oraclelab3 ~]$
[oracle@oraclelab3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:00:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT dbid, name FROM v$database;
DBID NAME
---------- ---------
982765001 DEVDB
SQL> SELECT name FROM v$database;
NAME
---------
DEVDB
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL>
[oracle@oraclelab3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:02:53 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> alter database open;
Database altered.
SQL> alter user sys identified by Mallik123;
User altered.
SQL>
[oracle@oraclelab3 dbs]$ sqlplus sys/Mallik123@DEVDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:04:01 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL>
[oracle@oraclelab3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:04:55 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ nid TARGET=SYS/Mallik123@DEVDB DBNAME=TESTDB SETNAME=YES
DBNEWID: Release 19.0.0.0.0 - Production on Fri Jul 16 12:05:30 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to database DEVDB (DBID=982765001)
Connected to server version 19.3.0
Control Files in database:
+DATA/DEVDB/CONTROLFILE/current.261.1077961585
+RECO/DEVDB/CONTROLFILE/current.256.1077961585
Change database name of database DEVDB to TESTDB? (Y/[N]) => Y
Proceeding with operation
Changing database name from DEVDB to TESTDB
Control File +DATA/DEVDB/CONTROLFILE/current.261.1077961585 - modified
Control File +RECO/DEVDB/CONTROLFILE/current.256.1077961585 - modified
Datafile +DATA/DEVDB/DATAFILE/system.257.107796146 - wrote new name
Datafile +DATA/DEVDB/DATAFILE/sysaux.258.107796151 - wrote new name
Datafile +DATA/DEVDB/DATAFILE/undotbs1.259.107796154 - wrote new name
Datafile +DATA/DEVDB/DATAFILE/users.260.107796154 - wrote new name
Datafile +DATA/DEVDB/TEMPFILE/temp.265.107796159 - wrote new name
Control File +DATA/DEVDB/CONTROLFILE/current.261.1077961585 - wrote new name
Control File +RECO/DEVDB/CONTROLFILE/current.256.1077961585 - wrote new name
Instance shut down
Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
[oracle@oraclelab3 dbs]$ ls -ltrh
total 24K
-rw-r--r--. 1 oracle oinstall 3.1K May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 24 Jul 15 10:54 lkDEVDB
-rw-r-----. 1 oracle oinstall 608 Jul 15 11:19 initDEVDB.ora
-rw-r-----. 1 oracle oinstall 6.0K Jul 16 12:05 orapwDEVDB
-rw-rw----. 1 oracle oinstall 1.6K Jul 16 12:05 hc_DEVDB.dat
[oracle@oraclelab3 dbs]$ mv initDEVDB.ora initTESTDB.ora
[oracle@oraclelab3 dbs]$ mv orapwDEVDB orapwTESTDB
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ cat /etc/oratab
#Backup file is /u01/app/oracle/crsdata/oraclelab3/output/oratab.bak.oraclelab3.oracle line added by Agent
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/19.0.0.0/grid:N # line added by Agent
DEVDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N # line added by Agent
[oracle@oraclelab3 dbs]$ vi /etc/oratab >>> update the DB name from DEVDB to TESTDB
#Backup file is /u01/app/oracle/crsdata/oraclelab3/output/oratab.bak.oraclelab3.oracle line added by Agent
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/19.0.0.0/grid:N # line added by Agent
TESTDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N # line added by Agent
[oracle@oraclelab3 dbs]$ . oraenv
ORACLE_SID = [TESTDB] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ env |grep ORA
ORACLE_SID=TESTDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Jul 16 12:05 hc_DEVDB.dat
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 608 Jul 15 11:19 initTESTDB.ora
-rw-r-----. 1 oracle oinstall 24 Jul 15 10:54 lkDEVDB
-rw-r-----. 1 oracle oinstall 6144 Jul 16 12:05 orapwTESTDB
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ vi initTESTDB.ora >>>> Change db_name=TESTDB
[oracle@oraclelab3 dbs]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs
[oracle@oraclelab3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:10:15 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTESTDB.ora';
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> SELECT name FROM v$database;
NAME
---------
TESTDB
SQL> SELECT dbid, name FROM v$database;
DBID NAME
---------- ---------
982765001 TESTDB
SQL>
[oracle@oraclelab3 dbs]$ sqlplus sys/Mallik123@DEVDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:13:20 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Jul 16 2021 12:05:30 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string DEVDB
SQL>
[oracle@oraclelab3 dbs]$ cd ../network/admin/
[oracle@oraclelab3 admin]$ ll
total 12
drwxr-xr-x. 2 oracle oinstall 64 Apr 17 2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 14 2018 shrept.lst
-rw-r--r--. 1 oracle oinstall 125 Jul 16 09:36 sqlnet.ora
-rw-r-----. 1 oracle oinstall 447 Jul 15 11:19 tnsnames.ora
[oracle@oraclelab3 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_DEVDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab3.localdomain.com)(PORT = 1521))
DEVDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab3.localdomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEVDB)
)
)
[oracle@oraclelab3 admin]$ vi tnsnames.ora >>> chnage tnsnmae alias to TESTDB
[oracle@oraclelab3 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_DEVDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab3.localdomain.com)(PORT = 1521))
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab3.localdomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEVDB)
)
)
[oracle@oraclelab3 admin]$
[oracle@oraclelab3 admin]$ sqlplus sys/Mallik123@TESTDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:14:32 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Jul 16 2021 12:13:20 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT dbid, name FROM v$database;
DBID NAME
---------- ---------
982765001 TESTDB
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL> exit
[oracle@oraclelab3 admin]$
[oracle@oraclelab3 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:15:43 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
ORA-01103: database name 'TESTDB' in control file is not 'DEVDB'
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
Issue:
Since we have modify the DBNAME from DEVDB to TESTDB, we need recreate the spfile which is inside the ASM +DATA diskgroup
SQL> startup pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTESTDB.ora';
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile from pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTESTDB.ora';
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
ORA-01103: database name 'TESTDB' in control file is not 'DEVDB'
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
Issue:
We have to specify the spfile location otherwise it will not create the spfile inside the ASM +DATA diskgroup.
ASMCMD [+DATA/DEVDB] > cd PARAMETERFILE/
ASMCMD [+DATA/DEVDB/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 Y spfile.266.1077967163
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 Y spfile.267.1078057127
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 N spfileDEVDB.ora => +DATA/DEVDB/PARAMETERFILE/spfile.266.1077967163
ASMCMD [+DATA/DEVDB/PARAMETERFILE] > pwd
+DATA/DEVDB/PARAMETERFILE
SQL> startup pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTESTDB.ora';
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> create spfile='+DATA/DEVDB/PARAMETERFILE/spfileTESTDB.ora' from pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTESTDB.ora';
File created.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
ASMCMD [+DATA/DEVDB/PARAMETERFILE] > pwd
+DATA/DEVDB/PARAMETERFILE
ASMCMD [+DATA/DEVDB/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 Y spfile.266.1077967163
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 Y spfile.267.1078057127
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 Y spfile.268.1078057341
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 N spfileDEVDB.ora => +DATA/DEVDB/PARAMETERFILE/spfile.266.1077967163
PARAMETERFILE UNPROT COARSE JUL 16 12:00:00 N spfileTESTDB.ora => +DATA/DEVDB/PARAMETERFILE/spfile.268.1078057341
ASMCMD [+DATA/DEVDB/PARAMETERFILE] >
[oracle@oraclelab3 dbs]$ cat initTESTDB.ora
undo_tablespace=UNDOTBS1
db_recovery_file_dest_size=8405385216
processes=300
pga_aggregate_target=808452096
dispatchers="(PROTOCOL=TCP) (SERVICE=DEVDBXDB)"
sga_target=2432696320
db_recovery_file_dest="+RECO"
db_block_size=8192
diagnostic_dest=/u01/app/oracle
audit_file_dest="/u01/app/oracle/admin/DEVDB/adump"
db_create_file_dest="+DATA"
compatible=12.2.0
control_files='+DATA/DEVDB/CONTROLFILE/current.261.1077961585','+RECO/DEVDB/CONTROLFILE/current.256.1077961585'
audit_trail=DB
db_name="TESTDB"
remote_login_passwordfile=EXCLUSIVE
open_cursors=300
db_unique_name="DEVDB"
local_listener=LISTENER_DEVDB
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:24:07 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/DEVDB/PARAMETERFILE/spfi
letestdb.ora
SQL> alter database mount;
Database altered.
SQL>
3. Changing Both Your Database Name and Database Identifier:
[oracle@oraclelab3 admin]$ sqlplus sys/Mallik123@TESTDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:14:32 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Jul 16 2021 12:13:20 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT dbid, name FROM v$database;
DBID NAME
---------- ---------
982765001 TESTDB
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/DEVDB/PARAMETERFILE/spfi
letestdb.ora
SQL>
[oracle@oraclelab3 dbs]$ nid TARGET=SYS/Mallik123@TESTDB DBNAME=UATDB
DBNEWID: Release 19.0.0.0.0 - Production on Fri Jul 16 12:25:17 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to database TESTDB (DBID=982765001)
Connected to server version 19.3.0
Control Files in database:
+DATA/DEVDB/CONTROLFILE/current.261.1077961585
+RECO/DEVDB/CONTROLFILE/current.256.1077961585
Change database ID and database name TESTDB to UATDB? (Y/[N]) => y
Proceeding with operation
Changing database ID from 982765001 to 1542247341
Changing database name from TESTDB to UATDB
Control File +DATA/DEVDB/CONTROLFILE/current.261.1077961585 - modified
Control File +RECO/DEVDB/CONTROLFILE/current.256.1077961585 - modified
Datafile +DATA/DEVDB/DATAFILE/system.257.107796146 - dbid changed, wrote new name
Datafile +DATA/DEVDB/DATAFILE/sysaux.258.107796151 - dbid changed, wrote new name
Datafile +DATA/DEVDB/DATAFILE/undotbs1.259.107796154 - dbid changed, wrote new name
Datafile +DATA/DEVDB/DATAFILE/users.260.107796154 - dbid changed, wrote new name
Datafile +DATA/DEVDB/TEMPFILE/temp.265.107796159 - dbid changed, wrote new name
Control File +DATA/DEVDB/CONTROLFILE/current.261.1077961585 - dbid changed, wrote new name
Control File +RECO/DEVDB/CONTROLFILE/current.256.1077961585 - dbid changed, wrote new name
Instance shut down
Database name changed to UATDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database UATDB changed to 1542247341.
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.
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ ll
total 28
-rw-rw----. 1 oracle oinstall 1544 Jul 16 12:05 hc_DEVDB.dat
-rw-rw----. 1 oracle oinstall 1544 Jul 16 12:25 hc_TESTDB.dat
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 610 Jul 16 12:10 initTESTDB.ora
-rw-r-----. 1 oracle oinstall 24 Jul 15 10:54 lkDEVDB
-rw-r-----. 1 oracle oinstall 6144 Jul 16 12:25 orapwTESTDB
[oracle@oraclelab3 dbs]$ mv initTESTDB.ora initUATDB.ora
[oracle@oraclelab3 dbs]$ vi initUATDB.ora >>> Chnage to db_name as UATDB
[oracle@oraclelab3 dbs]$ ll
total 28
-rw-rw----. 1 oracle oinstall 1544 Jul 16 12:05 hc_DEVDB.dat
-rw-rw----. 1 oracle oinstall 1544 Jul 16 12:25 hc_TESTDB.dat
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 609 Jul 16 12:26 initUATDB.ora
-rw-r-----. 1 oracle oinstall 24 Jul 15 10:54 lkDEVDB
-rw-r-----. 1 oracle oinstall 6144 Jul 16 12:25 orapwTESTDB
[oracle@oraclelab3 dbs]$ vi /etc/oratab >>> update the DB name from TESTDB to UATDB
[oracle@oraclelab3 dbs]$ . oraenv
ORACLE_SID = [TESTDB] ? UATDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab3 dbs]$
[oracle@oraclelab3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 16 12:27:17 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initUATDB.ora';
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
SQL> create spfile='+DATA/DEVDB/PARAMETERFILE/spfileUATDB.ora' from pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initUATDB.ora';
File created.
SQL> SELECT name, dbid FROM v$database;
NAME DBID
--------- ----------
UATDB 1542247341
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/DEVDB/PARAMETERFILE/spfi
leuatdb.ora
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab3 dbs]$
Regards,
Mallik
No comments:
Post a Comment