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
Tuesday, October 19, 2010
Monday, October 11, 2010
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.
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.
Saturday, October 2, 2010
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
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
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
Tuesday, September 28, 2010
Configure EM DBcontrol in 11gR2
[11gr2@rh5 grid]$ emca -config dbcontrol db -repos create
STARTED EMCA at Sep 28, 2010 12:27:55 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u11gr2/app/oracle/product/11.2.0/grid ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): luodonghua@gmail.com
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u11gr2/app/oracle/product/11.2.0/grid ]:
ASM SID [ +ASM ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]:
ASM user password:
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/dbhome_1
Local hostname ................ rh5.lab.dbaglobe.com
Listener ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/grid
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... luodonghua@gmail.com
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/grid
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Sep 28, 2010 12:28:31 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u11gr2/app/oracle/cfgtoollogs/emca/orcl/emca_2010_09_28_00_27_55.log.
Sep 28, 2010 12:28:33 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Sep 28, 2010 12:37:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Sep 28, 2010 12:37:10 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Sep 28, 2010 12:38:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Sep 28, 2010 12:38:59 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Sep 28, 2010 12:38:59 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Sep 28, 2010 12:39:33 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Sep 28, 2010 12:39:33 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Sep 28, 2010 12:40:00 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Sep 28, 2010 12:40:00 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 28, 2010 12:42:12 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 28, 2010 12:42:14 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://rh5.lab.dbaglobe.com:1158/em <<<<<<<<<<<
Sep 28, 2010 12:42:22 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u11gr2/app/oracle/product/11.2.0/dbhome_1/rh5.lab.dbaglobe.com_orcl/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 28, 2010 12:42:22 AM
STARTED EMCA at Sep 28, 2010 12:27:55 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u11gr2/app/oracle/product/11.2.0/grid ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): luodonghua@gmail.com
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u11gr2/app/oracle/product/11.2.0/grid ]:
ASM SID [ +ASM ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]:
ASM user password:
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/dbhome_1
Local hostname ................ rh5.lab.dbaglobe.com
Listener ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/grid
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... luodonghua@gmail.com
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u11gr2/app/oracle/product/11.2.0/grid
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Sep 28, 2010 12:28:31 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u11gr2/app/oracle/cfgtoollogs/emca/orcl/emca_2010_09_28_00_27_55.log.
Sep 28, 2010 12:28:33 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Sep 28, 2010 12:37:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Sep 28, 2010 12:37:10 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Sep 28, 2010 12:38:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Sep 28, 2010 12:38:59 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Sep 28, 2010 12:38:59 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Sep 28, 2010 12:39:33 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Sep 28, 2010 12:39:33 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Sep 28, 2010 12:40:00 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Sep 28, 2010 12:40:00 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 28, 2010 12:42:12 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 28, 2010 12:42:14 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://rh5.lab.dbaglobe.com:1158/em <<<<<<<<<<<
Sep 28, 2010 12:42:22 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u11gr2/app/oracle/product/11.2.0/dbhome_1/rh5.lab.dbaglobe.com_orcl/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 28, 2010 12:42:22 AM
Sunday, September 19, 2010
Identify the Current Version of SQL Server 2000
To identify the installed version of SQL Server 2000 Database Components
Execute one of the following queries against an instance of the database engine by using isql, osql, or Query Analyzer.
SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')
Execute one of the following queries against an instance of the database engine by using isql, osql, or Query Analyzer.
SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')
Find out your version of Database Components by means of the following table. SQL Server 2000 Version and Level @@VERSION Product Level
>>> SQL Server 2000 Original Release 8.00.194 RTM
>>> Database Components SP1 8.00.384 SP1
>>> Database Components SP2 8.00.534 SP2
>>> Database Components SP3, SP3a or MSDE 2000 Release A. 8.00.760 SP3
>>> Database Components SP4 8.00.2039 SP4
Note Your product version may be different than these values if you applied a hotfix after installing the product or after installing a previous service pack. For example, @@VERSION returns a value of 8.00.818 after you apply the security fix MS03-031 to SQL Server 2000 SP3a.
(Optional) If you are not sure whether you are running an edition of the SQL Server 2000 database engine or MSDE 2000, use isql, osql or Query Analyzer to execute the following query against the instance in question.
SELECT SERVERPROPERTY('Edition')
If this query returns "desktop engine," you are running an instance of MSDE 2000; otherwise, you are running an instance of SQL Server 2000 database engine.
Saturday, September 18, 2010
How flashback database can saves your time
============ Part 1, crash the database ============
SQL> conn / as sysdba
SQL> delete from sys.user$;
41 rows deleted.
SQL> commit;
SQL> select * from dba_users;
no rows selected
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [],
[], [], [], [], []
Process ID: 3366
Session ID: 125 Serial number: 5
============ Part 2, flashback the database ============
SQL> startup mount
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE
-------------------- -------------------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
680531 2010-SEP-05 06:35:35 1440 75988992
95232000
SQL> flashback database to timestamp to_timestamp('2010-SEP-05 20:30:00','YYYY-MON-DD HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from sys.user$;
COUNT(*)
----------
41
SQL> conn / as sysdba
SQL> delete from sys.user$;
41 rows deleted.
SQL> commit;
SQL> select * from dba_users;
no rows selected
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [],
[], [], [], [], []
Process ID: 3366
Session ID: 125 Serial number: 5
============ Part 2, flashback the database ============
SQL> startup mount
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE
-------------------- -------------------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
680531 2010-SEP-05 06:35:35 1440 75988992
95232000
SQL> flashback database to timestamp to_timestamp('2010-SEP-05 20:30:00','YYYY-MON-DD HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from sys.user$;
COUNT(*)
----------
41
Subscribe to:
Posts (Atom)
