Saturday, April 19, 2014

TDE Part-2: Implement TDE Tablespace Encryption


In this part, we will implement TDE Tablespace Encryption. As TDE Tablespace Encryption have several major benefits over TDE Column Encryption ( Please read Part-1 for details ), we skip TDE column encryption and give our full focus on TDE Tablespace Encryption.

In order to implement TDE tablespace encryption, your Oracle Database version must be 11g release 1 (11.1) or higher. If you wish to test the enhanced tablespace encryption features, Oracle Database 11g Release 2 (11.2) is required.

TDE tablespace encryption will encrypting entire tablespaces. That means all objects stored in such tablespace will be 'By Default' encrypted. There is no restrictions to have encrypted and unencrypted tablespaces simultaneously in you database. You only encrypt those tablespaces which contain user/application data, system tablespaces must be remain as it is (i.e, unencrypted).


How to Encrypt a Tablespace Using TDE


Step 1 : Configure Oracle Wallet


Oracle Wallet is a secure password storage container which is placed outside of Oracle Database. To reduce the content of this post, we will give a brief configure of Oracle wallet here (if you have more interest please through this post for details). For TDE, oracle recommended to place the wallet outside of Oracle database though default wallet locations are inside database directories:

  • $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet
  • $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet.

When selecting which wallet to use, TDE first try to use the wallet specified by the parameter ENCRYPTION_WALLET_LOCATION. If the parameter is not set, then it looks the parameter WALLET_LOCATION. If this is not set as well, then TDE looks for a wallet at the default database location which i mention above. There are several usages of Oracle Wallet, TDE is one of them. Oracle strongly recommends that you use a separate wallet to store TDE master encryption keys. To set-up a separate wallet, set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file to point to the wallet used exclusively by TDE.

We Like to put the wallet files outside of Oracle installation directories. Therefore we chose location '/u01/encryption_wallet/'. Now, add following lines to sqlnet.ora and save it.

ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/encryption_wallet)))


Step 2: Create an Oracle PKCS#12 standard wallet 


Command: orapki wallet create -wallet '/u01/encryption_wallet' -pwd "oracle123"
This command creates a wallet file 'ewallet.p12' in specified location. The wallet password must be consist of eight or more alphanumeric characters. If no password has been specified on the command line, it prompts you to enter and reenter the wallet password.

Step 3: Generate Master encryption Key into wallet.


SQL> CONN / AS SYSDBA
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle123";
These will generate a random TDE master encryption key and save it to wallet. The password you given here is the wallet password not the master encryption key.

Step 4: Open the wallet if it is closed.


Before you proceed to create an encrypted tablespace, the Oracle wallet containing the tablespace master encryption key must be open. The wallet must also be open before you can access data in an encrypted tablespace. Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data. Once the wallet has been opened, it remains open until you shut down the database
instance, or close it explicitly by issuing the close command. When you restart the instance, you must issue the OPEN command again.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle123";
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "oracle123";

Step 5: Create Encrypted Tablespace


CREATE TABLESPACE enc_tbs DATAFILE '/oradata/enc_tbs01.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M ENCRYPTION USING 'AES128' DEFAULT STORAGE(ENCRYPT);
You can chose other alternative encryption algorithms that are 3DES168, AES192, AES256. The key lengths are included in the names of the algorithms themselves. If no encryption algorithm is specified, the default encryption algorithm AES128 is used.

Step 6: Data Migration


You cannot encrypt an existing tablespace. So if you wish to encrypt existing data, you need to move them from unencrypted tablespaces to encrypted tablespaces. For doing this you use:

  1. Oracle Data Pump utility.
  2. Commands like CREATE TABLE...AS SELECT... 
  3. Move tables like ALTER TABLE...MOVE..  or rebuild indexes.
  4. Oracle Table Redefinition. 

To check whether the tablesspaces is encrypted
SQL> SELECT TABLESPACE_NAME,ENCRYPTED FROM DBA_TABLESPACES;                                                            
TABLESPACE_NAME                ENCRYPTED
------------------------------ ---------------------
SYSAUX                       NO
USER                             NO
ENC_TBS                   YES

Setting and Resetting the Master Encryption Key
ALTER SYSTEM SET ENCRYPTION KEY ["certificate_ID"] IDENTIFIED BY "password";


Opening and Closing the Encrypted Wallet


The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces. Opening the wallet allows the database to access the master encryption key. Use the following ALTER SYSTEM command to explicitly open the wallet:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
Once the wallet has been opened, it remains open until you shut down the database instance, or close it explicitly by issuing the following command:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password";
Each time you restart a database instance, you must explicitly open the wallet before open the database.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
SQL> ALTER DATABASE OPEN;


Backup and Recovery of Master Encryption Keys


You cannot access any encrypted data without the master encryption key. As the master encryption key is stored in the Oracle wallet, the wallet should be periodically backed up in a secure location. You must back up a copy of the wallet whenever a new master encryption key is set. Recovery Manager (RMAN) does not back up the wallet as part of the database backup. If you lose the wallet that stores the master encryption key, you can restore access to encrypted data by copying the backed-up version of the wallet to the appropriate location. If the restored wallet was archived after the last time that the master encryption key was reset, then no additional action needs to be taken. If the restored wallet does not contain the most recent master encryption key, then you can recover old data up to the point when the master encryption key was reset by rolling back the state of the database to that point in time. All modifications to encrypted columns after the master encryption key was reset are lost.

Friday, April 18, 2014

TDE Part-1: Transparent Data Encryption

Transparent Data Encryption (TDE), part of Oracle Advanced Security,  encrypts critical data that are stored in data files in the form of tables, indexes, materialized views etc. Oracle provides several security mechanism for authentication and authorization to validate database users who access data. All these measures are there for secure access control but not any for database files such as datafiles, redo log and archive log files. If you have the datafiles, you can see the data though you have no access in the database. Therefore file level security is required to protect the data. On other side, you want minimal configurations or changes in middle and top layer of the application to achieve database file level security.

In 10g Release 2, Oracle first introduce TDE, a transparent data encryption-decryption mechanism to secure data stored in datafiles. Here transparent means database users or application need not to do any changes to achieve TDE. Database itself will encrypt data while writing into datafiles and decrypt then while reading. database users and applications not even bother for data encryption-decryption, Oracle database will do these transparently for them.    To prevent unauthorized decryption, transparent data encryption stores the encryption keys in a
security module (oracle wallet) external to the database. Data is transparently encrypt/decrypted by the database, user does not require any action on their part.

There are two types of TDE:

  1. TDE Column Encryption - data encryption-decryption for columns of a table. It encrypts/decrypts data at the SQL layer. This feature introduce in Oracle 10g R2.
  2. TDE tablespace Encryption - data encryption-decryption for all objects that stored into the encrypted tablespace. It encrypts/decrypts data during read/write operations This feature introduced in Oracle 11g R1.

Restrictions on Using TDE Column Encryption


Do not use TDE column encryption with the following database features:

  1. Index types other than B-tree
  2. Range scan search through an index
  3. External large objects (BFILE)
  4. Synchronous Change Data Capture
  5. Transportable Tablespaces
  6. Original import/export utilities does not support. You need to use Oracle Data Pump.
  7. TDE encrypted columns can't be use as foreign key constraints.

Restrictions on Using TDE Tablespace Encryption


Majority of the restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions are not applicable to TDE tablespace encryption. Though, the are some other restrictions
that apply to TDE tablespace encryption:

  1. External Large Objects (BFILEs) can not be encrypted using TDE tablespace encryption. This is because these files reside outside the database.
  2. To perform import and export operations, you need to use Oracle Data Pump.

Things You Need To Consider Before Implementing TDE

  1. 4-8% performance impact in end-user response time and an increase of 1-5% in CPU usage. Though it ensure below 10% performance impact.
  2. Existing tablespace can not be converted to encrypted tablespace. You need to use table reorganize/redefinition or datapump Export-Import for Migration.
  3. Increase disk consumption. Encrypting a single column would require between 32 and 48 bytes of additional storage for each row, on average. Though TDE tablespace encryption has no storage overheads.
  4. Require Goldengate 11.1.1.1 for replication
  5. Increase RMAN(backup/restore) & DataPump(export/import) duration.
  6. Need Separate backup task for Wallet that contains encryption decryption master key.
  7. Need Oracle Advanced Security Licence (11,500 USD/Core).
  8. Using a PKI key pair (PKI certificate) as a master encryption key may result in greater performance degradation when accessing encrypted columns in the database.

Sunday, July 7, 2013

OPTIMIZER_FEATURES_ENABLE: Downgrade Oracle optimizer functionality

After database up gradation or patched, you may find some under performing SQL statements. Due to one or more bug fixes Oracle optimizer my generate different execution plans for those SQL statements,  hence performance degraded . You need reasonable amount of time to tune them but unable to afford it in production environment! You have to do something quick ..........

In such situation, you can downgrade the optimizer functionality to a previous version or patch set; so that  optimizer just act like it's previous behavior. When you finish SQL optimization/tuning, you can switch to the upgraded or patched  optimizer version.

OPTIMIZER_FEATURES_ENABLE - An Initialization Parameter which can be altered at the system or session level to facilitate down gradation of oracle optimizer.

Current version (after up gradation or patched): 11.2.0.3

SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.2' scope=both;
or
SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';

Switch to current version
SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.3' scope=both;

Thursday, July 4, 2013

Measure Table Size in ORACLE

Sometimes it is required to measure how much space a tables occupy in Oracle. The size sum-up table and its co-related indexes, partitions, lobs, lob partitions. here are two SQL scripts, first one listed all tables own by a specific user and second one  include tablespace too.

SELECT segment_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
,round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.segment_type
)
GROUP BY segment_name
ORDER BY size_mb DESC;


SELECT segment_name, tablespace_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
, x.tablespace_name
, round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.tablespace_name,x.segment_type
)
GROUP BY segment_name, tablespace_name
ORDER BY size_mb DESC;

Thursday, June 27, 2013

Recover Database from ORA-00333: redo log read error

In development environment, it is very common scenario that we have multiple databases in a single machine by using VMware (i.e, each VMware contains one database). Again those machines doesn't have consistant power backup. Therefore we have to face power failure or VMware hang-up. So, we are forced to restart the machine while databases are still up & running. After restarting the machine, we have mostly got he following error:

   ORA-00333: redo log read error block count .

Here are the steps to overcome the error
SQL> startup
ORACLE instance started.
Total System Global Area ***** bytes
Fixed Size               ***** bytes
Variable Size            ***** bytes
Database Buffers         ***** bytes
Redo Buffers             ***** bytes
Database mounted.
 
 ORA-00333: redo log read error block *Number* count *Number*

 
Step 1: As the Db is in mount mode, We can query v$log & v$logfile to identify the status of log file group and their member.

 SQL> select l.status, member from v$logfile inner join v$log l using (group#); 
 STATUS  MEMBER
 ------------- --------------------------------------
 CURRENT /oracle/fast_recovery_area/redo01.log
 INACTIVE /oracle/fast_recovery_area/redo02.log
 INACTIVE /oracle/fast_recovery_area/redo03.log

 
Step 2: Recover the database using ackup controlfile.

 SQL> recover database using backup controlfile;
ORA-00279: change  generated at  needed for thread 1
ORA-00289: suggestion : /oracle/fast_recovery_area/archivelog/o1_mf_1_634_%u_.arc
ORA-00280: change  for thread 1 is in sequence #
Specify log: {=suggested | filename | AUTO | CANCEL}


Step3: Give 'CURRENT' log file member along with location as input. If it does not work give other log file members along with location in input prompt. In our case we give 
/oracle/fast_recovery_area/redo01.log
Log applied.
Media recovery complete.


Step 4: Open the database with reset logfile

SQL> alter database open resetlogs;
Database altered.

Wednesday, June 19, 2013

Manage SYS.AUD$ table

From 11g, Oracle by default enable auditing with 'DB' option.  That means audit information will store in database table. With this settings Oracle will audit following activities:

ALTER ANY PROCEDURE, ALTER ANY TABLE, ALTER DATABASE,ALTER PROFILE, ALTER SYSTEM, ALTER USER, AUDIT SYSTEM, CREATE ANY JOB, CREATE ANY LIBRARY, CREATE ANY PROCEDURE, CREATE ANY TABLE, CREATE EXTERNAL JOB, CREATE PUBLIC DATABASE LINK, CREATE SESSION, CREATE USER, DATABASE LINK, DROP ANY PROCEDURE, DROP ANY TABLE, DROP PROFILE
DROP USER, EXEMPT ACCESS POLICY, GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, GRANT ANY ROLE, PROFILE, PUBLIC SYNONYM ROLE, SYSTEM AUDIT

Apart from lot of benefits,  auditing may raise performance issues and the reasons are:
  • Too much activity is being audited
  • AUD$ table still placed in the SYSTEM tablespace
  • Oracle bugs.
1. Too much is being audited

The more activity you audit the more audit records will generate.  You need to restrict unnecessary auditing because it cost your resources and hamper performance. To see which activities are being audited
  
SELECT  * FROM DBA_PRIV_AUDIT_OPTS UNION SELECT * FROM DBA_STMT_AUDIT_OPTS;


2. AUD$ table still placed in SYSTEM tablespace:
By default AUD$ table lies in SYSTEM tablespace.  You will face space managment issue if you not move AUD$ table in SYSAUX tablespace along with indexes.  Use Oracle recommended package DBMS_AUDIT  to purge old unnecessary entries. Periodically shrink / truncate AUD$ table.

 BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'SYSAUX');
END;
/

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/


CREATE TABLE AUD_BACKUP AS SELECT * FROM AUD$;
TRUNCATE AUD$;
INSERT INTO AUD$ SELECT * FROM AUD_BACKUP;
DROP TABLE AUD_BACKUP PURGE;



3. Oracle bugs
Well there are couple of  known bugs, so apply latest patch.

Resources:

Wednesday, May 29, 2013

Alert: ASH performed an emergency flush

Alert

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 123...7 bytes.


Cause

This alert indicate sudden increase of database active sessions,  that fill-up ASH buffer faster then usual and therefore raise this alert. Actually this is not a problem but an indication of more  ASH buffer is needed to  support peak activity on DB.


Solution

 _ASH_SIZE is a hidden parameter and oracle not suggested to set such parameter without consulting their support. There is a meta link doc ( id 1385872.1) suggest we can increase 50% of current  ASH buffer, if we repeatedly receive this alert.   To monitor use this 

SELECT total_size,awr_flush_emergency_count FROM v$ash_info;

Monday, May 13, 2013

Oracle Wallet: secure external password storage


Some times we need database connection from shell script stored on file system. This can be a security issue, if the script contains database connection credential. To nullify this problem oracle provide a solution called wallet. Oracle wallet is a client-side secure external password container where DB login credentials are stored. Using this shell scripts can connect to DB using the "/@db_alias" syntax.

Step 1 : Set location for wallet

we Like to put the wallet files in $ORACLE_HOME/network/admin. Thus the location will be '/oracle/product/11.2.0/dbhome_1/network/admin'. Add following lines to sqlnet.ora

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/product/11.2.0/dbhome_1/network/admin)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0


Step 2: Set DB alias

Add the following lines to listerer.ora

ora_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA.DB1.ORACLE.COM) ) )


Step 3: Create Wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -create

You will ask to enter password. The password length must be 8+ containing alpha-numeric characters.


Step 4: Add database login credentials into wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -createCredential ora_db scott tiger

This will ask for password conformation and you shout give the same password which you gave when creating wallet.


Step 5: Listing credentials present in wallet 

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -listCredential


Step 6: Connect Db using wallet

sqlplus /@ora_db

Wednesday, May 1, 2013

Oracle Recovery Manager (RMAN) Overview

In general, backup & recovery combines several strategies & procedures which are protecting database against data lose and reconstructing the database after any kind of data loss.


There are two types of backup strategies:
  1. Physical Backup – are backups of the physical files used in restoring &recovering database, such as datafiles, controlfiles and archived redo logs. 
  2. Logical Backup – contains logical data exported from database with oracle utilities (i.e, datapump) and stored in a binary file.

Physical backup is the foundation of any sound backup & recovery strategy. Logical backup is a useful supplement to physical backup but not ensure sufficient protection against data loss without physical backup. Unless otherwise specified, the term backup refers to physical backup.


Oracle provides two backup & recovery solutions –
  • Recovery Manager (RMAN) managed backup & recovery.
  • User managed backup & recovery.

RMAN can take backups of –
  1. Datafiles
  2. Controlfiles
  3. Redo logs / archived redo logs
  4. Serve parameter file (SPFiles)

A database recovery involved two tasks:
  • Restore- retrieve datafiles or controlfiles from backup.
  • Recover- apply archived & on-line redo log changes on restored datafiles ( it is also known as media recovery)

Monday, March 25, 2013

DBMS_XMLGEN: SAve SQL query result in XML

DBMS_XMLGEN is a useful Oracle package that gives you a query result in XML file. Couple of days ago I have got a task to export all tables of an schema in different XML files. Here  is the PL/SQL code


grant read,write on directory DUMP_DIR to scott;
grant execute on DBMS_XMLGEN to scott;
grant execute on utl_file to scott;

set serveroutput on;
set echo on;
set timing on;
spool export_schema_xml.log;
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  l_output utl_file.file_type;
  l_amount NUMBER default 4000;
  l_offset NUMBER(38) default 1;
  l_length NUMBER(38);
  sqltext VARCHAR2 (4000 CHAR);
  result CLOB;
  l_buffer VARCHAR2 (4000 CHAR);
BEGIN
FOR x IN (SELECT x.table_name FROM user_tables x)
LOOP     
  l_offset :=1;
  sqltext := 'SELECT * FROM '||x.table_name;
  qryCtx :=  dbms_xmlgen.newContext (sqltext);
  DBMS_XMLGEN.SETCONVERTSPECIALCHARS (qryCtx,FALSE);
  result :=  DBMS_XMLGEN.getXML(qryCtx);
  l_output := utl_file.fopen('DUMP_DIR', x.table_name||'.xml', 'w', 32760);
  l_length:=nvl(dbms_lob.getlength(result),0);
  WHILE ( l_offset < l_length AND l_length > 0 )
  LOOP
    DBMS_LOB.READ (result, l_amount, l_offset, l_buffer);
    utl_file.put (l_output, l_buffer);
    utl_file.fflush(l_output);
      utl_file.fflush(l_output);
    l_offset := l_offset + l_amount;
  END LOOP;
  utl_file.fflush(l_output);
  utl_file.fclose(l_output);
  DBMS_XMLGEN.CLOSECONTEXT (qryCtx);
  dbms_output.put_line(x.table_name||' Data Exported');
END LOOP;
END;
/

spool off;

Saturday, March 16, 2013

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

ERROR
ORA-03113: end-of-file on communication channel
Process ID: 28105
Session ID: 130 Serial number: 5

Cause: This error may pop-up when you trying to start-up a database after executing 'shutdown abort' or unexpected shutdown of db due to powe failure. This error occurs when oracle fail to archive online redo log file.

Action: if your db is in archivelog mode then bring the db in noarchivelog mode and startup the db. Then, execute a normal shutdown and bring back the db in archivelog mode.

SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Wednesday, March 13, 2013

Finding Duplicate SQL

The presence of duplicate SQL indicate that there are some SQL statements which doesn't have Bind variables. These duplicate SQL can raise performance issue because they will increase the number of hard parse in database.

ORACLE 10g introduced two new columns in v$sql view, which can help to identifing duplicate SQL more accurately. Those two new columns are:
  1. force_matching_signature 
  2. exact_matching_signature

exact_matching_signature - If two or more SQL has same value in this column, ORACLE assumes they are same after making some cosmetic adjustments (removing white space, uppercasing all keywords etc) to them. The is simmiler, when parameter cursor_sharing is set to EXACT.

force_matching_signature - the same value in this column (excluding 0) marks SQLs that ORACLE will consider they are same when it replaces all literals with binds (that is, if cursor_sharing=FORCE).



SELECT sql_text , count(1)
FROM v$sql
WHERE force_matching_signature > 0
  AND force_matching_signature <> exact_matching_signature
GROUP BY sql_text
HAVING count(1) > 10
ORDER BY 2;

Friday, April 27, 2012

Transportable Tablespace (TTS)



'Transportable Tablespace' (TTS) first introduce in Oracle 8i and it become matured as time goes by.  You can use this  feature to copy a set of tablespaces from one Oracle Database to another.


For a DBA, transport data from one database to another is a very common task and you can do it in different ways

Method           Hardness     Best Suitable For    Availability

Database Link        Easy           Less than 50 GB           High
Data Pump           Moderate     Over 50 GB                 High
TTS                 Moderate     Terabytes of Data        Low
RMAN Duplicate    Hard           Full Database              High




Requirements:

  • A tablespace must be totally self contained to be transportable.
  • The source and target database must use the same character set and national character set.
  • You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
  • Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
  • There are some limitations in encryption tables.
Lets see how it works.................
 
IN SOURCE DATABASE


1. Create two tablespaces and one user

sqlplus / as sysdba;

CREATE TABLESPACE TT01 DATAFILE '/oradata/tt_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 8K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;

CREATE TABLESPACE TT02 DATAFILE '/oradata/tt_02.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;


CREATE USER test identified by test default tablespace tt01;
GRANT connect,resource to test;

2. Create a table with some data

conn test/test;

CREATE TABLE test_data(
id number (10),
alphabate varchar2 (10)
) TABLESPACE tt01;

ALTER TABLE test_data ADD CONSTRAINT PK_TEST_DATA_ID PRIMARY KEY (ID) using index tablespace tt02;

INSERT INTO test_data VALUES (1,'A');
INSERT INTO test_data VALUES (2,'B');
INSERT INTO test_data VALUES (3,'C');
INSERT INTO test_data VALUES (4,'D');
INSERT INTO test_data VALUES (5,'E');

commit;

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME       TABLESPACE_NAME
------------------ ------------------
TEST_DATA          TT01
PK_TEST_DATA_ID    TT02

So we have a table in one tablespace and an unique index (creat for primary key) in another tablespace.



3. tablespace's 'self contained' Test

conn / as sysdba;

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01', incl_constraints => TRUE);

SELECT * FROM sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index TEST.PK_TEST_DATA_ID in tablespace TT02 enforces primary constraints  of table TEST.TEST_DATA in tablespace TT01.

we got this error because TT01 tablespace contain a table which has an index stored in another tablespace.

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01,TT02', incl_constraints => TRUE);

SQL> SELECT * FROM sys.transport_set_violations;

no rows selected

This time no Error because all two tablespaces are included. Now make them 'Read Only'.


ALTER TABLESPACE tt01 READ ONLY;
ALTER TABLESPACE tt02 READ ONLY;


4. Take metadata of those 2 tablespaces by using datapump

expdp system directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/ ******** directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /d01/dump_dir/trans_tablespaces.dmp
******************************************************************************
Datafiles required for transportable tablespace TT01:
  /oradata/tt_01.dbf
Datafiles required for transportable tablespace TT02:
  /oradata/tt_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 02:27:17


5. Copy the datafiles to destination database

scp tt_01.dbf oracle@db2:/oradata/
scp tt_02.dbf oracle@db2:/oradata/

6. Make the read_only tablespaces to read write mode

sqlplus / as sysdba

ALTER TABLESPACE tt01 READ WRITE;
ALTER TABLESPACE tt02 READ WRITE;


IN DESTINATION DB

1. Create a user same as source DB

sqlplus / as sysdba;

CREATE USER test identified by test ;
GRANT connect,resource to test;


2. Import the metadata

impdp system directory=dump_dir dumpfile=trans_tablespaces.dmp transport_datafiles='/oradata/tt_01.dbf','/oradata/tt_02.dbf' logfile=imp_trans_tablespaces.log


3. Verify Tablespace and Object Movement

conn test/test

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
TEST_DATA            TT01
PK_TEST_DATA_ID      TT02

SELECT * FROM TEST_DATA;

        ID ALPHABATE
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E

Well we are done! To know more about TTS please go through the Oracle Doc http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm

Friday, January 13, 2012

CTAS : Create Table as

We all are very familiar with CTAS or 'Create Table as' clause that used to construct new table and populated it with data extract by 'Select' statement. That means table creation and population are done by executing a single sql CTAS statement.

Fom example,

CREATE TABLE employee_info As
SELECT emp.id, emp.name, dp.name FROM employee emp, department dp WHERE emp.dp_id =dp.id;

The above CTAS statment create a new table named 'employee_info' and store the result of SELECT statement into that table.

Here are some common use of CTAS statement:
  • Store the result of select statement into a table.
  • create a duplicate table for backup purpose.
  • Reorganize existing table for better performance.
There are some limitations of CTAS statement:
  • Can't create indexes ( in case of table duplication or Reorganization).
  • Can't create primary, unique or foreign key constraints ( in case of table duplication or Reorganization). But it can create 'NOT NULL' constraint.
  • Though CTAS mirrors metadata structure, it can't mirror 'DEFAULT' value checker for columns.
I have learned the 3rd pitfall of CTAS recently. I used CTAS to construct a duplicate table ( a sub task of table partitioning). The partitioning job was successful but the problem arose when we inserted new rows into the partitioned table. One of the column got null value stored instead of zero (the column's default value was zero). Through an investigation we discover that CATS statement skipped default value check of that column.

Wednesday, December 28, 2011

SPM - SQL PLAN MANAGER

As a DBA some I feel that Oracle is living thing ! for example query performance. Out of no where a gentle looking SQL statement may pop up your top time consuming query list. A sql query which gives efficient performance in 3 months ago (Or even week ago), now giving horrible performance that threatening you cool database performance :)

Yes, we all DBA know that a gentle statement can screwed up due to
- Re-guthering optimizer statistics
- Change in optimizes parameters
- Change in Schema? Object structure (i.e, metadata definition)


Though Optimizer always try to choose list expensive plan for each SQL statement execution, Some time It (optimizer) choose expensive plans and raise annoying situations for peace loving DBAs :)

Well Oracle 11g come up with a new feature that can handle such weird situation. The simplest solution we can think is a mechanism that only allow trusted SQL plans to be executed and reject untrusted plans. O Yes Oracle 11g gives us a manager named SPM that do the same thing.

SQL PLAN MANAGER (SPM) has three major components

  • SQL Plan Baseline Capture - Create SQL Plan Baseline for trusted (accepted) SQL plans.
  • SQL Plan Baseline Selection - Conform that only accepted SQl plans are used when an SQL statement is executed.
  • SQL Plan Baseline Evolution - Evaluated all SQL Plans (old & new) for each SQL statement. The new/old plan will only be excepted if it gives better or equal performance compared with existing trusted plan otherwise rejected. (If no trusted plan exist then current plan will be accepted)


To enable this cool feature we just do the followings

ALTER SYSTEM set optimizer_capture_sql_plan_baselines= TRUE scope=both; [Default value is false]
ALTER SYSTEM set optimizer_user_sql_plan_baselines= TRUE scope=both; [Default value is true, so just check the parameter value]

You are done ! Keep an eye in DBA_SQL_PLAN_BASELINES view and enjoying SPM's magic

Tuesday, December 27, 2011

TABLE_EXISTS_ACTION

Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is 'SKIP', so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND - The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE - If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE - This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

Wednesday, October 19, 2011

ORA-08102

ORA-08102: index key not found, obj# 290, file 1, block 4353


Cause: Internal error: possible inconsistency in index.

Action: Send trace file to your customer support representative, along with information on reproducing the error


Well I do not have access to oracle support (if you need it, you need to buy that service). So first of all, I tried to identify which index cause that problem.


SQL>select OWNER,OBJECT_NAME,OBJECT_ID, OBJECT_TYPE from dba_objects x where x.object_id=290;                                                                                                         

OWNER       OBJECT_NAME      OBJECT_ID   OBJECT_TYPE
-------- ------------------- ---------- ------------

SYS         I_JOB_NEXT            290    INDEX

Guess what ? it's an index own by 'SYS' user. So I issued index rebuild command and it didn't work. As the index lies in system tablespace, I didn't want to move it to other tablespace. Thus I droped and created the index again. So first gather the index DDL from database

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('INDEX','I_JOB_NEXT') from DUAL;
output:
CREATE INDEX "SYS"."I_JOB_NEXT" ON "SYS"."JOB$" ("NEXT_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"

SQL> DROP INDEX SYS.I_JOB_NEXT;
SQL> CREATE INDEX "SYS"."I_JOB_NEXT" ON "SYS"."JOB$" ("NEXT_DATE") TABLESPACE "SYSTEM";

It works. Previously, I thought that 'REBUILD' command internaly drop an object and recreate it. But, now I need to explore 'How rebuild command works ?'.

Sunday, October 16, 2011

ORA-01591: lock held by in-doubt distributed transaction string

Cause: Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.

Action: DBA should query the DBA_PENDING_TRANSACTIONS and DBA_2PC_PENDING, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact end user for rollback/commit the transection.

sqlplus / as sysdba;

SQL> SELECT * FROM DBA_2PC_PENDING;

SQL> ROLLBACK FORCE LOCAL_TRAN_ID;

SQL> commit;

SQL> alter system enable distributed recovery;

SQL> execute dbms_transaction.purge_lost_db_entry('LOCAL_TRAN_ID');

SQL > commit;

For more details plese go through this document

Monday, July 25, 2011

Table Comparison and Synchronisation

If you are managing more than one databases for same application, it is very common that you need to compare one table ( say scott.employee) in two different databases. I think, we had previous experiences to do such job :) . The simplest way is just minus two tables. Like

1. Two different tables In same DB - SELECT * FROM table1 minus SELECT * FROM table2;
2. Same table in two different DB - SELECT * FROM table1 minus SELECT * FROM table2@remote_db;

But this simple task can be time consuming if your tables are very big (say 1000000+ records). If targeted tables do not contain BLOB,CLOB or Long data type columns, you can easily use DBMS_COMPARISON, a new package introduce in Oracle 11g, to compare them. More over this package also provide Synchronization to remove miss match records in targeted tables. For details please go through Oracle Documentations.

Here we are going to compare and synchronise 'employee' table in two different DB.

Requirement: A database link between host (i.e, where these script will be run) and remote DB.

Step One: Create comparison Task

BEGIN
DBMS_COMPARISON.CREATE_COMPARISON(
comparison_name => 'compare_employee'
, schema_name => 'SCOTT'
, object_name => 'EMPLOYEE'
, dblink_name => 'db_link_to_remote'
, remote_schema_name=>'SCOTT'
, remote_object_name=>'EMPLOYEE'
);
END;
/

If you wish to compare two different table in same DB, set dblink_name => NULL, object_name => 'table1' and remote_object_name=>'table2'

Step Two: Execute comparison Task

DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE( comparison_name =>'compare_employee', scan_info=> scan_info, perform_row_dif => TRUE);
DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
IF consistent=TRUE THEN DBMS_OUTPUT.PUT_LINE('No differences found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Differences were found.');
END IF;
END;
/

Step Three: See how may difference you got

col COMPARISON_NAME format a15;
col SCHEMA_NAME format a15;
col OBJECT_NAME format a15;


SELECT s.scan_id ,c.COMPARISON_NAME,c.SCHEMA_NAME,c.OBJECT_NAME ,s.CURRENT_DIF_COUNT
FROM USER_COMPARISON c,USER_COMPARISON_SCAN_SUMMARY s
WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND s.scan_id = 514; (this number is a out put of step two)


Step Four: Synchronise Remote table with host table.

DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE (comparison_name => 'compare_employee',
scan_id => 506,
scan_info => scan_info, --this number is a out put of step two
converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS);
DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
END;
/

You can also Synchronise Host table by using Remote table, please visit given link.

I hope you all will enjoy this exiting new package of Oracle 11g.

Monday, July 11, 2011

Traveling past with Log Miner

Some times you may wish to see what happen in recent past. In a beautiful morning, you may discover that a misshape occure in your database :(. Unfortunately you did not enabled any auditing options earlier.

If your database is in archivelog mod and supplemental log is enabled then 'Log Miner' can save the day ! Oracle Log Miner is also capable of mining redo log, archive log of remote DB. Please see Oracle Utility Document for details.

In this post, we only consider a scenario in which Source DB and Mining DB is same. Lets describe how you can do this


STEP 1 : Create Log Miner User

CREATE USER logminer_adm identified by logminer_adm;

GRANT resource,connect to logminer_adm;
GRANT EXECUTE_CATALOG_ROLE,DBA to logminer_adm;


Step 2 : Enable Supplemental Login

Check whether supplemental login is enabled

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

If not, then you can't mine all transections. So enable it for future and follow the remaining steps to mine a subset of all transections.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;



Step 3: Add Archieve Log Files to Log Miner

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/u/archivelog/2011_07_10/o1_mf_1_470_71m4jv90_.arc', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/u/archivelog/2011_07_10/o1_mf_1_471_71m4kn09_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);


You can add as much files as you need

Step 4: Start Log Miner

EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME =>'10-JUL-2011 19:50:00',ENDTIME => '10-JUL-2011 19:55:00',OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+DBMS_LOGMNR.CONTINUOUS_MINE);

Step 5: View What Happen Last Night :)

SELECT SEG_OWNER ,OPERATION, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SYS';


Step 6: Stop Log Miner Session

EXECUTE DBMS_LOGMNR.END_LOGMNR;