Thursday 27 June 2013

Virtual Box – Shared Folders in Oracle Enterprise Linux as a guest OS and Windows as Host

 It should be work with most Linux environments, but for our case is tested on Oracle Enterprise Linux.
I was trying to install a Oracle Database in a VBOX with OEL Linux as a guest and then I realized that the external disk are no detected automatically.
Then after some research I found what we have to do. The steps below are what I did to enable it.
Pre-requisite -- VBOX Additional Guest Tool must be installed
1. In VirtualBox Add the disk/Folder to be shared. Devices → Shared Folders

lsmod   vboxvfs must be loaded in memory(see below image). If not, please verify that Guest additions were installed on the Operating System Guest. If so, please load the vboxvfs.

[root@db12c d_soft]# /sbin/modprobe vboxvfs




Job done !

VirtualBox 4.2.14 Guest Additions Building the main Guest Additions module [FAILED]

Add caption


ORA-22924: snapshot too old

ORA-22924: snapshot too old ( While Exporting LOBs)

I have migration projects to migrate one particular Schema from 10g(Windows) to 11g(Linux). And the schema size was 100gb as one of the table got LOB Columns. But while exporting this schema to 10g datapump utility , we keep hitting following errors. The investigation result requires modification in database.

ORA-31693: Table data object "HPADMIN"."T7751" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old 

I instantly checked the undo tablespace whether it was undersized or not. It was set to "auto extend on" as expected. Then I checked out the undo retention parameter-

SQL> show parameter undo_retention

It was set to default 900. I was thinking to modify it but was not so sure as oracle supporting documents were not available at hand.

Later, when I  found that the error comes for the LOB segment undo problem. When data is modified, oracle keeps a version of the data in undo segment for read consistency.

I had two options-

1. Increase PCTVERSION in LOB table columns
2. Increase RETENTION in LOB table columns

1. PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle Database tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data.

PCTVERSION has a default of 10 (%), a minimum of 0, and a maximum of 100. Setting PCTVERSION to twice the default value allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of LOB columns, it may be useful to retain old versions of LOB pages. In this case, LOB storage may grow because the database will not reuse free pages aggressively. PCTVERSION=0; the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause "snapshot too old" errors.

2. As an alternative to the PCTVERSION parameter, one can specify the RETENTION in CREATE TABLE or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a parameter in the LOB storage clause of the period of time, rather than using a percentage of the table space.

DBA Querty to check LOB Parameters

select column_name, nvl(retention, 0) retention, pctversion from dba_lobs where table_name = 'TABLE_NAME';

I have planned to go with option 2 -

ALTER SYSTEM SET UNDO_RETENTION=2700 scope=both;
ALTER TABLE TABLE_NAME MODIFY LOB (COLUMN_NAME) (RETENTION);

The LOB RETENTION will take value from UNDO_RETENTION parameter.

But it didn't work, And end up with same error. So I had to go with Option 1.

ALTER TABLE TABLE_NAME MODIFY LOB (COLUMN_NAME) (PCTVERSION 20);

and guess what  ??  it works !!



Summary -
Cause: The version of the LOB value needed for the consistent read was already overwritten by another writer.
Action: Use a larger version pool/retention time.

One very important article of Tom Kyte -

LOB RETENTION

In the past, one would use the PCTVERSION storage parameter for their LOB segments to reserve a percentage of storage space for read consistency of LOB segments. In Oracle 11g, you can now use the RETENTION parameter. The RETENTION parameter will use the UNDO_RETENTION parameter for determining how long to keep LOB data for read-consistency purposes. But be advised that it does not use the Undo tablespace! The LOB segment’s tablespace is used for read-consistency purposes.
When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified. If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter. To change the LOB segment’s RETENTION value to match the new UNDO_RETENTION value, do the following:

ALTER TABLE my_table MODIFY LOB (lob_column) (PCTVERSION 20);
ALTER TABLE my_table MODIFY LOB (lob_column) (RETENTION);
By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used. You need to do this for all LOB segments that you intend to modify.

Wednesday 26 June 2013

Oracle database 12c New Feature Highlight




                                                            

Move SPFILE from one ASM diskgroup to another (ORACLE RAC)

##
## "Move" spfile from:
##    +OLDLOC/DBNAME/spfileDBNAME.ora
##  to:
##    +NEWLOC/DBNAME/spfileDBNAME.ora
##

## NOTE:
##   in this example there are FOUR nodes:
##      rac1
##      rac2
##      rac3
##      rac4


## run all steps from first node
##
## "copy" spfile to +NEWLOC
sqlplus /nolog
connect / as sysdba
create pfile='/u01/app/oracle/admin/DBNAME/scripts/init_new.ora' from spfile='+OLDLOC/DBNAME/spfileDBNAME.ora';
create spfile='+NEWLOC/DBNAME/spfileDBNAME.ora' from pfile='/u01/app/oracle/admin/DBNAME/scripts/init_new.ora';
exit

## modify init*.ora files to point to new location
##
echo "SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME1.ora
ssh rac2 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME2.ora"
ssh rac3 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME3.ora"
ssh rac4 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME4.ora"

## modify OCR with new SPFILE location
##
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
srvctl modify database -d DBNAME -p +NEWLOC/DBNAME/spfileDBNAME.ora

## bounce ALL instances to switch to new SPFILE
##
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
srvctl stop database -d DBNAME
srvctl start database -d DBNAME

## all services will be down because DB got bounced -- restart it
##
srvctl start service -d DBNAME



## drop old spfile
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM1
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP OLDLOC DROP FILE '+OLDLOC/DBNAME/spfileDBNAME.ora';
exit

## verify "old" spfile is gone from OLDLOC
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM1
asmcmd
ASMCMD> find / *spfile*
+NEWLOC/DBNAME/PARAMETERFILE/spfile.259.667587125
+NEWLOC/DBNAME/spfileDBNAME.ora

Move/Copy controlfile from file system to ASM

Current Controlfie location (using Init.ora file here)

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ cat initTEST11G.ora
*.control_files='/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl'

Change to new location in ASM with random controlfile number, that will changed later by RMAN

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora
*.control_files='+DATA/TESTDB/controlfile/current.261.772293749','+DATA/TESTDB/controlfile/current.260.772293749'

Startup database with nomount

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:55:43 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SYS@TESTDB >> startup nomount;
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2499806416 bytes
Database Buffers          822083584 bytes
Redo Buffers               16330752 bytes
SYS@TESTDB >> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Connect Target db with RMAN and restore controlfile from original location

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jun 26 16:56:24 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDB (not mounted)

RMAN> restore controlfile from '/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl';

Starting restore at 26-JUN-2013 16:56:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/TESTDB/controlfile/current.261.819132997
output file name=+DATA/TESTDB/controlfile/current.260.819132997
Finished restore at 26-JUN-2013 16:56:39

RMAN> exit


Recovery Manager complete.

Confirm location of new controlfile

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ s
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:57:04 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SYS@TESTDB >> alter database mount;

Database altered.

SYS@TESTDB >> show parameter controlfile
SYS@TESTDB >> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DATA/TESTDB/controlfile/cur
                                                 rent.261.819132997, +DATA/test
                                                 sits/controlfile/current.260.8
                                                 19132997
control_management_pack_access       string      DIAGNOSTIC+TUNING

SYS@TESTDB >> alter database open;
Database altered.

SYS@TESTDB >> exit 

Modified Init.ora files with new controlfile location that created by RMAN , and restart the database.

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora
*.control_files='+DATA/TESTDB/controlfile/current.261.819132997','+DATA/TESTDB/controlfile/current.260.819132997'

SYS@TESTDB >> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@TESTDB >> startup
ORACLE instance started.
Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2499806416 bytes
Database Buffers          822083584 bytes
Redo Buffers               16330752 bytes
Database mounted.
Database opened.


Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact

Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact


This Blog is intended to resolve below errors observed in DB alert log file due connectivity issue between DB Instance & ASM disk/Instance


Errors:

Fatal NI connect error 12547, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/grid/product/11.2.0/grid/bin/oracle)(ARGV0=oracle+ASM_asmb_remdev)(ENVS='ORACLE_HOME=/u01/app/grid/product/11.2.0/grid,ORACLE_SID=+ASM')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(enable=setuser)(CONNECT_DATA=(CID=(PROGRAM=oracle)(HOST=remedy-ebu-dev-db1)(USER=ora11g))))
TNS-12547: TNS:lost contact
    ns secondary err code: 12560
    nt main err code: 517

ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contact
TNS-12545: Connect failed because target host or object does not exist

ERROR: Failed to connect with connect string: (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=stance
ORA-12547: TNS:lost contact
,ORACLE_SID=055: unable to connect to ASM instance



Cause:

$GRID_HOME/bin/oracle or $ORACLE_HOME/bin/oracle permission has got changed

ls -al $GRID_HOME/bin/oracle
-rwxr-x--x 1 grid oinstall 200678464 Feb 28 14:54 oracle

ls -al $ORACLE_HOME/bin/oracle
-rwxr-x--x 1 oracle asmadmin 228886191 Feb 28 15:41 oracle

Solution:

Change permissions as below

cd $GRID_HOME/bin
chmod 6751 oracle

cd $ORACLE_HOME/bin
chmod 6751 oracle

it should look like having sticky bit ..

ls -l $GRID_HOME/bin/oracle
-rw
sr-s--x 1 grid oinstall 203974257 May 11 09:30 oracle

ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 232399431 May 14 13:47 oracle

or 

ls -l $GRID_HOME/bin/oracle*
-rwxr-x--- 1 oracle oinstall         0 Sep  5  2010 oracleO*
-rwsr-s--x 1 oracle asmadmin 228886468 Jan 11  2012 oracle*

ls -l $ORACLE_HOME/bin/oracle*
-rwxr-x--- 1 oracle oinstall         0 Sep  5  2010 oracleO*
-rwsr-s--x 1 oracle asmadmin 228886468 Jan 11  2012 oracle*


Restart the ASM and DB instance after changing permission  .. DB will come up

Friday 21 June 2013

Listener Fails to start TNS-12557,12560,00527 , ORA-12547: TNS:lost contact -- Windows Environment


We have faced very wired issue with Oracle database 11g Listener in Windows environment. For some reason 
it's not starting up and giving  following errors. We have tried all the solutions to resolve this issue, like starting/stopping listener through different Oracle Home, configuring timeout in sqlnet.ora , amending hosts files,creating another listener through netca , even try installing new oracle client with different ORACLE_HOME, but none of this solutions helped. 

But one thing i forget to mentioned here , we have also got another Oracle product on same machine.

WEBLOGIC  10.3.3
FMW 11.1.1.3 

It mean Our Windows Registry have some many key/value for the Oracle product. And we didn't have any clue what was causing problem.

But at last we tried one very simple thing and it works. That I have mentioned in Solution part. 

And guess what , it works !! 

And therefor I guess, one good Oracle DBA hates Windows for their databases.

Errors

TNSListener fails to startup.

Errors include

ORA-12547: TNS:lost contact
TNS-12557: TNS:protocol adapter not loadable
TNS-12560: TNS:protocol adapter error
TNS-527: Protocol Adapter not loadable

Also seen with
Failed to start service, error 997.
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error

Solution

Remove the ORACLE_HOME system environment variable.
From Start button, navigate to Control Panel.  Then select the System icon and click the Advanced tab.
Click the Environment Variables button
Find the ORACLE_HOME in the system environment variables and delete it.
Re-boot the machine.