Database Links

Create New Directory:

CREATE DIRECTORY dmpdir AS '/opt/oracle';

 

Set Permissions on New Directory:

GRANT read, write ON DIRECTORY dmpdir TO scott;

 

Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

Show Current Directory:

SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

 

DIRECTORY_PATH
--------------------------------------------------------------------------------
/home/oracle/app/oracle/admin/DBname/dpdump/

 

 

As root user:

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle

 

Oracle Required Packages:
yum install xorg-x11-utils
yum install xorg-x11-fonts-*
yum install xorg-x11-xauth
yum install xorg-x11-apps
yum install binutils
yum install compat-libcap1
yum install compat-libstdc++-33
yum install libgcc
yum install libstdc++
yum install libstdc++-devel
yum install sysstat
yum install gcc
yum install gcc-c++
yum install ksh
yum install make
yum install glibc
yum install glibc-devel
yum install libaio
yum install libaio-devel


As oracle user:

- Set kernal parameters and file limits.  (Oracle can do this for you during install after setting DB passwords)

- Select Server class
- Select Single Instance DB installation
- Select Advanced Install
- Select your language(s)
- Select location to install DB software
- Select General Purpose
- name the DB
- name the container DB
** - Select the Character set (Unicode AL32UTF8)
- Select location to install the DB files
- Don't register with Cloud Control
- Enable recovery (If Required, yes for production)
- Set DB user passwords
- Select Fix and check again

 

 

Ora12 Install

 

As root user:

/home/oracle/app/oraInventory/orainstRoot.sh
/home/oracle/app/oracle/product/12.1.0/dbhome_1/root.sh

 

Modify /etc/oratab to autoload DB

 

 

 

 

http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31042/configsql.htm#EGBEHDJD
http://www.bereanstechnology.com/?q=node/42

Install:

binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.1
gcc-c++-4.1.1
glibc-2.5-12
glibc-2.5-12 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5-12 (32 bit)
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libgcc-4.1.1
libgcc-4.1.1 (32 bit)
libstdc++-4.1.1
libstdc++-4.1.1 (32 bit)
libstdc++-devel 4.1.1
make-3.81
numactl-devel-0.9.8.x86_64
sysstat-7.0.0
unixODBC-2.2.11 (32 bit) or later
unixODBC-devel-2.2.11 (64 bit) or later
unixODBC-2.2.11 (64 bit) or later

unixODBC unixODBC-devel binutils compat-libstdc++ elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc-devel libaio libaio-devel libgcc libstdc++ libstdc++-devel make numactl-devel sysstat

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -g oinstall -G dba oracle
passwd oracle
<oracle>


vi /etc/sysctl.conf

add:

fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576


/sbin/sysctl -p
/sbin/sysctl -a

vi /etc/security/limits.conf

add:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536


mkdir -p /usr/local/app/
chown -R oracle:oinstall /usr/local/app/
chmod -R 775 /usr/local/app/


su - oracle

vi .bash_profile

add:

TMP=/usr/local/app/tmp
TMPDIR=/usr/local/app/tmp
export TMP TMPDIR
ORACLE_BASE=/usr/local/app/oracle
ORACLE_SID=ARS
export ORACLE_BASE ORACLE_SID

export DISPLAY=oss-srv-lab-01:0.0

runInstaller


***************POST INSTALLATION:********************************


vi .bash_profile

add:

TMP=/usr/local/app/tmp
TMPDIR=/usr/local/app/tmp
export TMP TMPDIR
ORACLE_BASE=/usr/local/app/oracle
ORACLE_SID=ARS
ORACLE_HOME=/usr/local/app/oracle/product/11.2.0/dbhome_1
export ORACLE_BASE ORACLE_SID ORACLE_HOME

PATH=$PATH:$HOME/bin:/usr/local/bin:/usr/bin:$ORACLE_HOME/bin
LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:$ORACLE_HOME/lib

export PATH LD_LIBRARY_PATH


vi /etc/oratab

add:

ARS:/usr/local/app/oracle/product/11.2.0/dbhome_1:Y


As root user create new file "oracle" (init script for startup and shutdown the database) in /etc/init.d/ directory with following content:


#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/usr/local/app/oracle/product/11.2.0/db_1"

case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

Execute (as root) following commands (First script change the permissions, second script is configuring execution for specific runlevels):


chmod 750 /etc/init.d/oracle
chkconfig --add oracle --level 0356

2. (Optional) Auto Startup and Shutdown of Enterprise Manager Database Control

As root user create new file "oraemctl" (init script for startup and shutdown EM DB Console) in /etc/init.d/ directory with following content:


#!/bin/bash
#
# oraemctl Starting and stopping Oracle Enterprise Manager Database Control.
# Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Enterprise Manager DB Control startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/usr/local/app/oracle/product/11.2.0/db_1"

case "$1" in
start)
echo -n $"Starting Oracle EM DB Console:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle EM DB Console:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

Execute (as root) following commands (First script change the permissions, second script is configuring execution for specific runlevels):


chmod 750 /etc/init.d/oraemctl
chkconfig --add oraemctl --level 0356

*****************SETUP GATEWAYS******************

Run gateways installer.

setup:
$ORACLE_HOME/network/admin/listner.ora
$ORACLE_HOME/network/admin/tnsnames.ora
$ORACLE_HOME/dg4msql/admininitdg4msql.ora - This file name needs to match the SID in listner.ora

lsnrctl stop
lsnrctl start

Create Database link in sqlplus:

CREATE PUBLIC DATABASE LINK twapp CONNECT TO "system" IDENTIFIED BY "ars" USING 'dg4msql';

************NOTES*************************

oracle scripts to be run as root:

/usr/local/app/oraInventory/orainstRoot.sh
/usr/local/app/oracle/product/11.2.0/dbhome_1/root.sh

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /usr/local/app/oracle/product/11.2.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


-------------------------------------------------------------------
The Database Control URL is https://oss-srv-dodb-02.sydney.gnoc.lucent.com:1158/em

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.
The encryption key has been placed in the file: /usr/local/app/oracle/product/11.2.0/dbhome_1/oss-srv-dodb-02.sydney.gnoc.lucent.com_ars/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

https://oss-srv-dodb-02.sydney.gnoc.lucent.com:1158/em

alter user <username> identified by "<password>";

Make sure global domain is set:

SELECT * FROM GLOBAL_NAME;
ALTER DATABASE RENAME GLOBAL_NAME TO <New Name>;


To Show DB Links:

COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/


Test DB Link:

select sysdate from dual@<DB NAME>

CREATE PUBLIC DATABASE LINK SDB01NEXTGENFANTOM CONNECT TO GENERICREADONLY IDENTIFIED BY "generic=readonly" USING 'dg4msql-sdb-01-NextgenFantom';
CREATE PUBLIC DATABASE LINK SDB01NEXTGENSERVICES CONNECT TO tracetools IDENTIFIED BY "trace=tools" USING 'dg4msql-sdb-01';
CREATE PUBLIC DATABASE LINK SDB02INVENTORYMANAGER CONNECT TO ordersync IDENTIFIED BY "order=sync" USING 'dg4msql-sdb-02-im';
CREATE PUBLIC DATABASE LINK SDB02PANDADB CONNECT TO GENERICREADONLY IDENTIFIED BY "generic=readonly" USING 'dg4msql-sdb-02-PandaDB';
CREATE PUBLIC DATABASE LINK SDB02SERVICES CONNECT TO ordersync IDENTIFIED BY "order=sync" USING 'dg4msql-sdb-02-services';
CREATE PUBLIC DATABASE LINK XCOM CONNECT TO system IDENTIFIED BY manager USING 'XCOM';
CREATE PUBLIC DATABASE LINK DEV03NEXTGENFANTOMRO CONNECT TO GENERICREADONLY IDENTIFIED BY "generic=readonly" USING 'dg4msql-dev-03-NextgenFANTOM';
CREATE PUBLIC DATABASE LINK DEV03NEXTGENFANTOMRW CONNECT TO remedywriteaccess IDENTIFIED BY "remedy=writeaccess" USING 'dg4msql-dev-03-NextgenFANTOMRW';


CREATE PUBLIC DATABASE LINK DEV03test CONNECT TO tracetools IDENTIFIED BY "trace=tools" USING 'dg4msql-dev-03-NextgenFANTOMTEST';

Drop Database Link:

DROP PUBLIC DATABASE LINK <db name>;


Show how many links can be open:

SQL> sho parameter links;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 100
open_links_per_instance integer 100
SQL>

Alter this:

alter system set open_links=100 scope=spfile;
alter system set open_links_per_instance=100 scope=spfile;
dbshut / dbstart

 

S5 Box

Login