Oracle Database DBA

TNS-12557: TNS:protocol adapter not loadable

2012-01-26T23:17:00.000-08:00

TNS-12557: TNS:protocol adapter not loadable
Might occur on a Windows machine that has two or more Oracle homes installed.

Solution

Set the ORACLE_HOME environment variable using the OUI under Start > Programs > Oracle_home > Oracle Installation Products > Universal Installer. Use the OUI to set the Environment properly on Windows.

* Refer metalink doc 556895.1 for details.

Have a question or comment? Post it below!

ORA-07445 [kkqvSearchLogOpn()]

2011-09-07T07:42:00.000-07:00

ORA-07445: exception encountered: core dump [kkqvSearchLogOpn()+15]

Bug 9053676

Fixed in 11.2.0.2

Workaround:

Disable cost based query transformation.
eg: Set "_OPTIMIZER_COST_BASED_TRANSFORMATION" = OFF

Have a question or comment? Post it below!

ORA-7445 [nstimexp]

2011-09-03T04:18:00.001-07:00

ORA-07445: exception encountered: core dump [nstimexp()+36] [SIGSEGV]

This error function could occur if sqlnet.expire_time is set in sqlnet.ora.

Bug 3934729

Fixed in 10.2.0.3, 11.2.0.2

Have a question or comment? Post it below!

ORA-12571: TNS:packet writer failure

2011-08-30T03:04:00.000-07:00

This is one of the tricky errors which could have been caused because of various components.

In general this error occurs when there is an interruption on the Packet being communicated between client and server. The interruption can be caused by a a software or application (example spyware / anitivirus / download accelerators / compression softwares / local firewall settings on client machine / NIC settings etc)

Have a question or comment? Post it below!

Upgrading 10g RAC Clusterware to 11g RAC Clusterware

2011-05-26T06:20:00.000-07:00

This article lists the steps to upgrade Oracle 10g RAC Clusterware to 11g RAC Clusterware. There are few new requirements in 11g like kernel parameters and SCAN IP which are discussed herewith.

Existing Environment:

Oracle 10g Two Node RAC Database using ASM on Sun Solaris 10 Update 9.

Preliminary Settings:

- Set the following kernel parameters in /etc/system file on both nodes:

set noexec_user_stack=1
set shmsys:shminfo_shmmax=8589934590
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set semsys:seminfo_semmns=2000
set semsys:seminfo_semmsl=1000
set semsys:seminfo_semmni=100
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmseg=10
set rlim_fd_cur=1024
set max_nprocs=30000
set maxuprc=16384
set default_stksize=32768

- Oracle 11g requires additional IPs for SCAN configuration. Recommended is 3 IPs and minimum is 1. If using 3 IPs, you need to make entry in DNS for round robin name resolution. If using single IP, then hosts file resolution could be used, in which case, cluster verification utility during installation would show as failed, which could be ignored. To avoid this, you need to make a Host entry in DNS Server.

E.g. make an entry in /etc/hosts

#SCAN
10.192.154.234 SCAN-PROD

The IP address should be of same subnet like public IP.

- Download the 11g Software from oracle website.

Unzip the software and run the Cluster Verification Utility.

./runcluvfy.sh stage -pre crsinst -n < node1 >, < node2 > –verbose

As Oracle Clusterware was already running on these servers, most of the settings like user equivalence, patches etc would be already in place. In case of any errors, take appropriate action.

After Pre-checks are successful, we can further create the home directory for Grid software.

e.g.

/project/11.2.0/grid

- Shutdown the Database and ASM Instances. There is no need to stop the Clusterware.

- Unset the following environment variables:

ORA_CRS_HOME
ORACLE_HOME
ORA_NLS10
TNS_ADMIN

- Execute RunInstaller from Clusterware Software:

On Screen Selections:

Step 1 of 8

Select - Upgrade Grid Infrastructure
Click Yes on message ‘Existing ASM Instances detected’

Step 2 of 8

Click Next on Select Language screen

Step 3 of 8

Select existing Cluster Nodes.
Make sure check box ‘Update cluster ASM’ is checked

Step 4 of 8

SCAN Information:
Enter Cluster name:      CRS
Scan name:                  SCAN-PROD
Scan port:                    1521

Step 5 of 8

Specify password for ASMSNMP user

Step 6 of 8

Select OS group - oinstall

Step 7 of 8

Specify Oracle Base and Grid Home location

Step 8 of 8

Verify all Prerequisite checks have passed and click finish to start Setup.

Run ‘rootupgrade.sh’ script as root on all nodes one by one, when prompted.

After it finishes, Oracle 10g Clusterware and ASM Instances had been successfully upgraded to 11g Clusterware.

Have a question or comment? Post it below!

Sample Listener and Tnsnames files in 11g RAC

2011-03-29T00:07:00.000-07:00

The contents of Listener.ora and tnsnames.ora in 11g RAC are shown below.

#listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

#tnsnames.ora

# tnsnames.ora Network Configuration File: /project/oracle/orabase/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

BVNPRD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = crs-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bvnprd)

)

)

#endpoints_listener.ora

LISTENER_IBS-ASH-SR147=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ibs-ash-sr147-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.192.154.205)(PORT=1521)(IP=FIRST)))) # line added by Agent

Have a question or comment? Post it below!

ORA-31693: Table data object failed to load/unload and is being skipped due to error

2011-03-11T22:40:00.000-08:00

Export or Import could fail with the following error:

ORA-31693: Table data object failed to load/unload and is being skipped due to error:

ORA-31640: unable to open dump file for read
ORA-19505: failed to identify file
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

Solution:

Try running export or import without the 'Parallel' option.

Have a question or comment? Post it below!

High CPU usage-spike-load on Database Server

2011-03-03T23:13:00.000-08:00

One of the issues that could make the DBA life uncomfortable is high CPU usage. Ocassionally, you might see High CPU usage or CPU spikes on the Database Server. Ideally, the Operating systems are designed to use CPU to maximum to get high throughput, however at the same time we need to make sure that a single process doesn’t eats up the whole CPU, causing the other processes to starve.

‘Load average’ field in the top command would tell you the average number of processes ready to run in the last 1, 5 and 15 minutes, which could be viewed in combination with number of processes waiting for run time shown by vmstat command. It could be a point of concern if this starts happening frequently and stays for long duration. In this post, we would discuss how to identify and fix the cause of high CPU usage.

The first thing is to find out the process that is causing high CPU usage. It’s not necessary that it would be a DB process. There is a possibility that some other process at Operating system level might be consuming high CPU. You can use ‘top’ command to find the same.

- Run the top command at OS prompt
- Sort the output by CPU usage (Shift + p in linux)
- To see individual CPU usage, press 1

Now the output of top command would be sorted by CPU usage. You can get the Process ID of the top CPU consuming processes. The user name would tell you whether it’s a Database process or some other.

You can easily get the details about that session e.g. username, program, module, sql etc by providing the OS Process ID to the following Script.

You have now identified the SQL which is consuming high CPU and the corresponding program or module running it. To fix the issue, you need to tune the SQL.

Have a question or comment? Post it below!

ORA-12542 or TNS-12542

2010-12-27T06:36:00.000-08:00

ORA-12542 or TNS-12542 could occur in few situations.

More info:

TNS:address already in use
TNS:protocol adapter error

Case 1: While starting the listener.
- Change the port number, in case another listener is running on same port.
-   Delete the .oracle subdirectory under /tmp to repopulate the hung TCP/IP socket.

Case 2: While connecting from Application/Client Server
   - Bounce the Application Services as maximum port limit has reached.
   - Increase the free port limit on the server.

More Troubleshooting Steps

Have a question or comment? Post it below!

Commit statement

2010-12-05T06:33:00.000-08:00

COMMIT statement is used to make permanent all changes performed in the transaction.

SQL> Commit;

Note: Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.

Have a question or comment? Post it below!

Previous Tutorial

Rollback Statement

2010-12-05T06:29:00.000-08:00

Use the ROLLBACK statement to undo the work done in the current transaction.

SQL> Rollback;

rolls back the entire transaction.

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

Delete statement

2010-12-05T05:53:00.000-08:00

DELETE statement is used to remove rows from the table.

Syntax:
delete from <table_name> [where clause]

e.g.
SQL>delete from employees where employee_id = 902;

CAUTION: Delete without 'Where' clause will remove all rows from table.

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

Update statement

2010-12-05T05:41:00.000-08:00

UPDATE statement is used to change existing values in a table.

Syntax:
Update <table_name> set <column_name> = value [where clause]

e.g.
SQL> update employees set first_name='Johnson' where employee_id=901;

CAUTION: Update Statement without the WHERE clause will update all rows in the table.

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

Insert Statement

2010-12-05T05:23:00.000-08:00

INSERT Statement is used to add new rows to a table.

Syntax:

Insert into <table_name> values (value1,value2..);

e.g.

SQL> insert into employees values(901,'John','S','john@gmail.com','949999333','17-JUN-2010','AD_PRES',20000,0,100,90);

To insert values in specific columns:

SQL>insert into employees(employee_id,first_name,last_name,email,hire_date,job_id) values(902,'Steven','J','steven@gmail.com','17-JUN-2010','AD_PRES');

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

Adding a datafile in Tablespace

2010-12-03T07:37:00.000-08:00

Use the 'Alter Tablespace' SQL statement to add a datafile in a tablespace.

Alter tablespace <tablespace_name> add datafile 'file_name' size int_value [K|M];

e.g.

Alter Tablespace USERS add datafile '/u01/oracle/oradata/orcl/user02.dbf' size 100M;

Have a question or comment? Post it below!

ORDER BY Clause

2010-11-13T07:17:00.000-08:00

Use the 'Order By' clause to sort the data in ascending or descending order.

e.g.

SQL> select first_name,last_name,salary from employees where salary between 1000 and 3000 order by salary;

would display the output in increasing order of salary.

SQL> select first_name,last_name,salary from employees where salary between 1000 and 3000 order by salary desc;

would display the output with salary in descending order.

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

BETWEEN and IN operators

2010-11-13T07:08:00.000-08:00

The operators IN and BETWEEN facilitates selection of particular rows based on range or list of values.

'IN' operator is used to specify a list of values.

e.g.

SQL> select first_name,last_name from employees where first_name IN ('Anthony','Sarah','Kevin');

would list employees whose first_name is either of the list.

'BETWEEN' operator is used to specify a range of values.

e.g.

SQL> select first_name,last_name from employees where salary between 1000 and 3000;

would list employees whose salary is between 1000 and 3000, both inclusive.

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

LIKE operator

2010-11-13T01:28:00.000-08:00

LIKE operator is used for pattern matching.

e.g.

To find name and salary of employees whose first name starts with 'A':

SQL> select first_name,salary from employees where first_name like 'A%';

To find name and salary of employees whose first name starts with 'A' and followed by 3 characters:

SQL> select first_name,salary from employees where first_name like 'A___';

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

WildCard Characters

2010-11-13T01:20:00.000-08:00

SQL gives you two wildcard characters '%' and ' _' which are used to substitute zero or more characters.

% - Used to specify zero or more characters.

_ - Used to specify any one character.

We would see their usage in next tutorial.

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

WHERE Clause

2010-11-12T07:29:00.000-08:00

The 'Where' clause lets you restrict rows in the output based on the given condition.

e.g.

To select details of employees whose salary is greater than 15000:

SQL> select * from employees where salary > 15000;

AND Operator:

'And' operator is used to combine multiple conditions, and returns rows which satisfy All of the given conditions.

e.g.

SQL> Select * from employees where salary > 5000 and department_id = 110;

OR Operator:

'OR' operator is also used to combine multiple conditions, and returns rows which satisfy Either of the given conditions.

e.g.

SQL> Select * from employees where salary > 5000 or department_id = 110;

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

DESCRIBE Command

2010-11-11T08:17:00.000-08:00

Describe is used to display the structure of the table, view etc. It lists the columns, their data types i.e. the type of data they can store etc.

SQL> DESCRIBE DEPARTMENTS

where Departments is the name of the table.

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

Select statement

2010-11-11T08:02:00.000-08:00

The Select statement is used to retrieve data from one or more tables, views etc.

To Select all data from 'Departments' table:

SQL> Select * from departments;

To Select specific columns:

SQL> Select department_id, department_name from departments;

DISTINCT or UNIQUE:

Use distinct or unique to omit duplicate values from the output.

e.g. to retrieve unique employee first name

SQL> Select distinct first_name from employees;

Have a question or comment? Post it below!

Previous Tutorial Next Tutorial

Convert / Migrate single Instance database to RAC database manually

2010-07-26T00:03:00.000-07:00

Detailed steps to convert / migrate your single instance Oracle 10g Database to 2 node RAC database manually. The current environment is a single Instance Oracle 10g Database running on a local storage. We would migrate it to shared storage with 2 RAC instances.

HIGH LEVEL STEPS:

Ø Preliminary Configuration and Verification

Ø Migrate the existing database to shared storage (Raw devices / ASM)

Ø Install the CRS Software

Ø Install Oracle RAC Software

Ø Configure Listeners, tnsnames files

Ø Modify/Create Initialization parameter, Password files

Ø Start the first Instance

Ø Create RAC data dictionary views

Ø Create Undo tablespace, Redo log files

Ø Bring up the Second RAC Instance

Ø Verify status of RAC processes/Services

DETAILED STEPS:

Pre-Setup:

----------

- Install the Oracle Clusterware and Oracle RAC Database software on nodes

- Copy/Edit original init file as init$SID1.ora and init$SID2.ora

- Make sure SID, listeners, tnsnames, profile, password file are correct on both nodes

Sample init, tnsnames, listener files

Moving DB to shared storage:

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

Create pfile from spfile on original instance, if using spfile

Shutdown original instance

Copy only datafiles on raw devices:

e.g.

dd if=/oradata/file_name of=/dev/raw/raw5

Start instance in mount state

STARTUP MOUNT;

Rename datafiles:

e.g.

alter database rename file '/u01/app/oracle/oradata/test10g/users01.dbf' to '/dev/raw/raw7';

alter database rename file '/u01/app/oracle/oradata/test10g/temp01.dbf' to '/dev/raw/raw8';

Alter database open;

ADD LOGFILES on shared storage:

(Don't try to drop active group)

select group#, bytes, status from v$log;

select group#, member from v$logfile;

e.g.

alter database add logfile group 4 '/dev/raw/raw13' size 128M;

alter database add logfile group 5 '/dev/raw/raw14' size 128M;

select group#, status from v$log;

alter system switch logfile;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

select group#, bytes, status from v$log;

Alter database backup controlfile to trace;

shutdown immediate;

Edit init file and give raw location of controlfiles (rename spfile so Instance do not start using spfile)

STARTUP NOMOUNT;

Recreate controlfile;

Alter database open;

shutdown immediate;

Now Database has been moved to shared storage on original instance.

Now Start DB from RAC instance 1:

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

(Make sure RAC instances have the latest init file)

START the first instance

Add tempfile

e.g.

ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/raw/raw8'

Create RAC dictionary views:

@$ORACLE_HOME/rdbms/admin/catclust

shutdown immediate

From Linux prompt:

srvctl add database -d -o $ORACLE_HOME

srvctl add instance -d test10g -i test10g1 -n rac1

srvctl add instance -d test10g -i test10g2 -n rac2

srvctl start instance -d test10g -i test10g1

/ as sysdba

alter database add logfile thread 2 group 1 '/dev/raw/raw15' size 128M;

alter database add logfile thread 2 group 2 '/dev/raw/raw16' size 128M;

alter database enable thread 2;

create undo tablespace UNDOTBS2 datafile '/dev/raw/raw10' size 25M;

- Start second instance

srvctl start instance -d test10g -i test10g2

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

Verify the RAC services:

crs_stat -t

srvctl status database -d test10g

srvctl stop database -d test10g

srvctl start database -d test10g

select instance_number instance#, instance_name, host_name, status from gv$instance;

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

You have now converted your single instance database to a 2 node RAC database.

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

Have a question or comment? Post it below!

Difference in Schema and User in Oracle Database

2010-07-19T02:52:00.000-07:00

Schema Vs. User

Whenever a new user is created, a schema with the same name as of user is also created. The user owns the schema and all the objects created in his schema.

The term 'user' is normally used in context to 'login' and schema is used while refering to database objects e.g. tables, views etc. However, both terms could be used interchangeably and you can consider a user to be schema and vica versa.

Have a question or comment? Post it below!

Understanding Database Performance Tuning and Resolving Performance Issues

2010-06-06T10:56:00.000-07:00

Performance tuning is basically a reactive process. You need to identify the bottleneck and make the appropriate changes to remove or reduce the bottleneck. The purpose of tuning is to reduce the Resource consumption, so the overall response time for an operation could be reduced.

To identify performance issues, you can create a Baseline AWR Report i.e. a report when the system is performing fine. You can then compare this Baseline report with the one created, when you see Performance issues. Basically, you would be comparing the Volume of transactions, Database, Operating System and Input-Output Statistics in the two reports to identify where the bottleneck is.

To resolve the Performance issues, you could make changes at the Application level, Database level or the Hardware level. And in 90% cases, it would be the Application. So, let’s see how an Application works.

An Application would trigger hundred’s of SQL queries on the Database, which could consume huge amount of DB Server resources e.g. CPU, I/O, Memory, Locks and Latches etc, depending on how badly are the SQL queries written. A SQL query goes through various phases like Evaluation, Transformation, and Optimization before being finally executed. The Oracle Query Optimizer uses the Statistics gathered at the Database level to choose the Optimal data access path. So, gathering Statistics at regular intervals is very important for the SQL queries to perform efficiently.

There are various tools provided by Oracle which could be used to monitor and resolve performance issues, for example AWR and ADDM Reports, Session Tracing, Performance Views etc. We would be looking into these tools in detail in the next posts.

Click here to find how to handle high CPU usage issues

Have a question or comment? Post it below!