How to Kill Multiple *nix processes

Example 1: Remove FNDLIBR processes

kill `ps -ef | grep FNDLIBR | grep -v grep | awk '{print $2}'`

Example 2: Remove DCLIENT_PROCESSID  processes
kill `ps -ef | grep DCLIENT_PROCESSID | grep -v grep | awk '{print $2}'`

Instance Crash -terminating the instance due to error 63999

One afternoon I noticed that one of my databases was down. There was no business reason for it to be down. I startup the database and encountered the following.

ALTER DATABASE OPEN

Wed Feb 07 01:59:08 2018

Errors in file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_ora_30542.trc  (incident=1232241):

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 30486'

Incident details in: /dbapps/oracle/diag/rdbms/r12t1/R12T1/incident/incdir_1232241/R12T1_ora_30542_i1232241.trc

Wed Feb 07 01:59:38 2018

Killing enqueue blocker (pid=30486) on resource CF-00000000-00000000 by (pid=30542)

 by killing session 4401.1

Killing enqueue blocker (pid=30486) on resource CF-00000000-00000000 by (pid=30542)

 by terminating the process

USER (ospid: 30542): terminating the instance due to error 2103

Wed Feb 07 01:59:38 2018

System state dump requested by (instance=1, osid=30542), summary=[abnormal instance termination].

System State dumped to trace file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_diag_30478_20180207015938.trc

Dumping diagnostic data in directory=[cdmp_20180207015938], requested by (instance=1, osid=30542), summary=[abnormal instance termination].

Termination issued to instance processes. Waiting for the processes to exit

Wed Feb 07 01:59:49 2018

Instance termination failed to kill one or more processes

Instance terminated by USER, pid = 30542

Errors in file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_dbw0_11551.trc:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/dba0A/oradata/R12T1/system01.dbf'

ORA-27086: unable to lock file - already in use

Linux-x86_64 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 6315

Errors in file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_dbw0_11551.trc:

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/dba0A/oradata/R12T1/system02.dbf'

ORA-27086: unable to lock file - already in use

Linux-x86_64 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 6315

Errors in file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_dbw0_11551.trc:

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/dba0A/oradata/R12T1/system03.dbf'

ORA-27086: unable to lock file - already in use

Linux-x86_64 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 6315

Errors in file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_dbw0_11551.trc:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/dba0A/oradata/R12T1/system04.dbf'

ORA-27086: unable to lock file - already in use

Linux-x86_64 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 6315

It looks like the DB needed to be restored from backup. However I was curious what was the reason it went down. Examining the alert log from a few days back I saw this line.

Tue Feb 06 17:04:06 2018

KCF: read, write or open error, block=0x108b71 online=1

        file=1034 '/dba0B/oradata/R12T1/APPS_TS_TX_DATA108.dbf'

        error=27072 txt: 'Linux-x86_64 Error: 5: Input/output error

Additional information: 4

Additional information: 1084273

Additional information: -1'

Errors in file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_dbw0_6231.trc:

Errors in file /dbapps/oracle/diag/rdbms/r12t1/R12T1/trace/R12T1_dbw0_6231.trc:

ORA-63999: data file suffered media failure

ORA-01114: IO error writing block to file 1034 (block # 1084273)

ORA-01110: data file 1034: '/dba0B/oradata/R12T1/APPS_TS_TX_DATA108.dbf'

ORA-27072: File I/O error

Linux-x86_64 Error: 5: Input/output error

Additional information: 4

Additional information: 1084273

Additional information: -1

DBW0 (ospid: 6231): terminating the instance due to error 63999

I checked it by 

! ORCL_11204:server.domain:/dbapps/oracle/diag/rdbms/ORCL/ORCL/trace> cd /dba0B/oradata/ORCL/

! ORCL_11204:server.domain:/dba0B/oradata/ORCL> vi something.txt

! ORCL_11204:server.domain:/dba0B/oradata/ORCL> touch something

touch: cannot touch `something': Read-only file system

I told the SA to check /dba0B and make it read only. 

After getting the confirmation he fixed it. I was able to startup the DB normally.

How To Get Versions on EBS

One of the daunting tasks of being new to EBS is how to get the versions of its components. For me as a DBA I just say Oracle 12c, 12c R2, 10g, 9i  etc.   However, in EBS there are several others.

On this post lets get you started with some basic ones.

AD and TXK

SQL> set linesize 132
col ABBREVIATION format A20
col CODELEVEL format A20

select ABBREVIATION, CODELEVEL
from AD_TRACKABLE_ENTITIES
where ABBREVIATION in ('ad', 'txk');SQL> SQL> SQL> SQL>   2    3

ABBREVIATION         CODELEVEL
-------------------- --------------------
ad                   C.6
txk                  C.6

So for this one it is a delta 6 

Also a better way to do this is by looking at the Oracle Application Manager in EBS. The navigation is Sytem Administrator>Oracle Application Manager > Dashboard > software updates > other links > code level summary

EXPDP AND IMPDP Tricks

Have you ever been a situation that you needed to refresh a schema on DEV using data from PROD? This is a common scenario for a DBA.

Case1: Different SCHEMA and/or TABLESPACE

In my years of experience it is common to see that PROD and DEV environments does not have the same structure in terms of server capacity, file structure, schema and even tablespaces.

Let's say we have target DB called TDB and a source DB called SDB (pretty cool, i know right!). Our schema are TSCHEMA and SSCHEMA respectively and TSPACE and SSPACE for the tablespaces.

Step 1

In your source DB do this 

expdp directory=DATA_PUMP_DIR schemas=SSCHEMA dumpfile=expSSCHEMA.dmp logfile=expSSCHEMA.log 

(if you are in a *nix box, create this in a expdpSSCHEMA.sh so you would only have to key in the user and password. It is efficient instead of looking at "Burlesin Pages" all the time).


Step 2

Transfer expSSCHEMA.dmp to the target server.

Step 3

Once it is there do this

impdp directory=DUMPDIR 
remap_schema=expSSCHEMA:expTSCHEMA 
REMAP_TABLESPACE=SSPACE:TSSPACE
TABLE_EXISTS_ACTION=TRUNCATE 
dumpfile=expSSCHEMA.dmp
logfile=impSSCHEMA.log 

(if you are in a *nix box, create this in a expdpSSCHEMA.sh so you would only have to key in the user and password. It is efficient instead of looking at "Burlesin Pages" all the time).

Your Done.

ORA-39065: unexpected master process exception in DISPATCH

During expdp I received an error. See below


Export: Release 11.2.0.4.0 - Production on Tue Jan 9 00:28:47 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found

ORA-39097: Data Pump job encountered unexpected error 100

To Validate the Problem I ran

SQL> select count(*) from metanametrans$;
  COUNT(*)
----------
         0

It should have more values than that!
Solution
@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

Then try again.

EBS Compliance Checker

Fix data dictionary or time stamp mismatch

Apply the correct Online Patching Readiness and GSCC Report Patch for the code level from which you are upgrading, if not already applied. (Follow first section of the same document for this)

Step 1

1. Run the file $AD_TOP/patch/115/sql/adzddtsfix.sql (Following the usage instructions provided in it).
2. Run the $AD_TOP/sql/ADZDDBCC.sql script to identify whether the data dictionary corruption is still present.
a. If no corruption is found, proceed with the upgrade.
b. If corruption is still present, follow Step 2 below.

Step 2

Fix Logical data dictionary corruption (missing-parent)

Follow this step only when logical data-dictionary corruption is present.
1. Connect to database as "SYSDBA". 2. Run the $AD_TOP/patch/115/sql/adzddmpfix.sql 3. Run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether the logical data dictionary corruption is still present.
a. If no corruption is found, proceed with the upgrade or adop patching-cycle.
b. If corruption is still present, contact Oracle Support and log a bug.

Step 3

Follow this step only when data dictionary corruption is still present after following Step 1 above.
1. On database node, go to $ORACLE_HOME/rdbms/admin directory.
2. Run utlirp.sql (following the usage instructions provided).
3. Run utlrp.sql (following the usage instructions provided).
4. Run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether the data dictionary corruption is still present.
a. If no corruption is found, proceed with the upgrade.
b. If corruption is still present, contact Oracle Support and log a bug.

adop phase=cleanup loosing columns on a table.

When doing an adop cycle users complain that a table has lost its columns

Solution:

exec ad_zd_table.upgrade(<TABLE NAME>);



SQL> exec ad_zd_table.upgrade('MONT','MONT_EXPLODED_BOM');

SQL> exec ad_zd_table.patch('MONT','MONT_EXPLODED_BOM');

PL/SQL procedure successfully completed.