How to change DBNAME on Oracle RAC

André Luiz Dutra Ontalba publicado leitura 5 min cost 1,150 palavras

Change Oracle DB name using NID: How to Change the DBID, DBNAME Using NID Utility (Doc ID 863800.1)

1. In Real Application Cluster, we need to set cluster_database parameter to false and mount an instance on only one node:

sqlplus / as sysdba
alter system set cluster_database=false scope=spfile;


oracle@db01:~# . oraenv
ORACLE_SID = [DUTSDB] ? DUTSPD
The Oracle base has been changed from /opt/Oracle/app/oracle to /opt/Oracle/database
oracle@db01:~#
oracle@db01:~#
oracle@db01:~#
oracle@db01:~#
oracle@db01:~#
oracle@db01:~# sqlplus / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DUTSPD

SQL> exit

oracle@db01:~# srvctl stop database -d dutspd



oracle@db01:~# sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.6435E+10 bytes
Fixed Size                  2242304 bytes
Variable Size            8925481216 bytes
Database Buffers         7482638336 bytes
Redo Buffers               24707072 bytes
Database mounted.
SQL> SQL>

2. From the 1st node run nid utility. Specify username with sysdba privilege, target database name, and SETNAME parameter to yes.

oracle@db01:~# clear
oracle@db01:~# ps -ef | grep pmon
  oracle  9127  5662   0   Jun 05 ?           4:28 asm_pmon_+ASM1
  oracle 11287  5662   0   Jun 05 ?          25:09 ora_pmon_DUTSDB
  oracle 16694  5662   0 12:13:19 ?           0:00 ora_pmon_DUTSPD
  oracle 17630 10739   0 12:15:05 pts/4       0:00 grep pmon
oracle@db01:~# . oraenv
ORACLE_SID = [DUTSDB] ? DUTSPD
The Oracle base remains unchanged with value /opt/Oracle/database
oracle@db01:~#



oracle@db01:~# nid TARGET=SYS DBNAME=DUTSPB

DBNEWID: Release 19.16.0.0.0 - Production on Mon Dec 28 12:17:04 2022

Password:
Connected to database DUTSPD (DBID=2319832658)

Connected to server version 19.16.0

Control Files in database:
    +DATA/dutspd/controlfile/current.265.1075567891
    +RECO/dutspd/controlfile/current.317.1075567891

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

Proceeding with operation
Changing database ID from 2319832658 to 2596561288
Changing database name from DUTSPD to DUTSPB
    Control File +DATA/dutspd/controlfile/current.265.1075567891 - modified
    Control File +RECO/dutspd/controlfile/current.317.1075567891 - modified
    Datafile +DATA/dutspd/datafile/system.343.107556773 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/sysaux.346.107556773 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/undotbs1.336.107556773 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/users.335.107556773 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/undotbs2.327.107556790 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcsys_data.342.107640472 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcsys_index.348.107640472 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/learch_tbs.337.107640472 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip2.338.107640472 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/cfnweb.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip_qweeby.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip_lbbw.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip_eptca.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcbws.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip_learch_acceptance.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip_editus.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcsys_train_data.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcsys_train_index.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip_bil.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcip_test.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcep_test.db - dbid changed, wrote new name
    Datafile +DATA/dutspd/tempfile/temp.270.107556789 - dbid changed, wrote new name
    Datafile +DATA/dutspd/datafile/arcsys_train_temp.db - dbid changed, wrote new name
    Control File +DATA/dutspd/controlfile/current.265.1075567891 - dbid changed, wrote new name
    Control File +RECO/dutspd/controlfile/current.317.1075567891 - dbid changed, wrote new name
    Instance shut down

Database name changed to DUTSPB.
Modify parameter file and generate a new password file before restarting.
Database ID for database DUTSPB changed to 2596561288.
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@db01:~#

3. Change db_name parameter in the initialization parameter file:

oracle@db01:~# . oraenv
ORACLE_SID = [DUTSPD] ? DUTSPB
The Oracle base remains unchanged with value /opt/Oracle/database
oracle@db01:~# sqlplus / as sysdba
Connected to an idle instance.

SQL> startup nomount;
alter system set db_name=DUTSPB scope=spfile;
alter system set cluster_database=true scope=spfile;
shut immediate;
ORACLE instance started.

Total System Global Area 1.6435E+10 bytes
Fixed Size                  2242304 bytes
Variable Size            8925481216 bytes
Database Buffers         7482638336 bytes
Redo Buffers               24707072 bytes
SQL>
System altered.

SQL>
System altered.

SQL> ORA-01507: database not mounted



ORACLE instance shut down.
SQL> SQL>


oracle@db01:dbs# sqlplus / as sysdba
Connected to an idle instance.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1.6435E+10 bytes
Fixed Size                  2242304 bytes
Variable Size            8925481216 bytes
Database Buffers         7482638336 bytes
Redo Buffers               24707072 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL>

4. Remove existing password file entry and create a new one in both nodes:

oracle@db01:db_home_1# cd $ORACLE_HOME
oracle@db01:db_home_1# cd dbs
oracle@db01:dbs# orapwd  file=orapwDUTSPB

Enter password for SYS:
oracle@db01:dbs#

5.  Change the service of the database
oracle@db01:dbs# sqlplus / as sysdba


SQL> alter system set db_unique_name=DUTSPB scope=spfile sid='*';

System altered.

SQL>


oracle@db01:dbs#  srvctl add database -d DUTSPB -o /opt/Oracle/database/product/19.0.0/db_home_1 -r primary -s OPEN -p '+DATA/DUTSPB/spfileDUTSPB.ora' -n db01
oracle@db01:dbs# srvctl add instance -d DUTSPB -i DUTSPB1 -n db01
oracle@db01:dbs# srvctl add instance -d DUTSPB -i DUTSPB2 -n db02
oracle@db01:dbs#

oracle@db01:dbs# srvctl config database -d DUTSPB
Database unique name: DUTSPB
Database name: DUTSPB
Oracle home: /opt/Oracle/database/product/19.0.0/db_home_1
Oracle user: oracle
Spfile: +DATA/DUTSPB/spfileDUTSPB.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: DUTSPB
Database instances: DUTSPB1, DUTSPB2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
oracle@db01:dbs#


oracle@db01:dbs# . oraenv
ORACLE_SID = [+ASM1] ? DUTSPB
The Oracle base remains unchanged with value /opt/Oracle/database
oracle@db01:dbs#
oracle@db01:dbs# sqlplus / as sysdba
Connected to an idle instance.

SQL>  startup mount pfile=/opt/Oracle/database/product/19.0.0/db_home_1/dbs/initDUTSPB.ora

SQL> create spfile='+DATA/DUTSPB/ spfileDUTSPB.ora' from pfile='/opt/Oracle/database/product/19.0.0/db_home_1/dbs/initDUTSPB.ora;
File created.
SQL>exit

oracle@db01:dbs# srvctl start database -d  DUTSPB


Hope this helps you!!!

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful

André Luiz Dutra Ontalba

DBA ATG Oracle há 27 anos, especialista em Banco de Dados Oracle e SQL SERVER, MySQL, Azure, AWS, Oracle Cloud. Bacharel em Ciências da Computação Pós Graduado pela FIAP em Administração de Banco de dados, MBA em Administração de TI pela FGV a MBA em Master Information Technology pela FIAP e BABSON e Mestrado em Gestão Estratégica em Tecnologias de Informação pela UNIATLANTICO e UNIB . Com sólidos conhecimentos em Banco de Dados e Sistemas operacionais, possui certificações Oracle OCP 10g/11/12g/2019 Oracle Cloud Professional, AWS Professional, Azure Expert e mais 85 outra certificações. Strategic Customer Service Manager at Oracle / Oracle ACE Alumni Member

Deixe um comentário

Your email address will not be published. Required fields are marked *

->NESTED LOOPSmesma categoria

Continue lendo