-- Displays the overall pga usage
--
select sum(value) max_pga_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session pga memory max';
-- Displays the overall current pga usage
select sum(value) all_current_sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session pga memory';
--this should be accurate
select name, sum(value/1024) "Value - KB"
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
and s.username is not NULL
and n.name in ('session pga memory', 'session pga memory max',
'session uga memory', 'session uga memory max')
group by name;
Tuesday, April 29, 2008
SORT_AREA_SIZE in 10g shared server
--In current 9i database, we don't use PGA_AGGREGATE_TARGET and use SORT_AREA_SIZE =1M
--In 10g database, we want to use PGA_AGGEGATE_TARGET, however below excepted from Oracle Document "Database Reference" confuses me, as we have shared server running with this database. Do I still need to set it explicitly?
"Note: Oracle does not recommend using the
Conclusion from AskTom:
can safely leave sort_area_retained_size & sort_area_size as default shown below for 10g database using shared server, if set
sort_area_size integer 65536
ANSWERS from AskTom:
--In 10g database, we want to use PGA_AGGEGATE_TARGET, however below excepted from Oracle Document "Database Reference" confuses me, as we have shared server running with this database. Do I still need to set it explicitly?
"Note: Oracle does not recommend using the
SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility."Conclusion from AskTom:
can safely leave sort_area_retained_size & sort_area_size as default shown below for 10g database using shared server, if set
PGA_AGGREGATE_TARGET and workarea_size_policy='auto' sort_area_retained_size integer 0sort_area_size integer 65536
ANSWERS from AskTom:
if ( version = 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
if ( connection is dedicated server )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set, shared server uses sort area size
end if
else
they do need to be set
end if
elsif (version > 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set
end if
version 9i:
shared server -> sort_area_size is used regardless.
dedicated server AND workarea = auto -> pga_aggregate_target is used
dedicated server AND workarea = manual -> sort_area_size is used
version 10g
workarea=auto -> pga_aggregate_target
workarea= manual -> sort_area_size
you need not remove the sort_area_size, and since sessions could set workarea to manual - you might
just want to leave it in with YOUR default.
end if
Automatic PGA Memory Management
Starting with Oracle9i, Release 1, a new way to manage PGA memory was introduced that avoids using
the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE Parameters. It was introduced to
attempt to address a few issues:
* Ease of use: Much confusion surrounded how to set the proper *_AREA_SIZE parameters. There
was also much confusion over how those parameters actually worked and how memory was allocated.
* Manual allocation was a “one-size-fits-all” method: Typically as the number of users
running similar applications against a database went up, the amount of memory used for
sorting/hashing went up linearly as well. If 10 concurrent users with a sort area size of 1MB used
10MB of memory, 100 concurrent users would probably use 100MB, 1,000 would probably use 1000MB, and
so on. Unless the DBA was sitting at the console continually adjusting the sort/hash area size
settings, everyone would pretty much use the same values all day long. Consider the previous
example, where you saw for yourself how the physical I/O to temp decreased as the amount of RAM we
allowed ourselves to use went up. If you run that example for yourself, you will almost certainly
see a decrease in response time as the amount of RAM available for sorting increases. Manual
allocation fixes the amount of memory to be used for sorting at a more or less constant number,
regardless of how much memory is actually available. Automatic memory management allows us to use
the memory when it is available; it dynamically adjusts the amount of memory we use based on the
workload.
* Memory control: As a result of the previous point, it was hard, if not impossible, to keep
the Oracle instance inside a “box” memory-wise. You could not control the amount of memory the
instance was going to use, as you had no real control over the number of simultaneous sorts/hashes
taking place. It was far too easy to use more real memory (actual physical free memory) than was
available on the machine.
Enter automatic PGA memory management. Here, you first simply set up and size the SGA. The SGA is a
fixed-size piece of memory, so you can very accurately see how big it is, and that will be its
total size (until and if you change that). You then tell Oracle, “This is how much memory you
should try to limit yourself across all workareas—a new umbrella term for the sorting and hashing
areas you use.” Now, you could in theory take a machine with 2GB of physical memory and allocate
768MB of memory to the SGA and 768MB of memory to the PGA, leaving 512MB of memory for the OS and
other processes. I say “in theory” because it doesn’t work exactly that cleanly, but it’s close.
Before I discuss why that is true, we’ll take a look at how to set up automatic PGA memory
management and turn it on.
The process to set this up involves deciding on the proper values for two instance initialization
parameters, namely
* WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which will use the sort
area and hash area size parameters to control the amount of memory allocated, or AUTO, in which
case the amount of memory allocated will vary based on the current workload present in the
database. The default and recommended value is AUTO.
* PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should
allocate, in total, for all workareas used to sort/hash data. Its default value varies by version
and may be set by various tools such as the DBCA. In general, if you are using automatic PGA memory
management, you should explicitly set this parameter.
So, assuming that WORKAREA_SIZE_POLICY is set to AUTO, and PGA_AGGREGATE_TARGET has a nonzero
value, you will be using the new automatic PGA memory management. You can “turn it on" in your
session via the ALTER SESSION command or at the system level via the ALTER SESSION command.
Note Bear in mind the previously discussed caveat that in Oracle9i, shared server connections
will not use automatic memory management; rather, they will use the SORT_AREA_SIZE and
HASH_AREA_SIZE parameters to decide how much RAM to allocate for various operations. In Oracle 10g
and up, automatic PGA memory management is available to both connection types. It is important to
properly set the SORT_AREA_SIZE and HASH_AREA_SIZE parameters when using shared server connections
with Oracle9i.
So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the
same time not using more RAM than you want. Under manual memory management, this was virtually
impossible goal to achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort
operation that user would use up to 10MB for the sort workarea. If 100 users were doing the same,
they would use up to 1,000MB of memory. If you had 500MB of free memory, the single user performing
a sort by himself could have used much more memory, and the 100 users should have used much less.
That is what automatic PGA memory management was designed to do. Under a light workload, memory
usage could be maximized as the load increases on the system, and as more users perform sort or
hash operations, the amount of memory allocated to them would decrease—to obtain the goal of using
all available RAM, but not attempting to use more than physically exists.
Monday, April 28, 2008
35岁前应做好的十件事
35岁是青春的后期，35岁以后是收获的季节，如果你没有资格说这句话，你将会憎恨自己。所以在35岁以前，你最好把下面十件事做好。
第一，学会本行业所需要的一切知识并有所发展。已故零件大王布鲁丹在他35岁时，已经成为零件行业的领袖，并且组建了年收入达千万美元的海湾与西部工业公司。每个人在年轻时都可能有过彻夜不眠、刻苦攻读，这在20岁甚或30岁都没有问题，但到了35岁，就不应该再为学习基本技能而大伤脑筋了。35岁之前是一个人从事原始积累的阶段，35岁之后就应该薄发了。
第二，养成个人风格。在35岁以前，找出你所喜欢的，不论是衣着或是爱好，哪怕是与众不同的小习惯也好。20岁、30岁时你可以不断尝试、不断改变，但是到了35岁，你便要明确地建立个人风格。
第三，在感情生活方面平和安定。在攀登事业的高峰时，如果私人生活不愉快，陷入感情危机，对你会产生很大的干扰，甚至会逐渐令你对别的事物失去兴趣。那些在35岁之前私人生活已经平和安定的人，一般都比生活动荡不安的人有更大的机会获得成功。在35岁以后，你应该专注地看着你对事业的投资开始获利。
第四，明白自己的短处。承认有些事情你的确做不好，或者不愿做。在35岁之前，一定要投入你所喜爱、所擅长的那种工作。否则，35岁之后必然会有一段郁郁不乐的日子。而且，真正的成功可能因为活力的消退而丧失。
第五，知道自己的长处。你应该知道自己擅长什么，并且清楚你所喜欢做而又做得比别人好的事情。不管你目前担任什么样的角色，知道自己的长处对成功都很重要。
第六，储备辞职另谋生路的钱。在这个多变的职业世界里，你也许不会永远在一个地方工作，或者永远在一个位置上淋漓尽致地发挥自己，当你感到无法施展时，你很可能会想到辞职，如果你事先储蓄了足够的钱，你便有了一个安全的后盾。
第七，建立人际关系网。如果到了35岁你仍未建立起牢固的人际关系网，那你就有麻烦了。人际关系网不是一朝一夕就能建立起来的，它需要几年甚至十几年的培养。一个人在事业上、生活上的成功其实如同一个政党的成功，你要有许多人散布在适当的地方，你可以依赖他们，他们也可以依赖你。
第八，学会授权他人。许多人不肯或不能这样做，因此始终被钉在从属的职位上。授权他人是成功的一半，一个事无巨细，不能将工作授权别人的人，注定会遇到极大的障碍。到了35岁，你最好已成为这方面的专家。换言之，你懂得挑选合适的人并信任他们。
第九，学会在什么时候三缄其口。因说话不小心而自毁前程的人，比因为任何其他原因丧失成功的人都多。要学会保持沉默而且看起来机智———别人自然以为你知道的比实际还多。别讲别人的闲话，别谈论你自己的大计，守口如瓶所赢得的声誉，远比讲人闲话所带来的东西更加珍贵。
第十，对人要忠诚。如果你到了35岁仍未能建立起坚如磐石的忠诚信誉，这一缺点将会困扰你一生。35岁以前，忠诚只是投资；35岁以后，你会作为一个可以信赖的人收到忠诚的回报。”。
第一，学会本行业所需要的一切知识并有所发展。已故零件大王布鲁丹在他35岁时，已经成为零件行业的领袖，并且组建了年收入达千万美元的海湾与西部工业公司。每个人在年轻时都可能有过彻夜不眠、刻苦攻读，这在20岁甚或30岁都没有问题，但到了35岁，就不应该再为学习基本技能而大伤脑筋了。35岁之前是一个人从事原始积累的阶段，35岁之后就应该薄发了。
第二，养成个人风格。在35岁以前，找出你所喜欢的，不论是衣着或是爱好，哪怕是与众不同的小习惯也好。20岁、30岁时你可以不断尝试、不断改变，但是到了35岁，你便要明确地建立个人风格。
第三，在感情生活方面平和安定。在攀登事业的高峰时，如果私人生活不愉快，陷入感情危机，对你会产生很大的干扰，甚至会逐渐令你对别的事物失去兴趣。那些在35岁之前私人生活已经平和安定的人，一般都比生活动荡不安的人有更大的机会获得成功。在35岁以后，你应该专注地看着你对事业的投资开始获利。
第四，明白自己的短处。承认有些事情你的确做不好，或者不愿做。在35岁之前，一定要投入你所喜爱、所擅长的那种工作。否则，35岁之后必然会有一段郁郁不乐的日子。而且，真正的成功可能因为活力的消退而丧失。
第五，知道自己的长处。你应该知道自己擅长什么，并且清楚你所喜欢做而又做得比别人好的事情。不管你目前担任什么样的角色，知道自己的长处对成功都很重要。
第六，储备辞职另谋生路的钱。在这个多变的职业世界里，你也许不会永远在一个地方工作，或者永远在一个位置上淋漓尽致地发挥自己，当你感到无法施展时，你很可能会想到辞职，如果你事先储蓄了足够的钱，你便有了一个安全的后盾。
第七，建立人际关系网。如果到了35岁你仍未建立起牢固的人际关系网，那你就有麻烦了。人际关系网不是一朝一夕就能建立起来的，它需要几年甚至十几年的培养。一个人在事业上、生活上的成功其实如同一个政党的成功，你要有许多人散布在适当的地方，你可以依赖他们，他们也可以依赖你。
第八，学会授权他人。许多人不肯或不能这样做，因此始终被钉在从属的职位上。授权他人是成功的一半，一个事无巨细，不能将工作授权别人的人，注定会遇到极大的障碍。到了35岁，你最好已成为这方面的专家。换言之，你懂得挑选合适的人并信任他们。
第九，学会在什么时候三缄其口。因说话不小心而自毁前程的人，比因为任何其他原因丧失成功的人都多。要学会保持沉默而且看起来机智———别人自然以为你知道的比实际还多。别讲别人的闲话，别谈论你自己的大计，守口如瓶所赢得的声誉，远比讲人闲话所带来的东西更加珍贵。
第十，对人要忠诚。如果你到了35岁仍未能建立起坚如磐石的忠诚信誉，这一缺点将会困扰你一生。35岁以前，忠诚只是投资；35岁以后，你会作为一个可以信赖的人收到忠诚的回报。”。
Shared server health check
prompt ***************************************
prompt Dispatcher detail usage
prompt ***************************************
prompt ( if time busy >50, then change MTS_MAX_DISPATCHERS in init.ora)
select name, status, idle, busy, busy/(busy+idle)*100 "Time Busy Rate" from v$dispatcher;
--select network "Protocol",
-- to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') "%Busy"
--from v$dispatcher
--group by network
--order by to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') desc;
--Use the following statement to check for responses to user processes that are waiting in a queue to be sent to the user:
Prompt *************************************
Prompt Average wait time for response queue
Prompt *************************************
select Network Protocol,
Decode (Sum(Totalq), 0, 'No Responses',
Sum(Wait) / Sum(TotalQ) || ' hundredths of a second')
"Average Wait Time Per Response"
from V$Queue Q, V$Dispatcher D
where Q.Type = 'DISPATCHER'
and Q.Paddr = D.Paddr
group by Network;
--Use the following statement to check the requests from user processes that are waiting in a queue to be sent to the user:
Prompt *******************************************
Prompt All average wait time for common requests
Prompt *******************************************
select Decode (Sum(Totalq), 0, 'Number of Requests',
Sum(Wait) / Sum(TotalQ) || 'hundredths of a second')
"Average Wait Time Per Request"
from V$Queue
where Type = 'COMMON';
prompt Dispatcher detail usage
prompt ***************************************
prompt ( if time busy >50, then change MTS_MAX_DISPATCHERS in init.ora)
select name, status, idle, busy, busy/(busy+idle)*100 "Time Busy Rate" from v$dispatcher;
--select network "Protocol",
-- to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') "%Busy"
--from v$dispatcher
--group by network
--order by to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') desc;
--Use the following statement to check for responses to user processes that are waiting in a queue to be sent to the user:
Prompt *************************************
Prompt Average wait time for response queue
Prompt *************************************
select Network Protocol,
Decode (Sum(Totalq), 0, 'No Responses',
Sum(Wait) / Sum(TotalQ) || ' hundredths of a second')
"Average Wait Time Per Response"
from V$Queue Q, V$Dispatcher D
where Q.Type = 'DISPATCHER'
and Q.Paddr = D.Paddr
group by Network;
--Use the following statement to check the requests from user processes that are waiting in a queue to be sent to the user:
Prompt *******************************************
Prompt All average wait time for common requests
Prompt *******************************************
select Decode (Sum(Totalq), 0, 'Number of Requests',
Sum(Wait) / Sum(TotalQ) || 'hundredths of a second')
"Average Wait Time Per Request"
from V$Queue
where Type = 'COMMON';
Saturday, April 26, 2008
v$sqlarea,v$sql,v$sqltext这三个视图提供的sql语句有什么区别？
V$SQLAREA
V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
V$SQL
V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
V$SQLTEXT
This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.
v$sqltext
存储的是完整的SQL,SQL被分割
SQL> desc v$sqltext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4) ---------
HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql
COMMAND_TYPE NUMBER
PIECE NUMBER ---------- 分片之后的顺序编号
SQL_TEXT VARCHAR2(64) -------------- 注意长度
v$sqlarea --------- 存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息
SQL> desc v$sqlarea
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(25)
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
v$sql ---------- 存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息
SQL> desc v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ---------- 注意这个
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER -------------- 注意这里跟 outline 有关
CHILD_ADDRESS RAW(4)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(38)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
另外注意这个
QL> desc v$sql_plan
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段
OPERATION VARCHAR2(60)
OPTIONS VARCHAR2(60)
OBJECT_NODE VARCHAR2(20)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(70)
PARTITION_START VARCHAR2(10)
PARTITION_STOP VARCHAR2(10)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(40)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
实际上，看起来同样的一句SQL ，往往具有不同的执行计划
如果是不同的数据库用户，那么相应的涉及的 对象 可能都不一样，注意v$sql 中
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)
即使是相同的数据库用户，若 session 的优化模式、session 级的参数 等不一样，执行计划也能不同。所以即使相同的sql，也可能具有不同的执行计划！
v$sql join to v$sql_plan 就代表了具体的sql的执行计划,通过下面3个字段做连接
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
而v$SQLAREA 忽略了 执行计划 等差异，只是在形式上sql文本看起来一样！相当于做了个聚合，是多个不同执行计划的sql的聚合和累计信息
V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
V$SQL
V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
V$SQLTEXT
This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.
v$sqltext
存储的是完整的SQL,SQL被分割
SQL> desc v$sqltext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4) ---------
HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql
COMMAND_TYPE NUMBER
PIECE NUMBER ---------- 分片之后的顺序编号
SQL_TEXT VARCHAR2(64) -------------- 注意长度
v$sqlarea --------- 存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息
SQL> desc v$sqlarea
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(25)
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
v$sql ---------- 存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息
SQL> desc v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ---------- 注意这个
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER -------------- 注意这里跟 outline 有关
CHILD_ADDRESS RAW(4)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(38)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
另外注意这个
QL> desc v$sql_plan
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段
OPERATION VARCHAR2(60)
OPTIONS VARCHAR2(60)
OBJECT_NODE VARCHAR2(20)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(70)
PARTITION_START VARCHAR2(10)
PARTITION_STOP VARCHAR2(10)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(40)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
实际上，看起来同样的一句SQL ，往往具有不同的执行计划
如果是不同的数据库用户，那么相应的涉及的 对象 可能都不一样，注意v$sql 中
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)
即使是相同的数据库用户，若 session 的优化模式、session 级的参数 等不一样，执行计划也能不同。所以即使相同的sql，也可能具有不同的执行计划！
v$sql join to v$sql_plan 就代表了具体的sql的执行计划,通过下面3个字段做连接
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
而v$SQLAREA 忽略了 执行计划 等差异，只是在形式上sql文本看起来一样！相当于做了个聚合，是多个不同执行计划的sql的聚合和累计信息
补充：
1、查一下这些视图的定义你就能理解，它们的源都是一个。
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL';
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL_AREA';
2、实际上最模糊的是v$sql与v$sqlarea,区别与联系除biti说的还有：
a、v$sql_area相当于是按INST_ID, KGLNAOBJ, KGLHDPAR, KGLNAHSH, KGLNATIM, GLOBTS0,GLOBT19, KGLOBTS1, KGLOBT20,DECODE(KGLOBT33, 1, 'Y', 'N'),KGLHDCLT这些列的自v$sql的group by，也就是说v$sql的每一行表示的是每一个sql语句的一个versiion，而v$sqlarea存放的是相同语句不同version一个汇总。
b、 v$sql与v$sqlarea的源都是一个：X$KGLCURSOR
c、实际调优中建议使用v$sql,相对来说比v$sqlarea快，而且还不会产生share pool latch的争用。
3、因v$sql及v$sqlarea存放着统计信息在调优时使用居多，但其sql是不全的，如果想获得完整的sql就要用v$sqltext了。
1、查一下这些视图的定义你就能理解，它们的源都是一个。
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL';
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL_AREA';
2、实际上最模糊的是v$sql与v$sqlarea,区别与联系除biti说的还有：
a、v$sql_area相当于是按INST_ID, KGLNAOBJ, KGLHDPAR, KGLNAHSH, KGLNATIM, GLOBTS0,GLOBT19, KGLOBTS1, KGLOBT20,DECODE(KGLOBT33, 1, 'Y', 'N'),KGLHDCLT这些列的自v$sql的group by，也就是说v$sql的每一行表示的是每一个sql语句的一个versiion，而v$sqlarea存放的是相同语句不同version一个汇总。
b、 v$sql与v$sqlarea的源都是一个：X$KGLCURSOR
c、实际调优中建议使用v$sql,相对来说比v$sqlarea快，而且还不会产生share pool latch的争用。
3、因v$sql及v$sqlarea存放着统计信息在调优时使用居多，但其sql是不全的，如果想获得完整的sql就要用v$sqltext了。
ASH size
Sizing ASH
ASH is actually a first-in, first-out (FIFO) buffer in memory that collects statistics on current session
activity. These statistics are gathered by extracting samples from V$SESSION every second.
Because this kind of frequent gathering could quickly overwhelm the system, ASH continually
ages out old statistics to make room for new ones.
ASH resides in the System Global Area (SGA) and its size is fixed for the lifetime of the
instance. Its size is calculated using the following calculation:
The lesser of:
Total number of CPUs × 2MB of memory
5 percent of the Shared Pool size
So, on a 16-processor system, with a Shared Pool size of 500MB, the size of the ASH buffer
could be calculated as follows:
ASH desired size: 16 × 2MB = 32MB
5 percent of Shared Pool size: 500MB × 5% = 25MB
Final ASH size: 25MB
Using the Automatic Workload Repository (AWR) 265
Because the desired ASH size exceeded 5 percent of the Shared Pool size, Oracle will choose
the lesser size. There are, therefore, only two ways to increase the ASH buffer size:
Increase the number of CPUs.
Increase the Shared Pool size.
select * from v$sgastat where name like '%ASH%';
ASH is actually a first-in, first-out (FIFO) buffer in memory that collects statistics on current session
activity. These statistics are gathered by extracting samples from V$SESSION every second.
Because this kind of frequent gathering could quickly overwhelm the system, ASH continually
ages out old statistics to make room for new ones.
ASH resides in the System Global Area (SGA) and its size is fixed for the lifetime of the
instance. Its size is calculated using the following calculation:
The lesser of:
Total number of CPUs × 2MB of memory
5 percent of the Shared Pool size
So, on a 16-processor system, with a Shared Pool size of 500MB, the size of the ASH buffer
could be calculated as follows:
ASH desired size: 16 × 2MB = 32MB
5 percent of Shared Pool size: 500MB × 5% = 25MB
Final ASH size: 25MB
Using the Automatic Workload Repository (AWR) 265
Because the desired ASH size exceeded 5 percent of the Shared Pool size, Oracle will choose
the lesser size. There are, therefore, only two ways to increase the ASH buffer size:
Increase the number of CPUs.
Increase the Shared Pool size.
select * from v$sgastat where name like '%ASH%';
Friday, April 25, 2008
How To Use Automatic Shared Memory Management (ASMM) In Oracle10g
Pls refer to metalink doc. Note:295626.1
Keys things are:
1. To use the feature in 10g, we need to set sga_target value. The default value is 0 , which means disable ASMM.
2. Important dict tables
v$sga_dynamic_component
v$sga_dynamic_free_memory
v$sga_resize_ops
Wednesday, April 23, 2008
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK
Hit below error, symptoms:
1. cpu 100% or server almost hang.
2. can't connect /as sysdba
solution:
ps -ef |grep -i beq
and kill those accordingly
root cause:
so far don't know. why it is ora-4031?
Sat Apr 19 03:50:02 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_cjq0_15634.trc:
ORA-07445: exception encountered: core dump [kghpir()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFD0] [] []
Sat Apr 19 03:51:15 2008
Restarting dead background process CJQ0
CJQ0 started with pid=10, OS id=18756
Sat Apr 19 04:00:32 2008
Thread 1 advanced to log sequence 9558
Current log# 2 seq# 9558 mem# 0: /bkp003/oradata/CUST33B/redoCUST33B02a.log
Current log# 2 seq# 9558 mem# 1: /bkp003/oradata/CUST33B/redoCUST33B02b.log
Sat Apr 19 04:16:46 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_mmon_15636.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C22A2648], [], [], [], [], [], []
Sat Apr 19 04:17:13 2008
ORA-600 encountered when generating server alert SMG-3503
Sat Apr 19 04:42:18 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_j000_22062.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2604248], [], [], [], [], [], []
Sat Apr 19 05:23:14 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_s000_15642.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C37E10E0], [], [], [], [], [], []
Sat Apr 19 06:03:54 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_j000_29503.trc:
ORA-12012: error on auto execute of job 23935
ORA-04031: unable to allocate ORA-04031: unable to allocate 61912 bytes of shared memory ("shared pool","DBMS_STATS","PL/SQL MPCODE","BAMIMA: Bam Buffer")
bytes of shared memory ("","","","")
Sat Apr 19 07:21:52 2008
Thread 1 advanced to log sequence 9559
Current log# 3 seq# 9559 mem# 0: /bkp003/oradata/CUST33B/redoCUST33B03a.log
Current log# 3 seq# 9559 mem# 1: /bkp003/oradata/CUST33B/redoCUST33B03b.log
Sat Apr 19 07:51:32 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_mmon_15636.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C3D88198], [], [], [], [], [], []
Sat Apr 19 07:51:44 2008
ORA-600 encountered when generating server alert SMG-3503
Sat Apr 19 08:04:08 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_q000_15727.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2D63BD0], [], [], [], [], [], []
Sat Apr 19 08:04:37 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_q000_15727.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2D63BD0], [], [], [], [], [], []
Sat Apr 19 08:09:41 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_m000_13077.trc:
ORA-07445: exception encountered: core dump [kghpir()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFD0] [] []
Sat Apr 19 08:32:51 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 08:34:38 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 09:23:09 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 09:24:35 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 10:13:17 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 10:14:50 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 11:03:24 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 11:04:34 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 11:53:29 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 11:54:38 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 12:43:15 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 12:44:27 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 13:33:12 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 13:34:34 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sun Apr 20 13:05:58 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sun Apr 20 13:11:11 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=23
System State dumped to trace file /software/oracle/admin/CUST33B/udump/cust33b_ora_22232.trc
Mon Apr 21 11:38:25 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=27
dev07:B.11:CUST33B:/software/oracle/admin/CUST33B/bdump> ps -ef |grep CUST33B
oracle 15636 1 0 Apr 13 ? 64:47 ora_mmon_CUST33B
oracle 15620 1 0 Apr 13 ? 5:18 ora_dbw0_CUST33B
oracle 15640 1 255 Apr 13 ? 2627:20 ora_d000_CUST33B
oracle 13858 13857 0 Apr 20 ? 2:26 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 16786 16783 0 10:48:08 ? 0:47 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26564 26563 0 14:51:25 ? 1:36 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15614 1 0 Apr 13 ? 4:16 ora_psp0_CUST33B
oracle 127 1 0 13:00:36 ? 0:01 ora_q000_CUST33B
oracle 13246 1 0 Apr 19 ? 1:27 ora_q002_CUST33B
oracle 7012 16816 0 14:12:11 pts/2 0:00 grep CUST33B
oracle 18756 1 0 Apr 19 ? 54:59 ora_cjq0_CUST33B
oracle 15676 1 0 Apr 13 ? 2:08 ora_qmnc_CUST33B
oracle 3746 3742 0 Apr 20 ? 2:03 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 9931 1 255 Apr 20 ? 841:35 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15730 1 0 Apr 13 ? 7:29 ora_q001_CUST33B
oracle 15638 1 0 Apr 13 ? 20:15 ora_mmnl_CUST33B
oracle 15628 1 0 Apr 13 ? 19:33 ora_smon_CUST33B
oracle 22232 1 0 Apr 20 ? 2:01 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15630 1 0 Apr 13 ? 48:06 ora_reco_CUST33B
oracle 15624 1 0 Apr 13 ? 5:25 ora_lgwr_CUST33B
oracle 15618 1 0 Apr 13 ? 3:23 ora_mman_CUST33B
oracle 28226 1 0 15:04:15 ? 1:47 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15626 1 0 Apr 13 ? 10:31 ora_ckpt_CUST33B
oracle 7404 1 255 Apr 19 ? 2063:13 ora_j000_CUST33B
oracle 15612 1 255 Apr 13 ? 2045:52 ora_pmon_CUST33B
dev07:B.11:CUST33B:/software/oracle/admin/CUST33B/bdump> kill 3746 9931 22232 2822613858 16786 26564
1. cpu 100% or server almost hang.
2. can't connect /as sysdba
solution:
ps -ef |grep -i beq
and kill those accordingly
root cause:
so far don't know. why it is ora-4031?
Sat Apr 19 03:50:02 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_cjq0_15634.trc:
ORA-07445: exception encountered: core dump [kghpir()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFD0] [] []
Sat Apr 19 03:51:15 2008
Restarting dead background process CJQ0
CJQ0 started with pid=10, OS id=18756
Sat Apr 19 04:00:32 2008
Thread 1 advanced to log sequence 9558
Current log# 2 seq# 9558 mem# 0: /bkp003/oradata/CUST33B/redoCUST33B02a.log
Current log# 2 seq# 9558 mem# 1: /bkp003/oradata/CUST33B/redoCUST33B02b.log
Sat Apr 19 04:16:46 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_mmon_15636.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C22A2648], [], [], [], [], [], []
Sat Apr 19 04:17:13 2008
ORA-600 encountered when generating server alert SMG-3503
Sat Apr 19 04:42:18 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_j000_22062.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2604248], [], [], [], [], [], []
Sat Apr 19 05:23:14 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_s000_15642.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C37E10E0], [], [], [], [], [], []
Sat Apr 19 06:03:54 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_j000_29503.trc:
ORA-12012: error on auto execute of job 23935
ORA-04031: unable to allocate ORA-04031: unable to allocate 61912 bytes of shared memory ("shared pool","DBMS_STATS","PL/SQL MPCODE","BAMIMA: Bam Buffer")
bytes of shared memory ("","","","")
Sat Apr 19 07:21:52 2008
Thread 1 advanced to log sequence 9559
Current log# 3 seq# 9559 mem# 0: /bkp003/oradata/CUST33B/redoCUST33B03a.log
Current log# 3 seq# 9559 mem# 1: /bkp003/oradata/CUST33B/redoCUST33B03b.log
Sat Apr 19 07:51:32 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_mmon_15636.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C3D88198], [], [], [], [], [], []
Sat Apr 19 07:51:44 2008
ORA-600 encountered when generating server alert SMG-3503
Sat Apr 19 08:04:08 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_q000_15727.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2D63BD0], [], [], [], [], [], []
Sat Apr 19 08:04:37 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_q000_15727.trc:
ORA-00600: internal error code, arguments: [17148], [0xC0000004C2D63BD0], [], [], [], [], [], []
Sat Apr 19 08:09:41 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_m000_13077.trc:
ORA-07445: exception encountered: core dump [kghpir()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFD0] [] []
Sat Apr 19 08:32:51 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 08:34:38 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 09:23:09 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 09:24:35 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 10:13:17 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 10:14:50 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 11:03:24 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 11:04:34 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 11:53:29 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 11:54:38 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 12:43:15 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 12:44:27 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sat Apr 19 13:33:12 2008
System State dumped to trace file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc
Sat Apr 19 13:34:34 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sun Apr 20 13:05:58 2008
Errors in file /software/oracle/admin/CUST33B/bdump/cust33b_reco_15630.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-44203: timeout waiting for lock on cursor
Sun Apr 20 13:11:11 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=23
System State dumped to trace file /software/oracle/admin/CUST33B/udump/cust33b_ora_22232.trc
Mon Apr 21 11:38:25 2008
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=27
dev07:B.11:CUST33B:/software/oracle/admin/CUST33B/bdump> ps -ef |grep CUST33B
oracle 15636 1 0 Apr 13 ? 64:47 ora_mmon_CUST33B
oracle 15620 1 0 Apr 13 ? 5:18 ora_dbw0_CUST33B
oracle 15640 1 255 Apr 13 ? 2627:20 ora_d000_CUST33B
oracle 13858 13857 0 Apr 20 ? 2:26 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 16786 16783 0 10:48:08 ? 0:47 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26564 26563 0 14:51:25 ? 1:36 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15614 1 0 Apr 13 ? 4:16 ora_psp0_CUST33B
oracle 127 1 0 13:00:36 ? 0:01 ora_q000_CUST33B
oracle 13246 1 0 Apr 19 ? 1:27 ora_q002_CUST33B
oracle 7012 16816 0 14:12:11 pts/2 0:00 grep CUST33B
oracle 18756 1 0 Apr 19 ? 54:59 ora_cjq0_CUST33B
oracle 15676 1 0 Apr 13 ? 2:08 ora_qmnc_CUST33B
oracle 3746 3742 0 Apr 20 ? 2:03 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 9931 1 255 Apr 20 ? 841:35 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15730 1 0 Apr 13 ? 7:29 ora_q001_CUST33B
oracle 15638 1 0 Apr 13 ? 20:15 ora_mmnl_CUST33B
oracle 15628 1 0 Apr 13 ? 19:33 ora_smon_CUST33B
oracle 22232 1 0 Apr 20 ? 2:01 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15630 1 0 Apr 13 ? 48:06 ora_reco_CUST33B
oracle 15624 1 0 Apr 13 ? 5:25 ora_lgwr_CUST33B
oracle 15618 1 0 Apr 13 ? 3:23 ora_mman_CUST33B
oracle 28226 1 0 15:04:15 ? 1:47 oracleCUST33B (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 15626 1 0 Apr 13 ? 10:31 ora_ckpt_CUST33B
oracle 7404 1 255 Apr 19 ? 2063:13 ora_j000_CUST33B
oracle 15612 1 255 Apr 13 ? 2045:52 ora_pmon_CUST33B
dev07:B.11:CUST33B:/software/oracle/admin/CUST33B/bdump> kill 3746 9931 22232 2822613858 16786 26564
如何从根本上提高软件质量
如何从根本上提高软件质量
作者：唐俊 来源：希赛网
在软件开发过程中，有几条准则是已经被无数次验证的。
1、在项目发布后发现和修复Bug的成本是需求和设计阶段所需的一百倍！
2、80%可避免的重复劳动源自于20%的缺陷，其中两大主要来源包括草率的需求定制和象征性的案例
设计和开发。
3、大约80%的缺陷来自20%的模块，而约半数的模块是几乎没有缺陷。
4、90%的软件的停工期最多来自于10%的缺陷。
上面四条原则说明了两个问题，一是错误越早发现成本越低，而且大部分的错误都是在软件开发的前面阶段引入的。二是大部分的错误都集中在少数的模块。
测试作为最有效的“马后炮”，一直被认为最有效的保证软件质量的手段。果真那么有效果吗？首先得考虑一下这个问题：“为什么80%的缺陷会在20%的模块，而过半数的模块几乎没有缺陷呢？”。
缺陷集中出现有两种可能，一是大量出现缺陷的模块特别复杂，以至于软件设计者和程序员没有能力保证程序没有错误。二是编写这些模块的程序员比编写其 他模块的程序员水平要低，或者做事情要毛糙。第一种可能是可以避免的，如果模块太复杂就将其分解为若干更小的模块，直道划分的模块够简单为止，这也是模块 划分过程中应该要做的。核心技术应该由骨干人员进行技术攻关，保证其正确无误的实现。至今也没有听说过有程序员实现不了的软件，程序员、特别是优秀的软件 设计师的能力无需怀疑的。那么问题出现在编写程序的程序员的水平有高低，或者质量意识不够强。10个程序员中如果9个编写的程序都没有问题，另外1个人水 平欠缺就可能导致问题都出现在他编写模块中。
等到软件编码完成后，进行测试的时候发现了问题，这个时候再去改正，那么错误修正费用已经发生了。何不一开始就替换掉能力低下的程序员，或者干脆少了这两个程序员而延长项目开发时间来保证软件的质量呢？测试虽然能够发现问题，却不能节约成本。
将测试引入到需求分析阶段，将需求的问题，在需求分析阶段就找出来。这样就可以节约100倍修复开销，这样的只赚不赔的事情为什么不做呢？
软件质量靠的不仅是测试，而是软件企业对软件质量的关注程度，如果一开始就将质量放到一个比较高的位置，我想测试这种“马后炮”才能够更充分的发挥它的作用。
作者：唐俊 来源：希赛网
在软件开发过程中，有几条准则是已经被无数次验证的。
1、在项目发布后发现和修复Bug的成本是需求和设计阶段所需的一百倍！
2、80%可避免的重复劳动源自于20%的缺陷，其中两大主要来源包括草率的需求定制和象征性的案例
设计和开发。
3、大约80%的缺陷来自20%的模块，而约半数的模块是几乎没有缺陷。
4、90%的软件的停工期最多来自于10%的缺陷。
上面四条原则说明了两个问题，一是错误越早发现成本越低，而且大部分的错误都是在软件开发的前面阶段引入的。二是大部分的错误都集中在少数的模块。
测试作为最有效的“马后炮”，一直被认为最有效的保证软件质量的手段。果真那么有效果吗？首先得考虑一下这个问题：“为什么80%的缺陷会在20%的模块，而过半数的模块几乎没有缺陷呢？”。
缺陷集中出现有两种可能，一是大量出现缺陷的模块特别复杂，以至于软件设计者和程序员没有能力保证程序没有错误。二是编写这些模块的程序员比编写其 他模块的程序员水平要低，或者做事情要毛糙。第一种可能是可以避免的，如果模块太复杂就将其分解为若干更小的模块，直道划分的模块够简单为止，这也是模块 划分过程中应该要做的。核心技术应该由骨干人员进行技术攻关，保证其正确无误的实现。至今也没有听说过有程序员实现不了的软件，程序员、特别是优秀的软件 设计师的能力无需怀疑的。那么问题出现在编写程序的程序员的水平有高低，或者质量意识不够强。10个程序员中如果9个编写的程序都没有问题，另外1个人水 平欠缺就可能导致问题都出现在他编写模块中。
等到软件编码完成后，进行测试的时候发现了问题，这个时候再去改正，那么错误修正费用已经发生了。何不一开始就替换掉能力低下的程序员，或者干脆少了这两个程序员而延长项目开发时间来保证软件的质量呢？测试虽然能够发现问题，却不能节约成本。
将测试引入到需求分析阶段，将需求的问题，在需求分析阶段就找出来。这样就可以节约100倍修复开销，这样的只赚不赔的事情为什么不做呢？
软件质量靠的不仅是测试，而是软件企业对软件质量的关注程度，如果一开始就将质量放到一个比较高的位置，我想测试这种“马后炮”才能够更充分的发挥它的作用。
如何学习ORACLE优化
反正有空，说一下我能想到的，很多方面我的了解都很浅薄，甚至只有一个不甚清楚的概念，仅供参考。
最基本的还是要了解oracle的工作方式，oracle的架构，存储的，内存的，进程的，dedicate server/shared server, redo和undo，latch和lock。。。。。。no knowledge, no tune.
了解常见的调优思想，responese time=wait time+service time是最基本的思想，基于ratio的思想虽然总是被批驳，但有时候值得参考。对于wait time你要了解什么是wait event，了解常见的wait event。idle的event，non-idle的event，到底先定位哪个问题？idle的event是不是真的就可以忽略? 都是需要商榷的。对于service time你要关心CPU的使用，你的系统花费了多少CPU，都用在哪里？怎么获取这些信息？你是否能够v$sysstat和v$sesstat找到你需要 的信息？
了解常用的调优工具，explain plan, sql trace, 10046 event, 10053 event, other events，tkprof。。。。。。还有最常用的statspack，给你一个statspack report，你能挖掘多少信息？了解常见的数据字典并能够从中迅速获取信息。v$latch, v$lock, v$session, v$session_longops, v$session_wait, v$sysstat.........
熟悉sql优化，这恐怕是个无止境的topic，总之尽力而为吧。。。不停的学习体会，了解各种Hint，一点一点的了解神秘的CBO optimier，了解各种相关的初始化参数，了解典型的SQL计划，nested loop, hash join, merge join, inlist, index range scan, fast full index scan, full table scan......
对sql的优化当然也包括所谓的physical design, 建什么样的索引，什么样的表，了解heap table, IOT, cluster table, btree index, bitmap index，分区表/索引，物化视图......各有什么特点，优点，缺点。
要有大局观，什么时候从session级别着手，什么时候从系统级别着手，什么时候能够斩钉截铁的说：“这不是数据库的问题！请检查硬件/网络/应用。。。”
要有前瞻性，不是一定要等到问题发生的时候才去研究问题，如果用户的并发数翻一倍，系统的压力会增加多少个precent？如果系统的CPU使用率从30%涨到60%，性能的下降将是线性的还是指数性的？下降多少个precent？queuing theory是什么？
最后怎么把所有的这些知识融会贯通？
当然有时候也可以唯心一把，祈祷一下你会获得更多的奇思妙想，更多的灵感。不过有时候你可能会发现，当你绞尽脑汁，百思不得其解，即使在睡梦中也难以释怀之时，灵感却在第二天的清晨不期而至
最基本的还是要了解oracle的工作方式，oracle的架构，存储的，内存的，进程的，dedicate server/shared server, redo和undo，latch和lock。。。。。。no knowledge, no tune.
了解常见的调优思想，responese time=wait time+service time是最基本的思想，基于ratio的思想虽然总是被批驳，但有时候值得参考。对于wait time你要了解什么是wait event，了解常见的wait event。idle的event，non-idle的event，到底先定位哪个问题？idle的event是不是真的就可以忽略? 都是需要商榷的。对于service time你要关心CPU的使用，你的系统花费了多少CPU，都用在哪里？怎么获取这些信息？你是否能够v$sysstat和v$sesstat找到你需要 的信息？
了解常用的调优工具，explain plan, sql trace, 10046 event, 10053 event, other events，tkprof。。。。。。还有最常用的statspack，给你一个statspack report，你能挖掘多少信息？了解常见的数据字典并能够从中迅速获取信息。v$latch, v$lock, v$session, v$session_longops, v$session_wait, v$sysstat.........
熟悉sql优化，这恐怕是个无止境的topic，总之尽力而为吧。。。不停的学习体会，了解各种Hint，一点一点的了解神秘的CBO optimier，了解各种相关的初始化参数，了解典型的SQL计划，nested loop, hash join, merge join, inlist, index range scan, fast full index scan, full table scan......
对sql的优化当然也包括所谓的physical design, 建什么样的索引，什么样的表，了解heap table, IOT, cluster table, btree index, bitmap index，分区表/索引，物化视图......各有什么特点，优点，缺点。
要有大局观，什么时候从session级别着手，什么时候从系统级别着手，什么时候能够斩钉截铁的说：“这不是数据库的问题！请检查硬件/网络/应用。。。”
要有前瞻性，不是一定要等到问题发生的时候才去研究问题，如果用户的并发数翻一倍，系统的压力会增加多少个precent？如果系统的CPU使用率从30%涨到60%，性能的下降将是线性的还是指数性的？下降多少个precent？queuing theory是什么？
最后怎么把所有的这些知识融会贯通？
当然有时候也可以唯心一把，祈祷一下你会获得更多的奇思妙想，更多的灵感。不过有时候你可能会发现，当你绞尽脑汁，百思不得其解，即使在睡梦中也难以释怀之时，灵感却在第二天的清晨不期而至
Sunday, April 20, 2008
10g Time Model
Time model statistics show the amount of CPU time that has been required to complete each type of database processing work. Examples include sql execute elapsed time, parse time elapsed and PL/SQL execution elapsed time statistics.
The most important time model statistic is DB time, which represents the total time spent by Oracle to process all database calls. In fact, it describes the total database workload. DB time is calculated by aggregating the CPU and all non-idle wait times for all sessions in the database after the last startup.
Because DB_TIME is an aggregate value gathered for all non-idle sessions, the total time will nearly always exceed the total elapsed time since instance startup.
For example, an instance that has been up for 10 hours may have had 60 sessions that were active for 30 minutes each. These would show a total time of 60 × 30 minutes, or 30 hours.
Oracle Database 10g introduces time models for identifying the time spent in various places. The overall system time spent is recorded in the viewV$SYS_TIME_MODEL. Here is the query and its output：
SQL> select * from v$sys_time_model;
This view shows the overall system times as well; however, you may be interested in a more granular view: the session level times. The timing stats are captured at the session level as well, as shown in the viewV$SESS_TIME_MODEL, where all the stats of the current connected sessions, both active and inactive, are visible. The additional column SID specifies the SID of the sessions for which the stats are shown：
SQL> select * from v$sess_time_model where sid=335;
The challenge ahead is how to drill down the interval to same as snapshot's . ! ? This is especially good to certain critical period while cyclical critical job runs.
The most important time model statistic is DB time, which represents the total time spent by Oracle to process all database calls. In fact, it describes the total database workload. DB time is calculated by aggregating the CPU and all non-idle wait times for all sessions in the database after the last startup.
Because DB_TIME is an aggregate value gathered for all non-idle sessions, the total time will nearly always exceed the total elapsed time since instance startup.
For example, an instance that has been up for 10 hours may have had 60 sessions that were active for 30 minutes each. These would show a total time of 60 × 30 minutes, or 30 hours.
Oracle Database 10g introduces time models for identifying the time spent in various places. The overall system time spent is recorded in the viewV$SYS_TIME_MODEL. Here is the query and its output：
SQL> select * from v$sys_time_model;
This view shows the overall system times as well; however, you may be interested in a more granular view: the session level times. The timing stats are captured at the session level as well, as shown in the viewV$SESS_TIME_MODEL, where all the stats of the current connected sessions, both active and inactive, are visible. The additional column SID specifies the SID of the sessions for which the stats are shown：
SQL> select * from v$sess_time_model where sid=335;
The challenge ahead is how to drill down the interval to same as snapshot's . ! ? This is especially good to certain critical period while cyclical critical job runs.
Saturday, April 19, 2008
企业如何选择和安排DBA
在每个企业的发展过程中，几乎都需要设立一个DBA的职位。问题是应该什么时候招聘公司的DBA，以及该如何选择合适的DBA。
如果你是一家中小型企业的老板，那么当企业发展到一定阶段时，有个问题就会摆在你面前：是否需要一个数据库管理员（DBA）？（对于这个问题，大型企业一般都会有肯定的回答。）
之所以会出现这个问题，主要有几种情况。比如，你的业务软件供应商建议你在使用他们的软件时，最好配备一个专业的DBA；或者随着业务的发展，你的数据库 已经大到让那些普通员工无法管理的地步了，由于普通的员工都没有真正的数据库管理经验，因此这时候你也应该考虑聘用一个专业的DBA。
另外，也许你是遇到了一些麻烦，才开始打算聘用一个DBA的，比如你的数据库被损坏了，而现有的员工谁也没有能力让它重新运转起来；或者你的技术人员可以 处理一些DBA的工作，但是他同时还要负责网络维护、系统管理、客户支持以及开发新应用等工作，这么多工作一个人很难应付。
由于以上原因，你所考虑的不再是“是否”需要一个DBA了，而是“何时”聘用一个专职的DBA来管理你的数据库。也就是说，现在的问题不是该不该聘用，而是聘用什么样的DBA以及聘用多少个DBA。
雇用一个DBA
培训自己的员工成为DBA
联系一个提供DBA服务的网站
支付一定费用，让自己企业所采用的应用程序的厂家提供相应的数据库支持
支付费用采用远程DBA 服
每一种选择都有自身的优缺点。另外，这五种选择可以适应企业发展的不同阶段的需求。随着需求的改变，现在看起来比较合适的选择，也许过一段时间就会变得不合适，因此一定要结合你的企业现有的状态来灵活确定。
关于聘用一个DBA
DBA和其他技术人员一样，也分很多种，比如没有什么经验的新手、稍有经验的、经验比较丰富的、高手，甚至是高手中的高手。当然，我们都会想到，雇用等级 越高的DBA，你需要支付的薪水也就越高。另外，由于你雇用的是正式员工，因此各种税务以及福利待遇等，都不能缺少。
雇用DBA的优点在于，他们是全职的，可以随时辅助你企业中的新项目，建立新的IT环境，他们会掌握你的业务情况，并壮大企业IT团队的力量。一般来说， 这个选择是所有选择中最昂贵的，它更适合规模较大或者变化较快的业务环境，或者是当你需要一个相对长期的技术团队时。
关于培养自己的DBA
将公司现有的技术人员培养成具有专业水平的DBA，不论是对于员工本人还是对于企业来说，都是个相当不错的方案。不过对于当前企业面临的急需解决的困难来 说，这个方案无法立刻见效。可以说这是为企业的未来进行投资的一种方案。这种投资方案也许非常有效，但同时它也具有很大的风险。当你的技术人员水平不断提 高，有能力成为一个出色的DBA时，他也许会对现有的工资和福利待遇感到不满，也许会有跳槽的打算。另外，如果没有一个水平更高的DBA在公司里，就很难 对其他员工实施有效的培训，而公司也必须忍耐员工的自学速度以及学习中所犯的错误。
现场DBA服务
除了雇用DBA或者培养自己的DBA外，你也可以联系一些提供现场DBA服务的公司，一般只要一个电话，他们都会在一小时内赶到帮助你解决棘手的困难。这 个方案的优势在于，DBA服务公司的服务都比较灵活，你可以在需要时才要求他们上门服务，而且可以要求短期的持续技术支持，比如在一个项目开始时让他们帮 助你完成基本数据库架构的建设。一般来说，从短期上来看，这要比雇佣一个全职的DBA廉价多了，但是如果长期需要DBA，这个方案就不太合适了。对于大多 数DBA服务公司的技术人员来说，在来到你的公司后，都会有一个了解你公司环境的过程，而你公司的一些特殊细节，他们可能并不熟悉，从未接触过。
应用产品厂商提供的DBA服务
当企业购买了一套基于数据库后台的复杂的应用程序后，厂商会提供DBA服务作为软件的配套服务，这种DBA服务也许是远程的，也许是上门服务，也许是二者结合。
这类服务一般都限定于只针对应用软件所使用的数据库后台。假如你的公司采用的是Microsoft SQL Server数据库管理系统，但新购进的一套软件是基于Oracle数据库的，没有员工熟悉Oracle系统，此时这种方案就非常合适了。不过从每小时的 服务成本来看，这种方案是很昂贵的，并且受到的限制也很多。
远程DBA 服务
最后一种方案是选择远程DBA服务。在这种方案中，你公司之外的数据库管理员会通过网络连接到你的数据库上进行管理。这类厂商所提供的服务范围很广，一般来说包含以下几方面：
针对你的数据库提供24X7的一般性维护
数据库调整
出现问题时向公司的指定人员报警
备份/恢复计划和服务
灾难恢复计划和服务
通过一系列工具监视数据库的每日运行状况
每周及每月工作状态报告
数据库升级咨询和具体实施
为企业指派全职的DBA
远程DBA服务的优势在于它非常适合于那些数据库环境相对稳定的企业。由于这类服务可以完全处理数据库的日常维护和备份等工作，因此你可以让公司的技术员工更专心的致力于其它新的开发项目或者更关键的问题。
远程DBA服务的成本要低于应用程序厂商提供的DBA服务或现场DBA服务。大部分远程DBA服务商都采用按月支付费用的方式，少数则采用按小时计费的方 式。不论何种方式，其成本都非常具有竞争力，因此很值得考虑。另外，由于这是一种服务，因此维护你的数据库的技术人员并不是固定的，这样就会有多个DBA 熟悉你的数据库环境，当其中一个DBA离开了远程DBA服务公司时，不至于对你的数据库维护工作产生影响。
当然，有些事情你也许要记住。首先，远程DBA服务需要依靠你的网络和硬件来支持。如果远程DBA服务的工作人员连接不上你的网络或数据库环境，他就无法帮助你管理数据库。另外，如果你的数据库采用了非常独特的技术或设备，那么远程DBA服务可能也会无能为力。
第二，如果你的数据库环境变化比较快，并且经常需要添加新的组件，那么联系一个现场DBA服务要比通过电话进行的DBA服务有效的多。
正确选择
Managing Volatile Object Statistics
Volatile objects are objects that can drastically change in size over the
course of a day. For example, tables that are the target of a bulk-load operation (where the
number of rows increases by 10 percent or more) would be considered volatile. Also, tables
that are truncated or dropped, and then rebuilt, would also be considered volatile. Volatile
objects run the risk of having no statistics (if they were dropped and rebuilt) or having inaccurate
statistics.
As part of Oracle’s query optimization, any table with no statistics will have them generated dynamically via the dynamic sampling feature. This “just-in-time” statistics generation ensures that no query will be executed without statistics. The parameter OPTIMIZER_DYNAMIC_SAMPLING needs to be set to a value of 2 (the default) or higher to enable this feature.
SQL> select LAST_ANALYZED,avg_row_len from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN
--------- -----------
14-APR-08 3
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
14-APR-08 3 1
--To set statistics for a table to NULL, delete and lock the statistics as shown:
SQL> exec dbms_stats.delete_table_stats('HR','T1');
PL/SQL procedure successfully completed.
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
--The second option is to set statistics to values that are typical for the table and lock them. To
--achieve this, gather statistics when the table is at a typical size. When complete, lock the table’s
--statistics, as shown in the preceding example.
SQL> exec dbms_stats.lock_table_stats('HR','T1');
PL/SQL procedure successfully completed.
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
SQL> insert into hr.t1 values(100);
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table hr.t1 compute statistics;
analyze table hr.t1 compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL> exec dbms_stats.unlock_table_stats('HR','T1');
PL/SQL procedure successfully completed.
SQL> analyze table hr.t1 compute statistics;
Table analyzed.
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
18-APR-08 6 2
course of a day. For example, tables that are the target of a bulk-load operation (where the
number of rows increases by 10 percent or more) would be considered volatile. Also, tables
that are truncated or dropped, and then rebuilt, would also be considered volatile. Volatile
objects run the risk of having no statistics (if they were dropped and rebuilt) or having inaccurate
statistics.
As part of Oracle’s query optimization, any table with no statistics will have them generated dynamically via the dynamic sampling feature. This “just-in-time” statistics generation ensures that no query will be executed without statistics. The parameter OPTIMIZER_DYNAMIC_SAMPLING needs to be set to a value of 2 (the default) or higher to enable this feature.
SQL> select LAST_ANALYZED,avg_row_len from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN
--------- -----------
14-APR-08 3
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
14-APR-08 3 1
--To set statistics for a table to NULL, delete and lock the statistics as shown:
SQL> exec dbms_stats.delete_table_stats('HR','T1');
PL/SQL procedure successfully completed.
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
--The second option is to set statistics to values that are typical for the table and lock them. To
--achieve this, gather statistics when the table is at a typical size. When complete, lock the table’s
--statistics, as shown in the preceding example.
SQL> exec dbms_stats.lock_table_stats('HR','T1');
PL/SQL procedure successfully completed.
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
SQL> insert into hr.t1 values(100);
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table hr.t1 compute statistics;
analyze table hr.t1 compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL> exec dbms_stats.unlock_table_stats('HR','T1');
PL/SQL procedure successfully completed.
SQL> analyze table hr.t1 compute statistics;
Table analyzed.
SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';
LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
18-APR-08 6 2
Friday, April 18, 2008
browser crashed while invoke Java applet
Today I can't access all my internet bankings. all browsers (firefox, IE, safari) crashed when I access banking.
Tried on other computer, it works. The problem must be within my laptop.
What was the change ?
-- Upgraded OpenOffiice from 2.3 to 2.4 ?
-- firefox upgraded to 2.0.0.13 to 2.0.0.14 ?
-- any stubbed file in temporary directory ?
-- Because of spyware software ?
After one day's hard thinking. I think it must related to JAVA.
Checked the folder, oh my godness on 16-Apr it is automatically update from jre1.6.0_03 to jre1.6.0_04.
Suspected jre1.6.0_04 has problem. Tried to check any update there, yes there is jre1.6.0_05 ready for update, but as of this moment, has problem with the upgrade (some error related to can't find file for copy)
Rollback to jre1.6.0_03 (uninstall jre1.6.0_04) , everything works fine now
Cheers!
Something learned, don't upgrade to latest version without notification or eager to do so. Let it stable first !!!
--this morning find SUN gave the solution of offline installation.
http://java.com/en/download/help/index.xml
Tried on other computer, it works. The problem must be within my laptop.
What was the change ?
-- Upgraded OpenOffiice from 2.3 to 2.4 ?
-- firefox upgraded to 2.0.0.13 to 2.0.0.14 ?
-- any stubbed file in temporary directory ?
-- Because of spyware software ?
After one day's hard thinking. I think it must related to JAVA.
Checked the folder, oh my godness on 16-Apr it is automatically update from jre1.6.0_03 to jre1.6.0_04.
Suspected jre1.6.0_04 has problem. Tried to check any update there, yes there is jre1.6.0_05 ready for update, but as of this moment, has problem with the upgrade (some error related to can't find file for copy)
Rollback to jre1.6.0_03 (uninstall jre1.6.0_04) , everything works fine now
Cheers!
Something learned, don't upgrade to latest version without notification or eager to do so. Let it stable first !!!
--this morning find SUN gave the solution of offline installation.
http://java.com/en/download/help/index.xml
ORA-00600: internal error code, arguments: [729], [12256], [space leak], [], [], [], [], []
DESCRIPTION:
A space leak has been detected in the User Global Area (UGA).
There is NO data corruption as a result of this error.
It is an internal memory housekeeping problem.
ARGUMENTS:
Arg [a] This is the number of bytes leaked
FUNCTIONALITY:
MEMORY COMPONENT
IMPACT:
PROCESS FAILURE - But only during logoff so impact is minimal.
NON CORRUPTIVE - No underlying data corruption.
SUGGESTIONS:
Event 10262 can be set to safely ignore small memory leaks.
Set the following in init.ora for example to disable space
leaks less than 4000 bytes:
event = "10262 trace name context forever, level 4000"
and stop and restart the database.
Repeated errors or large memory leaks can be diagnosed further by
sending the alert.log and trace files to Oracle support.
ORA-00600[729] can be considered as an informational message rather than an error message.Oracle detected a space leak and it is informing
the same. When a user session connects certian amount of memory is allocated to
it. When session logs off this memroy has to released. If Orcle find a differenc
e between the allocated memory and released memory that difference will be repo
rted as a leak. The leaked space will be given back to OS>
In this case space leak is of 28 bytes which is negligible.
However you can set the event 10262 to stop this error.
In init.ora -> event ="10262 trace name context forever, level 90000"
In spfile.ora -> alter system set event="10262 trace name context forever,
level 90000" scope=spfile;
Please restart the instance once you set this parameter.
A space leak has been detected in the User Global Area (UGA).
There is NO data corruption as a result of this error.
It is an internal memory housekeeping problem.
ARGUMENTS:
Arg [a] This is the number of bytes leaked
FUNCTIONALITY:
MEMORY COMPONENT
IMPACT:
PROCESS FAILURE - But only during logoff so impact is minimal.
NON CORRUPTIVE - No underlying data corruption.
SUGGESTIONS:
Event 10262 can be set to safely ignore small memory leaks.
Set the following in init.ora for example to disable space
leaks less than 4000 bytes:
event = "10262 trace name context forever, level 4000"
and stop and restart the database.
Repeated errors or large memory leaks can be diagnosed further by
sending the alert.log and trace files to Oracle support.
ORA-00600[729] can be considered as an informational message rather than an error message.Oracle detected a space leak and it is informing
the same. When a user session connects certian amount of memory is allocated to
it. When session logs off this memroy has to released. If Orcle find a differenc
e between the allocated memory and released memory that difference will be repo
rted as a leak. The leaked space will be given back to OS>
In this case space leak is of 28 bytes which is negligible.
However you can set the event 10262 to stop this error.
In init.ora -> event ="10262 trace name context forever, level 90000"
In spfile.ora -> alter system set event="10262 trace name context forever,
level 90000" scope=spfile;
Please restart the instance once you set this parameter.
lost domain controller leads to SQL server 26-Error
[THE PROBLEM
]TITLE: Connect to Server
------------------------------
Cannot connect to m1dwdev\dw_dev.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
Many are discussed in http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
but none is related to domain controller.
The error message in event viewer is:
Event Type: Error
Event Source: NETLOGON
Event Category: None
Event ID: 5719
Date: 4/18/2008
Time: 4:47:37 AM
User: N/A
Computer: M1DWDEV
Description:
No Windows NT or Windows 2000 Domain Controller is available for domain M1. The following error occurred:
There are currently no logon servers available to service the logon request.
Data:
0000: 5e 00 00 c0 ^..à
[THE SOLUTION]
Disable/Enable network. Problem solved.
]TITLE: Connect to Server
------------------------------
Cannot connect to m1dwdev\dw_dev.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
Many are discussed in http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
but none is related to domain controller.
The error message in event viewer is:
Event Type: Error
Event Source: NETLOGON
Event Category: None
Event ID: 5719
Date: 4/18/2008
Time: 4:47:37 AM
User: N/A
Computer: M1DWDEV
Description:
No Windows NT or Windows 2000 Domain Controller is available for domain M1. The following error occurred:
There are currently no logon servers available to service the logon request.
Data:
0000: 5e 00 00 c0 ^..à
[THE SOLUTION]
Disable/Enable network. Problem solved.
Thursday, April 17, 2008
How to find the SQL use massive shared pool
Live case learned today.
One user query used up to 740Mb shared pool. Surprising ? ! You can guess the impact.
Of course I am suspecting we are hitting a bug. The only things to note is that there about 260 values inside IN clause.
--Finding statement/s which use lots of shared pool memory:
SELECT substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > 10485760 ;
--then join with v$session to find out the user id, program
set pagesize 24
set newpage 1 set linesize 125 column sql_text format a100 column user_name format a12 select u.sid, serial# substr(u.username,1,12) user_name, s.sql_text from v$sql s, v$session u where s.hash_value = u.sql_hash_value and sql_text like '%%' ;
--terminate the DB session
alter system kill session ',';
--although the status in v$session may be marked as KILLED
-- Monitor the shared pool used by the session may still keep growing , as the OS process is still alive
--join with v$process to get OS pid
--use kill -6 to kill the process gracefully, even it is a shared server process. This may take up to 15 minutes to finish, as process rollback in the background. The CPU could 100% for this process. Worth and safe to wait.
-- after this plan downtime to restart database as soon as possible , since we killed a SHARED SERVER. Otherwise may still face ora-00600 and ora-00745
One user query used up to 740Mb shared pool. Surprising ? ! You can guess the impact.
Of course I am suspecting we are hitting a bug. The only things to note is that there about 260 values inside IN clause.
--Finding statement/s which use lots of shared pool memory:
SELECT substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > 10485760 ;
--then join with v$session to find out the user id, program
set pagesize 24
set newpage 1 set linesize 125 column sql_text format a100 column user_name format a12 select u.sid, serial# substr(u.username,1,12) user_name, s.sql_text from v$sql s, v$session u where s.hash_value = u.sql_hash_value and sql_text like '%
--terminate the DB session
alter system kill session '
--although the status in v$session may be marked as KILLED
-- Monitor the shared pool used by the session may still keep growing , as the OS process is still alive
--join with v$process to get OS pid
--use kill -6 to kill the process gracefully, even it is a shared server process. This may take up to 15 minutes to finish, as process rollback in the background. The CPU could 100% for this process. Worth and safe to wait.
-- after this plan downtime to restart database as soon as possible , since we killed a SHARED SERVER. Otherwise may still face ora-00600 and ora-00745
千万别把自己当人才
| 4月，正是经理人入职的高峰期与试用期。本文将献给那些正在入职和入职不久的空降经理人。 这个时期的经理人能否在企业生存下来，干出成绩，除了主观的努力、能力、经验外，还有一个重要因素，那就是心态——如果总把自己当人才，认为自己是被请来的，结果往往都是试用期不过，就卷铺盖走人。 英年经理人入门折戟 下面出场的，是我见过的最把自己当人才的经理人。结果，三个月试用期没过就被降职减薪，五个月后就被辞走人了。 此公，富有才情，年方30，以40万年薪加盟大M集团，任职某中心副总经理。行前找我来进行入职咨询，开口就是：“您帮我分析一下，此次大M集团诚邀我 加盟，是让我冲锋陷阵，还是革故鼎新，抑或是力挽狂澜？”我一听，就知道心态不对，太把自己当人才了！若不及时改变心态，根据我的经验，百分之百要出问 题。于是我答道：“你说的这三点都不是，无论大M集团邀请您的初衷是什么，作为个人，首先是抱着学习的心态。这么大一个企业集团，能走到今天是无数人的教 训、经验和智慧的结晶，任何个人的能量都很难与之匹敌。作为个人，加盟企业应该首先是学习这些已经积累下来的经验智慧。只有在学习的基础上，才有可能适 应、生存和发展，否则是自找麻烦。当然，这也不是说让你缩手缩脚，像个大学毕业生一样。” 听毕，这位经理人似乎认同了我的观点，至少是表面上，并拿出小本记了不少我和他交流的内容。我心想：又麻烦了，太书生了！这位年轻人从我这里离开时，变 得很谦虚，甚至有些谨慎，一反一小时前见我时的傲然和自信……可能是我们的沟通在起作用？我心想这也麻烦：短暂的个把小时沟通，就有如此大的改变，我担心 他在入职后把握不好度，更何况他刚刚30岁的年龄，正是忽左忽右、忽冷忽热的职场布朗运动期。而且我还担心他承受不了这40万年薪的压力…… 事实上，这位年轻的经理人入职后的表现正是忽冷忽热、忽左忽右，心态始终把握不好。他很想表现好，可要么过了，言行盛气凌人；要么不够，凡事都小心翼 翼。结果，半年不到灰溜溜走人。假若当初没有到我这里咨询，会怎样？估计个把月就被撵走了。试想，偌大的企业集团，差不多都标准化和流程化了，怎么会让一 个不着调的狂人在高管岗位上现眼呢？ 如果说30岁还幼稚，可即使40岁以上的经理人，也会犯这类错误。尤其刚从知名外企跳槽空降到民企的，最容易犯这种错误。比如K林先生。 此公40岁时从某知名外企中国区副总经理，跳槽到规模算比较大的民企。两年时间换了三家公司，其中还休息了将近半年，平均在每家企业也就半年。 他后来看到我的文章后，就找到我做职业咨询。他见我问的第一个问题就是：“每次老板都是把我当人才诚邀加盟，当人物似地优待，什么要求都答应我，可进去以后就满不那回事，处处挚肘，根本施展不开，最后我不得不离开。您说这是什么原因？” 老板视“才”如宝的五步棋 我和K林在沟通交流中，发现他在三家民企工作时有一个共同的特点，就是老板都太把K林当人才了。 第一步，进去前老板真把K林当人才。老板姿态很低，虚心求教，而且无论是行业前景，还是职务、薪酬、工作条件都非常有吸引力，再加上老板的诚心和热情，K林先生终于忍不住欣然地冲了进去。 第二步，老板郑重宣布公司引进了高级管理人才，担任××高管职务。在老板的心目中，K林就像熊猫宝贝似的。 第三步，K林提的很多工作上的要求，老板也都答应。 第四步，很多场合，讨论问题老板总是当着大家的面问K林意见，K林也热衷于展示自己，证明自己的能力和老板的器重。 第五步，K林说怎么办就怎么办。某些情况下更进一步讲：他的意见就是我的意见。 上面的老板五步棋，K林打心眼儿里高兴。其实正是老板的这五步棋害了K林们。 经理人自比管仲六连步 不仅老板们把K林们当作人才，就连K林们自己也把自己当人才。从另外一角度讲，之所以老板把K林们当人才看，是因为K林们自己把自己当人才看，在老板面前吹嘘了一大通，最后老板发现上当了，于是又想千方设百计把K林们逼走。 下面我们就演绎一下K林们把自己当人才的过程。 第一步，进来前以师自居。K林们有时尽管嘴上谦虚，其心里并不谦虚，认为老板请自己来，就是解决问题的。进来前老板向自己逐一请教了许多解决问题的办 法，自己真的以为进来后就应按与老板沟通好的来解决问题，并向老板申请了许多特权，把自己当成了企业的救世主、老板的老师、治愈其他员工的医生。 第二步，入职后高傲，蔑视一切。由于K林们把自己当成了救世主，所以他们把自己的心态放到了比普通员工更高的位置上，试想提前有和老板沟通的尚方宝剑，进到企业后能把自己的身段降下来吗？很难。所以，蔑视一切，包括老板。 第三步，口无遮拦，轻狂至极。正是由于有如此高傲蔑视的心态，所以才有言行之间，带着救世主的姿态，老师的做派，“你们不行！你们要行的话还请我来干 嘛”？甚至认为老板也不行，因为老板也解决不了问题，所以才请我来。于是处处以教训的口吻对待元老：“你们这种做法太老旧了，太落后了，太土了！必须彻底 改变。”而在国外工作时间长者更会说：“你们中国人怎么怎么”云云。 第四步，生搬教条。语言轻狂外，还表现在实际操作上，把过去在其他企业的经验做法往本企业套，随意改变企业现有的流程制度，以为自己过去的操作办法就是先进的。 第五步，批量裁减员工。更有甚者不顾实际情况，大胆起用所谓先进的绩效考核方法，这几乎涉及到每个人的饭碗，通过搞末尾淘汰，批量裁减员工。 第六步，招募新人和旧臣。裁减员工的同时，又不得不招募新员工，甚至招聘自己的旧部等。 成为“人才”后的步步深渊 让我们分析一下K林们上述做法的严重后果。 第一，被妒火烧烤。高级的职务职权、丰厚的薪酬待遇、特殊的工作条件，再加上老板的诚心和殷勤，能不招人嫉妒吗？从人性来讲，空降进来，没有任何业绩就有如此显赫的待遇，谁不嫉妒？所以，空降经理人首先遇到的是老板以外所有员工的嫉妒之火。 第二，成为众矢之的。进门后，老板公开肯定你是引进的高级人才，并把你当人物宝贝似的供着、敬着，这不就等于宣布了别人不是人才？老板在抬高你的时候，又打压了其他人，你的存在给他们的生存造成了恐惧，于是你不反成了大家妒火中烧的对象，又成了箭把子。 第三，遭暗剑频刺。高傲的心态，救世主般的口气，教训人的嘴脸，都会无形之中伤害着各层面的员工，而且伤害到自尊心，试想这些人能不反击吗？他们会用各种办法，一剑一剑刺向你。也许你会感觉到，也许你根本就没有感觉到，因为你正忘乎所以。 第四，遭众人围攻，步履蹒跚。 随意改变制度、修改流程，其结果是触动了人性中的第二天性——惰性。只要做制度和流程的改动，就会改变大家的舒服程度，这一改动不要紧，你等于与大家为 敌。你让别人不舒服，别人能让你舒服吗？群起而攻之，甚至公开对抗，开始和你近距离的肉搏战，你感受到了大家要把你压倒，你喘着气，步履艰难接着前行。 第五， 遭遇组织抗击，变成“残废”。绩效考核涉及到每个员工的切身利益，末尾淘汰会使绩效差乃至平庸的员工心生恐惧，批量裁员等于端掉了他人饭碗。新官上任三把 火，裁减员工是最厉害的一把火，烧得人心惶惶，烧得人人自危，于是大家开始有组织地反击。你的令发不下去了，你失去了眼睛、胳膊和腿，就变成了“残疾”。 所以我曾说：总裁上任就裁员，是不通人性的，而没有人性的经理人能把企业做好吗？ 第六，老板忍不住出手，经理人由残疾变囚徒。遇到了整建制的抵抗，遇到了正规军的反击，你火冒三丈，把一个个总监、经理给开掉了、逼走了，你急需把自己 从残疾恢复成肢体健全者，于是你快速招聘人马，甚至招聘旧臣部下，于是乎新的问题出现了：新人同样不了解情况，会在你的指挥下共犯常识性错误，于是新人团 和老人团打将起来，企业一团乱糟，正常的经营无法保证。这时老板该出面了：第一，质疑你的能力，为什么搞得一团糟？本来让你来推进企业发展的，你倒好，把 企业带入了战争状态。第二，老板质疑你的动机：你干嘛总换老人？不断招聘自己的旧臣，动机何在？这样下去企业即使不崩盘也变味了。再加上众多老人的鼓噪， 以及语重心长的忠告——“我们离开了无所谓，您可要小心某些人的动机不纯，千万警惕啊！！！”更有好事者，会抓住K林们新政后带来的问题不放。于是，K林 们这些老板眼中的红人，会迅速从残疾人变成了阶下囚，彻底败倒。 这就是经理人太把自己当人才的悲惨下场。为此，我建议：空降经理人千万别把自己当人才！只要是企业，都应遵循这一角色规则。 进门之后，只是员工 人才是需要吸引的，员工是需要凝聚的。人才不吸引是进不来的，员工不凝聚是干不成活的。众多K林们遇到的问题，就是没把自己的身份搞清楚：进门前是人 才，企业会用各种办法吸引你；进门后就变成了员工，只不过级别不同。是员工就需要凝聚，凝聚靠什么？是靠文化凝在一起，靠制度聚在一起。而文化需要认同， 制度需要遵守。K林们，乐在发动文化大革命，何谈认同？总是随意超越制度，改变流程，又何谈遵守？ 为什么这样？其原因就是K林们没把自己当员工，没有把自己当成企业人，依然自视人才，当成局外人。自己的心没有沉下来，是不可能对企业真正负责的。这样的心态作用下带来的结果，是留下一个烂摊子给老板来收拾，带来的阵痛和苦果让其他员工承受。企业要他何用？ 因此，无论多么大腕儿的经理人，空降后要想把事情做好。第一件事情，把心态调整到位，把姿态放下来，先把自己当员工看，老员工才会把你当成自己人看。自己都把自己当成了局外人，他人怎么会把你当成局内人。局外人也许能看明白事情，但一定做不好局内的事情。 自己的环境，自己创造 我也常听有些经理人讲：“老板请我来，就应该给我创造一个宽松的工作环境，让我痛痛快快地干一场。”我就反问一句：“凭什么？”老板要是能把所有的事情摆平，还要你来做什么？况且是老板请你来，但其他人并没有请你来，凭什么其他人都得为你服务？ 所以，经理人正确的想法和做法应该是：自己为自己施展才华创造一个宽松的环境。不仅如此，凭借自己的能力和智慧，还应该为更多的员工创造一个宽松的环境。这就是经理人的职责。如果自己真有才的话，应该用在这个地方。 总之，经理人要想做出成绩，至少不能停留在仅做事的层面，而应该迈上更高的做人层面。否则，你还只是一名专业技术人员，是滥竽充数的经理人，失败就是必然。如果你讨厌复杂的人际关系，那么劝你还是退回去做一名专业技术人员，当然也就别再奢求高级经理人的薪水和待遇
|  |
Tuesday, April 15, 2008
using baseline
I've generated some baselines for the critical batch jobs which you can view from dba_hist_baseline so that you would be aware of which range of statistics to be extracted.
These will serve to be useful whenever we need to troubleshoot as we need to have baselines to work from. For last week's issue on the filesystem, I worked
from a base set of performance numbers before I concluded that there is something wrong at the system.
Numbers that I looked at include the average response time and absolute time for the background events, response table for the tablespaces, transaction rate for I/O related statistics. Let me know if you are unsure.
--CREATE_BASELINE Procedure Parameters
Parameter Description
START_SNAP_ID Lowest snapshot ID in the range
END_SNAP_ID Highest snapshot ID in the range
BASELINE_NAME Unique name for the baseline
DBID Optional database ID
For example, if you want to baseline the job that creates nightly reports, you could use the following
example, assuming that snapshots 42 and 43 mark the beginning and ending of the job:
BEGIN
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID =>42,
➥END_SNAP_ID =>43,
BASELINE_NAME => ’REPORTS’);
END
--Dropping Baselines
--DROP_BASELINE Procedure Parameters
Parameter Description
BASELINE_NAME Name of the baseline to be dropped
CASCADE Boolean to determine whether the associated snapshots will be dropped
(default = FALSE)
DBID Optional database ID
Exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(‘REPORTS’,FALSE);
To query existing baselines, can use below command.
chk_baselines.sql"
set pages 1000
column BASELINE_NAME format a20
column START_SNAP_TIME format a30
column END_SNAP_TIME format a30
select BASELINE_ID,BASELINE_NAME,START_SNAP_TIME,END_SNAP_TIME from dba_hist_baseline order by baseline_id;
--CREATE_BASELINE Procedure Parameters
Parameter Description
START_SNAP_ID Lowest snapshot ID in the range
END_SNAP_ID Highest snapshot ID in the range
BASELINE_NAME Unique name for the baseline
DBID Optional database ID
For example, if you want to baseline the job that creates nightly reports, you could use the following
example, assuming that snapshots 42 and 43 mark the beginning and ending of the job:
BEGIN
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID =>42,
➥END_SNAP_ID =>43,
BASELINE_NAME => ’REPORTS’);
END
--Dropping Baselines
--DROP_BASELINE Procedure Parameters
Parameter Description
BASELINE_NAME Name of the baseline to be dropped
CASCADE Boolean to determine whether the associated snapshots will be dropped
(default = FALSE)
DBID Optional database ID
Exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(‘REPORTS’,FALSE);
To query existing baselines, can use below command.
chk_baselines.sql"
set pages 1000
column BASELINE_NAME format a20
column START_SNAP_TIME format a30
column END_SNAP_TIME format a30
select BASELINE_ID,BASELINE_NAME,START_SNAP_TIME,END_SNAP_TIME from dba_hist_baseline order by baseline_id;
using AWR
DBMS_WORKLOAD_REPOSITORY Procedures
LOW_SNAP_ID The lowest snapshot ID of range to drop
HIGH_SNAP_ID The highest snapshot ID of range to drop
DBID Optional database ID
SQL> exec dbms_workload_repository.drop_snapshot_range(6,14,3491152056);
PL/SQL procedure successfully completed.
--Creating Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;
--Modifying Snapshot Frequency
RETENTION Length of time to retain snapshots (in minutes). Must be between 1,440
(one day) and 52,596,000 (100 years).
INTERVAL Time interval between snapshots (in minutes). Must be between 10 and
525,600 (one year).
DBID The database ID (defaults to the local DBID).
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
RETENTION => 14400, INTERVAL => 45);
END;
--To To view the current AWR settings, you can use the DBA_HIST_WR_CONTROL view, as
shown here:
SQL> Select snap_interval, retention From dba_hist_wr_control;
SQL> select * from dba_hist_wr_control;
DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL
----------
3491152056
+00000 01:00:00.0
+00007 00:00:00.0
DEFAULT
SQL> desc dba_hist_wr_control;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NOT NULL NUMBER
SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
TOPNSQL VARCHAR2(10)
- CREATE_SNAPSHOT Creates manual snapshots
- DROP_SNAPSHOT_RANGE Drops a range of snapshots at once
- CREATE_BASELINE Creates a single baseline
- DROP_BASELINE Drops a single baseline
- MODIFY_SNAPSHOT_SETTINGS Changes the RETENTION and INTERVAL settings
LOW_SNAP_ID The lowest snapshot ID of range to drop
HIGH_SNAP_ID The highest snapshot ID of range to drop
DBID Optional database ID
SQL> exec dbms_workload_repository.drop_snapshot_range(6,14,3491152056);
PL/SQL procedure successfully completed.
--Creating Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;
--Modifying Snapshot Frequency
RETENTION Length of time to retain snapshots (in minutes). Must be between 1,440
(one day) and 52,596,000 (100 years).
INTERVAL Time interval between snapshots (in minutes). Must be between 10 and
525,600 (one year).
DBID The database ID (defaults to the local DBID).
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
RETENTION => 14400, INTERVAL => 45);
END;
--To To view the current AWR settings, you can use the DBA_HIST_WR_CONTROL view, as
shown here:
SQL> Select snap_interval, retention From dba_hist_wr_control;
SQL> select * from dba_hist_wr_control;
DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL
----------
3491152056
+00000 01:00:00.0
+00007 00:00:00.0
DEFAULT
SQL> desc dba_hist_wr_control;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NOT NULL NUMBER
SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
TOPNSQL VARCHAR2(10)
manuipulation of outline
all run as sysdba
--to turn off outline
SQL> alter system set use_stored_outlines=false;
System altered.
--to capture outline
SQL> alter system flush shared_pool;
System altered.
SQL> alter system set create_stored_outlines='ARBOR_1154_10G_BIP_LQ0415';
System altered.
--to rename outline
select 'alter outline ' || ol_name || ' rename to ' || 'BIF_1154_EBF' || substr(ol_name,12,30)||';' from outln.ol$ where category='ARBOR_1154_10G_BIP_LQ0415';
--to merge outline ARBOR_1154_10G_CAP into USE_CUSTPA
select count(*) from outln.ol$ where category='ARBOR_1154_10G_CAP';
select count(*) from outln.ol$ where category='USE_CUSTPA';
execute outln_pkg.update_by_cat('ARBOR_1154_10G_CAP','USE_CUSTPA');
select count(*) from outln.ol$ where category='ARBOR_1154_10G_CAP';
select count(*) from outln.ol$ where category='USE_CUSTPA';
--to turn on outline
alter system set use_stored_outlines=USE_CUSTPA;
--to turn off outline
SQL> alter system set use_stored_outlines=false;
System altered.
--to capture outline
SQL> alter system flush shared_pool;
System altered.
SQL> alter system set create_stored_outlines='ARBOR_1154_10G_BIP_LQ0415';
System altered.
--to rename outline
select 'alter outline ' || ol_name || ' rename to ' || 'BIF_1154_EBF' || substr(ol_name,12,30)||';' from outln.ol$ where category='ARBOR_1154_10G_BIP_LQ0415';
--to merge outline ARBOR_1154_10G_CAP into USE_CUSTPA
select count(*) from outln.ol$ where category='ARBOR_1154_10G_CAP';
select count(*) from outln.ol$ where category='USE_CUSTPA';
execute outln_pkg.update_by_cat('ARBOR_1154_10G_CAP','USE_CUSTPA');
select count(*) from outln.ol$ where category='ARBOR_1154_10G_CAP';
select count(*) from outln.ol$ where category='USE_CUSTPA';
--to turn on outline
alter system set use_stored_outlines=USE_CUSTPA;
run awrrpt
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1068_1073.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: ssonp_awrrpt_0415.html
Using the report name ssonp_awrrpt_0415.html
select output from table(dbms_workload_repository.awr_report_html( :dbid,
*
ERROR at line 1:
ORA-00904: : invalid identifier
Report written to ssonp_awrrpt_0415.html
[SOLUTION]
grant EXECUTE on DBMS_WORKLOAD_REPOSITORY to liqy;
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1068_1073.html. To use this name,
press
Enter value for report_name: ssonp_awrrpt_0415.html
Using the report name ssonp_awrrpt_0415.html
select output from table(dbms_workload_repository.awr_report_html( :dbid,
*
ERROR at line 1:
ORA-00904: : invalid identifier
Report written to ssonp_awrrpt_0415.html
[SOLUTION]
grant EXECUTE on DBMS_WORKLOAD_REPOSITORY to liqy;
Monday, April 14, 2008
startup restrict
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2062264 bytes
Variable Size 264243272 bytes
Database Buffers 801112064 bytes
Redo Buffers 6324224 bytes
Database mounted.
Database opened.
SQL> alter user orara identified by sds123;
User altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus orara@vcdrt2
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:38:20 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:41:15 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> revoke dba from orara;
Revoke succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:41:53 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> show user;
USER is "SYS"
SQL> alter system enable restricted session;
System altered.
liqy@mp4is_liqy ~
$ sqlplus orara/sds123@VCDRT2
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 4月 14 10:43:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Enter user-name:
SQL> alter system disable restricted session;
System altered.
$ sqlplus orara/sds123@VCDRT2
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 4月 14 10:43:38 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2062264 bytes
Variable Size 264243272 bytes
Database Buffers 801112064 bytes
Redo Buffers 6324224 bytes
Database mounted.
Database opened.
SQL> alter user orara identified by sds123;
User altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus orara@vcdrt2
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:38:20 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:41:15 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> revoke dba from orara;
Revoke succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:41:53 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> show user;
USER is "SYS"
SQL> alter system enable restricted session;
System altered.
liqy@mp4is_liqy ~
$ sqlplus orara/sds123@VCDRT2
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 4月 14 10:43:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Enter user-name:
SQL> alter system disable restricted session;
System altered.
$ sqlplus orara/sds123@VCDRT2
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 4月 14 10:43:38 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Sunday, April 13, 2008
Using Flashback Table
The Flashback Table must have ROW MOVEMENT enabled with the following
command: ALTER TABLE tablename ENABLE ROW MOVEMENT.
SQL> alter table hr.t1 enable row movement;
Table altered.
SQL> select * from hr.t1;
F1
----------
50
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1175441
SQL> update hr.t1 set f1=5000;
1 row updated.
SQL> commit;
Commit complete.
SQL> flashback table hr.t1 to scn 1175441;
Flashback complete.
SQL> select * from hr.t1;
F1
----------
50
command: ALTER TABLE tablename ENABLE ROW MOVEMENT.
SQL> alter table hr.t1 enable row movement;
Table altered.
SQL> select * from hr.t1;
F1
----------
50
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1175441
SQL> update hr.t1 set f1=5000;
1 row updated.
SQL> commit;
Commit complete.
SQL> flashback table hr.t1 to scn 1175441;
Flashback complete.
SQL> select * from hr.t1;
F1
----------
50
user fuser to find out process
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> opatch apply
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /software/oracle/product/10.2.0
Central Inventory : /software/oracle/oraInventory10g
from : /software/oracle/product/10.2.0/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /software/oracle/product/10.2.0/oui
Log file location : /software/oracle/product/10.2.0/cfgtoollogs/opatch/opatch-2008_Apr_13_16-57-54-SGT_Sun.log
ApplySession applying interim patch '5169475' to OH '/software/oracle/product/10.2.0'
Invoking fuser to check for active processes.
Invoking fuser on "/software/oracle/product/10.2.0/bin/oracle"
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/software/oracle/product/10.2.0')
Is the local system ready for patching?
Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5169475' for restore. This might take a while...
Backing up files affected by the patch '5169475' for rollback. This might take a while...
Patching component oracle.rdbms.rsf, 10.2.0.2.0...
Updating archive file "/software/oracle/product/10.2.0/lib/libgeneric10.a" with "lib/libgeneric10.a/slrac.o"
Updating archive file "/software/oracle/product/10.2.0/lib32/libgeneric10.a" with "lib32/libgeneric10.a/slrac.o"
Patching component oracle.rdbms, 10.2.0.2.0...
Running make for target client_sharedlib
Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/software/oracle/product/10.2.0"....'rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
'
rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
Do you want to proceed? [y|n]
n
User Responded with: N
Apply Session failed: ApplySession failed in system modification phase... 'ApplySession::apply failed: rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
'
OPatch will attempt to restore the system...
Restoring the Oracle Home...
Checking if OPatch needs to invoke 'make' to restore some binaries...
Make result:
Command: /usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/software/oracle/product/10.2.0
Returned code: 1
Stdout output:
/software/oracle/product/10.2.0/bin/genclntsh
*** Error exit code 1
Stderr output:
rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
--------------------------------------------------------------------------------
The following Make Commands have failed:
cd $ORACLE_HOME/rdbms/lib;/usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=$ORACLE_HOME
These are commands from the file /software/oracle/product/10.2.0/.patch_storage/5169475_Mar_19_2007_03_45_26/make.txt
Invoke these commands manually to restore the binaries in the Oracle Home.
OPatch failed to restore OH '/software/oracle/product/10.2.0'. Consult OPatch document to restore the home manually before proceeding.
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) OUI-67200:Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/software/oracle/product/10.2.0"....'rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
'
--------------------------------------------------------------------------------
OPatch failed with error code 115
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> /usr/sbin/fuser /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
/software/oracle/product/10.2.0/lib/libclntsh.so.10.1: 7679m
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> /usr/sbin/fuser /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> ps -ef |grep 7679
oracle 12729 11267 0 17:01:19 pts/0 0:00 grep 7679
oracle 7679 1 0 Mar 27 ? 52:31 /software/oracle/product/10.2.0/bin/tnslsnr LISTENER10g -inherit
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> lsnrctl stop LISTENER10g
LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 13-APR-2008 17:01:29
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dev07.m1.com.sg)(PORT=1522)))
The command completed successfully
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> /usr/sbin/fuser /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
/software/oracle/product/10.2.0/lib/libclntsh.so.10.1:
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475>
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /software/oracle/product/10.2.0
Central Inventory : /software/oracle/oraInventory10g
from : /software/oracle/product/10.2.0/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /software/oracle/product/10.2.0/oui
Log file location : /software/oracle/product/10.2.0/cfgtoollogs/opatch/opatch-2008_Apr_13_16-57-54-SGT_Sun.log
ApplySession applying interim patch '5169475' to OH '/software/oracle/product/10.2.0'
Invoking fuser to check for active processes.
Invoking fuser on "/software/oracle/product/10.2.0/bin/oracle"
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/software/oracle/product/10.2.0')
Is the local system ready for patching?
Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5169475' for restore. This might take a while...
Backing up files affected by the patch '5169475' for rollback. This might take a while...
Patching component oracle.rdbms.rsf, 10.2.0.2.0...
Updating archive file "/software/oracle/product/10.2.0/lib/libgeneric10.a" with "lib/libgeneric10.a/slrac.o"
Updating archive file "/software/oracle/product/10.2.0/lib32/libgeneric10.a" with "lib32/libgeneric10.a/slrac.o"
Patching component oracle.rdbms, 10.2.0.2.0...
Running make for target client_sharedlib
Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/software/oracle/product/10.2.0"....'rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
'
rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
Do you want to proceed? [y|n]
n
User Responded with: N
Apply Session failed: ApplySession failed in system modification phase... 'ApplySession::apply failed: rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
'
OPatch will attempt to restore the system...
Restoring the Oracle Home...
Checking if OPatch needs to invoke 'make' to restore some binaries...
Make result:
Command: /usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/software/oracle/product/10.2.0
Returned code: 1
Stdout output:
/software/oracle/product/10.2.0/bin/genclntsh
*** Error exit code 1
Stderr output:
rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
--------------------------------------------------------------------------------
The following Make Commands have failed:
cd $ORACLE_HOME/rdbms/lib;/usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=$ORACLE_HOME
These are commands from the file /software/oracle/product/10.2.0/.patch_storage/5169475_Mar_19_2007_03_45_26/make.txt
Invoke these commands manually to restore the binaries in the Oracle Home.
OPatch failed to restore OH '/software/oracle/product/10.2.0'. Consult OPatch document to restore the home manually before proceeding.
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) OUI-67200:Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/software/oracle/product/10.2.0"....'rm: /software/oracle/product/10.2.0/lib/libclntsh.so.10.1 not removed. Text file busy
genclntsh: Failed to remove /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
Stop.
'
--------------------------------------------------------------------------------
OPatch failed with error code 115
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> /usr/sbin/fuser /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
/software/oracle/product/10.2.0/lib/libclntsh.so.10.1: 7679m
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> /usr/sbin/fuser /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> ps -ef |grep 7679
oracle 12729 11267 0 17:01:19 pts/0 0:00 grep 7679
oracle 7679 1 0 Mar 27 ? 52:31 /software/oracle/product/10.2.0/bin/tnslsnr LISTENER10g -inherit
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> lsnrctl stop LISTENER10g
LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 13-APR-2008 17:01:29
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dev07.m1.com.sg)(PORT=1522)))
The command completed successfully
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475> /usr/sbin/fuser /software/oracle/product/10.2.0/lib/libclntsh.so.10.1
/software/oracle/product/10.2.0/lib/libclntsh.so.10.1:
dev07:B.11:TESTDB:/software/oracle/admin/PATCH/5169475>
Saturday, April 12, 2008
use RMAN to flashback database to time;
14:31:17 SQL> prompt NOW test flashback to time;
NOW test flashback to time
14:31:59 SQL> select * from hr.test_table1;
no rows selected
14:32:10 SQL> insert into hr.test_table1 values(100);
1 row created.
14:32:25 SQL> commit;
Commit complete.
14:32:30 SQL> select * from hr.test_table1;
F1
----------
100
14:32:33 SQL> drop table hr.test_table1;
Table dropped.
14:32:48 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:33:09 SQL> startup mount;
ORACLE instance started.
Total System Global Area 1035993088 bytes
Fixed Size 2077904 bytes
Variable Size 624954160 bytes
Database Buffers 402653184 bytes
Redo Buffers 6307840 bytes
Database mounted.
14:33:25 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle*OCP10G@localhost ~]$rman
Recovery Manager: Release 10.2.0.3.0 - Production on Sat Apr 12 14:33:29 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target
connected to target database: OCP10G (DBID=3491152056, not open)
RMAN> flashback database to time="to_date('04/12/2008 14:32:30','mm/dd/yyyy hh24:mi:ss')";
Starting flashback at 12-APR-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished flashback at 12-APR-08
RMAN> exit
Recovery Manager complete.
[oracle*OCP10G@localhost ~]$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Apr 12 14:42:14 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.test_table1;
F1
----------
100
NOW test flashback to time
14:31:59 SQL> select * from hr.test_table1;
no rows selected
14:32:10 SQL> insert into hr.test_table1 values(100);
1 row created.
14:32:25 SQL> commit;
Commit complete.
14:32:30 SQL> select * from hr.test_table1;
F1
----------
100
14:32:33 SQL> drop table hr.test_table1;
Table dropped.
14:32:48 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:33:09 SQL> startup mount;
ORACLE instance started.
Total System Global Area 1035993088 bytes
Fixed Size 2077904 bytes
Variable Size 624954160 bytes
Database Buffers 402653184 bytes
Redo Buffers 6307840 bytes
Database mounted.
14:33:25 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle*OCP10G@localhost ~]$rman
Recovery Manager: Release 10.2.0.3.0 - Production on Sat Apr 12 14:33:29 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target
connected to target database: OCP10G (DBID=3491152056, not open)
RMAN> flashback database to time="to_date('04/12/2008 14:32:30','mm/dd/yyyy hh24:mi:ss')";
Starting flashback at 12-APR-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished flashback at 12-APR-08
RMAN> exit
Recovery Manager complete.
[oracle*OCP10G@localhost ~]$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Apr 12 14:42:14 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.test_table1;
F1
----------
100
rman-06747
Backup of the flash recovery area is an important activity because this area contains important
backup information that is critical to the recovery of the database. Oracle has developed special
commands to back up the flash recovery area. This is performed to a tape device so that the
backups to the flash recovery can be recovered if there is a disk failure that supports the flash
recovery area.
RMAN> backup recovery area;
Starting backup at 12-APR-08
specification does not match any archive log in the recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/12/2008 09:15:19
RMAN-06747: at least 1 channel of tertiary storage must be allocated to execute this command
RMAN> backup recovery files;
Starting backup at 12-APR-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/12/2008 09:15:34
RMAN-06747: at least 1 channel of tertiary storage must be allocated to execute this command
[oracle*OCP10G@localhost oracle]$oerr rman 6747
6747, 1, "at least 1 channel of tertiary storage must be allocated to execute this command"
// *Cause: The executed command requires a SBT channel, but no channels of
// type SBT were configured or allocated.
// *Action: ALLOCATE or CONFIGURE a SBT channel.
10g引进了flash recovery area，同时在rman备份中支持对该区域的备份，昨天考试的时候还遇到这个问题，就是在备份闪回恢复区域时，oracle会备份那些文件，而不备份那些文件。这个从来还没有考虑过，当然在考场上也是很紧张的。今天查了很多资料，终于在metalink上找到一段话：
To free space in the FRA we could do take a backup of the Flash Recovery Area using the command BACKUP RECOVERY AREA.This command will take the backup of all the files in the FRA to tape only. After this the space occupied by the files in the FRA will be marked as reclaimable
这段话阐明了两个主要问题：
1、就是对recovery area的备份只能备份到tape上，如果备份到disk上，则会报以下错误：
RMAN>
启动 backup 于 31-10月-07
说明与资料档案库中的任何数据文件副本都不匹配
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: backup 命令 (在 10/31/2007 19:38:48 上) 失败
RMAN-06747: 至少必须分配一个三级存储通道以执行此命令
2、会备份存储在fra中的所有文件。即备份集、镜像备份、自动备份的控制文件和spfile文件、flashback log、归档重做日志等。
backup information that is critical to the recovery of the database. Oracle has developed special
commands to back up the flash recovery area. This is performed to a tape device so that the
backups to the flash recovery can be recovered if there is a disk failure that supports the flash
recovery area.
RMAN> backup recovery area;
Starting backup at 12-APR-08
specification does not match any archive log in the recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/12/2008 09:15:19
RMAN-06747: at least 1 channel of tertiary storage must be allocated to execute this command
RMAN> backup recovery files;
Starting backup at 12-APR-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/12/2008 09:15:34
RMAN-06747: at least 1 channel of tertiary storage must be allocated to execute this command
[oracle*OCP10G@localhost oracle]$oerr rman 6747
6747, 1, "at least 1 channel of tertiary storage must be allocated to execute this command"
// *Cause: The executed command requires a SBT channel, but no channels of
// type SBT were configured or allocated.
// *Action: ALLOCATE or CONFIGURE a SBT channel.
10g引进了flash recovery area，同时在rman备份中支持对该区域的备份，昨天考试的时候还遇到这个问题，就是在备份闪回恢复区域时，oracle会备份那些文件，而不备份那些文件。这个从来还没有考虑过，当然在考场上也是很紧张的。今天查了很多资料，终于在metalink上找到一段话：
To free space in the FRA we could do take a backup of the Flash Recovery Area using the command BACKUP RECOVERY AREA.This command will take the backup of all the files in the FRA to tape only. After this the space occupied by the files in the FRA will be marked as reclaimable
这段话阐明了两个主要问题：
1、就是对recovery area的备份只能备份到tape上，如果备份到disk上，则会报以下错误：
RMAN>
启动 backup 于 31-10月-07
说明与资料档案库中的任何数据文件副本都不匹配
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: backup 命令 (在 10/31/2007 19:38:48 上) 失败
RMAN-06747: 至少必须分配一个三级存储通道以执行此命令
2、会备份存储在fra中的所有文件。即备份集、镜像备份、自动备份的控制文件和spfile文件、flashback log、归档重做日志等。
Thursday, April 10, 2008
How to check default temporary tablespace
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200
SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200
SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
Temporary Tablespace Group
Oracle 10g introduces a new term called “temporary tablespace group.”
Temporary Tablespace Group Overview
A temporary tablespace group consists of only temporary tablespace, and has the following properties:
* It contains one or more temporary tablespaces.
* It contains only temporary tablespace.
* It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group.
Temporary Tablespace Group Benefits
Temporary tablespace group has the following benefits:
* It allows multiple default temporary tablespaces to be specified at the database level.
* It allows the user to use multiple temporary tablespaces in different sessions at the same time.
* It allows a single SQL operation to use multiple temporary tablespaces for sorting.
New Data Dictionary View
Oracle 10g introduces a new data dictionary view, dba_tablespace_groups, for the temporary tablespace group. Using a tablespace with a temporary tablespace group will result in the following select statement. However, using a tablespace without a temporary tablespace group will not return the select statement below.
select
tablespace_name, group_name
from
DBA_TABLESPACE_GROUPS;
Multiple Temporary Tablespaces
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:
-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;
-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;
The tablespaces assigned to a group can be viewed using:
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2
2 rows selected.
Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:
-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;
-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;
A tablespace can be removed from a group using:
ALTER TABLESPACE temp2 TABLESPACE GROUP '';
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
1 row selected.
There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';
-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;
no rows selected
Temporary Tablespace Group Overview
A temporary tablespace group consists of only temporary tablespace, and has the following properties:
* It contains one or more temporary tablespaces.
* It contains only temporary tablespace.
* It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group.
Temporary Tablespace Group Benefits
Temporary tablespace group has the following benefits:
* It allows multiple default temporary tablespaces to be specified at the database level.
* It allows the user to use multiple temporary tablespaces in different sessions at the same time.
* It allows a single SQL operation to use multiple temporary tablespaces for sorting.
New Data Dictionary View
Oracle 10g introduces a new data dictionary view, dba_tablespace_groups, for the temporary tablespace group. Using a tablespace with a temporary tablespace group will result in the following select statement. However, using a tablespace without a temporary tablespace group will not return the select statement below.
select
tablespace_name, group_name
from
DBA_TABLESPACE_GROUPS;
Multiple Temporary Tablespaces
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:
-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;
-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;
The tablespaces assigned to a group can be viewed using:
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2
2 rows selected.
Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:
-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;
-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;
A tablespace can be removed from a group using:
ALTER TABLESPACE temp2 TABLESPACE GROUP '';
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
1 row selected.
There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';
-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;
no rows selected
Saturday, April 05, 2008
run RMAN without TAPE
--error
RMAN> delete backupset 163;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 04/05/2008 15:11:35
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
--solution
RMAN> allocate channel for maintenance type disk;
allocated channel: ORA_MAINT_DISK_1
channel ORA_MAINT_DISK_1: sid=134 devtype=DISK
RMAN> delete backupset 163;
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
167 163 1 1 UNAVAILABLE DISK /apps/oracle/rmanbackup_area/c-3491152056-20080419-01
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/apps/oracle/rmanbackup_area/c-3491152056-20080419-01 recid=7 stamp=652487787
Deleted 1 objects
RMAN> delete backupset 163;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 04/05/2008 15:11:35
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
--solution
RMAN> allocate channel for maintenance type disk;
allocated channel: ORA_MAINT_DISK_1
channel ORA_MAINT_DISK_1: sid=134 devtype=DISK
RMAN> delete backupset 163;
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
167 163 1 1 UNAVAILABLE DISK /apps/oracle/rmanbackup_area/c-3491152056-20080419-01
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/apps/oracle/rmanbackup_area/c-3491152056-20080419-01 recid=7 stamp=652487787
Deleted 1 objects
Thursday, April 03, 2008
recover by SCN
16:21:06 SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2062264 bytes
Variable Size 264243272 bytes
Database Buffers 801112064 bytes
Redo Buffers 6324224 bytes
Database mounted.
16:23:35 SQL> recover database until change 900335279 using backup controlfile;
ORA-00279: change 900335086 generated at 04/03/2008 15:48:24 needed for thread
1
ORA-00289: suggestion :
/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_1.arc
ORA-00280: change 900335086 for thread 1 is in sequence #1
16:24:04 Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 900335233 generated at 04/03/2008 16:18:01 needed for thread
1
ORA-00289: suggestion :
/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_2.arc
ORA-00280: change 900335233 for thread 1 is in sequence #2
ORA-00278: log file '/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_1.arc'
no longer needed for this recovery
16:24:07 Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
16:24:12 SQL> alter database open resetlogs;
Database altered.
16:24:29 SQL> select * from orara.test1;
F1
----------
102
103
101
16:24:37 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--DO the same experiment, but will try another command "alter database recover database ..."
--it shows that they are different at mount state.
radev01:*:VCDRT2:/ra012/oradata/VCDRT2> ls -lrt oraarch
total 976
-rw-r----- 1 orara dba 434176 Apr 3 16:18 VCDRT2_0001_651080862_1.arc
-rw-r----- 1 orara dba 55296 Apr 3 16:20 VCDRT2_0001_651080862_2.arc
-rw-r----- 1 orara dba 2048 Apr 3 16:20 VCDRT2_0001_651080862_3.arc
--to show commands "alter database recover database" is different with "recover database"
16:26:16 SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2062264 bytes
Variable Size 264243272 bytes
Database Buffers 801112064 bytes
Redo Buffers 6324224 bytes
Database mounted.
16:28:37 SQL> alter database recover database until change 900335279 using backup controlfile;
alter database recover database until change 900335279 using backup controlfile
*
ERROR at line 1:
ORA-00279: change 900335086 generated at 04/03/2008 15:48:24 needed for thread
1
ORA-00289: suggestion :
/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_1.arc
ORA-00280: change 900335086 for thread 1 is in sequence #1
16:28:57 SQL>
--I also learned that the SCN should be specified in decimal.
in 9i, it is hexadecimal in alert.log
so, need to use scientific calculator to covert it, eg. (7924614612541=0x0735.17d6c63d)
otherwise, error prompted .
--wrong
17:57:30 SQL> recover database until change 0x073517d6c63d using backup controlfile;
ORA-00905: missing keyword
--correct
17:57:58 SQL> recover database until change 7924614612541 using backup controlfile;
ORA-00279: change 7924607131028 generated at 02/02/2008 21:30:17 needed for
thread 1
ORA-00289: suggestion : /ict22/oraarch/ICTP/ICT_ARC_1_42077.arc
ORA-00280: change 7924607131028 for thread 1 is in sequence #42077
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2062264 bytes
Variable Size 264243272 bytes
Database Buffers 801112064 bytes
Redo Buffers 6324224 bytes
Database mounted.
16:23:35 SQL> recover database until change 900335279 using backup controlfile;
ORA-00279: change 900335086 generated at 04/03/2008 15:48:24 needed for thread
1
ORA-00289: suggestion :
/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_1.arc
ORA-00280: change 900335086 for thread 1 is in sequence #1
16:24:04 Specify log: {
ORA-00279: change 900335233 generated at 04/03/2008 16:18:01 needed for thread
1
ORA-00289: suggestion :
/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_2.arc
ORA-00280: change 900335233 for thread 1 is in sequence #2
ORA-00278: log file '/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_1.arc'
no longer needed for this recovery
16:24:07 Specify log: {
Log applied.
Media recovery complete.
16:24:12 SQL> alter database open resetlogs;
Database altered.
16:24:29 SQL> select * from orara.test1;
F1
----------
102
103
101
16:24:37 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--DO the same experiment, but will try another command "alter database recover database ..."
--it shows that they are different at mount state.
radev01:*:VCDRT2:/ra012/oradata/VCDRT2> ls -lrt oraarch
total 976
-rw-r----- 1 orara dba 434176 Apr 3 16:18 VCDRT2_0001_651080862_1.arc
-rw-r----- 1 orara dba 55296 Apr 3 16:20 VCDRT2_0001_651080862_2.arc
-rw-r----- 1 orara dba 2048 Apr 3 16:20 VCDRT2_0001_651080862_3.arc
--to show commands "alter database recover database" is different with "recover database"
16:26:16 SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2062264 bytes
Variable Size 264243272 bytes
Database Buffers 801112064 bytes
Redo Buffers 6324224 bytes
Database mounted.
16:28:37 SQL> alter database recover database until change 900335279 using backup controlfile;
alter database recover database until change 900335279 using backup controlfile
*
ERROR at line 1:
ORA-00279: change 900335086 generated at 04/03/2008 15:48:24 needed for thread
1
ORA-00289: suggestion :
/ra012/oradata/VCDRT2/oraarch/VCDRT2_0001_651080862_1.arc
ORA-00280: change 900335086 for thread 1 is in sequence #1
16:28:57 SQL>
--I also learned that the SCN should be specified in decimal.
in 9i, it is hexadecimal in alert.log
so, need to use scientific calculator to covert it, eg. (7924614612541=0x0735.17d6c63d)
otherwise, error prompted .
--wrong
17:57:30 SQL> recover database until change 0x073517d6c63d using backup controlfile;
ORA-00905: missing keyword
--correct
17:57:58 SQL> recover database until change 7924614612541 using backup controlfile;
ORA-00279: change 7924607131028 generated at 02/02/2008 21:30:17 needed for
thread 1
ORA-00289: suggestion : /ict22/oraarch/ICTP/ICT_ARC_1_42077.arc
ORA-00280: change 7924607131028 for thread 1 is in sequence #42077
BUG: Error 7356 from a Distributed Query
APPLIES TO
• Microsoft SQL Server 7.0 Standard Edition
???
In my env, we still hit the problem with SQL server 2000 SP4.
• Microsoft SQL Server 7.0 Standard Edition
???
In my env, we still hit the problem with SQL server 2000 SP4.
Subscribe to:
Posts (Atom)
