ORA-01103: database name ‘MW’ in control file is not ‘MWTST’

Shadab Mohammad

6 years ago

This error most likely occurred when you duplicated a database and the DB_NAME parameter in spfile is not matching the one in the control file. Sometimes it so happened that after duplicating the database name in SPFILE with which you started the instance with is not matching the one in the control file.

Explanation:
=====================
The database name in the controlfile is not consistent with the db_name
parameter in the init.ora file.

Solution Step 1:
Change the DB_NAME parameter in spfile and bounce the database
STARTUP NOMOUNT
ALTER SYSTEM SET DB_NAME=ORIGINAL_DBNAME SCOPE=spfile;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;
If you get like below error
ORA-19838: Cannot use this control file to open database then have to recreate the control file from trace . Go to Solution 2:

See Oracle support Doc ID 1906009.1

Solution Step 2:

Now the only way to bring up the cloned DB is to re-create controlfile from trace. Login to the source database and run below commands.
SQL> alter database backup controlfile to trace;
Check location of controlfile which is created to trace; checking in ALERT LOG
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Backup controlfile written to trace file /u02/app/oracle/diag/rdbms/mw/mw1/trace/mw1_ora_146366.trc
Then Copy/Edit the Details of ControlFile from the block like below.
[code language=”sql”]
CREATE CONTROLFILE SET DATABASE “MW” RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 9344
LOGFILE
GROUP 1 (
‘+DATAC1/mwtst1/onlinelog/group_1.366.854619221’,
‘+RECOC1/mwtst1/onlinelog/group_1.504.854619221’
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
‘+DATAC1/mwtst1/onlinelog/group_2.367.854619389’,
‘+RECOC1/mwtst1/onlinelog/group_2.505.854619389’
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
‘+DATAC1/mwtst1/onlinelog/group_3.368.854619411’,
‘+RECOC1/mwtst1/onlinelog/group_3.506.854619411’
) SIZE 100M BLOCKSIZE 512,
GROUP 4 (
‘+DATAC1/mwtst1/onlinelog/group_4.365.854619175’,
‘+RECOC1/mwtst1/onlinelog/group_4.507.854619175’
) SIZE 100M BLOCKSIZE 512
DATAFILE
‘+DATAC1/mwtst1/datafile/SYSTEM.467.971185463’,
‘+DATAC1/mwtst1/datafile/SYSAUX.439.971266529’,
‘+DATAC1/mwtst1/datafile/USERS.473.971266529’,
‘+DATAC1/mwtst1/datafile/UNDOTBS1.468.971185463’,
‘+DATAC1/mwtst1/datafile/UNDOTBS2.466.971185463’,
‘+DATAC1/mwtst1/datafile/MQ.357.971266521’,
‘+RECOC1/mwtst1/datafile/AUDIT_TBS.1787.971185463′
CHARACTER SET AL32UTF8
;
[/code]
Save it as control_file.sql
Ensure you check the Datafile location and name, it should be same as in what resides on your filesystem. This is specially true when you using ASM and OMF together.

Now start the instance

SQL> startup nomount
SQL> @control_file.sql
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
——————–
READ WRITE
SQL> select open_mode,name from v$database;
OPEN_MODE NAME
——————– ———
READ WRITE MW
SQL> select * from v$controlfile;
STATUS
——-
NAME
——————————————————————————–
IS_ BLOCK_SIZE FILE_SIZE_BLKS
— ———- ————–
+RECOC1/mw/controlfile/current.2156.971360621
YES 16384 1878
Now change the database name in control file using nid utility
nid TARGET=system/****** DBNAME=MWTST SETNAME=Y

DBNEWID: Release 11.2.0.4.0 – Production on Wed Mar 21 14:27:18 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database MW (DBID=1312370394)
Connected to server version 11.2.0
Control Files in database:
+RECOC1/mw/controlfile/current.2156.971360621
Change database name of database MW to MWTST? (Y/[N]) => Y
Proceeding with operation
Changing database name from MW to MWTST
Control File +RECOC1/mw/controlfile/current.2156.971360621 – modified
Datafile +DATAC1/mwtst1/datafile/system.467.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/sysaux.439.97126652 – wrote new name
Datafile +DATAC1/mwtst1/datafile/undotbs1.468.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/users.473.97126652 – wrote new name
Datafile +DATAC1/mwtst1/datafile/undotbs2.466.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/mq.357.97126652 – wrote new name
Datafile +RECOC1/mwtst1/datafile/audit_tbs.1787.97118546 – wrote new name
Control File +RECOC1/mw/controlfile/current.2156.971360621 – wrote new name
Instance shut down
Database name changed to MWTST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.

Change PARAMETER in PFILE or in Memory
SQL> alter system set db_name=’MWTST’ scope=spfile;
System altered.
SQL> show parameter cluster
NAME TYPE VALUE
———————————— ———– ——————————
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string

SQL> alter system set db_unique_name=’MWTST’ scope=spfile;
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database open;
Database altered.
SQL> select open_mode,name from v$database;
OPEN_MODE NAME
——————– ———
READ WRITE MWTST

Share this: