How to check NLS parameter value of Oracle Database.

How to check NLS parameter value of Oracle Database.

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AR8ISO8859P6
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              11.1.0.7.0

20 rows selected.

11g R2 RAC : Oracle Local Registry (OLR)

11g R2 RAC : Oracle Local Registry (OLR) 

In Oracle Clusterware 11g Release 2 an additional component related to the OCR called the Oracle Local Registry (OLR) is installed on each node in the cluster. The OLR is a local registry for node specific resources. THe OLR is not shared by other nodes in the cluster. It is installed and configured when Oracle clusterware is installed.

Purpose of OLR:

It is the very first file that is accessed to startup clusterware when OCR is stored on ASM. OCR should be accessible to find out the resources which need to be started on a node. If OCR is on ASM, it can’t be read until ASM (which itself is a resource for the node and this information is stored in OCR) is up. To resolve this problem, information about the resources which need to be started on a node is stored in an operating system  file which is called Oracle Local Registry or OLR. Since OLR is a file an operating system file, it can be accessed by various processes on the node for read/write irrespective of the status of the clusterware (up/down). Hence, when  a node joins the cluster,  OLR on that node is read, various resources ,including ASM  are started on the node  . Once ASM is up , OCR is accessible and is used henceforth to manage all the clusterware resources. If OLR is missing or corrupted, clusterware can’t be started on that node!

Where is OLR located?

The OLR file is located in the grid_home/cdata/<hostname>.olr . The location of OLR is stored in /etc/oracle/olr.loc.and used by OHASD .

What does OLR contain?

The OLR stores data about
ORA_CRS_HOME
localhost version
active version
GPnP details
OCR latest backup time and location
information about OCR daily, weekly backup location
node name etc.

This information stored in the OLR is needed by OHASD to start or join a cluster.
A quick  peek at the backup of the olr shows the resources that are being maintained.

root@host01 ~]# ocrconfig -local -manualbackup

host01     2013/01/18 01:20:27     /u01/app/11.2.0/grid/cdata/host01/backup_20130118_012027.olr

root@host01 ~]# strings /u01/app/11.2.0/grid/cdata/host01/backup_20130118_012027.olr |grep -v type |grep ora!

ora!drivers!acfs
ora!crsd
ora!asm
ora!evmd
ora!ctssd
ora!cssd
ora!cssdmonitor
ora!diskmon
ora!gpnpd
ora!gipcd
ora!mdnsd

OLR administration:

You can view the status of the OLR file on each node by using the ocrcheck command with the –local parameter as seen here:

#ocrcheck -local
ocrdump can be used to  dump the contents of the OLR to tthe text terminal:

#ocrdump -local -stdout
You can use the ocrconfig command to export and import the OLR as seen in these examples:

#ocrconfig -local -export <export file name >

#ocrconfig -local -import <file name>

And you can repair the OLR file should it become corrupted with the ocrconfig command as seen in this example:

#ocrconfig -local -repair olr <file name>

The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR.

To manually back up OLR:
# ocrconfig –local –manualbackup

To view the contents of the OLR backup file:
#ocrdump -local -backupfile olr_backup_file_name

To change the OLR backup location
#ocrconfig -local -backuploc new_olr_backup_path

To restore OLR:
# crsctl stop crs
# ocrconfig -local -restore file_name
# ocrcheck -local
# crsctl start crs
$ cluvfy comp olr

what is oracle database ?

It is a very common question. Before starting Oracle Database architecture, you should know what exactly the database server is.
I have came across a lot of answers, and examples. Combining everything I am sharing this. Hope u will find it useful…

let’s imagine a scenario first. Consider YOURSELF as a person who has some special capability.
You can listen very fast, you can see anything very easily, you can interact with others very quickly.
Of course as a human being you have couple of hands, eyes , ears , legs and so on. And also you have got certain amount of MEMORY inside your BRAIN.
But also you have got a big PROBLEM. You CAN NOT remember those things for a long time.

So one of your well wishers give you some NOTE BOOKS to write whatever you listen or see.

NOW, if you relate above mentioned things with ORACLE. Then YOU are basically an Oracle INSTANCE, consists of some MEMORY (RAM) which is like your brain. and some BACKGROUND PROCESSES like your eyes, hands, ears etc, using which YOU can do something..
And those NOTE BOOKS in which you write everything.. basically those are DATAFILES.

So all together this Oracle Instance and Data files are known as ORACLE DATABASE SERVER.

Gather Schema and Tables Statistics

Gather Schema and Tables Statistics

Gather Schema Statistics – Concurrent Program

Connect as System Administrator

Concurrent - Request – Run

Select - Gather Schema Statistics

Click OK button

Estimate Percent: Using any value larger than 50 will force a compute statistics to be gathered; any value less than 50 only provide estimated statistics. Computed statistics in some cases could provide a significant performance improvement for Application modules.

Click the Schedule button to schedule as per your company needs, As a general rule, schedule the Gather Schema Statistics concurrent program to run once a week, during off hours, for your entire database

Gather Table Statistics Concurrent Program

If you have volatile tables that are updated, inserted into or deleted from frequently, then you should consider running Gather Table Statistics for those tables more frequently, perhaps nightly during off hours. In the following figure, we’ve chosen a particular table, FND_CONCURRENT_REQUESTS, and selected 99 for the percent to analyze to ensure that the table is analyzed using compute, rather than estimate.

Note- Using these two Concurrent Programs also generates statistics on the associated indexes.

How to Clear Oracle Application Cache from front end without bounce the application in Oracle Applications.

How to Clear Oracle Application Cache from front end without bounce the application in Oracle Applications.

Navigation:- Functional Administrator > Home >
It will open Webpage

Click on tab Core Services

Click on sub tab Caching Framework

Then click on Left side Menu Global Configuration

Click on Clear All Cache

Click Yes on confirmation.

Suspending MMON slave action kewrmafsa_ for 82800 seconds

Suspending MMON slave action kewrmafsa_ for 82800 seconds

One of our database environment we noticed that AWR report is not available for particular time interval. In further investigating we found one MMON related error in the alert log.

Error:

Suspending MMON slave action kewrmapsa_ for 82800 seconds

Cause:

While MMON activity is important in maintaining information in the database, if the cpu utilization and database activities  exceeds cpu or run time policies, then MMON activity can be temporarily suspended. This is done so that more important activity can continue.  The "Suspending MMON ... for 82800 seconds" warning messages indicate that you should look at the overall performance of your system to check if there is anything that could be causing MMON activities to exceed their resource allocation such that they are suspended.

Solution:

Increase the Server configuration add RAM and more CPUs.

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

ORA-06550: line 1, column 807:

PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared


Cause:

You might get this error when database is created using template from old database or by cloning from another database.  Therefore, table DBSNMP.BSLN_BASELINES contains inconsistent information that is causing the job to fail.



Solution:

If you delete the inconsistent rows from table DBSNMP.BSLN_BASELINES, it will fix the problem but you can fix this easiest way by following the below guidelines.

SQL> select log_date,status from dba_scheduler_job_run_details where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                                                                    STATUS

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

13-AUG-14 09.45.36.831010 AM +02:00                                         FAILED

17-AUG-14 09.00.00.980275 AM +02:00                                         FAILED


SQL> sqlplus / as sysdba

-- Drop the DBSNMP user by executing catnsnmp.sql script.

SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql

-- Create the DBSNMP user by executing catsnmp.sql

SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql

-- Re-execute the job

SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

PL/SQL procedure successfully completed.

SQL> select log_date,status from dba_scheduler_job_run_details where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                                                                    STATUS

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

13-AUG-14 09.45.36.831010 AM +02:00                                         FAILED

17-AUG-14 09.00.00.980275 AM +02:00                                         FAILED

20-AUG-14 02.50.36.799841 PM +02:00                                         SUCCEEDED

Reference metalink Doc ID 1413756.1

ORA-609 : opiodr aborting process unknown ospid (8327_47148946930848)

ORA-609 : opiodr aborting process unknown ospid (8327_47148946930848)

As a general error, the ORA-609 error indicates that a client connection failed to complete.  This can be an ORA-609 from an abort or killing an Oracle session.

To diagnose any error, you start by using the OERR UTILITY to display the ORA-609 error:

Example :

bash-3.2$ oerr ora 609
00609, 00000, "could not attach to incoming connection"
// *Cause:  Oracle process could not answer incoming connection
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.

Cause:

The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection
process before the server process was completely spawned.
Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.

This is also triggered, when a DB session is killed/aborted manually from the OS prompt.

Solution:

Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.
If the problem  is due to connection timeouts,an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

Reference metalink Doc ID 1121357.1