Oracle DBA Place

General information In this setup we are: Installing docker Creating Non-root user (ynixon) with sudo and docker privileges ASM device: /dev/sdb1 Enabling sqlnet + ssh to the container Default ASM port is 1521 ssh port 2222 Passwords for root + grid os users in the container are “ynixon” Password for sys ASM user is “ynixon” Grid software is 12.2 without any patches Container Operating system is Oracle Linux 7.5 Within the container, there is no use of UDEV / ASMLIB or ASMFD – the asm_diskstring='/dev/asm*' ,'/dev/*' All test done on regular Ubuntu 14.04 There is a crontab job to keep 15 days of trace files + remove audit files. Prepare host for ASM device Make sure the device has permissions of the same container ids by applying UDEV rules $ vi /etc/udev/rules.d/100-asm.rules KERNEL=="sdb1", NAME="ASM_DISK", OWNER="54421", GROUP="54421", MODE="0660" udevadm trigger --sysname-match=sdb1 --ver...

In the past I wrote a post about  Bypassing the listener and connecting to ASM without a password from Python code Now, I've been asked to prove that it can be done in Java. The solution is almost the same. Java sample code using OCI: $ vi OracleCon.java import java.sql.*; class OracleCon {     public static void main(String args[]) {         try {             Class.forName("oracle.jdbc.driver.OracleDriver");             Connection con = DriverManager.getConnection(                 "jdbc:oracle:oci:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=BEQ) (PROGRAM=oracle) (ARGV0=oracle+ASM) (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') (ENVS = 'ORACLE_HOME=/oracle/product/12.1.0.2/grid,ORACLE_SID=+ASM'))) (CONNECT_DATA= (SID=+ASM)))", "sys as sysdba", "change on install");             Statement...

Hi, In our lab environments we test Data Guard on a daily basis, and we frequently “play” with failover, switchover, and flashback . The output of this playground is that we have some leftovers in the ASM; we call these leftovers orphan files . To solve this, I created SQL to query ASM views against database views. This query should run on the database (not ASM). SET VERIFY OFF SET LINESIZE 200 SET SERVEROUTPUT ON SET PAGESIZE 50000 DECLARE    cmd   CLOB; BEGIN    FOR c IN (SELECT name Diskgroup                FROM V$ASM_DISKGROUP)    LOOP       FOR l          IN (SELECT 'rm ' || files files                FROM                     (SELECT '+' || c.Diskgroup || files files, TYPE                        ...

View all mounted asm_diskgroups in the ASM instance SYS@+ASM> select NAME,STATE from v$asm_diskgroup 2 / NAME STATE ------------------------------ ----------- DATA2 MOUNTED DATA3 MOUNTED DATADG MOUNTED View all defined asm_diskgroups in the current ASM instance SYS@+ASM>show parameter disk NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ asm_diskgroups string BBXDG , DATADG, BBXDG3, BBXDG2, BBXL7, BBXL7REDO, BBXL7ARCHIV E , DATA2, BBXL7512, BBXL74096, BBXSTORAGE, L7BBX4096, BBXL7S B, BBXL7RAWDG, TEST, BBX7ONLIN ELOG, BBX7CONTROLFILE, BBXL7RA WASM , DATA3, BBXL7ASM512, BBX_ SP03, BBX_FDB asm_diskstring string /dev/raw/raw* Change spfile only to the mounted diskgroups SYS@+ASM> alter system set asm_diskgroups='DATA2','DATA3','DATADG'; Sys...

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

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

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

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

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: 0% 50%; PADDING-BOTTOM: 0.5em; MARGIN: 5px 20px 20px; OVERFLOW: auto; BORDER-LEFT: 1px solid; WIDTH: 95%; PADDING-TOP: 0.5em; BORDER-BOTTOM: 1px solid; moz-background-clip: -moz-initial; moz-background-origin: -moz-initial; moz-background-inline-policy: -moz-initial"> 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 ...