Oracle - SQL Server Gateway Installation and configuration

1) Download the gateway binaries

2) SQL Server IP Address and hostname has to registered in /etc/hosts of the oracle server.

3) Unzip the gateway software

4) Install the gateway binaries

Duing installation it prompts for the SQL Server Configuration Information

- SQL Server Database server host name

- SQL Server Database server port number

- SQL Server instance name

- SQL Server Database name

5) Run script root.sh as root user from gateway home.

6) runInstaller invoke NETCA to configure the listener, Cancel it.


---------------------------------------------------
Configuring Oracle Database gateway for SQL Server:
---------------------------------------------------

1) Configure gateway initalization parameter file

- Choose System Identifier (SID) for the gateway

* We need one gateway instance for each sql server database we are accessing.

* The default SID is dg4msql

* If we need to access two SQL server databases the we require two gateways

- Customize the initalization parameter file

* The parameter file must be avilable when the gateways is started.

* Location of the parameter file is $ORACLE_HOME/dg4msql/admin/initdg4msql.ora

* HS_FDS_CONNECT_INFO=hostname:port number//database_name or instance_name

e.g: HS_FDS_CONNECT_INFO=[10.1.100.40]:1433//SSEM

2) Configure Oracle Net for the Gateway

- Gateway information needed to be added to the oracle net listener configuration file listener.ora resides in $ORACLE_HOME/rdbms/admin, Where $ORACLE_HOME is the location where Gateway has been installed.

LISTENER =
(ADDRESS=
(PROTOCOL=TCP)
(HOST=HOSTNAME)
(PORT=1530))

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4mssql)
(ORACLE_HOME=Gateway_HOME)
(PROGRAM=dg4msql)
)
)

3) Stop and start the gateway listener

- export ORACLE_HOME and PATH environment variables pointing to GATEWAY_HOME

- lsnrctl stop

- lsnrctl start


4) Configure the Oracle database for gateway access

- Configure tnsnames.ora of the database home

dg4msql=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTNAME)(PORT=1530))

(CONNECT_DATA=
(SID=dg4msql)
)
(HS=OK)
)


5) Create database links



- Create public database link dg4msql connect to "sa" identified by "P@55w0rd!" using 'dg4msql';

Netbackup Notes

RMAN is most common utility used to take backup, restore and recover the oracle databases. It has the capabilities to interact with 3^rd party media management software like VERITAS’s Netbackup, IBM’s TIVOLI, and HP’s data protector. Oracle has secure backup, its own media management solution.

Implementing RMAN solution depends on number of factors:

·         Number of Databases you plan to backup

·         the size of the database

·         Recovery time objective

·         Recovery point objective

·         Accounting for loss of datafile

·         Account for block level corruption recovery.

·         Available Netbackup media streams

·         RMAN channel allocation

·         Tablespaces structure (multiple datafiles or single bigfile), #CPU, Network bandwidth, are some of the factors which play roles in designing RMAN Backup and Recovery solution.

RTO: Recovery Time Objective

                The maximum time where the databases and applications to be restored and available for the business

RPO: Recovery Point Objective

                The amount of data loss can be tolerated. This is usually dictated by the business continuity group within your organization.

A net backup set consists of the following

1)      Netbackup master server

2)      Netbackup media server

3)      Netbackup media tape manager unit

4)      Netbackup disk storage unit.

Netbackup server is responsible for managing backup, archive and restore operations, It has its own database where it stores information about backups, archives and restore activities.

NB_ORA_SERV: Netbackup Master server, Here an Oracle DBA interact with Netbackup master server to perform backup and restore activities.

IF NB_ORA_SERV is not configured properly we may get the following alerts.

ORA-19511: Error received from media manager layer, error text:

   VxBSAValidateFeatureId: Failed with error:

   Server Status:  cannot connect on socket

Netbackup Policy:

Q) Why a DBA should know the Netbackup Policy?

A) A DBA should know where backups would go and what happens to the backup files etc. So that A DBA can create backup and restore scripts with proper policies.

A Netbackup policy has the following two sub sections

1)      Client (NB_ORA_CLIENT)

2)      Schedule (NB_ORA_SCHED)

Netbackup policy is typically linked to SLP i.e. Storage Life Cycle Policy where we define where and how the backup files will be stored and how long it can be preserved.

RMAN uses SEND command to indicate where the backups will be stored.

We should use ORACLE when we are defining Netbackup policy type to work with RMAN and a Netbackup oracle database agent license is required to select policy type as ORACLE.

A policy can be defined by NB_ORA_POLICY, If it is not defined properly then we may get following errors.

ORA-19511: Error received from media manager layer, error text:

   VxBSACreateObject: Failed with error:

   Server Status:  the specified policy does not exist in the configuration database

NETBACKUP Schedule for RMAN backups

Every policy has a schedule, upon creating a Netbackup policy a default schedule will be automatically created.

The schedule is nothing but the date and time when the backups need to occur as well as type of the backup (User Defined or Automatic) to initiate.

Automatic Backup Types: FULL, Cumulative, Incremental and differential.

User backup Types: Application Backup

Backup Window: Open schedule, Open schedule include the entire week, a job may run at any time of day or night.

NETBACKUP Client for RMAN backups:

Client is another subsection of Netbackup policy

NB_ORA_CLIENT is used to specify the client name, here the oracle database server from where RMAN backup and restore will be initiated is the Netbackup client.

Netbackup Oracle Database agent:

On the host machine where the oracle database is running we should install Netbackup Oracle database agent. RMAN uses Netbackup libraries to interact with Netbackup media.

Netbackup Database Agent license verification:

Following command could be run on Netbackup Master Server

/usr/openv/netbackup/bin/admincmd/get_license_key

The command will display a Menu on the Unix terminal. Press option F to list the license. Next it prompts for hostname.

Pressing Enter, will lists information for all keys including Oracle Database Agent Active

Ensure Netbackup Oracle software is installed on the Oracle database server, this includes the Netbackup database agent.

Once the Netbackup client is installed, you will see either of the directories created on the oracle database server:

/usr/openv/netbackup/dbext or

/usr/openv/netbackup/ext/db_ext

dbext/db_ext will have database specific subfolder - db2, informix, oracle, sap, sybase with sample Netbackup scripts.

$ pwd

/usr/openv/netbackup

$ cd dbext

$ cd ..

$ cd ext/db_ext

$ cd oracle

$ pwd

/usr/openv/netbackup/ext/db_ext/oracle

Re-LINK Oracle backup Library:

Before you can use RMAN to interact with Netbackup, Oracle database has to be re-linked to use Netbackup's backup library. Netbackup provides Oracle specific backup library for every platform, Identify the appropriate library for your environment.

In this exercise, we are using Oracle database version 11.2.0.1.0 64bit installed on Solaris 10 SPARC servers.Netbackup Library is: Solaris (SPARC) 64-bit libobk.so64.1

Two ways to do this:

   1) Automatically linking [preferred method]

   2) Manual Linking

Automatic Linking:

1.      Set ORACLE_HOME

2.      Set ORACLE_SID

3.      Shutdown Oracle Instance before re-linking the Netbackup library

4.      To automatically re-link Oracle library run the script provided by the Netbackup. Script is located in following directory /usr/openv/netbackup/bin/oracle_link. This script writes output to /tmp/make_trace

5.      $ pwd

6.      /usr/openv/netbackup/bin

7.      $ ls -ltr *link*

8.      -r-xr-xr-x   1 root     bin        26286 Apr 24  2014 oracle_link

9.      Start Oracle Instance

10.  Repeat this on all Oracle databases servers

$

$ ls -l $ORACLE_HOME/lib/libobk.so

/u01/apps/oracle/product/11.2.0/dbhome_1/lib/libobk.so: No such file or directory

$

$

$

$ /usr/openv/netbackup/bin/oracle_link

Sunday, April  7, 2013  5:05:31 PM EDT

All Oracle instances should be shutdown before running this script.

Please log into the Unix system as the Oracle owner for running this script

Do you want to continue? (y/n) [n] y

LIBOBK path: /usr/openv/netbackup/bin

ORACLE_HOME: /u01/apps/oracle/product/11.2.0/dbhome_1

Oracle version: 11.2.0.1.0

Platform type: sparc

Linking LIBOBK:

ln -s /usr/openv/netbackup/bin/libobk.so64.1 /u01/apps/oracle/product/11.2.0/dbhome_1/lib/libobk.so

Done

Please check the trace file located in /tmp/make_trace.12150

to make sure the linking process was successful.

$

$

$

$ ls -l $ORACLE_HOME/lib/libobk.so

lrwxrwxrwx   1 oracle  dba         38 Apr  7 17:05 /u01/apps/oracle/product/11.2.0/dbhome_1/lib/libobk.so -> /usr/openv/netbackup/bin/libobk.so64.1

$

$

$ cat /tmp/make_trace.12150

Sunday, April  7, 2013  5:05:31 PM EDT

All Oracle instances should be shutdown before running this script.

Please log into the Unix system as the Oracle owner for running this script

Do you want to continue? (y/n) [n] y

LIBOBK path: /usr/openv/netbackup/bin

ORACLE_HOME: /u01/apps/oracle/product/11.2.0/dbhome_1

Oracle version: 11.2.0.1.0

Platform type: sparc

Linking LIBOBK:

ln -s /usr/openv/netbackup/bin/libobk.so64.1 /u01/apps/oracle/product/11.2.0/dbhome_1/lib/libobk.so

Done

$

$

Manual Linking:

1.      To manually link, perform following steps

2.      Set ORACLE_HOME

3.      Set ORACLE_SID

4.      Shutdown Oracle Instance before re-linking the Netbackup library

5.      Verify if $ORACLE_HOME/lib/libobk.so file is present. If new installation and first time setup the file will not exists. If the file is present, backup the file to different name like libobk.so.bak.`date +"%Y%m%d%H%M%S"`. 

6.      Run this command to re-link the library 

      ln -s /usr/openv/netbackup/bin/libobk.so64.1 libobk.so

7.      Start Oracle Instance 

http://www.em-dba.com/emblog02.html

Oracle Enterprise Manager Cloud Control Notes

EM cloud control delivers centralized monitoring, Administering the oracle and non-oracle systems.

Architecture of Oracle EM Cloud Control:

EM Cloud control includes the following components

                - Oracle Management agent

                - Oracle Management Service

                - Oracle management Repository

                - Plug-ins

                - EM cloud control console

Oracle Management Agent:

                - OMA enables us to convert an unmanaged host to a managed host in the Enterprise manager system

                - It works in conjunction with Plug-ins to monitor the targets running on the managed host.

                - By installing OMS for the first time we receive a central agent.

                - The central agent monitors the first OMS host and the targets running on the first OMS host

                - To monitor other hosts and the targets running on the other hosts, we must install Standalone Management Agent on the each hosts.

Oracle Management Service:

                - OMS is a web based application that organizes OMA and plug-ins to discover targets, monitor and manage them and stores then in the repository for the future reference and analysis.

                - It also renders (Provide/give) user interface for the EM Cloud control

                - OMS is deployed in the middleware home which is the top directory for Oracle Weblogic server, JDK, OMS, Plug-Ins, Oracle WT directory, Oracle Common, Other relevant configuration files and directories.

                - EM Clod Control installation wizard install automatically install the Oracle weblogic server and JDK while installing OMS

Oracle Management Repository:

                - It is storage location for keeping all the information collected by agents.

                - It consists of objects like tablespaces, jobs, packages, procedures and views.

                - The OMS uploads the monitoring data it receives from the OMA to management repository and then management repository organizes the data in the repository and makes it available to OMS to display on the EMC console.

                - At the time installation EMCC installation wizard configures the management repository in the existing certified database, it doesn't install a new database

Plug-Ins:

                - Pug-Ins are pluggable entities, Had special management capabilities to suit specific target types.

                - Plug-ins work in conjunction with OMS and management agent monitor every target in the environment.

                - Plug-ins have independent release cycles, So every time a new oracle product is released a supported plug-in for the product will be released to support monitoring of that new product.

Oracle JVMD Engine:

                - JVMD enables to diagnose the performance problems related to java applications

                - From 13c R1, JVMD engine will be installed and configured by default on the OMS as part of the fusion middleware deployment.

Oracle BI Publisher:

                - BI is oracle's primary reporting tool

                - From 13c R1, Oracle BI publisher will be installed and configured by default on the OMS.

EMCC Console:

                - It is user interface for the oracle EMCC.

                - With the help of the console we can monitor and administer the all targets.

                - All the systems and services including enterprise application systems, databases, hosts, middleware application servers, listeners, and so on, are easily managed from one central location.

EMCTL:

                - It is a command line tool where we can execute certain tasks on the OMS and management agents.

                - We can use it for starting and stopping OMS, agents and Setting up properties on OMS, list of targets being monitored by a specific management agent.

EMCLI