Oracle DBA Place

Hi, Struggling the right syntax in Unix environment for importing a lost view, here is the proper way: impdp user/password@database directory=dir_dump dumpfile=dump.dmp logfile=imp_MY_VIEW.log INCLUDE=VIEW:\"= \'MY_VIEW\'\" job_name=imp_MY_VIEW

while running archivelog backup and the file is missing: RMAN-06059: expected archived log not found, lost of archived log compromises recoverability trying to fix it by crosscheck: run { allocate channel c1 type disk ; crosscheck archivelog all ; release channel c1 ; } validation succeeded for archived log archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638 still need to run: allocate channel for maintenance type disk; crosscheck archivelog all; release channel;

We already had the same symptom of ORA-15032 & ORA-15063 mentioned in my last post . Trying to follow these instructions didn't help this time. ls -l /dev/oracleasm/disks/* rpm -qa | grep oracleasm kfod asm_diskstring='ORCL:*' disks=all /etc/sysconfig/oracleasm conatin ORACLEASM_SCANORDER=dm spfile has: asm_diskstring = /dev/dm* All of these command worked fine. Beacause of the dm* parameter I checked the filesystem: $ ls -l /dev/dm-* /bin/ls: /dev/dm-*: No such file or directory It seems that the dm* devices are missing and must be there for ASM by Note 602952.1 : NOTE: When scanning, only the device names known by the kernel are scanned. With device-mapper, the kernel sees the devices as /dev/dm-XX. The /dev/mapper/XXX names are created by udev for human readability. Any configuration of ORACLEASM_SCANORDER or ORACLEASM_SCANEXCLUDE must use the dm prefix. Since this Host is RedHat 5, we found the source of the problem by Note 558596.1: Cause: The oracleasm scans /p...

We are cloning our primary production database to a test system periodically. Sometimes it happens that we are adding a datafile to the primary database while the copy is in progress. After the copy we are creating a new control file based on the production ( with the new datafile ), trying to recover the cloned database we are getting the following error (from the alert.log): SQL> alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile; File #114 added to control file as ' UNNAMED00114 '. Originally created as: ' /bzq1/oracalls_2009_indx1/q109_icalls_ts_05.dbf ' Errors with log /bilpre/oraarch/bzq1_1_0000048990_561428818.arc Some recovered datafiles maybe left media fuzzy Media recovery may continue but open resetlogs may fail Media Recovery failed with error 1244 ORA-283 signalled during: alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile... It seems that in the archive log t...

In Oracle 10g, the glogin.sql and login.sql are run whenever you connect to a new user. This is the glogin.sql additions that I am using in 10g: set verify off termout off head off feed off col login_prompt new_value welcome SELECT upper(SYS_CONTEXT('USERENV','SERVER_HOST') ||' ' || SYS_CONTEXT('USERENV','CURRENT_USER') ||'@' || SYS_CONTEXT('USERENV','DB_NAME')) login_prompt FROM DUAL ; set sqlprompt "_user'@'_connect_identifier> " set verify on termout on head on feed on prompt ************************************ prompt WELCOME TO &&welcome prompt ************************************ prompt set echo off serveroutput on size 100000 line 100 trims on

There is a small difference of the connection string when the target is SID or SERVICE jdbc:oracle:driver_type:[username/password]@//host_name:port_number : SID_NAME jdbc:oracle:driver_type:[username/password]@//host_name:port_number / SERVICE_NAME Colon means SID Slash means Service name

On regular basis, we encounter the need to copy a whole directory between two hosts. Usually we do it when we clone oracle application software. Each time I am looking for the right syntax … :( NO MORE !!! tar cvf‎ – source_dir / | ssh target_host "cd `pwd`;tar xvf‎ -" This one will copy source_dir to‎ target_host For slow connection‎: tar cvf‎ – source_dir / | gzip -c‎ | ssh target_host "cd `pwd`;gunzip -c|tar xvf‎ -" You will enter password once‎ (probably‎)

switch the database to archivelog mode. SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; switch database to noarchivelog mode. SQL> shutdown immediate; SQL> startup mount; SQL> alter database noarchivelog; SQL> alter database open;

The trcsess utility consolidates trace output from selected trace files based on several criteria: trcsess &nbsp[output=output_file_name]         &nbsp[session=session_id]         &nbsp[clientid=client_id]         &nbsp[service=service_name]         &nbsp[action=action_name]         &nbsp[module=module_name]         &nbsp[trace_files] trcsess output=main.trc service=psdwh *trc After the consolidate trace file had been generated you can execute tkprof on it. For example: tkprof main.trc main.prf sys=no sort=exeela TKPROF: Release 10.2.0.3.0 - Production on Thu Feb 28 13:38:23 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. for more information: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm

In my firm we have a seperation between System guys to the DBAs, we don't have root privileges. this is the reason that they are peparing us the ASM disks. After using "/etc/init.d/oracleasm createdisk..." I tailored a script to see all the information that I can get WITHOUT the need to connect to the ASM instance. #!/bin/ksh export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2` export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin export SID=`grep ASM /etc/oratab | cut -d: -f1` printf "\n%-15s %-14s %-11s %s\n" "ASM disk" "based on" "Minor,Major" "Size (Mb)" printf "%-15s %-14s %-11s %s\n" "===============" "=============" "===========" "=========" for i in `/etc/init.d/oracleasm listdisks` do v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk '{print $2}' | sed 's/\"//g'` v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}...

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                                         ...

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     ...

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= writ...

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...