Septembre 2019

08h54 24 sept. 2019

Migrate database from One RMAN catalog to another RMAN catalog

http://www.nazmulhuda.info/migrate-database-from-one-rman-catalog-to-another-rman-catalog

Migrate database from One RMAN catalog to another RMAN catalog

In that scenario we will move catalog from stagecat(source catalog) to target database CLOUD.

[oracle@rmancatalog2 ~]$ rman target / catalog=rman/kjh7Rjsr1

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 21 09:28:47 2016

connected to target database: CLOUD (DBID=1801493836)

connected to recovery catalog database

RMAN> list db_unique_name all;

List of Databases
DB Key DB Name DB ID            Database Role    Db_unique_name
------- ------- ----------------- --------------- ------------------
49382       DB2   3896599226       PRIMARY          DB2
49382        DB2   3896599226       STANDBY          DB1
1                DB1   4139331099       PRIMARY          DB1
1                DB1   4139331099       STANDBY          DB2

SQL> select * from rman.rc_database;

DB_KEY	DBINC_KEY	DBID	NAME	RESELOGS_CHANGE#	RESETLOGS_TIME
1	247199	4139331099	DB1	116371882675	06/07/2015 18:23
49382	49383	3896599226	DB2	90051108188	09/02/2012 16:18

RMAN> import catalog rman/U98wsjdw2#@stagecat;

Starting import catalog at 2016-12-21 09:36:33

connected to source recovery catalog database

import validation complete

Finished import catalog at 2016-12-21 09:36:40

RMAN> list db_unique_name all;

List of Databases
DB Key DB Name DB ID            Database Role    Db_unique_name
------- ------- ----------------- --------------- ------------------
548781 DB3     829073653        PRIMARY          DB3
514674 DB4      1753676295       PRIMARY          DB4
523618 DB5      3779067219       PRIMARY          DB5
551022 DB6      4018492764       PRIMARY          DB6
545408 DB7      4110887114       PRIMARY          DB7

SQL> select * from rman.rc_database;

DB_KEY	DBINC_KEY	DBID	NAME	RESELOGS_CHANGE#	RESETLOGS_TIME
1	247199	4139331099	DB1	116371882675	06/07/2015 18:23:36
49382	49383	3896599226	DB2	90051108188	09/02/2012 16:18:57
548781	548782	829073653	DB3	1	08/09/2015 09:48:37
514674	514675	1753676295	DB4	1	29/06/2015 09:19:03
523618	523619	3779067219	DB5	1	27/08/2015 12:55:47
551022	551023	4018492764	DB6	1	02/09/2015 09:18:20
545408	545409	4110887114	DB7	1	22/06/2015 13:41:30

On the Source Catalog, the database will be automatically unregistered after the IMPORT CATALOG. If you need to retain the catalog on the source side even after the import then use keyword NO UNREGISTER.

Example:

RMAN> import catalog rman/U98wsjdw2#@stagecat no unregister;

17h08 23 sept. 2019

Checking Firewall Ports on remote Host with SSH auth

#!/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/

17h07 23 sept. 2019
Patching OEM 13c Release 2 PSU for OMS and Agent

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)
15h04 20 sept. 2019
optimizer_index_cost_adj.sql

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

source : https://www.techrepublic.com/article/database-optimization-tweaking-oracle-sql-performance-parameters/#

https://www.techrepublic.com/html/tr/sidebars/5138236-0.html
15h02 20 sept. 2019
PGA_AGGREGATE_TARGET vs PGA_AGGREGATE_LIMIT

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;

Captured and imported the result into Excel spreadsheet. Then I created the following two charts showing PGA allocation and usage for the past 60 days and 7 days.

Source :https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/
10h34 20 sept. 2019
Paramètre obsolète
https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:
- OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
- OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default
https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2
09h36 20 sept. 2019
Adjusting the optimizer_index_cost_adj Parameter

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) */.
11h37 18 sept. 2019
PLug in sqlserver pour OEM 13c

https://docs.oracle.com/cd/E63000_01/SQLPG/overview.htm#SQLPG108

https://docs.oracle.com/cd/E24628_01/doc.121/e24473/plugin_mngr.htm#EMADM13102
11h34 18 sept. 2019
OEM Agent 12c: Common issues and resolutions

https://developer.rackspace.com/blog/oemagent12c/

CPU too heavy
09h38 13 sept. 2019
Constraint NOT NULL missing

Data Pump Import With EXCLUDE=CONSTRAINT Or INCLUDE=CONSTRAINT Is Excluding And Respectively Not Including NOT NULL Constraints (Doc ID 1930631.1)

After doing a Data Pump import (impdp) in 12.1.0.2 database release with parameter EXCLUDE=CONSTRAINT the NOT NULL constraints from source tables are not in target tables. Sequence of steps leading to the problem:
– Data Pump export of tables with NOT NULL and referential integrity constraints
– Data Pump import with EXCLUDE=CONSTRAINT

http://blog.umairmansoob.com/data-dump-import-missing-not-null-constraints/

[ORACLE] BACKUP

[ORACLE] Check DB Activity HTML version

[ORACLE] Check DB Activity

[ORACLE] Diagnostic from the PID (top CPU) ->...

[ORACLE] Diagnostic from the PID (top CPU) ->...

[PostgreSQL]pg_auto_failover

[POSTGRESQL] Les extensions indispensables

Coffre - Fort pour les identifiants mots de passe

Anonymization & Data Masking for PostgreSQL

Calculate the value for memory postgresql...

Septembre 2019

Migrate database from One RMAN catalog to another RMAN catalog

Migrate database from One RMAN catalog to another RMAN catalog

Migrate database from One RMAN catalog to another RMAN catalog

Checking Firewall Ports on remote Host with SSH auth

Patching OEM 13c Release 2 PSU for OMS and Agent

optimizer_index_cost_adj.sql

PGA_AGGREGATE_TARGET vs PGA_AGGREGATE_LIMIT

Paramètre obsolète

Adjusting the optimizer_index_cost_adj Parameter

PLug in sqlserver pour OEM 13c

OEM Agent 12c: Common issues and resolutions

Constraint NOT NULL missing