Friday, 11 August 2017

ORA-12091 Cannot online redefine table with materialized views error Solution

Hi DBA-mates,
We are back once more with some issue and solutions step by step with some explanations.
Recently when we attempt to re-organize a table ONLINE that table contains materialized views. We used the DBMS_REDEFINITION.START_REDEF_TABLE procedure to do this, and we got an ORA-12091 "cannot online redefine table "ora-data"."SKU" with materialized views error.
ora-data = user name
SKU = table name
‘Column mapping i.e. source table and reorg table’ 

For example:

SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',  'SKU', 'INT_SKU','mapping column names');
   ERROR at line 1:
   ORA-12091: cannot online redefine table "ora-data"."SKU" with materialized views
   ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
   ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
   ORA-06512: at line 1

If you are also facing the same issue in your environments, you can check and do workaround which we have followed in our real time environments.

First we need to remove the snapshot log and materialized view which was created during failed execution of the command BMS_REDEFINITION.START_REDEF_TABLE procedure, which is mentioned above error.

Now, Check for log details as shown below commands:
SQL> select log_table from user_snapshot_logs;

Now, we will drop the Materialized view log from below querry.
SQL> drop snapshot log on SKU;  
   Materialized view log dropped.

Now, we are checking for Interim Table
SQL> select TABLE_NAME , status from user_snapshots;
TABLE_NAME                     STATUS
   ------------------------------ -------
   INT_SKU                        INVALID

SQL> select mview_name, compile_state from user_mviews;
   MVIEW_NAME                     COMPILE_STATE
   ------------------------------ -------------------
   INT_SKU                         ERROR

Here, we need to drop this also, because some data may stored from failed above command.

So, please drop the interim table also as shown below:

SQL> drop materialized view int_sku;
   Materialized view dropped.

Now, we are trying again:
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('ora-data',  'SKU', 'INT_SKU','mapping column names');

PL/SQL procedure successfully completed.

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

ora-data Team.

Tuesday, 25 July 2017

Oracle Apps DBA Realtime Interview Questions

Hey DBA-mates,
When we prepared for the oracle dba interviews, we all need guidance for preparing interviews especially when we are fresher and going for any MNC companies such as ORACLE, CISCO, IBM, TCS, WIPRO etc...

That time we need to prepare the important questions and answers which we do in our college life hheheh and we start to search in Google for important dba interviews Questions and Answers. We read that and go for interviews.

But if you are fresher then you will not understand the stuffs correctly and you will not able to answers of the questions. And in MNC they will ask as per their working experiences.

Even I also used to do same things during my struggling time. Hey these words may hurt you but it is true. It is very hard to get chance without any experiences. We have to struggle and win it. We everyone do it. Only some lucky people get chance easily.

If you really want to test yourself, Please try these below questions which are very important for especially for MNC companies. Even these questions are for experienced DBAs also.

1). Let me know the flow of application when user login?
2). What is the purpose of authentication to connect todatabase?
3). Why u need GUEST/ORACLE to connect to database?
4). What is the purpose of JSERV?
5). How to increase the performance of Apache?

6). Tell me some issues with Apache?
7). Why you need Why you need to clear that?
8). How do you increase the jvm size?
9). What is JVM?
10). If One of our dba can launch the forms and another unable to launch the forms? What will be the issue?

11). How to set trace at forms level?
12). If u have Concurrent request with pending–standby how do you troubleshoot?
13). If you have long pending requests and system become slow what you do?
14). How autoconfig works?
15). What Concurrent Tables that will cleanup when u run cmclean.sql?

16). What is PCP configuration? What is the use behind it? How to you configured?
17). What is diff between distributed AD and Shared APPL_TOP? What happens when u apply patch and distributed AD and Shared APPL_TOP?
18). What happens if you Miss Pre-Req patch and apply main patch and how to do continue?
19). How to do you skip a worker?

Hope this may useful and helpful. We have some more which we will post very soon as per real time experiences very important Questions and Answers. Please let us know for any concerns or suggestions through either comments box or Contact Us +ORA-DATA All the BEST DBA-mates.

Some more important links:


ora-data Team.

Friday, 21 July 2017

ORA-03113: end-of-file on communication channel Fixed and Troubleshooting

Hi Friends,
Recently we faced this issue which was very critical. It is very general ora alert which almost time we can ignore but sometimes it is very critical which I have mentioned below the criticality.

ORA-03113: end-of-file on communication channel
This error can occur at the time such as:
1. While we are starting the oracle database.
2. While making any connection with database.
3. While running any SQL/PLSQL queries.
4. While mounting the database.
5. While recovering the database.
6. While opening database through Alter command.
7. While connecting as SYSDBA.

This error ORA-03113: end-of-file on communication channel occurs while a client process trying to connect a oracle database.

Above alert can be because of Network issue also.
Or may be server process was killed by some other users at OS level.
This can cause other ora alerts also such as: ora-1041, ora-3114, ora-1012 etc.

These all are like disconnection of the client from oracle database.
Here, I will show you as example while startup the oracle database we faced this below issue:

SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1919
Session ID: 326 Serial number: 62214

There may be many reasons as below:
1. May be redo log file system has full; and there is 0% space free.

For this, we can clean up the old redo log file system and again startup the database.
We can move the old redo logs file system to new location file system by which there space can available and we can try to startup the database.

If above check is fine then we can check the alert log, from there we can check the exact error. And will be helpful for future investigations.

In case, If getting error during the connecting as SYSDBA then need to check below points such as:
1. Check for environments file properly.
2. Check for LD_LIBRARY_PATH entries and setting should be properly.
3. Check for correct ORACLE_SID and ORACLE_HOME path.
4. And may be needed to check some more as per alert log error and details.

And others many more may also reason... for more info: Doc ID 1506805.1
Hope these above points clear and helpful. Please let us know for any concerns and suggestions.

Some more useful links:

ora-data Team.