Oracle Database 12c - Performance Enhancement - Scalable Log Writer

One of the Performance Enhancement in Oracle Database 12c is Scalable Log Writer.

Log writer (LGWR) process and Log Writer Groups (LGnn) Background process are part of the Scalable Log Writer.

Redo Life Cycle 

Life cycle of Redo logs involves processes like Foreground, Redo Buffer, Log writer (LGWR), Redo Log File, System Monitor (SMON) for Instance Recovery and Data Files. 

Redo Generation has multiple buffer strands and each foreground process writes to the respective redo buffer strands. 

Foreground process waits on Log writer (LGWR) to complete a write on large system with multiple CPU and foreground processes for commits. 

New LGWR Behavior

LGWR Process

Newer architecture of Log writer (LGWR) improves by reducing the waits when writing to large or multiple CPU systems.  This is done with the help of concurrent helper process and optimized log file writes. 

However some of the restrictions of Parallel Log writer (LGWR) with standby database are Synchronous redo transport is not supported.  Serial LGWR is used instead.  Asynchronous redo transport is supported. 

Reference / Additional Reading

1. Oracle Documentation

http://docs.oracle.com/cd/B28359_01/server.111/b28318/process.htm

Oracle Database Concepts

11g Release 1 (11.1)

B28318-06

Chapter 9 Process Architecture

2. Oracle Documentation

http://docs.oracle.com/cd/E11882_01/server.112/e41573.pdf

Oracle Database Performance Tuning Guide 11g Release 2 (11.2) E41573-03 

3. Oracle multiple log writer (LGWR) processes

http://www.dba-oracle.com/t_multiple%20log_writer_lgwr_processes.htm

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;