Solution to ORA-28547: connection to server failed, probable Oracle Net admin error

When trying to perform sqlplus actions against one of my databases, I received

oracle@oric-dbserver01:[mydb01]# sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 17 10:43:56 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error

Cause:

This is a consequence of the fact that oracle nls files are not found in its default location for this particular server.

If there are no .nlb files in $ORACLE_HOME/nls/data, you need to find out where they are located and set the ORA_NLS10 parameter correctly

If you lookup the error you will get a hint about this fact:

oracle@oric-dbserver01:[mydb01]# oerr ora 28547
A failure occurred during initialization of a network connection from a client process to the Oracle server.  ... 

The most frequent specific causes are: [ oracle lists several potential causes here ]

The character set of the database is not recognized by the client process, which may be caused by an incorrect or unnecessary setting 
of the ORA_NLS10 client environment variable or by a new or user-defined character set installed in the Oracle server and used for the database.

Solution:

Set the ORA_NLS10 environment variable:

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

When this is done, sqlplus will work.

Don't forget to add the same to your .bash_profile for the operating system user owning and running the oracle server software. For most installations I have seen, this will be the os user called "oracle".

How to trim away all extra output from a result using sqlplus

The following code can be put in a shell script and executed by root.

Note the sqlplus directives which will remove all unwanted output from the resulting file /tmp/count_options.out

su - oracle <<!
echo "
set lines 10
set pages 0
set trimspool on
set verify off
set heading off
set feedback off
set echo off
spool /tmp/count_options.out
SELECT TRIM(COUNT(*)) FROM DBA_REGISTRY;
" > /tmp/count_options.sql
sqlplus -s / as sysdba @/tmp/count_options.sql
!

Output is:

 /tmp]# cat count_options.out
4

Note that without the TRIM function, the output will be

/tmp]# cat count_options.out
         4

The -s switch will execute sqlplus silently, without it, the output would be

cat count_options.out
         4
SYS@mydb01>SQL>

How come I cannot logon to my instance as sysdba, when the ORACLE_SID variable is set correctly?

Here's a reminder if you use a symbolic link for your ORACLE_HOME variable:

You will not be able to attach to your instance using OS authentication, like under the following circumstances:

echo $ORACLE_SID
testdb01
echo $ORACLE_HOME=/orasw/oracle/product/current
ls -la /orasw/oracle/product/current
lrwxrwxrwx 1 oracle dba 12 Jan 31 08:34 /orasw/oracle/product/current -> 19.21
ls -la 
sqlplus / as sysdba

The above statement will not be able to connect to your instance.

You can, however, connect to the instance by going through the listener instead:

sqlplus sys@testdb01 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 31 08:38:42 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

SYS@testdb01>SQL>show user
USER is "SYS"

So in order to create a bequeath session, you need an ORACLE_HOME variable that does not use symbolic links.

See this post about IPC and bequeath sessions for more details.

How to use the html markup option in sqlplus

Use the following directives in sqlplus:

set timing on
set trimspool on
set markup html on spool on head MYHEADLINEHERE table "width='50%' border='1'"
spool myfile.html

How to connect via sqlplus with the connect string

To connect as sys using the entire connect string, use the following syntax:

sqlplus /nolog
SQL>connect sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddb01.mydomain.com)(PORT=1534))(CONNECT_DATA=(SERVICE_NAME=proddb01.mydomain.com)(INSTANCE_NAME=proddb01)(SERVER=DEDICATED)))' as sysdba

Result:

Enter password:
Connected.
SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
proddb01  PRIMARY

How to generate dbms_application_info settings together with DDL in a sql script

Many DBAs forget to incoporate usage of dbms_application_info into their own scipts. This is particulary important if you start long-running maintenance jobs that could last for days or weeks.

By sending MODULE and ACTION to the databaser server, you allow for more granular searching using v$session, end-to-end tracing using trcsess. In addtion, more information is collected by any ongoing auditing so it's easier to see who did what later.

Here is a sniplet that can be used when generating a DDL script for multiple objects.

accept table_owner prompt 'Table owner: '
accept table_name prompt 'Table name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_lob_&&table_owner..&&table_name..sql
select 'alter session set nls_language=''american'';' from dual;
select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_move_lob_&&table_owner..&&table_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveLOB_&&table_name''); ' from dual;

-- your DDL statements generated here, for example:
select 'alter table ' || owner || '.' || table_name || ' move tablespace data2 online;'
from dba_tables
where owner='&&table_owner';

select 'exit' from dual;
exit

How to extract all mountpoints for oracle data files in a database

To extract all unique mount points used for datafiles in a database, used the query below:

SELECT distinct SUBSTR(FILE_NAME, 1,
               INSTR(FILE_NAME, '/', -1, 1) --> Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" --> starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
;

Example output:

PATH
---------------------------------
/sales_db_02/oradata/SALES
/salesdb/oradata/SALES
/sales_db_01/oradata/SALES

If the database is in mount-mode (for example, a physical standby database), exchange dba_data_files with v$datafile:

 SELECT distinct SUBSTR(NAME, 1,
                   INSTR(NAME, '/', -1, 1)  
                  -1) "PATH" 
FROM v$datafile;

For logfiles, exchange dba_data_files with v$logfile and file_name with member:

  SELECT distinct SUBSTR(member, 1,
               INSTR(member, '/', -1, 1) 
              -1) "PATH" 
FROM v$logfile
;

How to solve SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

Logged in as the user owning the objects, you are getting the following error when trying to use the autotrace feature:

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Solution: run the necessary scripts first:

conn / as sysdba
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to scott;

conn scott/password

@$ORACLE_HOME/rdbms/admin/utlxplan.sql
exit

After this is done, the errors should no longer appear.

Create a tablespace using input variables

Here is a script to help set up tablespaces based on the path used for the system tablespace:

SET LINES 200
-- NEW_VALUE in sqlplus specifies a variable to hold a column value
COL tabspace_path FORMAT A50 NEW_VALUE path

SELECT SUBSTR(FILE_NAME, 1, INSTR(FILE_NAME, '/', -1) -1) tabspace_path
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSTEM';

SELECT '&path' variable_value
FROM DUAL;

CREATE BIGFILE TABLESPACE test_tbspc DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

exit

Run the script:

oracle@myserver:[testdb01]# sqlplus / as sysdba @test.sql

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


TABSPACE_PATH
--------------------------------------------------
/u02/oradata/testdb01
old   1: SELECT '&path' variable_value
new   1: SELECT '/u02/oradata/testdb01' variable_value

VARIABLE_VALUE
---------------------
/u02/oradata/testdb01

Enter value for tablespace_name: mytablespace
old   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
new   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '/u02/oradata/testdb01/mytablespace.dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED

Tablespace created.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

How to add a line feed to your spool file

If you want to spool out a file from you sqlplus session, and need a line feed in your string, you can use the function

char(int)

to concatenate a line feed into your string. Integer represents the decimal value of the character you'd like to send to the database.

Example:

set trimspool on
set lines 200
set pages 0
set heading off
set verify off
set echo off
set feedback off
spool rowcount.sql
select 'spool rowcount.out' from dual;
select 'prompt rowcount for ' || owner || '.' || table_name || chr(10)|| 'select count(*) from ' || owner || '.'  || table_name || ';'
from dba_tables
where owner='SCOTT';
select 'exit' from dual;
exit

The chr(10) will return a line feed (may not be apparent in the code below but there is a line feed in there):

SYS@fsprod SQL> select chr(10) from dual;

C
-

The chr(10) corresponds to the hexadecimal value of A, which in turns corresponds to a line feed under the characterset that I happen to use in this database, which is WE8ISO8859P1.


The result is a file which looks like this:

spool rowcount.out
prompt rowcount for SCOTT.EMP
select count(*) from SCOTT.EMP;

prompt rowcount for SCOTT.DEPT
select count(*) from SCOTT.DEPT;

prompt rowcount for SCOTT.BONUS
select count(*) from SCOTT.BONUS;
.
.
.
exit

Sources: ISO code pages

What is the difference between a BEQUEATH connection and an IPC connection?

A bequeath connection

runs on your local host

bypasses the listener

the protocol creates the server process for you directly

An IPC (Inter-Process Communication) connection

will use the native protocol on each OS, but uses the generic term "IPC" for all of them

can only be used when the Client and Server reside on the same host

can only be used by having the Client connect through the Oracle Listener

the Database Listener must be configured to listen on an IPC endpoint

the listener spawns the server process for you

Example setup:

Listener.ora
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
   )

tnsnames.ora:

proddb01_ipc =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = proddb01)
     )
   )

Connect to your database locally:

sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015

Copyright (c) 1982,2013, Oracle. All rights reserved.

SQL> connect scott/tiger
Connected.

From another window, create another session:

sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015

Copyright (c) 1982,2013, Oracle. All rights reserved.

SQL> connect scott/tiger@proddb01_ipc
Connected.

Check the connections and their types:

SELECT S.SID, S.OSUSER,S.PROGRAM,S.USERNAME,S.MACHINE, SCI.NETWORK_SERVICE_BANNER,S.LOGON_TIME,S.STATUS
 FROM V$SESSION S INNER JOIN V$SESSION_CONNECT_INFO SCI
 ON S.SID = SCI.SID
 WHERE S.USERNAME = UPPER('scott')
 AND SCI.NETWORK_SERVICE_BANNER LIKE '%IPC%'
 OR  SCI.NETWORK_SERVICE_BANNER LIKE INITCAP('%BEQ%')
 AND S.TYPE <> 'BACKGROUND'
 ORDER BY LOGON_TIME;

And here is the output. Notice how the first session (.. / as sysdba) results in a Bequeath session, while the other one ( ...@proddb_ipc) results in a session using IPC:

SID	OSUSER	PROGRAM	USERNAME	MACHINE	NETWORK_SERVICE_BANNER	LOGON_TIME	STATUS
9	oracle	sqlplus@myserver.mydomain.com (TNS V1-V3)	SCOTT	myserver.mydomain.com	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production	22.01.2015 15:35:49	INACTIVE
1160	oracle	sqlplus@myserver.mydomain.com (TNS V1-V3)	SCOTT	myserver.mydomain.com	Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.4.0 - Production	22.01.2015 15:40:44	INACTIVE

How to create the PLUSTRACE role in order to use the AUTOTRACE feature in sqlplus

Create the PLAN_TABLE in the schema you want to use it for (in this example, SCOTT):

CONNECT SCOTT 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 

Create the PLUSTRACE role:

CONNECT / AS SYSDBA 
@$ORACLE_HOME/sqlplus/admin/plustrce.sql 

Grant the PLUSTRACE role to SCOTT:

CONNECT / AS SYSDBA 
grant plustrace to scott;

You are now able to use the autotrace feature of sqlplus for user SCOTT.

Source: Oracle Documentation

How to deal with ORA-00020: maximum number of processes (%s) exceeded

I recently had a situation where access to the database was completely blocked because of the infamous error message

ORA-00020: maximum number of processes (%s) exceeded

Facts:

The database had processes set to 1000.

The Cloud Control agent was spawning hundreds of processes (obviously an error to troubleshoot as a separate action)

Connecting through sqlplus with os authentication (sqlplus / as sysdba) didn't work due to the same reason

At that time, the database had to become available to the users again ASAP.

When I have encountered these situations in the past, I have had to kill all the operating system processes and restart the instance. A brut-force method that is not particularly pretty, but sometimes necessary:

for a in $(ps -ef |grep $ORACLE_SID | grep -v grep | awk '{ print $2}'); do 
>kill -9 $a; 
>done

It normally does the job when you really have no other option.
This time however, after having killed all the processes, Oracle still rejected connections to the database using sqlplus:

sqlplus /nolog

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 @ SQL> connect /
 ERROR:
 ORA-00020: maximum number of processes (1000) exceeded

I then found the page by tech.e2sn.com that showed how to use sqlplus with the "preliminary connection".

Simply by using

sqlplus -prelim "/as sysdba"

I was able to connect and shutdown the database with the abort option.

sqlplus -prelim "/ as sysdba"

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 SQL> shutdown abort
 ORACLE instance shut down.
 SQL> exit
 Disconnected from ORACLE

After this point the database could once again be restarted:

sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:10:38 2014
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 Connected to an idle instance.
SQL> startup
 ORACLE instance started.

 Total System Global Area 2137886720 bytes
 Fixed Size                  2248080 bytes
 Variable Size            1258291824 bytes
 Database Buffers          855638016 bytes
 Redo Buffers               21708800 bytes
 Database mounted.
 Databasen opened.

The article referred to above is worth reading, but in short, the -prelim option will not try to create private session structures in the SGA. This allows you to connect to perform debugging or shutdown operations.

How to use the glogin.sql file to set global sqlplus attributes

The file $ORACLE_HOME/sqlplus/admin/glogin.sql can be used to define global attributes for users of sqlplus on that particular host.

For example:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER SQL>"
SET LINESIZE 200
SET PAGSEIZE 200

On Production servers, you may consider adding a colored prompt, for example red:

SET SQLPROMPT "'^[[41m'_USER'@'_CONNECT_IDENTIFIER'^[[0m' SQL> "

why am I getting ORA-14006: invalid partition name when attempting to drop a partition in TOAD?

In TOAD you are trying to drop a partition, as follows:

alter table segment_sizes drop partition SYS_P41;

But you get ORA-14006: invalid partition name as a result.

Solution: remove the ";" at the end of the statement, and try again.
Alternatively, execute the statement through SQL*plus.

Source: Derya Oktay's Oracle Weblog

How to use the sqlplus "autotrace" facility

SET AUTOT ON
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace on")

SET AUTOT TRACE
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace traceonly")

Note: Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

SET AUTOT TRACE EXP
Explains the SQL, omits statistics and does not execute the SQL(shorthand for "set autotrace traceonly explain")
The SQL statement is never execute for real, only explained.

SET AUTOT TRACE EXP STAT
Executes the SQL, displays the execution plan, displays statistics. Executes the SQL, but supresses the output (shorthand for "set autotrace traceonly explain statistics")

SET AUTOT OFF
Disables autotraceing (shorthand for "set autotrace off")

If you have trouble getting the autotrace feature to work, make sure you have created the PLUSTRACE role.

Source: Oracle 19c Documentation

How to hide the input from the user when using the accept directive in sqlplus

accept sch prompt "Schema : "
accept pwd prompt "Password (not displayed) : " hide

How to use the PRODUCT_USER_PROFILE (PUP) table to prevent SQL*Plus access to database

This article is relevant to versions < 18.1. In Oracle 18c, the PRODUCT_USER_PROFILE table is deprecated, and will be completely removed in Oracle 19c. For more information, consult the documentation

In cases where you have an oracle client installed on a user's PC, skilled users can easily us sqlplus as a "backdoor" to the database by connecting directly through sqlplus instead of using their designated applications. Here is a trick to prevent that from happening, without too much effort for the DBA nor for the PC maintenance guys.

What is it?
-----------
Users can be restricted based on the SQL*Plus product usage.
This is achieved by inserting the restrictions in the PRODUCT_USER_PROFILE (PUP) table, owned by the SYSTEM user.
SQL*Plus reads the restrictions from the PUP table when a user logs in and applies those restrictions for the session.

How does it work?
-----------------
SQL*Plus will not read the PUP table if a user logs in with the SYSDBA or
SYSOPER privilege and therefore no restrictions will apply.

How to set up?
--------------
The table SQLPLUS_PRODUCT_PROFILE is automatically created on installation in the SYSTEM schema.
To explicitly create it, run pupbld.sql script

Then insert the following lines to prevent user SCOTT from doing DML and SELECT:

insert into product_user_profile(product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','SELECT','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','UPDATE','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','INSERT','DISABLED');
insert into product_user_profile (product,userid,attribute,char_value) values ('SQL*Plus','SCOTT','DELETE','DISABLED');

Check with:

SELECT product,userid,attribute,char_value
FROM system.product_user_profile;

PRODUCT    USERID    ATTRIBUTE             NUMERIC_VALUE 
---------- --------- --------------------  ------------- 
SQL*Plus   SCOTT     DELETE                DISABLED
SQL*Plus   SCOTT     INSERT                DISABLED
SQL*Plus   SCOTT     SELECT                DISABLED

NB!
To prevent access from using PL/SQL:
For example, the DELETE privilege disabled for user SCOTT can easily be executed through a PL/SQL block!
This can be avoided by removing the PL/SQL block creation access itself.

insert into system.product_profile (product, userid, attribute, char_value) 
                               values ('SQL*Plus', 'SCOTT', 'DECLARE', 'DISABLED'); 

   insert into system.product_profile (product, userid, attribute, char_value) 
                             values ('SQL*Plus', 'SCOTT', 'BEGIN', 'DISABLED'); 

Result:

SQL> connect scott/tiger
Connected.
SQL> select * from user_tables;
SP2-0544: Command "select" disabled in Product User Profile

Restrictions:

- Can only be used for SQL*Plus
- Only applies to local databases. The PUP table restrictions will not be implemented using a database link to a remote database.

More info here

How to properly mask qotation marks in an SQL statement

Example:

You want to insert the string

order_date=to_date('2013-11-14','YYYY-MM-DD')

into a column of a table.

For oracle to understand that you want the single quotes to be part of the inserted string, you need to mask it as below:

INSERT INTO SCOTT.FILTER_TABLE
       (filter_name,
        filter_priority,
        table_owner_pattern,
        table_name_pattern,
        column_name_pattern,
        sql_filter)
VALUES ('MYFILTER',
        3,
        'LXPROD%',
        '%',
        'ORDER_DATE',
        'order_date=to_date(''&&target_date'',''YYYY-MM-DD'')');