Thinking Out Loud

November 20, 2019

What’s Taking So Long For Autoupgrade

Filed under: Uncategorized,upgrade — mdinh @ 2:53 pm

Directory for autoupgrade/log: $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log, where $ORACLE_UNQNAME=db_unique_name

Create upgrade.config as shown: $ORACLE_BASE/admin/$ORACLE_UNQNAME/${ORACLE_UNQNAME}_upgrade.config

global.autoupg_log_dir=$ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log
upg1.log_dir=$ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log
upg1.dbname=$ORACLE_UNQNAME
upg1.sid=$ORACLE_SID
upg1.start_time=NOW
upg1.source_home=/oracle/app/product/11.2/dbhome_1
upg1.target_home=/oracle/app/product/12.2/dbhome_1
upg1.upgrade_node=localhost
upg1.target_version=12.2
upg1.timezone_upg=no
upg1.restoration=yes

Let’s take a took at summary for autograde job process 102.
Find autoupgrade directories.

$ export JOBNO=102
$ ls -l $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/*/*/*
-rwx------    1 oracle   dba           73349 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/autoupgrade_20191104.log
-rwx------    1 oracle   dba             233 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/autoupgrade_20191104_user.log
-rwx------    1 oracle   dba               0 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/autoupgrade_err.log
-rwx------    1 oracle   dba           71390 Nov 04 13:07 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/autoupgrade_20191104.log
-rwx------    1 oracle   dba             233 Nov 04 13:06 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/autoupgrade_20191104_user.log
-rwx------    1 oracle   dba               0 Nov 04 13:06 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/autoupgrade_err.log
-rwx------    1 oracle   dba          891207 Nov 04 16:01 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/autoupgrade_20191104.log
-rwx------    1 oracle   dba           12371 Nov 04 16:01 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/autoupgrade_20191104_user.log
-rwx------    1 oracle   dba             245 Nov 04 15:50 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/autoupgrade_err.log
-rwx------    1 oracle   dba            1118 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/after_upgrade_pfile_ORACLE_SID.ora
-rwx------    1 oracle   dba               0 Nov 04 14:05 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID.restart
-rwx------    1 oracle   dba            2236 Nov 04 15:48 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_autocompile.sql
-rwx------    1 oracle   dba           16805 Nov 04 13:52 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_catnoamd.sql
-rwx------    1 oracle   dba            3685 Nov 04 13:56 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_catnoexf.sql
-rwx------    1 oracle   dba           19753 Nov 04 13:56 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_catnorul.sql
-rwx------    1 oracle   dba           20740 Nov 04 13:54 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_emremove.sql
-rwx------    1 oracle   dba             883 Nov 04 15:48 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_objcompare.sql
-rwx------    1 oracle   dba            1118 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/before_upgrade_pfile_ORACLE_SID.ora
-rwx------    1 oracle   dba            1168 Nov 04 14:00 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/during_upgrade_pfile_ORACLE_SID.ora

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/prechecks:
total 376
-rwx------    1 oracle   dba            2781 Nov 04 12:46 ORACLE_SID_checklist.cfg
-rwx------    1 oracle   dba            9328 Nov 04 12:46 ORACLE_SID_checklist.json
-rwx------    1 oracle   dba            9962 Nov 04 12:46 ORACLE_SID_checklist.xml
-rwx------    1 oracle   dba           25980 Nov 04 12:46 ORACLE_SID_preupgrade.html
-rwx------    1 oracle   dba           10018 Nov 04 12:46 ORACLE_SID_preupgrade.log
-rwx------    1 oracle   dba          121687 Nov 04 12:46 prechecks_ORACLE_SID.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/prechecks:
total 376
-rwx------    1 oracle   dba            2781 Nov 04 13:07 ORACLE_SID_checklist.cfg
-rwx------    1 oracle   dba            9328 Nov 04 13:07 ORACLE_SID_checklist.json
-rwx------    1 oracle   dba            9962 Nov 04 13:07 ORACLE_SID_checklist.xml
-rwx------    1 oracle   dba           25980 Nov 04 13:07 ORACLE_SID_preupgrade.html
-rwx------    1 oracle   dba           10018 Nov 04 13:07 ORACLE_SID_preupgrade.log
-rwx------    1 oracle   dba          121687 Nov 04 13:07 prechecks_ORACLE_SID.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/dbupgrade:
total 446064
-rwx------    1 oracle   dba           13059 Nov 04 15:48 autoupgrade20191104135121ORACLE_SID.log
-rwx------    1 oracle   dba           12042 Nov 04 15:48 ORACLE_SID_autocompile20191104135121ORACLE_SID0.log
-rwx------    1 oracle   dba             551 Nov 04 15:48 ORACLE_SID_autocompile20191104135121ORACLE_SID_catcon_10617264.lst
-rwx------    1 oracle   dba       208215073 Nov 04 15:48 catupgrd20191104135121ORACLE_SID0.log
-rwx------    1 oracle   dba         7481470 Nov 04 15:46 catupgrd20191104135121ORACLE_SID1.log
-rwx------    1 oracle   dba         5527017 Nov 04 15:46 catupgrd20191104135121ORACLE_SID2.log
-rwx------    1 oracle   dba         7040784 Nov 04 15:46 catupgrd20191104135121ORACLE_SID3.log
-rwx------    1 oracle   dba             527 Nov 04 14:01 catupgrd20191104135121ORACLE_SID_catcon_17039806.lst
-rwx------    1 oracle   dba               0 Nov 04 15:23 catupgrd20191104135121ORACLE_SID_datapatch_normal.err
-rwx------    1 oracle   dba            1050 Nov 04 15:46 catupgrd20191104135121ORACLE_SID_datapatch_normal.log
-rwx------    1 oracle   dba               0 Nov 04 15:17 catupgrd20191104135121ORACLE_SID_datapatch_upgrade.err
-rwx------    1 oracle   dba             702 Nov 04 15:18 catupgrd20191104135121ORACLE_SID_datapatch_upgrade.log
-rwx------    1 oracle   dba            9877 Nov 04 15:18 during_upgrade_pfile_catctl.ora
-rwx------    1 oracle   dba           32649 Nov 04 14:01 phase.log
-rwx------    1 oracle   dba            1489 Nov 04 15:48 upg_summary.log
-rwx------    1 oracle   dba              46 Nov 04 15:48 upg_summary_report.log
-rwx------    1 oracle   dba             408 Nov 04 15:48 upg_summary_report.pl

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/drain:
total 16
-rwx------    1 oracle   dba            4952 Nov 04 14:01 drain_ORACLE_SID.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/postchecks:
total 104
-rwx------    1 oracle   dba             969 Nov 04 15:50 ORACLE_SID_checklist.cfg
-rwx------    1 oracle   dba            3202 Nov 04 15:50 ORACLE_SID_checklist.json
-rwx------    1 oracle   dba            3395 Nov 04 15:50 ORACLE_SID_checklist.xml
-rwx------    1 oracle   dba           16916 Nov 04 15:50 ORACLE_SID_postupgrade.html
-rwx------    1 oracle   dba            3383 Nov 04 15:50 ORACLE_SID_postupgrade.log
-rwx------    1 oracle   dba           14861 Nov 04 15:50 postchecks_ORACLE_SID.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/postfixups:
total 40
-rwx------    1 oracle   dba           14864 Nov 04 16:00 postchecks_ORACLE_SID.log
-rwx------    1 oracle   dba            3262 Nov 04 15:59 postfixups_ORACLE_SID.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/postupgrade:
total 24
-rwx------    1 oracle   dba           10177 Nov 04 16:01 postupgrade.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/prechecks:
total 376
-rwx------    1 oracle   dba            2781 Nov 04 13:52 ORACLE_SID_checklist.cfg
-rwx------    1 oracle   dba            9328 Nov 04 13:52 ORACLE_SID_checklist.json
-rwx------    1 oracle   dba            9962 Nov 04 13:52 ORACLE_SID_checklist.xml
-rwx------    1 oracle   dba           25980 Nov 04 13:52 ORACLE_SID_preupgrade.html
-rwx------    1 oracle   dba           10018 Nov 04 13:52 ORACLE_SID_preupgrade.log
-rwx------    1 oracle   dba          121687 Nov 04 13:51 prechecks_ORACLE_SID.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/prefixups:
total 304
-rwx------    1 oracle   dba          121708 Nov 04 14:00 prechecks_ORACLE_SID.log
-rwx------    1 oracle   dba           32727 Nov 04 14:00 prefixups_ORACLE_SID.log

ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/preupgrade:
total 8
-rwx------    1 oracle   dba              98 Nov 04 13:51 preupgrade.log

/orahome/oracle/app/admin/ORACLE_SID/autoupgrade/log/cfgtoollogs/upgrade/auto:
total 880
-rwx------    1 oracle   dba          414589 Nov 04 16:01 autoupgrade.log
-rwx------    1 oracle   dba             780 Nov 04 13:00 autoupgrade_err.log
-rwx------    1 oracle   dba            3113 Nov 04 16:01 autoupgrade_user.log
drwx------    2 oracle   dba            4096 Nov 04 14:01 config_files
drwx------    2 oracle   dba             256 Nov 04 16:01 lock
-rwx------    1 oracle   dba           12381 Nov 04 16:01 state.html
drwx------    2 oracle   dba            4096 Nov 04 16:01 status
$

Find timing for autoupgrade process.

$ tail -12 `ls $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/$ORACLE_UNQNAME/$JOBNO/autoupgrade*.log|egrep -v 'user|err'`
2019-11-04 16:01:21.082 INFO ----------------------Stages  Summary------------------------ - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.082 INFO     SETUP             1 min                                  - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.083 INFO     PREUPGRADE        1 min                                  - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.083 INFO     PRECHECKS         1 min                                  - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.083 INFO     GRP               1 min                                  - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.084 INFO     PREFIXUPS         8 min                                   - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.084 INFO     DRAIN             1 min                                  - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.084 INFO     DBUPGRADE         108 min                                 - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.085 INFO     POSTCHECKS        1 min                                  - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.085 INFO     POSTFIXUPS        9 min                                   - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.086 INFO     POSTUPGRADE       1 min                                  - DispatcherOSHelper.writeStageSummary
2019-11-04 16:01:21.086 INFO End of dispatcher instance for ORACLE_SID - AutoUpgDispatcher.run

Find timing for database upgrade.

$ tail -35 $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/$ORACLE_UNQNAME/102/dbupgrade/catupgrd*${ORACLE_UNQNAME}0.log
========== PROCESS ENDED ==========
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Start of Summary Report
------------------------------------------------------

Oracle Database 12.2 Post-Upgrade Status Tool           11-04-2019 15:46:14

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.2.0.1.0  00:19:53
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:07:34
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:02:16
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:23
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:23
Oracle XDK                             UPGRADED      12.2.0.1.0  00:01:15
Oracle Text                            UPGRADED      12.2.0.1.0  00:01:24
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:05:28
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:25
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:03:25
Spatial                                UPGRADED      12.2.0.1.0  00:09:03
Oracle Application Express             UPGRADED     5.0.4.00.12  00:23:17
Final Actions                                                    00:04:44
Post Upgrade                                                     00:00:09

Total Upgrade Time: 01:20:32

Database time zone version is 14. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:1h:47m:34s]

End of Summary Report
------------------------------------------------------
$

Find timing for datapatch.

$ cat $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/$ORACLE_UNQNAME/$JOBNO/dbupgrade/catupgrd*datapatch_normal.log
SQL Patching tool version 12.2.0.1.0 Production on Mon Nov 4 15:23:22 2019
Copyright (c) 2012, 2019, Oracle. All rights reserved.

Log file for this invocation: /orahome/oracle/app/cfgtoollogs/sqlpatch/sqlpatch_14221746_2019_11_04_15_23_22/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
ID 190716 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
29757449 (DATABASE JUL 2019 RELEASE UPDATE 12.2.0.1.190716)

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...
Patch 29757449 apply: SUCCESS
logfile: /orahome/oracle/app/cfgtoollogs/sqlpatch/29757449/23013437/29757449_apply_ORACLE_SID_2019Nov04_15_24_11.log (no errors)
SQL Patching tool complete on Mon Nov 4 15:46:00 2019
$

Might be better to use grep -A vs tail; however, I was on AIX and was not able to find option.

August 6, 2019

19c Grid Dry-Run Upgrade

Filed under: 19c,awk_sed_grep,Grid Infrastructure,upgrade — mdinh @ 12:42 pm

First test using GUI.

[oracle@racnode-dc2-1 grid]$ /u01/app/19.3.0.0/grid/gridSetup.sh -dryRunForUpgrade
Launching Oracle Grid Infrastructure Setup Wizard...

The response file for this session can be found at:
 /u01/app/19.3.0.0/grid/install/response/grid_2019-08-06_00-20-31AM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2019-08-06_00-20-31AM/gridSetupActions2019-08-06_00-20-31AM.log
[oracle@racnode-dc2-1 grid]$

Create dryRunForUpgradegrid.rsp from grid_2019-08-06_00-20-31AM.rsp (above GUI test)

[oracle@racnode-dc2-1 grid]$ grep -v "^#" /u01/app/19.3.0.0/grid/install/response/grid_2019-08-06_00-20-31AM.rsp | grep -v "=$" | awk 'NF' > /home/oracle/dryRunForUpgradegrid.rsp

[oracle@racnode-dc2-1 ~]$ cat /home/oracle/dryRunForUpgradegrid.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0
INVENTORY_LOCATION=/u01/app/oraInventory
oracle.install.option=UPGRADE
ORACLE_BASE=/u01/app/oracle
oracle.install.crs.config.scanType=LOCAL_SCAN
oracle.install.crs.config.ClusterConfiguration=STANDALONE
oracle.install.crs.config.configureAsExtendedCluster=false
oracle.install.crs.config.clusterName=vbox-rac-dc2
oracle.install.crs.config.gpnp.configureGNS=false
oracle.install.crs.config.autoConfigureClusterNodeVIP=false
oracle.install.crs.config.gpnp.gnsOption=CREATE_NEW_GNS
oracle.install.crs.config.clusterNodes=racnode-dc2-1:,racnode-dc2-2:
oracle.install.crs.configureGIMR=true
oracle.install.asm.configureGIMRDataDG=false
oracle.install.crs.config.storageOption=FLEX_ASM_STORAGE
oracle.install.crs.config.sharedFileSystemStorage.ocrLocations=
oracle.install.crs.config.useIPMI=false
oracle.install.asm.diskGroup.name=CRS
oracle.install.asm.diskGroup.AUSize=0
oracle.install.asm.gimrDG.AUSize=1
oracle.install.asm.configureAFD=false
oracle.install.crs.configureRHPS=false
oracle.install.crs.config.ignoreDownNodes=false
oracle.install.config.managementOption=NONE
oracle.install.config.omsPort=0
oracle.install.crs.rootconfig.executeRootScript=false
[oracle@racnode-dc2-1 ~]$

Create directory grid home for all nodes:

[root@racnode-dc2-1 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54318(asmdba),54322(dba),54323(backupdba),54324(oper),54325(dgdba),54326(kmdba)

[root@racnode-dc2-1 ~]# mkdir -p /u01/app/19.3.0.0/grid
[root@racnode-dc2-1 ~]# chown oracle:oinstall /u01/app/19.3.0.0/grid
[root@racnode-dc2-1 ~]# chmod 775 /u01/app/19.3.0.0/grid

[root@racnode-dc2-1 ~]# ll /u01/app/19.3.0.0/
total 4
drwxrwxr-x 2 oracle oinstall 4096 Aug  6 02:07 grid
[root@racnode-dc2-1 ~]#

Extract grid software for node1 ONLY:

[oracle@racnode-dc2-1 ~]$ unzip -qo /media/swrepo/LINUX.X64_193000_grid_home.zip -d /u01/app/19.3.0.0/grid/

[oracle@racnode-dc2-1 ~]$ ls /u01/app/19.3.0.0/grid/
addnode     clone  dbjava     diagnostics  gpnp          install        jdbc  lib      OPatch   ords  perl     qos       rhp            rootupgrade.sh  sqlpatch  tomcat  welcome.html  xdk
assistants  crs    dbs        dmu          gridSetup.sh  instantclient  jdk   md       opmn     oss   plsql    racg      root.sh        runcluvfy.sh    sqlplus   ucp     wlm
bin         css    deinstall  env.ora      has           inventory      jlib  network  oracore  oui   precomp  rdbms     root.sh.old    sdk             srvm      usm     wwg
cha         cv     demo       evm          hs            javavm         ldap  nls      ord      owm   QOpatch  relnotes  root.sh.old.1  slax            suptools  utl     xag

[oracle@racnode-dc2-1 ~]$ du -sh /u01/app/19.3.0.0/grid/
6.0G    /u01/app/19.3.0.0/grid/
[oracle@racnode-dc2-1 ~]$

Run gridSetup.sh -silent -dryRunForUpgrade:

[oracle@racnode-dc2-1 ~]$ env|grep -i ora
USER=oracle
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin
PWD=/home/oracle
HOME=/home/oracle
LOGNAME=oracle

[oracle@racnode-dc2-1 ~]$ date
Tue Aug  6 02:35:47 CEST 2019

[oracle@racnode-dc2-1 ~]$ /u01/app/19.3.0.0/grid/gridSetup.sh -silent -dryRunForUpgrade -responseFile /home/oracle/dryRunForUpgradegrid.rsp
Launching Oracle Grid Infrastructure Setup Wizard...

[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/gridSetupActions2019-08-06_02-35-52AM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/gridSetupActions2019-08-06_02-35-52AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
 /u01/app/19.3.0.0/grid/install/response/grid_2019-08-06_02-35-52AM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/gridSetupActions2019-08-06_02-35-52AM.log


As a root user, execute the following script(s):
        1. /u01/app/19.3.0.0/grid/rootupgrade.sh

Execute /u01/app/19.3.0.0/grid/rootupgrade.sh on the following nodes:
[racnode-dc2-1]

Run the script on the local node.

Successfully Setup Software with warning(s).
[oracle@racnode-dc2-1 ~]$

Run rootupgrade.sh for node1 ONLY and review log:

[root@racnode-dc2-1 ~]# /u01/app/19.3.0.0/grid/rootupgrade.sh
Check /u01/app/19.3.0.0/grid/install/root_racnode-dc2-1_2019-08-06_02-44-59-241151038.log for the output of root script

[root@racnode-dc2-1 ~]# cat /u01/app/19.3.0.0/grid/install/root_racnode-dc2-1_2019-08-06_02-44-59-241151038.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/19.3.0.0/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Performing Dry run of the Grid Infrastructure upgrade.
Using configuration parameter file: /u01/app/19.3.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/oracle/crsdata/racnode-dc2-1/crsconfig/rootcrs_racnode-dc2-1_2019-08-06_02-45-31AM.log
2019/08/06 02:45:44 CLSRSC-464: Starting retrieval of the cluster configuration data
2019/08/06 02:45:52 CLSRSC-729: Checking whether CRS entities are ready for upgrade, cluster upgrade will not be attempted now. This operation may take a few minutes.
2019/08/06 02:47:56 CLSRSC-693: CRS entities validation completed successfully.
[root@racnode-dc2-1 ~]#

Check grid home for node2:

[oracle@racnode-dc2-2 ~]$ du -sh /u01/app/19.3.0.0/grid/
6.6G    /u01/app/19.3.0.0/grid/
[oracle@racnode-dc2-2 ~]$

Check oraInventory for ALL nodes:

[oracle@racnode-dc2-2 ~]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2019, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.2.0.7.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/u01/app/12.2.0.1/grid" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/12.2.0.1/db1" TYPE="O" IDX="2"/>
==========================================================================================
<HOME NAME="OraGI19Home1" LOC="/u01/app/19.3.0.0/grid" TYPE="O" IDX="3"/>
==========================================================================================
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
[oracle@racnode-dc2-2 ~]$

Check crs activeversion: 12.2.0.1.0

[oracle@racnode-dc2-1 ~]$ . /media/patch/gi.env
The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.2.0.1/grid
ORACLE_HOME=/u01/app/12.2.0.1/grid
Oracle Instance alive for sid "+ASM1"

[oracle@racnode-dc2-1 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [12.2.0.1.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [927320293].
[oracle@racnode-dc2-1 ~]$

Check log location:

[oracle@racnode-dc2-1 ~]$ cd /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/

[oracle@racnode-dc2-1 GridSetupActions2019-08-06_02-35-52AM]$ ls -alrt
total 17420
-rw-r-----  1 oracle oinstall     129 Aug  6 02:35 installerPatchActions_2019-08-06_02-35-52AM.log
-rw-r-----  1 oracle oinstall       0 Aug  6 02:35 gridSetupActions2019-08-06_02-35-52AM.err
drwxrwx---  3 oracle oinstall    4096 Aug  6 02:35 temp_ob
-rw-r-----  1 oracle oinstall       0 Aug  6 02:39 oraInstall2019-08-06_02-35-52AM.err
drwxrwx--- 17 oracle oinstall    4096 Aug  6 02:39 ..
-rw-r-----  1 oracle oinstall     157 Aug  6 02:39 oraInstall2019-08-06_02-35-52AM.out
-rw-r-----  1 oracle oinstall       0 Aug  6 02:43 oraInstall2019-08-06_02-35-52AM.err.racnode-dc2-2
-rw-r-----  1 oracle oinstall     142 Aug  6 02:43 oraInstall2019-08-06_02-35-52AM.out.racnode-dc2-2
-rw-r-----  1 oracle oinstall 9341920 Aug  6 02:43 gridSetupActions2019-08-06_02-35-52AM.out
-rw-r-----  1 oracle oinstall   13419 Aug  6 02:43 time2019-08-06_02-35-52AM.log
-rw-r-----  1 oracle oinstall 8443087 Aug  6 02:43 gridSetupActions2019-08-06_02-35-52AM.log
drwxrwx---  3 oracle oinstall    4096 Aug  6 02:56 .
[oracle@racnode-dc2-1 GridSetupActions2019-08-06_02-35-52AM]$

After dryRunForUpgrade, detach 19.3.0.0 grid home and remove directory (19.3.0.0/grid) from all nodes.

export ORACLE_HOME=/u01/app/19.3.0.0/grid
$ORACLE_HOME/oui/bin/runInstaller -detachHome -silent ORACLE_HOME=$ORACLE_HOME

March 3, 2018

Upgrade 12.2 Journey – DataGuard

Filed under: 12.2,Dataguard,upgrade,Upgrade 12.2 Journey — mdinh @ 6:07 pm

Oracle Data Guard Broker Changes in Oracle Database 12c Release 2 (12.2.0.1)

How to resolve MRP stuck issues on a physical standby database? (Doc ID 1221163.1)
Starting from 12.2 use V$DATAGUARD_PROCESS view instead of v$managed_standby

March 18, 2016

Migrate to 12c using Export/Import

Filed under: 12c,oracle,upgrade — mdinh @ 2:07 am

If you had success doing this, please share.

This took some troubleshooting and more than one pass.

Still not comfortable to guarantee target is identical to source.

Here are all the references, I have gathered if you want an adventure.

How to Perform a Full Database Export Import During Upgrade, Migrate, Copy or Move of a Database (Doc ID 286775.1)

Information On Installed Database Components and Schemas (Doc ID 472937.1)
Schemas CTXSYS MDSYS ORDSYS Are Not Exported (Doc ID 228482.1)

How to Install/Deinstall Oracle Workspace Manager (Doc ID 263428.1)
How to Determine if Workspace Manager is Being Used? (Doc ID 727765.1)

Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)
After Full Import All Object Privileges Granted By SYS Are Lost (Doc ID 97902.1)

DataPump Export Fails With Error ORA-39095 (Doc ID 1071931.1)

++++++++++
How many components are installed?

*** dba_registry ***

ACTUAL SOURCE:
COMP_ID    COMP_NAME			       VERSION	       STATUS	    NAMESPACE	 SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
CATALOG    Oracle Database Catalog Views       10.2.0.4.0      VALID	    SERVER	 SYS
CATPROC    Oracle Database Packages and Types  10.2.0.4.0      VALID	    SERVER	 SYS
EM	   Oracle Enterprise Manager	       10.2.0.4.0      VALID	    SERVER	 SYSMAN
OWM	   Oracle Workspace Manager	       10.2.0.4.3      VALID	    SERVER	 WMSYS

ONE BAD SOURCE:
COMP_ID    COMP_NAME                           VERSION         STATUS       NAMESPACE    SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
APS        OLAP Analytic Workspace             11.2.0.4.0      VALID        SERVER       SYS
CATALOG    Oracle Database Catalog Views       11.2.0.4.0      VALID        SERVER       SYS
CATJAVA    Oracle Database Java Packages       11.2.0.4.0      VALID        SERVER       SYS
CATPROC    Oracle Database Packages and Types  11.2.0.4.0      VALID        SERVER       SYS
CONTEXT    Oracle Text                         11.2.0.4.0      VALID        SERVER       CTXSYS
EXF        Oracle Expression Filter            11.2.0.4.0      VALID        SERVER       EXFSYS
JAVAVM     JServer JAVA Virtual Machine        11.2.0.4.0      VALID        SERVER       SYS
ORDIM      Oracle Multimedia                   11.2.0.4.0      VALID        SERVER       ORDSYS
OWB        OWB                                 11.2.0.4.0      VALID        SERVER       OWBSYS
OWM        Oracle Workspace Manager            11.2.0.4.0      VALID        SERVER       WMSYS
RUL        Oracle Rules Manager                11.2.0.4.0      VALID        SERVER       EXFSYS
SDO        Spatial                             11.2.0.4.0      VALID        SERVER       MDSYS
XDB        Oracle XML Database                 11.2.0.4.0      VALID        SERVER       XDB
XML        Oracle XDK                          11.2.0.4.0      VALID        SERVER       SYS
XOQ        Oracle OLAP API                     11.2.0.4.0      VALID        SERVER       SYS

15 rows selected.

++++++++++
Export error using FLASHBACK_TIME

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Took a some time but finally found a work around other than using FLASHBACK_SCN which is hokie.

expdp_full.par

USERID="/ as sysdba"
directory=DATA_PUMP_DIR
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')"
EXCLUDE=statistics
exclude=schema:"IN ('WMSYS','SYSMAN')"
LOGFILE=exp_full.log

++++++++++
Create 12c non-PDB database.

whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
set echo on
shu abort;
create spfile from pfile;
startup force nomount;
CREATE DATABASE
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 
GROUP 1 size 100M,GROUP 2 size 100M,GROUP 3 size 100M
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
DEFAULT TABLESPACE USERS DATAFILE SIZE 257M AUTOEXTEND ON NEXT 257M MAXSIZE 30721M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
;
set echo off
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/catio.sql
@?/rdbms/admin/dbmsiotc.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/userlock.sql
@?/rdbms/admin/utlrp.sql
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
connect system/oracle
@?/sqlplus/admin/pupbld.sql
exit

++++++++++
Check for errors.
grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f1|sort -u

ORA-00942
ORA-01432
ORA-01434
ORA-01921
ORA-02289
ORA-04043
ORA-29807

oerr ora 1432

01432, 00000, "public synonym to be dropped does not exist"
// *Cause:
// *Action:

grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f2|grep -v "not exist"|sort -u

 role name 'EXP_FULL_DATABASE' conflicts with another user or role
 role name 'IMP_FULL_DATABASE' conflicts with another user or role

++++++++++
Don’t use FILESIZE – I know, there are presentation showing usage of FILESIZE.

Example:
FILESIZE=8g
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

++++++++++
DATAFILE SIZE 2097152000 is larger than MAXSIZE 1048576000.

ORA-39083: Object type TABLESPACE:"USERS" failed to create with error:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause
Failing sql is:
CREATE TABLESPACE "USERS" DATAFILE SIZE 2097152000 AUTOEXTEND ON NEXT 104857600 MAXSIZE 1048576000

Related error:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
SYS triggers need to be extracted from Source and created at Target.
Remember this one – OJVM?

SQL> CREATE OR REPLACE TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" before create
2 on database
3 begin
4 if (ora_dict_obj_type='JAVA')
5 then
6 raise_application_error(-20031,'Java Development Disabled');
7 end if;
8 end;
9 /
Trigger created.

SQL> ALTER TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" DISABLE;

Trigger altered.
SYS@SQL>

++++++++++
Tablespaces were pre-created since datafile locations changed.  Alternatively, use REMAP_DATAFILE.

set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);
define _filename=none
COL name NEW_VALUE name
select name from v$database;
define _filename='tbs_&name..sql'
spool &_filename
select dbms_metadata.get_ddl('TABLESPACE',TABLESPACE_NAME) from dba_tablespace
;
spool off
set term on
pro
pro *** Spool Output: &_filename
pro
pro
exit

++++++++++
Import done in 2 passes, impdp_full_user.par and impdp_full.par

impdp_full_user.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_user.log
INCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace

Want to detect as many issues as possible before actual data import.
ORA-39083: Object type TABLESPACE:"USERS" failed to create with error

ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
grep -v ORA-31684 imp_user.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 26 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 7796 USER objects in 20 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 321 ROLE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Completed 21442 ROLE_GRANT objects in 129 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Completed 7796 DEFAULT_ROLE objects in 10 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
     Completed 7790 TABLESPACE_QUOTA objects in 24 seconds

++++++++++
impdp_full.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_full.log
EXCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace
STREAMS_CONFIGURATION=NO

++++++++++
grep -v ORA-31684 imp_full.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 6 PROC_SYSTEM_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 145 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
     Completed 197 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 205 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 7 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 12 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 7 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 1139 SYNONYM objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Completed 1014 SYNONYM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Completed 180 TYPE objects in 3 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Completed 1302 TABLE objects in 18 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Startup took 356 seconds
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 68094 OBJECT_GRANT objects in 91 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed 238 CONSTRAINT objects in 86 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed 696 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
     Completed 1 AUDIT_OBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/RLS_POLICY
     Completed 68 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Completed 148 PACKAGE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 195 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Completed 42 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 52 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Completed 16 PROCEDURE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Completed 42 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 16 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Completed 72 VIEW objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 155 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Completed 62 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/RLS_POLICY
     Completed 4 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed 148 PACKAGE_BODY objects in 4 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
     Completed 16 TYPE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Completed 68 TRIGGER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
     Completed 3 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
     Completed 1 MATERIALIZED_VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/JOB
     Completed 5 JOB objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/AUDIT
     Completed 5 AUDIT objects in 0 seconds

August 27, 2013

12c Grid Infrastructure Standalone Silent Upgrade

Filed under: 12c,upgrade — mdinh @ 1:17 pm

This is what insomnia will do to you.

After Apply PSU 11.2.0.3.7 for Grid Infrastructure Standalone and DB with Oracle Restart, I was curious to see if I can upgrade just the Grid Infrastructure to 12c.

I came across something new that I have not seen before. Uh oh! Time to go stealing.

Successfully Setup Software.
As install user, execute the following script to complete the configuration.
        1. /u01/app/12.1.0/grid/cfgtoollogs/configToolAllCommands RESPONSE_FILE=<response_file>

        Note:
        1. This script must be run on the same host from where installer was run.
        2. This script needs a small password properties file for configuration assistants that require passwords (refer to install guide documentation).

There are not many posts with silent operation. Luckily for me, look at what I found: How to install Grid Infrastructure 12.1 in silent mode

Can you believe it? Upgrade completed successfully.

12c_grid_silent_upgrade log
grid12c_upgrade.rsp

Should I shorten the response file to make it cleaner or keep it that way for clarity?

July 3, 2013

Upgrade to 12c using DBUA Silent (shhh) Mode

Filed under: 12c,upgrade — mdinh @ 1:52 am

Single instance database upgrade from 11.2.0.3 to 12.1.0.1.0.

There’s plenty of install posts already out there; hence, I am not going to blog about it.

If you find a post on DBUA Silent mode, then please let me know.

Oracle Database 12c Release 1 (12.1) DBUA in Silent mode [ID 1516616.1]

First attempt failed with errors:

ORA-00823: Specified value of sga_target greater than sga_max_size
ORA-01078: failure in processing system parameters

ORA-00823 – Specified value of sga_target greater than sga_max_size Error During Upgrade Using DBUA [ID 1439671.1]

As an alternative solution SGA_MAX_SIZE can be removed as it is not mandatory while upgrade

Is Oracle lying to me? Let’s look at spfile before and after.

$ tail spfiledb01.ora.orig

*.processes=50
*.remote_logCC"-in_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_max_size=512M
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'

$ tail spfiledb01.ora

*.pga_aggregate_target=128M
*.processes=50
*.remoCC"n&te_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'

[oracle@lax:db01]/home/oracle
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbua -silent \
> -sid db01 \
> -oracleHome /u01/app/oracle/product/11.2.0.3/db_1 \
> -diagnosticDest /u01/app/oracle \
> -autoextendFiles \
> -recompile_invalid_objects true \
> -degree_of_parallelism 2 \
> -upgradeTimezone \
> -emConfiguration NONE \
> -keepHiddenParams \
> -gatheringStatistics \
> -createGRP true \
> -upgrade_parallelism 2

Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
Performing Database Backup
16% complete
Performing Pre Upgrade
20% complete
33% complete
Performing RDBMS Upgrade
34% complete
49% complete
50% complete
Performing Post Upgrade
51% complete
53% complete
66% complete
Upgrading Timezone
83% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file "/u01/app/oracle/cfgtoollogs/dbua/logs/silent.log_1372773594442" for upgrade details.

DBUA performed the following actions:

Pre-Upgrade Checks

Database contains INVALID objects prior to upgrade. 
The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. 
The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. 
Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
It is required that the recycle bin is empty prior to upgrading your database. 
Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.

Initialization Parameter changes

The following changes will be made in the initialization parameters:
sga_target 536870912|624951296 (old|new)
processes  50|300

Obsolete Parameters to be removed

sec_case_sensitive_logon

Timezone Upgrade

The time zone version and TIMESTAMP WITH TIME ZONE data of the Database will be upgraded from version 14 to version 18.

Recovery options

Create a new guaranteed restore point before upgrade and flashback the database in case of any upgrade failure.

Upgrade Details

The following summary lists the steps performed during the upgrade process.
Log files for all the steps, as well as this summary, are available at “/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1”.

Database Backup     -Backup.log
Pre Upgrade         -PreUpgrade.log (PreUpgradeResults.html is a lot better)
RDBMS Upgrade       -Oracle_Server.log
Post Upgrade        -PostUpgrade.log
Timezone Upgrade    -UpgradeTimezone.log
Generate Summary    -generateSummary.log (does not exist)

Let’s review the logs.

$ORACLE_BASE=/u01/app/oracle

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ ls -l

total 64
-rw-r----- 1 oracle oinstall  3592 Jul  2 10:48 silent.log_1372773594442
-rw-r----- 1 oracle oinstall 17721 Jul  2 07:02 sqls.log
-rw-r----- 1 oracle oinstall 38554 Jul  2 07:02 trace.log

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ head -20 silent.log_1372773594442

Upgrading the database dbName="db01"
Database contains INVALID objects prior to upgrade. The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
Your recycle bin contains objects. It is required that the recycle bin is empty prior to upgrading your database. Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.
Supported upgrade version check succeeded.
The oratab permission check succeeded.
Oracle Home Owner Check succeeded.
Database does not use ASM storage.
Space usage summary

Space usage reason:  Upgrade

Tablespace Name:  SYSTEM
Additional space required:  159 MB
Datafile:  /oracle/oradata/db02/system_1.dbf
Auto Extensible:  Yes

Tablespace Name:  SYSAUX
Additional space required:  244 MB
Datafile:  /oracle/oradata/db02/sysaux_1.dbf
Auto Extensible:  Yes

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ tail -20 silent.log_1372773594442

UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 50%
Performing Post Upgrade
UPGRADE_PROGRESS : 51%
UPGRADE_PROGRESS : 53%
UPGRADE_PROGRESS : 66%
Upgrading Timezone
UPGRADE_PROGRESS : 83%
Generating Summary
UPGRADE_PROGRESS : 100%
Database upgrade has been completed successfully, and the database is ready to use.

The following document describes important behavioral changes from previous database releases:
/u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbua/doc/DefaultBehaviorChanges_<NLS_LANG>.html

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ ls -alrt

total 24208
-rw-r----- 1 oracle oinstall        0 Jul  2 07:02 trace.log.lck
drwxr-x--- 3 oracle oinstall     4096 Jul  2 07:02 ..
-rw-r----- 1 oracle oinstall     2595 Jul  2 07:04 upgrade.xml1
-rw-r----- 1 oracle oinstall     2256 Jul  2 07:07 upgrade.xml
-rw-r----- 1 oracle oinstall     4504 Jul  2 07:07 PreUpgradeResults.html
-rw-r----- 1 oracle oinstall        0 Jul  2 07:07 Backup.log
-rw-r----- 1 oracle oinstall     1168 Jul  2 07:07 init.ora
-rwxr-xr-x 1 oracle oinstall      173 Jul  2 07:07 createSPFile_db01.sql
-rwxr-xr-x 1 oracle oinstall      127 Jul  2 07:07 grpOpen_db01.sql
-rwxr-xr-x 1 oracle oinstall     1257 Jul  2 07:07 db01_restore.sh
-rwxr-xr-x 1 oracle oinstall       42 Jul  2 07:07 shutdown_db01.sql
-rwxr-xr-x 1 oracle oinstall       35 Jul  2 07:07 startup_db01.sql
-rw-r----- 1 oracle oinstall      486 Jul  2 07:10 PreUpgrade.log
-rw-r----- 1 oracle oinstall  8407445 Jul  2 10:16 catupgrd1.log
-rw-r----- 1 oracle oinstall 15815542 Jul  2 10:16 catupgrd0.log
-rw-r----- 1 oracle oinstall   152750 Jul  2 10:20 Oracle_Server.log
-rw-r----- 1 oracle oinstall     9703 Jul  2 10:34 PostUpgrade.log
-rw-r----- 1 oracle oinstall      364 Jul  2 10:48 UpgradeTimezone.log
-rw-r----- 1 oracle oinstall     6323 Jul  2 10:48 UpgradeResults.html
-rw-r----- 1 oracle oinstall   212540 Jul  2 10:48 trace.log
-rw-r----- 1 oracle oinstall   101483 Jul  2 10:48 sqls.log
drwxr-x--- 2 oracle oinstall     4096 Jul  2 14:38 .

-createGRP true (NF – create a guaranteed restore point when database is in archive log and flashback mode)

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat db01_restore.sh

#!/bin/sh

# -- Run this Script to Restore Oracle Database Instance db01
echo -- Bringing down the database from the new oracle home
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql
echo You should Remove this entry from the /etc/oratab: db01:/u01/app/oracle/product/12.1.0/dbhome_1:Y
echo -- Bringing up the database from the source oracle home
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
unset LD_LIBRARY_PATH; unset LD_LIBRARY_PATH_64; unset SHLIB_PATH; unset LIB_PATH
echo You should Add this entry in the /etc/oratab: db01:/u01/app/oracle/product/11.2.0.3/db_1:Y
cd /u01/app/oracle/product/11.2.0.3/db_1
echo -- Removing /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt file
rm -f /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt ;
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql

connect / as sysdba
shutdown abort;
exit;
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
connect / as sysdba
CREATE SPFILE='/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfiledb01.ora' from pfile='/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/init.ora';
exit;

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql

connect / as sysdba
startup mount;
flashback database to restore point grp_1372774067744;
alter database open resetlogs;
exit;

Improved sysresv
$ sysresv

IPC Resources for ORACLE_SID "db01" :
Maximum shared memory segment size (shmmax): 4398046511104 bytes
Total system shared memory (shmall): 4398046511104 bytes
Total system shared memory count (shmmni): 4096
Total /dev/shm size: 1581465600 bytes, used: 278528 bytes
Shared Memory:
ID              KEY
24084484        0x450f83c4
24117253        0x00000000
Semaphores:
ID              KEY
1867778         0x0c97f794
1900547         0x0c97f795
1933316         0x0c97f796
Oracle Instance alive for sid "db01"

Don’t forget to change compatible
$ sqlplus “/ as sysdba”

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 14:54:29 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

LAX:(SYS@db01)> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.3
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE
LAX:(SYS@db01)> exit

November 7, 2010

Upgrade Pain01

Filed under: oracle,upgrade — mdinh @ 2:07 pm

Completed upgrade of OLTP database from 10.2.0.4 to 11.2.0.2

To my surprise, I found 199 dead locks and increasing fast in the alert log as shown:

> grep -ic ‘ORA-00060: Deadlock detected’ alert_db06.log
199

The trace file shows:

*** 2010-11-07 03:53:20.238
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TM-0003b69d-00000000 113 38 SX SSX 119 221 SX SSX
TM-0003b69d-00000000 119 221 SX SSX 113 38 SX SSX

session 38: DID 0001-0071-00000004 session 221: DID 0001-0077-00000004
session 221: DID 0001-0077-00000004 session 38: DID 0001-0071-00000004

Rows waited on:
Session 38: no row
Session 221: no row

—– Information for the OTHER waiting sessions —–
Session 221:
sid: 221 ser: 4 audsid: 795496700 user: 211/JOHN
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 119 O/S info: user: oracle, term: UNKNOWN, ospid: 25907
image: oracle@proddb06
client details:
O/S info: user: john, term: unknown, ospid: 1234
machine: terminator program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
DELETE FROM parent WHERE pk_id = :”SYS_B_0″

The following deadlock is not an ORACLE error. It is a deadlock due to the design of an application

Try telling this to the duhvelopers.

Before going further, I would like to thank Google and everyone who shares his/her knowledge and pain.

google: deadlock 11g and found Deadlock on Oracle 11g but Not on 10g

Root cause analysis:

  • Bad Design
  • Anecdotal Upgrade Testing
  • Upgrade
  • Not spending enough time on the internet
  • July 12, 2010

    Using DBUA in Silent Mode

    Filed under: 11g,oracle,upgrade — mdinh @ 5:38 pm

    I just completed upgrade to 11gR2 for a test and small reporting database.

    This time I used DBUA (recommended) versus the manual process.

    /u01/app/oracle/product/11gR2/dbhome_1/bin/dbua -silent \
    -sid dbtest \
    -oracleHome /u01/app/oracle/product/10.2.0/db_1 \
    -diagnosticDest /u01/app/oracle \
    -autoextendFiles \
    -recompile_invalid_objects true \
    -degree_of_parallelism 4 \
    -emConfiguration NONE

    The summary log files are located at $ORACLE_BASE/cfgtoollogs/dbua/logs (ORACLE_BASE=/u01/app/oracle)

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua/logs
    > ll
    total 144
    drwxr-x--- 4 oracle dba 96 Jun 18 10:11 ..
    -rw-r----- 1 oracle dba 19287 Jun 22 10:03 sqls.log
    -rw-r----- 1 oracle dba 28750 Jun 22 10:03 trace.log
    -rw-r----- 1 oracle dba 1833 Jun 22 10:38 silent.log
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 .

    The detailed logs are located at $ORACLE_BASE/cfgtoollogs/dbua/<db_unique_name>/upgrade#

    Example: db_name=dbtest and db_unique_name=dc_dbtest (dc is the name for data center)

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua
    > ll
    total 16
    drwxr-x--- 5 oracle dba 96 Jun 18 10:10 ..
    drwxr-x--- 4 oracle dba 96 Jun 18 10:11 .
    drwxr-x--- 4 oracle dba 96 Jun 22 10:03 dc_dbtest
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 logs

    I attempted 2 upgrades just so I can get the feel of things.

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua/dc_dbtest
    > ll
    total 32
    drwxr-x--- 4 oracle dba 96 Jun 18 10:11 ..
    drwxr-x--- 2 oracle dba 8192 Jun 18 10:13 upgrade1
    drwxr-x--- 4 oracle dba 96 Jun 22 10:03 .
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 upgrade2

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua/dc_dbtest/upgrade2
    > ll
    total 41136
    drwxr-x--- 4 oracle dba 96 Jun 22 10:03 ..
    -rw-r----- 1 oracle dba 1732 Jun 22 10:03 upgrade.xml
    -rw-r----- 1 oracle dba 1167 Jun 22 10:03 Upgrade_Directive.log
    -rw-r----- 1 oracle dba 397 Jun 22 10:03 SpaceUsage.txt
    -rw-r----- 1 oracle dba 5210 Jun 22 10:03 PreUpgradeResults.html
    -rw-r----- 1 oracle dba 442 Jun 22 10:04 PreUpgrade.log
    -rw-r----- 1 oracle dba 20751782 Jun 22 10:36 Oracle_Server.log
    -rw-r----- 1 oracle dba 9317 Jun 22 10:38 PostUpgrade.log
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 .
    -rw-r----- 1 oracle dba 6087 Jun 22 10:38 UpgradeResults.html
    -rw-r----- 1 oracle dba 155467 Jun 22 10:38 trace.log
    -rw-r----- 1 oracle dba 73544 Jun 22 10:38 sqls.log

    More information can be found in the upgrade guide (open new window)

    Create a free website or blog at WordPress.com.