Oracle Applications and Fusion DBA concepts: 2013

Thursday, December 12, 2013

Scripts to find USED,FREE and total DataBase sizes

--> Script to find Total DB size:

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes)/1024/1024/1024,0) temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c;

--> Script to find Total and Free DB sizes:

col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024 /1024) || ' GB' "Database Size"
, round(free.p / 1024 / 1024 /1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/

--> Script to find USED DB size:

SELECT round(SUM(bytes)/1024/1024/1024) "GB" FROM dba_segments;

--> Script to report true free space within the used portion of the TEMPFILE:

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

RC:crs services are not coming up after removal OS user - CRS-0184: Cannot communicate with the CRS daemon after reboot

Steps performed in CRS startup issue and its resolution after OS user (ORATEST) removal:
--> Error message: the message lists the serverpool, listener and data type entries for user “ORATEST”
[SYSTEM.CRSD.TYPES.ora!database!type.TYPE_ACL.CONFIG]
ORATEXT : DEFAULT=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~FLAGS=_CONFIG~ID=1078~TYPE=_STRING~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost1!lsnr]
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost1!lsnr.CONFIG]
ORATEXT : ACL=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=/d06/ORATEST/product/11.2.0/bin/racgwrap%CRS_SCRIPT_SUFFIX%~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%~ALIAS_NAME=ora.%CRS_CSS_NODENAME_LOWER_CASE%.LISTENER_ORATEST_myhost1_%CRS_CSS_NODENAME_UPPER_CASE%.lsnr~AUTO_START=never~BASE_TYPE=ora.local_resource.type~CHECK_INTERVAL=60~CHECK_TIMEOUT=30~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=listener) PROPERTY(LISTENER_NAME=PARSE(%NAME%, ., 2))~DEGREE=1~DESCRIPTION=Oracle Listener resource~ENABLED=1~ENDPOINTS=TCP:1521~LOAD=1~LOGGING_LEVEL=1~NAME=ora.LISTENER_ORATEST_myhost1.lsnr~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/d06/ORATEST/product/11.2.0~PORT=1521~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=5~SCRIPT_TIMEOUT=60~START_DEPENDENCIES=hard(type:ora.cluster_vip_net1.type) pullup(type:ora.cluster_vip_net1.type)~START_TIMEOUT=180~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=0~TYPE=ora.listener.type~TYPE_ACL=owner:crstest:rwx,pgrp:oinstall:rwx,other::r--~TYPE_VERSION=1.2~UPTIME_THRESHOLD=1d~USR_ORA_ENV=~USR_ORA_OPI=false~VERSION=11.2.0.2.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

..
[SYSTEM.CRSD.RESOURCES.ora!LISTENER_ORATEST_myhost2!lsnr.CONFIG]
ORATEXT : ACL=owner:ORATEST:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=/d06/ORATEST/product/11.2.0/bin/racgwrap%CRS_SCRIPT_SUFFIX%~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%~ALIAS_NAME=ora.%CRS_CSS_NODENAME_LOWER_CASE%.LISTENER_ORATEST_myhost2_%CRS_CSS_NODENAME_UPPER_CASE%.lsnr~AUTO_START=restore~BASE_TYPE=ora.local_resource.type~CHECK_INTERVAL=60~CHECK_TIMEOUT=30~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=listener) PROPERTY(LISTENER_NAME=PARSE(%NAME%, ., 2))~DEGREE=1~DESCRIPTION=Oracle Listener resource~ENABLED=1~ENDPOINTS=TCP:1521~LOAD=1~LOGGING_LEVEL=1~NAME=ora.LISTENER_ORATEST_myhost2.lsnr~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/d06/ORATEST/product/11.2.0~PORT=1521~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=5~SCRIPT_TIMEOUT=60~START_DEPENDENCIES=hard(type:ora.cluster_vip_net1.type) pullup(type:ora.cluster_vip_net1.type)~START_TIMEOUT=180~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=0~TYPE=ora.listener.type~TYPE_ACL=owner:crstest:rwx,pgrp:oinstall:rwx,other::r--~TYPE_VERSION=1.2~UPTIME_THRESHOLD=1d~USR_ORA_ENV=~USR_ORA_OPI=false~VERSION=11.2.0.2.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

--> The commands to stop and delete the server pool associated with ORATEST.

[root@myhost1 ~]# cd $GRID_HOME/bin
./crsctl stop resource ora.ORATEST.db
./crsctl delete resource ora.ORATEST.db
./crsctl delete serverpool ora.ORATEST
./crsctl getperm serverpool ora.ORATEST
./crsctl status serverpool

--> Commands to remove the ORATEST listeners
[root@myhost1 bin]# ./srvctl remove listener -l LISTENER_ORATEST_myhost1
[root@myhost1 bin]# ./srvctl remove listener -l LISTENER_ORATEST_myhost2
[root@myhost1 bin]# ./srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): myhost1,myhost2

--> Commands executed during action to delete data type

1. Check database type
# ./crsctl status type |grep database
TYPE_NAME=ora.database.type

2. Check/Remove database
cd $ORACLE_HOME/bin
$srvctl config database -d ORATEST
$srvctl remove database -d db_name

3. Delete database type resource
cd $GRID_HOME/bin
# ./crsctl delete type ora.database.type
# ./crsctl status type |grep database
/d01/crstest/product/11.2.0/bin/crsctl status type ora.database.type -p | grep DEFAULT_VALUE=owner
4. Get ocrdump With ROOT user
cd $GRID_HOME/bin
./ocrdump /tmp/ocrdump_after.log
# cat /tmp/ocrdump_after.log|grep -i ORATEST
5. Add database
Syntax --
$ ./srvctl add database -d <db_name> -p <Spfile> -o<Oracle_home>
$ ./srvctl add instance -d <db_name> -i <instance_name> -n <node>

Commands --
./srvctl add database -d VAL -o /d01/oraval/product/10.2.0
./srvctl add instance -d VAL -i VAL1 -n myhost1
./srvctl add instance -d VAL -i VAL2 -n myhost2

6. Get ocrdump.
./ocrdump /tmp/ocrdump_withdb.log
7. Stop/Start CRS (As OS user already deleted)
[root@myhost2 bin]# id ORATEST
id: ORATEST: No such user
#crsctl stop crs
#crsctl start crs
8. Stop crs/reboot node
#crsctl stop crs
9. Reboot nodes.
10. Check CRS services:
[root@myhost1 bin]# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE myhost1
ONLINE ONLINE myhost2
ora.asm
OFFLINE OFFLINE myhost1
OFFLINE OFFLINE myhost2
ora.gsd
OFFLINE OFFLINE myhost1
OFFLINE OFFLINE myhost2
ora.net1.network
ONLINE ONLINE myhost1
ONLINE ONLINE myhost2
ora.ons
ONLINE ONLINE myhost1
ONLINE ONLINE myhost2
ora.registry.acfs
OFFLINE OFFLINE myhost1
OFFLINE OFFLINE myhost2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE myhost2
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE myhost1
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE myhost1
ora.VAL.VAL1.inst
1 ONLINE ONLINE myhost1
ora.VAL.VAL2.inst
1 ONLINE ONLINE myhost2
ora.VAL.db
1 ONLINE ONLINE myhost1
ora.cvu
1 ONLINE ONLINE myhost1
ora.oc4j
1 ONLINE ONLINE myhost1
ora.myhost1.LISTENER_VAL_myhost1.lsnr
1 ONLINE ONLINE myhost1
ora.myhost1.vip
1 ONLINE ONLINE myhost1
ora.myhost2.LISTENER_VAL_myhost2.lsnr
1 ONLINE ONLINE myhost2
ora.myhost2.vip
1 ONLINE ONLINE myhost2
ora.scan1.vip
1 ONLINE ONLINE myhost2
ora.scan2.vip
1 ONLINE ONLINE myhost1
ora.scan3.vip
1 ONLINE ONLINE myhost1

Wednesday, October 9, 2013

Discoverer upgrade steps (Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2 to Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.3.0)

Steps:

1) Upgrade the infra – database to 10.1.0.5 from 10.1.0.4.2

2) 10g 10.1.2.3 patch set 3 application

3) Apply latest cumulative patch 11674847

---1) Upgrade the infra – database to 10.1.0.5 from 10.1.0.4.2

a) Bring down Discoverer , Infra and Database services

-rw-r--r-- 1 oradisco dba 210 Jul 16 10:58 disco.env

-rw-r--r-- 1 oradisco dba 233 Jul 16 11:04 infra.env

[oradisco@disco ~]$ pwd

/home/oradisco

n AS Home

[oradisco@disco ].

[oradisco@disco bin]$ ./opmnctl stopall

opmnctl: stopping opmn and all managed processes...

================================================================================

opmn id=disco.world.com:6201

6 of 7 processes stopped.

ias-instance id=oraas1.disco.world.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

ias-component/process-type/process-set:

OC4J/OC4J_BI_Forms/default_island

Error

--> Process (pid=1999)

time out while waiting for a managed process to stop

Log:

/data/oradisco/OraHome_AS1/opmn/logs/OC4J~OC4J_BI_Forms~default_island~1

opmnctl: graceful stop of processes failed, trying forceful shutdown...

[oradisco@disco bin]$

[oradisco@disco bin]$ ./emctl status iasconsole

TZ set to US/Pacific

Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2

http://disco.world.com:1156/emd/console/aboutApplication

Oracle Enterprise Manager 10g Application Server Control is running.

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

Logs are generated in directory /data/oradisco/OraHome_Infra/sysman/log

[oradisco@disco bin]$ ./emctl stop iasconsole

TZ set to US/Pacific

Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2

http://disco.world.com:1156/emd/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Application Server Control ... ... Stopped.

n Infra Home

[oradisco@disco bin]$ ./opmnctl stopall

opmnctl: stopping opmn and all managed processes...

================================================================================

[oradisco@disco bin]$ ./emctl stop iasconsole

TZ set to US/Pacific

Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2

http://disco.world.com:1156/emd/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Application Server Control ... ... Stopped.

n Database

SQL> select name from v$database;

NAME

---------

INFRA

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oradisco@disco bdump]$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 12-OCT-2011 23:17:54

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=disco.world.com)(PORT=1521)))

The command completed successfully

n Take the existed Homes backup

[oradisco@disco oradisco]$ pwd

/data/oradisco

[oradisco@disco oradisco]$ vi /home/oradisco/nohup.out

[oradisco@disco oradisco]$ jobs

[1] Running nohup tar czf oradata_12OCT11.tar.gz oradata &

[2]- Running nohup tar czf OraHome_Infra_12OCT11.tar.gz OraHome_Infra &

[3]+ Running nohup tar czf OraHome_AS1_12OCT11.tar.gz OraHome_AS1 &

n install 10.1.0.5 DB patch set

[oradisco@disco Disk1]$ ./runInstaller -ignoreSysPreReqs

Starting Oracle Universal Installer...

(/data/oradisco/OraHome_Infra/root.sh)and following is the output of the same.

-------------Output------------------

Running Oracle 10g root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oradisco

ORACLE_HOME= /data/oradisco/OraHome_Infra

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying dbhome to /usr/local/bin ...

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying oraenv to /usr/local/bin ...

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

[root@disco ~]#

[oradisco@disco bin]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Oct 12 23:09:41 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP UPGRADE

ORACLE instance started.

Total System Global Area 281018368 bytes

Fixed Size 779000 bytes

Variable Size 227810568 bytes

Database Buffers 50331648 bytes

Redo Buffers 2097152 bytes

Database mounted.

Database opened.

SQL> SPOOL patch.log

SQL> @/ORACLE_HOME/rdbms/admin/catpatch.sql

SP2-0310: unable to open file "/ORACLE_HOME/rdbms/admin/catpatch.sql"

SQL> @$ORACLE_HOME/rdbms/admin/catpatch.sql

DOC>######################################################################

DOC> The following PL/SQL block will cause an ORA-20000 error and

DOC> terminate the current SQLPLUS session if the user is not SYS.

DOC> Disconnect and reconnect with AS SYSDBA.

DOC>######################################################################

DOC>#

DOC>#######################################################################

DOC>

DOC> The above query lists the SERVER components in the database,

DOC> along with their current patch set version and status.

DOC>

DOC> Please review the status and version columns and look for

DOC> any errors in the spool log file. If there are errors in the spool

DOC> file, or any components are not VALID or not the correct version,

DOC> consult the Patch Set Notes.

DOC>

DOC> Next shutdown immediate, restart for normal operation, and then

DOC> run utlrp.sql to recompile any invalid application objects.

DOC>

DOC>#######################################################################

DOC>#

SQL>

SQL> spool off

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 281018368 bytes

Fixed Size 779000 bytes

Variable Size 227810568 bytes

Database Buffers 50331648 bytes

Redo Buffers 2097152 bytes

Database mounted.

Database opened.

SQL> select name from v$database;

NAME

---------

INFRA

1 row selected.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2011-10-12 23:33:33

1 row selected.

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2011-10-12 23:33:59

1 row selected.

PL/SQL procedure successfully completed.

Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK

Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status = 'INVALID';

COUNT(*)

----------

1 row selected.

n Application Server 10g 10.1.2.3 patch set 3 Patch 5983622 on AS Home (Discoverer)

[oradisco@disco patchset_10123]$ cd Disk1/

[oradisco@disco Disk1]$ ls -ltr

total 620

-rwxr-xr-x 1 oradisco dba 1280 Dec 18 2007 runInstaller

drwxr-xr-x 5 oradisco dba 4096 Jan 2 2008 install

drwxr-xr-x 2 oradisco dba 4096 Mar 31 2008 doc

drwxr-xr-x 10 oradisco dba 4096 Apr 3 2008 stage

-rwxr-xr-x 1 oradisco dba 612054 Apr 4 2008 README.html

[oradisco@disco Disk1]$ ./runInstaller

Starting Oracle Universal Installer...

Checking installer requirements...

--- Note as we don’t know the user “ias_admin” credentials, had reset the password as follows in both Infra and AS Homes

Change ias_admin password directly in configuration file
–Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
–Search for entry like below
<user>
    <name>ias_admin</name>
      <credentials>{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu</credentials>
</user>
and Update new password (welcome2 like )
<user>
    <name>ias_admin</name>
      <credentials>!welcome2</credentials>
</user>

Note ! (Exclamation Mark in front of password. This signifies that password is stored in clear text)

[oradisco@disco config]$ vi jazn-data.xml
[oradisco@disco config]$ pwd
/data/oradisco/OraHome_Infra/sysman/j2ee/config
[oradisco@disco config]$ cd /data/oradisco/OraHome_Infra/bin
[oradisco@disco bin]$ emctl set password welcome2 welcome123
TZ set to US/Pacific
-------à AS Home
[oradisco@disco ~]$ cd /data/oradisco/OraHome_AS1/sysman/j2ee/config/jazn-data.xml
[oradisco@disco ~]$ cp /data/oradisco/OraHome_AS1/sysman/j2ee/config/jazn-data.xml /data/oradisco/OraHome_AS1/sysman/j2ee/config/jazn-data.xml_bkp
[oradisco@disco ~]$ vi /data/oradisco/OraHome_AS1/sysman/j2ee/config/jazn-data.xml
[oradisco@disco ~]$ cd $ORACLE_HOME/bin
[oradisco@disco bin]$ emctl set password welcome2 welcome123
TZ set to US/Pacific
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

----Note as we don’t know the user “cn=orcladmin” password credentials , had reset the password as follows on both Infra and AS nodes

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where USERNAME like '%ODS%';

USERNAME ACCOUNT_STATUS

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

ODS OPEN

SQL> alter user ODS identified by welcome2;

User altered.

[oradisco@disco ~]$ cd /data/oradisco/OraHome_Infra/opmn/bin/

[oradisco@disco bin]$ ./opmnctl stopproc ias-component=OID

Internal error message 684 could not be found in the msb file

opmnctl: stopping opmn managed processes...

Internal error message 684 could not be found in the msb file

[oradisco@disco bin]$ ps -ef | grep -i oidmon

oradisco 3182 15070 0 02:49 pts/1 00:00:00 grep -i oidmon

[oradisco@disco bin]$ ps -ef | grep -i oidldapd

oradisco 3207 15070 0 02:50 pts/1 00:00:00 grep -i oidldapd

[oradisco@disco bin]$ ps -ef | grep -i odisrv

oradisco 3361 15070 0 02:50 pts/1 00:00:00 grep -i odisrv

[oradisco@disco bin]$ cd $ORACLE_HOME/ldap/admin

[oradisco@disco admin]$ ls -ltr oidpwdlldap1

-rw-r-x--- 1 oradisco dba 207 Oct 13 01:46 oidpwdlldap1

[oradisco@disco admin]$ ls -ltr oidpwdrxxxx

ls: oidpwdrxxxx: No such file or directory

[oradisco@disco admin]$ ls -ltr oidpwdr*

-rw-r-x--- 1 oradisco dba 331 May 19 2009 oidpwdrinfra

[oradisco@disco admin]$ mv oidpwdlldap1 oidpwdlldap1_bkp1

[oradisco@disco admin]$ mv oidpwdrinfra oidpwdrinfra_bkp2

[oradisco@disco admin]$ sqlplus ods/welcome1@INFRA

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Oct 13 02:52:01 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

With the Partitioning, OLAP and Data Mining options

[oradisco@disco admin]$ oidpasswd connect=INFRA create_wallet=true

password:

confirm password:

password set

[oradisco@disco admin]$ ls -ltr oidpwdr*

-rw-r-x--- 1 oradisco dba 331 May 19 2009 oidpwdrinfra_bkp2

-rw-r--r-- 1 oradisco dba 215 Oct 13 02:52 oidpwdrinfra

[oradisco@disco admin]$ ls -ltr oidpwdlldap1

-rw-r--r-- 1 oradisco dba 207 Oct 13 02:52 oidpwdlldap1

[oradisco@disco admin]$ oidpasswd connect=INFRA unlock_su_acct=true

OID DB user password:

OID super user account unlocked successfully.

[oradisco@disco admin]$ oidpasswd connect=INFRA reset_su_password=true

OID DB user password:

new password:

confirm password:

password set

[oradisco@disco bin]$ $ORACLE_HOME/bin/ldapbind -p 389 -D cn=orcladmin -w welcome2

bind successful

[oradisco@disco bin]$ pwd

/data/oradisco/OraHome_Infra/opmn/bin

n Application Server 10g 10.1.2.3 patch set 3 Patch 5983622 on Infra Home

As I ran into below issue and Infra home seems not upgraded then I applied the same patch set on Infra Home too

[oradisco@disco bin]$ ./emctl stop iasconsole

TZ set to US/Pacific

Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2

http://disco.ntc.com:1156/emd/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Application Server Control ...

Cannot determine Oracle Enterprise Manager 10g Application Server Control process; /data/oradisco/OraHome_Infra/bin/emctl.pid does not exist.

Oracle Enterprise Manager 10g Application Server Control may not be running.

[oradisco@disco bin]$ pwd

/data/oradisco/OraHome_Infra/bin

n apply the latest Cumulative patch 11674847 if not the following error will thrown out while connecting Discoverer

"Missing Applications Security Library: fnd115"

[oradisco@disco 11674847]$ which opatch

/usr/bin/which: no opatch in (/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oradisco/bin:/data/oradisco/OraHome_AS1/bin:/data/oradisco/OraHome_AS1/opmn/bin)

[oradisco@disco 11674847]$ /data/oradisco/OraHome_AS1/OPatch/opatch apply

Oracle Interim Patch Installer version 1.0.0.0.58

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 : /data/oradisco/OraHome_AS1

Oracle Home Inventory : /data/oradisco/OraHome_AS1/inventory

Central Inventory : /home/oradisco/oraInventory

from : /etc/oraInst.loc

OUI location : /data/oradisco/OraHome_AS1/oui

OUI shared library : /data/oradisco/OraHome_AS1/oui/lib/linux/liboraInstaller.so

Java location : /data/oradisco/OraHome_AS1/jre/1.4.2/bin/java

Log file location : /data/oradisco/OraHome_AS1/.patch_storage/<patch ID>/*.log

Creating log file "/data/oradisco/OraHome_AS1/.patch_storage/11674847/Apply_11674847_10-13-2011_04-15-00.log"

Looking for /home/oradisco/patchset_10123/11674847/files/ocm.zip...

Invoking fuser to check for active processes.

Backing up comps.xml ...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shut down Oracle instances running out of this ORACLE_HOME

(Oracle Home = /data/oradisco/OraHome_AS1)

Is this system ready for updating?

Please respond Y|N >

Executing the Apply pre-patch script (/home/oradisco/patchset_10123/11674847/custom/scripts/pre)...

Applying patch 11674847...

Patching copy files...

Inventory is good and does not have any dangling patches.

Updating inventory...

Verifying patch...

Backing up comps.xml ...

Executing the Apply post-patch script (/home/oradisco/patchset_10123/11674847/custom/scripts/post)...

OPatch succeeded.

Reference doc : How to Unlock/Reset Super User cn=orcladmin When the ODS's Password Has Been Forgotten or is Unknown? [ID 472752.1]

https://updates.oracle.com/Orion/Services/download?type=readme&aru=10097049

Oracle Application Server Patch Set Notes Addendum 10g Release 2 (10.1.2) Patch Set 3 (10.1.2.3.0) [ID 555681.1]

Steps/How to download latest patchsets.sh using sftp

Here are the steps to download, to access this, the port 2021 needs to be opened from your environment.

oracle-ven1-/home/oradev $sftp -o PORT=2021 dba@xyz.com@sftp.oracle.com

Connecting to sftp.oracle.com...

The authenticity of host 'sftp.oracle.com (141.146.1.165)' can't be established.

RSA key fingerprint is ab:8f:0e:ef:57:ae:3f:49:b9:ab:d6:2d:b5:47:42:25.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'sftp.oracle.com,141.146.1.165' (RSA) to the list of known hosts.

*******************************************************************************

Oracle SFTP Server

The use of this SFTP server is governed by United States Export Administration

Regulations. Details of the U.S. Commercial Encryption Export Controls can be

found at the Bureau of Industry and Security web site.

All Oracle products are subject to U.S. Export Laws.

Diversion contrary to U.S. law is prohibited

http://www.oracle.com/us/products/export/export-regulations-345813.html

Oracle Employees:

Under no circumstances are Oracle Employees authorized to download software for

the purpose of distributing it to customers. Oracle products are available to

employees for internal use or demonstration purposes only. Customers may

download Oracle software through the Oracle Technology Network or through

Oracle's e-Delivery site. Failure to comply with these requirements may result

in disciplinary action.

****************************************************************************

password

Enter password for dba@xyz.com

Password:

sftp> pwd

Remote working directory: /

sftp> cd support /outgoing/PATCHSET_COMPARE_TOOL

sftp> get patchsets.sh

Fetching //support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh to patchsets.sh

//support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh 100% 816KB 815.9KB/s 00:01

sftp> bye

Ref: Oracle Applications Current Patchset Comparison Utility - patchsets.sh (Doc ID 139684.1)

Thursday, October 3, 2013

Inbound mails not delivering to oracle apps after user approved - workflow

Issue:Inbound mails not delivering to oracle apps after user approved in workflow and are failing with time out error

Solution:

I cleared the unwanted INBOX mails for wf@ account and bounced the workflow services , now I see all INBOX mails got processed.

Ref: 11i-12 A Guide For Troubleshooting Workflow Notification Emails - Inbound and Outbound (Doc ID 831982.1)

Saturday, September 28, 2013

Workflow mailer configuration setup

1) Login to Oracle Applications Manager using sysadmin account

2) In the drop down box choose “Workflow Manager” and click GO

3) Click on Service componets

4) Choose Workflow Notification Mailer and then click on Edit

Make sure you brought down the workflow services before edit to avoid the unnecessary notifications

5) Enter the "Outbound Server Name"

6) Edit the "From"
7) Edit the "Reply-to-Address"

8) Disable/Enable scheduled workflow start/stop events if needed.

9) Review the details and Finish

SQL query to verify the workflow details:

set lines 1000
col VALUE for a40
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER','ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX');

SQL query to verify the workflow services status:

set lines 1000
col COMPONENT_STATUS_INFO for a40
col COMPONENT_NAME for a50
SELECT component_name, component_status, component_status_info
FROM fnd_svc_components_v
WHERE component_name like 'Workflow%';

Few reference documents:

How to Verify/Start WorkFlow Background Engine? [ID 112935.1]
How to Stop/Start Workflow Service Containers From Command Line (Doc ID 743264.1)
How To Automatically Refresh, Resume, Start, Stop, Suspend, or Launch Summary Notifications for the Workflow Mailer as needed? (Doc ID 443643.1)

Saturday, September 21, 2013

ORA-12899: value too large for column "APPLSYS"."FND_OAM_APP_SYS_STATUS"."NODE_NAME"

Error: OAM Applications Dashboard Collection concurrent request failing with below error

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

Start of log messages from FND_FILE

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

java.sql.SQLException: ORA-12899: value too large for column "APPLSYS"."FND_OAM_APP_SYS_STATUS"."NODE_NAME" (actual: 34, maximum: 30)

ORA-06512: at "APPS.FND_OAM_COLLECTION", line 635

ORA-06512: at "APPS.FND_OAM_COLLECTION", line 1246

ORA-01403: no data found

ORA-06512: at "APPS.FND_OAM_COLLECTION", line 1350

ORA-06512: at line 1

Solution:

Verified the fnd_nodes and found DB node not exist in table

SQL> select NODE_NAME,SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN,SUPPORT_DB from fnd_nodes;

NODE_NAME S S S S S

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

AUTHENTICATION N N N N N

NSAPPS196 Y Y Y Y N

Brought down the apps node , ran autoconfig on DB and Apps nodes, brought up the services.

NODE_NAME S S S S S

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

AUTHENTICATION N N N N N

NSAPPS196 Y Y Y Y N

NSDB191 N N N N Y

The request completed successful now.

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

Updated system property java.protocol.handler.pkgs to HTTPClient

Refreshing All ...

Refreshing Applications System Status ...

Refreshing Activity ...

Refreshing Configuration Changes ...

Refreshing Throughput ...

Refreshing Exceptions Summary ...

Refreshing User Alert Summary ...

Done Refreshing All ...

Checking collected data for alerting ...

Cancelling any pending requests for FNDOAMCOL ...

Done Cancelling any pending requests for FNDOAMCOL ...

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

About The Applications Dashboard

The Applications Dashboard provides a "snapshot" of your Oracle E-Business Suite system. Information is grouped under the following tabs: Overview, Performance, Critical Activities, Diagnostics, Business Flows, Security, and Software Updates.

From the Dashboard you can navigate to the Site Map, or use the drop-down menu to navigate to any of the following pages:

Application Services
Configuration - Overview
Forms Sessions
Database Status
Applied Patches
Patch Wizard
Workflow Manager

Oracle Applications Manager uses the collection program OAM Applications Dashboard Collection (short name: FNDOAMCOL) to gather the information displayed. The default repeat interval for this program is 10 minutes. To immediately regather the data and update the display for a particular region, click the corresponding Refresh icon. If the OAM Applications Dashboard Collection request is not running when you log in to the Oracle Applications Manager, a request will be submitted automatically under your username.

Monday, July 22, 2013

AUTOTRACE Option in SQL*Plus

admin]$ sqlplus '/as sysdba'

SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> grant plustrace to xxuser;

Grant succeeded.

GRANTEE TYP PRIVILEGE OR ROLE
------------------------------ --- --------------------------------------------------------------------------
XXUSER PLUSTRACE

Refer: AUTOTRACE Option in SQL*Plus (Doc ID 43214.1)

Thursday, June 6, 2013

ORA-01630: max # extents (505) reached in temp segment in tablespace

I got ORA-01630: max # extents (505) reached in temp segment in tablespace while creating the index.

SQL> show user
USER is "APPS"
SQL> create index xyz.xygts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id);
create index xyz.xygts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id)
*
ERROR at line 1:
ORA-01630: max # extents (505) reached in temp segment in tablespace
XY_DATA

SQL> select BYTES/1024/1024 from dba_segments where SEGMENT_NAME='MTL_MATERIAL_TRANSACTIONS';

BYTES/1024/1024
---------------
10510

-- Verified the pctincrease value

SQL> select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces where TABLESPACE_NAME='XYZ_DATA';

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS

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

PCT_INCREASE

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

XYZ_DATA 40960 40960 505

SQL> ALTER TABLESPACE XYZ_DATA DEFAULT STORAGE (pctincrease 50);

Tablespace altered.

SQL> select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces where TABLESPACE_NAME='XYZ_DATA';

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
------------------------------ -------------- ----------- -----------
PCT_INCREASE
------------
XYZ_DATA 40960 40960 505
50
-- I able to create index after increasing the pctincrease to 50

SQL> conn apps
Enter password:
Connected.
SQL> create index xyz.xxgts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id);

Index created.

Ref : TROUBLESHOOTING GUIDE (TSG) - UNABLE TO CREATE / EXTEND Errors [ID 1025288.6]

Oracle Applications and Fusion DBA concepts

Search This Blog

Thursday, December 12, 2013

Scripts to find USED,FREE and total DataBase sizes

RC:crs services are not coming up after removal OS user - CRS-0184: Cannot communicate with the CRS daemon after reboot

Wednesday, October 9, 2013

Discoverer upgrade steps (Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2 to Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.3.0)

Steps/How to download latest patchsets.sh using sftp

Thursday, October 3, 2013

Inbound mails not delivering to oracle apps after user approved - workflow

Saturday, September 28, 2013

Workflow mailer configuration setup

Saturday, September 21, 2013

ORA-12899: value too large for column "APPLSYS"."FND_OAM_APP_SYS_STATUS"."NODE_NAME"

Monday, July 22, 2013

AUTOTRACE Option in SQL*Plus

Thursday, June 6, 2013

ORA-01630: max # extents (505) reached in temp segment in tablespace

Transportable tablespace refresh

Pages

My Blog List

Translate

Total Pageviews

Oracle Blogs | Technology Blogs | Economy Blogs