One partition on the disk was almost full and I had to move database files around. This is what I did on Tempfile and Redo logs without re-creating control file. It also shows a way to re-size the tempfile (sometimes, you have to do that because it becomes too big).
SQL> conn / as sysdba
Connected.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 274995432 bytes
Database Buffers 260046848 bytes
Redo Buffers 1048576 bytes
Database mounted.
-- Drop the tempfile when the database is mounted
SQL> alter database tempfile '/var/oracle/oradata/emrep/temp01.dbf' drop including datafiles;
Database altered.
-- Rename the online redo logs. Make sure you copied all them to the new loaction before issuing below lines to avoid ORA-27037 error.
SQL> alter database rename file '/app/oracle/oradata/emrep/redo01.log'
2 to '/var/oracle/oradata/emrep/redo01.log';
Database altered.
SQL> alter database rename file '/app/oracle/oradata/emrep/redo02.log'
2 to '/var/oracle/oradata/emrep/redo02.log';
Database altered.
SQL> alter database rename file '/app/oracle/oradata/emrep/redo03.log'
2 to '/var/oracle/oradata/emrep/redo03.log';
Database altered.
SQL> alter database rename file '/app/oracle/oradata/emrep/redo04.log'
2 to '/var/oracle/oradata/emrep/redo04.log';
Database altered.
-- Open the database
SQL> alter database open;
Database altered.
-- Run "SQL> select * from v$logfile;" to verify all redo logs are in "ONLINE" status.
-- Verify the tempfile was dropped
SQL> select * from dba_temp_files;
no rows selected
-- Add tempfile to the new location. Make sure there is no same file in the location to avoid over-writing an existin one!
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/emrep/temp01.dbf'
2 SIZE 8012M REUSE AUTOEXTEND ON NEXT 102400000 MAXSIZE 12000M;
Tablespace altered.
-- Verify the result
SQL> select tablespace_name, file_name, bytes/(1024*1024) from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024)
--------------- --------- -----------------
TEMP /app/oracle/oradata/emrep/temp01.dbf 8012
-- Backup the new controlfile
SQL> alter database backup controlfile to trace;
Database altered.
Metalink note 99275.1 gives more details on "alter database rename file '/old path/filename.dbf' to '/new path/filename.dbf';"
Tuesday, December 30, 2008
Tuesday, December 23, 2008
Display CLOB data in Sql*Plus
If you have the session ID (982) and the session is actively running, you use following code to display the content in a CLOB field.
DECLARE
clob_field CLOB;
charbuf VARCHAR2(4000);
read_offset PLS_INTEGER :=1;
lob_length PLS_INTEGER;
BEGIN
-- for screen output
DBMS_OUTPUT.ENABLE (1000000);
-- Make sure that only one row gets returned!!
SELECT b.sql_fulltext INTO clob_field
FROM v$sqlarea b,v$session a
WHERE a.sid=982 and a.sql_address=b.address;
--get length of the clob field
lob_length := DBMS_LOB.GETLENGTH(clob_field);
--loop through clob and print in 255 length segments
WHILE (read_offset <= lob_length) LOOP
charbuf := DBMS_LOB.SUBSTR(clob_field, 255, read_offset);
dbms_output.put_line(trim(charbuf));
read_offset := read_offset + 255;
END LOOP;
END;
/
DECLARE
clob_field CLOB;
charbuf VARCHAR2(4000);
read_offset PLS_INTEGER :=1;
lob_length PLS_INTEGER;
BEGIN
-- for screen output
DBMS_OUTPUT.ENABLE (1000000);
-- Make sure that only one row gets returned!!
SELECT b.sql_fulltext INTO clob_field
FROM v$sqlarea b,v$session a
WHERE a.sid=982 and a.sql_address=b.address;
--get length of the clob field
lob_length := DBMS_LOB.GETLENGTH(clob_field);
--loop through clob and print in 255 length segments
WHILE (read_offset <= lob_length) LOOP
charbuf := DBMS_LOB.SUBSTR(clob_field, 255, read_offset);
dbms_output.put_line(trim(charbuf));
read_offset := read_offset + 255;
END LOOP;
END;
/
Monday, December 22, 2008
Display package code in Sql*Plus
All packages and procedures are stored in DBA_SOURCE, by one line in a row. Unless you use a third party tool, it is not easy to read them in Sql*Plus for a large package or procedure.
Here are the settings for displaying all code in a readable format:
set pagesize 0
set arraysize 10
set long 200
col text format a80 word_wrap
select text from dba_source
where name = 'PACK_NAME'
-- and owner = 'XXX'
;
Note the view dba_source is defined in 10g as
Name Type
----- ------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
Here are the settings for displaying all code in a readable format:
set pagesize 0
set arraysize 10
set long 200
col text format a80 word_wrap
select text from dba_source
where name = 'PACK_NAME'
-- and owner = 'XXX'
;
Note the view dba_source is defined in 10g as
Name Type
----- ------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
Wednesday, November 19, 2008
Find a foreign key references to a table
Foreign key can be created in the table creation or by "alter table ..."
CREATE TABLE my_table
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constrint_fk
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (col_1, col_2, ... col_n)
);
ALTER TABLE my_table ADD (
CONSTRAINT constrint_fk
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (col_1, col_2, ... col_n)
enable validate);
Here, my_table is the child table to the foreign key constrint_fk. Columns (col_1, col_2, ... col_n) shall be the primary key or an unique index of the parent_table. Columns (column1, column2, ... column_n) shall be an index of my_table to avoid 'eng: TM' locks in a busy database when the parent_table gets DELETE or UPDATE operations frequently.
When I tried to truncate a table before refreshing it, I got error "ORA-02266: unique/primary keys in table referenced by enabled foreign keys". Below query will identify all foreign keys referenced by child tables:
SELECT DISTINCT a.table_name CHILD_NAME, a.constraint_name F_KEY, a.status, a.last_change
FROM dba_constraints a, dba_constraints b
WHERE a.OWNER = UPPER('<owner>')
AND a.r_constraint_name = b.constraint_name
AND a.constraint_type = 'R'
AND b.constraint_type in ('P', 'U')
AND b.table_name = UPPER('<MY_TABLE>');
Then, run "alter table owner.CHILD_NAME disable constraint F_KEY;" to disable all constraints (foreign keys). After that, I can truncate table MY_TABLE. But if I want to drop the table, I need to drop the constraints first.
One table can have many foreign keys referencing multiple parent tables. Below query finds all foreign keys that links MY_TABLE to all parent tables:
SELECT c.table_name, c.constraint_name, c.constraint_type, c2.table_name P_table, c2.constraint_name P_constraint
FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
WHERE c.OWNER = UPPER('<owner>')
AND c.table_name =UPPER('<MY_TABLE>')
AND c.constraint_TYPE = 'R';
When I delete/update data in table MY_TABLE, I do not need to disable foreign keys to all parent table generated from above query.
CREATE TABLE my_table
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constrint_fk
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (col_1, col_2, ... col_n)
);
ALTER TABLE my_table ADD (
CONSTRAINT constrint_fk
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (col_1, col_2, ... col_n)
enable validate);
Here, my_table is the child table to the foreign key constrint_fk. Columns (col_1, col_2, ... col_n) shall be the primary key or an unique index of the parent_table. Columns (column1, column2, ... column_n) shall be an index of my_table to avoid 'eng: TM' locks in a busy database when the parent_table gets DELETE or UPDATE operations frequently.
When I tried to truncate a table before refreshing it, I got error "ORA-02266: unique/primary keys in table referenced by enabled foreign keys". Below query will identify all foreign keys referenced by child tables:
SELECT DISTINCT a.table_name CHILD_NAME, a.constraint_name F_KEY, a.status, a.last_change
FROM dba_constraints a, dba_constraints b
WHERE a.OWNER = UPPER('<owner>
AND a.r_constraint_name = b.constraint_name
AND a.constraint_type = 'R'
AND b.constraint_type in ('P', 'U')
AND b.table_name = UPPER('<MY_TABLE>
Then, run "alter table owner.CHILD_NAME disable constraint F_KEY;" to disable all constraints (foreign keys). After that, I can truncate table MY_TABLE. But if I want to drop the table, I need to drop the constraints first.
One table can have many foreign keys referencing multiple parent tables. Below query finds all foreign keys that links MY_TABLE to all parent tables:
SELECT c.table_name, c.constraint_name, c.constraint_type, c2.table_name P_table, c2.constraint_name P_constraint
FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
WHERE c.OWNER = UPPER('<owner>
AND c.table_name =UPPER('<MY_TABLE>
AND c.constraint_TYPE = 'R';
When I delete/update data in table MY_TABLE, I do not need to disable foreign keys to all parent table generated from above query.
Tuesday, November 18, 2008
hanganalyze and systemstate
Oracle Support defines a "true" database hang as "an internal deadlock or a cyclical dependency between two or more processes." When dealing with DML locks (that is, enqueue type TM), Oracle is able to detect this dependency and roll back one of the processes to break the cyclical condition. On the other hand, when this situation occurs with internal kernel-level resources (such as latches or pins), Oracle is usually unable to automatically detect and resolve the deadlock.
To troubleshoot the hung database with the hang in progress, Oracle provides utilities to use kernel calls to identify blocking and waiting sessions.
- Hanganalyze
Hanganalyze is a newer utility to get a trace, and can be invoked from SQL *Plus or through oradebug (which is available when connected as SYS in the SQL *Plus utility).
SQL> connect / as sysdba
Connected.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/admin/biqa/udump/biqa_ora_1962226.trc
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/admin/biqa/udump/biqa_ora_1962226.trc
SQL> exit
or
SQL > alter session set events 'immediate trace name hanganalyze level 3';
Various levels and trace information:
1-2 Only hanganalyze output, no process dump at all
3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF, LEAF_NW, IGN_DMP state)
5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
10 Dump all processes (IGN state)
In RAC environment, above command is slightly different because an lock in an instance may be from processes of aonther instance. See Metalink Note 175006.1.
- SYSTEMSTATE Dump
In a database hang situation, Oracle Support may ask systemstate dumps to diagnose the root cause of the problem. It is important to take at least three of them a few minutes apart, on all instances of your database, to capture evidence shows whether a resource is still being held from one time to the next.
1. By oradebug
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> exit
In a RAC, oradebug allows you to dump the global system state by connecting to one node "SQL> oradebug -g all dump systemstate 266". The -g option will dump system states for all the instances. The SYSTEMSTATE dump/trace file can be found in the user_dump_dest directory on the instance where the dump was generated.
2. By Sql*Plus
It is recommended to reconnect every time so that it gets a new process ID and also the new trace files.
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
-- Take the 1st systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
-- Take the 2nd systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
-- Take the 3rd systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
The dump file size depends on the size of the System Global Area (SGA), the number of sessions logged in, and the workload on the system. If using systemstate level 266 takes much longer (hours) than expected to generate the dump file, then end the systemstate dump and try level 258 (or even 10).
- SQL *Plus with the prelim option
When the entire database is hung and you cannot connect to SQL *Plus, you can try invoking SQL *Plus with the prelim option if you're using Oracle 10g or later. This attaches the process to the Oracle instance and no SQL commands are run. No login triggers or no pre-processing is done, and no SQL queries are allowed to run.
$sqlplus -prelim
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 11:42:23 2005 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
SQL>
To troubleshoot the hung database with the hang in progress, Oracle provides utilities to use kernel calls to identify blocking and waiting sessions.
- Hanganalyze
Hanganalyze is a newer utility to get a trace, and can be invoked from SQL *Plus or through oradebug (which is available when connected as SYS in the SQL *Plus utility).
SQL> connect / as sysdba
Connected.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/admin/biqa/udump/biqa_ora_1962226.trc
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/admin/biqa/udump/biqa_ora_1962226.trc
SQL> exit
or
SQL > alter session set events 'immediate trace name hanganalyze level 3';
Various levels and trace information:
1-2 Only hanganalyze output, no process dump at all
3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF, LEAF_NW, IGN_DMP state)
5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
10 Dump all processes (IGN state)
In RAC environment, above command is slightly different because an lock in an instance may be from processes of aonther instance. See Metalink Note 175006.1.
- SYSTEMSTATE Dump
In a database hang situation, Oracle Support may ask systemstate dumps to diagnose the root cause of the problem. It is important to take at least three of them a few minutes apart, on all instances of your database, to capture evidence shows whether a resource is still being held from one time to the next.
1. By oradebug
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> exit
In a RAC, oradebug allows you to dump the global system state by connecting to one node "SQL> oradebug -g all dump systemstate 266". The -g option will dump system states for all the instances. The SYSTEMSTATE dump/trace file can be found in the user_dump_dest directory on the instance where the dump was generated.
2. By Sql*Plus
It is recommended to reconnect every time so that it gets a new process ID and also the new trace files.
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
-- Take the 1st systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
-- Take the 2nd systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
-- Take the 3rd systemstate level 266:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 266';
The dump file size depends on the size of the System Global Area (SGA), the number of sessions logged in, and the workload on the system. If using systemstate level 266 takes much longer (hours) than expected to generate the dump file, then end the systemstate dump and try level 258 (or even 10).
- SQL *Plus with the prelim option
When the entire database is hung and you cannot connect to SQL *Plus, you can try invoking SQL *Plus with the prelim option if you're using Oracle 10g or later. This attaches the process to the Oracle instance and no SQL commands are run. No login triggers or no pre-processing is done, and no SQL queries are allowed to run.
$sqlplus -prelim
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 11:42:23 2005 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
SQL>
Monday, October 6, 2008
DBVERIFY
DBVERIFY is an Oracle utility for verifying the structure in data files when you experiences ORA-01578 / ORA-08103 or any other kind of corrupt messages.
During a database recovery, I gor error from creating control file:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/t10/app/oracle/oradata/lawtest/lawts_accons_ptn_idx_7_1.dbf'
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: 2
DBVERIFY provided great evidence to System Admin that the copy of some datafiles is a bad copy by OS command, bacause DBVERIFY showed no error on other files:
$ dbv file=lawts_accons_ptn_idx_7_1.dbf blocksize=16384
DBVERIFY: Release 9.2.0.6.0 - Production on Sun Apr 27 15:03:36 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBV-00100: Specified FILE (lawts_accons_ptn_idx_7_1.dbf) not accessible
$ dbv file=lawts_acbudhdr_ptn_idx_max_1.dbf blocksize=16384
DBVERIFY: Release 9.2.0.6.0 - Production on Sun Apr 27 15:04:11 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = lawts_acbudhdr_ptn_idx_max_1.dbf
DBVERIFY - Verification complete
Total Pages Examined : 16384
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 9996
Total Pages Failing (Index): 0
Total Pages Processed (Other): 120
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6268
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 36435995208 (8.2076256840)
Blocksize is specified by parameter DB_BLOCK_SIZE and is necessary in running DBV.
If ASM storage is used, we need to specify USERID to get authenticated on ASM Instance. The below script will get lines for running DBV on all datafiles of a database on ASM:
select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=sys/&SYS_PASSWORD logfile=' ||
substr(name, instr(name, '/', -1, 1) +1) || '.' || file# || '.log'
from v$datafile;
During a database recovery, I gor error from creating control file:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/t10/app/oracle/oradata/lawtest/lawts_accons_ptn_idx_7_1.dbf'
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: 2
DBVERIFY provided great evidence to System Admin that the copy of some datafiles is a bad copy by OS command, bacause DBVERIFY showed no error on other files:
$ dbv file=lawts_accons_ptn_idx_7_1.dbf blocksize=16384
DBVERIFY: Release 9.2.0.6.0 - Production on Sun Apr 27 15:03:36 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBV-00100: Specified FILE (lawts_accons_ptn_idx_7_1.dbf) not accessible
$ dbv file=lawts_acbudhdr_ptn_idx_max_1.dbf blocksize=16384
DBVERIFY: Release 9.2.0.6.0 - Production on Sun Apr 27 15:04:11 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = lawts_acbudhdr_ptn_idx_max_1.dbf
DBVERIFY - Verification complete
Total Pages Examined : 16384
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 9996
Total Pages Failing (Index): 0
Total Pages Processed (Other): 120
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6268
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 36435995208 (8.2076256840)
Blocksize is specified by parameter DB_BLOCK_SIZE and is necessary in running DBV.
If ASM storage is used, we need to specify USERID to get authenticated on ASM Instance. The below script will get lines for running DBV on all datafiles of a database on ASM:
select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=sys/&SYS_PASSWORD logfile=' ||
substr(name, instr(name, '/', -1, 1) +1) || '.' || file# || '.log'
from v$datafile;
Wednesday, October 1, 2008
Place a blackout in Grid Control
There are two ways to place a blackout in Grid Control.
- From the Grid Control UI (Console), using Setup to create a blackout or using the icon on each Target page.
- From the command line at the agent home.
Here are the commands for creating a blackout:
- Place a blackout at host level. In $AGENT_HOME/bin
$ ./emctl start blackout test_dev3 -nodelevel -d 12:00
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackout test_dev3 added successfully
EMD reload completed successfully
$./emctl status blackout
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackoutname = Blackout-Sep 11 2008 11:47:59 PM
Targets = (bidev.corpnet.com:oracle_database,)
Time = ({2008-9-11|20:50:2|3600 Sec,|} )
Expired = True
Blackoutname = test_dev3(NODE_LEVEL)
Targets = (dbdev:host,)
Time = ({2008-10-01|11:09:33|720 Min,|} )
Expired = False
- Place a blackout on a target / database. In $AGENT_HOME/bin
$ ./emctl start blackout test_dev3 bidev.corpnet.com:oracle_database -d 03:00
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackout test_dev3 added successfully
EMD reload completed successfully
./emctl status blackout
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackoutname = Blackout-Sep 11 2008 11:47:59 PM
Targets = (bidev.corpnet.com:oracle_database,)
Time = ({2008-9-11|20:50:2|3600 Sec,|} )
Expired = True
Blackoutname = test_dev3
Targets = (bidev.corpnet.com:oracle_database,)
Time = ({2008-10-01|12:50:49|180 Min,|} )
Expired = False
- To stop the blackout, run
$ ./emctl stop blackout test_dev3
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackout test_dev3 stopped successfully
EMD reload completed successfully
- Additional notes
1. After "emctl start blackout test_dev3 ..." is executed, it will add an entry in $AGENT_HOME/sysman/emd/blackouts.xml. When "emctl stop blackout test_dev3 ..." is run, the entry is removed from the file. It seems if the blackout expires before the stop command is run, the entry will stay in the file for ever.
2. If you do not use the "-d" option in running "emctl start blackout ..." command, the GC console (Setup --> Blackouts) will list the blackout with "Ended" status and "Indefinite" duration immediately. It seems the blackout without "-d" option stops out quickly. It may be a bug in Grid Control 10.2.0.1.0.
3. Checking the blackout status from the repository table MGMT_BLACKOUTS.
SQL> select * from sysman.MGMT_BLACKOUTS
where BLACKOUT_NAME like 'test_dev3%';
- From the Grid Control UI (Console), using Setup to create a blackout or using the icon on each Target page.
- From the command line at the agent home.
Here are the commands for creating a blackout:
- Place a blackout at host level. In $AGENT_HOME/bin
$ ./emctl start blackout test_dev3 -nodelevel -d 12:00
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackout test_dev3 added successfully
EMD reload completed successfully
$./emctl status blackout
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackoutname = Blackout-Sep 11 2008 11:47:59 PM
Targets = (bidev.corpnet.com:oracle_database,)
Time = ({2008-9-11|20:50:2|3600 Sec,|} )
Expired = True
Blackoutname = test_dev3(NODE_LEVEL)
Targets = (dbdev:host,)
Time = ({2008-10-01|11:09:33|720 Min,|} )
Expired = False
- Place a blackout on a target / database. In $AGENT_HOME/bin
$ ./emctl start blackout test_dev3 bidev.corpnet.com:oracle_database -d 03:00
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackout test_dev3 added successfully
EMD reload completed successfully
./emctl status blackout
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackoutname = Blackout-Sep 11 2008 11:47:59 PM
Targets = (bidev.corpnet.com:oracle_database,)
Time = ({2008-9-11|20:50:2|3600 Sec,|} )
Expired = True
Blackoutname = test_dev3
Targets = (bidev.corpnet.com:oracle_database,)
Time = ({2008-10-01|12:50:49|180 Min,|} )
Expired = False
- To stop the blackout, run
$ ./emctl stop blackout test_dev3
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Blackout test_dev3 stopped successfully
EMD reload completed successfully
- Additional notes
1. After "emctl start blackout test_dev3 ..." is executed, it will add an entry in $AGENT_HOME/sysman/emd/blackouts.xml. When "emctl stop blackout test_dev3 ..." is run, the entry is removed from the file. It seems if the blackout expires before the stop command is run, the entry will stay in the file for ever.
2. If you do not use the "-d" option in running "emctl start blackout ..." command, the GC console (Setup --> Blackouts) will list the blackout with "Ended" status and "Indefinite" duration immediately. It seems the blackout without "-d" option stops out quickly. It may be a bug in Grid Control 10.2.0.1.0.
3. Checking the blackout status from the repository table MGMT_BLACKOUTS.
SQL> select * from sysman.MGMT_BLACKOUTS
where BLACKOUT_NAME like 'test_dev3%';
Tuesday, September 30, 2008
CRS related commands
Oracle provides useful commands to manage CRS (Cluster Ready Services):
crs_stat -t --> Shows HA resource / service status (hard to read)
crsstat --> Ouptut of crs_stat -t formatted nicely (see Metalink note 259301.1)
crs_stop -all --> Stops all registered resources (but keeps CRS running!)
crs_start -all --> Starts all registered resources
crsctl check crs --> Verifies CSS,CRS,EVM functioning
crsctl stop crs --> Stops crs and all other services
crsctl start crs --> Starts crs and all other services
Two commands
crsctl disable crs --> Prevents CRS from starting on reboot
crsctl enable crs --> Enables CRS start on reboot
will update the file /etc/oracle/scls_scr/Node_name/root/crsstart which contains the string “enable” or “disable” as appropriate.
Command "ps -ef | grep d.bin" will check three main background processes. They are normally started by init during the operating system boot process. They can be started and stopped manually by issuing the command /etc/init.d/init.crs {start | stop | enable | disable} (or /etc/init.crs {start | stop | enable | disable})
oracle 2498 2091 0 Aug 18 - 8:18 /u01/crs/oracle/product/crs/bin/evmd.bin
root 2580 1927 0 Aug 18 - 705:21 /u01/crs/oracle/product/crs/bin/crsd.bin reboot
oracle 2662 2542 0 Aug 18 - 557:13 /u01/crs/oracle/product/crs/bin/ocssd.bin
root 2785 2951 0 Aug 18 - 1:59 /u01/crs/oracle/product/crs/bin/oprocd.bin run -t 1000 -m 500 -f
Here is a short description of each of the CRS daemon processes (Note 259301.1):
CRSD:
- Engine for HA operation
- Manages 'application resources'
- Starts, stops, and fails 'application resources' over
- Spawns separate 'actions' to start/stop/check application resources
- Maintains configuration profiles in the OCR
- Stores current known state in the OCR.
- Runs as root
- Is restarted automatically on failure
OCSSD:
- OCSSD is part of RAC and Single Instance with ASM
- Provides access to node membership
- Provides group services
- Provides basic cluster locking
- Integrates with existing vendor clusteware, when present
- Can also runs without integration to vendor clustware
- Runs as Oracle.
- Failure exit causes machine reboot. --> This is a feature to prevent data corruption in event of a split brain.
EVMD:
- Generates events when things happen
- Spawns a permanent child evmlogger
- Evmlogger, on demand, spawns children
- Scans callout directory and invokes callouts.
- Runs as Oracle.
- Restarted automatically on failure
Once the above processes are running, they will automatically start the following services in the following order if they are enabled.
- The nodeapps (gsd, VIP, ons, listener) are brought online.
- The ASM instances are brought online.
- The database instances are brought online.
- Any defined services are brought online.
Outputs from running some commands:
[root:/dssdb1]# crsctl stop crs
Stopping resources.
This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.Shutting down CSS daemon.
Shutdown request successfully issued.
[root:/dssdb1]#
[root:/dssdb1]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
[root:/dssdb1]#
[oracle:/dssdb1]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle:/dssdb1]$
If you see message other than above lines, you may use "ps -ef | grep d.bin" and "crs_stat -t" to check the status on each daemon and service.
Oracle provides an utility CLUVFY in $ORACLE_HOME/bin to verify the CRS as well:
[oracle:/dssdb1]$ cluvfy stage -post crsinst -n all -verbose
crs_stat -t --> Shows HA resource / service status (hard to read)
crsstat --> Ouptut of crs_stat -t formatted nicely (see Metalink note 259301.1)
crs_stop -all --> Stops all registered resources (but keeps CRS running!)
crs_start -all --> Starts all registered resources
crsctl check crs --> Verifies CSS,CRS,EVM functioning
crsctl stop crs --> Stops crs and all other services
crsctl start crs --> Starts crs and all other services
Two commands
crsctl disable crs --> Prevents CRS from starting on reboot
crsctl enable crs --> Enables CRS start on reboot
will update the file /etc/oracle/scls_scr/Node_name/root/crsstart which contains the string “enable” or “disable” as appropriate.
Command "ps -ef | grep d.bin" will check three main background processes. They are normally started by init during the operating system boot process. They can be started and stopped manually by issuing the command /etc/init.d/init.crs {start | stop | enable | disable} (or /etc/init.crs {start | stop | enable | disable})
oracle 2498 2091 0 Aug 18 - 8:18 /u01/crs/oracle/product/crs/bin/evmd.bin
root 2580 1927 0 Aug 18 - 705:21 /u01/crs/oracle/product/crs/bin/crsd.bin reboot
oracle 2662 2542 0 Aug 18 - 557:13 /u01/crs/oracle/product/crs/bin/ocssd.bin
root 2785 2951 0 Aug 18 - 1:59 /u01/crs/oracle/product/crs/bin/oprocd.bin run -t 1000 -m 500 -f
Here is a short description of each of the CRS daemon processes (Note 259301.1):
CRSD:
- Engine for HA operation
- Manages 'application resources'
- Starts, stops, and fails 'application resources' over
- Spawns separate 'actions' to start/stop/check application resources
- Maintains configuration profiles in the OCR
- Stores current known state in the OCR.
- Runs as root
- Is restarted automatically on failure
OCSSD:
- OCSSD is part of RAC and Single Instance with ASM
- Provides access to node membership
- Provides group services
- Provides basic cluster locking
- Integrates with existing vendor clusteware, when present
- Can also runs without integration to vendor clustware
- Runs as Oracle.
- Failure exit causes machine reboot. --> This is a feature to prevent data corruption in event of a split brain.
EVMD:
- Generates events when things happen
- Spawns a permanent child evmlogger
- Evmlogger, on demand, spawns children
- Scans callout directory and invokes callouts.
- Runs as Oracle.
- Restarted automatically on failure
Once the above processes are running, they will automatically start the following services in the following order if they are enabled.
- The nodeapps (gsd, VIP, ons, listener) are brought online.
- The ASM instances are brought online.
- The database instances are brought online.
- Any defined services are brought online.
Outputs from running some commands:
[root:/dssdb1]# crsctl stop crs
Stopping resources.
This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.Shutting down CSS daemon.
Shutdown request successfully issued.
[root:/dssdb1]#
[root:/dssdb1]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
[root:/dssdb1]#
[oracle:/dssdb1]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle:/dssdb1]$
If you see message other than above lines, you may use "ps -ef | grep d.bin" and "crs_stat -t" to check the status on each daemon and service.
Oracle provides an utility CLUVFY in $ORACLE_HOME/bin to verify the CRS as well:
[oracle:/dssdb1]$ cluvfy stage -post crsinst -n all -verbose
Wednesday, September 17, 2008
Database views for backuppiece info
1. On the RMAN catalog database
SELECT bp_key backuppiece_key, bs_key backupset_key, recid, set_count, incremental_level, handle, media, completion_time, status,
decode(backup_type, 'D', 'FULL', 'I', 'INCREMENTAL', 'L', 'LOGS') type
FROM rc_backup_piece
WHERE db_key = (select max(db_key) FROM rc_database WHERE name = 'PDWS')
-- and recid=2876
-- and status = 'X'
ORDER BY completion_time desc
;
After you get the backupset_key from this query, you can use "RMAN> list backupset backupset_key" or "RMAN> list backuppiece backuppiece_key" to see its contents (data files and/or archivelogs).
Once a backup piece has been deleted by "delete expired backup" and "delete obsolete", its record will be removed from this view and so all information on the backup piece in RMAN will be gone.
View RC_BACKUP_SET gives similar information, but is less useful.
2. On the target database
SELECT recid, set_count, handle, status, tag, media, completion_time, deleted
FROM v$BACKUP_PIECE
-- where recid=2876
order by completion_time desc;
This view keeps more records (maybe three months). But, after a backup piece has been deleted by "delete expired backup" and "delete obsolete", the HANDLE column of its record will become blank. So it is only useful if you want to find the tape ID from the MEDIA column.
SELECT bp_key backuppiece_key, bs_key backupset_key, recid, set_count, incremental_level, handle, media, completion_time, status,
decode(backup_type, 'D', 'FULL', 'I', 'INCREMENTAL', 'L', 'LOGS') type
FROM rc_backup_piece
WHERE db_key = (select max(db_key) FROM rc_database WHERE name = 'PDWS')
-- and recid=2876
-- and status = 'X'
ORDER BY completion_time desc
;
After you get the backupset_key from this query, you can use "RMAN> list backupset backupset_key" or "RMAN> list backuppiece backuppiece_key" to see its contents (data files and/or archivelogs).
Once a backup piece has been deleted by "delete expired backup" and "delete obsolete", its record will be removed from this view and so all information on the backup piece in RMAN will be gone.
View RC_BACKUP_SET gives similar information, but is less useful.
2. On the target database
SELECT recid, set_count, handle, status, tag, media, completion_time, deleted
FROM v$BACKUP_PIECE
-- where recid=2876
order by completion_time desc;
This view keeps more records (maybe three months). But, after a backup piece has been deleted by "delete expired backup" and "delete obsolete", the HANDLE column of its record will become blank. So it is only useful if you want to find the tape ID from the MEDIA column.
Catalog the RMAN backuppieces
After the RMAN records are already deleted (by "delete expired backup" or "delete obsolete"), you need to catalog the RMAN backuppieces once you have the tape containing the RMAN backup of those backup pieces.
Same on the disk. After a file is deleted by RMAN, the file will no longer exist on disk. If somehow you saved a copy of the backup and later want to know what is in it, you have to use the RMAN "catalog" command to have RMAN review the file header, place in the controlfile the details about the backup. Then you run the "list backup" command to get details on the backup.
I believe Oracle is more confident on getting them back into RMAN if the backup pieces are on the disk than on the tape. If the backup is on the disk, see Note 727655.1 on getting it back. Here is an unpublished document (Note 550082.1) by Oracle on how to catalog tape backup pieces. I have not got chance to test it.
~~~~~~~~~~~~~~~~~~~~~~~~
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1 to 11.1
Information in this document applies to any platform.
Applies to databases release 10g and further
Goal
Starting with 10g, it's possible to use the rman CATALOG command to add backuppieces stored in disk to the rman repository.
You may need to catalog backup pieces in the following situations:
. You copy or move a backup piece with an operating system utility and want it to be usable by RMAN.
. The RMAN metadata for the backup piece was removed, but the backup piece still exists. This situation can occur if you ran the DELETE command on a backup piece that was only temporarily unavailable.
. You make a NOCATALOG backup on one database host in a Data Guard environment and move the backup piece to the same location on a different database host. In this case, the recovery catalog has no record of the original backup piece.
. You do not use a recovery catalog and must re-create the control file, thereby losing all RMAN repository data. Cataloging your backups makes them available again.
. When control file autobackup is disabled, you back up the control file and then back up the archived redo logs. You can restore and mount the control file, but must catalog the backup pieces containing the archived redo logs backed up after the control file.
But it's not possible to use the CATALOG command for backup pieces stored in TAPE. This note explains how to add backuppieces stored in TAPE to the repository
Solution
From 10.1, there is an undocumented command that allows to catalog tape backup pieces:
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '';
* Prerequisites
1. Use automatic channel configuration. It's mandatory to configure one sbt_tape device channel in your rman automatic configuration parameters;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS ''
2. It's necessary to know the backup piece file name in the tape and the backup piece file needs to be available and accessible.
* How to
Once there is a tape channel configured for accessing to the tape, the rman CATALOG command can be used to insert in RMAN catalog the tape backup piece:
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '';
* Examples
- This is an example using Oracle Secure Backup (OSB):
1. Define a tape channel in the RMAN automatic configuration:
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so,ENV=(OB_MEDIA_FAMILY=RMAN-DEFAULT)';
2. Check that channel configuration is correct
RMAN> show all;
....
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so,ENV=(OB_MEDIA_FAMILY=RMAN-DEFAULT)';
....
3. Catalog the backup piece
RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '0pivagf8_1_1';
- The following will catalog a backuppiece on netbackup:
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_CLASS=oraclebkup, SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so';
RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'lij1qaa3_1_1';
Same on the disk. After a file is deleted by RMAN, the file will no longer exist on disk. If somehow you saved a copy of the backup and later want to know what is in it, you have to use the RMAN "catalog" command to have RMAN review the file header, place in the controlfile the details about the backup. Then you run the "list backup" command to get details on the backup.
I believe Oracle is more confident on getting them back into RMAN if the backup pieces are on the disk than on the tape. If the backup is on the disk, see Note 727655.1 on getting it back. Here is an unpublished document (Note 550082.1) by Oracle on how to catalog tape backup pieces. I have not got chance to test it.
~~~~~~~~~~~~~~~~~~~~~~~~
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1 to 11.1
Information in this document applies to any platform.
Applies to databases release 10g and further
Goal
Starting with 10g, it's possible to use the rman CATALOG command to add backuppieces stored in disk to the rman repository.
You may need to catalog backup pieces in the following situations:
. You copy or move a backup piece with an operating system utility and want it to be usable by RMAN.
. The RMAN metadata for the backup piece was removed, but the backup piece still exists. This situation can occur if you ran the DELETE command on a backup piece that was only temporarily unavailable.
. You make a NOCATALOG backup on one database host in a Data Guard environment and move the backup piece to the same location on a different database host. In this case, the recovery catalog has no record of the original backup piece.
. You do not use a recovery catalog and must re-create the control file, thereby losing all RMAN repository data. Cataloging your backups makes them available again.
. When control file autobackup is disabled, you back up the control file and then back up the archived redo logs. You can restore and mount the control file, but must catalog the backup pieces containing the archived redo logs backed up after the control file.
But it's not possible to use the CATALOG command for backup pieces stored in TAPE. This note explains how to add backuppieces stored in TAPE to the repository
Solution
From 10.1, there is an undocumented command that allows to catalog tape backup pieces:
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '
* Prerequisites
1. Use automatic channel configuration. It's mandatory to configure one sbt_tape device channel in your rman automatic configuration parameters;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS '
2. It's necessary to know the backup piece file name in the tape and the backup piece file needs to be available and accessible.
* How to
Once there is a tape channel configured for accessing to the tape, the rman CATALOG command can be used to insert in RMAN catalog the tape backup piece:
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '
* Examples
- This is an example using Oracle Secure Backup (OSB):
1. Define a tape channel in the RMAN automatic configuration:
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so,ENV=(OB_MEDIA_FAMILY=RMAN-DEFAULT)';
2. Check that channel configuration is correct
RMAN> show all;
....
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so,ENV=(OB_MEDIA_FAMILY=RMAN-DEFAULT)';
....
3. Catalog the backup piece
RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '0pivagf8_1_1';
- The following will catalog a backuppiece on netbackup:
RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_CLASS=oraclebkup, SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so';
RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'lij1qaa3_1_1';
Monday, September 15, 2008
Crosscheck archivelog all
The RMAN commands "crosscheck archivelog all" and "change archivelog all crosscheck" will check the archivelogs whether they are physically available on disk. If the archivelogs are no longer on disk, then the status in the RMAN catalog and controlfile will be marked from"A" for available to "X" for expired.
The RMAN command "delete noprompt expired archivelog all" will only delete entries on expired archivelogs from the RMAN catalog and controlfile (not from the disk bacause "X" means the archivelog has been removed from OS).
To see the list of expired archivelogs, run "delete expired archivelog all" and then answer "no" on confirmation.
Two ways to get archivelog space back from disk:
1. If the archivelogs have never been backed up to tape, back them up and delete them in one command:
RMAN> backup archivelog until logseqdelete all input;
2. If you know the archivelogs have a good backup on the tape, you can remove them at OS level (Note 249452.1):
a) delete unwanted archive log files from disk ( rm /del )
b) connect to rman
c) RMAN> crosscheck archivelog all;
d) RMAN> delete expired archivelog all;
Note that "crosscheck backup of archivelog all" means crosschecking backups of archivelog files on the tape, and "delete expired backup of archivelog all" deletes the backup pieces from the MMD database. (??)
The RMAN command "delete noprompt expired archivelog all" will only delete entries on expired archivelogs from the RMAN catalog and controlfile (not from the disk bacause "X" means the archivelog has been removed from OS).
To see the list of expired archivelogs, run "delete expired archivelog all" and then answer "no" on confirmation.
Two ways to get archivelog space back from disk:
1. If the archivelogs have never been backed up to tape, back them up and delete them in one command:
RMAN> backup archivelog until logseq
2. If you know the archivelogs have a good backup on the tape, you can remove them at OS level (Note 249452.1):
a) delete unwanted archive log files from disk ( rm /del )
b) connect to rman
c) RMAN> crosscheck archivelog all;
d) RMAN> delete expired archivelog all;
Note that "crosscheck backup of archivelog all" means crosschecking backups of archivelog files on the tape, and "delete expired backup of archivelog all" deletes the backup pieces from the MMD database. (??)
Crosscheck Backup
The RMAN command "crosscheck backup" will verify whether the RMAN backuppieces are still on the tape. If the RMAN backuppieces are no longer available in the tape, then the status will be changed from "A" for available to "X" for expired. Meanwhile, the RMAN command "delete force expired backup" will permanently delete the RMAN backuppieces from the tape that have a status of "X".
I believe "crosscheck backup" only check the MMD (media management device) database. Even the tape that holds the backup piece is not physically on the tape drive, the command will still consider the backup piece is available if it has not been deleted by the "delete obsolete" command (and is within the retention period).
I ran "list backup" and "list backupset", and saw every backup piece from both commands is on the list of backup pieces found Available from "crosscheck backup" command.
What could make the list of obsolete backups and the list of expired backups different?
"delete force expired backup" will permanently delete the RMAN backups that have a status of "X". However, the "force" clause will delete the corresponding records in the RMAN data dictionary even if RMAN cannot find the corresponding RMAN backuppieces on tape. My job running "delete force expired backup" against Veritas NetBackup hanged when the tape was not on the tape drive, but the one without "force" clause worked fine. It seems that "delete expired backup" only deletes entries from the MMD database.
If you just want to see the list of expired backups, use "delete expired backup" and then answer "no" on confirmation, or query the STATUS column (with 'X') of view RC_BACKUP_PIECE.
Sometimes, you may see erros:
RMAN-06207: WARNING: 41 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: ----- --------------------------------
RMAN-06214: Backup Piece ukjamesq_1_1
RMAN-06214: Backup Piece uljamesr_1_1
The RMAN-06207 and RMAN-06208 errors indicate that the RMAN backuppieces are no longer in the tape. To avoid the two errors, run the following RMAN commands, as shown below.
RMAN> allocate channel for maintenance type 'SBT_TAPE';
RMAN> crosscheck backup;
RMAN> delete expired backup;
But, it seems "crosscheck backup" only looks back to a certain time frame. Fairly old backup pieces may not get the status checked by the command, and the two errors keep showing up in the RMAN log on them. In some occasions, I had to query the BS_KEY column from view RC_BACKUP_PIECE on the catalog database, and then run "RMAN> delete FORCE NOPROMPT BACKUPSET bs_key#;" to delete the backupset after maintenace CHANNEL allocation. Keyword "force" is necessary in below run:
RMAN> delete force NOPROMPT BACKUPSET 218128;
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
218353 218128 1 1 AVAILABLE SBT_TAPE 36j9fc7c_1_1
deleted backup piece
backup piece handle=36j9fc7c_1_1 recid=102 stamp=647475436
Deleted 1 objects
One strange error I got is that I could not use tape channle to run the command on an instance:
RMAN> run {
2> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
3> crosscheck backup;
4> RELEASE CHANNEL ch01;
5> }
allocated channel: ch01
channel ch01: sid=79 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
released channel: ch01
RMAN-00571: ===================================
RMAN-00569: ==== ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===================================
RMAN-03002: failure of crosscheck command at 09/15/2008 12:40:34
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
I believe "crosscheck backup" only check the MMD (media management device) database. Even the tape that holds the backup piece is not physically on the tape drive, the command will still consider the backup piece is available if it has not been deleted by the "delete obsolete" command (and is within the retention period).
I ran "list backup" and "list backupset", and saw every backup piece from both commands is on the list of backup pieces found Available from "crosscheck backup" command.
What could make the list of obsolete backups and the list of expired backups different?
"delete force expired backup" will permanently delete the RMAN backups that have a status of "X". However, the "force" clause will delete the corresponding records in the RMAN data dictionary even if RMAN cannot find the corresponding RMAN backuppieces on tape. My job running "delete force expired backup" against Veritas NetBackup hanged when the tape was not on the tape drive, but the one without "force" clause worked fine. It seems that "delete expired backup" only deletes entries from the MMD database.
If you just want to see the list of expired backups, use "delete expired backup" and then answer "no" on confirmation, or query the STATUS column (with 'X') of view RC_BACKUP_PIECE.
Sometimes, you may see erros:
RMAN-06207: WARNING: 41 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: ----- --------------------------------
RMAN-06214: Backup Piece ukjamesq_1_1
RMAN-06214: Backup Piece uljamesr_1_1
The RMAN-06207 and RMAN-06208 errors indicate that the RMAN backuppieces are no longer in the tape. To avoid the two errors, run the following RMAN commands, as shown below.
RMAN> allocate channel for maintenance type 'SBT_TAPE';
RMAN> crosscheck backup;
RMAN> delete expired backup;
But, it seems "crosscheck backup" only looks back to a certain time frame. Fairly old backup pieces may not get the status checked by the command, and the two errors keep showing up in the RMAN log on them. In some occasions, I had to query the BS_KEY column from view RC_BACKUP_PIECE on the catalog database, and then run "RMAN> delete FORCE NOPROMPT BACKUPSET bs_key#;" to delete the backupset after maintenace CHANNEL allocation. Keyword "force" is necessary in below run:
RMAN> delete force NOPROMPT BACKUPSET 218128;
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
218353 218128 1 1 AVAILABLE SBT_TAPE 36j9fc7c_1_1
deleted backup piece
backup piece handle=36j9fc7c_1_1 recid=102 stamp=647475436
Deleted 1 objects
One strange error I got is that I could not use tape channle to run the command on an instance:
RMAN> run {
2> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
3> crosscheck backup;
4> RELEASE CHANNEL ch01;
5> }
allocated channel: ch01
channel ch01: sid=79 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
released channel: ch01
RMAN-00571: ===================================
RMAN-00569: ==== ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===================================
RMAN-03002: failure of crosscheck command at 09/15/2008 12:40:34
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
Friday, September 12, 2008
Find the Archivelog names by using the SCN
During database recovery, you may have a SCN number and need to know the archivelog names. Here is the SQL for the answer:
column first_change# format 9,999,999,999
column next_change# format 9,999,999,999
alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 35297312527 between first_change# and next_change#;
If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.
SEQUENCE# number usually shows up on the archivelog name. You can use RMAN command to restore them:
restore archivelog from logseq=45164 until logseq=45179;
Or, use commands to check the backup status:
list backup of archivelog all completed after 'SYSDATE - 21';
list backup of archivelog from logseq=45164 until logseq=45179;
column first_change# format 9,999,999,999
column next_change# format 9,999,999,999
alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 35297312527 between first_change# and next_change#;
If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.
SEQUENCE# number usually shows up on the archivelog name. You can use RMAN command to restore them:
restore archivelog from logseq=45164 until logseq=45179;
Or, use commands to check the backup status:
list backup of archivelog all completed after 'SYSDATE - 21';
list backup of archivelog from logseq=45164 until logseq=45179;
RMAN "delete noprompt obsolete;"
The "delete obsolete" or "delete noprompt obsolete" will delete the archivelog files past the retention from disk and will also delete any backups on disk or tape.
allocate channel for maintenance type 'SBT_TAPE';
delete noprompt obsolete;
release channel;
will list two parts
. The first part lists the obsolete backups and copies (including archive logs).
. The 2nd part confirms what have been really deleted on archive logs and backup piece, with statement "Deleted xx objects".
When flash recovery area (FRA) is used, Oracle will automatically remove archive logs when space pressure is seen in the FRA. "delete obsolete" command will not remove obsolete archivelog files from the FRA, and so it only reports the obsolete archive logs without "Deleted xx objects" under them.
After a osboleted backpiece has been deleted by the command, the LIST command will not be able to see it any more. For example, I see follwoings on backup piece 635938 in the log:
Backup Set 635930 12-SEP-08
Backup Piece 635938 12-SEP-08 vcjqcgn8_1_1
deleted backup piece
backup piece handle=vcjqcgn8_1_1 recid=2021 stamp=665207528
Now, LIST on them returns errors:
RMAN> list backupset 635930;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571:===================================
RMAN-03002: failure of list command at 09/12/2008 15:32:05
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20215: backup set not found
RMAN-06159: error while looking up backup set
RMAN> list backuppiece 635938;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ==================================
RMAN-03002: failure of list command at 09/12/2008 15:32:34
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20260: backup piece not found in the recovery catalog
RMAN-06092: error while looking up backup piece
You can use "report obsolete" to find the obsolete backups. If you want to find what will become obsolete in next backup run, use "report obsolete redundancy 6" if the retention redundancy is 7.
allocate channel for maintenance type 'SBT_TAPE';
delete noprompt obsolete;
release channel;
will list two parts
. The first part lists the obsolete backups and copies (including archive logs).
. The 2nd part confirms what have been really deleted on archive logs and backup piece, with statement "Deleted xx objects".
When flash recovery area (FRA) is used, Oracle will automatically remove archive logs when space pressure is seen in the FRA. "delete obsolete" command will not remove obsolete archivelog files from the FRA, and so it only reports the obsolete archive logs without "Deleted xx objects" under them.
After a osboleted backpiece has been deleted by the command, the LIST command will not be able to see it any more. For example, I see follwoings on backup piece 635938 in the log:
Backup Set 635930 12-SEP-08
Backup Piece 635938 12-SEP-08 vcjqcgn8_1_1
deleted backup piece
backup piece handle=vcjqcgn8_1_1 recid=2021 stamp=665207528
Now, LIST on them returns errors:
RMAN> list backupset 635930;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571:===================================
RMAN-03002: failure of list command at 09/12/2008 15:32:05
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20215: backup set not found
RMAN-06159: error while looking up backup set
RMAN> list backuppiece 635938;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ==================================
RMAN-03002: failure of list command at 09/12/2008 15:32:34
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20260: backup piece not found in the recovery catalog
RMAN-06092: error while looking up backup piece
You can use "report obsolete" to find the obsolete backups. If you want to find what will become obsolete in next backup run, use "report obsolete redundancy 6" if the retention redundancy is 7.
Monday, August 25, 2008
Command line to compile invalid objects
You can have a script to compile invalid 11i database objects, and put the script in cron.
1. Script
#!/bin/ksh
APPSPASS="pwd1"
SYSTEMPASS="pwd2"
. /path/to/EBSSITE_servername.env
echo ${APPSPASS}
sqlplus -s APPS @${AD_TOP}/admin/sql/adutlrcmp.pls APPLSYS ${APPSPASS} APPS ${APPSPASS} ${SYSTEMPASS} 8 0 NONE FALSE
2. Log will look like:
Enter password:
Arguments are:
AOL_schema = APPLSYS, AOL_password = *****,Schema_to_compile = APPS, Schema_to_compile_pw = *****,SYSTEM_password = *****, Total_workers = 8, Logical_worker_num = 0
Object_type_to_not_compile = NONE
Use_stored_dependencies = FALSE
Connected.
Running utl_recomp.recomp_parallel(8), if it exists
OWNER NUM_INVALID
------------------ -----------
BOLINF 3
APPS 1
Elapsed: 00:00:00.68
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
STATUS_MESSAGE
------------------------------------------------------------------------------
Running UTL_RECOMP.RECOMP_PARALLEL...
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
STATUS_MESSAGE
------------------------------------------------------------------------------
Successfully ran UTL_RECOMP.RECOMP_PARALLEL
no rows selected
Elapsed: 00:00:00.28
Done running utl_recomp.recomp_parallel
Commit complete.
Elapsed: 00:00:00.00
3. Cron line (like below)
0 * * * * /path/scriptname.sh > /path/to_log.log 2>&1 &
1. Script
#!/bin/ksh
APPSPASS="pwd1"
SYSTEMPASS="pwd2"
. /path/to/EBSSITE_servername.env
echo ${APPSPASS}
sqlplus -s APPS @${AD_TOP}/admin/sql/adutlrcmp.pls APPLSYS ${APPSPASS} APPS ${APPSPASS} ${SYSTEMPASS} 8 0 NONE FALSE
2. Log will look like:
Enter password:
Arguments are:
AOL_schema = APPLSYS, AOL_password = *****,Schema_to_compile = APPS, Schema_to_compile_pw = *****,SYSTEM_password = *****, Total_workers = 8, Logical_worker_num = 0
Object_type_to_not_compile = NONE
Use_stored_dependencies = FALSE
Connected.
Running utl_recomp.recomp_parallel(8), if it exists
OWNER NUM_INVALID
------------------ -----------
BOLINF 3
APPS 1
Elapsed: 00:00:00.68
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
STATUS_MESSAGE
------------------------------------------------------------------------------
Running UTL_RECOMP.RECOMP_PARALLEL...
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
STATUS_MESSAGE
------------------------------------------------------------------------------
Successfully ran UTL_RECOMP.RECOMP_PARALLEL
no rows selected
Elapsed: 00:00:00.28
Done running utl_recomp.recomp_parallel
Commit complete.
Elapsed: 00:00:00.00
3. Cron line (like below)
0 * * * * /path/scriptname.sh > /path/to_log.log 2>&1 &
Sunday, August 17, 2008
Find the target on a host for Grid Control
One database disappeared from Grid Control. I executed "agentca -d" on the target server to re-run the agent configuration assistant and look for new targets. It did bring the database back to Grid Control.
/u01/app/oracle/product/agent10g/bin ORA$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /u01/app/oracle/product/agent10g
Agent binaries : /u01/app/oracle/product/agent10g
Agent Process ID : 585838
Parent Process ID : 569446
Agent URL : http://dssqa:3872/emd/main/
Repository URL : http://dssgrid:4889/em/upload/
Started at : 2008-08-15 19:44:26
Started by user : oracle
Last Reload : 2008-08-16 01:26:09
Last successful upload : 2008-08-16 13:42:01
Total Megabytes of XML files uploaded so far : 94.10
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 25.83%
Last successful heartbeat to OMS : 2008-08-16 13:41:51
---------------------------------------------------------------
Agent is Running and Ready
/u01/app/oracle/product/agent10g/bin ORA$ ./agentca -d
Stopping the agent using /u01/app/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/app/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent10g
ACTION=Configure MODE=Perform
RESPONSE_FILE=/u01/app/oracle/product/agent10g/response_file
RERUN=TRUE
INV_PTR_LOC=/etc/oraInst.loc
Perform - mode is starting for action: Configure
Perform - mode finished for action: Configure
You can see the log file: /u01/app/oracle/product/agent10g/cfgtoollogs/oui/confi gActions2008-08-16_01-43-06-PM.log
/u01/app/oracle/product/agent10g/bin ORA$ ls -al agentca
-rwxr-xr-x 1 oracle oinstall 657 Oct 19 2007 agentca
/u01/app/oracle/product/agent10g/bin ORA$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /u01/app/oracle/product/agent10g
Agent binaries : /u01/app/oracle/product/agent10g
Agent Process ID : 585838
Parent Process ID : 569446
Agent URL : http://dssqa:3872/emd/main/
Repository URL : http://dssgrid:4889/em/upload/
Started at : 2008-08-15 19:44:26
Started by user : oracle
Last Reload : 2008-08-16 01:26:09
Last successful upload : 2008-08-16 13:42:01
Total Megabytes of XML files uploaded so far : 94.10
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 25.83%
Last successful heartbeat to OMS : 2008-08-16 13:41:51
---------------------------------------------------------------
Agent is Running and Ready
/u01/app/oracle/product/agent10g/bin ORA$ ./agentca -d
Stopping the agent using /u01/app/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/app/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent10g
ACTION=Configure MODE=Perform
RESPONSE_FILE=/u01/app/oracle/product/agent10g/response_file
RERUN=TRUE
INV_PTR_LOC=/etc/oraInst.loc
Perform - mode is starting for action: Configure
Perform - mode finished for action: Configure
You can see the log file: /u01/app/oracle/product/agent10g/cfgtoollogs/oui/confi gActions2008-08-16_01-43-06-PM.log
/u01/app/oracle/product/agent10g/bin ORA$ ls -al agentca
-rwxr-xr-x 1 oracle oinstall 657 Oct 19 2007 agentca
Thursday, August 7, 2008
"alter profile ..." could shut down entire 11i applications
Co-worker misunderstood the concept of database password management when tried to set the length of 11i Application password expiration, and ran two lines as sysdba:
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 1;
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 2;
That action expired APPS password, and even SYSTEM password:
SQL> conn system/xxx@tns
ERROR:ORA-28002: the password will expire within 0 days
Connected.
and so the entire Oracle EBS instance went down.
To fix the issue, tried following steps:
1. Run the below sql's as sysdba to change the profile back.
1) ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;
2) ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;
2. Then try to connect as apps user in SQL*Plus. If it is requesting for a password change, cancel it.
3. Use normal approach of changing the apps password.
1) Stop MT services
2) Change APPS password using FNDCPASS
3) Change the password in wdbsvr.app and cgicmd.dat
4) Execute cmclean.sql connected to the database as APPS
5) Start MT services.
But, FNDCPASS keep getting error:
$ FNDCPASS apps/xxxxxx 0 Y system/xxxxxx SYSTEM APPLSYS xxxxxxx
APP-FND-01564: ORACLE error 28001 in AFPCOA
Cause: AFPCOA failed due to ORA-28001: the password has expired
Even, after APPS account is unlocked by SQL*Plus,
$ FNDCPASS apps/*** 0 Y system/*** system APPLSYS ***
fails with these error messages in logfile:
FNDCPASS was not able to decrypt password for ANONYMOUS during applsys password change.
FNDCPASS was not able to decrypt password for AUTOINSTALL during applsys password change.
FNDCPASS was not able to decrypt password for CONCURRENT MANAGER during applsys password change.
... 500 lines generated.
Metalink note 459601.1 suggests to change password manually for 500 users, which doesn't sound like a good solution.
Finally, we have to refresh the instance by using another instance.
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 1;
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 2;
That action expired APPS password, and even SYSTEM password:
SQL> conn system/xxx@tns
ERROR:ORA-28002: the password will expire within 0 days
Connected.
and so the entire Oracle EBS instance went down.
To fix the issue, tried following steps:
1. Run the below sql's as sysdba to change the profile back.
1) ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;
2) ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;
2. Then try to connect as apps user in SQL*Plus. If it is requesting for a password change, cancel it.
3. Use normal approach of changing the apps password.
1) Stop MT services
2) Change APPS password using FNDCPASS
3) Change the password in wdbsvr.app and cgicmd.dat
4) Execute cmclean.sql connected to the database as APPS
5) Start MT services.
But, FNDCPASS keep getting error:
$ FNDCPASS apps/xxxxxx 0 Y system/xxxxxx SYSTEM APPLSYS xxxxxxx
APP-FND-01564: ORACLE error 28001 in AFPCOA
Cause: AFPCOA failed due to ORA-28001: the password has expired
Even, after APPS account is unlocked by SQL*Plus,
$ FNDCPASS apps/*** 0 Y system/*** system APPLSYS ***
fails with these error messages in logfile:
FNDCPASS was not able to decrypt password for ANONYMOUS during applsys password change.
FNDCPASS was not able to decrypt password for AUTOINSTALL during applsys password change.
FNDCPASS was not able to decrypt password for CONCURRENT MANAGER during applsys password change.
... 500 lines generated.
Metalink note 459601.1 suggests to change password manually for 500 users, which doesn't sound like a good solution.
Finally, we have to refresh the instance by using another instance.
Friday, August 1, 2008
Run 11i SQL files to fix an issue on OPP
All concurrent managers are running, but the Output Post Processor does not process requests and its log gives error:
[7/28/08 1:06:08 PM] [main] Starting GSF service with concurrent process id = 93868.
[7/28/08 1:06:08 PM] [main] Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
[7/28/08 1:06:08 PM] [Thread-4] Service thread starting up.
[7/28/08 1:06:08 PM] [Thread-5] Service thread starting up.
[7/28/08 1:06:18 PM] [EXCEPTION] [OPPServiceThread0] java.sql.SQLException: ORA-00600: internal error code, arguments: [kqludp2],
[0x0A68239A8], [0], [], [], [], [], []
ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85
ORA-06512: at line 1
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)
It seems that some OPP packages were deleted after patching (and FND_CP_GSM_OPP_AQ queue was not up and running), because below query only got 12 rows:
SQL> select OBJECT_NAME , CREATED, STATUS from all_objects where OBJECT_NAME like 'FND_CP_GSM%';
Per Metalink Note 311851.1, the fix is to re-create the packages by executing two seeded files under $FND_TOP. After the fix is applied, the same query returns 21 rows and OPP works well.
Here is how to run the two 11i files:
=========================
Ran script: afopp001.sql:
=========================
SQL> select name, user from v$database;
NAME USER
--------- ------------------------------
TJYTI SYSTEM
SQL> !date
Thu Jul 31 04:55:32 MST 2008
SQL> !pwd
/tjyti/applmgr/11510/fnd/11.5.0/patch/115/sql
SQL> !ls -ltr afopp001.sql
-rwxr-xr-x 1 aptjyti aatjyti 2079 Mar 21 2005 afopp001.sql
SQL> @afopp001.sql
Enter value for 1: system
Enter value for 2: ******
Connected.
PL/SQL procedure successfully completed.
Grant succeeded.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[AMD64] aptjyti@auohsjyt02 >
=========================
Ran script: afopp002.sql:
=========================
SQL> select name, user from v$database;
NAME USER
--------- ------------------------------
TJYTI APPLSYS
SQL> !date
Thu Jul 31 04:59:23 MST 2008
SQL> !pwd
/tjyti/applmgr/11510/fnd/11.5.0/patch/115/sql
SQL> !ls -ltr afopp002.sql
-rwxr-xr-x 1 aptjyti aatjyti 4536 Feb 8 2006 afopp002.sql
SQL> @afopp002.sql
Enter value for 1: applsys
Enter value for 2: ******
Connected.
PL/SQL procedure successfully completed.
......
[7/28/08 1:06:08 PM] [main] Starting GSF service with concurrent process id = 93868.
[7/28/08 1:06:08 PM] [main] Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
[7/28/08 1:06:08 PM] [Thread-4] Service thread starting up.
[7/28/08 1:06:08 PM] [Thread-5] Service thread starting up.
[7/28/08 1:06:18 PM] [EXCEPTION] [OPPServiceThread0] java.sql.SQLException: ORA-00600: internal error code, arguments: [kqludp2],
[0x0A68239A8], [0], [], [], [], [], []
ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85
ORA-06512: at line 1
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)
It seems that some OPP packages were deleted after patching (and FND_CP_GSM_OPP_AQ queue was not up and running), because below query only got 12 rows:
SQL> select OBJECT_NAME , CREATED, STATUS from all_objects where OBJECT_NAME like 'FND_CP_GSM%';
Per Metalink Note 311851.1, the fix is to re-create the packages by executing two seeded files under $FND_TOP. After the fix is applied, the same query returns 21 rows and OPP works well.
Here is how to run the two 11i files:
=========================
Ran script: afopp001.sql:
=========================
SQL> select name, user from v$database;
NAME USER
--------- ------------------------------
TJYTI SYSTEM
SQL> !date
Thu Jul 31 04:55:32 MST 2008
SQL> !pwd
/tjyti/applmgr/11510/fnd/11.5.0/patch/115/sql
SQL> !ls -ltr afopp001.sql
-rwxr-xr-x 1 aptjyti aatjyti 2079 Mar 21 2005 afopp001.sql
SQL> @afopp001.sql
Enter value for 1: system
Enter value for 2: ******
Connected.
PL/SQL procedure successfully completed.
Grant succeeded.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[AMD64] aptjyti@auohsjyt02 >
=========================
Ran script: afopp002.sql:
=========================
SQL> select name, user from v$database;
NAME USER
--------- ------------------------------
TJYTI APPLSYS
SQL> !date
Thu Jul 31 04:59:23 MST 2008
SQL> !pwd
/tjyti/applmgr/11510/fnd/11.5.0/patch/115/sql
SQL> !ls -ltr afopp002.sql
-rwxr-xr-x 1 aptjyti aatjyti 4536 Feb 8 2006 afopp002.sql
SQL> @afopp002.sql
Enter value for 1: applsys
Enter value for 2: ******
Connected.
PL/SQL procedure successfully completed.
......
Friday, July 25, 2008
Database audit and failed login trace
1. When database account keeps getting locked, below trigger is useful to find failed logins.
CREATE TABLE system.LOGIN_AUDIT
(
LOGIN_TIME DATE,
MACHINE VARCHAR2(64 BYTE),
IP_ADDR VARCHAR2(64 BYTE),
OSUSER VARCHAR2(30 BYTE),
USERNAME VARCHAR2(30 BYTE),
PROGRAM VARCHAR2(48 BYTE),
MODULE VARCHAR2(64 BYTE)
)
TABLESPACE users;
CREATE OR REPLACE TRIGGER system.LOGIN_AUDIT
AFTER SERVERERROR
ON DATABASE
DECLARE
v_prog login_audit.PROGRAM%TYPE;
v_mod login_audit.module%TYPE;
BEGIN
-- Login failure
IF (is_servererror(1017)) THEN
SELECT PROGRAM, module INTO v_prog, v_mod FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID') AND ROWNUM<2;
INSERT INTO LOGIN_AUDIT (
username
,machine
,ip_addr
,osuser
,login_time
,PROGRAM,
MODULE
)
VALUES (
SYS_CONTEXT('USERENV','SESSION_USER')
,SYS_CONTEXT('USERENV', 'HOST')
,SYS_CONTEXT('USERENV', 'IP_ADDRESS')
,SYS_CONTEXT('USERENV', 'OS_USER')
,SYSDATE
,v_prog
,v_mod
);
END IF;
END;
/
2. Metalink note 352389.1 also gives a way to find the source of failed login attempts
3. Database auditing
To turn on audit on database level may not practically acceptable to users due to potential impact on database performance and a database downtime.
Auditing can be enabled by setting the AUDIT_TRAIL init parameter, which has the following allowed values.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
. none or false - Auditing is disabled. This is the default.
. db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
. db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
. xml - Auditing is enabled, with all audit records stored as XML format OS files.
. xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
. os - Auditing is enabled, with all audit records saved to OS files.
The AUDIT_SYS_OPERATIONS init parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail defined by AUDIT_FILE_DEST parameter. The audit on activities by a user with DBA privilege is not controled by AUDIT_SYS_OPERATIONS parameter.
The AUDIT_FILE_DEST init parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used (In addition, V$XML_AUDIT_TRAIL for xml format). (It seems to me that AUDIT_FILE_DEST holds audit files for activities of SYSDBA or SYSOPER. All other audit files go to adump directory).
To audit all operations by user JY, run below lines by SYS:
SQL> CONNECT sys/password AS SYSDBA
SQL> AUDIT ALL BY jy BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY jy BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY jy BY ACCESS;
These options audit all DDL and DML, along with some system events.
. DDL (CREATE, ALTER & DROP of objects)
. DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
. SYSTEM EVENTS (LOGON, LOGOFF etc.)
Without specifying the username JY, the audit trial will be on all users. You can see all enabled options from view DBA_STMT_AUDIT_OPTS:
SELECT user_name, audit_option, success, failure
FROM sys.dba_stmt_audit_opts;
You can get the list of views that hold the audit trail by below query:
SELECT view_name FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%' ORDER BY view_name;
The three main views are:
. DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
. DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
. DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size. Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:
AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Fine grained auditing (FGA) extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. For more details, check out DBMS_FGA package.
CREATE TABLE system.LOGIN_AUDIT
(
LOGIN_TIME DATE,
MACHINE VARCHAR2(64 BYTE),
IP_ADDR VARCHAR2(64 BYTE),
OSUSER VARCHAR2(30 BYTE),
USERNAME VARCHAR2(30 BYTE),
PROGRAM VARCHAR2(48 BYTE),
MODULE VARCHAR2(64 BYTE)
)
TABLESPACE users;
CREATE OR REPLACE TRIGGER system.LOGIN_AUDIT
AFTER SERVERERROR
ON DATABASE
DECLARE
v_prog login_audit.PROGRAM%TYPE;
v_mod login_audit.module%TYPE;
BEGIN
-- Login failure
IF (is_servererror(1017)) THEN
SELECT PROGRAM, module INTO v_prog, v_mod FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID') AND ROWNUM<2;
INSERT INTO LOGIN_AUDIT (
username
,machine
,ip_addr
,osuser
,login_time
,PROGRAM,
MODULE
)
VALUES (
SYS_CONTEXT('USERENV','SESSION_USER')
,SYS_CONTEXT('USERENV', 'HOST')
,SYS_CONTEXT('USERENV', 'IP_ADDRESS')
,SYS_CONTEXT('USERENV', 'OS_USER')
,SYSDATE
,v_prog
,v_mod
);
END IF;
END;
/
2. Metalink note 352389.1 also gives a way to find the source of failed login attempts
3. Database auditing
To turn on audit on database level may not practically acceptable to users due to potential impact on database performance and a database downtime.
Auditing can be enabled by setting the AUDIT_TRAIL init parameter, which has the following allowed values.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
. none or false - Auditing is disabled. This is the default.
. db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
. db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
. xml - Auditing is enabled, with all audit records stored as XML format OS files.
. xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
. os - Auditing is enabled, with all audit records saved to OS files.
The AUDIT_SYS_OPERATIONS init parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail defined by AUDIT_FILE_DEST parameter. The audit on activities by a user with DBA privilege is not controled by AUDIT_SYS_OPERATIONS parameter.
The AUDIT_FILE_DEST init parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used (In addition, V$XML_AUDIT_TRAIL for xml format). (It seems to me that AUDIT_FILE_DEST holds audit files for activities of SYSDBA or SYSOPER. All other audit files go to adump directory).
To audit all operations by user JY, run below lines by SYS:
SQL> CONNECT sys/password AS SYSDBA
SQL> AUDIT ALL BY jy BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY jy BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY jy BY ACCESS;
These options audit all DDL and DML, along with some system events.
. DDL (CREATE, ALTER & DROP of objects)
. DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
. SYSTEM EVENTS (LOGON, LOGOFF etc.)
Without specifying the username JY, the audit trial will be on all users. You can see all enabled options from view DBA_STMT_AUDIT_OPTS:
SELECT user_name, audit_option, success, failure
FROM sys.dba_stmt_audit_opts;
You can get the list of views that hold the audit trail by below query:
SELECT view_name FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%' ORDER BY view_name;
The three main views are:
. DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
. DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
. DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size. Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:
AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Fine grained auditing (FGA) extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. For more details, check out DBMS_FGA package.
Wednesday, July 23, 2008
Apply patch by adpatch or opatch
- adpatch to apply EBS patches
- The autopatch log is in below directory with default name adpatch.log:
$APPL_TOP/admin/<SID>/log
File adpatch.lgi is the file that has information (about patch applied), for example, about files
that were not applied because of some kind of problem that occurs or because patches had been applied already. See below UPDATES 2.
Ignore warning messages. See Doc ID 401424.1 (for R12.1.3)
- Apply the patch on the CM node first. The patching process on other nodes will skip some steps that already ran in CM node.
- To apply GDF patches, maintenance mode is not necessary.
$ cd XXXXXXX
$ adpatch options=hotpatch
- For patch analysis, run
$ cd XXXXXXX
$ adpatch apply=n options=hotpatch
- Merge multiple patches: syntax
$ admrgpch -s /path/to/sourceFolder -d /patch/to/mergedPatch -manifest manifest_list.txt
After that, folder mergedPatch can be zipped up and copied to other servers. File manifest_list.txt shall list all .zip files with full patch.
- If patch failed with below similar error, you can answer No or answer Yes to finish the process. Then come back to fix the individual error.
The following Oracle Forms objects did not generate successfully:
ar forms/US ARXCWMAI.fmx
An error occurred while generating Oracle Forms files.
Continue as if it were successful [No] : No
Freeing includes hash table
Freeing fixes hash table
Freeing basedons hash table
Freeing entities hash table
If you answer NO, you have to re-run adpatch after you have fixed (i.e. compiled successfully) what failed. Re-run adpatch :
$ adpatch
... ...
Your previous AutoPatch session did not run to completion.
Do you wish to continue with your previous AutoPatch session [Yes] ? Yes
... ...
It will NOT ask you any passwords and will re-try what failed in previous session:
Assigned: file ARXCWMAI.fmx on worker 1 for product ar username AR.
Completed: file ARXCWMAI.fmx on worker 1 for product ar username AR.
... ...
Running AutoConfig ...
Running AutoConfig on : All products ...
... ...
Saving Patch History information to Database...
Updating the current-view snapshot...
... ... ...
- To check if an EBS patch is installed or not, run a single SELECT statement:
SQL> select * from apps.ad_bugs where bug_number = 1313962;
- BTW, as sysdba use sql to check if a database patch is installed or not
SQL> set serveroutput on;
SQL> exec dbms_qopatch.get_sqlpatch_status;
or
SQL> select xmltransform(dbms_qopatch.is_patch_installed('31219939'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;
SQL> select * from sys.registry$history;
UPDATES 1:
Below query show status on each node if patch is not included in a merged patch. It is tested in R12.1.
SQL> SELECT aap.patch_name, aat.name, apr.end_date
FROM ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_appl_tops aat
WHERE aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and aat.appl_top_id = apr.appl_top_id
and aap.patch_name = '9239090';
UPDATES 2:
xxxxx.lgi file reports files that were not applied. For example, adpatch only compares the package versions on file system and does not check the version inside the database. If for some reason a newer one was copied to the file system, the patch file will not get applied.
$ grep apgdfalb $APPL_TOP/admin/${TWO_TASK}/log/16981628.lgi
Will not apply apgdfalb.pls: Patch file is older.
Patch : /path/to/16981628/ap/patch/115/sql/apgdfalb.pls, v120.1.12010000.68
On-Site: $AP_TOP/patch/115/sql/apgdfalb.pls, v120.1.12010000.72
Checkfile sql ap apgdfalb.pls
But, the package version in the database is much lower. So I had to manually run file apgdfalb.pls in Sql*Plus to get the newer one.
SQL> select text from dba_source where name='AP_ACCTG_DATA_FIX_PKG' and line=2;
/* $Header: apgdfalb.pls 120.1.12010000.43 2011/08/24 05:55:58 kpasikan ship $ */
SQL> @/path/to/16981628/ap/patch/115/sql/apgdfalb.pls
Package created.
- opatch to apply patches to ORACLE_HOME
$ cd 7120514
- Apply the patch
$ opatch apply
(or, $ opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc )
Notes:
(1) Use below line to get opatch version:
$ ./opatch -version
(2) While applying the patch, if OPatch detects a different platform ID in the applying instance, then do the below work-around to make Opatch work fine. For example, if the patch is available for Linux86 but the instance OS at Linux AMD x86-64, set an env variable:
$ export OPATCH_PLATFORM_ID=64
(3) Steps to Rollback the patch
1. Stop all services
2. Use the following command:
$ cd 7120514
$ opatch rollback -id 7120514
UPDATES:
1. If a patch is for a lower version of product or is using a wrong ORACLE_HOME, opatch may not apply it. For example, when I wrongly downloaded zip file of patch 12965674 for 10.1.3.4 and then applied it to a 10.1.3.5 ORACLE_HOME, it just gave message:
SKIPPING_COMPONENT=oracle.j2ee,10.1.3.4.0
None of the patch actions is applicable to the Oracle Home.
OPatch will not apply this patch.
OPatch succeeded.
2. When the folder of the unzipped files had permission issue (owned by a different user), opatch stopped in the middle with below message. I answered N to apply it and then rolled it back successfully.
$ opatch apply
. . . . . . . . .
Replying 'Y' will terminate the patch installation immediately. It WILL NOT restore any updates that have been performed to this point. It WILL NOT update the inventory.
Replying 'N' will update the inventory showing the patch has been applied.
NOTE: After replying either 'Y' or 'N' it is critical to review:
My Oracle Support Note 312767.1 How to rollback a failed Interim patch installation.
Do you want to STOP?
Please respond Y|N >
N
Running make for target ias_install.
Inventory is good and does not have any dangling patches.
Updating inventory...
Verifying patch...
Verifying that patch ID is in Oracle Home inventory.
Verifying archive files.
Comparing "/path/to/11780669/files/lib/librw.a/rwadr.o" and "$ORACLE_HOME/.patch_storage/verify/lib/librw.a/rwadr.o"
OPATCH_JAVA_ERROR: Unable to verify if patch has been applied.
Exception in thread "main" java.io.FileNotFoundException: /path/to/11780669/files/lib/librw.a/rwadr.o (Permission denied)
at java.io.FileInputStream.open(Native Method)
at java.io.FileInputStream.<init>(Unknown Source)
at java.io.FileInputStream.<init>(Unknown Source)
at opatch.VerifyPatch.verify(VerifyPatch.java:663)
at opatch.VerifyPatch.main(VerifyPatch.java:933)Verification of the patch failed.
ERROR: OPatch failed as verification of the patch failed.
$ opatch rollback -id 11780669
. . . . . . . . .
Is this system ready for updating?
Please respond Y|N >
Y
Removing patch 11780669...
Restoring archive files...
Running make for target ias_install.
Updating inventory...
Backing up comps.xml ...
Inventory is good and does not have any dangling patches.
OPatch succeeded.
Verify if a server patch is installed or not
When you install a server patch on your database server or Discoverer server, you need to check if the patch or pre-requisite patch has been installed or not. Here is how you can do that:
$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 6472361
If this command returns anything, the patch 6472361 may be installed on your server.
Below line also gives the list of installed patches. But, it does not give the patch number for the Top-level upgrade (such as 5337014 for upgrading 10.2.0.1.0 to 10.2.0.3.0. Without it, it is not easy to identify the binary file in download).
$ ./opatch lsinventory -oh $ORACLE_HOME
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
There are 2 products installed in this Oracle Home.
Interim patches (3) :
Patch 6596361 : applied on Fri Nov 23 10:24:05 MST 2007
Created on 20 Nov 2007, 02:42:09 hrs PST8PDT,M3.2.0,M11.1.0
Bugs fixed:
6596361
Patch 5556081 : applied on Mon Sep 17 14:21:59 MST 2007
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
5556081
Patch 5557962 : applied on Mon Sep 17 14:21:39 MST 2007
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
4269423, 5557962, 5528974
Rac system comprising of multiple nodes
Local node = dssdb1p
Remote node = dssdb2p
$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 6472361
If this command returns anything, the patch 6472361 may be installed on your server.
Below line also gives the list of installed patches. But, it does not give the patch number for the Top-level upgrade (such as 5337014 for upgrading 10.2.0.1.0 to 10.2.0.3.0. Without it, it is not easy to identify the binary file in download).
$ ./opatch lsinventory -oh $ORACLE_HOME
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
There are 2 products installed in this Oracle Home.
Interim patches (3) :
Patch 6596361 : applied on Fri Nov 23 10:24:05 MST 2007
Created on 20 Nov 2007, 02:42:09 hrs PST8PDT,M3.2.0,M11.1.0
Bugs fixed:
6596361
Patch 5556081 : applied on Mon Sep 17 14:21:59 MST 2007
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
5556081
Patch 5557962 : applied on Mon Sep 17 14:21:39 MST 2007
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
4269423, 5557962, 5528974
Rac system comprising of multiple nodes
Local node = dssdb1p
Remote node = dssdb2p
Monday, June 16, 2008
Script to find the trace file by Request ID
This script will identify the trace file on the database server from a Request ID if it generates a trace file. It also gives the session information if the session is still running. Usually if the trace option is turned on, a trace file is created by the Request.
SQL> SELECT 'Request id: '|| request_id ,
'Trace id: '|| oracle_Process_id,
'Trace Flag: '|| req.enable_trace,
'Trace Name: '|| dest.value || '/' || lower(dbnm.value) || '_ora_' || oracle_process_id || '.trc',
'Prog. Name: '|| prog.user_concurrent_program_name,
'File Name: '|| execname.execution_file_name || ' ' || execname.subroutine_name ,
'Status : '|| decode(phase_code,'R','Running') || '-' || decode(status_code,'R','Normal'),
'SID Serial: ' || ses.sid || ',' || ses.serial#,
'Module : ' || ses.module
from apps.fnd_concurrent_requests req, v$session ses,
v$process proc, v$parameter dest, v$parameter dbnm,
apps.fnd_concurrent_programs_vl prog, apps.fnd_executables execname
where req.request_id = 31414905
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id ;
Similar information can be got from two simple queries:
SQL> select value from v$parameter where name = 'user_dump_dest';
VALUE
----------------------------------------------------------------------
/path/to/udump
SQL> select oracle_process_id, enable_trace from fnd_concurrent_requests where request_id = 5589516;
ORACLE_PROCESS_ID ENABLE_TRACE
-------------------------- ------------------
11662 N
SQL> SELECT 'Request id: '|| request_id ,
'Trace id: '|| oracle_Process_id,
'Trace Flag: '|| req.enable_trace,
'Trace Name: '|| dest.value || '/' || lower(dbnm.value) || '_ora_' || oracle_process_id || '.trc',
'Prog. Name: '|| prog.user_concurrent_program_name,
'File Name: '|| execname.execution_file_name || ' ' || execname.subroutine_name ,
'Status : '|| decode(phase_code,'R','Running') || '-' || decode(status_code,'R','Normal'),
'SID Serial: ' || ses.sid || ',' || ses.serial#,
'Module : ' || ses.module
from apps.fnd_concurrent_requests req, v$session ses,
v$process proc, v$parameter dest, v$parameter dbnm,
apps.fnd_concurrent_programs_vl prog, apps.fnd_executables execname
where req.request_id = 31414905
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id ;
Similar information can be got from two simple queries:
SQL> select value from v$parameter where name = 'user_dump_dest';
VALUE
----------------------------------------------------------------------
/path/to/udump
SQL> select oracle_process_id, enable_trace from fnd_concurrent_requests where request_id = 5589516;
ORACLE_PROCESS_ID ENABLE_TRACE
-------------------------- ------------------
11662 N
Wednesday, May 28, 2008
Two RMAN errors during restore
1. "restore database from tag=TAG20080113T210205" got errors:
ORA-19870: error reading backup piece bk_a2j634ef_1_1_643928527
ORA-19507: failed to retrieve sequential file, handle="bk_a2j634ef_1_1_643928527", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup filenot found in NetBackup catalog
Even "RMAN> list backup of database completed after 'sysdate - 16';" show the backuppiece (and the TAG) is in status "AVAILABLE ".
In this case, most likely the backup tape has been recycled. You may not have chance to restore the database from this backup set.
2. If you see below errors:
......
channel ch02: reading from backup piece pejdb4dd_1_1
ORA-19870: error reading backup piece pajdb2gd_1_1
ORA-19507: failed to retrieve sequential file, handle="pajdb2gd_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to open backup file for restore.
ORA-19870: error reading backup piece ppjddfcb_1_1
ORA-19507: failed to retrieve sequential file, handle="ppjddfcb_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file
Most likely RMAN can not locate the next tape. You need to make sure all tapes for the backup set are in the tape drive.
ORA-19870: error reading backup piece bk_a2j634ef_1_1_643928527
ORA-19507: failed to retrieve sequential file, handle="bk_a2j634ef_1_1_643928527", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file
Even "RMAN> list backup of database completed after 'sysdate - 16';" show the backuppiece (and the TAG) is in status "AVAILABLE ".
In this case, most likely the backup tape has been recycled. You may not have chance to restore the database from this backup set.
2. If you see below errors:
......
channel ch02: reading from backup piece pejdb4dd_1_1
ORA-19870: error reading backup piece pajdb2gd_1_1
ORA-19507: failed to retrieve sequential file, handle="pajdb2gd_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to open backup file for restore.
ORA-19870: error reading backup piece ppjddfcb_1_1
ORA-19507: failed to retrieve sequential file, handle="ppjddfcb_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file
Most likely RMAN can not locate the next tape. You need to make sure all tapes for the backup set are in the tape drive.
Monday, May 26, 2008
Delete stats to speed up RMAN backup
There is a way that may speed up the RMAN backing up in 10G.
1. Run the following as sysdba on the target database:
SQL>exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
2. Implement the following sql statement as the first entry in your RMAN backup script:
run {sql "alter session set optimizer_mode=RULE";
...
}
Note: the 1st statement will cleanup old stats. You may want to wait until rman finishes if it is running. You cannot do anything to improve performance of the currently running rman session.
1. Run the following as sysdba on the target database:
SQL>exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
2. Implement the following sql statement as the first entry in your RMAN backup script:
run {sql "alter session set optimizer_mode=RULE";
...
}
Note: the 1st statement will cleanup old stats. You may want to wait until rman finishes if it is running. You cannot do anything to improve performance of the currently running rman session.
Friday, May 23, 2008
.profile to setup Oracle login environment
This is to show how .profile uses to a 2nd file on Linux (ksh) to set up Oracle environment.
auohsjy02 > cat .profile
. ./profile.iascc_env
auohsjy02 > ls -al *profile*
-rw-r--r-- 1 iasjy iasjy 2651 Mar 23 14:39 profile.iascc_env
auohsjy02 > cat profile.iascc_env
trap "rm - f /tmp/oh_list.$$ /tmp/valid_oh_list.$$ /tmp/sid_list_tmp.$$ /tmp/sid_list.$$; return" 1 2 3 15
# get list of ORACLE_HOME
case `uname` in
"Linux")
oratab=/etc/oratab
;;
"HP-UX")
oratab=/etc/oratab
;;
"SunOS")
oratab=/var/opt/oracle/oratab
;;
esac
awk -F":" "/^[^#]/ {print \$2}" $oratab sortuniq > /tmp/oh_list.$$
# validate the list
"rm" -f /tmp/valid_oh_list.$$
total=0
for oh in `cat /tmp/oh_list.$$`; do
if [ -w $oh/bin/genclntsh ]; then
echo $oh >> /tmp/valid_oh_list.$$;
total=`expr $total + 1`
fi
done
"rm" -f /tmp/oh_list.$$
# show menu
if [ $total -gt 1 ]; then
echo "Available ORACLE_HOME:"
echo ""
i=1
for oh in `cat /tmp/valid_oh_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_HOME from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_HOME=`sed -n ${answer}p /tmp/valid_oh_list.$$ 2>/dev/null`
export ORACLE_HOME
"rm" -f /tmp/valid_oh_list.$$
echo "$ORACLE_HOME " grep -i "ocsmt_904" 1>/dev/null 2>/dev/null
if [ "$?" = "0" ]; then
CTSIS_IPC_PATH=/var/opt/oracle/ocs/tmp
export CTSIS_IPC_PATH
fi
. /etc/ora_cc_env
# ORACLE_SID
ORACLE_SID=
total=0
# check spfile first
sidlist=`"ls" $ORACLE_HOME/dbs/spfile?*.ora 2>/dev/null`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b7-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
else
# then init.ora
sidlist=`"ls" $ORACLE_HOME/dbs/init?*.ora 2>/dev/null grep -v initdw`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b5-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
fi
fi
sort /tmp/sid_list_tmp.$$ 2>/dev/null uniq > /tmp/sid_list.$$
total=`"wc" -l /tmp/sid_list.$$ awk '{print $1}'`
"rm" -f /tmp/sid_list_tmp.$$
# show menu
if [ "$total" -gt 1 ]; then
echo "ORACLE_SID list:"
echo ""
i=1
for oh in `cat /tmp/sid_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_SID from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_SID=`sed -n ${answer}p /tmp/sid_list.$$ 2>/dev/null`
export ORACLE_SID
"rm" -f /tmp/sid_list.$$
trap 1 2 3 15
echo "ORACLE_HOME=$ORACLE_HOME"
echo "ORACLE_SID=$ORACLE_SID"
auohsjy02 > echo $SHELL
/bin/ksh
auohsjy02 > cat .profile
. ./profile.iascc_env
auohsjy02 > ls -al *profile*
-rw-r--r-- 1 iasjy iasjy 2651 Mar 23 14:39 profile.iascc_env
auohsjy02 > cat profile.iascc_env
trap "rm - f /tmp/oh_list.$$ /tmp/valid_oh_list.$$ /tmp/sid_list_tmp.$$ /tmp/sid_list.$$; return" 1 2 3 15
# get list of ORACLE_HOME
case `uname` in
"Linux")
oratab=/etc/oratab
;;
"HP-UX")
oratab=/etc/oratab
;;
"SunOS")
oratab=/var/opt/oracle/oratab
;;
esac
awk -F":" "/^[^#]/ {print \$2}" $oratab sortuniq > /tmp/oh_list.$$
# validate the list
"rm" -f /tmp/valid_oh_list.$$
total=0
for oh in `cat /tmp/oh_list.$$`; do
if [ -w $oh/bin/genclntsh ]; then
echo $oh >> /tmp/valid_oh_list.$$;
total=`expr $total + 1`
fi
done
"rm" -f /tmp/oh_list.$$
# show menu
if [ $total -gt 1 ]; then
echo "Available ORACLE_HOME:"
echo ""
i=1
for oh in `cat /tmp/valid_oh_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_HOME from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_HOME=`sed -n ${answer}p /tmp/valid_oh_list.$$ 2>/dev/null`
export ORACLE_HOME
"rm" -f /tmp/valid_oh_list.$$
echo "$ORACLE_HOME " grep -i "ocsmt_904" 1>/dev/null 2>/dev/null
if [ "$?" = "0" ]; then
CTSIS_IPC_PATH=/var/opt/oracle/ocs/tmp
export CTSIS_IPC_PATH
fi
. /etc/ora_cc_env
# ORACLE_SID
ORACLE_SID=
total=0
# check spfile first
sidlist=`"ls" $ORACLE_HOME/dbs/spfile?*.ora 2>/dev/null`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b7-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
else
# then init.ora
sidlist=`"ls" $ORACLE_HOME/dbs/init?*.ora 2>/dev/null grep -v initdw`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b5-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
fi
fi
sort /tmp/sid_list_tmp.$$ 2>/dev/null uniq > /tmp/sid_list.$$
total=`"wc" -l /tmp/sid_list.$$ awk '{print $1}'`
"rm" -f /tmp/sid_list_tmp.$$
# show menu
if [ "$total" -gt 1 ]; then
echo "ORACLE_SID list:"
echo ""
i=1
for oh in `cat /tmp/sid_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_SID from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_SID=`sed -n ${answer}p /tmp/sid_list.$$ 2>/dev/null`
export ORACLE_SID
"rm" -f /tmp/sid_list.$$
trap 1 2 3 15
echo "ORACLE_HOME=$ORACLE_HOME"
echo "ORACLE_SID=$ORACLE_SID"
auohsjy02 > echo $SHELL
/bin/ksh
Friday, May 16, 2008
Discoverer Logs
Discoverer 10g (10.1.2.48.18 in my server) provides a script to collect all logs for troubleshooting. The script called collectlogs.sh is located in $ORACLE_HOME/discoverer/util. (Note $ORACLE_HOME could be null in the Discoverer installation acconut).
I ran it by below line. It created two files: the .tar file, and loglist.txt file for listing all log files collected in the tar file.
$ pwd
/sjy/disco/product/disco_1012/discoverer/util
$ ./collectlogs.sh disco_log.tar > loglist.txt
Initializing ORB...
ORB initialized successfully.
Checking for Discoverer "ServicesStatus"...
"ServicesStatus" is running.
Checking the adminstrator set soft limit on no. of sessions ...
Current value of soft limit = 50 sessions.
Hint: Administrators can increase the soft limit on Discoverer sessions by modifying
"//ias-component[id='Discoverer']/process-type[id='SessionServer']/process-set/@maxprocs" attribute in the file /sjy/disco/product/disco_1012/opmn/conf/opmn.xml
Checking for Discoverer Preferences component...
Found Discoverer Preferences component.
Binding to Discoverer Server...
Successfully bound to Discoverer Server.
Checking that the machine has only one network card...
Yes.
cp: cannot stat `/spulti/disco/product/disco_1012/discoverer/util/diag.log': No such file or directory
cp: cannot stat `/spulti/disco/product/disco_1012/network/admin/sqlnet.ora': No such file or directory
cp: omitting directory `/spulti/disco/product/disco_1012/opmn/logs/states'
For troubleshooting purpose, three commands are useful to check the status of Discoverer processes and environment:
$ $ORACLE_HOME/dcm/bin/dcmctl getstate -v -d
Current State for Instance:sjy_disco.auohsjy02.com
Component Type Up Status In Sync Status
==============================================
1 home OC4J Up True
2 HTTP_Server HTTP_Server Up True
3 OC4J_BI_Forms OC4J Up True
$ opmnctl status
Processes in Instance: sjy_disco.auohsjy02.com
-------------------+--------------------+---------+---------
ias-component process-type pid status
-------------------+--------------------+---------+---------
DSA DSA N/A Down
LogLoader logloaderd N/A Down
dcm-daemon dcm-daemon 11231 Alive
WebCache WebCache 9828 Alive
WebCache WebCacheAdmin 9814 Alive
OC4J home 9815 Alive
OC4J OC4J_BI_Forms 9816 Alive
HTTP_Server HTTP_Server 9817 Alive
Discoverer ServicesStatus 9818 Alive
Discoverer PreferenceServer 9819 Alive
$ emctl status iasconsole
TZ set to US/Central
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://auohsjy02.com:10923/emd/console/aboutApplication
Oracle Enterprise Manager 10g Application Server Control is running.
I ran it by below line. It created two files: the .tar file, and loglist.txt file for listing all log files collected in the tar file.
$ pwd
/sjy/disco/product/disco_1012/discoverer/util
$ ./collectlogs.sh disco_log.tar > loglist.txt
Initializing ORB...
ORB initialized successfully.
Checking for Discoverer "ServicesStatus"...
"ServicesStatus" is running.
Checking the adminstrator set soft limit on no. of sessions ...
Current value of soft limit = 50 sessions.
Hint: Administrators can increase the soft limit on Discoverer sessions by modifying
"//ias-component[id='Discoverer']/process-type[id='SessionServer']/process-set/@maxprocs" attribute in the file /sjy/disco/product/disco_1012/opmn/conf/opmn.xml
Checking for Discoverer Preferences component...
Found Discoverer Preferences component.
Binding to Discoverer Server...
Successfully bound to Discoverer Server.
Checking that the machine has only one network card...
Yes.
cp: cannot stat `/spulti/disco/product/disco_1012/discoverer/util/diag.log': No such file or directory
cp: cannot stat `/spulti/disco/product/disco_1012/network/admin/sqlnet.ora': No such file or directory
cp: omitting directory `/spulti/disco/product/disco_1012/opmn/logs/states'
For troubleshooting purpose, three commands are useful to check the status of Discoverer processes and environment:
$ $ORACLE_HOME/dcm/bin/dcmctl getstate -v -d
Current State for Instance:sjy_disco.auohsjy02.com
Component Type Up Status In Sync Status
==============================================
1 home OC4J Up True
2 HTTP_Server HTTP_Server Up True
3 OC4J_BI_Forms OC4J Up True
$ opmnctl status
Processes in Instance: sjy_disco.auohsjy02.com
-------------------+--------------------+---------+---------
ias-component process-type pid status
-------------------+--------------------+---------+---------
DSA DSA N/A Down
LogLoader logloaderd N/A Down
dcm-daemon dcm-daemon 11231 Alive
WebCache WebCache 9828 Alive
WebCache WebCacheAdmin 9814 Alive
OC4J home 9815 Alive
OC4J OC4J_BI_Forms 9816 Alive
HTTP_Server HTTP_Server 9817 Alive
Discoverer ServicesStatus 9818 Alive
Discoverer PreferenceServer 9819 Alive
$ emctl status iasconsole
TZ set to US/Central
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://auohsjy02.com:10923/emd/console/aboutApplication
Oracle Enterprise Manager 10g Application Server Control is running.
Wednesday, May 14, 2008
Monitor Discoverer Processes (dis51ws) Not Terminated by Timeout
If user does not use "file > exit" or "file > close" to gracefully close the workbook or Discoverer, an orphaned session may be created. Some times, user may get error while trying to connect to Discoverer Viewer and Plus:
A connection error has occurred.
- Attempt 1. CORBA proto : Hard limit on number of sessions reached. Please contact your administrator or retry after some time.
Hint: An administrator can further diagnose connection problems by running the "checkdiscoverer" script under <ORACLE_HOME>/discoverer/util.
Here is a useful korn shell script:
$ cat script.ksh
# script to display dis51ws processes over 5 hours old.
# you need to be in the korn shell to run this.
echo "\nDD:HH:MM:SS PID Process"
echo "------------------------"
for i in `ps -A -o etime,pid,args grep dis51ws sed 's/ /_/g'`
do
myday=`echo $i awk '{ fred2=substr($1,1,2);print fred2}'`
myhour=`echo $i awk '{ fred2=substr($1,4,2);print fred2}'`
# the above line extracts the hour .
if test $myday = "__*"
then
if test $myhour != "__*"
then
if test $myhour -gt 5
# the last number here is the hour limit -
# you can customize this from 0 to 23.
then
process=`echo $i sed 's/_/ /g'`
echo $process
fi
fi
else # if any day at all is listed, then the proc must be over 5 hours.
process=`echo $i sed 's/_/ /g'`
echo $process
fi
done
echo "\n"
$ for i in ` ksh script.ksh awk '{ print $2}'`
> do
> kill -9 $i
> done
Note that Discoverer uses $ORACLE_HOME/discoverer/util/perf.txt to set up the timeout variable: Timeout = 1800 seconds. $ORACLE_HOME/opmn/conf/opmn.xml also has entries for time: <start timeout="600" /> <stop timeout="120" /> (not sure what it's for).
A connection error has occurred.
- Attempt 1. CORBA proto : Hard limit on number of sessions reached. Please contact your administrator or retry after some time.
Hint: An administrator can further diagnose connection problems by running the "checkdiscoverer" script under <ORACLE_HOME>/discoverer/util.
Here is a useful korn shell script:
$ cat script.ksh
# script to display dis51ws processes over 5 hours old.
# you need to be in the korn shell to run this.
echo "\nDD:HH:MM:SS PID Process"
echo "------------------------"
for i in `ps -A -o etime,pid,args grep dis51ws sed 's/ /_/g'`
do
myday=`echo $i awk '{ fred2=substr($1,1,2);print fred2}'`
myhour=`echo $i awk '{ fred2=substr($1,4,2);print fred2}'`
# the above line extracts the hour .
if test $myday = "__*"
then
if test $myhour != "__*"
then
if test $myhour -gt 5
# the last number here is the hour limit -
# you can customize this from 0 to 23.
then
process=`echo $i sed 's/_/ /g'`
echo $process
fi
fi
else # if any day at all is listed, then the proc must be over 5 hours.
process=`echo $i sed 's/_/ /g'`
echo $process
fi
done
echo "\n"
$ for i in ` ksh script.ksh awk '{ print $2}'`
> do
> kill -9 $i
> done
Note that Discoverer uses $ORACLE_HOME/discoverer/util/perf.txt to set up the timeout variable: Timeout = 1800 seconds. $ORACLE_HOME/opmn/conf/opmn.xml also has entries for time: <start timeout="600" /> <stop timeout="120" /> (not sure what it's for).
Friday, May 2, 2008
Profile Options Could Stop Concurrent Jobs
In production instance, MRP Workbench does not have data. The concurrent job "Purchasing MRP (Report)" failed with error in the log:
Request restarted at :01-MAY-2008 01:05:57
**Starts**01-MAY-2008 01:05:57
**Ends**01-MAY-2008 01:05:57
The set completed normally with outcome Error. The outcome was determined by the final stage, Load/Copy/Merge MDS (10).
The log on program "Load/Copy/Merge MDS" says that
APP-MRP-22132: Cannot get profile option MRP_DEBUG
Cause: The current routine cannot retrieve a value for the specified profile option.
Below query confirms that two profile options are blank because of human error, as it returns 0 row:
SELECT fpo.profile_option_name Profile,
fpov.profile_option_value Value,
decode(fpov.level_id,10001, 'SITE',10002, 'APPLICATION',10003, 'RESPONSIBILITY', 10004,'USER') "LEVEL",
fa.application_short_name App,
fr.responsibility_name Responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options fpo,
fnd_application fa,
apps.fnd_responsibility_vl fr,
fnd_user fu, fnd_logins fl
WHERE fpo.profile_option_id=fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.application_id(+)=fpov.level_value_application_id
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN
and fpo.profile_option_name in ('MRP_TRACE','MRP_DEBUG')
order by 1,3
After setting profile options "MRP:Debug" and "MRP:Trace" to 'No' at Site level, the MRP request set runs successfully.
Request restarted at :01-MAY-2008 01:05:57
**Starts**01-MAY-2008 01:05:57
**Ends**01-MAY-2008 01:05:57
The set completed normally with outcome Error. The outcome was determined by the final stage, Load/Copy/Merge MDS (10).
The log on program "Load/Copy/Merge MDS" says that
APP-MRP-22132: Cannot get profile option MRP_DEBUG
Cause: The current routine cannot retrieve a value for the specified profile option.
Below query confirms that two profile options are blank because of human error, as it returns 0 row:
SELECT fpo.profile_option_name Profile,
fpov.profile_option_value Value,
decode(fpov.level_id,10001, 'SITE',10002, 'APPLICATION',10003, 'RESPONSIBILITY', 10004,'USER') "LEVEL",
fa.application_short_name App,
fr.responsibility_name Responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options fpo,
fnd_application fa,
apps.fnd_responsibility_vl fr,
fnd_user fu, fnd_logins fl
WHERE fpo.profile_option_id=fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.application_id(+)=fpov.level_value_application_id
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN
and fpo.profile_option_name in ('MRP_TRACE','MRP_DEBUG')
order by 1,3
After setting profile options "MRP:Debug" and "MRP:Trace" to 'No' at Site level, the MRP request set runs successfully.
Wednesday, April 30, 2008
SQL to show 11i Profile Options
After some Profile options were turned on Site, Application, Responsibility, or User level, you might forget to turn them off which may lead to application behavior variation by user or responsibility. Metalink Doc ID: 146705.1 provides a SQL script to display all saved values for the specified profile options. Here is what it looks like on running it:
SQL> @a_profile_opt_R11i.sql
Enter value for profile_like: Utili%Diag%
Creation Date: 27-DEC-99 Created By: AUTOINSTALL
Date Active From: 01-JAN-80 To:
Profile Option Name: DIAGNOSTICS
User Profile Name: Utilities:Diagnostics
Profile Description:
Value determines whether diagnostic utilities, such as Examine, may be used
Level Value Profile Value
------- -------------- ----------
Site SITE N
User JJOHN Y
Code from the Oracle document:
SQL> @a_profile_opt_R11i.sql
Enter value for profile_like: Utili%Diag%
Creation Date: 27-DEC-99 Created By: AUTOINSTALL
Date Active From: 01-JAN-80 To:
Profile Option Name: DIAGNOSTICS
User Profile Name: Utilities:Diagnostics
Profile Description:
Value determines whether diagnostic utilities, such as Examine, may be used
Level Value Profile Value
------- -------------- ----------
Site SITE N
User JJOHN Y
Code from the Oracle document:
Program
-------
- - - - - - - - - - - Code begins here - - - - -- - - - - - - - -
rem
rem File: a_profile_opt_R11i.sql
rem Created: Vitaliy Mogilevskiy (vit100gain@earthlink.net)
rem Modified: Eric Santos, Run in 11i and NLS
rem Desc: Reports Profile Options For Oracle Applications
rem with NOT NULL values, groups by Profile Option Name
rem breaks by SITE, RESPONSIBILITY, APPLICATION, USER
rem Takes Parameter Name as search string
rem Allows you to view ALL possible values for profile
rem including SITE, RESPONSIBILITY, APPLICATION and USER.
rem This is impossible in Oracle Apps GUI mode
rem
clear col
clear breaks
set pages 9000
set lines 132
set verify off
col pov format a45 word_wrapped heading "Profile Value"
col lo format a5 heading "Level"
col lov format a40 heading "Value"
col pon noprint new_value n_pon
col upon noprint new_value n_upon
col sda noprint new_value n_sda
col eda noprint new_value n_eda
col cd noprint new_value n_cd
col cb noprint new_value n_cb
col d format a78 word_wrapped noprint new_value n_d
break on pon skip page
ttitle -
"Creation Date: " n_cd " Created By: " n_cb -
skip 1 -
"Date Active From:" n_sda " To:" n_eda -
skip 1 -
"Profile Option Name: " n_pon -
skip 1 -
"User Profile Name: " n_upon -
skip 1 -
"Profile Description: " -
skip 1 -
n_d -
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Site' lo
, 'SITE' lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10001 /* Site Level */
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Apps' lo
, fa.application_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_APPLICATION_TL fa
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10002 /* Application Level */
and fpov.level_value = fa.application_id
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Resp' lo
, frt.responsibility_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_RESPONSIBILITY_TL frt
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and frt.language = Userenv('Lang')
and fpot.language = Userenv('Lang')
and fpov.level_id = 10003 /* Responsibility Level */
and fpov.level_value = frt.responsibility_id
and fpov.level_value_application_id = frt.application_id
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'User' lo
, fu2.user_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_USER fu2
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10004 /* User Level */
and fpov.level_value = fu2.user_id
and fpot.language = Userenv('Lang')
order by upon, lo, lov
/
undefine profile_like
ttitle off
- - - - - - - - - - - - Code ends here - - - - - - - - - - - -
How to Get a Debug Log in 11i
A debug log is different from a trace file. Usually, a trace file is saved on the database server (see Metalink Note 141278.1 for 11i tracing).
You have to make a change in Profile to have debug information written into the Log file of a concurrent program. For example, below action will get the debug log for "PRC: Transaction Inventory":
System Adminstrator / Application Administration --> Profile
Set profile option "PA: Debug Mode" to "Yes"
for user USER_NAME (who will run the concurrent program)
For HTML web apps, it is not easy to get debug information or trace file. One way you can try is to turn on the trace on SQL. I tried that on BOM Configurator and got the trace when I turned on the trace only on Site level which may overload the system. Following action will turn on SQL trace on a user level:
Set profile option "Utilities: SQL Trace" to "Yes"
for user USER_NAME
Steps to read the trace file:
1. Retrieve the trace file, usually identified by timestamp.
2. Issue a command like the following to create a TKPROF version of the trace file:
tkprof filename.trc output_filename.txt sys=no explain=apps/password
You have to make a change in Profile to have debug information written into the Log file of a concurrent program. For example, below action will get the debug log for "PRC: Transaction Inventory":
System Adminstrator / Application Administration --> Profile
Set profile option "PA: Debug Mode" to "Yes"
for user USER_NAME (who will run the concurrent program)
For HTML web apps, it is not easy to get debug information or trace file. One way you can try is to turn on the trace on SQL. I tried that on BOM Configurator and got the trace when I turned on the trace only on Site level which may overload the system. Following action will turn on SQL trace on a user level:
Set profile option "Utilities: SQL Trace" to "Yes"
for user USER_NAME
Steps to read the trace file:
1. Retrieve the trace file, usually identified by timestamp.
2. Issue a command like the following to create a TKPROF version of the trace file:
tkprof filename.trc output_filename.txt sys=no explain=apps/password
Saturday, April 26, 2008
OS Process ID for Troubled Concurrent Manager
From time to time, some concurrent manger hangs and it can not be fixed by using adcmctl.sh to re-start ICM. The reason is that the OS process for the concurrent manager may be "dead" and you have to kill it at OS level before the concurrent manager can be re-started.
Steps for finding concurrent manager, such as Output Post Processor, stuck in 'Initializing' or other phase:
. Log on to EBS Forms or to Oracle Application Manager.
. Find the concurrent manager in question.
. Click on "Process" button or Drill down to find the processes shown as active or initializing.
. If the operating system process ID shown is running on the concurrent processing node, kill that process.
. Activate the concurrent manager.
. Verify that the concurrent manager comes up and the number of active managers is equal to the target number.
Steps for finding concurrent manager, such as Output Post Processor, stuck in 'Initializing' or other phase:
. Log on to EBS Forms or to Oracle Application Manager.
. Find the concurrent manager in question.
. Click on "Process" button or Drill down to find the processes shown as active or initializing.
. If the operating system process ID shown is running on the concurrent processing node, kill that process.
. Activate the concurrent manager.
. Verify that the concurrent manager comes up and the number of active managers is equal to the target number.
Friday, April 11, 2008
View other's Output files and Log files within a Responsibility
By default, user can not view the Output of concurrent program/request submitted by others. This setup will allow all users of a responsibility to view each others log and report outputs.
. Login to Apps as SYSADMIN
. Navigate to SysAdmin > Profile > System.
. From the "Find System Profile Values" window query up the Profile named "Concurrent: Report Access Level"
. In the "System Profile Values" window find "Concurrent: Report Access Level" profile.
. Set the Site Column LOV to "Responsibility" (be careful not to set the Site Column to “User” because that will completely open the system up where all requests will be available to all users regardless of level)
. Set the "Concurrent: Report Access Level" profile to "Responsibility" (Setting this value to “User” here exhibits the behavior where a user is restricted from viewing other users’ concurrent requests).
The important profile value to set is "Concurrent: Report Access Level", and also the setting is at the responsibility level, instead of system level.
UPDATE: For R12, Profile option "Concurrent: Report Access Level" does not exist and it is more difficult to see other's Output file. Check Oracle document IDs 804296.1 and 2112509.1
. Login to Apps as SYSADMIN
. Navigate to SysAdmin > Profile > System.
. From the "Find System Profile Values" window query up the Profile named "Concurrent: Report Access Level"
. In the "System Profile Values" window find "Concurrent: Report Access Level" profile.
. Set the Site Column LOV to "Responsibility" (be careful not to set the Site Column to “User” because that will completely open the system up where all requests will be available to all users regardless of level)
. Set the "Concurrent: Report Access Level" profile to "Responsibility" (Setting this value to “User” here exhibits the behavior where a user is restricted from viewing other users’ concurrent requests).
The important profile value to set is "Concurrent: Report Access Level", and also the setting is at the responsibility level, instead of system level.
UPDATE: For R12, Profile option "Concurrent: Report Access Level" does not exist and it is more difficult to see other's Output file. Check Oracle document IDs 804296.1 and 2112509.1
Sunday, April 6, 2008
Directory defined by $APPLPTMP & $APPLTMP
Oracle Doc ID 1355735.1 (Difference between APPLPTMP and APPLTMP Directories in EBS) tells the difference between APPLPTMP and APPLTMP Directories in EBS. Here is more info on troubleshooting.
Instead, the directory has to exist on the database server. Otherwise, below error will show up in CM program log. I also saw similar error when the folder has too many files.
ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: File o0275459.tmp creation for FND_FILE filed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 410
ORA-06512
And, make sure the directory is part of the database parameter utl_file_dir:
SQL> select name, value from v$parameter where name = 'utl_file_dir';
To test its setup, run
SQL> exec FND_FILE.PUT_LINE(fnd_file.log, 'Hello World!');
A new file with a name like l000xxxx.tmp is created in the first directory in the db parameter utl_file_dir, containing 'Hello World!'.
UPDATE: *warning* In 12c database, after above statement, it needs to run CLOSE line. Otherwise the log file size may be 0!
SQL> exec fnd_file.close;
Below line will work but will not create a file o000xxxx.tmp and write one line to it until CLOSE line was run.
SQL> exec FND_FILE.PUT_LINE(fnd_file.output, 'Hello output!');
- Variable $APPLTMP is the location to save temporary files from forms run-time on the forms/web server. A cron job can be scheduled to delete old files in that directory.
One day, server experienced files filling up $APPLTMP quickly with files in 2GB size:
-rw-r----- 1 user1 users 2147483647 Feb 4 07:12 OFN6pYCK.t
-rw-r----- 1 user1 users 2147483647 Feb 4 07:16 OFNF0HJf.t
-rw-r----- 1 user1 users 2007334912 Feb 4 07:39 OFVuhSzG.t
The file holds entries of hours earlier than the file's timestamp:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Subledger Accounting: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XLAACCUP module: Accounting Program
+---------------------------------------------------------------------------+
Current system time is 04-FEB-20XX 01:02:47
... ... ...
I found debug log was turned on by "FND: Debug Log Enabled =Yes" on site level. So, make sure below settings are off in Profile options:
FND: Debug Log Enabled = No
SLA: Enable Diagnostics = No
AR: Enable Debug Message Output = No
- Variable s_forms_tmpdir in CONTEXT_FILE also defines a location holding forms temp files.
On a busy day, Sys Admin saw /tmp was almost full by 7 GB file /tmp/file75jBt9.TMP, while EBS R12 variable s_forms_tmpdir sets to /tmp directory. Even this file was marked "deleted", the space was not released as long as a frmweb / forms session had it open by a user who was extracting a large portion of year-end data.
SQL> exec fnd_file.close;
One day, server experienced files filling up $APPLTMP quickly with files in 2GB size:
-rw-r----- 1 user1 users 2147483647 Feb 4 07:12 OFN6pYCK.t
-rw-r----- 1 user1 users 2147483647 Feb 4 07:16 OFNF0HJf.t
-rw-r----- 1 user1 users 2007334912 Feb 4 07:39 OFVuhSzG.t
The file holds entries of hours earlier than the file's timestamp:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Subledger Accounting: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XLAACCUP module: Accounting Program
+---------------------------------------------------------------------------+
Current system time is 04-FEB-20XX 01:02:47
... ... ...
I found debug log was turned on by "FND: Debug Log Enabled =Yes" on site level. So, make sure below settings are off in Profile options:
FND: Debug Log Enabled = No
SLA: Enable Diagnostics = No
AR: Enable Debug Message Output = No
- Variable s_forms_tmpdir in CONTEXT_FILE also defines a location holding forms temp files.
On a busy day, Sys Admin saw /tmp was almost full by 7 GB file /tmp/file75jBt9.TMP, while EBS R12 variable s_forms_tmpdir sets to /tmp directory. Even this file was marked "deleted", the space was not released as long as a frmweb / forms session had it open by a user who was extracting a large portion of year-end data.
UPDATE in Sept 2022:
Starting in Oracle Database 18c, parameter UTL_FILE_DIR is no longer supported. Instead, specify the name of a directory object.
Since the UTL_FILE_DIR initialization parameter is deprecated, it is no longer listed in V$SYSTEM_PARAMETER and related views:
SQL> SELECT value FROM v$system_parameter WHERE name='utl_file_dir' ;
no rows selected
SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir' ;
VALUE
--------------------------------------------------------------------------------
/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV
And, it can not be altered at DB level:
SQL> alter system set utl_file_dir='/path/to/utl_dir';
Wednesday, April 2, 2008
Log files from various programs in 11i
Log files are most important in helping troubleshooting. Various parograms in 11i put log files in different locations.
CONTENTS (See Metalink Note 130183.1)
--------
1. Internal manager
2. Concurrent managers
3. Concurrent programs
4. AD, FND and other utilities
5. User exits
6. Forms server
7. SQL*NET, NET8 listener
8. Apache, Jserv
9. SSP5 (iProcurement)
10. PO Document Approval Manager
11. XML Publisher (Output Post Processor)
1. INTERNAL MANAGER (also called ICM)
-------------------
a. when the ICM is started by adcmctl.sh, a log file is created in
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is: $<SID>_mmdd.mgr
(or <mgrname>.mgr. mgrname is figured in startmgr, and is 'std' by default. startmgr is a shell script under $FND_TOP/bin/) (version depend?)
- To debug the ICM startup, set the value of DIAG_PARAM to Y in adcmctl.sh.
- The ICM log file on startup can be also located by SQL:
SELECT 'LOG=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'AND fcp.process_status_code = 'A';
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Internal Manager Log
2. CONCURRENT MANAGERS (See note 105133.1)
----------------------
a. retrieve Manager log files under :
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is:
w<Concurrent Process Id>.mgr (log from one of Concurrent managers)
c<Concurrent Process Id>.mgr (Conflict Resolution manager log)
t<Concurrent Process Id>.mgr (Transaction manager log)
s<Concurrent Process Id>.mgr
FNDxxxxx.mgr
If you see errors on cartridge in the logfile, below query will help to identify the queue:
SELECT q.concurrent_queue_id, q.concurrent_queue_name
FROM apps.fnd_concurrent_queues q, apps.fnd_cp_services s
WHERE enabled_flag = 'Y'
AND q.manager_type = s.service_id
AND s.cartridge_handle = 'CMDCART'; -- for example
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Requests, choose a request ...
menu: Special => Manager Log
or
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Manager Log
3. CONCURRENT PROGRAMS
----------------------
a. retrieve Concurrent Program log files under :
$APPLCSF/$APPLLOG (if $APPLCSF and $APPLLOG exist) or
$<module_top>/$APPLLOG
File format is: l<Concurrent Request Id>.req
- If the concurrent program generates a output file, it goes to $APPLCSF/$APPLOUT.
- SQL can be used to get the exact file names for Log file and Output file:
SELECT logfile_name, outfile_name, outfile_node_name, last_update_date
FROM apps.FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = &requestID;
- There are also log files for concurrent requests under $COMMON_TOP/admin/log/$SID_$hostname. (Will they be useful only when you have Forms auditing on?)
b. directly from Oracle Applications :
System Administrator responsability (for own and other users requests) :
Navigator: Concurrent => Requests, button View Log...
Note: User may not be able to view other's Output (and Log) files.
4. AD, FND AND OTHER UTILITIES
-------------------------------
Some AD utilities like adpatch, adadmin, etc. generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file names are :
adunload.log
adrelink.log
adlibout.log
adlibin.log
adfrmgen.log
admrgpch.log
adrepgen.log
adctrl.log
admvcode.log
adaimgr.log
adwork01.log, adwork02.log, adwork03.log, ...
Utility adodfcmp generates log file under $<module_top>/admin/odf/ or $<module_top>/patch/<version>/odf/, file name is:
adodfcmp.log
FND utilities like fdfcmp, FNDFFVGN, FNDMDGEN generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file name is :
L<request_id>.req
5. USER EXITS (See note 292058.1)
--------------
User exits from forms generate log file under directory from where the forms server is started if variable FDUDEBUG is set to ON. File name is:
userexit.log
6. FORMS SERVER (See note 471921.1)
----------------
When starting forms server you can specify a log file name.
Exemples:
Windows: f60srv32 log=c:\temp\f60srv.log
Unix: f60ctl start port=9000 log=/temp/f60srv.log
(f60ctl is a script located in $ORACLE_HOME/bin)
Note: $COMMON_TOP/admin/log/$SID_$hostname/f60svrm.txt has very basic info from executing adfrmctl.sh
7. SQL*NET, NET8 LISTENER
--------------------------
You can specify LOG_DIRECTORY_<LISTENER_NAME> and LOG_FILE_<LISTENER_NAME> specific values for theses parameters in listener.ora.
You can set LOG_FILE and TRACE_FILE variables from a lsnrctl session.
There are also LOG_DIRECTORY_SERVER and LOG_FILE_SERVER parameters in sqlnet.ora.
8. APACHE, JSERV
----------------
Logs from Apache server are under $APACHE_TOP/logs, file names are:
error_log
access_log
httpds_access_log
httpds_error_log
sqlnet.log
Logs from Jserv are under $ORACLE_HOME/Apache/Jserv/logs (?), file names are:
jserv.log
debug.log
Note: it is recommended to clean up these log files, shutdown and restart Apache server before trying to reproduce the issue and analyze.
$APACHE_TOP is not defined in any $APPL_TOP/*.env file ("grep APACHE_TOP $APPL_TOP/*.env" gets nothing).
9. SSP5 (iPROCUREMENT)
---------------------
Apache and Jserv logs are useful to check for iProcurement (SSP5), you can also add these lines in ssp_init.txt (should be under $ORACLE_HOME/Apache/Jserv/etc) to generate specific SSP5 log file:
DebugOutput=<Directory/File name> (preferably under $APACHE_TOP/logs)
DebugLevel=5
DebugSwitch=ON
Note: don't forget to restart Apache server after editing ssp_init.txt.
10. PO Document Approval Manager
------------------------------------
Responsibility - System Administrator responsibility (Nav - Concurrent Manager - Administer).
Locate and find the PO Document Approval Manager and then click on Processes button and then select an Active concurrent manager process and click on the Manager Log button to display the log file.
CONTENTS (See Metalink Note 130183.1)
--------
1. Internal manager
2. Concurrent managers
3. Concurrent programs
4. AD, FND and other utilities
5. User exits
6. Forms server
7. SQL*NET, NET8 listener
8. Apache, Jserv
9. SSP5 (iProcurement)
10. PO Document Approval Manager
11. XML Publisher (Output Post Processor)
1. INTERNAL MANAGER (also called ICM)
-------------------
a. when the ICM is started by adcmctl.sh, a log file is created in
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is: $<SID>_mmdd.mgr
(or <mgrname>.mgr. mgrname is figured in startmgr, and is 'std' by default. startmgr is a shell script under $FND_TOP/bin/) (version depend?)
- To debug the ICM startup, set the value of DIAG_PARAM to Y in adcmctl.sh.
- The ICM log file on startup can be also located by SQL:
SELECT 'LOG=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'AND fcp.process_status_code = 'A';
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Internal Manager Log
2. CONCURRENT MANAGERS (See note 105133.1)
----------------------
a. retrieve Manager log files under :
$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG
File format is:
w<Concurrent Process Id>.mgr (log from one of Concurrent managers)
c<Concurrent Process Id>.mgr (Conflict Resolution manager log)
t<Concurrent Process Id>.mgr (Transaction manager log)
s<Concurrent Process Id>.mgr
FNDxxxxx.mgr
If you see errors on cartridge in the logfile, below query will help to identify the queue:
SELECT q.concurrent_queue_id, q.concurrent_queue_name
FROM apps.fnd_concurrent_queues q, apps.fnd_cp_services s
WHERE enabled_flag = 'Y'
AND q.manager_type = s.service_id
AND s.cartridge_handle = 'CMDCART'; -- for example
b. directly from Oracle Applications :
System Administrator responsability
Navigator: Concurrent => Requests, choose a request ...
menu: Special => Manager Log
or
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Manager Log
3. CONCURRENT PROGRAMS
----------------------
a. retrieve Concurrent Program log files under :
$APPLCSF/$APPLLOG (if $APPLCSF and $APPLLOG exist) or
$<module_top>/$APPLLOG
File format is: l<Concurrent Request Id>.req
- If the concurrent program generates a output file, it goes to $APPLCSF/$APPLOUT.
- SQL can be used to get the exact file names for Log file and Output file:
SELECT logfile_name, outfile_name, outfile_node_name, last_update_date
FROM apps.FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = &requestID;
- There are also log files for concurrent requests under $COMMON_TOP/admin/log/$SID_$hostname. (Will they be useful only when you have Forms auditing on?)
b. directly from Oracle Applications :
System Administrator responsability (for own and other users requests) :
Navigator: Concurrent => Requests, button View Log...
Note: User may not be able to view other's Output (and Log) files.
4. AD, FND AND OTHER UTILITIES
-------------------------------
Some AD utilities like adpatch, adadmin, etc. generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file names are :
adunload.log
adrelink.log
adlibout.log
adlibin.log
adfrmgen.log
admrgpch.log
adrepgen.log
adctrl.log
admvcode.log
adaimgr.log
adwork01.log, adwork02.log, adwork03.log, ...
Utility adodfcmp generates log file under $<module_top>/admin/odf/ or $<module_top>/patch/<version>/odf/, file name is:
adodfcmp.log
FND utilities like fdfcmp, FNDFFVGN, FNDMDGEN generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file name is :
L<request_id>
5. USER EXITS (See note 292058.1)
--------------
User exits from forms generate log file under directory from where the forms server is started if variable FDUDEBUG is set to ON. File name is:
userexit.log
6. FORMS SERVER (See note 471921.1)
----------------
When starting forms server you can specify a log file name.
Exemples:
Windows: f60srv32 log=c:\temp\f60srv.log
Unix: f60ctl start port=9000 log=/temp/f60srv.log
(f60ctl is a script located in $ORACLE_HOME/bin)
Note: $COMMON_TOP/admin/log/$SID_$hostname/f60svrm.txt has very basic info from executing adfrmctl.sh
7. SQL*NET, NET8 LISTENER
--------------------------
You can specify LOG_DIRECTORY_<LISTENER_NAME>
You can set LOG_FILE and TRACE_FILE variables from a lsnrctl session.
There are also LOG_DIRECTORY_SERVER and LOG_FILE_SERVER parameters in sqlnet.ora.
8. APACHE, JSERV
----------------
Logs from Apache server are under $APACHE_TOP/logs, file names are:
error_log
access_log
httpds_access_log
httpds_error_log
sqlnet.log
Logs from Jserv are under $ORACLE_HOME/Apache/Jserv/logs (?), file names are:
jserv.log
debug.log
Note: it is recommended to clean up these log files, shutdown and restart Apache server before trying to reproduce the issue and analyze.
$APACHE_TOP is not defined in any $APPL_TOP/*.env file ("grep APACHE_TOP $APPL_TOP/*.env" gets nothing).
9. SSP5 (iPROCUREMENT)
---------------------
Apache and Jserv logs are useful to check for iProcurement (SSP5), you can also add these lines in ssp_init.txt (should be under $ORACLE_HOME/Apache/Jserv/etc) to generate specific SSP5 log file:
DebugOutput=<Directory/File name> (preferably under $APACHE_TOP/logs)
DebugLevel=5
DebugSwitch=ON
Note: don't forget to restart Apache server after editing ssp_init.txt.
10. PO Document Approval Manager
------------------------------------
Responsibility - System Administrator responsibility (Nav - Concurrent Manager - Administer).
Locate and find the PO Document Approval Manager and then click on Processes button and then select an Active concurrent manager process and click on the Manager Log button to display the log file.
11. Output Post Processor
---------------------------
See Metalink note 364547.1
Subscribe to:
Posts (Atom)