Oracle: A safe way to invalid execution plan related with sepcific objects

SQL> SELECT EXECUTIONS,INVALIDATIONS,PARSE_CALLS from v$sqlarea
2 where sql_text like 'select /*+ testsql%';

EXECUTIONS INVALIDATIONS PARSE_CALLS
---------- ------------- -----------
1 3 1

SQL> COMMENT ON TABLE EMPLOYEES IS 'THIS IS TEST EMPLOYEE';

Comment created.

SQL> select /*+ testsql*/ count(*) from employees where employee_id=1;


COUNT(*)
----------
0

SQL> SQL>
SQL> SELECT EXECUTIONS,INVALIDATIONS,PARSE_CALLS from v$sqlarea
2 where sql_text like 'select /*+ testsql%';

EXECUTIONS INVALIDATIONS PARSE_CALLS
--- ------- ------------- -----------
1 4 1

SQL> select /*+ testsql*/ count(*) from employees where employee_id=1;

COUNT(*)
----------
0

SQL> SELECT EXECUTIONS,INVALIDATIONS,PARSE_CALLS from v$sqlarea
2 where sql_text like 'select /*+ testsql%';

EXECUTIONS INVALIDATIONS PARSE_CALLS
---------- ------------- -----------
2 4 2

How to reslove ORA-08104 : this index object 75350 is being online built or rebuilt

SQL> alter index S.SYS_C007740 rebuild online tablespace s_data01;
alter index S.SYS_C007740 rebuild online tablespace s_data01
*
ERROR at line 1:
ORA-08104: this index object 75350 is being online built or rebuilt

SQL> conn / as sysdba
Connected.
SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2563


SQL> declare
2 isclean boolean;
3 begin
4 isclean :=false;
5 while isclean=false
6 loop
7 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
8 dbms_lock.sleep(10);
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2051


SQL> conn donghua2/donghua2
Connected.
SQL> alter index S.SYS_C007740 rebuild online tablespace s_data01;

Index altered.

ORA-14037 & nls_date_format

SQL> conn donghua/donghua
Connected.
SQL> create table t (rtime varchar2(31))
2 partition by range (rtime)
3 (partition p201001 values less than (to_date('20100201','YYYYMMDD')),
4 partition p201002 values less than (to_date('20100301','YYYYMMDD')),
5 partition p201003 values less than (to_date('20100401','YYYYMMDD')))
6
SQL> /
partition p201002 values less than (to_date('20100301','YYYYMMDD')),
*
ERROR at line 4:
ORA-14037: partition bound of partition "P201002" is too high


SQL> alter session set nls_date_format='YYYYMMDD';

Session altered.

SQL> create table t (rtime varchar2(31))
2 partition by range (rtime)
3 (partition p201001 values less than (to_date('20100201','YYYYMMDD')),
4 partition p201002 values less than (to_date('20100301','YYYYMMDD')),
5 partition p201003 values less than (to_date('20100401','YYYYMMDD')));

Table created.

SQL>


[oracle@rh5 ~]$ expdp donghua/donghua directory=dir1 dumpfile=t.dmp tables=T

Export: Release 11.2.0.2.0 - Production on Sat Oct 2 10:26:56 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "DONGHUA"."SYS_EXPORT_TABLE_01": donghua/******** directory=dir1 dumpfile=t.dmp tables=T
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DONGHUA"."T":"P201001" 0 KB 0 rows
. . exported "DONGHUA"."T":"P201002" 0 KB 0 rows
. . exported "DONGHUA"."T":"P201003" 0 KB 0 rows
Master table "DONGHUA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DONGHUA.SYS_EXPORT_TABLE_01 is:
/u11gr2/dir1/t.dmp
Job "DONGHUA"."SYS_EXPORT_TABLE_01" successfully completed at 10:27:12






[oracle@rh5 ~]$ impdp donghua/donghua directory=dir1 dumpfile=t.dmp tables=T table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Sat Oct 2 10:29:42 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "DONGHUA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "DONGHUA"."SYS_IMPORT_TABLE_01": donghua/******** directory=dir1 dumpfile=t.dmp tables=T table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"DONGHUA"."T" failed to create with error:
ORA-14037: partition bound of partition "P201002" is too high
Failing sql is:
CREATE TABLE "DONGHUA"."T" ("RTIME" VARCHAR2(31 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARTITION BY RANGE ("RTIME") (PARTITION "P201001" VALUES LESS THAN (to_date('20100201','YYYYMMDD')) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MA
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DONGHUA"."T":"P201001" 0 KB 0 rows
. . imported "DONGHUA"."T":"P201002" 0 KB 0 rows
. . imported "DONGHUA"."T":"P201003" 0 KB 0 rows
Job "DONGHUA"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 10:29:45


[oracle@rh5 ~]$ sqlplus donghua/donghua

SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 2 10:32:24 2010

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


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

SQL> desc t
ERROR:
ORA-04043: object t does not exist

[bug?]After patchset 11.2.0.2 installation, ASM/Listener still run under old home (using Oracle Restart)

[Update - begin]
After manually correct following files to point correct grid home, problem resolved.
/etc/init.d/ohasd
/etc/init/init.ohasd


[oracle@rh5 ~]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid_11202
End points: TCP:1521

The root cause is /u11gr2/app/oracle/product/11.2.0/grid_11202/rootupgrade.sh did not update above files for out-of-place patchset installation.

[Update - end]

[11gr2@rh5 ~]$ cd /u11gr2/app/oracle/diag/asm/+asm/+ASM/trace/
[11gr2@rh5 trace]$ less alert_+ASM.log

NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 1
SUCCESS: diskgroup DATA was mounted
SUCCESS: ALTER DISKGROUP ALL MOUNT /* asm agent */
SQL> ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent */
SUCCESS: ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent */
NOTE: diskgroup resource ora.DATA.dg is online
Sat Oct 02 07:56:10 2010
ERROR: Refusing connection from client orcl:orcl because client
software version (11.2.0.2.0) is greater than current version 11.2.0.1.0
Sat Oct 02 07:56:11 2010
ERROR: Refusing connection from client orcl:orcl because client
software version (11.2.0.2.0) is greater than current version 11.2.0.1.0

[11gr2@rh5 trace]$ ls -l /u11gr2/app/oracle/product/11.2.0/grid_11202/dbs/
total 16
-rw-rw---- 1 11gr2 oinstall 1743 Oct 1 21:11 ab_+ASM.dat
-rw-rw---- 1 11gr2 oinstall 1544 Oct 1 21:11 hc_+ASM.dat
-rw-r--r-- 1 11gr2 oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 11gr2 oinstall 1536 Sep 28 14:03 orapw+ASM
[11gr2@rh5 trace]$ ls -l /u11gr2/app/oracle/product/11.2.0/grid/dbs/
total 20
-rw-rw---- 1 11gr2 oinstall 939 Oct 2 07:55 ab_+ASM.dat
-rw-rw---- 1 11gr2 oinstall 1544 Oct 2 07:55 hc_+ASM.dat
-rw-r--r-- 1 11gr2 oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 11gr2 oinstall 1536 Sep 27 22:30 orapw+ASM
drwx------ 2 11gr2 oinstall 4096 Sep 27 22:29 peshm_+ASM_1
[11gr2@rh5 trace]$


[11gr2@rh5 trace]$ ps -ef|grep tns
11gr2 3248 1 0 07:54 ? 00:00:00 /u11gr2/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit
11gr2 4854 4491 0 08:15 pts/0 00:00:00 grep tns
[11gr2@rh5 trace]$
[11gr2@rh5 trace]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rh5
ora....ER.lsnr ora....er.type ONLINE ONLINE rh5
ora.asm ora.asm.type ONLINE ONLINE rh5
ora.cssd ora.cssd.type ONLINE ONLINE rh5
ora.diskmon ora....on.type ONLINE ONLINE rh5
ora.evmd ora.evm.type ONLINE ONLINE rh5
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcl.db ora....se.type ONLINE OFFLINE


[11gr2@rh5 trace]$ srvctl config asm
ASM home: /u11gr2/app/oracle/product/11.2.0/grid_11202
ASM listener: LISTENER
Spfile: +DATA/asm/asmparameterfile/registry.253.730852187
ASM diskgroup discovery string: /dev/hda*
[11gr2@rh5 trace]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521
[11gr2@rh5 trace]$


[11gr2@rh5 trace]$ srvctl modify listener -o /u11gr2/app/oracle/product/11.2.0/grid_11202
[11gr2@rh5 trace]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521
[11gr2@rh5 trace]$ srvctl modify listener -l LISTENER -o /u11gr2/app/oracle/product/11.2.0/grid_11202
[11gr2@rh5 trace]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521
[11gr2@rh5 trace]$



[11gr2@rh5 trace]$ srvctl modify listener -o /u11gr2/app/oracle/product/11.2.0/grid_11202
[11gr2@rh5 trace]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521
[11gr2@rh5 trace]$ srvctl modify listener -l LISTENER -o /u11gr2/app/oracle/product/11.2.0/grid_11202
[11gr2@rh5 trace]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521
[11gr2@rh5 trace]$ srvctl remove listener
PRCR-1025 : Resource ora.LISTENER.lsnr is still running

[11gr2@rh5 trace]$ srvctl stop listener
[11gr2@rh5 trace]$ srvctl remove listener
[11gr2@rh5 trace]$ srvctl add listener -l LISTENER -o /u11gr2/app/oracle/product/11.2.0/grid_11202 -p 1521
[11gr2@rh5 trace]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521
[11gr2@rh5 trace]$ srvctl remove listener
[11gr2@rh5 trace]$ srvctl add listener -l LISTENER -o /u11gr2/app/oracle/product/11.2.0/grid_11202 -p 1521
[11gr2@rh5 trace]$ cat /u11gr2/app/oracle/product/11.2.0/grid_11202/network/admin/
listener.ora listener.ora.bak.rh5 samples/ shrept.lst sqlnet.ora
[11gr2@rh5 trace]$ cat /u11gr2/app/oracle/product/11.2.0/grid_11202/network/admin/listener.ora
# listener.ora Network Configuration File: /u11gr2/app/oracle/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rh5.lab.dbaglobe.com)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u11gr2/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
[11gr2@rh5 trace]$ srvctl add listener -l LISTENER -o "/u11gr2/app/oracle/product/11.2.0/grid_11202" -p 1521
[11gr2@rh5 trace]$ srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521



[11gr2@rh5 trace]$ /u11gr2/app/oracle/product/11.2.0/grid_11202/bin/srvctl config listener
Name: LISTENER
Home: /u11gr2/app/oracle/product/11.2.0/grid
End points: TCP:1521
[11gr2@rh5 trace]$


[11gr2@rh5 trace]$ strace srvctl modify listener -l LISTENER -o /u11gr2/app/oracle/product/11.2.0/grid_11202
execve("/u11gr2/app/oracle/product/11.2.0/grid/bin/srvctl", ["srvctl", "modify", "listener", "-l", "LISTENER", "-o", "/u11gr2/app/oracle/product/11.2."], [/* 28 vars */]) = 0