Wednesday, June 10, 2015

ORA-1166 file number 101 is larger than MAXDATAFILES (100)

I am in the process of upgrading EBS R12 DB from 11.2.0.3 to 11.2.0.4 using DBUA. The end of the upgrade, DBUA fails with following error message.

During the DBUA, I have selected option 'move the database files location'.

ORA-1503: CREATE CONTROLFILE failed
ORA-1166: file number 101 is larger than MAXDATAFILES (100)
ORA-1110: data file 101: '/u01/EBS/data/opmor01.dbf'

So then I have verified oracle documentation and support docs. There is a bug in DBUA, the problem is Database Upgrade Assistant (DBUA) tries to re-create the control file with MAXDATAFILES set to 100 instead of using the value from the old control file.

To fix this issue one of the following workarounds should be used:

1) Upgrade the database using DBUA (without moving the database files option) and If you are using ASM, after completed the upgrade use RMAN to move the database files to ASM.
2) Manually upgrade the database and manually re-create the control file (setting MAXDATAFILES to a higher value).

Thats it..Hope this will helps you..:)

Thanks,
Chowdari.

Thursday, June 4, 2015

Find your ORACLE session ID number

In Oracle, it provides a view V$SESSION to display all current session details. There are other several queries you can use to see your own session ID.

Find below queries to get your own session ID:

select sys_context('USERENV','SID') SESSION_ID from dual;

select distinct sid from v$mystat;


select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;


select sid from v$mystat where rownum <=1;


Sample Outputs:

SQL> select sys_context('USERENV','SID') SESSION_ID from dual;

SESSION_ID

--------------------------------------------------------------------------------
283

SQL>


SQL> select sid from  v$mystat where  rownum <=1;


       SID

----------
       283

SQL> select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;


     MYSID

----------
       283

SQL> select distinct sid from v$mystat;


       SID

----------
       283

SQL>



Thanks,
Chowdari

Some Most Popular Articles