How to Verify if OID services are Completely Down

Dear All,

We had a situation where in we were asked to rebuild indexes of OID using "bulkload".Within OID all attributes needs to be indexed or attributes which are indexed needs to be rebuild.

Before we start rebuilding activity the OID should be either in 'read-only' mode or 'shutdown'.I have issued 'opmnctl stopall' and all services were shutdown cleanly,but when we ran bulkload we got the message that OID services are still running ,pelase shutdown.

So below are simple steps to check if OID services are shutdown cleanly.

1. First step is to use the normal 'OS command'

ps -ef | grep -i oid ( or ) ps -ef | grep -i <username>

<username> - is the user which you start/stop OID services

2. The next step is to use the 'oidctl' utility

$ oidctl connect=<servicename> status

<service name> - is the name of the DB service.

Eg
===
$ oidctl connect=OIDB status (in my case 'OIDB' is the database name)
NLS_LANG not set in environment
Setting NLS_LANG to AMERICAN_AMERICA.AL32UTF8
oidctl:Checking Oracle Internet Directory Processes ...
Process oidmon is not running ------------------> if any OID process is running,it would give you the PID
Checking OIDLDAPD instance 1 ...
Instance is shutdown ------------------> if any OIDLDAPD process is running,it would give you the PID and ports of dispatcher and server
OIDREPLD instance is not configured
ODISRV instance is not configured



3. The third step is to check the table 'ODS.ODS_PROCESS'

Normally after OID is shutdown there are no entries on these tables.Sometimes if the shutdown is not clear or due to any bugs
there are normally some entries on the table.Use the below query to check

set lines 200
col HOSTNAME format a30
col FLAGS format a20

select instance,PID,SERVERID,FLAGS,HOSTNAME,STATE,RETRYCOUNT,LASTWAKETIME from ods.ods_process;

If the above query returns any values after stopping OID,then it is not a clean shutdown. After ensuring the OID services are down ,truncate the table ODS.ODS_PROCESS.

truncate table ODS.ODS_PROCESS;

So now we have gone through the different ways/methods of how to check if OID services are down before starting any maintenance activities.

More about Cron

Hello ,

All of you would be aware that cron is a time based scheduler used in Unix based operarting systems.We used cron to schedule and execute shell scripts or commands periodically or during any date / time.

There was a requirement within our system to run a job at specific time intervals,no better option than cron.

When I logged in and issued "crontab -l" I received the following message

crontab: you are not authorized to use cron. Sorry.


On further researching found that there are two important control files which control the permissions and access to cron.They are

"cron.allow and cron.deny",both these files are ascii text and owned by root.

These files are generally found under directory /etc/cron.d/ .

If the cron.allow file does not exist and your username is not there in entry cron.deny you will be able to use cron.

If the file exists and your username does not exist in the cron.allow file then you will not be able to use cron.

The cron.deny will contain all users who are denied access to cron.

My case I just requested Unix sysadmin to add my username in file - cron.allow.

During an 11GR2 DB installation Solaris OS Patch 124861-15 missing

If you are not installing the Pro*C precompiler, this message can be safely ignored by checking the box "Ignore all" and pressing "Next" and continue with installation.

If you wish to install the Pro*C precompiler, please install Sun Studio 12 plus patch 124861-15 (NOT Sun Studio 12 Update 1) before installing the Oracle software

Ref - BUG:9214210

Thanks to Metalink note id - 969497.1

Diferrent Datafile Renaming Methodologies

As far as renaming a datafile is concerned we have 3 diferrent ways through which it can be achieved

method 1 - Offline a particular datafile and renaming it
method 2 - offline the tablespace and renaming the datafile alone
method 3 - shutdown DB,mount DB and rename the datafile



Method 1
=========

Before you can use this method the database must be in archive log mode.
In the below scenario I am renaming a datafile names test11.dbf to test12.dbf



SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline;
alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
SQL> alter database open;

Database altered.

SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline;

Database altered.


$ cp test11.dbf test12.dbf
$

SQL> alter database rename file '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' to '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf';

Database altered.

SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online;
alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf'


SQL> recover datafile 8;
Media recovery complete.
SQL>
SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online;

Database altered.

SQL>





Method 2
=========



Tablespace Offline Datafile Rename method

I take the particular tablespace offline and then rename the datafile.

a)

alter tablespace APPS_TS_TX_IDX offline;

b)

mv /u01/oracle/PROD/db/apps_st/data5/a_media19.dbf /u01/oracle/PROD/db/apps_st/index3/a_txn_ind43.dbf


c)

alter tablespace APPS_TS_TX_IDX rename datafile
/u01/oracle/PROD/db/apps_st/data5/a_media19.dbf
to
/u01/oracle/PROD/db/apps_st/index3/a_txn_ind43.dbf;

d)

alter tablespace APPS_TS_TX_IDX online;




Method 3
=========

In this scenario we take the database down ,start the database in mount option and then rename the datafile.Finally open the database.

In the below example I am renaming datafile test2.dbf to test3.dbf.



SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u06/oracle/11GR2/oradata/D11GR2/system01.dbf
/u06/oracle/11GR2/oradata/D11GR2/sysaux01.dbf
/u06/oracle/11GR2/oradata/D11GR2/undotbs01.dbf
/u06/oracle/11GR2/oradata/D11GR2/users01.dbf
/u06/oracle/11GR2/oradata/D11GR2/example01.dbf
/u06/oracle/11GR2/oradata/D11GR2/test1.dbf
/u06/oracle/11GR2/oradata/D11GR2/test2.dbf
/u06/oracle/11GR2/oradata/D11GR2/test12.dbf

8 rows selected.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> exit


$ mv /u06/oracle/11GR2/oradata/D11GR2/test2.dbf /u06/oracle/11GR2/oradata/D11GR2/test3.dbf



$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 4 17:18:22 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2723282944 bytes
Fixed Size 2151200 bytes
Variable Size 1509952736 bytes
Database Buffers 1191182336 bytes
Redo Buffers 19996672 bytes
Database mounted.
SQL>
SQL>
SQL> alter database rename file '/u06/oracle/11GR2/oradata/D11GR2/test2.dbf' to '/u06/oracle/11GR2/oradata/D11GR2/test3.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u06/oracle/11GR2/oradata/D11GR2/system01.dbf
/u06/oracle/11GR2/oradata/D11GR2/sysaux01.dbf
/u06/oracle/11GR2/oradata/D11GR2/undotbs01.dbf
/u06/oracle/11GR2/oradata/D11GR2/users01.dbf
/u06/oracle/11GR2/oradata/D11GR2/example01.dbf
/u06/oracle/11GR2/oradata/D11GR2/test1.dbf
/u06/oracle/11GR2/oradata/D11GR2/test3.dbf
/u06/oracle/11GR2/oradata/D11GR2/test12.dbf

8 rows selected.

SQL>

Oracle Trainings in support.oracle.com

Hi Viewers,

Oracle is maintaining all the recorded Webcast recorded sessions in the form of Oracle Documents in http://support.oracle.com .

The document id is 740964.1 [Advisor Webcast Archived Recordings ]

You can get all useful and valuable trainings from this document.

Thanks,
Sasikumar.M

"Synchronize WF LOCAL tables" request is failing with "ORA-20002: 4006:" error

Whenever we submit the "Synchronize WF LOCAL tables" request, it is failing with following error message,


FNDWFLSC module: Synchronize WF LOCAL tables
+---------------------------------------------------------------------------+

Current system time is 01-JUN-2010 01:41:09

+---------------------------------------------------------------------------+

**Starts**01-JUN-2010 01:41:09
**Ends**01-JUN-2010 01:41:43
ORA-20002: 4006: While creating necessary indexes, duplicate values were found preventing the creation of a unique index. This is caused by an improper view definition or data corruption that requires the attention of the Product Development Team handling 'PER_ROLE'.
ORA-20002: 4006: While creating necessary indexes, duplicate values were found preventing the creation of a unique index. This is caused by an improper view definition or data corruption that requires the attention of the Product Development Team handlin
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

After further troubleshooting, We used the following SQL statement and Identified the duplicate rows in APPS.PER_ROLE_ROLES_V view,

SELECT NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID,PARTITION_ID, COUNT(*) FROM
APPS.PER_ROLE_ROLES_V GROUP BY NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID,PARTITION_ID
HAVING COUNT(*) > 1;


Finally we did the following DML for removing the duplication row,

update per_all_people_f
set effective_end_date = to_date ('23-oct-2009','dd-mon-yyyy')
where person_id= (Person_Id_Return_by_Select_Query)
and effective_start_date= to_date('23-Oct-2009','dd-mon-yyyy')
and effective_end_date= to_date('14-Jan-2010','dd-mon-yyyy');


delete from per_all_people_f
where person_id= (Person_Id_Return_by_Select_Query)
and effective_start_date= to_date('15-Jan-2010','dd-mon-yyyy');

commit;

Reran the "Synchronize WF LOCAL tables" request and it is completing normal.

Thanks,
Sasikumar.M

APEX 4.0.2 installation with E-Business Suite R12

Recently I installed the APEX 4.0.2 in R12 ( 12.0.4 ) instances. Here are the steps which I followed.

1. Download the Apex software from http://download.oracle.com/otn/java/appexpress/apex_4.0.2.zip
2. Unzip the apex_4.0.2.zip file.
3. Take the invalid object counts.
4. Create the new table space APEX402 with 1 GB size

SQL> create tablespace APEX402 datafile '/oracle/VIS/oradata/apex402_01.dbf' size 1G autoextend on;

5. Login as SYSDBA user and execute the following SQL from the unzipped folder

SQL> @apexins.sql APEX402 APEX402 TEMP /i/

6. After 20-30 min, You will get following confirmation message,

Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the APEX_040000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

JOB_QUEUE_PROCESSES: 5

Performing Application Express component validation - please wait...

Completing registration process. 13:35:57
Validating installation. 13:35:57
...Database user "SYS", database schema "APEX_040000", user# "587" 13:35:57

7. Once you see this message, the Apex installation to the Oracle EBS database is completed.
8. Unlock the APEX_PUBLIC_USER account from the apex directory.

$ sqlplus "/as sysdba"
SQL> @apxchpwd.sql

9. Copy all the images files under apex/images directory to $COMMON_TOP/imagess (New folder) on the Front End Application server
10.Update the following entires in $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf file

Alias /i/ "/apps/vis/apps/apps_st/comn/images/"
AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString testdb.cisco.com:1531:VIS <-- Enter the DB connection Details
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword welcome <-- Enter the Admin Password
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all
</Location>

11. Make sure $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf file included in $IAS_ORACLE_HOME/Apache/modplsql/conf/plsql.conf.
12. Ensure $IAS_ORACLE_HOME/Apache/modplsql/conf/plsql.conf is included in $INST_TOP/ora/10.1.3/Apache/Apache/conf/oracle_apache.conf
13. By default, httpd.conf file should included the file $INST_TOP/ora/10.1.3/Apache/Apache/conf/oracle_apache.conf.
14. Bounce Apache services.
15. Access the APEX URL http://host:port/pls/apex/apex_admin and http://host:port/pls/apex

Comments are Welcome!!!

Thanks,
Sasikumar.M