This chapter describes how to create and manage databases on Oracle Database Appliance. It also covers how to use Oracle Database Appliance solid-state drives (SSDs) and how to update and upgrade Oracle Database on Oracle Database Appliance.
Many tasks related to managing Oracle Databases, described in the Oracle Database documents on the Oracle Help Center page at http://docs.oracle.com, are also required with databases on Oracle Database Appliance. However, Oracle Database Appliance provides its own command-line tool (OAKCLI), to manage all components on the system. This includes commands to create, upgrade, and patch databases; create and upgrade Oracle homes, and create and modify database creation parameter files.
To simplify database creation and related tasks, Oracle Database Appliance combines the capabilities of the database administrator role into its root user. Therefore, these tasks should always be performed using OAKCLI. See "Oracle Appliance Manager Command-Line Interface" for detailed syntax and usage information of all OAKCLI commands.
The Oracle Database Appliance plug-in allows you to monitor Oracle Database Appliance targets using Oracle Enterprise Manager Cloud Control 12c. The plug-in provides configuration and monitoring information about any Oracle Database Appliance target running Appliance Manager 12.1.2.2 or later.
See the Oracle Enterprise Manager Plug-in for Oracle Database Appliance User's Guide for more information about the plug-in.
You can also manage your database with Oracle Enterprise Manager Database Express. Oracle Enterprise Manager Database Express is a web-based tool for managing Oracle Database 12c. Built inside the database server, it offers support for basic administrative tasks such as storage and user management. Oracle Enterprise Manager Database Express also provides comprehensive solutions for performance diagnostics and tuning. In addition, Oracle Enterprise Manager Database Express provides an interface for performance advisors and for Oracle Database utilities such as SQL*Loader and Oracle Recovery Manager (RMAN).
Oracle Database 2 Day DBA for an introduction to Oracle Enterprise Manager Database Express.
If you are loading data or migrating data from an existing database to Oracle Database Appliance, then you can use tools such as SQL*Loader, Oracle Data Pump, transportable tablespaces, and RMAN. You can also use the RMAN utility to back up and recover databases on Oracle Database Appliance.
Oracle Database Backup and Recovery User's Guide
Oracle Database Backup and Recovery Reference
Oracle Database Utilities for information about data loading
"Performing Oracle ASM Data Migration with RMAN" in Oracle Automatic Storage Management Administrator's Guide for an overview of how to migrate data to Oracle Automatic Storage Management (Oracle ASM).
Oracle Clusterware provides the cluster technology required for Oracle Real Application Clusters (Oracle RAC). In addition, Oracle Clusterware manages applications and processes as resources that you register with Oracle Clusterware. The number of resources that you register with Oracle Clusterware to manage an application depends on the application. Applications that consist of only one process are usually represented by only one resource. More complex applications that were built on multiple processes or components might require multiple resources.
Oracle Clusterware Administration and Deployment Guide for information about making applications highly available with Oracle Clusterware
Oracle Real Application Clusters One Node (Oracle RAC One Node) is a single instance of an Oracle RAC database that runs on one node in a cluster. Instead of stopping and starting instances, you can use the Oracle RAC One Node online database relocation feature to relocate an Oracle RAC One Node instance to another server.
You administer Oracle RAC One Node databases a bit differently than Oracle RAC or single-instance Oracle Databases. For Oracle RAC One Node databases, one node is the primary node, and the other node is a candidate node, which is available to accommodate services if the primary node fails or is shut down for maintenance. The nodes, databases, and database services reside in the generic server pool.
Oracle Real Application Clusters Administration and Deployment Guide for more information about administering Oracle RAC One Node
Oracle Read Application Clusters (Oracle RAC) provides technology that links two or more individual computers so that they function as one system. Oracle RAC deployed on Oracle Database Appliance enables each node to share access to a database. If one node fails or is taken offline, then the other node continues operating and the entire Oracle RAC database remains available.
Oracle Database Appliance currently supports only administrator-managed databases, where the database administrator allocates each instance of the database to a specific node in the cluster. Policy-managed databases, where the database administrator defines the number of database instances required, but not the nodes where they will run, are not available on Oracle Database Appliance.
When you review the database resource for an administrator-managed database, you see a server pool defined with the same name as the Oracle database. This server pool is part of a special Oracle-defined server pool called Generic. Oracle RAC manages the Generic server pool to support administrator-managed databases. When you add or remove an administrator-managed database using either the Server Control (SRVCTL) utility or Oracle Database Configuration Assistant (DBCA), Oracle RAC creates or removes the server pools that are members of Generic. You cannot use SRVCTL or Oracle Clusterware Control (CRSCTL) utility commands to modify the Generic server pool.
During configuration, two administrative accounts are created for Oracle Database Appliance: the user grid, with a user ID (UID) of 1000, and the user oracle, with a UID of 1001. The user grid is the Oracle Grid Infrastructure installation owner and the user oracle is the Oracle Database installation owner and the owner of all Oracle Database homes. By default, these users belong to groups shown in the following table.
| Group Name | Group ID (GID) | grid is a member | oracle is a member |
|---|---|---|---|
oinstall |
1001 | yes (primary group) | yes (primary group |
dba |
1002 | no | yes |
racoper |
1003 | yes | yes |
asmdba |
1004 | yes | yes |
asmoper |
1005 | yes | no |
asmadmin |
1006 | yes | no |
If you create an initial database during deployment, then the password for the SYS and SYSTEM users is welcome1. You should change this password for both users as soon as possible to prevent unauthorized access to your database using these privileged accounts.
Oracle Grid Infrastructure Installation Guide for Linux for information about operating system privileges groups
Oracle Automatic Storage Management Administrator's Guide for information about Oracle ASM system privileges
Use the Oracle Appliance Manager commands described in "Oracle Appliance Manager Command-Line Interface" to create and manage databases on Oracle Database Appliance. This will help you avoid using commands and parameters that could reduce the functionality of your databases, such as changing parameters associated with database file locations (for example, control_files, db_create_file_dest, db_recovery_file_dest).
Use the oakcli create database command to create additional databases on Oracle Database Appliance.
When you run this command, respond to each prompt by entering the number that corresponds with the option you want to apply to your database. When a default is supplied and is the value you want to use (typically shown as option 1), then press the Enter key to accept that value. When there are many options and the value you want isn't displayed, press 0 to reveal all of the options.
Use the oakcli create db_config_params params_file command to create a configuration file for configuring multiple databases on Oracle Database Appliance. params_file is the name of the configuration file that you generate.
When you run this command, respond to each prompt by entering the number that corresponds with the option you want to apply to your database. When a default is supplied and is the value you want to use (typically shown as option 1), press Enter to accept that value. When there are many options, you might need to press 0 to reveal all of the options if the value you want is not displayed.
To see your existing database configuration files, use the oakcli show db_config_params command, as in the following example:
# oakcli show db_config_params Available DB configuration files are: default eurodbs 4kblockdbs mytest.params
Note that only nondefault extensions are included in the output. The default extension, .dbconf, is not shown.
To use a database configuration file to create a database, or many databases with identical profiles, use the oakcli create database -db db_name -params params_file command. db_name is the name of the database you want to create and params_file is the name of the configuration file (for example, oakcli create database -db myxldb -params myxldb.dbconf).
Remove unwanted database configuration files with the oakcli delete db_config_params params_file command, providing the name of the configuration file name as the params_file value. As with other Oracle Database Appliance Manager commands related to database configuration files, you do not need to include the extension if your file has the default extension value, which is .dbconf.
An Oracle snapshot database is created by taking a snapshot of the Oracle ASM Cluster File System (Oracle ACFS) where the source data files reside. The source database can be a single instance, Oracle RAC, or Oracle RAC One Node. Compared to other methods of creating copies of databases, snapshot databases require less time and storage space and involve no downtime of the source database. Additionally, you can create any database type and class from any other type and class. For example, you can create an Oracle RAC database from an Oracle RAC One Node database. Similarly, you can create a database that is different in size than the source database.
On Oracle Database Appliance, you can create snapshot databases from any Oracle Database instance stored on Oracle ACFS. Beginning with Oracle Database Appliance release 12.1.2.0.0, this includes any Oracle Database 11g release 11.2.0.4 or later database created or upgraded on the system. Additional requirements for a database to be used as the source for a snapshot database include:
Must not be a standby or container database
Must not be running in read-only mode, or in restricted mode, or in online backup mode
Must be in ARCHIVELOG mode
Must have all defined data files available and online
Also, ensure that the system clocks on the two Oracle Database Appliance nodes are synchronized before you create a snapshot database. If the clocks are significantly different, then the command might fail.
To create a snapshot database, use the oakcli create snapshotdb command. The following example creates a snapshot database named snapprod from the database named prod.
oakcli create snapshotdb -db snapprod -from prod
Caution:
Oracle Database Appliance does not support centralized wallets with Transparent Data Encryption. Recovery of encrypted data might fail in the snapshot database if the source database relies on an external, centralized wallet.Use the rconfig command-line utility as described in the Oracle Real Application Clusters Administration and Deployment Guide to convert a single-instance database to either Oracle RAC or Oracle RAC One Node. The contents of a ConvertToRAC_AdminManaged.xml file determine the type and other characteristics of the converted database.
Oracle Real Application Clusters Installation and Configuration Guide, "Converting to Oracle RAC and Oracle RAC One Node from Single-Instance Oracle Databases"
An Oracle home is the directory in which you install Oracle Database binaries, and from which Oracle Database runs. Use Oracle Appliance Manager (through oakcli commands) to create and manage multiple Oracle homes and databases on Oracle Database Appliance. Oracle Database Appliance Manager automatically creates an Oracle Database Oracle home that is compliant with the Optimal Flexible Architecture (OFA) standards.
Oracle Database Appliance supports multiple Oracle homes including different versions for Oracle Database 11g Release 2 and Oracle Database 12c Release 1. The exact releases differ from version to version. Check the related readme files or Release Notes for specific versions.
Refer to My Oracle Support note 888888.1, at https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=888888.1, for more details about the available database versions.
When you use oakcli commands to create multiple homes on Oracle Database Appliance, the commands start the cloning process used by Oracle Grid Infrastructure. In the current release, the user oracle owns all of the Oracle homes.
Note:
If you are not upgrading from an earlier release, then download the Oracle Database Appliance End-User Bundle for the Oracle Database version that you want to install. See My Oracle Support note 888888.1 for more details:https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=888888.1
Use oakcli commands to create, manage, patch, and upgrade multiple databases on Oracle Database Appliance. The oakcli create database command enables you to create a database with minimal user input. When used without any additional options, the command creates a new database home. Alternatively, create a database in an existing home by using the -oh option. Note that Oracle does not recommend applying RDBMS patches directly. Instead, you should only use Oracle Database Appliance patch bundles, which are tested to work across the whole software stack.
Note:
Useoakcli commands to create new databases in either existing Oracle homes or in new Oracle homes.Oracle Database provides a method for managing CPU allocations on a multi-CPU server that runs multiple database instances. This method is called instance caging. Instance caging and Oracle Database Resource Manager (the Resource Manager) collaborate to support your desired service levels across multiple instances. Consolidation can minimize idle resources, maximize efficiency, and lower costs.
Oracle Database Appliance templates are already tuned for the size of each database instance workload. They are designed to run on a specific number of cores. Instance caging ensures that each database workload is restricted to the set of cores allocated by the template, enabling multiple databases to run concurrently with no performance degradation, up to the capacity of Oracle Database Appliance. You can select database template sizes larger than your current needs to provide for planned growth.
Note:
Oracle strongly recommends that you use the Oracle Database Appliance templates, because they implement best practices and are configured specifically for Oracle Database Appliance.Tip:
The Oracle Database Appliance Manager interface refers to the database sizing templates as classes of databases.By default, instance caging is not enabled on Oracle Database Appliance. To enable instance caging, set the initialization parameter, RESOURCE_MANAGER_PLAN, for each database on Oracle Database Appliance. The parameter specifies the plan to be used by the Resource Manager for the current instance. Setting this parameter directs the Resource Manager to allocate core resources among databases. If no plan is specified with this parameter, then the Resource Manager is not enabled and instance caging will not be enabled.
Instance caging allocation of core resources is enabled in accordance with the Oracle Database Appliance database template size that you select for each database. The CPU_COUNT initialization parameter is set in the template. Use the CPU_COUNT setting that matches the size of each database to consolidate, and follow the standard instructions for configuring instance caging.
Oracle Database Administrator's Guide for more information about enabling and configuring instance caging and the Resource Manager
Oracle Database Appliance includes solid-state drives (SSDs) to enhance the performance of certain operations. SSDs are used for:
Oracle Database Appliance contains four dedicated SSDs in slots 20 through 23 specifically for database redo logs. An Oracle ASM disk group named +REDO with High Redundancy is provisioned during the deployment process to accelerate database redo log write operations and improve latency. Databases automatically utilize these SSDs, and no other files can be hosted on them.
Oracle Database Appliance X5-2 introduces four additional 400 GB SSDs in slots 16 through 19 that can be used to host database files, or as a database flash cache in addition to the buffer cache.
An Oracle ASM disk group named +FLASH with Normal Redundancy is provisioned on these SSDs. All of the storage in the +FLASH disk group is allocated to an Oracle ASM Dynamic Volume (flashdata), and formatted as an Oracle ACFS file system. Storage in this flashdata file system is then made available as an Oracle ACFS file system and is used to create database flash cache files that accelerate read operations. The file that contains the flash cache is automatically created for each database and is specified using the database init.ora parameter db_flash_cache_file. By default, db_flash_cache_size is set to 3 times the size of SGA, up to 196 GB, unless there is not enough space, in which case the size parameter is set to 0. After you change the db_flash_cache_size parameter, you must restart the database to use the newly sized flash cache.
Oracle Database Administrator's Guide for information about configuring a database smart flash cache
Oracle Database Appliance Manager configurator and the oakcli create database command provide the option to store entire databases in flash memory using the flashdata Oracle ACFS file system on the 400 GB SSDs (also used for the database flash cache).
If there is not enough space available in the FLASH disk group, the oakcli create database command will not prompt you with an option to store databases in flash, and the databases files will automatically be created in the +DATA disk group. You can also store database data files on both flash and hard disk drives but this must be manually managed. It requires a thorough understanding of database usage patterns and is only recommended for advanced administrators.
The limitations of this strategy are:
Oracle Database must be release 11.2.0.4 or later
Database type must be OLTP
Only non-container databases (CDBs) can be completely stored in flash
There must be 160 GB of available space in the +FLASH disk group
"Storage on Oracle Database Appliance" for information about Oracle ACFS storage architecture on Oracle Database Appliance
oakcli create database command reference
To patch Oracle Database, use the appropriate Oracle Database Appliance patch bundle. Typically, you would update or upgrade your Oracle Database using the latest Oracle Database Appliance patch bundle listed in MOS note 888888.1. Refer to the readme file for obtaining the patch for upgrading Infratructure (INFRA) and Grid Infrastructure (GI) components.
Here are instructions for applying each of the available database patches. The patches perform rolling upgrades that automatically patch Node 1 after patching Node 0.
The following examples outline the steps required to update or upgrade the Oracle Database on Oracle Database Appliance.
Example 1: Updating to Oracle Database 12.1.0.2.5 from Previous Oracle Database 12.1.0.2.x Releases
Example 2: Upgrading to Oracle Database 12.1.0.2.5 from Oracle Database 11.2.0.4.x Releases
Run the oakcli show databases command, on Node 0 only, to confirm that you have a database with the appropriate release number for this update. The command and output should look similar to the following example:
#oakcli show databases Name Type Storage HomeName HomeLocation Version ---- ---- ------- -------- ------------ ------- db1 RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.4(20831110,20831110) db2 SINGLE ACFS OraDb12102_home2 /u01/app/oracle/product/12.1.0.2/dbhome_2 12.1.0.2.4(20831110,20831110) db3 RACOneNode ACFS OraDb12102_home3 /u01/app/oracle/product/12.1.0.2/dbhome_3 12.1.0.2.5(21359755,21359758) db4 RAC ACFS OraDb12102_home4 /u01/app/oracle/product/12.1.0.2/dbhome_4 12.1.0.2.5(21359755,21359758)
Note:
You cannot perform individual database updates for databases running in the same home. All databases running in the homes that you update, such as the two databases listed in the example in Step 1, will be patched to Oracle Database 12.1.0.2.5.Run the oakcli update -patch 12.1.2.5.0 --database command, on Node 0 only. The command automatically patches both nodes. If you have more than one database home that could be patched, then the software provides a select list from which you pick one, some, or all of the database homes to update.
After the command completes on both nodes, check your database version with the oakcli show databases command on Node 0. The command and output should now look similar to the following example.
#oakcli show databases Name Type Storage HomeName HomeLocation Version ---- ---- ------- -------- ------------ ------- db1 RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.5(21359755,21359758) db2 SINGLE ACFS OraDb12102_home2 /u01/app/oracle/product/12.1.0.2/dbhome_2 12.1.0.2.5(21359755,21359758) db3 RACOneNode ACFS OraDb12102_home3 /u01/app/oracle/product/12.1.0.2/dbhome_3 12.1.0.2.5(21359755,21359758) db4 RAC ACFS OraDb12102_home4 /u01/app/oracle/product/12.1.0.2/dbhome_4 12.1.0.2.5(21359755,21359758)
Download the 12.1.0.2.5 RDBMS Clone Patch 19520042 (file name p19520042_121200_Linux-x86-64.zip) from My Oracle Support and reate a 12.1.0.2.5 database home on Node 0:
# /opt/oracle/oak/bin/oakcli create dbhome -version 12.1.0.2.5
Run the command oakcli show dbhomes. The output appears similar to the result in this example:
oakcli show dbhomes -detail Oracle HomeName Oracle Home Version Oracle HomeLocation Database Name Database Type --------------- ------------------- ------------------- ------------- ------------- OraDb11203_home1 11.2.0.3.15(20760997,17592127) /u01/app/oracle/product/11.2.0.3/dbhome_1 no DB available OraDb11204_home1 11.2.0.4.8(21352635,21352649) /u01/app/oracle/product/11.2.0.4/dbhome_1 no DB available OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 one43 RACOneNode OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 ee120 SINGLE OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 one311 RACOneNode OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 ee311 SINGLE OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 rac311 RAC OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 ee43 SINGLE OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 one120 RACOneNode OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 odacn RAC OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 rac43 RAC OraDb12102_home1 12.1.0.2.5(21359755,21359758) /u01/app/oracle/product/12.1.0.2/dbhome_1 rac120 RAC
Run the oakcli show databases command, on Node 0 only, to confirm that you have a database with the appropriate release number for this upgrade. The command and output should look similar to the following example, which shows two databases that are candidates to be upgraded.
#oakcli show databases Name Type Storage HomeName HomeLocation Version ---- ---- ------- -------- ------------ ------- db1 RAC ACFS OraDb12102_home1 /u01/app/oracle/product/11.2.0.4/dbhome_1 11.2.0.4.7(20760982,20831122) db2 SINGLE ACFS OraDb12102_home2 /u01/app/oracle/product/12.2.0.4/dbhome_2 11.2.0.4.7(20760982,20831122) db3 RACOneNode ACFS OraDb12102_home3 /u01/app/oracle/product/12.1.0.2/dbhome_3 12.1.0.2.5(21359755,21359758) db4 RAC ACFS OraDb12102_home4 /u01/app/oracle/product/12.1.0.2/dbhome_4 12.1.0.2.5(21359755,21359758)
Upgrade a database by running the oakcli upgrade database command, on Node 0 only, providing the name of the database to upgrade and the name of the database home containing the version (12.1.0.2.5) to which you want to upgrade. The following example shows how to upgrade the tpcc database, listed in Step 4, using the dbhome12102_home1 identified in the example shown in Step 3:
# oakcli upgrade database -db tpcc -to dbhome12102_home1
After the command completes, verify your database version with the oakcli show databases command on Node 0 again. The command and output should now look similar to the following example:
#oakcli show databases Name Type Storage HomeName HomeLocation Version ---- ---- ------- -------- ------------ ------- db1 RAC ACFS OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1 12.1.0.2.5(21359755,21359758) db2 SINGLE ACFS OraDb12102_home2 /u01/app/oracle/product/12.1.0.2/dbhome_2 12.1.0.2.5(21359755,21359758) db3 RACOneNode ACFS OraDb12102_home3 /u01/app/oracle/product/12.1.0.2/dbhome_3 12.1.0.2.5(21359755,21359758) db4 RAC ACFS OraDb12102_home4 /u01/app/oracle/product/12.1.0.2/dbhome_4 12.1.0.2.5(21359755,21359758)