How to automatically copy archivelogs from ASM to local disc


This script will create folder on local disk, copy archivelogs of yesterday to this folder and compress folder with zip

#!/bin/bash
. /home/oracle/.bash_profile
cd /home/oracle/backup/rman/archivelogs/
mkdir `date -d "1 day ago" "+%Y_%m_%d"`
. /home/oracle/grid_env
for i in $(asmcmd ls +FRA/TESTDB/ARCHIVELOG/`date -d "1 day ago" "+%Y_%m_%d"`);
do asmcmd cp +FRA/TESTDB/ARCHIVELOG/`date -d "1 day ago" "+%Y_%m_%d"`/$i /home/oracle/backup/rman/archivelogs/`date -d "1 day ago" "+%Y_%m_%d"`;
done
zip -r `date -d "1 day ago" "+%Y_%m_%d"`.zip `date -d "1 day ago" "+%Y_%m_%d"`
rm -r `date -d "1 day ago" "+%Y_%m_%d"`

TOAD və s. proqramların production DB-da istifadəsini qadağan edən trigger


CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog 
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  
    AND  rownum = 1;  

  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR	-- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Kenar DB aletlerinden istifade qadaqandir!');
  END IF;
END;

Script for getting table size


[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

  SELECT segment_name "CEDVELIN ADI",
         SUM (bytes) / (1024 * 1024) "CEDVELIN OLCHUSU (MB)"
    FROM user_extents
   WHERE segment_type = 'TABLE' AND segment_name = UPPER ('&cedvel_adi')
GROUP BY segment_name

[/pcsh][pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

  SELECT segment_name "Table nameI",
         SUM (bytes) / (1024 * 1024) "Table size (MB)"
    FROM user_extents
   WHERE segment_type = 'TABLE' AND segment_name = UPPER ('&table_name')
GROUP BY segment_name

[/pcsh]

Ümumi baza ölçüsünü tapmaq


DB ölçüsünun boyük bir hissəsini datafile-lar təşkil edir. Bütün datafayllara nə qədər yer ayrıldığını bilmək üçün:

select sum(bytes)/1024/1024 "Umumi datafayl olchusu (MB)" from dba_data_files;

Bütün TEMP fayllarinin ölçüsünü əldə etmək üçün:

select nvl(sum(bytes),0)/1024/1024 "Umumi TEMP fayl olchusu (MB)" from dba_temp_files;

On-line redo-logların ülçüsünü əldə etmək üçün

select sum(bytes)/1024/1024 "ONline redo logs olchusu (MB)" from sys.v_$log;

Daha sonra bunların hamısını bir sorğu içinə yerləşdiririk və bazamızın ümumi ölçüsünü əldə edirik:

select a.data_size+b.temp_size+c.redo_size "Umumi olchu (MB)"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c

Script for getting Database size


DB ölçüsünun boyük bir hissəsini datafile-lar təşkil edir. Bütün datafayllara nə qədər yer ayrıldığını bilmək üçün:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

select sum(bytes)/1024/1024 "Umumi datafayl olchusu (MB)" from dba_data_files;

[/pcsh]

Bütün TEMP fayllarinin ölçüsünü əldə etmək üçün:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

select nvl(sum(bytes),0)/1024/1024 "Umumi TEMP fayl olchusu (MB)" from dba_temp_files;

[/pcsh]

On-line redo-logların ülçüsünü əldə etmək üçün

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

select sum(bytes)/1024/1024 "ONline redo logs olchusu (MB)" from sys.v_$log;

[/pcsh]

Daha sonra bunların hamısını bir sorğu içinə yerləşdiririk və bazamızın ümumi ölçüsünü əldə edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SELECT a.data_size + b.temp_size + c.redo_size "Umumi olchu (MB)"
  FROM (SELECT SUM (bytes) data_size FROM dba_data_files) a,
       (SELECT NVL (SUM (bytes), 0) temp_size FROM dba_temp_files) b,
       (SELECT SUM (bytes) redo_size FROM sys.v_$log) c

[/pcsh]DB ölçüsünun boyük bir hissəsini datafile-lar təşkil edir. Bütün datafayllara nə qədər yer ayrıldığını bilmək üçün:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

select sum(bytes)/1024/1024 "Umumi datafayl olchusu (MB)" from dba_data_files;

[/pcsh]

Bütün TEMP fayllarinin ölçüsünü əldə etmək üçün:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

select nvl(sum(bytes),0)/1024/1024 "Umumi TEMP fayl olchusu (MB)" from dba_temp_files;

[/pcsh]

On-line redo-logların ülçüsünü əldə etmək üçün

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

select sum(bytes)/1024/1024 "ONline redo logs olchusu (MB)" from sys.v_$log;

[/pcsh]

Daha sonra bunların hamısını bir sorğu içinə yerləşdiririk və bazamızın ümumi ölçüsünü əldə edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SELECT a.data_size + b.temp_size + c.redo_size "Total size (MB)"
  FROM (SELECT SUM (bytes) data_size FROM dba_data_files) a,
       (SELECT NVL (SUM (bytes), 0) temp_size FROM dba_temp_files) b,
       (SELECT SUM (bytes) redo_size FROM sys.v_$log) c

[/pcsh]

Script for getting used and free tablespace size


[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

  SELECT Total.name "Tablespace adi",
         NVL (Free_space, 0) "Bosh yer(MB)",
         NVL (total_space - Free_space, 0) "Istifade olunmushdur(MB)",
         total_space "Umumi olchu(MB)"
    FROM (  SELECT tablespace_name, SUM (bytes / 1024 / 1024) free_space
              FROM sys.dba_free_space
          GROUP BY tablespace_name) Free,
         (  SELECT b.name, SUM (bytes / 1024 / 1024) total_space
              FROM sys.v_$datafile a, sys.v_$tablespace B
             WHERE a.ts# = b.ts#
          GROUP BY b.name) Total
   WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

[/pcsh][pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

  SELECT Total.name "Tablespace name",
         NVL (Free_space, 0) "Free space(MB)",
         NVL (total_space - Free_space, 0) "Used space(MB)",
         total_space " Total space(MB)"
    FROM (  SELECT tablespace_name, SUM (bytes / 1024 / 1024) free_space
              FROM sys.dba_free_space
          GROUP BY tablespace_name) Free,
         (  SELECT b.name, SUM (bytes / 1024 / 1024) total_space
              FROM sys.v_$datafile a, sys.v_$tablespace B
             WHERE a.ts# = b.ts#
          GROUP BY b.name) Total
   WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

[/pcsh]

İstifadə olunmuş və boş tablespace həcminə baxmaq üçün script


SELECT Total.name "Tablespace adi",
       nvl( Free_space, 0) "Bosh yer(MB)",
       nvl( total_space- Free_space , 0) "Istifade olunmushdur(MB)" ,
       total_space "Umumi olchu(MB)"
FROM
  (select tablespace_name , sum( bytes /1024 / 1024) free_space
     from sys . dba_free_space
    group by tablespace_name
  ) Free ,
  (select b .name,   sum( bytes /1024 / 1024) total_space
     from sys . v_$datafile a, sys .v_$tablespace B
    where a. ts# = b .ts#
    group by b.name
  ) Total
WHERE Free. Tablespace_name (+) = Total .name
ORDER BY Total .name;

Insert into view with trigger


[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
SYSTEM@valeh> insert into emp_list values (1,'Valeh');
SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
SYSTEM@valeh> insert into emp_age values (1,1,26);
SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;

create or replace trigger view_trigger
instead of insert on emp_view
referencing new as new old as old
declare
v_id VARCHAR(20);
begin
if :new.id is not null then
insert into emp_list values (:new.id,:new.name);
insert into emp_age values (:new.age_id,:new.emp_id,:new.emp_age)
returning :new.id into v_id;
else
raise_application_error (-20999, 'Cannot create employee without name');
end if;
end;

SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

[/pcsh]

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
SYSTEM@valeh> insert into emp_list values (1,'Valeh');
SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
SYSTEM@valeh> insert into emp_age values (1,1,26);
SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;

create or replace trigger view_trigger
instead of insert on emp_view
referencing new as new old as old
declare
v_id VARCHAR(20);
begin
if :new.id is not null then
insert into emp_list values (:new.id,:new.name);
insert into emp_age values (:new.age_id,:new.emp_id,:new.emp_age)
returning :new.id into v_id;
else
raise_application_error (-20999, 'Cannot create employee without name');
end if;
end;

SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

[/pcsh]

2 cədvəlin birləşməsi olan VİEW-ya trigger vasitəsi ilə insert


SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
 SYSTEM@valeh> insert into emp_list values (1,'Valeh');
 SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
 SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;
create or replace trigger view_trigger
 instead of insert on emp_view
 referencing new as new old as old
 declare
 v_id VARCHAR(20);
 begin
 if :new.id is not null then
 insert into emp_list (id,name) values (:new.id,:new.name);
 insert into emp_age (age_id,emp_id,emp_age) values (:new.age_id,:new.emp_id,:new.emp_age)
 returning :new.id into v_id;
 else
 raise_application_error (-20999, 'Cannot create employee without name');
 end if;
 end;
 
SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;