HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK

Subject: HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
Doc ID: Note:122793.1 Type: TROUBLESHOOTING
Last Revision Date: 25-JUL-2005 Status: PUBLISHED


PURPOSE
-------

In some situations it may happen your session is 'hanging' and is awaiting for
a 'Library cache lock'. This document describes how to find the session that
in fact has the lock you are waiting for.


SCOPE & APPLICATION
-------------------

Support analysts, dba's, ..


HOW TO FIND THE SESSION HOLDING A A LIBRARY CACHE LOCK
------------------------------------------------------

Common situations:

* a DML operation that is hanging because the table which is accessed is currently
undergoing changes (ALTER TABLE). This may take quite a long time depending on
the size of the table and the type of the modification
(e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).

* The compilation of package will hang on Library Cache Lock and Library Cache Pin
if some users are executing any Procedure/Function defined in the same package.

In the first situation the V$LOCK view will show that the session doing the
'ALTER TABLE' has an exclusive DML enqueue lock on the table object (LMODE=6,
TYPE=TM and ID1 is the OBJECT_ID of the table). The waiting session however does
not show up in V$LOCK yet so in an environment with a lot of concurrent sessions
the V$LOCK information is insufficient to track down the culprit blocking your
operation.

METHOD 1: SYSTEMSTATE ANALYSIS
------------------------------

One way of finding the session blocking you is to analyze the system state dump.
Using the systemstate event one can create a tracefile containing detailed
information on every Oracle process. This information includes all the resources
held & requested by a specific process.

Whilst an operation is hanging, open a new session and launch the following
statement:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) of the 'hanging' session from the V$PROCESS by matching
PADDR from V$SESSION with ADDR from V$PROCESS:

SELECT PID FROM V$PROCESS WHERE ADDR=
(SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);

The systemstate dump contains a separate section with information for each
process. Open the tracefile and do a search for 'PROCESS pid_from_select_stmt'.
In the process section look up the wait event by doing a search on 'waiting for'.

Example output:

PROCESS 8:
----------------------------------------
SO: 50050b08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 82 0 4
last post received-location: kslpsr
last process to post me: 5004ff08 1 2
last post sent: 0 0 13
last post sent-location: ksasnd
last process posted by me: 5004ff08 1 2
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 50058ac4
O/S info: user: daemon, term: pts/1, ospid: 15161
OSD pid info: 15161
----------------------------------------
SO: 5005f294, type: 3, owner: 50050b08, flag: INIT/-/-/0x00
(session) trans: 0, creator: 50050b08, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, user: 41/LC
O/S info: user: daemon, term: pts/1, ospid: 15160, machine: goblin.forgotten.realms
program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x0 seq=253 wait_time=0
!>> handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

Using the 'handle address' you can look up the process that is keeping a lock
on your resource by doing a search on the address within the same tracefile.

Example output:

PROCESS 9:
----------------------------------------
SO: 50050e08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0

....

----------------------------------------
SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>> LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0
user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]

From the output we can see that the Oracle process with PID 9 has an exclusive
lock on the object we are trying to access. Using V$PROCESS and V$SESSION we can
retrieve the sid,user,terminal,program,... for this process. The actual statement
that was launched by this session is also listed in the tracefile (statements and
other library cache objects are preceded by 'name=').


METHOD 2: EXAMINE THE X$KGLLK TABLE
-----------------------------------

The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the
library object locks (both held & requested) for all sessions and
is more complete than the V$LOCK view although the column names don't
always reveal their meaning.

You can examine the locks requested (and held) by the waiting session
by looking up the session address (SADDR) in V$SESSION and doing the
following select:

select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'

This will show you all the library locks held by this session where
KGLNAOBJ contains the first 80 characters of the name of the object.
The value in KGLLKHDL corresponds with the 'handle address' of the
object in METHOD 1.

You will see that at least one lock for the session has KGLLKREQ > 0
which means this is a REQUEST for a lock (thus, the session is waiting).
If we now match the KGLLKHDL with the handles of other sessions in
X$KGLLK that should give us the address of the blocking session since
KGLLKREQ=0 for this session, meaning it HAS the lock.

SELECT * FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0);

If we look a bit further we can then again match KGLLKSES with SADDR
in v$session to find further information on the blocking session:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

In the same way we can also find all the blocked sessions:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);


RELATED DOCUMENTS
-----------------

Note 1020008.6 SCRIPT FULLY DECODED LOCKING SCRIPT
Note 1054939.6 COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK

a fga + vpd example

DROP PACKAGE MAINKEY;
begin
DBMS_RLS.DROP_POLICY (
'TEST','UTIPOSMAINKEY','PROTECTMAINKEY');
end;
/
begin
DBMS_FGA.DROP_POLICY(
object_schema=>'TEST',
object_name=>'UTIPOSMAINKEY',
policy_name=>'AUDITMAINKEY');
end;
/

=========================

CREATE OR REPLACE PACKAGE mainkey AS
FUNCTION usercontrol (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY mainkey AS
FUNCTION usercontrol (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2
IS
D_predicate VARCHAR2 (2000);
BEGIN
D_predicate := 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST''';
RETURN D_predicate;
END usercontrol;
END mainkey;
/


BEGIN
DBMS_RLS.ADD_POLICY (
object_schema=>'TEST',
object_name=>'UTIPOSMAINKEY',
policy_name=>'PROTECTMAINKEY',
function_schema=>'TEST',
policy_function=>'MAINKEY.USERCONTROL',
statement_types=>'SELECT, UPDATE, DELETE, INSERT, INDEX',
update_check=>TRUE,
policy_type=>DBMS_RLS.STATIC);
END;
/

begin
DBMS_FGA.ADD_POLICY(
object_schema=>'TEST',
object_name=>'UTIPOSMAINKEY',
policy_name=>'AUDITMAINKEY',
audit_condition=>'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') <> ''TEST''',
statement_types=>'INSERT, UPDATE, DELETE, SELECT',
audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED);
end;
/

范式, NF, normal form

4.4 关系模式的范式


4.4.1 第一范式
考核要求：达到“领会”层次
知识点：1NF的定义


--------------------------------------------------------------------------------

1NF：第一范式—— 即关系模式中的属性的值域中每一个值都是不可再分解的值。
如果某个数据库模式都是第一范式的，则称该数据库模式是属于第一范式的数据库模式。

比如有一个关系 study={学号，课程}，若有这样几行记录： 学号
课程

99001
C语言

99002
数据结构

99003
C语言，数据结构

　

这时的第三条记录就表示本关系模式不是1NF的，因为课程中的值域还是可以分解的，它包括了两门课程。

如果改为：

学号
课程

99001
C语言

99002
数据结构

99003
C语言

99003
数据结构

　
则成为1NF的关系。


4.4.2 第二范式
考核要求：达到“领会”层次
知识点：2NF的定义


--------------------------------------------------------------------------------

如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键， 则称为第二范式模式。

首先温习、理解“非主属性”、“完全函数依赖”、“候选键”这三个名词的含义。
（1）候选键：可以唯一决定关系模式R中某元组值且不含有多余属性的属性集。
（2）非主属性：即非键属性，指关系模式R中不包含在任何建中的属性。
（3）完全函数依赖：设有函数依赖W→A，若存在XW，有X→A成立，那么称W→A是局部依赖，否则就称W→A是完全函数依赖。

在分析是否为第2范式时，应首先确定候选键，然后把关系模式中的非主属性与键的依赖关系进行考察， 是否都为完全函数依赖，如是，则此关系模式为2NF。
如果数据库模式中每个关系模式都是2NF的，则此数据库模式属于2NF的数据库模式。

比如有一个关系 study={学号，学生姓名，课程,成绩} 学号
姓名
课程
成绩

99001
Lily
C语言
91

99002
Rose
数据结构
82

99003
Keven
C语言
77

99003
keven
数据结构
86



其中，（学号，课程）为候选键；“成绩”对键的函数依赖为完全函数依赖，而“姓名”只依赖于“学号”， 对键的依赖为部分函数依赖。所以，该关系模式不符合2NF。如果将该 模式分解为以下两个关系：
　　　　　student={学号，姓名｝
　　　　　study={学号，课程，成绩}
则分解后的两个关系模式均为2NF


4.4.3 第三范式
考核要求：达到“领会”层次
知识点：3NF的含义


--------------------------------------------------------------------------------


如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。
传递依赖的含义： 在关系模式中，如果Y→X，X→A，且XY(X不决定Y)和AX(A不属于X)，那么Y→A是传递依赖。 Notice:要求非主属性都不传递依赖于候选键。

上一小节例子中student={学号，姓名｝，study={学号，课程，成绩}都是3NF




4.4.4 BCNF
考核要求：达到“领会”层次
知识点：BCNF的含义


--------------------------------------------------------------------------------

这个范式和第三范式有联系，它是3NF的改进形式。
若关系模式R是第一范式，且每个属性都不传递依赖于R的候选键。这种关系模式就是BCNF模式。
四种范式，可以发现它们之间存在如下关系：
BCNF3NF2NF1NF



1NF
↓ 消去非主属性对键的部分函数依赖
2NF
↓ 消去非主属性对键的传递函数依赖
3NF
↓ 消去主属性对键的传递函数依赖
BCNF





4.4.5 分解成BCNF模式集的算法
考核要求：达到“识记”层次
知识点：分解成BCNF模式集的算法


--------------------------------------------------------------------------------

对于任一关系模式，
(1)可找到一个分解达到3NF，且具有无损联接和保持函数依赖性。
(2)对于BCNF分解，则可以保证无损联接但不一定能保证保持函数依赖集。
（定理4.9）
（算法4.3）
无损联接分解成BCNF模式集的算法：
(1)置初值ρ={R};
(2)如果ρ中所有关系模式都是BCNF，则转(4);
(3)如果ρ中有一个关系模式S不是BCNF，则S中必能找到一个函数依赖集X→A有X不是S的键，且A不属于X，设S1=XA，S2=S-A，用分解S1,S2代替S，转(2)；
(4)分解结束。输出ρ。
Notice:重点在于(3)步，判断哪个关系不是BCNF，并找到X和A。
（以上内容可结合习题内容加以理解）

4.4.6 分解成3NF模式集
考核要求：达到“识记”层次
知识点：算法4.4


--------------------------------------------------------------------------------

(算法4.4)
(1)如果R中的某些属性在F的所有依赖的左边和右边都不出现，那么这些属性可以从R中分出去，单独构成一个关系模式。
(2)如果F中有一个依赖X→A有XA=R，则ρ={R}，转(4)
(3)对于F中每一个X→A，构成一个关系模式XA，如果F有有X→A1，X→A2...X→An,则可以用模式XA1A2...An代替n个模式XA1,XA2...XAn;
(4)w分解结束，输入ρ。
（以上内容可结合习题内容加以理解）

4.4.7 模式设计方法的原则
考核要求：达到“识记”层次
知识点：四项特性和三条原则


--------------------------------------------------------------------------------

关系模式R相对于函数依赖集F分解成数据库模式ρ={R1,R2...Rk}，一般具有下面四项特性：
(1)ρ中每个关系模式Ri上应具有某种范式性质(3NF或BCNF)
(2)无损联接性。
(3)保持函数依赖集。
(4)最小性，即ρ中模式个数应最少且模式中属性总数应最少。

一个好的模式设计方法应符合下列三条原则：
(1)表达性
(2)分离性
(3)最小冗余性


4.4.8 多值依赖
考核要求：达到“识记”层次
知识点：多值依赖的概念及其和函数依赖的区别


--------------------------------------------------------------------------------

函数依赖有效地表达了属性值之间多对一的联系，是最重要的一种数据依赖；而多值依赖是为了刻划属性值之间一对多的联系.


4.4.9 第四范式
考核要求：达到“识记”层次
知识点：第四范式的概念


--------------------------------------------------------------------------------

设R是一个关系模式，D是R上的多值依赖集合。如果D中成立非平凡多值依赖X→→Y时，X必是R的超键， 那么称R是第四范式（4NF）。
（定理4.13) 一个关系模式若属于4NF，则必然属于BCNF。
（算法4.5) ：了解一下。

Which indexes are used and how many times they are used?

Which indexes are used and how many times they are used?

col c1 heading object|Owner format a15
col c2 heading object|Name format a25
col c3 heading operation format a10
col c4 heading option format a15
col c5 heading index|Usage|Count format 999,999

select
p.object_owner c1,
p.object_name c2,
p.operation c3,
p.options c4,
count(1) c5
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like 'INDEX%' and
p.sql_id = s.sql_id
group by
p.object_owner,
p.object_name,
p.operation,
p.options
order by
1,2,3,4;

Ora-02437 When Creating PK Using Novalidate When Table Has duplicate data iPK Columns

Subject: Ora-02437 When Creating PK Using Novalidate When Table Has duplicate data iPK Columns
Doc ID: Note:264003.1 Type: PROBLEM
Last Revision Date: 02-JUL-2007 Status: MODERATED


The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
This problem can occur on any platform.

Errors
ORA 2437 cannot enable - primary key violated


Symptoms
Have a table with duplicate data in the PK columns.
Trying to create the PK with the novalidate option fails with an ora-02437.

Test Case to exhibit the problem:

CREATE TABLE chris (col1 INTEGER)
/
INSERT INTO chris VALUES (1)
/
INSERT INTO chris VALUES (2)
/
INSERT INTO chris VALUES (3)
/
INSERT INTO chris VALUES (4)
/
INSERT INTO chris VALUES (4)
/
ALTER TABLE chris ADD PRIMARY KEY (col1) NOVALIDATE
/
-- fails with error ORA-02437
ALTER TABLE chris ADD PRIMARY KEY (col1) DISABLE
/
-- ok
ALTER TABLE chris ENABLE NOVALIDATE PRIMARY KEY
/
-- again fails with error ORA-02437

Cause
This error occurs because it tries to create a unique index for the unique or primary
key index. The uniqueness is checked during the creation of the unique index.
Fix
- First, create a NON-UNIQUE index on the same fields that you want to
include in the constraint.
- Then add the unique or primary key constraint.

Thats is:

create index idx_chris_col1 on chris(col1);

After this give:

ALTER TABLE chris ADD PRIMARY KEY (col1) NOVALIDATE
/

OR

ALTER TABLE chris ENABLE NOVALIDATE PRIMARY KEY
/

References
Bug 544502 - Enabling Novalidate Not Deferrable Unique Constraint Creates Unique Index

index rebuild

The index is valid during rebuild or rebuild online if it is valid before rebuild.
Query started during rebuild or rebuild online can use this index.

How to find which indexes are used and which indexes are NOT used?

In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used.

select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%' and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;

advanced SQL tuning

Subject: TROUBLESHOOTING: Advanced Query Tuning
Doc ID: Note:163563.1 Type: TROUBLESHOOTING
Last Revision Date: 18-JUL-2006 Status: PUBLISHED

Purpose

The purpose of this article is to assist with the handling of query tuning issues. This article provides guidelines for dealing with tuning problems. It assumes that there really is a query tuning problem as opposed to some other issue that makes queries perform in a sub-optimal way. There are already plenty of articles on this topic. These will be referenced from this article where appropriate rather than trying to duplicate existing material. Obviously an article such as this cannot cover all possibilities but rather attempts to indicate what to look for.

Scope and Application

All

Resolving Query Tuning Issues

Queries can run slowly for any number of reasons. In order to try to determine where the problem lies and thus how to deal with it, this article has been divided into a number of sections containing pertinent questions to help the reader . Remember that initially you should be thinking towards providing at least a satisfactory workaround to the issue if the absolute root cause cannot be determined. Once a Workaround has been put in place and the pressure is off (to some degree at least), then the root cause can be determined (if necessary).

The points in this article are applicable to both the Rule (RBO) and Cost Based Optimizers (CBO)

Investigation

The first step must be to collect information about the problem itself:

Do you know which query is causing the problems?
If not, then Note 33089.1 contains information that may help identify problem queries.
The Tuning Pack in Oracle Enterprise Manager is also very useful for identifying problematic statements. There is also useful information in Chapter 6 of the Oracle9i Database Performance Guide and Reference.

Tools such as STATSPACK can also assist in determining which query is causing problems. See Note 94224.1

Information on this particular query
At this stage it is not recommended to get exhaustive information regarding this query. With experience, it is often possible to resolve issues with only small amounts of data, such as the query itself and the explain plan. Note 68735.1 Information required to diagnose a Query Performance Problem is an exhaustive list of the type of information required.



Diagnostics

Once the statement (or statements) have been determined then the next stage is to find the reason for the query running slow.

Has this query always run slowly? See Note 179668.1
Investigate the following areas
Explain plan for the query
In a perfect world, you would wish to gather the explain plan for both the 'slow' and 'not so slow' queries. Obviously this may not be possible but if this can be achieved it makes generation of a workaround much quicker because there is immediately a goal to aim at that has known and acceptable performance.

The best (standard) way of getting an explain plan is to use autotrace.
See Note 68735.1 regarding how to do this.

Once you have the explain plan, review it alongside the query text looking for anything unusual. The goal here is to identify any problem areas or anything that could be modified to run more efficiently.

Identify the optimizer in use (See Note 66484.1)
By far the most common issue encountered will be that the 'wrong' optimizer has been chosen and the system has not been set up to use it. This results in choices being made based on incorrect information.

Look for cost figures in the explain output to indicate that the CBO has been used
Missing cost figures do not necessarily indicate that the RBO has been used.
Look for CBO specific features (Hash Join, Index Fast Full Scan etc) as these confirm CBO usage
Once the optimizer has been determined, establish that the system is setup to work effectively with this optimizer. For example if CBO is in use then ensure that statistics are upto date and reflect the dataset.

Once the optimizer has been determined, establish that the system is setup to work effectively with this optimizer. For example if CBO is in use then ensure that statistics are up to date and reflect the dataset. See Statistics and analysis strategy below

Identify operations that may cause problems on your system:
Remember that these can only be general suggestions and many systems will work fine that do not match this structure.

Online Transaction Processing (OLTP) System

Full Table Scan
Hash or Sort merge joins
Index fast full scans or (large) index range scans
Parallel query
Nested loop joins with high cardinality outer table
Batch/Warehouse system

Nested loop joins
Index lookups
See the Query text section below for more general suggestions of things to look for.

Identify expensive operations:
There are 2 main categories of potentially expensive operations: High cardinality (large number of rows) and High cost. If the explain plan has steps that show large cardinality or cost figures then these may be areas where the biggest benefits of tuning can be reaped.

Statistics and analysis strategy
The CBO requires accurate statistics to enable it to work efficiently. By default no statistics are gathered on any objects. If the CBO is to be used effectively, statistics must be gathered. See Note 44961.1 for Analysis recommendations. A quick way to see if statistics are present is to select the NUM_ROWS column for your base table from dba_tables. If the column value is non-NULL then statistics have been gathered at some point. If it is suspected that the statistics may be old, then the LAST_ANALYZED column in DBA_TABLES can be used to determine when the table was last analyzed.

Query text
Review the query looking for any constructs which may cause you problems.
These come from experience but you are looking for:

Large INlists / OR statements Note 62153.1
Outer joins - There are a number of restrictions on the use of outer joins - see Oracle9i Database Performance Guide and Reference
Set operators (UNION etc) - Incorrect usage of set operations can be very inefficient
Partitioned Tables (or Views Oracle 7.3) - ensure that elimination is taking place as expected
No where clause or missing join predicates (potential for Cartesian Products)
Aggregate functions (cpu intensive functions applied to large rows sources can cause problems in some cases)
Sorting issues (These will typically be identified in combination with Explain plan) Note 67409.1 and Note 10577.1
Implicit type conversion - ensure that the datatypes of compared objects are the same or conversion overheads can occur
Any other 'strange' or unexpected constructs
Views, Inline views or Subqueries - is view merging or subquery unnesting taking place or not? Are predicates being passed in?
Finding an acceptable plan

Examination of the explain plan for the query can only give information on the query as it is running currently. If the query is running poorly then this may not help determine how the query could run more efficiently. Also this may be a new query where no acceptable plan has been determined yet so there is nothing to aim for. The following section gives suggestions for how a better plan may be found

Trace statistics
I/O and timing statistics (elapsed and cpu) can be very helpful in determining which part of a query is the root cause of the problem. Especially useful are actual row counts as these can be compared against the predicted row counts (expected row counts (cardinalities e.g. Card=12345) can be found in explain plans for CBO queries, actual row counts can be found in tkprof output or in the STAT lines from raw sqltrace/10046 output ). Any anomalies can then be investigated. For example, if the actual number of rows returned by a particular explain plan step differs significantly from the CBO cardinality estimates, then it is possible that this is a source of a bad plan choice. The cause of the incorrect statistics can be investigated and corrected. A fully detailed analysis is beyond the scope of this document.

Where excessive I/O has been identified, some potential causes are:

Full Table Scans and Index Fast Full Scans - this would be characterised by multi block i/o and likely waits for 'db file scattered read'. Note that Full table scans on the Right hand side (inner) of a nested loops join is unlikley to be performant since the table will be scanned once for every row on the Left hand side (outer).
Index range or Full Scan - If a large proportion (or indeed all) of an index is scanned then this can lead to excessive I/O (and CPU). This would be characterised by high single block i/o activity and likely waits for 'db file sequential read'. Join order can play a very significant part in this
Join order - It is advisable to choose a join order that eliminates as many rows as possible early in the query execution plan. If this does not occur then large volumes of data may be scanned that is later eliminated by the application of join predicates. If the join predicates can be applied earlier, then this volume of data may not need to be scanned and may reduce I/O (and CPU) requirements.
Excessive sort/hash areas - If sort/hash areas are excessive, the optimizer may start to choose plans which take advantage of these which may result in large amounts of I/O. For example, hash or sort merge joins may be chosen in preference to otjher methods. If parallel execution is also being used then this is even more likely.

Where excessive CPU usage has been identified, some potential causes are:

Index range or Full Scan - If a large proportion (or indeed all) of an index is scanned then this can lead to excessive CPU since much of the index may be cached. Join order can play a very significant part in this
Join order - As with I/O, it is advisable to choose a join order that eliminates as many rows as possible early in the query execution plan. If this does not occur then large volumes of data may be scanned that is later eliminated by the application of join predicates. If the join predicates can be applied earlier, then this volume of data may not need to be scanned and may reduce CPU (and I/O) requirements.
Excessive sort/hash areas - Sorting is very CPU intensive.
Nested Loops joins with high number of rows in the left hand side (outer table) will result in a large number of lookups on the Right hand side (inner table). The repeated lookup process is CPU intensive.

Break Query down into component parts
Most complex queries are a combination of many simpler queries. If a query is broken into its constituent parts and there are then optimized a good indication of the overall (desired) plan can be built up by combining each tuned section. Remember that in some cases significant benefits can be gained from the combination of steps and so even better performance can be obtained from a combined query than a number of standalone queries. Generally it is much easier to tune a simple query than a complex one.

Application knowledge
It may be that the application designer has information about how a particular query should run based on the application design. If that is the case then that can be used that to compare the plan that is generated with the expected (designed) access path. For example, if it is felt that a query should read table A with a full table scan and then use the information to drive an index lookup on table B with a nested loop join, then check this against what it was designed to do. If it does not match up then it may perform more acceptably if it is forced to use the designed access path, perhaps by using hints..

If specific application knowledge is not available then similar results may be attainable by considering the table sizes and organizing them so that the most rows are eliminated as early as possible in the query. If rows are eliminated early, then they do not have to be considered later and therefore may improve the overall query performance.

Trial and Error
Often trying a plan can give you a pointer as to a potential better plan. For example if a query performs badly under the RBO, analyze the tables and use CBO. It may be that the plan produced is a good one and can then be used in your environment. Even if the method used to create the plan is not feasible for use in the real environment, the fact that it actually produces the required output in a timely manner may be useful in as much as it proves that the operations are possible.

Summary: Compare real figures with optimizer estimates, break the query down into its constituent pieces and tune these individually, utilise application knowledge to suggest improvements in join orders and utilise trial and error to test potential plan choices.

Solutions

By looking at the diagnostics above, hopefully the root cause of the problem can be determined. The articles below cover most common solution areas.

Query does not use an index See Note 67522.1 Why is my index not used?
My hints are being ignored See Note 69992.1 Why is my hint ignored?
Changed oracle version and now query(ies) are slow See Note 160089.1 Why are my queries slow after upgrading my database?
Query produces wrong results See Note 150895.1 Handling Wrong Results Issues
Workarounds

Often the provision of a workaround is enough to diffuse a serious situation. It is worth determining if a workaround is applicable early on so that you can formulate your strategy around that. Even the knowledge that a workaround in not applicable can be useful as it can concentrate the mind on other solutions to the problem. Also creating a workaround can actually confirm that as solution is viable.

Is a Workaround going to be a valid solution to the problem?
Usually the most effective workaround is to use a hint. Can the query be hinted to force the desired plan?
See Note 29236.1 Hints Quick Reference or
Note 69992.1 Why hints are ignored.

What to do if the query cannot be modified?
For more suggestions and what to do if the query itself cannot be modified because it is generated or fixed code, or for any other reason then see Note 122812.1



RELATED DOCUMENTS

Note 233112.1 START HERE> Diagnosing Query Tuning Problems Using a Decision Tree

Note 372431.1 TROUBLESHOOTING: Tuning a New Query
Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Note 122812.1 Tuning Suggestions When Query Cannot be Modified
Note 67522.1 Diagnosing Why a Query is Not Using an Index

Note 214106.1 Using TKProf to compare actual and predicted row counts

photo

I forgot my root password, how can I get into my system?

Issue:
I forgot my root password, how can I get into my system? Resolution:You can change your root password from single user mode or rescue mode. Getting into single use mode depends on your bootloader:

GRUB
Booting into single user mode using GRUB is accomplished by editing the kernel line of the boot configuration. This assumes that either the GRUB boot menu is not password protected or that you have access to the password if it is.

If the GRUB boot menu is password protected and you do not have access to the password, then you will need to use a rescue disk to boot the system. Follow the instructions given by the rescue disk boot process to recover your installation and then chroot to your system image (usually accomplished by issuing the command chroot /mnt/sysimage). From this point you should be able to use the passwd to change the root password of the system.

At the boot prompt, select the kernel that you wish to boot with and press 'e' (for edit). You will now be taken to a screen where you can edit the boot parameters. Move the cursor to the kernel line and press 'e' again. Now append an 'S' to the end of the line, press Return, and then 'b' (for boot). The system will now start in single user mode and you can change the root password using the passwd command.

LILO
When the system comes to the LILO: prompt, type linux single . When you get the # prompt you will need to type passwd root. This will update the password to a newer one. At this point you can type exit and your system should return to the boot sequence. Alternatively, you can reboot your system with the shutdown -r now or reboot commands. The system should boot up normally. You can now use your new root password to gain root access.

If LILO is configured to not wait at the boot menu (timeout value in /etc/lilo.conf set to 0) you can still halt the boot process by pressing any key in the split second before LILO boots the kernel.

Database is Shown with Status 'Pending' in Grid Control

Subject: Problem: Database is Shown with Status 'Pending' in Grid Control
Doc ID: Note:312797.1 Type: PROBLEM
Last Revision Date: 26-MAR-2007 Status: PUBLISHED

In this Document
Symptoms
Cause
Solution



--------------------------------------------------------------------------------



Applies to: Enterprise Manager Grid Control - Version: 10.1.0.2 to 10.1.0.4
This problem can occur on any platform.
SymptomsDatabase is shown with status pending in Grid Control.
CauseThe DBSNMP user on the databases is locked and the dbsnmp password has not been configured
for the database. SolutionTo implement the solution, please execute the following steps:

1. Connect to database using sqlplus as sysdba and run the following command:



alter user DBSNMP account unlock;

2. Configure DBSNMP passwords for each database:


In Grid Control go to Management System > Agents and click on the link for the agent of the database
Select the database from the list of targets and click on the 'Configure' button
Specify the correct dbsnmp password in the 'Monitor Password' field
Click on the 'Test Connection' button.
You should see a 'Success' indication at the top of the page. Click Next and complete the Configure Database dialog appropriate to your needs.


3. Wait for 4 minutes before checking the status of the newly configured database (It should take a maximum of 4 minutes to determine the status of the database)

Keywords'GRID~CONTROL' 'CONFIGURE~DATABASE' 'LOCK' 'DBSNMP'
--------------------------------------------------------------------------------

Grid Control install failed with Ora-30041: Cannot grant quota on the tablespace

Subject: Grid Control install failed with Ora-30041: Cannot grant quota on the tablespace
Doc ID: Note:329375.1 Type: PROBLEM
Last Revision Date: 27-JUL-2005 Status: MODERATED

In this Document
Symptoms
Cause
Solution



--------------------------------------------------------------------------------


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to:
Enterprise Manager Grid Control - Version: 10.1.0.3.0
This problem can occur on any platform.

Symptoms
Getting error ORA-30041: Cannot grant quota on the tablespace when installing Grid Control 10.1.0.3.0 using an existing 10.2 database.
Cause
10gR2 databases are not supported to be used as the Oracle Management Repository for Grid Control release 1.
Solution
To implement the solution, please execute the following steps:

1. Install the Grid Control using the 'Enterprise Manager 10g Grid Control Using a New database' option

OR

2. Install the Grid Control using the 'Enterprise Manager 10g Grid Control Using an Existing Database' option with a 9.2.0.5+ or 10.1.0.3+ database but not a 10gR2 database.

Errors
ORA-30041 Cannot grant quota on the tablespace

If you grid is 10g Release 3 (10.2.0.3.0), ensure the database release should be 9.2.0.6 and later, or 10.1.0.4 and later.

How to Clean Up a Failed Grid Control Installation

Subject: How to Clean Up a Failed Grid Control Installation
Doc ID: Note:434858.1 Type: HOWTO
Last Revision Date: 04-JUN-2007 Status: MODERATED

In this Document
Goal
Solution
Option 1: Configuration Assistant
Option 2: Full Clean up of Failed Installation



--------------------------------------------------------------------------------


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to:
Enterprise Manager Grid Control - Version: 10.2.0.0
Information in this document applies to any platform.

Goal
The following provides 2 different options on how to progress with a failed installation for Grid Control.

IMPORTANT NOTE: Please make sure that any issues identified has been corrected before re-attempting to complete installation
Solution
Option 1: Configuration Assistant
If partial installation has failed, it is possible to manually run the following Configuration Assistants rather than performing a re-installation.

NOTE: In some circumstances a full reinstallation process may be required. Please see Option 2, for procedure on full cleanup and re-installation.

OMS:

runConfig.sh ORACLE_HOME= ACTION=Configure MODE=Perform

Please see Note 362217.1 for further details on using runConfig.sh

Agent:

/bin/agentca -f

Option 2: Full Clean up of Failed Installation
1. Abort the runInstaller wizard.

2. Manually kill off any rogue/residue processes still running from failed installation:

Windows:

Kill any opmn, agent processes owned by oracle via Task Manager and then perform reboot.

Unix:

ps -ef|grep opmn ps -ef|grep agent |grep oracle kill

3. Cleanup Repository as per

4. Execute runInstaller go to:

Installed Products > Select items "oms" and "agent" > Click Remove

NOTE: If the option chosen for initial installation was for "New Database" (default), verify that database is running ok and alert.log does not report any issues or errors. Keep this db10g installation and on next attempt of re-installation rather than choosing "New Database", choose the "Existing Database" option.
6) Remove subdirectories for oms10g and agent10g

rm -rf oms10g/ agent10g/

7) Remove Oracle installation subdirectories under /tmp

Unix only:

rm -rf /tmp/em rm -rf /tmp/OraInstall*

8) Then perform a re-installation:

runInstaller




++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Subject: How To Drop, Create And Recreate DB Control In A 10g Database
Doc ID: Note:278100.1 Type: BULLETIN
Last Revision Date: 05-JUN-2007 Status: PUBLISHED

In this Document
Purpose
Scope and Application
How To Drop, Create And Recreate DB Control In A 10g Database
DB Control options:
A. Delete DB Control Objects:
B. Create DB Control Objects
C. Recreate/ReConfig DB Control



--------------------------------------------------------------------------------



Applies to:
Enterprise Manager Grid Control - Version: 10.1 to 10.2
Information in this document applies to any platform.
DBConsole
Purpose
This article provides detailed instructions on how to 1) create, 2) drop and 3)recreate the repository and configuration files for the DB Control application used to manage a single 10g Database.


Scope and Application
The steps in this article are written for a DBA or System Administrator who needs to create, drop or reconfigure the DB Control Application.

The format of the document will include steps for both 10.1 and 10.2 EMCA because the commands changed between the two releases.
How To Drop, Create And Recreate DB Control In A 10g Database
DB Control options:


A. Delete DB Control Objects:
Option 1. Delete DB Control Configuration Files using EMCA scripts
Option 2. Delete DB Control Configuration Files Manually:
Option 3. Delete DB Control Repository Objects using RepManager
Option 4. Delete DB Control Repository Objects Manually
Option 5. Delete DB Control Configuration Files and Repository Objects using EMCA


Option 1. Delete DB Control Configuration Files using EMCA scripts:

For DB Control 10.1.x, run the command: /bin/emca -x For DB Control 10.2.x, run the command: bin/emca -deconfig dbcontrol db

Option 2. Delete DB Control Configuration Files Manually:

Remove the following directories from your filesystem:
/
/oc4j/j2ee/OC4J_DBConsole__


NOTE:
On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it


Alternatively on Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete '

- where is the DB Control service name (typically: OracleDBConsole)

Also available from Microsoft is the delsrv.exe command. (Free download from Microsoft)



Option 3. Delete DB Control Repository using RepManager:
This option is not as complete as the other options. You may find that dropping the repository using the commandline options is a better solution. Also note, RepManager is not used to create a DB Control Repository.

In both 10g R1 and R2 run: /sysman/admin/emdrep/bin/RepManager -action drop

Option 4. Delete DB Control Repository Objects Manually
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:SQL> SHUTDOWN
IMMEDIATE;SQL> STARTUP RESTRICT;SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;SQL> EXEC sysman.setEMUserContext('',5);SQL>
REVOKE dba FROM sysman;SQL> DECLARECURSOR c1 ISSELECT owner, synonym_name nameFROM dba_synonymsWHERE table_owner
= 'SYSMAN';BEGINFOR r1 IN c1 LOOPIF r1.owner = 'PUBLIC' THENEXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;ELSEEXECUTE
IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;END IF;END LOOP;END;/SQL> DROP USER mgmt_view CASCADE;SQL> DROP ROLE mgmt_user;SQL> DROP USER sysman CASCADE;SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Note: The above will completely delete the DB Control repository from the database; under certain circumstances (e.g. you want to recreate the repository later on) the following statements may be sufficient to remove the repository:


Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:SQL> drop user
sysman cascade;SQL> drop role MGMT_USER;SQL> drop user MGMT_VIEW cascade;SQL> drop public synonym MGMT_TARGET_BLACKOUTS;SQL> drop public synonym SETEMVIEWUSERCONTEXT;

Option 5. Delete DB Control Configuration Files and Repository Objects using EMCA

For DB Control 10.1.x, dropping both the configuration files and the repository objects is a two step process. Run the following two commands:

/bin/emca -x /sysman/admin/emdrep/bin/RepManager -action drop

For DB Control 10.2.x, both configuration files and repository objects can be deleted with a single command. Run the command:

/bin/emca -deconfig dbcontrol db -repos drop


B. Create DB Control Objects
Option 1. Create the DB Control Configuration Files
Option 2. Create the DB Control Repository Objects and Configuration Files


Option 1. Create the DB Control Configuration Files
To create only the DB Control configuration files, skipping the repository creation (this would be done for instance if you dropped only the files and left the repository in place):

For DB Control 10.1.x, run the command: /bin/emca -rFor DB Control 10.2.x, run the command: /bin/emca -config dbcontrol db


Option 2. Create the DB Control Repository Objects and Configuration Files

For DB Control 10.1.x, run the command: /bin/emcaFor DB Control 10.2.x, run the command: bin/emca -config dbcontrol db -repos create


C. Recreate/ReConfig DB Control
Option 1. Recreate the DB Control Configuration Files only (leave Repository intact)
Option 2. Recreate the DB Control Configuration Files and Repository

In 10.2, the EMCA commands can be used to reconfigure the existing installs without removing them first.

Option 1. Recreate the DB Control Configuration Files only (leave Repository intact):
For DB Control 10.2.x, run the command:

/bin/emca -config dbcontrol db

Option 2. Recreate the DB Control Configuration Files and Repository
For DB Control 10.2.x, run the command:

/bin/emca -config dbcontrol db -repos recreate

For additional information on EMCA commandline options, please see the Oracle Enterprise Manager 10g Advanced Configuration Guide or see Note 330130.1 Overview Of The EMCA Commands Available for DB Control 10.2


Keywords
'EMCA' 'REPMANAGER' 'DB~CONTROL' 'DBCONSOLE'

How to use RMAN to create a physical standby database

How to use RMAN to create a physical standby database

文書番号 125354 最終更新日 2007-07-04 公開区分 契約顧客のみ
製品名(バージョン)[コンポーネント] Oracle Server - Enterprise Edition (9.2.0 - ) [Stanby・Database/Dataguard]
プラットフォーム(バージョン) すべてのプラットフォーム ( - )
関連文書 80299 　 66875 　

概要 [DataGuard] RMAN を使用したフィジカル・スタンバイの作成方法
内容:
[概要]
RMAN を使用した Data Guard 環境(フィジカル・スタンバイ)の構築方法について
説明します。


[対象リリース]
Oracle Database 10g Release2(10.2.0)
Oracle Database 10g Release1(10.1.0)
Oracle9i Database Release2(9.2.0)


[対象プラットフォーム]
すべてのプラットフォーム


[詳細]
手順の概要は以下のようになります。

1. プライマリ側のデータベースの作成
2. プライマリ・データベースをアーカイブログモードに変更
3. プライマリ・データベースのバックアップの取得
4. 取得したバックアップのスタンバイ側へのコピー
5. スタンバイでの設定
6. スタンバイでのバックアップのリストア
7. スタンバイの管理リカバリモードへの変更

詳細な手順は以下のようになります。

1. プライマリ側のデータベースの作成

スタンバイは意識せず、通常のシングルの環境と同様に DBCA を使用して作成
してください。このとき、リスナーの設定も行ってください。

2. プライマリ・データベースをアーカイブログモードに変更

以下の KROWN を参考にしてください。

KROWN:67982 自動アーカイブログモードへの変更方法
2.2 データベースをアーカイブログモードに変更(OPS,RAC環境の場

R10.1 以降では、log_archive_start のパラメータの設定は不要です。
アーカイブログの出力先ディレクトリは、log_archive_dest_1 に設定します。

3. プライマリ・データベースのバックアップの取得

RMAN(Recovery Manager) を使用してプライマリデータベースのバックアップ
を取得します。

% rman target / nocatalog
RMAN> backup database;
RMAN> backup current controlfile for standby;
RMAN> sql 'alter system archive log current';
RMAN> backup archivelog all;

4. 取得したバックアップのスタンバイ側へのコピー

手順 3 により $ORACLE_HOME/dbs 配下に取得したバックアップのファイルが
出力されています。バックアップのファイル名は、RMAN でのコマンド実行時
のログや、RMAN から以下のコマンドを実行することで確認が可能です。

RMAN> list backup;

上記のファイルをスタンバイ側の $ORACLE_HOME/dbs 配下にコピーします。

5. スタンバイでの設定

スタンバイ側で以下の設定を行います。

1) 初期化パラメータファイル
2) tnsnames.ora (プライマリとスタンバイの両方に接続できる設定をします)
3) listener.ora
4) パスワードファイル
5) サービス(Windows の場合のみ、oradim コマンドで作成します)

なお、以下の文章を参考にしてください。

KROWN:66875 初心者のためのDataGuard(Physical Standby)環境作成の手順
KROWN:80299 [10g Data Guard] Physical Standby Datbase 環境の構築方法

6. スタンバイでのバックアップのリストア

取得していたバックアップをスタンバイ側にリストアします。
スタンバイ側で以下のようなコマンドを実行します。

% sqlplus "/as sysdba"
SQL> startup nomount
SQL> exit

% rman target sys/@<プライマリに接続するサービス名> auxiliary /
RMAN> duplicate target database for standby dorecover;

なお、プライマリとスタンバイでディレクトリ構成が同じ場合(同じパス設定
でデータファイルを作成する場合)、RMAN でのコマンドは NOFILENAMECHECK
オプションを指定した以下のようなものを使用してください。

RMAN> duplicate target database for standby nofilenamecheck dorecover;

7. スタンバイの管理リカバリモードへの変更

以下のコマンドにより、スタンバイを管理リカバリモードに変更します。

SQL> recover automatic standby database;


[参照情報]
マニュアル
『 Oracle Data Guard 概要および管理 10g リリース2（10.2）』
F Recovery Manager を使用したスタンバイ・データベースの作成

『 Oracle Data Guard 概要および管理 10g リリース1（10.1）』
D Recovery Manager を使用したフィジカル・スタンバイ・データベースの作成

『 Oracle9i Recovery Manager ユーザーズ・ガイド リリース2（9.2）』
13章 Recovery Manager によるスタンバイ・データベースの作成

KROWN:66875 初心者のためのDataGuard(Physical Standby)環境作成の手順
KROWN:80299 [10g Data Guard] Physical Standby Datbase 環境の構築方法

[更新履歴]
2007/07/04 本文書を公開

MAA - Creating a Single Instance Physical Standby for a RAC Primary

Subject: MAA - Creating a Single Instance Physical Standby for a RAC Primary
Doc ID: Note:387339.1 Type: WHITE PAPER
Last Revision Date: 14-AUG-2006 Status: PUBLISHED

MAA / Data Guard 10g Setup Guide –

Creating a Single Instance
Physical Standby for a RAC Primary


Oracle Maximum Availability Architecture White Paper

April 2006



Overview
Oracle Maximum Availability Architecture (MAA) [1] is Oracle's best practices blueprint based on proven Oracle high-availability technologies and recommendations. The goal of MAA is to remove the complexity in designing the optimal high-availability architecture.

Published as part of the MAA series of white papers, this paper focuses on creating a single instance physical standby database for a RAC primary database. This document assumes that there is an existing RAC database and you want to implement Data Guard by adding a physical standby database to the configuration. The end configuration for this document is a RAC primary database with a single-instance physical standby database. The steps outlined in this document use SQL*Plus, apply to both Oracle Database 10g Release 1 and Oracle Database 10g Release 2, and they assume using ASM/OMF, and that the software and ASM instance on the standby host have already been installed/created.

The example used in this document has the database unique name of the RAC database as CHICAGO. The instance names of the two RAC instances are CHICAGO1 (on node chicago_host1) and CHICAGO2 (on node chicago_host2). The database unique name of the single-instance physical standby database is BOSTON (on node boston_host1).

This document includes the following tasks:

Task 1: Gather Files and Perform Back Up
Task 2: Configure Oracle Net on the Physical Standby
Task 3: Create the Physical Standby Instance and Database
Task 4: Configure the Primary Database for Data Guard
Task 5: Verify Data Guard Configuration
This document assumes that the following conditions are met:

The primary RAC database is using ASM.
The standby host has an existing Oracle software installation.
The physical standby database storage uses an existing ASM instance.
The primary and standby databases are using a flash recovery area.
Oracle Managed Files (OMF) is used for all storage.


Task 1: Gather Files and Perform Back Up

On the primary node, create a staging directory. For example:
[oracle@chicago_host1 oracle]$ mkdir -p /opt/oracle/stage


Create the same exact path on the standby host:
[oracle@boston_host1 oracle]$ mkdir -p /opt/oracle/stage


On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. For example:
SQL> CREATE PFILE='/opt/oracle/stage/initCHICAGO.ora' FROM SPFILE;


On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory. For example:
[oracle@chicago_host1 stage]$ rman target /
RMAN> backup device type disk format '/opt/oracle/stage/%U' database plus archivelog;
RMAN> backup device type disk format '/opt/oracle/stage/%U' current controlfile for standby;


Place a copy of the listener.ora, tnsnames.ora, and sqlnet.ora files into the staging directory. For example:
[oracle@chicago_host1 oracle]$ cp $ORACLE_HOME/network/admin/*.ora /opt/oracle/stage


Copy the contents of the staging directory on the RAC node to the staging directory on the standby host. For example:
[oracle@chicago_host1 oracle]$ scp /opt/oracle/stage/* oracle@boston_host1:/opt/oracle/stage

Task 2: Configure Oracle Net SERVICES on the Standby

Copy the listener.ora, tnsnames.ora, and sqlnet.ora files from the staging directory on the standby host to the $ORACLE_HOME/network/admin directory on the standby host.
Modify the listener.ora file on the standby host to contain the hostname of the standby host.
Modify the tnsnames.ora file on each node, including the primary RAC nodes and standby host, to contain all primary and standby net service names. In this example, each tnsnames.ora file should contain all three of the net service names in the following table:
Example Entries in the tnsnames.ora Files

Primary Net Service Names Standby Net Service Name
CHICAGO1_SERV = (DESCRIPTION = (ADDRESS =
(PROTOCOL = TCP)
(HOST =chicago_host1vip)
(PORT = 1521))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME =CHICAGO)
(INSTANCE_NAME =CHICAGO1)
)
)
BOSTON =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST =boston_host1)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BOSTON)
)
)

CHICAGO2_SERV =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = chicago_host2vip)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CHICAGO)
(INSTANCE_NAME = CHICAGO2)
)
)



Task 3: Create the Physical Standby Instance and Database

To enable secure transmission of redo data, make sure the primary and physical standby databases use a password file, and make sure the password for the SYS user is identical on every system. For example:
$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwBOSTON password=oracle


The naming and location of the password file varies on different platforms. See “Creating and Maintaining a Password File” in the Oracle Database Administrator’s Guide for more information.

Copy and rename the primary database PFILE from the staging area on the standby host to the $ORACLE_HOME/dbs directory on the standby host. For example:
[oracle@boston_host1 stage]$ cp initCHICAGO.ora $ORACLE_HOME/dbs/initBOSTON.ora


Modify the standby initialization parameter file copied from the primary node to remove RAC parameters and to include Data Guard parameters as illustrated in the following table:
Initialization Parameter Modifications

arameter
Category
Before After

RAC Parameters *.cluster_database=true
*.db_unique_name=CHICAGO
CHICAGO1.instance_name=CHICAGO1
CHICAGO2.instance_name=CHICAGO2
CHICAGO1.instance_number=1
CHICAGO2.instance_number=2
CHICAGO1.thread=1
CHICAGO2.thread=2
CHICAGO1.undo_tablespace=UNDOTBS1
CHICAGO2.undo_tablespace=UNDOTBS2
*.remote_listener=LISTENERS_CHICAGO
CHICAGO1.LOCAL_LISTENER=LISTENER_CHICAGO_HOST1
CHICAGO2.LOCAL_LISTENER=LISTENER_CHICAGO_HOST2
*.cluster_database=false
*.db_unique_name=BOSTON
*.instance_name=BOSTON
*.thread=1
*.undo_tablespace=UNDOTBS1


Data Guard Parameters
*.log_archive_config='dg_config=
(BOSTON,CHICAGO)'
*.log_archive_dest_2='service=CHICAGO1_SERV
valid_for=(online_logfiles,primary_role)
db_unique_name=CHICAGO'
*.db_file_name_convert='+DATA/CHICAGO/',
'+DATA/BOSTON/','+RECOVERY/CHICAGO',
'+RECOVERY/BOSTON'
*.log_file_name_convert='+DATA/CHICAGO/',
'+DATA/BOSTON/','+RECOVERY/CHICAGO',
'+RECOVERY/BOSTON'
*.standby_file_management=auto
*.fal_server='CHICAGO1_SERV','CHICAGO2_SERV'
*.fal_client='BOSTON'
*.service_names='BOSTON'


Other parameters *.background_dump_dest=
/opt/oracle/admin/CHICAGO/bdump
*.core_dump_dest=
/opt/oracle/admin/CHICAGO/cdump
*.user_dump_dest=
/opt/oracle/admin/CHICAGO/udump
*.audit_file_dest=
/opt/oracle/admin/CHICAGO/adump
*.db_recovery_dest=’+RECOVERY’
*.log_archive_dest_1 =
'LOCATION=+DATA/CHICAGO/'
*.dispatchers=CHICAGOXDB

*.background_dump_dest=
/opt/oracle/admin/BOSTON/bdump
*.core_dump_dest=
/opt/oracle/admin/BOSTON/cdump
*.user_dump_dest=
/opt/oracle/admin/BOSTON/udump
*.audit_file_dest=
/opt/oracle/admin/BOSTON/adump
*.db_recovery_dest=’+RECOVERY’
*.log_archive_dest_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.dispatchers=BOSTONXDB





For more information about these initialization parameters, see Chapter 13, “Initialization Parameters” in Oracle Data Guard Concepts and Administration manual.

If you are using an SPFILE instead of an initialization parameter file, then see the “Managing Initialization Parameters Using a Server Parameter File” section in the Oracle Database Administrator’s Guide for instructions on managing an SPFILE.

Connect to the ASM instance on the standby host, and create a directory within the DATA disk group that has the same name as the DB_UNIQUE_NAME of the physical standby database. For example:
SQL> alter diskgroup data add directory '+DATA/BOSTON';


Connect to the physical standby database, with the standby in the IDLE state, and create an SPFILE in the standby DATA disk group:
SQL> CREATE SPFILE='+DATA/BOSTON/spfileBOSTON.ora' FROM PFILE='?/dbs/initBOSTON.ora';


In the $ORACLE_HOME/dbs directory on the standby host, create a PFILE that is named initoracle_sid.ora that contains a pointer to the SPFILE. For example:
[oracle@boston_host1 oracle]$ cd $ORACLE_HOME/dbs
[oracle@boston_host1 dbs]$ echo "SPFILE='+DATA/BOSTON/spfileBOSTON.ora'" > initBOSTON.ora


Create the dump directories on the standby host as referenced in the standby initialization parameter file. For example:
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/bdump
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/cdump
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/udump
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/adump


After setting up the appropriate environment variables on the standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the physical standby database instance without mounting the control file.
SQL> STARTUP NOMOUNT


From the standby host, duplicate the primary database as a standby into the ASM disk group. For example:
$ rman target sys/oracle@CHICAGO1_SERV auxiliary /
RMAN> duplicate target database for standby;


Connect to the physical standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as the primary database online logs. The recommended number of standby redo logs is:
(maximum # of logfiles +1) * maximum # of threads


This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;


You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;


You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;


See the “Configure a Standby Redo Log” section in Oracle Data Guard Concepts and Administration manual for more information.

Start managed recovery and real-time apply on the standby database:
SQL> ALTER DATABASE recover managed standby database using current logfile disconnect;

Task 4: Configure The Primary Database For Data Guard
Configure the primary database initialization parameters to support both the primary and standby roles.
*.log_archive_config='dg_config=(BOSTON,CHICAGO)'
*.log_archive_dest_2='service=BOSTON
valid_for=(online_logfiles,primary_role)
db_unique_name=BOSTON'
*.db_file_name_convert='+DATA/BOSTON/',’+DATA/CHICAGO/', ’+RECOVERY/BOSTON’,’+RECOVERY/CHICAGO’
*.log_file_name_convert='+DATA/BOSTON/',’+DATA/CHICAGO/', ’+RECOVERY/BOSTON’,’+RECOVERY/CHICAGO’
*.standby_file_management=auto
*.fal_server='BOSTON'
CHICAGO1.fal_client='CHICAGO1_SERV'
CHICAGO2.fal_client='CHICAGO2_SERV'
*.service_names=CHICAGO

For more information about these initialization parameters, see Chapter 13, “Initialization Parameters” in the Oracle Data Guard Concepts and Administration manual.

If you are using an SPFILE instead of an initialization parameter file, then see the “Managing Initialization Parameters Using a Server Parameter File” section in the Oracle Database Administrator’s Guide for instructions on managing an SPFILE.

Note that all the parameters listed above can be dynamically modified with the exception of the standby role parameters log_file_name_convert and db_file_name_convert. It is recommended to set the parameters with “scope=spfile” so that they can be put into effect upon the next role change.

Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. Because this example has two online redo logs for each thread, three standby redo logs are required for each thread.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;


SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;


These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;


You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;


You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;


See the “Configure a Standby Redo Log” section in Oracle Data Guard Concepts and Administration manual for more information.


Task 5: Verify Data Guard Configuration

On the physical standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#;


On the primary database, issue the following SQL statement to force a log switch and archive the current online redo log file group:
SQL> alter system archive log current;


On the physical standby database, query the V$ARCHIVED_LOG view to verify that the redo data was received and archived on the standby database:
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#;



References

Oracle Maximum Availability Architecture website on OTN
http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm

wrap utility

Wrap utility can encrypt sql files.
People usually use ".plb" to identify encrypted files.
The objects created by ".plb" files are also encrypted, for example, in the TEXT column of dba_resource.

The ".plb" file may lead to ORA-4030 in some oracle versions if it is corrupted.

See below for details.

Subject: ORA-04030 (PLS non-lib hp,PAR.C:parchk) While Running a Wrapped Script
Doc ID: Note:280168.1 Type: PROBLEM
Last Revision Date: 18-MAY-2007 Status: MODERATED


The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 9.2.0.5
This problem can occur on any platform.

Errors
ORA-04030

Symptoms
A wrapped plsql script is being run and we get the following error message:

CREATE OR REPLACE PROCEDURE p5 wrapped
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 659072 bytes (PLS non-lib hp,PAR.C:parchk:p

The plsql is wrapped and is not the ASCII source code.
Cause
Corrupted wrapped code. To see this we can use a small testcase.

1] We create a plsql script which has the following code in it:

create or replace procedure p1
as
x number;
begin
select count(*)
into x
from user_source;
end;
/

2] By running e.sql we can create the procedure fine.

SQL> @c:\e.sql
Procedure created.

3] We will wrap the plsql script.

SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

D:\Ora92\BIN>wrap iname=c:\e.sql oname=c:\a.plb

PL/SQL Wrapper: Release 9.2.0.4.0- Production on Tue Jul 27 20:50:59 2004
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing c:\e.sql to c:\a.plb

D:\Ora92\BIN>exit

SQL> @c:\a.plb
Procedure created.

So the wrapped code is fine now and we are able to create the procedure

4] We will corrupt the wrapped code a.plb (opened the a.plb file with notepad/vi editor and added or deleted some characters)

SQL> set lines 5000
SQL> @c:\a.plb
CREATE OR REPLACE PROCEDURE p5 wrapped
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 659072 bytes (PLS non-lib hp,PAR.C:parchk:p

This has been reproduced on:
o 9.2.0.4 on Windows2000
o 8.1.7.4 on Linux
o 9.2.0.5 on Linux

Fix

The error message (memory related) is somewhat misleading. If we find the ORA-04030 with such an argument (PLS non-lib hp,PAR.C:parchk:) then we should check if the wrapped code is ok or not. We can either rewrap it or can possibly run the unwrapped code.

Note: On 10g DB the error message is proper and instead of giving a ORA-04030 it gives a relevent PLS- error :

SQL> @a.plb
Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -------------------------------------------
0/0 PLS-00801: internal error [pkg_read:ver]

This has been tested on 10.1.0.2 on Windows and Linux.

Manual Installation/Registration of Java Plug-in—Linux (for mozilla)

Manual Installation/Registration of Java Plug-in—Linux(for mozilla)

--------------------------------------------------------------------------------


To install the Java Plug-in follow these steps.

Note:

Only Mozilla can be registered with Java Plug-in.
Only one Java Plug-in can be registered at a time. When you want to use a different version, unregister any other version and register the new one.
Example:

Suppose for JDK 5.0 you created a symbolic link in the Mozilla plugins directory to:

~/jdk1.5.0/jre/plugin/i386/ns7/libjavaplugin_oji.so

Then cd to the Mozilla plugins directory and remove the link as shown below:

rm libjavaplugin_oji.so

Also check that there are no links in the Mozilla components directory.


1. Uninstall any previous installations of Java Plug-in.

rm -fr $HOME/.netscape/java
rm $HOME/.netscape/plugins/javaplugin.so
rm $HOME/.netscape/plugins/libjavaplugin.so

One or more of these files may exist on your machine, depending on which previous versions of Java Plug-in you have installed.

2. For the Mozilla browser create a symbolic link to the Plug-in in the Mozilla plugins directory.

Create a symbolic link to the Java Plug-in libjavaplugin.so file in the Mozilla plugins directory.

cd /plugins (usually /usr/lib/mozilla/plugins)
ln -s /plugin/i386//libjavaplugin_oji.so . (if you haven't installed jre, you can use jre installed with oracle instead, which is usually in $ORACLE_HOME/jre/1.4.2/plugin/i386/ns610-gcc32/)

In the ln command line above, use ns7-gcc29 if Mozilla was compiled with gcc2.9.

Be sure to include the period at the end.

3. Start the Mozilla browser, or restart it if it is already up.

Note that if you have other Mozilla "objects" open before restarting (Messenger, Composer ...) you will need to shut them down as well.
In Mozilla do Help -> About Plug-ins to confirm that the Java Plug-in is loaded.



In addition, redhat enterprise linux 4 uses Konqueror as its default browser.
You can use "vnc://hostname:5901" to access vnc server directly.

The meaning of checkpoint scn in backupsets

backupset中checkpoint scn的意义


until scn XXXXXX:
Specifies an SCN as an upper limit. RMAN selects only files that can be used to restore or recover up to but not including the specified SCN. For example, RESTORE DATABASE UNTIL SCN 1000 chooses only backups that could be used to recover to SCN 1000.（出处：手册）

上面就是说，rman使用list backupset命令显示的checkpoint scn就是该backupset能recover到的最小scn


如果使用的是user-managed hot backup的话，就必须recover到end backup marker以后：
If the datafiles comprising the partially restored database were in hot
backup mode at the time the table was dropped, it is necessary to restore
a previous backup. The reason for this is that the hot backup must be rolled
forward past the 'end backup' markers (the time that the tablespaces were taken
out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the
following error when an attempt is made to open the database:

ORA-1195 "online backup of file %s needs more recovery to be consistent"

（下面附了2篇note，第一篇辅助说明第一个问题，第二篇是上面那段话的出处。）

Subject: RMAN: Set Until Time does not Restore the Required Backup
Doc ID: Note:124788.1 Type: PROBLEM
Last Revision Date: 09-MAY-2005 Status: PUBLISHED


Problem Description
-------------------

You need to restore to a specific backup that was taken using RMAN and you
specify the set until time option but the backup used for the restore is not
the backup you wanted.

Each night the database is shutdown and then a startup mount is done. A backup
job is then run after this. It was discovered that a table had been dropped
approximately five minutes before the database was shutdown. You look at the
rc_backup_piece view and find the start_time of the nightly backup. You
subtract five minutes from the start_time and this is the time that you want
the point-in-time restore and recovery to finish at. You run a restore and
recovery job from RMAN and expect the previous night's backup to be selected.
This is not the case; the latest backup is selected. This is illustrated
below:


The following lists the two backups taken by rman.


List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
3993 34 413309153 0 413309090 43 10-NOV-00 16:05

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
3995 1 1 AVAILABLE 10-NOV-00 16:05 1bca5652_1_1

List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
1 /u01/app/oracle/oradata/test/systest.dbf 0 Full 36384 10-NOV-00 16:01
2 /u01/app/oracle/oradata/test/rbstest.dbf 0 Full 36384 10-NOV-00 16:01
3 /u01/app/oracle/oradata/test/temptest.dbf 0 Full 36384 10-NOV-00 16:01
4 /u01/app/oracle/oradata/test/tempusers.dbf 0 Full 36384 10-NOV-00 16:01




List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
4004 35 413309939 0 413309866 44 10-NOV-00 16:18

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
4006 1 1 AVAILABLE 10-NOV-00 16:18 1cca56ta_1_1

List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
1 /u01/app/oracle/oradata/test/systest.dbf 0 Full 36398 10-NOV-00 16:08
2 /u01/app/oracle/oradata/test/rbstest.dbf 0 Full 36398 10-NOV-00 16:08
3 /u01/app/oracle/oradata/test/temptest.dbf 0 Full 36398 10-NOV-00 16:08
4 /u01/app/oracle/oradata/test/tempusers.dbf 0 Full 36398 10-NOV-00 16:08


The table was dropped five minutes before the second backup was started,
so the following sql query was run against the rman user:

select bp_key,start_time,completion_time from rc_backup_piece;


BP_KEY START_TIME COMPLETION_TIME
---------- ------------------ ----------------------------
4006 10-nov-00 16:17 10-nov-00 16:18


The backup started at 16:17. The following RMAN job was set up and run:


RMAN> run {
2> set until time '10-nov-00 16:10';
3> allocate channel t1 type 'sbt_tape';
4> restore database;
5> recover database;
6> release channel t1;}


RMAN-03022: compiling command: set

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: t1
RMAN-08500: channel t1: sid=11 devtype=SBT_TAPE
RMAN-08526: channel t1: MMS Version 2.2.0.1

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel t1: starting datafile backupset restore
RMAN-08502: set_count=44 set_stamp=413309866 creation_time=10-NOV-00 16:17
RMAN-08089: channel t1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to /u01/app/oracle/oradata/test/systest.dbf
RMAN-08523: restoring datafile 00002 to /u01/app/oracle/oradata/test/rbstest.dbf
RMAN-08523: restoring datafile 00003 to /u01/app/oracle/oradata/test/temptest.dbf
RMAN-08523: restoring datafile 00004 to /u01/app/oracle/oradata/test/testusers.dbf
RMAN-08023: channel t1: restored backup piece 1
RMAN-08511: piece handle=1cca56ta_1_1 tag=NIGHTLY BACKUP params=NULL
RMAN-08024: channel t1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-08055: media recovery complete

RMAN-03022: compiling command: recover(4)

RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: t1


The second backup is used, not the first backup as expected.


Solution Description
--------------------

The SET UNTIL TIME is the time the database be recovered to.
The SET UNTIL TIME should have been set prior to the database
being shutdown. Or more generic the SET UNTIL TIME should have
been set prior to the CHECKPOINT TIME of the datafiles (10-NOV-00 16:08)

The CHECKPOINT TIME of the datafiles will be different for an ONLINE backup,
and therefor the SET UNTIL TIME should be BEFORE the 'oldest' backup CHECKPOINT TIME
of the related datafiles in that backup.

References
----------

Oracle8i Recovery Manager User's Guide and Reference


Additional Search Words
-----------------------




Subject: Recovering a Dropped Table from a Full Database Backup
Doc ID: Note:96197.1 Type: TROUBLESHOOTING
Last Revision Date: 20-MAY-2007 Status: PUBLISHED


PURPOSE
-------

This bulletin outlines the steps to perform recovery to restore
a dropped table without recovering the entire database. The bulletin
assumes the reader is familiar with Oracle's recovery procedures which
are documented in the Oracle documentation set.

SCOPE & APPLICATION
----------------------

This bulletin discusses dropped table recovery using traditional (Oracle7)
backup and recovery procedures. These can equally be applied to Oracle8. It
does NOT discuss tablespace point in time recovery (TSPITR) or the Recovery
Manager (RMAN) duplicate database feature.

The examples in this bulletin are UNIX-based, but can be easily applied to
other platforms with little modification.


RECOVERING A DROPPED TABLE FROM A FULL DATABASE BACKUP
------------------------------------------------------

TERMINOLOGY
-----------

PROD machine - Host computer on which the production database runs. Also
denotes the ORACLE_SID of the production instance.
TEST machine - Host computer, physically distinct from the machine on which the
production database runs.
TEMP - The ORACLE_SID of the instance used to access the restored
database.
The backup - The set of backed up database structures (see REQUIREMENTS) used
as the basis for recovering the dropped table. Once restored,
this set of structures is referred to as the partially restored
database.

REQUIREMENTS
------------

The following data structures must be available from the backup:
- All system tablespace datafiles
- All rollback segment datafiles
- All datafiles in which the table to recovered is stored
- Archive logs - see Note (a)
Notes:
(a) If the restored datafiles are part of a hot backup, or you wish to
roll them forward, the required archivelogs must be available.

In an ideal world the partially restored database will be made available for
recovery on a TEST machine. However this may not always be possible, and
it may be necessary to restore to the same machine on which the 'source'
(PROD) database resides. The latter is NOT RECOMMENDED as a user error in the
recovery process can corrupt the production database.

If the datafiles comprising the partially restored database were in hot
backup mode at the time the table was dropped, it is necessary to restore
a previous backup. The reason for this is that the hot backup must be rolled
forward past the 'end backup' markers (the time that the tablespaces were taken
out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the
following error when an attempt is made to open the database:

ORA-1195 "online backup of file %s needs more recovery to be consistent"

I. RECOVERY ON A TEST MACHINE:
------------------------------

The following steps are to be used when performing recovery on a TEST machine,
the assumption being that there is no instance with the ORACLE_SID "PROD"
or "TEMP" already running on the TEST machine.

1. On the PROD database, connect as a privileged user and create an ASCII
dump of the controlfile. For example:

SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;

A trace file will be generated in the user_dump_dest destination. Rename
this trace file to ccf.sql and save it.

2. Create a directory on the TEST machine. The restored database will be
located in this directory. In this example the directory is called
/RESTORE. The only stipulation is that there is enough space to easily
accomodate the restored database and any archivelogs required for recovery.
If filesystem space is at a premium, the archivelogs may be optionally
restored to /RESTORE as required by recovery, and removed after they have
been applied.

3. Restore the backup to the /RESTORE directory.

4. Optionally, restore all archivelogs required for recovery to the /RESTORE
directory.

5. Copy the init.ora file (initPROD.ora) from the PROD database to the /RESTORE
directory on the TEST machine. This assumes that all database parameters are
specified in only the init.ora file. If an include files (ifile) is
referenced, also copy this file to /RESTORE.

6. Rename the initPROD.ora to initTEMP.ora on the TEST machine.

7. Edit the initTEMP.ora and make the following modifications:
a. control_files = /RESTORE/cntrlTEMP.dbf
b. if applicable, change any ifile references to point to the copied
include file
c. log_archive% parameters should be changed to reflect the restored
archivelogs:
log_archive_destination
log_archive_format
For example:
log_archive_destination=/RESTORE
log_archive_format=arch_%s.dbf
d. %dump_dest parameters should be changed to point to suitable directories

e. If audit is on, turn it off.

8. Ensure that the shell environment is set correctly. At the very least, the
following environment variables must be defined:
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)

9. Connect as a privileged user. For example:

SVRMGR> connect / as sysdba

10. Start the instance, specifying TEMP's init.ora:

SVRMGR> startup nomount pfile=/RESTORE/initTEMP.ora

11. The ccf.sql file (created in Step 1) contains the syntax necessary to
create a new controlfile. Copy this script to the TEST machine and edit
it to save only the text between (and including) 'CREATE CONTROLFILE'
and it's terminating semi-colon. Modify the following sections:

a. LOGFILE. Edit this section to reflect the names of the online logs to
be created for the TEMP instance. If the PROD instance has a large
number of large online log members, it is advisable to specify a
reduced number of smaller log members. You MUST however specify at
least two online log groups.
b. DATAFILE. Edit this section to reflect the names of the restored
datafiles only.
c. Miscellaneous:
- Remove the REUSE keyword
- Optionally change the ARCHIVELOG keyword to NOARCHIVELOG (so the
TEMP database will not operate in archivelog mode)

An example of the completed ccf.sql script might be:

CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 20
MAXINSTANCES 1
MAXLOGHISTORY 337
LOGFILE
GROUP 1 '/RESTORE/log1PROD.dbf' SIZE 1M,
GROUP 2 '/RESTORE/log2PROD.dbf' SIZE 1M
DATAFILE
'/RESTORE/sys1PROD.dbf',
'/RESTORE/rbs1RBS.dbf',
'/RESTORE/users1PROD.dbf'
;

Then execute ccf.sql script to create control file(s).

12. Recover the database. The database is to be recovered to a time before
the table was dropped. There are two options here:
a. Time-based incomplete recovery. Database recovery is stopped at the
specified date and time.
b. Cancel-based incomplete recovery. Database recovery is stopped on
an archivelog boundary i.e. the granularity of cance-based recovery
is the archivelog.

Example of time-based recovery:

SVRMGR> recover database until time '2000-01-10:12:00:00' using backup controlfile

In the above example, apply archivelogs as requested. Recovery will stop
automatically at 12:00 on Januaru 10th, 2000.

Example of cancel-based recovery:

SVRMGR> recover database until cancel using backup controlfile

As soon as you have applied the last desired archivelog, type CANCEL
to stop recovery.

13. Open the database with the RESETLOGS option:

SVRMGR> alter database open resetlogs;

14. Export the table(s).

15. Import the table(s) into the PROD database.

16. Once the necessary tables have been imported, the TEMP instance can be
shutdown and all associated files removed. It is worthwhile verifying that
the import has completed successfully before removing the TEMP instance.

II. RECOVERY ON A PRODUCTION MACHINE:
-------------------------------------

If a TEST machine is not available for performing recovery, the PROD machine
can be used. It is important to exercise extreme caution when doing this. The
restored database will be operate under the TEST instance as before. During
this procedure the restored database's name is changed in order to avoid
problems in acquisition of the mount lock.

1. Take a FULL backup of the database running against the PROD instance
before performing any recovery.

2. While connected to the PROD instance, create an ASCII dump of the
controlfile:

SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;

3. Create a /RESTORE directory as before (Step 2 above).

4. Restore the backup (and optionally the archivelogs) to the /RESTORE
directory (Steps 3 and 4 above).

5. Create the initTEMP.ora file (Steps 5, 6, 7 above). In addition to the
changes already made, modify the db_name parameter, for example:
db_name=TEMP

6. Ensure that the shell environment is set correctly (Step 8 above):
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)

7. Start the TEMP instance (Steps 9, 10 above). It is critical to ensure
that the correct pfile is used to start the instance.

8. Modify the ccf.sql file (Step 11 above). It is critical to ensure that
the DATAFILE and LOGFILE names reference the backup location and NOT the
PROD instance database's files. In addition to the changes already made,
modify the first line to set the new database name, for example:
from: CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
to: CREATE CONTROLFILE SET DATABASE "TEMP" RESETLOGS NOARCHIVELOG

9. Recover the database (Step 12 above).

10. Open the database (Step 13 above).

11. Export the table(s).

12. Import the tables(s).

13. Cleanup the TEMP instance (Step 16 above).

RELATED DOCUMENTS
-----------------
Oracle7 Server Administrator's Guide
Oracle8 Backup and Recovery Guide

Why do tablespaces not require hot backup mode or extra logging

Q: Why do tablespaces not require hot backup mode or extra logging
when using RMan for backups?

A: To understand why RMAN does not require extra logging or backup mode,
you must first understand why those features are required for non-RMAN
online backups.

A non-RMAN online backup consists of a non-Oracle tool, such as cp or
dd, backing up a datafile at the same time that DBWR is updating the
file. We can't prevent the tool from reading a particular block at the
exact same time that DBWR is updating that block. When that happens,
the non-Oracle tool might read a block in a half-updated state, so that
the block which is copied to the backup media might only have been
updated in its first half, while the second half contains older data.
This is called a "fractured block". If this backup needs to be restored
later, and that block needs to be recovered, recovery will fail because
that block is not usable.

The 'alter tablespace begin backup' command is our solution for the
fractured block problem. When a tablespace is in backup mode, and a
change is made to a data block, instead of logging just the changed
bytes to the redo log, we also log a copy of the entire block image
before the change, so that we can reconstruct this block if media
recovery finds that this block was fractured. That block image logging
is what causes extra redo to be generated while files are in backup
mode.

The reason that RMAN does not require extra logging is that it
guarantees that it will never back up a fractured block. We can make
that guarantee because we know the format of Oracle data blocks, and we
verify that each block that we read is complete before we copy it to the
backup. If we read a fractured block, we read the block again to obtain
a complete block before backing it up. non-Oracle tools are not able to
do the same thing because they do not know how to verify the contents of
an Oracle data block.

Backup mode has another effect, which is to 'freeze' the checkpoint in
the header of the file until the file is removed from backup mode.
We do this because we cannot guarantee that the third-party backup
tool will copy the file header prior to copying the data blocks.
RMAN does not need to freeze the file header checkpoint because we
know the order in which we will read the blocks, which enables us to
capture a known good checkpoint for the file.

The correct NLS_LANG setting in Unix Environments

Subject: The correct NLS_LANG setting in Unix Environments
Doc ID: Note:264157.1 Type: HOWTO
Last Revision Date: 02-AUG-2006 Status: PUBLISHED

In this Document
Goal
Solution
References



--------------------------------------------------------------------------------



Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
Information in this document applies to any platform.
GoalHow to set the NLS_LANG correctly in Unix environments:

-1- Check which locale is set and correct this if needed.
-2- Check if the choosen locale is indeed installed and install this if needed.
-3- Check if you telnet/ssh software is properly set up.
-4- Set the NLS_LANG and Test.
-5- What to do if this is not working?
-6- More in depth debugging.
Solution

-1- Check which locale is set and correct this if needed.
---------------------------------------------------------

Please note that the ouput of this command is not exactly the same on all the Unix environments.
Contact your Unix vendor if you have questions.

The example used here is to configure your unix environment so that you can use Unicode (UTF8) with sqlplus on your unix box.

To see your current setup, use the "locale" command like this:

$ locale

example of output:

LANG=fr_FR
LC_CTYPE="fr_FR.iso885915@euro"
LC_COLLATE="fr_FR.iso885915@euro"
LC_MONETARY="fr_FR.iso885915@euro"
LC_NUMERIC="fr_FR.iso885915@euro"
LC_TIME="fr_FR.iso885915@euro"
LC_MESSAGES="fr_FR.iso885915@euro"
LC_ALL=fr_FR.iso885915@euro


Most Unix versions have this as default:

$ locale

LANG=
LC_CTYPE="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_MESSAGES="C"
LC_ALL=

"C" means US7ASCII , this implies that you can only display a-z, A-Z and 0-9 and nothing else.

We recommend to use UTF-8 when possible, this should look like:

$ locale

LANG=en_US
LC_CTYPE="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_ALL=en_US.UTF-8

When you have choosen a value, for example "en_US.UTF-8" on Linux, you can set it like this:

$ export LC_ALL=en_US.UTF-8

or

% setenv LC_ALL en_US.UTF-8

However you should set the needed variables in the user ".profile" file and then log out an in agian and check if the "locale" command returns the wanted output.
If you have questions on how to configure a user environment on your specific Unix/Linux flavor please consult your OS vendor.

-2- Check if the choosen locale is indeed installed and install this if needed.
-------------------------------------------------------------------------------

Please *do* check that you have the defined locale really installed.

To see all installed locales issue:

$ locale -a

sample output:

$ locale -a

POSIX
common
en_US.UTF-8
C
iso_8859_1
iso_8859_15
en_CA
en_CA.ISO8859-1
en_US
en_US.ISO8859-1
en_US.ISO8859-15
en_US.ISO8859-15@euro
fr_CA
fr_CA.ISO8859-1
th
th_TH
th_TH.TIS620
ja


This lists all installed locales for the unix box, for example "fr_FR.iso885915@euro" is missing in the list, so if you want to use it then you need to install this first.

If you set the user environment to a locale that is not installed you will not get an error but it will not work.
Please also note that you need to have installed the exact locale , if you have "fr_FR.UTF8" or "UTF-8" installed but want to use "en_US.UTF8" you need to install "en_US.UTF8".

In the example you see that "en_US.UTF-8" is listed , so we can use it on this server.

-3- Check if you telnet/ssh software is properly set up.
--------------------------------------------------------

You will need *also* to check that your TELNET/SSH software is correctly configured.
It's your telnet software is responible for the conversion of the Unix locale to the client's environment (most likly a windows system).

We suggest that you try first with the free PUTTY client.

Putty download site - http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

When using the "en_US.UTF-8" like in our example on the unix side change the following setting in Putty:

Open the configuration window, navigate to "Window" then to "Translation" and set "Received data assumed to be in which character set" to "UTF-8".

Then navigate to "Window" then to "Appearance" and choose a font in the "Font used in the terminal window" that supports the languages you want to use/see.
For non-asian languages you can use the default "Courier New".

On windows you can use the windows tool "character map" to see what characters are know in a font.

Putty UTF8 how to - http://www.laug.org/2004/11/putty-utf8-howto.html

If it works with Putty but not with your telnet/ssh package then please consult the vendor of your telnet/ssh software.

If you do not use telnet but a "real" unix display please see Note 265090.1 - How to check Unix terminal Environments for the capability to display extended characters.

-4- Set the NLS_LANG and Test.
------------------------------

Once you have 1) configured the locale correctly , 2) have checked that the used locale is really installed and 3) configured your telnet/ssh client, then you can use a NLS_LANG set to LC_TYPE values.
In our "en_US.UTF-8" example this means setting NLS_LANG=AMERICAN_AMERICA.UTF8 (note the difference in notation between UTF-8 (unix) and UTF8 (oracle) )

so, login with your Unix user and then

a) check with locale if it's correctly set (assuming here en_US.UTF8)

b) set the NLS_LANG to match the locale settings

$ export NLS_LANG=AMERICAN_AMERICA.UTF8

or

% setenv NLS_LANG AMERICAN_AMERICA.UTF8

c) connect with sqlplus to your database and select some data.

For example "select UNISTR('\20AC') from dual;" will give the euro symbol when selecting using a 9i or up database that can handle the euro (UTF8,WE8MSWIN1252,...) and a correct UTF-8 or ISO8859-15 Unix enviroment.

If this works, then set the NLS_LANG also in the profile of your user.

-5- What to do if this is not working?
--------------------------------------

If you do not see the expected characters then please double check your settings.

However it's also possible that you have wrong data in your database.

The easy way to check:

Use a windows client, download and install SQL developer http://www.oracle.com/technology/products/database/project_raptor/, connect to your database and see if your data is correctly displayed in that tool.
If it is visible in SQL developer then the data is correct in the database, if not then you have wrong data in your database.

The harder way:

If for example "select ename from scott.emp where empno='7369';" is a select that returns one row of your data then do "select dump(ename,1016),ename from scott.emp where empno='7369';".
You can then look up if the codes match the characters you expect for your database characterset ( select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET'; ) in NOTE.282336 Charts of most current mono-byte Character sets or in case you have a (AL32)UTF8 database use Note:69518.1 Determining the codepoint for UTF8 characters.


If you can't figure out what's wrong log a tar, refer to this note and provide:
* the info asked in Note 226692.1 Finding out your NLS Setup.
* a spool (!not copy paste!) of the output of your version of the
"select dump(ename,1016),ename from scott.emp where empno='7369';" select .

It might also be good to follow Database Character Set Healthcheck,
same here, if you are stuck, log a tar and provide all gatherd information.

note to support: if a ct log's a TAR about this please *do* request a ARE review.

-6- More in depth debugging.
----------------------------

The steps 1-4 should be enough for 99% of the cases, the rest of the note is more in depth debugging

On some platforms, it can be usefull to use the following syntax to have more details about
the codepage really used:

$ locale LC_CTYPE | head

example of output in a HP-UX env:
""
""
"iso885915"
""
example of output in a Linux env:
upper;lowe ;alpha;digit;xdigit;space;print;graph;blank;cntrl;punct;alnum;combining;combining_level3
toupper;tolower;totitle
16
1
ISO-8859-15
70
84
1
0
1

$ locale LC_CTYPE | head
upper;lower;alpha;digit;xdigit;space;print;graph;blank;cntrl;punct;alnum;combining;combining_level3
toupper;tolower;totitle
16
6
UTF-8
70
84
1
0
1


On Solaris, AIX, TRU64, this syntax doesn't give interesting complementary information.
To find more details about these settings:
on Solaris, have a look in /usr/lib/locale.
on AIX, have a look in /usr/lib/nls/README
on TRU64, have a look in /usr/lib/nls
on HP-UX, have a look in /usr/lib/nls/config
on Linux, have a look in /usr/share/locale/locale.alias


How to check the codepoints managed by the O.S.:

To know which code point is generated for a character in a Unix Environment,
you can use the "od" command like this (examples with a iso-8859-1 locale):

$ od -xc
é^D^D
0000000 00e9
351 \0
0000001

as you can see the hexa-decimal code point e9 is corresponding to the "é" (lower e acute)
351 is the corresponding Octal value (Octal is the native mode of the od command).

You can also check the character corresponding to a code point using the "echo" command like this:

for Solaris, AIX, HP-UX, TRU64:

$echo '\0351'
é

for Linux:

$echo -e '\0351'
é

As you can see, echo uses the Octal value, so you need to convert in octal the value you want to check.


ReferencesNote 158577.1 - NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 179133.1 - The correct NLS_LANG in a Windows Environment
Note 265090.1 - How to check Unix terminal Environments for the capability to display extended characters.
http://www.xfree86.org/4.2.0/xfontsel.1.html - to see which fonts are known in the xwin env
http://www.xfree86.org/4.2.0/xlsfonts.1.html - to see what fonts are installed on the system
http://publib.boulder.ibm.com/infocenter/pseries/index.jsp?topic=/com.ibm.aix.doc/aixprggd/nlsgdrf/locale_env.htm - IBM locale settings reference
http://docs.hp.com/en/5187-3605/ch02.html - HP Encoding Characters Reference
http://docs.hp.com/en/UXL10N-90302/UXL10N-90302.pdf - HP Language configuration reference
http://developers.sun.com/dev/gadc/faq/locale.html - Sun Solaris locale reference
http://www.opengroup.org/onlinepubs/007908799/xbd/locale.html - UNIX Open Group locale specifications reference
Note 119119.1 - AL32UTF8 / UTF8 (unicode) Database Character Set Implications