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

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.