Bushmelev Anton – Страница 2

journald limit log size

13.11.2017 Bushmelev Anton Комментариев нет

quick way to achieve that is:

sed -i 's/1G/512M/g' /etc/systemd/journald.conf
systemctl restart systemd-journald
journalctl --vacuum-size=500M

how to find pid which spam to alert log

19.10.2017 Bushmelev Anton Комментариев нет

if u dont know who is spamming to alert log there is the way to figure out this

col cnt format a20
col host_id format a20
col message_text format a40
col process_id format a10
select count(*) cnt,host_id,message_text,process_id from X$DBGALERTEXT where message_text like '%Turning off busy%' group by host_id,message_text,process_id;

       CNT | HOST_ID              | MESSAGE_TEXT                             | PROCESS_ID
---------- | -------------------- | ---------------------------------------- | ----------
     28857 | xxxxxxxxxxxxxxxxxxxx | Turning off busy                         | 28518     

[xxxxxxxxxxxxxxxxxxxx ]# ps -ef | grep 28518 | grep -v "grep"
oracle   28518     1 25 Oct16 ?        10:23:42 ora_fbda_xxxxxx2

and it’s Flasback process =)

ps:oracle 12.1

postgresq table column stats

30.08.2017 Bushmelev Anton Комментариев нет

select attname, inherited, n_distinct,
       array_to_string(most_common_vals, E';') as most_common_vals,most_common_freqs
from pg_stats
where tablename = 'table_name' ;

PostgreSQL explain plan operations

10.07.2017 Bushmelev Anton Комментариев нет

to be continued

> – what does “Bitmap Heap Scan” phase do?

A plain indexscan fetches one tuple-pointer at a time from the index,
and immediately visits that tuple in the table. A bitmap scan fetches
all the tuple-pointers from the index in one go, sorts them using an
in-memory “bitmap” data structure, and then visits the table tuples in
physical tuple-location order. The bitmap scan improves locality of
reference to the table at the cost of more bookkeeping overhead to
manage the “bitmap” data structure — and at the cost that the data
is no longer retrieved in index order, which doesn’t matter for your
query but would matter if you said ORDER BY.

> – what is “Recheck condition” and why is it needed?

If the bitmap gets too large we convert it to “lossy” style, in which we
only remember which pages contain matching tuples instead of remembering
each tuple individually. When that happens, the table-visiting phase
has to examine each tuple on the page and recheck the scan condition to
see which tuples to return.

> – why are proposed “width” fields in the plan different between the two
> plans?

Updated statistics about average column widths, presumably.

> (actually, a nice explanation what exactly are those widths would also
> be nice :) )

Sum of the average widths of the columns being fetched from the table.

> – I thought “Bitmap Index Scan” was only used when there are two or more
> applicable indexes in the plan, so I don’t understand why is it used
> now?

True, we can combine multiple bitmaps via AND/OR operations to merge
results from multiple indexes before visiting the table … but it’s
still potentially worthwhile even for one index. A rule of thumb is
that plain indexscan wins for fetching a small number of tuples, bitmap
scan wins for a somewhat larger number of tuples, and seqscan wins if
you’re fetching a large percentage of the whole table.

regards, tom lane

https://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us

Oracle, find query witch runs more than &1 sec

20.06.2017 Bushmelev Anton Комментариев нет

with stats as (
select /*+ materialize */
 * from (
select  parsing_schema_name,t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  s.parsing_schema_name,min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.begin_interval_time > trunc(sysdate-4,'iw')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > &1
group by  sql_id, plan_hash_value,parsing_schema_name
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs*avg_sec desc ) tt where
 rownum <= 50 )
 select * from stats s 
 where parsing_schema_name not like 'SYS%'
  and s.sql_text not like '%/* DS_SVC */%'
  and s.sql_text not like '%_job_proc%'
  and s.sql_text not like '%SQL Analyze%'
  and lower(s.sql_text) not like '%dbms_feature%'
  and s.sql_text not like '%SYS_DBA_SEGS%'
  and lower(s.sql_text) not like '%v$sys%'
  and lower(s.sql_text) not like '%dba_audit_trail%'
  and lower(s.sql_text) not like '%no_index(mytab)%';

Postgresql how-to find top query for last 15 seconds

15.06.2017 Bushmelev Anton Комментариев нет

DROP TABLE IF EXISTS pg_stat_statements_temp; 
  create table pg_stat_statements_temp as
  SELECT  d.datname,u.usename, s.queryid,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean,
now() as cur_date
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid;
 
    select pg_sleep(15);



 with stat as (SELECT  d.datname,u.usename, s.queryid,s.query,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid  )
    select  s.datname,s.usename,s.queryid,regexp_replace(s.query, E'[\n\r]+', ' ', 'g' )::varchar(80) AS short_query,
    s.total_time-t.total_time as time,
    s.calls-t.calls as calls,
    s.mean -t.mean as mean, 
    round( 
      (100 * (s.total_time-t.total_time) /sum(s.total_time-t.total_time) OVER ()
            )::numeric, 2)     as cpu,
    now()-t.cur_date as diff_time, 
CASE WHEN s.calls-t.calls >0  THEN 
     (s.calls-t.calls )/(extract ( second from now()-t.cur_date) + extract ( minutes from now()-t.cur_date)*60 + extract ( minutes from now()-t.cur_date)*60*60 ) 
    else '0' 
    end as tps
     from stat s
    	join pg_stat_statements_temp t on s.datname=t.datname and s.usename=t.usename and s.queryid=t.queryid
    	order by cpu desc limit 20;

Oracle find query runs slower than ‘&1’ seconds in ‘&2’ minutes

11.06.2017 Bushmelev Anton Комментариев нет

with stats as (
select /*+ materialize */
 * from (
select  t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.begin_interval_time > (sysdate -&2/1440)
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > &1
group by  sql_id, plan_hash_value
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs*avg_sec desc ) tt where
 rownum <= 50 )
 select * from stats s 
 where 1=1
  and s.sql_text not like '%/* DS_SVC */%'
  and s.sql_text not like '%_job_proc%'
  and s.sql_text not like '%SQL Analyze%'
  and lower(s.sql_text) not like '%dbms_feature%'
  and s.sql_text not like '%SYS_DBA_SEGS%'
  and lower(s.sql_text) not like '%v$sys%'
  and lower(s.sql_text) not like '%dba_audit_trail%'
  and lower(s.sql_text) not like '%no_index(mytab)%';

oracle12c how-to get detailed info about gather stat job

01.06.2017 Bushmelev Anton Комментариев нет

from oracle 12c it is possible to find out detailed information about gather stats job
in em you may find it at

or use dbms_stats.report_* to get info
detailed at https://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL95102

I use it this way:

variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_stats_operations(
    since=>SYSTIMESTAMP-3,
    until=>SYSTIMESTAMP,
    detail_level=>'ALL',
    format=>'TEXT');
    end;
   /

print mystatsreport

variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_single_stats_operation ( opid => 11898,detail_level => 'TYPICAL', format =>'TEXT' );
    end;
   /
print mystatsreport

variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_single_stats_operation ( opid => 11898,detail_level => 'TYPICAL', format =>'TEXT' );
    end;
   /
   print mystatsreport

how to copy schema statistics from prod to dev stand

30.05.2017 Bushmelev Anton Комментариев нет

hello, dev team ask me to look at their stand under load testing and advice how-to improve results. So first thing that I found was statistics locked for last 3 years =)

So fist thing that I have done it’s to copy stats from prod database.
To archive this I’ve done this:

create stats table and export schema stats to it:

exec dbms_stats.create_stat_table ( ownname => 'XXXX' , stattab => 'XXXX_STATS_TABLE' ) ;
exec dbms_stats.export_schema_stats ( ownname => 'XXXX' , stattab => 'XXXX_STATS_TABLE') ;

next thing was transfering dump file to destination host:

[oracle@p00XXXXdb01 ~]$ expdp \'/ as sysdba\' tables=XXXX.XXXX_STATS_TABLE  directory=temp_dump dumpfile=XXXX_STATS_TABLE.dmp logfile=XXXX_STATS_TABLE.log reuse_dumpfiles=y

on destination (dev stand) host I’ve prepared scripts for lock and unlock statistics:

select distinct(' exec dbms_stats.lock_table_stats(''XXXX'',''' || table_name ||''');' ) from dba_tab_statistics where owner='XXXX' and STATTYPE_LOCKED='ALL' ; 


exec dbms_stats.unlock_table_stats('XXXX','BKG_CFG');
exec dbms_stats.unlock_table_stats('XXXX','PSO');
exec dbms_stats.unlock_table_stats('XXXX','OBJ');
exec dbms_stats.unlock_table_stats('XXXX','AUT_PAR');
exec dbms_stats.unlock_table_stats('XXXX','IT_RES');
exec dbms_stats.unlock_table_stats('XXXX','CTY');
exec dbms_stats.unlock_table_stats('XXXX','SRV_PVI');
exec dbms_stats.unlock_table_stats('XXXX','ADR');
exec dbms_stats.unlock_table_stats('XXXX','ACC_AUT_PAR');
exec dbms_stats.unlock_table_stats('XXXX','REG_AUT');
exec dbms_stats.unlock_table_stats('XXXX','CFM_COD');
exec dbms_stats.unlock_table_stats('XXXX','CTT');
exec dbms_stats.unlock_table_stats('XXXX','CTZ_SHP');
exec dbms_stats.unlock_table_stats('XXXX','APV');
exec dbms_stats.unlock_table_stats('XXXX','CRL');
exec dbms_stats.unlock_table_stats('XXXX','AUT');
exec dbms_stats.unlock_table_stats('XXXX','ACC_BKP');
exec dbms_stats.unlock_table_stats('XXXX','ACC_AUT');
exec dbms_stats.unlock_table_stats('XXXX','SAG');
exec dbms_stats.unlock_table_stats('XXXX','OPN_HIS');
exec dbms_stats.unlock_table_stats('XXXX','OBJ_TEC');
exec dbms_stats.unlock_table_stats('XXXX','ACS_TKN');
exec dbms_stats.unlock_table_stats('XXXX','SES_CXT');
exec dbms_stats.unlock_table_stats('XXXX','LGN_BKG');
exec dbms_stats.unlock_table_stats('XXXX','RSP');
exec dbms_stats.unlock_table_stats('XXXX','GRP');
exec dbms_stats.unlock_table_stats('XXXX','ORG');
exec dbms_stats.unlock_table_stats('XXXX','REQ');
exec dbms_stats.unlock_table_stats('XXXX','TAS');
exec dbms_stats.unlock_table_stats('XXXX','ACT_SES');
exec dbms_stats.unlock_table_stats('XXXX','CTT_TEC');
exec dbms_stats.unlock_table_stats('XXXX','ACS_LST');
exec dbms_stats.unlock_table_stats('XXXX','STF_UNT');
exec dbms_stats.unlock_table_stats('XXXX','OPN_HIS_PAR');
exec dbms_stats.unlock_table_stats('XXXX','BKG_LOG');
exec dbms_stats.unlock_table_stats('XXXX','DOC');
exec dbms_stats.unlock_table_stats('XXXX','IT_SYS');

select distinct('exec dbms_stats.unlock_table_stats(''XXXX'',''' || table_name ||''');' ) from dba_tab_statistics where owner='XXXX' and STATTYPE_LOCKED='ALL' ;
exec dbms_stats.lock_table_stats('XXXX','CTT');
exec dbms_stats.lock_table_stats('XXXX','AUT_PAR');
exec dbms_stats.lock_table_stats('XXXX','ACT_SES');
exec dbms_stats.lock_table_stats('XXXX','BKG_LOG');
exec dbms_stats.lock_table_stats('XXXX','TAS');
exec dbms_stats.lock_table_stats('XXXX','CTZ_SHP');
exec dbms_stats.lock_table_stats('XXXX','CTT_TEC');
exec dbms_stats.lock_table_stats('XXXX','REG_AUT');
exec dbms_stats.lock_table_stats('XXXX','ACS_TKN');
exec dbms_stats.lock_table_stats('XXXX','CFM_COD');
exec dbms_stats.lock_table_stats('XXXX','ORG');
exec dbms_stats.lock_table_stats('XXXX','ACS_LST');
exec dbms_stats.lock_table_stats('XXXX','OPN_HIS');
exec dbms_stats.lock_table_stats('XXXX','OBJ');
exec dbms_stats.lock_table_stats('XXXX','REQ');
exec dbms_stats.lock_table_stats('XXXX','AUT');
exec dbms_stats.lock_table_stats('XXXX','ACC_BKP');
exec dbms_stats.lock_table_stats('XXXX','IT_RES');
exec dbms_stats.lock_table_stats('XXXX','CTY');
exec dbms_stats.lock_table_stats('XXXX','GRP');
exec dbms_stats.lock_table_stats('XXXX','SAG');
exec dbms_stats.lock_table_stats('XXXX','ADR');
exec dbms_stats.lock_table_stats('XXXX','RSP');
exec dbms_stats.lock_table_stats('XXXX','STF_UNT');
exec dbms_stats.lock_table_stats('XXXX','OPN_HIS_PAR');
exec dbms_stats.lock_table_stats('XXXX','OBJ_TEC');
exec dbms_stats.lock_table_stats('XXXX','CRL');
exec dbms_stats.lock_table_stats('XXXX','LGN_BKG');
exec dbms_stats.lock_table_stats('XXXX','ACC_AUT_PAR');
exec dbms_stats.lock_table_stats('XXXX','BKG_CFG');
exec dbms_stats.lock_table_stats('XXXX','PSO');
exec dbms_stats.lock_table_stats('XXXX','APV');
exec dbms_stats.lock_table_stats('XXXX','DOC');
exec dbms_stats.lock_table_stats('XXXX','ACC_AUT');
exec dbms_stats.lock_table_stats('XXXX','SRV_PVI');
exec dbms_stats.lock_table_stats('XXXX','SES_CXT');
exec dbms_stats.lock_table_stats('XXXX','IT_SYS');

after unlocking stats run:

[oracle@u00XXXXdb01 ~]$impdp \'/ as sysdba\' directory=dump dumpfile=XXXX_STATS_TABLE.dmp  logfile=imp.log

check stats:

14:14:12 (1)[u00XXXX]system@u00XXXX> @table_stats XXXX.acc %

OWNER           | TABLE_NAME                     |   NUM_ROWS |     BLOCKS | S_LOCK               | S_STALE              |  AVG_SPACE | AVG_ROW_LEN
--------------- | ------------------------------ | ---------- | ---------- | -------------------- | -------------------- | ---------- | -----------
XXXX            | ACC                            |    7189756 |     139407 | ALL                  | YES                  |          0 |         129

1 row selected.

Elapsed: 00:00:00.06

OWNER           | TABLE_NAME                     |  COLUMN_ID | COLUMN_NAME                    |    DENSITY | NUM_DISTINCT |  NUM_NULLS | HISTOGRAM       | LAST_ANALYZED
--------------- | ------------------------------ | ---------- | ------------------------------ | ---------- | ------------ | ---------- | --------------- | -------------------
XXXX            | ACC                            |          1 | ID_OBJ                         | 1.3909E-07 |      7189756 |          0 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          2 | LGN                            | 1.3862E-07 |      7189756 |          0 | HEIGHT BALANCED | 10.01.2014 22.02.18
XXXX            | ACC                            |          3 | STU                            | 6.9311E-08 |            2 |          0 | FREQUENCY       | 10.01.2014 22.02.18
XXXX            | ACC                            |          4 | SCT_QSN                        | 2.8562E-06 |       350112 |     348895 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          5 | SCT_ANR                        | 6.7852E-07 |      1473792 |     350288 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          6 | CRT_ON                         | 1.3909E-07 |      7189756 |          0 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          7 | UPD_ON                         | 1.3862E-07 |      7120896 |          0 | HEIGHT BALANCED | 10.01.2014 22.02.18
XXXX            | ACC                            |          8 | DSC                            |          1 |            1 |     294329 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          9 | SBJ_TYP                        | 6.9299E-08 |            2 |          0 | FREQUENCY       | 10.01.2014 22.02.18
XXXX            | ACC                            |         10 | PSV                            | 7.2131E-08 |            1 |     294329 | FREQUENCY       | 10.01.2014 22.02.18
XXXX            | ACC                            |         11 | OTP_AUT                        |         .5 |            2 |    1643025 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |         12 | OTP_MTD                        |          1 |            1 |    7181747 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |         13 | OTP_DVC_ID                     | .000125203 |         7987 |    7181747 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |         14 | NO_PWD                         |          1 |            1 |    7187683 | NONE            | 10.01.2014 22.02.18

14 rows selected.

Elapsed: 00:00:00.93

OWNER           | TABLE_NAME                     | EXTENSION_NAME                      | EXTENSION                                                                                  | CREATO | DRO
--------------- | ------------------------------ | ----------------------------------- | ------------------------------------------------------------------------------------------ | ------ | ---
XXXX            | ACC                            | SYS_NC00017$                        | (UPPER("LGN"))                                                                             | SYSTEM | NO

import stats ^

14:42:48 (1)[u00XXXX]system@u00XXXX>  EXEC DBMS_STATS.import_schema_stats ('XXXX','XXXX_STATS_TABLE',null,'XXXX');

check results:

14:51:41 (1)[u00XXXX]system@u00XXXX> @table_stats XXXX.acc %

OWNER           | TABLE_NAME                     |   NUM_ROWS |     BLOCKS | S_LOCK               | S_STALE              |  AVG_SPACE | AVG_ROW_LEN
--------------- | ------------------------------ | ---------- | ---------- | -------------------- | -------------------- | ---------- | -----------
XXXX            | ACC                            |   48444484 |     518959 | <NULL>               | NO                   |          0 |          86

1 row selected.

Elapsed: 00:00:00.12

OWNER           | TABLE_NAME                     |  COLUMN_ID | COLUMN_NAME                    |    DENSITY | NUM_DISTINCT |  NUM_NULLS | HISTOGRAM       | LAST_ANALYZED
--------------- | ------------------------------ | ---------- | ------------------------------ | ---------- | ------------ | ---------- | --------------- | -------------------
XXXX            | ACC                            |          1 | ID_OBJ                         | 2.0642E-08 |     48444484 |          0 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          2 | LGN                            |          0 |     48444484 |          0 | HYBRID          | 27.05.2017 13.01.50
XXXX            | ACC                            |          3 | STU                            | 1.0321E-08 |            3 |          0 | FREQUENCY       | 27.05.2017 13.01.50
XXXX            | ACC                            |          4 | SCT_QSN                        | 5.9769E-06 |       167312 |   47750066 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          5 | SCT_ANR                        | 3.6648E-06 |       272864 |   47750066 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          6 | CRT_ON                         | 2.0642E-08 |     48444484 |          0 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          7 | UPD_ON                         |          0 |     47513600 |          0 | HYBRID          | 27.05.2017 13.01.50
XXXX            | ACC                            |          8 | DSC                            |          1 |            1 |   48413684 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          9 | SBJ_TYP                        | 1.0321E-08 |            2 |          0 | FREQUENCY       | 27.05.2017 13.01.50
XXXX            | ACC                            |         10 | PSV                            |          1 |            1 |   48413684 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         11 | OTP_AUT                        |         .5 |            2 |          0 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         12 | OTP_MTD                        |          1 |            1 |    8911005 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         13 | OTP_DVC_ID                     | 2.6103E-08 |     38309888 |    8911005 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         14 | NO_PWD                         |          1 |            1 |   48441875 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         15 | LOGIN_BY_DS_ALLOWED            |         .5 |            2 |   47807130 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         16 | FRST_LGN                       |          1 |            1 |   47951901 | NONE            | 27.05.2017 13.01.50
XXXX            | <NULL>                         | <NULL>     | SYS_NC00017$                   | 2.0642E-08 |     48444484 |          0 | NONE            | 27.05.2017 13.01.50

17 rows selected.

Elapsed: 00:00:01.35

OWNER           | TABLE_NAME                     | EXTENSION_NAME                      | EXTENSION                                                                                  | CREATO | DRO
--------------- | ------------------------------ | ----------------------------------- | ------------------------------------------------------------------------------------------ | ------ | ---
XXXX            | ACC                            | SYS_NC00017$                        | (UPPER("LGN"))                                                                             | SYSTEM | NO

and lock stats using scripts we generate before

How-to restore dropped flashback archive or dropped table

25.03.2017 Bushmelev Anton Комментариев нет

Hello, it’s happened that my college instead of disassociating flashback archive disabled it ( so all archive was lost ) and I had to restore it, here is what I have done to fix it:

1) restore controlfile:

SET DBID xxxxxxxx

run {
    allocate channel t1 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxxx.xxxx.local,NB_ORA_CLIENT=xxxxx.00.xxxx.local)';
    restore controlfile from autobackup;
}

2) find out tablespaces that must be restored:

 select distinct tablespace_name from dba_segments where owner in ('SYS', 'SYSTEM'); 	

SYSAUX
UNDOTBS1
USERS
SYSTEM
UNDOTBS2

3) some fixes

SQL> alter database flashback off;
SQL> alter database disable  BLOCK CHANGE TRACKING;

4) Generate restore list ( list of tablespaces with flashback archives )

select listagg (name,',') WITHIN GROUP (order by name) from v$tablespace where name  like '%ARCH%' or name  in ('SYSAUX','UNDOTBS1','USERS','SYSTEM','UNDOTBS2','SLOW_FRA');

xxxx_ARCH,xxxx_ARCH_02,xxxx_ARCH_03,xxxx_ARCH_04_BIG,xxxx_ARCH_05,xxxx_ARCH_EMERGENCY,SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS

5)Generate skip list

select listagg (name,',') WITHIN GROUP (order by name) from v$tablespace where name not like 'PRE_DM_%' and name not in (
'xxxx_ARCH','xxxx_ARCH_02','xxxx_ARCH_03','xxxx_ARCH_04_BIG','xxxx_ARCH_05','xxxx_ARCH_EMERGENCY','SYSAUX','SYSTEM','UNDOTBS1','UNDOTBS2','USERS');

AUD_EVT_01,AUD_EVT_02,AUD_EVT_03,AUD_EVT_04,AUD_EVT_05,AUD_EVT_06,EGS_DB,EGS_SIA_DB,xxxx,xxxx_ADM,xxxx_CPP,xxxx_PDS,xxxx_SUPPORT,xxxx_TECH,xxxx_TEMP,SIA_DB,SIA_TEMP,SLOW_DATA,SLOW_FDA,TEMP

6) rman script look that way

run {
 	allocate channel t1 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxxx.xxx.local,NB_ORA_CLIENT=xxxxxx.xx.local)';
    allocate channel t2 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxxx.xx.local)';
    allocate channel t3 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxxx.xx.local)';
    allocate channel t4 DEVICE TYPE 'SBT_TAPE' PARMS='ENV=(NB_ORA_POLICY=Oracle-DB-xxxx2,NB_ORA_SERV=xxxxx.xxx.local,NB_ORA_CLIENT=xxxx.xx.local)';

set until scn 1142308665621;
set newname for datafile 39 to '+DATA';
restore tablespace xxxx_ARCH,xxxx_ARCH_02,xxxx_ARCH_03,xxxx_ARCH_04_BIG,xxxx_ARCH_05,xxxx_ARCH_EMERGENCY,SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS,SLOW_FDA;
switch datafile all;
recover database skip forever tablespace  AUD_EVT_01,AUD_EVT_02,AUD_EVT_03,AUD_EVT_04,AUD_EVT_05,AUD_EVT_06,xxx_DB,xxx_xxxxx_DB,xxxx,xxxx_ADM,xxxx_CPP,xxxx_PDS,xxxx_SUPPORT,xxxx_TECH,xxxx_TEMP,xxx_DB,xxx_TEMP,SLOW_DATA;
}

7) alter database backup controlfile to trace as ‘/home/oracle/control.ctl’;
then I’ve removed all non restored datafiles and got this script:

controlfile

CREATE CONTROLFILE REUSE DATABASE “P00xxxx” RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 37376
LOGFILE
GROUP 1 ‘+DATA/s00xxxx/onlinelog/group_1.263.857911649’ SIZE 500M BLOCKSIZE 512,
GROUP 2 ‘+DATA/s00xxxx/onlinelog/group_2.256.857911657’ SIZE 500M BLOCKSIZE 512,
GROUP 3 ‘+DATA/s00xxxx/onlinelog/group_3.265.857911663’ SIZE 500M BLOCKSIZE 512,
GROUP 4 ‘+DATA/s00xxxx/onlinelog/group_4.284.857911669’ SIZE 500M BLOCKSIZE 512,
GROUP 5 ‘+DATA/s00xxxx/onlinelog/group_5.283.857911675’ SIZE 500M BLOCKSIZE 512
— STANDBY LOGFILE
— GROUP 101 ‘+DATA/S00xxxx/ONLINELOG/group_101.267.936536737’ SIZE 500M BLOCKSIZE 512,
— GROUP 102 ‘+DATA/s00xxxx/onlinelog/group_102.293.851280253’ SIZE 500M BLOCKSIZE 512,
— GROUP 103 ‘+DATA/s00xxxx/onlinelog/group_103.294.851280265’ SIZE 500M BLOCKSIZE 512,
— GROUP 104 ‘+DATA/s00xxxx/onlinelog/group_104.295.851280275’ SIZE 500M BLOCKSIZE 512,
— GROUP 105 ‘+DATA/s00xxxx/onlinelog/group_105.296.851280285’ SIZE 500M BLOCKSIZE 512,
— GROUP 106 ‘+DATA/s00xxxx/onlinelog/group_106.277.867109841’ SIZE 500M BLOCKSIZE 512
DATAFILE
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.259.936581479’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.262.936580465’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.263.936580465’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.264.936580465’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.288.936583713’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.289.936583745’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch.306.936581753’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_02.278.936583949’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_02.293.936583947’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_02.294.936583949’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_03.302.936582809’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_03.304.936581961’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_04_big.276.936584015’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_05.305.936581923’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_emergency.277.936584001’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_emergency.291.936583867’,
‘+DATA/S00xxxx/DATAFILE/xxxx_arch_emergency.292.936583931’,
‘+DATA/S00xxxx/DATAFILE/slow_fda.298.936582809’,
‘+DATA/S00xxxx/DATAFILE/sysaux.258.936581503’,
‘+DATA/S00xxxx/DATAFILE/system.290.936583843’,
‘+DATA/S00xxxx/DATAFILE/undotbs1.261.936581223’,
‘+DATA/S00xxxx/DATAFILE/undotbs1.299.936582325’,
‘+DATA/S00xxxx/DATAFILE/undotbs1.312.936583685’,
‘+DATA/S00xxxx/DATAFILE/undotbs2.260.936581325’,
‘+DATA/S00xxxx/DATAFILE/undotbs2.310.936582849’,
‘+DATA/S00xxxx/DATAFILE/undotbs2.311.936582849’,
‘+DATA/S00xxxx/DATAFILE/users.287.936583685’
CHARACTER SET AL32UTF8
;

[свернуть]

ALTER TABLESPACE TEMP ADD TEMPFILE ;
ALTER TABLESPACE SIA_TEMP ADD TEMPFILE ;
ALTER TABLESPACE xxxx_TEMP ADD TEMPFILE ;

8) after that database is opened and query work like a charm:

create table xxxx.reg_ctx_flashback tablespace xxxx_fbda parallel (degree 4) unrecoverable as select * from xxxx.SYS_FBA_HIST_112683;

SQL> select /*+ parallel 4*/ count (*) from xxxx.SYS_FBA_HIST_112683;

  COUNT(*)
----------
  84774720

9) CTAS all flashback data and export it, then move to target host:

  SQL> create table xxxx.reg_ctx_flashback tablespace xxxx parallel (degree 4) unrecoverable as select * from xxxx.SYS_FBA_HIST_112683;

   expdp \'/ as sysdba\' directory=dump dumpfile=reg_cxt_fbda.dmp logfile=DROP_ME:export.log tables=xxxx.reg_ctx_flashback;

10) restore flashback

alter table xxxx.reg_cxt FLASHBACK ARCHIVE xxxx_ARCH_FL;

BEGIN
  DBMS_FLASHBACK_ARCHIVE.import_history (
    owner_name1       => 'xxxx',
    table_name1       => 'REG_CXT', 
    temp_history_name => 'REG_CTX_FLASHBACK', 
    options           => DBMS_FLASHBACK_ARCHIVE.NODELETE); END;
/

bushmelev-aa.ru

Posts authored by Bushmelev Anton