Oracle DBA Daily Experiences

Immediately after a successful RDBMS installation (perhaps even including a sample DB instance creation), sqlplus will not start:

[oracle@test ~]$ sqlplus ‘/as sysdba’
sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

The reason is that SELinux is running in “enforcing” mode.

You can check it on file /etc/pam.d/login

Oracle development has recommended the following workaround while they correct the problem:

Switch SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode.

Commands, as root:
======================
getenforce       (returns “Enforcing”)
setenforce 0
getenforce       (returns “Permissive”)

This allows SELinux to continue running, and logging denial messages, but SELinux will not actually deny any operations. Once Development has resolved this issue, you can (and should) return SELinux to the default “Enforcing” mode as follows:

Commands, as root:
======================
setenforce 1
getenforce       (returns “Enforcing”)

Using the following query one can find the datafiles that had nologging turned on, direct load or unrecoverable option was used on a datafile since the last successful backup.

SELECT df.name, to_char(df.unrecoverable_time, ‘DD-MON-YYYY HH24:MI:SS’) unrecover_time
FROM v$datafile df,
(SELECT bd.file#, max(completion_time) completion_time
FROM v$backup_datafile bd GROUP BY bd.file#) bd
WHERE bd.file# (+) = df.file#
AND df.unrecoverable_time > bd.completion_time;

If it returns no rows it means no unrecoverable/nologging options were used since the last backup.

NAME UNRECOVER_TIME
—————————————————————- ———————-
/u01/oradata/TESTDB/INDX_01.dbf 27-APR-2009 13:31:52
/u03/oradata/TESTDB/DATA_01.dbf 27-APR-2009 03:52:59

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’ is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set to “BASIC” table monitoring is disabled. In 10g when the value of statistics_level is “typical” or “all” table monitoring is turned enabled. In 9i one can enable/disable table monitoring by calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/
– shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE
…

Note: To find schema level stats that are stale one can call DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

The view has the monitoring information about tables user_tab_modifications, all_tab_modifications and dba_tab_modifications.

To use automatic shared memory management, the value of statistics_level must be set to “typical” or “all”. The parameter can be changed without restarting the database.

The following views shows information on SGA resize operations:
V$SGA_RESIZE_OPS – the view contains information about the last 400 completed SGA resize operations.
V$SGA_DYNAMIC_COMPONENTS – Shows current size, minimum size, maximum size, last operation (LAST_OPER_TYPE value: GROW, SHRINK, STATIC), OPER_COUNT (# of times SGA operations performed, at startup the value is 0) during the instance started of SGA components.
V$SGA_DYNAMIC_FREE_MEMORY – Shows free memory available for future SGA memory resize.
V$SGA_CURRENT_RESIZE_OPS – Currently SGA resize operations in progress.
V$SGAINFO – has usage and free size informaton on SGA size of different components.
V$SGASTAT – detail information on usage of SGA
V$SGA_DYNAMIC_COMPONENTS – information on dynamic components of SGA
V$SGA_TARGET_ADVICE – gives information on tuning of SGA_TARGET

Automatic memory management has it’s own background process (mman). It monitors the instance to find the best memory allocation for SGA.

If the database is iin automatic shared memory enabled the value of statistics_level cannot be changed to basic. If the value of sga_target is not set or if the value is 0 automatic shared memory management, statistics_level can be set to basic which means automatic memory management is turned off.

SQL> alter system set statistics_level=basic;
alter system set statistics_level=basic
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

If SGA_MAX_SIZE is not set or it is less than SGA_TARGET then Oracle will SGA_MAX_SIZE to be the same value as SGA_TARGET. If one tries to increase SGA_TARGET to more than SGA_MAX_SIZE then you will get ORA-00823 so you may want to set SGA_MAX_SIZE which will allow one to increase SGA with restart of the instance.

SQL> show parameter sga;

NAME TYPE VALUE
———————————— ———– ——————————
..
sga_max_size big integer 160M
sga_target big integer 160M

SQL> alter system set sga_target=170M;
alter system set sga_target=170M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

Using stty one can set some of keys like backsapce, interrupt, suspend and kill.
$ stty -a # shows the current terminal settings
intr = ^C; quit = ^\; erase = ^?; kill = ^U; eof = ^D; eol = ; eol2 = ; start = ^Q;
stop = ^S; susp = ^Z; rprnt = ^R; werase = ^W; lnext = ^V; flush = ^O; min = 1; time = 0;

$ stty erase \^? # set backspace key for backspace
$ stty intr \^C # set Control-C for interrupt
$ stty susp \^Z # set Control-Z for suspend
$ stty kill \^U # set Control-U for kill

Using strace (linux)/truss (AIX) one can trace the system calls as program executes. It can be useful in identifying where a program identify an issue depending on the situation.

Example below shows output when strace is called for echo
$ strace echo “Hello”
execve(“/bin/echo”, ["echo", "Hello"], [/* 42 vars */]) = 0 – shows process being executed
uname({sys=”Linux”, node=”localhost.localdomain”, …}) = 0
brk(0) = 0×505000
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95556000
access(“/etc/ld.so.preload”, R_OK) = -1 ENOENT (No such file or directory)
open(“/etc/ld.so.cache”, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=101242, …}) = 0
mmap(NULL, 101242, PROT_READ, MAP_PRIVATE, 3, 0) = 0x2a95557000
close(3) = 0
open(“/lib64/tls/libc.so.6″, O_RDONLY) = 3 – libraries being loaded
read(3, “\177ELF\2\1\1\3>\1\240\304\241\f=”…, 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=1622288, …}) = 0
mmap(0x3d0ca00000, 2314184, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x3d0ca00000
mprotect(0x3d0cb2c000, 1085384, PROT_NONE) = 0
mmap(0x3d0cc2c000, 20480, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x12c000) = 0x3d0cc2c000
mmap(0x3d0cc31000, 16328, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x3d0cc31000
close(3) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95570000
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95571000
mprotect(0x3d0cc2c000, 12288, PROT_READ) = 0
mprotect(0x3d0c914000, 4096, PROT_READ) = 0
arch_prctl(ARCH_SET_FS, 0x2a95570b00) = 0
munmap(0x2a95557000, 101242) = 0
brk(0) = 0×505000
brk(0×526000) = 0×526000
open(“/usr/lib/locale/locale-archive”, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=48509536, …}) = 0
mmap(NULL, 48509536, PROT_READ, MAP_PRIVATE, 3, 0) = 0x2a95572000
close(3) = 0
fstat(1, {st_mode=S_IFCHR|0600, st_rdev=makedev(136, 2), …}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a983b6000
write(1, “Hello\n”, 6Hello — message being written to stdout (1)
) = 6
exit_group(0) = ?

In v$session osuser, program are not set when oracle client 10.2.0.1 is used when SQLDeveloper and oracle client are installed on the same machine. This issue doesn’t occur when oracle client software (10.2.0.1) is not installed with SQLDeveloper, a workaround the issue is to update the following JDBC jars in $ORACLE_HOME. The jar files can be downloaded from the following URL http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html.

Following jars were replaced in oracle client directory (Note: you may want to backup the original jars before overwriting them):
ojdbc14.jar – $ORACLE_HOME/jdbc/lib
ojdbc14_g.jar – $ORACLE_HOME/jdbc/lib
ojdbc14dms.jar – $ORACLE_HOME/jdbc/lib
ojdbc14dms_g.jar – $ORACLE_HOME/jdbc/lib
orai18n.jar – $ORACLE_HOME/jlib

When an java app connects to oracle depending on the driver such as osuser, program in v$session will not be set (i.e. will be NULL) in Oracle. This issue is fixed in 10.2.0.4 oracle JDBC driver which can be downloaded from the following URL (http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html).

Another workaround this issue is to pass the value in java.util.Properties when establishing the connection.
(This workaround fix depends on the driver being used so it may not work in all situations).
….
java.util.Properties props = new java.util.Properties();

props.put(“v$session.program”, “testprogram”);
props.put(“v$session.osuser”,”Mike”);
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:scott/tiger@host:1521:TESTSID”, props);
….

In a separate window, during the execution of the program one can see the value of osuser and program is set.
SELECT osuser, program from V$session
OSUSER PROGRAM
————————- —————————
Mike testprogram

h – Left
l – Right
k – Up
j – Down
w – Move forward to beginning of next word
e – Move end of the word
b – Move backward to beginning of the previous word
0 – First position on the line
$ – Last position on the line
^ – First non-blank position on the line
+ or <Return> – First non-blank character on the next line
- – First non-blank character on the previous line
n| – Column n of the current line
H – Top line of screen
L – Bottom line of screen
M – Middle line of screen

Here are some of the scenarios when one gets ORA-01031:

1) Insufficient privileges error is when “oracle” user is not part of the “dba” group when one tries to connect as sysdba, so it’s required user oracle is part of the “dba” group As you see in the following example below:

$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Apr 5 16:26:06 2009

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:
$ id
uid=501(oracle) gid=500(oinstall) groups=500(oinstall) context=user_u:system_r:unconfined_t

2) A user/schema tries to truncate table owned by another user/schema and if the user doesn’t have access one will receive ORA-01031.

– connect as user1 which is trying to truncate table owned by scott
SQL> connect user1
Password:

SQL> truncate table scott.table1
truncate table scott.table
*
ERROR at line 1:
ORA-01031: insufficient privileges

There are couple of ways to grant this access:
(1) grant user1 “DROP ANY TABLE” granting this access may be an issue as the user can drop a table in any schema so the work around.
(2) Another way to give this grant is to create a store procedure in scott’s schema that truncates the table and grant user1 execute access to the store procedure.