Monday, September 18, 2006

HP-UX top

It can be useful to read the changes document that accompanies each new software version. On HP-UX, a flag was added to the top command to not display individual CPU utilization metrics. This can be useful for allowing more process lines to be displayed on machines that have many CPUs.

From the HP example:
Once a 2 processor system, executing top displays individual CPU statistics:

System: 2wide Tue Apr 20 16:15:08 1
999
Load averages: 0.50, 0.51, 0.85
101 processes: 96 sleeping, 4 running, 1 zombie
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 1.00 91.1% 0.0% 0.8% 8.1% 0.0% 0.0% 0.0% 0.0%
1 0.00 0.0% 0.0% 0.0% 100.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.50 45.5% 0.0% 0.4% 54.1% 0.0% 0.0% 0.0% 0.0%

Memory: 125556K (101488K) real, 140160K (121952K) virtual, 14404K free Pag
e# 1/3
.
.
.


Once the same 2 processor system, executing top -h displays the average CPU statistics:

System: 2wide Tue Apr 20 16:15:59 1
999
Load averages: 0.50, 0.51, 0.84
101 processes: 96 sleeping, 4 running, 1 zombie
Cpu states: (avg)
LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0.50 49.6% 0.0% 1.2% 49.2% 0.0% 0.0% 0.0% 0.0%

Memory: 131488K (107388K) real, 146016K (127172K) virtual, 8460K free Page
# 1/3
.
.
.

Sunday, September 17, 2006

Oracle OCI Error

Recently a PC user was able to use most functionality of an application. One part of the application would consistently encounter ORA-01036: illegal variable name/number. In this case, the issue was an incorrect version of Oracle networking on the PC. The issue was resolved with removal of the more recent Oracle networking version, and installation of the correct version.

Thursday, September 07, 2006

Generate Oracle ReGrants "WITH GRANT OPTION"

Oracle user "tableowner" owns tables.
User tableowner grants SELECT to user "reportowner".
Report owner creates view "V" and grants SELECT on V to role "reportrole".
Role "reportrole" is granted to user "enduser".

The reportowner is able to create and select from the views. Enduser receives permission errors when selecting from reportowner's views. To resolve this issue, grant select WITH GRANT OPTION to the selectrole.

In this case the SELECT grants were already in place, and they needed be upgraded to include "WITH GRANT OPTION". This SQL will generate a SQL script that can be run to apply the "WITH GRANT OPTION".

$ cat gen_grantSelect.sql
set lines 90
set pages 0
set feedback off

spool grantSelect.sql
prompt spool grantSelect

select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || 'REPORTOWNER with grant option;'
from dba_tab_privs
where grantor='TABLEOWNER' and grantee = 'REPORTOWNER'
order by 1
/
spool off

Tuesday, September 05, 2006

Oracle Archive Log Mode

Follow these steps to take an Oracle database out of archive log mode.

Log in to sqlplus as sysdba.

-- shutdown the instance.
shutdown
startup mount exclusive
alter database noarchivelog
alter database open
-- check alert.log for archiving messages.



Follow these steps to put an Oracle database into archive log mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5554802160 bytes
Fixed Size 744944 bytes
Variable Size 2164260864 bytes
Database Buffers 3388997632 bytes
Redo Buffers 798720 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> show parameter log_archive_start;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean TRUE
SQL> alter system switch logfile;

System altered.


From alert.log:
Tue Aug 8 22:09:10 2006
Thread 1 advanced to log sequence 4224
Current log# 4 seq# 4224 mem# 0: /u111/oradata/KANQA/redo04a.log
Current log# 4 seq# 4224 mem# 1: /u110/oradata/KANQA/redo04b.log
Tue Aug 8 22:09:10 2006
ARC0: Evaluating archive log 6 thread 1 sequence 4223
ARC0: Beginning to archive log 6 thread 1 sequence 4223
Creating archive destination LOG_ARCHIVE_DEST_2: '/u116/oraarchive/KANQA/arch4223.arc'
ARC0: Completed archiving log 6 thread 1 sequence 4223


Verify the archived log file has been created.
$ ls -l /u116/oraarchive/KANQA
total 2656
-rw-rw---- 1 oracle dba 1352704 Aug 8 22:09 arch4223.arc