Posts

Showing posts from 2007

df emulation in ASM - asmbdf

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/REDUNDENCY)*1024,($8-$9)…

Fixing & Registering ORACLE_HOMES in Central Inventory

Central Inventory location is determined by oraInst.loc located in:
HP/Solaris /var/opt/oracle/oraInst.locLinux/etc/oraInst.locWindows\\HKEY_LOCAL_MACHINE\Software\Oracle\inst_locTo 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:
Name Location

Ora-29701 When Starting ASM Instance

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

corrupt redo log block header

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 headerORA-00353: log corruption near block 24184 change 45850151 time 06/03/2007 03:10:12ORA-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;

restoring a complete database via ASM in RAC environment

stop all cluster resources
Linux> sudo crsctl stop crscreate 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 channel 2 device type…

Changing archive location intergrating of OMF on ASM & RMAN

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 initialization parameter I figured the source of the problem SQL> show parameter log_…

RMAN in RAC environment

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; The configuration are written into the control file and then synced to the catalog. You 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 CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTRO…

Datapump NOT RUNNING & master table

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_name
  3      ,job_name
  4      ,operation
  5      …

Empty emoms.properties - The Solution

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.emdRepPwd=write here an unencrypted password
emdrep.ping.pingCommand=/bin/ping <…

UNIX Commands for DBAs

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 -mOn 64-bit, you will get:x86_64On 32-bit, you will get:i686 or similarHP:getconf KERNEL_BITSSolaris: /usr/bin/isainfo –kvOn 64-bit, you will get:64-bit sparcv9 kernel modulesOn 32-bit, you will get:32-bit sparc kernel modulesChecking whether current oracle installation is 32-bit or 64-bit.file $ORACLE_HOME/bin/oracleLinux: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 strippedOn 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 linked (uses shared libs), not strippedHP:On 6…

Automatic Monitoring and Alerting

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

RMAN-06019: rman does not recognizes plugged in tablespaces

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 are put in …

ASM instance: ORA-15032 and ORA-15063 errors occur after shutdown and startup

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
Variable Size 102786600 bytes
AS…

Hidden initialization parameters

Image
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 '/';

spfile is created in folder DB_UNKNOWN

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.

Tracing Commands

====================================
Trace Specific ORA-XXXXX - Current Session
====================================Activate:SQL> alter session set events '3113trace 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 '3113trace name errorstack level 3'; Stop:
SQL> alter system set events '3113 trace name context off'Or at init.oraevent="3113trace 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 '10046 trace name context forever, level 12';
<Yo…

Trace Analyzer TRCANLZR

Image
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 :)

Udump trace file name & location

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');

The orakill utility

Image
orakill The orakillutility 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 –9command 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 psUNIX 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 therefore, is not exposed …