Paulo Portugal

Undo segments status is PARTLY AVAILABLE

2012-01-07T10:57:00.000-02:00

Alert log shows:

SMON: about to recover undo segment 309
SMON: mark undo segment 309 as available
SMON: about to recover undo segment 312
SMON: mark undo segment 312 as available
SMON: about to recover undo segment 40

To fix:

Check which segments have status PARTLY AVAIABLE and if you do not have any in-doubt transaction (dba_2pc_pending , v$transaction, etc) them:

alter rollback segment XXXXX online;

Best Regards,
Paulo Portugal

Resseting Oracle VM Manager Password for ADMIN or Any other user

2011-09-23T11:14:00.001-03:00

1-Login to database as SYS user or OVS user

2-Take a backup from OVS_USER table
create table OVS_USER_BKP as select * from OVS_USER;

3-Run the command below:
UPDATE OVS_USER set password='testepasswd' where account_name='ADMIN';
commit;

4-Encrypt the password with this command below:
UPDATE OVS_USER set password=create_encrypt_passwd('testepasswd') where account_name='$USER';
commit;

Best Regards,
Paulo Portugal

Mount ISO on Linux

2011-09-21T14:35:00.000-03:00

1-Create directory to be used
mkdir -p /mnt/disk

2-Mount using command below:
mount -o loop OracleVM-Manager-2.2.0.iso /mnt/disk

Best Regards,
Paulo Portugal

Creating OVS repository - Oracle VM

2011-09-21T14:33:00.002-03:00

1-Create partition using fdisk

2-Make ocfs2
mkfs.ocfs2 /dev/sda4

3-Create repository
./opt/ovs-agent-2.3/utils/repos.py -n /dev/sda4 --options rw,sync

4-List ID for repository
./opt/ovs-agent-2.3/utils/repos.py -l

5-Mark it as a ROOT repos
./opt/ovs-agent-2.3/utils/repos.py -r 12d25384-1eda-4e95-9f91-1b6f304a5587

Best Regards,
Paulo Portugal

adopmnctl.sh httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

2011-08-25T14:26:00.000-03:00

If you got message below:

"adopmnctl.sh httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory"

Apply patch 6078836 (that can be downloaded from Metalink).

Best Regards,
Paulo Portugal

Installing RPM packages for EBS R12

2011-08-25T14:22:00.000-03:00

RPM packages elfutils-libelf-devel-0.125 and elfutils-libelf-devel-static-0.125 are pre-req for installing Oracle EBS R12.

When you first try to install it separately, it shows failed dependencies error.

To fix it you must install both packages together as follows:

##############################
rpm -Uvh elfutils-libelf-devel-0.137-3.el5.i386.rpm elfutils-libelf-devel-static-0.137-3.el5.i386.rpm
warning: elfutils-libelf-devel-0.137-3.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:elfutils-libelf-devel-s########################################### [ 50%]
2:elfutils-libelf-devel ########################################### [100%]
##############################

Best Regards,
Paulo Portugal

How to change WEBLOGIC Console admin password for 11g (10.3)

2011-06-02T11:59:00.001-03:00

1-First set environment variables
export BEAHOME=/u01/app/oracle/product/middleware
export DOMAIN_HOME=$BEAHOME/user_projects/domains/base_domain
export CLASSPATH=$CLASSPATH:$BEAHOME/wlserver_10.3/server/lib/weblogic.jar
cd $DOMAIN_HOME/bin
. ./setDomainEnv.sh

2- Now execute the command below to create the new password
cd ../security
mv DefaultAuthenticatorInit.ldift oldDefaultAuthenticator
java weblogic.security.utils.AdminAccount weblogic new_password . --DON'T FORGET THE "."

3- If it exists remove boot.properties file

cd ../servers/AdminServer
mv data data_old
cd security/
mv boot.properties oldboot.properties

4- Restart Weblogic server

cd $DOMAIN_HOME./startWebLogic.sh

Best Regards,
Paulo Portugal

Display number values in words

2011-05-06T16:53:00.001-03:00

--Create emp table
create table emp
(emp_name varchar2(30),
sal number);

--Insert some data
insert into emp values ('Nome1',1300);
insert into emp values ('Nome2',1400);
insert into emp values ('Nome3',2300);
insert into emp values ('Nome4',5300);
insert into emp values ('Nome5',11300);
commit;

--Query and get number into words
col sal for 99999
col "Extenso" for a70
select sal, (to_char(to_date(sal,'j'), 'jsp')) "Extenso" from emp;

set linesize 140
col parameter for a30
select * from v$nls_valid_values where value like '%BRA%';
alter session set NLS_LANGUAGE='BRAZILIAN PORTUGUESE';
alter session set NLS_TERRITORY='BRAZIL';
export LANG=pt_BR

Regards,
Paulo Portugal

Exadata CellCLI command sintaxe:

2011-05-06T16:18:00.000-03:00

Exadata DCLI utility Examples

2011-05-03T15:14:00.001-03:00

Below some dcli command examples:

--Check date of all your cells
$dcli -g mycells date

--List cell status
$dcli -c xxxxcell01,xxxxcell02,xxxxcell03,xxxxcell04 cellcli -e list cell

--Running a CellCLI script on many cells
$dcli -g mycells -x listphisicaldiskcommand.scl

--Running a script on all db servers logged in as oracle

$dcli -g mydbservers -l oracle -x dbwork.sh

Best Regards,
Paulo Portugal

Creating IORM Plans on Oracle Exadata:

2011-04-14T16:13:00.000-03:00

Create a simple plan:

CellCLI> alter iormplan
dbplan=((name=db1_prod, level=1, allocation=70),
(name=db2_prod, level=1, allocation=30),
(name=db1_dev, level=2, allocation=100),
(name=db1_test, level=3, allocation=70),
(name=other, level=3, allocation=40)),
catplan=''

CellCLI> alter iormplan active

Create a plan based on a Data Guard environment:

CellCLI> alter iormplan dbplan=( -
(name=mydbprod1, level=1, allocation=40, role=primary), -
(name=mydbprod2, level=1, allocation=30, role=primary), -
(name=mydbprod, level=2, allocation=20, role=standby), -
(name=mydbprod2, level=2, allocation=25, role=standby), -
(name=other, level=3, allocation = 50))

Best Regards,
Paulo Portugal

RMAN Delete archivelog backups

2011-04-06T10:44:00.001-03:00

If you want to delete archivelog backups made before sysdate-2 execute the command below:

delete noprompt backup of archivelog all completed before 'sysdate-2';

Your archive backup script would be like that:

########################
run{
allocate channel t1 device type disk;

backup
format '/u01/backup/YOUR_DB_%t_%s_%U'
archivelog all
tag 'BKP_ARCH_YOURDB_2hs_2hs';

delete noprompt backup of archivelog all completed before 'sysdate-2';
}
########################

Best Regards,
Paulo Portugal

How to change Java Heap Size - Weblogic

2011-03-24T15:27:00.000-03:00

If you are trying to deploy and are facing timeout on Weblogic, try to increse Java Heap Size.

Find and edit file setDomainEnv.sh (path example: /ora01/XXX/WEBLOGIC/BEA/user_projects/domains/xxx_domain/bin):

Add lines below:

USER_MEM_ARGS="-Xms1024m -Xmx1024m -XX:PermSize=128m -XX:MaxPermSize=512m"
export USER_MEM_ARGS

Restart Weblogic:
stopWebLogic.sh
startWebLogic.sh

Test again!

Best Regards,
Paulo Portugal

Check whether Oracle Installation is 32bit or 64bit

2011-03-24T15:21:00.000-03:00

Run the PL/SQL Block bellow to check which Oracle is installed:

################################################
set serveroutput on
declare
v_metadata varchar2(1000);
v_plataform varchar2(1000);
begin

select metadata into v_metadata from sys.kopm$;

select substr(lpad(v_metadata,112),109,4) into v_plataform from dual;

if v_plataform = 'B023' then
dbms_output.put_line(a => 'My Oracle installation is ----32bit----!');
else
dbms_output.put_line(a => 'My Oracle installation is ----64bit----!');
end if;

end;
/
################################################

Or just check oracle file in $ORACLE_HOME/bin:
--64 bit example
$ file oracle
oracle: setuid setgid ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

--32bit example:
$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped

Best Regards,
Paulo Portugal

Change hostname | IP address of an APEX Server

2011-03-02T21:58:00.001-03:00

If you need to change IP address or | and hostname of server that hosts Oracle Application Express just run the command below after changing IP and hostname:

begin

dbms_xdb.setlistenerendpoint(
endpoint => dbms_xdb.XDB_ENDPOINT_HTTP
, host => '10.10.10.18'
, port => 8080
, protocol => dbms_xdb.XDB_PROTOCOL_TCP);
end;
/

After that, access your Apex site like http://10.10.10.18:8080/apex/apex_admin

Best Regards,
Paulo Portugal

ERROR: ccr.oracle.com installing Oracle Agent

2011-02-04T16:17:00.000-02:00

If you are installing Oracle Grid Control agent in silent mode and got this error below:

Checking Temp space: must be greater than 150 MB. Actual 52640 MB Passed
Checking swap space: must be greater than 150 MB. Actual 5686 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-02-04_04-04-02PM. Please wait ...ERROR: ccr.oracle.com

Run the installation again adding the "-y" parameter like this:

./agentDownload.linux -b /u01/agent11g -y

Best Regards,
Paulo Portugal

Using Flash Cash on Tables with Exadata

2011-02-01T21:47:00.001-02:00

When creating tables to use Exadata Flash Cash use the sintaxe below:

CREATE TABLE tab1 (col1 number)
PARTITION BY RANGE (col1)
(PARTITION p1 VALUES LESS THAN (1000)
STORAGE (CELL_FLASH_CACHE KEEP),
PARTITION p2 VALUES LESS THAN (2000)
STORAGE (CELL_FLASH_CACHE NONE),
PARTITION p3 VALUES LESS THAN (3000)
STORAGE (CELL_FLASH_CACHE DEFAULT));

You can also use ALTER TABLE or ALTER INDEX as follows:

ALTER INDEX idx1 STORAGE (CELL_FLASH_CACHE KEEP);

Best Regards,
Paulo Portugal

Table compression in 11gR2 Databases and Exadata

2011-02-01T19:22:00.001-02:00

If you need to create a compress table for an OLTP system use the syntax below:

create table TAB_1(
col1 number,
col2 varchar2(30))
COMPRESS FOR OLTP;

If you need to create a compress table for Direct Load use the command below:

create table TAB_2 (
col1 number,
col2 varchar2(30))
COMPRESS BASIC;

Now you want to compress for Exadata Machine. For warehouse compression use:

CREATE TABLE tab_1 (
col1 number,
col2 varchar2(30))
COMPRESS FOR QUERY [ LOW | HIGH ] ;

For Online Archival Compression use:

CREATE TABLE tab_1 (
col1 number,
col2 varchar2(30))
COMPRESS FOR ARCHIVE [ LOW | HIGH ] ;

Best Regards,
Paulo Portugal

Exadata Smart Scan - CELL_OFFLOAD_PLAN_DISPLAY parameter

2011-02-01T18:45:00.001-02:00

If you want to control whether the offload status is show in execution plan set the parameter as below:

ALTER SYSTEM OR SESSION SET CELL_OFFLOAD_PLAN_DISPLAY=ALWAYS;

The value options for this parameter are:
AUTO -> display the predicate if cell is present and table is on the cell
ALWAYS -> always show
NEVER -> never show offload status

Some views that can be used to gather cell statistics information:

V$SQLAREA
V$SQLSTATS
V$SQLAREA_PLAN_HASH
V$SQLSTATS_PLAN_HASH

on columns like :

IO_CELL_OFFLOAD_ELIGIBLE_BYTES
IO_CELL_OFFLOAD_RETURNED_BYTES

Best Regards,
Paulo Portugal

Configuring a parallel backup in RMAN with RAC

2011-01-30T20:23:00.001-02:00

In order to make the most use of your RAC environment you can parallel your backups simply by changing the following parameters:

RMAN> configure default device type to sbt;
RMAN> configure device type sbt parallelism 3;
RMAN> configure channel device type sbt connect='sys/passwd@MYDB';

Best Regards,
Paulo Portugal

Create an ASM instance in Silent mode using DBCA

2011-01-30T20:06:00.001-02:00

Use the command below to create an ASM instance using dbca in Silent mode.

dbca -silent -nodelist node1,node2 -configureASM -asmSysPassword manager -diskString /dev/sd* -diskList /dev/sda2,/dev/sdb2 -diskGroupName DG_DATA -redundancy EXTERNAL

Best Regards,
Paulo Portugal

How to find where is my OCR and Voting Disk devices

2011-01-27T14:27:00.001-02:00

--For OCR get the value in ocr.loc file
[oracle@host1]/etc/oracle> cat /etc/oracle/ocr.loc
ocrconfig_loc=/dev/rlv_OCR1
ocrmirrorconfig_loc=/dev/rlv_OCR2
local_only=FALSE

OR use ocrcheck:

[oracle@host1]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048100
Used space (kbytes) : 3848
Available space (kbytes) : 1044252
ID : 865433463
Device/File Name : /dev/raw/raw4
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

--For voting use the command below:

[oracle@host1]/etc/oracle> crsctl query css votedisk
0. 0 /dev/rlv_VOTE1
1. 0 /dev/rlv_VOTE2
2. 0 /dev/rlv_VOTE3

Best Regards,
Paulo Portugal

How to Convert Single Instance to RAC Using rconfig - Summary

2011-01-26T12:48:00.001-02:00

1-Edit ConvertToRAC.xml file that can be found at $ORACLE_HOME/assistants/rconfig/sampleXMLs directory.
2-Run the command below:
rconfig single_to_rac.xml

3-We can chose to only verify if the convertion will be OK using the option: Convert verify="ONLY".

4-To not check pre-req user Convert Verify="NO" otherwise use Convert verify="YES"

Best Regards,
Paulo Portugal

How to change ADMIN Oracle Apex Password

2011-01-25T17:36:00.001-02:00

In order to change ADMIN password for your APEX run the command below:

SQL> conn / as sysdba
SQL> @apxxepwd.sql
Session altered.

...changing password for ADMIN
Enter value for 1: manager
Enter value for 1: manager

PL/SQL procedure successfully completed.

Commit complete.

Best Regards,
Paulo Portugal

SSH with password in background NOHUP

2011-01-16T21:42:00.000-02:00

Create a shell script to copy big files and run in background informing password inside scripts like that:

[hostname]$ cat cp_datafiles.sh
echo 'password_user_f2c' | scp f2c@10.1.0.7:/ocfs03/oradata/PEBS/data/* .

Best Regards,
Paulo Portugal

Reinstall SSH Linux - Error "Value too large for defined data type"

2011-01-07T22:18:00.001-02:00

If getting error like below:

[appprdts@f2c04 Stage]$ ls -la
ls: bkp_appl_Prod110105_1909.cpio: Value too large for defined data type

--Remove SSH
yum remove openssh-server

--Install
yum install openssh-server

Best Regards,
Pauo Portugal

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

2010-11-11T19:39:00.000-02:00

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

2010-11-06T16:42:00.000-02:00

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

2010-11-05T16:39:00.000-02:00

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

2010-10-08T11:11:00.000-03:00

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

2010-09-28T13:11:00.001-03:00

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

2010-09-28T12:10:00.001-03:00

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

2010-09-17T11:38:00.000-03:00

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

2010-09-12T11:31:00.000-03:00

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"

2010-09-07T15:21:00.000-03:00

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

2010-09-04T13:04:00.000-03:00

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

2010-09-04T12:30:00.000-03:00

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

2010-08-31T18:55:00.000-03:00

--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

2010-08-25T18:27:00.000-03:00

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

2010-08-19T18:34:00.000-03:00

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."

2010-07-14T22:02:00.000-03:00

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

2010-05-10T20:54:00.001-03:00

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

2010-05-10T20:52:00.000-03:00

--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.

2010-05-10T17:16:00.000-03:00

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

2010-05-09T12:50:00.003-03:00

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

2010-05-07T16:41:00.000-03:00

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

2010-05-06T16:58:00.000-03:00

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

2010-04-30T16:50:00.001-03:00

--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

2010-04-29T10:24:00.000-03:00

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

2010-04-28T14:54:00.000-03:00

"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

2010-04-23T20:28:00.000-03:00

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

2010-04-23T15:24:00.000-03:00

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."

2010-04-23T13:09:00.000-03:00

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

2010-03-04T16:11:00.000-03:00

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

2010-03-03T10:10:00.000-03:00

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

2010-02-09T22:22:00.000-02:00

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

2010-01-29T15:12:00.000-02:00

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"

2010-01-22T13:46:00.000-02:00

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

Creating XA Oracle Services

2009-11-14T11:05:00.000-02:00

--Create the service with this command
srvctl add service -d -s -r -a

--Start the service
srvctl start service -d -s

--Configure the service to use DTP that will guarantee that all global transaction will stay on the same instance
begin
DBMS_SERVICE.MODIFY_SERVICE(
service_name=>'',
dtp => TRUE
);
end;
/

--Service_name to insert into your tnsnames.ora client file
SERV_XA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP_MACHINE_DB_SERVER)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
)
)

--If you reach ORA-12520 error and are using RAC Database then execute the following commands on one instance connected as SYSDBA

1-
sqlplus / as sysdba
SQL> alter system set local_listener='LISTENER_MIDDLE_BWMDPR1' scope=both sid='BWMDPR1'; --Use the same value that is already in use

System altered.

SQL> alter system set local_listener='LISTENER_MIDDLE_BWMDPR2' scope=both sid='BWMDPR2'; --Use the same value that is already in use

System altered.

SQL>alter system register;

System altered.

2-Stop and Start listener (reload didn't help for me!)

To move datafile from one disk group to another you need to follow these steps

2009-11-11T21:48:00.001-02:00

To move datafile from one disk group to another you need to follow these steps:

1-Identify datafiles that will be moved:
SQL>select
file_name
from
dba_data_files;

2-Identify the disk group destination
--Connect to ASM instance
SQL> select
name
from
v$asm_diskgroup;

3-Take the datafile OFFLINE
SQL>alter database datafile 'file_name' offline;

4-Copy the file using either dbms_file_transfer or RMAN:
--DBMS_FILE_TRANSFER
begin
dbms_file_transfer.copy_file(
source_directory_object => 'DIR_ORIG', --This is an object directory created pointing to original datafile location
source_file_name => 'Data_File_Name.dbf',
destination_directory_object => 'DIR_DEST', --This is an object directory pointing to destination of datafile
destination_file_name => 'Data_File_Name_New.dbf');
end;

--RMAN
RMAN>copy datafile '+DG_ORIG/dbname/datafiles/Data_File_Name.dbf' TO '+DB_DEST';

5-Rename the datafile
SQL alter database rename file '+DG_ORIG/dbname/datafiles/Data_File_Name.dbf' to '+DG_DEST/db_name/datafiles/Data_File_Name_New.dbf'

6-Recover the datafile
SQL>recover datafile '+DG_DEST/db_name/datafiles/Data_File_Name_New.dbf';

7-Put the datafile ONLINE
SQL>alter database datafile '+DG_DEST/db_name/datafiles/Data_File_Name_New.dbf' online;

8-Delete the old datafile using ASMCMD
ASMCMD> cd +DG_ORIG/dbname/datafiles/
ASMCMD> rm -rf Data_File_Name.dbf

9-Backup your tablespace or your database

Best Regards,
Paulo Portugal

Using DBMS_FILE_TRANSFER or RMAN to move datafiles from one Disk Group to another

2009-11-11T20:46:00.001-02:00

Session Marked for Killed Forever

2009-11-10T17:06:00.000-02:00

Find the OS process and kill it using kill -9 . Be carefull because some of then are backgorund process or parallel process for example so, use "ps -ef | grep ospid" before killing it. Example:

--Query to find the OSPID of session killed (for RAC run this query on each instance instead of using gv$ tables)

SQL>SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1
FROM v$session
WHERE paddr = addr);

[hostname-SID1]/home/oracle> ps -ef | grep 2744432
oraebs 2601182 3592334 0 16:43:23 pts/7 0:00 grep 2744432
oraebs 2744432 1 0 16:59:17 - 0:00 oracleSID3 (LOCAL=NO)

This one you can kill but not this one below (This is a parallel process):

[hostname-SID3]/home/oracle> ps -ef | grep 5136530
oraebs 2601176 3592334 0 16:43:23 pts/7 0:00 grep 5136530
oraebs 5136530 1 0 Nov 07 - 7:44 ora_pz99_SID3

Best Regards,
Paulo Portugal

Convert Single Instance to RAC Database 10g

2009-11-10T16:01:00.001-02:00

--Create bdumo,cdump and adump directories on both nodes
mkdir 1
mkdir -p /dbdbname/admin/1/bdump
mkdir -p /dbdbname/admin/1/cdump
mkdir -p /dbdbname/admin/1/udump
mkdir -p /dbdbname/admin/1/adump

mkdir 2
mkdir -p /dbdbname/admin/2/bdump
mkdir -p /dbdbname/admin/2/cdump
mkdir -p /dbdbname/admin/2/udump
mkdir -p /dbdbname/admin/2/adump

-On First Node:
--Install Oracle Clusterware (If you don't have it already installed)
--Create the listener on both nodes
--Backup the database and restore it on a shared location like ASM
--Add the following parameters to your pfile
*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
1.undo_tablespace=undotbs (undo tablespace which already exists)
1.instance_name=1
1.instance_number=1
1.thread=1
1.local_listener=LISTENER_DB_hostname01
2.instance_name=2
2.instance_number=2
2.local_listener=LISTENER_DB_hostname02
2.thread=2
2.undo_tablespace=UNDOTBS2
2.cluster_database = TRUE

--Copy the $ORACLE_HOME/dbs/init.ora to $ORACLE_HOME/dbs/init.ora on node1 and on node2 as SID2

--After create the listener on both nodes, add them at tnsnames.ora file
LISTENER_BPLS_hostname01 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname01-vip.b2winc.com)(PORT = 1539))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.31)(PORT = 1539))
)

LISTENER_BPLS_hostname02 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname01-vip.b2winc.com)(PORT = 1539))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.31)(PORT = 1539))
)

--Use an pfile to startup nomount and them create the spfile

SQL>startup nomount pfile='temp_pfile.ora'

SQL>create SPFILE='+DG_dbname_FRA//spfile.ora' from pfile='temp_pfile_1.ora';

SQL>shutdown immediate

SQL>startup mount

--Create UNDO tablespaces for both nodes
SQL>create undo tablespace UNDOTBS1 datafile '+DG_dbname_FRA' size 500M;
SQL>create undo tablespace UNDOTBS2 datafile '+DG_dbname_FRA' size 500M;

--Create redo groups for thread 2
SQL>alter database add logfile thread 2 group 4 size 50M;
SQL>alter database add logfile thread 2 group 5 size 50M;
SQL>alter database add logfile thread 2 group 6 size 50M;

--Open the database and enable thread 2
SQL>alter database open;

SQL>alter database enable public thread 2;

--On second node, copy the init file, change the name to dbnameSID2.ora and start the database
export ORACLE_SID=dbname.ora
sqlplus / as sysdba
SQL>startup

--Finally create services
srvctl add database -d -o /dbdbname/products/rdbms -p /dbdbname/products/rdbms/init.ora

srvctl add instance -d -i 1 -n hostname01

srvctl add instance -d -i 2 -n hostname02

=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname01-vip.b2winc.com)(PORT = 1539))
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname02-vip.b2winc.com)(PORT = 1539))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

Best Regards,
Paulo Portugal

Using OS tools to monitor swap space and check packages, patches and 64 bit Capacle

2009-11-10T15:19:00.000-02:00

--AIX
host:root@/> usr/sbin/lsps -a
Page Space Physical Volume Volume Group Size %Used Active Auto Type
paging00 hdisk0 rootvg 6912MB 2 yes yes lv
hd6 hdisk1 rootvg 6912MB 2 yes yes lv
host:root@/>

--Check packages on AIX
lslpp -w | grep -i "Patch_Name" (applies to APARs and PTFs)

--Check patches on AIX
/usr/sbin/instfix -ik patch number

--Check packages on HP-UX
/usr/sbin/swlist -lproduct PH
/usr/contrib/bin/show_patches

--Check patches pn HP-UX
/usr/sbin/swlist -l fileset | grep -i

--Check patches on Solaris
/bin/showrev -p

--Check packages on Solaris
/bin/pkginfo -l | grep -i "Pkg_Name"

--Linux
rpm -qa | grep "package name"
rpm -qa --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" > /tmp/rpm-queryformat.txt
rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" | grep "PKG_Name"

--Show What package have the library specified on Linux
rpm -q --whatprovides --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" "full path/library name"

--Check if OS is 64 bit or not
--AIX
lslpp -L | grep 64bit
--HP
getconf KERNEL_BITS
--Solaris
/bin/isainfo -kv

--sar example
sar -r 5 5

Best Regards,
Paulo Portugal

Close trace that has been removed to free up space on operational system

2009-11-10T14:48:00.000-02:00

--You have killed a session that was generating big trace file and deleted the file but the space wasn't freed

Fisrt, you need to get the spid using the query bellow or use the name of trace file that has this number:

select
p.spid
from
v$process p,
v$session s
where
p.addr=s.paddr
and
s.sid=;

Then, you will use oradebug to close the trace
oradebug setospid
oradebug close_trace

This operation will close the trace file removed and thus freeing up space on your OS.

Best Regards,
Paulo Portugal

TNS-12560 and TNS-00583 when starting Listener

2009-11-10T11:44:00.000-02:00

If you are trying to start the listener and are runing into the message below:

###############################################
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db11g/network/admin/list ener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbms/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbms.f2c.com.br)(P ORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters

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

Then , rename your sqlnet.ora, listener.ora and create another listener using netca or netmgr an try again.

This should solve your problem.

Best Regards,
Paulo Portugal

Scripts to Reorg LOB Segments

2009-11-09T16:37:00.000-02:00

This to script are used to reorg LOB segments.

The first one will change some parameters of segment and will also shrink it so the move command will run faster.

The seccond script is the MOVE command.

Below the examples:
###############################
#####Script Pre-Reorg##########
###############################

#!/bin/ksh
sqlplus /nolog < ###"connect system/passwd@SID
show user
spool reorg_otm_18102009_30.log
select sysdate from dual;

set time on
set timing on
SET LINESIZE 150
SET PAGESIZE 100
SET HEAD OFF
SET ECHO OFF
SET TIME ON;
SET TIMING ON;

alter table GLOGOWNER.I_TRANSMISSION modify lob (XML_BLOB) (freepools 1);
alter table GLOGOWNER.I_TRANSMISSION modify lob (XML_BLOB) (shrink space cascade);
alter table GLOGOWNER.I_TRANSMISSION modify lob (XML_BLOB) (pctversion 0);
alter table GLOGOWNER.I_TRANSMISSION modify lob (XML_BLOB) (retention);

spool off
exit;
###EOF - Remove '###'

###############################
#####Script Reorg##############
###############################
#!/bin/ksh
sqlplus /nolog <###connect system/passwd@SID
show user
spool reorg_otm_18102009_32.log
select sysdate from dual;

set time on
set timing on
SET LINESIZE 150
SET PAGESIZE 100
SET HEAD OFF
SET ECHO OFF
SET TIME ON;
SET TIMING ON;

ALTER TABLE GLOGOWNER.I_TRANSMISSION move partition INBOUND2 LOB (XML_BLOB) STORE AS (TABLESPACE LOB4) parallel 8 nologging;

spool off
exit;
###EOF - Remove '###'

Backing up OCR , Voting ,Tar CRS_HOME and Inventory Home Before Applying Patch Set

2009-11-09T15:02:00.000-02:00

Before applying Oracle Patch Set 10.2.0.x on your $CRS_HOME it's advisable to backup your binaries on $CRS_HOME and also your OCR and Voting devices.

Here is a simple example of how to do that:

--Backup CRS_HOME
tar cvhf crs_bkp_06_nov_2009_node1.tar /opt/oracle/products/crs
tar cvhf crs_bkp_06_nov_2009_node2.tar /opt/oracle/products/crs
tar cvhf crs_bkp_06_nov_2009_node3.tar /opt/oracle/products/crs

----Backup inventory
tar cvhf inventory_bkp_06_nov_2009_node1.tar /opt/oracle/oraInventory
tar cvhf inventory_bkp_06_nov_2009_node2.tar /opt/oracle/oraInventory
tar cvhf inventory_bkp_06_nov_2009_node3.tar /opt/oracle/oraInventory

--Backup OCR (Backups are done automatically by Oracle but it's a good idea to make it again)
ocrconfig -showbackup
ocrconfig -export OCR_export_07_nov_2009_node1 -s online
ocrconfig -export OCR_export_07_nov_2009_node2 -s online
ocrconfig -export OCR_export_07_nov_2009_node3 -s online
ocrcheck --checking where they are
--Using dd command:
dd if=/dev/rlv_OCR1 of=bkp_OCR1_node1
dd if=/dev/rlv_OCR1 of=bkp_OCR1_node2
dd if=/dev/rlv_OCR1 of=bkp_OCR1_node3

--Backup voting disks
crsctl query css votedisk --Checking where they are
dd if=/dev/rlv_VOTE1 of=bkp_VOTE1_node1
dd if=/dev/rlv_VOTE1 of=bkp_VOTE1_node2
dd if=/dev/rlv_VOTE1 of=bkp_VOTE1_node3

Now you can Upgrade your CRS_HOME using Rolling Upgrade Option or NoRolling (all of then at the same time).

It's important to remember that when you are upgrading CRS_HOME, all nodes will be marked by default, you can install binaries with all services online (instances, listeners, services and CRS deamons) and just need to shutdown services at the end of installation before runing the root102.sh script. At that time you can stop one node at a time and this is rolling upgrade.

Make sure that you have read all the Patch Set Note(Installation Note)!!!

Backup OCR and Voting Disks

2009-11-09T14:02:00.001-02:00

Befeore applying Oracle Set Patch like 10.2.0.4 on your $CRS_HOME you should

Oracle OPatch Utility

2009-11-08T13:26:00.001-02:00

--opatch prereq commands

--Check if ORACLE_HOME is valid
$ opatch prereq CheckOracleHome

--Check if the oraInst.loc file
$ opatch prereq CheckOraInstLocation

--Check Central Inventory
$ opatch prereq CheckCentralInventoryLocation

--Check if OH is registered in Inventory
$ opatch prereq CheckCentralInventoryForOH

--Check Inventory for Read and Write Permission
$opatch prereq CheckCentralInventoryForRWSession

--Check if OH have JDK in it
$opatch prereq CheckJDK

--Check if oui in OH is compatible with OPatch utility
$opatch prereq CheckOUIVersionCompatible

--Check if libraries required are OK
$opatch prereq CheckRequiredLibs

--Check if another session is already locking OH
$opatch prereq CheckIfOHLockedForPatching

--Check System Space required
$opatch prereq CheckSystemSpace

--Check for conflits among patches already installed
$opatch prereq CheckConflictAmongPatchesWithDetail

--Check RAC nodes
$opatch prereq CheckRACNodeList

--Check if commands can be invoked on remote machines
$opatch prereq CheckRemoteCommandInvocable

--Check permissions for copy and remove files on remote machines
$opatch prereq CheckRemoteCopyAndRemove

--Check if Central Inventory has a CRS Home registered
$opatch prereq CheckForCRSHomeIfRAC

--opatch util commands

--Apply a set of patches
$opatch napply

ORA-28368: cannot auto-create wallet

2009-11-05T17:52:00.000-02:00

If you got this error simple create a directory named "wallet" on your $ORACLE_BASE/admin/$ORACLE_SID.

SQL> alter system set encryption key identified by manager;
alter system set encryption key identified by manager
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet

[ora11g@dbms admin]$ mkdir -p $ORACLE_BASE/admin/$ORALE_SID wallet

[ora11g@dbms dbms]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 5 13:30:43 2009

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter system set encryption key identified by manager;

System altered.

SQL> exit

Oracle Services - SRVCTL Command

2009-11-05T16:24:00.000-02:00

################################################
############SRVCTL Command Examples#############
################################################
--Stopping an instance
srvctl stop instance -d capdb14p -i capdb14p1

--Database status
srvctl status database -d BWEBTST

--Creating Services
srvctl add service -d database_unique_name -s service_name -r preferred_list[-a
available_list] [-P TAF_policy]

--Stopping and Starting databases
srvctl start service -d database_unique_name [-s service_name_list] [-i inst_name]
[-o start_options] [-c connect_str | -q]

srvctl stop service -d database_unique_name -s service_name_list [-i inst_name]
[-o start_options] [-c connect_str | -q]

--Enable/Disable Services
srvctl enable service -d database_unique_name -s service_name_list [-i inst_name]
srvctl disable service -d database_unique_name -s service_name_list [-i inst_name]

--Relocating Services
srvctl relocate service -d apps -s crm -i apps1 -t apps3

--Services Status
srvctl status service -d BWWMSPR -s crm

--Checking Services
srvctl config service -d BWWMSPR -s crm -a

--Check services status deep info
srvctl status service -d BWSLPR -s SERV1,SERV2,SERV3,SERVxxx -v -S 9 -f

Connecting to TimesTen Instance

2009-11-04T22:22:00.000-02:00

[oratt@dbms bin]$ ./ttisql

Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command> connect "dsn=TT_1121"; --At this time the database is created and loaded into memory
Connection successful:
Command>

Installing Oracle TimesTen on Linux

2009-11-04T18:59:00.000-02:00

--TimesTen information
###############################################
##############Installing#######################
###############################################
--Default instance name is tt1121_32 and tt1121_64 for 32 and 64bit
--On UNIX the default port is 53384 for 32bit 53388 for 64bit
--Create a group named timesten and put the user on this group
--Create the directory and group
mkdir /etc/TimesTen
groupadd ttadmin
useradd -g ttadmin -oinstall oratt
--Give the right privileges on directories
# chgrp -R ttadmin /etc/TimesTen
# chmod 770 /etc/TimesTen/
# chmod 660 /etc/TimesTen/*
--Install TimesTen using record option to use with batch option in future (optional)
./setup.sh -record setup_rec_04112009.rec
--Start TimesTen Instance on rebbot:
# cd install_dir/bin
# setuproot -install
--Execute the following command:
setprivgrp ttadmin MLOCK
--Check the privilege of the group
getprivgrp timesten

--Output
[oratt@dbms linux86]$ ./setup.sh -record setup_rec_04112009.rec

NOTE: Each TimesTen installation is identified by a unique instance name.
The instance name must be a non-null alphanumeric string, not longer
than 255 characters.

Please choose an instance name for this installation? [ tt1121 ] oratt
Instance name will be 'oratt'.
Is this correct? [ yes ]

Of the three components:

[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only

Which would you like to install? [ 1 ]
Where would you like to install the oratt instance of TimesTen? [ /home/oratt ] /u01/app/oracle/product/timesten
Where would you like to create the daemon home directory? [ /u01/app/oracle/product/timesten/TimesTen/oratt/info ]
Installing into /u01/app/oracle/product/timesten/TimesTen/oratt ...
Uncompressing ...

NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
daemon port number must be the same across all TimesTen installations
managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
port number that is set at installation time. The daemon port number can
be verified by running 'ttVersion'.

The default port number is 53384.

Do you want to use the default port number for the TimesTen daemon? [ yes ]
The daemon will run on the default port number (53384).

NOTE: For security, we recommend that you restrict access to the
TimesTen installation to members of a single OS group. Only members of
that OS group will be allowed to perform direct mode connections to
TimesTen, and only members of that OS group will be allowed to perform
operations that access TimesTen data stores, TimesTen files and shared
memory. The OS group defaults to the primary group of the instance
administrator. You can default to this group, choose another OS group
or you can make this instance world-accessible. If you choose to make
this instance world-accessible, all database files and shared memory
are readable and writable by all users.

Restrict access to the the TimesTen installation to the group 'ttadmin'? [ yes ]

NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.

Would you like to enable PL/SQL for this instance? [ yes ]

The daemon logs will be located in /u01/app/oracle/product/timesten/TimesTen/oratt/info
Would you like to specify a different location for the daemon logs? [ no ]

In order to use the 'In-Memory Database Cache' feature in any databases
created within this installation, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using /bin/ttModInstall.

Please enter a value for TNS_ADMIN (s=skip)? [ ]
Please enter a value for TNS_ADMIN (s=skip)? [ ]
Please enter a value for TNS_ADMIN (s=skip)? [ ] skip
skip is not a readable directory, use it anyways ? [ no ]
Please enter a value for TNS_ADMIN (s=skip)? [ ] /u01/app/oracle/product/timesten/TimesTen/

TNS_ADMIN will be set to /u01/app/oracle/product/timesten/TimesTen
You can change TNS_ADMIN later by running /bin/ttmodinstall.

Installing server components ...
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53385 ]
Do you want to install QuickStart and the TimesTen Documentation? [ no ] Yes
Where would you like to install the quickstart and doc directories (s=skip)? [ /u01/app/oracle/product/timesten/TimesTen/oratt ] /u01/app/oracle/product/timesten/TimesTen/oratt/docs
The directory /u01/app/oracle/product/timesten/TimesTen/oratt/docs does not exist.
Do you want to create it? [ yes ]

The TimesTen Quickstart applications can take up to 64 Mbytes of disk space.
Depending on how your system is configured, you may not want to create the
QuickStart DemoDataStore directory in the default location,
/u01/app/oracle/product/timesten/TimesTen/oratt/info/DemoDataStore

Where would you like to create the DemoDataStore directory? [ /u01/app/oracle/product/timesten/TimesTen/oratt/info ] /u01/app/oracle/product/timesten/TimesTen/oratt/info/demo
The directory /u01/app/oracle/product/timesten/TimesTen/oratt/info/demo does not exist.
Do you want to create it? [ yes ]
Creating /u01/app/oracle/product/timesten/TimesTen/oratt/info/demo/DemoDataStore ...

Installing client components ...

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ]

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the 'setuproot' script :
cd /u01/app/oracle/product/timesten/TimesTen/oratt/bin
./setuproot -install
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
'/u01/app/oracle/product/timesten/TimesTen/oratt/startup/tt_oratt'.

The Quickstart home page can be accessed here :
'/u01/app/oracle/product/timesten/TimesTen/oratt/quickstart/index.html'

The 11.2.1.3 Release Notes are located here :
'/u01/app/oracle/product/timesten/TimesTen/oratt/README.html'

Starting the daemon ...
TimesTen Daemon startup OK.
End of TimesTen installation.
[oratt@dbms linux86]$ cd /u01/app/oracle/product/timesten/TimesTen/oratt/bin
[oratt@dbms bin]$ ./setuproot -install
Sorry, you must be root to install the TimesTen daemon start script ...

[root@dbms timesten]# cd /u01/app/oracle/product/timesten/TimesTen/oratt/bin
[root@dbms bin]# ./setuproot -install
Would you like to install the TimesTen daemon startup scripts into /etc/init.d? [ yes ]
Copying /u01/app/oracle/product/timesten/TimesTen/oratt/startup/tt_oratt to /etc/init.d

Successfully installed the following scripts :
/etc/init.d/tt_oratt
/etc/rc.d/rc0.d/K45tt_oratt
/etc/rc.d/rc1.d/K45tt_oratt
/etc/rc.d/rc2.d/S90tt_oratt
/etc/rc.d/rc3.d/S90tt_oratt
/etc/rc.d/rc5.d/S90tt_oratt
/etc/rc.d/rc6.d/K45tt_oratt

###############################################
###########Start/Stop##########################
###############################################
[oratt@dbms startup]$ pwd
/u01/app/oracle/product/timesten/TimesTen/oratt/startup
[oratt@dbms startup]$ ls
tt_oratt
[oratt@dbms startup]$ ./tt_oratt stop
Stopping TimesTen Daemon : [ OK ]
[oratt@dbms startup]$ ./tt_oratt start
Starting TimesTen Daemon : [ OK ]
[oratt@dbms startup]$

Database Resource Manager on 11gR2

2009-10-28T22:47:00.000-02:00

Creating a plan that kill session if this session exceeds the CPU limit specified:

begin

dbms_resource_manager.create_plan_directive(plan => 'HIGH',
switch_estimate => TRUE,
group_or_subplan => 'oltp_pl',
mgmt_p1 => 85,
switch_group => 'kill_session',
switch_time => 50);
end;

When the session exceeds 50 seconds of CPU usage time, the session is killed.

Calibrating I/O on 11g

2009-10-28T19:17:00.000-02:00

You can use PL/SQL to calibrate I/O as you can use Database Control. A simple example of how to use this tool using PL/SQL is below:

declare

v_max_mbps binary_integer;
v_actual_latency binary_integer;
v_max_iops binary_integer;
begin
dbms_resource_manager.calibrate_io(
num_physical_disks => 10,
max_latency => 15,
max_iops => v_max_iops,
max_mbps => v_max_mbps,
actual_latency => v_actual_latency);
end;
/

where:
num_disks: number of disks on your storage (approximately)
max_latency: maximum value for a tolerable latency in miliseconds
max_iops: maximu number of random db-block size read request
actual_latency: average latency

--Now query the view v$io_calibration_status to check the status of job
--Finally check dba_rsrc_io_calibrate to check the results

Installing Oracle 11gR2 on RH5 UP3

2009-10-27T17:53:00.000-02:00

When I was installing Oracle Database 11gR2 on RH5 UP3 I didn't found all elf*.rpm files. As it is a database that I'm using for training purpose, I have just ignored these files and everything os working fine until now :).

!!!DON'T DO THAT IF YOU ARE INSTALLING FOR A PRODUCTION ENVIRONMENT!!!

Creating Single AWR Baseline Template

2009-10-27T17:48:00.000-02:00

We cam now , in 11gR2 schedule baseline template to run in a future time just using the package dbms_workload like bellow:

begin

dbms_workload_repository.create_baseline_template (
start_time => to_date('2009/10/28 22:00:00','yyyy/mm/dd hh24:mi:ss'),
end_time => to_date('2009/10/29 08:00:00','yyyy/mm/dd hh24:mi:ss'),
baseline_name => 'baseline1',
template_name => 'base_temp_1',
expiration => 30);
end;
/

Then, you can check this object using view wrm$_baseline_template;

In this example the AWR will create a baseline captured between 2009/10/28 22:00 and 2009/10/29 08:00.

Installing Win XP Professional - Missing xml files

2009-10-14T12:59:00.000-03:00

If you find problems when installing your win XP Professional because of missing .xml files, try to format your partition and install again. It works for me!

Ensure that you have backed up everything before that :)

LOB Segments and enq: HW Contention

2009-10-13T22:30:00.001-03:00

If you have LOB segments and are experiencing system wait event 'enq: HW Contention' then you should use this workaround:

1-Check for HW contention:
select
*
from
gv$session_wait
where
event like '%contention%';

2-Get the block and file id
select
dbms_utility.data_block_address_file(id2) file#,
dbms_utility.data_block_address_block(id2) block#
from
gv$lock
where
type='HW'
and
inst_id= (&use the instance number where you found the HW contention);

3-Find the object
select
owner,
segment_type,
segment_name
from
dba_extents
where
file_id=&USE_THE_FILE#
and
&USE_BLOCK_NUMBER between block_id
and
block_id + blocks - 1;

4-Check the extent size and add one extent:
alter table owner.table_name
modify lob (lob_name) (allocate extent (size 131072k));

5-Your HW Contention have gone. I hope so:)

Pending Transactions

2009-10-13T17:04:00.000-03:00

Use the following queries to get pending transactions and after commit or rollback, purge then if it's necessary:

--Identifying transactions
select
'commit force '''local_tran_id'''; '
from
dba_2pc_pending;

--Purging
select
'exec dbms_transaction.purge_lost_db_entry('''local_tran_id'''); '
from
dba_2pc_pending
where
state='forced commit';