1. 设置rman CONTROLFILE AUTOBACKUP ON时,要注意进行表空间变化时,如增加数据文件,且要增加很多个数据文件,且是一个一个语句的加。那就要注意会生成自动备份的控制文件,每个语句会生成一个,因此,对于比较大型的系统,某些表空间已经到达几个TB了,要让使用率从90%加数据文件到85%,就要加很多数据文件,如果每个数据文件一个语句,就会生成很多自动备份的控制文件,从而造成文件系统撑爆。另外注意一下,如果设置了db_recovery_file_dest,自动备份的控制文件就会生成在这个路径下,如果该参数为空,自动备份的控制文件就会在dbs目录下。
解决方法是:将加多个数据文件的语句放在一个语句中执行。
2.设置autoextend on不生效。是因为resize了datafile,且resize的大小大于maxisize。
表空间已创建。
已用时间: 00: 00: 02.53
sys@ORA10G(9.186.80.102)> alter database datafile 'E:\ORA10G\ORACLE\ORADATA\ORA10G\TEST01.DBF' resize 8m;
数据库已更改。
已用时间: 00: 00: 00.40
sys@ORA10G(9.186.80.102)>
sys@ORA10G(9.186.80.102)> select file_name,AUTOEXTENSIBLE,BYTES,MAXBYTES from dba_data_files;
FILE_NAME AUTOEX BYTES MAXBYTES
------------------------------------------------------------ ------ ---------- ----------
E:\ORA10G\ORACLE\ORADATA\ORA10G\TEST01.DBF YES 8388608 4194304
此时autoextend on就不生效,表空间最大只能撑到8M。maxsize在这里无意义。
3. 在10.2.0.1 windows中,如果process设置过小,会连接不上,但是报错不是ora-00020(oracle 9i中的超过process的报错是ora-00020),而是这样的报错:
C:\Documents and Settings\Administrator>sqlplus system/oracle@ora10g
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 6 10:10:25 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
##通过sqlplus直接连接:
C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 6 10:12:08 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected.
ERROR:
ORA-01012: not logged on
>
5. 9i 开始,analyze table/index validate structure可以用online参数,对于大的表或者索引,如果怀疑有逻辑坏块,需要用validate structure来检查,但是如果不用online参数,将会锁表很长时间,这对大型OLTP系统是不可接受的。此时我们可以用online参数。
上面的结论,我们可以通过10704的事件(10704,To trace which enqueues are being obtained use)来进行验证,看是否有TM的锁:
非online的情况:
已处理的语句
sys@ORA10G(9.115.104.68)> oradebug UNLIMIT
已处理的语句
sys@ORA10G(9.115.104.68)> oradebug event 10704 trace name context forever,level 10;
已处理的语句
sys@ORA10G(9.115.104.68)> analyze table t1 validate structure;
表已分析。
已用时间: 00: 00: 00.95
sys@ORA10G(9.115.104.68)> oradebug event 10704 trace name context off;
已处理的语句
sys@ORA10G(9.115.104.68)> oradebug TRACEFILE_NAME
e:\ora10g\oracle\admin\ora10g\udump\ora10g_ora_8692.trc
sys@ORA10G(9.115.104.68)>
C:\Documents and Settings\Administrator>cat e:\ora10g\oracle\admin\ora10g\udump\ora10g_ora_8692.trc
Dump file e:\ora10g\oracle\admin\ora10g\udump\ora10g_ora_8692.trc
Thu Sep 22 14:03:23 2011
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3
CPU : 4 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:593M/2995M, Ph+PgF:2490M/4883M, VA:1283M/2047M
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 8692, image: ORACLE.EXE (SHAD)
*** SERVICE NAME:(SYS$USERS) 2011-09-22 14:03:23.437
*** SESSION ID:(416.19) 2011-09-22 14:03:23.437
*** 2011-09-22 14:03:23.437
ksqgtl *** CU-33d4e9c0-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: xcb=0x325F53D4, ktcdix=2147483647, topxcb=0x325F53D4
ktcipt(topxcb)=0x0
*** 2011-09-22 14:03:23.453
ksucti: init txn DID from session DID 0001-0012-00000006
ksqgtl:
ksqlkdid: 0001-0012-00000006
*** 2011-09-22 14:03:23.468
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0012-00000006
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0012-00000006
ksqgtl: RETURNS 0
*** 2011-09-22 14:03:23.468
ksqrcl: CU,33d4e9c0,0
ksqrcl: returns 0
*** 2011-09-22 14:03:23.468
ksqgtl *** CU-33d4e9c0-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: xcb=0x325F53D4, ktcdix=2147483647, topxcb=0x325F53D4
ktcipt(topxcb)=0x0
*** 2011-09-22 14:03:23.484
ksucti: init session DID from txn DID: 0001-0012-00000006
ksqgtl:
ksqlkdid: 0001-0012-00000006
*** 2011-09-22 14:03:23.484
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0012-00000006
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0012-00000006
ksqgtl: RETURNS 0
*** 2011-09-22 14:03:23.500
ksqrcl: CU,33d4e9c0,0
ksqrcl: returns 0
*** 2011-09-22 14:03:23.515
ksqgtl *** CU-28dfe768-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: xcb=0x325F53D4, ktcdix=2147483647, topxcb=0x325F53D4
ktcipt(topxcb)=0x0
*** 2011-09-22 14:03:23.515
ksucti: init session DID from txn DID: 0001-0012-00000006
ksqgtl:
ksqlkdid: 0001-0012-00000006
*** 2011-09-22 14:03:23.531
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0012-00000006
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0012-00000006
ksqgtl: RETURNS 0
*** 2011-09-22 14:03:23.531
ksqrcl: CU,28dfe768,0
ksqrcl: returns 0
*** 2011-09-22 14:03:23.546
ksqgtl *** CU-28dfe768-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: xcb=0x325F53D4, ktcdix=2147483647, topxcb=0x325F53D4
ktcipt(topxcb)=0x0
*** 2011-09-22 14:03:23.546
ksucti: init session DID from txn DID: 0001-0012-00000006
ksqgtl:
ksqlkdid: 0001-0012-00000006
*** 2011-09-22 14:03:23.546
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0012-00000006
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0012-00000006
ksqgtl: RETURNS 0
*** 2011-09-22 14:03:23.562
ksqrcl: CU,28dfe768,0
ksqrcl: returns 0
*** 2011-09-22 14:03:23.562
ksqgtl *** TM-0000ca0e-00000000 mode=4 flags=0x401 timeout=0 *** < <<<< 注意这里!
ksqgtl: xcb = 0x325F53D4 , ktcdix = 2147483647 , topxcb = 0x325F53D4
ktcipt ( topxcb )= 0x0
*** 2011-09-22 14:03:23 . 562
ksucti: init session DID from txn DID: 0001-0012-00000006
ksqgtl:
ksqlkdid: 0001-0012-00000006
*** 2011-09-22 14:03:23 . 578
*** ksudidTrace: ksqgtl
ktcmydid () : 0001-0012-00000006
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0012-00000006
ksqgtl: RETURNS 0
*** 2011-09-22 14:03:24 . 281
ksqgtl *** TX-00030017-000001ee mode = 6 flags = 0x401 timeout = 0 ***
ksqgtl: xcb = 0x325F53D4 , ktcdix = 2147483647 , topxcb = 0x325F53D4
ktcipt ( topxcb )= 0x0
*** 2011-09-22 14:03:24 . 296
ksucti: init session DID from txn DID: 0001-0012-00000006
ksqgtl:
ksqlkdid: 0001-0012-00000006
*** 2011-09-22 14:03:24 . 296
*** ksudidTrace: ksqgtl
ktcmydid () : 0001-0012-00000006
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0012-00000006
ksqgtl: RETURNS 0
*** 2011-09-22 14:03:24 . 312
ksqrcl: TX , 30017 , 1ee
ksqrcl: returns 0
*** 2011-09-22 14:03:24 . 312
ksqrcl: TM , ca0e , 0
ksqrcl: returns 0
C: \ Documents and Settings \ Administrator >
online的情况:
已处理的语句
sys@ORA10G(9.115.104.68)> oradebug UNLIMIT
已处理的语句
sys@ORA10G(9.115.104.68)> oradebug event 10704 trace name context forever,level 10;
已处理的语句
sys@ORA10G(9.115.104.68)> analyze table t1 validate structure online;
表已分析。
已用时间: 00: 00: 00.53
sys@ORA10G(9.115.104.68)> oradebug event 10704 trace name context off;
已处理的语句
sys@ORA10G(9.115.104.68)> oradebug TRACEFILE_NAME
e:\ora10g\oracle\admin\ora10g\udump\ora10g_ora_8216.trc
sys@ORA10G(9.115.104.68)>
C:\Documents and Settings\Administrator>cat e:\ora10g\oracle\admin\ora10g\udump\ora10g_ora_8216.trc
Dump file e:\ora10g\oracle\admin\ora10g\udump\ora10g_ora_8216.trc
Thu Sep 22 14:05:00 2011
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3
CPU : 4 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:598M/2995M, Ph+PgF:2495M/4883M, VA:1288M/2047M
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 8216, image: ORACLE.EXE (SHAD)
*** SERVICE NAME:(SYS$USERS) 2011-09-22 14:05:00.843
*** SESSION ID:(416.21) 2011-09-22 14:05:00.843
*** 2011-09-22 14:05:00.843
ksqgtl *** TX-00040022-000001e0 mode=6 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x325D8568, ktcdix=2147483647, topxcb=0x325D8568
ktcipt(topxcb)=0x0
*** 2011-09-22 14:05:00.875
ksucti: init txn DID from session DID 0001-0012-00000008
ksqgtl:
ksqlkdid: 0001-0012-00000008
*** 2011-09-22 14:05:00.875
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0012-00000008
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0012-00000008
ksqgtl: RETURNS 0
*** 2011-09-22 14:05:00.875
ksqrcl: TX,40022,1e0
ksqrcl: returns 0
C:\Documents and Settings\Administrator>
在这里我们看到,如果没加online参数,会有mode 4的TM锁,也就是共享的表锁,只能select,不能dml。
非online时的锁:
1* select * from dba_dml_locks
sys@ORA10G(9.115.104.68)> /
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTED LAST_CONVERT BLOCKING_OTHERS
---------- -------------------- ---------- -------------------------- -------------------------- ------------ ----------------------------
429 SYS T1 Share None 3 Not Blocking
已用时间: 00: 00: 00.01
sys@ORA10G(9.115.104.68)>
此时的锁和lock table in share mode一样:
表已锁定。
已用时间: 00: 00: 00.00
sys@ORA10G(9.115.104.68)> select * from dba_dml_locks;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTED LAST_CONVERT BLOCKING_OTHERS
---------- ---------- ---------- -------------------------- -------------------------- ------------ -------------------------------------
429 SYS T1 Share None 39 Not Blocking
已用时间: 00: 00: 00.01
sys@ORA10G(9.115.104.68)>
sys@ORA10G(9.115.104.68)> rollback;
回退已完成。
已用时间: 00: 00: 00.00
sys@ORA10G(9.115.104.68)> select * from dba_dml_locks;
未选定行
已用时间: 00: 00: 00.00
sys@ORA10G(9.115.104.68)>
online时,检查锁情况:
1* select * from dba_dml_locks
sys@ORA10G(9.115.104.68)> /
未选定行
已用时间: 00: 00: 00.01
sys@ORA10G(9.115.104.68)>