Thursday, 7 September 2017

How to add datafiles to Tablespace in realtime scenario

Hey DBA-mates,
Welcome Again in Oracle Hub.
Adding a data file in Tablespace is a one of the daily task for any dba in any environments or corporations.

And for this also, I have seen almost DBA will search in Google for command and check for the command and process. But during interviews same people will the question about please tell the command for adding datafiles in tablespace.

One thing I don’t understand if a DBA have to search in Google during adding datafiles then why we need to remember and why we should give answer in interviews hehehhehhh.
But boss that is like a process which we can’t change. :D

I have very simple steps to add datafiles in tablespace which will be very important for DBA especially fresher. If they have joined any company and they are not sure about the stuff, they can refer it.

Do you know one reality, even a senior or junior joined any New corp./company, even they know the dba stuff but still they will cross check in Google or with some other colleague and there is nothing wrong in that.
Asking is better than doing mistake and also takes it as positive if you are doing mistake means you are working or learning hehehe yessssss. 
Even I am known for asking the stuff but if never ask twice same things.

Now let’s move to point:
Please follow the below points to add datafiles:

Check for Pre-Check Tablespace details from below command:

select a.tablespace_name,,
       c.available, d.large_extent,
       ( - c.available) used,
       round(((( - c.available)*100)/,2) pctused
  from (select tablespace_name, (sum(bytes)/1024)/1024 total
          from dba_data_files group by tablespace_name)  b,
       (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available
          from dba_free_space group by tablespace_name)  c,
       (select tablespace_name, (max(bytes)/1024)/1024 large_extent
          from dba_free_space group by tablespace_name)  d,
       dba_tablespaces  a
 where a.tablespace_name = b.tablespace_name(+)
   and a.tablespace_name = c.tablespace_name(+)
   and a.tablespace_name = d.tablespace_name(+)
   and a.tablespace_name = upper ('&tablespace')

Output Sample:

Enter value for tablespace: SKU
old  15:    and a.tablespace_name = upper ('&tablespace')
new  15:    and a.tablespace_name = upper ('SKU')

ORA-DATA - Tablespace Usage Report                              September 04, 2017

                          Total  Available  Available       Used Percent
Tablespace                   MB         MB     Extent         MB    Used
-------------------- ---------- ---------- ---------- ---------- -------
SKU                    306,742     43,845        412    262,897   85.71
                     ----------                       ----------
sum                     306,742                          262,897

Now check for all the datafiles for a given tablespace.
SQL>set verify off

col file_id   heading ' File ID'   format 999999
col file_name heading ' File Name' format a50
col MB        heading 'Megabytes'  format 999999

select file_id, file_name, sum(bytes)/1024/1024 MB
  from dba_data_files
 where tablespace_name = upper('&tblspace')
 group by file_id, file_name

set verify on

Output Sample:
Enter value for tblspace: SKU

 File ID  File Name                                         Megabytes
-------- -------------------------------------------------- ---------
     600 /ora-data/SKU/data19/sku101.dbf                    1024
     289 /ora-data/SKU/data21/sku102.dbf                     1024

Check for space available in directory where you are adding datafile.
Check for last name of datafile and add datafile name which is not in list.

Example: In my list, last datafile is 102. So, I will add 103 datafile as shown below:
SQL> show user

SQL> alter tablespace SKU  add datafile '/ora-data/SKU/data21/sku103.dbf ' size 1024 M;

Again Check for Tablespace details and space available from above command. 

Hope this may useful and helpful. Please let us know for any concerns either in comment box or contact

Some more important Points:

ora-data Team.

Thursday, 31 August 2017

oacore not starting up troubleshooting tips

Hi DBA-mates,
Welcome back you all again. I would like to share one more real time environments experience which we faced recently in our environment.
Today, I am not telling about any ora-error or any particular things, just this is troubleshooting and sharing the real time experience.

I am sharing this because this may helpful in your interview also.
oacore is not starting or coming up after bounce the services.
We applied a patch and after that we started the services but oacore was not starting, even we tried the services one by one it was not starting.

We checked the logs but there was nothing in services start log. Then we checked for oacore log which is not easy to find out for any fresher or the people who did not worked in r12.

We checked in Google then we found about to persistence directory in which we have to rename or remove some files. We tried but this also did not work.
rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

Then again we reverted back the files at same places.
Then we checked others some log which we got from below command. Easy step to find out the log:

$echo $LOG_HOME

Then you can follow the path and error log.
After that we found out that there was issue with server.xml file and there was only a space which causes the issue and we were not able to start the services properly.

Then we remove the space and junk character from the file and saved it. And again we started the services which got up all the services without any issue.

Hope this may useful and helpful. Please let us know for any concerns or issue either in comment box or contact us

Some more useful links:

ora-data Team.

Saturday, 19 August 2017

ora-01578: oracle data block corrupted Fix and Solution

Hi Friends,
We faced this below issue which is very critical and tricky also.
After cloning issue:
Self service url was not working
The only visible message was:

You have encountered an unexpected error. Please contact the System Administrator for assistance."

1. Then we changed user’s password but still not working.
2. Checked db alert log, but didn’t get any no clue.
3. Checked apache error log, access log and jserv log but nothing found.

The error message is below. : file # 368, block # 29907
Exception:oracle.apps.fnd.cache.CacheException at oracle.apps.fnd.cache.AppsCache.get( at oracle.apps.fnd.functionSecurity.User.getUser( at oracle.apps.fnd.functionSecurity.FunctionSecurity.getUser( at oracle.apps.fnd.functionSecurity.FunctionSecurity.init( at oracle.apps.fnd.functionSecurity.FunctionSecurity.( at oracle.apps.fnd.sso.SSOUtil.getStartPageUrl( at oracle.apps.fnd.sso.SessionMgr.createAppsSession( at _oa__html._fndvald._jspService( at oracle.jsp.runtime.HttpJsp.service( at at oracle.jsp.JspServlet.doDispatch( at oracle.jsp.JspServlet.internalService( at oracle.jsp.JspServlet.service( at javax.servlet.http.HttpServlet.service( at org.apache.jserv.JServConnection.processRequest( at at Caused by: oracle.apps.jtf.base.resources.FrameworkException: Failure in CacheLoader: oracle.apps.jtf.base.resources.FrameworkException at oracle.apps.jtf.cache.IASCacheProvider.get( at oracle.apps.jtf.cache.CacheManager.getInternal( at oracle.apps.jtf.cache.CacheManager.get( at oracle.apps.fnd.cache.AppsCache.get( ... 16 more Caused by: oracle.apps.jtf.base.resources.FrameworkException at oracle.apps.fnd.cache.GenericCacheLoader.load( at oracle.apps.fnd.cache.GenericCacheLoader.load( at oracle.apps.fnd.cache.GenericCacheLoader.load( at oracle.apps.fnd.cache.GenericCacheLoader.load( at oracle.apps.jtf.cache.GenericCacheLoader.load( at oracle.ias.cache.CacheLoader.callLoad(Unknown Source) at oracle.ias.cache.CacheHandle.findObject(Unknown Source) at oracle.ias.cache.CacheHandle.locateObject(Unknown Source) at oracle.ias.cache.CacheAccess.get(Unknown Source) at oracle.apps.jtf.cache.IASCacheProvider.get( ... 19 more Caused by: oracle.apps.jtf.base.resources.FrameworkException: ORA-01578: ORACLE data block corrupted (file # 368, block # 29907) ORA-01110: data file 368: '/ora-data/sku/data2/applsysd09.dbf' ORA-26040: Data block was loaded using the NOLOGGING option   at oracle.apps.jtf.base.resources.FrameworkException.convertException( at oracle.apps.jtf.base.resources.FrameworkException.addException( at oracle.apps.jtf.base.resources.FrameworkException.( at oracle.apps.jtf.base.resources.FrameworkException.( at oracle.apps.jtf.base.resources.FrameworkException.( at oracle.apps.jtf.base.resources.FrameworkException.( at oracle.apps.jtf.base.resources.FrameworkException.( ... 29 more
Then checked for WF related tables
The below error comes if we query the below tables
SQL> select count(1) from WF_LOCAL_USER_ROLES;
select count(1) from WF_LOCAL_USER_ROLES
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 151, block # 82209)
ORA-01110: data file 2197: '/oradata/sku/data20/applsysd47.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Solution and Work-around:
Check details of the block which table it belongs.

select SEGMENT_NAME,SEGMENT_TYPE,BLOCK_ID,OWNER,TABLESPACE_NAME from dba_extents where BLOCK_ID like '29907' and BLOCK_ID like '82209';



Run the dbv utility from OS prompt

$ dbv blocksize=8192  file=/oradata/sku/data2/applsysd09.dbf feedback=100

$ dbv blocksize=8192  file=/oradata/sku/data20/applsysd47.dbf feedback=100

Partition of the table WF_LOCAL_USER_ROLES will have problem. This Table partion will be having "LOGGING" column value "NO" which should be actually "YES".
Change the same.

Table altered.

Create the necessary auxiliary tables for identifying corrupt blocks in the table in question:

TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,

PL/SQL procedure successfully completed.

SQL> desc repair_table
SQL> var c number
SQL> exec dbms_repair.check_object(schema_name=>'APPLSYS',

PL/SQL procedure successfully completed.

SQL> select :c from dual;



PL/SQL procedure successfully completed.

SQL> exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( SCHEMA_NAME => 'APPLSYS', OBJECT_NAME => 'WF_LOCAL_USER_ROLES', OBJECT_TYPE => dbms_repair.table_object, FLAGS => dbms_repair.skip_flag);

PL/SQL procedure successfully completed.

SQL> select count(1) from applsys.WF_LOCAL_USER_ROLES;
Check front end login for the affected users.

Some more useful link: