Monday, April 1, 2019

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.

No comments:

Post a Comment

Popular Posts - All Times