Linux : add additonal disk as a logical volume

# Add new disk as a logical volume # Create partition fdisk -l fdisk /dev/sdb n-1-rtn-rtn # Create a physical volume pvcreate /dev/sdb1 pvdisplay # Create a virtual group VolGroup01 vgcreate VolGroup01 /dev/sdb1 # Get PE size from vgdisplay vgdisplay  Total PE              4094 # Create a logical volume lvcreate -n vol01 -l 4094 VolGroup01 lvdisplay mkfs.ext3 /dev/VolGroup01/vol01 # Add to fstab vi /etc/fstab /dev/VolGroup01/vol01    /u01         ext3    defaults        0 0 mount /u01

BOXI : Restore to point in time with oracle

Shut down services on lonmisapp01 Restore database to point in time before deletion Export ORACLE_SID=MISDB1 Identify required sequence number from alert log 139776 (8:04 am) Disable dataguard dgmgrl<br />DGMGRL> connect sys<br />DGMGRL> show configuration<br />DGMGRL> disable configuration<br /> Run rman nocatalog connect target<br />shutdown immediate<br />startup mount<br /><br />run {<br /> set until sequence 139776;<br /> restore database;<br /> recover database;<br /> }<br /><br />alter database open;<br /> Restore file store to point in time before deletion - safe to use last full backup cd /u04/oradata/backup/MISDB1/BO-filestore<br />tar xvfz lonmisapp01_BO_FILESTORE_20090910012217_20426.tgz<br /><br />mv /u04/boxi/lonmisapp01/FileStore /u04/boxi/lonmisapp01/FileStore_Old<br />mv ./u04/boxi/lonmisapp01/FileStore /u04/boxi/lonmisapp01/FileStore<br /><br />chown -R root: /u04/boxi/lonmisapp01/FileStore<br /> Restart services on lonmisapp01 and test

Oracle : Refresh the dataguard instance and re-enable

Disable dataguard on primary dgmgrl DGMGRL> connect sys DGMGRL> show configuration DGMGRL> disable configuration Shutdown standby in sqlplus alter database recover managed standby database cancel shudown immediate Clear out standby data files cd /u04/oradata/STANDBYDB/<br />rm *.dbf<br /> Create backup of primary control file and then shut down primary in sqlplus alter database create standby controlfile as '/tmp/stnd_controlfile.bkp';<br />shutdown immediate;<br /> Copy control file to standby and then all data files (NOT archive logs, redo logs, standby logs) scp /tmp/stnd_controlfile.bkp oracle@greoemdb01:/u04/oradata/MISDG1<br />cd /u04/oradata/PRIMARYDB/<br />scp * oracle@greoemdb01:/u04/oradata/STABDBYDB<br /> Startup standby in sqlplus startup mount<br />alter database recover managed standby database disconnect;<br /> Startup primary in sqlplus startup <br /> Re-enable dataguard configuration in dgmgrl dgmgrl<br />DGMGRL> connect sys<br />DGMGRL> show configuration<br />DGMGRL> enable configuration<br /> Check db logs on both sides! Note did not need to create standby logs as they already exist on the primary (and so are referenced in the control file (possibly) )  did not need to rename any files on the standby as the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT params are both set correctly

Oracle : 11GR2 - File Watchers

Trigger scheduler jobs when a glob-matched file is created in a directory BEGIN DBMS_SCHEDULER.CREATE_FILE_WATCHER( FILE_WATCHER_NAME => 'EOD_FILE_WATCHER', DIRECTORY_PATH => '?/eod_reports', FILE_NAME => 'eod*.txt', CREDENTIAL_NAME => 'WATCH_CREDENTIAL', DESTINATION => NULL, ENABLED => FALSE); END; / SELECT file_watcher_name, destination, directory_path, file_name, credential_name FROM dba_scheduler_file_watchers; <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/scheduse005.htm#CIABIEJA">Ref</a>

Oracle : 11GR2 - IGNORE_ROW_ON_DUPKEY_INDEX Hint

Silently ignore new rows that violate a single unique index.  The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a SINGLE specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used. Ref Bulk operations? Multiple rows with same UK being inserted - which get in - arbitrary ?

Oracle : 11GR2 - Recursive subqueries for hierarchy traversal

Recursive Subquery Example =========================== WITH   reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS   (      SELECT employee_id, last_name, manager_id, 0 reportLevel      FROM employees      WHERE employee_id = 101    UNION ALL      SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1      FROM reports_to_101 r /* MAGIC IS HERE */          ,employees e      WHERE r.eid = e.manager_id   ) SELECT eid, emp_last, mgr_id, reportLevel FROM reports_to_101 ORDER BY reportLevel, eid;        EID EMP_LAST                      MGR_ID REPORTLEVEL ---------- ------------------------- ---------- -----------        101 Kochhar                          100           0        108 Greenberg                        101           1        200 Whalen                           101           1        203 Mavris                           101           1        204 Baer                             101           1        205 Higgins                          101           1        109 Faviet                           108           2        110 Chen                             108           2        111 Sciarra                          108           2        112 Urman                            108           2        113 Popp                             108           2        206 Gietz                            205           2 Ref

Oracle : 11GR2 - LISTAGG - the long missing string concatentation built-in

Finally LISTAGG Example =============== SELECT department_id "Dept.",   LISTAGG(last_name, '; ')    WITHIN GROUP (ORDER BY hire_date) "Employees"    FROM employees    GROUP BY department_id; Dept. Employees ------ ------------------------------------------------------------     10 Whalen     20 Hartstein; Fay     30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares     40 Mavris     50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie        s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot        ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat        el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;         Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv        an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle     60 Austin; Pataballa; Lorentz; Ernst; Hunold     70 Baer Throws a ORA-01489: result of string concatenation is too long is > 4000 chars ! - No way to suppress (?) so need to avoid with base_data as ( select table_name, sum(length(table_name)) over (order by table_name)   + row_number () over (order by table_name)        tot_length from dba_tables where 1=1 and owner = 'SYS' order by table_name ) select listagg(table_name,';') within group (order by table_name) from base_data b where tot_length < 4000 Ref