Tuesday, December 29, 2009

Null and execution plan for B* Tree Index and Bitmap Index

Finding 1: B* Tree Index does not contain NULL values, so it can only be used in "some" scenarios when the column defined as "NOT NULL"


SQL> create table t
2 as
3 select prod_id,cust_id,promo_id from sh.sales;

Table created.

SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER

SQL> create index t_n1 on t(prod_id);

Index created.



SQL> explain plan for select count(*) from t;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2225357383

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 718 (1)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_N1 | 967K| 718 (1)| 00:00:09 |
----------------------------------------------------------------------


When NULL is possible for that column, then full tablescan must be used, instead of INDEX FAST FULL SCAN.


SQL> alter table t modify (prod_id null);

Table altered.

SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NUMBER
CUST_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER

SQL> explain plan for select count(*) from t;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 817 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 967K| 817 (1)| 00:00:10 |
-------------------------------------------------------------------



NULL Value is included in the bitmap indexes.


SQL> drop index t_n1;

Index dropped.

SQL> create bitmap index t_b1 on t(prod_id);

Index created.

SQL> explain plan for select count(*) from t;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3600098451

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 967K| 27 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| T_B1 | | | |
------------------------------------------------------------------------------

Sunday, December 27, 2009

CPU_COUNT initialization parameter

Following 2 behaviors are observed when altering CPU_COUNT initialization parameter.

Envrionment:


SQL> select * from v$version;

BANNER
---------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Observation 1: CPU_COUNT is capped to 3x initial CPU_COUNT value


SQL> show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- -----------------
cpu_count integer 2

SQL> alter system set cpu_count=100;

System altered.

SQL> show parameter cpu_count

NAME TYPE VALUE
------------------------------------ ----------- -----------------------
cpu_count integer 6


2009-12-27 20:12:46.709000 +08:00
Requested CPU count 100 higher than the allowed maximum (6), capped
(3 times CPU count at instance startup)
RM cpu_count => Low Server Threshold (2) : High Server Threshold (5)
ALTER SYSTEM SET cpu_count=100 SCOPE=BOTH;


Observation 2: Setting CPU_COUNT=0 will let Oracle to decide the correct value


SQL> alter system set cpu_count=0;

System altered.

SQL> show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
cpu_count integer 2
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 2


2009-12-27 20:20:41.798000 +08:00
Setting CPU count to 2
ALTER SYSTEM SET cpu_count=0 SCOPE=BOTH;

Wednesday, December 23, 2009

ORA-12005: may not schedule automatic refresh for times in the past

Symptoms:

File: alert_orcl.log

Wed Dec 23 16:44:04 2009
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_20112.trc:
ORA-12012: error on auto execute of job 23
ORA-12005: may not schedule automatic refresh for times in the past

File: orcl_j000_20112.trc

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_20112.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rh4.ncs.com.sg
Release: 2.6.18-128.1.10.el5
Version: #1 SMP Thu May 7 10:39:21 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 20112, image: oracle@rh4.ncs.com.sg (J000)


*** 2009-12-23 16:44:04.666
*** SESSION ID:(19.444) 2009-12-23 16:44:04.666
*** CLIENT ID:() 2009-12-23 16:44:04.666
*** SERVICE NAME:(SYS$USERS) 2009-12-23 16:44:04.666
*** MODULE NAME:() 2009-12-23 16:44:04.666
*** ACTION NAME:() 2009-12-23 16:44:04.666

ORA-12012: error on auto execute of job 23
ORA-12005: may not schedule automatic refresh for times in the past


Root Causes:

Next schedule based on interval evaluate to "past time" due to wrong interval formula.

Wrong formula to calculate 30 minutes interval:

trunc(SYSDATE,'HH')+30/60/24

Correct formular to calculate 30 minutes interval:

trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60)

Solutions:


SQL> exec dbms_job.interval(job=>23,interval=>'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)');

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select next_date,interval from dba_jobs where job=23;

NEXT_DATE
--------------------
INTERVAL
---------------------------------------------------------------------------------
2009-DEC-23 17:00:04
trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60)

Tuesday, December 22, 2009

Using dbms_system.ksdddt to write customized trace file


[oracle@rh4 sql]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 22 12:06:51 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @dbms_utility_demo.sql
SQL> declare
2 elapsed_time_t1 number;
3 elapsed_time_t2 number;
4 cpu_time_t1 number;
5 cpu_time_t2 number;
6 begin
7 elapsed_time_t1 := dbms_utility.get_time;
8 cpu_time_t1 := dbms_utility.get_cpu_time;
9 dbms_stats.gather_schema_stats('SPOTLIGHT');
10 elapsed_time_t2 := dbms_utility.get_time;
11 cpu_time_t2 := dbms_utility.get_cpu_time;
12 sys.dbms_system.ksdddt; -- Prints the date stamp to the target file (trace,alertlog, or both)
13 -- 1st argument of dbms_system.ksdwrt
14 -- 1: Write to the standard trace file
15 -- 2: Write to the alert log
16 -- 3: Write to both files at once
17 sys.dbms_system.ksdwrt(3,'=== Elapsed Time: '||
18 to_char((elapsed_time_t2-elapsed_time_t1)/100) ||
19 ' sec CPU: '||
20 to_char((cpu_time_t2-cpu_time_t1)/100) ||
21 ' sec');
22 end;
23 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30185.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



[oracle@rh4 sql]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30185.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30185.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rh4.ncs.com.sg
Release: 2.6.18-128.1.10.el5
Version: #1 SMP Thu May 7 10:39:21 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 30185, image: oracle@rh4.ncs.com.sg (TNS V1-V3)


*** 2009-12-22 12:07:07.982
*** SESSION ID:(20.210) 2009-12-22 12:07:07.982
*** CLIENT ID:() 2009-12-22 12:07:07.982
*** SERVICE NAME:(SYS$USERS) 2009-12-22 12:07:07.982
*** MODULE NAME:(sqlplus@rh4.ncs.com.sg (TNS V1-V3)) 2009-12-22 12:07:07.982
*** ACTION NAME:() 2009-12-22 12:07:07.982

=== Elapsed Time: 1 sec CPU: .97 sec

*** 2009-12-22 12:07:15.051
Processing Oradebug command 'setmypid'

*** 2009-12-22 12:07:15.051
Oradebug command 'setmypid' console output:

*** 2009-12-22 12:07:20.178
Processing Oradebug command 'tracefile_name'

*** 2009-12-22 12:07:20.178
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30185.trc
[oracle@rh4 sql]$



[oracle@rh4 sql]$ tail -n 4 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
Tue Dec 22 10:28:37 2009
SMCO started with pid=19, OS id=9591
Tue Dec 22 12:07:07 2009
=== Elapsed Time: 1 sec CPU: .97 sec

Sunday, December 20, 2009

11gR2 PGA related hidden parameters

These parameter name with 'pga' are in bytes, and these parameter name with 'smm' are in kilobytes.


SQL> @pga_parameter.sql
SQL> SELECT x.ksppinm name,
2 y.ksppstvl value,
3 x.ksppdesc description
4 FROM x$ksppi x, x$ksppcv y
5 WHERE x.inst_id = userenv('Instance')
6 AND y.inst_id = userenv('Instance')
7 AND x.indx = y.indx
8 AND (x.ksppinm like '%pga%'
9 OR x.ksppinm like '%smm%')
10 /

NAME VALUE DESCRIPTION
------------------------- ---------- ------------------------------
_pga_large_extent_size 1048576 PGA large extent size
_use_ism_for_pga TRUE Use ISM for allocating large e
xtents

_ldr_pga_lim 0 pga limit, beyond which new pa
rtition loads are delayed

_kdli_sio_pga FALSE use PGA allocations for direct
IO

_kdli_sio_pga_top FALSE PGA allocations come from topl
evel PGA heap

_pgactx_cap_stacks FALSE capture stacks for setting pga
ctx

pga_aggregate_target 0 Target size for the aggregate
PGA memory consumed by the ins
tance

__pga_aggregate_target 188743680 Current target size for the ag
gregate PGA memory consumed

_pga_max_size 209715200 Maximum size of the PGA memory
for one process

_smm_auto_min_io_size 56 Minimum IO size (in KB) used b
y sort/hash-join in auto mode

_smm_auto_max_io_size 248 Maximum IO size (in KB) used b
y sort/hash-join in auto mode

_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size pol
icy cost functions

_smm_control 0 provides controls on the memor
y manager

_smm_trace 0 Turn on/off tracing for SQL me
mory manager

_smm_min_size 184 minimum work area size in auto
mode

_smm_max_size 36864 maximum work area size in auto
mode (serial)

_smm_px_max_size 92160 maximum work area size in auto
mode (global)

_smm_retain_size 0 work area retain size in SGA f
or shared server sessions (0 f
or AUTO)

_smm_bound 0 overwrites memory manager auto
matically computed bound

_smm_advice_log_size 0 overwrites default size of the
PGA advice workarea history l
og

_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
_smm_freeable_retain 5120 value in KB of the instance fr
eeable PGA memory to retain

_smm_isort_cap 102400 maximum work area for insertio
n sort(v1)


23 rows selected.

SQL>

How to disable yum faster mirror plugin (CentOS/Fedora Core)

Symptoms:

Faster-mirror plugin for YUM does not choose the "faster" mirrors, which are within my country.

For example, YUM faster-mirror chooses a mirror in Australia, instead of Singapore.

[root@vmxdb02 ~]# yum update
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.aarnet.edu.au
* updates: mirror.optus.net
* addons: mirror.aarnet.edu.au
* extras: mirror.primusdatacentre.com.au
primary.sqlite.bz2 | 1.3 MB 01:28

Current download cancelled, interrupt (ctrl-c) again within two seconds to exit.

primary.sqlite.bz2 | 0 B 00:00


Solutions:

Step 1, disable fast-mirror plugin, by editing file "/etc/yum/pluginconf.d/fastestmirror.conf", change "enabled=1" to "enabled=0"

[main]
enabled=0
verbose=0
socket_timeout=3
hostfilepath=/var/cache/yum/timedhosts.txt
maxhostfileage=10
maxthreads=15
#exclude=.gov, facebook


Step 2, get URLs for list of mirrors near you. This URL is for CentOS: http://www.centos.org/modules/tinycontent/index.php?id=32


Step 3, Comments these mirrorlist URLs, and use the baseurl (multiple baseurls can be put together to enable failover.) Including "failovermethod=priority" line as well.

[base]
name=CentOS-$releasever - Base
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
failovermethod=priority
baseurl=http://mirror.averse.net/centos/$releasever/os/$basearch/
http://mirror.nus.edu.sg/centos//$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#released updates
[updates]
name=CentOS-$releasever - Updates
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
failovermethod=priority
baseurl=http://mirror.averse.net/centos/$releasever/updates/$basearch/
http://mirror.nus.edu.sg/centos//$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

Friday, December 18, 2009

ORA-16766: Redo Apply unexpectedly offline in Dataguard environment

Synptoms:


DG 2009-12-18-20:32:19 0 2 0 Executing SQL [ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE]
DG 2009-12-18-20:32:25 0 2 0 SQL [ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE] Executed
successfully
DG 2009-12-18-20:32:46 0 2 705961914 rfm_inst_phase_dispatch 16 END phase processing
DG 2009-12-18-20:32:46 0 2 705961914 DMON: CTL_ENABLE operation completed
DG 2009-12-18-20:32:56 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-12-18-20:32:56 0 2 705959745 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-12-18-20:33:56 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-12-18-20:33:56 0 2 705959748 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-12-18-20:34:56 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline


Root Cause:

One datafile was not been restored in the "restore database" command, because of "restore database" command skips readonly tablespaces by default.


Fri Dec 18 20:32:24 2009
Errors in file /u01/app/oracle/admin/hkdrt/bdump/hkdrt1_dbw0_29459.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+DATADG/hkdrt/datafile/wk_data_1.465.704715067'
ORA-17503: ksfdopn:2 Failed to open file +DATADG/hkdrt/datafile/wk_data_1.465.704715067
ORA-15012: ASM file '+DATADG/hkdrt/datafile/wk_data_1.465.704715067' does not exist
ORA-17503: ksfdopn:2 Failed to open file +DATADG/hkdrt/tempfile/temp.458.704725981
ORA-15012: ASM file '+DATADG/hkdrt/tempfile/temp.458.704725981' does not exist
Fri Dec 18 20:32:24 2009
MRP0: Background Media Recovery terminated with error 1110
Fri Dec 18 20:32:24 2009
Errors in file /u01/app/oracle/admin/hkdrt/bdump/hkdrt1_mrp0_30074.trc:
ORA-01110: data file 6: '+DATADG/hkdrt/datafile/wk_data_1.465.704715067'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+DATADG/hkdrt/datafile/wk_data_1.465.704715067'
Fri Dec 18 20:32:24 2009
Managed Standby Recovery not using Real Time Apply

Monday, December 14, 2009

Sample RMAN backup in noarchivelog mode

RMAN Backup Scripts

RMAN> run {
2> backup database format '/u01/stage/db_%U';
3> backup current controlfile format '/u01/stage/cntrl_%U';
4> backup spfile format '/u01/stage/spfile_%U';
4> sql "alter database backup controlfile to ''/u01/stage/control.ctl'' reuse";
4> sql "alter database backup controlfile to trace as ''/u01/stage/control.trc'' reuse";
4> sql "create pfile=''/u01/stage/pfile.ora'' from spfile";
5> }


RMAN Outputs

[oracle@vmxdb01 TEST]$ ls -l /u01/stage/
total 317908
-rw-r----- 1 oracle oinstall 7110656 Dec 14 17:47 cntrl_07l0tcpr_1_1
-rw-r----- 1 oracle oinstall 7061504 Dec 14 19:21 control.ctl
-rw-r--r-- 1 oracle oinstall 6015 Dec 14 19:22 control.trc
-rw-r----- 1 oracle oinstall 303767552 Dec 14 17:47 db_05l0tcp9_1_1
-rw-r----- 1 oracle oinstall 7143424 Dec 14 17:47 db_06l0tcpo_1_1
-rw-r--r-- 1 oracle oinstall 981 Dec 14 19:22 pfile.ora
-rw-r----- 1 oracle oinstall 98304 Dec 14 17:47 spfile_08l0tcps_1_1


RMAN Restoration Using Above Backup

[oracle@vmxdb01 stage]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Dec 14 19:34:26 2009

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

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initTEST.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1266344 bytes
Variable Size 54529368 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes

RMAN> restore spfile from '/u01/stage/spfile_08l0tcps_1_1';

Starting restore at 14-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /u01/stage/spfile_08l0tcps_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 14-DEC-09

RMAN> startup force nomount

Oracle instance started

Total System Global Area 608174080 bytes

Fixed Size 1268920 bytes
Variable Size 167773000 bytes
Database Buffers 436207616 bytes
Redo Buffers 2924544 bytes

RMAN> restore controlfile from '/u01/stage/cntrl_07l0tcpr_1_1';

Starting restore at 14-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/TEST/control01.ctl
output filename=/u01/app/oracle/oradata/TEST/control02.ctl
output filename=/u01/app/oracle/oradata/TEST/control03.ctl
Finished restore at 14-DEC-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database check readonly;

Starting restore at 14-DEC-09
Starting implicit crosscheck backup at 14-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 14-DEC-09

Starting implicit crosscheck copy at 14-DEC-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-DEC-09

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/TEST/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/TEST/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/TEST/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/TEST/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/TEST/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/stage/db_05l0tcp9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/stage/db_05l0tcp9_1_1 tag=TAG20091214T174721
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 14-DEC-09

RMAN> alter database open resetlogs;

database opened

Thursday, December 10, 2009

11g new feature: enable_ddl_logging parameter

Parameter description:


SQL> select value,isdefault,isses_modifiable,
2 issys_modifiable,isbasic,description
3 from v$parameter
4 where name='enable_ddl_logging'
5 /

VALUE ISDEFAULT ISSES ISSYS_MOD ISBAS DESCRIPTION
---------- --------- ----- --------- ----- --------------------
FALSE TRUE TRUE IMMEDIATE FALSE enable ddl logging


Input from SQL*Plus:


SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> conn donghua/donghua
Connected.
SQL> create table t1 as select * from dual;

Table created.

SQL> alter table t1 add (id number,name varchar2(20));

Table altered.

SQL> grant select on t1 to public;

Grant succeeded.

SQL> alter table t1 read only;

Table altered.

SQL> drop table t1;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.


Output from alert log


ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
2009-12-10 21:50:50.562000 +08:00
create table t1 as select * from dual
2009-12-10 21:51:13.062000 +08:00
alter table t1 add (id number,name varchar2(20))
2009-12-10 21:51:40.062000 +08:00
Starting background process CJQ0
CJQ0 started with pid=21, OS id=4896
2009-12-10 21:51:43.078000 +08:00
alter table t1 read only
2009-12-10 21:51:55.359000 +08:00
drop table t1

/usr/bin/ld: crt1.o: No such file: No such file or directory

Environment:
Oracle 11gR1 (x64) Client + RHEL 5.3 (x86_64)

What happened:
Applying 11.1.0.7 patchset

Symptoms:

INFO: gcc -m32 -o /u01/app/oracle/product/11.1.0/client/sqlplus/bin/sqlplus32 -L/u01/app/oracle/product/11.1.0/client/sqlplus/lib32/ -L/u01/app/oracle/produc
t/11.1.0/client/lib32/ -L/u01/app/oracle/product/11.1.0/client/lib32/stubs/ /u01/app/oracle/product/11.1.0/client/sqlplus/lib32/s0afimai.o -lsqlplus -lclnts
h `cat /u01/app/oracle/product/11.1.0/client/lib32/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.1.0/client/
lib32/ldflags` -lncrypt11 -lnsgr11 -lnzjs11
INFO: -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11 -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lc
ore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /u01/app/oracle/product/11.1.0/client/lib32/ldflags` -lncrypt11 -l
nsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.1.0/client/lib32/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnet
d11 -lvsn11 -lcommon11 -lgeneric11 -lsnls11
INFO: -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11 -lvsn11
-lcommon11 -lgeneric11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `c
at /u01/app/oracle/product/11.1.0/client/lib32/sysliblist` -Wl,-rpath,/u01/app/oracle/product/11.1.0/client/lib32 -lm -lpthread `cat /u01/app/oracle/produc
t/11.1.0/client/lib32/sysliblist` -ldl -lm -lpt
INFO: hread -L/u01/app/oracle/product/11.1.0/client/lib32

INFO: /usr/bin/ld: crt1.o: No such file: No such file or directory

INFO: collect2: ld returned 1 exit status

INFO: make[1]: Leaving directory `/u01/app/oracle/product/11.1.0/client/sqlplus/lib'

INFO: make[1]: *** [/u01/app/oracle/product/11.1.0/client/sqlplus/bin/sqlplus32] Error 1

INFO: make: *** [newsqlplus32] Error 2

INFO: End output from spawned process.
INFO: ----------------------------------
INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'install' of makefile '/u01/app/oracle/product/11.1.0/client/sqlplus/lib/ins_sqlplus.mk'. See '/u01/app/oraInvento
ry/logs/installActions2009-12-10_10-46-17AM.log' for details.
Exception Severity: 1
INFO: The output of this make operation is also available at: '/u01/app/oracle/product/11.1.0/client/install/make.log'

Root cause:
32bit "glibc-devel" has not been installed.

[oracle@vmxdb01 ~]$ rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep glibc-devel
glibc-devel-2.5-34 (x86_64)

Solution:

Using "yum" to install glibc-devel-2.5-34.i386

[oracle@vmxdb01 ~]$ rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep glibc-devel
glibc-devel-2.5-34 (i386)
glibc-devel-2.5-34 (x86_64)

How to check whether CPU and/or Windows Bundle patch applied to database

It is possible to check against the registry$history, in the "comments" column, it shows the decription for CPU patch (for example CPUJul2009) or Windows bundle patchs (for example, Patch 18).

For example:

SQL> col action for a15
SQL> col ACTION_TIME for a30
SQL> col COMMENTS for a30
SQL> col version for a15
SQL> set lin 100

SQL> select action,action_time,version,comments from registry$history;

ACTION ACTION_TIME VERSION COMMENTS
--------------- ------------------------------ --------------- ------------------------------
UPGRADE 10-DEC-09 10.47.04.162576 AM 11.1.0.7.0 Upgraded from 11.1.0.6.0
APPLY 10-DEC-09 12.50.17.018749 PM 11.1.0.7 CPUJul2009
CPU 10-DEC-09 12.52.37.373144 PM view recompilation

Tuesday, December 8, 2009

How to backup archivelog in Dataguard environment

Usually the RMAN backup scripts (with Veritas NBU) for archivelog portion look like below:


# backup all archive logs
BACKUP
TAG pr_arc_bk
filesperset 50
FORMAT '"$NB_ORA_PC_SCHED"_arc_%d%Y%M%D_%s'
ARCHIVELOG ALL delete input;


But you will hit following errors if you have connected to RMAN CATALOG server, which connects to both Standby and Primary database.


Starting backup at 2009-DEC-02 11:05:34
current log archived
released channel: ch00
released channel: ch01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/02/2009 11:05:39
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file +ARCHDG/hkdrt/archivelog/2009_11_27/thread_1_seq_63.555.704024185
ORA-17503: ksfdopn:2 Failed to open file +ARCHDG/hkdrt/archivelog/2009_11_27/thread_1_seq_63.555.704024185
ORA-15012: ASM file '+ARCHDG/hkdrt/archivelog/2009_11_27/thread_1_seq_63.555.704024185' does not exist


The reason for the error is trying to backup standby archivelog at the primary site.

How to Resolve it?
Answer is matching the archivelog using "pattern".

For example:


# backup all archive logs
BACKUP
TAG pr_arc_bk
filesperset 50
FORMAT '"$NB_ORA_PC_SCHED"_arc_%d%Y%M%D_%s'
ARCHIVELOG LIKE '+ARCHDG/hktst%' delete input;

Sunday, December 6, 2009

Does PSU Released For Oracle Change The Database Version/Fifth Digit? (Answer is NO)

Intro to Patch Set Updates (PSU) [ID 854428.1] - Original

2.3 Baseline Version for Easier Tracking
Oracle associates a version number to the patch. The fifth number of the database version is incremented for each PSU. For example, the initial PSU is version 10.2.0.4.1, the next PSU for Release 10.2.0.4 will be 10.2.0.4.2, and so on.

This enables customers and Oracle Support to refer to just one number, the version, rather than a list of one-off patches or sets of bundles that have been applied.

Any new one-off patches are requested on the five-number version number.


Does PSU Released For Oracle 10gR2 (10.2.0.4)and 11g R1 (11.10.7) Change The Database Version/Fifth Digit ? [ID 861152.1] - Updated through Metalink

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.1.0.7
Information in this document applies to any platform.

Goal
This article helps to find whether PSU released for Oracle Database Server 10gR2 ( 10.2.0.4 ) and 11gR2 (11.1.0.7) change the database version.
Solution

Answer is NO.

The PSU i.e 10.2.0.4.x and 11.1.0.7.x released for Oracle Database Server version 10.2.0.4.0 and 11.1..0.7.0 respectively does NOT change the database version . That means PSU 10.2.0.4.x and 11.1.0.7.x ( where x is the fifth digit ) does NOT change the 5th digit of the Oracle Database Server version.

After applying the PSU 10.2.0.4.x and 11.1.0.7.x "opatch lsinventory" still shows the version as 10.2.0.4.0 for Oracle 10g R2 a nd 11.1.0.7 for Oracle 11g R1.

PSU also does not change the version of oracle binaries (like sqlplus, exp/imp etc.)

It was suggested that starting in the October 2009 release of the PSU that the version number would be updated in the binaries and dba_registry. Development determined that would not be possible for current releases as it also would require a change the the OUI. Therefore, this will not occur on any PSU release prior to 11.2 and possibly not until the 1st patchset of 11.2




[oracle@vmxdb01 view_recompile]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 6 09:43:15 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select * from registry$history;

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER
------------------------------ ---------- ---------- ---------- --------- -------------------- ----------
06-DEC-09 09.34.25.885126 AM APPLY SERVER 10.2.0.4 2 PSU 10.2.0.4.2 PSU
06-DEC-09 09.38.42.581477 AM CPU 6452863 view recompilation


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Saturday, December 5, 2009

RMAN-20005 & How to unregister database

If more than one database with same database name, but different DBID, registered to the same calalog, during the restoration, must set DBID, otherwise, "RMAN-20005: target database name is ambiguous" error will raise.


$ rman target / catalog rman/xxxxx@rmandb

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 3 11:56:56 2009

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

connected to target database: hktst (not mounted)
connected to recovery catalog database

RMAN> run {
2> allocate channel ch0 device type 'sbt';
3> restore controlfile;
4> }

allocated channel: ch0
channel ch0: sid=147 instance=hkdrt1 devtype=SBT_TAPE
channel ch0: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

Starting restore at 2009-DEC-03 11:57:20

released channel: ch0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/03/2009 11:57:20
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous

RMAN> exit


select * from rc_database where name='HKTST' order by resetlogs_time;


DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
29211 29212 1902399835 HKTST 602821 15-OCT-09
30110 52364 1902498142 HKTST 841865 21-OCT-09
36494 221103 1902647353 HKTST 5191383 03-DEC-09


How to unresgister a database


man catalog rman/xxxxx@rmandb

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 4 11:11:14 2009

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

connected to recovery catalog database

RMAN> set dbid 1902399835;

executing command: SET DBID
database name is "HKTST" and DBID is 1902399835

RMAN> unregister database;

database name is "HKTST" and DBID is 1902399835

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog



RMAN> set dbid 1902498142

executing command: SET DBID
database name is "HKTST" and DBID is 1902498142

RMAN> unregister database noprompt;

database name is "HKTST" and DBID is 1902498142
database unregistered from the recovery catalog


select * from rc_database where name='HKTST' order by resetlogs_time;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
36494 221103 1902647353 HKTST 5191383 03-DEC-09


1 row selected.

Wednesday, October 14, 2009

Oracle license: Diagnostics Pack and Tuning Pack

How it affects your Oracle Enterprise Manager database Control?

Diagnostics Pack:

If you disable access to diagnostics pack, following features are disabled:
Performance (tab)
ADDM (Advisor Central)

Tuning Pack: (requires Diagnostics Pack)

If you disable access to tuning pack, following features are disabled:
SQL Access Advisor (Advisor Central)
SQL Tuning Advisor (Advisor Central)

Following features do not require any Oracle license:

Segment Advisor (Advisor Central)
MTTR Advisor (Advisor Central)
Memory Advisor (Advisor Central)
Undo Management (Advisor Central)

Monday, October 12, 2009

How to: troubleshooing Oracle Clusterware is not up

Symptoms:

After server boots up, Oracle clusterware does not startup successfully.

Error message: (Case 1)

/var/log/messages

Oct 9 10:08:24 VMXDB03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.10862.

/tmp/crsctl.10862

OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]

Possbile Causes:

OCR device does not exist or it's not accessible


Error message: (Case 2)

/var/log/messages

Oct 9 10:08:24 VMXDB03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.11713.

/tmp/crsctl.11713

OCR initialization failed with invalid format: PROC-22: The OCR backend has an invalid format

Possbile Causes:

OCR device corrupted, or it's not the correct device due to issues of PowerPath/Linux Divice-Mapper.

How much free space required for Oracle Binaries

In the offical documentation, it states 1.9GB free space required for Enterprise/Standard edition installation, and 2.0GB for Custom type installation. But is it enough?

In recent 10gR2 (10.2.0.4) RAC implementation, it shows at least 7.6GB for Database and 2.0GB for Oracle Clusterware.


[oracle@VMXDB03 10.2.0]$ ls |xargs du -sh
7.6G asm
2.0G crs
7.6G db


The top space consumer is patch backup files.

[oracle@VMXDB03 db]$ ls -a .patch_storage|xargs du -sh
5.9G .patch_storage


[oracle@VMXDB03 .patch_storage]$ ls |xargs du -sh
116M 4693355_Sep_8_2008_04_01_48
111M 5259835_Jan_26_2009_03_10_54
115M 5348308_Oct_9_2008_21_37_51
110M 5747462_Apr_8_2009_16_52_05
110M 5756769_Apr_8_2009_16_52_07
110M 5868257_Jun_5_2008_23_04_30
110M 5879114_Dec_23_2008_21_11_23
110M 5895190_Apr_8_2009_16_52_09
109M 6051177_Apr_8_2009_16_52_10
111M 6052226_Sep_15_2008_02_20_06
2.8M 6079224_Sep_15_2008_03_58_35
109M 6084232_Apr_8_2009_16_52_13
112M 6163771_Jul_30_2008_02_54_13
110M 6200820_Nov_12_2008_06_29_55
109M 6327692_Aug_13_2008_06_40_23
110M 6374297_Apr_8_2009_16_52_03
110M 6455659_Oct_23_2008_04_35_27
109M 6638558_Apr_8_2009_16_52_01
110M 6678845_Jan_9_2009_00_45_07
14M 6679303_Apr_8_2009_16_51_59
110M 6919819_Feb_17_2009_02_57_35
130M 6923450_Apr_8_2009_16_52_19
110M 6972843_Jan_9_2009_00_45_13
110M 7008262_Jan_9_2009_00_45_15
120M 7123643_Jun_26_2008_01_23_32
448K 7155248_Jul_3_2008_02_10_38
348K 7155249_Jul_3_2008_02_10_48
110M 7155250_Jul_3_2008_02_10_51
109M 7155251_Jul_3_2008_02_10_52
110M 7155252_Jul_3_2008_02_10_54
110M 7189447_Jan_9_2009_00_45_16
156K 7197583_Jul_3_2008_02_11_22
110M 7257461_Feb_24_2009_08_55_57
110M 7257770_Jan_12_2009_23_29_55
21M 7272297_Jan_9_2009_00_45_18
111M 7278117_Aug_13_2008_06_35_40
110M 7309458_Apr_8_2009_16_52_21
110M 7340448_Apr_8_2009_16_52_17
109M 7375611_Sep_16_2008_01_15_53
384K 7375613_Sep_16_2008_01_15_57
636K 7375617_Sep_16_2008_01_15_59
112M 7378661_Sep_15_2008_02_14_51
110M 7552067_Nov_16_2008_22_14_33
110M 7606362_Mar_29_2009_22_37_12
3.3M 7609057_Dec_31_2008_04_17_07
109M 7609058_Dec_31_2008_04_15_23
119M 7691766_Jan_9_2009_00_45_09
106M 7715304_Jan_30_2009_06_03_15
104K 7936793_Mar_19_2009_02_45_58
104K 7936993_Mar_25_2009_04_05_16
784K 8227091_Feb_10_2009_04_36_51
118M 8227106_Mar_17_2009_21_25_14
110M 8230457_Feb_11_2009_00_45_48
131M 8287504_Mar_19_2009_03_50_27
232K 8290506_Mar_24_2009_12_34_30
110M 8309587_Mar_24_2009_12_34_32
109M 8309592_Mar_24_2009_12_34_34
720K 8309623_Mar_24_2009_12_34_36
144K 8309632_Mar_24_2009_12_34_37
2.8M 8309637_Mar_24_2009_12_34_45
6.5M 8309639_Mar_24_2009_12_34_42
1.1M 8309642_Mar_24_2009_12_34_40
120M 8340379_Mar_31_2009_22_25_20
110M 8340383_Mar_30_2009_22_42_04
110M 8340387_Apr_1_2009_21_47_43
104K 8344348_Apr_5_2009_23_07_18
112M 8347704_Apr_7_2009_03_46_17
104K 8362683_Apr_8_2009_16_51_57
111M 8373286_Apr_15_2009_03_06_31
6.5M 8534387_Jun_8_2009_20_04_36
6.5M 8568395_Jun_8_2009_20_04_38
1.7M 8568397_Jun_8_2009_20_04_39
172M 8568398_Jun_25_2009_12_05_00
109M 8568402_Jun_8_2009_20_04_44
3.6M 8568404_Jun_8_2009_20_04_46
110M 8568405_Jun_8_2009_20_04_47
4.0K LatestOPatchSession.properties
37M NApply
0 patch_free

Thursday, October 8, 2009

Oracle Bugs: Drop redo log group inside asm diskgroup

Symptons:


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [],
[], []


SQL> alter database drop standby logfile group 13;
alter database drop standby logfile group 13
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [],
[], []


Environment:

Linux (5.3) x86_64 + Oracle 10.2.0.4 64bit (with oracle clusterware/asm/db)

Workaround:

Indeed the redo log groups has been dropped, so these ora-600 errors can be ignored.

Wednesday, October 7, 2009

Oracle Bugs: Disable interconnect network interface

Test Envrionment:
Oracle: oracle 10.2.0.4 (x86_64)
OS: Redhat 5.3 (x86_64)

Error message when unplugging the interconnect cable:


Wed Oct 7 15:14:29 2009
ospid 21322: network interface with IP address 192.168.5.1 no longer running (check cable)


Error message when disabling the interface:


ed Oct 7 15:21:29 2009
ospid 21322: network interface with IP address 192.168.5.1 no longer operational
requested interface 192.168.5.1 not found. Check output from ifconfig command
... ...
Wed Oct 7 15:23:27 2009
Errors in file /u01/app/oracle/admin/vodka/udump/vodka1_ora_21851.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-27544: Failed to map memory region for export
ORA-27300: OS system dependent operation:bind failed with status: 99
ORA-27301: OS failure message: Cannot assign requested address
ORA-27302: failure occurred at: sskgxpcre3
... ...
Errors in file /u01/app/oracle/admin/vodka/bdump/vodka1_ora_29422.trc:
ORA-07445: exception encountered: core dump [ksxpsigosderr()+167] [SIGSEGV] [Address not mapped to object] [0x000000061] [] []
ORA-27300: OS system dependent operation:if_not_found failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpvaddr9
ORA-27303: additional information: requested interface 192.168.5.1 not found. Check output from ifconfig command


Solution:

This is the expected behavior when disable the interconnect interface.

Friday, October 2, 2009

Oracle & Linux Hugepages settings

Question:

Is hugepages still recommended for 64bit Linux + Oracle?

Answer:

Yes!

Why:

Without hugepages, each page is 4kB by default, to maintain a pagetable of 32GB memory addressing, we need 32*1024*1024/4*8/1024/1024=64MB (each page reference point takes 4 bytes in 32bit OS and 8bytes in 64bit OS). For example, we have 100 such processes; we need 6400MB memory to maintain the pagetable only. (But normally we will not hit this worst situation, because of lazy allocation memory algorism)

With hugepages, each page is 2MB on EM64 architecture. To maintain a pagetable of 32GB memory addressing, we only need 32*1024/2*8/1024=128KB. For example, we have 100 such processes, we only need 12.5MB memory for pagetable.

Another reason to configure hugepages is memory associated hugepages are not swappable, a.k.a, always in memory.

One more benefit, hugepages increases the efficiency of TBL.

Another one, "Cached" memory with hugepages does not include SGA any more.

How to set?

[root@VMXDB01 ~]# cat /etc/sysctl.conf
... ...
vm.nr_hugepages = 16640
... ...

Method of calculating vm.nr_hugepages:

Get Hugepagesize from /proc/meminfo, for our case, it's 2MB.

vm.nr_hugepages = sum (all sga_max_target size in MB) / Hugepagesize + min(2,value_you_feel_conformtable)


[root@VMXDB01 ~]# cat /etc/security/limits.conf
... ...
oracle soft memlock 33564672 <-- larger than max SGA, in KB
oracle hard memlock 33564672 <-- larger than max SGA, in KB
... ...

Verify:

[root@VMXDB01 ~]# cat /proc/meminfo
MemTotal: 64951768 kB
MemFree: 29433020 kB
Buffers: 62304 kB
Cached: 701376 kB
SwapCached: 0 kB
Active: 726660 kB
Inactive: 575368 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 64951768 kB
LowFree: 29433020 kB
SwapTotal: 33551744 kB
SwapFree: 33551744 kB
Dirty: 376 kB
Writeback: 0 kB
AnonPages: 549344 kB
Mapped: 114368 kB
Slab: 63620 kB
PageTables: 23268 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 48988268 kB
Committed_AS: 2529688 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 283056 kB
VmallocChunk: 34359455303 kB
HugePages_Total: 16640
HugePages_Free: 13887
HugePages_Rsvd: 13695
Hugepagesize: 2048 kB

Set different bonding mode for each bonding interface

Instead of settings in the /etc/modprobe.conf to specify bonding mode systemwide, we also can specify bond mode in scripts of each bonding interface. (Avaliable from RHEL 5 onwards)

For example, we can specify bonding mode 4, regardless of the systemwide settings of mode 1.

/etc/modprobe.conf

alias bond0 bonding
alias bond1 bonding
alias bond2 bonding
options bonding miimon=100 mode=1


[root@VMXDB01 ~]# cat /etc/sysconfig/network-scripts/ifcfg-bond2
DEVICE=bond2
USERCTL=no
ONBOOT=yes
BOOTPROTO=none
IPADDR=192.168.0.20
NETMASK=255.255.255.0
TYPE=Ethernet
IPV6INIT=no
PEERDNS=yes
BONDING_OPTS="mode=4 miimon=100"

Restart the interface to make the settings active:

[root@VMXDB01 ~]# ifdown bond2
[root@VMXDB01 ~]# ifup bond2


[root@VMXDB01 ~]# cat /proc/net/bonding/bond2
Ethernet Channel Bonding Driver: v3.2.4 (January 28, 2008)

Bonding Mode: IEEE 802.3ad Dynamic link aggregation
Transmit Hash Policy: layer2 (0)
MII Status: up
MII Polling Interval (ms): 100
Up Delay (ms): 0
Down Delay (ms): 0

802.3ad info
LACP rate: slow
Active Aggregator Info:
Aggregator ID: 1
Number of ports: 1
Actor Key: 17
Partner Key: 1
Partner Mac Address: 00:25:83:46:a2:80

Slave Interface: eth0
MII Status: up
Link Failure Count: 0
Permanent HW addr: 00:24:e8:61:5e:7f
Aggregator ID: 1

Slave Interface: eth1
MII Status: up
Link Failure Count: 0
Permanent HW addr: 00:24:e8:61:5e:81
Aggregator ID: 2


Reference:
http://www.dbaglobe.com/2009/09/how-to-linux-ethernet-bonding.html

Thursday, October 1, 2009

Useful query to find out Oracle Licensing Option usage

Below SQL statement is useful to comply Oracle licensing. It also as a useful tools to check whether specific option has been used before it can be safely removed/deinstalled.


SQL> desc DBA_FEATURE_USAGE_STATISTICS
Name Null? Type
----------------------------------------- -------- -----------------
DBID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
VERSION NOT NULL VARCHAR2(17)
DETECTED_USAGES NOT NULL NUMBER
TOTAL_SAMPLES NOT NULL NUMBER
CURRENTLY_USED VARCHAR2(5)
FIRST_USAGE_DATE DATE
LAST_USAGE_DATE DATE
AUX_COUNT NUMBER
FEATURE_INFO CLOB
LAST_SAMPLE_DATE DATE
LAST_SAMPLE_PERIOD NUMBER
SAMPLE_INTERVAL NUMBER
DESCRIPTION VARCHAR2(128)


SQL> select name,last_usage_date from dba_feature_usage_statistics
2 where detected_usages>0 order by 1;

NAME LAST_USAG
------------------------------------------------------- ---------
Audit Options 23-SEP-09
Automatic Maintenance - Optimizer Statistics Gathering 23-SEP-09
Automatic Maintenance - SQL Tuning Advisor 23-SEP-09
Automatic Maintenance - Space Advisor 23-SEP-09
Automatic Memory Tuning 23-SEP-09
Automatic SQL Tuning Advisor 25-JUN-09
Automatic Segment Advisor 25-JUN-09
Automatic Segment Space Management (system) 23-SEP-09
Automatic Segment Space Management (user) 23-SEP-09
Automatic Undo Management 23-SEP-09
Automatic Workload Repository 11-MAY-09

NAME LAST_USAG
------------------------------------------------------- ---------
Backup BZIP2 Compression 23-SEP-09
Character Set 23-SEP-09
Client Identifier 14-MAR-09
Dynamic SGA 22-MAR-09
EM Database Control 11-MAY-09
Extensibility 23-SEP-09
LOB 23-SEP-09
Locally Managed Tablespaces (system) 23-SEP-09
Locally Managed Tablespaces (user) 23-SEP-09
Materialized Views (User) 23-SEP-09
Object 23-SEP-09

NAME LAST_USAG
------------------------------------------------------- ---------
Oracle Managed Files 23-SEP-09
Parallel SQL DDL Execution 14-MAR-09
Partitioning (system) 23-SEP-09
RMAN - Disk Backup 23-SEP-09
Recovery Area 23-SEP-09
Recovery Manager (RMAN) 23-SEP-09
Resource Manager 20-MAY-09
Restore Point 23-SEP-09
Segment Advisor 30-AUG-09
Server Parameter File 23-SEP-09
Services 23-SEP-09

NAME LAST_USAG
------------------------------------------------------- ---------
XDB 23-SEP-09

Wednesday, September 30, 2009

How to: ORA-00059: maximum number of DB_FILES exceeded

Symptom:


create tablespace data_tbs38 datafile "+DATA' size 100M;
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded


Solution:

Increase db_files initialization parameter and bounce the database. Increase this parameter will incease the PGA consumation for every server process.


SQL> alter system set db_files=500 scope=spfile sid='*';

System altered.


How about MAXDATAFILES specified when creating database?

From Oracle8 onwards, Controlfile will auto-expend if MAXDATAFILES is smaller. similar inforamtion could be found in the alert log:


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.0.1 - Beta
PL/SQL Release 11.2.0.0.1 - Beta
CORE 11.2.0.0.1 Beta
TNS for 32-bit Windows: Version 11.2.0.0.1 - Beta
NLSRTL Version 11.2.0.0.1 - Beta



SQL> select type,record_size rs, records_total rt, records_used ru,first_index f
2 last_index li, last_recid lr from v$controlfile_record_section;

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
REDO THREAD 256 8 1 0 0 0
REDO LOG 72 16 3 0 0 0
DATAFILE 520 52 23 0 0 17
FILENAME 524 2138 27 0 0 0
TABLESPACE 68 40 24 0 0 18
TEMPORARY FILENAME 56 20 1 0 0 1
RMAN CONFIGURATION 1108 50 2 0 0 2
LOG HISTORY 56 292 1 1 1 1
OFFLINE RANGE 200 81 0 0 0 0

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
ARCHIVED LOG 584 308 3 1 3 3
BACKUP SET 40 409 0 0 0 0
BACKUP PIECE 736 44 0 0 0 0
BACKUP DATAFILE 200 81 0 0 0 0
BACKUP REDOLOG 76 215 0 0 0 0
DATAFILE COPY 736 44 0 0 0 0
BACKUP CORRUPTION 44 371 0 0 0 0
COPY CORRUPTION 40 409 0 0 0 0
DELETED OBJECT 20 818 0 0 0 0
PROXY COPY 928 88 0 0 0 0
BACKUP SPFILE 124 131 0 0 0 0

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
DATABASE INCARNATION 56 292 1 1 1 1
FLASHBACK LOG 84 2048 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0
RMAN STATUS 116 141 0 0 0 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0
MTTR 100 8 1 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0
STANDBY DATABASE MATRIX 400 31 31 0 0 0
GUARANTEED RESTORE POINT 212 2048 0 0 0 0

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
RESTORE POINT 212 2083 0 0 0 0
DATABASE BLOCK CORRUPTION 80 2045 0 0 0 0
ACM OPERATION 104 64 5 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0

37 rows selected.


Expanded controlfile section 6 from 20 to 40 records
The number of logical blocks in section 6 remains the same
Completed: create tablespace test12 datafile size 10m
create tablespace test13 datafile size 10m
Expanded controlfile section 4 from 20 to 52 records
Requested to grow by 32 records; added 1 blocks of records

How to enable powerpath failed path in a faster way

By unplug one of the Fiber cable to simulate FC HBA failure, the result as expect:



[root@VMXDB01 ~]# powermt display
Symmetrix logical device count=39
CLARiiON logical device count=0
Hitachi logical device count=0
Invista logical device count=0
HP xp logical device count=0
Ess logical device count=0
HP HSx logical device count=0
==============================================================================
----- Host Bus Adapters --------- ------ I/O Paths ----- ------ Stats ------
### HW Path Summary Total Dead IO/Sec Q-IOs Errors
==============================================================================
3 qla2xxx optimal 39 0 - 0 0
4 qla2xxx failed 39 39 - 0 39


[root@VMXDB01~]# powermt display dev=emcpowera
Pseudo name=emcpowera
Symmetrix ID=0009899012321
Logical device ID=0141
state=alive; policy=SymmOpt; priority=0; queued-IOs=0
==============================================================================
---------------- Host --------------- - Stor - -- I/O Path - -- Stats ---
### HW Path I/O Paths Interf. Mode State Q-IOs Errors
==============================================================================
3 qla2xxx sdbe FA 7aA active alive 0 0
4 qla2xxx sdce FA 9aA active dead 0 1


After put back the fiber cable, it takes 5 minutes to detect this recovery action and change the state to "alive".

How to make it faster? the answer is to use "powermt enable hba=#hba" to enable the hba (path) manually. Here #hba is referring to the first column of the output (hba is 3 or 4)


[root@VMXDB01 ~]# powermt enable hba=4

Monday, September 28, 2009

Using datapump to migrate data between databases

Objective:

Refresh data from VMXDB01 to VMXDB02

From Source VMXDB01

Create directory object


SQL> create directory logical_export as '/u01/logical_export';


Export data:


[oracle@VMXDB01 ~]$ expdp \'/ as sysdba\' directory=logical_export dumpfile=code.dmp logfile=code.log tables=donghua.tbl_code

Export: Release 10.2.0.4.0 - 64bit Production on Sunday, 27 September, 2009 21:12:29

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' directory=logical_export dumpfile=code.dmp logfile=code.log tables=donghua.tbl_code
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DONGHUA"."TBL_CODE" 3.553 MB 43960 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/logical_export/code.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 21:12:40


To Destination VMXDB02

Create directory object


SQL> create directory logical_export as '/u01/logical_export';


Import data:


[oracle@VMXDB02 ~]$ impdp \'/ as sysdba\' directory=logical_export dumpfile=code.dmp logfile=code.log full=y content=data_only table_exists_action=truncate

Import: Release 10.2.0.4.0 - 64bit Production on Sunday, 27 September, 2009 21:16:57

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": '/******** AS SYSDBA' directory=logical_export dumpfile=code.dmp logfile=code.log full=y content=data_only table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DONGHUA"."TBL_CODE" 3.553 MB 43960 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 21:17:04


References:

TABLE_EXISTS_ACTION
Default: SKIP (Note that if CONTENT=DATA_ONLY is specified, the default is APPEND, not SKIP.)

Purpose

Tells Import what to do if the table it is trying to create already exists.

Syntax and Description

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

The possible values have the following effects:

•SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

•APPEND loads rows from the source and leaves existing rows unchanged.

•TRUNCATE deletes existing rows and then loads rows from the source.

•REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

The following considerations apply when you are using these options:

•When you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.

•When you use SKIP, APPEND, or TRUNCATE, existing table-dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. For REPLACE, the dependent objects are dropped and re-created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.

•When you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action.

The existing table is loaded using the external tables access method if the existing tables have active constraints and triggers. However, be aware that if any row violates an active constraint, the load fails and no data is loaded.

If you have data that must be loaded, but may cause constraint violations, consider disabling the constraints, loading the data, and then deleting the problem rows before reenabling the constraints.

•When you use APPEND, the data is always loaded into new space; existing space, even if available, is not reused. For this reason, you may wish to compress your data after the load.

Restrictions

•TRUNCATE cannot be used on clustered tables or over network links.

Sunday, September 27, 2009

Symmetrix parition alignment (Windows and Linux)

On Linux/Winows, first 63 blocks has been reserved for MBR (master boot record), and first data partition starts with offset at 63*512bytes=31.5KB.

However, Symmetrix array define tracks to be 64KB on DMX-3 and later (32KB for DMX-2 and earlier). This misalignment can use misalignment on Symmetrix memory cache (Just imaging one 64KB track on Host mapping to 2 tracks in the memory cache and storage), this will cause performance degradation.

Below steps shows how to manually perform the alignment using fdisk:
(128 blocks x 512bytes/block = 64KB)


[root@VMXDB01 ~]# fdisk /dev/emcpowera
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklab
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n <-- New partition
Command action
e extended
p primary partition (1-4)
p <-- Primary partition
Partition number (1-4): 1 <-- 1st partition
First cylinder (1-1011, default 1): <-- leave default to use 1st cylinder
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1011, default 1011): <-- Default
Using default value 1011

Command (m for help): x <-- enter eXpert mode

Expert command (m for help): p <-- print partition table

Disk /dev/emcpowera: 67 heads, 62 sectors, 1011 cylinders

Nr AF Hd Sec Cyl Hd Sec Cyl Start Size ID
1 00 1 1 0 66 62 1010 62 4199632 83
2 00 0 0 0 0 0 0 0 0 00
3 00 0 0 0 0 0 0 0 0 00
4 00 0 0 0 0 0 0 0 0 00

Expert command (m for help): b <-- move to beginning of the partition
Partition number (1-4): 1 <-- choose 1st partition
New beginning of data (62-4199693, default 62): 128 <-- Enter starting block

Expert command (m for help): p <-- print partition table

Disk /dev/emcpowera: 67 heads, 62 sectors, 1011 cylinders

Nr AF Hd Sec Cyl Hd Sec Cyl Start Size ID
1 00 1 1 0 66 62 1010 128 4199566 83
Partition 1 has different physical/logical beginnings (non-Linux?):
phys=(0, 1, 1) logical=(0, 2, 5)
2 00 0 0 0 0 0 0 0 0 00
3 00 0 0 0 0 0 0 0 0 00
4 00 0 0 0 0 0 0 0 0 00

Expert command (m for help): w <-- save partition table
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@VMXDB01 ~]# fdisk -l /dev/emcpowera

Disk /dev/emcpowera: 2150 MB, 2150891520 bytes
67 heads, 62 sectors/track, 1011 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes

Device Boot Start End Blocks Id System
/dev/emcpowera1 1 1011 2099783 83 Linux
Partition 1 has different physical/logical beginnings (non-Linux?):
phys=(0, 1, 1) logical=(0, 2, 5)

Saturday, September 26, 2009

Exploring EMC Storage: Introducing Symmetrix Software

EMC Solutions Enabler Kit

Contains all the base management software that provides a host with SYMAPI-shared libraries and the basic Symmetrix command line interface (SYMCLI)

What is SRDF?

The Symmetrix Remote Data Facility (SRDF) is a business continuity solution to replicate between Symmetrix storage arrays.

SRDF currently supports the followin modes of operation:
  1. Synchronous mode (SRDF/S)
  2. Asynchronous mode (SRDF/A)
  3. Adaptive copy mode (transferring data regardless of order/consistency)

What is EMC TimeFinder?

TimeFinder is replicating data within a Symmetrix storage array.

  • TimeFinder/Mirror - Genral monitor and control operations for Business continuance Volumes (BCV)
  • TimeFinder/CG - Consistency groups
  • TimeFinder/Clone - Clone copy
  • TimeFinder/Snap - Snap copy

What is PowerPath?

PowerPath is host-based software that works with networked storage systems to intelligently manage I/O paths. PowerPath manages multiple paths to a storage array. Supporting multiple paths enables recovery from path failure because PowerPath automatically detects path failures and redirects I/O to other available paths.

A key feature of path management in PowerPath is dynamic, multipath load balancing.

What is EMC Replication Manager?

Replication Manager has a generic storage technology interface that allows it to connect and invoke replication methodologies available on:

  • EMC Symmetrix arrays
  • EMC CLARiiON arrays
  • HP StorageWorks arrays

Exploring EMC Storage: Introducing Clariion Storage

Clariion array was originally designed for AViiON computer within Data General Corporation, and acquired by EMC on 1999. Occupying the entry-level and mid-range of EMC's SAN disk array product palette, it is complemented by the high-end Symmetrix.

CLARiiON AX-Series



  • AX4: level disk array, support upto 60 SAS/SATA disks through expension pack, with total capacity 60TB


CLARiiON CX-Series

  • CX-300/CX-300i/CX-500/CX-500i/CX-700: End-of-Life Product, each with unique data-in-place upgrade to the next level. ("i" inside the product name refers to "iscsi")

  • CX3 Model 10/CX3 Model 20/CX3 Model 40/CX3 Model 80: each with unique data-in-place upgrade to the next level.



  • CX4 Model 120/CX4 Model 240/CX4 Model 480/CX4 Model 960: each with unique data-in-place upgrade to the next level. Number (120/240/480/960) in the product represents maximum disk drives, and maximum capacity.





Reference:

http://en.wikipedia.org/wiki/Clariion

http://www.dg.com/collateral/hardware/comparison/emc-clariion.htm

Exploring EMC Storage: Introducing Symmetrix Storage

Symmetrix storage is designed for high-end, contrasting to Clariion, which is targeting to entry-midsize market.

These EMC Symmetrix product before DMX-2 have been omitted.

DMX-2 configurations: (Based on Direct Matrix Architecture)

  • Symmetrix DMX-800EL: 8-60 drives, 4-64 GB cache memory, 8 x 2 GB FC front ends, modular rack mount
  • Symmetrix DMX-800: 60-120 drives, 4-64 GB cache memory, 16 x 2 GB FC front ends, modular rack mount
  • Symmetrix DMX-1000: up to 144 drives, 64-128 GB cache memory, 48 x 2 GB FC front ends, single cabinet
  • Symmetrix DMX-2000: up to 288 drives, 128-256 GB cache memory, 64 x 2 GB FC front ends, two cabinets
  • Symmetrix DMX-3000: up to 576 drives, 128-256 GB cache memory, 64 x 2 GB FC front ends, three cabinets

DMX-3 configurations:

  • Symmetrix DMX-3 950: non-expandable version of Symmetrix, up to 360 drives, up to 128 GB cache memory, 8 front-end ports, one or two cabinets
  • Symmetrix DMX-3: up to 2400 drives, up to 512 GB cache memory, 64 x 2 GB FC front ends (even 4 Gb FC's are available at this time), two to nine cabinets

DMX-4 configurations:

  • Symmetrix DMX-4: up to 1920 drives (or up to 2400 drives for 500 GB drive only configurations), up to 512 GB cache memory, 64 x 4 GB FC front ends, two to nine cabinets

V-Max configurations: (Based on Virtial Matrix Architecture)

  • Symmetrix V-Max SE: a entry point single enginee storage system
  • Symmetrix V-Max: high-end storage array that scales from a single configuration with a dedicated system cabinet and a single storage bay to a larger eight-engine configuration with up to 10 storage bays capable of holding 2400 physical disk drivers.

Reference:

http://en.wikipedia.org/wiki/EMC_Symmetrix

EMC Symmetrix DMX Architecture Product Description Guide

Oracle Databases on EMC Symmetrix Storage Systems

Thursday, September 24, 2009

How to: Linux Ethernet Bonding

How to create:


[root@VMXDB01 u01]# cat /etc/modprobe.conf
alias eth0 bnx2
alias eth1 bnx2
alias eth2 bnx2
alias eth3 bnx2
alias eth4 bnx2
alias eth5 bnx2
alias eth6 bnx2
alias eth7 bnx2
alias scsi_hostadapter megaraid_sas
alias scsi_hostadapter1 ata_piix
alias scsi_hostadapter2 qla2xxx
alias bond0 bonding
alias bond1 bonding
options bonding miimon=100 mode=1
alias net-pf-10 off
alias ipv6 off
install usb-storage :



[root@VMXDB01 u01]# cat /etc/sysconfig/network-scripts/ifcfg-bond0
DEVICE=bond0
USERCTL=no
ONBOOT=yes
NETMASK=255.255.255.224
BROADCAST=192.168.0.159
GATEWAY=192.168.0.129
IPADDR=192.168.0.144
BOOTPROTO=none
TYPE=Ethernet
IPV6INIT=no
PEERDNS=yes



[oracle@VMXDB01 ~]$ cat /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
USERCTL=no
ONBOOT=yes
MASTER=bond0
SLAVE=yes
BOOTPROTO=none
TYPE=Ethernet
IPV6INIT=no
PEERDNS=yes



[oracle@VMXDB01 ~]$ cat /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE=eth1
USERCTL=no
ONBOOT=yes
MASTER=bond0
SLAVE=yes
BOOTPROTO=none
TYPE=Ethernet
IPV6INIT=no
PEERDNS=yes



How to monitor:


[root@VMXDB01 u01]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:10:18:4F:BA:E0
UP BROADCAST RUNNING SLAVE MULTICAST MTU:1500 Metric:1
RX packets:2877200 errors:0 dropped:0 overruns:0 frame:0
TX packets:560853 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:4091658143 (3.8 GiB) TX bytes:62462481 (59.5 MiB)
Interrupt:169 Memory:ce000000-ce012100



[root@VMXDB01 u01]# ifconfig eth1
eth1 Link encap:Ethernet HWaddr 00:10:18:4F:BA:E0
UP BROADCAST RUNNING SLAVE MULTICAST MTU:1500 Metric:1
RX packets:1043 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:66822 (65.2 KiB) TX bytes:0 (0.0 b)
Interrupt:218 Memory:da000000-da012100



[root@VMXDB01 u01]# ifconfig bond0
bond0 Link encap:Ethernet HWaddr 00:10:18:4F:BA:E0
inet addr:192.168.0.144 Bcast:192.168.0.159 Mask:255.255.255.224
UP BROADCAST RUNNING MASTER MULTICAST MTU:1500 Metric:1
RX packets:2878296 errors:0 dropped:0 overruns:0 frame:0
TX packets:560895 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:4091729879 (3.8 GiB) TX bytes:62468081 (59.5 MiB)



[root@VMXDB01 u01]# cat /proc/net/bonding/bond0
Ethernet Channel Bonding Driver: v3.2.4 (January 28, 2008)

Bonding Mode: fault-tolerance (active-backup)
Primary Slave: None
Currently Active Slave: eth0
MII Status: up
MII Polling Interval (ms): 100
Up Delay (ms): 0
Down Delay (ms): 0

Slave Interface: eth0
MII Status: up
Link Failure Count: 0
Permanent HW addr: 00:10:18:4f:ba:e0

Slave Interface: eth1
MII Status: up
Link Failure Count: 0
Permanent HW addr: 00:10:18:4f:b9:94


Interesting informationfrom dmesg and /var/log/messages

eth0: Broadcom NetXtreme II BCM5709 1000Base-T (C0) PCI Express found at mem ce000000, IRQ 169, node addr 0010184fbae0
... ...
eth1: Broadcom NetXtreme II BCM5709 1000Base-T (C0) PCI Express found at mem da000000, IRQ 218, node addr 0010184fb994
... ...
bonding: MII link monitoring set to 100 ms
bonding: bond0: Adding slave eth0.
bnx2: eth0: using MSIX
bonding: bond0: enslaving eth0 as a backup interface with a down link.
bonding: bond0: Adding slave eth1.
bnx2: eth1: using MSIX
bonding: bond0: enslaving eth1 as a backup interface with a down link.
bnx2: eth0 NIC Copper Link is Up, 1000 Mbps full duplex
bonding: bond0: link status definitely up for interface eth0.
bonding: bond0: making interface eth0 the new active one.
bonding: bond0: first active interface up!
bnx2: eth1 NIC Copper Link is Up, 1000 Mbps full duplex
bonding: bond0: link status definitely up for interface eth1.


How many "mode" do we have:

/usr/src/kernels/2.6.18-128.2.1.el5-x86_64/include/linux/if_bonding.h

#define BOND_MODE_ROUNDROBIN 0
#define BOND_MODE_ACTIVEBACKUP 1
#define BOND_MODE_XOR 2
#define BOND_MODE_BROADCAST 3
#define BOND_MODE_8023AD 4
#define BOND_MODE_TLB 5
#define BOND_MODE_ALB 6 /* TLB + RLB (receive load balancing) */



Important: Switch configuration required

For this section, "switch" refers to whatever system the bonded devices are directly connected to (i.e., where the other end of the cable plugs into). This may be an actual dedicated switch device, or it may be another regular system (e.g., another computer running Linux),

The active-backup, balance-tlb and balance-alb modes do not require any specific configuration of the switch.

The 802.3ad mode requires that the switch have the appropriate ports configured as an 802.3ad aggregation. The precise method used to configure this varies from switch to switch, but, for example, a Cisco 3550 series switch requires that the appropriate ports first be grouped together in a single etherchannel instance, then that etherchannel is set to mode "lacp" to enable 802.3ad (instead of standard EtherChannel).

The balance-rr, balance-xor and broadcast modes generally require that the switch have the appropriate ports grouped together. The nomenclature for such a group differs between switches, it may be called an "etherchannel" (as in the Cisco example, above), a "trunk group" or some other similar variation. For these modes, each switch will also have its own configuration options for the switch's transmit policy to the bond. Typical choices include XOR of either the MAC or IP addresses. The transmit policy of the two peers does not need to match. For these three modes, the bonding mode really selects a transmit policy for an EtherChannel group; all three will interoperate with another EtherChannel group.


Reference:
Net: Bonding
Metalink Notes: 456055.1, 436555.1, 434375.1

Wednesday, September 23, 2009

Differences between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE

SELECT ANY DICTIONARY is a system privilege, but SELECT_CATALOG_ROLE is a role, with pre-granted access to views under sys schema. (SELECT_CATALOG_ROLE has 2197 granted object privileges in 11.2.0.1)

If "O7_DICTIONARY_ACCESSIBILITY" is true, then "SELECT ANY TABLE" privilege can select any tables/views, from sys and non-sys schemas.

If "O7_DICTIONARY_ACCESSIBILITY" is false, and SELECT_CATALOG_ROLE granted, user can select these pre-granted views from sys schema.

If "SELECT ANY DICTIONARY" granted, it can access sys schema tables/views.

If you want to create a super read-only user, grant it with "CREATE SESSION", "SELECT ANY TABLE", "SELECT ANY DICTIONARY" system privileges.

Examples of PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME Used Together

Symptoms:

ORA-28007: the password cannot be reused


9i(and above) Behaviour

a. If both PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME are unlimited, it means
that this parameter is ignored.
This is so for the backward compatibility also.

b. If PASSWORD_REUSE_MAX is set to 10 and PASSWORD_REUSE_TIME is set to
UNLIMITED, the user can never reuse his/her password.

c. If PASSWORD_REUSE_TIME is set to 1 month and PASSWORD_REUSE_MAX is set to
UNLIMITED, the user can never reuse his/her password

d. If PASSWORD_REUSE_TIME is set to 1 month and PASSWORD_REUSE_MAX is set to 10,
the user is allowed to reuse his/her password after 1 month if the user has
changed the password 10 times

8i Behavior

If either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX is set to unlimited, the
password can be reused whichever command you use, SQL*Plus command
PASSWORD or SQL ALTER USER ...IDENTIFIED by.

Reference:

Oracle notes: 228991.1

News: End of Service Life Status for Solaris Operating System

Currently Status:

Solaris 8: 10/3/08 (LS), 10/23/10(Phase 1), 10/23/13 (Phase 2)
Solaris 9: 10/30/09 (LS), 10/30/11(Phase 1), 10/30/14 (Phase 2)
Solaris 10: TBD for LS, Phase1/2





As shown above in Figure 1, the Solaris life cycle is defined by seven major events:
E1.
General Availability (GA): This represents when a new version, e.g. Solaris 10, becomes available in the market.
E2.
End of Life (EOL) Pre-Notification: When Sun can provide the first notification to customers that it will be announcing the EOL of a milestone version. At this time, the date of the final EOL announcement is set to be one year later1.
E3.
End of Life (EOL) Announcement: When Sun announces that it will stop active development of a milestone version. The period from GA to the EOL announcement is at least four years and six months, and could be longer. EOL announcement triggers the start of a 90 day Last Order period.
E4.
Last Order Date (LOD): Marks the end of a 90 day period after the EOL announcement during which customers can place their final orders for the product. Note: customers can continue to purchase and renew regular support contracts until End of Service Life (E7).
E5.
Last Ship Date (LSD): Marks the 90 day period after LOD for Sun to ship the EOL product to customers. LSD marks the formal start of the five year Retirement Phase of that product.
E5 to E6.
Phase 1: Phase 1 support will provide contract customers with full remedial support, accepting requests for enhancements and cosmetic bugs. Solaris SW updates, formerly known as patches, will be created as needed, and SunAlerts and recommended Patch Clusters will be maintained through the end of Phase 1. You will continue to receive the same level of support that you received before the product End-Of-Lifed.
E6 to E7.
Phase 2: Contract customers will continue to receive online and telephone support and have access to pre-existing Solaris SW updates (patches). Pre-existing means Solaris SW updates created prior to start of Phase 2. Sun may offer the option to purchase a Solaris Vintage Patch Service during Phase 2.

Reference:
http://www.sun.com/software/solaris/lifecycle.xml
http://www.sun.com/service/eosl/eosl_solaris.html?cid=e9497

Monday, September 21, 2009

News: Oracle Enterprise Linux 5.4 is available for download

You can download OEL 5.4 from edelivery.oracle.com, which is a "clone" of RHEL 5.4. Comparing OEL and "CentOS", which is another clone of RHEL, OEL is better when we just want to test Oracle installation with RHEL clone Linux.



There is a public yum server created for OEL by Oracle, but currently YUM repository for OEL 5.4 is still not avaliable yet, but coming soon.

Sunday, September 20, 2009

How to: CAP_PROPAGATE CAP_BYPASS_RAC_VMM CAP_NUMA_ATTACH

Symptoms:

During 10.2.0.3 RAC upgrading to 10.2.0.4, OS is AIX 5300-09-03

The output during execution of root102.sh as following:

Error : Please change the CRS_ORACLE_USER id to have the following OS capabilities :

< CAP_PROPAGATE CAP_BYPASS_RAC_VMM CAP_NUMA_ATTACH >
by running command then 'crsctl stop crs' and 'crsctl start crs'.
The CSS daemon, ocssd.bin, will not run in realtime without this change
Oracle strongly recommends that the CSS daemon run realtime


$ORA_CRS_HOME/log//cssd/cssdOUT.log

priority string (0)
s0clssscSetScheduling: realtime requested, but not supported by user authorization Please ensure that this user has the followingcapabilities, using the /usr/bin/chuser command:
CAP_NUMA_ATTACH CAP_BYPASS_RAC_VMM CAP_PROPAGATE
setsid: failed with -1/1
2009-09-20 17:51 CSSD starting


Causes:

These capabilities CAP_NUMA_ATTACH, CAP_BYPASS_RAC_VMM, and CAP_PROPAGATE were introduced in AIX 5.3 TL5 (5300-05)

Solution:


chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE,CAP_NUMA_ATTACH


Verify:


/usr/sbin/lsuser -a capabilities


References:

Metalink notes: 726234.1, 739371.1