DBA in the Trenches

I've been noticing lately that the database does not seem to be cleaning up after itself and the processes just keep climbing and climbing. Some sessions are more than two weeks old. I thought this was a bit weird and when our monitoring system started alerting to the number of sessions I realised there was a lot of dead session that are inactive but not being cleaned up. This is probably due to pooling and the way the DB is trying to save resources but in the EBS once a user closes a form they will not be using that session again. This sql give me the code to kill the session both in the DB as well as on the server itself: select vp.SPID,vs.username, UPPER(vs.program) program, logon_time,         floor(vs.last_call_et / 60 ) min_inactive        ,floor(vs.last_call_et / 60 /60 ) hr_inactive, vs.status, vs.status        ,'alter system kill session '''||vs.sid||','||vs.serial#||''';'       ...

I found this neat little tool for digging into the alert log without having to actually open it up and start searching line by line. Produces a nice little summary and also generates a report if you need to upload something to MOS. adrci> show problem ADR Home = /u03/oratest/testdb/11.2.0.4/admin/TEST_oratest/diag/rdbms/test/TEST: ************************************************************************* PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                              -------------------- ----------------------------------------------------------- -------------------- ----------------------------------------  2           ...

Had a few issues come up in the alert log regarding memory with dreaded ORA-600 and ORA-7445. The search tool didn't really show anything regarding the problems so opened an SR and need to attach the incident package. Pretty simple setup and extracting and something I'd like to dig into a bit. Here's how its done: #adrci adrci>show incident -mode detail -p "incident_id=117345" adrci>set homepath diag/rdbms/test/TEST adrci>IPS CREATE PACKAGE INCIDENT 117345 Created package 3 based on incident id 117345, correlation level typical adrci>IPS GENERATE PACKAGE 3 IN /tmp Generated package 3 in file /tmp/ORA7445pe_20160211093814_COM_1.zip, mode complete

Need to find the location of the alert log file. It's by default in the background dump destination. You can find it in the parameters of the instance. SQL> show parameter background_dump_dest NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ background_dump_dest                 string      /u01/app/oracle/diag/rdbms/dev/dev/trace SQL>  !ls -ltr /u01/app/oracle/diag/rdbms/dev/dev/trace/al* -rw-r-----. 1 oracle oinstall 134067 Jan 23 10:59 /u01/app/oracle/diag/rdbms/dev/dev/trace/alert_dev.log

Came across this issue at a customer site or should I say my Liron ran across the issue and asked Arty and myself to investivate. He gave us the hint that it looked like XML Database was not installed. exec sys.dbms_network_acl_admin.create_acl (acl => 'test.xml', description => 'test acl', principal => 'test', is_grant => true, privilege => 'resolve') ;  PLS-00201: identifier 'DBMS_XEVENT.XDBREPOSITORYEVENT' must be declared Check which options are selected in the database select * from v$option order by value,parameter; PARAMETER                                                        VALUE ---------------------------------------------------------------- ---------- Active Data Guard                                        ...

I'm not a big fan of creating a db link in the tnsnames.ora file because during clones you need to start messing around with custom files. My preferred method is using a bit of SQL to create the db link. Using the details from the tnsname.ora file of the target database: TEST=  (DESCRIPTION=   (ADDRESS=(PROTOCOL=tcp)(HOST=oradbtest.company.com)(PORT=1521))  (CONNECT_DATA=   (SERVICE_NAME=TEST)   (INSTANCE_NAME=TEST)   )  ) I can now create the database link as follows: create public database link TESTDBLINK connect to apps identified by apps using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradbtest.company.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TEST)))'; Just remember you might want to not make it public and you may need to change user names and passwords to the target instance.

This is probably in a million places out there but just posting it here for my own reference and you might find it useful too. Disable Archiving SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u04/archive/ref4data Oldest online log sequence     1 Next log sequence to archive   4 Current log sequence           4 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 8551575552 bytes Fixed Size                  2270360 bytes Variable Size            2634025832 bytes Database Buffers         5804916736 bytes Redo Buffers           ...

When checking for the latest PSU you might want to know what the current PSU is. I also keep a list of PSU updates and latest CPU updates on the Critical Patch Updates page. If the below returns nothing you are on the base level, e.g. 11.2.4.0 opatch lsinventory -bugs_fixed | egrep -i 'PSU|DATABASE PATCH SET UPDATE'  10248523 13696224 Fri Mar 30 14:00:25 EDT 2012 DATABASE PSU 11.2.0.2.1 11724916 13696224 Fri Mar 30 14:00:25 EDT 2012 DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011) 12419331 13696224 Fri Mar 30 14:00:25 EDT 2012 DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011) 12827726 13696224 Fri Mar 30 14:00:25 EDT 2012 DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011) 13343424 13696224 Fri Mar 30 14:00:25 EDT 2012 DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPUJAN2012) 13696224 13696224 Fri Mar 30 14:00:25 EDT 2012 DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPUAPR2012) Reference : Patch Set Updates for Oracle Products (Doc ID 854428.1...

During a PSU check I saw a list of patches that are recommended. I saw a list that has JVM and the DB bundled. I wasn't sure what this was and which one I should apply. After some digging around I understood that I could check whether I had JVM installed on the database although now I understand that with EBS it will probably be used by the database. This quick and dirty SQL will give you a list of components loaded into the database. One of which will be JServer JAVA Virtual Machine Release XXXXXXX. If its not there you probably don't have it installed. SQL> set pages 100 SQL> select * from all_registry_banners; BANNER -------------------------------------------------------------------------------- Oracle Database Catalog Views Release 11.2.0.4.0 - 64bit Production Oracle Database Packages and Types Release 11.2.0.4.0 - Development JServer JAVA Virtual Machine Release 11.2.0.4.0 - Development Oracle XDK Release 11.2.0.4.0 - Development Oracle Database Java Pac...

During the last upgrade I had to upgrade the database but also prepare the database for R12.2 so there were a few db patches that needed to be applied. Normally with two or three patches this is not a problem. However with twenty patches this makes things a bit more monotanous. My boss came to the rescue with this little jewel. First unzip ALL patches to $PATCH_TOP on DB tier unzip "p*.zip" -d $PATCH_TOP Add opatch to PATH in the $HOME/.bash_profile if not already in PATH export PATH=$PATH:$ORACLE_HOME/Opatch Check opatch is working opatch lsinventory  Apply all patches in a single command (one line) opatch napply -silent $PATCH_TOP -id 18485835,18689530,18893947,18966843,19291380,19393542,19472320,19627012,19649152,19779059,19835133,19896336,20093776,20177858,20181016,20204035,20294666,20476776,20830911,20994102,21091055 Now all I need is to figure out how to use this in non interactive mode so that I don't have to also answer the questions in between.

First create a decruption function that will decrypt the password. create or replace function xx_decrypt(key in varchar2, value in varchar2) return varchar2 as language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; Create a view used the decryption function: create or replace view xx_user_pass as SELECT user_name,       (SELECT xx_decrypt(UPPER(                               (SELECT (SELECT xx_decrypt(UPPER((SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)),usertable.encrypted_foundation_password) FROM dual) AS apps_password                               FROM fnd_user usertable                               WHERE usertable.user_name LIKE   ...

Responsibility: System Administrator or Similar Navigation: Profile > System Profile Name: “Self Service Personal Home Page mode” Options: Framework only Framework Simplified Framework Tree No Homepage Reference: How to Change Homepage   Steven Chan

Often I get a new server to clone an instance. This is the way I setup the user so that I don't have to do any change ownership to the files being cloned as they get the same id as the PROD system. This obviously will only work with single instances on a server. groupmod -g 500 dba useradd -gdba -s/bin/bash -poratest -d/home/oratest -m oratest usermod -u 500 oratest

There are lots of logs created in a system and like cleaning the house over the weekend you also need to keep the system clean. Sometimes this can free up quite a bit of space in the storage. crontab is my preferred method of doing this sort of scheduled cleanup although it can be niggly at times. # Minute  Hour    Day of Month   Month    Day of Week   Command # (0-59)  (0-23)  (1-31)         (1-12)   (0-6) 30   11   *   *   6   find /u01/archive/*.dbf -mtime +7 -exec rm {} \; So this means that I want the script to run once a week on a Saturday as 11:30 in the morning. It will find all the archives (*.dbf) that are older that 7 days and remove them. Really powerful so you also need to becareful how to use this command. Great information of the power of crontab

In R12.2 ETCC is used to check for bug fixes on the DB and Middle Tier. It is required to run the ETCC script on the database tier after each clone as adop check's this as one of its preliminary check's so you may as well keep this one up to date. Lastest version can be found at 17537119 . Prepare the files to run them mkdir /stage/install/ETCC chmod 777 /stage/install/ETCC cd /stage/install/ETCC unzip p17537119_R12_GENERIC.zip -d /stage/install/ETCC chmod a+x /stage/install/ETCC/* Run the script on each of the tier's that the services are running on: Database Tier: echo $CONTEXT_FILE | /stage/install/ETCC/checkMTpatch.sh  Middle Tier: echo $CONTEXT_FILE | /stage/install/ETCC/checkDBpatch.sh  Reference:  How To Run The 12.2 EBS Technology Code Level Checker (ETCC) ? (Doc ID 2008451.1) 12.2 E-Business Suite - How to identify patches for listed ETCC missing database and middle tier bugs (Doc ID 2057925.1)

With R12.2 you now have two file systems (fs): Run and Patch. Both actually being self explanatory. The run fs is where the current instance is being run from ($RUN_BASE) and patch fs is what adop uses to apply patches to before cutting over to use as the run fs ($PATCH_BASE). So one of the issues possibly for developers is that upon opening a ssh session they will need to source the run files system or even for DBA work you normally use the run fs unless specific work is needed on the patch fs. So how too make it easier than sourcing EBSapps.env each time and then entering either Run or Patch.  This is what is needed: source /u01/oracle/EBSapps.env run Now you could call this ever time you log into the instance or you could add it to $HOME/.bash_profile This way you will not have to worry about it and the person logging in will be using the run fs where most work is needed. Reference: How To Automatically Set the Current Run or Patch Edition / File System ...

Quick and simple way of recompiling invalid objects: sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlrp.sql 

On the Application Tier cd $COMMON_TOP/admin/scripts/CONTEXT_NAME ./adstpall.sh apps/APPS_PWD ###############GOTCHA ############### The Concurrent manager takes quite a while to shutdown. To work around this first shutdown the Concurrent manager with the abort command. ./adcmctl.sh abort apps/APPS_PWD ./adstpall.sh apps/APPS_PWD ####################################### On the Database Tier cd $ORACLE_HOME/appsutil/scripts/ ./addlnctl.sh stop ./addbctl.sh stop immediate Resource: Oracle-Base (Great Resource in General)