Rename Oracle database using NID

 SQL> set pages 999 lines 999;
col instance_name for a10;
col host_name for a20;
col startup_time for a30;
set colsep |
set underline =
select instance_name,host_name,name,open_mode,log_mode,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') startup_time,version
from v$database, v$instance;SQL> SQL> SQL> SQL> SQL> SQL>   2

INSTANCE_N|HOST_NAME           |NAME     |OPEN_MODE           |LOG_MODE    |STATUS      |STARTUP_TIME                  |VERSION
==========|====================|=========|====================|============|============|==============================|=================
bihar     |oel7.malam.com      |BIHAR    |READ WRITE          |ARCHIVELOG  |OPEN        |20-mar-2022 22:26:58          |12.1.0.2.0

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

Total System Global Area|1593835520|bytes
Fixed Size              |   2924880|bytes
Variable Size           | 503320240|bytes
Database Buffers        |1073741824|bytes
Redo Buffers            |  13848576|bytes
Database mounted.
SQL>
SQL>
SQL>
SQL> set pages 999 lines 999;
col instance_name for a10;
col host_name for a20;
col startup_time for a30;
set colsep |
set underline =
select instance_name,host_name,name,open_mode,log_mode,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') startup_time,version
from v$database, v$instance;SQL> SQL> SQL> SQL> SQL> SQL>   2

INSTANCE_N|HOST_NAME           |NAME     |OPEN_MODE           |LOG_MODE    |STATUS      |STARTUP_TIME                  |VERSION
==========|====================|=========|====================|============|============|==============================|=================
bihar     |oel7.malam.com      |BIHAR    |MOUNTED             |ARCHIVELOG  |MOUNTED     |20-mar-2022 23:11:57          |12.1.0.2.0

SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oel7 u01]$
[oracle@oel7 u01]$
[oracle@oel7 u01]$ env | grep ORACLE
ORACLE_SID=bihar
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0
[oracle@oel7 u01]$
[oracle@oel7 u01]$
[oracle@oel7 u01]$ nid target=sys dbname=patna

DBNEWID: Release 12.1.0.2.0 - Production on Sun Mar 20 23:12:57 2022

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

Password:
Connected to database BIHAR (DBID=2907458956)

Connected to server version 12.1.0

Control Files in database:
    /u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl
    /u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl

Change database ID and database name BIHAR to PATNA? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2907458956 to 1164850626
Changing database name from BIHAR to PATNA
    Control File /u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl - modified
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_system_jt3q4j9v_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_sysaux_jt3q2h3d_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_undotbs1_jt3q797l_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_system_jt3q9t24_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_users_jt3q75kc_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_sysaux_jt3q9t13_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_system_jt3r6bcc_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_sysaux_jt3r6bcp_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_users_jt3r7wvk_.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/cdb_data_files_o1.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/pdb_data_files_o1.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_temp_jt3q9nq4_.tm - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/datafile/pdbseed_temp012021-11-27_12-55-37-PM.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_temp_jt3r6bcr_.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl - dbid changed, wrote new name
    Instance shut down

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

Make entry in /etc/oratab for patna
create pfile for patna db at /u01/app/oracle/product/12.1.0/dbs/initpatna.ora

cat $ORACLE_HOME/dbs/initpatna.ora
*.audit_file_dest='/u01/app/oracle/admin/patna/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl','/u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='patna'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=patnaXDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=502m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1506m
*.undo_tablespace='UNDOTBS1'


[oracle@oel7 u01]$
[oracle@oel7 u01]$ . oraenv
ORACLE_SID = [bihar] ? patna
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel7 u01]$
[oracle@oel7 u01]$ env | grep ORACLE
ORACLE_SID=patna
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0
[oracle@oel7 u01]$

[oracle@oel7 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 20 23:20:12 2022

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

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initpatna.ora';
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size                  2924880 bytes
Variable Size             503320240 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
SQL>
SQL>
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initpatna.ora';

File created.

SQL> shu immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size                  2924880 bytes
Variable Size             503320240 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> set pages 999 lines 999;
col instance_name for a10;
col host_name for a20;
col startup_time for a30;
set colsep |
set underline =
select instance_name,host_name,name,open_mode,log_mode,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') startup_time,version
from v$database, v$instance;SQL> SQL> SQL> SQL> SQL> SQL>   2

INSTANCE_N|HOST_NAME           |NAME     |OPEN_MODE           |LOG_MODE    |STATUS      |STARTUP_TIME                  |VERSION
==========|====================|=========|====================|============|============|==============================|=================
patna     |oel7.malam.com      |PATNA    |READ WRITE          |ARCHIVELOG  |OPEN        |20-mar-2022 23:24:13          |12.1.0.2.0

SQL>

How to add new and drop old online redo log;

SET PAGES 999 LINES 999;
COL INSTANCE_NAME FOR A10;
COL HOST_NAME FOR A20;
COL STARTUP_TIME FOR A30;
SET COLSEP |
SET UNDERLINE =
SELECT INSTANCE_NAME,HOST_NAME,NAME,OPEN_MODE,LOG_MODE,STATUS,TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') STARTUP_TIME,VERSION
FROM V$DATABASE, V$INSTANCE;

INSTANCE_N|HOST_NAME      |NAME     |OPEN_MODE   |LOG_MODE    |STATUS  |STARTUP_TIME         |VERSION
==========|===============|=========|============|============|========|=====================|==========
bihar     |oel7.malam.com |BIHAR    |READ WRITE  |ARCHIVELOG  |OPEN    |20-MAR-2022 22:26:58 |12.1.0.2.0



SET PAGES 999 LINES 999;
COL MEMBER FOR A80;
SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_     CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
         3         ONLINE  /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_3_jt3q9gpd_.log                    NO           0
         3         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_3_jt3q9gsn_.log         YES          0
         2         ONLINE  /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_2_jt3q9dvr_.log                    NO           0
         2         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_2_jt3q9dys_.log         YES          0
         1         ONLINE  /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_1_jt3q9blf_.log                    NO           0
         1         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_1_jt3q9btr_.log         YES          0

6 rows selected.

SQL> select group#, sequence#, bytes, members, status from v$log;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
         1         25   52428800          2 CURRENT
         2         23   52428800          2 INACTIVE
         3         24   52428800          2 INACTIVE

SQL> alter database add logfile group 4
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo') size 10M;  2    3

Database altered.

SQL> alter database add logfile group 5
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo') size 10M;  2    3

Database altered.

SQL> alter database add logfile group 6
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo') size 10M;  2    3

Database altered.

SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_     CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
         3         ONLINE  /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_3_jt3q9gpd_.log                    NO           0
         3         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_3_jt3q9gsn_.log         YES          0
         2         ONLINE  /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_2_jt3q9dvr_.log                    NO           0
         2         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_2_jt3q9dys_.log         YES          0
         1         ONLINE  /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_1_jt3q9blf_.log                    NO           0
         1         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_1_jt3q9btr_.log         YES          0
         4         ONLINE  /u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo                             NO           0
         4         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo                  NO           0
         5         ONLINE  /u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo                             NO           0
         5         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo                  NO           0
         6         ONLINE  /u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo                             NO           0
         6         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo                  NO           0

12 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select group#, sequence#, bytes, members, status,ARCHIVED from v$log;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS           ARC
---------- ---------- ---------- ---------- ---------------- ---
         1         31   52428800          2 INACTIVE         YES
         2         29   52428800          2 INACTIVE         YES
         3         30   52428800          2 INACTIVE         YES
         4         32   10485760          2 CURRENT          NO
         5         27   10485760          2 INACTIVE         YES
         6         28   10485760          2 INACTIVE         YES

6 rows selected.


SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SQL> select group#, sequence#, bytes, members, status,ARCHIVED from v$log;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS           ARC
---------- ---------- ---------- ---------- ---------------- ---
         4         32   10485760          2 CURRENT          NO
         5         27   10485760          2 INACTIVE         YES
         6         28   10485760          2 INACTIVE         YES

SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_     CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
         4         ONLINE  /u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo                             NO           0
         4         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo                  NO           0
         5         ONLINE  /u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo                             NO           0
         5         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo                  NO           0
         6         ONLINE  /u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo                             NO           0
         6         ONLINE  /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo                  NO           0

6 rows selected.