Skip to content

Create restore point without sysdba role

Create restore point without sysdba role published on Комментариев к записи Create restore point without sysdba role нет

create procedure to create\drop restore point:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
create or replace procedure   sys.restore_point_proc ( g_operation in varchar2 DEFAULT 'LIST', g_rp_name in varchar2 DEFAULT NULL)
as
l_rp_check varchar2(20);
e_rp_name_t EXCEPTION;
e_rp_name_f EXCEPTION;
e_not_valid_input EXCEPTION;
e_rp_check EXCEPTION;
begin
 
 
select count(*) into l_rp_check from v$restore_point where upper(name)=upper(g_rp_name);
  
CASE upper(g_operation) 
    when 'CREATE' THEN
        if l_rp_check >0 then
          dbms_output.put_line ('Restore point already exists');
          raise e_rp_name_t;
      end if;
      dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',1,'/oracle/scripts/create_restore_point.sh'); 
      dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',2,g_rp_name);  
      dbms_scheduler.run_job('RESTORE_POINT_JOB');
      select count(*) into l_rp_check from v$restore_point where upper(name)=upper(g_rp_name);
          if l_rp_check =0 then
          dbms_output.put_line ('Restore point not created');
          raise e_rp_check;
      end if;
    WHEN 'DELETE' THEN
     if l_rp_check =0 then
          dbms_output.put_line ('Restore point does not exists');
          raise e_rp_name_f;
     end if;  
      dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',1,'/oracle/scripts/drop_restore_point.sh'); 
      dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',2,g_rp_name);  
      dbms_scheduler.run_job('RESTORE_POINT_JOB');
      select count(*) into l_rp_check from v$restore_point where upper(name)=upper(g_rp_name);
      if l_rp_check >0 then
          dbms_output.put_line ('Restore point not deleted');
          raise e_rp_check;
      end if;
     WHEN 'LIST' THEN 
    for rec in ( select name,GUARANTEE_FLASHBACK_DATABASE,TIME as time# from sys.v_$restore_point)
      loop
          dbms_output.put_line ('name: '||rec.name||' GUARANTEE:' ||rec.GUARANTEE_FLASHBACK_DATABASE||' creation time:' ||rec.time# );
      end loop;
ELSE
RAISE e_not_valid_input;
END CASE;
EXCEPTION
   WHEN e_rp_name_t         THEN    RAISE_APPLICATION_ERROR(-20001,'Restore point already exist');
   WHEN e_rp_name_f         THEN    RAISE_APPLICATION_ERROR(-20001,'Restore point does not exist');
   WHEN e_not_valid_input   THEN    RAISE_APPLICATION_ERROR(-20001,'Input must be CREATE or DELETE');
   WHEN e_rp_check          THEN    RAISE_APPLICATION_ERROR(-20001,'Operation with restore point did not succeed');
END;

create scheduler job to call os scripts

1
2
3
4
5
6
7
8
9
10
begin
dbms_scheduler.create_job (
        job_name        =>'RESTORE_POINT_JOB',
        job_type        =>'executable',
        job_action      =>'/bin/sh',
        number_of_arguments=>2,
        auto_drop       =>false,
        enabled         =>false);
end;
  /

create and assign credentials and pin scheduler job to first node ( where scripts placed )

1
2
3
exec    sys.dbms_scheduler.create_credential(        username => 'oracle',        password => 'OS_ORACLE_PASSWORD',        credential_name => '"SYS"."RP_CREDENTIAL"' );
exec  DBMS_SCHEDULER.set_attribute( name => '"SYS"."RESTORE_POINT_JOB"', attribute => 'credential_name', value => '"SYS"."RP_CREDENTIAL"');
exec dbms_scheduler.set_attribute('RESTORE_POINT_JOB','instance_id',1);

drop_restore_point.sh

1
2
3
4
5
6
7
#!/bin/bash
. /home/oracle/.bash_profile
 
sqlplus / as sysdba << EOF
drop restore point $1;
exit;
EOF

create_restore_point.sh

1
2
3
4
5
6
7
#!/bin/bash
. /home/oracle/.bash_profile
 
sqlplus / as sysdba << EOF
create restore point $1 guarantee flashback database;
exit;
EOF

so, after all create and delete restore point looks like :

1
2
exec sys.restore_point_proc('CREATE','BEFORE_RELEASE_TEST4');
exec sys.restore_point_proc('DELETE','BEFORE_RELEASE_TEST4');

oracle restart single change hostname

oracle restart single change hostname published on Комментариев к записи oracle restart single change hostname нет

deconfig

1
$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl -deconfig -force

config

1
$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl

add resources
change hostname in
$ORACLE_HOME/netowrk/admin/listener.ora

grid user

1
2
3
4
5
srvctl add listener
srvctl add asm -d '/dev/oracleasm/disks/*'
 
srvctl start asm
srvctl start listener

oracle user:

1
srvctl add database -d $ORACLE_SID -o $ORACLE_HOME

find current effective hidden param

find current effective hidden param published on Комментариев к записи find current effective hidden param нет
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
col PARAMETER format a20
col SESSION_VALUE format a20
col INSTANCE_VALUE format a20
col DEFAULT_VALUE format a20
col DESCRIPTION format a100
select a.ksppinm as Parameter,
b.ksppstvl Session_Value,
c.ksppstvl Instance_Value ,
b.ksppstdf Default_value,
decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,
a.ksppdesc Description
from sys.x$ksppi a, sys.x$ksppcv b , sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx
and REGEXP_LIKE (ksppinm, '^\_[^\_]')
and lower(b.ksppstdf) !='true'
order by a.ksppinm;

script to analyze partition tables data distribution

script to analyze partition tables data distribution published on Комментариев к записи script to analyze partition tables data distribution нет
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select table_owner||'.'||table_name,partition_name,100* (round (num_rows/ case when
        sum(num_rows) over( partition by table_name) >0 then sum(num_rows) over( partition by table_name)
        else 1
        end       
        ,3)) as pct_of_rows
        ,num_rows, sum(num_rows) over( partition by table_name)  total_row_cnt
        from dba_tab_partitions
    where table_name in
    ( select table_name from DBA_PART_TABLES dpt where dpt.owner not like 'SYS%' and dpt.interval is null  and dpt.partitioning_type like '%RANGE%' )
order by 1,3 desc nulls last;
 
TABLE_NAME                               | PARTITION_NAME                 | PCT_OF_ROWS |   NUM_ROWS | TOTAL_ROW_CNT
---------------------------------------- | ------------------------------ | ----------- | ---------- | -------------
XXXX.OPN_HIS                             | P_MAX                          |         100 |   63308846 |      63309145
XXXX.OPN_HIS                             | P_2012_09                      |           0 |          2 |      63309145
XXXX.OPN_HIS                             | P_2012_10                      |           0 |        297 |      63309145
XXXX.OPN_HIS                             | P_2012_08                      | <NULL>      | <NULL>     |      63309145

disk latency from ash by time

disk latency from ash by time published on 1 комментарий к записи disk latency from ash by time

some queryes to measure disk io

1
2
select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(wait_class,'CPU') wait_class from v$active_session_history group by wait_class order by 1 desc;
select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(event,'CPU') event from v$active_session_history where wait_class like '%I/O%' group by event order by 1 desc;

generate pivot list

1
select listagg( ''''||event||'''',',') within group (order by event )from v$active_session_history where wait_class like '%I/O%' group by event ;

or top wait pivot list:

1
2
3
with disk_events as  (
  select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(event,'CPU') event from v$active_session_history where wait_class like '%I/O%' group by event )
 select listagg( ''''||event||'''',',') within group (order by event )from disk_events where pct >5 ;

query and result

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
new:select * from
(select event,case when est_waits >0 
      then  round (est_dbtime_ms / est_waits,1)
               
                else null
                end
  as est_avg_latency_ms,
    time#
from (
      select event,
        round(
              sum(
                    case when time_waited >0
                    then greatest(1,1000000/time_waited)
                            else 0 end )
            ) as est_waits,
      sum(1000) as est_dbtime_ms ,
 TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), 5) /(24 * 60) time#
from v$active_session_history ash
  where ash.wait_class ='User I/O'
      group by TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM SAMPLE_TIME), 5) /(24 * 60) ,event
      )    )
 pivot ( sum(est_avg_latency_ms) for event in (
 'db file scattered read','db file sequential read','log file parallel write')
 ) order by time#
 
TIME#               |                'db file scattered read' |               'db file sequential read' |               'log file parallel write'
------------------- | --------------------------------------- | --------------------------------------- | ---------------------------------------
10.08.2015 14.10.00 |                                     2,2 |                                      ,3 |                                  <NULL>
10.08.2015 14.15.00 |                                     2,4 |                                      ,2 |                                  <NULL>
10.08.2015 14.20.00 |                                     1,7 |                                      ,3 |                                  <NULL>
10.08.2015 14.25.00 |                                     1,3 |                                      ,3 |                                  <NULL>
10.08.2015 14.30.00 |                                     3,5 |                                      ,2 |                                  <NULL>
10.08.2015 14.35.00 |                                     1,8 |                                      ,3 |                                  <NULL>
10.08.2015 14.40.00 |                                     1,1 |                                      ,3 |                                  <NULL>
10.08.2015 14.45.00 |                                     2,6 |                                      ,2 |                                  <NULL>
10.08.2015 14.50.00 |                                     1,5 |                                      ,3 |                                  <NULL>
10.08.2015 14.55.00 |                                     1,2 |                                      ,3 |                                  <NULL>
10.08.2015 15.00.00 |                                     2,6 |                                      ,2 |                                  <NULL>
10.08.2015 15.05.00 |                                     1,3 |                                      ,4 |                                  <NULL>
10.08.2015 15.10.00 |                                     1,6 |                                      ,3 |                                  <NULL>
10.08.2015 15.15.00 |                                       3 |                                      ,2 |                                  <NULL>
10.08.2015 15.20.00 |                                     1,4 |                                      ,3 |                                  <NULL>
10.08.2015 15.25.00 |                                     1,6 |                                      ,3 |                                  <NULL>
10.08.2015 15.30.00 |                                     3,1 |                                      ,2 |                                  <NULL>
10.08.2015 15.35.00 |                                     1,8 |                                      ,4 |                                  <NULL>
10.08.2015 15.40.00 |                                     1,2 |                                      ,3 |                                  <NULL>
10.08.2015 15.45.00 |                                     2,5 |                                      ,2 |                                  <NULL>
10.08.2015 15.50.00 |                                     1,6 |                                      ,3 |                                  <NULL>
10.08.2015 15.55.00 |                                     1,4 |                                      ,3 |                                  <NULL>
10.08.2015 16.00.00 |                                     4,8 |                                      ,2 |                                  <NULL>
10.08.2015 16.05.00 |                                     1,8 |                                      ,4 |                                  <NULL>
10.08.2015 16.10.00 |                                     1,2 |                                      ,4 |                                  <NULL>
10.08.2015 16.15.00 |                                       3 |                                      ,2 |                                  <NULL>
10.08.2015 16.20.00 |                                     1,3 |                                      ,3 |                                  <NULL>
10.08.2015 16.25.00 |                                     1,1 |                                      ,3 |                                  <NULL>
10.08.2015 16.30.00 |                                     3,1 |                                      ,2 |                                  <NULL>
10.08.2015 16.35.00 |                                     1,6 |                                      ,3 |                                  <NULL>
10.08.2015 16.40.00 |                                     1,6 |                                      ,3 |                                  <NULL>
10.08.2015 16.45.00 |                                     2,9 |                                      ,2 |                                  <NULL>
10.08.2015 16.50.00 |                                     1,6 |                                      ,3 |                                  <NULL>
10.08.2015 16.55.00 |                                     1,3 |                                      ,2 |                                  <NULL>
10.08.2015 17.00.00 |                                     2,2 |                                      ,2 |                                  <NULL>
10.08.2015 17.05.00 |                                     1,5 |                                      ,4 |                                  <NULL>
10.08.2015 17.10.00 |                                     1,7 |                                      ,3 |                                  <NULL>
10.08.2015 17.15.00 |                                       3 |                                      ,2 |                                  <NULL>
10.08.2015 17.20.00 |                                     1,8 |                                      ,3 |                                  <NULL>
10.08.2015 17.25.00 |                                     1,3 |                                      ,3 |                                  <NULL>
10.08.2015 17.30.00 |                                     3,3 |                                      ,3 |                                  <NULL>
10.08.2015 17.35.00 |                                     1,6 |                                      ,3 |                                  <NULL>
10.08.2015 17.40.00 |                                      ,7 |                                      ,3 |                                  <NULL>

this output can be used to build graphs:

Снимок экрана 2015-08-10 в 17.38.43

audit purge

audit purge published on Комментариев к записи audit purge нет

short way truncate table sys.aud$ =)))

1
2
3
4
5
6
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24 /* hours */);
END;
/
1
2
3
4
5
6
7
8
9
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/
1
2
3
4
5
6
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-31);
END;
/

create purge audit job

1
2
3
4
5
6
7
8
9
--create STANDARD_AUDIT_TRAIL_PURGE_JOB
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Or audit all purge job:

1
2
3
4
5
6
7
8
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'ALL_Audit_Trail_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

create job to update what can job delete from audit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
      ,start_date      => trunc (SYSTIMESTAMP)
      ,repeat_interval => 'FREQ=DAILY;INTERVAL=1'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => '
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   last_archive_time => SYSTIMESTAMP-31);
'
      ,comments        => NULL
    );
end;
/
 
BEGIN
DBMS_SCHEDULER.set_attribute( name => '"SYS"."MOVE_LAST_TIMESTAMP_FORWARD"', attribute => 'auto_drop', value => FALSE);
DBMS_SCHEDULER.enable(name=>'"SYS"."MOVE_LAST_TIMESTAMP_FORWARD"');
END;
/

SELECT * FROM dba_audit_mgmt_config_params;
SELECT * FROM dba_audit_mgmt_last_arch_ts;
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

Standard_Audit_Trail_Purge_Job

STOP:

1
2
3
4
5
6
BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'STANDARD_AUDIT_TRAIL_PURGE_JOB',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);
end;
/

Drop:

1
2
3
4
BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
  AUDIT_TRAIL_PURGE_NAME  => 'STANDARD_AUDIT_TRAIL_PURGE_JOB');
END;

Manual run:

1
2
3
4
5
BEGIN
  DBMS_AUDIT_MGMT.clear_last_archive_timestamp(
    audit_trail_type     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/

list of DBMS_AUDIT_MGMT. —>AUDIT_TRAIL_ALL<-- can be found here
almost all material from oracle-base.com

postgrespro pg_stat_wait patch

postgrespro pg_stat_wait patch published on Комментариев к записи postgrespro pg_stat_wait patch нет

usefull materials
github postgrespro
Waits monitoring on mail list of postgresql.org
pgconf2015 slides

Packages

ubuntu:

1
apt-get install gcc libreadline-dev zlib1g-dev libxml2-dev libldap2-dev python-dev libssl-dev build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libperl-dev

fedora:

1
yum install -y gcc bison-devel flex bison readline-devel zlib-devel openssl-devel wget perl-ExtUtils-MakeMaker perl-ExtUtils-Embed  readline-devel zlib-devel libxml2-devel openldap-devel python-devel openssl-devel

Install from source

1
2
3
4
5
6
7
8
9
10
11
mkdir /postgres/src -p
cd /postgres/src
wget https://github.com/postgrespro/postgres/archive/waits_monitoring_94.zip
unzip waits_monitoring_94.zip
cd postgres-waits_monitoring_94/
./configure --with-ldap --with-perl --with-python --with-openssl --with-libxml --prefix=/usr/pgsql-pro --exec-prefix=/usr/pgsql-pro
make
make install
/postgres/src/postgres-waits_monitoring_94/contrib/pg_stat_wait
make
make install
1
2
groupadd postgres -g 26
useradd --uid 26 --gid postgres --groups postgres -d /postgres -s /bin/bash -c "PostgreSQL Software Owner" postgres

enable waits monitoring

add to postgresql.conf

1
2
3
4
5
6
7
#History GUC parameters:
 
 shared_preload_libraries = 'pg_stat_wait.so' #for background worker that will be sample waits.
 pg_stat_wait.history = on #/off - turn on/off history recording
 pg_stat_wait.history_size = 1000 #how many records keep in history
 pg_stat_wait.history_period = 100 # period in millseconds between the sampling
waits_monitoring=on

### alter system set waits_monitoring=on;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
postgres=# select * from pg_stat_wait_current;
  pid  |          sample_ts           | class_id | class_name | event_id | event_name | wait_time  |  p1  |  p2   |  p3   | p4 |   p5
-------+------------------------------+----------+------------+----------+------------+------------+------+-------+-------+----+--------
 19213 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        0 | READ       |    2971593 | 1663 | 13056 | 12817 |  0 |      6
 19061 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        0 | READ       |        622 | 1663 | 13056 | 12810 |  0 |     44
 19054 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        1 | WRITE      |      47717 | 1663 | 16418 | 16431 |  0 | 165053
 19056 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |      15808 |    0 |     0 |     0 |  0 |      0
 19059 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |       3179 |    0 |     0 |     0 |  0 |      0
 19057 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |    2971267 |    0 |     0 |     0 |  0 |      0
 19055 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |     157947 |    0 |     0 |     0 |  0 |      0
 19206 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        544 |    0 |     0 |     0 |  0 |      0
 19204 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       1958 |    0 |     0 |     0 |  0 |      0
 19209 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        380 |    0 |     0 |     0 |  0 |      0
 19062 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       | 2726490525 |    0 |     0 |     0 |  0 |      0
 19201 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        704 |    0 |     0 |     0 |  0 |      0
 19203 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       2002 |    0 |     0 |     0 |  0 |      0
 19208 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       1625 |    0 |     0 |     0 |  0 |      0
 19210 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        999 |    0 |     0 |     0 |  0 |      0
 19205 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        248 |    0 |     0 |     0 |  0 |      0
 19202 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        648 |    0 |     0 |     0 |  0 |      0
 19207 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        718 |    0 |     0 |     0 |  0 |      0

ps: Many thanks to Vladimir Borodin for help !

awr report by hour

awr report by hour published on Комментариев к записи awr report by hour нет

simplest way to generate awr report by hour after benchmark
export awr

1
@?/rdbms/admin/awrextr.sql

import awr

1
@?/rdbms/admin/awrload.sql

— make sure to set line size appropriately
— set linesize 152

generate awr

1
2
3
4
5
6
7
set termout off
set linesize 80
set pagesize 10000
select 'spool awrrpt_dwhfrn_'|| snap_id ||'.html'|| chr(13)||
 'select output from table(dbms_workload_repository.awr_report_text('||dbid||',1,'||snap_id||','|| LEAD (snap_id, 1)  OVER (ORDER BY  dbid,snap_id ) ||',0));' || chr(13)||
 ' spool off '
 from dba_hist_snapshot where dbid !=1399642255 and extract( minute from begin_interval_time ) <2 ;

virtualbox shared storage create on linux

virtualbox shared storage create on linux published on Комментариев к записи virtualbox shared storage create on linux нет

create shared storage:
data:

1
for i in {1..10};do vboxmanage createhd --filename asm_$i --size 10240 --format VDI --variant Fixed; done

OCR:

1
for i in {1..3};do vboxmanage createhd --filename vote_$i --size 1200 --format VDI --variant Fixed; done

make disk shareable:

1
for i in `ls -1 *.vdi`; do vboxmanage modifyhd $i --type shareable; done

add disk to virtual machine

1
2
c=1; for i in `ls rac*.vdi -1` ; do c=$((c+=1)) echo vboxmanage storageattach oel6-1 --storagectl "SATA" --port $c --type hdd --medium $i --mtype shareable; done
c=1; for i in `ls rac*.vdi -1` ; do c=$((c+=1))  vboxmanage storageattach oel6-1 --storagectl "SATA" --port $c --type hdd --medium $i --mtype shareable; done

make disk partitions:
1. generate create partion table :

1
fdisk -l | grep GB | awk '{ print "parted -s " $2 " mklabel  gpt"}' | sed 's/://g

need to automate create partition script

1
echo -e "-1" | (parted -a optimal /dev/sdb mkpart primary 1 )

no free vm to test

2. generate create asm disk:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[root@oel6-1 ~]# fdisk -l | grep 10.7 | awk '{ print "oracleasm createdisk RAC_ASM"NR " " $2 "1"  }' | sed 's/://g'
oracleasm createdisk RAC_ASM1 /dev/sdb1
oracleasm createdisk RAC_ASM2 /dev/sdc1
oracleasm createdisk RAC_ASM3 /dev/sdd1
oracleasm createdisk RAC_ASM4 /dev/sde1
oracleasm createdisk RAC_ASM5 /dev/sdf1
oracleasm createdisk RAC_ASM6 /dev/sdg1
oracleasm createdisk RAC_ASM7 /dev/sdh1
oracleasm createdisk RAC_ASM8 /dev/sdi1
oracleasm createdisk RAC_ASM9 /dev/sdj1
oracleasm createdisk RAC_ASM10 /dev/sdk1
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM2 /dev/sdc1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM3 /dev/sdd1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM4 /dev/sde1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM5 /dev/sdf1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM6 /dev/sdg1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM7 /dev/sdh1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM8 /dev/sdi1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM9 /dev/sdj1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM10 /dev/sdk1
Writing disk header: done
Instantiating disk: done

Normal storage how to:

1
2
for i in {1..5};do vboxmanage createhd --filename stdb1_asm_$i --size 10240 --format VDI ; done
c=1; for i in `ls stdb1_asm*.vdi -1` ; do c=$((c+=1))  vboxmanage storageattach stdb1 --storagectl "SATA" --port $c --type hdd --medium $i ; done

offline oracle agent install on windows

offline oracle agent install on windows published on 2 комментария к записи offline oracle agent install on windows

get list of supported platform:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[oracle@monitoring tmp]$  emcli get_supported_platforms
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Linux x86-64
-----------------------------------------------
Version = 12.1.0.4.0
 Platform = Microsoft Windows x64 (64-bit)
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Oracle Solaris on SPARC (64-bit)
-----------------------------------------------
Version = 12.1.0.4.0
 Platform = Linux x86-64
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Microsoft Windows (32-bit)
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Microsoft Windows x64 (64-bit)
-----------------------------------------------
Platforms list displayed successfully.

get agent :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[oracle@monitoring tmp]$ emcli get_agentimage -destination=/tmp/win_agent -platform="Microsoft Windows x64 (64-bit)" -version=12.1.0.4.0
 === Partition Detail ===
Space free : 1 GB
Space required : 1 GB
Check the logs at /oracle/.emcli/get_agentimage_2015-04-13_18-42-53-PM.log
Downloading /tmp/win_agent/12.1.0.4.0_AgentCore_233.zip
File saved as /tmp/win_agent/12.1.0.4.0_AgentCore_233.zip
Downloading /tmp/win_agent/12.1.0.4.0_PluginsOneoffs_233.zip
File saved as /tmp/win_agent/12.1.0.4.0_PluginsOneoffs_233.zip
Downloading /tmp/win_agent/unzip
File saved as /tmp/win_agent/unzip
ERROR: Command /tmp/win_agent/unzip /tmp/win_agent/12.1.0.4.0_AgentCore_233.zip agentcoreimage.zip -d /tmp/win_agent execution failed.
RECOMMENDATION: If your agent image platform and the emcli client setup is on a different platform then you need to set the environment variable ZIP_LOC to absolute path to the zip utility which is greater than or equal to version 3.0.
Example: setenv ZIP_LOC /usr/local/bin/zip
[oracle@monitoring tmp]$

copy to destination & install:

C:\Users\sql> C:\oracle_agent\distrib\agentDeploy.bat AGENT_BASE_DIR=c:\oracle_agent\agent12c4 RESPONSE_FILE=C:\oracle_agent\distrib\agent3.rsp -ignorePrereqs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
C:\Users\sql> C:\oracle_agent\distrib\agentDeploy.bat AGENT_BASE_DIR=c:\oracle_agent\agent12c4 RESPONSE_FILE=C:\oracle_agent\distrib\agent3.rsp -ignorePrereqs
C:\oracle_agent\distrib
Present working directory:C:\oracle_agent\distrib
Archive location:C:\oracle_agent\distrib  directory
AGENT_BASE_DIR
AGENT_BASE_DIR
c:\oracle_agent\agent12c4
Agent base directory:c:\oracle_agent\agent12c4
c:\oracle_agent\agent12c4
RESPONSE_FILE
C:\oracle_agent\distrib\agent3.rsp
-ignorePrereqs
Режим вывода команд на экран (ECHO) отключен.
Agent base directory:c:\oracle_agent\agent12c4
OMS Host:
Agent image loc :"C:\oracle_agent\distrib"
c:\oracle_agent\agent12c4 configonlyfalse
Reading the properties file: "C:\oracle_agent\distrib"\agentimage.properties
Скопировано файлов:         1.
This is the version 12.1.0.4.0
This is the type core
This is the aru id 233
"Validating the OMS_HOST &amp; EM_UPLOAD_PORT"
"c:\oracle_agent\agent12c4\core\12.1.0.4.0"
"Executing command : c:\oracle_agent\agent12c4\core\12.1.0.4.0\jdk\bin\java -classpath c:\oracle_agent\agent12c4\core\12.1.0.4.0\jlib\agentInstaller.jar:c:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\jlib\OraInstaller.jar oracle.sysman.agent.installer.AgentInstaller c:\or
 
Validating oms host &amp; port with url: http://192.168.226.20:4900/empbs/genwallet
Validating oms host &amp; port with url: https://192.168.226.20:4900/empbs/genwallet
Return status:3-oms https port is passed
"C:\oracle_agent\distrib"\12.1.0.4.0_PluginsOneoffs_233.zip
"Executing command : c:\oracle_agent\agent12c4\core\12.1.0.4.0\jdk\bin\java -classpath c:\oracle_agent\agent12c4\core\12.1.0.4.0\jlib\OraInstaller.jar:c:\oracle_agent\agent12c4\core\12.1.0.4.0\sysman\jlib\emInstaller.jar:c:\oracle_agent\agent12c4\core\12.1.0.4.0\jlib\xml
 
Cloning the agent home...
Executing command: cmd /c C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\setup.exe -debug -noconsole -ignoreSysPrereqs   -clone -forceClone -silent -waitForCompletion -nowait ORACLE_HOME=C:\oracle_agent\agent12c4\core\12.1.0.4.0 -responseFile C:\oracle_agent\distrib\a
 
Cloning of agent home completed successfully
 
Attaching sbin home...
Executing command: cmd /c C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\setup.exe -debug -noconsole -ignoreSysPrereqs   -attachHome -waitForCompletion -nowait ORACLE_HOME=C:\oracle_agent\agent12c4\sbin ORACLE_HOME_NAME=sbin12c1 -force
 
Attach home for sbin home completed successfully.
 
Updating home dependencies...
Executing command: cmd /c C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\setup.exe -debug -noconsole -ignoreSysPrereqs   -updateHomeDeps -waitForCompletion HOME_DEPENDENCY_LIST={C:\oracle_agent\agent12c4\sbin:C:\oracle_agent\agent12c4\core\12.1.0.4.0} -invPtrLoc C:\or
 
Update home dependency completed successfully.
Executing command: C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\runConfig.bat ORACLE_HOME=C:\oracle_agent\agent12c4\core\12.1.0.4.0 RESPONSE_FILE=c:\oracle_agent\agent12c4\core\12.1.0.4.0\agent.rsp ACTION=configure MODE=perform COMPONENT_XML={oracle.sysman.top.agent
 
ERROR: Agent Configuration Failed SEVERE:emctl secure agent command has failed with status=1SEVERE:emctl secure agent command has failed with status=1SEVERE:emctl secure agent command has failed with status=1
 
Agent Deploy Log Location:c:\oracle_agent\agent12c4\core\12.1.0.4.0\cfgtoollogs\agentDeploy\agentDeploy_2015-06-25_12-40-40-PM.log

fix local host file to include hostname for CC12 host

after

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
C:\Users\sql> c:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent is Not Running
 
C:\Users\sql> ;c:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Служба "Oracleagent12cAgent" запускается.............
Служба "Oracleagent12cAgent" успешно запущена.
 
C:\Users\sql> c:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl secure agent truepasswd
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Agent successfully stopped...   Done.
Securing agent...   Started.
Agent successfully restarted...   Done.
Securing agent...   Successful.
 
 
C:\Users\sql>C:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl config agent addinternaltargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
 
C:\Users\sql>;

extract from rsp file:

1
2
3
4
5
6
7
8
9
10
11
RESPONSEFILE_VERSION=2.2.1.0.0
s_agentHomeName="agent12gR1"
OMS_HOST=monitoring.sys.local
EM_UPLOAD_PORT=4900
AGENT_REGISTRATION_PASSWORD=truepasswd
AGENT_INSTANCE_HOME=C:\oracle_agent\agent12c4\
AGENT_PORT=3872
b_startAgent=true
s_agentHomeName=agent12c4_home
s_agentSrvcName="OracleAgent12c"
EM_INSTALL_TYPE="AGENT"

if faced with

Validating the OMS_HOST EM_UPLOAD_PORT
ERROR: OMS_HOST cannot be null.

do the same in one string

1
C:\Users\sql> C:\oracle_agent\distrib\agentDeploy.bat AGENT_BASE_DIR=C:\oraclea_agent\agent12c4 OMS_HOST=192.168.226.20 EM_UPLOAD_PORT=4900 AGENT_INSTANCE_HOME=C:\oracle_agent\agent12c4\ s_agentHomeName=agent12c4_home s_agentSrvcName=Oracleagent12cAgent b_forceInstCheck=true AGENT_PORT=3872 EM_INSTALL_TYPE=AGENT AGENT_REGISTRATION_PASSWORD=xxxxxx -ignorePrereqs

upload ms sql agent plugin to agent

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[oracle@monitoring ~]$ emcli get_plugin_deployment_status
Displaying status for the latest deployment activity(deployment ID 260)
Plug-in Deployment/Undeployment Status
 
Destination          : Management Agent - MKSSQL1:3872
Plug-in Name         : Microsoft SQLServer Database
Version              : 12.1.0.5.0
ID                   : oracle.em.smss
Content              : Plug-in
Action               : Deployment
Status               : Deploying
Steps Info:
---------------------------------------- ------------------------- ------------------------- ----------
Step                                     Start Time                End Time                  Status
---------------------------------------- ------------------------- ------------------------- ----------
Submit job for deployment                6/25/15 12:04:41 PM MSK   6/25/15 12:04:41 PM MSK   Success
 
Initialize                               6/25/15 12:04:43 PM MSK   6/25/15 12:04:52 PM MSK   Success
 
Validate Environment                     6/25/15 12:04:52 PM MSK   N/A                       Running
 
---------------------------------------- ------------------------- ------------------------- ----------

Deploy MS SQL SERVER PLUGIN

ms sql extension 1

ms sql extension 2

ms sql extension 3

ms-sql-extension-4

ms sql extension 5

ms sql extension 6

ms sql extension 7

ms sql extension 8

Primary Sidebar