Non-system schema Exports in Oracle 9i

Shell Script:

oracle on Server-Name:/admin/home/oracle> cat non_system_schemas.sh

# /bin/ksh

exp userid=\'/ as sysdba\' parfile=/admin/home/oracle/non_system_schemas.par

PAR file:

oracle on Server-Name:/admin/home/oracle> cat non_system_schemas.par

file=/backup2/non_system_schemas.dmp log=/backup2/non_system_schemas.log owner=schema1,

schema2,

schema3,

schema4,

schema5,

schema6,

schema7,

schema8,

schema9,

schema10

Shell script Execution:

oracle on Server-Name:/admin/home/oracle>nohup /admin/home/oracle/non_system_schemas.sh &

[1]     7410

oracle on Server-Name:/admin/home/oracle>Sending output to nohup.out

oracle on Server-Name:/admin/home/oracle>jobs

[1] +  Running                 nohup /admin/home/oracle/non_system_schemas.sh &

oracle on Server-Name:/backup2>jobs

'[1] +  Running                 nohup /admin/home/oracle/non_system_schemas.sh &

Export terminated successfully without warnings.

^C[1] +  Done                    nohup /admin/home/oracle/non_system_schemas.sh &

List of system's schemas

SQL> select username,created from dba_users where USERNAME IN ('SYS','SYSTEM','OUTLN','ANONYMOUS','CTXSYS','DBSNMP','LBACSYS','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','WKSYS','WMSYS','XDB');

USERNAME                       CREATED

------------------------------ ---------

DBSNMP                         12-JUN-04

OUTLN                          30-JUL-99

SYSTEM                         30-JUL-99

SYS                            30-JUL-99

Set Commands

     /*  
        Multiple line comments
        Can go between these delimiters

     */ 

SET TERM      OFF 
-- TERM = ON will display on terminal screen (OFF = show in LOG only)

If we set TERM OFF output will not be returned to the standard output when we execute a script, It will be logged in to the spool file , If it set to ON then will be returned to standard output screen as well as it logged in to the spool file.

SET ECHO      ON 
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.

SET TRIMSPOOL ON 
-- TRIMSPOOL = ON will remove trailing spaces from spooled output

SET TRIMOUT   ON 

-- TRIMOUT = ON will remove trailing spaces from output

SET HEADING   OFF 
-- HEADING = OFF will hide column headings

SET FEEDBACK  OFF 
-- FEEDBACK = ON will count rows returned

SET PAUSE     OFF 
-- PAUSE = ON .. press return at end of each page

SET PAGESIZE  0   
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)

e.g.

SQL> set pages 0
SQL> select tablespace_name,current_users,round (USED_BLOCKS*8/1024/1024,2) "GB in Use",
round(FREE_BLOCKS*8/1024/1024,2) "Free GB",round(MAX_SIZE/1024,2) "Msize GB",
round(MAX_USED_BLOCKS*8/1024/1024,2) "Max used GB"
from v$sort_segment;  2    3    4
TEMP                                      460       1.88      37.54      39.42       39.42

In the above e.g.By setting pagesize to 0, It supress the headings.

SET LINESIZE  80 
-- LINESIZE = width of page (80 is typical)

SET VERIFY    OFF 
-- VERIFY = ON will show before and after substitution variables

-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG

--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;

SPOOL OFF

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

Understanding AWR

Report Header:

                This is a self-explanatory section which provides information about database name,database id, instance name, instance number, startup time, release number, RAC/NON-RAC system, Hostname, platform, number of cpus, cores, memory and snap interval etc.

Load Profile:

                It gives the information about workload on the database during the snapshot captured time / interval time.

DB Time: It is the amount of time spent on executing user calls. It doesn’t include background processes

CPU Time: It is the amount of CPU time spent on executing user calls. It doesn’t inclu background processes and time is in milliseconds.

Logical Reads: Consistent GETS + DB blocks GETS = Logical Reads

Block Changes: The number blocks modified during snapshot interval time

Physical Reads: The number of blocks request causing an I/O operation

Physical Writes: The number of physical writes writes

User calls: Number of user queries generated

Parses: The total number of parses including soft parses as well as hard parses

Hard parses: The number of hard parses

Soft parses: parses-hard parses

Sorts: Number of sorts performed

Logons: Number of logons during snapshot interval time

Executes: Number of SQLs executed during snapshot interval time

Transactions: Number of transactions per second

Compare the statistics with a base line snapshot report to determine if the database activity is abnormal.

Instance Efficiency Percentage:

                This section displays the collected information in the form of ratios for the several buffers like database buffer cache hit percentage and library cache hit percentage and shared pool memory usage statistics also available in this section. Instance efficiency should be close to 100%.

Execute to parse % and Parse CPU to Parse Elapsed %, having low values means that there could be a parsing problem. We may need to look at shared pool sizing issue or bind variables issue.

Redo NoWait%: Usually this stats is 99% or greater

In-memory Sort%: This deals about sort_area_size, hash_area_size and pga_aggregate_area, if any of these are not enough in size to handle the database load then In-memory Sort% will go down

Latch Hit%: Should be close to 100%

Soft parse%: with 98.20 % for the soft parse meaning that about 1.72 % (100 -soft parse) is happening for hard parsing. You might want to look at you bind variables issues.

% Non-Parse CPU: Most of our statements were already parsed so we weren't doing a lot of re parsing. Re parsing is high on CPU and should be avoided.

Buffer Nowait %:	99.99	Redo NoWait %:	100.00
Buffer Hit %:	93.06	In-memory Sort %:	100.00
Library Hit %:	98.67	Soft Parse %:	98.20
Execute to Parse %:	3.40	Latch Hit %:	99.98
Parse CPU to Parse Elapsd %:	0.01	% Non-Parse CPU:	96.21

Shared pool Statistics:

	Begin	End
Memory Usage %:	73.86	75.42
% SQL with executions>1:	92.61	93.44
% Memory for SQL w/exec>1:	94.33	94.98

Memory usage % is the shared pool usage, SO here 73.86% of our shared pool used and out of that almost 94% is being re-used.

If memory usage % is larger like 90% means that our shared pool is too small and if it is at 50% then shared pool is too large

crontab

Scheduling at OS can be performed in the following ways

1) Using crontab

* Crontab stands for cron table, Which dervied from greek word chronos (Chronos means time).
 * To schedule multiple system events at regular intervals we use crontab.
* Each user can have their own crontab.
* The location of the crontab files is /var/spool/cron/crontabs.
* The crontab files which control the access are /etc/cron.d/cron.allow and /etc/cron.d/cron.deny.
* cron jobs can be allowed or disallowed for individual users, as specified in the files cron.allow and cron.deny
* A user should be placed in the cron.allow to get access to the crontab.
* If the cron.allow file does not exist but the cron.deny file does, then a user must not be listed there in order to use a given    command.
* If neither of these files exists, only the superuser will be allowed to use a given command.
* Blank lines and leading spaces and tabs are ignored.
* If an entry in the crontab starts with # then the line will be treated as a commnet
* Comments cannot be allowed in the same line as cron commands,

2) Using at

* To schedule a single system event we use at
* The location of the at is /var/spool/cron/atjobs.
* The at files which control the access are /etc/cron.d/at.deny.


Crontab entry format should be as follows

m h dom mon dow <File name>

m - Minutes (0-59)

h - Hours (0-23)

dom - Date of Month (1-31)

mon - Month (1-12)

dow - Day of Week (0-6) ==> 0 means Sunday and 6 means Saturday


* Crontab entries can be listed by contab -l

* To view the your crontab, crontab -v

* crontab can be Edited by crontab -e

* Saving the edited crontab can be carried out as follows

Esc + :wq + Enter

we get the following message

crontab: Installting new crontab and the control will be returned to the command line.

* To remove crontab, crontab -r / contab -i which prompts [Y|N] before removing crontab.

Examples of crontab

Database full backup at 09:10 AM on every sunday.

10 09 * * 0 /home/oracle/Backup-Full.sh

Database incremental backup daily at 08:30 PM except on sundays

30 20 * * 1-6 /home/oracle/Backup-Incr.sh

Archivelog backup at every 3 hours starting at 12:00 AM

00 00,03,06,09,12,15,18,21 * * 1-6 /home/oracle/Backup-Archive.sh

Note: I have posted the examples without testing the commands, Please make sure to check it in practice system.The notes is based on my understanding.


Thanks,