How To Find Free Space in DBFS (Oracle Database File System)

To find free space in DBFS (Oracle Database File System) use Oracle packages like dbms_space.space_usage.

Sample Script: 

SQL> set serveroutput on

SQL> declare
 v_segment_size_blocks number;
 v_segment_size_bytes number;
 v_ number;
 v_used_blocks number;
 v_used_bytes number;
 v_expired_blocks number;
 v_expired_bytes number;
 v_unexpired_blocks number;
 v_unexpired_bytes number;
 begin
 dbms_space.space_usage ('DBFS_USER', 'LOB_SFS$_FST_1', 'LOB',
 v_segment_size_blocks, v_segment_size_bytes,
 v_used_blocks, v_used_bytes, v_expired_blocks, v_expired_bytes,
 v_unexpired_blocks, v_unexpired_bytes );
 dbms_output.put_line('Expired Blocks = '||v_expired_blocks);
 dbms_output.put_line('Expired Bytes = '||v_expired_bytes);
 dbms_output.put_line('UnExpired Blocks = '||v_unexpired_blocks);
 dbms_output.put_line('UnExpired Bytes = '||v_unexpired_bytes);
 end;
/

Sample Out: 

Expired Blocks = 42865646
Expired Bytes = 351155372032
UnExpired Blocks = 26808
UnExpired Bytes = 219611136

PL/SQL procedure successfully completed.

Expired Bytes = 351155372032 + UnExpired Bytes = 219611136) is considered as the free space for DBFSTS (Oracle Database File System Tablespace).

Reference / Additional Read

1. Oracle® Database SecureFiles and Large Objects Developer's Guide
12c Release 1 (12.1)
E17605-10

Chapter 10 Using DBFS

URL: http://docs.oracle.com/cd/E16655_01/appdev.121/e17605/adlob_client.htm

2. Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 2 (11.2)
E18294-04

Chapter  6 DBFS File System Client

URL: http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_client.htm#ADLOB0006

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;

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

Auditing - Oracle 12c Security Feature - What is New?

Applies to Software Auditing, Database Auditing, Database Application Auditing, Oracle 12c.

Key features of Oracle 12c Security are Auditing, Privileges, Data Redaction, Database Vault.

We will discuss about Auditing in more detail.   Privileges, Data Redaction, and Database Vault will be discussed as separate blogs.

1. Auditing Overview 

Let's see what features are available in existing Oracle releases up to Oracle 11g Release 2.

There are different types of Auditing that you can perform at Oracle Database level.  In general, auditing is performed to keep safe Database and to have better user control.  It can also be used monitor any activities at Database level.  For example: To monitor suspicious activity. At Database parameter level, we can use AUDIT_TRAIL, AUDIT_SYS_OPERATIONS and use package DBMS_FGA.ADD_POLICY for fine grained auditing.

2. Audit Trail Implementation Packages 

Where Audits are Stored?

How Audit Data are Secured?

Audits are secured within the Oracle database at syslog audit: AUDIT_SYSLOG_LEVEL= local1.info
and by using Oracle Audit Vault.

3. Basic Audit Information (BAI) vs Extended Audit Information (EAI) 

4. Extended Audit Information (EAI) 

EAI is from SYS object view (SYS.UNIFIED_AUDIT_TRAIL) that has columns like FGA_POLICY_NAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1, RMAN_OPERATION, RMAN_OBJECT_TYPE, RMAN_DEVICE_TYPE, RMAN_xxx, OLS_POLICY_NAME, OLS_xxx, DV_xxx

5. Fine Grained Audit (FGA) Policy 

FGA policy is added by using package DBMS_FGA.ADD_POLICY and any changes are recorded in UNIFIED_AUDIT_TRAIL which can be queried. 

6. Data Pump Audit Policy

Audit policy can be created at Data pump using component datapump.

7. Sample UNIFIED_AUDIT_TRAIL Query 

8. RMAN Audit Information 

Audit policy can be created at RMAN level by flushing the audit data using object SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL.

9. Database Vault Audit Policy 

Audit policy can be created at Database Vault using component dv.  Any configuration changes at Database Vault can be audited.

10. OLS or RAS Audit Policy

Oracle Label Security (OLS)
Real Application Security (RAS)

Audit policy can be using component OLS.

11. Unified Audit Implementation (UAI) 

Unified Audit Implementation can be achieved using UNIFIED_AUDIT_TRAIL and uniaud_on binary. 

12. Security Challenges - Roles

Packages like DBMS_AUDIT_MGMT can assist with AUDIT_ADMIN (Audit Administration) challenges.  AUDIT_VIEWER can get UNIFIED_AUDIT_TRAIL views.  AUDSYS is a dedicated schema at Database level.

13. Security Challenge - SYS Auditing 

Non Unified Audit gets recorded in OS audit directory and Unified Audit can be performed at SYS.UNIFIED_AUDIT_TRAIL.

14. Simplicity Challenges - Audit 

No longer need parameters like AUDIT_xxx.  Audit policies can simply be created using CREATE AUDIT POLICY command and viewed at UNIFIED_AUDIT_TRAIL.

15. Steps to Create AUDIT Policy (STEP 1)

Using 'Create Audit Policy' command, create system privileges, actions, roles individually or all in one command.

16. Creating Object Specific Actions

Using 'Create Audit Policy' command, you can also create object-specific actions such as LOCK on a specific table or executing a grant on a specific object (For example: PL/SQL Procedure or Package).

17. Creating Audit Policy - Condition and Evaluation

Using 'Create Audit Policy' command, you can also add Conditions and Evaluations per session, statement and instance basis.  Actions e.g., RENAME, ALTER on a particular Table.

18. Steps to Create AUDIT Policy (STEP 2)

To enable or to disable the audit policy.  AUDIT POLICY Command can be used to enable or disable the audit policies.

19. How to view Audit Policy?

Use views like AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES.

20. Predefined Audit Policies

Policies like ORA_ACCOUNT_MGMT, ORA_DATABASE_PARAMETER, ORA_SECURECONFIG can be used to check on different audit options.

Auditing Requirements

Commonly used auditing requirements are done for compliance and certifications reasons.  Regulatory bodies with common auditing requirements:

Sarbanes-Oxley Act (SOX)

Health Insurance Portability and Accountability Act (HIPAA)

Financial Services Agency (FSA)

Payment Card Industry (PCI)

Data Security Standard (DSS)

Personally Identifiable Information (PII)

International Convergence of Capital Measurement and Capital Standards: a Revised Framework (Basel II) (ICCM) (BCBS)

Bank for International Settlements (BIS)

Japan Privacy Law / Japanese Hitoshi

Japanese Requirement Sarbanes-Oxley Act Section 302 (JSOX) Management Certification

European Union Directive on Privacy and Electronic Communications  EU (DPEC)

Reference / Read More 

1. Oracle Database 2 Day + Security Guide
12c Release 1 (12.1)
E17609-15

Chapter 9 Auditing Database Activity

http://docs.oracle.com/cd/E16655_01/server.121/e17609/tdpsg_auditing.htm

2. Oracle Database Security Guide
12c Release 1 (12.1)
E17607-19

Chapter 21 Introduction to Auditing
and
Chapter 22 Configuring Audit Policies

http://docs.oracle.com/cd/E16655_01/network.121/e17607/auditing.htm

3. Security Inside Out, Plug into Defense-in-Depth with Oracle Database 12c
https://blogs.oracle.com/securityinsideout/entry/plug_into_defense_in_depth
https://blogs.oracle.com/securityinsideout/

RMAN - Configuration Check and Tests

Applies to Oracle Database (9i ~ 12c) - RMAN (Recovery Manager).

To check RMAN Configuration:

1. Current RMAN Configuration :

RMAN> show all;

2. To Test RMAN:

Test 1 - Backup to disk:

$ rman target  /   log=rman_disk.log

RMAN> set echo on;
RMAN> show all;
RMAN> run
{
allocate channel d1 device type  disk;
backup datafile 1 ;
}

Look for rman_disk.log file for the results.

Test 2 - Backup to tape:

$ rman target  /   log=rman_tape.log

RMAN> set echo on;
RMAN> show all;
RMAN> backup datafile 1 ;

Look for rman_tape.log file for the results.

3. Execute the following RMAN Command to make sure RMAN checks what physical files exist or not :

$ rman target  / log=crosscheck_out.log

RMAN> set echo on;
RMAN> crosscheck backup ;

4. If you are using rman script for backup, try running that script with log option.

$ rman target  / log=crosscheck_out.log

RMAN> set echo on;
RMAN> <execute your RMAN Script>

Look for crosscheck_out.log file for the results.


Reference / Read More:

1. Oracle Database Backup and Recovery Reference
11g Release 2 (11.2)
E10643-07

http://docs.oracle.com/cd/E11882_01/backup.112/e10643/toc.htm

2. Oracle Database Backup and Recovery User's Guide
11g Release 2 (11.2)
E10642-06

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/toc.htm

3. Oracle Database Recovery Manager Reference
10g Release 1 (10.1)
Part Number B10770-01

Section: ALLOCATE CHANNEL

http://docs.oracle.com/cd/B12037_01/server.101/b10770/rcmsynta4.htm

XML File Transformation - Multiplication Sample

Applies to Oracle 9i Release 2 or later, SOA.

Sample code to demonstrate xml transformation using two string multiplication.

Within a SOA composite, xml transformation using two string multiplication.
The strings contain numeric values, so there is no exception.

1. Create the input file named: input.xml

<?xml version="1.0" encoding="UTF-8" ?>
<singleString xmlns="http://xmlns.oracle.com/singleString">
  <name>35</name>
  <surname>2</surname>
</singleString>

2. Create the .xsl file transformation.xsl: 

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
               xmlns:inp1="http://xmlns.oracle.com/singleString">
 <xsl:template match="/">
   <xsl:variable name="gfhh"
                 select="/inp1:singleString/inp1:name * /inp1:singleString/inp1:surname"/>
   <inp1:singleString>
     <inp1:surname>
       <xsl:value-of select="$gfhh"/>
     </inp1:surname>
   </inp1:singleString>
 </xsl:template>
</xsl:stylesheet>

3.  Make sure you have your jdk bin directory in your path.  

For example;  /refresh/home/Oracle/jdk1.6.0_30/bin directory (Java Compiler)

4. Execute the following command:

java -cp ./xmlparserv2.jar oracle.xml.parser.v2.oraxsl -v input.xml transformation.xsl

Please note xmlparserv2.jar is a .jar file which is part of Oracle XDB/XML Parser library ($ORACLE_HOME/lib/xmlparserv2.jar).

5. Execution of above command will produce xml output of:

<?xml version = '1.0' encoding = 'UTF-8'?>
<inp1:singleString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:inp1="http://xmlns.oracle.com/singleString"><inp1:surname>70</inp1:surname></inp1:singleString>

This demonstrates the transformation, and the use of the variable types within the SOA composite.

SOA relies on the transformation code within the xmlparserv2.jar file.

The expected output is 70, if the input is not a whole number the output will be rounded to 2 digits.

Reference:

XSLT Transformations - Peachpit Press

http://www.peachpit.com/articles/article.aspx?p=21845&seqNum=3

How to Handle Java Serialized Object in Oracle?

Applies to Java, JEE Environments.

Take the serialized object, which is a byte array, convert it to a string and write to a file instead of storing it in Oracle.

Error Cases:

1. If you try to store it in a varchar2 column in Oracle Database you might get error when retrieving the object and deserialize it.  Error could state that 'serialVersionUID had changed'.

2. If you store the serialized object (a byte array) into a blob column.  And when trying to retrieve the blob to deserialize the object, you might get an error saying that the object had been corrupted.  That is because of the array size difference.  i.e., stored was 102 bytes and the one retrieved was 203 bytes.

Reference / Read More:

java.io
Interface Serializable
http://docs.oracle.com/javase/7/docs/api/java/io/Serializable.html

Serializable Objects
http://docs.oracle.com/javase/jndi/tutorial/objects/storing/serial.html

Java Object Serialization
http://docs.oracle.com/javase/7/docs/technotes/guides/serialization/

http://docs.oracle.com/javase/7/docs/platform/serialization/spec/serialTOC.html

http://docs.oracle.com/javase/7/docs/platform/serialization/spec/serial-arch.html

SQL Tuning - Oracle 12c - What is New?

Applies to Oracle 12c.

SQL Tuning has three major modules in Oracle 12c.

- Adaptive SQL Plan Management

- Adaptive Execution Plans

- Optimizer Statistics Management

1. Adaptive SQL Plan Management

Summary of SQL Plan Management (SPM):

SQL Plan Baseline:

SQL Management Base Enhancements:

2. Adaptive Execution Plans

Adaptive Execution Plans:

2.1 Dynamic Adaptive Plan:

Using Dynamic Plans:

Dynamic Plan - Adaptive Process:

Dynamic Adaptive Plan Example:

2.2 Reoptimization Adaptive Plan:

3. Optimizer Statistics Management

3.1 SQL Plan Directives

Creating SQL Plan Directives

Using SQL Plan Directives:

SQL Plan Directives - Example:

3.2 Statistics Gathering Performance Improvements

3.2.1 Online Statistics Gathering:

3.2.2 Concurrent Statistics Gathering:

Concurrent Statistics Gathering - Creating Jobs at Different Levels:

3.2.3 Incremental Statistical Gathering Improvements:

Incremental Global Statistics - Workflow:

3.2.4 Session Private Statistics for Global Temporary Tables: 

3.3 Histogram Enhancements: 

3.3.1 Top Frequency Histograms: 

3.3.2 Hybrid Histograms: 

Hybrid Histogram Example: 

3.4 Enhancements to Extended Statistics: 

3.4.1 Column Group: 

Example:

3.5 Dynamic Sampling Enhancements: 

Automatic Dynamic Sampling - optimizer_dynamic_sampling:

Summary of SQL Tuning in Oracle 12c: 

•Adaptive SQL Plan Management

•Enhancements to the SQL management base

•How to use Dynamic plan to improve query performance

•Reoptimization for adaptive execution plans

•How to use SQL plan directives to generate a better plan

•Statistics Gathering performance improvements

•How to use new histograms

•Enhancements to extended statistics and how to detect useful column groups for a specific workload

•Enhancements to dynamic sampling

Reference / Read More: 

Oracle Database 11g: The Top Features for DBAs and Developers
By Arup Nanda
Adaptive Cursors and SQL Plan Management
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html

SQL Plan Management with Oracle Database 12c
Oracle White Paper, June 2013
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features
By Maria Colgan on Feb 02, 2009
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_4_of_4_user_interfaces_and_other_features

How do adaptive cursor sharing and SQL Plan Management interact?
By Allison on Feb 11, 2013
https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing