Skip to content

oracle12c how-to get detailed info about gather stat job

oracle12c how-to get detailed info about gather stat job published on Комментариев к записи oracle12c how-to get detailed info about gather stat job нет

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

how to copy schema statistics from prod to dev stand published on Комментариев к записи how to copy schema statistics from prod to dev stand нет

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

How-to restore dropped flashback archive or dropped table published on Комментариев к записи How-to restore dropped flashback archive or dropped table нет

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;
/

how-to download Oracle Java from console

how-to download Oracle Java from console published on Комментариев к записи how-to download Oracle Java from console нет
wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u121-b13/e9e7ea248e2c4826b92b3f075a80e441/jdk-8u121-linux-x64.rpm"

this link you may change to other version:

http://download.oracle.com/otn-pub/java/jdk/8u121-b13/e9e7ea248e2c4826b92b3f075a80e441/jdk-8u121-linux-x64.rpm

oracle TM lock, how-to find who forgot index on FK ?

oracle TM lock, how-to find who forgot index on FK ? published on Комментариев к записи oracle TM lock, how-to find who forgot index on FK ? нет

Lets do TM lock and find out what object should be indexed

create table tt ( id number ,fld varchar2(50) );
create table ttt ( id number ,id_tt number );
alter table tt add constraint pk_id primary key (id) ;
alter table ttt add foreign key ( id_tt ) references tt (id);
insert into tt select level ,rpad ('x',40) from dual connect by level <=100;
commit;

sess 1

 insert into ttt values ( 1,1);

sess 2

 delete from tt where id=1;

I use Tanel Poder sripts in this example
this one ( ashtop.sql )
and this one

oid.sql

— Script by Tanel Poder (http://www.tanelpoder.com)

— Look up object info by object id

col o_owner heading owner for a25
col o_object_name heading object_name for a30
col o_object_type heading object_type for a18
col o_status heading status for a9

select
owner o_owner,
object_name o_object_name,
object_type o_object_type,
subobject_name,
created,
last_ddl_time,
status o_status,
data_object_id
from
dba_objects
where
object_id in (&1)
order by
o_object_name,
o_owner,
o_object_type

[свернуть]

result :

or use this scipt to find nonindexed foreing keys:

fk_no_indx

select table_name, column_name
from ( select c.table_name, cc.column_name, cc.position column_position
from all_constraints c, all_cons_columns cc
where c.constraint_name = cc.constraint_name
and c.constraint_type = ‘R’ and c.owner=cc.owner and c.owner=’&1′
minus
select i.table_name, ic.column_name, ic.column_position
from all_indexes i, all_ind_columns ic
where i.index_name = ic.index_name and i.owner=ic.table_owner and i.owner=’&1′);

[свернуть]
18:13:51 (1)c##bushmelev_aa@u10pgp> @fk_no_indx C##BUSHMELEV_AA


TABLE_NAME | COLUMN_NAME
---------- | ---------------
TTT        | ID_TT

postgresql table index usage info

postgresql table index usage info published on Комментариев к записи postgresql table index usage info нет
    
   SELECT 
            pg_stat_all_tables.schemaname,relid::regclass AS table, 
            indexrelid::regclass AS index, 
            pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, 
            pg_stat_user_indexes.idx_tup_read,            
                round (100*(
                    sum( pg_stat_user_indexes.idx_tup_read)/
                    ( sum(pg_stat_user_indexes.idx_tup_read) over ()) ),1) as pct_idx_tup_read,
            pg_stat_user_indexes.idx_tup_fetch,
             round(100*(
                    sum(pg_stat_user_indexes.idx_tup_fetch)/
                    (sum(pg_stat_user_indexes.idx_tup_fetch) over ()) ),1) as pct_idx_tup_fetch,
            pg_stat_user_indexes.idx_scan,
            round(100*(
                    sum(   pg_stat_user_indexes.idx_scan)/
                    (     sum(pg_stat_user_indexes.idx_scan) over ()) ),1) as pct_idx_scan,
            pg_get_indexdef(pg_index.indexrelid) as indexdef
        FROM 
            pg_stat_user_indexes 
            JOIN pg_index USING (indexrelid)
            join pg_stat_all_tables using (relid) 
        WHERE 
             (relid::regclass)::text ='table_name' 
             group by idx_tup_read,pg_stat_all_tables.schemaname,relid,indexrelid,pg_stat_user_indexes.idx_tup_fetch,pg_stat_user_indexes.idx_scan,pg_index.indexrelid
             order by idx_scan desc ;
schemaname |     table      |                   index                    | index_size | idx_tup_read | pct_idx_tup_read | idx_tup_fetch | pct_idx_tup_fetch | idx_scan  | pct_idx_scan |                                                     indexdef
------------+----------------+--------------------------------------------+------------+--------------+------------------+---------------+-------------------+-----------+--------------+------------------------------------------------------------------------------------------------------------------
 public     | fin_bill_steps | fin_bill_steps_bill_id_customer_id_idx     | 1462 MB    |    395213398 |             83.7 |     197898196 |              88.7 | 158647275 |         89.8 | CREATE INDEX fin_bill_steps_bill_id_customer_id_idx ON fin_bill_steps USING btree (bill_id, customer_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_srv_rendered_id_idx | 5061 MB    |     66673035 |             14.1 |      23200795 |              10.4 |  16720920 |          9.5 | CREATE INDEX fin_bill_steps_bill_id_srv_rendered_id_idx ON fin_bill_steps USING btree (bill_id, srv_rendered_id)
 public     | fin_bill_steps | fin_bill_steps_spec_item_id_idx            | 4999 MB    |      1198864 |              0.3 |       1187406 |               0.5 |   1187537 |          0.7 | CREATE INDEX fin_bill_steps_spec_item_id_idx ON fin_bill_steps USING btree (spec_item_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_case_id_idx         | 5087 MB    |      8697564 |              1.8 |        748173 |               0.3 |     90822 |          0.1 | CREATE INDEX fin_bill_steps_bill_id_case_id_idx ON fin_bill_steps USING btree (bill_id, case_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_steps_id_idx        | 5692 MB    |       143034 |              0.0 |          4065 |               0.0 |      4055 |          0.0 | CREATE INDEX fin_bill_steps_bill_id_steps_id_idx ON fin_bill_steps USING btree (bill_id, step_id)
(5 rows)

postgresql top 10 tables by io operations

postgresql top 10 tables by io operations published on Комментариев к записи postgresql top 10 tables by io operations нет

helps to keep focus on “hot” tables
top 10 tables by sum of io operatoins:

SELECT
st.schemaname||'.'||st.relname,
round (100*( 
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) 
) 
	/ 
(
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) over () 
),2) as pct_io_ops
 from pg_stat_user_tables st
JOIN pg_class c ON c.oid=st.relid 
LEFT JOIN pg_tablespace t ON t.oid=c.reltablespace
WHERE
coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)>100
group by st.schemaname||'.'||st.relname,st.n_tup_ins,st.n_tup_upd,st.n_tup_hot_upd,st.n_tup_del
order by pct_io_ops desc 
limit 10;

top 10 tables by index scans:

   SELECT 
        pg_stat_all_tables.schemaname||'.'||relid::regclass AS table, 
        round (100* (sum(pg_stat_user_indexes.idx_scan))
                      / (sum(pg_stat_user_indexes.idx_scan) over () )
                       ,2) pct_indx_scans
    FROM 
        pg_stat_user_indexes 
        JOIN pg_index USING (indexrelid)
        join pg_stat_all_tables using (relid)
        group by pg_stat_all_tables.schemaname||'.'||relid::regclass,pg_stat_user_indexes.idx_scan
        order by 2 desc 
        limit 10;

ORA-00600: internal error code, arguments: [smboPut:fixedlen2]

ORA-00600: internal error code, arguments: [smboPut:fixedlen2] published on Комментариев к записи ORA-00600: internal error code, arguments: [smboPut:fixedlen2] нет

while import full dump from AIX machine to X86 faced this error:

ORA-00600: internal error code, arguments: [smboPut:fixedlen2], [2], [7], [0], [66436], [], [], [], [], [], [], []
Failing sql is:
CREATE INDEX SHIPMENT_INS_DATE ON SHIPMENT ("INSERT_DATE") 
ORA-39083: Object type INDEX failed to create with error:
ORA-00600: internal error code, arguments: [smboPut:fixedlen2], [2], [7], [0], [66436], [], [], [], [], [], [], []

helped to set this parameter:

ALTER system SET "_newsort_enabled" = FALSE;

ps: version is 11.2.0.4.5

ORA-25152: TEMPFILE cannot be dropped at this time

ORA-25152: TEMPFILE cannot be dropped at this time published on Комментариев к записи ORA-25152: TEMPFILE cannot be dropped at this time нет

hello, here how I dropped tempfile :

start with:

ALTER TABLESPACE temp  DROP TEMPFILE '+DATADG/p00pgp/tempfile/temp.340.882540285';
ALTER TABLESPACE temp  DROP TEMPFILE '+DATADG/p00pgp/tempfile/temp.339.882540283';

but when I try to drop datafiles I’ve got this error:
ORA-25152: TEMPFILE cannot be dropped at this time

helped this sript

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     and b.segfile# in ( 205,206)
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

which helped me to find sessions using tempfile
sessions was idle, so I kill them, after that this command have no errors:

ALTER DATABASE TEMPFILE '+DATADG/p00pgp/tempfile/temp.339.882540283' DROP      INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '+DATADG/p00pgp/tempfile/temp.340.882540285' DROP      INCLUDING DATAFILES;

postgresql pgpool-II autofailover and failback

postgresql pgpool-II autofailover and failback published on Комментариев к записи postgresql pgpool-II autofailover and failback нет

Hello, try to find a good material about how-to configure pgpool and found nothing, so I’ve decide to write my own article =))

I’m recommend to read previous articles:
How to install postgres
How to configure repmgr to manage standby databases
because I use those config in this scenario

So, lets start =)

First of all lest configure OS to match our needs ( I had to use RH 6 distributive )
1. configure passwordless ssh to all nodes from root and from postgres user
2. configure passwordless sudo to allow this commands:
add to /etc/sudoers

postgres ALL=(root) NOPASSWD: /sbin/ip
postgres ALL=(root) NOPASSWD: /sbin/arping
postgres ALL=(root) NOPASSWD: /sbin/ifconfig
postgres ALL=(root) NOPASSWD: /etc/init.d/postgresql-9.5

To allow passwordless pcp commands create this file in postgres and root users:

cat $HOME/.pcppass
*:*:pgpooladmin:q1
chmod 600 .pcppass

now lets configure pgpool

start with easiest part, configure pcp.conf
to do this run pg_md5 command with password as first argument:

[postgres@pg1 ~]$ pg_md5 q1
ff33f1b12213e021c2c4a888141953ba

result add to pcp.conf with format of username:pg_md5_pass

echo "pgpooladmin:ff33f1b12213e021c2c4a888141953ba" >> pcp.conf

I’m allow connect to pgpool from whole network, to archive this I’ve add this line to pool_hba.conf

host	all		all	10.10.10.0/24	md5

next session is to configure pgpool.conf
I’ve made it from pgpool.conf.sample-stream

diff pgpool.conf.sample-stream pgpool.conf

27c27,28
< listen_addresses = 'localhost' --- > listen_addresses = ‘*’
>
65c66
< backend_hostname0 = 'host1' --- > backend_hostname0 = ‘pg1’
71c72
< backend_data_directory0 = '/data' --- > backend_data_directory0 = ‘/u01/postgres/9.5/main/pgdata’
76,80c77,89
< #backend_hostname1 = 'host2' < #backend_port1 = 5433 < #backend_weight1 = 1 < #backend_data_directory1 = '/data1' < #backend_flag1 = 'ALLOW_TO_FAILOVER' --- > backend_hostname1 = ‘pg2’
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = ‘/u01/postgres/9.5/main/pgdata’
> backend_flag1 = ‘ALLOW_TO_FAILOVER’
>
> backend_hostname2 = ‘pg3’
> backend_port2 = 5432
> backend_weight2 = 1
> backend_data_directory2 = ‘/u01/postgres/9.5/main/pgdata’
> backend_flag2 = ‘ALLOW_TO_FAILOVER’
>
>
84c93
< enable_pool_hba = off --- > enable_pool_hba = on
120c129
< num_init_children = 32 --- > num_init_children = 300
123c132
< max_pool = 4 --- > max_pool = 1
218c227
< pid_file_name = '/var/run/pgpool/pgpool.pid' --- > pid_file_name = ‘/var/run/pgpool-II-95/pgpool.pid’
333c342
< sr_check_user = 'nobody' --- > sr_check_user = ‘repmgr’
337c346
< sr_check_password = '' --- > sr_check_password = ‘q1’
348c357
< follow_master_command = '' --- > follow_master_command = ‘/u01/postgres/9.5/main/conf/follow_master.sh %d %H %m %P %h’
367c376
< health_check_period = 0 --- > health_check_period = 5
373c382
< health_check_user = 'nobody' --- > health_check_user = ‘repmgr’
375c384
< health_check_password = '' --- > health_check_password = ‘q1’
377c386
< health_check_database = '' --- > health_check_database = ‘postgres’
379c388
< health_check_max_retries = 0 --- > health_check_max_retries = 5
381c390
< health_check_retry_delay = 1 --- > health_check_retry_delay = 5
383c392
< connect_timeout = 10000 --- > connect_timeout = 30000
394c403
< failover_command = '' --- > failover_command = ‘/u01/postgres/9.5/main/conf/failover.sh %d %H %m %P %h’
408c417,418
< failback_command = '' --- > failback_command = ‘/u01/postgres/9.5/main/conf/stb_recreate.sh %d %H %m %P %h’
> #failback_command = ”
439c449
< recovery_user = 'nobody' --- > recovery_user = ‘repmgr’
441c451
< recovery_password = '' --- > recovery_password = ‘q1’
465c475
< use_watchdog = off --- > use_watchdog = on
471c481
< trusted_servers = '' --- > trusted_servers = ‘app01,app02’
482c492
< wd_hostname = '' --- > wd_hostname = ‘pg1’
505c515
< delegate_IP = '' --- > delegate_IP = ‘10.10.10.20’
509c519
< if_cmd_path = '/sbin' --- > if_cmd_path = ‘/usr/bin’
512c522
< if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' --- > if_up_cmd = ‘sudo ip addr add $_IP_$/24 dev eth1 label eth1:1’
515c525
< if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' --- > if_down_cmd = ‘sudo ip addr del $_IP_$/24 dev eth1:1’
518c528
< arping_path = '/usr/sbin' --- > arping_path = ‘/usr/bin’
521c531
< arping_cmd = 'arping -U $_IP_$ -w 1' --- > arping_cmd = ‘sudo arping -U $_IP_$ -w 1’
569c579
< heartbeat_destination0 = 'host0_ip1' --- > heartbeat_destination0 = ‘pg2’
586,588c596,598
< #heartbeat_destination1 = 'host0_ip2' < #heartbeat_destination_port1 = 9694 < #heartbeat_device1 = '' --- > heartbeat_destination1 = ‘pg3’
> heartbeat_destination_port1 = 9694
> heartbeat_device1 = ”
601c611
< wd_lifecheck_user = 'nobody' --- > wd_lifecheck_user = ‘pgpool’
604c614
< wd_lifecheck_password = '' --- > wd_lifecheck_password = ‘w7DeMth2oid3oYyE’
610c620
< #other_pgpool_hostname0 = 'host0' --- > other_pgpool_hostname0 = ‘pg2’
613c623
< #other_pgpool_port0 = 5432 --- > other_pgpool_port0 = 9999
616c626
< #other_wd_port0 = 9000 --- > other_wd_port0 = 9000
619,621c629,631
< #other_pgpool_hostname1 = 'host1' < #other_pgpool_port1 = 5432 < #other_wd_port1 = 9000 --- > other_pgpool_hostname1 = ‘p01db03’
> other_pgpool_port1 = 9999
> other_wd_port1 = 9000

[свернуть]

in my configuration I use trusted servers ( app servers ) you may leave it blank, also there was 3 node config ( for quorum) but in my test stand I use only two stands for one datacenter.

Key thig here is to keep first part of this file the same on all host ( where backend_hostname defenition is described )

copy config to second node:

scp  /etc/pgpool-II-95/* pg2:/etc/pgpool-II-95/

modify:
wd_hostname to second node
heartbeat_destination0 to first node
other_pgpool_hostname0 to first node

if you have any problems start pgpool in foreground mode ( also can be usefull to enable debug )
pgpool -f /etc/pgpool-II-95/pgpool.conf -n -d

usefull commands:
check node status
I use connect to VIP ip and port 9999

psql -U repmgr -d postgres -p 9999 -h 10.10.10.20 -c "show pool_nodes"
 node_id | hostname | port | status | lb_weight |  role   | select_cnt
---------+----------+------+--------+-----------+---------+------------
 0       | pg1      | 5432 | 2      | 0.500000  | primary | 0
 1       | pg2      | 5432 | 2      | 0.500000  | standby | 0 

scripts ( check that they are executable or add chmod +x /u01/postgres/9.5/main/conf/*.sh )

follow_master.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf -D $PGDATA -h $new_master -U repmgr -d postgres standby follow"
 	else  /usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf -D $PGDATA -h $new_master -U repmgr -d postgres standby follow
fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

[свернуть]

failover.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "/usr/bin/ssh -T -l postgres $new_master \"/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-\" "
 	else /usr/bin/ssh $new_master "/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
fi
sleep 5
if [ $UID -eq 0 ]
 	then su postgres -c  "/usr/bin/ssh -T -l postgres $new_master \"pcp_attach_node -p 9898 -U pgpooladmin -w -n $new_master_id\""
	else /usr/bin/ssh -T -l postgres $new_master "/usr/bin/ssh -T -l postgres $new_master \"pcp_attach_node -p 9898 -U pgpooladmin -w -n $new_master_id\""
fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

[свернуть]
stb_recreate.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
repmgr_conf=/u01/postgres/9.5/main/conf/repmgr.conf
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "
/usr/pgsql-9.5/bin/repmgr -c -h $master_node -d postgres -U repmgr -D $PGDATA -f $repmgr_conf standby clone
sudo /etc/init.d/postgresql-9.5 start
/usr/pgsql-9.5/bin/repmgr -f $repmgr_conf standby register -F
sleep 15
pcp_attach_node -h localhost -U pgpooladmin -w -n 1
"
 	else
	/usr/pgsql-9.5/bin/pg_ctl -m fast stop
	mv $PGDATA "$(echo $PGDATA)_$(echo `date +%H:%M_%d%m%Y`)"
	/usr/pgsql-9.5/bin/repmgr -c -h $new_master -d postgres -U repmgr -D $PGDATA -f $repmgr_conf standby clone
	sudo /etc/init.d/postgresql-9.5 start
	/usr/pgsql-9.5/bin/repmgr -f $repmgr_conf standby register -F
	sleep 15
	pcp_attach_node -h localhost -U pgpooladmin -w -n 1

fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_recreate.log

[свернуть]

Primary Sidebar

Яндекс.Метрика