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

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

 

 

 

 

 

Delete Duplicate Rows Oracle

Check for Duplicate Row using Group By function

This script deletes each row in the group after the first row.

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3… ;

delete FROM TXN_TABLE
WHERE rowid not in
(SELECT MIN(rowid)
FROM TXN_TABLE
GROUP BY TRNX_UID);

select COUNT(*) FROM TXN_TABLE
WHERE rowid not in
(SELECT MIN(rowid)
FROM TXN_TABLE
GROUP BY TRNX_UID);