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
Anuj Singh Oracle DBA
Search This Blog
Total Pageviews
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)
Oracle DBA
anuj blog Archive
- ► 2011 (362)