alter trigger RMAN.VPC_CONTEXT_TRG disable;
=> marche pas :(
lancer :
@?/rdbms/admin/dbmsrmanvpc.sql -vpd rman
puis UPGRADE CATALOG ;
En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.
alter trigger RMAN.VPC_CONTEXT_TRG disable;
=> marche pas :(
lancer :
@?/rdbms/admin/dbmsrmanvpc.sql -vpd rman
puis UPGRADE CATALOG ;
Merci aux dev sql qui m'ont bien aidé sur ce forum :
The Solution
To resolve this error, you must increase the values for INBOUND_ CONNECT_ TIMEOUT on both the listener side and the server side. If you are getting ORA-609 because of a connection timeout issue, increasing the values should fix the problem. You should change INBOUND_ CONNECT_ TIMEOUT to read as the following:
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120
Above, the timeout seconds are being set to 120 in place of the default 60. While this simple change should fix most ORA-609 errors, if you continue to see this error then you should try to locate which client is causing the error. Set the sqlnet.ora file parameter to
sqlnet.ora file: DIAG_ADR_ENABLED = OFF
Putain je me suis pris la tête avec mon client pour cette erreur à la con!!!
https://www.tekstream.com/resource-center/ora-609-opiodr-aborting-process-unknown-ospid/
http://www.runningoracle.com/product_info.php?products_id=435
Summary
You can create an SQL TUNING TASK manually ad hoc with the following simple steps.
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.
In case this is a current sql running use the v$session.
select sql_id from v$session where sid = :x
1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:
SET SERVEROUTPUT ON declare stmt_task VARCHAR2(40); begin stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq'); DBMS_OUTPUT.put_line('task_id: ' || stmt_task ); end; / task_id: TASK_69287
2. Run the SQL TUNING TASK
begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287'); end; /
3. You can monitor the processing of the tuning task with the statement
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_69287';
4. When the task has a status=COMPLETED, then run:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual;
5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.
begin DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE); end; / 6. You can check the database sql profiles with the statement: select * from dba_sql_profiles; In case you want to disable an sql profile use the statement: begin DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED'); end; /
https://dbaclass.com/article/how-to-run-sql-tuning-advisor-for-a-sql_id/
declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 4883,
end_snap => 4888,
sql_id => '1pfbzr2wbnwsy',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => 'tune_sql_1pfbzr2wbnwsy',
description => 'tuning_du_sql_1pfbzr2wbnwsy');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_sql_1pfbzr2wbnwsy');
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('tune_sql_1pfbzr2wbnwsy') from dual;
Pour afficher les stats sur les tables :
set pages 200
col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10
spool checkstat.lst
PROMPT Regular Tables
select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in ('SYS','SYSTEM')
order by owner,table_name
/
Pour les tables avec partitions :
set pages 200
col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10
spool checkstat.lst
PROMPT Partitioned Tables
select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in ('SYS','SYSTEM')
order by table_owner,table_name, partition_name
/
Pour afficher les stats sur les indexes ;
set pages 500
set lines 200
col index_owner form a10
col index_name for a40
col table_owner form a10
col owner form a10
select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by 3
/
Pour afficher les stats manquant avec un taux de % de 10 :
select m.TABLE_OWNER,
m.TABLE_NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
t.num_rows as last_known_rows_number,
t.last_analyzed
From dba_tab_modifications m,
dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;
--Check the parameter is enabled or disable for Audit
select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';
--Statement Audits Enabled on this Database
column user_name format a10
column audit_option format a40
select * from sys.dba_stmt_audit_opts;
--Privilege Audits Enabled on this Database
select * from dba_priv_audit_opts;
-- Object Audits Enabled on this Database
select (owner ||'.'|| object_name) object_name,
alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
from dba_obj_audit_opts
where alt != '-/-' or aud != '-/-'
or com != '-/-' or del != '-/-'
or gra != '-/-' or ind != '-/-'
or ins != '-/-' or loc != '-/-'
or ren != '-/-' or sel != '-/-'
or upd != '-/-' or ref != '-/-'
or exe != '-/-';
--Default Audits Enabled on this Database
select * from all_def_audit_opts;
Voir si les stats Oracle sont calculées sur mon schéma :
set lines 150
set pages 3000
col TABLE_NAME for a30
col INDEX_NAME for a30
col COLUMN_NAME for a30
define schema=&schema
spool stats_&schema
-- Voir les stats sur mes tables
select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = '&schema' order by table_name;
--Voir les stats sur mes indexes
select INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, LAST_ANALYZED from DBA_IND_STATISTICS where OWNER = '&schema' order by TABLE_NAME, INDEX_NAME;
--Voir les stats sur mes colonnes
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, LAST_ANALYZED, AVG_COL_LEN from DBA_TAB_COL_STATISTICS where owner = '&schema' order by TABLE_NAME, COLUMN_NAME;
spool off
exit
-- Mise en place des statistiques systèmes et os (sans mode 'concurrent true' !)
execute sys.dbms_stats.gather_system_stats('Start');
execute sys.dbms_stats.gather_system_stats('Stop');
execute sys.dbms_stats.gather_system_stats('interval',interval => 15); # durée de 60 mins
execute sys.dbms_stats.delete_system_stats();
-- Vérifier si les statistiques sont calculées
select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' order by 1;
Autre source d'infos :
https://fadace.developpez.com/oracle/statistiques-systeme/
http://dbaoraclesql.canalblog.com/archives/2019/03/30/37218417.html
http://www.nazmulhuda.info/migrate-database-from-one-rman-catalog-to-another-rman-catalog
Migrate database from One RMAN catalog to another RMAN catalog
|
#!/bin/bash input file format is: hostname USAGE: ./scan_oem_ports_remote_hosts.sh hostfile if test 'X'"$1" = 'X'; then echo -n "ENter full path and name of file containing host names: ";read file else file="$1" fi grep -v "^#" $file|while read host do if test 'X'"$host" = 'X'; then echo "Malformed line in input file, skipping, should be name $host" else RESULTS=$(ssh -n -q $host "timeout 5 bash -c 'oemap01.mydomain.com:4903 NOT open"}' if test "$RESULTS" != "OPEN"; then /usr/bin/dig +short $host | awk -v ip=$host '{print ip," " $NF " -->oemap01.mydomain.com 4903 NOT open"}' fi fi done
Other Tools you can use for the same instead of /dev/tcp
nc -vz -w5 hostname port (TCP)
nc -vzu -w5 hostname port (UDP)
Reference: https://superuser.com/questions/621870/test-if-a-port-on-a-remote-system-is-reachable-without-telnet
https://arunbavera.wordpress.com/category/em13c/
Patching OMS 13c Release 2 (13.2.0.0.181016) System PSU Patch :-
Download Link : OPatch version 13.9.3.3.0 for EMCC 13.x customers Only (not for FMW 12c) (Patch)
Download Link : OPatch version 13.9.3.3.0 for EMCC 13.x customers Only (not for FMW 12c) (Patch)
col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;
https://www.techrepublic.com/html/tr/sidebars/5138236-0.html
WITH
pgastat_denorm_1
AS
(
SELECT
/*+ MATERIALIZE NO_MERGE */
snap_id,
dbid,
instance_number,
SUM
(
CASE
name
WHEN
'PGA memory freed back to OS'
THEN
value
ELSE
0
END
) pga_mem_freed_to_os,
SUM
(
CASE
name
WHEN
'aggregate PGA auto target'
THEN
value
ELSE
0
END
) aggr_pga_auto_target,
SUM
(
CASE
name
WHEN
'aggregate PGA target parameter'
THEN
value
ELSE
0
END
) aggr_pga_target_param,
SUM
(
CASE
name
WHEN
'bytes processed'
THEN
value
ELSE
0
END
) bytes_processed,
SUM
(
CASE
name
WHEN
'extra bytes read/written'
THEN
value
ELSE
0
END
) extra_bytes_rw,
SUM
(
CASE
name
WHEN
'global memory bound'
THEN
value
ELSE
0
END
) global_memory_bound,
SUM
(
CASE
name
WHEN
'maximum PGA allocated'
THEN
value
ELSE
0
END
) max_pga_allocated,
SUM
(
CASE
name
WHEN
'maximum PGA used for auto workareas'
THEN
value
ELSE
0
END
) max_pga_used_aut_wa,
SUM
(
CASE
name
WHEN
'maximum PGA used for manual workareas'
THEN
value
ELSE
0
END
) max_pga_used_man_wa,
SUM
(
CASE
name
WHEN
'total PGA allocated'
THEN
value
ELSE
0
END
) tot_pga_allocated,
SUM
(
CASE
name
WHEN
'total PGA inuse'
THEN
value
ELSE
0
END
) tot_pga_inuse,
SUM
(
CASE
name
WHEN
'total PGA used for auto workareas'
THEN
value
ELSE
0
END
) tot_pga_used_aut_wa,
SUM
(
CASE
name
WHEN
'total PGA used for manual workareas'
THEN
value
ELSE
0
END
) tot_pga_used_man_wa,
SUM
(
CASE
name
WHEN
'total freeable PGA memory'
THEN
value
ELSE
0
END
) tot_freeable_pga_mem
FROM
dba_hist_pgastat
WHERE
name
IN
(
'PGA memory freed back to OS'
,
'aggregate PGA auto target'
,
'aggregate PGA target parameter'
,
'bytes processed'
,
'extra bytes read/written'
,
'global memory bound'
,
'maximum PGA allocated'
,
'maximum PGA used for auto workareas'
,
'maximum PGA used for manual workareas'
,
'total PGA allocated'
,
'total PGA inuse'
,
'total PGA used for auto workareas'
,
'total PGA used for manual workareas'
,
'total freeable PGA memory'
)
AND
snap_id
in
(
select
snap_id
from
dba_hist_snapshot
where
begin_interval_time > sysdate -60)
GROUP
BY
snap_id,
dbid,
instance_number
),
pgastat_denorm_2
AS
(
SELECT
/*+ MATERIALIZE NO_MERGE */
h.dbid,
h.instance_number,
s.startup_time,
MIN
(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
MIN
(h.bytes_processed) bytes_processed,
MIN
(h.extra_bytes_rw) extra_bytes_rw
FROM
pgastat_denorm_1 h,
dba_hist_snapshot s
WHERE
s.snap_id = h.snap_id
AND
s.dbid = h.dbid
AND
s.instance_number = h.instance_number
GROUP
BY
h.dbid,
h.instance_number,
s.startup_time
),
pgastat_delta
AS
(
SELECT
/*+ MATERIALIZE NO_MERGE */
h1.snap_id,
h1.dbid,
h1.instance_number,
s1.begin_interval_time,
s1.end_interval_time,
ROUND((
CAST
(s1.end_interval_time
AS
DATE
) -
CAST
(s1.begin_interval_time
AS
DATE
)) * 24 * 60 * 60) interval_secs,
(h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
h1.aggr_pga_auto_target,
h1.aggr_pga_target_param,
(h1.bytes_processed - h0.bytes_processed) bytes_processed,
(h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
h1.global_memory_bound,
h1.max_pga_allocated,
h1.max_pga_used_aut_wa,
h1.max_pga_used_man_wa,
h1.tot_pga_allocated,
h1.tot_pga_inuse,
h1.tot_pga_used_aut_wa,
h1.tot_pga_used_man_wa,
h1.tot_freeable_pga_mem
FROM
pgastat_denorm_1 h0,
pgastat_denorm_1 h1,
dba_hist_snapshot s0,
dba_hist_snapshot s1,
pgastat_denorm_2
min
/* to see cumulative use (replace h0 with min on select list above) */
WHERE
h1.snap_id = h0.snap_id + 1
AND
h1.dbid = h0.dbid
AND
h1.instance_number = h0.instance_number
AND
s0.snap_id = h0.snap_id
AND
s0.dbid = h0.dbid
AND
s0.instance_number = h0.instance_number
AND
s1.snap_id = h1.snap_id
AND
s1.dbid = h1.dbid
AND
s1.instance_number = h1.instance_number
AND
s1.snap_id = s0.snap_id + 1
AND
s1.startup_time = s0.startup_time
AND
s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60)))
/* filter out snaps apart < 1 min */
AND
min
.dbid = s1.dbid
AND
min
.instance_number = s1.instance_number
AND
min
.startup_time = s1.startup_time
)
SELECT
snap_id,
TO_CHAR(
MIN
(begin_interval_time),
'YYYY-MM-DD HH24:MI'
) begin_time,
TO_CHAR(
MIN
(end_interval_time),
'YYYY-MM-DD HH24:MI'
) end_time,
ROUND(
SUM
(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
ROUND(
SUM
(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
ROUND(
SUM
(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
ROUND(
SUM
(bytes_processed) / POWER(2, 30), 3) bytes_processed,
ROUND(
SUM
(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
ROUND(
SUM
(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
ROUND(
SUM
(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
ROUND(
SUM
(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
ROUND(
SUM
(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
ROUND(
SUM
(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
ROUND(
SUM
(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
ROUND(
SUM
(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
ROUND(
SUM
(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
ROUND(
SUM
(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
0 dummy_15
FROM
pgastat_delta
GROUP
BY
snap_id
ORDER
BY
snap_id;
https://xcoolwinds.wordpress.com/2013/08/23/adjusting-the-optimizer-index-cost-adj-parameter/
Issue the following query to view the average wait times for both of the wait events:
SQL> select event, average_wait from v$system_event
where event like 'db file s%read';
EVENT AVERAGE_WAIT
--------------------------------- -------------------------------
db file sequential read .91
db file scattered read 1.41
SQL>
Based on the output of this query, single block sequential reads take roughly 75% of the time it takes to perform a multiblock (scattered) read. This indicates that the optimizer_index_cost_adj
parameter should be set to somewhere around 75. However, as mentioned earlier, setting the parameter at the database level isn't recommended—instead, use this parameter sparingly for specific statements where you want to force the use of an index. Note that setting the parameter at the session level requires the DBA to grant the user privileges to issue the alter session
command (or you must do it through some other procedural mechanism). A good alternative to having to issue the alter session
statement is to specify the OPT_PARAM
hint to change the parameter for a specific query, such as /*+ opt_param('optimizer_index_cost_adj',50) */
.
La requête suivant permet de te lister la taille des partitions qui contiennent des LOBS :
SELECT DS.TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(DS.BYTES)/(1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE
DS.TABLESPACE_NAME not in ('SYSAUX','SYSTEM')
AND
SEGMENT_TYPE='LOB PARTITION'
GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME,PARTITION_NAME
ORDER BY 4;
La requête suivante permet de te lister la taille des partitions pour le segment TOTO :
SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE SEGMENT_NAME ='TOTO'
GROUP BY DS.TABLESPACE_NAME,
SEGMENT_NAME;
Je ne suis pas un escroc, je suis un opportuniste...
begin
for f in (select table_name from user_tables) loop
execute immediate 'drop table "'||f.table_name||'" cascade constraints';
end loop;
for f in (select object_type, object_name from user_objects
where object_type in ('SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM','DATABASE LINK')) loop
execute immediate 'drop '||f.object_type||' "'||f.object_name||'"';
end loop;
for f in (select type_name from user_types) loop
execute immediate 'drop type "'||f.type_name||'" force';
end loop;
end;
/