What’s with MGTDB anyways

For those who have either upgraded or fresh-installed 12.1 (12c) Grid Infrastructure stack, will notice a new database instance (-MGMTDB) that was provisioned automagically. So what is this MGMTDB and why do I need this overhead.

Si let’s recap what the DB is and what it does…
Management Database is the central repository to store Cluster Health Monitor, the Grid Infrastructure Management Repository.

MGMT database is a container database (CDB) with one pluggable database (PDB) running. However, this database runs out of the Grid Infrastructure home.
The MGMTDB is a Rac One Node database; i.e., it runs on one node at a time, but because this is Clustered Resource, it can be started or failed over on any node in the cluster. MGMTDB is as a non-critical component of the GI stack (with no “real” hard dependencies). This means that if MGMTDB fails or becomes unavailable, Grid Infrastructure continues running

MGMTDB is configured (subject to change) with 750 MB SGA/325 MB PGA, and 5GB database size. But note that, due to the footprint MGMT’s SGA is not configured for hugepages . Since, this database is dynamically created on install, the OUI installer does not have pre-knowledge of the database that are configured or will be migrated to this cluster, thus in order to avoid any database names conflict the name “-MGMTDB” was chosen (notice the “-“). Note, bypassing MGMTDB installation is only allowed for upgrades to 12.1.0.2. New 12.1.0.2 installations or upgrades to future releases will require MGMTDB to be installed. if MGMTDB is not selected during upgrade, all features (Cluster Health Monitor (CHM/OS) etc) that depend on it will be disabled.

So if you are wondering where the datafiles and other structures are stored for this database. Well they would will be stored in the same diskgroup as OCR and VOTE However, these dtabase files can be migrated into ASM diskgroup post install.

MGMTDB will store a subset of Operating System (OS) performance data for longer term to provide diagnostic information and support intelligent workload management. Performance data (OS metrics similar to, but a subset of Exawatcher) collected by the ‘Cluster Health Monitor’ (CHM) is stored also on local disk, so when not using MGMTDB, CHM data can still be obtained from local disk but intelligent workload management (QoS) will be disabled. onger term MGMTDB will become a key component of the Grid Infrastructure and provide services for important components, because of this MGMTDB will eventually become a mandatory component in future upgrades to releases on Exadata.

See document 1568402.1 for more details.

New [rarely covered and rarely discussed] 12c features

With busy weeks of IOUG and other conferences coming up, we have little time to blog…. So, in the coming weeks, I’m just going to do some “baby” blogs; i.e., some quick tips and new features

Here’s a new 12c new feature that simplifies snapshotting databases

Snapshot Optimized Recovery

There’s many of you that take snapshot copies of database, either via server-side snapshot tools or using storage level snapshots. Usually this required a cold database or putting the database in hot-backup mode. However, there are downsides to both options

In Oracle 12c, third-party snapshots technologies that meet the following requirements can be taken without requiring the database to be placed in backup mode:

Database is crash consistent at the point of the snapshot.
Write ordering is preserved for each file within a snapshot.
Snapshot stores the time at which a snapshot is completed.

The new RECOVER SNAPSHOT TIME command is introduced to recover a snapshot to a consistent point, without any additional manual procedures for point-in-time recovery needs.
This command performs the recovery in a single step. Recovery can be either to the current time or to a point in time after the snapshot was taken

Though there is a bit upfront overhead; e.g.,additional redo logging and a complete database checkpoint.

Queryable Opatch and Datapatch

One of my Oracle Support Buddies mentioned to me a cool feature called Query-able Opatch.  This new feature of 12c Oracle Database provides the capability to store, in-database, and query the patch inventory.  Note this feature is specific to Database Home, it does fit into the for Grid Infrastructure or other Oracle Homes.

I wasn't quite sure what problem this feature was trying to solve or what big value it was attempting to bring on.  Regardless, I thought I'd investigate and see what this feature was about.  Mind you I didn't do any in-depth analysis, but enough to shed light on the topic.  I'll followup later was detailed analysis.    We will also touch on the new Datapatch feature as well. 

Query-able Opatch
In versions prior to 12c, the typical stack flow of getting Oracle patch inventory information was :  
opatch lsinventory —> oraInventory_loc  —> Central Inventory (OBase) —> local inventory (OHome) 

Now, In 12c, the stack flow is as follows, if you implement and configure Queryable Opatch feature: 
opatch lsinvenroty (XML) —> queryable patch interface (qpi) —{XML} —> Inventory data (in database) 
  
The key ingredient here is the queryable patch interface (qpi).  
QPI consists of 
	•	      External table (patch_xml_inv) created by catqitab.sql. 
	•	      Uses oracle_load and and prepocessor (opatch_script_dir —>qop)
	•	      SQL interface dbms_qopatch, dbmsqopi.sql, used as plsql interface to query. The dbms_opatch package contains the following procedures/functions : get_patch, 
			  get_patch_lsinventory, get_sqlpatch_status


Once the external table is created using the catqitab.sql script, you can then execute the load and instantiation of the Opatch Registry data.  

Process of instantiation of Opatch Registry data: 


NewImage


1. Select against the opatch_xml_inv (external table) 2. Execution of opatch lsinventory -xml (pre-processor program) 3. Load inventory data into table(s)
SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name like 'OPATCH%'; DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ----------------------------------------------- OPATCH_LOG_DIR /u01/app/oracle/product/12.1/db_home1/QOpatch OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1/db_home1/QOpatch NewImage




Note, that the DBMS_QOPATCH returns XML format, thus you'll need to transform the XML format into something more readable; e.g., using a stylesheet (XSLT). Luckily Oracle provides a default XSLT, GET_OPATCH_XSLT. GET_OPATCH_XSLT is function of DBMS_QPOPATCH. You can use this function or you can build your own XSLT sheet. NewImage







DataPatch is also a new sub-feature of Queryable Patch Inventory.  DataPatch is a driver script that automates the post-patch SQL actions for database patches 
It is applicable only to database home (not GI home) and for patches that have a SQL changes. 
When the  binary patch is successfully applied, Datapatch updates the SQL Patch Registry in the database (table) —> dba_registry_history/dba_registry_sqlpatch 
Note, DataPatch has to be executed per database.  Also, YOU STILL INSTALL PATCHES using Opatch first !!! 
Without DataPatch you could never tell if the database had the SQL part of the patch applied.

NewImage

Installing Oracle Linux 6.6, Prepping Linux OS for Oracle Database 12c Install, and Installing Oracle Database 12c

Installing Oracle Linux 6.6, Prepping Linux OS for Oracle Database 12c Install, and Installing Oracle Database 12c

I know it seems simple to install Linux and Oracle Dtaabase. But I felt a need to standardize on how we do it internally at Viscosity. So I provided this little note to our DBA team to build out a new Oracle Database 12c on Linux 6.6 server.  There are options also for configuring for VMWare as well.  PS, Thx to EMC folks for helping out w/the hardware config

In this example, we will be implementing 12c Grid Infrastructure/Automatic Storage Management and create two disk groups (data and deco)  using four-disks each.

Installing Oracle Linux 6.6
1. Insert the Oracle Linux 6.6 DVD into the server, and boot to it.
2. Select Install or upgrade an existing system
3. Skip
4. In the opening splash screen, select Next.
5. Choose the language you wish to use, and click Next.
6. Select the keyboard layout, and click Next.
7. Select Basic Storage Devices, and click Next.
8. Select Fresh Installation, and click Next.
9. Insert the hostname, and select Configure Network.
10. In the Network Connections menu, configure network connections.
11. After configuring the network connections, click Close.
12. Click Next.
13. Select the nearest city in your time zone, and click Next.
14. Enter the root password, and click Next.
15. Select Use All Space, and click Next.
16. When the installation prompts you to confirm that you are writing changes to the disk, select Write changes to disk.
17. Select Software Basic Server, and click Next. Oracle Linux installation begins.
18. When the installation completes, select Reboot to restart the server.

Initial configuration tasks
Complete the following steps to provide the functionality that Oracle Database requires. We performed all of these tasks as root.
Disable firewall services. In the command line (as root), type:
 # service iptables stop
# chkconfig iptables off
# service ip6tables stop
# chkconfig ip6tables off

Set SELinux:
 # vi /etc/selinux/config

SELINUX=permissive

Modify /etc/hosts to include the IP address of the internal IP and the hostname.
Edit 90-nproc.conf:

# vi /etc/security/limits.d/90-nproc.conf
 Change this:
 * soft nproc 1024

To this:
 * – nproc 16384

Install 12c RPM packages, resolve package dependencies, and modify kernel parameters:                                        

# yum install oracle-rdbms-server-12cR1-preinstall–y

Install automatic system tuning for database storage through yum:
 # yum install tuned
# chkconfig tuned on
# tuned-adm profile enterprise-storage

Using yum, install the following prerequisite packages for Oracle Database:
 # yum install elfutils-libelf-devel
# yum install xhost
# yum install unixODBC
# yum install unixODBC-devel
# yum install oracleasm-support oracleasmlib oracleasm

Create the oracle user account and groups and password:

 # groupadd -g 6003 oper
 # groupadd -g 6004 asmadmin
 # groupadd -g 6005 asmdba
 # groupadd -g 6006 asmoper
 # usermod -G dba,oper,asmadmin,asmdba,asmoper oracle # passwd oracle

Create the /u01 directory for Oracle inventory and software and give it to the oracle user:

 # mkdir -p /u01/app/oracle/product/12.1.0/grid
 # mkdir -p /u01/app/oracle/product/12.1.0/dbs                                                 
# chown -R oracle:oinstall /u01
 # chmod -R 775 /u01

Optionally, edit bash profiles to set up user environments:
# vim /home/oracle/.bash_profile

Adding the four data and four log drives to the VM and modifying the vmfx file
1. Power off the VM.
2. Right-click the VM in the vSphere Web Client, and choose Edit Settings…
3. Click on the VM Options tab, and expand the Advanced menu option.
4. Choose Edit Configuration…
5. Click Add Row, and enter disk.EnableUUID in the parameter field and TRUE in the value field.
6. Go back to the Virtual Hardware tab.
7. Click the drop-down menu for New device, and choose New Hard Disk.
8. Name the Hard Disk and choose the size that you want it to be.
9. Repeat steps 7 and 8 for all remaining drives.
10. Click OK.
11. Power the VM back on.

 Configuring disks for ASM
For each of the eight shared disks, create a GPT label, and create one partition. For example, see the following shell script:
 for disk in sdb sdc sdd sde sdf sdg sdh sdi; do
parted /dev/$disk mklabel gpt
parted /dev/$disk mkpart primary “1 -1″
 done

If desired, label the disk’s partition with its Oracle function. For example:
 # parted /dev/sdc name 1 DATA1
# parted /dev/sdd name 1 DATA2
# parted /dev/sde name 1 DATA3
# parted /dev/sdf name 1 DATA4
# parted /dev/sdg name 1 LOG1
# parted /dev/sdh name 1 LOG2
# parted /dev/sdh name 1 LOG3
# parted /dev/sdi name 1 LOG4

Initialize Oracle ASM on each server by executing the following commands as root on each node.
 oracleasm init
oracleasm configure -e -u grid -g oinstall -s y -x sda

Label each shared disk-partition with an appropriate ASM name. For example, following the OS partition names
created above, execute the following commands on one system:
 # oracleasm createdisk DATA1 /dev/sdc1
# oracleasm createdisk DATA2/dev/sdd1
# oracleasm createdisk DATA3 /dev/sde1
# oracleasm createdisk DATA4 /dev/sdf1
# oracleasm createdisk LOG1 /dev/sdg1
# oracleasm createdisk LOG2 /dev/sdh1
# oracleasm createdisk LOG3 /dev/sdi1
# oracleasm createdisk LOG4 /dev/sdj1

On each server, scan the disks to make the disks immediately available to Oracle ASM.
 # oracleasm scandisks
# oracleasm listdisks

Installing Oracle Grid Infrastructure 12c
1. Log in as the oracle user.
2. Unzip linuxamd64_12c_grid_1of2.zip and linuxamd64_12c_grid_2of2.zip
3. Open a terminal to the unzipped database directory.
4. Type grid_env to set the Oracle grid environment.
5. To start the installer, type./runInstaller
6. At the Updates screen, select Skip updates.
7. In the Select Installation Option screen, select Install and Configure Grid Infrastructure for a Standalone Server,and click Next.
8. Choose the language, and click Next.
9. In the Create ASM Disk Group screen, choose the Disk Group Name, and change redundancy to External.
10. Select the four disks that you are planning to use for the database, and click Next.
11. In the Specify ASM Password screen, choose Use same password for these accounts, write the passwords for the ASM users, and click Next.
12. Leave the default Operating System Groups, and click Next.
13. Leave the default installation, and click Next.
14. Leave the default inventory location, and click Next.
15. Under Root script execution, select Automatically run configuration scripts and enter root credentials.
16. In the Prerequisite Checks screen, make sure that there are no errors.
17. In the Summary screen, verify that everything is correct, and click Finish to install Oracle Grid Infrastructure.
18. At one point during the installation, the installation prompts you to execute two configuration scripts as root.

Follow the instructions to run the scripts.
19. At the Finish screen, click Close.
20. To run the ASM Configuration Assistant, type asmca.
21. In the ASM Configuration Assistant, click Create.
22. In the Create Disk Group window, name the new disk group log, choose redundancy External (None), and select the four disks for redo logs.
23. Click Advanced Options, and type 12.1.0.0.0 in ASM Compatibility and Database Compatibility. Click OK.
24. Right-click the DATA drive, and choose Edit Attributes. Make sure both ASM and Database Compatibility fields list 12.1.0.0.0, and click OK.
25. Exit the ASM Configuration Assistant.

Installing Oracle Database 12c
1. Unzip linuxamd64_12c_database_1_of_2.zip and linuxamd64_12c_database_2_of_2.zip.
2. Open a terminal to the unzipped database directory.
3. Type db_env to set the Oracle database environment.
4. Run ./runInstaller.sh.
5. Wait for the GUI installer loads.
6. On the Configure Security Updates screen, enter the credentials for My Oracle Support. If you do not have an account, uncheck the box I wish to receive security updates via My Oracle Support, and click Next.
7. At the warning, click Yes.
8. On the Download Software Updates screen, enter the desired update option, and click Next.
9. On the Select Installation Option screen, select Install database software only, and click Next.
10. On the Grid Installation Options screen, select Single instance database installation, and click Next.
11. On the Select Product Languages screen, leave the default setting of English, and click Next.
12. On the Select Database Edition screen, select Enterprise Edition, and click Next.
13. On the Specify Installation Location, leave the defaults, and click Next.
14. On the Create Inventory screen, leave the default settings, and click Next.
15. On the Privileged Operating System groups screen, keep the defaults, and click Next.
16. Allow the prerequisite checker to complete.
17. On the Summary screen, click Install.
18. Once the Execute Configuration scripts prompt appears, ssh into the server as root, and run the following command:

# /home/oracle/app/oracle/product/12.1.0/dbs/root.sh
19. Return to the prompt, and click OK.
20. Once the installer completes, click Close.

New [rarely covered and rarely discussed] 12c features

With busy weeks of IOUG and other conferences coming up, we have little time to blog…. So, in the coming weeks, I’m just going to do some “baby” blogs; i.e., some quick tips and new features

Here’s a new 12c new feature that simplifies snapshotting databases

Snapshot Optimized Recovery

There’s many of you that take snapshot copies of database, either via server-side snapshot tools or using storage level snapshots. Usually this required a cold database or putting the database in hot-backup mode. However, there are downsides to both options

In Oracle 12c, third-party snapshots technologies that meet the following requirements can be taken without requiring the database to be placed in backup mode:

Database is crash consistent at the point of the snapshot.
Write ordering is preserved for each file within a snapshot.
Snapshot stores the time at which a snapshot is completed.

The new RECOVER SNAPSHOT TIME command is introduced to recover a snapshot to a consistent point, without any additional manual procedures for point-in-time recovery needs.
This command performs the recovery in a single step. Recovery can be either to the current time or to a point in time after the snapshot was taken

Though there is a bit upfront overhead; e.g.,additional redo logging and a complete database checkpoint.