Please click on link to open document:
October 22, 2020
When Upgrading DB Don’t Trust Doc Alone
What’s up Doc!
So there I was, reading documentation and planning upgrade but still not perfect.
DBUA Command-Line Syntax for Active and Silent Mode
changeUserTablespacesReadOnly does not show from help but exists in documenation.
$ which dbua
/app/product/19.3.0.0/bin/dbua
$ dbua -help
Usage: dbua [<flag>] [<option>]
Following are the possible flags:
-createPartialBackup – Flag to create a new offline partial RMAN backup by setting the user tablespaces in R/O mode.
-backupLocation
-disableParallelUpgrade – Flag to disable the parallel execution of database upgrade.
-executePreReqs – Flag to execute the pre-upgrade checks alone for the specified database.
-sid | -dbName
-sid
-dbName
-help – Shows this usage help.
-ignorePreReqs – Ignore error conditions in pre-upgrade checks.
-keepEvents – Flag to keep the configured database events during upgrade.
-silent – This flag allows you to carry on configuration in silent mode.
-sid | -dbName
-sid
-dbName
-skipListenersMigration – Flag to skip the listener migration process as part of the database upgrade.
Following are the possible options:
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-backupLocation – <Specify directory to backup your database before starting the upgrade>]
[-createGRP – <true | false> To create a guaranteed restore point when database is in archive log and flashback mode.]
[-createListener – <true | false> To create a listener in newer release Oracle home specify listenrName:lsnrPort.]
[-dbName – <Specify Database Name>]
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-disableUpgradeScriptLogging – <true | false> This command disables the detailed log generation for running SQL scripts during the upgrade process. By default this is enabled. To enable the log generation, don’t specify this command.]
[-emConfiguration – <DBEXPRESS | CENTRAL | BOTH | NONE>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-emPassword – <Specify EM admin user password>]
[-emUser – <Specify EM admin username to add or modify targets>]
[-emExpressPort – <Specify the port where EM Express will be configured>]
[-omsHost – <Specify EM management server host name>]
[-omsPort – <Specify EM management server port number>]
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-ignoreScriptErrors – <true | false> Specify this flag for ignoring ORA errors during custom scripts.]
[-initParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-excludeInitParams – <Specify a comma separated list of initialization parameters to be excluded.>]
[-keepDeprecatedParams – <true | false> To retain deprecated parameters during database upgrade.]
[-localListenerWithoutAlias – To set LOCAL_LISTENER without TNS Alias.]
[-listeners – <To register the database with existing listeners, specify listeners by comma separated listenerName:Oracle Home. Listeners from lower release home are migrated to newer release home. Specifying -listeners lsnrName1,lsnrName2 or -listeners lsnrName1:<Oracle home path>,-listeners lsnrName2:<Oracle home path>, DBUA searches specified listeners from GI home (if configured), target home and source home>]
[-localRacSid – <Specify the local System Identifier of the cluster database if the cluster database is not registered in OCR>]
[-logDir – <Specify the path to a custom log directory>]
[-newGlobalDbName – <Specify New Global Database Name. This option can only be used for Oracle Express Edition upgrade>]
[-newSid – <Specify New System Identifier. This option can only be used for Oracle Express Edition upgrades>]
[-newInitParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value. Use this option to specify parameters that are allowed only on the target Oracle home>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-oracleHomeUserPassword – <Specify Oracle Home user password>]
[-pdbs – <Specify a comma separated list with the names of the pluggable databases (PDB) that will be upgraded. Specify ALL to select all or NONE to select none of the pluggable databases for upgrade>]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-pdbsWithPriority – <Specify a comma separated list of pluggable databases (PDB) to be upgraded along with its corresponding priorities (being 1 the top priority) of the format <pdb name>:<upgrade priority>,<pdb name>:<upgrade priority> >]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-performFixUp – <true | false> Enable or disable fix ups for the silent upgrade mode.]
[-postUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed at the end of the upgrade>]
[-preUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed before the upgrade>]
[-recompile_invalid_objects – <true | false> Recompile invalid objects as part of the upgrade.]
[-upgrade_parallelism – <Specify number of CPU’s to be used for parallel upgrade>]
[-upgradeTimezone – <true | false> Upgrade the timezone files of the database.]
[-upgradeXML – <Specify the path to the existing pre-upgrade XML file> This option only applies to in-place database upgrades.]
[-useExistingBackup – <true | false> To restore database using existing RMAN backup.]
[-useGRP – <Specify the name of the existing guaranteed restore point> To restore the database using a specified guaranteed restore point.]
Even when -createListener show as valid syntax, using -createListener is not recognized.
$ cat run_dbua.sh
date /app/product/19.3.0.0/bin/dbua -silent \ -sid db01 \ -oracleHome /app/product/11.2.0.4 \ -useGRP upgrade19c \ -recompile_invalid_objects TRUE \ -upgradeTimezone TRUE \ -emConfiguration NONE \ -skipListenersMigration \ -createListener false \ --- failed -upgrade_parallelism 8 date exit ./run_dbua.sh: line 10: -createListener: command not found This works. /app/product/19.3.0.0/bin/dbua -silent -sid db01 -skipListenersMigration -oracleHome /app/product/11.2.0.4 -recompile_invalid_objects true -upgradeTimezone true -emConfiguration NONE -upgrade_parallelism 4 -createListener false
What am i missing?
Hopefully, you will have better luck than I did.
Update: there as a space from line above thanks to https://twitter.com/VincePoore
$ grep -r '[[:blank:]]$' run_dbua.sh -skipListenersMigration \ $ grep -r '[[:blank:]]$' run_dbua.sh | wc -l 1
October 18, 2020
Followup with Database runInstaller applyRU Failed Me
This is a followup Database runInstaller applyRU Failed Me
I finally figured out my error which I should have seen from the beginning and better error reporting would have helped.
Can you guess what’s wrong?
unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_LINUX.zip; echo $?
versus
unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?
The wrong platform for opatch was used.
Here is what should have been deployed.
[oracle@ol7-112-dg1 ~]$ unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?
0
[oracle@ol7-112-dg1 ~]$
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$
[oracle@ol7-112-dg1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0/db_1
[oracle@ol7-112-dg1 ~]$
### This failed:
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
Java (1.7) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1
[oracle@ol7-112-dg1 ~]$
### This works but why?
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version -jdk $ORACLE_HOME/jdk
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$
### Here is java version and noticed it's 64-Bit
[oracle@ol7-112-dg1 bin]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@ol7-112-dg1 bin]$$ORACLE_HOME/runInstaller -applyRU /home/oracle/patch/31305339 should now work.
October 17, 2020
Database runInstaller applyRU Failed Me
I still remembered the cliche a manager used to tell me, “Slow and steady win the race.”
Looks like it is true with Oracle software as you never know what’s going to get.
I am trying to install 19.3 database software and apply Patch 31305339 – GI Release Update 19.8.0.0.200714 which failed misserably.
There is little to no information for the failure (at least from what I was able to ascertain).
Resolving the issue requires starting over from the beginning.
Here are the steps taken:
[oracle@ol7-112-dg1 ~]$ cat /etc/system-release Oracle Linux Server release 7.7 [oracle@ol7-112-dg1 ~]$ ll /etc/ora* -rw-r--r--. 1 root root 32 Aug 8 2019 /etc/oracle-release -rw-rw-r--. 1 oracle oinstall 790 Oct 14 19:13 /etc/oratab [oracle@ol7-112-dg1 ~]$ echo $new_db_home /u01/app/oracle/product/19.3.0.0/db_1 [oracle@ol7-112-dg1 ~]$ rm -rf $new_db_home [oracle@ol7-112-dg1 ~]$ ls $new_db_home ls: cannot access /u01/app/oracle/product/19.3.0.0/db_1: No such file or directory [oracle@ol7-112-dg1 ~]$ echo $zip_loc /vagrant_software [oracle@ol7-112-dg1 ~]$ ls -l $zip_loc/LINUX.X64_193000_db_home.zip -rwxrwxrwx. 1 vagrant vagrant 3059705302 Sep 5 2019 /vagrant_software/LINUX.X64_193000_db_home.zip [oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/LINUX.X64_193000_db_home.zip -d $new_db_home; echo $?; ls $new_db_home 0 addnode crs dbjava dmu hs jdbc md olap ords plsql rdbms runInstaller sqlj ucp apex css dbs drdaas install jdk mgw OPatch oss precomp relnotes schagent.conf sqlpatch usm assistants ctx deinstall dv instantclient jlib network opmn oui QOpatch root.sh sdk sqlplus utl bin cv demo env.ora inventory ldap nls oracore owm R root.sh.old slax srvm wwg clone data diagnostics has javavm lib odbc ord perl racg root.sh.old.1 sqldeveloper suptools xdk [oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded. [oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/p6880880_190000_LINUX.zip -d $new_db_home; echo $? 0 [oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version /u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments /u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments Java (1.7) could not be located. OPatch cannot proceed! OPatch returns with error code = 1 [oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version -jdk $new_db_home/jdk OPatch Version: 12.2.0.1.21 OPatch succeeded. [oracle@ol7-112-dg1 ~]$ cd $new_db_home [oracle@ol7-112-dg1 db_1]$ ls -l /home/oracle/patch/31305339 total 132 drwxr-x---. 5 oracle oinstall 81 Jul 10 05:20 31281355 drwxr-x---. 5 oracle oinstall 62 Jul 10 05:17 31304218 drwxr-x---. 5 oracle oinstall 62 Jul 10 05:18 31305087 drwxr-x---. 4 oracle oinstall 48 Jul 10 05:20 31335188 drwxr-x---. 2 oracle oinstall 4096 Jul 10 05:18 automation -rw-rw-r--. 1 oracle oinstall 5054 Jul 10 05:46 bundle.xml -rw-rw-r--. 1 oracle oinstall 120878 Jul 20 19:04 README.html -rw-r--r--. 1 oracle oinstall 0 Jul 10 05:17 README.txt [oracle@ol7-112-dg1 db_1]$ pwd /u01/app/oracle/product/19.3.0.0/db_1 [oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339 Preparing the home to patch… Applying the patch /home/oracle/patch/31305339… OPatch command failed while applying the patch. For details look at the logs from /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto/. [oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto -bash: cd: /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto: No such file or directory [oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/ [oracle@ol7-112-dg1 cfgtoollogs]$ ll total 4 drwxrwx---. 2 oracle oinstall 4096 Oct 17 16:44 oui [oracle@ol7-112-dg1 cfgtoollogs]$ [oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339 ERROR: The home is not clean. This home cannot be used since there was a failed OPatch execution in this home. Use a different home to proceed. [oracle@ol7-112-dg1 db_1]$ Executing "gridSetup.sh" Fails with "ERROR: The home is not clean"(Doc ID 2279633.1) Fails with "ERROR: The home is not clean" . [root]# rm-Rf [oracle]$ unzip linuxx64_12201_grid_home.zip -d
Database 19c Upgrade Land Mines And Resources
Here are the contents of for a tweet I started.
Hard to imagine setting SQLPATH and glogin.sql would affect upgrade.
That’s the only changes I made.
“ORA-04023: Object SYS.STANDARD Could Not Be Validated or Authorized” during database upgrade (Doc ID 984511.1)
Documentation does not mention having customized glogin.sql and/or SQLPATH will cause upgrade to fail. Documentation has now been updated.
You can read more about Upgrade and profile scripts
AutoUpgrade validates glogin for you before starting. It looks like any improvements are only added to AutoUgrade and not traditional methods. Not only do you need to read the documentation, you also need to read again because of changes.
Lastly, here are Useful List of Examples which I find helpful.
Thinking Out Loud 