Travellers Trail

Saturday, 8 September 2012

Excel notes for phone style

=IF(MID(AN54,1,2)="79",CONCATENATE("0",MID(AN54,1,2),"-",MID(AN54,3,3),"-",MID(AN54,6,10)),AN54)

Thursday, 23 August 2012

Grant Materialized view


grant query rewrite to user1;
grant create materialized view to user1;

Make sure:


SYSTEM> show parameter query

NAME                           TYPE        VALUE
------------------------------ ----------- ------------------
query_rewrite_enabled          string      TRUE

Thursday, 12 July 2012

Zen cart About Page goes to Page not found

Zen Cart (Zencart / Zen-Cart) About Page goes to Page not found
After much searching.
Copied the whole folder from hosting site to local disk and done a diff and found following folder


(location)/includes/modules/pages/about_us/

was missing on hosting.

Tuesday, 5 June 2012

USB bootable OS

How to create and use Live USB


https://fedorahosted.org/liveusb-creator/

Took very long to fine the CenOS 5 live cd:


http://vault.centos.org/5.5/isos/x86_64/

For Linux, do:


yum install liveusb-creator

Other option, worked for Alex: http://unetbootin.sourceforge.net/ http://unetbootin.sourceforge.net/unetbootin-windows-latest.exe

Tuesday, 22 May 2012

ASM tips

ASM Clients and status

-- Clients
select INSTANCE_NAME, DB_NAME, STATUS from v$asm_client order by DB_NAME,INSTANCE_NAME;

ASM Diskgroups & Space, diskgroup size

col pct_used for a9
set line 200
select name, group_number,state, round((total_mb-free_mb)/total_mb*100)||'%' pct_used,
total_mb/1024 total_gb,round(free_mb/1024) free_gb 
from V$ASM_DISKGROUP;

ASM Archivelog Usage by DB

col path for a40
set line 200
SELECT substr(full_path,1,instr(full_path,'/',1,2)) path,
       round(sum(f.bytes)/1024/1024) sum_MB,
       count(*) qty
  FROM (SELECT CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
               group_number, file_number
         FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
                      a.reference_index rindex, a.group_number, a.file_number
                 FROM v$asm_alias a, v$asm_diskgroup g
                WHERE a.group_number = g.group_number)
        START WITH (MOD(pindex, POWER(2, 24))) = 0
        CONNECT BY PRIOR rindex = pindex) sub,
       v$asm_file f
 WHERE sub.group_number = f.group_number
   AND sub.file_number = f.file_number
   AND f.type = 'ARCHIVELOG'
 GROUP BY substr(full_path,1,instr(full_path,'/',1,2))
 ORDER BY 2;

Disk Usage by file/object name

select substr(al.name, 0, instr(al.name, '.')-1) Friendly_name, round(sum(a.bytes)/1024/1024, 2)
from v$asm_file a, v$asm_alias al 
where al.file_number = a.file_number and a.group_number = 2 
group by substr(al.name, 0, instr(al.name, '.')-1);

If a db has a restore point and you want to turn off flash back, it was slightly tricky on 10g. After few tries found that i had to drop the restore point first.


select flashback_on from v$database;

select name, scn, time, guarantee_flashback_database from v$restore_point;

DROP RESTORE POINT FRI_1120_TEST_RW;

alter database flashback off;

Drop files.


$ ORACLE_SID=+ASM; export ORACLE_SID

$ sqlplus "/ as sysdba"

SQL> ALTER DISKGROUP TESTDB_DATA2 DROP FILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837';