Oracle 12c Redaction Policy

     Oracle Data Redaction is one of the new features introduced in Oracle Database 12c. This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real time, without requiring changes to the application.

     Oracle Database 12c applies protection at query execution time. The stored data remain unchanged, while the data to be displayed is transformed on-the-fly before leaving the database.

     This feature is not to be confused with Oracle Data Masking which has been available since version 11g. With Oracle Data Masking, the data is processed using masked shapes and this updated data is stored in new data blocks. For this reason, Data Masking is more suitable for non-production environments.

Below are some other features that already existed to help making the data more secure:

Virtual Private Database (VPD) - Allows control access on both row and column levels by dynamically adding a predicate to SQL statements issued against the database.

Oracle Label Security – Allows you to add user-defined values to table records combining it with VPD to allow fine control of who sees what.

Database Vault – Data Redaction does not prevent privileged users (such as DBAs) from having access to the data being protected. To solve this, you can make use of Database Vault.

Licensing wise, Oracle Data Masking is available only with Enterprise Edition database and it requires licensing of Advanced Security.

How It Works

We can create redaction policies which specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.

The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for full redaction policy.

DBMS_REDACT.ALTER_POLICY – allows changes to existing policies.

DBMS_REDACT.DISABLE_POLICY – disables an existing policy.

DBMS_REDACT.DROP_POLICY – drop an existing policy.

DBMS_REDACT.ENABLE_POLICY – enables an existing policy.

DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES – change the default return value for full redaction. You must restart the database to take effect.



You can protect data at the column level using one of the following methods:

Full redaction – All content of the column is protected and the type of value returned depends on the data type of the column. For numeric columns, the value zero will be returned. For columns of type character, a space will be returned. This setting can be changed at the database level.

Partial redaction – Only part of the information is changed. For example, the first digits of the credit card number are replaced by asterisks.

Regular expressions - You can use regular expressions to search for patterns of data that must be protected.

Random redaction – Returned values ??are random; each time a query is executed, the displayed data will be different.

No redaction - Allows testing the inner workings of redaction policies, with no effect on the results of current running queries. This is widely used during testing phase of redaction policies that will eventually find their way to production environments.

Oracle 12c Pluggable Databases

     Oracle is doing every thing to jump into the cloud bandwagon. With 12C, Oracle is trying to address the problem of Multitenancy through this feature. There is a radical change and a major change in the core database architecture through the introduction of Container Databases also called CBD and Pluggable Databases (PDB).

     The memory and process is owned by the Container Database. The container holds the metadata where the PDBs hold the user data.

     You can create upto 253 PDBs including the seed PDB. In a large setup, it is common to see 20 or 30 different instances running in production environment. With these many instances, it is a maintenance nightmare as all these instances have to be separately

•Upgraded

•Patched

•Monitored

•Tuned

•RAC Enabled

•Adjusted

•Backed up and

•Data Guarded

     With Pluggable Databases featureparate public synonyms. Additionally, 2 PDBs can talk to each other through the regular DB Link feature.

     There is no high startup cost of creating a database any more. Instead of one instance per day, the shift is into one instance per many databases. For the developer community, you can be oblivious of all this and still continue to use the PDBs as if it were a traditional database, but for the DBAs the world would look like it has changed a lot.

e, you just have to do all this for ONE single instance. Without this feature, prior to 12C, you would have to create separate schemas and there is always a thread of security how much ever the isolation we build into it. There are problems with namespace conflicts, there is always going to be one public synonym that you can create. With PDBs you can have a separate HR or Scott schema for each PDB, separate Emp, Dept Tables and s

About Containers in a CDB

A container is either a PDB or the root. The root container is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.

Every CDB has the following containers:

Exactly one root

The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. The root container is named CDB$ROOT.

Exactly one seed PDB

The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

Zero or more user-created PDBs

A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.

The following figure shows a CDB with four containers: the root, seed, and two PDBs. Each PDB has its own dedicated application. A different PDB administrator manages each PDB. A common user exists across a CDB with a single identity. In this example, common user SYS can manage the root and every PDB. At the physical level, this CDB has a database instance and database files, just as a non-CDB does.

Benefits of the Multitenant Architecture for Database Consolidation

Database consolidation is the process of consolidating data from multiple databases into one database on one computer. Starting in Oracle Database 12c, the Oracle Multitenant option enables you to consolidate data and code without altering existing schemas or applications.

The PDB/non-CDB compatibility guarantee means that a PDB behaves the same as a non-CDB as seen from a client connecting with Oracle Net. The installation scheme for an application back end that runs against a non-CDB runs the same against a PDB and produces the same result. Also, the run-time behavior of client code that connects to the PDB containing the application back end is identical to the behavior of client code that connected to the non-CDB containing this back end.

Operations that act on an entire non-CDB act in the same way on an entire CDB, for example, when using Oracle Data Guard and database backup and recovery. Thus, the users, administrators, and developers of a non-CDB have substantially the same experience after the database has been consolidated.

Cost reduction

By consolidating hardware and sharing database memory and files, you reduce costs for hardware, storage, availability, and labor. For example, 100 PDBs on a single server share one database instance and one set of database files, thereby requiring less hardware and fewer personnel.

Easier and more rapid movement of data and code

By design, you can quickly plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. The implementation technique for plugging and unplugging is similar to the transportable tablespace technique.

Easier management and monitoring of the physical database

The CDB administrator can attend to one physical database (one set of files and one set of database instances) rather than split attention among dozens or hundreds of non-CDBs. Backup strategies and disaster recovery are simplified.

Separation of data and code

Although consolidated into a single physical database, PDBs mimic the behavior of non-CDBs. For example, if user error loses critical data, a PDB administrator can use Oracle Flashback or point-in-time recovery to retrieve the lost data without affecting other PDBs.

Secure separation of administrative duties

A user account is common, which means that it can connect to any container on which it has privileges, or local, which means that it is restricted to a specific PDB. A CDB administrator can use a common user account to manage the CDB. A PDB administrator uses a local account to manage an individual PDB. Because a privilege is contained within the container in which it is granted, a local user on one PDB does not have privileges on other PDBs within the same CDB.

Ease of performance tuning

It is easier to collect performance metrics for a single database than for multiple databases. It is easier to size one SGA than 100 SGAs.

Support for Oracle Database Resource Manager

In a multitenant environment, one concern is contention for system resources among the PDBs running on the same computer. Another concern is limiting resource usage for more consistent, predictable performance. To address such resource contention, usage, and monitoring issues, you can use Oracle Database Resource Manager (see "Database Resource Manager").

Fewer database patches and upgrades

It is easier to apply a patch to one database than to 100 databases, and to upgrade one database than to upgrade 100 databases.

ORA-01000: maximum open cursors exceeded

     You get this error when a user of a host program attempts to open more cursors than they are allowed. 

     The number of cursors allowed is dictated by the OPEN_CURSORS initialization parameter, and this quota can be eaten up by both implicit and explicit cursors. If you run into this error, there is a possibility that there is a bug in your application. Perhaps you’ve got an open cursor statement within a loop and you do not have a matching close cursor, and as a result your code is bleeding cursors all over the place.

     However, it is possible that the OPEN_CURSORS number is just too low for the needs of your application and has to be upped. The default value is 50; however, the only factor limiting how high this number can go – 300, 1000, 2000 even – is what the operating system can take. However, it may be unwise to choose to change the OPEN_CURSORS parameter too steeply, rather than examine your code for leaks. 

     ORA-01000, the maximum-open-cursors error, is an extremely common error in Oracle database development. In the context of Java, it happens when the application attempts to open more ResultSets than there are configured cursors on a database instance.

Common causes are:

Configuration mistake

You have more threads in your application querying the database than cursors on the DB. One case is where you have a connection and thread pool larger than the number of cursors on the database.

You have many developers or applications connected to the same DB instance (which will probably include many schemas) and together you are using too many connections.

Solution:

Increasing the number of cursors on the database (if resources allow) or
Decreasing the number of threads in the application.

Cursor leak

The applications is not closing ResultSets (in JDBC) or cursors (in stored procedures on the database)

Solution:

Cursor leaks are bugs; increasing the number of cursors on the DB simply delays the inevitable failure. Leaks can be found using static code analysis, JDBC or application-level logging, and database monitoring.
Background

This section describes some of the theory behind cursors and how JDBC should be used. If you don't need to know the background, you can skip this and go straight to 'Eliminating Leaks'.

What is a cursor?

A cursor is a resource on the database that holds the state of a query, specifically the position where a reader is in a ResultSet. Each SELECT statement has a cursor, and PL/SQL stored procedures can open and use as many cursors as they require. You can find out more about cursors on Orafaq.

A database instance typically serves several different schemas, many different users each with multiple sessions. To do this, it has a fixed number of cursors available for all schemas, users and sessions. When all cursors are open (in use) and request comes in that requires a new cursor, the request fails with an ORA-010000 error.

Finding and setting the number of cursors

The number is normally configured by the DBA on installation. The number of cursors currently in use, the maximum number and the configuration can be accessed in the Administrator functions in Oracle SQL Developer. From SQL it can be set with:

ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;
Relating JDBC in the JVM to cursors on the DB

The JDBC objects below are tightly coupled to the following database concepts:

JDBC Connection is the client representation of a database session and provides database transactions. A connection can have only a single transaction open at any one time (but transactions can be nested)
A JDBC ResultSet is supported by a single cursor on the database. When close() is called on the ResultSet, the cursor is released.
A JDBC CallableStatement invokes a stored procedure on the database, often written in PL/SQL. The stored procedure can create zero or more cursors, and can return a cursor as a JDBC ResultSet.
JDBC is thread safe: It is quite OK to pass the various JDBC objects between threads.

For example, you can create the connection in one thread; another thread can use this connection to create a PreparedStatement and a third thread can process the result set. The single major restriction is that you cannot have more than one ResultSet open on a single PreparedStatement at any time. See Does Oracle DB support multiple (parallel) operations per connection?

Note that a database commit occurs on a Connection, and so all DML (INSERT, UPDATE and DELETE's) on that connection will commit together. Therefore, if you want to support multiple transactions at the same time, you must have at least one Connection for each concurrent Transaction.

Closing JDBC objects

A typical example of executing a ResultSet is:

Statement stmt = conn.createStatement();
try {
    ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" );
    try {
        while ( rs.next() ) {
            System.out.println( "Name: " + rs.getString("FULL_NAME") );
        }
    } finally {
        try { rs.close(); } catch (Exception ignore) { }
    }
} finally {
    try { stmt.close(); } catch (Exception ignore) { }
}


*Note how the finally clause ignores any exception raised by the close():

If you simply close the ResultSet without the try {} catch {}, it might fail and prevent the Statement being closed
We want to allow any exception raised in the body of the try to propagate to the caller. If you have a loop over, for example, creating and executing Statements, remember to close each Statement within the loop.
In Java 7, Oracle has introduced the AutoCloseable interface which replaces most of the Java 6 boilerplate with some nice syntactic sugar.

Holding JDBC objects

JDBC objects can be safely held in local variables, object instance and class members. It is generally better practice to:

Use object instance or class members to hold JDBC objects that are reused multiple times over a longer period, such as Connections and PreparedStatements
Use local variables for ResultSets since these are obtained, looped over and then closed typically within the scope of a single function.
There is, however, one exception: If you are using EJBs, or a Servlet/JSP container, you have to follow a strict threading model:

Only the Application Server creates threads (with which it handles incoming requests)
Only the Application Server creates connections (which you obtain from the connection pool)
When saving values (state) between calls, you have to be very careful. Never store values in your own caches or static members - this is not safe across clusters and other weird conditions, and the Application Server may do terrible things to your data. Instead use stateful beans or a database.
In particular, never hold JDBC objects (Connections, ResultSets, PreparedStatements, etc) over different remote invocations - let the Application Server manage this. The Application Server not only provides a connection pool, it also caches your PreparedStatements.
Eliminating leaks

There are a number of processes and tools available for helping detect and eliminating JDBC leaks:

During development - catching bugs early is by far the best approach:

Development practices: 

Good development practices should reduce the number of bugs in your software before it leaves the developer's desk. Specific practices include:
Pair programming, to educate those without sufficient experience
Code reviews because many eyes are better than one
Unit testing which means you can exercise any and all of your code base from a test tool which makes reproducing leaks trivial
Use existing libraries for connection pooling rather than building your own
Static Code Analysis: Use a tool like the excellent Findbugs to perform a static code analysis. This picks up many places where the close() has not been correctly handled. Findbugs has a plugin for Eclipse, but it also runs standalone for one-offs, has integrations into Jenkins CI and other build tools

At runtime:

Holdability and commit

If the ResultSet holdability is ResultSet.CLOSE_CURSORS_OVER_COMMIT, then the ResultSet is closed when the Connection.commit() method is called. This can be set using Connection.setHoldability() or by using the overloaded Connection.createStatement() method.

Logging at runtime

Put good log statements in your code. These should be clear and understandable so the customer, support staff and teammates can understand without training. They should be terse and include printing the state/internal values of key variables and attributes so that you can trace processing logic. Good logging is fundamental to debugging applications, especially those that have been deployed.
You can add a debugging JDBC driver to your project (for debugging - don't actually deploy it). One example (I have not used it) is log4jdbc. You then need to do some simple analysis on this file to see which executes don't have a corresponding close. Counting the open and closes should highlight if there is a potential problem
Monitoring the database. Monitor your running application using the tools such as the SQL Developer 'Monitor SQL' function or Quest's TOAD. Monitoring is described in this article. During monitoring, you query the open cursors (eg from table v$sesstat) and review their SQL. If the number of cursors is increasing, and (most importantly) becoming dominated by one identical SQL statement, you know you have a leak with that SQL. Search your code and review.

ORA-03113: end-of-file on communication channel

     This error means that your connection has died. For some reason, your client machine and the database server have stopped talking to each other. 

     That reason could be one of a rather long list: has your server crashed? has someone pulled out your network cable? was your process killed at the O/S level? is your Windows log full? or maybe there is an Oracle internal error?

     My advice is this: do not overlook the obvious. This error is sometimes caused by the simplest of things. If, however, it is caused by an Oracle internal error, look to your alert log for further information.
     
ORA-03113: end-of-file on communication channel

Cause: The connection between Client and Server process was broken. It may also happen if the external agent extproc crashes for some reason.

Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time. There may be some system calls in the .NET function which might terminate the process. Remove such calls.

ORA-00600: internal error code , and fixing

     One of the nightmares of Oracle DBA's is the dreaded ORA-00600 error code.

Whenever you see one of this pops up , it is bad news. It is an internal error code which implies that something grave happened , and oracle writed an entry to the trace file.
Your alert.log contains the path to that trace file. Take the information you get from your trace file and the first argument in the square brackets (the internal message number). This error is a bit different than the other simple ORA error codes with an explanation itself on the error. 

Now you have 2 options in your hands:

1. If you have an oracle support agreement, you can open a service request to oracle, then hand them over what they want for investigation. Generally they send you a tool which packes up the alert log , trace file , and some information regarding the db you are working on and simply pack it for you to send them.
2. Second , you can try to resolve it by yourself. But you can say how, i dont have anything regarding the error except the internal message number. Now this is where Metalink try to help you. You can head over to My Oracle Support (Metalink) and search for the Error look-up Tool, which will help you identify the reasons behind your dreaded error.

Here is a screenshot of it :

ORA-12154: TNS:could not resolve the connect identifier specified

     This error is, often the very first one you get on trying to establish a connection to your database. Simply put, it means that you have named the database you wish to be connected to and Oracle doesn’t know who you are referring to.

     This error is often caused by typos, maybe in your connection string, but possibly in your tnsnames.ora file. It is also possible that the tnsnames.ora file is not accessible or does not even exist. If it does, ensure that it contains the service name you are using; also go over it to make sure it doesn’t have any unpaired parentheses or such.

     Sometimes when you edit your tnsnames.ora file , especially if you copy-paste , some blank characters can be formed. The best solution is to open your tnsnames.ora file in a text editor and fine tune the format.

(tnsnames.ora file resides in path: /home/oracle/network/admin/tnsnames.ora)

Generic formatting should look like this: 

NEWDB =
    ( DESCRIPTION =
    ( ADDRESS = (PROTOCOL = TCP)(HOST = HOST)(PORT = 1521) )
    ( CONNECT_DATA =
         ( SERVER = DEDICATED )
         ( SERVICE_NAME = NEWDB )
      )
  )


If you are working on a large corporate, sometimes your DNS cannot resolve the service name too. Lets say you have a DB with an HACMP configuration. 
dbserver1.corphost , dbserver2.corphost.
Sometimes the connection cannot resolve the naming and DNS. So you better check first with ip connection before further investigation.

Invalid or Nonexistent Column Name – ORA 00904

There are multiple reasons which can lead to this common error code.

Below, there are some scenarios which can occur.

• The column name entered is either missing or invalid.Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

• If you are copying column definition from some other table’s DDL statement and if the selected  column is not the last one you will also copy comma, and if you put it as last column in your DDL statement you will see “ORA-00904: invalid identifier”because after comma Oracle expect another column declaration.

• If you will see the error Schema Creation Failed: ORA-00904: invalid identifier. The reason is that your schema creation failed because AUDIT is a reserved word in Oracle 11g R2.+

• Apart from table creation, you will see error “ORA-00904: invalid identifier” if you use wrong column name in INSERT statement or use a non-existent column name. Most of the time it happens because of typo, but some other time it could be due to parallel update e.g. someone changed the schema of table and renamed or dropped the column you are referring in INSERT query.