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

 

 

 

 

 

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

This error normally occurs when you are trying to import a dump file which is residing on an NFS file system or you are copying a file from NFS filesystem onto ASM. The way to resolve the problem is to mount the NFS filesystem with the correct options.

1. Unmount the NFS file system where the file is residing

[sourcecode language=”sql”] umount -f /filesystem [/sourcecode]

2. Mount with correct options

[sourcecode language=”sql”]  mount -F nfs -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,forcedirectio,nointr,proto=tcp,suid  host:/folder1/to1 /folder2/to2 [/sourcecode]

Now try importing the dump/ copying the file to ASM again and it should work like a charm.

ORA-29701: unable to connect to Cluster Synchronization Service

Problem
——
Error on starting ASM from SQLPLUS or ASMCMD

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL> exit
Disconnected

Solution
——–

As Grid User

$ crsctl start res ora.cssd
CRS-2672: Attempting to start ‘ora.cssd’ on ‘prmdb’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘prmdb’
CRS-2676: Start of ‘ora.diskmon’ on ‘prmdb’ succeeded

$ asmcmd
Connected to an idle instance.
ASMCMD> startup
ORA-00099: warning: no parameter file specified for ASM instance

ASM instance started

Total System Global Area 317333504 bytes
Fixed Size 2221120 bytes
Variable Size 289946560 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

$ sqlplus “/as sysasm”

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 15 12:53:09 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Automatic Storage Management option

SQL> select name,state from v$asm_diskgroup;

NAME STATE
—————————— ———–
DATA MOUNTED

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

bash-3.2$ impdp system/***** directory=import_dir nologfile=y dumpfile=exp_swx_20092012.dmp full=y

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/USwitch/exportdump/exp_swx_20092012.dmp" for read
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 18

While trying to import a dumpfile from an NFS mount point via impdp in Oracle 11g 11.2.0.3 you get below error

Solution : I solved this issue using a dual approach. the 2nd step is one I got from searching on the internet but the directory permission i discovered after a lot of trial and error methods.

1. Give full permission to the import directory from OS. Suppose the directory which you created from the database was called import_dir . Then check location of directoru from view dba_directories and give full permission to it.

SQL> select * from dba_directories;

$ chmod 777 /USwitch/exportdump

Unmount and Mount the NFS file system again.

2. Set below parameter in init.ora file and then bounce database

event=’10298 trace name context forever, level 32′

Now try import again. If running RAC use nologfile=y parameter

bash-3.2$ impdp system/sys123 directory=import_dir nologfile=y dumpfile=exp_swx_20092012.dmp full=y

ORA-00257 archiver error. Connect internal only, until freed

// *Cause: The archiver process received an error while trying to archive
// a redo log. If the problem is not resolved soon, the database
// will stop executing transactions. The most likely cause of this
// message is the destination device is out of space to store the
// redo log file.
// *Action: Check archiver trace file for a detailed description
// of the problem. Also verify that the
// device specified in the initialization parameter
// ARCHIVE_LOG_DEST is set up properly for archiving.

This error occurs when the archive log destination has got full and archiver process cannot flush redo log file to disk. The database will not be able to execute any transactions. The best way is to take backup of the archivelogs and free the disk space where archivelogs reside.

Delete large number of records Oracle

When a large number of records neds to be deleted from a table. If you do a normal delete it will consume the UNDO tablespace. To avoid such a scenario in a production environment. You can use below procedure to commit frequently. In my opinion it is better to schedule the procedure as a daily job.

declare

— commit every ‘i_commit’ rows
i_commit pls_integer := 1000;

— row counter, ‘i_rowcount’ rows deleted
i_rowcount pls_integer := 0;

begin

— define ‘infinite’ loop
loop

— delete ‘i_commit’ rows, name of table and other criteria
delete from obj
where rownum <= i_commit and dd_date < sysdate-3;

i_rowcount := i_rowcount + sql%rowcount;

— now it's time to exit the loop
if sql%rowcount = 0 then
commit;
exit;
end if;

commit;

end loop;

— feedback
dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) ||

' rows deleted') ;

end;
/

ORA-00980: synonym translation is no longer valid

This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been dropped or deleted. You can check for the invalid synonyms using below query.

select * from dba_synonyms s

where table_owner not in(‘SYSTEM’,’SYS’)

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

 

Solution:

Replace the synonym with the name of the object it references or re- create the synonym so that it refers to a valid table, view, or synonym.

The Invalid synonyms must be dropped and recreated with the right owner. I normally do this using below query.(In this example the owner of the synonym was dropped and I am altering the synonym to reference the new owner)

DROP PUBLIC SYNONYM SYNONYM_NAME;
CREATE PUBLIC SYNONYM SYNONYM_NAME FOR NEW_OWNER.OBJECT_NAME;

ORA-38753: Cannot flashback data file ; no flashback log data. ORA-01110: data file

CAUTION !!!! DO NOT DO THIS ON YOUR PRIMARY. IF YOU DO THEN YOU WOULD REQUIRE MEDIA RECOVERY ON THE DATAFILE YOU BROUGHT OFFLINE !!!

 

If your getting this error when you converting a Snapshot Standby Database (11gR2) back to Physical Standby Database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ERROR at line 1: ORA-38753: Cannot flashback data file 4; no flashback log data. ORA-01110: data file 4: ‘+DATA/swxstandby/datafile/swx.271.780160475’

When you get this error do as below :

SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;

TS# TS_NAME                        FLA ———- ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— ———- —————————— —          1 +DATA/swxstandby/datafile/system.270.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             0 SYSTEM                         YES          2 +DATA/swxstandby/datafile/sysaux.269.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             1 SYSAUX                         YES          3 +DATA/swxstandby/datafile/undotbs1.268.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           2 UNDOTBS1                       YES          5 +DATA/swxstandby/datafile/undotbs2.267.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           5 UNDOTBS2                       YES          4 +DATA/swxstandby/datafile/swx.271.780160475                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                7 SWX                            NO          6 +DATA/swxstandby/datafile/osms.266.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               9 OSMS                           YES

6 rows selected.

See which datafile has flashback_on colmn set as NO

SQL> alter database datafile 4 offline;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— RESTORE POINT ONLY

SQL> select name, flashback_on from v$tablespace ;

NAME                           FLA —————————— — SYSTEM                         YES SYSAUX                         YES UNDOTBS1                       YES SWX                            NO TEMP                           YES UNDOTBS2                       YES OSMS                           YES

SQL> alter tablespace SWX flashback on;

Tablespace altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— NO

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE            SWITCHOVER_STATUS    DATABASE_ROLE ——————– ——————– —————- MOUNTED              RECOVERY NEEDED      PHYSICAL STANDBY

Active Session History 11g

Even when sessions are inactive they can be executing SQL’s in the background. You have to identify the SQL’s which are executing.

Run this query as sysdba user. If you are using RAC then replace v$active_session_history with gv$active_session_history

select
ash.SQL_ID ,
sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”,
sum(decode(ash.session_state,’WAITING’,1,0)) –
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “WAIT” ,
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “IO” ,
sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id ;

You will see something like

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
8tfvwyvfm5cjn          0          1          0          1
1xpfbutrrzkph          0          1          0          1
aykvshm7zsabd          0          1          0          1
92f47aa2q2rmd          0          1          0          1
92b382ka0qgdt          0          4          0          4
1h50ks4ncswfn          0          3          1          4
f99a23s9aba0z          0          0          1          1
f6cz4n8y72xdc          0          0          1          1
cvn54b7yz0s8u          0          0          1          1
3s58mgk0uy2ws          0          1          0          1
fnk7155mk2jq6          0          1          0          1

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
c2p32r5mzv8hb          0          1          0          1
5p6a1yss527ap          0          1          0          1
6gvch1xu9ca3g          0          1          0          1

Now see which statement shows maximum value in Total column. Take the SQL ID  and run below query

SELECT sql_text FROM v$sqlarea WHERE sql_id = ‘1h50ks4ncswfn’;

This will give you an SQL statement. See which table is it running on and what is it doing. Maybe it is a poorly written SQL which is locking the table or looping on the table. Do an explain plan for this statement.

SELECT * FROM table(dbms_xplan.display_awr(‘1h50ks4ncswfn’));

This way you can identify the top consuming SQL’s running inside your Oracle database. This procedure was run on a 11gR2 database but is also valid for 10gR2.

ORA-01858 a non-numeric character was found where a numeric was

Error: ORA-01858: a non-numeric character found where a digit was expected
Cause: You tried to enter a date value using a specified date format, but you entered a non-numeric character where a numeric character was expected.
Action: The options to resolve this Oracle error are:

  1. Check the date formats recognized by the to_date function. Correct the date value and retry.

Solution:

This error is mostly related to DATE column. You are either trying to insert a string in a Date column. On Windows I solved this problem in my environment using the NLS_LANG parameter in registry. Check HKLM\Software\Oracle\Home… and set the NLS_LANG parameter matching with your database character set.