ORACLE: DBA's day out

TDE Part-2: Implement TDE Tablespace Encryption

noreply@blogger.com (Mohammad Hasan Shaharear) — Sat, 19 Apr 2014 20:14:00 +0600

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:

Oracle Data Pump utility.
Commands like CREATE TABLE...AS SELECT...
Move tables like ALTER TABLE...MOVE.. or rebuild indexes.
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.

TDE Part-1: Transparent Data Encryption

noreply@blogger.com (Mohammad Hasan Shaharear) — Fri, 18 Apr 2014 21:09:00 +0600

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:

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.
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:

Index types other than B-tree
Range scan search through an index
External large objects (BFILE)
Synchronous Change Data Capture
Transportable Tablespaces
Original import/export utilities does not support. You need to use Oracle Data Pump.
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:

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

Things You Need To Consider Before Implementing TDE

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.
Existing tablespace can not be converted to encrypted tablespace. You need to use table reorganize/redefinition or datapump Export-Import for Migration.
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.
Require Goldengate 11.1.1.1 for replication
Increase RMAN(backup/restore) & DataPump(export/import) duration.
Need Separate backup task for Wallet that contains encryption decryption master key.
Need Oracle Advanced Security Licence (11,500 USD/Core).
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.

OPTIMIZER_FEATURES_ENABLE: Downgrade Oracle optimizer functionality

noreply@blogger.com (Mohammad Hasan Shaharear) — Sun, 7 Jul 2013 17:06:00 +0600

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;

Measure Table Size in ORACLE

noreply@blogger.com (Mohammad Hasan Shaharear) — Thu, 4 Jul 2013 11:29:00 +0600

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;

Recover Database from ORA-00333: redo log read error

noreply@blogger.com (Mohammad Hasan Shaharear) — Thu, 27 Jun 2013 18:56:00 +0600

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.

Manage SYS.AUD$ table

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 19 Jun 2013 15:40:00 +0600

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:

http://juliandontcheff.wordpress.com/2011/05/12/auditing-vs-performance-in-the-oracle-database
Oracle support - [ID 1375419.1, 1080112.1, 1375419.1]
http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php#purging_audit_trail_records

Alert: ASH performed an emergency flush

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 29 May 2013 16:52:00 +0600

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;

Oracle Wallet: secure external password storage

noreply@blogger.com (Mohammad Hasan Shaharear) — Mon, 13 May 2013 16:49:00 +0600

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

Oracle Recovery Manager (RMAN) Overview

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 1 May 2013 19:10:00 +0600

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:

Physical Backup – are backups of the physical files used in restoring &recovering database, such as datafiles, controlfiles and archived redo logs.
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 –

Datafiles
Controlfiles
Redo logs / archived redo logs
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)

DBMS_XMLGEN: SAve SQL query result in XML

noreply@blogger.com (Mohammad Hasan Shaharear) — Mon, 25 Mar 2013 15:30:00 +0600

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;

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

noreply@blogger.com (Mohammad Hasan Shaharear) — Sat, 16 Mar 2013 20:49:00 +0600

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;

Finding Duplicate SQL

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 13 Mar 2013 12:36:00 +0600

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:

force_matching_signature
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;

Transportable Tablespace (TTS)

noreply@blogger.com (Mohammad Hasan Shaharear) — Fri, 27 Apr 2012 13:43:00 +0600

'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

CTAS : Create Table as

noreply@blogger.com (Mohammad Hasan Shaharear) — Fri, 13 Jan 2012 08:15:00 +0600

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.

SPM - SQL PLAN MANAGER

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 28 Dec 2011 17:50:00 +0600

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

TABLE_EXISTS_ACTION

noreply@blogger.com (Mohammad Hasan Shaharear) — Tue, 27 Dec 2011 15:33:00 +0600

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.

ORA-08102

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 19 Oct 2011 16:59:00 +0600

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 ?'.

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

noreply@blogger.com (Mohammad Hasan Shaharear) — Sun, 16 Oct 2011 16:57:00 +0600

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

Table Comparison and Synchronisation

noreply@blogger.com (Mohammad Hasan Shaharear) — Mon, 25 Jul 2011 19:04:00 +0600

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.

Traveling past with Log Miner

noreply@blogger.com (Mohammad Hasan Shaharear) — Mon, 11 Jul 2011 14:55:00 +0600

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;

DATA PUMP: Network mode import

noreply@blogger.com (Mohammad Hasan Shaharear) — Sat, 2 Jul 2011 13:21:00 +0600

Some time we need to copy one schema from one DB machine to another machine or copy one schema as schema (i.e. remap schema) in same DB. The simplest way is to

(a) export required schema, move dump to remote DB and import [copy in 2 DB]

(b) export required schema and import the dump with schema replace option [copy in same DB]

Scenario ONE

Suppose the source schema is too small (say 1-3 GB) the above process is time consuming and labours compare with task requirements.

Scenario TOW

Suppose the source schema is too big (say 100 GB+) and there is not enough disk space to store the 100G size dump.

In above cases you can use the option of network import. But you need to consider that network import is a slower process because all the data should be travel in connection cable if your target is coping a schema in two different DB machine.

Lets describe how we can achieve this. we move user_info schema from source db to remote db.

STEP 1: grant exp_full_database role to socre shcema [IN REMOTE DB]
conn system/pass;

GRANT EXP_FULL_DATABASE to user_info;

STEP 2: [IN DESTINATION DB] Create destination user and grant necessary roles

conn system/pass;
CREATE USER user_info
IDENTIFIED BY pass
DEFAULT TABLESPACE USER
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT , RESOURCE TO user_info;

STEP 3: [IN DESTINATION DB] grant read/write on dump directory

conn system/pass;

GRANT read,write on DIRECTORY dump_directory to user_info;

Network import does not requer any dump file. This directory is only requer to write the import log file.

STEP 4: [IN DESTINATION DB] create public DB Link

conn system/pass;
CREATE PUBLIC DATABASE LINK SATURN
connect to user_info identified by pass
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_db)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=orcl.oracle.com)
(server=DEDICATED)))';

STEP 5: [IN DESTINATION DB MACHINE] execute impdp

impdp user_info/pass directory=dump_dir network_link=SATURN logfile=net_import_proddev.log EXCLUDE=GRANT,STATISTICS,SYNONYM,DB_LINK REMAP_SCHEMA=USER_INFO:USER_INFO

Multiplex online Redo Logfile

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 4 May 2011 09:37:00 +0600

It is important to have multiple (at list two) copy of each online redo logfile. To avoid the disaster of disk failure, it is recommended to place each member of a specific online redo logfile group in different disk.

To view to current online redo logfile group members

SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILES;

Suppose we have disk01 (mount point /u01 ) that contains current online redolog files .To add member in redo logfile group 1,2 and 3 in disk02 ( mount point /u02 )

ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo01.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo02.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo03.log' TO GROUP 3;

Track Database Growth

noreply@blogger.com (Mohammad Hasan Shaharear) — Mon, 21 Mar 2011 10:03:00 +0600

Some time it become very important to monitor your database growth specially in test database machines. Usually these machines have very limited disk space.
Thus, tracking the consumption of disk space is one of the frequent tasks in administrative checklist. Here is a SQL script that gives you the current size of database and average disk space consumed in each day.

SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
SELECT a.days,a.tsname
, used_size_mb
, used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
FROM (
SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
,ts.tsname
,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days
) a
) b GROUP BY b.tsname ORDER BY b.tsname;

ORA-14519 Conflicting tablespace blocksizes while importing partition tables

noreply@blogger.com (Mohammad Hasan Shaharear) — Wed, 26 Jan 2011 16:34:00 +0600

ORA-14519: Conflicting tablespace blocksizes for table : Tablespace T2 block size 16384 [partition specification] conflicts with previously specified/implied tablespace T1 block size 8192 [object-level default]

Well :) the error massage clearly indicate that I am trying to create a partition table in more than one tablespaces where the blocksize of the tablespaces are different. We all know that table partitions can not be placed in different blocksize tablespaces. Though I know this and not did this mistake before, I get this error when I am trying to make a replica schema in local test database.

I took dump by using EXPDP without any error and get the above error when try to import it by using REMAP_SCHEMA option of IMPDP. It is really annoying to get such error that can not be explainable. All of my table partitions are placed in a single tablespace but the error message says that I am trying to place table partitions in two tablespaces where their blocksize is different ! It sounds simply horrible.

But at the end of a long research :) I have found the mistake.The user's default tablespace is T1 [8k blocksize] and I create the table in tablespace T2 [16K blocksize] just like this -

CREATE TABLE TEST (ID NUMBER (10), TEXT VARCHAR2(50)

) PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This script create 2 table partitions of TEST table in T2 tablespace but unfortunately the default tablespace attribute of TEST table set to T1 (As I did not mansion default table space of table TEST when I create it. So ORACLE set this attribute same as user's default tablespace). See! Sometime ORACLE does something smartly that can cause stupid scenario. IF you want to see this unbelievable thing, just generate the table's SQL. You will see this

CREATE TABLE TEST (ID NUMBER (10),TEXT VARCHAR2(50)

) TABLESPACE T1

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This sturdiness of oracle cause ORA-14519 when I tryed to import TEST table because oracle can't create a partition table in different blocksize tablespaces.

So The solution is simple; mention default tablespace when you create a partition table. Just like this

CREATE TABLE TEST (

ID NUMBER (10),

TEXT VARCHAR2(50)

) TABLESPACE T2

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

[ It is not mandatory that all tablespace should be same (t2 in this case). You can put 3 different tablespace but their blocksize should be same.]

But, what is the solution for already created partition tables ? Well, the solution is very simple, just change the default tablespace attribute of your partition tables that are already created. Here is a script to identify the problematic tables.

SELECT * FROM (

SELECT x.table_name, x.def_tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name=x.def_tablespace_name) df

, x.tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name = x.tablespace_name) tb

FROM (

SELECT distinct t.table_name, t.def_tablespace_name, p.tablespace_name

FROM user_part_tables t, user_tab_partitions p

WHERE t.table_name = p.table_name

) x ) k WHERE k.tb <> k.df;

Then reset the default tablespace attribute

ALTER TABLE TEST MODIFY default attributes TABLESPACE T2;

Now take dump and apply it without facing ORA-14519 !

PL/SQL - Operator Precedence

noreply@blogger.com (Mohammad Hasan Shaharear) — Fri, 8 Oct 2010 14:50:00 +0600

Hi all, It has been almost an year since I posted a topic in this blog. After a 1 year study break, I back to my usual job and hopfully I am become regular in this blog again.

In SQL or PL/SQL we use several operators. Some are mathematical, logical and comparison operatiors. Oracle follow a order of precedence when execute an expression that contains more than one operators. If operatiors with same precidence are occured then it does not follow any order. Otherwise Oracle maintain the following order of precedence of operators.

Order |------------| Operator |------------| Operation
1 |------------| ** |------------| exponentiation
2 |------------| +, |------------| identity, negation
3 |------------| *, / |------------| multiplication, division
4 |------------| +, -, || |------------| addition, subtraction, concatenation
5 |------------| =, <, >, <=, >=, |------------| comparison
<>, !=, ~=, ^=, IS NULL, LIKE,
BETWEEN, IN
6 |------------| NOT |------------| logical negation
7 |------------| AND |------------| conjunction
8 |------------| OR |------------| inclusion

For example, when NOT, AND and OR operators are used in the same statement NOT is evaluated first, then AND and finally OR.