Search This Blog

Total Pageviews

Monday 30 April 2018

Oracle How write into alert.log

Oracle How to write into alert.log


custom messages in alert log
package dbms_system.ksdwrt

        option 1- for trace file
        option 2- for alert log
        option 3- for both
connect / as sysdba

begin
    sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' --- Anuj test --- ');
end;
/

exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location'); 
exec dbms_system.ksdwrt(2, 'This message goes to the alert log');
exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location')

begin
    exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Anuj---******');
end;
/

or 

exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Anuj---******');
exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Start---******');
exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Start Failover Change No: 7777777 ---******');


Saturday 7 April 2018

Spfile asm in ASM 11gR2/12C

Spfile asm in ASM  11gR2/12C


How to find out spfile in +ASM
How to find out OCR in +ASM 


SQL> !olsnodes -c

olsnodes -c  <<<< To Find Out cluster Name 

set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = LOWER('&CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;

with date

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER,CREATION_DATE  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type,c.CREATION_DATE
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE','PARAMETERFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
            where a.group_number = b.group_number
            and (mod(a.parent_index, power(2, 24))) = 0
--          and a.name = LOWER('CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;


ASMCMD [+] > find --type PARAMETERFILE '*' '*'
ASMCMD [+] > spget
ASMCMD> spcopy +DATA/racluster/asmparameterfile/registry.253.792630659 /tmp/spfilecopyASM.ora
ASMCMD> spbackup +DATA....   +LOGS/spfileBackASM.bak

Oracle DBA

anuj blog Archive