How to start oracle automatically after a system restart

Here I am explaining the simplest way to start oracle database and listener automatically after a restart of the server. This will be the best method to implement on your test or development server.

When you do a search on how to start oracle database automatically after a system restart, you will find out the method to start oracle database and the corresponding listener by editing the oratab file and creating scripts in init.d directory. That method is the traditional one and it will start the database along with the system reboot. It will result in a long reboot time for the server depending up on the size of your database.

In the method described here to start oracle database and listener automatically, the database startup will happen after the system reboot only. So you can do other activities on the server by the time the database gets started.

What are the steps you follow to start the database manually after a system reboot? Just write them in a file to create a script. Then run the script after reboot using crontab. This is my idea. It is very simple right?

Now, lets check how to implement this method to start the oracle database and listener automatically after every server reboot.

Just look into the example given below.
1. create a file (db.sh in /root) with the following content. (changes has to be made with respect to your database environment)

su - oracle <<EOS
export ORACLE_SID=orcl
sqlplus -s /nolog <<SQL
conn / as sysdba
startup
exit
SQL
lsnrctl start
EOS

I create the file with this content because these are the commands I use to start the database and listener manually. I have already given the ORACLE_HOME and other environment variables in the bash_profile of the user.

May the "<<EOS" will not be familiar for you, it says the commands between two EOS has to be run as oracle user. In the same way the commands between two SQL has to be run inside the sqlplus. (instead of EOS and SQL you can give any thing say.. ABC, XYZ)

OK. fine.
Now how to run this script at boot or immediately after the boot so that we can start the database and listener automatically. We have an amazing option in crontab.

execute crotab -e and add the following line to it
@reboot /bin/sh /root/db.sh

Done!!!
You have configured the database and the listener to start automatically after reboot of the server.
Restart your server to test if it is working. 

Script to rebuild all UNUSABLE indexes in oracle

Index rebuild has to be done in oracle databases for different reasons. A planned index rebuild activity can be done monthly or ewe weekly on all indexes that has a benefit after rebuild.

Read Oracle support note on how to find the candidate index to rebuild for performance benefit.

Script: Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1)

Another immediate index rebuild is to be done when there are unusable indexes in the database.
Oracle index can become unusable for many reasons. Presence of unusable index will effect DML operations in a large extend. Unusable indexes should be rebuilt immediately they are discovered.
(Read: How does an oracle index become unusable)

If there are only a few indexes unusable, We can rebuild the unusable indexes one by one manually. But is there are a lot of indexes to be rebuild, manually rebuilding each index one by one will be a time consuming activity. Also it will take longer if there are partitioned and sub-partitioned indexes.

The script given below will rebuild all indexes selected by the query
   SELECT OWNER, INDEX_NAME,  INDEX_TYPE, PARTITIONED
   FROM ALL_INDEXES 
   WHERE  STATUS = 'UNUSABLE' 
   ORDER BY 1,4;

The WHERE condition can be changed to change the candidate index set. For example if you want to rebuild all indexes from a particular schema you can give condition on the column OWNER.

Find the pl/sql script below just copy paste it to rebuild all unusable indexes including index partitions and index subpartitions in your oracle database.



SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting: UNUSABLE index rebuild');
DBMS_OUTPUT.PUT_LINE('UNPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
            SELECT OWNER,INDEX_NAME FROM ALL_INDEXES 
            WHERE STATUS='UNUSABLE' AND PARTITIONED='NO' ORDER BY 1,2
    )
    LOOP
        BEGIN
              EXECUTE IMMEDIATE 'ALTER INDEX ' || I.OWNER ||'.'|| I.INDEX_NAME || ' REBUILD' ;
              DBMS_OUTPUT.PUT_LINE('REBUILT INDEX '|| I.OWNER ||'.'|| I.INDEX_NAME );
        EXCEPTION
              WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING INDEX: '|| I.OWNER ||'.'|| I.INDEX_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('PARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME FROM ALL_IND_PARTITIONS
             WHERE STATUS='UNUSABLE' AND SUBPARTITION_COUNT=0 ORDER BY 1,2,3
                        )
    LOOP
        BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD PARTITION ' || I.PARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' PARTITION ' || I.PARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING PARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.PARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('SUBPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME FROM ALL_IND_SUBPARTITIONS
             WHERE  STATUS='UNUSABLE' ORDER BY 1,2,3
     )
     LOOP
         BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD SUBPARTITION ' || I.SUBPARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '||  I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' SUBPARTITION ' || I.SUBPARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING SUBPARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.SUBPARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP;
DBMS_OUTPUT.PUT_LINE('END: UNUSABLE index rebuild');               
END;
/

Here in the script only basic offline rebuild is included. Other clauses like ONLINE, PARALLEL, NOLOGGING etc can be given as per your requirement.

How to insert & in Oracle table example

I used to get so many calls from developers asking how to insert & into a varchar2 column in oracle?
It is not a difficult question to answer. There are many ways to insert & into a  table column in oracle database.

First of all, if you want to insert simply an &, then have you tried this?

SQL>CREATE TABLE my_table (col1 VARCHAR2(15));
SQL>INSERT INTO my_table VALUES ('&');
1 row created.

It will work perfectly!!. The problem arises when there is something written after &. For example,  When you try to insert the string  'SQL & JAVA' in oracle table, into a varchar2 column,You will get a message 'Enter value for JAVA:'
Do you know why?
Because the character & is set as the define character in oracle by default.
So it defines JAVA as a substitution variable and asks value to be substituted.
You can check it as given below.

SQL> show define
define "&" (hex 26)

Let us move into the topic of how to insert & in oracle table. You have different ways to insert & into oracle table. Here I am explaining them.

1. Put nothing after &

When there is something after &, oracle defines it as a substitution variable(because & means define), and will ask for entering a value for the variable when you execute the command.

Here, I need to insert the string 'SQL & JAVA', then how can I put nothing after &? how will I add 'JAVA' to the string?

 I will split the string into two, 'SQL &' and ' JAVA', and then I will concatenate them. as given in below example.

SQL>INSERT INTO my_table VALUES('SQL &' || ' JAVA');

1 row created.

2.Unset the define character

As I have shown above, & is the default define character. We can unset it and insert & in any oracle table.
Here is the example:

SQL> set define off

SQL>INSERT INTO my_table VALUES('SQL & JAVA');

1 row created.

SQL> set define &

The last line is added to set the define character back to the default settings after inserting the string including & into the desired column.

3. Use escape character

There is no default escape character in Oracle, So we have to define an escape character if you want to use one. You can define any character as an escape character. 

Here is the example of inserting & into an oracle table using escape character,

SQL> show escape

escape OFF

SQL> set escape \

SQL> show escape

escape "\" (hex 5c)

SQL>INSERT INTO my_table VALUES('SQL \& JAVA');

1 row created.

SQL> set escape off

4. Use ascii instead of &

This actually is an over head, but it will be helpful in some type of programs.
The ascii of & is (hex 26) or (dec 38). We can just check it out.

SQL> select chr(38) from dual;

C
-
& 

So in the statement 
INSERT INTO my_table VALUES('SQL & JAVA'); 
we will replace the & with, '||chr(38)||'
so the statement will be 
SQL>INSERT INTO my_table VALUES('SQL '||chr(38)||' JAVA'); 
1 row created.

-----------------------

Let us check whether all values in the table MY_TABLE is inserted correctly by selecting from the table.

SQL> select * from my_table;

COL1
---------------
&
SQL & JAVA
SQL & JAVA
SQL & JAVA
SQL & JAVA

So, The data were inserted correctly. We have learned how to insert & in oracle table.

If you have any other idea, Please post as comment. 

Session generating huge Archive logs

Yesterday we had an issue in our database, I was not there in office and my junior was taking care of the databases.

He was informed that one of the mount point is growing very fast and is about 90% used. The infrastructure team was afraid  of running the server out of space. He some how found out it was huge generation of archive log caused the issue. The growth rate was 1GB per minute.

As an immediate response he took a compressed backup of the archive logs, to the backup mount point from where it will be moved to tape later. He made enough room in the mount point to accommodate archive logs. The archive log generation went normal after some time. But he did not try to catch the session generating huge archive logs.

Today,  I need to find the sessions generated this much archive logs. And also the queries which was responsible for the huge archive log generation yesterday.

Well, I could have used  the query below to list the sessions and the generated redo in MB if the session is still connected. This was the query I used to use whenever I notice a huge archive log generation.
select s.username, s.osuser, s.status,s.sql_id,  sr.*  from 
  (select sid, round(value/1024/1024) as "RedoSize(MB)" 
          from v$statname sn, v$sesstat ss
          where sn.name = 'redo size'
                and ss.statistic# = sn.statistic#
          order by value desc) sr,
   v$session s
where sr.sid = s.sid
and   rownum <= 10;

This could not help me as the connected sessions listed were not responsible for huge redo generation. The session would have logged out yesterday itself.

I thought of changing the above query to list it from the dba_hist views. Well, I can get the history of v$session from dba_hist_active_sess_history. and the v$statsname is static, we just need to get the statistics name from there. But the historical v$sessstat is not there. DBA_HIST_SESSMETRIC_HISTORY is supposed to have the history but the view is not populated(!).

I googled a lot to get some hints. Searched in the Oracle support site. Got a lot of information about redo generation, redo and archive log maintenance. I have listed some of the links in the bottom.

I took an AWR report for the problematic window, and scrolled up and down many times. My eye stuck on the section  'Segments by DB Blocks Changes' where one of the segment was having very huge DB_BLOCK_CHANGES. Which means what? I got the segment which was having huge block changes. Now I just need to find out what was the SQL statement causing this segment change a lot. That will be the SQL statement caused the huge archive generation.  Then I searched in the section 'Complete List of SQL Text' of AWR report to find out the list of SQLs which refers the mentioned segment. With God's grace I could find out the query and session generated huge archive logs yesterday in our database. 

Later on I found this blog related to the same what I need. This is really helpful, He is giving queries to find out the same I did using AWR.
http://appcrawler.com/wordpress/2009/04/15/who-is-generating-all-the-redo/

Some other useful  links:
http://www.4tm.com.ar/blog/2008/oracle-howto-collect-session-history-metrics.html
Master Note: Troubleshooting Redo Logs and Archiving[Article ID 1507157.1]

SQL: How to Find Sessions Generating Lots of Redo or Archive logs

[Article ID 167492.1]

Troubleshooting High Redo Generation Issues[Article ID 782935.1]
http://4dag.cronos.be/village/dvp_forum.OpenThread?ThreadIdA=42511
http://www.orafaq.com/forum/t/180221/0/

Manual Script to compile invalid objects in Oracle

We have many methods to compile invalid objects in oracle, most of the provided methods are for bulk recompiling of the objects. Some times we may have invalid objects distributed in several schema but not all of the objects. In such cases we may have to write manual script to validate/ compile them.

Below is the script to compile all invalid objects which includes PACKAGE, PACKAGE BODY, PROCEDURE, MATERIALIZED VIEW, SYNONYM, VIEW, FUNCTION and TRIGGER.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_invobj IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   dba_objects
                  WHERE  status = 'INVALID'
                  AND    object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','MATERIALIZED VIEW','SYNONYM','VIEW','FUNCTION','TRIGGER')
                  ORDER BY 3)
  LOOP
    BEGIN
      IF cur_invobj.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_invobj.owner ||'"."' || cur_invobj.object_name || '" COMPILE BODY';
      ElSE
        EXECUTE IMMEDIATE 'ALTER ' || cur_invobj.object_type || ' "' || cur_invobj.owner || '"."' || cur_invobj.object_name || '" COMPILE';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Not Compiled: '|| cur_invobj.object_type || ' : ' || cur_invobj.owner || '.' || cur_invobj.object_name);
    END;
  END LOOP;
END;
/

At the end you will get the objects which cannot be compiled. It is not possible to compile an object if there is an error in the code. The actual errors will not be listed while compiling them.
Read: How to find compilation errors related to specific object in oracle

Note:

For invalid synonyms, a select from the synonym will compile them.

Example:

SELECT count (1) FROM my_synonym;

The syntax to compile invalid objects except package body is 

ALTER <OBJECT_TYPE> [<SCHEMA_NAME>].<OBJECT_NAME> COMPILE;

To compile package body, the syntax is,

ALTER PACKAGE [<PACKAGE_OWNER>].<PACKAGE_NAME> COMPILE BODY;

ORA-04031: unable to allocate bytes of shared memory ("","","","")

I see the following lines continuously in the alert log. 

Fri Nov 01 04:04:14 2013

Errors in file /sw/11.2.0/admin/EBSPRD_ebsprddb01/diag/rdbms/ebsprd/EBSPRD/trace/EBSPRD_q003_9998.trc:

ORA-22303: type "SYS"."AQ$_AGENT" not found

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","unknown object","KKSSP^774","kgllk")

Fri Nov 01 04:05:28 2013

Errors in file /sw/11.2.0/admin/EBSPRD_ebsprddb01/diag/rdbms/ebsprd/EBSPRD/trace/EBSPRD_j000_11170.trc:

ORA-12012: error on auto execute of job 340

ORA-04031: unable to allocate  bytes of shared memory ("","","","")

ORA-04031: unable to allocate  bytes of shared memory ("","","","")

ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","SCHEDULER$_CLASS","KKSSP^2525","kgllk")

Fri Nov 01 04:06:32 2013

Fri Aug 20 15:53:23 2010
Errors in file /opt/oracle/admin/w552pr/dump/bg/orcl102a_j000_14645.trc:
ORA-12012: error on auto execute of job 634953
ORA-04031: unable to allocate 39920 bytes of shared memory ("shared pool","DBMS_AQADM_SYS","PL/SQL MPCODE","BAMIMA: Bam Buffer")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"
ORA-06512: at line 1

Below note from the oracle support gives some idea about the same. 

Advanced Queuing Notification Callback Procedure Not Executed Due to Error - How to Retrigger the Callback Procedure [Article ID 1265138.1]

High CPU load analysis in ORACLE with OS PID

What will you do if there is a high load in in your ORACLE database server?

I am sure that you can do something if you can identify what is the oracle database session causes the load and what is the SQL being run by the session.

We can identify the operating system process id (OSPID) which consumes high CPU from the output of "top" command in Linux. You can see from the output of top, whether it is oracle sessions causes the load or something other than the database.

Below is the query/script to display top 15 sessions in the order of CPU consumption with all the session details including SQL ID and OSPID. We can compare the top output and the output of the below query to analyze what are the top CPU consuming sessions.

set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a20
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and ss.username !='SYS' and
ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc)
where rownum <16;

The above query will display only ACTIVE sessions, you can remove the given condition to get INACTIVE sessions as well. Also it has to be noted that the output of the above query will list in the order of total CPU usage by the sessions. Not by the current CPU usage by the sessions or the database sessions caused the spike. 

We can modify the above query to get all the sessions listed in the top output so that we can get the sessions which are causing the high CPU usage at the given point of time. The modified query will be like

set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a20
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and ss.username is not null
and ss.paddr=p.addr and value > 0
and p.spid in (give the PIDs from the top output separated by comma)
order by se.VALUE desc;

Now, what we have is the sessions caused high CPU load in our server. While giving the PIDs in the above modified query to find oracle sessions consuming high CPU, make sure that the PIDs are of the oracle process itself.

It is also necessary to check the details of the queries. whether the SQL statements causes high CPU utilization in the database has been running for long time? How long this query is being run? When this is expected to finish? How much percentage of the activity has been done?

We can check all these details from the view V$SESSION_LONG_OPS. Below given query can be used to do the same.

set lines 1000
COLUMN percent FORMAT 999.99 
col start_time for a20
col message for a45
SELECT sid,SERIAL#,SQL_ID,to_char(start_time,'dd-mm-yy hh24:mi:ss') start_time,
ELAPSED_SECONDS/60 Elapsed_Mins,TIME_REMAINING/60 "TIME_REMAINING_Mins" ,message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1;

Here we have SQL ID and other session details. We can compare this with the previous output for analysis.

After the analysis you may reach in a conclusion that a particular statement is responsible for the high CPU load. To find out the SQL text of that particular SQL, We can use the below given query

select sql_text from v$sqltext where sql_id ='&SQL_ID' order by piece;

I hope you must have found out what is the exact reason of the high CPU.

opiodr aborting process unknown ospid () as a result of ORA-28

I  have been seeing the message opiodr aborting process unknown ospid () as a result of ORA-28 in the alert log of my 11g database daily and frequently. The message will be more frequent when there is a heavy load in the server. 

I know that ORA-28 is a result of killing a session with alter session command
[oracle@dbtest ~]$ oerr ora 28
00028, 00000, "your session has been killed"
// *Cause:  A privileged user has killed your session and you are no longer logged on to the database.

// *Action: Login again if you wish to continue working.

You can give me many ideas about this message opiodr aborting process unknown ospid () as a result of ORA-28 like,

This is an informational message only. 

This is newly added to Oracle 11g. 

This is introduced to ensure that all the processes are monitored in the database and captured alerts for unusual exit of any process. 

But what if nobody is actually killing any sessions manually in the database?. 

I got some useful information from the oracle support site. Here are the links to access them

"opiodr aborting process" Messages in Alert.Log

[Article ID 1230858.1]

Bug 6377339 - Misleading alert log message for "opiodr aborting" [Article ID 6377339.8]

ORA-28 : OPIODR ABORTING PROCESS UNKNOWN OSPID ERRORS IN THE ALERT.LOG DAILY [Bug ID 7659739]

How to get compilation errors in oracle?

We DBAs used to compile invalid objects to make them valid. You may compile the whole database using utlrp.sql, or all invalid objects in a schema or even using manual scripts.

Read : Manual script to compile invalid objects in Oracle

While compiling, if there are any errors in the code, it will end up with a warning as given below

SQL> alter procedure empsal compile ;

Warning: Procedure altered with compilation errors.

To get the actual errors with the object (here it is a procedure in the example) you have to run SHOW  ERRORS after compiling the object.

SQL> show errors

Errors for PROCEDURE EMPSAL:

LINE/COL ERROR

-------- -------------------------------------------------

3/1      PL/SQL: SQL Statement ignored

3/20     PLS-00201: identifier 'V_SAL' must be declared

3/26     PL/SQL: ORA-00904: : invalid identifier

4/1      PL/SQL: Statement ignored

4/23     PLS-00201: identifier 'V_SAL' must be declared

Here you can see the actual error with the procedure. (or function, package, package body etc)

If you have compiled many objects together with a script, and a few has the warning, then show errors will give you the errors with the latest compiled one only. In such cases you have to give the object type and object name to get errors related to a specific object. The complete Syntax of SHOW ERRORS is given below.

 SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |   PACKAGE BODY | TRIGGER | VIEW   | TYPE | TYPE BODY | DIMENSION   | JAVA SOURCE | JAVA CLASS } [schema.]name]

The same for the given example is given below

SQL> show errors procedure empsal

Errors for PROCEDURE EMPSAL:

LINE/COL ERROR

-------- -------------------------------------------------

3/1      PL/SQL: SQL Statement ignored

3/20     PLS-00201: identifier 'V_SAL' must be declared

3/26     PL/SQL: ORA-00904: : invalid identifier

4/1      PL/SQL: Statement ignored

4/23     PLS-00201: identifier 'V_SAL' must be declared

SHOW ERRORS can be abbreviated to SHO ERR also.

SQL> sho err

Errors for PROCEDURE EMPSAL:

LINE/COL ERROR

-------- ---------------------------------------------------

3/1      PL/SQL: SQL Statement ignored

3/20     PLS-00201: identifier 'V_SAL' must be declared

3/26     PL/SQL: ORA-00904: : invalid identifier

4/1      PL/SQL: Statement ignored

4/23     PLS-00201: identifier 'V_SAL' must be declared

You can use SHOW ERRORS command if you are using SQLPLUS,SQLDeveleoper, TOAD or any such interfaces.

ORA-600 [kkqjpdpvpd: No join pred found.]

ORA-00600:  [kkqjpdpvpd: No join pred found.] is an internal error. There are known issues which causes this error.You may have to raise an SR after looking in to the error look up tool described in 
ORA-600/ORA-7445 Error Look-up Tool *[ID 153788.1]*

Before going to submit the details to support we can go through the known issues related to this.
According to the Doc ID 1200518.1 : ORA-600 [kkqjpdpvpd: No join pred found.] there are five known bugs.
They are numbered as 16601689, 13501787, 10288630, 9240305 and 9031353
According to the notes the error can be from any of the following.
1. From SET query with GROUP BY using multi-level push down.

2. From A query with internal union all views may fail with internal error [kkqjpdpvpd: no join pred found.]

3. From optimizing a query with a full outer join which undergoes join predicate push down (JPPD).

Ref: https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle

4. From  join predicate pushdown for a query that has a view in side a view.

5. When attempting join predicate pushdown (JPPD) if the SQL contains a UNION ALL view and each branch contains at least two tables.

Also there are workaround for all these bugs. Go through the Support documents to know better on the topic.

Oracle 12c : PDB and CDB

Oracle Database 12c introduces a new multitenant architecture. It enables an Oracle database to contain a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a separate database. This self-contained collection is called a pluggable database (PDB). A multitenant container database (CDB) is an Oracle database that includes zero, one, or many user-created PDBs

The screen shot below will give you a good idea about the  CDB and PDB in oracle 12c

-click on image to enlarge-

[content given below is just copied from Oracle document]

A container is either a PDB or the root container (also called the root). The root is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.
Every CDB has the following containers:
Exactly one root
The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages . A common user is a database user known in every container  The root container is named CDB$ROOT.

Exactly one seed PDB
The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

Zero or more user-created PDBs
A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.

Ref:
http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdbovrvw.htm

ORACLE TEXT: implementing scoring text search engine with example

Oracle Text is a tool that enables you to build text query applications and document classification applications. Oracle Text provides indexing, word and theme searching, and viewing capabilities for text.
This post is related to text query functionality. Here I will explain how to use oracle scoring function with an example.

Using oracle text and score function we can implement  search functionality on several database columns, and results will be ordered by their relevance.We can implement searches on text columns using "LIKE" but it will not give you how relevant is each result.

Oracle text is already installed in oracle 11g and oracle 12c versions. If you are using oracle 10g Please refer this url to know how to install the oracle text extension http://download.oracle.com/docs/cd/B19306_01/install.102/e10319/initmedia.htm
Once installed, in order to run query on your text columns, you must add a index. Oracle Text provides three type of index for your text/documents, Below is the explanation copied from the oracle documentation.

CONTEXT: 
Use this index to build a text retrieval application when your text consists of large coherent documents. You can index documents of different formats such as Microsoft Word, HTML, XML, or plain text.
You can customize your index in a variety of ways.
This index uses CONTAINS clause
CTXCAT:
Use this index type to improve mixed query performance. Suitable for querying small text fragments with structured criteria like dates, item names, and prices that are stored across columns.
This index uses CATSEARCH clause
CTXRULE:
Use to build a document classification application. You create this index on a table of queries, where each query has a classification.
Single documents (plain text, HTML, or XML) can be classified by using the MATCHES operator.
This index use MATCHES clause

In order to use SCORING function, that returns results’ relevance, we have to use CONTEXT index.
Please follow the below example step by step to understand how to implement scoring function to a text column.
SQL> create table friends (id number, name varchar2(23), about varchar2(100));
SQL> CREATE INDEX scoreindx ON friends(about) INDEXTYPE IS CTXSYS.CONTEXT;
 Insert some rows...
SQL> select * from friends;
        ID NAME                    ABOUT
---------- ----------------------- -----------------------------------------
         1 fahad                   colq rmte frnd
         2 arun                    frnd frnd rmte
         3 javed                   colq colq colq
         4 rajeev                  colq colq frnd

Now let us run the following queries to check how score() function is working.
SQL> select name, score(1) as rating from friends where contains(about, 'frnd', 1)>0 order by rating desc;
 NAME                        RATING
----------------------- ----------
arun                             7
rajeev                           3
fahad                            3
 SQL> select name, score(1) as rating from friends where contains(about, 'colq', 1) >0 order by rating desc;
 NAME                        RATING
----------------------- ----------
javed                           10
rajeev                           7
fahad                            3
You can see that the output is ordered with respect to the relevance of the search term. The column rating in the output gives the relevance. 
If you want to search for more than one text the score() function can be used as below.
SQL> select name, score(1)+score(2) as rating from friends where contains(about, 'rmte', 1) >0 or contains(about, 'frnd', 2)>0 order by rating desc;
 NAME                        RATING
----------------------- ----------
arun                            11
fahad                            7
rajeev                           3
Normally we will not be aware of the number of words in the search string. So we may have to create the query dynamically in the application and run the same. 
Here below I am giving you an example to create the query with score() function dynamically with respect to the query term you provide. 
Here in this example I am searching for 'I am going to America' you can change this string to anything, and obviously you will be using a variable to give the search string in our application.

declare
   search_string varchar2(1000) :='i am going to america' ;
   words varchar2(25);
   select_string varchar2(1000) :='select name, ';
   cond_string varchar2(1000) := 'where ';
   wcount number ;
   statement varchar2(1000);
begin
   select length(search_string)-length(replace(search_string , ' ' ))+1 into wcount from dual;
   select_string:=select_string || 'score(1) as rating1';
   cond_string:= cond_string||' contains (about, '''|| search_string ||''', 1)>0';
   for i in 2..wcount+1 loop
       select regexp_substr(search_string, '[^ ]+', 1, i-1) into words  from dual;
       if i=2 then
            select_string:=select_string||', score('||i||')';
            cond_string:=cond_string||' or contains(about,''' || words||''','||i||')>0 ';
      else
            select_string:=select_string||'+score('||i||')';
            cond_string:=cond_string||' or contains(about,''' || words||''','||i||')>0 ';
      end if;
   end loop;
   statement:= select_string || ' as rating2 from friends '||chr(10) || cond_string ||' order by rating1 desc, rating2 desc';
   dbms_output.put_line(statement);
end;
/
Output will be as given below.

select name score(1) as rating1, score(2)+score(3)+score(4)+score(5)+score(6) as rating2 from friends
where  contains (about, 'i am going to america', 1)>0 contains(about,'am',2)>0 or contains(about,'going',3)>0 or contains(about,'to',4)>0 or contains(about,'america',5)>0 or
contains(about,'',6)>0  order by rating1 desc, rating2 desc

You can do modification in the script if the provided one does not satisfy your need. I hope this one will help you to create a new one. 
Ref:
http://docs.oracle.com/cd/B10501_01/text.920/a96517/cdefault.htm

SQL Loader with condition - example

Just follow the steps below. You will understand how to do SQL loader with condition. Condition can be written in the loader controlfile with WHEN clause. I am using scott as the working schema

1. Create a table in the SCOTT schema

 create table aaa (

   field1       varchar2(10),

   field2       varchar2(10)  

 );

  

2. Create a file data.dat  in /u01/ with the following contents. 

  

Banana;Fruit;

Lemon;Fruit;

Avocado;Fruit;

Ford;Car;

Pear;Fruit;

Apple;Fruit;

3. Create the controlfile control.ctl in   /u01/  with the following contents 

load data

infile '/u01/data.dat'

DISCARDFILE '/u01/emp.dsc'

insert

into table aaa when field2='Fruit'

fields terminated by ';'

(

  field1      char,

  field2      char

)

4. Run the SQL*Loader as given below

  

sqlldr control=/u01/control.ctl userid=scott/tiger

5. Check the discard file as well as the table scott.aaa to verify we have done what we were supposed to do ..

ORA-07445: exception encountered

What is the significance of the error ORA-7445?

[oracle@dbtest ~]$ oerr ora 7445
07445, 00000, "exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]"
// *Cause: An OS exception occurred which should result in the creation of a core file.  This is an internal error.
// *Action: Contact your customer support representative.

An ORA-7445 error is raised by an Oracle server process when it has received a fatal signal from the operating system.

ORA-7445 errors are likely caused by OS problems rather than an Oracle problem.

An ORA-7445 is a generic error, and can occur anywhere in the Oracle code.  

Before contacting the customer support, Read the following MOS notes to understand better on the error ORA-7445

Customer Introduction to ORA-7445 Errors (Doc ID 211909.1)

ORA-7445 [kgghash] (Doc ID 310172.1)

ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)

ORA-16957: SQL Analyze time limit interrupt

Cause:

The ORA-16957 error is an internal error code used to indicate that SQL analyze has reached its time limit.

Action:
According to MOS Doc ID 1275248.1  , 
(This is an unpublished bug. The bug is fixes in version 12.1)

The error ORA-16957: SQL Analyze time limit interrupt, can be ignored safely, as it does not indicate any problems with the database.

If the error ORA-16957: SQL Analyze time limit interrupt comes along with

ORA-00600: internal error code, arguments: [17092], [16957], [], [], [], [], [], []

you cannot simply ignore it, you have to  read the below MOS note and apply recommended patches:

ORA-600[17092] And ORA-16957 Reported While Running DBMS_SCHEDULER (Doc ID 743873.1)

How to configure ASM in windows?

Automatic Storage Management, or ASM, is a facility provided with the Oracle database for managing your disks. It is an Oracle-aware logical volume manager, or LVM, that can stripe and mirror database files and recovery files across a number of physical devices. This is an area where the database administration domain
overlaps with the system administration domain.
Many databases will not use ASM: they will store their files on the volumes provided by the operating system, which may well be managed by an LVM. But if you do not have a proper logical LVM, as will probably be the case with low-end systems running on, for example, Linux or Windows, then ASM provides an excellent (and bundled) alternative to purchasing and installing one. On high-end systems, ASM can work with whatever LVM is provided by the operating system.

You can play with ASM even in your local computer by creating virtual ASM disks. There are different ways to do it in windows and Linux. Here I am explaining a method to create asm disks in window.

You can just follow the steps given below.


Step 1: Creating a dummy disks

We need 1G free space to do this demo. We are going to create two dummy disks of 512 MB.

(Assuming D:  drive is having more than 1G free space )

Create a directory called asmdisks in D:\ drive 

D:\>cd asmdisks
D:\asmdisks>asmtool -create F:\asmdisks\ disk1 512
D:\asmdisks>asmtool -create F:\asmdisks\ disk2 512


Now you have 2 dummy disks  of 512MB

Step 2:  Configuring Cluster Synchronization Service

C:\>c:\oracle\product\ 10.2.0\db_ 1\BIN\localconfi g add

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
Step 3: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'ap\arogyaa' , privgrp ''..
Operation successful.
Step 4: creating new CSS service
successfully created local CSS service
successfully reset location of CSS setup


step 3: Creating init file (pfile)

 Save a file as "C:\oracle\product\ 10.2.0\db_ 1\database\ init+ASM. ora" with the following lines in it.

INSTANCE_TYPE= ASM
DB_UNIQUE_NAME= +ASM
LARGE_POOL_SIZE= 8M
ASM_DISKSTRING= 'D:\asmdisks\ *'
_ASM_ALLOW_ONLY_ RAW_DISKS= FALSE

Step 4: Creating service and password file

c:\> oradim -NEW -ASMSID +ASM -STARTMODE auto
c:\> orapwd file=C:\oracle\ product\10.2.0\db_1\ database\ PWD+ASM.ora password=oracle

Step 5:  Creating  ASM disk group and enter in parameter file
 Create asm disk group

set ORACLE_SID=+ASM
sqlplus / as sysdba
SQL> select path, mount_status from v$asm_disk;
PATH                  MOUNT_STATUS
--------------------- -------------
D:\ASMDISKS\DISK1     CLOSED
D:\ASMDISKS\DISK2     CLOSED

SQL> create diskgroup D1 external redundancy disk
 'D:\ASMDISKS\ DISK1',
 'D:\ASMDISKS\ DISK2';
Diskgroup created.

create spfile from pfile and add newly created diskgroup in to it.

SQL> create spfile from pfile;
SQL> startup force;
SQL> alter system set asm_diskgroups= D1 scope=spfile;
SQL> startup force
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>


Your AMS instance is ready with ASMDISKS mounted. You can use this disk to store ASMFILES.