Oracle Database: AWR Reports Summary - AWR Hatırlatması

Today, we  tried to run AWR reports in our system but we encountered an error so we decided that we could have a brief summary on AWR Reports.

First we will start with ORA-20200 error. We tried to run awrrpt.sql found under $ORACLE_HOME/rdbms/admin and we encountered with that error since we had no AWR reports although it had been months since se installed our Oracle Applications Database(EBS).

Then we come up with the suggested solution below however our conditions were not that critical(Ref-1). We also didnt have those snapshots but we were able to create these snapshots. In that solution, it was suggested that we recreate the awr.

How to drop and recreate AWR?
Note:782974.1 How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository 

Our solution was however is to just recreate our snapshot to solve our ORA-20200.



References:

1- AWR Error - ORA 20200
 http://fayoubi.blogspot.com.tr/2013/09/ora-13516-awr-operation-failed-or-ora.html

How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1) 2- Awr generation How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)

3- Systemwide Tuning using STATSPACK Reports (Doc ID 228913.1)

4-How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)

5-Awr reports Reading

http://oracle-base.com/articles/10g/automatic-workload-repository-10g.php

Oracle Database: Compiling Invalid Objects Continued - Invalid Objelerin Compile Edilmesi Devam

During our health check, ıt appeared to us that we were working with invalid objects and we thought that there must be some script Oracle has done for it. We found out that we were right .

Oracle has 2 scripts for it. "Utlprp.sql" and "Utlrp.sql" which are found under $ORACLE_HOME/rdbms/admin.

It asks for just one input value. Its answer must be given accordingly.
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
However sometimes we need to some manuel compilation.

For example the following states a compilation of package.

 BEGIN  
  FOR cur_rec IN (SELECT owner,  
              object_name,  
              object_type,  
              DECODE(object_type, 'PACKAGE', 1,  
                        'PACKAGE BODY', 2, 2) AS recompile_order  
          FROM  dba_objects  
          WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')  
          AND  status != 'VALID'  
          ORDER BY 4)  
  LOOP  
   BEGIN  
    IF cur_rec.object_type = 'PACKAGE' THEN  
     EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||   
       ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';  
    ElSE  
     EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||   
       '"."' || cur_rec.object_name || '" COMPILE BODY';  
    END IF;  
   EXCEPTION  
    WHEN OTHERS THEN  
     DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||   
                ' : ' || cur_rec.object_name);  
   END;  
  END LOOP;  
 END;  
 / 

References:

1- Compile Invalid Objects

http://oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php

Oracle Database: Error ORA-01655 - Unable to Extend - Tablespace Sorunu

While we were checking our alert.log for possible error, we noticed that there was an error that was saying that our tablespace was having a lack of space:


----------
Errors in file /u01/install/PROD/11.2.0/admin/TEST_DBMACHINE/diag/rdbms/test/TEST/trace/TEST_smon_1166.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01655: unable to extend cluster SYS.SMON_SCN_TO_TIME_AUX by 128 in tablespace SYSAUX
Wed Mar 11 11:36:24 2015
----------

Solution:

We added a datafile to the system in order to add more space to the tablespace:

The following is the sql:

-----------------
ALTER TABLESPACE SYSAUX
  ADD DATAFILE '/u01/install/PROD/data/sysaux03.dbf'
  SIZE 1024M
  AUTOEXTEND ON
  NEXT 50M
  MAXSIZE UNLIMITED;
---------------

References:

1- Tablespace Clauses:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3002.htm#SQLRF01002

2- Unable To Extend Error 

https://community.oracle.com/thread/950421

Oracle Database: Error ORA-48165 In the Sqlnet.log File

 ORA-48165 this error was noticed while we were checking for sqlnet.ora log file.  The error message was like the following.


--------
NL-08014: Failed to initialize Diagnosability framework, falling back to old network tracing/logging

 NL-08015: Client(OCI) side initialization of Diagnosability framework failed
  ORA-48165: user missing read, write, or exec permission on specified ADR Base directory []
User inputted base directory is invalid [48187] [/u01/E-BIZ/db/tech_st/11.2.0/admin/VISION_slc01ozg]

--------

This message was in fact indicating us that we had misconfiguration in our sqlnet.ora file. Therefore we had to change it.

Solution:

Go to $ORACLE_HOME/network/admin and then change the ADR_BASE to the correct value.

The correct value has to be value of the diagnostic_dest:

SQL> show parameter diag

NAME                                 TYPE        VALUE

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

diagnostic_dest                      string      /u01/install/PROD/11.2.0/admin

                                                 /TEST_DBMACHINE

References:

1-http://onlineappsdba.blogspot.com.tr/2007/11/oracle-11g-alert-log-file.html

2- Oracle Documentation About Network Connectivity Issues:

https://docs.oracle.com/cd/A57673_01/DOC/net/doc/NWTR23/apa.htm

Oracle Database: Reading X$DBGALERTEXT Table - Reading Alert Log Via SQL - SQL ile Alert.log'ları okumak

With Oracle Database 11g, we can read the alerts which are critical for us from a table. In the prior editions, we had to parse the table so that we could extract the information that we need but we can access it directly.

How to read from x$dbagalertext?

In order to read from x$dbagalertext we need to be a user who is sys or sys privileged. However we could also access to the x$dbagalertext by creating a  view so that we can query it.(Ref-1)

References:

1- Reading from x$dbagalertext from a use who is not "sys"  privileged.

https://community.oracle.com/thread/2356037

2-  Alert Log Reading

http://www.oradba.ch/2013/07/query-alert-log-from-sqlplus/

3-  Reading alert log

http://blog.tanelpoder.com/2009/03/21/oracle-11g-reading-alert-log-via-sql/

Oracle Support: How to Use Certifications Tab - Learning Which Application Certified With Which Component - Ürünlerin Birbirleriyle Destekli Olup Olmadığının Öğrenilmesi

Today we will try to show what can be done with the certifications tab found in the Oracle Support, what is its use and how can we use it.

First ol all with Certifications option in the metalink we could analyze what are the components needed for our application. This is a crucial knowledge because usually we install individual products on our environments and we need to be aware of the versions of the other products  if we want to have a succesful installation.

Although there is a video on how we can utilize this tool, we could very well fill the inputs and go on.

We could see the certified versions of our specific product with just one search

Moreover we could compare the certification of any Oracle application with each other.

Below is the result of the comparison.  What we have highligted  is the text that we have to understand to prevent any misunderstandings. According to the selection made in the left side, that sentence changes. Depending on our purpose, we  could make a decision on which versions of the products that we should use.

In the examples below we searched the certified product against the latest Oracle Discoverer 11.1.1.7


References:

1- My Oracle Support Certification Tool for Oracle Fusion Middleware Products 1368736.1

Oracle Database: SqlPlus Backspace Fix - Deleting Characters in SqlPlus- Sqlplus'taki Silme Problemi

Deleting problem is a pain for most of the SQL users. The ability to delete becomes a bliss in sqlplus
In default settings, when you mistype a key in sqlplus and you try to delete them, you encounter "^H" problem. That is something like below

 SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 5 11:47:04 2015  
   
 Copyright (c) 1982, 2013, Oracle. All rights reserved.  
   
   
 Connected to:  
 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
 With the Partitioning, OLAP, Data Mining and Real Application Testing options  
   
 SQL> select * instasada^H^H^H^H  

It means that when you mistype instance and try to correct it by deleting the wrong characters, you end up with "^H" strings all over.

 The reason why was explained by Erman Arslan in his following post.

Erman Arslan's Oracle Blog: Linux -- SQLPLUS backspace (^H) problem & fix - vt100,vt220

Before, we were solving it with ctrl+backspace key combination.  However if you dont want to deal with you just apply the solution below.

I just wanted to share it because I know that many people experience this problem.

The solution is to add this to " stty erase ^H " the .bash_profile which is found in the user directory (e.g. /home/applmgr/.)

 # .bash_profile  
   
 # Get the aliases and functions  
 if [ -f ~/.bashrc ]; then  
     . ~/.bashrc  
 fi  
   
 # User specific environment and startup programs  
   
 PATH=$PATH:$HOME/bin  
   
 export PATH  
   
 export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH  
   
   
 stty erase ^H 

Like the example above , we added the stty string to bash profile and then we were able to delete the wrong characters with ease.

References:
1- Erman Arslan's Blog :
http://ermanarslan.blogspot.com.tr/2015/01/linux-sqlplus-backspace-h-problem-fix.html?utm_source=feedburner&utm_medium=email&utm_campaign=Feed:+ErmanArslansOracleBlog+(Erman+Arslan%27s+Oracle+Blog)