Tuesday 26 March 2013

SecureFiles Oracle 11g

Oracle Secure Files also known as Fast Files is Oracle’s improvement to storage of large object types (LOBs). Secure Files gives comparable performance to a file system for LOBs and LOBs can store many types from data from images, large amounts of text, word documents, excel spreadsheets, XML, HTML, etc. as well as DICOM formatted medical images.
This is a step forward in helping manage unstructured data with the Oracle database by boosting performance, but also by improving security. Secure Files extends Transparent Data Encryption to LOBs, this not only makes storing and managing unstructured content easier, but improves the security of the unstructured content.
If that was not enough Secure Files also gives advanced file system features such as compression and data de-duplication. Data De-duplication is where duplicate objects in LOBs tied to many records within the database is only stored once rather then a copy for each record. This not only improves storage space, but can also offer performance improvements. Compression like it indicates compresses LOB data transparently offering storage savings and a performance boost, but Oracle takes it a step further and automatically determines if the data is able to be compressed and if so are the space savings from the compression of benefit.
By default normal LOB storage is used, called BASIC file. To use SecureFile for LOB storage the SECUREFILE lob Storage keyword must be used.

The default behavior for securefile usage can be changed via the db_securefile initialization parameter.
db_securefile={ALWAYS FORCE PERMITTED NEVER IGNORE}

ALWAYS – Always attempts to create all LOBs as SECUREFILE LOBs
FORCE - all LOBs created in the system will be created as SECUREFILE LOBs.
PERMITTED – Allows LOBs that use SecureFiles.
NEVER – Never Allows LOBs that use SecureFiles, they will always be BASICFILE LOBs
IGNORE – Ignore errors that otherwise would be raised by forcing LOBs as SECUREFILE LOBs


Can be set dynamically via an ALTER SYSTEM:
SQL> ALTER SYSTEM SET db_securefile = 'ALWAYS' ;
Securefiles offer a lot of benefits over the old LOB storage method, such as deduplication capability, compression and encryption.
Compression has 2 forms medium which is the default and high. Keep in mind the high level of compression the larger resource impact on the database you should expect.

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
(COMPRESS
CACHE
NOLOGGING );

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
CACHE
NOLOGGING );


Encryption for SecureFiles is implemented via the Transparent Data Encryption (TDE) and SecureFiles extends that TDE for LOB data types. Encryption is performed at the block level and uses the following valid encryption levels 2DES168, AES128, AES192 (default) and AES256. Keep in mind for SecureFiles the NO SALT option is not supported.

CREATE TABLE table1 ( a CLOB ENCRYPT USING 'AES128')
LOB(a) STORE AS SECUREFILE
( CACHE );


DeDuplication can be a powerful feature that can reduce the amount of storage space for LOBs as all duplicated LOBs are only stored once. With the desire to reduce database disk space the compress and DeDuplication can provide significant cost savings for storage.

CREATE TABLE table1 ( REGION VARCHAR2(20), ID NUMBER, a BLOB)
LOB(a) STORE AS SECUREFILE
( DEDUPLICATE
CACHE)

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
DEDUPLICATE
CACHE ) ;


Oracle Secure Files utilizes Shared IO Pool. The shared IO pool is used from the SGA and allocations are always for a specific session and therefore the data is specific to the session. We can look at the Shared IO Pool via the v$sga_dynamic_componets and v$sgainfo V$ views. If we examine the memory structures of the SGA we can see what the Shared IO Pool max size is in relationship to the other memory structures of the database.

SQL> select name, bytes from v$sgainfo ;
NAME BYTES
-------------------------------- ----------
Fixed SGA Size 1334380
Redo Buffers 5844992
Buffer Cache Size 268435456
Shared Pool Size 239075328
Large Pool Size 4194304
Java Pool Size 12582912
Streams Pool Size 4194304
Shared IO Pool Size 0
Granule Size 4194304
Maximum SGA Size 535662592
Startup overhead in Shared Pool 46137344
Free SGA Memory Available 0


SQL> select * from v$sga_dynamic_components where component='Shared IO Pool' ;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
Shared IO Pool 0
0 0 0 0 STATIC
4194304


When a session is unable to find free memory in the Shared IO Pool, PGA memory would be used. To see PGA memory allocations you can use the V$SECUREFILE_TIMER view which gets an entry each time memory is allocated out of the PGA.

SQL> select * from v$securefile_timer where name like '%PGA%';
NAME LAYER_ID OWNTIME MAXTIME MINTIME INVOCATIONS LAYER_NAME
------------------------- ---------- ------- ------- ------- ----------- --------------------------------------------------
kdlw kcbi PGA alloc timer 2 0 0 0 0
Write gather cache
kdlw kcbi PGA free timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA borrow timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA free timer 2 0 0 0 0
Write gather cache

Monday 25 March 2013

ORA-03106: fatal two-task communication protocol error

Error:

ORA-24330: internal OCI error
ORA-03106: fatal two-task communication protocol error
ORA-03114: not connected to ORACLE


Where: Error logged on oracle 11.2.0.2 client application server. Application based on c/c++ coding. Solaris 10 SPARC

Database 11.2.0.2 compatible, Solaris 10 SPARC

Background:
ORA-24330: internal OCI error & ORA-03114: not connected to ORACLE errors caused due to session disconnection byORA-03106: fatal two-task communication protocol error

Solution:
1. Set db_securefile to PERMITTED & re-create all LOB data type tables (Workaround)
2. Replace blank row column values of the table by some value or NULL flag, and enable NOT NULL constraint for those columns

Lesson Learned:

As the parameter db_securefile default value is PERMITTED , which got changed to ALWAYS as a part if databasehardening as per CSI benchmark 11g


Need to have proper testing & review from application after DB hardening
 
NOTE:-- If need more details please check below blog:-
http://www.moreajays.com/2013/03/ora-03106-fatal-two-task-communication.html
 
 

Thursday 21 March 2013

Nulls in ORACLE


Nulls

If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.
Do not use null to represent a value of zero, because they are not equivalent.

-------------------------------------------------------------------------
Note:
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.
-------------------------------------------------------------------------


Nulls in SQL Functions

All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function to return a value when a null occurs. For example, the expression NVL(commission_pct,0) returns 0 if commission_pct is null or the value of commission_pct if it is not null.
Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

Nulls with Comparison Conditions

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function. Please refer to DECODE for syntax and additional information.
Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.

Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.
Table 2-20 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.




 

 

Tuesday 19 March 2013

send file from unix server to mail ID (Unix -->> mailx)


The following command can be used to attach files in the mail while sending it using 'mailx' command in UNIX OS.

uuencode sourcefilename destfilename | mailx -m -s "My attachment" shishir@shishirtekade.com

The uuencode command converts a binary file to ASCII data. This is useful before using BNU (or uucp) mail to send the file to a remote system. The uudecode command converts ASCII data created by the uuencode command back into its original binary form.

The uuencode command takes the named SourceFile (default standard input) and produces an encoded version on the standard output. The encoding uses only printable ASCII characters, and includes the mode of the file and the OutputFile filename used for recreation of the binary image on the remote system.

To encode the file unix on the local system and mail it to the user shishir on another system called mysys, enter:

uuencode unix unix | mail shishir@shishirtekade.com

ORA 12154 error after install new OS windows 7 and PL/SQL tool

we are getting ORA 12154 error from my PL/SQL application for new OS windows7






The TNS entries are perfect and oracle home is set properly, but I couldn't login.

Solution:--

There are two program files folder in windows 7. one is 'Program files' and other one is 'Program Files(x86)'.
While installing oracle I've selected 'Program Files(x86)' as destination. That' the reason for the error.
I've moved the ‘PLSQL Developer’ folder to the 'Program Files' folder and it has worked.


 

ERROR 1396 (HY000): Operation DROP USER

 
Created new user like below mention snap


MySQL>grant select on migrate.active_table on 'qvviznte'@'121.244.254.30'
>identified by 'password';

MySQL>flush privileges;

but after trying to access user from application end its given some error.
so I decide drop and re-create the new user but I'm getting ERROR-1396(HY000)

 














Solution:-

delete from mysql.user where User = 'qvviznet';
flush privileges;
Then try to re-create the same user

grant select on migrate.active_table on 'qvviznte'@'121.244.254.30' identified by 'password';

flush privileges;

then its working fine from application end....




 

Monday 18 March 2013

Terminal too wide in SunOS

Error:--

[oracle@web-dev:/export/home/oracle>]crontab -e
Terminal too wide
:
At EOF


OR

bash-2.05$ vi all_subs_ded_20080203.txt
Terminal too wide
:q
1 more file to edit:q

Solution:--

make your putty window smaller ( less wide ), it is a limitation in pre-solaris10 vi.
minimize putty window then try.....




Thursday 14 March 2013

ORA-10631: SHRINK clause should not be specified for this object


PROBLEM:
ORA-10631:SHRINK clause should not be specified for this object

SOLUTION:
This error is due to the fact that an object you are trying to shrink has an "function-based" index on it. Objects with function-based indexes cannot be shrunk. To reclaim the space you need to drop and readd the function-based index after shrinking the object*.

*Before dropping any indexes make sure you understand what the index is for and have a script prepared to replace the index. Removing an index can affect performance of the query or the application

1-Drop the function-based index.
3- Shrink the table.
4- Recreate the index again on the table.

Wednesday 13 March 2013

ADMIN_RESTRICTIONS_listener_name=on


Setting ADMIN_RESTRICTIONS_listener_name=on disables the runtime
modification of parameters in listener.ora.

That is, the listener will refuse to accept SET commands that alter its parameters.
To change any of the parameters in listener.ora, including ADMIN_RESTRICTIONS_listener_name itself, modify the listener.ora file manually and reload its parameters (with the RELOAD
command) for the new changes to take effect without explicitly stopping and
restarting the listener.


The parameter is useful if the listener is not
password-protected.

Tuesday 12 March 2013

ORA-4023 WHEN SELECTING FROM VIEW

ORA-4023 WHEN SELECTING FROM VIEW
                                  OR
ora-04023 summary object could not be validated


PROBLEM:
--------
When executing SELECT on a view which is used in the proc it is throwing
ORA-4023 error but the object status shows VALID only. After dropping and
recreating view works fine for some time then again results in ORA-4023.


DIAGNOSTIC ANALYSIS:
--------------------
Requested the customer, if the error repeats again, to set
set events '4023 trace name ERRORSTACK level 12';
and upload the error stack.


alter system flush shared_pool;

Monday 11 March 2013

Disable case-sensitive checking in MySQL


On Operating Systems like Windows where filesystem is case-insensitive MySQL would be case-insensitive by default.
But on operating systems like Linux MySQL is case-sensitive. To disable case-sensitivity in Linux we can add following line in '/etc/my.cnf' and restart mysqld service.
lower_case_table_names=0

There are other MySQL variables other then this which can help in changing the behavior of MySQL. Use 'show variables' command to see values of variables.
We can also use 'SET GLOBAL <variable_name>=<value>;' syntax for changing values of variables on running MySQL.....

also use below method for disable case-sensitivity (using startup command)

 

[ERROR] Error in accept: Too many open files


--open-files-limit=count

Changes the number of file descriptors available to mysqld. You should try increasing the value of this option if mysqld gives you the error Too many open files. mysqld uses the option value to reserve descriptors with setrlimit().
If the requested number of file descriptors cannot be allocated, mysqld writes a warning to the error log.

mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed.


Action Plan:--

1) Add this to your my.cnf file:-
[mysqld]
open-files-limit = 12000

2) Restart mysqld


 

Thursday 7 March 2013

How to unzip a .bz2 file????


If you have a file with .tar.bz2 extension you can easily unzip it with a tar command on the commandline. Here is the actual command

tar -xvjpf filename.tar.bz2

In addition .bz files can also be opened / unzip with a bunzip2 command as follow. However this command will only work if you have already bunzip2 installed.

bunzip2 filename.bz2

unzip multiple files...

bunzip2 1_14686_629553575.arc.bz2 1_14687_629553575.arc.bz2 1_14688_629553575.arc.bz2

 

Wednesday 6 March 2013

How to Drop Database in Oracle 10g????

Dropping a database is really easy with oracle 10g. Earlier in order to drop a database it was required to manually remove all the datafiles, control files, redo, logfiles and init.ora, password file etc but with oracle 10g dropping a database is a single command.
In order to drop the database start the database in restrict mode and bring it in mount state as shown:


sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup restrict mount;

SQL> drop database;
Database dropped

SQL> exit

 

Tuesday 5 March 2013

ORA-07445: exception encountered: core dump [kqrpre1()+2780]


ORA-10387: parallel query server interrupt (normal)
ORA-07445: exception encountered: core dump [kqrpre1()+2780]

ORA-07445: exception encountered: core dump [kghadjust()+120]

Stack trace for the error ORA-07445 [kghadjust()+120] is similar to:
kghadjust kghupr_flg kghupr kglophup kglHandleUnpin




Cause:--
This is unpublished Bug:4927533 - INSTANCE CRASHED WITH ORA-7445 [KGHADJUST],

Solution:--
Fix:
Unpublished Bug:4927533 is fixed in 10.2.0.3 and in 11g.

One-off patches may be available depending on your current release and operating system.
To obtain a patch from MetaLink:
1) Click on 'Patches and Updates'
2) Click on 'Simple Search'
3) Enter patch number: 4927533
4) Select your O/S
5) Click Go.

Workaround:--
set _bloom_filter_enabled=FALSE.

Setting the parameter _bloom_filter_enabled=FALSE may have a preformance impact,
re-gather statistics on the object to resolve the performance issue.

ORA-00600: internal error code, arguments: [17147]
------------------------------------------------------------------------------------------------------------

 BUG:5469910 - Abstract: INSTANCE CRASHED WITH ORA-7445 [KGHADJUST]