from https://www.ludovicocaldara.net/dba/real-memory-usage-on-linux/
cat mem.sh
#!/bin/bash
username=`whoami`
username=oracle
sids=`ps -eaf | grep "^$username" | grep pmon | grep -v " grep " | awk '{print substr($NF,10)}'`
total=0
for sid in $sids ; do
pids=`ps -eaf | grep "^$username" | grep -- "$sid" | grep -v " grep " | awk '{print $2}'`
mem=`pmap $pids 2>&1 | grep "K " | sort | awk '{print $1 " " substr($2,1,length($2)-1)}' | uniq | awk ' BEGIN { sum=0 } { sum+=$2} END {print sum}' `
echo "$sid : $mem"
total=`expr $total + $mem`
done
echo "total : $total"
./mem.sh
ugaryd : 6790308
ugary : 9707680
irac1 : 23071180
total : 39569168
Monday 14 October 2024
Oracle Instances memory consumption on Linux
Patch 36582781 - Database Release Update 19.24.0.0.240716
Patch 36582781 - Database Release Update 19.24.0.0.240716 ....
Oracle Database 19c Proactive Patch Information (Doc ID 2521164.1) Patch 36582781 - Database Release Update 19.24.0.0.240716
===============================================================================
Patch 36582781: DATABASE RELEASE UPDATE 19.24.0.0.0
Last Updated Jul 16, 2024 6:06 AM (2+ months ago)
Product Oracle Database - Enterprise Edition
(More...)
Release Oracle Database 19.0.0.0.0
Platform Linux x86-64
Size 1.8 GB file size
==================================================
[oracle@srv1 36582781]$ pwd
/home/oracle/36582781
[oracle@srv1 36582781]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@srv1 36582781]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/19.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.0.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.42
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2024-10-14_07-50-20AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[oracle@srv1 36582781]$
[oracle@srv1 36582781]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 14 07:54:15 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
[oracle@srv1 36582781]$ pwd
/home/oracle/36582781
[oracle@srv1 36582781]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.0.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.42
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2024-10-14_07-55-29AM_1.log
Verifying environment and performing prerequisite checks...
--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...
Oracle Interim Patch Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.0.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.42
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2024-10-14_07-58-08AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 36582781
Do you want to proceed? [y|n]
Could not recognize input. Please re-enter.
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '36582781' to OH '/u01/app/oracle/product/19.0.0/db_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.precomp.companion, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.rdbms, 19.0.0.0.0...
Patching component oracle.rdbms.util, 19.0.0.0.0...
Patching component oracle.rdbms.rsf, 19.0.0.0.0...
Patching component oracle.assistants.acf, 19.0.0.0.0...
Patching component oracle.assistants.deconfig, 19.0.0.0.0...
Patching component oracle.assistants.server, 19.0.0.0.0...
Patching component oracle.blaslapack, 19.0.0.0.0...
Patching component oracle.buildtools.rsf, 19.0.0.0.0...
Patching component oracle.ctx, 19.0.0.0.0...
Patching component oracle.dbdev, 19.0.0.0.0...
Patching component oracle.dbjava.ic, 19.0.0.0.0...
Patching component oracle.dbjava.jdbc, 19.0.0.0.0...
Patching component oracle.dbjava.ucp, 19.0.0.0.0...
Patching component oracle.duma, 19.0.0.0.0...
Patching component oracle.javavm.client, 19.0.0.0.0...
Patching component oracle.ldap.owm, 19.0.0.0.0...
Patching component oracle.ldap.rsf, 19.0.0.0.0...
Patching component oracle.ldap.security.osdt, 19.0.0.0.0...
Patching component oracle.marvel, 19.0.0.0.0...
Patching component oracle.network.rsf, 19.0.0.0.0...
Patching component oracle.odbc.ic, 19.0.0.0.0...
Patching component oracle.ons, 19.0.0.0.0...
Patching component oracle.ons.ic, 19.0.0.0.0...
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patching component oracle.perlint, 5.28.1.0.0...
Patching component oracle.precomp.common.core, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf.ic, 19.0.0.0.0...
Patching component oracle.precomp.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.crs, 19.0.0.0.0...
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
Patching component oracle.rdbms.deconfig, 19.0.0.0.0...
Patching component oracle.rdbms.oci, 19.0.0.0.0...
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...
Patching component oracle.rdbms.scheduler, 19.0.0.0.0...
Patching component oracle.rhp.db, 19.0.0.0.0...
Patching component oracle.rsf, 19.0.0.0.0...
Patching component oracle.sdo, 19.0.0.0.0...
Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...
Patching component oracle.sqlplus, 19.0.0.0.0...
Patching component oracle.sqlplus.ic, 19.0.0.0.0...
Patching component oracle.wwg.plsql, 19.0.0.0.0...
Patching component oracle.xdk.rsf, 19.0.0.0.0...
Patching component oracle.ovm, 19.0.0.0.0...
Patching component oracle.rdbms.drdaas, 19.0.0.0.0...
Patching component oracle.oraolap, 19.0.0.0.0...
Patching component oracle.rdbms.dv, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf.lbuilder, 19.0.0.0.0...
Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...
Patching component oracle.rdbms.hs_common, 19.0.0.0.0...
Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...
Patching component oracle.install.deinstalltool, 19.0.0.0.0...
Patching component oracle.oraolap.api, 19.0.0.0.0...
Patching component oracle.xdk.parser.java, 19.0.0.0.0...
Patching component oracle.mgw.common, 19.0.0.0.0...
Patching component oracle.network.listener, 19.0.0.0.0...
Patching component oracle.xdk.xquery, 19.0.0.0.0...
Patching component oracle.ldap.ssl, 19.0.0.0.0...
Patching component oracle.rdbms.rman, 19.0.0.0.0...
Patching component oracle.odbc, 19.0.0.0.0...
Patching component oracle.sdo.locator, 19.0.0.0.0...
Patching component oracle.ldap.client, 19.0.0.0.0...
Patching component oracle.rdbms.install.common, 19.0.0.0.0...
Patching component oracle.ctx.atg, 19.0.0.0.0...
Patching component oracle.rdbms.dm, 19.0.0.0.0...
Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...
Patching component oracle.xdk, 19.0.0.0.0...
Patching component oracle.javavm.server, 19.0.0.0.0...
Patching component oracle.dbtoolslistener, 19.0.0.0.0...
Patching component oracle.rdbms.locator, 19.0.0.0.0...
Patching component oracle.ctx.rsf, 19.0.0.0.0...
Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...
Patching component oracle.network.client, 19.0.0.0.0...
Patching component oracle.rdbms.rat, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.lbac, 19.0.0.0.0...
Patching component oracle.precomp.common, 19.0.0.0.0...
Patching component oracle.precomp.lang, 19.0.0.0.0...
Patching component oracle.jdk, 1.8.0.201.0...
Patch 36582781 successfully applied.
Sub-set patch [36233263] has become inactive due to the application of a super-set patch [36582781].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2024-10-14_07-58-08AM_1.log
OPatch succeeded.
[oracle@srv1 36582781]$
[oracle@srv1 36582781]$
**********************************************************************************************
show pdbs;
SQL> alter pluggable database all open;
**************************************************************************************************
./datapatch -sanity_checks
SQL Patching sanity checks version 19.24.0.0.0 on Mon 14 Oct 2024 08:16:05 AM +04
Copyright (c) 2021, 2024, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20241014_081605_9239/sanity_checks_20241014_081605_9239.log
Running checks
JSON report generated in /u01/app/oracle/cfgtoollogs/sqlpatch/sanity_checks_20241014_081605_9239/sqlpatch_sanity_checks_summary.json file
Checks completed. Printing report:
Check: Database component status - OK
Check: PDB Violations - OK
Check: Invalid System Objects - OK
Check: Tablespace Status - OK
Check: Backup jobs - OK
Check: Temp file exists - OK
Check: Temp file online - OK
Check: Data Pump running - OK
Check: Container status - OK
Check: Oracle Database Keystore - OK
Check: Dictionary statistics gathering - OK
Check: Scheduled Jobs - OK
Check: GoldenGate triggers - OK
Check: Logminer DDL triggers - OK
Check: Check sys public grants - OK
Check: Statistics gathering running - OK
Check: Optim dictionary upgrade parameter - OK
Check: Symlinks on oracle home path - OK
Check: Central Inventory - OK
Check: Queryable Inventory dba directories - OK
Check: Queryable Inventory locks - OK
Check: Queryable Inventory package - OK
Check: Queryable Inventory external table - OK
Check: Imperva processes - OK
Check: Guardium processes - OK
Check: Locale - OK
SQL Patching sanity checks completed on Mon 14 Oct 2024 08:17:23 AM +04
[oracle@srv1 OPatch]$
**********************************************************************************************
show pdbs;
SQL> alter pluggable database all open;
**************************************************************************************************
[oracle@srv1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.24.0.0.0 Production on Mon Oct 14 08:18:27 2024
Copyright (c) 2012, 2024, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10162_2024_10_14_08_18_27/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 31219897 (OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)):
Binary registry: Installed
PDB CDB$ROOT: Applied successfully on 10-SEP-20 12.34.31.168250 PM
PDB PDB$SEED: Applied successfully on 10-SEP-20 12.34.40.711871 PM
PDB PDB1: Applied successfully on 10-SEP-20 12.41.43.472665 PM
Current state of release update SQL patches:
Binary registry:
19.24.0.0.0 Release_Update 240627235157: Installed
PDB CDB$ROOT:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 20-APR-24 10.05.35.931655 PM
PDB PDB$SEED:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 20-APR-24 10.09.18.365114 PM
PDB PDB1:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 20-APR-24 10.09.18.242487 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1
No interim patches need to be rolled back
Patch 36582781 (Database Release Update : 19.24.0.0.240716 (36582781)):
Apply from 19.23.0.0.0 Release_Update 240406004238 to 19.24.0.0.0 Release_Update 240627235157
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 36582781 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_ORADB_CDBROOT_2024Oct14_08_22_11.log (no errors)
Patch 36582781 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_ORADB_PDBSEED_2024Oct14_08_23_53.log (no errors)
Patch 36582781 apply (pdb PDB1): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_ORADB_PDB1_2024Oct14_08_23_53.log (no errors)
SQL Patching tool complete on Mon Oct 14 08:26:00 2024
[oracle@srv1 OPatch]$
[oracle@srv1 OPatch]$
[oracle@srv1 OPatch]$
[oracle@srv1 OPatch]$
[oracle@srv1 OPatch]$
[oracle@srv1 OPatch]$
[oracle@srv1 OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 14 08:27:39 2024
Version 19.24.0.0.0 --------------------------------------------------------
show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
du -sh */
3.8G 36582781/
12K Desktop/
0 Documents/
3.8G Downloads/
0 Music/
0 Pictures/
0 Public/
0 Templates/
0 Videos/
[oracle@srv1 ~]$ rm -rf 36582781/
Tuesday 24 September 2024
Sql plan changed !!!
unstable plans
set lines 200 pages 9999
col execs for 999,999,999
col min_etime for 999,999.99
col max_etime for 999,999.99
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
from (
select sql_id, plan_hash_value, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
group by sql_id, plan_hash_value
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2)
and max_etime > nvl(to_number('&min_etime'),.1)
order by norm_stddev
/
-- whats_changed_c.sql
DEF days_of_history_accessed = '5';
DEF captured_at_least_x_times = '1';
DEF captured_at_least_x_days_apart = '5';
DEF med_elap_microsecs_threshold = '1e4';
DEF min_slope_threshold = '0.1';
DEF max_num_rows = '20';
SET lin 300 ver OFF;
set linesize 500
COL row_n format A2 HEA '#';
COL change format A13 justify left;
COL slope format 9999.99 HEA 'SLOPE' justify right;
COL pctdbtim format 99.99 HEA 'Total Elapsed|% DB Time' justify right;
COL med_secs_per_exec format a15 HEA 'Median Secs|Per Exec' justify right;
COL std_secs_per_exec format a15 HEA 'Std Dev Secs|Per Exec' justify right;
COL avg_secs_per_exec format a15 HEA 'Avg Secs|Per Exec' justify right;
COL min_secs_per_exec format a15 HEA 'Min Secs|Per Exec' justify right;
COL max_secs_per_exec format a15 HEA 'Max Secs|Per Exec' justify right;
COL plans format 9999 justify right;
COL sql_text_80 format A80 justify left;
PRO SQL Statements with "Elapsed Time per Execution" changing over time
WITH
per_time AS
(
SELECT
h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE) days_ago,
SUM(h.elapsed_time_delta) / SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) time_per_exec,
--SUM(h.elapsed_time_delta) time_per_exec,
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) execs
FROM
dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND h.parsing_schema_name NOT IN ('SYS','SYSTEM','SYSTEM2','DBSNMP')
AND h.executions_delta > 0
AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
GROUP BY
h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE)
HAVING
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) >= 1
)
,
db_time AS
(
SELECT
tdbtim
FROM
(
(
SELECT
e.stat_name ,
(e.value - NVL(b.value,0)) value
FROM
dba_hist_sys_time_model b ,
dba_hist_sys_time_model e
WHERE
e.dbid = b.dbid
AND e.instance_number = b.instance_number
AND e.snap_id =
(
SELECT
MAX(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.snap_id =
(
SELECT
MIN(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.stat_id = e.stat_id
AND e.stat_name IN ('DB time','DB CPU',
'background elapsed time','background cpu time')
)
pivot (SUM(value) FOR stat_name IN ('DB time' tdbtim)) )
)
,
sql_dbt AS
(
SELECT
h.sql_id,
ROUND(h.time_per_exec*100/d.tdbtim,2) pctdbtim
FROM
(
SELECT
sql_id,
SUM(time_per_exec*execs) time_per_exec
FROM
per_time
GROUP BY
sql_id
)
h,
db_time d
)
,
avg_time AS
(
SELECT
dbid,
sql_id,
MEDIAN(time_per_exec) med_time_per_exec,
STDDEV(time_per_exec) std_time_per_exec,
AVG(time_per_exec) avg_time_per_exec,
MIN(time_per_exec) min_time_per_exec,
MAX(time_per_exec) max_time_per_exec
FROM
per_time
GROUP BY
dbid,
sql_id
HAVING
COUNT(*) >= &&captured_at_least_x_times.
--AND MAX(days_ago) - MIN(days_ago) >=
-- &&captured_at_least_x_days_apart.
AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold.
)
,
time_over_median AS
(
SELECT
h.dbid,
h.sql_id,
h.days_ago,
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
a.med_time_per_exec,
a.std_time_per_exec,
a.avg_time_per_exec,
a.min_time_per_exec,
a.max_time_per_exec
FROM
per_time h,
avg_time a
WHERE
a.sql_id = h.sql_id
)
,
ranked AS
(
SELECT
RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med,t.days_ago)) DESC) rank_num,
t.dbid,
t.sql_id,
CASE
WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0
THEN 'IMPROVING'
ELSE 'REGRESSING'
END change,
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3)
slope,
ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
FROM
time_over_median t
GROUP BY
t.dbid,
t.sql_id
HAVING
ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT
row_n,
r.sql_id,
change,
slope,
s.pctdbtim,
lpad(med_secs_per_exec,15,' ') med_secs_per_exec,
lpad(std_secs_per_exec,15,' ') std_secs_per_exec,
lpad(avg_secs_per_exec,15,' ') avg_secs_per_exec,
lpad(min_secs_per_exec,15,' ') min_secs_per_exec,
lpad(max_secs_per_exec,15,' ') max_secs_per_exec,
plans,
sql_text_80
FROM
(
SELECT
LPAD(ROWNUM, 2) row_n,
r.sql_id,
r.change,
--TO_CHAR(r.slope, '990.000MI') slope,
ROUND(r.slope,2) slope,
--TO_CHAR(s.pctdbtim,'99.9999') ela_pct_dbtime,
TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec,
TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec,
TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec,
TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec,
TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec,
(
SELECT
COUNT(DISTINCT p.plan_hash_value)
FROM
dba_hist_sql_plan p
WHERE
p.dbid = r.dbid
AND p.sql_id = r.sql_id
)
plans,
REPLACE(
(
SELECT distinct
sys.DBMS_LOB.SUBSTR(s.sql_text, 80)
FROM
dba_hist_sqltext s
WHERE
s.dbid = r.dbid
AND s.sql_id = r.sql_id
)
, CHR(10)) sql_text_80
FROM
ranked r
WHERE
r.rank_num <= &&max_num_rows.
ORDER BY
r.rank_num
)
r,
sql_dbt s
WHERE
r.sql_id = s.sql_id
ORDER BY
row_n
/
Total Elapsed Median Secs Std Dev Secs Avg Secs Min Secs Max Secs
# SQL_ID CHANGE SLOPE % DB Time Per Exec Per Exec Per Exec Per Exec Per Exec PLANS SQL_TEXT_80
-- ------------- ------------- -------- ------------- --------------- --------------- --------------- --------------- --------------- ----- --------------------------------------------------------------------------------
1 00nxwgnnnhd9z REGRESSING -.62 .15 151.788 140.202 222.579 131.888 384.062 2 SELECT AUDITSIGNATURE,SEQUENCEGENERATORPOOLNAME,SEQUENCEGENERATORID,SEQUENCENUMB
2 02jrgb8ppzbpx IMPROVING .13 .32 262.992 168.498 285.982 53.514 523.788 0 call FTRESS_FT.purge_exp_sessions ( )
COL force_matching_signature format 999999999999999999999999 HEA 'FORCE_MATCHING' justify right;
WITH
per_time AS
(
SELECT
h.dbid,
--h.sql_id,
h.force_matching_signature,
SYSDATE - CAST(s.end_interval_time AS DATE) days_ago,
SUM(h.elapsed_time_delta) / SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) time_per_exec,
--SUM(h.elapsed_time_delta) time_per_exec,
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) execs
FROM
dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE
h.executions_delta > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND h.parsing_schema_name NOT IN ('SYS','SYSTEM','SYSTEM2', 'DBSNMP')
AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
GROUP BY
h.dbid,
--h.sql_id,
h.force_matching_signature,
SYSDATE - CAST(s.end_interval_time AS DATE)
HAVING
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) >= 1
)
,
db_time AS
(
SELECT
tdbtim,
tdbcpu,
tbgtim,
tbgcpu
FROM
(
(
SELECT
e.stat_name ,
(e.value - NVL(b.value,0)) value
FROM
dba_hist_sys_time_model b ,
dba_hist_sys_time_model e
WHERE
e.dbid = b.dbid
AND e.instance_number = b.instance_number
AND e.snap_id =
(
SELECT
MAX(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.snap_id =
(
SELECT
MIN(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.stat_id = e.stat_id
AND e.stat_name IN ('DB time','DB CPU' ,'background elapsed time','background cpu time')
)
pivot (SUM(value) FOR stat_name IN ('DB time' tdbtim ,'DB CPU' tdbcpu ,'background elapsed time' tbgtim ,'background cpu time' tbgcpu)))
)
,
sql_dbt AS
(
SELECT
--h.sql_id,
h.force_matching_signature,
ROUND(h.time_per_exec*100/d.tdbtim,2) pctdbtim
FROM
(
SELECT
--sql_id,
force_matching_signature,
SUM(time_per_exec*execs) time_per_exec
FROM
per_time
GROUP BY
--sql_id
force_matching_signature
)
h,
db_time d
)
,
avg_time AS
(
SELECT
dbid,
--sql_id,
force_matching_signature,
MEDIAN(time_per_exec) med_time_per_exec,
STDDEV(time_per_exec) std_time_per_exec,
AVG(time_per_exec) avg_time_per_exec,
MIN(time_per_exec) min_time_per_exec,
MAX(time_per_exec) max_time_per_exec
FROM
per_time
GROUP BY
dbid,
--sql_id
force_matching_signature
HAVING
COUNT(*) >= &&captured_at_least_x_times.
--AND MAX(days_ago) - MIN(days_ago) >=
-- &&captured_at_least_x_days_apart.
AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold.
)
,
time_over_median AS
(
SELECT
h.dbid,
--h.sql_id,
h.force_matching_signature,
h.days_ago,
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
a.med_time_per_exec,
a.std_time_per_exec,
a.avg_time_per_exec,
a.min_time_per_exec,
a.max_time_per_exec
FROM
per_time h,
avg_time a
--WHERE a.sql_id = h.sql_id
WHERE
a.force_matching_signature = h.force_matching_signature
)
,
ranked AS
(
SELECT
RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med,
t.days_ago)) DESC) rank_num,
t.dbid,
--t.sql_id,
t.force_matching_signature,
CASE
WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0
THEN 'IMPROVING'
ELSE 'REGRESSING'
END change,
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3)
slope,
ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
FROM
time_over_median t
GROUP BY
t.dbid,
--t.sql_id
t.force_matching_signature
HAVING
ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT
row_n,
--r.sql_id,
r.force_matching_signature,
change,
slope,
s.pctdbtim,
lpad(med_secs_per_exec,15,' ') med_secs_per_exec,
lpad(std_secs_per_exec,15,' ') std_secs_per_exec,
lpad(avg_secs_per_exec,15,' ') avg_secs_per_exec,
lpad(min_secs_per_exec,15,' ') min_secs_per_exec,
lpad(max_secs_per_exec,15,' ') max_secs_per_exec
-- plans,
--sql_text_80
FROM
(
SELECT
LPAD(ROWNUM, 2) row_n,
--r.sql_id,
r.force_matching_signature,
r.change,
--TO_CHAR(r.slope, '990.000MI') slope,
ROUND(r.slope,2) slope,
--TO_CHAR(s.pctdbtim,'99.9999') ela_pct_dbtime,
TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec,
TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec,
TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec,
TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec,
TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec
--(SELECT COUNT(DISTINCT p.plan_hash_value) FROM
-- dba_hist_sql_plan p WHERE p.dbid = r.dbid AND p.sql_id =
-- r.sql_id) plans,
--REPLACE((SELECT sys.DBMS_LOB.SUBSTR(s.sql_text, 80) FROM
-- dba_hist_sqltext s WHERE s.dbid = r.dbid AND s.sql_id =
-- r.sql_id), CHR(10)) sql_text_80
FROM
ranked r
WHERE
r.rank_num <= &&max_num_rows.
ORDER BY
r.rank_num
)
r,
sql_dbt s
WHERE
r.force_matching_signature = s.force_matching_signature
ORDER BY
row_n
/
Total Elapsed Median Secs Std Dev Secs Avg Secs Min Secs Max Secs
# FORCE_MATCHING CHANGE SLOPE % DB Time Per Exec Per Exec Per Exec Per Exec Per Exec
-- ------------------------- ------------- -------- ------------- --------------- --------------- --------------- --------------- ---------------
1 15936051349132279638 REGRESSING -.62 .15 151.788 140.202 222.579 131.888 384.062
SQL>
min_elapsed_time
define min_elapsed_time=10
define min_repeat_executions_filter=10
col 1 FOR 99999
col 2 FOR 99999
col 3 FOR 9999
col 4 FOR 999
col 5 FOR 99
col av FOR 99999
col ct FOR 99999
col mn FOR 999
col av FOR 99999.9
col MAX_RUN_TIME FOR a40
col longest_sql_exec_id FOR A20
set linesize 500
set pages 9999
WITH pivot_data AS
(SELECT sql_id,
ct,
mxdelta mx,
mndelta mn,
ROUND(avdelta) av,
WIDTH_BUCKET(delta_in_seconds,mndelta,mxdelta+.1,5) AS bucket ,
SUBSTR(times,12) max_run_time,
SUBSTR(longest_sql_exec_id, 12) longest_sql_exec_id
FROM
(SELECT sql_id,
delta_in_seconds,
COUNT(*) OVER (PARTITION BY sql_id) ct,
MAX(delta_in_seconds) OVER (PARTITION BY sql_id) mxdelta,
MIN(delta_in_seconds) OVER (PARTITION BY sql_id) mndelta,
AVG(delta_in_seconds) OVER (PARTITION BY sql_id) avdelta,
MAX(times) OVER (PARTITION BY sql_id) times,
MAX(longest_sql_exec_id) OVER (PARTITION BY sql_id) longest_sql_exec_id
FROM
(SELECT sql_id,
sql_exec_id,
MAX(delta_in_seconds) delta_in_seconds ,
LPAD(ROUND(MAX(delta_in_seconds),0),10)
|| ' '
|| TO_CHAR(MIN(start_time),'YY-MM-DD HH24:MI:SS')
|| ' '
|| TO_CHAR(MAX(end_time),'YY-MM-DD HH24:MI:SS') times,
LPAD(ROUND(MAX(delta_in_seconds),0),10)
|| ' '
|| TO_CHAR(MAX(sql_exec_id)) longest_sql_exec_id
FROM
( SELECT sql_id, TO_CHAR(sql_exec_id)||'_'||to_char(sql_exec_start,'J') sql_exec_id,
CAST(sample_time AS DATE) end_time,
CAST(sql_exec_start AS DATE) start_time,
((CAST(sample_time AS DATE)) - (CAST(sql_exec_start AS DATE))) * (3600*24) delta_in_seconds
FROM dba_hist_active_sess_history
WHERE sql_exec_id IS NOT NULL
AND sql_id='00nxwgnnnhd9z'
)
GROUP BY sql_id,
sql_exec_id
)
)
WHERE ct >
&min_repeat_executions_filter
AND mxdelta >
&min_elapsed_time )
SELECT *
FROM pivot_data PIVOT ( COUNT(*) FOR bucket IN (1,2,3,4,5))
ORDER BY mx DESC,
av DESC ;
5
------------- ------ ---------- ---- -------- ---------------------------------------- -------------------- ------ ------ ----- ---- ---
00nxwgnnnhd9z 43 1123 411 995.0 24-07-30 16:32:52 24-07-30 16:51:35 16777217_2460522 2 1 0 3 37
SQL>
from https://blog.go-faster.co.uk/2019/10/purging-sql-statements-and-execution.html
set lines 300 pages 99 col costpctdiff heading 'Cost|%Diff' format 99999 col costdiff heading 'Cost|Diff' format 99999999 col plan_hash_value heading 'SQL Plan|Hash Value' col child_number heading 'Child|No.' format 9999 col inst_id heading 'Inst|ID' format 999 col hcost heading 'AWR|Cost' format 99999999999 col ccost heading 'Cursor|Cost' format 9999999 col htimestamp heading 'AWR|Timestamp' col ctimestamp heading 'Cursor|Timestamp' col end_interval_time format a26 col snap_id heading 'Snap|ID' format 99999999 col awr_cost format 9999999999999 col optimizer_Cost heading 'Opt.|Cost' format 99999999 col optimizer_env_hash_value heading 'Opt. Env.|Hash Value' col num_stats heading 'Num|Stats' format 9999 alter session set nls_date_format = 'hh24:mi:ss dd.mm.yy'; break on plan_hash_value skip 1 on sql_id on dbid ttitle 'compare AWR/recent plan costs' with h as ( /*captured plan outside retention limit*/ select p.dbid, p.sql_id, p.plan_hash_Value, max(cost) cost , max(p.timestamp) timestamp from dba_hist_sql_plan p , dba_hist_wr_control c where p.dbid = c.dbid and p.cost>0 and (p.object_owner != 'SYS' OR p.object_owner IS NULL) --omit SYS owned objects and p.timestamp < sysdate-c.retention group by p.dbid, p.sql_id, p.plan_hash_value ), s as ( /*SQL statistics*/ select t.dbid, t.sql_id, t.plan_hash_value, t.optimizer_env_hash_value , t.optimizer_cost , MIN(t.snap_id) snap_id , MIN(s.end_interval_time) end_interval_time , COUNT(*) num_stats from dba_hist_snapshot s , dba_hist_sqlstat t where s.dbid = t.dbid and s.snap_id = t.snap_id and t.optimizer_cost > 0 GROUP BY t.dbid, t.sql_id, t.plan_hash_value, t.optimizer_env_hash_value , t.optimizer_cost ), x as ( Select NVL(h.dbid,s.dbid) dbid , NVL(h.sql_id,s.sql_id) sql_id , NVL(h.plan_hash_value,s.plan_hash_value) plan_hash_value , h.cost hcost, h.timestamp htimestamp , s.snap_id, s.end_interval_time , s.optimizer_env_hash_value, s.optimizer_cost , s.num_stats , s.optimizer_cost-h.cost costdiff , 100*s.optimizer_cost/NULLIF(h.cost,0) costpctdiff From h join s on h.plan_hash_value = s.plan_hash_value and h.sql_id = s.sql_id and h.dbid = s.dbid ), y as ( SELECT x.* , MAX(ABS(costpctdiff)) OVER (PARTITION BY dbid, sql_id, plan_hash_value) maxcostpctdiff , MAX(ABS(costdiff)) OVER (PARTITION BY dbid, sql_id, plan_hash_value) maxcostabsdiff FROM x ) SELECT dbid, sql_id, plan_hash_value, hcost, htimestamp , snap_id, end_interval_time, optimizer_env_hash_value, optimizer_cost, num_stats, costdiff, costpctdiff FROM y WHERE maxcostpctdiff>=10 And maxcostabsdiff>=10 order by plan_hash_value,sql_id,end_interval_time / break on report ttitle off
Saturday 31 August 2024
Create database via 19c dbca
Create database via 19c dbca ...
[oracle@ora2 ~]$ hostname ora2 check below first !! export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/dell/srvadmin/bin:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/u01/app/oracle/product/19.0.0/dbhome_3/bin
check this
which dbca
/u01/app/oracle/product/19.0.0/dbhome_3/bin/dbca
dbca -silent -nodelist 'ora2,ora3' -createDatabase -responseFile NO_VALUE -gdbName ORCLX -templateName General_Purpose.dbc -characterSet AL32UTF8 -datafileDestination '+DATA' -sid ORCLX -redoLogFileSize 50 -systemPassword welcome1 -sysPassword welcome1 -enableArchive true -createAsContainerDatabase true -pdbName UTIL01 -numberOfPDBs 1 -useLocalUndoForPDBs true -pdbAdminPassword welcome1 -recoveryAreaDestination '+DATA' -recoveryAreaSize 8024 -sampleSchema false -databaseType MULTIPURPOSE -nationalCharacterSet AL16UTF16 -databaseConfigType RAC -automaticMemoryManagement false -initParams SGA_TARGET='5000M' PGA_AGGREGATE_TARGET='200M'
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06801] Specified Fast Recovery Area size (8,024 MB) is less than the recommended value.
CAUSE: Fast Recovery Area size should at least be three times the database size (3,328 MB).
ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
Prepare for db operation
7% complete
Copying database files
27% complete
Creating and starting Oracle instance
28% complete
31% complete
35% complete
37% complete
40% complete
Creating cluster database views
41% complete
53% complete
Completing Database Creation
57% complete
59% complete
60% complete
Creating Pluggable Databases
64% complete
80% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/ORCLX.
Database Information:
Global Database Name:ORCLX
System Identifier(SID) Prefix:ORCLX
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCLX/ORCLX.log" for further details.
INSTANCE_NUMBER.............................................: 1
INSTANCE_NAME...............................................: ORCLX1
HOST_NAME...................................................: ora2
VERSION.....................................................: 19.0.0.0.0
STARTUP_TIME................................................: 31-08-2024 08:48:58
STATUS......................................................: OPEN
PARALLEL....................................................: YES
THREAD#.....................................................: 1
ARCHIVER....................................................: STARTED
LOG_SWITCH_WAIT.............................................:
LOGINS......................................................: ALLOWED
SHUTDOWN_PENDING............................................: NO
DATABASE_STATUS.............................................: ACTIVE
INSTANCE_ROLE...............................................: PRIMARY_INSTANCE
ACTIVE_STATE................................................: NORMAL
BLOCKED.....................................................: NO
INSTANCE_NUMBER.............................................: 2
INSTANCE_NAME...............................................: ORCLX2
HOST_NAME...................................................: ora3
VERSION.....................................................: 19.0.0.0.0
STARTUP_TIME................................................: 31-08-2024 08:48:59
STATUS......................................................: OPEN
PARALLEL....................................................: YES
THREAD#.....................................................: 2
ARCHIVER....................................................: STARTED
LOG_SWITCH_WAIT.............................................:
LOGINS......................................................: ALLOWED
SHUTDOWN_PENDING............................................: NO
DATABASE_STATUS.............................................: ACTIVE
INSTANCE_ROLE...............................................: PRIMARY_INSTANCE
ACTIVE_STATE................................................: NORMAL
BLOCKED.....................................................: NO
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 5008M
sga_min_size big integer 0
sga_target big integer 5008M
unified_audit_sga_queue_size integer 1048576
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 UTIL01 READ WRITE NO
Tuesday 20 August 2024
Rman duplicate pluggable database
Rman duplicate pluggable database ...
on same server !!!!
ORCL:/u01/app/oracle/product/19.0.0/dbhome_1:N
orclcd:/u01/app/oracle/product/19.0.0/dbhome_1:N
ORCL:/u01/app/oracle/product/19.0.0/dbhome_1:N
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO >>> aim to duplicate pluggable database -------------------------------------->
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
orclcd:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@ ~]$ . oraenv
ORACLE_SID = [ORCL] ? orclcd
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 19 12:38:57 2024
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPBB READ WRITE NO <<<<< and plug here <<<------------------------------------------------------------ be="" end="" like="" result="" sql="" this="" would=""> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPBB READ WRITE NO
4 ORCLPDBD READ WRITE NO
SQL>
rman
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 20 03:55:26 2024
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
connected to target database: ORCL (DBID=1618442726)
RMAN> connect auxiliary sys/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=orclcd)))
connected to auxiliary database: ORCLCD (DBID=2923364259)
RMAN> DUPLICATE PLUGGABLE DATABASE ORCLPDB as ORCLPDBD TO orclcd FROM ACTIVE DATABASE ;
Starting Duplicate PDB at 20-AUG-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=855 device type=DISK
current log archived
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
current log archived
contents of Memory Script:
{
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 12 to new;
restore
from nonsparse clone foreign pluggable database
"ORCLPDB"
from service '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))' ;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-AUG-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring foreign file 9 to +DATA/ORCLCD/D2909A01684E4D42E053F214A8C08735/DATAFILE/system.335.1177474125
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring foreign file 10 to +DATA/ORCLCD/D2909A01684E4D42E053F214A8C08735/DATAFILE/sysaux.334.1177474129
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring foreign file 11 to +DATA/ORCLCD/D2909A01684E4D42E053F214A8C08735/DATAFILE/undotbs1.333.1177474133
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring foreign file 12 to +DATA/ORCLCD/D2909A01684E4D42E053F214A8C08735/DATAFILE/users.332.1177474133
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-AUG-24
contents of Memory Script:
{
set archivelog destination to '+DATA';
restore clone force from service '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))'
foreign archivelog from scn 78208744;
}
executing Memory Script
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 20-AUG-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to user-specified destination
archived log destination=+DATA
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1925
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 20-AUG-24
Performing import of metadata...
Finished Duplicate PDB at 20-AUG-24
===
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPBB READ WRITE NO
4 ORCLPDBD READ WRITE NO
SQL>
=======
Error !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
RMAN> DUPLICATE PLUGGABLE DATABASE ORCLPDB as ORCLPDBD TO orclcd FROM ACTIVE DATABASE SECTION SIZE 400M; <<<< SECTION SIZE 400M; remove this check output above
Starting Duplicate PDB at 20-AUG-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=607 device type=DISK
current log archived
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
current log archived
contents of Memory Script:
{
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 12 to new;
restore
from nonsparse section size
400 m clone foreign pluggable database
"ORCLPDB"
from service '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host= anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))' ;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-AUG-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:13
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473013' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 9 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473013' contains an invalid file number
continuing other job steps, job failed will not be re-run
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:13
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473015' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 10 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473015' contains an invalid file number
continuing other job steps, job failed will not be re-run
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:14
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473017' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 11 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473017' contains an invalid file number
continuing other job steps, job failed will not be re-run
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=anujlab.com)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:14
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473019' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 12 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473019' contains an invalid file number
continuing other job steps, job failed will not be re-run
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate PDB command at 08/20/2024 03:50:14
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:14
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473019' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 12 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:14
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473017' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 11 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:13
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473015' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 10 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/
RMAN-03009: failure of IRESTORE command on ORA_AUX_DISK_1 channel at 08/20/2024 03:50:13
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/DATAFILE/.335.1177473013' contains an invalid file number
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 9 could not be verified
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15122: ASM file name '+DATA/ORCLCD/D
Thursday 1 August 2024
Unix split command
Unix split command
Created 1gb file for testing
dd if=/dev/urandom of=anuj.trc bs=64M count=16 iflag=fullblock
16+0 records in
16+0 records out
1073741824 bytes (1.1 GB) copied, 65.8006 s, 16.3 MB/s
[oracle@rac01:~] $ls -ltr anuj.trc
-rw-r--r-- 1 oracle oinstall 1073741824 Aug 1 04:46 anuj.trc
[oracle@ibrac01:~] $ls -lhtr anuj.trc
-rw-r--r-- 1 oracle oinstall 1.0G Aug 1 04:46 anuj.trc
Split the file 400m chunks
[oracle@rac01:~] $
tar cz anuj.trc | split -b 400m - anuj.trc_split
rac01:~] $ls -ltrh anuj*
-rw-r--r-- 1 oracle oinstall 1.0G Aug 1 04:46 anuj.trc
-rw-r--r-- 1 oracle oinstall 400M Aug 1 04:49 anuj.trc_splitaa
-rw-r--r-- 1 oracle oinstall 400M Aug 1 04:49 anuj.trc_splitab
-rw-r--r-- 1 oracle oinstall 225M Aug 1 04:50 anuj.trc_splitac
delete the old file to test !!!
rm anuj.trc
To Join
cat anuj.trc_split* | tar xz
ls -ltrh anuj*
-rw-r--r-- 1 oracle oinstall 1.0G Aug 1 04:46 anuj.trc <<<<<----Now file is back
-rw-r--r-- 1 oracle oinstall 400M Aug 1 04:49 anuj.trc_splitaa
-rw-r--r-- 1 oracle oinstall 400M Aug 1 04:49 anuj.trc_splitab
-rw-r--r-- 1 oracle oinstall 225M Aug 1 04:50 anuj.trc_splitac
Tuesday 16 July 2024
show_dumpfile_info
from Web
CREATE OR REPLACE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir = directory object where dump file can be found
-- p_file = simple filename of export dump file (case-sensitive)
v_separator VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path all_directories.directory_path%type := '?';
v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp 3=ext
v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 (etc.)
v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
type valtype IS VARRAY(23) OF VARCHAR2(2048);
var_values valtype := valtype();
no_file_found EXCEPTION;
PRAGMA exception_init(no_file_found, -39211);
BEGIN
-- Dump file details:
-- ==================
-- For Oracle10g Release 2 and higher:
-- dbms_datapump.KU$_DFHDR_FILE_VERSION CONSTANT NUMBER := 1;
-- dbms_datapump.KU$_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2;
-- dbms_datapump.KU$_DFHDR_GUID CONSTANT NUMBER := 3;
-- dbms_datapump.KU$_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4;
-- dbms_datapump.KU$_DFHDR_CHARSET_ID CONSTANT NUMBER := 5;
-- dbms_datapump.KU$_DFHDR_CREATION_DATE CONSTANT NUMBER := 6;
-- dbms_datapump.KU$_DFHDR_FLAGS CONSTANT NUMBER := 7;
-- dbms_datapump.KU$_DFHDR_JOB_NAME CONSTANT NUMBER := 8;
-- dbms_datapump.KU$_DFHDR_PLATFORM CONSTANT NUMBER := 9;
-- dbms_datapump.KU$_DFHDR_INSTANCE CONSTANT NUMBER := 10;
-- dbms_datapump.KU$_DFHDR_LANGUAGE CONSTANT NUMBER := 11;
-- dbms_datapump.KU$_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12;
-- dbms_datapump.KU$_DFHDR_DIRPATH CONSTANT NUMBER := 13;
-- dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
-- dbms_datapump.KU$_DFHDR_DB_VERSION CONSTANT NUMBER := 15;
-- For Oracle11gR1:
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT CONSTANT NUMBER := 16;
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
-- dbms_datapump.KU$_DFHDR_DATA_COMPRESSED CONSTANT NUMBER := 18;
-- dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED CONSTANT NUMBER := 19;
-- dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED CONSTANT NUMBER := 20;
-- For Oracle11gR2:
-- dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED CONSTANT NUMBER := 21;
-- dbms_datapump.KU$_DFHDR_ENCRIPTION_MODE CONSTANT NUMBER := 22;
-- For Oracle12cR1:
-- dbms_datapump.KU$_DFHDR_COMPRESSION_ALG CONSTANT NUMBER := 23;
-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 22;
-- For Oracle12cR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 23;
-- Show header output info:
-- ========================
dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile. Version: 18-DEC-2013');
dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
dbms_output.put_line('. ' ||
'Export dumpfile version: 7.3.4.0.0 or higher');
dbms_output.put_line('. ' ||
'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
dbms_output.put_line('Usage....: ' ||
'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
dbms_output.put_line('Example..: ' ||
'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
dbms_output.put_line(v_separator);
dbms_output.put_line('Filename.: ' || p_file);
dbms_output.put_line('Directory: ' || p_dir);
-- Retrieve Export dumpfile details:
-- =================================
SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);
dbms_datapump.get_dumpfile_info(
filename => p_file, directory => UPPER(p_dir),
info_table => v_info_table, filetype => v_filetype);
var_values.EXTEND(23);
FOR i in 1 .. 23 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := '';
END;
END LOOP;
dbms_output.put_line('Disk Path: ' || v_path);
IF v_filetype >= 1 THEN
-- Get characterset name:
BEGIN
SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
')' INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
'1', '1 (Direct Path)', var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Direct Path Export Mode.......: ' || var_values(13));
dbms_output.put_line('...Export Version................: ' || var_values(15));
ELSIF v_filetype = 1 OR v_filetype = 3 THEN
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
'0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
'4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Password and Wallet)',
'5', '5 (Wallet)',
var_values(22)) INTO var_values(22) FROM dual;
SELECT DECODE(var_values(23),
'2', '2 (None)',
'3', '3 (Basic)',
'4', '4 (Low)',
'5', '5 (Medium)',
'6', '6 (High)',
var_values(23)) INTO var_values(23) FROM dual;
IF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Master Present in dump file...: ' || var_values(2));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN
dbms_output.put_line('...Master in how many dump files.: ' || var_values(16));
dbms_output.put_line('...Master Piece Number in file...: ' || var_values(17));
END IF;
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...Job Name......................: ' || var_values(8));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
dbms_output.put_line('...Metadata Compressed...........: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
END IF;
dbms_output.put_line('...Metadata Encrypted............: ' || var_values(19));
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Column Data Encrypted.........: ' || var_values(21));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
ELSE
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (External Table dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
END IF;
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
END IF;
dbms_output.put_line('...Internal Flag Values..........: ' || var_values(7));
dbms_output.put_line('...Max Items Code (Info Items)...: ' ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line('Filetype.: ' || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Not an export dumpfile.');
END IF;
dbms_output.put_line(v_separator);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Disk Path: ?');
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Directory Object does not exist.');
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line('Disk Path: ' || v_path);
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: File does not exist.');
dbms_output.put_line(v_separator);
END;
/
SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'DATA_PUMP_DIR', p_file=> 'to.dmp');
Filetype.: ?
----------------------------------------------------------------------------
ERROR....: File does not exist.
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
Subscribe to:
Posts (Atom)
