a dbas toolbox - doag

A DBAs ToolboxInsights in a DBA teams toolbox

Martin BergerDOAG 2017

Martin Berger

Oracle DBA since 2000

http://berxblog.blogspot.com@martinberx

[email protected]

SQL Scripts / Tricks?

SQL Scripts / Tricks?

not in this presentation

From Excel to…?

From Excel to …?

From Excel to APEX!

From Excel to APEX! - Logbook

From Excel to APEX! - Logbook- Logbook

recurring tasks

James!

Jamesjames is a script executor. nothing more nothing less.scripts are grouped into groups called workflows.workflows are grouped into categories.


● logging variable assigment: dgname=SVENCRYPTFS_ACFSvariable assigment: grid_version=12cvariable assigment: pattern=SVENCRYPTFS_000loading workflow specific variables.Full Oracle Cluster detected. Cluster name is crsxxx with cluster nodes "avxxxxxt avyyyyyt", remote nodes "avyyyyyt"

====> executing workflow diskgroup_create from category database from /zzz/james/workflows_database/diskgroup_create <====

========> executing workflow step /zzz/james/workflows_database/diskgroup_create/10.create_diskgroup.sh

SQL*Plus: Release 12.1.0.2.0 Production on Wed May 10 13:11:15 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Real Application Clusters and Automatic Storage Management options

Diskgroup created.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Real Application Clusters and Automatic Storage Management options


● logging● resumable

loading workflow specific variables.Full Oracle Cluster detected. Cluster name is crsxxx with cluster nodes "avxxxxxt avyyyyyt", remote nodes "avyyyyyt"

====> executing workflow rdbms_12102_Apr17BPJa from category install from /zzz/james/workflows_install/rdbms_12102_Apr17BPJa

resume directory /hhh/james/rdbms_12102_Apr17BPJa/resume exists. following step have been already executed:

20.prechecks.rdbms.12102.remote.sh… < some more steps> … 62.opatch.install.sh

hit Ctrl+C here and delete some files from /hhh/james/rdbms_12102_Apr17BPJa/resume if you want to reexecute some steps.

resume/continue the previous run? [Y(es)/(N)o]: <N>type (N)o again to confirm that you DO NOT want to continue in the workflow. workflow will restart from the beginning. [Y(es)/(N)o]: <N>


● logging● resumable● reusable functions

check_functions:check_directory_exists.sh check_kernel_param.sh check_line_in_file.sh check_swap_mb.shcheck_directory_space_mb.sh check_limit_max_locked_mem.sh check_mem_mb.sh check_user_is_oracle.shcheck_directory_writable.sh check_limit_max_proc.sh check_package.sh check_user_primary_group.shcheck_dns_for_entry.sh check_limit_mem_size.sh check_process.sh check_user_secondary_group.shcheck_files_writable.sh check_limit_open_files.sh check_root_execution.shcheck_hugepage_mem_size.sh check_limit_stack_size.sh check_shared_mem_size.sh



check_functions:check_directory_exists.sh check_kernel_param.sh check_line_in_file.sh check_swap_mb.shcheck_directory_space_mb.sh check_limit_max_locked_mem.sh check_mem_mb.sh check_user_is_oracle.shcheck_directory_writable.sh check_limit_max_proc.sh check_package.sh check_user_primary_group.shcheck_dns_for_entry.sh check_limit_mem_size.sh check_process.sh check_user_secondary_group.shcheck_files_writable.sh check_limit_open_files.sh check_root_execution.shcheck_hugepage_mem_size.sh check_limit_stack_size.sh check_shared_mem_size.sh

function check_package () {regexp_in=$1echo -n "checking for package ${regexp_in}"special_chars="\+"for char in ${special_chars} ; do

regexp_in=$(echo ${regexp_in} | sed -e "s/${char}/\\\\${char}/g")done# package=$(perlre_extract_string "${regexp_in}" "$(rpm -qa --queryformat '%{NAME}-%{VERSION}-%{RELEASE}

(%{ARCH})\n')")package=$(rpm -qa --queryformat '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' | egrep "${regexp_in}")if [[ ${package} != "" ]] ; then

echo " => OK (${package})"else

echo " => FAILED (${package})"echoerror=yes



misc_functions:adapt_template.sh detect_oracle_cluster.sh oratab_add_entry.sh oratab_remove_entry.sh

oracle_database:backup_vip.start.sh backup_vip.stop.sh

Jamesexisting categories:

installdatabase

clonepasswordsoraclehomegoldengate

griddwh

James - installavailable workflows:

client_12102_Jul16JCustom 12.1.0.2.0(JUL2014) + OJVM PSU 12.1.0.2.160719 + DB PSU 12.1.0.2.160719client installation with following components selected:SQLJ, Database Utilities, Java Client, SQL*Plus, JDBC/THIN Interfaces, OID Client, OCI,Programmer, XML Development Kit, Advanced Security, Oracle Net, Multimedia Client Option, ODBC Driverrelease build date 2016-10-03


ebs_rdbms_11204_Oct16a_1Oracle Database 11.2.0.4.161018 (OCT2016) EE + Examples + OJVM PSU 11.2.0.4.161018 (OCT2016)with required patches either for ebiz suite 12 and patches for encountered bugsas of release build date.release build date 2016-11-07

rdbms_12102_Apr17BPJaOracle Database 12.1.0.2.170418 BP (APR2017) EE + Examples +OJVM 12.1.0.2.170418 (APR2017) + encountered bugs

James - databaseavailable workflows:

backupvip_registerwill register given backup to GI

diskgroup_createwill create a diskgroup and mount it on all cluster nodes.

acfs_logdir_createwill create given database acfs log dirs across cluster

acfs_logdir_dropwill drop and deconfigure given database acfs log dirs

component_unload_11gunloads a component from a 11g database


createcreates an oracle database.includes all H3G AT specific features.

patch_11g_offlineOut of place patching of Oracle Database from 11.2.0.4.X to thegiven oracle home of 11.2.0.4 version. Offline patching that isdatabase will be shutdown and mounted in upgrade mode for timeof patching (needed for JVM).

patch_12c_offlineOut of place patching of Oracle Database from 12.1.0.2.X to thegiven oracle home of 12.1.0.2 version. Offline patching that isdatabase will be shutdown and mounted in upgrade mode for timeof patching (needed for JVM).

James - oraclehomeavailable workflows:

cloneworkflow to clone existing oracle home to other as different name

detachscript to detach oracle home

James - gridavailable workflows:

prepcheck_for_install_12102prepare and checks servers for upcoming grid 12.1.0.2 installation.

prepcheck_for_addnode_12102prepare server for upcoming oracle 12.1.0.2 cluster inclusion.

install_121020Install and configure from scratchOracle Grid Infrastructure 12.1.0.2.0 (JULY2014)

patch_12102X_12102Apr17BPPatching of Oracle Grid Infrastructure Clusterfrom version 12.1.0.2.X to 12.1.0.2.170418BP (APR2017)

add_node_12102integrates server to a 12.1.0.2 cluster

James - workflow structureupgrade_to_12201

+- description.txt+- variables.sh+- files

+- gridsetup.rsp.tmpl+- workflow+- 10.checks.sh -> checks.sh+- 15.checks.remote.sh+- 20.gridsetup_make_reponse_file.sh+- 25.gridsetup.sh+- checks.sh

James - workflow structurerdbms_12102_Apr17BPJa

+- description.txt+- variables.sh+- dist-Linux_x86_64

+- rdbms_121020+- rdbms_121020.rsp.meta+- examples_121020+- examples_121020.rsp.meta+- patches-Linux_x86_64+- p25433352_121020_Linux-x86-64.zip +- p20599273_121020_Generic.zip+- p20599273_121020_Generic.zip.prepatch.sh+- p16623661_12102160119DBEngSysandDBIM_Generic.zip...+- p25929518_12102170418ProactiveBP_Linux-x86-64.zip+- p25929546_12102170418ProactiveBP_Linux-x86-64.zip+- p25929584_12102170418ProactiveBP_Linux-x86-64.zip+- PATCH_ORIGINS.txt

James - workflow structure - cont.rdbms_12102_Apr17BPJa

+- workflow

20.prechecks.rdbms.12102.remote.sh 73.oratab.add_entry.remote.sh 95.cmdb.register_software.sh25.prechecks.rdbms.12102.sh 74.oratab.add_entry.sh 96.outro.sh32.makeresponsefile.rdbms_121020.sh _75.oracle_home.create_9idata.sh libobk.make_symlink.sh33.runinstaller.preinstall.rdbms_121020.sh _76.oracle_home.create_9idata.remote.sh opatch122.apply_patches.sh35.runinstaller.install.rdbms_121020.sh 78.postinstall.create_logdiag_dir.sh opatch122.install.sh38.rootsh.exec.remote.sh 80.setasmgidwrap.root.sh oracle_home.admin_dir.sh39.rootsh.exec.sh 81.setasmgidwrap.root.remote.sh oracle_home.create_9idata.sh42.makeresponsefile.examples_121020.sh 84.oracle_home.admin_dir.sh oracle_home.grid_logs_dir.sh45.runinstaller.install.examples_121020.sh 85.oracle_home.admin_dir.remote.sh oratab.add_entry.sh48.rootsh.exec.remote.sh 88.oracle_home.grid_logs_dir.remote.sh prechecks.rdbms.12102.sh49.rootsh.exec.sh 89.oracle_home.grid_logs_dir.sh rootsh.exec.sh60.opatch.install.remote.sh 90.tnsadmin.config.remote.sh setasmgidwrap.root.sh62.opatch.install.sh 92.tnsadmin.config.sh tnsadmin.config.sh70.opatch.apply_patches.remote.sh 93.libobk.make_symlink.remote.sh72.opatch.apply_patches.sh 94.libobk.make_symlink.sh

James - workflow structure - cont.rdbms_12102_Apr17BPJa

+- workflow

20.prechecks.rdbms.12102.remote.sh 73.oratab.add_entry.remote.sh 95.cmdb.register_software.sh25.prechecks.rdbms.12102.sh 74.oratab.add_entry.sh 96.outro.sh32.makeresponsefile.rdbms_121020.sh _75.oracle_home.create_9idata.sh libobk.make_symlink.sh33.runinstaller.preinstall.rdbms_121020.sh _76.oracle_home.create_9idata.remote.sh opatch122.apply_patches.sh35.runinstaller.install.rdbms_121020.sh 78.postinstall.create_logdiag_dir.sh opatch122.install.sh38.rootsh.exec.remote.sh 80.setasmgidwrap.root.sh oracle_home.admin_dir.sh39.rootsh.exec.sh 81.setasmgidwrap.root.remote.sh oracle_home.create_9idata.sh42.makeresponsefile.examples_121020.sh 84.oracle_home.admin_dir.sh oracle_home.grid_logs_dir.sh45.runinstaller.install.examples_121020.sh 85.oracle_home.admin_dir.remote.sh oratab.add_entry.sh48.rootsh.exec.remote.sh 88.oracle_home.grid_logs_dir.remote.sh prechecks.rdbms.12102.sh49.rootsh.exec.sh 89.oracle_home.grid_logs_dir.sh rootsh.exec.sh60.opatch.install.remote.sh 90.tnsadmin.config.remote.sh setasmgidwrap.root.sh62.opatch.install.sh 92.tnsadmin.config.sh tnsadmin.config.sh70.opatch.apply_patches.remote.sh 93.libobk.make_symlink.remote.sh72.opatch.apply_patches.sh 94.libobk.make_symlink.sh

30 steps

James - workflow structurepatch_12c_offline

+- description.txt+- variables.sh+- workflow

11.registry_check.sh 37.startup.sh 88.cmdb.oracle_db_change_home.sh12.checks.sh 38.run_datapatch.sh 90.outro.sh15.checks.remote.sh 40.update_libraries_paths.sh checks.sh17.oldhome_sqlpatch_copy.sh 41.run_utlrp.sh copy_dbs_files.remote.sh18.oldhome_sqlpatch_copy.remote.sh 42.cluster_database_true.sh copy_dbs_files.sh20.stop_backupvip.sh 45.shutdown.sh oldhome_sqlpatch_copy.remote.sh22.copy_dbs_files.sh 50.srvctl_modify_database.sh oldhome_sqlpatch_copy.sh23.copy_dbs_files.remote.sh 60.srvctl_start_database.sh oratab.modify.remote.sh30.stop_database.sh 80.start_backupvip.sh oratab.modify.sh35.oratab.modify.sh 85.oratab.modify.remote.sh36.cluster_database_false.sh 87.logbook_database.sh

Jameswhen not to use James?

Jameswhen not to use James?

cd $ORACLE_BASE/product ; tar xvf $INSTALL_DIR/Linux_x86-64/instantclient_11_2.tar

Multiple Systems?

Hydra (tame!)

TischregelnHydra (tame!)

Hydra (tame!)

Run Queue

round robin on (job.target)● total limit of heads● imits per job type:

○ maxruntime○ parallel○ target parallel (general)○ specific per target

Work (to be done) QUEUE

job.target.description

file content

Hydra (tame!)

ssh -o ConnectTimeout=15 -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -o ServerAliveInterval=30 -o TCPKeepAlive=yes -q -t somehost "somescript someparameters”

logs

Hydra (tame!)

logdir/<job>/<ziel>.<Beschreibung>.<YYYYMMDD>.out

2017-06-08 08:10:25 begin /var/tmp/hydra/rq/JOB.ZIEL.Beschreibung.head.12618.1496902225.3500The Oracle base has been set to /appl/oracle

PL/SQL procedure successfully completed.

2017-06-08 08:10:34 end /var/tmp/hydra/rq/JOB.ZIEL.Beschreibung.head.12618.1496902225.3500

………

All together …

All together …

BACKUP

● James

BACKUP

● consistent environment● perparation of DB, Directories, …

● James ● APEX

BACKUP

● Backup Config

● James ● APEX

BACKUP

● Backup Config● Backup Plan

● James ● APEX● Hydra

BACKUP

● working queue with backup tasks


BACKUP

● working queue with backup tasks● collects all results


BACKUP

● Backup Config● central LOG Information

● backupctl

BACKUP

● script (again)● takes care of “boring” syntax & parameters

● backupctl

BACKUP

backupctl

{backup|restore|duplicate|report|manage}

oracle@av3l958t:/appl/oracle/product/rdbms_11204_Apr17Ja/ [+ASM1] /appl/oracle/backupctl/backupctl backup

backup {database|archivelog|datafile|tablespace|controlfile} dbname=XYZ

commandline variables with (default values):

type=[full|level0|level1] () scope:database,datafile,tablespacesubsystem=[netbackup|disk|auxiliary] (netbackup) scope:alldeleteinput=[yes|no] (yes) scope:archivelogtablespaces=TS1,TS2,.. () scope:tablespacedatafiles=X,Y,Z,.. () scope:datafilecompression=[none|basic|medium|high] (basic) scope:allchannels=X (2) scope:alldstdir=/full/path () scope:all,valid when subsystem=disk or auxiliarytag= () scope:allnbh3gdefaults=[yes|no] (yes) scope:all,valid when sysbsystem=netbackupnb_ora_policy= () scope:all,valid when sysbsystem=netbackupnb_ora_client= () scope:all,valid when sysbsystem=netbackupdryrun=[yes|no] (yes) scope:allascopy=[yes|no] (no) scope:allmaxpiecesize= (100G) scope:allmaxopenfiles= () scope:allchannelextras="PARMS 'SBT_LIBRARY=/whatever.so,ENV=(WHATEVER=whatever)'" () scope:allauxiliary=sys/password@database () scope:all,valid when subsystem=auxiliaryswitchlog=[yes|no] (no) scope:all

● backupctl

BACKUP

backupctl

{backup|restore|duplicate|report|manage}

oracle@av3l958t:/appl/oracle/product/rdbms_11204_Apr17Ja/ [+ASM1] /appl/oracle/backupctl/backupctl backup

backup {database|archivelog|datafile|tablespace|controlfile} dbname=XYZ

commandline variables with (default values):

type=[full|level0|level1] () scope:database,datafile,tablespacesubsystem=[netbackup|disk|auxiliary] (netbackup) scope:alldeleteinput=[yes|no] (yes) scope:archivelogtablespaces=TS1,TS2,.. () scope:tablespacedatafiles=X,Y,Z,.. () scope:datafilecompression=[none|basic|medium|high] (basic) scope:allchannels=X (2) scope:alldstdir=/full/path () scope:all,valid when subsystem=disk or auxiliarytag= () scope:allnbh3gdefaults=[yes|no] (yes) scope:all,valid when sysbsystem=netbackupnb_ora_policy= () scope:all,valid when sysbsystem=netbackupnb_ora_client= () scope:all,valid when sysbsystem=netbackupdryrun=[yes|no] (yes) scope:allascopy=[yes|no] (no) scope:allmaxpiecesize= (100G) scope:allmaxopenfiles= () scope:allchannelextras="PARMS 'SBT_LIBRARY=/whatever.so,ENV=(WHATEVER=whatever)'" () scope:allauxiliary=sys/password@database () scope:all,valid when subsystem=auxiliaryswitchlog=[yes|no] (no) scope:all

./backupctl backup datafile type=level1 dbname=ROLIT01 subsystem=netbackup datafiles=1,3,5 using following netbackup environment settingsexport NB_ORA_SCHED=databasefilesexport NB_ORA_POLICY=ora_rolit01export NB_ORA_CLIENT=ROLIT01b-vip.it.internal

/appl/oracle/product/rdbms_12102_Apr17BPJb/bin/rman cmdfile=/appl/oracle/backupctl/backup.datafile.level1.ROLIT01.20171120.37270.rman

CONNECT TARGET /;CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';CONFIGURE MAXSETSIZE TO UNLIMITED;RUN { ALLOCATE CHANNEL CH1 TYPE SBT MAXOPENFILES 4 MAXPIECESIZE 100G ;ALLOCATE CHANNEL CH2 TYPE SBT MAXOPENFILES 4 MAXPIECESIZE 100G ;SEND "NB_ORA_SCHED=databasefiles";BACKUP AS COMPRESSED BACKUPSET FILESPERSET 64 INCREMENTAL LEVEL 1 DATAFILE 1,3,5 FORMAT 'ROLIT01_%I_DF1_20171120_%U' ;RELEASE CHANNEL CH1;RELEASE CHANNEL CH2;}

patches

patches

know your ORACLE_HOMEs

patches

know your ORACLE_HOMEs 1) store information centrally2) query it

patches


1) store information centrally2) query it

ohregistry list|register|unregister|patchdiff|bugdiff|listbugsfixed|listOHbyBug

+--------------------------------------------------------------------------------+| list: will list registered OHs. no other arguments available, needed |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| register: will register the current OH to the OH_REGISTRY table || required parameter is version=XX.YY.... see registered OH for inspiration || optional parameter name=OHname. if not supplied name is taken as basename of || current OH. you might want to explicitly specify name if in phase of building || the release |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| unregister: will unregister the current OH to the OH_REGISTRY table || optional parameter name=OHname. name of OH which to be unregistered. || if not supplied name is taken as basename of current OH. |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| patchdiff: will show differences in patches beetween two homes || home1=OHName home2=OHname are required parameters || optional parameter full=yes which will make full listing of patches. || that is also show intersecting ones |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| bugdiff: will show differences in bugs beetween two homes || |

patches



ohregistry register:

bugs_fixed_file=/tmp/${name}.bugs_fixed.xml${ORACLE_HOME}/OPatch/opatch lsinventory -bugs_fixed -xml ${bugs_fixed_file}

${SQLCL} "${connection_string}" << EOFscriptvar fileName="${bugs_fixed_file}"; var theBlob = conn.createBlob();var outBlob = theBlob.setBinaryStream(1);var path = java.nio.file.FileSystems.getDefault().getPath(fileName);theBlob.setBytes(1, java.nio.file.Files.readAllBytes(path));var HashMap = Java.type("java.util.HashMap");bind_map = new HashMap(); bind_map.put("theblob", theBlob);bind_map.put("name", "${name}"); bind_map.put("version", "${version}");try {var retval = util.execute("insert into oh_registry (name, version, bugs_fixed)

values (:name, :version, blob2clob(:theblob))", bind_map);} catch (e) { ctx.write("\n\n ERROR:" + e + "\n\n"); }sqlcl.setStmt("select name, version, dbms_lob.getlength(bugs_fixed)

from oh_registry where name='${name}'");sqlcl.run();/commit;exitEOF

patches



./ohregistry list

NAME VERSION-------------------------------- --------------------------------ebs_rdbms_11204_Oct16a_1 11.2.0.4.161017PSUebs_rdbms_11204_Oct16a_1_ADG 11.2.0.4.161017PSUebs_rdbms_11204_Oct16a_1_ADG_D01 11.2.0.4.161017PSUebs_rdbms_11204_Oct16a_1_ADG_P01 11.2.0.4.161017PSUrdbms_11204_Apr17Ja 11.2.0.4.170418PSUrdbms_11204_Apr17Jb 11.2.0.4.170418PSUrdbms_11204_Oct17Ja 11.2.0.4.171017PSUrdbms_112048Ja 11.2.0.4.8PSUrdbms_12102160119Jc 12.1.0.2.160119PSUrdbms_12102_Jan17BPJa 12.1.0.2.170117BPebs_rdbms_12102_Apr17a_1 12.1.0.2.170418BPebs_rdbms_12102_Apr17b_1 12.1.0.2.170418BPebs_rdbms_12102_Apr17c_1 12.1.0.2.170418BPebs_rdbms_12102_Apr17d_1 12.1.0.2.170418BPrdbms_12102_Apr17BPJa 12.1.0.2.170418BPrdbms_12102_Apr17BPJb 12.1.0.2.170418BPrdbms_12102_Apr17BPJc 12.1.0.2.170418BPrdbms_12102_Apr17BPJd 12.1.0.2.170418BPrdbms_12102_Aug17BPJa_beta2 12.1.0.2.170418BPrdbms_12102_Aug17BPJa_beta1 12.1.0.2.170814PSUrdbms_12102_Oct17BPJa 12.1.0.2.171017BPrdbms_12102_Oct17BPJa_beta1 12.1.0.2.171017BPrdbms_12102_Oct17BPJa_beta2 12.1.0.2.171017BPrdbms_12102_Oct17BPJa_beta3 12.1.0.2.171017BP

patches



bugdiff:========./ohregistry bugdiff home1=rdbms_12102_Oct17BPJa_beta1 \

home2=rdbms_12102_Oct17BPJa_beta5

rdbms_12102_Oct17BPJa_beta1 rdbms_12102_Oct17BPJa_beta5 DESCRIPTION--------------------------- --------------------------- ----------------------------------------------------------------

20807398 ORA-00600 [KGL-HASH-COLLISION] WITH FIX TO BUG 2046558221385422 12C DB ORA-46264 DURING DBMS_AUDIT_MGMT OPERATION21529241 DBMS_STATS ORA-06502 PL/SQL NUMERIC OR VALUE ERROR26516536 REMOVE REDUNDANT HBB GETS AND OPTIMIZE KTSLA_FSG_UNLINK()

patches



listOHbyBug:============

22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES

./ohregistry listOHbyBug bug=22652097

NAME PATCHID PATCHDESCRIPTION BUG BUGDESCRIPTION--------------------------- -------- --------------------- -------- --------------rdbms_12102_Apr17BPJa 25929584 MERGE REQUEST ... 22652097 PROVIDE SEPARA...rdbms_12102_Apr17BPJb 25929584 MERGE REQUEST ... 22652097 PROVIDE SEPARA...rdbms_12102_Apr17BPJc 25929584 MERGE REQUEST ... 22652097 PROVIDE SEPARA...rdbms_12102_Apr17BPJd 25929584 MERGE REQUEST ... 22652097 PROVIDE SEPARA...rdbms_12102_Aug17BPJa_beta2 27011973 MERGE REQUEST ... 22652097 PROVIDE SEPARA...rdbms_12102_Jan17BPJa 25635590 MERGE REQUEST ... 22652097 PROVIDE SEPARA...rdbms_12102_Oct17BPJa 26717470 DBBP: 12.1.0.2.171017 22652097 PROVIDE SEPARA...rdbms_12102_Oct17BPJa_beta1 26717470 DBBP: 12.1.0.2.171017 22652097 PROVIDE SEPARA...rdbms_12102_Oct17BPJa_beta2 26717470 DBBP: 12.1.0.2.171017 22652097 PROVIDE SEPARA...rdbms_12102_Oct17BPJa_beta3 26717470 DBBP: 12.1.0.2.171017 22652097 PROVIDE SEPARA...rdbms_12102_Oct17BPJa_beta4 26717470 DBBP: 12.1.0.2.171017 22652097 PROVIDE SEPARA...rdbms_12102_Oct17BPJa_beta5 26717470 DBBP: 12.1.0.2.171017 22652097 PROVIDE SEPARA...rdbms_12102_Oct17BPJb_beta1 26717470 DBBP: 12.1.0.2.171017 22652097 PROVIDE SEPARA...

patches



listOHbyBug:============

WITH dim_1AS (SELECT name as OH_NAME, xml1.patchid,

Nvl(xml1.patchdescription, (SELECT descriptionFROM oracle_patches_manualdescWHERE patchnum = xml1.patchid))

patchdescription, bugsFROM oh_registry,XMLTABLE( '/InventoryInstance/patches/patch' passing xmltype(bugs_fixed)

COLUMNSpatchid NUMBER path 'patchID'

, bugs xmltype path 'bugs', patchdescriptionVARCHAR2(64) path 'patchDescription' ) xml1 )

SELECT dim_1.OH_NAME as NAME, patchid, patchdescription, bug, bugdescriptionFROM dim_1,

XMLTABLE( '//bug' passing bugs COLUMNSbug NUMBER path '@id'

, bugdescription VARCHAR(64) path 'description' )WHERE bug= to_number('${bug}')ORDER BY dim_1.OH_NAME,

patchid,bug;

insert into oh_registry (name, version, bugs_fixed) values (:name, :version, blob2clob(:theblob))

patches

know your patches


register_patch.sh

unzip ${full_patchfile} "*/inventory.xml" "*/actions.xml" "*/README*" "*/bundle.xml" > /dev/null 2>&1cd ${patchnum} > /dev/null

readme_txt=$(find . -maxdepth 1 -type f -name "README.txt")readme_html=$(find . -maxdepth 1 -type f -name "README.html")

if [[ ${readme_txt} != "" ]] ; thenecho " found README.txt"readme_txt_info=$(sed -n -e 's/.*$${patchnum}:.*$/\1/p' ${readme_txt} | tr -echo " patch info from README.txt: ${readme_txt_info}"if [[ "${readme_txt_info}" != "" ]] ; then

patch_description="${readme_txt_info}"patch_infosource="readmetxt"

fifi

if [[ ${readme_html} != "" ]] ; thenecho " found README.html"readme_html_info="$( ${XIDEL} -q --extract //title ${readme_html} | tr -d '\n' )"echo " patch info from README.html: ${readme_html_info}"if [[ "${readme_html_info}" != "" ]] ; then

patch_description="${readme_html_info}"patch_infosource="readmehtml"

fifi

patches

know your patches


patchgistry list|listbugs|patchfixesbug|patchbugdiff|patchconflict|patchconflict_oh

+--------------------------------------------------------------------------------+| list: will list known patches. no other arguments available, needed |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| listbugs: will list known bugs. no other arguments available, needed |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| listpatchbugs: will list fixed in given patch || patch=patchnum is a required parameters |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| patchfixesbug: will show which patches are fixing the given bug || bugnum=bugnum is a required parameters |+--------------------------------------------------------------------------------+...

+--------------------------------------------------------------------------------+| patchconflict: will show conflicting actions beetween two patches || patch1=patchnum patch2=patchnum are required parameters |+--------------------------------------------------------------------------------+

+--------------------------------------------------------------------------------+| patchconflict_oh: will show conflicting actions beetween a patch || and a oracle home from OH registry (see ohregistry list for names) || patch=patchnum home=OHName |+--------------------------------------------------------------------------------+

patches

know your patches


./patchregistry patchfixesbug bugnum=22652097

BUGNUM DESCRIPTION---------- ---------------------------------------------------------------------22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTI...

single patchesPATCHNUM DESCRIPTION

---------- ---------------------------------------------------------------------26886035 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 21156...27011973 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170718 FOR BUGS 26165...25929584 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 18650...... 26898071 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170718 FOR BUGS 21156...26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470)

14 rows selected.

composite patchesPATCHNUM DESCRIPTION

---------- ------------------------------------------------------------------26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470)

bundle patchesPATCHNUM DESCRIPTION

---------- ------------------------------------------------------------------26635880 OracleÂ® Database Patch 26635880 - Database Proactive Bundle Patch...

patches

know your patches


./patchregistry listpatchbugs patch=25929584

BUGNUM BUG_DESCRIPTION---------- ----------------------------------------------------------------------------------------------------18650065 WRONG RESULTS ON QUERY WITH SUBQUERY USING OR EXISTS18961555 STATIC PL/SQL BASELINE REPRODUCTION BROKEN BY FIX 1802039419450139 KN LNX PERFORMANCE ISSUE WHEN RUNNING GATHER TABLE STATS WITH INCREMENTAL STATS20508819 WRONG RESULTS OR ORA-7445 WHEN _OPTIMIZER_AGGR_GROUPBY_ELIM=TRUE20807398 ORA-00600 [KGL-HASH-COLLISION] WITH FIX TO BUG 2046558221171382 AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY21529241 DBMS_STATS ORA-06502 PL/SQL NUMERIC OR VALUE ERROR21826068 WRONG RESULTS STILL WHEN _OPTIMIZER_AGGR_GROUPBY_ELIM=TRUE22324460 ORA-600 QKSANGETTEXTSTR 1 AND CORRUPT PLANS WITH SPM22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES22913528 WRONG RESULTS WITH PARTITION PRUNING AND MIN/MAX SCANS

11 rows selected.

patches

know your patches


./patchregistry patchbugdiff patch1=25929584 patch2=27011973

25929584_BUGS 27011973_BUGS BUG_DESCRIPTION------------- ------------- ----------------------------------------------------------------------------------------------------

20508819 WRONG RESULTS OR ORA-7445 WHEN _OPTIMIZER_AGGR_GROUPBY_ELIM=TRUE21826068 WRONG RESULTS STILL WHEN _OPTIMIZER_AGGR_GROUPBY_ELIM=TRUE

patches

know your patches


./patchregistry patchconflict_oh patch=20925154 home=rdbms_12102_Oct17BPJb_beta1

...oh patches:

PATCH PATCH_DESCRIPTION...49 rows selected.

vs patch:

... PATCH PATCH_DESCRIPTION--- ----------------------------------------------------------------------------------------------------

20925154 ORA-39126 WORKER UNEXPECTED FATAL ERROR IN KUPW$WORKER.GATHER_PARSE_ITEMS JAVA

conflicts on:

ACTION ARGUMENTS--------------- ----------------------------------------------------------------------------------------------------copy %ORACLE_HOME%/rdbms/admin/prvtbpw.plb

1 row selected.

conflict details:PATCHDESC ACTION ARGUMENTS---------------------... ------- --------------------------------------------------20925154 - ORA-39126 ... copy %ORACLE_HOME%/rdbms/admin/prvtbpw.plb25740076 - copy %ORACLE_HOME%/rdbms/admin/prvtbpw.plb2 rows selected.

Q&AMartin BergerDOAG 2017

From Excel to APEX!



misc_functions:adapt_template.sh detect_oracle_cluster.sh oratab_add_entry.sh oratab_remove_entry.sh

oracle_database:backup_vip.start.sh backup_vip.stop.sh

if [[ ${skipclusterdetect} != "yes" ]] ; thenoracle_cluster_detected=""oracle_cluster_name=""oracle_cluster_node_list=""oracle_restart_detected=""oracle_cluster_remote_nodes=""oracle_crs_home=""

fi

function detect_oracle_cluster () {if [[ ${skipclusterdetect} == "yes" ]] ; then

returnfi# echo "detecting cluster"if [[ -f ${inventory_location}/ContentsXML/inventory.xml ]] ; then

# echo "found inventory.xml in ${inventory_location}/ContentsXML/inventory.xml"


cman_121020Oracle Connection Manager 12.1.0.2.0 also sqlplus


rdbms_12102_Apr17BPJaOracle Database 12.1.0.2.170418 BP (APR2017) EE + Examples +OJVM 12.1.0.2.170418 (APR2017) + encountered bugs


backupvip_registerwill register given backup to GI

component_unload_11gunloads a component from a 11g database

diskgroup_createwill create a diskgroup and mount it on all cluster nodes.

James - gridavailable workflows:

install_121020Install and configure from scratchOracle Grid Infrastructure 12.1.0.2.0 (JULY2014)

patch_12102X_12102Apr17BPPatching of Oracle Grid Infrastructure Clusterfrom version 12.1.0.2.X to 12.1.0.2.170418BP (APR2017)

a dbas toolbox - doag

Documents