ORA-01503: CREATE CONTROLFILE failed with ORA-01192

There could be several reasons for facing ORA-01503 while creating a new controlfile. When we plan to create a controlfile, we can write our own script, but creating a script using “ALTER DATABASE BACKUP CONTROLFILE TO TRACE” command generates a perfect script for us and we do not need to write script ourselves. In the following I will discuss a scenario where I ORA-01503 and how I solved it.

I wanted to open a test database using “WITH RESETLOGS” option. Database was created using duplicate command. Opening database was failing because one of my redo log group member was showing partial path of ASM diskgorup (not full path of redo log member).

alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00392: log 1 of thread 1 is being cleared, operation not allowed ORA-00312: online log 1 thread 1: '+fra' SQL>select member,group# from v$logfile; MEMBER                                                                                               GROUP# ------------------------------------------------------------------------------------------------------ +FRA                                                                                                         1  +DATA/mcrspos/onlinelog/group_2.302.946118381                              2  +DATA/mcrspos/onlinelog/group_3.276.946118357                              3

ORA-01623, ORA-00312, ORA-00360

I tried to drop this group, but status of this group was clearing so I was not able to drop this group.

alter database drop logfile group 1; alter database drop logfile group 1 * ERROR at line 1: ORA-01623: log 1 is current log for instance mcrspos (thread 1) - cannot drop ORA-00312: online log 1 thread 1: '+orafra'

I added a new member to group 1 so that I can drop this member, but it also did not work.

SQL> alter database drop logfile member '+fra'; alter database drop logfile member '+fra' * ERROR at line 1: ORA-00360: not a logfile member: +fra

As a workaround, I thought to recreate controlfile by omitting the logfile group 1 while creating the controlfile. I backed up the control to trace and then executed the create controlfile script, but this time it failed to create controlfile with error ORA-01503.

CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 192 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 32 MAXLOGHISTORY 1168 LOGFILE GROUP 2 '+DATA/mydb/onlinelog/group_2.302.946118381'  SIZE 100M BLOCKSIZE 512, GROUP 3 '+DATA/mydb/onlinelog/group_3.276.946118357'  SIZE 100M BLOCKSIZE 512 DATAFILE '+DATA/mydb/datafile/system.284.946075827', … … … CHARACTER SET UTF8; ERROR: ORA-06550: line 1, column 29: PLS-00553: character set name is not recognized ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS FORCE LOGGING ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01192: must have at least one enabled thread

To solve this, I changed “NORESETLOGS FORCE LOGGING ARCHIVELOG” to “RESETLOGS“ in the first line of the script. After this change, I re-executed create controlfile script and it worked like a charm and controlfile got created. There was no redo log group in the DB this time and I was also able to open database with RESETLOG option successfully.