Oracle DBA Place

When talking ASM between DBAs and System guys I had to let them "see" the new filesystem in the way they are used to. I parsed the output of asmcmd utility to be as close as it can be to the command in Unix/Linux, I called it asmbdf: #!/bin/ksh export user=oracle export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2` export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin export ORACLE_SID=`grep ASM /etc/oratab | cut -d: -f1` asmcmd lsdg | \ awk '{ if ( FNR == 1 ) { printf "%-20s %10s %10s %10s %10s %-20s\n","Filesystem","Size","Used","Avail","Use%","Mounted on" } if ( FNR > 1 ) { if ( $2 == "EXTERN" ) { REDUNDENCY=1 } if ( $2 == "NORMAL" ) { REDUNDENCY=2 } if ( $2 == "HIGH" ) { REDUNDENCY=3 } printf "%-20s %10d %10d %10d %10.2f%% %-20s\n",$(NF),($8/REDUNDENCY)*1024,(($8-$9)/REDUNDENCY)*1024,($9/REDUNDE

Central Inventory location is determined by oraInst.loc located in: HP/Solaris /var/opt/oracle/oraInst.loc Linux /etc/oraInst.loc Windows \\HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc To check if all oracle homes are registered within the Central Inventory run the following command: ${ORACLE_HOME}/oui/bin/opatch lsinventory -all Invoking OPatch 10.2.0.3.0 Oracle interim Patch Installer version 10.2.0.3.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /software/oracle/OEM10gR2/agent10g Central Inventory : /software/oracle/oraInventory from : /var/opt/oracle/oraInst.loc OPatch version : 10.2.0.3.0 OUI version : 10.2.0.3.0 OUI location : /software/oracle/OEM10gR2/agent10g/oui Log file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/opatch/opatch....log Lsinventory Output file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/....lsinventory.....txt ----------------------------------... List of Oracle Homes:

On Linux RedHat 4.0 On the first shutdown of non-RAC ASM, and then startup of the ASM we got the following error: ORA-29701: unable to connect to Cluster Manager It seems that CSS daemon does not want to start any more (occsd.bin is not launched). I run manual restart of daemon and reconfiguration (with localconfig) of CSS registry (as root): $ORACLE_HOME/bin/localconfig reset (CSS is running in the Oracle home of the ASM instance) It solved the problem. When I looked for the solution I found some other scenarios for the same error: Metalink Note: 264235.1 - ORA-29701 On Reboot When Instance Uses Automatic Storage Management (ASM) Metalink Note: ORA-29701 Not able to start the CSS for ASM

Today we had 100% storage in the filesystem of the database and archive logs (to many netapp snapshots).   In the alert.log file we got:   ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 24184 change 45850151 time 06/03/2007 03:10:12 ORA-00312: online log 2 thread 1: '/cycprd/oraredo2/redo02_2.log' ORA-00312: online log 2 thread 1: '/cycprd/oraredo2/redo02.log' ARC0: All Archive destinations made inactive due to error 354    After freeing this filesystem, the sollution was:    alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 2 ; alter database clear unarchived logfile group 3;   Checking if it worked:    alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile;

stop all cluster resources Linux> sudo crsctl stop crs create a temporary init.ora file init.ora ====== db_name=nplprd1  <== in RAC use instance name!!!! Linux> export ORACLE_SID=nplprd1 RMAN> startup nomount pfile=/tmp/init.ora; RMAN> run { allocate channel a DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=nplprd,OB2BARLIST=nplprd_weekly)' FORMAT 'nplprd_weekly<nplprd_%s:%t:%p>.dbf'; # we are using Data Protector restore spfile; shutdown immediate; startup nomount; # starting up using the restored spfile restore controlfile; alter database mount; # mounting with the restored controlfile configure channel 1 device type disk clear; # Clear service information configure channel 2 device type disk clear restore database; recover database; alter database open RESETLOGS; configure channel 1 device type sbt_tape connect 'sys/<password>@nplprd1'; # returning the definitions were cleared configure

While I checked the file location of the archives, I discovered unneeded aliases   ASMCMD [+] > cd +DATA/nplprd ASMCMD [+DATA/nplprd] > ls -l Type           Redund  Striped  Time             Sys  Name                                                       1_12_621591104.dbf => +DATA/nplprd/archivelog/2007_05_10/thread_1_seq_12.295.622220233                                                  Y    ARCHIVELOG/                                                  Y    CHANGETRACKING/                                                  Y    CONTROLFILE/                                                  Y    DATAFILE/                                                  Y    ONLINELOG/                                                  Y    PARAMETERFILE/                                                  Y    TEMPFILE/                                                  N    spfilenplprd.ora => +DATA/NPLPRD/PARAMETERFILE/spfile.272.621591209   Looking at the initializatio

Configuring parameters in RMAN ========================== RMAN> CONFIGURE BACKUP OPTIMIZATION ON; RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE SBT_TAPE CONNECT 'SYS/ <password> @nplprd1'; # for RAC RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE SBT_TAPE CONNECT 'SYS/ <password> @nplprd2'; # for RAC RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2; RMAN> CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2; RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 84 DAYS;   T he configuration are written into the control file and then synced to the catalog. Y ou can view the configured parameters via the command SHOW ALL   RMAN> show all;   RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; CONFIGURE C

Monitoring  the progress of data pump jobs, we discovered some old jobs that is no longer relevant.   SQL > set lines 100 SQL > col OWNER_NAME for a10 SQL > col JOB_NAME  for a21 SQL > col  OPERATION for a10 SQL > col JOB_MODE for a10  SQL > SELECT   2       owner_name   3      ,job_name   4      ,operation   5      ,job_mode   6      ,state   7    FROM dba_datapump_jobs;   OWNER_NAME JOB_NAME              OPERATION  JOB_MODE   STATE ---------- --------------------- ---------- ---------- ----------- YOAV_B     SYS_EXPORT_FULL_01    EXPORT     FULL       NOT RUNNING SYSTEM     FULL_EXPORT           EXPORT     FULL       NOT RUNNING SYSTEM     SYS_EXPORT_SCHEMA_01  EXPORT     SCHEMA     NOT RUNNING 3 rows selected.   since the datapump job is stopped or killed and not needed any more, we decided to drop the master table   SQL > drop table YOAV_B.SYS_EXPORT_FULL_01 PURGE;   Table dropped.   SQL  > SELECT   2       owner_nam

When Oracle Management Service (OMS) filesystem is 100% full, you may loose <oms_home>/sysman/config/emoms.properties Actually the file will remain empty with size of zero bytes. A small file that can cause Grid Control not to function.   emctl start oms . . . Could not find Console Server Port in /software/oracle/OEM10gR2 /oms10g/sysman/config/emoms .properties Oracle claims that you have to install a new OMS instead of the old one. (Notes: 418453.1, 418159.1) After some struggles looking for the undocumented syntax of the file, I managed to make OMS working again ;))   The lesson 1. Always BACKUP 2. Monitor the filesystem of OMS itself 3. Create some jobs for deleting old logs and unneeded archives. 4. Don't belive everything is written on Metalink.   Anyway, here is my file Enjoy :)   emoms.properties #Sun Apr 15 19:12:18 IDT 2007 oracle.sysman.emSDK.svlt .ConsoleServerName=jigles oracle.sysman.eml.mntr.emdRepPw d= write here an unen

As DBAs that working tightly with the operation system (OS), we need to know how to query the OS and its hardware. Usually we do it before fresh install, upgrade or migration of the DB/OS. Here is the first and basic commands, Later on I will go further more. Is my Operating System is 64-bit ? Linux: uname -m On 64-bit, you will get: x86_ 64 On 32-bit, you will get: i686 or similar HP: getconf KERNEL_BITS Solaris: /usr/bin/isainfo –kv On 64-bit, you will get: 64 -bit sparcv9 kernel modules On 32-bit, you will get: 32 -bit sparc kernel modules Checking whether current oracle installation is 32-bit or 64-bit. file $ORACLE_HOME/bin/oracle Linux: On 64-bit, you will get: oracle: setuid setgid ELF 64 -bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), not stripped On 32-bit, you will get: oracle: setuid setgid ELF 32 -bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically l

In oracle 10g we were introduced MMON process which collects metrics and statistics. No more complex queries for monitoring the database No more heavy load on the database done by our own monitoring scripts Just query one view or two. Of course that you can enhance this mechanism by changing the thresholds, push the alerts via mail... All of these can be done via SQL or by using Enterprise Manager/Database Console. Warning & critical threshold can be set by using DBMS_SERVER_ALERT package Thresholds definition can reviewed by using: SELECT object_name, metrics_name, warning_value, critical_value FROM dba_thresholds; Outstanding alerts can be reviewed by using: SELECT reason FROM dba_outstanding_alerts; When the alerts are cleared it can be reviewed by querying DBA_ALERT_HISTORY See: Using DBMS_SERVER_ALERT

Background: Migrating HP-UX oracle 9i to 10g On Linux RH4 using transportable tablespace to ASM The last step in this kind of procedure is to transfer the database file from filesystem to ASM using RMAN: RMAN> backup as copy tablespace TRANS_TEST format '+DATA/psdwh/datafile'; Starting backup at 22-FEB-07 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=136 devtype=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 02/22/2007 14:06:40 RMAN-20202: tablespace not found in the recovery catalog RMAN-06019: could not translate tablespace name "TRANS_TEST" It looks like we are hit bug#2656503 (not published on Metalink) which states that rman does not recognizes plugged in tablespaces until they a

Background: Using ASM with asmlib by multipath software The Story: Restarted ASM instance for the first time: [loki:oracle@+ASM] /etc/init.d >su - Password: [root@loki ~]# /etc/init.d/oracleasm stop Unmounting ASMlib driver filesystem: [ OK ] Unloading module "oracleasm": [ OK ] [root@loki ~]# /etc/init.d/oracleasm start Loading module "oracleasm": [ OK ] Mounting ASMlib driver filesystem: [ OK ] Scanning system for ASM disks: [ OK ] [root@loki ~]# /etc/init.d/oracleasm scandisks Scanning system for ASM disks: [ OK ] [root@loki ~]# /etc/init.d/oracleasm listdisks PRDWH_DATA_1 PRDWH_DATA_2 [root@loki ~]# logout We got the following Error: [ loki:oracle@+ASM] /etc/init.d >sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 26 13:47:13 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup ASM instance started Total System Global Area 130023424 bytes Fixed Size 2071000 bytes Vari

It seemd that the number of hidden parameters is increasing between versions. a small check reveals that: version 8.1.7.4 contains 300 paramters. version 9.2.0.8 contains 613 paramters. version 10.2.0.3 contains 1179 paramters. Here is the query I used to locate these parameters: SELECT a.ksppinm "Parameter", a.ksppdesc "Description", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/';

Using Linux RH4 64Bit with Oracle 10.2.0.3 with OMF on ASM when we tried to "create spfile from pfile;" the spfile was located in a wrong location: +DATA1/ DB_UNKNOWN /PARAMETERFILE/SPFILE.185.634235343 The link was pointing to the right location. After some struggles we found the solution in Note: 393932.1 We even managed to fix it by the note instructions: 1. Create a pfile    SQL> create pfile='/tmp/pfile' from spfile'+DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.272.622486851'; 2. Shutdown and startup mount using the restored spfile    SQL> startup mount force pfile=/tmp/pfile 3. Restore the spfile again to the correct location as DB_NAME is correctly set this time.    RMAN> restore spfile; 4. Startup again using the spfile    SQL> startup force UNTIL the next time we had to use "create spfile ...;" the nightmare got back again. L Eventually we decided to give up this solution and we are using spfile in a regular file system.

==================================== Trace Specific ORA-XXXXX - Current Session ==================================== Activate: SQL> alter session set events ' 3113 trace name errorstack level 3';   Stop: SQL> alter session set events ' 3113 trace name context off'   ==================================== Trace Specific ORA-XXXXX – for all system ==================================== Activate: SQL> alter system set events ' 3113 trace name errorstack level 3';   Stop: SQL> alter system set events ' 3113 trace name context off'   Or at init.ora   event=" 3113 trace name ERRORSTACK level 3"   You'll need to bounce the database.   ============================= 10046 Trace - Current Session ============================= Open new session is SQL*Plus ALTER SESSION SET max_dump_file_size = unlimited; ALTER SESSION SET tracefile_identifier = '10046'; ALTER SESSION SET statistics_level = ALL; ALTER SESSION SET events &

I discovered that oracle has a utility to analyze EVENT 10046 trace files. It seems to arrange it in HTML and collect some more relevant information from the database. To create the trace file see tracing commands . The TRCANLZR can be found in Metalink Note: 224270.1 Enjoy it :)

SELECT p1.value'/'p2.value'_ora_'p.spid'.trc' filename FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.name = 'user_dump_dest' AND p2.name = 'db_name' AND p.addr = s.paddr AND s.audsid = USERENV ('SESSIONID');

orakill The orakill utility is provided only with Oracle databases on Windows platforms. The executable ( orakill.exe ) is available to DBAs to kill Oracle sessions directly from the DOS command line without requiring any connection to the database. In the UNIX world, a DBA can kill a shadow process by issuing the kill –9 command from the UNIX prompt. UNIX is able to provide this capability given that the UNIX operating system is based on processes that fork other processes. All processes can be listed by using the ps UNIX command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process. Unlike the UNIX operating system, Windows systems are thread-based. For each instance, the background processes and sessions are all contained within the oracle.exe executable. These processes are not listed in the "Processes" tab of Windows Task Manager. Each session creates its own thread within oracle.exe and t