Oracle Apps DBA, Qatari Diar real Estate investment Company.: December 2008

How to Change the System Name in Workflow

After Cloning required to change workflow system name.

Change the service name in TEST:

In apps node

SQL> select NAME from wf_systems;

NAME
--------
PROD.DOYENSYS.COM

SQL> select GUID from wf_systems;

GUID
--------------------------------
3CA4A184BD2C99FFE040500A1F033CD9

SQL> select NAME, SYSTEM_GUID from wf_agents;

Go to bellow path and run the following scripts txkWfClone.sh .

$ cd /TEST/apps/testcomn/admin/install/TEST_oratest2

$ sh txkWfClone.sh apps apps

txkWfClone.sh started at Mon Dec 29 03:49:05 EST 2008
The environment settings are as follows ...
apps_HOME : /TEST/apps/testora/8.0.6
apps_SID :
TWO_TASK : TEST_806_BALANCE
PATH : /TEST/apps/testora/iAS/Apache/perl/bin:/TEST/apps/testora/8.0.6/bin:/TEST/apps/testappl/fnd/11.5.0/bin:/TEST/apps/testappl/ad/11.5.0/bin:/TEST/apps/testcomn/util/java/1.5/jdk1.5.0_11/bin:/TEST/apps/testcomn/util/unzip/unzip/unzip-5.50::/TEST/apps/testora/8.0.6/bin:/usr/bin:/usr/ccs/bin:/usr/sbin:/TEST/apps/testora/iAS/Apache/perl/bin:/TEST/apps/testora/8.0.6/bin:/TEST/apps/testcomn/util/java/1.5/jdk1.5.0_11/bin:/TEST/apps/testcomn/util/unzip/unzip/unzip-5.50:/TEST/apps/testora/8.0.6/bin:/usr/bin:/usr/ccs/bin:/usr/sbin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/applmgr/bin
LD_LIBRARY_PATH : /TEST/apps/testora/8.0.6/network/jre11/lib/i686/native_threads:/TEST/apps/testora/8.0.6/network/jre11/lib/linux/native_threads:/TEST/apps/testappl/cz/11.5.0/bin:/TEST/apps/testora/8.0.6/lib:/usr/X11R6/lib:/usr/openwin/lib

Executable : /TEST/apps/testora/8.0.6/bin/sqlplus

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Dec 29 03:49:05 2008

(c) Copyright 1999 apps Corporation. All rights reserved.

Connected.

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from apps Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ERRORCODE = 0 ERRORCODE_END

After completing this scripts, now check againg the following query.
Now its changed the service name PROD to TEST.

SQL> show parameter service

NAME TYPE VALUE
---------------------------
service_names string TEST

SQL> select NAME from wf_systems;

NAME
------------------------------
TEST.DOYENSYS.COM

SQL> select GUID from wf_systems;

GUID
--------------------------------
5F2BB84D418D8F79E040500A0B02363F

SQL> select NAME, SYSTEM_GUID from wf_agents;

Refered Notes:

The agent could not be found: WF_CONTROL post-clone
Doc ID: 374529.1

How to Change the System Name in Workflow?
Doc ID: 387337.1

O/S-Error: (OS 8) Not enough storage is available to process this command

After increasing SGA_TARGET=2GB, We got following Error Message.

SQL> startup

ORA-27102: out of memory

OSD-00022: additional error information

O/S-Error: (OS 8) Not enough storage is available to process this command

C:\boot.ini old value

Old File:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /fastdetect

Solutions:

Modified Boot.ini File:

Note: In the boot.ini file we changed following parameter /3GB and /PAE

After Changing that parameter, we can able to increase UPTO 2.7GB sga target.

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /fastdetect /3GB /PAE

SQL> startup
ORACLE instance started.
Total System Global Area 2726297600 bytes
Fixed Size 1254760 bytes
Variable Size 553650840 bytes
Database Buffers 2164260864 bytes
Redo Buffers 7131136 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2784M
sga_target big integer 2784M

Reference:
Note 225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms

Note 46053.1 - Windows NT Memory Architecture Overview

ORA-00439: feature not enabled: Real Application Clusters

SQL> startup

ORA-00439: feature not enabled: Real Application Clusters

Turn on RAC (In All nodes)

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_on

If this step did not work, please go ahead below step.

make -f ins_rdbms.mk ioracle

Then Change cluster_name=true, and try to startup the both database.

Metalink Note ID : 211177.1

Using srvctl to Manage your 10g RAC Database

Oracle recommends that RAC databases be managed with srvctl, an Oracle-supplied tool that was first introduced with 9i RAC. The 10g version of srvctl is slightly different from the 9i implementation. In this article.

How and why -- to manage your 10g databases with srvctl.

RAC Architecture Overview

Brief overview of RAC architecture.

1. A cluster is a set of 2 or more nodes that share or coordinate resources to perform the same task.

2. A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.

3. Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.

Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.

Cluster Ready Services and the OCR

Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all
platforms.

CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.

CRS manages the following resources:

1. The ASM instances on each node
2. Databases
3. The instances on each node
4. Oracle Services on each node
5. The cluster nodes themselves, including the following processes:

1. VIP
2. GSD
3. The listener
4. The ONS daemon

CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.

Interacting with CRS and the OCR: srvctl

srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.

Using srvctl

Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.

srvctl must be run from the $ORACLE_HOME/bin of the RAC you are administering. The basic format of a srvctl command is

$ ./srvctl
Usage: srvctl
[]
command:
enabledisablestartstoprelocatestatusaddremovemodifygetenvsetenvunsetenvconfig
objects: databaseinstanceservicenodeappsasmlistener
For detailed help on each command and object and its options use:
srvctl -h
Examples of starting and stopping RAC follow:-

$ gsdctl stat
GSD is running on local node

$ gsdctl stop

$ gsdctl start

Start all its instances and all its services, on all nodes.
$ srvctl start database -d test

Stop all its instances and all its services, on all nodes.
$ srvctl stop database -d test

Startup up the TEST1 instance of the TEST database
$ srvctl start instance -d test -i test1

Bounce the TEST1 instance of the TEST database
$ srvctl stop instance -d test -i test2

To check the CRS status.

# ./crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora....D1.inst application OFFLINE OFFLINE
ora....D2.inst application OFFLINE OFFLINE
ora.test.db application OFFLINE OFFLINE
ora....od1.gsd application ONLINE ONLINE test1
ora....od1.ons application ONLINE ONLINE test1
ora....od1.vip application ONLINE ONLINE test2
ora....od2.gsd application ONLINE ONLINE test2
ora....od2.ons application ONLINE ONLINE test2
ora....od2.vip application ONLINE ONLINE test1

After db upgrade, Here some crs services are went OFFLINE.

Solution:

Please remove the DBSID from srvctl and add again, just follow the bellow steps.

su - oracle

$ cd $ORACLE_HOME/bin

$ ./srvctl remove database -d test

Remove the database test? (y/[n]) y

$ ./srvctl add database -d test -o /10.2.0/test/app/oracle
$ ./srvctl add instance -d test -i test1 -n test1
$ ./srvctl add instance -d test -i test2 -n test2
$ ./srvctl start database -d test

# ./crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora....D1.inst application ONLINE ONLINE test1
ora....D2.inst application ONLINE ONLINE test2
ora.test.db application ONLINE ONLINE test1
ora....od1.gsd application ONLINE ONLINE test1
ora....od1.ons application ONLINE ONLINE test1
ora....od1.vip application ONLINE ONLINE test1
ora....od2.gsd application ONLINE ONLINE test2
ora....od2.ons application ONLINE ONLINE test2
ora....od2.vip application ONLINE ONLINE test2

http://www.orafaq.com/node/70

http://www.oracledba.org/11g/rac/11g_RAC_Admin_Utilities.html

perform the following to change the database ID:

a. shutdown immediate;
b. change the initSID.ora (Cluster_database = FALSE)
c. startup pfile=$ORACLE_HOME/dbs/initSID.ora mount;
d. nid target=sys/password

Old ID TEST1:

SQL> select DBID,name from v$database;

DBID NAME
---------- ---------
13455769 TEST

Old ID TEST2:

SQL> select DBID,name from v$database;

DBID NAME
---------- ---------
13455769 TEST

Database ID for database TEST changed to 1969630541.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

SQL> startup mount pfile=/TEST/oracle/10.2.0/dbs/initTEST1.ora

[oracle@oratest1 dbs]$ nid target=sys/password
DBNEWID: Release 10.1.0.4.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database TEST (DBID=13455769)

Connected to server version 10.1.0

Control Files in database:
/u01/oradata/controlfiles/cntrl01.ctl
/u01/oradata/controlfiles/cntrl02.ctl
/u01/oradata/controlfiles/cntrl03.ctl

Change database ID of database TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 14213988 to 1969630541
Control File /u01/oradata/controlfiles/cntrl01.ctl - modified
Control File /u01/oradata/controlfiles/cntrl02.ctl - modified
Control File /u01/oradata/controlfiles/cntrl03.ctl - modified
Datafile /u01/oradata/system01.dbf - dbid changed
Datafile /u01/oradata/system02.dbf - dbid changed
Datafile /u01/oradata/system03.dbf - dbid changed
Datafile /u01/oradata/system04.dbf - dbid changed
Datafile /u01/oradata/system05.dbf - dbid changed
.
.
.
.
.
.
Datafile /u01/oradata/apps_ts_tx_idx04.dbf - dbid changed
Datafile /u01/oradata/apps_ts_seed02.dbf - dbid changed
Datafile /u01/oradata/temp01.dbf - dbid changed
Control File /u01/oradata/controlfiles/cntrl01.ctl - dbid changed
Control File /u01/oradata/controlfiles/cntrl02.ctl - dbid changed
Control File /u01/oradata/controlfiles/cntrl03.ctl - dbid changed
Instance shut down

Database ID for database TEST changed to 1969630541.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[oracle@oratest1 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.1.0.4.0 - Production on Sun Nov 23 07:25:47 2008

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

Connected to an idle instance.

SQL> startup mount pfile=/TEST/oracle/10.2.0/dbs/initTEST1.ora
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 782584 bytes
Variable Size 341053192 bytes
Database Buffers 721420288 bytes
Redo Buffers 10485760 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select dbid,name from v$database;

DBID NAME
---------- ---------
1896547098 TEST

Stale Statistics Operations

-- PURPOSE: CHECK_STALE_STATS.SQL
-- This script is an automated way to deal with stale statistics operations that are
-- required to be done as part of manual upgrade OR when reported by DBUA.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
--
SET FEEDBACK OFF
SET LINESIZE 250
SET SERVEROUTPUT ON

DECLARE
-- Variables declared
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
-- Cursor defined
CURSOR c1
IS
SELECT distinct schema
FROM dba_registry
ORDER by 1;

-- Beginning of the anonymous block
BEGIN
-- Verifying version from v$instance
SELECT version INTO p_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE(chr(13));
-- Defining Loop 1 for listing schema which have stale stats
FOR x in c1
LOOP
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST AUTO',OBJLIST=>p_otab);

-- Defining Loop 2 to find number of objects containing stale stats
FOR i in 1 .. p_otab.count
LOOP
IF p_otab(i).objname NOT LIKE 'SYS_%'
AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$',
'MON_MODS$','TABPART$','HISTGRM$',
'MON_MODS_ALL$',
'HIST_HEAD$','IN $','TAB$',
'WRI$_OPTSTAT_OPR','PUIU$DATA',
'XDB$NLOCKS_CHILD_NAME_IDX',
'XDB$NLOCKS_PARENT_OID_IDX',
'XDB$NLOCKS_RAW OKEN_IDX', 'XDB$SCHEMA_URL',
'XDBHI_IDX', 'XDB_PK_H_LINK')
THEN
-- Incrementing count for each object found with statle stats
mcount := mcount + 1;
END IF;
-- End of Loop 2
END LOOP;

-- Displays no stale statistics, if coun is 0
IF mcount!=0
THEN
-- Displays Schema with stale stats if count is greater than 0
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- ' x.schema ' schema contains stale statistics use the following to gather the statistics ''--');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
--DBMS_OUTPUT.PUT_LINE('');

-- Displays Command to be executed if schema with stale statistics is found depending on the version.
IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''x.schema''',OPTIONS=>''''GATHER'''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ''''FOR ALL COLUMNS SIZE AUTO'''', CASCADE => TRUE);');
ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''x.schema''',OPTIONS=>''''GATHER'''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ''''FOR ALL COLUMNS SIZE AUTO'''', CASCADE => TRUE);');
ELSE
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('Version is 'p_version);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in ' x.schema ' schema.');
DBMS_OUTPUT.PUT_LINE(chr(13));
END IF;
-- Reset count to 0.
mcount := 0;
-- End of Loop 1
END LOOP;
END;
/

SET FEEDBACK ON
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - -

ORA-01115 ORA-01110 ORA-27091 ORA-27072 Error: 5: I/O error

Symptoms

ORA-01115: IO error reading block from file 393 (block# 2959)
ORA-01110: data file 393: '/u01/proddata/data/ard01.dbf'
ORA-27091: skgfqio: unable to queue I/O
ORA-27027: skgfdisp: I/O error
IBM AIX RISC System/6000 Error: 5 I/O error

- The errors are not reported in the alert log file.
- Running dbv against datafiles from the error messages shows no errors.
- All the mandatory OS patches are applied.
- Oracle patch for Bug 5496862 - IO READING PROBLEMS AFTER INSTALLING IBM TECHNOLOGY LEVEL 5 (5300-05) was installed before patching the OS.

Solutions

Increase maxreqs from 8192 to 12288.

Steps:

1. run aioo -a command to double check current setting for aioo device
2. run aioo -o maxreqs=12288 to set maxreqs dynamically
3. chdev -l aioo -a maxreqs=12288 to set the value of maxreqs permanently for next reboot
4. run aioo -a to confirm change
5. restart oracle

Metalink Note:559697.1

Find the Meanings of Various Status Codes

Applications maintains this type of information in FND_LOOKUPS view.

The following query would provide you the information regarding the status_code and its meaning for fnd_concurrent_processes.

SQL> select lookup_code, meaning from fnd_lookups where lookup_type='CP_PROCESS_STATUS_CODE'

LOOKUP_CODE ----- MEANING
----------------- ------------
A -------------------> Active
C -------------------> Connecting
D -------------------> Deactiviating
G -------------------> Awaiting Discovery
K ------------------->Terminated
M ------------------>Migrating
P ------------------>Suspended
R ------------------>Running
S ------------------>Deactivated
T ------------------>Terminating
Z ------------------>Initializing

Here are the status_codes for fnd_concurrent_requests and their meanings :-

PROCESS_STATUS_CODE ------- Column:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A ------------------------------------>Waiting
B ------------------------------------>Resuming
C ------------------------------------>Normal
D ------------------------------------>Cancelled
E ------------------------------------>Error
F ------------------------------------>Scheduled
G ------------------------------------>Warning
H ------------------------------------>On Hold
I ------------------------------------->Normal
M ------------------------------------>No Manager
Q ------------------------------------>Standby
R ------------------------------------>Normal
S ------------------------------------>Suspended
T ------------------------------------>Terminating
U ------------------------------------>Disabled
W ----------------------------------->Paused
X ------------------------------------>Terminated
Z ------------------------------------>Waiting

PHASE_CODE -----------column
----------------------- ---------------
C ------------------------->Completed
I ------------------------->Inactive
P ------------------------->Pending
R ------------------------->Running

Performence Tuning Scripts List

All related COE, BDE, TRCA, SQLT and PROF scripts can be downloaded from MetaLink or directly from Oracle's external FTP server

ftp://oracle-ftp.oracle.com/apps/patchsets/AOL/SCRIPTS/PERFORMANCE

MetaLink
Script from FTP server
Title

224270.1
TRCANLZR.SQL
Trace Analyzer (8.1.6+)

215187.1
SQLTXPLAIN.SQL
Enhanced Explain Plan and related info for one SQL statement (8.1+)

243755.1
profiler.sql
Implementing and Using the PL/SQL Profiler (8.1+)

156959.1
coe_xplain_80.sql
Enhanced Explain Plan for given SQL Statement (8.0)

156960.1
coe_xplain_73.sql
Enhanced Explain Plan for given SQL Statement (7.3)

174603.1
bde_x.sql
Simple Explain Plan for given SQL Statement (8.1+)

156972.1
coe_view.sql
Clones views across instances for SQL tuning exercises (8.0+)

238684.1
SQLAREAT.SQL
Top 10 Expensive SQL from SQL Area (8.1+)

163209.1
coe_sqlarea_80.sql
Top 10 Expensive SQL from SQL Area (8.0)

169630.1
bde_session.sql
Expensive SQL and resources utilization for given Session ID (8.1+)

174605.1
bde_chk_cbo.sql
Current, required and recommended Apps 11i init.ora params (11i)

163208.1
bde_last_analyzed.sql
Verifies Statistics for all installed Apps modules (11i)

156968.1
coe_stats.sql
Automates CBO Stats Gathering using FND_STATS and Table sizes (11i)

156969.1
coe_trace.sql
SQL Tracing Apps online transactions with Event 10046 (11i)

156970.1
coe_trace_11.sql
SQL Tracing Apps online transactions with Event 10046 (11.0)

156971.1
coe_trace_all.sql
Turns SQL Trace ON for all open DB Sessions (8.0+)

156965.1
coe_locks.sql
Session and serial# for locked Rows (7.3+)

Templates
MetaLink
Apps Performance Bug Template

169937.1
Transaction Tuning

169938.1
Back-End Tuning

169939.1
Tech-Stack Tuning

Memory Notification: Library Cache Object loaded into SGA

alert_SID.log Error.

Memory Notification: Library Cache Object loaded into SGA
Heap size 2550K exceeds notification threshold (2048K)
KGL object name :select t1.owner, t1.name, t1.queue_table, t1.queue_type, t1.max_retries, t1.retry_delay, t1.retention, t1.user_comment, t2.type, t2.object_type from all_queues t1, all_queue_tables t2 where t1.owner=:1 and t1.name=:2 and t2.owner=:3 and t1.queue_table=t2.queue_table
Sat Nov 8 03:36:15 2008
Memory Notification: Library Cache Object loaded into SGA
Heap size 2546K exceeds notification threshold (2048K)
Details in trace file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_268966.trc

ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_302372.trc.
Fri Oct 31 15:08:04 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_129790.trc.
Fri Oct 31 15:08:35 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_66976.trc.
Fri Oct 31 15:08:35 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_196730.trc.
Fri Oct 31 15:08:37 2008
ORA-00060: Deadlock detected. More info in file /u01/11i/oracle/upgdb/10.2.0.1/admin/upg_zeus/udump/upg_ora_129790.trc.

Solutions:

SQL> SELECT a.ksppinm "Parameter",
2 b.ksppstvl "Session Value",
3 c.ksppstvl "Instance Value"
4 FROM x$ksppi a, x$ksppcv b, x$ksppsv c
5 WHERE a.indx = b.indx AND
6 a.indx = c.indx AND
7 a.ksppinm LIKE '%kgl_large_heap%';

Parameter Session Value Instance Value
--------------------------------------------------------------------------------

_kgl_large_heap_warning_threshold 2097152 2097152

Above parameter is hidden parameter. We have to change that hidden parameter.

If we using spfile, just login sql and change that parameter or Bounce the database and change the initSID.ora file.

$ sqlplus "/as sysdba"

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

OR

Bounce the DB and edit the init parameter file and startup.

Then, edit the initialization parameter file and add the below line.

_kgl_large_heap_warning_threshold=8388608

Shut down and re-start the database after changes
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ -----------
_kgl_large_heap_warning_threshold integer 8388608

To perform cancel-based recovery

RMAN> rman target / auxiliary sys/oracle@ROJA nocatalog

Recovery Manager: Release 10.2.0.2.0 - Production on Sun Nov 30 23:12:56 2008

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

connected to target database: PROD (DBID=78671012)
using target database control file instead of recovery catalog
connected to auxiliary database: ROJA (not mounted)

RMAN> @/u02/oracle/visarch/clone.rcv

RMAN> run
2> {
3> allocate auxiliary channel aux1 device type disk format '/u02/oracle/visarch';
4> allocate auxiliary channel aux2 device type disk format '/u02/oracle/visarch';
5> allocate auxiliary channel aux3 device type disk format '/u02/oracle/visarch';
6> allocate auxiliary channel aux4 device type disk format '/u02/oracle/visarch';
7> duplicate target database to "ROJA";
8> }
allocated channel: aux1
channel aux1: sid=386 devtype=DISK

allocated channel: aux2
channel aux2: sid=385 devtype=DISK

allocated channel: aux3
channel aux3: sid=384 devtype=DISK

allocated channel: aux4
channel aux4: sid=383 devtype=DISK

Starting Duplicate Db at 30-NOV-08

contents of Memory Script:

{
set until scn 5965096246191;
set newname for datafile 1 to
"/u01/oramgr/PROD/db/rojadata/system01.dbf";
set newname for datafile 2 to
"/u01/oramgr/PROD/db/rojadata/system02.dbf";
set newname for datafile 3 to
"/u01/oramgr/PROD/db/rojadata/system03.dbf";
set newname for datafile 4 to
"/u01/oramgr/PROD/db/rojadata/system04.dbf";
set newname for datafile 5 to
"/u01/oramgr/PROD/db/rojadata/system05.dbf";

.
.
.
.
.
.
.
.
.

"/u01/oramgr/PROD/db/rojadata/a_txn_ind04.dbf";
set newname for datafile 406 to
"/u01/oramgr/PROD/db/rojadata/a_txn_ind05.dbf";
set newname for datafile 407 to
"/u01/oramgr/PROD/db/rojadata/a_ref02.dbf";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME
.
.
.
.
.
.
.

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 30-NOV-08

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oramgr/PROD/db/rojadata/system01.dbf
restoring datafile 00002 to /u01/oramgr/PROD/db/rojadata/system02.dbf
restoring datafile 00003 to /u01/oramgr/PROD/db/rojadata/system03.dbf
restoring datafile 00004 to /u01/oramgr/PROD/db/rojadata/system04.dbf
.
.
.
.
.
.

restoring datafile 00406 to /u01/oramgr/PROD/db/rojadata/a_txn_ind05.dbf
restoring datafile 00407 to /u01/oramgr/PROD/db/rojadata/a_ref02.dbf
channel aux1: reading from backup piece /u02/oracle/visarch/datafile_t672174776_s1_p1
channel aux1: restored backup piece 1
piece handle=/u02/oracle/visarch/datafile_t672174776_s1_p1 tag=TAG20081130T191256
channel aux1: restore complete, elapsed time: 00:24:57
Finished restore at 30-NOV-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ROJA" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 14607
LOGFILE
GROUP 1 ( '/u01/oramgr/PROD/db/rojadata/log01a.dbf', '/u01/oramgr/PROD/db/rojadata/log01b.dbf' ) SIZE 1000 M REUSE,
GROUP 2 ( '/u01/oramgr/PROD/db/rojadata/log02a.dbf', '/u01/oramgr/PROD/db/rojadata/log02b.dbf' ) SIZE 1000 M REUSE
DATAFILE
'/u01/oramgr/PROD/db/rojadata/system01.dbf'
CHARACTER SET US7ASCII

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=672190745 filename=/u01/oramgr/PROD/db/rojadata/system02.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=672190745 filename=/u01/oramgr/PROD/db/rojadata/system03.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=672190745 filename=/u01/oramgr/PROD/db/rojadata/system04.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=672190745 filename=/u01/oramgr/PROD/db/rojadata/system05.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=672190745 filename=/u01/oramgr/PROD/db/rojadata/ctxd01.dbf
.

.
.
.
.
.

datafile 406 switched to datafile copy
input datafile copy recid=41 stamp=672190747 filename=/u01/oramgr/PROD/db/rojadata/a_txn_ind05.dbf
datafile 407 switched to datafile copy
input datafile copy recid=42 stamp=672190747 filename=/u01/oramgr/PROD/db/rojadata/a_ref02.dbf

contents of Memory Script:
{
set until scn 5965096246191;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 30-NOV-08

starting media recovery

channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=6
channel aux1: restoring archive log
archive log thread=1 sequence=7
channel aux1: reading from backup piece /u02/oracle/backup/VIS_arch_3_672176329_%r.arc
ORA-19870: error reading backup piece /u02/oracle/backup/VIS_arch_3_672176329_%r.arc
ORA-19505: failed to identify file "/u02/oracle/backup/VIS_arch_3_672176329_%r.arc"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oramgr/PROD/db/rojadata/system01.dbf'

released channel: aux1
released channel: aux2
released channel: aux3
released channel: aux4
RMAN-00571: ===========================================================
RMAN-00569: ERROR MESSAGE STACK FOLLOWS
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/30/2008 23:40:08
RMAN-03015: error occurred in stored script Memory Script
RMAN-20506: no backup of archivelog found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 7 lowscn 5965096246186 found to restore
RMAN-06025: no backup of log thread 1 seq 6 lowscn 5965096244536 found to restore

RMAN>
RMAN> **end-of-file**

RMAN> exit

Recovery Manager complete.

*********************************************************************
To perform cancel-based recovery:

1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:
sqlplus '/ AS SYSDBA'

2. Start a new instance and mount the database:

STARTUP MOUNT

3. Begin cancel-based recovery by issuing the following command:

If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

--------------------------------------------------------------------------------
Note:
If you fail to specify the UNTIL clause on the RECOVER command, then you will not be able to open the database until a complete recovery is done.

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

4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.

--------------------------------------------------------------------------------
Note:
If you use an Oracle Real Application Clusters configuration, and you are performing incomplete recovery or using a backup control file, then Oracle can only compute the name of the first archived redo log file from the first thread. The first redo log file from the other threads must be supplied by the user. After the first log file in a given thread has been supplied, Oracle can suggest the names of the subsequent log files in this thread.

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

5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:
CANCEL

Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then t rtry to open the database, you will get an ORA-1113 error if more recovery is necessary for the file. You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.

6. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example, enter:
ALTER DATABASE OPEN RESETLOGS;"

You copy that archivelog and change log name and apply it

cp PROD_arch_6_1_666832683.arc5 /u01/oramgr/PROD/db/tech_st/10.2.0/dbs/arch1_6_666832683.dbf
cp PROD_arch_7_1_666832683.arc6 /u01/oramgr/PROD/db/tech_st/10.2.0/dbs/arch1_7_666832683.dbf

Database is now mount stage, so controlfile created sucessfully, but database unable to open.
So we need to apply the archivelog files, you just apply two or more archive log and type cancel.

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

ORA-00279: change 5965096245461 generated at 11/30/2008 19:12:57 needed for
thread 1
ORA-00289: suggestion :
/u01/oramgr/PROD/db/tech_st/10.2.0/dbs/arch1_6_666832683.dbf
ORA-00280: change 5965096245461 for thread 1 is in sequence #6

Specify log: {=suggested filename AUTO CANCEL}
arch1_6_666832683.dbf
ORA-00279: change 5965096246186 generated at 11/30/2008 19:38:46 needed for
thread 1
ORA-00289: suggestion :
/u01/oramgr/PROD/db/tech_st/10.2.0/dbs/arch1_7_666832683.dbf
ORA-00280: change 5965096246186 for thread 1 is in sequence #7
ORA-00278: log file 'arch1_6_666832683.dbf' no longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}
arch1_7_666832683.dbf
ORA-00279: change 5965096246191 generated at 11/30/2008 19:38:47 needed for
thread 1
ORA-00289: suggestion :
/u01/oramgr/PROD/db/tech_st/10.2.0/dbs/arch1_8_666832683.dbf
ORA-00280: change 5965096246191 for thread 1 is in sequence #8
ORA-00278: log file 'arch1_7_666832683.dbf' no longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}
CANCEL (Here i typed Cancel), Now recovery is canceld and Database is now consistent stage.
Then try resetlogs.
Media recovery cancelled.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Finally bounce and startup the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1264892 bytes
Variable Size 427819780 bytes
Database Buffers 633339904 bytes
Redo Buffers 11317248 bytes
Database mounted.
Database opened.