Showing posts with label Oracle Routine DBA Scripts. Show all posts
Showing posts with label Oracle Routine DBA Scripts. Show all posts

Wednesday, 1 October 2014

Oracle Versiyonun Öğrenme

  SQL->  select * from v$version;
Posted by at No comments:
Labels:

Friday, 26 September 2014

Read Registry Value Using Java - Get Oracle Home From Regedit In Java

import java.io.*;

public class RegQuery {


    public static String executeRegistryQuery(String query, String regToke) {
        try {
            Process process = Runtime.getRuntime().exec(query);
            StreamReader reader = new StreamReader(process.getInputStream());

            reader.start();
            process.waitFor();
            reader.join();

            String result = reader.getResult();
            int p = result.indexOf(regToke);

            if (p == -1)
                return null;

            return result.substring(p + regToke.length()).trim();
        }
        catch (Exception e) {
            return null;
        }
    }


    static class StreamReader extends Thread {
        private InputStream is;
        private StringWriter sw;

        StreamReader(InputStream is) {
            this.is = is;
            sw = new StringWriter();
        }

        public void run() {
            try {
                int c;
                while ((c = is.read()) != -1)
                    sw.write(c);
            }
            catch (IOException e) { ; }
        }

        String getResult() {
            return sw.toString();
        }
    }

    public static void main(String s[]) {

        String REGQUERY_UTIL = "reg query ";
        String REGDWORD_TOKEN = "REG_DWORD";

        String PERSONAL_FOLDER_CMD = REGQUERY_UTIL +
                "\"HKCU\\Software\\Microsoft\\Windows\\CurrentVersion\\"
                + "Explorer\\Shell Folders\" /v Personal";
        String CPU_SPEED_CMD = REGQUERY_UTIL +
                "\"HKLM\\HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0\""
                + " /v ~MHz";
        String CPU_NAME_CMD = REGQUERY_UTIL +
                "\"HKLM\\HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0\""
                + " /v ProcessorNameString";

        String REGSTR_TOKEN = "REG_SZ";
        String key = "HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\KEY_OraDb11g_home1";
        String value = "ORACLE_HOME";
        String executeString =    REGQUERY_UTIL + "\"" + key + "\"" + " /v " + value;


        System.out.println("Query Result : "
                + executeRegistryQuery(executeString, REGSTR_TOKEN));
        System.out.println("Query Result : "
                + executeRegistryQuery(PERSONAL_FOLDER_CMD, REGSTR_TOKEN));
        System.out.println("Query Result : "
                + executeRegistryQuery(CPU_SPEED_CMD, REGDWORD_TOKEN));
        System.out.println("Query Result : "
                + executeRegistryQuery(CPU_NAME_CMD, REGSTR_TOKEN));
    }
}
Posted by at No comments:
Labels: ,

Friday, 19 September 2014

Tablespace free space and fragmentation 



      set linesize 150
 
     column tablespace_name format a20 heading 'Tablespace'
     column sumb format 999,999,999
     column extents format 9999
     column bytes format 999,999,999,999
     column largest format 999,999,999,999
     column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
     column Tot_Free format 999,999,999 heading 'Total Free(MB)'
     column Pct_Free format 999.99 heading '% Free'
     column Chunks_Free format 9999 heading 'No Of Ext.'
     column Max_Free format 999,999,999 heading 'Max Free(Kb)'
 
     set echo off
 
     PROMPT  FREE SPACE AVAILABLE IN TABLESPACES
 
     select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free,
     sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
     from
     (
     select tablespace_name,0 tots,sum(bytes) sumb,
     max(bytes) largest,count(*) chunks
     from dba_free_space a
     group by tablespace_name
     union
     select tablespace_name,sum(bytes) tots,0,0,0 from
      dba_data_files
     group by tablespace_name) a
     group by a.tablespace_name
     order by pct_free;
Posted by at No comments:
Labels:

Dumpig Oracle Database



Database
Dump database block

Demo provided by Richard Foote, Canberra, Australia
CREATE TABLE bowie_stuff (
album  VARCHAR2(30),
year   NUMBER,
rating VARCHAR2(30));

INSERT INTO bowie_stuff VALUES ('Man Who Sold The World', 1970, 'Bloody Good!!');

INSERT INTO bowie_stuff VALUES ('Diamond Dogs', 1974 , 'Brilliant');

INSERT INTO bowie_stuff VALUES ('Outside', 1995, 'Underrated Masterpiece');
COMMIT;

ALTER SYSTEM CHECKPOINT;

SELECT *
FROM bowie_stuff;

SELECT album, dbms_rowid.rowid_to_absolute_fno(rowid, 'UWCLASS', 'BOWIE_STUFF') ABSOLUTE_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO, dbms_rowid.rowid_row_number(rowid) ROWNUMBER
FROM bowie_stuff
WHERE album LIKE '%Dogs%';

ALTER SYSTEM DUMP DATAFILE 14 BLOCK 20238;

Trace file C:\APP\ORACLE\diag\rdbms\orabase2\orabase2\trace\orabase2_ora_8284.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 4 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:3425M/8075M, Ph+PgF:11352M/16148M
Instance name: orabase2
Redo thread mounted by this instance: 1
Oracle process number: 46
Windows thread id: 8284, image: ORACLE.EXE (SHAD)


*** 2014-01-05 10:17:07.304
*** SESSION ID:(246.1839) 2014-01-05 10:17:07.304
*** CLIENT ID:() 2014-01-05 10:17:07.304
*** SERVICE NAME:(pdbdev) 2014-01-05 10:17:07.304
*** MODULE NAME:(SQL*Plus) 2014-01-05 10:17:07.304
*** ACTION NAME:() 2014-01-05 10:17:07.304
*** CONTAINER ID:(4) 2014-01-05 10:17:07.304

Start dump data blocks tsn: 4 file#:14 minblk 20238 maxblk 20238
Block dump from cache:
Dump of buffer cache at level 4 for pdb=4 tsn=4 rdba=58740494
BH (0x7ff15f85a98) file#: 14 rdba: 0x03804f0e (14/20238) class: 1 ba: 0x7ff153c6000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 29,28
  dbwrid: 0 obj: 93311 objn: 93311 tsn: [4/4] afn: 14 hint: f
  hash: [0x7ff6fff2b10,0x7ff91b40628] lru: [0x7ff15f85cc8,0x7ff15f85a48]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x7ff15f85cf0,0x7ff8010fa28] objaq: [0x7ff15f85d00,0x7ff8010fa18]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x0.626ef5 tch: 5
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 4 rdba: 0x03804f0e (14/20238)
scn: 0x0.626ef6 seq: 0x01 flg: 0x06 tail: 0x6ef60601
frmt: 0x02 chkval: 0x0fd5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000015B46000 to 0x0000000015B48000
015B46000 0000A206 03804F0E 00626EF6 06010000 [.....O...nb.....]
015B46010 00000FD5 00000001 00016C7F 00626EF5 [.........l...nb.]
015B46020 00000000 00320002 03804F00 00100001 [......2..O......]
015B46030 00001311 01400C03 002605CC 00002003 [......@...&.. ..]
015B46040 00626EF6 00000000 00000000 00000000 [.nb.............]
015B46050 00000000 00000000 00000000 00000000 [................]
015B46060 00000000 00030100 0018FFFF 1F101F28 [............(...]
015B46070 00001F10 1F6C0003 1F281F4E 00000000 [......l.N.(.....]
015B46080 00000000 00000000 00000000 00000000 [................]
        Repeat 495 times
015B47F80 00000000 00000000 00000000 0703012C [............,...]
015B47F90 7374754F 03656469 166014C2 65646E55 [Outside...`.Unde]
015B47FA0 74617272 4D206465 65747361 65697072 [rrated Masterpie]
015B47FB0 012C6563 69440C03 6E6F6D61 6F442064 [ce,...Diamond Do]
015B47FC0 C2037367 42094B14 6C6C6972 746E6169 [gs...K.Brilliant]
015B47FD0 1603012C 206E614D 206F6857 646C6F53 [,...Man Who Sold]
015B47FE0 65685420 726F5720 C203646C 420D4714 [ The World...G.B]
015B47FF0 646F6F6C 6F472079 2121646F 6EF60601 [loody Good!!...n]
Block header dump: 0x03804f0e
 Object id on Block? Y
 seg/obj: 0x16c7f csc: 0x00.626ef5 itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x3804f00 ver: 0x01 opc: 0
     inc: 0 exflg: 0

 Itl           Xid                   Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.010.00001311   0x01400c03.05cc.26  --U-    3  fsc 0x0000.00626ef6
0x02   0x0000.000.00000000   0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x03804f0e
data_block_dump,data header at 0x15b46064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl:  0x15b46064
      76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f28
avsp=0x1f10
tosp=0x1f10
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f6c
0x14:pri[1]     offs=0x1f4e
0x16:pri[2]     offs=0x1f28
block_row_dump:
tab 0, row 0, @0x1f6c
tl: 44 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [22]
 4d 61 6e 20 57 68 6f 20 53 6f 6c 64 20 54 68 65 20 57 6f 72 6c 64
col 1: [ 3] c2 14 47
col 2: [13] 42 6c 6f 6f 64 79 20 47 6f 6f 64 21 21
tab 0, row 1, @0x1f4e
tl: 30 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [12] 44 69 61 6d 6f 6e 64 20 44 6f 67 73
col 1: [ 3] c2 14 4b
col 2: [ 9] 42 72 69 6c 6c 69 61 6e 74
tab 0, row 2, @0x1f28
tl: 38 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 7] 4f 75 74 73 69 64 65
col 1: [ 3] c2 14 60
col 2: [22]
 55 6e 64 65 72 72 61 74 65 64 20 4d 61 73 74 65 72 70 69 65 63 65
end_of_block_dump
End dump data blocks tsn: 4 file#: 14 minblk 20238 maxblk 20238
File Headers
ALTER SYSTEM SET EVENTS 'immediate trace name file_hdrs level 1';

-- does not appear to generate a file
Redlo Log File Headers
ALTER SYSTEM SET EVENTS 'immediate trace name redohdr level 1';

-- does not appear to generate a file
Control File

Level 1: file header
Level 2: database and checkpoint records
Level 3: circular reuse record types
Level 10: dump contents
col value format a50

SELECT value
FROM gv$parameter
WHERE name LIKE 'back%dump%dest';

ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';

[Click here to view trace file (116K)]

Instance
PGA (Process Global Area)
ORADEBUG DUMP HEAPDUMP 1

ALTER SESSION SET EVENTS 'immediate trace name global_area level 1';
SGA (System Global Area)
ORADEBUG DUMP HEAPDUMP 2

ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';
PGA + UGA
ORADEBUG DUMP HEAPDUMP 3

ALTER SESSION SET EVENTS 'immediate trace name global_area level 3';
UGA (User Global Area)
ORADEBUG DUMP HEAPDUMP 4

ALTER SESSION SET EVENTS 'immediate trace name global_area level 4';
PGA + UGA
ORADEBUG DUMP HEAPDUMP 5

ALTER SESSION SET EVENTS 'immediate trace name global_area level 5';
SGA + UGA
ORADEBUG DUMP HEAPDUMP 6

ALTER SESSION SET EVENTS 'immediate trace name global_area level 6';
PGA + SGA + UGA
ORADEBUG DUMP HEAPDUMP 7

ALTER SESSION SET EVENTS 'immediate trace name global_area level 7';
System State
ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE level 10';

ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE_CACHE OFF';

[Click here to view trace file (1.5M)]
Process State
ALTER SESSION SET EVENTS 'immediate trace name PROCESSSTATE level 10';

ALTER SESSION SET EVENTS 'immediate trace name PROCESSSTATE OFF';

[Click here to view trace file (347K)]
Library Cache
ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 10';

ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE OFF';

[Click here to view trace file (12K)]

Segments
Datafile Block
ALTER SYSTEM DUMP DATAFILE 6 BLOCK 21229;
Dumping an index tree including branch block headers, leaf block headers, and leaf block contents
col object_name format a30

SELECT object_name, object_id
FROM user_objects;

ALTER SESSION SET EVENTS 'immediate trace name treedump level 54220';
Alternative index dump
ORADEBUG DUMP TREEDUMP 54220;
Posted by at No comments:
Labels:

Dumping Redo Logs



To identify the current redo log use 
    SELECT member FROM v$logfile
    WHERE group# = 
    (
        SELECT group# FROM v$log
        WHERE status = 'CURRENT'
    );
To dump a redo log file use 
    ALTER SYSTEM DUMP LOGFILE 'FileName';
e.g. 
    ALTER SYSTEM DUMP LOGFILE 'R:\Oracle\Oradata\JD92001\Redo01.log';
The syntax of this statement is as follows 
    ALTER SYSTEM DUMP LOGFILE 'FileName'
        SCN MIN MinimumSCN
        SCN MAX MaximumSCN
        TIME MIN MinimumTime
        TIME MAX MaximumTime
        LAYER Layer
        OPCODE Opcode
        DBA MIN FileNumber . BlockNumber
        DBA MAX FileNumber . BlockNumber
        RBA MIN LogFileSequenceNumber . BlockNumber
        RBA MAX LogFileSequenceNumber . BlockNumber;
The minimum and maximum SCN is a decimal number
The minimum and maximum time is a decimal number representing the number of seconds since midnight 01Jan1988. These are calculated using the following formula 
    time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 
           60 + mi) * 60 + ss;
where

yyyy
Year
&nbsp
mm
month
01-12
dd
day
01-31
hh
hour
00-23
mi
minute
00-59
ss
second
00-59
This is the same formula that is used to represent time within the redo log
The layer and opcode are those used to indicate specific operations within the redo log e.g. LAYER 5 OPCODE 4 is an undo segment header commit operation
Note that there must be spaces around the periods in the DBA and RBA.
See Metalink Note 1031381.6 for further examples

Posted by at No comments:
Labels:

Create Control File


If control file exists, then :

alter database backup controlfile to trace as   '%PATH%\CREATE_CONTROL.sql' ;


Else, you should specify some parameters manually :

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "demo" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
LOGFILE
  GROUP 1 '/path/oracle/dbs/t_log1.f'  SIZE 500K,
  GROUP 2 '/path/oracle/dbs/t_log2.f'  SIZE 500K
# STANDBY LOGFILE
DATAFILE
  '/path/oracle/dbs/t_db1.f',
  '/path/oracle/dbs/dbu19i.dbf',
  '/path/oracle/dbs/tbs_11.f',
  '/path/oracle/dbs/smundo.dbf',
  '/path/oracle/dbs/demo.dbf'
CHARACTER SET WE8DEC
;
Posted by at No comments:
Labels:

Thursday, 18 September 2014

Database structure and file location 



set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name  format a60 heading "Control Files"
 
select name
from   sys.v_$controlfile
/
 
Prompt
Prompt Redo Log File Locations >>>>
Prompt
 
col Grp    format 9999
col member format a50 heading "Online REDO Logs"
col File#  format 9999
col name   format a50 heading "Online REDO Logs"
break on Grp
select group#,member
from   sys.v_$logfile
/
 
 
Prompt Data Files Locations >>>>
 
col Tspace    format a25
col status    format a3  heading Sta
col Id        format 9999
col Mbyte     format 999999999
col name      format a50 heading "Database Data Files"
col Reads     format 99,999,999
col Writes    format 99,999,999
 
break on report
compute sum label 'Total(MB)'  of Mbyte  on report
 
select F.file_id Id,
       F.file_name name,
       F.bytes/(1024*1024) Mbyte,
       decode(F.status,'AVAILABLE','OK',F.status) status,
       F.tablespace_name Tspace
from   sys.dba_data_files F
order by tablespace_name;
 
 
 
 
 
Control Files Location >>>>
 
Control Files
------------------------------------------------------------
/u03/oradata/rcatp/control01.ctl
/u05/oradata/rcatp/control02.ctl
 
 
Redo Log File Locations >>>>
 
 
    GROUP# Online REDO Logs
---------- --------------------------------------------------
         1 /u03/oradata/rcatp/redo01.log
         2 /u05/oradata/rcatp/redo02.log
         3 /u03/oradata/rcatp/redo03.log
         3 /u05/oradata/rcatp/redo03b.log
         1 /u05/oradata/rcatp/redo01b.log
         2 /u03/oradata/rcatp/redo02b.log
 
6 rows selected.
 
Data Files Locations >>>>
 
   ID Database Data Files                                     MBYTE Sta TSPACE
----- -------------------------------------------------- ---------- --- -------------------------
    9 /u03/oradata/rcatp/patrol01.dbf                            20 OK  PATROL
    7 /u03/oradata/rcatp/rman10p01.dbf                          466 OK  RMAN10P
    5 /u03/oradata/rcatp/rman11p01.dbf                          200 OK  RMAN11P
    8 /u03/oradata/rcatp/rman9p01.dbf                           106 OK  RMAN9P
    3 /u03/oradata/rcatp/sysaux01.dbf                           540 OK  SYSAUX
    1 /u03/oradata/rcatp/system01.dbf                           700 OK  SYSTEM
    2 /u03/oradata/rcatp/undotbs01.dbf                          220 OK  UNDOTBS1
    4 /u03/oradata/rcatp/users01.dbf                              6 OK  USERS
                                                         ----------
sum                                                            2258
 
8 rows selected.
 
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)