Where to find the database characterset in the dictionary

SQL> select value$ from sys.props$ where name = 'NLS_CHARACTERSET';

 VALUE$
 --------------------------------------------------------------------------------
 WE8MSWIN1252

 SQL> select * from nls_database_parameters WHERE PARAMETER='NLS_CHARACTERSET';

 PARAMETER                      VALUE
 ------------------------------ ----------------------------------------
 NLS_CHARACTERSET               WE8MSWIN1252

How to work around dbca error "The Oracle system identifier ... already exists, specify another SID"

If you have previously created a database through dbca and saved it as a template for future use, you may see the following error when trying to specify the same SID again:

The Oracle system identifier ... alerady exists, specify another SID

Solution to the problem is to remove the entry for the SID in /etc/oratab

Then click next in dbca, and the configuration wizard will proceed.

Thanks to Elena for pointing this out in her blog.

Potential cause of ORA-12547: TNS:lost contact

I was running a script against the database directly on the host, when the script timed out with:

ERROR:
 ORA-12547: TNS:lost contact

After a search on Oracle's support site I found doc 555565.1: Troubleshooting ORA-12547 TNS: Lost Contact

One potential cause was incorrect permissions on the oracle binary in the $ORACLE_HOME/bin directory. I checked my old OH and compared it with the new OH:

server1>cd $ORACLE_HOME
server1>pwd
 /u01/oracle/product/11204
server1>ls -latr bin/oracle
 -rwxr-x--x    1 ora11g   dba       309704130 Jun 11 11:00 bin/oracle
server1>ls -latr /u01/oracle/product/11.2.0.2/bin/oracle
 -rwsr-s--x    1 ora11g   dba       279291858 Mar 12 13:42 /u01/oracle/product/11.2.0.2/bin/oracle

As can be seen from the above statement, the oracle server executable was lacking the setuid bit

This bit is needed, since we need to make sure that any user who runs the executable file will inherit the user ID of the owner (or group) of the executable file. Note that LOCAL connections would work just fine, even if the permissions on the oracle executable is set incorrectly; only connections that are passed from the listener to the Oracle server are affected. These sessions must be able to spawn a new server process.

Oracle points out that this error is commonly seen "in environments where the listener is running in the GRID home and servicing connections to an instance in a different $ORACLE_HOME."

Connections via the listener are failing with ORA-12547. It is likely in this scenario that LOCAL or BEQ connections to the instance are successful.

Solution to my particular problem this time was to give $ORACLE_HOME/bin/oracle the proper permissions:

server1> sqlplus / as sysdba
SQL> shutdown immediate

server1>chmod 6751 oracle
server1>ls -latr oracle
 -rwsr-s--x    1 ora11g   dba       309704130 Jun 11 11:00 oracle

server1> sqlplus / as sysdba
SQL> startup

After this change the script ran through with no ORA-12547 errors.

Sources: Oracle System Administration Guide

How to disable or drop an SQL Profile

Find the profiles currently saved in the database:

SELECT NAME, CREATED,LAST_MODIFIED,TYPE,STATUS,FORCE_MATCHING
FROM DBA_SQL_PROFILES
ORDER BY CREATED DESC;

NAME	CREATED	LAST_MODIFIED	TYPE	STATUS	FORCE_MATCHING
SYS_SQLPROF_0146b2c081f9011a	19.06.2014 08:11:39,000000	19.06.2014 08:11:39,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_0146b2bf0d430118	19.06.2014 08:10:03,000000	19.06.2014 08:10:03,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_0146af25274a0117	18.06.2014 15:23:06,000000	18.06.2014 15:23:06,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_0146af1a61f40116	18.06.2014 15:11:20,000000	18.06.2014 15:11:20,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_0146af18d0960115	18.06.2014 15:09:37,000000	18.06.2014 15:09:37,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_0146af150fc30114	18.06.2014 15:05:31,000000	18.06.2014 15:05:31,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_01462313623f0113	22.05.2014 10:36:51,000000	22.05.2014 10:36:51,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_01461857e9680112	20.05.2014 08:35:53,000000	20.05.2014 08:35:53,000000	MANUAL	ENABLED	NO
SYS_SQLPROF_014614c584ab0111	19.05.2014 15:57:07,000000	19.05.2014 15:57:07,000000	MANUAL	ENABLED	NO

To disable the profile, which means they will not any longer be considered by the CBO until you once again accept them:

set serveroutput on
 begin
   dbms_sqltune.alter_sql_profile(name=>'SYS_SQLPROF_0146af25274a0117', attribute_name=>'STATUS',value=>'DISABLED');
 end;
/

To completely drop the profile:

set serveroutput on
 begin
   dbms_sqltune.drop_sql_profile(name=>'SYS_SQLPROF_0146af25274a0117');
end;
/

What does the TERRITORY component of the NLS_LANG parameter control?

From OTN:

"The territory component of the NLS_LANG parameter controls the operation of a subset of globalization support features.
It specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name, for example, AMERICA, FRANCE, or CANADA.
If the territory is not specified, then the value is derived from the language value."

For examples please see the Globalization Support Guide

How to work around ORA-12705 when connecting to your database

You get:

ERROR:
 ORA-12705: Cannot access NLS data files or invalid environment specified

Solution:
Incorrect or misspelled NLS_LANG.

For example

NLS_LANG=AMERICAN_NORWAY.UTF-8

is incorrect.

However

export NLS_LANG=AMERICAN_NORWAY.UTF8

is correct.

How to install the instant client from Oracle on a Linux application server

This is how I quite quickly installed sqlplus on a Linux appserver on request from my customer:

1. Download the two files

instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

from the otn network.


2. Create Directory for the installation files

mkdir  /u01/oracle/product/admin/install

3. Ftp the files over to the server

4. Put the installation files in the install Directory:

mv /tmp/instantclient*.zip /u01/oracle/product/admin/install

5. Unzip the files:

unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /u01/oracle/product
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip -d /u01/oracle/product

The files will be unzipped to the Directory /u01/oracle/Product/instantclient_11_2

6. Create a Directory for networking:

mkdir $ORACLE_HOME/network/admin

7. set up the .bash_profile:

export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/product/instantclient_11_2
export PATH=$PATH:$HOME/bin:$ORACLE_HOME
export LD_LIBRARY_PATH=/u01/oracle/product/instantclient_11_2
export NLS_LANG=NORWEGIAN_NORWAY.UTF8
export TNS_ADMIN=$ORACLE_HOME/network/admin

8. Create file tnsnames.ora, add an entry for each of the databases you'd like to Access via sqlplus.

All done!


Note that you do need *both* the basic and the sqlplus instantclient zip files to be unpacked into the same directory. If not, you will be missing Libraries in your installation and you'll receive the following error when launching sqlplus:

sqlplus: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or Directory