Oracle Database 12c - Performance Enhancement - Multiprocess Multithreaded Architecture (MPMT)

Oracle 12c Database has Multiprocess and Multithreaded (MPMT) architecture which closely shadows Linux/Unix OS process.  This feature would greatly enhance the performance of existing applications running on Database.

Benefits can be seen at CPU, Memory Usage, System Reliability and Parallel Operations level.

Multiprocess Multithreaded Architecture (MPMT)

How to implement Multiprocess and Multithreaded (MPMT) ? 

How to monitor Multiprocess and Multithreaded (MPMT) ? 

This Architecture will be mostly favorable on Linux/Unix based OS. 

Oracle process execution architecture for an Oracle database instance depends on the operating system (OS).  For example, an Oracle background process on Windows is a thread of execution within an OS process. On Linux and UNIX, an Oracle background process runs as an OS process.

Reference / Read More: 

1. Oracle Database Concepts

12c Release 1 (12.1)

E17633-20

http://docs.oracle.com/cd/E16655_01/server.121/e17633/process.htm

Section: Multiprocess and Multithreaded Oracle Database Systems

2. Oracle Database New Features Guide

12c Release 1 (12.1)

E17906-16 

http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm

How to Backup a Critical or Live Database?

Scenario: 

Oracle 9i 9.2 and Oracle 10g 10.2 Database are used for the Active Enterprise Applications like Peoplesoft and Oracle E-Business based Apps.  Database does not have any Dataguard or RAC.

Databases are standalone instances with live transactions - what would be the best backup strategy for this case.

In other words, How would you back up and recover an actively used Database which cannot afford any shutdown or maintenance window at minimal?

Suggestion: 

If you are working with transaction Production Databases the first thing I would do is setting the Databases in Archivelog mode to be able  to Backup the Databases online and also enable online recovery for some scenarios. 

In archivelog mode all Backups can be taken without needing to stop the Databases and with the archivelog files you can recover up to the last committed operation that occurred in your Database before a media failure (or) due to some other issue. 

To securely backup and to minimize the downtime when failures occur I would recommend considering having a Data guard or other type of replication (Streams, Golden Gate).

Reference / Read More: 

1. Oracle® Database Backup and Recovery Basics

10g Release 2 (10.2)

B14192-03

Chapter: 4.3 Backing Up Database Files and Archived Logs with RMAN

http://docs.oracle.com/cd/B19306_01/backup.102/b14192/bkup003.htm

2. Oracle® Database Administrator's Guide

11g Release 1 (11.1)

B28310-04

Section: Choosing Between NOARCHIVELOG and ARCHIVELOG Mode

http://docs.oracle.com/cd/B28359_01/server.111/b28310/archredo002.htm

Oracle Database 12c - Performance Enhancement - Smart Flash Cache

Applies to Oracle 12c Database Version.

Performance Enhancement - Smart Flash Cache

System Global Area and Smart Flash Cache: 

How to Implement or Use Flash Cache?

Reference / Read More: 

1. Oracle White Paper - Oracle Database Smart Flash Cache
http://www.oracle.com/technetwork/articles/systems-hardware-architecture/oracle-db-smart-flash-cache-175588.pdf

2. Oracle 12c Documentation on DB_FLASH_CACHE_SIZE and DB_FLASH_CACHE_FILE.

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10316.htm
http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10315.htm#I1010315

3. Oracle 11g 11.2 Documentation on DB_FLASH_CACHE_SIZE and DB_FLASH_CACHE_FILE.

http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams058.htm
http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams057.htm#I1010315

Difference between Pro*C and OCI

Applies to Oracle.

- Pro*C is a Precompiler.
You can write embedded SQL in your c/c++ application and then precompile it into pure C/C++.  Precompilers are part of Oracle Database install and complete installation can be normally found in Oracle Client installation.

- OCI or OCCI is an API that you write c/c++ code that calls Oracle routines, part of the Oracle Call Interface API. OCI/OCCI are part of Oracle Database install.

If you write embedded SQL (EXEC SQL ...) then you must precompile it into a C/C++ program to be compiled with a c/c++ compiler and linked into an execution.  Normally Oracle makefile compilation would do all the three steps in sequence.

Database Connection through Wallet Authorization

1. How to Create Wallet ?

From Oracle Database end, you can create/manage certificates using tools such as Oracle Wallet Manager, orapki utility.

Documented under 'Oracle Advanced Security' 

Oracle Database Advanced Security Administrator's Guide

11g Release 2 (11.2)

Part Number E10746-02

Chapter 12 Configuring Secure Sockets Layer Authentication

URL: http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asossl.htm#i1022705 

After creating wallets you can use establish connection using wallet authorization through utl_smtp.open_connection.

2. Oracle connection can be established using wallet authorization:  Samples

Example 1:

DECLARE
  c utl_smtp.connection;
BEGIN
  c := utl_smtp.open_connection(
     host => 'smtp.example.com',
     port => 25,
     wallet_path => 'file:/oracle/wallets/smtp_wallet',
     wallet_password => 'password',
     secure_connection_before_smtp => FALSE);
  utl_smtp.starttls(c);
END;

Example 2:

DECLARE
  c utl_smtp.connection;
BEGIN
  c := utl_smtp.open_connection(
      host => 'smtp.example.com',
      port => 25,
      wallet_path => 'file:/oracle/wallets/smtp_wallet',
      wallet_password => 'password',
      secure_connection_before_smtp => FALSE);
  UTL_SMTP.STARTTLS(c);
  UTL_SMTP.AUTH(
      c => c,
      username => 'scott',
      password => 'password'
      schemes  => utl_smtp.all_schemes);
END;

Reference / Read More:

Oracle Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
E40758-03

Chapter 236 UTL_SMTP
URL: http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/u_smtp.htm

3. Troubleshooting Connections: 

You should be able to perform: 

%> ping -a <hostname>

Example:

%> ping -a www.google.com

If that results in a successful ping, then UTL_HTTP will work the connecting from Oracle Database Server:

    

Example: 

SQL> SELECT utl_http.request('http://www.google.com') FROM dual;

And if that works; 

You would then need to check on the Wallet connection: 

Example:

[sunsys]/etc/ORACLE/WALLETS/oracle> ls -al

drwxr-xr-x   2 oracle   dba          512 Jan 28 11:33 ./

drwxr-xr-x  10 oracle   dba          512 Jan 30 08:39 ../

-r--------   1 oracle   dba         8581 Jan 17 11:31 ewallet.p12

With the wallet configured, access to the external web site can be tested using the following SQL.

SELECT utl_http.request('<url or secure url>', '<proxy server:port>', 'file:<wallet reference>', '<wallet password>') FROM DUAL; 

For example:

SELECT utl_http.request('http://www.google.com','proxy.mine.yours.com:80',

                        'file:/etc/ORACLE/WALLETS/oracle','welcome1') FROM DUAL;

Oracle SQLNET.ORA and TNSNAMES.ORA Settings to Make Wallet Work

You would need to open up your Database connection settings to allow Wallet connection.  These settings are done through Oracle SQLNET.ORA and TNSNAMES.ORA configuration file.

Applies to Oracle Wallet Manager, Wallet, Security, Weblogic, Oracle Database

Sample:

1. sqlnet.ora:

# sqlnet.ora Network Configuration File: /mysupport/etc/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/mysupport/home/users/shiva/mydir)))

2. tnsnames.ora

#TNSNAMES to point to the wallet name - ORACLE2

ORACLE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTO = TCP)(HOST = myhost)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORACLE)
    )
  )

Create a wallet for external identification:

Step 1:

Syntax: mkstore -wrl <wallet_location> -createCredential <tns alias> <user_name> <password>

Example:

mkstore -wrl /opt/oracle/mydir/wallet -createCredential ORACLE2 shivak password

[myos]/mysupport/home/users/shiva/mydir> mkstore -wrl /mysupport/home/users/shiva/mydir/test -create
Enter password: wallettest
Enter password again: wallettest

======================================

Step 2:

Syntax: mkstore -wrl <directory where the wallet needs to be created> -createCredential <tns_alias> <existing_user> <existing_passwd>

[myos]/mysupport/home/users/shiva/mydir>  mkstore -wrl /mysupport/home/users/shiva/mydir/test -createCredential SHIVAV1023W shivak dbpw

Enter password:  wallettest
Create credential oracle.security.client.connect_string1

======================================

Step 3: Check if WALLET is created

[myos]/mysupport/home/users/shiva/mydir> ls -l
total 36
-rw-------  1 mysupport udba  8316  Sep 30 13:27  cwallet.sso
-rw-------  1 mysupport udba  8288  Sep 30 13:27  ewallet.p12
-rw-r--r--  1 mysupport udba   470  Sep 30 21:03  listener.ora
-rw-r--r--  1 mysupport udba   318  Sep 30 13:16  sqlnet.ora
-rw-r--r--  1 mysupport udba   196  Sep 30 20:57  tnsnames.ora

======================================

Step 4: Connection from SQLPLUS to test WALLET

[myos]/mysupport/home/users/shiva/mydir> sqlplus /@SHIVAV1023W

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 13:29:38 2013

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
...

Reference / Read More: 

1. Oracle Database Security Guide
10g Release 2 (10.2)
B14266-09

Chapter 9 Secure External Password Store
URL: http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm

2. Oracle Fusion Middleware Administrator's Guide
11g Release 1 (11.1.1)
Part Number E10105-13

Chapter 8 Managing Keystores, Wallets, and Certificates
URL: http://docs.oracle.com/cd/E23943_01/core.1111/e10105/wallets.htm#CIHIHGJG

3. Oracle Database Advanced Security Administrator's Guide
11g Release 2 (11.2)
E40393-02

Section F orapki Utility
URL: http://docs.oracle.com/cd/E11882_01/network.112/e10746/asoappf.htm

Oracle Wallet - Creation and Usage Example

Applies to Oracle Database, Web Logic, Wallet, Oracle Wallet Manager.

Understanding is that you already have Oracle Wallet Manager installed, this is part of full Oracle Database installation.

1. To create wallet: 

1.1. Enter a new password as part of wallet creation.

[youros]/testcases/mydir> mkstore -wrl /testcases/mydir/mytest -create

Enter password:  mytest123
Enter password again:  mytest123

2. Wallet Created Directory:

Upon created of wallet you will find files like cwallet.sso, ewallet.p12 in your directory where the wallets were created:

[youros]/testcases/mydir> cd mytest
[youros]/testcases/mydir/mytest> ls
cwallet.sso   ewallet.p12

3. To create wallet credential: 

Syntax: mkstore -wrl <wallet_location> -createCredential <alias> <user_name> <password>

Enter wallet password: <password that was previously used for wallet creation>

[youros]/testcases/mydir/mytest> mkstore -wrl /testcases/mydir/mytest/ -createCredential SHIVAV1024U.yours.mine.com scott tiger

Enter wallet password: mytest123
Create credential oracle.security.client.connect_string1

Now you have both wallet and wallet credential ready.

4. To Test Wallet: 

You can use Oracle server side packages like utl_http to request external site.  You may need to use proxy servers if you are behind proxy.

Case 1: To make utl_http.request call using Proxy Server

Syntax: 

SELECT utl_http.request('<url>', '<proxy server:port>') FROM DUAL; 

Example: 

SQL> SELECT utl_http.request('http://www.formsite.com', 'www-proxy.yours.mine.com') from dual;

UTL_HTTP.REQUEST('HTTP://WWW.FORMSITE.COM','WWW-PROXY.YOURS.MINE.COM')
--------------------------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/x
html1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><!-- Instance
Begin template="/Templates/Full.dwt" codeOutsideHTMLIsLocked="false" -->

<head>
        <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
...
<removed rest of the contents>

Case 2: To make utl_http.request call using Proxy Server with Wallet

Syntax: 

SELECT utl_http.request('<url>', '<proxy server:port>', 'file:<wallet directory reference>', '<wallet password>') FROM DUAL; 

Example: 

SELECT utl_http.request('http://www.formsite.com', 'www-proxy.yours.mine.com','file:/testcases/mydir/mytest/','mytest123') FROM DUAL; 

UTL_HTTP.REQUEST('HTTP://WWW.FORMSITE.COM','WWW-PROXY.YOURS.MINE.COM','FILE:/TEST
--------------------------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/x
html1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><!-- Instance
Begin template="/Templates/Full.dwt" codeOutsideHTMLIsLocked="false" -->
...
<removed rest of the contents>


If the call fails, you might get errors like: 

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29024: Certificate validation failure
ORA-06512: at line 1

Reference / Read More

1. Oracle Fusion Middleware Administrator's Guide
11g Release 1 (11.1.1)
Part Number E10105-13

Chapter 8 Managing Keystores, Wallets, and Certificates
URL: http://docs.oracle.com/cd/E23943_01/core.1111/e10105/wallets.htm#CIHIHGJG

2. Oracle Database Advanced Security Administrator's Guide
11g Release 2 (11.2)
E40393-02

Section F orapki Utility
URL: http://docs.oracle.com/cd/E11882_01/network.112/e10746/asoappf.htm