Oracle RDBMS & Solaris : Few Random Tips (Feb 2012)

These tips are just some quick solutions or workarounds. Use these quickies at your own risk.

[#1] Oracle Data Pump

Q: How to exclude the table definition while importing a table using Oracle Data Pump import utility?

A: Use EXCLUDE=TABLE/TABLE option.

eg.,

impdp login/password DUMPFILE=<DUMP_FILENAME> LOGFILE=<LOGFILE_NAME> \
 DIRECTORY=<DB_DIR_NAME> TABLES=<TABLE_NAME> EXCLUDE=TABLE/TABLE

---

[#2] Workaround to ORA-01089: immediate shutdown in progress - no operations are permitted

When the database is in the middle of an instance shutdown, if another shutdown or startup was attempted, Oracle RDBMS may throw the above ORA-01089 error. The workaround is to force Oracle to start the database instance using startup force option. This option will shutdown the database instance (if running) using the abort command and then starts it up.

eg.,

SQL> STARTUP FORCE

---

[#3] Quick steps to upgrade the Oracle database from version 11.2.0.[1 or 2] to 11.2.0.3

Execute the following in the same sequence as sysdba.

startup upgrade
!cd $ORACLE_HOME/rdbms/admin
@utlu112i.sql  /* pre-upgrade information tool */
exec dbms_stats.gather_dictionary_stats (DEGREE => 64);
@catupgrd.sql  /* create/modify data dictionary tables */
@utlu112s   /* all components should be in VALID state */
shutdown immediate
startup
@catuppst.sql  /* upgrade actions that do not require DB in UPGRADE mode */
@utlrp.sql  /* recompile stored PL/SQL and Java code */
SELECT count(*) FROM dba_invalid_objects;  
                        /* verify that all packages and classes are valid */
exit

---

[#4] Q: Solaris: how to get rid of zombie processes?

A: Run the following with appropriate user privileges.

ps -eaf | grep defunct | grep -v grep | preap `awk '{ print $2 }'`

Alternative way: (not as good as the previous one - still may work as expected)

prstat -n 500 1 1 | grep zombie | preap `awk '{ print $1 }'`

---

[Added on 03/01/2012]

[#5] Solaris: Many TCP listen drops

eg.,

# netstat -sP tcp | grep tcpListenDrop
        tcpListenDrop       =2442553     tcpListenDropQ0     =     0

To alleviate numerous TCP listen drops, bump up the value for the tunable tcp_conn_req_max_q

# ndd -set /dev/tcp tcp_conn_req_max_q <value>

---

[Added on 03/02/2012]

[#6] Solaris ZFS: listing all properties and values for a zpool

Run: zfs get all <zpool_name> as any OS user

eg.,

% zpool list
NAME    SIZE  ALLOC   FREE    CAP  HEALTH  ALTROOT
rpool   276G   167G   109G    60%  ONLINE  -
spec    556G   168G   388G    30%  ONLINE  -

% zfs get all rpool
NAME   PROPERTY              VALUE                  SOURCE
rpool  type                  filesystem             -
rpool  creation              Fri May 27 17:06 2011  -
...
rpool  compressratio         1.00x                  -
rpool  mounted               yes                    -
rpool  quota                 none                   default
rpool  reservation           none                   default
rpool  recordsize            128K                   default
...
rpool  checksum              on                     default
rpool  compression           off                    default
...
rpool  logbias               latency                default
rpool  sync                  standard               default
rpool  rstchown              on                     default

---

[#7] Solaris: listing all ZFS tunables

Run: echo "::zfs_params" | mdb -k with root/super-user privileges

eg.,

# echo "::zfs_params" | mdb -k
arc_reduce_dnlc_percent = 0x3
zfs_arc_max = 0x10000000
zfs_arc_min = 0x10000000
arc_shrink_shift = 0x5
zfs_mdcomp_disable = 0x0
zfs_prefetch_disable = 0x0
..
..
zio_injection_enabled = 0x0
zvol_immediate_write_sz = 0x8000