Archive

Archive for June, 2011

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

June 24, 2011 Leave a comment

Today, I try to change a invisible index to visible, I get the following messages

“ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”. Before 11g, we can only retry and retry until get the resource.

In 11gR, we can change the session to wait for the resource lock by the command below

SQL> ALTER SESSION SET ddl_lock_timeout=40;<- this means that try to lock resource s until ddl_lock_timeout ( 40 sec)

Session altered.

SQL> alter index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 visible;
alter index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 visible
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ( after 40 second, still can’t lock the resources)

SQL> / <– retry again, it is ok

Advertisements

Create Invisible Index in 11gR1 for SQL Performance Tuning

June 24, 2011 Leave a comment

Today,  I need to create a index to test the performance of a SQL.In order to impact current SQL,  I use invisible option to create indexes. The following is the example to use the invisible option

SQL> create bitmap index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 on cmsnews.DATAFEED_NEWS_CONTROLFILE (Ltrim(Rtrim(Lower(filename)))) invisible;

SQL> set autotrace on

SQL> @pq01

lan hash value: 2791608648

—————————————————————————————————–
| Id  | Operation          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————–
|   0 | SELECT STATEMENT   |                                |     1 |  1011 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                                |     1 |  1011 |            |          |
|*  2 |   FILTER           |                                |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| DATAFEED_NEWS_CONTROLFILE_IDX1 |     1 |  1011 |     0   (0)| 00:00:01 |
—————————————————————————————————–

It shows that the index DATAFEED_NEWS_CONTR_INDEX2 is not used

SQL> ALTER SESSION SET “optimizer_use_invisible_indexes” = TRUE; <– alter session to  use the visible index

SQL> @pq01

Plan hash value: 73755227

——————————————————————————————————————–
| Id  | Operation                         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————————–
|   0 | SELECT STATEMENT                  |                                |     1 |  1011 |     2  (50)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |                                |     1 |  1011 |            |          |
|*  2 |   FILTER                          |                                |       |       |            |          |
|   3 |    BITMAP CONVERSION COUNT        |                                |     1 |  1011 |     2  (50)| 00:00:01 |
|   4 |     BITMAP AND                    |                                |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                                |       |       |            |          |
|   6 |       SORT ORDER BY               |                                |       |       |            |          |
|*  7 |        INDEX RANGE SCAN           | DATAFEED_NEWS_CONTROLFILE_IDX1 |       |       |     0   (0)| 00:00:01 |
|*  8 |      BITMAP INDEX SINGLE VALUE    | DATAFEED_NEWS_CONTR_INDEX2     |       |       |            |          |
——————————————————————————————————————–
It shows that the new index is used by the session

Finally, make the indexes visible to all sessions, by the command below

SQL> alter index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 visible

 

Categories: SQL Tuning

Configure Redhat XDMCP GNOME settings for Xmanager

June 23, 2011 Leave a comment

Redhat 5

1. Edit /etc/sysconfig/desktop to add the f0llowing entry

DESKTOP=”GNOME”

2.. Edit /etc/gdm/custom/conf to add the following entry under [xdmcp]

( maybe /etc/X11/gdm/gdm.conf)

Enable=true

3. Restart gdm by the command /usr/sbin/gdm-restart

Redhat 6 or above

1. Edit /etc/gdm/custom.conf to add the entries below

[security]
DisallowTCP=false

[xdmcp]
Enable=true

2. Stop iptables

$ chkconfig –level345 iptables off

 

3. Start the server

 

 

 

Categories: Redhat Administration

Fix Managed Standby Recovery (ora_mrp0_qtprod) can’t automatically apply archived logs

June 23, 2011 Leave a comment

1. Standby database can’t automatically apply archive logs when it is started by the following command

SQL> startup nomount

SQL>  alter database mount standby database

SQL> alter database recover managed standby database through all switchover disconnect parallel 1;

2. The standby database alert.log shows the messages

alter database mount standby database
Thu Jun 23 14:02:22 2011
Successful mount of redo thread 1, with mount id 2705790826
Thu Jun 23 14:02:22 2011
Standby Database mounted.
Completed: alter database mount standby database
Thu Jun 23 14:02:36 2011
alter database recover managed standby database through all switchover disconnect parallel 1
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=14
MRP0: Background Managed Standby Recovery process started
Media Recovery Waiting for thread 1 seq# 47075 <– waiting for archive log 47075
Thu Jun 23 14:02:42 2011
Completed: alter database recover managed standby database th
Thu Jun 23 14:04:17 2011
RFS: Successfully opened standby logfile 11: ‘/qt/oradata/qtprod/stby_redo01.log’
Thu Jun 23 14:04:26 2011
Fetching gap sequence for thread 1, gap sequence 47075-47077 <– try to get archive log 47075 to 47077
Trying FAL server: yck_qtprod
Thu Jun 23 14:05:34 2011
ARC1: Evaluating archive   log 11 thread 1 sequence 47079
ARC1: Beginning to archive log 11 thread 1 sequence 47079 <– archive log 47079 received

( it shows that the Managed Standby Recovery process still can’t apply the archive and is waiting for 47075 since the archive log 47079 has been received)

3. Query v$archive_gap, it shows

SQL> select low_sequence#, high_sequence# from  v$archive_gap;

LOW_SEQUENCE# HIGH_SEQUENCE#
————- ————–
47075          47077

4. Manually transfer the archive log 47075 to 47077 from mater database to standby database

5. Register the archived logs on the standby database

SQL> alter database register physical logfile ‘/qt/admin/qtprod/archive/qtprod_47075.arc’;
Database altered.
SQL> alter database register physical logfile ‘/qt/admin/qtprod/archive/qtprod_47076.arc’;
Database altered.
SQL> alter database register physical logfile ‘/qt/admin/qtprod/archive/qtprod_47077.arc’;
Database altered.
SQL> select low_sequence#, high_sequence# from  v$archive_gap;
no rows selected <– there is no gap now

6. Check the standby database  alert.log, it shows

alter database register physical logfile ‘/qt/admin/qtprod/archive/qtprod_47075.arc’ <– register archive logs
Thu Jun 23 14:19:29 2011
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Completed: alter database register physical logfile ‘/qt/admi
Thu Jun 23 14:19:41 2011
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47075.arc
Media Recovery Waiting for thread 1 seq# 47076
Fetching gap sequence for thread 1, gap sequence 47076-47077
Trying FAL server: yck_qtprod
Thu Jun 23 14:19:55 2011
alter database register physical logfile ‘/qt/admin/qtprod/archive/qtprod_47076.arc’ <– register archive logs
Thu Jun 23 14:19:55 2011
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Completed: alter database register physical logfile ‘/qt/admi
Thu Jun 23 14:19:57 2011
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47076.arc
Media Recovery Waiting for thread 1 seq# 47077
Fetching gap sequence for thread 1, gap sequence 47077-47077
Trying FAL server: yck_qtprod
Thu Jun 23 14:20:11 2011
alter database register physical logfile ‘/qt/admin/qtprod/archive/qtprod_47077.arc’ <– register archive logs
Thu Jun 23 14:20:11 2011
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Completed: alter database register physical logfile ‘/qt/admi
Thu Jun 23 14:20:12 2011
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47077.arc <– after archive gap is fixed, the Manged
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47078.arc  — Recovery Process immediately apply all
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47079.arc — rest archived logs >
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47080.arc
Media Recovery Waiting for thread 1 seq# 47081 (in transit)
?Thu Jun 23 14:20:46 2011
ARC0: Evaluating archive   log 11 thread 1 sequence 47081
ARC0: Beginning to archive log 11 thread 1 sequence 47081
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/qt/admin/qtprod/archive/qtprod_47081.arc’
ARC0: Completed archiving  log 11 thread 1 sequence 47081
Thu Jun 23 14:20:46 2011
RFS: Successfully opened standby logfile 11: ‘/qt/oradata/qtprod/stby_redo01.log’
Thu Jun 23 14:20:46 2011
ARC0: Cannot archive online log based on backup controlfile
Thu Jun 23 14:20:57 2011
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47081.arc
Media Recovery Waiting for thread 1 seq# 47082 (in transit)
Thu Jun 23 14:21:20 2011
ARC0: Evaluating archive   log 11 thread 1 sequence 47082
ARC0: Beginning to archive log 11 thread 1 sequence 47082
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/qt/admin/qtprod/archive/qtprod_47082.arc’
ARC0: Completed archiving  log 11 thread 1 sequence 47082
Thu Jun 23 14:21:20 2011
RFS: Successfully opened standby logfile 11: ‘/qt/oradata/qtprod/stby_redo01.log’
Thu Jun 23 14:21:20 2011
ARC0: Cannot archive online log based on backup controlfile <– this is normal message
Thu Jun 23 14:21:27 2011
Media Recovery Log /qt/admin/qtprod/archive/qtprod_47082.arc
Media Recovery Waiting for thread 1 seq# 47083 (in transit) <– it shows that Managed Standby Recovery process automatically apply archivelog.

Categories: Data Guard

VxVM vxdisksetup ERROR V-5-2-2480 Disk is too small for supplied parameters

June 17, 2011 Leave a comment

Today, I added new disks, get the error messages as following

1. Create /devices entries by OS command

# devfsadm -c disk

2. Initiate the disks, and get the following errors

wvprp06# ./vxdisksetup -ie c3t18d30
./vxdisksetup: test: argument expected
wvprp06# ./vxdisksetup -ie c3t18d30 format=sliced
./vxdisksetup: test: argument expected
wvprp06# ./vxdisksetup -ie c3t18d30 format=simple
./vxdisksetup: test: argument expected
wvprp06#  ./vxdisksetup -ie c3t18d30 format=cdsdisk
VxVM vxdisksetup ERROR V-5-2-1529 The -e flag is incompatible with the cdsdisk format.
wvprp06#  ./vxdisksetup -i  c3t18d30 format=cdsdisk
VxVM vxdisksetup ERROR V-5-2-2480 Disk is too small for supplied parameters
wvprp06# format

Solution

1. Then, I check the disk status, it showed the multi-pathing is invalid

wvprp06# hagrp -switch REP_GROUP   -to wvprp06
wvprp06# vxdisk list c3t18d30
Device:    c3t18d30s2
devicetag: c3t18d30
type:      auto
flags:     online error private autoconfig
pubpaths:  block=/dev/vx/dmp/c3t18d30s2 char=/dev/vx/rdmp/c3t18d30s2
guid:      –
udid:      FUJITSU%5FETERNUS%5FDXL%5F0210AD%5F0020
site:      –
errno:     Device path not valid <– the multi-pathing is invalid
Multipathing information:
numpaths:   1
c3t18d30s2      state=enabled

 

2. Re-enable vxdctl, the problem was fixed

wvprp06# vxdctl enable
wvprp06# vxdisk list c3t18d30
Device:    c3t18d30
devicetag: c3t18d30
type:      auto
info:      format=none
flags:     online ready private autoconfig invalid
pubpaths:  block=/dev/vx/dmp/c3t18d30 char=/dev/vx/rdmp/c3t18d30
guid:      –
udid:      FUJITSU%5FETERNUS%5FDXL%5F0210AD%5F0020
site:      –
Multipathing information:
numpaths:   2
c3t18d30        state=enabled
c2t19d30        state=enabled
wvprp06# ./vxdisksetup -ie c3t18d30
wvprp06#

 

 

Categories: Solaris Admin

ORA-39083: Object type OBJECT_GRANT failed to create with error:

June 14, 2011 Leave a comment

I imported a schema from Oracle 11.2.0.1 database to Oracle 11.1.0.7 database.  I got the following errors

ERRORS

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-00990: missing or invalid privilege
Failing sql is:
GRANT  ON “UNI_EDS3″.”PON_SEQID” TO “UNI_MLVPNX”

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-00990: missing or invalid privilege
Failing sql is:
GRANT  ON “UNI_EDS3″.”PON_SEQID” TO “UNI_PON”

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-00990: missing or invalid privilege
Failing sql is:
GRANT  ON “UNI_EDS3″.”COM_EXCH_MT_KEY” TO “UNI_MLVPNX”

After checking with support oracle, I got that it is bug. The details is referred to the Doc ID 1069105.11.  The solutions is applied the Patch 8856467. After patching the Oracle 11.2.0.1. it works

 

 

Categories: Oracle Bug

Change LDM guest vnet mac-addree

June 9, 2011 Leave a comment

1. Stop ldm guest

2. Unbind the ldm

ldm unbind ldg1

3. Change mac-address of vnet

# ldm set-vnet mac-addr=0:14:4f:f9:65:4e vnet0 ldg1
# ldm set-vnet mac-addr=0:14:4f:fa:76:1d vnet1 ldg1
# ldm bind ldg1
# ldm list-bindings ldg1

4. Start the guest

Categories: LDom