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.