ORA-23616: Failure in executing block 4 for script dbms_streams_adm

Error:

###########################################
ORA-23616: Failure in executing block 4 for script 94CDB67BB22F03BFE0400A0A710A1FF7 with
ORA-26723: user "DBMS_STR_USER" requires the role "DBA"
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 2422
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2975
###########################################

Find more info using view below:

SQL> select * from dba_recoverable_script_errors;

SCRIPT_ID BLOCK_NUM ERROR_NUMBER ERROR_MESSAGE ERROR_CREATION_TIME
-------------------------------- ---------- ------------ -------------------------------------------------------------------------------- -------------------
94CDA13FFD84CDE6E0400A0A710A1D8F 4 -26723 ORA-26723: user "DBMS_STR_USER" requires the role "DBA" 11/11/2010 19:31:16


################

Grant DBA to user.

test again.

Best Regards,
Paulo Portugal

EXPDP ORA-39002 ORA-39070 ORA-29283 ORA-06512 ORA-29283 LOGFILE

If experiencing this error below when trying to export using EXPDP:

############################################
[oraebs@hom1 bkp_old_hml]$ expdp system/xxxxxxx dumpfile=expdir:exp_hml_%U.dmp filesize=3G logfile=log_exp_hml.log FULL=Y

Export: Release 10.2.0.4.0 - 64bit Production on Saturday, 06 November, 2010 16:42:42

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
############################################

Just change the sintaxe to use the directory name together in LOGFILE parameter as below:

expdp system/xxxxxx directory=expdir dumpfile=expdir:exp_hml_%U.dmp filesize=3G logfile=expdir:log_exp_hml.log FULL=Y



Best Regards,
Paulo Portugal

Apache Web Server Listener is not running adstrtal.sh adapcctl.sh

While trying to start apache after a clone the error below appears:

###############
adapcctl status

Apache Web Server Listener is not running
Apache Web Server Listener (PLSQL) :httpd ( pid 2669 ) is running.

###############
Do this:

cd $IAS_ORACLE_HOME/bin
adlnkiAS.sh
cd $COMMON_TOP/admin/scripts/SID_Hostname
adapcctl.sh start

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


Best Regards,
Paulo Portugal

ORA-29275: partial multibyte character when querying gv$session

If you face this error when querying v$session just create a a view like below:

############################################################
create view my_gv$session as
select "INST_ID",
"SADDR",
"SID",
"SERIAL#",
"AUDSID",
"PADDR",
"USER#",
"USERNAME",
"COMMAND",
"OWNERID",
"TADDR",
"LOCKWAIT",
"STATUS",
"SERVER",
"SCHEMA#",
"SCHEMANAME",
"OSUSER",
"PROCESS",
"MACHINE",
"TERMINAL",
"PROGRAM",
"TYPE",
"SQL_ADDRESS",
"SQL_HASH_VALUE",
"SQL_ID",
"SQL_CHILD_NUMBER",
"PREV_SQL_ADDR",
"PREV_HASH_VALUE",
"PREV_SQL_ID",
"PREV_CHILD_NUMBER",
"PLSQL_ENTRY_OBJECT_ID",
"PLSQL_ENTRY_SUBPROGRAM_ID",
"PLSQL_OBJECT_ID",
"PLSQL_SUBPROGRAM_ID",
"MODULE",
"MODULE_HASH",
convert(action,'utf8','utf8') "ACTION",
"ACTION_HASH",
"CLIENT_INFO",
"FIXED_TABLE_SEQUENCE",
"ROW_WAIT_OBJ#",
"ROW_WAIT_FILE#",
"ROW_WAIT_BLOCK#",
"ROW_WAIT_ROW#",
"LOGON_TIME",
"LAST_CALL_ET",
"PDML_ENABLED",
"FAILOVER_TYPE",
"FAILOVER_METHOD",
"FAILED_OVER",
"RESOURCE_CONSUMER_GROUP",
"PDML_STATUS",
"PDDL_STATUS",
"PQ_STATUS",
"CURRENT_QUEUE_DURATION",
"CLIENT_IDENTIFIER",
"BLOCKING_SESSION_STATUS",
"BLOCKING_INSTANCE",
"BLOCKING_SESSION",
"SEQ#",
"EVENT#",
"EVENT",
"P1TEXT",
"P1",
"P1RAW",
"P2TEXT",
"P2",
"P2RAW",
"P3TEXT",
"P3",
"P3RAW",
"WAIT_CLASS_ID",
"WAIT_CLASS#",
"WAIT_CLASS",
"WAIT_TIME",
"SECONDS_IN_WAIT",
"STATE",
"SERVICE_NAME",
"SQL_TRACE",
"SQL_TRACE_WAITS",
"SQL_TRACE_BINDS"
from gv$session;

############################################################

Note that the problem is when you are using UTF8 character set. You need to convert action column as below:
---
convert(action,'utf8','utf8') "ACTION",
---

Best Regards,
Paulo Portugal

Using opatch brings error : OPatch detects your platform as 226 while this patch 8372118 supports platforms

I were trying to apply a patch and got the error show below:

########################################################
[appsoa@hom3 8372118]$ /app01/SOA/iassoa/OPatch/opatch apply

Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home : /app01/SOA/iassoa
Oracle Home Inventory : /app01/SOA/iassoa/inventory
Central Inventory : /app01/SOA/iassoa/oraInventory
from : /etc/oraInst.loc
OUI location : /app01/SOA/iassoa/oui
OUI shared library : /app01/SOA/iassoa/oui/lib/linux/liboraInstaller.so
Java location : /app01/SOA/iassoa/jre/1.4.2/bin/java
Log file location : /app01/SOA/iassoa/.patch_storage//*.log

Creating log file "/app01/SOA/iassoa/.patch_storage/8372118/Apply_8372118_09-28-2010_12-55-17.log"

Invoking fuser to check for active processes.


OPatch detects your platform as 226 while this patch 8372118 supports platforms:
0 (Generic Platform 1)

########################################################

The workaround is:

export OPATCH_PLATFORM_ID=0

And run the opatch again.

Best Regards,
Paulo Portugal

While trying to compile a form with frmcmp_batch.sh got ORA-12154

Example:

[appebs@hom3 PTB]$ frmcmp_batch.sh module=TACCOPH.fmb Userid=apps/appdesenv123 module_type=FORM compile_all=SPECIAL
Forms 10.1 (Form Compiler) Version 10.1.2.0.2 (Production)

Forms 10.1 (Form Compiler): Release - Production

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

PL/SQL Version 10.1.0.5.0 (Production)
Oracle Procedure Builder V10.1.2.0.2 - Production
Oracle Virtual Graphics System Version 10.1.2.0.0 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.5.0 Production
ORA-12154: TNS:could not resolve the connect identifier specified


Workaround:
Change TNS_ADMIN on frmcmp_batch.sh like bellow:

--OLD VALUE
TNS_ADMIN=$ORACLE_HOME/network/admin
export TNS_ADMIN

--NEW VALUE
# TNS_ADMIN=$ORACLE_HOME/network/admin
# export TNS_ADMIN

TNS_ADMIN=${TNS_ADMIN:-$ORACLE_HOME/network/admin}
export TNS_ADMIN


OR Apply patch 5456500 as per NOTE 431324.1

Best Regards,
Paulo Portugal

Running RDA Diagnostics on E-Business R12

Sometimes, when you have a Service Request open with Oracle Support, they can ask you to run Oracle Diagnostics Tool for some products on your R12 Application.

Here is a simple example of running for AR and JL products:
########################################
cd cd $IZU_TOP/bin
./rda.sh -vdCRP -e APPL_SHORT='JL' ACT

./rda.sh -vdCRP -e APPL_SHORT='AR' ACT
########################################

Then just upload the zip file to Oracle Suppoort.

Best Regards,
Paulo Portugal

Install iAS using non default ports - staticports.ini file

If you need to install iAS using non default ports.

Create a file using the custom ports (you can find an example in Disk1/stage/Response/statisticports.ini file)

Next step is to use the parameter on runInstaller as below:

./runInstaller oracle.ocs.onebox:s_staticPorts=/app01/XXX/Stage/Disk1/stage/Response/staticports.ini


Best Regards,
Paulo Portugal

ORA-26723: user "STR_TARGET" requires the role "DV_STREAMS_ADMIN"

If you face the error message below while configuring Oracle Streams:

ORA-26723: user "STR_TARGET" requires the role "DV_STREAMS_ADMIN"
ORA-06512: at "SYS.DBMS_STREAMS_ADM_UTL_INVOK", line 349
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 439
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 397
ORA-06512: at line 2


Just run the grant as follows and COMMIT after:
SQL> execute dbms_streams_auth.grant_admin_privilege('STR_SOURCE');

PL/SQL procedure successfully completed

SQL> commit;


Don't forget to COMMIT!

If it didn't work, then disable Database Vault before configuring Streams and enable it after. I use the following command to disable it in Oracle Binaries Files:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle

ATTENTION: This should not be done if you don't know who and why Database Vault is enabled in this database.

Best regards,
Paulo Portugal

How to cleanup .patch_storage directory

If you are experiencing space problems and found that your ORACLE_HOME have a .patch_storage directory that is too big here is a solution.

As you cannot delete this directory because it contains patches that maybe you need to rollback, Oracle provides a method to delete just unnecessary files by using Opatch utility (higher than 10.2.0.x).

This is very simple:

Example:

Size before running Opatch command:
[hostxxxx]/dbname/products/rdbms/.patch_storage> du -sg
2.45 .


--Running Opatch command:
[hostxxxx] opatch util cleanup
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /dbxxx/products/rdbms
Central Inventory : /opt/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.3
OUI version : 10.2.0.4.0
OUI location : /dbxxx/products/rdbms/oui
Log file location : /dbxxx/products/rdbms/cfgtoollogs/opatch/opatch2010-09-04_1 3-56-38PM.log

Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
Could not recognize input. Please re-enter.
y
User Responded with: Y
Size of directory "/dbxxx/products/rdbms/.patch_storage" before cleanup is 2618766807 bytes.
Size of directory "/dbxxx/products/rdbms/.patch_storage" after cleanup is 159049176 bytes.

UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.

OPatch succeeded.

--Size after clean up
[hostxxxx]/dbxxx/products/rdbms/.patch_storage> du -sg
0.15 .


Best Regards,
Paulo Portugal

The new Oracle XSTream

Oracle provides a new API called XSTream that is based on Oracle Streams and used to replicate data between Oracle Databases. It is also possible to replicate to other databases using Oracle Gateway, JMS, or directy with an third application consuming messages from a queue.

XSTream makes use of OCI or Java interface to capture and propagate data.

The configuration of XTream is done using DBMS_XSTREAM_ADM and it works with XSTream Outbound and XTream Inbound.

One simple example of how to configue an XTream Outbound for table HR.EMPLOYEES and HR.JOBS and also

DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tabs(1) := 'HR.EMPLOYEES';
tabs(2) := 'HR.JOBS';
my_schemas(1) := 'HR';
DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
server_name => 'MY_FIRST_OUT',
table_names => tabs,
schema_names => my_schemas);
END;
/

I will post more examples soon....

Best Regards,
Paulo Portugal

RMAN catalog and uncatalog examples

--RMAN catalog and uncatalog examples
--Catalog
RMAN>CATALOG ARCHIVELOG '/oracle/oradata/dbname/a_1_1883.arc', '/oracle/oradata/dbname/a_1_1885.arc';

RMAN>CATALOG DATAFILECOPY '/oradata/backup/data01.dbf' LEVEL 0;

RMAN>CATALOG CONTROLFILECOPY '/oradata/backup/ctl01.ctl';

RMAN>CATALOG START WITH '/backups/bkp_db' NOPROMPT;

RMAN>CATALOG RECOVERY AREA NOPROMPT;
or
RMAN>CATALOG DB_RECOVERY_FILE_DEST;


RMAN>CATALOG BACKUPPIECE '/u02/oradata/dbname/bkp_piece_name';


--Uncatalog
RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;

RMAN>CHANGE BACKUP OF TABLESPACE TBS_DATA01 UNCATALOG;

RMAN>CHANGE BACKUPPIECE '+DG_DATA/bakup/offr423' UNCATALOG;

ORA-30006: resource busy; acquire with WAIT timeout expired XA Conecction Oracle Retail

If you are facing erros like ORA-30006: resource busy; acquire with WAIT timeout expired when trying to connect to a a XA service, Kill inactive sessions on your database , restart XA service using (srvctl or crs_stop/crs_start) and reload database listeners.

It works for me!

Best Regards,
Paulo Portugal

Dropping Grid Control Repository

If you are reinstalling Grid Control you need to drop old repository using this command below:

./RepManager hostname 1526 ORACLESID -action drop -sys_password sys_password

Best Regards,
Paulo Portugal

Uploading agent error "EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors."

Agent is Running and Ready
[oraagent@host ~]$ emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors.


1-Stop agent
emctl stop agent
2-Delete all files in $AGENT_HOME/sysman/emd/upload

3-Clear agent
emctl clearstate agent

4-Secure the agent
emctl secure agent (inform agent password)

5-Start agent
emctl start agent


This works for me!

Best Regards,
Paulo Portugal

Cloning ORACLE_HOME Agent installation

If you need to clone your ORACLE_HOME Agent installation to another server execute the following steps:

1-TAR your OH source
tar -cvhf agent10g.tar agent10g

2-Transfer file to destination server

3-Create the new OH Agent directories and OS user

4-UnTAR the file
tar -xvf agent10g.tar

5-Run root.sh as root user

6-Clean old files from source installation
cd $AGENT_HOME/sysman/emd/collection
rm *

7-Run runInstaller to clone source OH as on example below:

./runInstaller -clone -forceClone ORACLE_HOME=/ora01/emagent/agent10g ORACLE_HOME_NAME=OraAgent10g -noconfig -silent

8-Configure the new agent
$ORACLE_HOME/bin/agentca -f

9-Run root.sh again

$ORACLE_HOME/root.sh --as root user

Generating statistics facing "ORA-38029: object statistics are locked" error

--If you try to analyze an object, or an entirely schema or even all database objects (using dbms_utility for instance) and face this error below:
ORA-38029: object statistics are locked
ORA-06512: at "SYS.DBMS_DDL", line 257
ORA-06512: at "SYS.DBMS_UTILITY", line 488
ORA-06512: at line 2


Fix this problem by following these next steps:

1-Find which schemas have locked tables
select
owner,
table_name,
stattype_locked
from
dba_tab_statistics
where
stattype_locked is not null;

2-Unlock table or all schema statistics using dbms_stats package:

begin
dbms_stats.unlock_schema_stats(ownname => 'SYS');
dbms_stats.unlock_schema_stats(ownname => 'USER1');
dbms_stats.unlock_schema_stats(ownname => 'USER2');
end;
/

3-Execute the analyze_database procedure again (or the procedure that you attempted before). (it must work).


Best Regards,
Paulo Portugal

OUI-10197:Unable to create a new Oracle Home at /ora01/emagent. Oracle Home already exists at this location. Select another location.

If you want to unregister your OH use command simmilar to this one below:

--Error displayed
OUI-10197:Unable to create a new Oracle Home at /ora01/emagent. Oracle Home already exists at this location. Select another location.

--Solution (cleaning inventory)
./runInstaller -silent -detachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/ora01/emagent" ORACLE_HOME_NAME="OraAgent10g"


Best Regards,
Paulo Portugal

Oracle Secure Backup commands

Some OSB commands:

--Check volumes
lsdev -lvg

--Check jobs
lsjob --all --long

--Change the host characteristics
obtool -u admin chhost -r client,admin,mediaserver "f2c07"

--Create a library
obtool -u admin mkdev -t library -o -a f2c07:/dev/obl0 test22

--Create a tape
obtool -u admin mkdev -t tape -o -a f2c07:/dev/obt0 -l test22


I will post more soon.

Regards,
Paulo Portugal

--Oracle Secure Backup Oracle Secure Backup error: 'no preauth config ound for OS user (OB tools) orasb

If you found an error like this one below:
################################################################################

released channel: devite
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on devite channel at 05/07/2010 16:28:55
ORA-19506: failed to create sequential file, name="01ld3fm6_1_1", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
sbt__rpc_cat_query: Query for piece 01ld3fm6_1_1 failed.
(Oracle Secure Backup error: 'no preauth config found for OS user (OB tools) orasb').
################################################################################


Fix it goint to your Web Tool Amin Configure>Users>Add (add the user name owner of database binaries) and then
go back to this page, click in user just created and click on Edit>Preauthorized Access. Here you will add the host and attributes that this user needs to have.

Try runing your backup again!

Best Regards,
Paulo Portugal

Using new Oracle Database 11g ADRCI utility example

ADRCI is a command line utility that can be used in order to help find and fix problems in database.
The name stands for Automatic Diagnostic Repository Command Interpreter.

Command line exaples:

adrci> SHOW PROBLEMS --Used to show problems founded on databases
ADR Home = /u01/app/oracle/diag/rdbms/dbms/dbms:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
9 ORA 7445 [jsv_udf_init()+62] 102465 2010-05-05 22:01:26.759000 -03:00
8 ORA 7445 [ktspfsall()+1079] 99828 2010-04-30 15:02:42.091000 -03:00
7 ORA 1578 91582 2010-04-27 21:05:44.602000 -03:00


adrci> show base --Used to show current ORACLE_BASE
ADR base is "/u01/app/oracle"

adrci> SET BASE /u01/app/oraebs --Uset to change ORACLE_BASE of database to be analyzed


adrci> set homepath diag/rdbms/dbms/dbms --Setting just one homepath

--To show ADR information use this command below
adrci> show control
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1824989551 720 8760 2009-10-27 11:01:08.204025 -02:00 2010-05-03 09:49:12.845517 -03:00 2009-11-16 12:57:16.833107 -02:00 1 2 76 1 2009-10-27 11:01:08.204025 -02:00
1 rows fetched

Blank page when trying to access forms on Oracle Applications

--Blank page when trying to access forms on Oracle Applications

Steps to reproduce the problem:
1-Login to Oracle Applications
2-Go to any Responsibility
3-Try to access any form and nothing happens
4-If you try to start or stop forms (adfrmctl.sh) or reports (adrepctl.sh), errors like these one are displayed.

#######################################################################

Executing service control script:
/u01/app/r11i/r11comn/admin/scripts/hmlr11_surucucu/adrepctl.sh start
script returned:
****************************************************

You are running adrepctl.sh version 115.33

Cannot reconnect to gateway

Cause: Application Object Library is unable to reconnect to your gateway ORACLE account after you unsuccessfully attempted to sign-on.

Action: Check that your gateway environment variable is set correctly.
starting Reports Server for hmlr11 on port 7070.
Cannot reconnect to gateway

Cause: Application Object Library is unable to reconnect to your gateway ORACLE account after you unsuccessfully attempted to sign-on.

Action: Check that your gateway environment variable is set correctly.

adrepctl.sh: exiting with status 0


.end std out.

.end err out.

#######################################################################
The first thing to check is if GUEST account is working fine using the SQL command below:

SQL> select fnd_web_sec.validate_login('GUEST','GUEST') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','GUEST')
--------------------------------------------------------------------------------
N


As you note, GUEST LOGIN is not working. Check the GUEST password in file $FND_TOP/secure/hostname_SID.dbc
# grep -i GUEST_USER_PWD /u01/app/r11i/r11appl/fnd/11.5.0/secure/surucucu_hmlr11.dbc

Check the GUEST user and password on your CONTEXT_FILE:
# grep guest $CONTEXT_FILE
GUEST
ORACLE

After checking and changing everything, run autoconfig.
cd $COMMON_TOP/admin/scripts/SID_hostname
./adautocfg.sh


Try to access your forms again.

Best Regards,
Paulo Portugal

Checking Dependency Objects using dba_dependencies or utldtree.sql

If I need to find out recursively dependent objects from an object I usually execute one of these two methods:

########################################################
1-Use the dba_dependencies view
col owner for a15
col name for a30
col type for a10
col referenced_owner for a15
col referenced_name for a30
col referenced_link_name for a10

select
*
from
dba_dependencies
where
name='TAB_EMP' and owner='PKG';

OWNER NAME TYPE REFERENCED_OWNE REFERENCED_NAME REFERENCED_TYPE REFERENCED DEPENDENCY_TYPE
--------------- ------------------------------ ---------- --------------- ------------------------------ ------------------ ---------- ---------------
PKG TAB_EMP TABLE SYS STANDARD PACKAGE HARD
PKG TAB_EMP TABLE PKG EMP_PERSON_TYP TYPE HARD

2-Use the utldtree.sql package. This package creates a procedure named deptree_fill and can be used as follows:

--run utldtree to create the procedure and view (user must be SYS in order to see all dependencies)
SQL>@?/rdbms/admin/utldtree.sql
SQL> exec deptree_fill(type =>'TABLE',schema => 'PKG',name => 'EMP_PERSON_OBJ_TABLE');

PL/SQL procedure successfully completed

SQL> select * from deptree order by seq#;

NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------ ---------- ------------------------------ ------------------------------ ----------
0 TABLE PKG EMP_PERSON_OBJ_TABLE 0

SQL> select * from ideptree;

DEPENDENCIES
--------------------------------------------------------------------------------
TABLE PKG.EMP_PERSON_OBJ_TABLE


########################################################


Regards,
Paulo Portugal

Cloning Oracle Home and Changing User and Group Installation Owner

"If you are cloning your Oracle Home and need to change the user and/or group owner execute the following steps:

1-First, tar your source ORACLE_HOME:
tar cvhf OH_Source.tar $ORACLE_HOME

2-Then, move the tar to the new host and untar it.
cd ORACLE_HOME
tax xvf OH_Source.tar

3-Use the command below to change the owner of files. (login as root)

find . -user ora10g -exec chown oraprd {} \; --where ora10g is the old user and oraprd is the new user

4-Change the group owner using the following command: (login as root)
find . -group dba10g -exec dba11g {} \; --where the old group is dba10g and new is dba11g

5-Now, change information on config.o file that controls which user is the installation owner.
5.1- For Linux
cd $ORACLE_HOME/rdbms/lib
cp config.o config.o_backup
vi config.c
--Find and replace the old group name (in our case is dba10g) to the new value (dba11g)
--Old value
#define SS_DBA_GRP "dba10g"
#define SS_OPER_GRP "dba10g"
--New value
#define SS_DBA_GRP "dba11g"
#define SS_OPER_GRP "dba11g"

5.2- For AIX
cd $ORACLE_HOME/rdbms/lib
mv config.o config.o_backup
vi config.c
--Old value
.csect H.12.NO_SYMBOL{RO}, 3
.string "dba10g"
:
.csect H.14.NO_SYMBOL{RO}, 3
.string "dba10g"
--Old value
.csect H.12.NO_SYMBOL{RO}, 3
.string "dba11g"
:
.csect H.14.NO_SYMBOL{RO}, 3
.string "dba11g"

6-Finally, execute the clone.pl script to clone the database or use the runInstaller as follows:
--Using clone.pl script
cd $ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME="/u01/app/oracle/product/11.2.0/db11g" ORACLE_HOME_NAME="OraDB11g"
--Or runInstaller
./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/u01/app/oracle/product/11.2.0/db11g" ORACLE_HOME_NAME="OraDB11g"

7-Run the root.sh script (loged in as root user)
$ORACLE_HOME/root.sh

8-Run ChangePerm script
$ORACLE_HOME/install/changePerm.sh


Your clone is done. Just check environment variables and start database and listener.


"

Start init.cssd reboot server

Hi,

If you are experiencing problems when trying to start CSS and the server goes down, try to find old init.css files from previous installations and delete these files or any reference to then :

--Run localconfig delete

--Delete files
$cd /etc
$rm init.crs init.crsd init.cssd init.evmd

$vi /etc/inittab (delete any reference to css)

--Run localconfig add

--Check for css
crsctl check crs (Just CSS should be online for non-RAC installations)


Regards,
Paulo Portugal

Patch Reporting - Oracle E-Business

There are 4 methods that you can use to generate patch report in Oracle EBS:

-> Using $AD_TOP/patch/115/sql/adphrept.sql.
Example:
$sqlplus apps/apps_password @adphrept.sql 2 ALL ALL ALL ALL ALL \
ALL ALL ALL ALL N N N N N my_patches.txt

->Using patchsets.sh script (download it updated using Oracle Metalink Note 139684.1.
Example:
patchsets.sh connect=apps_user/apps_pwd

-> By querying database using this query below:
set verify off
set pages 999
column patch_name format a20
column patch_type format a10
column creation_date format a20
column last_update_date format a20

undef 1

select
patch_name,
patch_type,
to_char(creation_date, 'yyyy-mm-dd hh24:mi') creation_date,
to_char(last_update_date, 'yyyy-mm-dd hh24:mi') last_update_date
from ad_applied_patches
where patch_name like upper('%&&1%')
union all
select
bug_number patch_name,
'BUG' patch_type,
to_char(creation_date, 'yyyy-mm-dd hh24:mi') creation_date,
to_char(last_update_date, 'yyyy-mm-dd hh24:mi') last_update_date
from ad_bugs
where bug_number like upper('%&1%')
order by patch_name
/



-> And finally, using the OAM (Oracle Application Manager) - Applied Patches session.


I hope that it help you find your applied patches.

Regards,
Paulo Portugal

Oracle EBS 12 "Function not Available to this Responsibility. Change responsibilities or contact your System Administrator."

If you are trying to access a custom forms in Oracle EBS and are receiveing this message below:
"Function not Available to this Responsibility. Change responsibilities or contact your System Administrator."

Fisrt, check if responsabilities, menus and forms were configured right.

In my case, I had to include the CUSTOM_TOP at the end of $ORA_CONFIG_HOME/10.1.2/forms/server/default.env file.

Example:
#############################
.
.
.
CUSTOMXXX_TOP=/ocfs02/appebs/PEBS/apps/apps_st/appl/f2c/12.0.0
#############################

Then, just bounce your forms using commands below:
./adformsctl.sh stop
./adformsctl.sh start

And see if it works. For me it works very well.

I hope that it could help you.

Regards,
Paulo Portugal

Golgen Gate error when runing ggsci libclntsh.so.10.1: cannot restore segment prot after reloc: Permission denied

If you face this error below:

./ggsci: error while loading shared libraries: /ora01/F2C/F2CDEV1/db/tech_st/10.2.0/lib/libclntsh.so.10.1: cannot restore segment prot after reloc: Permission denied


Execute the following steps as root user:
getenforce
setenforce 0
getenforce

And try again.

You will need to change it permanently in your GRUB boot adding this
kernel /vmlinuz-2.6.18-8.EL ro root=/dev/VolGroup00/LogVol00 rhgb quiet enforcing=0

Some GoldenGate Tips

Here is a review of some commands used to install and administer Oracle Golden Gate on Unix.

##############################################################
--GG Installations

--GG Command line
./ggsci

--Create directories
CREATE SUBDIRS

--Add Manager default port
EDIT PARAMS MGR
--insert port value 7809 and save

--Change recyclebin initialization parameters to off
alter system set recyclebin=off scope=spfile;
shutdown immediate
startup

--Login / as sysdba and run the following script
SQL>@marker_setup.sql
SQL>@ddl_setup.sql (Specify schema, INITIALSETUP (for first installation),YES to purge recyclebin)
SQL>@role_setup.sql

--Grant the GG role to Extract users
SQL>GRANT GGS_GGSUSER_ROLE TO GG;

--Enable triggers
SQL>@ddl_enable.sql

--Enable Supplemental Data on Database
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

--Create GG Server user
SQL> create user GGSERVER identified by ggserver123;

SQL> grant dba to GGSERVER;




--Start Manager
GGSCI (dbms.f2c.com.br) 2> START MANAGER


--Stop Manager
GGSCI (dbms.f2c.com.br) 2> STOP MANAGER [!]

--Start all extract groups
START EXTRACT *X*

--Show history of last commands
HISTORY

--Execute a command sequence
OBEY file_name_commands
--Example
ADD EXTRACT myext, TRANLOG, BEGIN now
START EXTRACT myext
ADD REPLICAT myrep, EXTTRAIL /ggs/dirdat/aa
START REPLICAT myrep
INFO EXTRACT myext, DETAIL
INFO REPLICAT myrep, DETAIL

--Create GLOBALS file
EDIT PARAMS ./GLOBALS
--Insert the line below to set GG schema user
GGSCHEMA gg

--Login on Database
DBLOGIN USERID gg, PASSWORD gg
##############################################################

I will post more ASAP!

Best Regards,
Paulo Portugal

yum Erros Cannot find a valid baseurl for repo: update

Hi,

I was creating an Amazon AMI and got the following error when runing yum utility:

Cannot find a valid baseurl for repo: update
Error: Cannot find a valid baseurl for repo: update


If you find this error just rename all files founded in/etc/yum.repos.d/ and execute the yum command again.

I hope that it works for you!

Best Regards,
Paulo Portugal

Errors RMAN-03009 ORA-19809 ORA-19804 When doing a backup

If you are trying to do a backup of your database and are receiving these errors below:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/11/2010 11:27:28
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 5218762752 limit

Check if you are using the db_recovery_dest as the backup destination and if yes, increase the value of db_recovery_file_dest_size initialization parameter to a value that could behave your database size.

Example:
alter system set db_recovery_file_dest_size=15G scope=both;


Best Regards,
Paulo Portugal

How to solve "Concurrent Request error Ora-20100 FND_FILE failed to create .tmp file"

If you are experiencing Ora-20100 when running a concurrent on your Oracle E-Business Suite environment, soexecute these steps below to solve this problem:

1-First, check if the .tmp file exists in the temp directory defined by $APPLPTMP variable. Example:

$echo $APPLPTMP
/usr/tmp

If is there any file with the same name but with a different owner, (this could happens if ou have a lot of EBS envronments at the same server what is not a good idead), remove this temp file and try running the concurrent again.

2- Otherwise, login through a sqlplus session using the apps user and execute command below to test generation of a temp file;
SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'TEST FND_FILE Utility');

If this command shows errors like ORA-20100 and ORA-0652, then, you need to stop the concurrent manager using adcmctl script, kill all FNDLIB that are still runing and start the concurrent manager agai. Steps below:

--Login as apps application owner under OS
cd $INST_TOP (for R12)
cd admin/scripts
adcmctl stop apps/passwod

--Kill FNDLIB
ps -ef | grep FNDLIB

--Login as apps user on database and run the cmclean.sl script
SQL>show user
USER is "apps"
SQL>@cmclean.sql
SQL>commit;

--Start Concurrent manager again
adcmctl start apps/pssword

--Run the concurrent and see if the problem was solved



Best Regards,
Paulo Portugal