Database Link between Oracle Database and SQL Server Database

We want to create database link from Oracle database to SQL Server database,
Environment :
Oracle Database 11.2.0.3.0 on Oracle Linux 6.5 x86_64
SQL Server 2008 on Windows Server 2008 R2

We need Software Oracle Database Gateways which can download from here

Here is step by step procedure for install Oracle Gateway and configure it for create database link from Oracle to SQL Server:
1) Install Oracle Database Gateways under ORACLE_HOME and check checkbox – Oracle Database Gateway for Microsoft SQL Server
1
2) Provide any existing Microsoft SQL Server details where you are planning to connect. It actually writes in configuration file and does not really verify
2
3) Run root script with root user
3
4) Make sure that the MS SQL database details are correct in the dg4msql parameter file.

vi $ORACLE_HOME/dg4msql/admin/initdg4msqltestdb.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=SQLServer_HOST_IP:1433//testdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=off
#HS_FDS_RECOVERY_ACCOUNT=RECOVER
#HS_FDS_RECOVERY_PWD=RECOVER
HS_NLS_NCHAR = UCS2
# Oracle Database Characterset 
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8

5) Append following in existing listener.ora. Change the name, path and port as required.

vi /u0/app/grid/product/11.2.0/network/admin/listener.ora
# listener.ora Network Configuration File: /u0/app/grid/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_oracle_db_ip)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u0/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = /u0/app/oracle/product/11.2.0/bin/dg4msql)
      (ENV = "LD_LIBRARY_PATH=/u0/app/oracle/product/11.2.0/dg4msql/driver/lib:/u0/app/oracle/product/11.2.0/lib")
      (SID_NAME = dg4msqltestdb)
      (ORACLE_HOME = /u0/app/oracle/product/11.2.0)
    )
)

6) Reload listener for get new configuration and Verify that the service is registered with the listener

lsnrctl reload LISTENER
lsnrctl status LISTENER
……
Service "dg4msqltestdb" has 1 instance(s).
  Instance "dg4msqltestdb", status UNKNOWN, has 1 handler(s) for this service...

7) Append following in tnsnames.ora

DG4MSQLTETSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = my_oracle_host_ip)(PORT = 1521))
    (CONNECT_DATA =
      (SID = dg4msqltestdb)
    )
    (HS = OK)
  )

8)

tnsping DG4MSQLTESTDB
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = my_oracle_host_ip)(PORT = 1521)) (CONNECT_DATA = (SID = dg4msqltestdb)) (HS = OK))
OK (50 msec)

9) create database link in oracle database, This username must be already created in the Microsoft SQL Server database with proper permissions.

CREATE DATABASE LINK testdb CONNECT TO “SQLUser” IDENTIFIED BY “SQLUserPass” USING ‘DG4MSQLTESTDB’;

10) test database link :

select count(1) from “SQLTable”@testdb;

Note :
If you want to create multiple Database Links from oracle Database to different Sql Server databases you need to create different initdg4msql.ora file under $ORACLE_HOME/dg4msql/admin with corresponding parameters and also add entries in listener.ora and tnsnames.ora files.

PLS-907 cannot load library unit

One of our database appeared this error after compiling some package and this package remain invalid state.

PLS-00907 : cannot load library unit schema.package

Cause:
PL/SQL is unable to find and load a library unit that was previously available. This typically happens when you try to load a unit which references another library unit that is non-existent or invalid.
Solution:

alter system flush shared_pool;

after that package compilation run successfully.

Solaris EM12c Agent Unreachable with Error “peer not authenticated”

Today faced this error on solaris 5.10 sparc

./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2  
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Status agent Failure:unable to connect to http server at https://myhost.mydomain.ge:3872/emd/lifecycle/main/. [peer not authenticated]

solution : Stop the Agent:

$ ./emctl stop agent

Verfiy the java process has stopped

  $ ps -ef |grep java

If the java process is still running you would see like the following still running

$AGENT_HOME/core/12.1.0.2.0/jdk/bin/sparcv9/java
$kill -9 pid

Update Config File:

$cd $AGENT_HOME/agent_inst/sysman/config
$cp s_jvm_options.opt s_jvm_options.opt.bkp

Add the following line to s_jvm_options.opt

-Dsun.security.pkcs11.enable-solaris=false

Restart Agent and Test Upload:

$ cd $AGENT_HOME/bin
$ ./emctl start agent
$ ./emctl upload agent

How to find mapping of ASM disks to Physical Devices?

Here is Video of these procedures
login with root user
1) identify asm disks

/etc/init.d/oracleasm listdisks
DATA01

2) query disks FOR DATA01

 
/etc/init.d/oracleasm querydisk -d DATA01
Disk "DATA01" is a valid ASM disk on device /dev/sde2[8,66]
ls -l /dev | grep 8 | grep 66
brw-r----- 1 root disk     8,  66 Jan 11 06:51 sde2

it means that physical partition sde2 is using for ASM disk DATA01.

After change hostname ASM did not start

On my test environment i changed hostname of Linux machine and after restart ASM did not start.
environment:

OS : Oracle Linux 6.5
DB : Oracle database 11g(11.2.0.3)

so let’s simulate changing hostname and see results:

[root@oel6 ~]# hostname
oel6
[root@oel6 ~]# vi /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=testdb
[root@oel6 ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.220.128 testdb

[root@oel6 ~]# reboot

our hostname changed after restart we can check it and try to start ASM instance.
old hostname — oel6 new hostname — testdb

[root@testdb ~]# hostname
testdb
su - grid
-bash-4.1$ srvctl start asm
PRCR-1070 : Failed to check if resource ora.asm is registered
Cannot communicate with crsd

sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 2 14:05:38 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service

Solution:
ok, ASM will not start because we need to reconfigure HAS(High Availability Service).
with root user go to $GRID_HOME/crs/install and run roothas.pl script for deconfiguration.

cd /u01/app/grid/product/11.2.0/grid/crs/install/
./roothas.pl -deconfig -force

Using configuration parameter file: ./crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CLSU-00100: Operating System function: opendir failed with error data: 2
CLSU-00101: Operating System error message: No such file or directory
CLSU-00103: error location: scrsearch1
CLSU-00104: additional error information: cant open scr home dir scls_scr_getval
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
Successfully deconfigured Oracle Restart stack

with root user go to $GRID_HOME/crs/install directory and run the roothas.pl script for reconfiguration

./roothas.pl

Using configuration parameter file: ./crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node testdb successfully pinned.
Adding Clusterware entries to upstart

testdb     2015/04/02 14:29:27     /u01/app/grid/product/11.2.0/grid/cdata/testdb/backup_20150402_142927.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

with grid user Register and start the resources

crsctl start resource ora.cssd
CRS-2672: Attempting to start 'ora.cssd' on 'testdb'
CRS-2672: Attempting to start 'ora.diskmon' on 'testdb'
CRS-2676: Start of 'ora.diskmon' on 'testdb' succeeded
CRS-2676: Start of 'ora.cssd' on 'testdb' succeeded

now we can add the listener as a resource and after that we can start it

srvctl add listener -l LISTENER
srvctl start listener
srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): testdb

now we can add the asm we do not need to create spfile for asm. we can use the one before.
if you haven’t spfile you can simple create pfile and then create spfile from this pfile for example create pfile – init+ASM.ora in $GRID_HOME/dbs with parameters:

instance_type=ASM
asm_diskstring='/dev/oracleasm/disks/DATA01'
asm_diskgroups='DG01'

sqlplus / as sysasm
SQL> startup pfile=init+ASM.ora;
SQL> create spfile='+DG01' from pfile='init+ASM.ora';
SQL> shutdown immediate;

with grid user

srvctl add asm -l LISTENER -p "/u01/app/grid/product/11.2.0/grid/dbs/init+ASM.ora"
srvctl start asm
srvctl status asm
ASM is running on testdb

at last we can add database

srvctl add database -d testdb -o /u01/app/oracle/product/11.2.0/dbhome_1 -p +DG01/spfiletestdb.ora -r PRIMARY -s open -t immediate -a "DG01"
srvctl start database -d testdb
srvctl status database -d testdb
Database is running.

that’s all, now we have new hostname and asm is up and running.

srvctl status asm
ASM is running on testdb

Moving an Oracle Database from filesystem to ASM

Here i’l show you how to move existing database from filesystem to ASM(Automatic Storage Management) on same host.
environment

OS : Oracle Linux 6.5
DB : Oracle database 11g(11.2.0.3.0)

First of all we need to configure Grid infrastructure, with root user make next steps:
1) install ASM packages, which you can find on official oracle site, for example:
HERE
2) add disk for ASM and partition it
3) add separate Operating system user and groups for ASM, which is recommendation from oracle, for example: grid user and groups: asmadmin,asmdba,asmoper

 groupadd  -g 2100 asmadmin
 groupadd  -g 2200 asmdba
 groupadd  -g 2300 asmoper

 mkdir -p /u01/app/grid
 mkdir -p /home/grid
 useradd -s /bin/bash -d /home/grid  -g oinstall -G asmadmin,asmdba,asmoper grid

4) give permissions to grid user

chown -R grid:oinstall /u01/app/grid
chown -R grid:oinstall /home/grid
chmod -R 775 /u01/app/grid
passwd grid

5) configure ASM library driver

/etc/init.d/oracleasm configure
grid
asmadmin
y
y

6) Mark disk for ASM

/etc/init.d/oracleasm createdisk DATA01 /dev/sdc1

7) add grid user limits in /etc/security/limits.conf

 vi /etc/security/limits.conf

grid   soft   nofile    1024
grid   hard   nofile    65536
grid   soft   nproc    16384
grid   hard   nproc    16384
grid   soft   stack    10240
grid   hard   stack    32768

8) install and configure Grid infrastructure with grid user

Now we are ready for migrate database from filesystem to ASM.
with oracle OS user:
1) connect to database with rman and make copy for database to the ASM

rman target /

RMAN> backup as copy database format '+DG01'; --our diskgroup name

2) make copy for current controlfile to ASM

backup as copy current controlfile format '+DG01';

3) Restore your spfile to a location inside ASM with the following rman script.

run {
backup as backupset spfile;
restore spfile to '+DG01/spfiletestdb.ora';
}

4) create pfile in $ORACLE_HOME/dbs with parameter SPFILE=’+DG01/spfiletestdb.ora’ and delete spfile from $ORACLE_HOME/dbs

cd $ORACLE_HOME/dbs
vi inittestdb.ora
SPFILE='+DG01/spfiletestdb.ora'

rm -rf spfiletestdb.ora

5) next steps include restore controlfile, switch database to copy, swith tempfile, add new redo logs in ASM and drop olders from filesystem:

sqlplus / as sysdba
SQL> startup nomount;
SQL> alter system set control_files='+DG01' scope=spfile;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> ! rman target /
RMAN> restore controlfile from '+dg01/testdb/controlfile/Backup.263.875890551';
RMAN> alter database mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> run {
set newname for tempfile 1 to '+DG01';
switch tempfile 1;
}
RMAN> exit
SQL> alter database add logfile '+DG01' size 50M;
SQL> alter database add logfile '+DG01' size 50M;
SQL> alter database add logfile '+DG01' size 50M;
SQL> alter database open resetlogs;
SQL> alter database drop logfile '/u01/app/oracle/oradata/TESTDB/onlinelog/redo03.log';
SQL> alter database drop logfile '/u01/app/oracle/oradata/TESTDB/onlinelog/redo02.log';
SQL> alter database drop logfile '/u01/app/oracle/oradata/TESTDB/onlinelog/redo01.log';

if you encounter an ORA-01623 while trying to delete a member just switch logs and retry. To switch a log execute

alter system switch logfile;

That’s all our database completely migrated to the ASM.

ORA-04098: trigger ‘SYS.XDB_PI_TRIG’ is invalid and failed re-validation

Today i’ll show you how to solve this error.

O/S : CentOS release 5.9 (Final)
DB  : Oracle database 11.2.0.3

We created database with script, with custom character set and with only XML DB component
1
2
after creation database there were many invalid objects related XML DB.
Solution:
add LD_LIBRARY_PATH in bash_profile or profile if it is not for oracle user

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Remove XDB, The catnoqm.sql script drops XDB.

SQL> shutdown immediate;
SQL> startup;
SQL> @ $ORACLE_HOME/rdbms/admin/catnoqm.sql

Install XDB,
The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified. The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage. If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:

SQL> catqm.sql A B C D

For Example:

SQL> @ $ORACLE_HOME/rdbms/admin/catqm.sql xdb_pass SYSAUX TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

SQL> shutdown immediate;
SQL> startup;
SQL> @ $ORACLE_HOME/rdbms/admin/catqm.sql xdb_pass SYSAUX TEMP YES     
SQL> @ $ORACLE_HOME/rdbms/admin/utlrp.sql

Check status of XDB

select comp_name, version, status from dba_registry where comp_id = 'XDB';

Check for invalid objects owned by XDB

select owner, object_name, object_type, status from dba_objects where status = 'INVALID' and owner = 'XDB';

good luck with XDB🙂

Enterprise Manager – perl: warning: Setting locale failed, LC_ALL = (unset)

today we faced warnings when starting agent of enterprise manager grid control.

emctl start agent

perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = "en_GB:en",
	LC_ALL = (unset),
	LANG = "en_GB"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").

check your locale settings by type locale
solution is very simple in the .bash_profile add LC_ALL=”en_GB.utf8″ :

locale
.........
.........
vi .bash_profile
export LC_ALL="en_GB.utf8"

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

when i was trying to connect rman appeared below error:

[oracle@oel6 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 10 15:06:05 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2647984357)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded


RMAN>

This is due too many RMAN connections, if there is not going RMAN backup then we can simple kill that processes.

select 'kill -9 ' || b.SPID,
       a.USERNAME,
       a.MACHINE,
       a.LAST_CALL_ET,
       a.MODULE,
       a.SID,
       a.PROCESS,
       a.EVENT,
       a.ACTION,
       a.osuser
  from v$session a, v$process b
 where a.PADDR = b.ADDR
   and module like '%rman%'
 order by MODULE;

result:
1
then just kill that processes from operating system

[oracle@oel6 ~]$ kill -9 23285
[oracle@oel6 ~]$ kill -9 23283
[oracle@oel6 ~]$ kill -9 23617
[oracle@oel6 ~]$ kill -9 23646

[oracle@oel6 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 10 15:59:14 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2647984357)

RMAN>

ORA-27369: job of type EXECUTABLE failed with exit code: 255

When i was trying to execute backup script from dbms_scheduler job, which looks like

begin
  sys.dbms_scheduler.create_job(job_name            => 'BACKUP_DB',
                                job_type            => 'EXECUTABLE',
                                job_action          => '/fra/backup_script/backup_full_disk.sh',
                                schedule_name       => 'BACKUP_DATABASE',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;

schedule is next

begin
  sys.dbms_scheduler.create_schedule(schedule_name   => 'BACKUP_DATABASE',
                                     start_date      => SYSDATE,
                                     repeat_interval =>'Freq=Daily;Interval=1;ByHour=09;ByMinute=00;BySecond=00',
                                     end_date        => to_date(null),
                                     comments        => '');
end;

manual execution script:

begin
dbms_scheduler.run_job('BACKUP_DB');
end;

appears next error:
ORA-27369: job of type EXECUTABLE failed with exit code: 255
ORA-06512: at “SYS.DBMS_ISCHED”, line 185
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 486
ORA-06512: at line 2

solution is very simple:
The script I was trying to execute was missing the

#!/bin/sh

as the first line of the script. This lets the exec function being called internally by the scheduler know that the script is a script, and not a binary executable. While this isn’t required to run a script from the command line, it is required to run it through the DBMS_SCHEDULER function on a Linux system.

So i added #!/bin/sh in my executable script at the first line and it worked.