The Oracle® Database version 19c introduced a slew of new and improved features.
Oracle 19c is the last in the Oracle Database 12c release 2 product family with long-term support. It is available on Linux®, Windows®, Solaris®, HP/UX®, and AIX® platforms as well as the Oracle Cloud. Oracle 19c provides customers with the best performance, scalability, reliability, and security features for all their operational and analytical workloads.
Following are the new features that make database administrator (DBA) tasks easier:
The following sections explore these features in more depth.
Use DBCA silent mode for cloning or relocating a PDB, CDB, or non-CDB database, as shown in the following examples:
To create a PDB by cloning a remote PDB with DBCA in silent mode, use the following syntax:
dbca -createPluggableDatabase -createFromRemotePDB -remotePDBName <REMOTE_PDB_NAME> -remoteDBConnString <REMOTE_DB_CONN_STRING> -sysDBAUserName <SYSDBUSERNAME> -sysDBAPassword <SYSDBAPASSOWRD> -dbLinkUsername <DBLINK_COMMON_USER_NAME> -dbLinkUserPassword <DBLINK_COMMON_USERNAME_PWD> -sourceDB <Local_CDB> -pdbName <PDBTOBECREATED>
To relocate a PDB to another CDB, use the following syntax:
dbca -silent -relocatePDB -remotePDBName <REMOTE_PDB_NAME> -remoteDBConnString <REMOTE_DB_CONN_STRING> -sysDBAUserName <SYSDBUSERNAME> -sysDBAPassword <SYSDBAPASSOWRD> -dbLinkUsername <DBLINK_COMMON_USERNAME> -dbLinkUserPassword <DBLINK_COMMON_USERNAME_PWD> -sourceDB <DBNAME_PDB_TOBERELOCATED> -pdbName <PDBTOBERECREATED
To create a duplicate of a non-CDB or Real Application Clusters (RAC) database, use the following syntax:
dbca -silent -createDuplicateDB -gdbName test -primaryDBConnectionString <Hostname>:1521/orcl.us.rackspace.com -sid test -databaseConfigType SINGLE -initParams db_unique_name=test -sysPassword XXXXX
This feature makes automation and prechecks tasks as real as possible by providing similar inputs and outputs that a DBA would use for upgrade tasks. This feature is for RAC only and not yet available for Oracle Restart. Following are the checks performed in a dry-run Grid upgrade:
Note: After a dry run, you must detach ORACLE_HOME on the first node to proceed with an actual upgrade. Use the following syntax to perform this task:
runInstaller -silent –waitforcompletion -detachHome ORACLE_HOME=<> -local
The primary database and standby are now available in the same current state. The standby is in sync with primary even if you flashback the primary database by using the flashback feature. In previous releases of the Oracle up to version 19c, you had to perform a manual process to keep the standby in the same state and synced with the primary.
You can now use DML and PL/SQL in standby mode. As an Oracle Active Data Guard user, you can run DML statements or PL/SQL codes at the database level. Also, you can run DML statements or PL/SQL codes at the session level as needed if you haven’t configured settings at the database level. Use the following settings and commands:
For all the sessions of DML activity, set the parameter at standby:
For all the sessions of PL/SQL Activity, set the parameter at standby:
For the current session settings, use:
ALTER SESSION ENABLE ADG_REDIRECT_DML ; ALTER SESSION ENABLE ADL_REDIRECT_PLSQL ;
Oracle introduced Automatic Diagnostic Repository (ADR) and non-ADR listener parameters to control log and trace file retention. Following are the parameters that take care of limitations. By default, the system does not set these. If set to 0, they work without limitation. You can set the following non-zero limits:
The newly introduced package lets you use SQL Access Advisor for automatic task configuration of index creation, rebuild, and analysis. The following packages work with it:
Use the DBA_INDEXES column, AUTO, to ensure that auto index configuration created an index.
Admin clients can now do image-based Oracle client installation the same as Oracle Database software binaries.
You can upgrade Oracle Databases automatically by using the AutoUpgrade feature. The configuration file is the base of this feature. By using it, you can guide the AutoUpgrade process, controlling the many Oracle Database upgrade options.
AutoUpgrade utility: oracle_home/rdbms/admin/autoupgrade.jar
Location of log/configuration files: Find the files in the following locations:
Default configuration file name: autoupgrade
What to upgrade or not with AutoUpgrade
The following list shows what to upgrade or avoid upgrading with AutoUpgrade:
Perform the following steps to set up and use AutoUpgrade:
Oracle recommends checking the current version and downloading the latest from MOS 2485457.1 for the upgrade.
Set up the configuration file parameters on the command line by using the following syntax:
java –jar autoupgrade.jar –config /dir/myconfig.cfg –config_values “source_home=/srcdir, target_home=/trgdir, sid=sales” –mode deploy
Alternately, you can use the following environment variables and config parameters declared empty:
ORACLE_HOME = /u01/app/oracle/product/184.108.40.206
ORACLE_TARGET_HOME = /u01/app/oracle/product/19.0.3
ORACLE_TARGET_VERSION = 18.3
The preceding commands generate a configuration file with a default name, which you can modify for your environment and then proceed with the following steps:
You can also define Global variables and local variables. Local variable declaration overrides global variables as shown in the following image:
You can also automatically run batch processing commands by using shell scripts or a PowerShell® script or batch file, as shown in the following image:
Upgrade the same source and target:
To upgrade the same server source and target Oracle Database, use the following sequence as recommended by Oracle:
java –jar autoupgrade.jar -config_values "" -mode analyze
java –jar autoupgrade.jar -config_values "" -mode fix
java –jar autoupgrade.jar -config_values "" -mode deploy
Upgrade a different source and target:
For different source and target Oracle product upgrades, use the
fix commands from the previous step on the source and use the following
upgrade command on the target:
java –jar autoupgrade.jar -config_values "" -mode upgrade
Monitor the upgrade:
You can monitoring AutoUpgrade by using the console, the HTML file in log location, and log files for validation and monitoring from the following locations:
Important: Here are some tips for the usage of commands in the AutoUpgrade console:
Use the following AutoUpgrade job status commands to start and stop jobs:
resume -job <number>
status [-job <number>]
restore -job <number>
abort -job <number>
By using the new features in this post, a DBA can easily manage database software installations, upgrades, and performance tune SQL queries with degraded performance caused by a lack of indexes or poor index performance. Don’t forget to check the usage of the Oracle 19c new features on your test environment before putting into an action plan on the production environment.
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.