So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.
This procedure will even work if your database is in NOARCHIVELOG mode.
You may have reached this posting through Google, and in a rush to get the solution right away, so here it is.
The recovery is done in two phases.
Phase 1: instant recovery to prevent Oracle errors (like ORA-27041 “unable to open file”, ORA-01110, ORA-01116)
1. Find the PID of DBWRITER for the right database.
ps -ef | grep dbw0_SID_OF_YOUR_DB
oracle 12272 1 0 10:55 ? 00:00:00 ora_dbw0_test
oracle 12941 11501 0 12:36 pts/0 00:00:00 grep dbw0_test
2. List the deleted file handles for that DBWRITER process.
ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted
lrwx------ 1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------ 1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)
3. Create a symbolic link to your datafile with the original name.
ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf
That’s all. Now you are no longer going to get errors. However, if your database goes down now, you will lose that datafile for good.
Phase 2: restore the file
ARCHIVELOG database
1. (Optional.) Issue a checkpoint. This is to reduce the recovery time when bringing the file online, depending on activity for that datafile. Unfortunately, you can’t checkpoint a single file, so the checkpoint may take some time.
alter system checkpoint;
2. Backup the datafile with rman. Why rman? It’s much easier then you think. Total downtime is about one second for inactive datafiles, and more for active ones (with writes).
rman target /
report schema;
backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';
sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';
switch datafile YOUR_DATAFILE_NUMBER to copy;
recover datafile YOUR_DATAFILE_NUMBER;
sql 'alter database datafile YOUR_DATAFILE_NUMBER online';
exit;
NOARCHIVELOG database
1. Make the tablespace with that datafile read only
select distinct tablespace_name from dba_data_files where file_name = 'YOUR_DELETED_FILE';
alter tablespace THE_TABLESPACE read only;
2. Copy the file from the symlink to a new name
cp SIM_LINK_DATA_FILE NEW_NAME_FOR_DATAFILE.dbf
3. WARNING: Ensure your copy is complete! Then, crash the database.
/*WAIT FOR COPY!!!*/
shutdown abort;
4. Remove the now invalid symlink, and rename the datafile to its original name. Be careful not to remove the wrong file now — that would be a disaster:
rm -i SIM_LINK_DATA_FILE
mv NEW_NAME_FOR_DATAFILE.dbf SIM_LINK_DATA_FILE
5. Startup your database normally and make the tablespace read/write.
startup
alter tablespace THE_TABLESPACE read write;
I hope this helps you to get out of a nasty situation.
Here’s both cases fully captured from terminal. Note, I am using Oracle-managed files. This doesn’t change the steps.
/ra5a/orabkp/test/TEST/datafile> sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:31:55 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
SQL>
SQL> select tablespace_name, file_name from dba_data_files
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------
USERS /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
SYSAUX /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
UNDOTBS1 /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
SYSTEM /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
EXAMPLE /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
LOST /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf
6 rows selected.
SQL> select tablespace_name, table_name from dba_tables where owner = 'TESTING';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
LOST LOST_TABLE
SQL> connect testing/testing
SQL> select count(*) from lost_table;
COUNT(*)
----------
50070
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from lost_table;
COUNT(*)
----------
50070
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r----- 1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 15 12:22 o1_mf_lost_3k74mq08_.dbf
-rw-r----- 1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r----- 1 oracle oinstall 26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r----- 1 oracle oinstall 5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf
/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:35:24 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
SQL> select count(*) from lost_table;
COUNT(*)
----------
50070
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from lost_table;
select count(*) from lost_table
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6:
'/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
/ra5a/orabkp/test/TEST/datafile> ps -ef|grep dbw0_test
oracle 12272 1 0 10:55 ? 00:00:00 ora_dbw0_test
oracle 12941 11501 0 12:36 pts/0 00:00:00 grep dbw0_test
/ra5a/orabkp/test/TEST/datafile> ls -l /proc/12272/fd|grep deleted
lrwx------ 1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
lrwx------ 1 oracle oinstall 64 Oct 15 12:17 26 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf (deleted)
/ra5a/orabkp/test/TEST/datafile> ln -s /proc/12272/fd/26 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> ls -lF
total 912620
-rw-r----- 1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx 1 oracle oinstall 17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r----- 1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 15 12:32 o1_mf_system_3k6xgwd4_.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r----- 1 oracle oinstall 26222592 Oct 15 12:32 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r----- 1 oracle oinstall 5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:38:18 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
SQL> select count(*) from lost_table;
COUNT(*)
----------
50070
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from lost_table;
COUNT(*)
----------
50070
SQL>
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
/ra5a/orabkp/test/TEST/datafile> rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Mon Oct 15 12:39:48 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=1934173752)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /ra5a/orabkp/test/TEST/datafile/o1_mf_system_3k6xgwd4_.dbf
2 25 UNDOTBS1 *** /ra5a/orabkp/test/TEST/datafile/o1_mf_undotbs1_3k6xgwg9_.dbf
3 230 SYSAUX *** /ra5a/orabkp/test/TEST/datafile/o1_mf_sysaux_3k6xgwdf_.dbf
4 5 USERS *** /ra5a/orabkp/test/TEST/datafile/o1_mf_users_3k6xgwhb_.dbf
5 150 EXAMPLE *** /ra5a/orabkp/test/TEST/datafile/o1_mf_example_3k6xjdjw_.dbf
6 100 LOST *** /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /ra5a/orabkp/test/TEST/datafile/o1_mf_temp_3k6xj9xn_.tmp
RMAN> backup as copy datafile 6 format '/ra5a/orabkp/test/TEST/datafile/lost.dbf';
Starting backup at 2007-10-15 12:40:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k74mq08_.dbf
output filename=/ra5a/orabkp/test/TEST/datafile/lost.dbf tag=TAG20071015T124045 recid=13 stamp=636036046
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2007-10-15 12:40:46
RMAN> sql 'alter database datafile 6 offline';
sql statement: alter database datafile 6 offline
RMAN> switch datafile 6 to copy;
datafile 6 switched to datafile copy "/ra5a/orabkp/test/TEST/datafile/lost.dbf"
RMAN> recover datafile 6;
Starting recover at 2007-10-15 12:41:07
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2007-10-15 12:41:07
RMAN> sql 'alter database datafile 6 online';
sql statement: alter database datafile 6 online
RMAN>
Recovery Manager complete.
/ra5a/orabkp/test/TEST/datafile> ls -lF
total 1015132
-rw-r----- 1 oracle oinstall 104865792 Oct 15 12:41 lost.dbf
-rw-r----- 1 oracle oinstall 157294592 Oct 15 12:22 o1_mf_example_3k6xjdjw_.dbf
lrwxrwxrwx 1 oracle oinstall 17 Oct 15 12:37 o1_mf_lost_3k74mq08_.dbf -> /proc/12272/fd/26
-rw-r----- 1 oracle oinstall 241180672 Oct 15 12:32 o1_mf_sysaux_3k6xgwdf_.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 15 12:38 o1_mf_system_3k6xgwd4_.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 15 10:17 o1_mf_temp_3k6xj9xn_.tmp
-rw-r----- 1 oracle oinstall 26222592 Oct 15 12:38 o1_mf_undotbs1_3k6xgwg9_.dbf
-rw-r----- 1 oracle oinstall 5251072 Oct 15 12:22 o1_mf_users_3k6xgwhb_.dbf
/ra5a/orabkp/test/TEST/datafile> rm o1_mf_lost_3k74mq08_.dbf
/ra5a/orabkp/test/TEST/datafile> sqlplus testing/testing
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 15 12:42:03 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
SQL> select count(*) from lost_table;
COUNT(*)
----------
50070
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from lost_table;
COUNT(*)
----------
50070
SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
Thursday, May 1, 2008
Saturday, April 19, 2008
PROCEDURES RELATED TO TABLE REORGANIZATION
PROCEDURES RELATED TO TABLE REORGANIZATION
Step1. Coalesce free extents in tablespaces, before and after each reorg.
select 'alter tablespace '||tablespace_name||' coalesce;'
from dba_tablespaces
order by tablespace_name;
Step 2: To determine the fragmentation level in a database - here are some fragmentation assessment criteria:
1. High numbers of extents (acceptable < 1024 extents for very large objects - look out for extents per object > 5)
ALL OBJECTS WITH NUMBER OF EXTENTS > 500 (except temporary and rollback segments))
Select owner||'.'||segment_name,segment_type, tablespace_name, count(*) exn,
round(sum(bytes/1024/1024),0) mbytes from dba_extents
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and segment_type not in ('TEMPORARY','ROLLBACK')
having count(*) > 500 group by owner||'.'||segment_name, segment_type, tablespace_name order by 4 desc, 1, 2;
2. High percentages of chained rows per table (acceptable < 3 percent- look out for percentages > 0.1 percent) - analyze the tables first
(ALL TABLES WITH PERCENTAGE OF CHAINED ROWS > 0.1% (acceptable < 3%))
select owner||'.'||table_name, nvl(chain_cnt,0)/(nvl(num_rows,0)+1)*100 chained_rows_percent, nvl(chain_cnt,0)/1000 num_chained_rows,
nvl(num_rows,0)/1000 num_rows from dba_tables
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and nvl(chain_cnt,0)/(nvl(num_rows,0)+1)*100 > 0.1
order by 2 desc, 1;
3. High percentages of free space inside blocks (look out for FREESPACE/BLOCK > 2*PCTFREE)
(ALL TABLES WITH FREESPACE/BLOCK > 2*PCTFREE)
select owner||'.'||table_name,
nvl(avg_space,0)/p.value*100 avg_space_percent, nvl(pct_free,0) percent_free,
nvl(pct_used,0) percent_used, nvl(avg_row_len,0) avg_row_len,
nvl(num_rows,0)/1000 num_rows from dba_tables, v$parameter p
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and p.name = 'db_block_size' and num_rows > 1000
and nvl(avg_space,0)/p.value*100 > 2*pct_free order by 2 desc, 5 desc;
NOTE-- avg_space is average free space in a block below HWM
4. High percentages of free space above highwatermark (look out for EMPTY BLOCKS ABOVE HWM > 50 percent)
(ALL TABLES WITH PERCENTAGE OF EMPTY BLOCKS (ABOVE HWM) > 50% (waisted space))
(select owner||'.'||table_name, nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)*100 empty_blocks_percent, nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)
*(nvl(t.empty_blocks+t.blocks,0)*p.value/1024/1024) waisted_space_mb,
nvl(t.empty_blocks+t.blocks,0)*p.value/1024/1024 table_size_mb,
nvl(t.avg_row_len,0) avg_row_len, nvl(t.num_rows,0)/1000 num_rows
from dba_tables t, v$parameter p where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER')
and p.name = 'db_block_size'
and t.num_rows > 1000 and nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)*100 > 50
order by 2 desc, 3 desc, 5 desc;
NOTE: -- empty blocks above HWM
1. Before doing the reorg take count of invalid objects in each schema as well for APPS, APPS_MRC, APPSVIEW. The reorgs will invalidate lot of objects in APPS, APPS_MRC, APPSVIEW schemas.
select owner, count(*) from dba_objects where status='INVALID' and owner like 'APP%' group by owner ;
select count(*) from dba_objects where status=’INVALID’ and owner=’’;
2. Check for the partitioned tables by using the following query.
select table_name ,partitioned from dba_tables where table_name=;
Based on the output we need to build the separate syntax to move the partioned tables and associated indexes.
3.ANALYSIS ON DATA TYPES:
Do the analysis of data types in the given schema other than standard (date, char, number, varchar2, rowid) data types.
Identify different types of datatypes for each schema by executing below sql
Select distinct data_type from dba_tab_columns where owner=’AAAA’;
Based on the output of this SQL we will decide which reorg mechanisim need to be followed.
4.ANALYSIS OF CHAINED ROWS:
Do the analysis for chained rows in the schema.
select owner,table_name, chain_cnt from dba_tables
where chain_cnt > 1 and owner in ('ONT','BOM','MRP') order by chain_cnt;
OWNER TABLE_NAME CHAIN_CNT
--------------- ------------------------------ ------------------
BOM BOM_OPERATIONAL_ROUTINGS 11
Modify the move syntax for those tables by adding PCTUSED 40 PCTFREE 25 clause.
Syntax:
alter table owner. move tablespace
storage (PCTUSED 40 PCTFREE 25 pctincrease 0);
5.FOR LOB DATA TYPE:
The default manual rebuild scripts generated by alter table move will be as below and it fails with below error. So use above SQL for LOB data types. This need to be done only for the index on LOB column and rest of the indexes can be done by manual scripts. So change the script with above sql where is LOB index with appropriate tablename and LOB column name.
alter index AR.SYS_IL0000716146C00221$$ rebuild tablespace ARXS1 storage(initial 128k next 128k pctincrease 0);
ERROR at line 1:
RA-02327: cannot create index on expression with datatype LOB
You can get the LOB column details by using the following query:
select table_name, column_name,segment_name from dba_lobs where Index_name='SYS_IL0000716146C00221$$' and owner='AR';
TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- -------------------- ------------------------------
HZ_PARTY_INTERFACE BUSINESS_REPORT SYS_LOB0000716146C00221$$
Syntax:
alter table AR.HZ_PARTY_INTERFACE move lob(BUSINESS_REPORT)
store as lobsegment (tablespace ARDS1 storage(initial 512k next 512k pctincrease 0));
NOTE: We have to rebuild other indexes after moving the lobsegment to another tablespace.
6.FOR LONG DATA TYPE:
If the datatype is LONG, then we have to export/import the table/schema
8. If we find any partitioned tables prepare the scripts to move the partioned tables and indexes.
Alter table table_name move partition partition_name;
9. For general reorg use below syntax for tables and their associated indexes.
10.
Syntax:
Alter table owner. move storage(initial 512k next 512k pctincrease 0);
Alter indeex owner. rebuild ;
Use same syntax for all the tables and indexes
10.. After completing reorgs, check invalids in schema as well for APPS, APPS_MRC, APPSVIEW and compile them manually or if the count is too high run adadmin compile apps schema.
select owner,count(*) from dba_objects where status='INVALID'and owner like'APP%' group by owner;
select count(*) from dba_objects where status=’INVALID’ and owner=’’;(object_name=’AAAAA’)
Step1. Coalesce free extents in tablespaces, before and after each reorg.
select 'alter tablespace '||tablespace_name||' coalesce;'
from dba_tablespaces
order by tablespace_name;
Step 2: To determine the fragmentation level in a database - here are some fragmentation assessment criteria:
1. High numbers of extents (acceptable < 1024 extents for very large objects - look out for extents per object > 5)
ALL OBJECTS WITH NUMBER OF EXTENTS > 500 (except temporary and rollback segments))
Select owner||'.'||segment_name,segment_type, tablespace_name, count(*) exn,
round(sum(bytes/1024/1024),0) mbytes from dba_extents
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and segment_type not in ('TEMPORARY','ROLLBACK')
having count(*) > 500 group by owner||'.'||segment_name, segment_type, tablespace_name order by 4 desc, 1, 2;
2. High percentages of chained rows per table (acceptable < 3 percent- look out for percentages > 0.1 percent) - analyze the tables first
(ALL TABLES WITH PERCENTAGE OF CHAINED ROWS > 0.1% (acceptable < 3%))
select owner||'.'||table_name, nvl(chain_cnt,0)/(nvl(num_rows,0)+1)*100 chained_rows_percent, nvl(chain_cnt,0)/1000 num_chained_rows,
nvl(num_rows,0)/1000 num_rows from dba_tables
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and nvl(chain_cnt,0)/(nvl(num_rows,0)+1)*100 > 0.1
order by 2 desc, 1;
3. High percentages of free space inside blocks (look out for FREESPACE/BLOCK > 2*PCTFREE)
(ALL TABLES WITH FREESPACE/BLOCK > 2*PCTFREE)
select owner||'.'||table_name,
nvl(avg_space,0)/p.value*100 avg_space_percent, nvl(pct_free,0) percent_free,
nvl(pct_used,0) percent_used, nvl(avg_row_len,0) avg_row_len,
nvl(num_rows,0)/1000 num_rows from dba_tables, v$parameter p
where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER') and p.name = 'db_block_size' and num_rows > 1000
and nvl(avg_space,0)/p.value*100 > 2*pct_free order by 2 desc, 5 desc;
NOTE-- avg_space is average free space in a block below HWM
4. High percentages of free space above highwatermark (look out for EMPTY BLOCKS ABOVE HWM > 50 percent)
(ALL TABLES WITH PERCENTAGE OF EMPTY BLOCKS (ABOVE HWM) > 50% (waisted space))
(select owner||'.'||table_name, nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)*100 empty_blocks_percent, nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)
*(nvl(t.empty_blocks+t.blocks,0)*p.value/1024/1024) waisted_space_mb,
nvl(t.empty_blocks+t.blocks,0)*p.value/1024/1024 table_size_mb,
nvl(t.avg_row_len,0) avg_row_len, nvl(t.num_rows,0)/1000 num_rows
from dba_tables t, v$parameter p where owner not in ('SYS', 'SYSTEM', 'MHSYS', 'OEM16', 'OEM204', 'OEM21', 'OEM22', 'SPC_DEMO_USER')
and p.name = 'db_block_size'
and t.num_rows > 1000 and nvl(t.empty_blocks,0)/(nvl(t.empty_blocks+t.blocks,0)+1)*100 > 50
order by 2 desc, 3 desc, 5 desc;
NOTE: -- empty blocks above HWM
1. Before doing the reorg take count of invalid objects in each schema as well for APPS, APPS_MRC, APPSVIEW. The reorgs will invalidate lot of objects in APPS, APPS_MRC, APPSVIEW schemas.
select owner, count(*) from dba_objects where status='INVALID' and owner like 'APP%' group by owner ;
select count(*) from dba_objects where status=’INVALID’ and owner=’
2. Check for the partitioned tables by using the following query.
select table_name ,partitioned from dba_tables where table_name=
Based on the output we need to build the separate syntax to move the partioned tables and associated indexes.
3.ANALYSIS ON DATA TYPES:
Do the analysis of data types in the given schema other than standard (date, char, number, varchar2, rowid) data types.
Identify different types of datatypes for each schema by executing below sql
Select distinct data_type from dba_tab_columns where owner=’AAAA’;
Based on the output of this SQL we will decide which reorg mechanisim need to be followed.
4.ANALYSIS OF CHAINED ROWS:
Do the analysis for chained rows in the schema.
select owner,table_name, chain_cnt from dba_tables
where chain_cnt > 1 and owner in ('ONT','BOM','MRP') order by chain_cnt;
OWNER TABLE_NAME CHAIN_CNT
--------------- ------------------------------ ------------------
BOM BOM_OPERATIONAL_ROUTINGS 11
Modify the move syntax for those tables by adding PCTUSED 40 PCTFREE 25 clause.
Syntax:
alter table owner.
storage (PCTUSED 40 PCTFREE 25 pctincrease 0);
5.FOR LOB DATA TYPE:
The default manual rebuild scripts generated by alter table move will be as below and it fails with below error. So use above SQL for LOB data types. This need to be done only for the index on LOB column and rest of the indexes can be done by manual scripts. So change the script with above sql where is LOB index with appropriate tablename and LOB column name.
alter index AR.SYS_IL0000716146C00221$$ rebuild tablespace ARXS1 storage(initial 128k next 128k pctincrease 0);
ERROR at line 1:
RA-02327: cannot create index on expression with datatype LOB
You can get the LOB column details by using the following query:
select table_name, column_name,segment_name from dba_lobs where Index_name='SYS_IL0000716146C00221$$' and owner='AR';
TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- -------------------- ------------------------------
HZ_PARTY_INTERFACE BUSINESS_REPORT SYS_LOB0000716146C00221$$
Syntax:
alter table AR.HZ_PARTY_INTERFACE move lob(BUSINESS_REPORT)
store as lobsegment (tablespace ARDS1 storage(initial 512k next 512k pctincrease 0));
NOTE: We have to rebuild other indexes after moving the lobsegment to another tablespace.
6.FOR LONG DATA TYPE:
If the datatype is LONG, then we have to export/import the table/schema
8. If we find any partitioned tables prepare the scripts to move the partioned tables and indexes.
Alter table table_name move partition partition_name
9. For general reorg use below syntax for tables and their associated indexes.
10.
Syntax:
Alter table owner.
Alter indeex owner.
Use same syntax for all the tables and indexes
10.. After completing reorgs, check invalids in schema as well for APPS, APPS_MRC, APPSVIEW and compile them manually or if the count is too high run adadmin compile apps schema.
select owner,count(*) from dba_objects where status='INVALID'and owner like'APP%' group by owner;
select count(*) from dba_objects where status=’INVALID’ and owner=’
Friday, March 21, 2008
RMAN incremental backup
RMAN> connect target *
run
{
allocate channel t1 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
allocate channel t2 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
allocate channel t3 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
backup incremental level 1 skip inaccessible
TAG='INCREMENTAL_LEVEL_1' database include current controlfile;
release channel t1;
release channel t2;
release channel t3;
allocate channel t1 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/al_%d_%p_%u_%t';
sql 'alter system archive log current';
change archivelog all crosscheck;
backup skip inaccessible archivelog all delete input;
crosscheck backup;
delete obsolete;
delete expired backup;
delete expired archivelog all;
release channel t1;
}
run
{
allocate channel t1 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
allocate channel t2 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
allocate channel t3 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/%d_%s_%t.bak';
backup incremental level 1 skip inaccessible
TAG='INCREMENTAL_LEVEL_1' database include current controlfile;
release channel t1;
release channel t2;
release channel t3;
allocate channel t1 type disk format
'/u01/OraApps/oracle/product/11.5.10.2/rman_backup/al_%d_%p_%u_%t';
sql 'alter system archive log current';
change archivelog all crosscheck;
backup skip inaccessible archivelog all delete input;
crosscheck backup;
delete obsolete;
delete expired backup;
delete expired archivelog all;
release channel t1;
}
Saturday, March 15, 2008
Script to Archive Automation
#!/usr/bin/ksh
# Functions
#A function to format the date
function formatDate {
date +%Y%m%d"."%H%M%S
}
#A function to generate a time stamp
function getTimestamp {
echo `formatDate` -- "${*}"
}
#A function to exit after an error condition
function exitError {
getTimestamp ${exitCodeArr[${1}]}
exit ${1}
}
#Function to set the environmental varriable from oratab.
function ToSetEnvVariables ()
{
if [ -f /etc/oratab ]; then
OraTab=/etc/oratab
elif [ -f /var/opt/oracle/oratab ]; then
OraTab=/var/opt/oracle/oratab
fi
unset ORACLE_HOME
OH=`cat /etc/oratab |grep -v "#"|grep -v "*."|grep "$1"|cut -d ":" -f2`
ORACLE_SID=$1;export ORACLE_SID
ORACLE_HOME=$OH;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:.;export PATH
}
#Function to backup the archive log file aftre reaching to threshold of archive log file system
function cleararchivelog ()
{
ToSetEnvVariables $1
$ORACLE_HOME/bin/rman target / cmdfile /u01/Scripts/backup_arch_rman.cmd msglog /u01/Scripts/backup_arch_rman.log
}
sqlplus -s "/as sysdba" < /dev/null
set feed off echo off head off trimspool on timing off set pagesize 1000 verify off
spool CheckarchDest.lst
select round(PERCENT_SPACE_USED) from v\$FLASH_RECOVERY_AREA_USAGE where FILE_TYPE='ARCHIVED LOG';
spool off
set feed on
eof
cat CheckarchDest.lst|grep -v "rows selected"|grep -v "SQL" |grep -v ^$ > check_percent_arch_full
while read line
do
Percent=`echo $line |awk '{print $1}'`
if [ ${Percent} -gt $2 ]; then
cleararchivelog $1
else
echo "Looks Okay"
fi
done < check_percent_arch_full
# Functions
#A function to format the date
function formatDate {
date +%Y%m%d"."%H%M%S
}
#A function to generate a time stamp
function getTimestamp {
echo `formatDate` -- "${*}"
}
#A function to exit after an error condition
function exitError {
getTimestamp ${exitCodeArr[${1}]}
exit ${1}
}
#Function to set the environmental varriable from oratab.
function ToSetEnvVariables ()
{
if [ -f /etc/oratab ]; then
OraTab=/etc/oratab
elif [ -f /var/opt/oracle/oratab ]; then
OraTab=/var/opt/oracle/oratab
fi
unset ORACLE_HOME
OH=`cat /etc/oratab |grep -v "#"|grep -v "*."|grep "$1"|cut -d ":" -f2`
ORACLE_SID=$1;export ORACLE_SID
ORACLE_HOME=$OH;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:.;export PATH
}
#Function to backup the archive log file aftre reaching to threshold of archive log file system
function cleararchivelog ()
{
ToSetEnvVariables $1
$ORACLE_HOME/bin/rman target / cmdfile /u01/Scripts/backup_arch_rman.cmd msglog /u01/Scripts/backup_arch_rman.log
}
sqlplus -s "/as sysdba" <
set feed off echo off head off trimspool on timing off set pagesize 1000 verify off
spool CheckarchDest.lst
select round(PERCENT_SPACE_USED) from v\$FLASH_RECOVERY_AREA_USAGE where FILE_TYPE='ARCHIVED LOG';
spool off
set feed on
eof
cat CheckarchDest.lst|grep -v "rows selected"|grep -v "SQL" |grep -v ^$ > check_percent_arch_full
while read line
do
Percent=`echo $line |awk '{print $1}'`
if [ ${Percent} -gt $2 ]; then
cleararchivelog $1
else
echo "Looks Okay"
fi
done < check_percent_arch_full
Thursday, March 6, 2008
ISSUE: 115102 upgrade Workflow Mailer Service
ISSUE:
After 115102 upgrade Workflow Mailer Service | Workflow Agent Listener Service | Workflow Document Web Services Service DO NOT STARTUP
Steps to reproduce:
1. configure WF mailer as per Note:268085.1
2. Activate WF Concurrent processing
Concurrent : Manager -> Administer
Workflow Agent Listener Service
Workflow Mailer Service
Workflow Document Web Services Service
Activate all of them
##
## Verify that workflow mailer and agent listener services running successfully
## without exceptions in the log files. [they are not]
##
dbtier.applmgr->ls -l $APPLCSF/$APPLLOG/FNDCPGSC*.txt
-rw-r--r-- 1 applmgr oaa 359 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199414.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199415.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199416.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199417.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199418.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199419.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199420.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:09 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199421.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:09 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199422.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:11 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199423.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:11 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199424.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:13 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199425.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:13 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199426.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:15 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199427.txt
##
## Here's the error that was reported in one of the log files
## after I Activated WF% cm queues
##
dbtier.applmgr->more /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199415.txt
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started.
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer.initializeStateMachine() : BEGIN
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsc.SvcComponentContainer.getNewWorkflowContext() : BEGIN
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsc.SvcComponentContainer.getNewWorkflowContext() : BEGIN
Could not start Service Component Container because an unexpected RuntimeException or other Throwable occurred -> java.lang.No
ClassDefFoundError: oracle/apps/pass/profiles/Profiles
java.lang.NoClassDefFoundError: oracle/apps/pass/profiles/Profiles
at oracle.apps.fnd.profiles.ExtendedProfileStore.clear(Compiled Code)
at oracle.apps.fnd.security.SessionManager.setUser(Compiled Code)
at oracle.apps.fnd.security.SessionManager.setUser(Compiled Code)
at oracle.apps.fnd.wf.apps.ContextFactory.makeContext(Compiled Code)
at oracle.apps.fnd.wf.common.WorkflowContext.(Compiled Code)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.getNewWorkflowContext(Compiled Code)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.start(Compiled Code)
at oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer.main(Compiled Code)
dbtier.applmgr->echo $AF_CLASSPATH
/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/appsborg.zip:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java
dbtier.applmgr->grep jar $APPL_TOP/admin/adjborg2.txt
/u02/app/applmgr/product/iAS_115102/jlib/jssl-1_1.jar
/u02/app/applmgr/product/iAS_115102/jlib/javax-ssl-1_1.jar
/u02/app/applmgr/product/iAS_115102/rdbms/jlib/aqapi.jar
/u02/app/applmgr/product/iAS_115102/rdbms/jlib/jmscommon.jar
/u02/app/applmgr/product/iAS_115102/Apache/xsu/xsu12.jar
/u02/app/applmgr/product/iAS_115102/Apache/xsu/oraclexmlsql.jar
/u02/app/applmgr/product/iAS_115102/rdbms/jlib/xsu12.jar
/u02/app/applmgr/product/iAS_115102/lib/http_client.jar
/u02/app/applmgr/product/iAS_115102/dm/lib/odmapi.jar
/u02/app/applmgr/product/iAS_115102/soap/webapps/soap/WEB-INF/lib/soap.jar
dbtier.applmgr->unzip -l $JAVA_TOP/appsborg2.zip | grep -i jms | wc -l
128
dbtier.applmgr->grep jar $APPL_TOP/admin/adjborg.txt
/u02/app/applmgr/product/8.0.6_115102/owm/jlib/jssl-1_1.jar
/u02/app/applmgr/product/8.0.6_115102/owm/jlib/javax-ssl-1_1.jar
/u02/app/applmgr/product/iAS_115102/dm/lib/odmapi.jar
dbtier.applmgr->
## this setting is coming from:
$APPL_TOP/admin/adovars.env
AF_CLASSPATH="/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/appsborg.zip:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java"
export AF_CLASSPATH
show be:
Example of AF_CLASSPATH setting:
/local/java/jdk1.3.1/lib/dt.jar:/local/java/jdk1.3.1/lib/tools.jar:/slot05/appmgr/atgwfqacomn/java/appsborg2.zip:/slot05/appmgr/atgwfqaora/8.0.6/forms60/java:/slot05/appmgr/atgwfqacomn/java
dbtier.applmgr->grep classpath $APPL_TOP/admin/BHUP_dbtier.xml
# wrapper.classpath =
# wrapper.classpath =
/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/tools.jar:/u02/app/applmgr/common/java/appsborg.zip:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java
/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/appsborg.zip:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java
.:/u02/app/applmgr/common/java/jdbc111.zip:/u02/app/applmgr/common/java/xmlparserv2.zip:/u02/app/applmgr/common/java:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/common/util/jre/1.1.8/classes:/u02/app/applmgr/common/util/jre/1.1.8/lib:/u02/app/applmgr/common/util/jre/1.1.8/lib/classes.zip:/u02/app/applmgr/common/util/jre/1.1.8/lib/classes.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/3rdparty/RFJavaInt.zip:
/u02/app/applmgr/common/util/jre/1.1.8/bin/jre -mx128m -classpath .:/u02/app/applmgr/common/java/jdbc111.zip:/u02/app/applmgr/common/java/xmlparserv2.zip:/u02/app/applmgr/common/java:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/common/util/jre/1.1.8/classes:/u02/app/applmgr/common/util/jre/1.1.8/lib:/u02/app/applmgr/common/util/jre/1.1.8/lib/classes.zip:/u02/app/applmgr/common/util/jre/1.1.8/lib/classes.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/3rdparty/RFJavaInt.zip: -Dengine.LogPath=/u02/app/applmgr/common/admin/log/BHUP_dbtier -Dengine.TempDir=/u02/app/applmgr/common_cm/temp -Dengine.CommandPort=9300 -Dengine.AOLJ.config=/u02/app/applmgr/11.5/fnd/11.5.0/secure/dbtier_xupg.dbc -Dengine.ServerID=5000 -Ddebug=full -Dengine.LogLevel=9 -Dlog.ShowWarnings=false -Dengine.FaxEnabler=oracle.apps.jtf.fm.engine.rightfax.RfFaxEnablerImpl -Dengine.PrintEnabler=oracle.apps.jtf.fm.engine.rightfax.RfPrintEnablerImpl -Dfax.TempDir=/u02/app/applmgr/common/admin/log/BHUP_dbtier -Dprint.TempDir=/u02/app/applmgr/common/admin/log/BHUP_dbtier oracle.apps.jtf.fm.FulfillmentServer >> /u02/app/applmgr/common/admin/log/BHUP_dbtier/jtffmctl.txt
things that need to be changed:
SOLUTION
----------
##
## FIX AF_CLASSPATH
##
## EXISTING SETTING [WRONG] ##
dbtier.applmgr->grep s_adovar_afclasspath $APPL_TOP/admin/BHUP_dbtier.xml
/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/appsborg.zip:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java
## SHOULD BE ##
Example of AF_CLASSPATH setting:
/local/java/jdk1.3.1/lib/dt.jar:
/local/java/jdk1.3.1/lib/tools.jar:
/slot05/appmgr/atgwfqacomn/java/appsborg2.zip:
/slot05/appmgr/atgwfqaora/8.0.6/forms60/java:
/slot05/appmgr/atgwfqacomn/java
## OUR MAPPING BE ##
/usr/j2se/lib/dt.jar:
/usr/j2se/lib/tools.jar:
/u02/app/applmgr/common/java/appsborg2.zip:
/u02/app/applmgr/product/8.0.6_115102/forms60/java:
/u02/app/applmgr/common/java
## OUR MAPPING MT ##
/usr/j2se/lib/dt.jar:
/usr/j2se/lib/tools.jar:
/u01/app/applmgr/common/java/appsborg2.zip:
/u01/app/applmgr/product/8.0.6_115102/forms60/java:
/u01/app/applmgr/common/java
## FIXED SETTING ##
dbtier.applmgr->grep s_adovar_afclasspath $APPL_TOP/admin/BHUP_dbtier.xml
/usr/j2se/lib/dt.jar:/usr/j2se/lib/tools.jar:/u02/app/applmgr/common/java/appsborg2.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java
midtier.applmgr->grep s_adovar_afclasspath $APPL_TOP/admin/BHUP_midtier.xml
/usr/j2se/lib/dt.jar:/usr/j2se/lib/tools.jar:/u01/app/applmgr/common/java/appsborg2.zip:/u01/app/applmgr/product/8.0.6_115102/forms60/java:/u01/app/applmgr/common/java
##
## FIX java exe definition
##
## EXISTING SETTING [WRONG] ##
dbtier.applmgr->grep -i AF_JRE_TOP $APPL_TOP/admin/BHUP_dbtier.xml
/u02/app/applmgr/common/util/jre/1.1.8
dbtier.applmgr->grep -i AFJVAPRG $APPL_TOP/admin/BHUP_dbtier.xml
/u02/app/applmgr/common/util/jre/1.1.8/bin/jre
## SHOULD BE ##
AF_JRE_TOP=/usr/j2se
export AF_JRE_TOP
AFJVAPRG=/usr/j2se/bin/java
export AFJVAPRG
## FIXED SETTING ##
dbtier.applmgr->grep -i AF_JRE_TOP $APPL_TOP/admin/BHUP_dbtier.xml
/usr/j2se
dbtier.applmgr->grep -i AFJVAPRG $APPL_TOP/admin/BHUP_dbtier.xml
/usr/j2se/bin/java
midtier.applmgr->grep -i AF_JRE_TOP $APPL_TOP/admin/BHUP_midtier.xml
/usr/j2se
midtier.applmgr->grep -i AFJVAPRG $APPL_TOP/admin/BHUP_midtier.xml
/usr/j2se/bin/java
##
## Re-run AutoConfig
##
## BE tier
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/BHUP_dbtier.xml appspass=pass
## MT tier
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/BHUP_midtier.xml appspass=pass
## MT tier
##
## comment out the following lines from jserv.properties and jserv.conf:
## refer to SR:780 11.5.10.2 AppsLocalLogin.jsp Internal Server Error
##
vi /u01/app/applmgr/product/iAS_115102/Apache/Jserv/etc/jserv.properties
#zones=mobile
#mobile.properties=/u01/app/applmgr/product/iAS_115102/Apache/Jserv/etc/mobile.properties
vi /u01/app/applmgr/product/iAS_115102/Apache/Jserv/etc/jserv.conf
#ApJServGroupMount /mobile balance://OACoreGroup/mobile
##
## Shutdown Services
##
## BE tier
/u02/app/applmgr/common/admin/scripts/BHUP_dbtier/adstpall.sh apps/pass
## MT tier
/u01/app/applmgr/common/admin/scripts/BHUP_midtier/adstpall.sh apps/pass
##
## Start Services
##
## BE tier
exit
su - applmgr
/u02/app/applmgr/common/admin/scripts/BHUP_dbtier/adstrtal.sh apps/pass
## MT tier
exit
su - applmgr
/u01/app/applmgr/common/admin/scripts/BHUP_midtier/adstrtal.sh apps/pass
##
## TEST call to external SMTP server
##
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=mailhub.company.com \
-Dport=25 -Daccount=oracle.dba@company.com -Dconnect_timeout=120 \
oracle.apps.fnd.wf.mailer.Mailer
dbtier.applmgr->$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=mailhub.company.com \
-Dport=25 -Daccount=oracle.dba@company.com -Dconnect_timeout=120 \
oracle.apps.fnd.wf.mailer.Mailer
> -Dport=25 -Daccount=oracle.dba@company.com -Dconnect_timeout=120 \
> oracle.apps.fnd.wf.mailer.Mailer
Server mailhub.company.com at port 25 is reachable
Successfully connected to the SMTP account
REFERENCE:
Note:242941.1 How To Troubleshoot Java-based Workflow Notification Mailer In 11.5.9 and OWF.G
Note:278127.1 Workflow Notification Will Not Start After Cloning
Note:278163.1 Workflow Notification Mailer Will Not Start
After 115102 upgrade Workflow Mailer Service | Workflow Agent Listener Service | Workflow Document Web Services Service DO NOT STARTUP
Steps to reproduce:
1. configure WF mailer as per Note:268085.1
2. Activate WF Concurrent processing
Concurrent : Manager -> Administer
Workflow Agent Listener Service
Workflow Mailer Service
Workflow Document Web Services Service
Activate all of them
##
## Verify that workflow mailer and agent listener services running successfully
## without exceptions in the log files. [they are not]
##
dbtier.applmgr->ls -l $APPLCSF/$APPLLOG/FNDCPGSC*.txt
-rw-r--r-- 1 applmgr oaa 359 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199414.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199415.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199416.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199417.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199418.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199419.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:08 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199420.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:09 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199421.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:09 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199422.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:11 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199423.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:11 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199424.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:13 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199425.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:13 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199426.txt
-rw-r--r-- 1 applmgr oaa 1270 Mar 8 17:15 /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199427.txt
##
## Here's the error that was reported in one of the log files
## after I Activated WF% cm queues
##
dbtier.applmgr->more /u02/app/applmgr/common_cm/log/BHUP_dbtier/FNDCPGSC199415.txt
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started.
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer.initializeStateMachine() : BEGIN
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsc.SvcComponentContainer.getNewWorkflowContext() : BEGIN
LOG_ID_UNKNOWN : oracle.apps.fnd.cp.gsc.SvcComponentContainer.getNewWorkflowContext() : BEGIN
Could not start Service Component Container because an unexpected RuntimeException or other Throwable occurred -> java.lang.No
ClassDefFoundError: oracle/apps/pass/profiles/Profiles
java.lang.NoClassDefFoundError: oracle/apps/pass/profiles/Profiles
at oracle.apps.fnd.profiles.ExtendedProfileStore.clear(Compiled Code)
at oracle.apps.fnd.security.SessionManager.setUser(Compiled Code)
at oracle.apps.fnd.security.SessionManager.setUser(Compiled Code)
at oracle.apps.fnd.wf.apps.ContextFactory.makeContext(Compiled Code)
at oracle.apps.fnd.wf.common.WorkflowContext.
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.getNewWorkflowContext(Compiled Code)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.start(Compiled Code)
at oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer.main(Compiled Code)
dbtier.applmgr->echo $AF_CLASSPATH
/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/appsborg.zip:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java
dbtier.applmgr->grep jar $APPL_TOP/admin/adjborg2.txt
/u02/app/applmgr/product/iAS_115102/jlib/jssl-1_1.jar
/u02/app/applmgr/product/iAS_115102/jlib/javax-ssl-1_1.jar
/u02/app/applmgr/product/iAS_115102/rdbms/jlib/aqapi.jar
/u02/app/applmgr/product/iAS_115102/rdbms/jlib/jmscommon.jar
/u02/app/applmgr/product/iAS_115102/Apache/xsu/xsu12.jar
/u02/app/applmgr/product/iAS_115102/Apache/xsu/oraclexmlsql.jar
/u02/app/applmgr/product/iAS_115102/rdbms/jlib/xsu12.jar
/u02/app/applmgr/product/iAS_115102/lib/http_client.jar
/u02/app/applmgr/product/iAS_115102/dm/lib/odmapi.jar
/u02/app/applmgr/product/iAS_115102/soap/webapps/soap/WEB-INF/lib/soap.jar
dbtier.applmgr->unzip -l $JAVA_TOP/appsborg2.zip | grep -i jms | wc -l
128
dbtier.applmgr->grep jar $APPL_TOP/admin/adjborg.txt
/u02/app/applmgr/product/8.0.6_115102/owm/jlib/jssl-1_1.jar
/u02/app/applmgr/product/8.0.6_115102/owm/jlib/javax-ssl-1_1.jar
/u02/app/applmgr/product/iAS_115102/dm/lib/odmapi.jar
dbtier.applmgr->
## this setting is coming from:
$APPL_TOP/admin/adovars.env
AF_CLASSPATH="/u02/app/applmgr/common/util/jre/1.1.8/lib/rt.jar:/u02/app/applmgr/common/util/jre/1.1.8/lib/i18n.jar:/u02/app/applmgr/common/java/appsborg.zip:/u02/app/applmgr/common/java/apps.zip:/u02/app/applmgr/product/8.0.6_115102/forms60/java:/u02/app/applmgr/common/java"
export AF_CLASSPATH
show be:
Example of AF_CLASSPATH setting:
/local/java/jdk1.3.1/lib/dt.jar:/local/java/jdk1.3.1/lib/tools.jar:/slot05/appmgr/atgwfqacomn/java/appsborg2.zip:/slot05/appmgr/atgwfqaora/8.0.6/forms60/java:/slot05/appmgr/atgwfqacomn/java
dbtier.applmgr->grep classpath $APPL_TOP/admin/BHUP_dbtier.xml
things that need to be changed:
SOLUTION
----------
##
## FIX AF_CLASSPATH
##
## EXISTING SETTING [WRONG] ##
dbtier.applmgr->grep s_adovar_afclasspath $APPL_TOP/admin/BHUP_dbtier.xml
## SHOULD BE ##
Example of AF_CLASSPATH setting:
/local/java/jdk1.3.1/lib/dt.jar:
/local/java/jdk1.3.1/lib/tools.jar:
/slot05/appmgr/atgwfqacomn/java/appsborg2.zip:
/slot05/appmgr/atgwfqaora/8.0.6/forms60/java:
/slot05/appmgr/atgwfqacomn/java
## OUR MAPPING BE ##
/usr/j2se/lib/dt.jar:
/usr/j2se/lib/tools.jar:
/u02/app/applmgr/common/java/appsborg2.zip:
/u02/app/applmgr/product/8.0.6_115102/forms60/java:
/u02/app/applmgr/common/java
## OUR MAPPING MT ##
/usr/j2se/lib/dt.jar:
/usr/j2se/lib/tools.jar:
/u01/app/applmgr/common/java/appsborg2.zip:
/u01/app/applmgr/product/8.0.6_115102/forms60/java:
/u01/app/applmgr/common/java
## FIXED SETTING ##
dbtier.applmgr->grep s_adovar_afclasspath $APPL_TOP/admin/BHUP_dbtier.xml
midtier.applmgr->grep s_adovar_afclasspath $APPL_TOP/admin/BHUP_midtier.xml
##
## FIX java exe definition
##
## EXISTING SETTING [WRONG] ##
dbtier.applmgr->grep -i AF_JRE_TOP $APPL_TOP/admin/BHUP_dbtier.xml
dbtier.applmgr->grep -i AFJVAPRG $APPL_TOP/admin/BHUP_dbtier.xml
## SHOULD BE ##
AF_JRE_TOP=/usr/j2se
export AF_JRE_TOP
AFJVAPRG=/usr/j2se/bin/java
export AFJVAPRG
## FIXED SETTING ##
dbtier.applmgr->grep -i AF_JRE_TOP $APPL_TOP/admin/BHUP_dbtier.xml
dbtier.applmgr->grep -i AFJVAPRG $APPL_TOP/admin/BHUP_dbtier.xml
midtier.applmgr->grep -i AF_JRE_TOP $APPL_TOP/admin/BHUP_midtier.xml
midtier.applmgr->grep -i AFJVAPRG $APPL_TOP/admin/BHUP_midtier.xml
##
## Re-run AutoConfig
##
## BE tier
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/BHUP_dbtier.xml appspass=pass
## MT tier
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/BHUP_midtier.xml appspass=pass
## MT tier
##
## comment out the following lines from jserv.properties and jserv.conf:
## refer to SR:780 11.5.10.2 AppsLocalLogin.jsp Internal Server Error
##
vi /u01/app/applmgr/product/iAS_115102/Apache/Jserv/etc/jserv.properties
#zones=mobile
#mobile.properties=/u01/app/applmgr/product/iAS_115102/Apache/Jserv/etc/mobile.properties
vi /u01/app/applmgr/product/iAS_115102/Apache/Jserv/etc/jserv.conf
#ApJServGroupMount /mobile balance://OACoreGroup/mobile
##
## Shutdown Services
##
## BE tier
/u02/app/applmgr/common/admin/scripts/BHUP_dbtier/adstpall.sh apps/pass
## MT tier
/u01/app/applmgr/common/admin/scripts/BHUP_midtier/adstpall.sh apps/pass
##
## Start Services
##
## BE tier
exit
su - applmgr
/u02/app/applmgr/common/admin/scripts/BHUP_dbtier/adstrtal.sh apps/pass
## MT tier
exit
su - applmgr
/u01/app/applmgr/common/admin/scripts/BHUP_midtier/adstrtal.sh apps/pass
##
## TEST call to external SMTP server
##
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=mailhub.company.com \
-Dport=25 -Daccount=oracle.dba@company.com -Dconnect_timeout=120 \
oracle.apps.fnd.wf.mailer.Mailer
dbtier.applmgr->$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=mailhub.company.com \
-Dport=25 -Daccount=oracle.dba@company.com -Dconnect_timeout=120 \
oracle.apps.fnd.wf.mailer.Mailer
> -Dport=25 -Daccount=oracle.dba@company.com -Dconnect_timeout=120 \
> oracle.apps.fnd.wf.mailer.Mailer
Server mailhub.company.com at port 25 is reachable
Successfully connected to the SMTP account
REFERENCE:
Note:242941.1 How To Troubleshoot Java-based Workflow Notification Mailer In 11.5.9 and OWF.G
Note:278127.1 Workflow Notification Will Not Start After Cloning
Note:278163.1 Workflow Notification Mailer Will Not Start
Wednesday, March 5, 2008
Concurrent manager -- Doc
http://www.dbatoolz.com/scripts_desc.htm --- Concurrent manager/program scripts
Concurrent Processing (CP) / APPS Reporting Scripts
Doc ID: Note:213021.1
Note:200358.1 Oracle Application Object Library Report Review Agent Setup Test
Note 74717.1: Troubleshooting Concurrent Manager Startup problems
Note 97798.1 ORA-904 when starting concurrent managers
Note 230121.1 Routine AFPEIM encountered an error while starting Concurrent Manager on Cloned instance
Note 1059201.6 Status pending with 'Inactive No Manager' cor Concurrent Requests
Note 113135.1 Conflict Resolution Manager FNDCRM Does Not Start; NoError Message.
Note 123607.1 CONCSUB SYSADMIN 'System Administrator' GIVES INVALID RESPONSIBILITY ERROR
Note 2055375.6 RESET PMON METHOD (CHOICES: LOCK, OS, RDBMS)
Note 2069781.6 Basic Troubleshooting of the Concurrent Managers
Note 2120154.6 Unable to start the ccm after install - core dump
Note 171855.1 CCM.sql Diagnostic Script for Concurrent Manager
Note 185036.1 Unable to Startup or Shutdown the Concurrent Managers in 11.5.5 by Using the Syntax adcmctl.sh apps/ Start or Stop
Concurrent Processing (CP) / APPS Reporting Scripts
Doc ID: Note:213021.1
Note:200358.1 Oracle Application Object Library Report Review Agent Setup Test
Note 74717.1: Troubleshooting Concurrent Manager Startup problems
Note 97798.1 ORA-904 when starting concurrent managers
Note 230121.1 Routine AFPEIM encountered an error while starting Concurrent Manager on Cloned instance
Note 1059201.6 Status pending with 'Inactive No Manager' cor Concurrent Requests
Note 113135.1 Conflict Resolution Manager FNDCRM Does Not Start; NoError Message.
Note 123607.1 CONCSUB SYSADMIN 'System Administrator' GIVES INVALID RESPONSIBILITY ERROR
Note 2055375.6 RESET PMON METHOD (CHOICES: LOCK, OS, RDBMS)
Note 2069781.6 Basic Troubleshooting of the Concurrent Managers
Note 2120154.6 Unable to start the ccm after install - core dump
Note 171855.1 CCM.sql Diagnostic Script for Concurrent Manager
Note 185036.1 Unable to Startup or Shutdown the Concurrent Managers in 11.5.5 by Using the Syntax adcmctl.sh apps/
Saturday, March 1, 2008
1. 4898608 - Latest Opatch (10.2.0.3.4 )
1. 4898608 - Latest Opatch (10.2.0.3.4 )
cd /admin/orapatch/oracle/10.2.0.3/Patches/OPatch
mv $ORACLE_HOME/OPatch/ $ORACLE_HOME/OPatch_old
mkdir $ORACLE_HOME/OPatch
cp -rp * $ORACLE_HOME/OPatch/.
2. 5929784 - MLR FOR THE FIXES IN 4966417/5605370 AND 5631915 ON TOP OF 10.2.0.3
cd /admin/orapatch/oracle/10.2.0.3/Patches/5929784
opatch apply -local
3. 6358777 - MLR for 6121268 and 5998987
cd /admin/orapatch/oracle/10.2.0.3/Patches/6358777
opatch apply -local
ApplySession applying interim patch '6358777' to OH '/u01/app/oracle/product/10.2.0.3_Aug/DBEE_1'
Interim patch 6358777 is a superset of the patch(es) [ 5998987 ] in OH /u01/app/oracle/product/10.2.0.3_Aug/DBEE_1
OPatch will rollback the subset patches and apply the given patch.
The local system has been patched and can be restarted.
4. 6487758 - MLR for 6397940 and 4587572
cd /admin/orapatch/oracle/10.2.0.3/Patches/6487758
opatch apply -local
5. 6523687 - MLR for 6397948 and 5606847
cd /admin/orapatch/oracle/10.2.0.3/Patches/6523687
opatch apply -local
# After applying/rolling back the patch, you need to run following :
#
Connect sys as sysdba;
ALTER SESSION SET CURRENT_SCHEMA=MDSYS;
$ORACLE_HOME/md/admin/prvtcat.plb
$ORACLE_HOME/md/admin/prvtrtr.plb
$ORACLE_HOME/md/admin/prvtsam.plb
ALTER SESSION SET CURRENT_SCHEMA = SYS;
6. 6646853 - Jan 2008 CPU
cd /admin/orapatch/oracle/10.2.0.3/Patches/6646853
./apply_cpu_proderp_aug.sh
a> On one RAC node, start the instance and execute:
cd $ORACLE_HOME/cpu/CPUJan2008
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catcpu.sql
SQL> QUIT
b> If catcpu.sql reports any Invalid Objects, run the following commands:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @utlrp.sql
c> You must recompile views for all databases except the following:
To recompile the views in the database, follow these steps:
Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP;
Check the log file for any errors. The log file is in the current directory and is named: vcomp__.log
d> If any invalid objects were reported, run the utlrp.sql script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @utlrp.sql
e> Verify that the view recompilation has been performed for the database, by executing the following statement:
SELECT * FROM registry$history where ID = '6452863';
If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.
cd /admin/orapatch/oracle/10.2.0.3/Patches/OPatch
mv $ORACLE_HOME/OPatch/ $ORACLE_HOME/OPatch_old
mkdir $ORACLE_HOME/OPatch
cp -rp * $ORACLE_HOME/OPatch/.
2. 5929784 - MLR FOR THE FIXES IN 4966417/5605370 AND 5631915 ON TOP OF 10.2.0.3
cd /admin/orapatch/oracle/10.2.0.3/Patches/5929784
opatch apply -local
3. 6358777 - MLR for 6121268 and 5998987
cd /admin/orapatch/oracle/10.2.0.3/Patches/6358777
opatch apply -local
ApplySession applying interim patch '6358777' to OH '/u01/app/oracle/product/10.2.0.3_Aug/DBEE_1'
Interim patch 6358777 is a superset of the patch(es) [ 5998987 ] in OH /u01/app/oracle/product/10.2.0.3_Aug/DBEE_1
OPatch will rollback the subset patches and apply the given patch.
The local system has been patched and can be restarted.
4. 6487758 - MLR for 6397940 and 4587572
cd /admin/orapatch/oracle/10.2.0.3/Patches/6487758
opatch apply -local
5. 6523687 - MLR for 6397948 and 5606847
cd /admin/orapatch/oracle/10.2.0.3/Patches/6523687
opatch apply -local
# After applying/rolling back the patch, you need to run following :
#
Connect sys as sysdba;
ALTER SESSION SET CURRENT_SCHEMA=MDSYS;
$ORACLE_HOME/md/admin/prvtcat.plb
$ORACLE_HOME/md/admin/prvtrtr.plb
$ORACLE_HOME/md/admin/prvtsam.plb
ALTER SESSION SET CURRENT_SCHEMA = SYS;
6. 6646853 - Jan 2008 CPU
cd /admin/orapatch/oracle/10.2.0.3/Patches/6646853
./apply_cpu_proderp_aug.sh
a> On one RAC node, start the instance and execute:
cd $ORACLE_HOME/cpu/CPUJan2008
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catcpu.sql
SQL> QUIT
b> If catcpu.sql reports any Invalid Objects, run the following commands:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @utlrp.sql
c> You must recompile views for all databases except the following:
To recompile the views in the database, follow these steps:
Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP;
Check the log file for any errors. The log file is in the current directory and is named: vcomp_
d> If any invalid objects were reported, run the utlrp.sql script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @utlrp.sql
e> Verify that the view recompilation has been performed for the database, by executing the following statement:
SELECT * FROM registry$history where ID = '6452863';
If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.
Subscribe to:
Posts (Atom)